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

mnaoumov
  • 2,146
  • 2
  • 22
  • 31

1 Answers1

0

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

mnaoumov
  • 2,146
  • 2
  • 22
  • 31
  • Make sure that `won't be able to read it back` is not a result of `'some string value'` not being valid XMP. Also `non-standard compress` is asking for trouble once others want to read your database: they might be unable to use foreign binaries or Windows. – AmigoJack Jul 12 '20 at 08:32
  • @AmigoJack obviously I tied proper xmp without success. And non-standard compress is not my choice, that's how Lightroom works internally – mnaoumov Jul 12 '20 at 19:46
  • Then your example code is incorrect and will still trigger further questions, such as if `SELECT compress( uncompress( xmp ) )= xmp` is TRUE or not. – AmigoJack Jul 12 '20 at 21:45
  • @AmigoJack thanks, modified code sample for more clarity – mnaoumov Jul 13 '20 at 12:38
  • It's worse than before: now it contradicts to your own answer - you already know how to decode the BLOB: by using `uncompress()`. Wasn't your actual problem that `compress()` might be working differently than expected? – AmigoJack Jul 13 '20 at 14:27
  • @AmigoJack yes, the real problem is that the Lightroom database decided to use their own compress/uncompress implementation. I found a library that reverse-engineered the `uncompress()` function, but not `compress()`, so that's why I need to implement it by myself – mnaoumov Jul 13 '20 at 14:32
  • 1
    Why is everyone referring to this compiled code without referencing the modifications or the source to compile it???? – Asher Apr 24 '22 at 22:12
  • @Asher if you need it for some reason, I can prepare a commit with changes on GitHub – mnaoumov Apr 25 '22 at 23:04