Skip to content

Instantly share code, notes, and snippets.

@esperlu
Last active November 13, 2018 16:55
Show Gist options
  • Save esperlu/4b5db44304aa8d5e4ec9d7a214ada781 to your computer and use it in GitHub Desktop.
Save esperlu/4b5db44304aa8d5e4ec9d7a214ada781 to your computer and use it in GitHub Desktop.
darktable: script to purge non existing images from DB (shell version with sqlite transaction)
#!/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):"
echo "BEGIN IMMEDIATE TRANSACTION;" > $TMPQUERIES
cat "$TMPFILE" | while read -r result
do
ID=$(echo "$result" | cut -f1 -d"|")
FD=$(echo "$result" | cut -f2 -d"|")
FL=$(echo "$result" | cut -f3 -d"|")
if ! [ -f "$FD/$FL" ]; then
echo " $FD/$FL with ID = $ID"
if [ $DRYRUN = no ]; then
for table in images meta_data; do
echo "DELETE FROM $table WHERE id=$ID;" >> $TMPQUERIES
done
for table in color_labels history mask selected_images tagged_images; do
echo "DELETE FROM $table WHERE imgid=$ID;" >> $TMPQUERIES
done
fi
fi
done
echo "COMMIT;" >> $TMPQUERIES
# 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
rm "$TMPFILE" "$TMPQUERIES"
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment