--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/tidy-fspot-database	Sat Aug 02 19:31:02 2014 +0100
@@ -0,0 +1,142 @@
+#!/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
+ 
+    # 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")
+ 
+    # 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}"
+                base_uri=${foo%/*}
+                filename=${foo##*/}
+                echo -n "Deleting URI $line from the database table photos..."
+                sqlite3 $DBPATH "DELETE FROM PHOTOS WHERE base_uri = '$base_uri' AND filename = '$filename'"
+                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}"
+                base_uri=${foo%/*}
+                filename=${foo##*/}
+                echo -n "Deleting URI $line from the database table photo_versions..."
+                sqlite3 $DBPATH "DELETE FROM PHOTO_VERSIONS WHERE base_uri = '$base_uri' AND filename = '$filename'"
+                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