De-duplicate database

["Michael Schmarck" <michael schmarck habmalnefrage de>]

Hello.

I'm using f-spot to manage my photos. To do so, I copy the pictures
from my camera to some folder on my harddisk. Then I use f-spot to
import the pictures to f-spot. I *always* uncheck the "copy files
to Photos folder", ie. they remain in a directory structure of my
liking.

Suppose I've got the images in ~/Desktop/My Pictures/Travel and
~/Desktop/My Pictures/Fun. I'd now import the Travel an Fun folders.
Later on, I make some new pictures and copy them to Travel and/or
Fun again. I'd now re-import the Travel and/or Fun folders.

What now happens is, that f-spot will also re-import all the already
known pictures. This is a known bug in f-spot -> http://bugzilla.gnome.org/169646

Does anyone know of a way to (more or less...) easily de-dupe the
f-spot database?

Right now, I do it somewhat "awkward", like that:

sqlite3 ~/.gnome2/f-spot/photos.db 'select uri from photos order by uri'|\
  uniq -d | while read uri; do 
    printf 'select id from photos where uri = "%s";' "$uri" | \
     sqlite3 photos.db | sort -n | sed 1d
  done

What this does, is that it reports all the "uris" from the photos table
which exist multiple times (sqlite3 ... photos.db ... | uniq -d). Next,
it queries the database again to determine the IDs of the dupes (that's
what the while loop does). From what's returned, I chop of the 1st line
(sed 1d), in the assumption that only the 1st/oldest entry should be
kept in the database. The output of all of that is a list of IDs that
could be deleted/dropped from photos and photo_tags and photo_versions.

As I said, that's a bit "awkward" :)

Does anyone know of a better way? Maybe a more clever SQL statement,
so that it's not necessary to query the database that often?

Thanks a lot!

-- 
-- 
Michael

Psst! Geheimtipp: Online Games kostenlos spielen bei den GMX Free Games! 
http://games.entertainment.web.de/de/entertainment/games/free