darktable: script to purge non existing images from DB (awk version)

purge_non_existing_images.awk.sh

#!/bin/sh

DRYRUN=yes

if [ "$1" = "-p" ]; then
    DRYRUN=no
fi

DBFILE=~/.config/darktable/library.db
TMPFILE=$(mktemp -t tmp.XXXXXXXXXX)
TMPQUERIES=$(mktemp -t tmp.XXXXXXXXXX)
QUERY="select A.id,B.folder,A.filename from images as A join film_rolls as B on A.film_id = B.id"
sqlite3 $DBFILE "$QUERY" > "$TMPFILE"
echo "Removing the following non existent file(s):"

awk -F"|" -v tmpQueries="$TMPQUERIES" '
BEGIN {
    # Store table names into array
    split("images meta_data", tables_1, " ")
    split("color_labels history mask selected_images tagged_images", tables_2, " ")
    # Start transaction
    print "BEGIN TRANSACTION;" > tmpQueries
}

{
    # check if file exists
    fileName = "\042"$2"/"$3"\042"
    if( system( "[ -f "fileName" ]" )  != 0 ) {
        printf( "%s/%s with ID = %s\n", $2, $3, $1 );
        for (table in tables_1) {
          printf("DELETE FROM %s WHERE id=%s;\n", tables_1[table], $1) >> tmpQueries
        }
        for (table in tables_2) {
          printf("DELETE FROM %s WHERE imgid=%s;\n", tables_2[table], $1) >> tmpQueries
        }
    }
}

END {
    print "COMMIT;" >> tmpQueries
}
' "$TMPFILE"

# Execute the DELETE query
if [ $DRYRUN = no ]; then
    sqlite3 "$DBFILE" < "$TMPQUERIES" 1>/dev/null 2>&1 || \
    echo "Please close darktable and run this script again."
fi

if [ $DRYRUN = no ]; then
    # delete now-empty filmrolls
    sqlite3 "$DBFILE" "DELETE FROM film_rolls WHERE (SELECT COUNT(A.id) FROM images AS A WHERE A.film_id=film_rolls.id)=0"
else
    echo
    echo Remove following now-empty filmrolls:
    sqlite3 "$DBFILE" "SELECT folder FROM film_rolls WHERE (SELECT COUNT(A.id) FROM images AS A WHERE A.film_id=film_rolls.id)=0"
fi

if [ $DRYRUN = yes ]; then
    echo
    echo to really remove non existing images from the database call:
    echo "$0" -p
fi

# Cleanup
rm "$TMPFILE" "$TMPQUERIES"