Highlighted

Lightroom SQLite database binary XMP format

Community Beginner ,
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

Most Valuable Participant
Correct answer by johnrellis | Most Valuable Participant

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.]

TOPICS
FAQ, How to, Import and export

Views

80

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

Lightroom SQLite database binary XMP format

Community Beginner ,
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

Most Valuable Participant
Correct answer by johnrellis | Most Valuable Participant

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.]

TOPICS
FAQ, How to, Import and export

Views

81

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
Jul 10, 2020 0
Most Valuable Participant ,
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
Reply
Loading...
Jul 10, 2020 1
Community Beginner ,
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
Reply
Loading...
Jul 10, 2020 0
Most Valuable Participant ,
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
Reply
Loading...
Jul 10, 2020 0
mnaoumov LATEST
Community Beginner ,
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
Reply
Loading...
Jul 10, 2020 0