#!/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