schussman.com logo

What's in your Lightroom 2 catalog?

A question recently came up over in the Flickr Lightroom group about generating a list of all filenames or selected filenames in a catalog. There’s a pretty nice straightforward bit of SQL that will produce just such a list.

My new favorite tool for poking around in the Lightroom database is the SQLite Manager extension for Firefox. It plugs seamlessly into Firefox and is a very good tool—faster than the old sqlite3 browser, and with the added bonus of not requiring Rosetta to be installed under Snow Leopard. Plug the following code into the “Execute SQL” box (or kick it old school via the command line sqlite3 tool):

SELECT pathFromRoot, baseName, extension FROM AgLibraryFile JOIN AgLibraryFolder ON AgLibraryFile.folder = AgLibraryFolder.id_local

You can do even better by using the sqlite3 concatenate operate in the select statement:

SELECT pathFromRoot || baseName || '.' || extension FROM AgLibraryFile JOIN AgLibraryFolder ON AgLibraryFile.folder = AgLibraryFolder.id_local

That will produce a nicely-formatted list of files:

	2007-02-19/IMGP1609.PEF
	2007-02-19/IMGP1610.PEF
	2007-02-19/IMGP1611.PEF
	2007-02-19/IMGP1612.PEF
	2007-02-19/IMGP1613.PEF
	2007-02-19/IMGP1614.PEF

Still building, we can add in the absolutePath field to get a complete path — very useful for when you have files stored in multiple locations, such as offline storage or backup locations.

SELECT absolutePath || pathFromRoot || baseName || '.' || extension FROM AgLibraryFile JOIN AgLibraryFolder ON AgLibraryFile.folder = AgLibraryFolder.id_local JOIN AgLibraryRootFolder ON AgLibraryFolder.rootFolder = AgLibraryRootFolder.id_local

And, finally, because how often do you really need to see the paths for all of the files in your library, really?, we can limit the query to just files in a quick collection. This takes a couple of extra steps, because we can’t directly get to the keyword data from the path data:

SELECT absolutePath || pathFromRoot || baseName || '.' || extension FROM AgLibraryFile JOIN AgLibraryFolder ON AgLibraryFile.folder = AgLibraryFolder.id_local JOIN AgLibraryRootFolder ON AgLibraryFolder.rootFolder = AgLibraryRootFolder.id_local JOIN Adobe_images ON AgLibraryFile.id_local = Adobe_images.rootFile JOIN AgLibraryTagImage ON AgLibraryTagImage.image = Adobe_images.id_local WHERE tagKind like "AgQuickCollectionTagKind"

And there you have it. One more fun trick you can pull thanks to the sqlite3 database that underlies your Lightroom 2 catalog.

 

Check out the rest of my Lightroom posts for much, much more, including more database tinkering, keywording, and workflow.