After settling on a Nikon D80, I was looking for a decent image library software. So far my photographer skills are not very advanced, so I don’t expect to alter the images too much. I came across Adobe Lightroom (hereafter called LR), and it seems to fit my bill very well. It allows me to manage all my images, and it even exports them to flickr (including tags).

During the course of playing with LR I imported a number of images I now want to get rid of – not just remove them from the library, but also physically delete them from the disk. I couldn’t find a way to do this in LR. Whatever I did, the files remained on disk.

I found this the perfect occasion to investigate the fact that LR’s database is actually a SQLite database. Shortly after downloading the sqlite commandline utility I was able to inspect the database schema (SQLite Database Browser helped a lot to understand it).

Finally, after some trial and error, I constructed the following statement that emits the full file path of all images belonging to a certain LR collection:

SELECT absolutePath FROM Adobe_imageFiles, AgLibraryTagImage, AgLibraryTag WHERE Adobe_imageFiles.image = AgLibraryTagImage.image AND AgLibraryTagImage.tag = AgLibraryTag.id_local AND AgLibraryTag.name = "My Collection";

I didn’t want to write to the database, so for now I just remove the images in LR and delete the files (from the results above) after closing it. But it’s easy to imagine a script that does this automatically (e.g. with the help of SQLite/Ruby).

Btw, Alan Schussman has another nice application for the raw dat: He builds a histogram of focal length use by extracting the relevant data from the SQLite database and feeding this into R. Nice.