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
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.]
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
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.]
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.]
Copy link to clipboard
Copied
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?
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.]
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