This issue is more complex than this, since we also need to support users who started using f-spot when they ran sqlite2 and now only have sqlite3 installed.  The only way to convert the databases is to have both installed and dump from 2 into 3.

So instead of always using the sqlite db installed, if 3 is detected, it should be used and any existing version 2 db's converted as sqlite2 will probably be removed of the user's machine sometime in the future.

As it is right now, F-Spot will simply not handle any of these cases well at all.

I think the solution to the upgrade problem is going to have involve distributions shipping a static binary sqlite2dump program that is required for upgrading or something similar

Yeah, the upgrade thing is a separate issue.  Not all distros shipped sqlite version 2 anyway (we did for a brief period in rawhide, but it never made a final release).  I'm mostly concerned with building against sqlite 3, having sqlite 3 as a dependency, and the user installing sqlite 2.  We should never use sqlite 2 in that situation (explicitly use sqlite version 3).

In Db.cs,  sqlite_connection.ConnectionString should be updated to have version info.  E.g.: 

sqlite_connection.ConnectionString = "URI=file:" + path + ",version=3";

(hack we're using in Fedora)

is there any way to tell from the file what version it is?  we don't want to make an error the other way and force loading version 3 if the db is version 2 either.

running file seems to have version info (i don't have an sqlite2 db around to verify that with).

% file ~/.gnome2/f-spot/photos.db
/home/caillon/.gnome2/f-spot/photos.db: SQLite database (Version 3)

So I imagine there is some way, but it should be insignificant if we use the right version, and dependencies prevent the required sqlite version from being removed.

*** Bug 333494 has been marked as a duplicate of this bug. ***

*** Bug 333550 has been marked as a duplicate of this bug. ***

ok a quick peek at shared-mime-info shows



 <mime-type type="application/x-sqlite2">
    <_comment>SQLite2 database</_comment>
    <magic>
      <match type="string" value="** This file contains an SQLite" offset="0"/>
    </magic>
  </mime-type>
  <mime-type type="application/x-sqlite3">
    <_comment>SQLite3 database</_comment>
    <magic>
      <match type="string" value="SQLite format 3" offset="0"/>
    </magic>
  </mime-type>

which means it should be pretty simple to detect the type ourselves

does version=2 work to force a version 2 connection?

Version=3,URI=... is what Aaron uses for banshee (though it's probably not case sensitive).

Yes, beagle uses SQLITE_MAJ_VER.  See http://cvs.gnome.org/viewcvs/beagle/Util/ExternalStringsHack.cs.in?rev=1.8&view=markup

Created attachment 60784 [details] [review]
patch to detect sqlite version

this patch should hopefully make the connection string match the version of the file.  Does this solve the downgrade problem?

I think the only problem that remains with this patch if if someone installs sqlite2 before running f-spot for the first time they'll end up with an sqlite2 db (if I understand the mono binding issue) but I think we can probably live with that.

The main issue I have with this approach is that you are assuming that because there is an sqlite 2 file around, the user still has sqlite 2 around.  That is not necessarily the case.  It should default to whatever the package is built to use.

Additionally, I don't believe it solves the issue where:

sqlite 2 and 3 are installed.  The package was built to use sqlite 3.  In this case, it will erroneously default to sqlite version 2.

I think the only problem that remains with this patch if (ignoreing how gross it is) is that if someone installs sqlite2 before running f-spot for the first time they'll end up with an sqlite2 db (if I understand the mono binding issue) but I think we can probably live with that.

(In reply to comment #14)
> I think the only problem that remains with this patch if if someone installs
> sqlite2 before running f-spot for the first time they'll end up with an sqlite2
> db (if I understand the mono binding issue) but I think we can probably live
> with that.

That will make life hard for distributors who need to figure out package dependencies.

Why can't you just _require_ sqlite3, and force any users with sqlite2 photos.db files lying around to convert them?  The conversion process is simple and worked flawlessly for me.

If it defaults to whatever the package is built with then people upgrading will be completely out of luck.  Unless you have a plan that solves that I'm not sure what you suggest is any better.

Ryan because converting them requires you to have both sqlite2 and sqlite3 installed, there is no way to convert versions with just sqlite3 installed is there?

Other distributors have already shipped f-spot with version two, which means requiring version3 would break every existing f-spot install on those distributions.

(In reply to comment #19)
> If it defaults to whatever the package is built with then people upgrading will
> be completely out of luck.

That's IMO more of a problem for packagers to solve.  The problem is that sqlite 2's threading model is broken to hell to begin with, and never should have been enabled in 2.x, so people using sqlite 2 are already losing.  We've had a few reports of broken behavior with stuff that went away when the new threading stuff went in.  We should make them convert.  I agree with Ryan that sqlite 3 is a much better proposition.

If someone has an sqlite2 db, then they already have sqlite2 installed, so the next version of f-spot that requires sqlite3 would install it, then they'd have both installed and the upgrade would be simple.  You could put a big fat warning in the release notes.  I don't know any way around this and it's undoubtedly going to cause problems for users who don't read the release notes after an upgrade, however it seems like it has to be done -some- time, so better sooner rather than later. :)

I'll also note that that beagle also does it based on what the package was compiled with.

callion: beagle's index doesn't hold anything that can't be regenerated by reindexing the files, so throwing away the db on upgrade isn't a problem.  Banshee  has a little bit of extra info but not much.  F-Spot has tags and descriptions that can't in some cases (and weren't in all cases until recently) written to the file so discarding is a lot less than ideal.  Also f-spot is careful about the threading so that is a nonissue in this case.

I don't see how working if you have both versions installed makes things harder for the distribution.  If sqlite2 isn't installed you're in no worse shape.

ryan: unfortunately that isn't how it works.

At some point they are going to have to upgrade, anyway.  I can't imagine sqlite2 being supported for years on into the future (maybe I'm wrong).  Why not sooner rather than later?  It becomes a bigger problem in the future the longer sqlite2 is allowed to linger.

yeah they'll definitely have to upgrade it is just the going from supported to completely broken with in a single release with a poor upgrade path that I'm trying to avoid.  On the other hand the latest mono changes seem to have broken things again so I may just go ahead and force things.

In my setup it is not enough to convert/force creation of photos.db as sqlite3. I renamed libsqlite.so.0.8.6 and started f-spot. The resulting photos.db is v3.

bh@lifebook34:~/.gnome2/f-spot$ file photos.db
photos.db: SQLite database (Version 3)

Everything works like a charm until I rerename libsqlite to its original name. Launching f-spot then results in:

An unhandled exception was thrown: file is encrypted or is not a database

in <0x000ab> Mono.Data.SqliteClient.SqliteConnection:Open ()
in <0x0009d> Db:Init (System.String path, Boolean create_if_missing)
in <0x0008d> FSpot.Core:.ctor ()
in <0x0037a> FSpot.Driver:Main (System.String[] args)
.NET Version: 1.1.4322.2032

Same thing happends if I convert. I originally filed in Bug 333494 which is fixed if I stay purely with sqlite3. Is there any way of forcing sqlite3? 

I'm going to work on a patch this weekend which will dump & reload the database to convert from sqlite 2 to 3, if it detects that photos.db is sqlite 2. This will require that both libraries be installed, but it's a reasonable assumption that if the user has an sqlite 2 db, they will still have the library available to use.

Why is that a reasonable assumption?  Distributors tend to upgrade libraries.  We shipped sqlite (version 3) and an sqlite2 package for mono.  I did the packaging work to make sure that our mono packages were updated to use sqlite 3.  When the conversion was done, we removed sqlite2 from Core.  Users can re-install sqlite2 if they absolutely need for other apps, but it was broken anyway, which is why we wanted to get rid of it.  :)

This all happened in a day, and I imagine there's room for other distros to do similar things.

Then in removing the sqlite2 libraries, you only give users the option of wiping their (sqlite2) photos.db & starting again. Could you give a better suggestion then of how this migration could work?

I don't know of any distributions that actually *removes* packages automatically for the user without asking them, but even if they exist I think it should be up to the packager to warn & instruct users of the appropriate course of action if they want to convert their old data upon upgrading.  As long as f-spot provides the functionality to convert to sqlite3, there's nothing more it can do.

Sure you do.  Any rpm based distro removes packages.  That's what rpm -U does.  Additionally, any time there is a conflicts: or obsoletes: line in an rpm, dependency resolvers figure that out and remove the appropriate packages.  That has to work, else the packagers and you, the packager user, are screwed.

Okay I guess you caught me, I didn't know rpm did that automatically when upgrading (non-conflicting) packages.  I'll add that to my list of reasons for hating rpm-based distros so much. :-P  Of course if f-spot is marked as conflciting with sqlite2 it should -ask- to remove sqlite2, but that's not the case here.

If you're that paranoid about upsetting users who will have to upgrade you could just depend on both sqlite versions for a year or so, but it really seems like hand-holding when they can just read a note on how to upgrade in a README file like every other package I've ever installed.

F-Spot is written to be usable by most anybody.  It's an end-user application, not a developer's application.

If F-Spot detects a version 2 database and can't open it because sqlite2 is no longer installed, it should ideally pop up a dialog explaning to the user exactly how to upgrade it (by installing sqlite2 and re-runing f-spot).  Or we could have a website that would run their db through a CGI program and convert it for them. :)  Hmm, I actually like that idea a lot.

We need to have F-Spot convert databases from 2 to 3 now if it can.  I know I have both installed at the moment, but I probably won't for long.

I think we also shouldn't allow any new sqlite2 databases to be created, period.  Sqlite3 should be required.

#28: yes, we can force version=3.

Which is why I think I'll just do a simple forced-conversion patch to dump & reload if it sees an sqlite 2 DB - I think that it should save a copy as photos.db.2 or similar in case it doesn't quite work properly. 

Has this issue been resolved? Quite some time since last entry here, and I think SQLite3 is coming close and closer or?

yeah I consider this resolved now, upgrades are manual, f-spot with automatically work with existing 2 or 3 and tries version3 by default.