Lightroom SQLite database binary XMP format

[mnaoumov]

Lightroom Version Number

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

 

[clee01l]

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.

 

[clee01l]

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.

 

[mnaoumov]

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

 

[clee01l]

Again unless you have a complete understanding of the realties and data base structure you are going to end up doing more harm than good.

 

[mnaoumov]

Again unless you have a complete understanding of the realties and data base structure you are going to end up doing more harm than good.

I work as a Senior Developer, I do such things on a daily basis )))

 

[johnbeardy]

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.

 

[mnaoumov]

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

 

[johnbeardy]

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?

 

[mnaoumov]

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.

 

[johnbeardy]

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.

 

[mnaoumov]

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?

 

[johnbeardy]

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.

 

[John R Ellis]

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:
https://community.adobe.com/t5/ligh...binary-xmp-format/m-p/11278896?page=1#M193423

 

[mnaoumov]

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:
https://community.adobe.com/t5/ligh...binary-xmp-format/m-p/11278896?page=1#M193423

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?

 

[clee01l]

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

 

[mnaoumov]

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

Absolutely agree. Using official APIs normally is the best way to go. I guess 'official API' for Lightroom would be lua plugins?

 

[johnbeardy]

Correct, known as the SDK.

 

[PhilBurton]

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

 

[Paul_DS256]

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.

 

[mnaoumov]

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.

 

[mnaoumov]

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]

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

 

[mnaoumov]

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

 

[johnbeardy]

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.