Posts Tagged ‘SQLiteSpy’

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.