Hi, very interesting work! Thanks for sharing.
Bad news is it isn't working on my Lucid with F-Spot 0.6.1.5. Maybe they changed database structure.
Here is the output:

What would you like to do:
1) Run in demo-mode
2) Clean up your f-spot database
3) Quit
Your choice: 2
Great. Cleaning up your database.
Error: no such column: URI
Error: no such column: URI
Your database has been backed up to /home/stefano/.config/f-spot/photos.db.2010-05-05.bak
Deleting URI from the database table photos...Error: no such column: uri
done.
Deleting URI from the database table photo_versions...Error: no such column: uri
done.

Hi,

thanks a lot for your piece of software, unfortunately as you said it seems that they changed the database model. I think we all understand that you do not have time for fixing, however do you have any general suggestions? I peeked at the new DB in lucid 10.04, admit I do not know very well sqlite3, however I guess the model is similar, maybe few substitutions on your shell will be enough...? If you can only post few lines with guidelines, some of us can fix it and post it here for you.

Thanks anyway,

Greg

# Select the filenames, and put them in a variable.
filenames=$(sqlite3 $DBPATH "SELECT BASE_URI||FILENAME FROM PHOTOS")
filenames_versions=$(sqlite3 $DBPATH "SELECT BASE_URI||FILENAME FROM PHOTO_VERSIONS")

Here's the output of diff showing the changes I made for this to work for me (on Lucid):
uu@pc ~> diff f-spot_photo_cleanup_original.sh f-spot_photo_cleanup.sh
41,42c41,44
< filenames=$(sqlite3 $DBPATH "SELECT URI FROM PHOTOS")
< filenames_versions=$(sqlite3 $DBPATH "SELECT URI FROM PHOTO_VERSIONS")
---
> ## filenames=$(sqlite3 $DBPATH "SELECT URI FROM PHOTOS")
> ## filenames_versions=$(sqlite3 $DBPATH "SELECT URI FROM PHOTO_VERSIONS")
> filenames=$(sqlite3 $DBPATH "SELECT BASE_URI||FILENAME FROM PHOTOS")
> filenames_versions=$(sqlite3 $DBPATH "SELECT BASE_URI||FILENAME FROM PHOTO_VERSIONS")
96c98,99
< sqlite3 $DBPATH "DELETE FROM PHOTOS WHERE uri = '$foo'"
---
> ##sqlite3 $DBPATH "DELETE FROM PHOTOS WHERE uri = '$foo'"
> sqlite3 $DBPATH "DELETE FROM PHOTOS WHERE base_uri || filename = '$foo'"
112c115,116
< sqlite3 $DBPATH "DELETE FROM PHOTO_VERSIONS WHERE uri = '$foo'"
---
> ##sqlite3 $DBPATH "DELETE FROM PHOTO_VERSIONS WHERE uri = '$foo'"
> sqlite3 $DBPATH "DELETE FROM PHOTO_VERSIONS WHERE base_uri || filename = '$foo'"

Hiya,

Here's a version that tests the db. It's a lazy version ie I haven't looked at the db structure.
Quick 'n Dirty as they call it.

Enjoy!

#!/bin/bash

# A script to find missing files in the f-spot database, and then delete them.
# At present these files crash f-spot. It's frustrating as all hell.

echo "Welcome to the f-spot database cleaner. All the usual disclaimers apply, as you might imagine."
echo "What would you like to do: "
echo " 1) Run in demo-mode "
echo " 2) Clean up your f-spot database"
echo " 3) Quit"
read -p "Your choice: " choice

case $choice in
1) demomode="true";;
2) demomode="false";;
3) exit 0;;
esac

# With that beginning stuff out of the way, let us do some functions
# First, a function to gather the database contents and to print out the ones that are orphans

