# Exploring the LR database



## Adri Hogewoning (Apr 6, 2016)

Currently I am exploring the LR database but cannot find much documentation about it. With some DBtools it is easy to see and study all tables, but it is unclear how these are related onto each onther. Can anyone help in that area? Does anyone have a database model showing HOW tables are related, by which field(s) they are connected and their relationship (many to one, one to many, etc)? Any version documentation can be useful to me. I hope someone can provide these or direct me to some resource.
Many thanks for anyone's help and thoughts in advance, sincerely,

Adri Hogewoning


----------



## clee01l (Apr 6, 2016)

Welcome to the forum.  I've not seen anything from Adobe to document the database construction. And they probably have no reason to make it public. However, much is self evident when exploring the table names and the fields with in.  Key fields help determine table relationships.  It seems evident that the database was originally derived from an older structure.


----------



## Linwood Ferguson (Apr 6, 2016)

I think a lot of us have poked in it.  It's fairly clear for the basic stuff, though they often rename keys (e.g. it will be something like id_local in the Adobe_image table, but then "photo" in another, or "image" in yet another. 

Where it gets hard to deal with, as it obviously is procedural code based, is in tables like AgLibraryPublishedCollectionContent, where the "content" column is a huge mess of settings in an internal format not really accessible from SQL, at least reasonably). 

I have never tried committing anything to writing to share, but I and others can try to answer questions if you have a specific one.


----------



## Adri Hogewoning (Apr 15, 2016)

Attached a first version (v0.1) of a part of the Adobe Lightroom (v6) database and a self-created reference document (v2).

I have tried to deduce the relations and connecting fields between the mentioned tables as good as I can. I also introduced some color to distinguish certain area’s / zones of same interest. Please note that no tables containing names with “…Face…”or “…Oz…”are added (yet). Especially the relation between “File”, “Folder”, “Collection” and “Library” is pretty unclear to me.  

_Everyone is free and strongly invited to comment on this version. Please send your ideas and suggestions to: Adri Hogewoning (__[email protected]__). I would like to add more tables and / or even complete the full database layout in such ER diagram model.  _


----------



## PhilBurton (Apr 15, 2016)

Ferguson said:


> I think a lot of us have poked in it.  It's fairly clear for the basic stuff, though they often rename keys (e.g. it will be something like id_local in the Adobe_image table, but then "photo" in another, or "image" in yet another.
> 
> Where it gets hard to deal with, as it obviously is procedural code based, is in tables like AgLibraryPublishedCollectionContent, where the "content" column is a huge mess of settings in an internal format not really accessible from SQL, at least reasonably).
> 
> I have never tried committing anything to writing to share, but I and others can try to answer questions if you have a specific one.



To all plug-in authors:

If Adri's project succeeds in producing good documentation for lrcat, how would that affect your ability to write useful plug-ins.

Phil


----------



## johnbeardy (Apr 16, 2016)

Not at all. Adobe's approved way to access a catalogue is via the SDK, not by SQL, and it would be irresponsible to distribute a plugin that updated the SQL directly. That limits any legitimate plugin to read operations. But you can't query the current Lightroom database so any data can't be real time, and any plugin author able to figure out the poor workaround for that limitation would be able to figure out the database for themselves. When you really do need the info, you can already dig in and find what you need. Oh, and the internal structure has occasionally changed when Adobe needed to do so, not much, but enough.

John


----------



## johnbeardy (Oct 20, 2021)

Linwood Ferguson said:


> Where it gets hard to deal with, as it obviously is procedural code based, is in tables like AgLibraryPublishedCollectionContent, where the "content" column is a huge mess of settings in an internal format not really accessible from SQL, at least reasonably).



I don't like to reopen old threads, and I've not changed from my view that "Adobe's approved way to access a catalogue is via the SDK, not by SQL". Anyway....

This thread happened to be top of a Google search when I was investigating AgLibraryPublishedCollectionContent. I'd been contacted by someone who needed to change the export location of a published service, and unless I've missed some trick, LR doesn't allow a plugin to do this. 

But in this AgLibraryPublishedCollectionContent the items called "export.service.connection" contain the publish service definitions, and it's possible to edit the content field - it's Lua format -  to point the service to a new location. Maybe some day someone will be Googling how to do this!


----------



## Gnits (Oct 21, 2021)

In the very early versions of Lr, I explored the size of the biggest tables, to try and determine what was causing the catalog to  expand so much.  I think I found that by regularly removing the history I reduced significantly the size of the catalog and found Lr got a little faster and more stable for a while. 

I have not considered doing this for years and years, I suspect because Lr became more stable and the need to do so no longer applied.

I also found that plugins , such as ListView by JohnBeardy allowed me to export data to text and excel files, so I could use this data to automate aspects of my workflow ( eg Word Mail Merge to create Pdf documents combining Lr images and text, InDesign to place metadata with images, integration of metadata and images into WordPress to automate creation of blog pages when travelling, etc).

John responded to many of my requests to include features in ListView plug-in to facilitate such activity.

I suspect I have got way to lazy now to consider delving into the database tables and never really cracked how the various tables were linked to each other to generate useful queries for myself.


----------

