tag | Schussman.com

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.

What's in your Lightroom 2 catalog?

About, the short version