# Lightroom SQLite database binary XMP format



## mnaoumov (Jul 10, 2020)

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 (Jul 10, 2020)

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 (Jul 10, 2020)

clee01l said:


> 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 (Jul 10, 2020)

clee01l said:


> 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 (Jul 10, 2020)

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 (Jul 10, 2020)

clee01l said:


> 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 (Jul 10, 2020)

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 (Jul 10, 2020)

johnbeardy said:


> 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 (Jul 10, 2020)

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 (Jul 10, 2020)

johnbeardy said:


> 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 (Jul 10, 2020)

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 (Jul 10, 2020)

johnbeardy said:


> 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 (Jul 10, 2020)

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.


----------



## johnrellis (Jul 10, 2020)

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 (Jul 10, 2020)

johnrellis said:


> 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 (Jul 10, 2020)

mnaoumov said:


> 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 (Jul 10, 2020)

clee01l said:


> 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 (Jul 10, 2020)

Correct, known as the SDK.


----------



## PhilBurton (Jul 11, 2020)

mnaoumov said:


> 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 (Jul 11, 2020)

mnaoumov said:


> I need access to it because I need to do some bulk operations that are impossible to perform from inside Lightroom itself





mnaoumov said:


> 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 (Jul 13, 2020)

Paul_DS256 said:


> 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 (Jul 13, 2020)

PhilBurton said:


> 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 (Jul 13, 2020)

mnaoumov said:


> 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 (Jul 13, 2020)

PhilBurton said:


> 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 (Jul 13, 2020)

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.


----------



## mnaoumov (Jul 10, 2020)

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


----------



## mnaoumov (Jul 15, 2020)

Unfortunately, SDK is not a silver bullet, it would not satisfy all the needs, even some basic ones. For example, it is impossible to set *Capture Time* using SDK. Some plugins modify the date via ExifTool and then read metadata from the file. But for video files, *Read metadata* is not available, so it is a dead-end. All the ways to overcome this issue, are dirty and lame workarounds


----------



## johnrellis (Jul 15, 2020)

mnaoumov said:


> it is impossible to set *Capture Time* using SDK. Some plugins modify the date via ExifTool and then read metadata from the file.


Yeah, that's a huge omission from the SDK. But putting aside the SDK, Capture Time in LR is a mess, with longstanding bugs arising out of basic architectural flaws in how it is represented and manipulated internally.



mnaoumov said:


> But for video files, *Read metadata* is not available, so it is a dead-end. All the ways to overcome this issue, are dirty and lame workarounds


In general, LR support for video has always been problematic since its introduction in LR 3. Basic features are missing, the metadata support is incomplete and buggy, and there's a constant, never-ending stream of bug reports about problems with import and playback on capable hardware. I gave up on it a long time ago, using my Any File plugin to catalog videos and relying on Quick Time to play them back with a keyboard shortcut.


----------



## mnaoumov (Jul 15, 2020)

johnrellis said:


> Yeah, that's a huge omission from the SDK. But putting aside the SDK, Capture Time in LR is a mess, with longstanding bugs arising out of basic architectural flaws in how it is represented and manipulated internally.
> 
> 
> In general, LR support for video has always been problematic since its introduction in LR 3. Basic features are missing, the metadata support is incomplete and buggy, and there's a constant, never-ending stream of bug reports about problems with import and playback on capable hardware. I gave up on it a long time ago, using my Any File plugin to catalog videos and relying on Quick Time to play them back with a keyboard shortcut.



I am a  bit newbie with LR. I have a need to have things organized.

Back in 2014, I was even asking a question Best ways to organize photo collections which I did not conclude. Before that, I tried to use Google Picasa but I figured out, later on, it kept its metadata database inside its folder, so I lost it when reinstalling Windows.

Then some of my coworkers told me about Lightroom, so I started to use it. But two things stopped me: 1. poor of video support; 2. difficulties choosing a good photo hosting provider for all my photos/videos.
Lightroom provides an export/publish functionality, but I see it is more about like a selective update made on-demand.
In my mind, I want to have an instant update of my whole catalog with the ability to access it from the other computer or phone.

Then for many years, I was trying to find a good alternative but after all, the most popular solution is said to be Lightroom.

I am not really committed to Lightroom (strangely to see such a statement on the website with Lightroom in its address). A don't really understand why some critical things sit on the Adobe's forums for ages and they are not even trying to communicate back.

I like approach by JetBrains company. Their bug tracker is publicly available and you can see the progress of the desired fix and expected version, or you can vote for the most required features. Adobe is not ready to be that transparent, apparently.

After many years of looking, I came back to try Lightroom again and I see everything that bothered me back in 2014 is still there. Well, we've got Adobe Cloud, which would solve my problem #2 mentioned above. However, Lightroom Classic doesn't upload videos there. I tried to use Lightroom CC instead, but I think it is not as friendly as Lightroom Classic.

I started to think maybe I am just stupid and moody since Lightroom is such a popular product. And I just keep nagging and nagging.
So I purchased some books to read:
1. Peter Krogh - Organizing Your Photos with Lightroom 5
2. Victoria Bampton - Adobe Lightroom Classic - The Missing FAQ (9.3-2020)
3. Peter Krogh - The DAM Book 3.0

Maybe I get some missing bits from them. So far read #1 and a half of #2. Let's see what I think after I read them all...


----------

