Removing dead/orphan entries from f-spot's database

This seems like a common problem for a lot of people -

dead or orphaned entries in f-spot :| technically, you shouldn't go behind f-spots back and delete the files directly, but sometimes ...well shit happens. like for me, I somehow managed to re-import the same pictures into my database twice..and was left with a crapload of duplicate images in my album..

the first problem is easily solvable, simply running

fdupes -dN

removes the duplicate entries in the given directory (use with caution)

but that still left us with f-spot's internal database still linking to dead entries

this makes me very sad...very very sad, as f-spot doesn't have a "refresh catalog" option.

so I came up with this not-so elegant but it works solution... so here we go

PROCEED WITH CAUTION....CAUTION I SAY!!! NOTE THE PROCEDURE BELOW WILL REMOVE REFERENCES TO MODIFIED VERSIONS AND SHITE AS WELL...


1.backup your database

cp ~/.gnome2/f-spot/photos.db ~/.gnome2/f-spot/photos.db.backup

CHECK CHECK AND DOUBLE CHECK if that worked..

2.extract the photo information

sqlite3 ~/.gnome2/f-spot/photos.db 'select id,uri from photos;' > entries

3.check for the dead entries

cat entries | while read i;do if [ ! -e "$(echo ${i} | cut -f2 -d'|' | sed 's/file:\/\///g')" ];then echo ${i};fi;done > deads

watch for line breaks...the above is a single line! Give it time to process...it may take a while depending on your db size (mine took 4 minutes)

4.delete the entries from the main table

cut -f1 -d"|" deads | while read i;do sqlite3 ~/.gnome2/f-spot/photos.db "delete from photos where id=${i};";done

.... AND from the version table

cut -f1 -d"|" deads | while read i;do sqlite3 ~/.gnome2/f-spot/photos.db "delete from photo_versions where photo_id=${i};";done

5. Now remve the working files you created

rm entries deads

and tadaaa!!!!!

a few things to note
1.it's not perfect
2.if you have edited versions, and the original is missing, references to edited versions will be removed.
3.you can fine tune the SQL in step 2 to restrict removals to specific folders or such;

eg:
SELECT id,uri FROM photos WHERE uri LIKE '%/2009/07/%';