#! /bin/sh # ############################################################################# NAME_="darktable-db" HTML_="" PURPOSE_="darktable database tool" SYNOPSIS_="$NAME_ [-s] [-i ] -o " REQUIRES_="standard GNU commands" VERSION_="0.5" DATE_="2016-04-02" AUTHOR_="H. Klemm" URL_="www.multimedia4linux.de" CATEGORY_="file" PLATFORM_="Linux" SHELL_="bash" DISTRIBUTE_="yes" DEPENDENCE_="darktable-cli, sqlite3, ls, wc, cut, rm, whoami, md5sum, printf, grep, exiftool" # ############################################################################# # This program is distributed under the terms of the GNU General Public License # HISTORY: # 2016-03-20 v0.2 - add export function # 2016-03-24 v0.3 - add root user blocking # - add reverse export # - add colorlable switch # 2016-03-27 v04 - add quality and quantity report option # 2016-03-29 v05 - add rating syncronization from bitmap with the xmp file # - add database backup function graceful_exit() { ##### # Function called for a graceful exit # No arguments ##### exit } error_exit() { ##### # Function for exit due to fatal program error # Accepts 1 argument # string containing descriptive error message ##### echo "${NAME}: ${1:-"Unknown Error"}" >&2 exit 1 } term_exit() { ##### # Function to perform exit if termination signal is trapped # No arguments ##### echo "${NAME}: Terminated" exit } int_exit() { ##### # Function to perform exit if interrupt signal is trapped # No arguments ##### echo "${NAME}: Aborted by user" exit } usage() { ##### # Function to display usage message (does not exit) # No arguments ##### echo "Usage: ${PROGNAME} -h | -c |-r |-s | -q |-u |-v | -e colorlable | -m write mode | -f export format | -i path | -o path | -l colorlable | -x width | -y hight" } helptext() { ##### # Function to display help message for program # No arguments ##### usage echo "Options:\n" echo "-h, Help Display this help message and exit" echo "-c Create database backup Save database file to ~/.config/darktable/db_backups" echo "-r Replace database file Overwrites the database file with a selected backup" echo "-s Show database report Shows darktable database informations" echo "-q Quality and quantity report Shows photo library information" echo "-u Update bitmap rating Synchronize darktable rating with bitmaps" echo "-v Version Database API version" echo "-e Export colorlabled bitmap red, yellow, green, blue, magenta" echo "-m Write mode dav = Delete all existing image versions and write new," echo " env = Export new version," echo " sev = Skip image if exist" echo "-f Export bitmap format jpg, png, tif, ppm, pfm, pdf" echo "-i Input directory /var/lib/photoarchiv or recursive" echo "-o Output direktory Optional if different with input directory" echo " /var/lib/photoarchiv/hdtv_export" echo "-l Switch colorlable after export to red, yellow, green, blue, magenta" echo "-x Image width in pixel Optional, default without scaling = 0" echo "-y Image hight in pixel Optinnal, default without scaling = 0" echo " -y 1080 for HDTV resolution" exit } create_database_backup() { checksumme=`md5sum ~/.config/darktable/library.db | cut -d" " -f1` datum=`date +"%Y-%m-%d_%H:%M"` if [ -d ~/.config/darktable/db_backups ] then cp ~/.config/darktable/library.db ~/.config/darktable/db_backups/library.db_$datum checksumme_backup=`md5sum ~/.config/darktable/db_backups/library.db_$datum | cut -d" " -f1` if [ "$checksumme" = "$checksumme_backup" ] then echo "Backup file ~/.config/darktable/db_backups/library.db_$datum successfully saved" exit else echo "Error backup file! Pleasure ckeck the backup." exit fi else mkdir ~/.config/darktable/db_backups cp ~/.config/darktable/library.db ~/.config/darktable/db_backups/library.db_$datum checksumme_backup=`md5sum ~/.config/darktable/db_backups/library.db_$datum | cut -d" " -f1` if [ "$checksumme" = "$checksumme_backup" ] then echo "Backup file library.db_$datum successfully saved. You can check it with the command darktable --library ~/.config/darktable/db_backups/library.db_$datum" exit else echo "Error backup file! Pleasure ckeck the backup." exit fi fi } replace_database() { ls -l ~/.config/darktable/db_backups | cut -d" " -f10 read -p "Please enter the file to which you want to replace the database: " replace_file if [ -e ~/.config/darktable/db_backups/$replace_file ] then if [ -e ~/.config/darktable/library.db ] then read -p "Are you sure that you want overwrite the library.db [Y/n]" overwrite if [ "$overwrite" = "Y" ] then cp -f ~/.config/darktable/db_backups/$replace_file ~/.config/darktable/library.db echo "library.db database has been successfully replaced" exit 0 else echo "Replace backup was aborted by user" exit 4 fi fi else echo "Error. Backup file $replace_file not found" exit 6 fi } show_db_info() { echo "Analysing Darktable Sqlite 3 database...\n" numberfilmrolls=`sqlite3 ~/.config/darktable/library.db "SELECT COUNT (*) FROM (SELECT DISTINCT folder FROM film_rolls)";` numberphotos=`sqlite3 ~/.config/darktable/library.db "SELECT COUNT (*) FROM (SELECT DISTINCT id FROM images)";` lablered=`sqlite3 ~/.config/darktable/library.db "SELECT COUNT (*) FROM color_labels WHERE COLOR = 0";` lableyellow=`sqlite3 ~/.config/darktable/library.db "SELECT COUNT (*) FROM color_labels WHERE COLOR = 1";` lablegreen=`sqlite3 ~/.config/darktable/library.db "SELECT COUNT (*) FROM color_labels WHERE COLOR = 2";` lableblue=`sqlite3 ~/.config/darktable/library.db "SELECT COUNT (*) FROM color_labels WHERE COLOR = 3";` labelmagenta=`sqlite3 ~/.config/darktable/library.db "SELECT COUNT (*) FROM color_labels WHERE COLOR = 4";` echo "The Darktable database includes:" echo "Filmrolls:" $numberfilmrolls echo "Photos:" $numberphotos "\n" echo "Photos with colorlabels:" echo "Red labels:" $lablered #ID 0 echo "Yellow labels:" $lableyellow #ID 1 echo "Green labels:" $lablegreen #ID 2 echo "Blue labels:" $lableblue #ID 3 echo "Magenta labels:" $labelmagenta #ID 4 echo "Without labels:" $((numberphotos-lablered-lableyellow-lablegreen-lableblue-labelmagenta)) "\n" echo "The filmrolls are saved at direktory:" sqlite3 ~/.config/darktable/library.db "SELECT folder FROM film_rolls"; exit } show_qaq_info() { filmrolls=`sqlite3 ~/.config/darktable/library.db "SELECT COUNT (*) FROM (SELECT DISTINCT folder FROM film_rolls)";` COUNTER=0 echo "Analysing darktable photo library. Please wait..." while [ $COUNTER -lt $filmrolls ]; do input_dir=`sqlite3 ~/.config/darktable/library.db "SELECT folder FROM film_rolls LIMIT $COUNTER,1"`; # Check RAW files quantity_dng=`ls -1 $input_dir"/"*.dng 2>/dev/null | wc -l`; #DNG RAW quantity_cr2=`ls -1 $input_dir"/"*.cr2 2>/dev/null | wc -l`; #Canon RAW quantity_raf=`ls -1 $input_dir"/"*.raf 2>/dev/null | wc -l`; #Fuji RAW quantity_raw=`ls -1 $input_dir"/"*.raw 2>/dev/null | wc -l`; #Leica RAW quantity_mrw=`ls -1 $input_dir"/"*.mrw 2>/dev/null | wc -l`; #Minolta RAW quantity_nef=`ls -1 $input_dir"/"*.nef 2>/dev/null | wc -l`; #Nikon RAW quantity_orf=`ls -1 $input_dir"/"*.orf 2>/dev/null | wc -l`; #Olympus RAW quantity_rw2=`ls -1 $input_dir"/"*.rw2 2>/dev/null | wc -l`; #Panasonic RAW quantity_pef=`ls -1 $input_dir"/"*.pef 2>/dev/null | wc -l`; #Pentax RAW quantity_srw=`ls -1 $input_dir"/"*.srw 2>/dev/null | wc -l`; #Samsung RAW quantity_x3f=`ls -1 $input_dir"/"*.x3f 2>/dev/null | wc -l`; #Sigma RAW quantity_arw=`ls -1 $input_dir"/"*.arw 2>/dev/null | wc -l`; #Sony RAW total_dng=`expr $total_dng + $quantity_dng` total_cr2=`expr $total_cr2 + $quantity_cr2` total_raf=`expr $total_raf + $quantity_raf` total_raw=`expr $total_raw + $quantity_raw` total_mrw=`expr $total_mrw + $quantity_mrw` total_nef=`expr $total_nef + $quantity_nef` total_orf=`expr $total_orf + $quantity_orf` total_rw2=`expr $total_rw2 + $quantity_rw2` total_pef=`expr $total_pef + $quantity_pef` total_srw=`expr $total_srw + $quantity_srw` total_x3f=`expr $total_x3f + $quantity_x3f` total_arw=`expr $total_arw + $quantity_arw` # Check Bitmap files quantity_jpg=`ls -1 $input_dir"/"*.jpg 2>/dev/null | wc -l`; quantity_png=`ls -1 $input_dir"/"*.png 2>/dev/null | wc -l`; quantity_tif=`ls -1 $input_dir"/"*.tif 2>/dev/null | wc -l`; quantity_ppm=`ls -1 $input_dir"/"*.ppm 2>/dev/null | wc -l`; quantity_pfm=`ls -1 $input_dir"/"*.pfm 2>/dev/null | wc -l`; quantity_pdf=`ls -1 $input_dir"/"*.pdf 2>/dev/null | wc -l`; total_jpg=`expr $total_jpg + $quantity_jpg` total_png=`expr $total_png + $quantity_png` total_tif=`expr $total_tif + $quantity_tif` total_ppm=`expr $total_ppm + $quantity_ppm` total_pfm=`expr $total_pfm + $quantity_pfm` total_pdf=`expr $total_pdf + $quantity_pdf` # Rating quantity_rating_0=`grep -r -i 'Rating="0"' $input_dir"/"*.xmp | wc -l` quantity_rating_1=`grep -r -i 'Rating="1"' $input_dir"/"*.xmp | wc -l` quantity_rating_2=`grep -r -i 'Rating="2"' $input_dir"/"*.xmp | wc -l` quantity_rating_3=`grep -r -i 'Rating="3"' $input_dir"/"*.xmp | wc -l` quantity_rating_4=`grep -r -i 'Rating="4"' $input_dir"/"*.xmp | wc -l` quantity_rating_5=`grep -r -i 'Rating="5"' $input_dir"/"*.xmp | wc -l` total_rating_0=`expr $total_rating_0 + $quantity_rating_0` total_rating_1=`expr $total_rating_1 + $quantity_rating_1` total_rating_2=`expr $total_rating_2 + $quantity_rating_2` total_rating_3=`expr $total_rating_3 + $quantity_rating_3` total_rating_4=`expr $total_rating_4 + $quantity_rating_4` total_rating_5=`expr $total_rating_5 + $quantity_rating_5` COUNTER=`expr 1 + $COUNTER` done rating100=`expr $total_rating_0 + $total_rating_1 + $total_rating_2 + $total_rating_3 + $total_rating_4 + $total_rating_5` ratingpercent_0=$(echo "scale=2 ; $total_rating_0*100/$rating100" |bc) ratingpercent_1=$(echo "scale=2 ; $total_rating_1*100/$rating100" |bc) ratingpercent_2=$(echo "scale=2 ; $total_rating_2*100/$rating100" |bc) ratingpercent_3=$(echo "scale=2 ; $total_rating_3*100/$rating100" |bc) ratingpercent_4=$(echo "scale=2 ; $total_rating_4*100/$rating100" |bc) ratingpercent_5=$(echo "scale=2 ; $total_rating_5*100/$rating100" |bc) #Output informations echo "The darktable photo library contains\n" echo "RAW files" if [ "$total_dng" -gt "0" ] then echo "DNG files:" $total_dng fi if [ "$total_cr2" -gt "0" ] then echo "CR2 files:" $total_cr2 fi if [ "$total_raf" -gt "0" ] then echo "RAF files:" $total_raf fi if [ "$total_raw" -gt "0" ] then echo "RAW files:" $total_raw fi if [ "$total_mrw" -gt "0" ] then echo "MRW files:" $total_mrw fi if [ "$total_nef" -gt "0" ] then echo "NEF files:" $total_nef fi if [ "$total_orf" -gt "0" ] then echo "ORF files:" $total_orf fi if [ "$total_rw2" -gt "0" ] then echo "RW2 files:" $total_rw2 fi if [ "$total_pef" -gt "0" ] then echo "PEF files:" $total_pef fi if [ "$total_srw" -gt "0" ] then echo "SRW files:" $total_srw fi if [ "$total_x3f" -gt "0" ] then echo "X3F files:" $total_x3f fi if [ "$total_raf" -gt "0" ] then echo "ARW files:" $total_arw fi echo "\n" echo "Bitmap files" if [ "$total_jpg" -gt "0" ] then echo "JPG files:" $total_jpg fi if [ "$total_png" -gt "0" ] then echo "PNG files:" $total_png fi if [ "$total_tif" -gt "0" ] then echo "TIF files:" $total_tif fi if [ "$total_ppm" -gt "0" ] then echo "PPM files:" $total_ppm fi if [ "$total_pfm" -gt "0" ] then echo "PFM files:" $total_pfm fi if [ "$total_pdf" -gt "0" ] then echo "PDF files:" $total_pdf fi echo "\n" echo "Image rating" echo "Images with 0 stars:" $total_rating_0 "("$ratingpercent_0"%)" echo "Images with 1 stars:" $total_rating_1 "("$ratingpercent_1"%)" echo "Images with 2 stars:" $total_rating_2 "("$ratingpercent_2"%)" echo "Images with 3 stars:" $total_rating_3 "("$ratingpercent_3"%)" echo "Images with 4 stars:" $total_rating_4 "("$ratingpercent_4"%)" echo "Images with 5 stars:" $total_rating_5 "("$ratingpercent_5"%)" exit } update_rating() { echo "Synchronize darktable rating with bitmaps from the whole photo library. The bitmap must be in the same directory as the raw file. This may take a very longer time. Please wait..." numberphotos=`sqlite3 ~/.config/darktable/library.db "SELECT COUNT (*) FROM (SELECT DISTINCT id FROM images)";` filmrolls=`sqlite3 ~/.config/darktable/library.db "SELECT COUNT (*) FROM (SELECT DISTINCT folder FROM film_rolls)";` COUNTER=0 jpg_updates=0 png_updates=0 tif_updates=0 checked_files=0 while [ $COUNTER -lt $filmrolls ]; do # Read path from filmroll 1,2,3... input_dir=`sqlite3 ~/.config/darktable/library.db "SELECT folder FROM film_rolls LIMIT $COUNTER,1"`; # Create file list from filmroll 1,2,3... rm -f /tmp/dt_input_raw_files.txt `sqlite3 ~/.config/darktable/library.db "SELECT distinct images.filename FROM film_rolls, images WHERE film_rolls.id = images.film_id AND film_rolls.folder ='$input_dir'" > /tmp/dt_input_raw_files.txt;` while read input_file do xmpfile=$input_dir"/"$input_file".xmp" jpgfile=$input_dir"/"`echo "$input_file" | cut -d'.' -f1`".jpg" pngfile=$input_dir"/"`echo "$input_file" | cut -d'.' -f1`".png" tiffile=$input_dir"/"`echo "$input_file" | cut -d'.' -f1`".tif" xmprating=`grep -r -i 'xmp:Rating=' $xmpfile | cut -c 16` jpgrating=`exiftool -p '$Rating' $jpgfile 2>/dev/null` pngrating=`exiftool -p '$Rating' $pngfile 2>/dev/null` tifrating=`exiftool -p '$Rating' $tiffile 2>/dev/null` if [ -f "$jpgfile" ] && [ "$xmprating" != "$jpgrating" ] then exiftool -overwrite_original -XMP:Rating=$xmprating $jpgfile >/dev/null 2>&1 echo "\n$jpgfile updated" jpg_updates=`expr 1 + $jpg_updates` fi if [ -f "$pngfile" ] && [ "$xmprating" != "$pngrating" ] then exiftool -overwrite_original -XMP:Rating=$xmprating $pngfile >/dev/null 2>&1 echo "\n$pngfile updated" png_updates=`expr 1 + $png_updates` fi if [ -f "$tiffile" ] && [ "$xmprating" != "$tifrating" ] then exiftool -overwrite_original -XMP:Rating=$xmprating $tiffile >/dev/null 2>&1 echo "\n$tiffile updated" tif_updates=`expr 1 + $tif_updates` fi checked_files=`expr 1 + $checked_files` printf "\r $checked_files files from $numberphotos checked" done < /tmp/dt_input_raw_files.txt COUNTER=`expr 1 + $COUNTER` done updates=`expr $jpg_updates + $png_updates + $tif_updates` clear echo "\n$updates Rating tags of the bitmaps were updated" exit } version_info() { dtdbversion=`sqlite3 ~/.config/darktable/library.db "SELECT value FROM db_info";` echo "Darktable library.db API Version" $dtdbversion exit } set_colorlable() { # extract file number from db if [ "$lable_in" != "$lable_out" ]; then if [ -e ~/.config/darktable/library.db.lock ]; then echo "The darktable database is locked. Please exit darktable, if you want switch the colorlable!" exit 6 else filenumber=`sqlite3 ~/.config/darktable/library.db "SELECT DISTINCT images.id FROM film_rolls, images WHERE images.film_id = film_rolls.id AND film_rolls.folder = '$input_dir' AND images.filename = '$input_file'";` # change colorlable sqlite3 ~/.config/darktable/library.db "UPDATE color_labels SET color = '$lable_out' WHERE imgid = '$filenumber'"; fi fi } export_image() { # delete temp file rm -f /tmp/dt_input_files.txt # Count colorlabled images numberlabled=`sqlite3 ~/.config/darktable/library.db "SELECT COUNT (*) FROM film_rolls, images, color_labels WHERE film_rolls.id = images.film_id AND images.group_id = color_labels.imgid AND film_rolls.folder ='$input_dir' AND color_labels.color='$lable_in'";` # Write message echo "Exporting $numberlabled ${colorlable_in}" labled raw images, from ${input_dir} as ${outputfileformat} to ${output_dir} ... # Create file list `sqlite3 ~/.config/darktable/library.db "SELECT distinct images.filename FROM film_rolls, images, color_labels WHERE film_rolls.id = images.film_id AND images.group_id = color_labels.imgid AND film_rolls.folder ='$input_dir' AND color_labels.color='$lable_in'" > /tmp/dt_input_files.txt;` while read input_file do case "$writemode" in dav) deltefile=$output_dir"/"$output_file`echo "$input_file" | cut -d'.' -f1`"*."$outputfileformat; rm -f $deltefile; darktable-cli $input_dir"/"$input_file $output_dir"/"$output_file`echo "$input_file" | cut -d'.' -f1`"."$outputfileformat --width $image_width --height $image_hight; ;; env) darktable-cli $input_dir"/"$input_file $output_dir"/"$output_file`echo "$input_file" | cut -d'.' -f1`"."$outputfileformat --width $image_width --height $image_hight; ;; sev) output_filecheck=$output_dir"/"$output_file`echo "$input_file" | cut -d'.' -f1`"."$outputfileformat; if [ -f "$output_filecheck" ]; then echo "[export_job] skip file $output_filecheck"; else darktable-cli $input_dir"/"$input_file $output_filecheck --width $image_width --height $image_hight; fi ;; esac set_colorlable done < /tmp/dt_input_files.txt rm -f /tmp/dt_input_files.txt } ########################################################################### # Program starts here ########################################################################### # Trap TERM, HUP, and INT signals and properly exit trap term_exit TERM HUP trap int_exit INT # Process command line arguments if [ "$1" = "-h" ]; then helptext graceful_exit fi # Display usage message if no options or arguments are given if [ "$1" = "" ]; then usage exit 1 fi if [ `whoami` = "root" ] then echo "The use of darktable-db is not possible as root user" exit 10 fi # Process arguments colorlable_in="" outputfileformat="" input_dir="" output_dir="" image_width=0 image_hight=0 while getopts ":hcrsquve:m:f:i:o:l:x:y:" options; do case "$options" in h) helptext ;; c) create_database_backup ;; r) replace_database ;; s) show_db_info ;; q) show_qaq_info ;; u) update_rating ;; v) version_info ;; e) colorlable_in="$OPTARG" ;; m) mode="$OPTARG" ;; f) outputfileformat="$OPTARG" ;; i) input_dir="$OPTARG" ;; o) output_dir="$OPTARG" ;; l) colorlable_out="$OPTARG" ;; x) image_width="$OPTARG" ;; y) image_hight="$OPTARG" ;; esac done case "$colorlable_in" in red) lable_in=0 ;; yellow) lable_in=1 ;; green) lable_in=2 ;; blue) lable_in=3 ;; magenta) lable_in=4 ;; *) echo "Unkown colorlable"; exit 9;; esac case "$colorlable_out" in red) lable_out=0 ;; yellow) lable_out=1 ;; green) lable_out=2 ;; blue) lable_out=3 ;; magenta) lable_out=4 ;; *) lable_out=$lable_in;; esac case "$mode" in dav) writemode=dav;; env) writemode=env;; sev) writemode=sev;; *) echo "Unkown write mode"; exit 8;; esac case "$outputfileformat" in jpg) ;; png) ;; tif) ;; ppm) ;; pfm) ;; pdf) ;; *) echo "Unknown or not supported export format"; exit 7;; esac # Main program if [ "$input_dir" = "recursive" ] then # Calculate number of filmrolls filmrolls=`sqlite3 ~/.config/darktable/library.db "SELECT COUNT (*) FROM (SELECT DISTINCT folder FROM film_rolls)";` COUNTER=0 while [ $COUNTER -lt $filmrolls ]; do input_dir=`sqlite3 ~/.config/darktable/library.db "SELECT folder FROM film_rolls LIMIT $COUNTER,1"`; output_dir=$input_dir export_image COUNTER=`expr 1 + $COUNTER` done else if [ "$output_dir" = "" ]; then output_dir=$input_dir fi export_image fi