NANPA - Nature Photography Forums

NANPA - Nature Photography Forums (http://www.nanpa.org/forums/index.php)
-   Image Processing and storage (http://www.nanpa.org/forums/forumdisplay.php?f=18)
-   -   Lightroom Catalog Table Structure (http://www.nanpa.org/forums/showthread.php?t=256)

plunkettphoto 05-30-2010 02:47 PM

Lightroom Catalog Table Structure
 
I posted the following message in the open discussions section but thought it fit fairly well here also. Sorry for the duplication.
_________________________________________

Some of you know that I am an old gray-beard programmer from a prior life. Well I decided to build an application for my own in-house use that reads information from a Lightroom Catalog. Doubt that it will ever be a commercially viable app but then you never know.

I need to read information from the LR catalog (actually, a copy of the catalog) which is actually a SQLite RDBMS. I can find all the information I am looking for in the various cryptic tables in the database. Problem is I can't figure out how to tie the rows in the various tables together for a particular image. Most tables contain fields for id_local and id_global but they don't match across tables.
I thought it would be obvious how the tables were structured by creating a catalog with only 3 images in it with very clear caption and title information in each. This is how I know that what I thought would be the primary keys (tied to a particular image) don't contain the same values.
Does anyone know how the tables are structured, or where they are documented or who I can contact for more information?
Thanks,
bill plunkett

jalock 06-01-2010 04:50 PM

There are some brief examples of querying the LR database structure at http://www.earthboundlight.com/phototips/querying-sqlite-lightroom-database.htm You might get some clues from that. I am assuming you already have some king of GUI tool for browsing the SQLite database structure. Most other references I have seen recommend getting the Lightroom SDK and documentation to get a peek inside.

plunkettphoto 06-03-2010 06:06 PM

All good suggestions John. Problem is, I've already done all that including the earthboundlight web site. He got about as far as I did on reverse engineering the structure.

The Lightroom Catalog is just a SQLite database which explains the upper limit on the catalog size. So it is browsable by any SQL browser - which I have several including the one I have written.

Since I am sure the Adobe Engineers are highly sophisticated and capable when it comes to database design, I am sure it is my limitations at work here. Because SQLite does have a size limit and because catalog integrity is paramount, I can only believe that some kind of very high level normalization is at work here. There is no one key that you can use to access all the information in all the tables related to any given image. You take the key from one table and access the next table with another field from the first table. I am now in the process of figuring out the pattern of keys. Let's just say "It ain't easy"

Of course to make matters worse, I have added custom metadata fields to LR to identify the records to the application I am working on outside of Lightroom.

I first envisioned that I would write a plug-in to read the metadata in an image record and write it out in some form that I can import into my app. Problem is, the SDK has very limited functions that can be programmed (Metadata, Export, Web Gallery) and the Lua language that you have to use for plug-ins can't write to external files.

I know I am way out on an edge here but if anyone can offer any suggestions I would greatly appreciate it....

bill

plunkettphoto 06-04-2010 04:14 PM

I found the way in at last! The specifics here will only apply to the way my system is setup.

I defined a custom metadata field in the LR catalog as an ID field that ties back to my standalone application. I was having a very hard time trying to figure out how to use the ID in my app to get all the info I wanted from the Lr catalog but I found the way. The AgSearchablePhotoProperty table contains the custom metadata field and the image number that is used by Adobe_Images table - this number, via 3 other fields, ties in a very crooked line to all the other tables.

It is a very circuitous path to get to all the information in all the different tables but it can be done.

It goes without saying, if anyone has any thoughts about doing this themselves, be SURE to use a BACKUP copy of the catalog.

If anyone wants to more about how to access the LR catalog via SQL commands you can email me at bill@plunkettphoto.com


All times are GMT -5. The time now is 01:56 AM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.