digressions from meandering

consolidating iPhoto library with disrespectful SQL hacking 11-Aug-2012 08:37 PM

Don't. Really. Unless you understand it.

I had an iPhoto library of some 30K pictures — some managed, some referenced and I wanted to consolidate the library — that is to copy the originals (a.k.a. masters) to the iPhoto Library for those photos that were copied with the appropriate preference off. Searching the web, looking at tools seemed to be of no help, so this is where I had to dig into iPhoto library guts and see if I can do it on my own.

It didn't take long to find that the Database/apdb/Library.apdb file and its RKMaster table have the information in question. Thank you, Apple, for adopting the sane standard for the databases, BTW. So, all I had to do is to copy files to the Masters directory (the subdirectory structure there is pretty straightforward — %Y/%m/%d/%Y%m%d-%H%M%S in terms of strftime) and update the database.

The shell script follows (note, that I used the script on linux, where the network storage resides, it may not be compatible with other environments out of the box). What makes it a bit longer than one might expect from the description above is that it puts no more than 2000 files in one directory.

ipl="/path/to/your/iPhoto Library.photolibrary"


trap "rm $lf" EXIT
sqlite3 "$ldb" "SELECT modelId,imagePath,originalFileName FROM RKMaster WHERE fileIsReference and fileVolumeUuid='$fvuuid'" >$lf \
|| { echo "Couldn't fetch the list of images" ; exit 1 ; }
while IFS=\| read mid ip ofn ; do
 [[ -r "$fvpath/$ip" ]] || { echo "$fvpath/$ip not found" ; continue; }
 [[ -n "$idir" && -e "$mpath/$idir/$ofn" ]] && idir=''
 [[ -z "$idir" || "$((++nf))" -ge 2000 ]] && { sleep $(((nf=0)+1)) ; idir="$(date +%Y/%m/%d/%Y%m%d-%H%M%S)" ; $dryrun mkdir -p "$mpath/$idir" ; }
 $dryrun $cpln -v "$fvpath/$ip" "$mpath/$idir/$ofn" || { echo "failed to $cpln $fvpath/$ip to $mpath/$idir/$ofn" ; exit 1; }
 $dryrun sqlite3 "$ldb" "UPDATE RKMaster SET fileVolumeUuid=NULL, fileIsReference=0, imagePath='$idir/$ofn' WHERE modelId=$mid" \
 || { echo "failed to update database to reflect copy of $fvpath/$ip to $mpath/$idir/$ofn" ; exit 1; }
done <$lf

Bold parts are the parameters to be modified. To get the uuid of the source volume one needs to peek into the database. It's the second column of the sqlite3 Database/apdb/Library.apdb "SELECT * FROM RKVolume" output.

And again. If you don't understand, don't do it. At any rate, backup what is there to backup. And don't hold it against me if you fail.

This page is a work of fiction and is intended to be viewed for depression purpose only. All characters and incidents mentioned or not are fictional, any resemblance to the actual companies, incidents, or persons living or dead is unintended and purely coincidental. Note: This disclaimer is also fictitious.
Michael Krelin
Munich, Germany
born on the 28th of February