Rummaging Around in the Lightroom Database
One of the key benefits of using a program like Adobe Lightroom as part of your workflow is that it is built around a true database for cataloging your images. While most users are content to use that database solely through Lightroom itself, some of us are more curious about what's actually inside and what they can do with it. You can get in pretty deep writing your own queries, or if you skip to the end of this article, I'll point you to a free program that will do a lot of the work for you.
When you create a Catalog in Adobe Lightroom you get a file with an LRCAT extension. Far from being some proprietary file structure though, this Lightroom catalog file is built using open source software known as SQLite. Those of you with past database experience probably know or at least can guess that SQLite is an implementation a relational database that uses the standard Structured Query Language (SQL). If however you aren't a database geek like I am, just accept it for now as given that SQL is a good thing. It means that the Lightroom database is built on a standards-based platform that is tried and true.
SQL has its origins in an IBM lab back in the 1970's. By the mid 1980's, relational databases built on SQL had supplanted most earlier database designs. Relational databases organize data into tables with rows and columns. Tables are related to other tables by means of keys and indexes built on values directly in those columns. An example relationship would be between one table with a row for each department in a company, and another table with rows for each employee. The two tables can be "joined" together by matching the department number from one table to the other. Another example would be a table with one row for each folder you use to organize your Lightroom images, and another for each image file. These two tables can be joined by key values to let you get a full view of what each file is and where it resides on your system.
While there are some syntax differences between various vendors and platforms, for the most part all SQL implementations support the same ISO (International Standards Organization) SQL-92 language. Other, more well known SQL database management systems include Microsoft SQL Server, Oracle Corporation's Oracle, and IBM's own DB2. SQLite doesn't support databases as large as does SQL Server or Oracle, but what it does have that these lack is its compactness and ease of implementation. The entire code base for SQLite weighs in at around 255 KB, a remarkable achievement for what it is capable of. It also doesn't require a complicated installation process and can be implemented simply by including that tiny code base as part of another software product. When you run Lightroom, you are running SQLite since it is embedded inside the code for Lightroom. In addition to Lightroom, a growing number of other software programs make use of SQLite to keep track of important information too. In fact, if you are reading this page using Mozilla Firefox and decide to bookmark it (please, go ahead), that bookmark will be stored in a SQLite database maintained by Firefox.
Before we get to discussing how to directly access your Lightroom database, a word of warning" most programs that can read a SQLite database can also write to it. That means you should back up your Lightroom database before we proceed. Please, go ahead. I don't want to be responsible for helping you corrupt your database. If you have a lot of images cataloged by Lightroom, you have a lot of important information in that database. While it is entirely possible to query your Lightroom database without messing it up, it's better to be safe than sorry. If you look at the General tab under Edit >> Catalog Settings, you can tell Lightroom to back up your catalog the next time you exit Ligthroom. Earlier versions of Lightroom instead gave you an option to back up the next time Lightroom starts but Lightroom 3 thankfully changed that. The new way is simpler.
Now that we have that out of the way, you'll need a program to run SQL queries against your Lightroom database. While you can buy one, there's really no need since you can download more than one perfectly usable one for free. My current favorite is actually a Firefox plug-in known as SQLite Manager. If you prefer a standalone program instead, two good options are the SQLite Database Browser from Sourceforge, and SQLite Spy from the Delphi Inspiration.
Before you open your backed up Lightroom database, make sure Lightroom isn't running. Only one program can access a SQLite database file at a time. The default naming convention for SQLite database files is to use the *.SQLite file type extension but it isn't required. Relevant to our task at hand, the Lightroom database doesn't follow that convention but instead names catalog files as *.LRCAT. You can find your Lightroom database in the root of the file you told Lightroom to create it in. If you don't remember where you created it, The Catalog Settings General tab will tell you where it lives and what its name is.
When you open your database, you'll be presented with a lengthy list of tables, an area where you can type a SQL query, and a grid where the results of your queries get displayed. To simply see what a given table contains, you can use the SQL "select" statement such as this"
Select * from AgLibraryFolder
AgLibraryFolder is the table I alluded to earlier that has one row per folder you have your Lightroom images in. The "*" means "give me the values in all columns." Instead, you could have specified a comma-delimited list of columns to be queried. As written, this query will also return data from all rows, meaning you will get one row per folder in your catalog. You can optionally add a "where" clause at the end of the query to limit the rows returned, but I don't want to turn this into a reference on writing SQL statements since your favorite search engine or bookstore can provide you with all you need in that department.
So far as I know, Adobe doesn't document what all these tables are for, but with a little poking around, you will undoubtedly find that you are familiar with at least some of what you find on them. After all, these are your images. So far myself, I've only scratched the surface of what is in the Lightroom database and how all these table relate to each other, but here's a query that will give you a list of all your images and their relative folder paths within your catalog"
select pathFromRoot, baseName, extension
from AgLibraryFile fi
join AgLibraryFolder fo on fi.folder = fo.id_local
If you want more, you can add the folder root, the image dimensions and the xmp metadata for each image by simply joining to more tables. Here I have also limited the rows returned to only images shot during February of this year and told SQLite to sort the results in file name order"
select rf.absolutePath || fo.pathFromRoot || fi.baseName || '.' || fi.extension as fullName,
i.fileHeight, i.fileWidth, i.captureTime, md.xmp
from Adobe_images i
join AgLibraryFile fi on i.rootFile = fi.id_local
join AgLibraryFolder fo on fi.folder = fo.id_local
join AgLibraryRootFolder rf on fo.rootFolder = rf.id_local
join Adobe_AdditionalMetadata md on md.image = i.id_local
where captureTime between '2010-02-01' and '2010-03-01'
order by 1
By now you can probably guess that really taping into this treasure house of information would take some fairly complex queries, but the principle remains the same throughout. All these tables relate to each other by joining them on certain key values. This has to be true since that's how relational databases work, and if it weren't so, Lightroom itself couldn't get at what it needed in your database.
But if all this SQL querying is a bit over your head, relax – there is an alternative. A guy by the name of Marc Rochkind makes a free utility named ImageReporter for both Windows and Mac OS X that can create great reports tallying up how many images you shot with what camera, what lens, focal length, ISO, and so on. Reports can be easily filtered capture time, rating and more. And best of all, it's free. You can find more about ImageReporter in an article written by Marc at The Online Photographer website, or download it from Marc's own site at ImageIngester.com. Marc also makes a number of other programs for photographers, many of which are also built around a Lightroom workflow. While you're downloading your copy of ImageReporter, check out his other offerings. Most aren't free, but they all seem reasonably priced. It's great to have folks like Marc out there making life easier for other photographers. Thanks Marc!
You can learn a lot about the kind of photography you actually do by querying the data stored in the Lightroom database. For instance, for the month of February it looks like I shot nearly a third of my images at 20mm or wider. But my point in writing this article is to encourage you to investigate your own shooting habits. The answers await you in your Lightroom database.