Posts Tagged ‘SQLite’

Browsing the Lightroom Catalog

October 20, 2009

I have not given up on my quest to be able to generate reports on my Lightroom database catalog. I talked about my reasons for it here and here.

The imaginatively named SQLite Database Browser I found at is very slow.

SQLite Database Browser About

SQLite Database Browser About

But yesterday a poster to this blog recommended a better one. It’s called SQLite Spy. It’s free for non-commercial use. I like free! You can download it here.

SQLiteSpy About

SQLiteSpy About

It is very fast – even when examining my 1GB main Lightroom Catalog. (I am living in fear that it will break soon. I’m approaching 50,000 pictures catalogued. See here and here.)

Here’s the SQLiteSpy display of the table “Adobe_images” that has one row for every photo (or virtual copy) catalogued by Lightroom.

SQLiteSpy Adobe_images - click to see full sized

SQLiteSpy Adobe_images - click to see full sized

Of course, this is an extremely powerful tool. I must be very careful, especially with my “production” catalog. I read a cautionary tale here about how a Lightroom user / database geek made Lightroom behave very strangely by making a change to the database using SQL.

He deleted references to some sidecar files by replacing the contents of the field with NULL. But Lightroom started acting strangely and would not display thumbnails consistently. It turned out that he should have used an empty string rather than NULL.

That distinction is a classic database programming error but it is very obscure to most people. Try explaining the difference between NULL and an empty string to a child! Actually, I thought that using NULL was better programming practice as database are optimized for NULL checks. But the Adobe engineer who wrote that part of Lightroom used an empty string and never imagined that anybody would mess with her tables.

I should spend the time to reverse-engineer the Lightroom Catalog schema. That’s the database geek word for the tables and most importantly the relationships between them. But my database knowledge is rusty and it would take me weeks. Adobe will release Lightroom 3.0 before I’m satisfied.

Also …

The ability to execute arbitrary SQL on the database is useful and it may save me from some problems that cannot be fixed using the Lightroom client. Maybe there’s something wrong with the database that causes my “Ever Changing Status” problem. Every time Lightroom crashes I worry the catalog will be corrupted.

But – it isn’t what I want. I have yet to find a fully functional ODBC driver that will allow me to attach SQLite tables to Microsoft Access. Then I can do the database reports that I crave. There’s so much good information in my metadata but I cannot present it as I would like.

What I’ve Been Working On

July 6, 2009

I have been trying to figure out the schema (design) of the Lightroom database. I want to be able to produce reports for the metadata I have lovingly added to my photos. I used to be able to do it for Thumbs Plus because it is based on Microsoft Access.

Lightroom uses an open source database called SQLite. I found a free database browser at and used it to look at the schema and examine the data in some of the tables.

I tried an open source ODBC driver to attach SQLite tables to Access with limited success. Maybe I am not setting things up correctly because many but not all tables show all the records as #Deleted. It could be a version issue.

I worked out how fields defined in a plug-in are held in Lightroom – hint, searchable and non-searchable are held in different tables.

I used the browser to dump three tables to CSV files and then imported them to a new Access database. (Access 2007 is a pain to someone used to 2003). Then I was able to use my primitive data warehouse to produce a report on the images to which I’d added some private metadata using my trivial first add-in.

I’ll write more when I feel I have something more solid.

Does anyone have any experience with this?

Image Reporter

July 4, 2009

One of the things I miss about Thumbs Plus when using Lightroom is the fact that Thumbs Plus uses a Microsoft Access database. In a previous life I did a lot of Access programming and I was able to attach the Thumbs Plus database to the Access 2003 client. the schema was self-explanatory and I wrote lots of reports on my photos.

Thumbs Plus has the concept of “user fields” where I could add as many typed fields as I liked to describe each image. I collect information on land, air and sea vehicles and I used user fields to store information like make, model, owner, fleet number and so on. I think a simpler method of defining user fields is a high priority enhancement for Lightroom.

I’ll talk more in another post about why user fields are much more useful than keywords or collections for categorizing my photos.

Anyway, the database geek in me was interested in this tool – Image Reporter. It comes from the same company that produced Image Ingester and Image Verifier.

Analyzes metadata in a Lightroom catalog and reports on cropping, camera makes and models, lenses, average focal length, ISO, and more. Filters by image type, rating, and capture time. Described in an article by Marc Rochkind at The Online Photographer.

I like free, so I downloaded it and tried it. Here’s a screen shot of my first report:

Image Reporter

Sorry to say its output is not very interesting to me. Cameras and lenses are not as interesting as statistics I can store and analyse on where my pictures were taken and what they are of.

Note it is a stand-alone program, not a Lightroom add-in.

I’d like to be able to write my own reporting program for Lightroom. My ideal would be to get a ODBC driver so I can attach its tables to Microsoft Access. Then I could write SQL queries against the database and use VBA to write tools.

Mark Rochkind in the article quoted about talks about how Lightroom’s catalog is based on a SQLite open source database and he references a tool that can examine it. I will investigate this. Maybe a solution to my reporting problem is closer than I thought.

I should also re-read the article and take note of how he uses its reports. Maybe I have more to learn than I thought in my summary dismissal above.