Jul 8, 2009

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


1.backup your database
cp ~/.gnome2/f-spot/photos.db ~/.gnome2/f-spot/photos.db.backup

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;
SELECT id,uri FROM photos WHERE uri LIKE '%/2009/07/%';


Anonymous said...

you should also remove entries drom the tags table otherwise f-spot will get confused.

Anonymous said...

In my case orphan photos had an icon but obviously cant open them (a red x appears). So selecting and deleting from catalog in f-spot ui removed them.

SoE said...

@anon1: thanks. I realized this too shortly after posting this.

@anon2: That's true, however when you have over a 100,000 photos in your archive things get a little more complicated.

Anonymous said...

See something like this is relatively conceptually simple and so is its implementation, but I consider it not only imminently useful but vital; why hasn't someone in the F-Spot team taken a couple hours and done this?

Perhaps add an entry in the menu next to the duplicate detection (which requires md5 hashing every photo in your library) to remove orphans or duplicate files (compare filenames)?

Anonymous said...

I got about as far as step 2, running with Ubuntu 10.04 and F-Spot as my screen saver (pretty much a default install). URI is no longer a column in the photos table, so before going on, I just renamed the photos.db file in .config/f-spot/ to photos.db.not and started F-Spot. I got an import dialog, navigated to the place where I keep all my pictures (some of which had been removed or moved) and let F-Spot rebuild the database, which it appears to have done. My screen saver hasn't kicked in 'for real' yet, but I ran a test by going to System > Preferences > Screen Saver and it looks to be working normally, without the red "X"s coming up.

SoE said...

rebuilding the database is always the cleaner option. I didn't want to take this route because I had a "very large" archive of photos. This method was a little more efficient for me personally.

In either case, F-spot and it's databases have undergone a lot of improvements since I wrote this, and some information might no longer be relevant.

Post a Comment