• Welcome to the Lightroom Queen Forums! We're a friendly bunch, so please feel free to register and join in the conversation. If you're not familiar with forums, you'll find step by step instructions on how to post your first thread under Help at the bottom of the page. You're also welcome to download our free Lightroom Quick Start eBooks and explore our other FAQ resources.
  • Stop struggling with Lightroom! There's no need to spend hours hunting for the answers to your Lightroom Classic questions. All the information you need is in Adobe Lightroom Classic - The Missing FAQ!

    To help you get started, there's a series of easy tutorials to guide you through a simple workflow. As you grow in confidence, the book switches to a conversational FAQ format, so you can quickly find answers to advanced questions. And better still, the eBooks are updated for every release, so it's always up to date.

Lightroom SQLite database binary XMP format

Joined
Jul 6, 2020
Messages
18
Lightroom Version
Lightroom Classic version: 9.3 Release, Build [202005281810-476e492c]
Operating System
  1. Windows 10
Lightroom catalog is a SQLite database. Some of the metadata values are stored under Adobe_AdditionalMetadata.XMP column which is a BLOB data type.

When I save this blob, it is some binary file that I have no idea how to convert to/from an editable form.

According to the documentation, xmp file has XML format

Here is the example of such blob from my database
 
Joined
Jun 20, 2009
Messages
18,035
Location
Houston, TX USA
Lightroom Experience
Power User
Lightroom Version
Cloud Service
Your file was not accessible. I suspect the Blob data is viewable using Apple's Xcode app because (as I learned recently) you can read the Preferences Plist file via Xcode.

