Lightroom SQLite database binary XMP format

Community Beginner ,
Jul 10, 2020 Jul 10, 2020

Copy link to clipboard

Copied

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

Views

469

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

LEGEND , Jul 10, 2020 Jul 10, 2020
A couple three years ago, LR started compressing the larger text blobs in the SQLite catalog. While there's a standard extension at sqlite.org using zlib providing compress() and uncompress() functions, Adobe in its wisdom used a slightly modified version that encodes the length of the uncompressed blob slightly differently. You can download my small modifications to the standard compress/uncompress from here: https://www.dropbox.com/s/cjnep7gdhki3e9j/sqlite.2020.07.10.zip?dl=0  See _REAMDE.tx...

Likes

Translate

Translate
LEGEND ,
Jul 10, 2020 Jul 10, 2020

Copy link to clipboard

Copied

A couple three years ago, LR started compressing the larger text blobs in the SQLite catalog. While there's a standard extension at sqlite.org using zlib providing compress() and uncompress() functions, Adobe in its wisdom used a slightly modified version that encodes the length of the uncompressed blob slightly differently. You can download my small modifications to the standard compress/uncompress from here:

https://www.dropbox.com/s/cjnep7gdhki3e9j/sqlite.2020.07.10.zip?dl=0 

 

See _REAMDE.txt.

 

[Use the blue reply button under the first post to ensure replies sort properly.]

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jul 10, 2020 Jul 10, 2020

Copy link to clipboard

Copied

@johnrellis

 

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 https://drive.google.com/file/d/1EuSB8SrOA2nAhwTqjI3V1xK44IyxI9gt/view?usp=sharing.

 

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

 

Just wondering, how did you find that format?

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 10, 2020 Jul 10, 2020

Copy link to clipboard

Copied

I don't have any plans or need to modify compress(), so if you need it, go ahead. (A couple of my plugins read the SQLite database but none modify it--in my commercially available plugins, it's one thing to read an undocumented database, another to modify it!)

 

One of my older plugins, Any Comment, read a column that started being compressed in LR 9 (?).  When first tryinging to decode these compressed columns, I came across the standard sqlite.org extensions compress() and  uncompress(). I figured LR was likely using zlib(), so it just took a little bit sleuthing to figure out Adobe's tweaked representation.  It's annoying but typical for Adobe to make such a gratuitous change.

 

[Use the blue reply button under the first post to ensure replies sort properly.]

 

 

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jul 10, 2020 Jul 10, 2020

Copy link to clipboard

Copied

Wow, this reverse engineering skill sounds like magic even for me, 12 years of professional development. Hats off. Thanks for your invaluable input

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 24, 2022 Apr 24, 2022

Copy link to clipboard

Copied

Could you please provide a description of your modifications?

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 25, 2022 Apr 25, 2022

Copy link to clipboard

Copied

LATEST

I am not the author of the modifications. The link to Dropbox by @johnrellis above contains the code which you can compare to the sources of the original SQLite codebase to figure out the difference. Essentially the implementaion of uncompress function slightly changed because of the byte order in the Lightroom's implementation

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines