Lightroom SQLite database binary XMP format

[#1]

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

[#2]

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

[#3 · Accepted solution]

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

[#4]

@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?

[#5]

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

[#6]

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