The more important question is what do you need this access outside of the Lightroom App? I can understand idle curiosity (I'm guilty) but without understanding all of the ramifications of making changes to to the Catalog outside of Lightroom, you are asking for trouble.
 
Joined
Jun 20, 2009
Messages
18,035
Location
Houston, TX USA
Lightroom Experience
Power User
Lightroom Version
Cloud Service
Your file was not accessible. I suspect the Blob data is viewable using Apple's Xcode app because (as I learned recently) you can read the Preferences Plist file via Xcode.

The more important question is what do you need this access outside of the Lightroom App? I can understand idle curiosity (I'm guilty) but without understanding all of the ramifications of making changes to to the Catalog outside of Lightroom, you are asking for trouble.
It is not decodable with the Xcode app either. Sorry.
 
Joined
Jul 6, 2020
Messages
18
Your file was not accessible. I suspect the Blob data is viewable using Apple's Xcode app because (as I learned recently) you can read the Preferences Plist file via Xcode.

The more important question is what do you need this access outside of the Lightroom App? I can understand idle curiosity (I'm guilty) but without understanding all of the ramifications of making changes to to the Catalog outside of Lightroom, you are asking for trouble.

Sorry, I did not realize, I did not share the link for everybody, now it is shared properly.

I need access to it because I need to do some bulk operations that are impossible to perform from inside Lightroom itself. It could be done via LR/Transporter plugin, however the procedure is extremely inefficient.

I have to export metadata first using this plugin, edit the generated file and then import it back, but it is unnecessarily slow. I tried this procedure for ~75000 images overnight, and the progress bar moved only for a few percent. Assuming it works correctly, it would take me weeks non-stop importing. I cannot stand that low performance
 
Joined
Oct 8, 2007
Messages
5,837
Location
London
Lightroom Experience
Power User
Have you not tried writing Lua for whatever you're trying to do?

Also note my Search & Replace plugin is intended for bulk updates of metadata. Depending on what you're trying to do, it may be helpful.
 
Joined
Jul 6, 2020
Messages
18
Have you not tried writing Lua for whatever you're trying to do?

Also note my Search & Replace plugin is intended for bulk updates of metadata. Depending on what you're trying to do, it may be helpful.

No, I did not try writing Lua yet. I am not familiar with it so I am not sure if it worth to learn it for one-time task. From the other hand, I know SQL so editing lrcat SQLite file directly seems to be the simplest for me.

However, having this unknown binary xmp column is not something I know how to deal with. As far as I googled, xmp column used to be a plain text which was changed in some recent versions of Lightroom

Yes, I tried your awesome Search & Replace plugin, but it won't suit my needs. If I understand correctly, it allows setting a static value to the metadata fields, but I need to get the values for each file from an external source, so I don't see how your plugin would help
 
Joined
Oct 8, 2007
Messages
5,837
Location
London
Lightroom Experience
Power User
If the source data is external, Search & Replace is less likely to help. It might work in combination with something like LrTransporter, manipulating data after it has been imported to a standard field.

I only began learning Lua after abandoning the idea of editing LR's SQL (I had SQL Server and Oracle experience) but from what I recall of AdditionalMetadata, it's a dump for stuff in the original that LR doesn't need or can't understand, allowing LR to preserve non-standadd third party metadata which can then be passed on into exported files. Do you have any idea what this data might be? Have you tried extracting the data with Exiftool instead of focussing on AdditionalMetadata?
 
Joined
Jul 6, 2020
Messages
18
If the source data is external, Search & Replace is less likely to help. It might work in combination with something like LrTransporter, manipulating data after it has been imported to a standard field.

I only began learning Lua after abandoning the idea of editing LR's SQL (I had SQL Server and Oracle experience) but from what I recall of AdditionalMetadata, it's a dump for stuff in the original that LR doesn't need or can't understand, allowing LR to preserve non-standadd third party metadata which can then be passed on into exported files. Do you have any idea what this data might be? Have you tried extracting the data with Exiftool instead of focussing on AdditionalMetadata?

Yes, I mentioned LrTransporter in my first comment but it is extremely inefficient for my task.

Yes, avoiding raw SQL manipulation is better, because we might not know all the places have to be modified, such as updating hash values if any.

Extracting and storing metadata values could be done via ExifTool, however, the problem is making these values back to Lightroom. Read metadata from file
could help, but it doesn't work for mp4 files, unfortunately. There is a workaround for mp4 files to remove the file from the catalog, modify via ExifTool, and then readd it back. However, this approach loses all the metadata that Lightroom could not save to mp4 file.

Having said that, editing metadata directly seems to be the only reliable way at least for the mp4 files.
 
Joined
Oct 8, 2007
Messages
5,837
Location
London
Lightroom Experience
Power User
LR's SQL was never meant to be accessed by humans, just record stuff any way, any how, and its ad hoc structure has been touched by a number of hands over the years. Maybe Adobe hires graphics specialists, not database guys!

What I was thinking with Exiftool was that you could extract that metadata in bulk, then use LrTransporter to bring it into the catalogue. But alternatively Exiftool can copy metadata from one field to another, so it could copy these non-standard values to a standard field such as Headline or Instructions, which you could then get into LR by the reimport method.

Coincidentally I have recently soft-released my Video Metadata plugin which saves and reads metadata to videos, eliminating that workaround. It's mainly about standard metadata, but it quietly imports the Codec too which I needed. It might import other stuff in future, but video metadata is much messier than with still images.
 
Joined
Jul 6, 2020
Messages
18
LR's SQL was never meant to be accessed by humans, just record stuff any way, any how, and its ad hoc structure has been touched by a number of hands over the years. Maybe Adobe hires graphics specialists, not database guys!

What I was thinking with Exiftool was that you could extract that metadata in bulk, then use LrTransporter to bring it into the catalogue. But alternatively Exiftool can copy metadata from one field to another, so it could copy these non-standard values to a standard field such as Headline or Instructions, which you could then get into LR by the reimport method.

Coincidentally I have recently soft-released my Video Metadata plugin which saves and reads metadata to videos, eliminating that workaround. It's mainly about standard metadata, but it quietly imports the Codec too which I needed. It might import other stuff in future, but video metadata is much messier than with still images.

Could not find Video Metadata plugin on your website, but it would be a missing part for my needs. Do you have any ETA when you gonna release it? Can I become a beta tester?
 
Joined
Oct 8, 2007
Messages
5,837
Location
London
Lightroom Experience
Power User
That's why mentioned "soft release". I still have to fix a couple of problems with the web site update notifications, but some people really wanted to use it. So while I've not yet publicised it, it's here. In trial mode it works fully.
 
Joined
Apr 3, 2012
Messages
950
Lightroom Experience
Advanced
Lightroom Version
Classic
Regarding the original question, a number of columns in the SQLite catalog database are compressed using slightly modified versions of the compress()/uncompress() extension from sqlite.org. See my reply to your other post:
 
Joined
Jul 6, 2020
Messages
18
Regarding the original question, a number of columns in the SQLite catalog database are compressed using slightly modified versions of the compress()/uncompress() extension from sqlite.org. See my reply to your other post:
As I replied in the original post

Thank you! uncompress works and I could extract the xmp in the XML format. However, compress function is not fixed, so it is not usable yet.

I recompiled for Windows and included query samples sqlite-with-lightroom-uncompress-fixed.zip.

Are you going to fix the compress logic, or should I?

Just wondering, how did you find that format?
 
Joined
Jun 20, 2009
Messages
18,035
Location
Houston, TX USA
Lightroom Experience
Power User
Lightroom Version
Cloud Service
I work as a Senior Developer, I do such things on a daily basis )))

