Search code examples
sqlitereverse-engineeringxmplightroom

Lightroom SQLite database binary XMP format

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

Solution

  • I was advised on the Lightroom forums, such columns use non-standard SQLite compress module.

    Here is the link to the SQLite Windows binaries recompiled with non-standard compress module: https://github.com/mnaoumov/sqlite-lightroom-compress-fix

    It can extract the xmp properties

    select uncompress(xmp) from Adobe_AdditionalMetadata where id_local = 4539794;

    But cannot write it back yet.

    update Adobe_AdditionalMetadata
set xmp = compress('some valid xmp string value')
where id_local = 4539794;

    It executes successfully but you won't be able to read it back with uncompress() function.

    That's because the library from the link above fixes only uncompress() function. I am working to fix the compress() function as well