05 June 2011

Stupid Unix Tricks: Downgrading Shotwell from 0.7.2 in Ubuntu Maverick to 0.6.1 in Debian Squeeze

I recently purchased a new computer and set it up from scratch with the latest version of Debian Stable (named"Squeeze").  This wouldn't be a problem, except my previous computer was running Ubuntu Maverick--- a Debian-based distro, but one with newer but less stable (in my experience, and why I switched) versions of the software.

Generally, I don't care about running the latest and greatest. And for some applications such as Firefox or Thunderbird, newer versions are available. I also installed some non-free applications that I used, such as Skype or Flash. No problem.

Except for Shotwell, the photo manager. Ubuntu Maverick runs version 0.7.2, while Debian Squeeze ran 0.6.2. The latest version on the Shotwell web site is 0.10.1, and I didn't want to faff about installing newer libraries and risking problems with my new machine.

Since I have about 15,000 digital photos accumulated over the past 15+ years, I didn't want to lose this information.

So, I did some hacking and managed to downgrade the database using the following steps:
  1. Back up the newer shotwell database (usually in ~/.shotwell/data/photo.db).

  2. Copy the photos (usually in ~/Pictures) to the new computer, preserving the same locations. (If you're feeling adventurous, update the PhotoTable table in the database after importing it.)

  3. Copy the thumbnails from ~/.shotwell/thumbs to the new computer.

  4. Run Shotwell to create a new blank database, or create a new blank database in SQLite using the following:

    PRAGMA foreign_keys=OFF;
    BEGIN TRANSACTION;
    CREATE TABLE VersionTable (id INTEGER PRIMARY KEY, 
      schema_version INTEGER,
      app_version TEXT, user_data TEXT NULL);
    INSERT INTO "VersionTable" VALUES(1,7,'0.6.1',NULL);
    CREATE TABLE PhotoTable (id INTEGER PRIMARY KEY, 
      filename TEXT UNIQUE NOT NULL,
      width INTEGER, height INTEGER,
      filesize INTEGER,
      timestamp INTEGER, exposure_time INTEGER,
      orientation INTEGER, original_orientation INTEGER,
      import_id INTEGER, event_id INTEGER, transformations TEXT,
      md5 TEXT, thumbnail_md5 TEXT, exif_md5 TEXT,
      time_created INTEGER, flags INTEGER DEFAULT 0,
      file_format INTEGER DEFAULT 0, title TEXT, backlinks TEXT,
      time_reimported INTEGER, editable_id INTEGER DEFAULT -1);
    CREATE TABLE EventTable (id INTEGER PRIMARY KEY, name TEXT, 
      primary_photo_id INTEGER, time_created INTEGER);
    CREATE TABLE TagTable (id INTEGER PRIMARY KEY,
      name TEXT UNIQUE NOT NULL, photo_id_list TEXT,
      time_created INTEGER);
    CREATE INDEX PhotoEventIDIndex ON PhotoTable (event_id);
    COMMIT;
    
  5. Run the following script, updating the filenames of work72 and blank with the filenames of the newer shotwell database and blank older shotwell datebase:

    #!/bin/bash
    
    work72=photo72.db
    blank=photo61.db
    
    dump72=`tempfile --suffix .sql`
    
    for i in EventTable PhotoTable TagTable
    do
      echo ".dump $i"
    done |sqlite3 $work72 >  $dump72
    
    work61=`tempfile --suffix .db`
    
    cat $dump72 |sqlite3 $work61
    
    echo "
    BEGIN TRANSACTION;
    CREATE TABLE PhotoTableTemp (id INTEGER PRIMARY KEY, 
      filename TEXT UNIQUE NOT NULL,
      width INTEGER, height INTEGER,
      filesize INTEGER,
      timestamp INTEGER, exposure_time INTEGER,
      orientation INTEGER, original_orientation INTEGER,
      import_id INTEGER, event_id INTEGER, transformations TEXT,
      md5 TEXT, thumbnail_md5 TEXT, exif_md5 TEXT,
      time_created INTEGER, flags INTEGER DEFAULT 0,
      file_format INTEGER DEFAULT 0, title TEXT, backlinks TEXT,
      time_reimported INTEGER, editable_id INTEGER DEFAULT -1);
    INSERT INTO PhotoTableTemp SELECT id, filename, width, height,
      filesize, timestamp, exposure_time, orientation,
      original_orientation, import_id, event_id, transformations,
      md5, thumbnail_md5, exif_md5, time_created, flags,
      file_format, title, backlinks, time_reimported,
      editable_id FROM PhotoTable;
    DROP TABLE PhotoTable;
    CREATE TABLE PhotoTable (id INTEGER PRIMARY KEY, 
      filename TEXT UNIQUE NOT NULL,
      width INTEGER, height INTEGER,
      filesize INTEGER,
      timestamp INTEGER, exposure_time INTEGER,
      orientation INTEGER, original_orientation INTEGER,
      import_id INTEGER, event_id INTEGER, transformations TEXT,
      md5 TEXT, thumbnail_md5 TEXT, exif_md5 TEXT,
      time_created INTEGER, flags INTEGER DEFAULT 0,
      file_format INTEGER DEFAULT 0, title TEXT, backlinks TEXT,
      time_reimported INTEGER, editable_id INTEGER DEFAULT -1);
    INSERT INTO PhotoTable SELECT * FROM PhotoTableTemp;
    DROP TABLE PhotoTableTemp;
    COMMIT;" | sqlite3 $work61
    
    echo ".dump" |sqlite3 $work61 | \\
      grep -v ^CREATE |sqlite3 $blank
    
    cat /dev/null | sqlite3 $blank
    
    rm $dump72
    rm $work6

    Basically, 0.7 adds a new field (rating) to the PhotoTable table. Because SQLite's ALTER TABLE implementation does not (currently) allow the removal of fields, we have to create a new table and copy the old table to the new one, then delete the old one and re-copy the new table to the new old table. Messy, but it works.

  6. Copy the resulting database back to ~/.shotwell/data/photo.db.

  7. Run Shotwell. Well, not quite. You may get a segmentation fault. After some faffing about, it seems that after running Shotwell three or four times, it fixes whatever problems it has with the database and runs with the imported data.

    Had I more time, I would have found out what Shotwell did to the data or some other configuration setting to work with the data. (I spent way too much time experimenting with the data before realizing the various changes that I made had nothing to do with getting it to run. Had I more time, I would have compared the changes Shotwell made with the original data.)

You're mileage may vary. I'd like to hear if it works for you (and if you figure out what to do to the database so that Shotwell won't crash initially).