Downgrading Shotwell database from 23/0.31.5 to 20/0.30.17

Downgrading Shotwell database.md

Downgrading Shotwell database from 23/0.31.5 to 20/0.30.17

What this is about

I decided to move back from Ubuntu (23.04) to Debian (Bookworm), essentially because the Snap package management system just annoyed the hell out of me. The one significant thing that broke was Shotwell. Debian Stable is stable precisely because it's not at the bleeding edge, and the version of Shotwell it ships with is older than that in Ubuntu 23.04, to the extent that the databases are incompatible.

How I arrived at this fix

If you're wanting to downgrade Shotwell databases the chances are the versions you're going to want to work between are different from those I wanted to work between, so how I derived my fix is probably more useful to you than what my actual fix was.

I dumped the schema from the Ubuntu version by (in Ubuntu)

sqlite3 ~/.local/share/shotwell/data/photo.db
.output ubuntu.schema.sql
.schema
.exit

I dumped the schema from the Debian version in exactly the same manner except setting output to debian.schema.sql.

I then compared the two files with diff -b. Unfortunately, the order in which entities were output by the .schema directive differed and the use of whitespace was inconsistent, so interpreting the output took some care. It would probably have been possible to automate this step, but it would have taken longer than doing it by hand. So, by hand, I derived this sequence of fixes:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
update VersionTable set schema_version = 20, app_version = '0.30.17' where id = 1;
alter table PhotoTable drop column has_gps;
alter table PhotoTable drop column gps_lat;
alter table PhotoTable drop column gps_lon;
alter table FaceTable drop column ref;
alter table FaceLocationTable drop column vec;
alter table FaceLocationTable drop column guess;
COMMIT;
.save

I then made a backup copy of ~/.local/share/shotwell/data/photo.db in case it all went horribly wrong, and then invoked

cat fixes.sql | sqlite3 ~/.local/share/shotwell/data/photo.db

Reader, it did not go horribly wrong.

fixes.sql

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
update VersionTable set schema_version = 20, app_version = '0.30.17' where id = 
1;
alter table PhotoTable drop column has_gps;
alter table PhotoTable drop column gps_lat;
alter table PhotoTable drop column gps_lon;
alter table FaceTable drop column ref;
alter table FaceLocationTable drop column vec;
alter table FaceLocationTable drop column guess;
COMMIT;
.save