Lightroom SQLite database binary XMP format

[mnaoumov]

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

[johnrellis · 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.]

[mnaoumov]

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

[johnrellis]

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

[mnaoumov]

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

[Asher-]

Could you please provide a description of your modifications?

[mnaoumov]

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