6
\$\begingroup\$

I'm looking for some documentation for the Lightroom Catalog (sqlite database) structures.

Here's a simple example of what I'm trying to do. I found a table containing the exif data (AgHarvestedExifMetadata) and now I'm trying to join it with the original image (AgLibraryFile) but can't find a common field to join on. In this example, it seems id_global might do it but I've had no luck so far.

Where can I find documentation or examples using the backend Lightroom catalog / database? I found documentation for sqlite but I'm looking for the Lightroom database structure (tables, foreign keys, referential integrity, etc).

Improve this question
\$\endgroup\$
4
  • 3
    \$\begingroup\$ This isn't really a photography question. The internal schema used in an embedded db in LR doesn't directly relate to photography. \$\endgroup\$
    – Robin
    Commented Oct 4, 2017 at 17:08
  • 6
    \$\begingroup\$ @Robin - I think it's more photography than software engineering & development. It's a question specific to a piece of software used exclusively by photographers to manage workflows. I'm hoping there are photographers out there that dig into the internals of their tools and build other tools to extend functionality. \$\endgroup\$
    – DenaliHardtail
    Commented Oct 4, 2017 at 17:16
  • \$\begingroup\$ If there is not official documentation from Adobe, it is reasonable to assume that reverse engineering is the alternative. If official documentation exists, it does not appear to be widely advertized. If a long standing open source project reverse engineering the Lightroom database exists, it does not appear to be widely advertized either. Because Lightroom is closed source and runs on proprietary operating systems, neither of these is surprising. Agree or disagree with Stallman, his intuition about freedoms of users seems to have been borne out over the past several decades. \$\endgroup\$
    – user50888
    Commented Oct 4, 2017 at 17:45
  • 1
    \$\begingroup\$ Have you tried contacting Adobe? \$\endgroup\$
    – user50888
    Commented Oct 4, 2017 at 17:46

4 Answers 4

Reset to default
8
\$\begingroup\$

I am sure there is no official documentation - Adobe does not really want us poking around in there. Having said that there are a few blog posts with information around the contents of the catalogue and even a few that deal with that table. Here are a few links...

This does not specifically address your question but may shed light on how it is joined to other tables: http://www.max.grenkowitz.net/?topic=1057

This article has several sql statements that might help you figure out how the table relates to others: https://forums.adobe.com/thread/1812037

A nice overview with additional links and some examples: http://www.earthboundlight.com/phototips/querying-sqlite-lightroom-database.html

Another example using that table: http://www.wideopenlens.com/blog/2012/08/hacking-lightroom-or-why-everyone-should-know-how-to-program

Improve this answer
\$\endgroup\$
1
  • 1
    \$\begingroup\$ Thanks, Ian. That is exactly the type of information I need. \$\endgroup\$
    – DenaliHardtail
    Commented Oct 4, 2017 at 18:01
1
\$\begingroup\$

Here are some Python bindings to the database published by a user:

https://github.com/bzimmer/openroom

Tables are modeled in the openroom/model.py file, including some foreign keys. For example, the AgHarvestedExifMetadata table:

class Exif(Base):
    __tablename__ = "AgHarvestedExifMetadata"
    id = Column("id_local", Integer, primary_key=True)
    imageId = Column("image", Integer, ForeignKey("Adobe_images.id_local"))
    image = relation(Image, backref=backref('exif', uselist=False))
    aperture = Column(Integer)
    cameraId  = Column("cameraModelRef", Integer, ForeignKey("AgInternedExifCameraModel.id_local"))
    camera = relation(Camera)
    cameraSNId  = Column("cameraSNRef", Integer)
    dateDay = Column(Integer)
    dateMonth = Column(Integer)
    dateYear = Column(Integer)
    flashFired = Column(Boolean)
    focalLength = Column(Numeric)
    hasGPS = Column(Boolean)
    isoSpeedRating = Column(Integer)
    lensId = Column("lensRef", Integer, ForeignKey("AgInternedExifLens.id_local"))
    lens = relation(Lens)
    shutterSpeed = Column(Numeric)

Only a few tables are represented and the definitions are from 2009 but I suspect they are the same or similar in more recent versions of Lightroom.

Improve this answer
\$\endgroup\$
1
\$\begingroup\$

Looking for such information recently myself I found a few websites providing Lightroom database information:

Furthermore, I had a look at the schema myself, "re-created" some foreign keys by their names and created a schema of Lightroom 5 catalogue using SchemaSpy:

Improve this answer
\$\endgroup\$
0
\$\begingroup\$

Licenses typically contain a line or paragraph that explicitly disallows reverse engineering; I am quite sure that this includes analyzing the database. I would be very careful unless you have verified the license conditions allow what you are trying to do. The full text is here: http://labs.adobe.com/technologies/eula/lightroom.html; it says:

[...] You may not reverse engineer, decompile, disassemble or otherwise attempt to discover the source code of the Software except to the extent you may be expressly permitted under applicable law to decompile only [...]

It might take a lawyer to interpret that for sure, as a database structure is not really source code in my mind. If the license includes the disallowing of the database analysis, it could well be that Adobe doesn’t care much that you do it, but that wouldn’t make it less illegal.

Improve this answer
\$\endgroup\$
1
  • 1
    \$\begingroup\$ IANAL, but I'm pretty sure the "source code of the Software" does not entail almost plain text representations of a library in SQLite format. \$\endgroup\$
    – Sinus has tentacles
    Commented May 26, 2019 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.