Updating star ratings using SQL

[lestercheung]

Hello fellow Lightroomers,

Not exactly a SDK question but - has anyone tried updating star ratings of their images in the Adobe_images table directly using SQL?

I wrote a little webapp that takes a catalog and the preview cache directory and display a web gallery - I'd like to let people to rate the images and have the rating trickle back into the catalog.

Studying how Lr5 updates the catalog I noticed that the following tables/columns got updated as you rate a photo:

• Adobe_variablesTable: Adobe_entityIDCounter->type
• Adobe_variablesTable: AgLibraryImageXMPUpdater_WorklistPending->value
• Adobe_images: rating, sidecarStatus, touchCount and touchTime
• AgLibraryImageXMPUpdater:

My question is:

If I only care about the rating in the catalog can I simply update the rating column and be happy? Will that break Lightroom in anyway?

Also, if I would like to have the metadata written back to the JPEGs and sidecar files - how do I schedule that?

Any comment will be appreciated!

-les

[areohbee]

Set sidecarStatus to 1 in Adobe_images table to set the "metadata needs saving" flag.

R

[lestercheung]

Thanks Rob. Appreciated!

-les

[areohbee]

You bet .

[derpzxc]

Unfortunately doesn't work anymore (I'm trying on Lr4 and Lr5). Image thumbnail is missing after changing rating manually in sqlite.

Tried to change separately and combined:

- rating

- sidecarStatus

- touchCount

- touchTime

- AgLibraryImageXMPUpdater_WorklistPending

- Adobe_entityIDCounter

Any help will be really appreciated.

[lestercheung]

Post the SQL statements that you used. It's still working for me with 5.6 here.  

-les

[derpzxc]

Wow, I tried to change rating and sidecarStatus in one SQL statement and it worked.

The reason it didn't work for me before - I was trying to change values in SQL via sqlitebrowser, one value at the time.

Thank you a lot for your response, I should tried to do it in one statement before

[hobster12345]

Hello, I have the same problem.

When I use SQLitebrowser to Update the star rating of an image the image thumbnail is missing or grey.

Here is my statement.

UPDATE Adobe_images SET rating = '5.0' WHERE id_local = xyz;

Do I have to update some other values, as well or do have to make changes in other tables as well?

My Lightroom version 5.7

I have another question how is the TouchTime calculated? I am not able to get the correct date value from it.

[hobster12345]

Hello everybody, I found the solution.

Thumbnails become grey, when the images data in Adobe_imgages is not valid.

In my case this was caused by apostrophes. When I leave them away and use following statement it works.

UPDATE Adobe_images SET rating = 5.0 WHERE id_local = xyz;

[johnrellis]

In my case this was caused by apostrophes. When I leave them away and use following statement it works.

UPDATE Adobe_images SET rating = 5.0 WHERE id_local = xyz;

This is because SQLite is dynamically typed, and the type declarations of columns are just hints about what types should be stored in the columns: https://sqlite.org/datatype3.html .

So when you stored the value '5.0', that was storing a text value in the column, not a number. Since any type can be stored in any column, no error was thrown when you did this.