«
»

What I’ve Been Working On

By bkkphotographer

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 Sourceforge.net 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?

Tags: , ,

This entry was posted on July 6, 2009 at 11:56 pm and is filed under Lightroom. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “What I’ve Been Working On”

  1. Checking Integrity « Bkkphotographer's Blog Says:
    August 13, 2009 at 8:42 pm | Reply

    [...] wish I could get the ODBC driver for the SQLLite database to work. I discussed it here. Then I could make my own checks by attaching it to a MS Access [...]

  2. BKKPhotographer Says:
    August 31, 2009 at 10:43 pm | Reply

    I haven’t made any progress on the ODBC driver. I need it more now. I want to be able to search for duplicate registrations to prove the theory that Thai DLT offices issue registrations independently of each other. There should be up to 76 buses driving around with registration 10-1234. I want to see if I have photographed any.

  3. Solved the Thai Code List – I Think « Bkkphotographer's Blog Says:
    October 11, 2009 at 11:28 am | Reply

    [...] I want to be able to search by Lightroom catalog for such duplicates. i have the data in my add-in but it is hard to write a SQL query – not least because I still have not found a working ODBC driver for the Lightroom database. [...]

  4. Browsing the Lightroom Catalog « Bkkphotographer's Blog Says:
    October 20, 2009 at 11:45 am | Reply

    [...] to be able to generate reports on my Lightroom database catalog. I talked about my reasons for it here and [...]

Leave a Reply