function findAndFixOrphans {
# find our db, and set a var. Checking for XDG path first, since it's the more recent location of the db
if [ -f $XDG_CONFIG_DIR/f-spot/photos.db ] #checks if the $XDG_CONFIG_DIR variable is in use
then
DBPATH=$XDG_CONFIG_DIR/f-spot/photos.db
elif [ -f $HOME/.config/f-spot/photos.db ] #uses the default $XDG location, if that's being used.
then
DBPATH=$HOME/.config/f-spot/photos.db
elif [ -f $HOME/.gnome2/f-spot/photos.db ] #uses the old location of the DB, if the former aren't in use.
then
DBPATH=$HOME/.gnome2/f-spot/photos.db
else
echo "Error: Could not find database. Damn."
exit 1
fi

filenames=$(sqlite3 $DBPATH "SELECT URI FROM PHOTOS" 1>/dev/null 2>/dev/null)
if [ -z "$filesnames" ]; then version=v3; else version=v2; fi
echo $version

# Select the filenames, and put them in a variable.
if [ "$version" == "v3" ]
then
filenames=$(sqlite3 $DBPATH "SELECT BASE_URI||FILENAME FROM PHOTOS")
filenames_versions=$(sqlite3 $DBPATH "SELECT BASE_URI||FILENAME FROM PHOTO_VERSIONS")
else
filenames=$(sqlite3 $DBPATH "SELECT URI FROM PHOTOS")
filenames_versions=$(sqlite3 $DBPATH "SELECT URI FROM PHOTO_VERSIONS")
fi

# Chomp off the first instance of file://, and replace the rest with newlines.
filenames=$(echo $filenames | sed 's/file:\/\///' | sed 's/file:\/\//\n/g' )
filenames_versions=$(echo $filenames_versions | sed 's/file:\/\///' | sed 's/file:\/\//\n/g' )

if [ $demomode == "true" ]
then
while read -r line
do
# Decode the filename
decodedLine=$(echo -e "${line//\%/\\x}")
if [ ! -f "$decodedLine" ]
then
# If the file doesn't exist, we output the filename, if in demomode, or we fix it if we are not in demomode.
echo "Errant record found in the photos table: $decodedLine"
fi
done <<< "$filenames"

# We do the same for the photo_versions table
while read -r line
do
# Decode filename
decodedLine=$(echo -e "${line//\%/\\x}")
if [ ! -f "$decodedLine" ]
then
# If the file doesn't exist, we output the filename
echo "Errant record found in the photo_versions table: $decodedLine"
fi
done <<< "$filenames_versions"

else
# We backup the database, and make the correction
cp $DBPATH $DBPATH.`date -I`.bak
if [ $? -eq 0 ]
then
#The backup worked, tell the user.
echo "Your database has been backed up to $DBPATH.`date -I`.bak"
else
echo "Error backing up your database."
exit 3
fi

# First we do the photos table
while read -r line
do
# Decode the filename
decodedLine=$(echo -e "${line//\%/\\x}")

if [ ! -f "$decodedLine" ]
then
# Do some sql here.
foo="file://${line}"
echo -n "Deleting URI $line from the database table photos..."
if [ "$version" == "v3" ]
then
sqlite3 $DBPATH "DELETE FROM PHOTOS WHERE base_uri||filename = '$foo'"
else
sqlite3 $DBPATH "DELETE FROM PHOTOS WHERE uri = '$foo'"
fi
echo "done."
fi
done <<< "$filenames"

# Then we do the photo_versions table
while read -r line
do
# Decode the filename
decodedLine=$(echo -e "${line//\%/\\x}")

if [ ! -f "$decodedLine" ]
then
#Do some sql stuff
foo="file://${line}"
echo -n "Deleting URI $line from the database table photo_versions..."
if [ "$version" == "v3" ]
then
sqlite3 $DBPATH "DELETE FROM PHOTO_VERSIONS WHERE base_uri||filename = '$foo'"
else
sqlite3 $DBPATH "DELETE FROM PHOTO_VERSIONS WHERE uri = '$foo'"
fi
echo "done."
fi
done <<< "$filenames_versions"

fi

}

if [ "$demomode" == "true" ]
then
echo "Great. Proceeding in demomode."

findAndFixOrphans

echo "Demomode successfully finished. Exiting."
exit 0;
elif [ "$demomode" == "false" ]
then
echo "Great. Cleaning up your database."

findAndFixOrphans

echo "Database cleaned successfully."
exit 0;
else
echo "Something strange happened. See the script for details. Exiting."
exit 2;
fi