North American Nature Photography Association

Connecting The Nature Photography Community

 
Join NANPA!



  #1  
Old 05-30-2010, 03:47 PM
plunkettphoto plunkettphoto is offline
NANPA Board Member
 
Join Date: Jul 2009
Location: Judson, TX
Posts: 21
Default 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
__________________
William (Bill) Plunkett
www.plunkettphoto.com
bill@plunkettphoto.com
NANPA Board Member
Reply With Quote
  #2  
Old 06-01-2010, 05:50 PM
jalock's Avatar
jalock jalock is offline
NANPA Webmaster
 
Join Date: Jun 2009
Location: Eatonton, GA
Posts: 17
Default

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.
__________________
John Lock
NANPA Webmaster
"He was a wise man who invented beer. -- Plato"
Reply With Quote
  #3  
Old 06-03-2010, 07:06 PM
plunkettphoto plunkettphoto is offline
NANPA Board Member
 
Join Date: Jul 2009
Location: Judson, TX
Posts: 21
Default

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
__________________
William (Bill) Plunkett
www.plunkettphoto.com
bill@plunkettphoto.com
NANPA Board Member

Last edited by plunkettphoto; 06-03-2010 at 07:13 PM.
Reply With Quote
  #4  
Old 06-04-2010, 05:14 PM
plunkettphoto plunkettphoto is offline
NANPA Board Member
 
Join Date: Jul 2009
Location: Judson, TX
Posts: 21
Default

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
__________________
William (Bill) Plunkett
www.plunkettphoto.com
bill@plunkettphoto.com
NANPA Board Member
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


Enabled for iPhone,Android,Nokia
Enabled for iPhone,Android,Nokia
Click for App

All times are GMT -5. The time now is 08:12 PM.


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

Web site design © Relevant Arts