I used to be a Senior Developer. That is why I recommend not doing such things. Good judgement comes from experience. Experience comes from bad judgement.


Sent from my iPad using Tapatalk
 
Joined
Oct 8, 2007
Messages
5,837
Location
London
Lightroom Experience
Power User
Correct, known as the SDK.
 

PhilBurton

Lightroom enthusiast (but still learning)
Premium Classic Member
Joined
Nov 16, 2015
Messages
2,506
Location
California, USA
Lightroom Experience
Intermediate
Lightroom Version
Classic
Absolutely agree. Using official APIs normally is the best way to go. I guess 'official API' for Lightroom would be lua plugins?
Remember that an API is a commitment of sorts, even if not "forever" to maintain a consistent way to access the database. However, every major revision of the database includes a database upgrade, and it's entirely possible that this Binary Large OBject could be reformatted with the next release, because Adobe doesn't commit to maintaining internal database formats.

I was never a developer, only a software product manager, but I've "been there, seen that."

Phil Burton
 
Joined
Mar 29, 2015
Messages
719
Lightroom Experience
Intermediate
Lightroom Version
Classic
I need access to it because I need to do some bulk operations that are impossible to perform from inside Lightroom itself
I tried this procedure for ~75000 images overnight, and the progress bar moved only for a few percent
I'm wondering if you need to look at this from a different perspective and ask if LR is right tool for you or if you need a formal DAM or at least something that handles complex media workflow. It sounds like you are trying to do something in LR it was never intended to support. Just a thought.
 
Joined
Jul 6, 2020
Messages
18
I'm wondering if you need to look at this from a different perspective and ask if LR is right tool for you or if you need a formal DAM or at least something that handles complex media workflow. It sounds like you are trying to do something in LR it was never intended to support. Just a thought.
I see LR as an image/video database that handles metadata, keywords, GPS data etc. I expect any modification of any metadata to be a normal workflow.
However, I found that using ExifTool outside of Lightroom and reading metadata back to Lightroom solves most of my problems. Unfortunately, Lightroom doesn't read/write metadata from/to video files. Thanks to the @johnbeardy , it will be possible to do with his plugin.
 
Joined
Jul 6, 2020
Messages
18
Remember that an API is a commitment of sorts, even if not "forever" to maintain a consistent way to access the database. However, every major revision of the database includes a database upgrade, and it's entirely possible that this Binary Large OBject could be reformatted with the next release, because Adobe doesn't commit to maintaining internal database formats.

I was never a developer, only a software product manager, but I've "been there, seen that."

Phil Burton
Yes, modifying the database directly is a risky and fragile step. Writing your own plugin seems to be an overkill for a one-time task. Probably the best approach would be to use Scripts
 

PhilBurton

Lightroom enthusiast (but still learning)
Premium Classic Member
Joined
Nov 16, 2015
Messages
2,506
Location
California, USA
Lightroom Experience
Intermediate
Lightroom Version
Classic
Yes, modifying the database directly is a risky and fragile step. Writing your own plugin seems to be an overkill for a one-time task. Probably the best approach would be to use Scripts
The argument for writing Lua plug-in is that you know that it works against published APIs only. And should you ever have the problem again, you already have most or all of the solution. As a developer, you can probably learn Lua a lot faster than the rest of us. You might even have the core of a plug-in that you can sell commercially.

I don't know what is involved in writing scripts, but I'm going to guess that you will need far more time than you originally planned for testing. And you will still not know with 100% assurance if there are unintended consequences because of undocumented relationships between data in different fields.

Phil Burton
 
Joined
Jul 6, 2020
Messages
18
The argument for writing Lua plug-in is that you know that it works against published APIs only. And should you ever have the problem again, you already have most or all of the solution. As a developer, you can probably learn Lua a lot faster than the rest of us. You might even have the core of a plug-in that you can sell commercially.

I don't know what is involved in writing scripts, but I'm going to guess that you will need far more time than you originally planned for testing. And you will still not know with 100% assurance if there are unintended consequences because of undocumented relationships between data in different fields.

Phil Burton
Sorry, I was not clear enough, I mean built-in Lua Script support in Lightroom, such as Script to extract previews of lost or deleted photos
 
Joined
Oct 8, 2007
Messages
5,837
Location
London
Lightroom Experience
Power User
I've posted one or two on this forum, and this JPEG sidecar killer too. For some people, maybe you're one, it's an easy way to automate certain basic tasks.
 
Top