Reading a SQLite3 database from a simple Mono/C# application?

Question

I have been having problems with my F-Spot database for a while, but nothing crippling. Now, however, it seems like I do; I can't even launch F-Spot, since it crashes on startup. Checking the console, what breaks it is a UriFormatException with the message "System.UriFormatException: URI scheme must start with a letter and must consist of one of alphabet, digits, '+', '-' or '.' character.". This is running on Mono 2.4.4.0, not Microsoft's runtime.

Basically, I'm trying to find out what exact data rows are broken, so I can either repair them, or file a bug report against F-Spot. (I have been meddling around in the database file directly before, which might have caused this trouble - either way, it'd seem like it should handle something like that better than by crashing hard, but at this very moment I'm more interested in fixing the data than the exact reason why the failure is not being handled in a slightly more graceful manner.) The database is a SQLite3 file, and running it manually in the command-line sqlite3 application, I can select both against table metadata as well as the data tables themselves. Yet, I get an unhandled exception System.ApplicationException: file is encrypted or is not a database from Mono.Data.SqliteClient.SqliteConnection.Open() from a simple test application that is all but nothing other than a copy-paste from the official guide for SQLite and Mono. Linking against System.Data.dll and Mono.Data.SqliteClient.dll, I'm copying the relevant code here as well for completeness:

public static void Main(string[] args) {
   string connectionString = "URI=file:f-spot.photos.db";
   IDbConnection dbcon;
   dbcon = (IDbConnection) new SqliteConnection(connectionString);
   dbcon.Open();
   dbcon.Close();
   dbcon = null;
}

The database file is definitely there, and working fine (sqlite3 itself has no complaints about it). Googling nets me lots of pages comparing various small-sized database engines, and hints at the file perhaps being a SQLite2 database. However, AFAIK I have no SQLite2 libraries on my system, and file specifically claims that the file is a "SQLite 3.x database". Not only that, F-Spot versions newer than 0.3.5 require SQLite 3, and I'm on version 0.6.2, and have been for a long while now. Am I trying to read it using the wrong API, or is by any chance the DSN wrong?

Any suggestions on what to try would be appreciated. Sure I can use sqlite3 to extract the data, save it to text files, then parse those, but that introduces another couple of layers of uncertainty that I'd really rather avoid if possible by using as close to the same API as F-Spot as I can to access the data.

Answer 1

I think you need specifically set the version of sqlite engine in the connection string. Try adding "version=3" to your connection string constant. I've changed a bit your code, see if it would work for you.

public static void Main(string[] args) {
   string connectionString = "URI=file:f-spot.photos.db,version=3"; //<-- version is set to 3
   IDbConnection dbcon;
   dbcon = (IDbConnection) new SqliteConnection(connectionString);
   dbcon.Open();
   dbcon.Close();
   dbcon = null;
}

For more details check "Connection String Format" section of the documentation here

hope this helps, regards