Skip to content

Instantly share code, notes, and snippets.

@esperlu
Last active November 13, 2018 16:52
Show Gist options
  • Save esperlu/3d4c58bd8de3b24f08ac6eac4144bd51 to your computer and use it in GitHub Desktop.
Save esperlu/3d4c58bd8de3b24f08ac6eac4144bd51 to your computer and use it in GitHub Desktop.
darktable: script to purge non existing images from DB (gawk version)
#!/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):"
gawk -F"|" -v tmpQueries="$TMPQUERIES" '
@load "filefuncs"
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 = $2"/"$3
if ( stat(fileName, fd) != 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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment