www.herd-of-neurons.com

Do you use F-Spot? At some point you'll want to move all those photos to a different drive or directory. When you do, you'll want F-Spot to keep track of them in their new home.

Here's my setup:
Ubuntu 9.04, x86_64 with f-spot 0.5.0.3

I had screwed up and imported about 1700 photos into my home directory instead of my Photos directory. I could see them and tag & comment on them ... but the photos themselves were in my home directory. I already had around 3000 photos in the Photos directory so my solution has to merge these two sets.

Step 1 shutdown F-Spot. We'll be playing with the database so lets not confuse F-Spot.

Step 2 was to backup the photos and the f-spot database. Don't skip this step - losing your photos is not a happy ending, spend the time it takes to make a backup.
To backup the database, copy ~/.gnome2/f-spot/photos.db <some place safe>

Here is how I solved this.
Items in RED are values from my equipment - you'll need to substitute values for your equipment.

Items in Green are commands to be entered.

Items in Grey are system prompts or responses.

Step 3 was to get sqlite installed:
$ sudo apt-get install sqlite3

Step 4 was to write a script that would move the photos to the correct destination folder and also write the sql commands to update f-spot's database. Here's the script I came up with:

for FILENAME in *jpg; do
   TARGETPATH=$(ls -l ${FILENAME} | sed -e "s/.*\([0-9][0-9][0-9][0-9]\)-\([0-9][0-9]\)-\([0-9][0-9]\).*/\1\/\2\/\3/")
   if [[ ! -d ${TARGETPATH} ]]; then
      if ! mkdirhier ${TARGETPATH}; then
         echo "Error creating ${TARGETPATH}" >&2
         continue
      fi
   fi
   mv ${FILENAME} ${TARGETPATH}
   echo "update photos set uri = \"file:///home/jardine/Photos/${TARGETPATH}/${FILENAME}\" where uri = \"file:///home/jardine/${FILENAME}\";" >> database.commands
   echo "update photo_versions set uri = \"file:///home/jardine/Photos/${TARGETPATH}/${FILENAME}\" where uri = \"file:///home/jardine/${FILENAME}\";" >> photo_versions.commands
done

TARGETPATH takes the year, month and day from file's timestamp and converts it into a path. F-Spot does this when it imports a photo - I'm just emulating that behaviour here. If your timestamps aren't correct then this may not work very well for you.

You'll need to swap /home/jardine/ for the directory your photos are currently in.

Here is the top 5 lines of each file:
$ head -5 photo_versions.commands database.commands
==> photo_versions.commands <==
update photo_versions set uri = "file:///home/jardine/Photos/2009/08/02/img_0526.jpg" where uri = "file:///home/jardine/img_0526.jpg";
update photo_versions set uri = "file:///home/jardine/Photos/2009/08/02/img_0527.jpg" where uri = "file:///home/jardine/img_0527.jpg";
update photo_versions set uri = "file:///home/jardine/Photos/2009/08/02/img_0529.jpg" where uri = "file:///home/jardine/img_0529.jpg";
update photo_versions set uri = "file:///home/jardine/Photos/2009/08/03/img_0530.jpg" where uri = "file:///home/jardine/img_0530.jpg";
update photo_versions set uri = "file:///home/jardine/Photos/2009/08/02/img_0531.jpg" where uri = "file:///home/jardine/img_0531.jpg";

==> database.commands <==
update photos set uri = "file:///home/jardine/Photos/2009/08/02/img_0526.jpg" where uri = "file:///home/jardine/img_0526.jpg";
update photos set uri = "file:///home/jardine/Photos/2009/08/02/img_0527.jpg" where uri = "file:///home/jardine/img_0527.jpg";
update photos set uri = "file:///home/jardine/Photos/2009/08/02/img_0529.jpg" where uri = "file:///home/jardine/img_0529.jpg";
update photos set uri = "file:///home/jardine/Photos/2009/08/03/img_0530.jpg" where uri = "file:///home/jardine/img_0530.jpg";
update photos set uri = "file:///home/jardine/Photos/2009/08/02/img_0531.jpg" where uri = "file:///home/jardine/img_0531.jpg";

The last step was to run those generated SQL commands in sqlite3:
cat photo_versions.commands | sqlite3 .gnome2/f-spot/photos.db
cat database.commands | sqlite3 .gnome2/f-spot/photos.db