SDK: New Lightroom Plugin to see Develop History Timestamps

[27shutterclicks]

Following a scenario not long ago where I got lost in all my files, virtual copies, stars, flag and color labels trying to match one of the Lightroom images with the physical print in my hands , I came across a couple of posts here in the forums where other people were asking about being able to see the timestamps for every develop history step.

Reference post 1: https://community.adobe.com/t5/lightroom-ecosystem-cloud-based-discussions/timestamp-in-lr-history-f...

Reference post 2: https://community.adobe.com/t5/lightroom-classic-discussions/where-is-the-date-a-photo-was-last-edit...

That got me thinking about looking into the .lrcat file for the develop history timestamps. I found them, then one thing led to another, I learned Lua and the SDK and made a plugin to display that information. Link below.

Develop History Timestamps Lightroom plugin: https://github.com/27shutterclicks/lrdevhisttimestamps

So far all works well for me, but being a novice at both the SDK and Lua as a language, I wanted to post about it here to get the input of more experienced developers as to the mechanism it uses to retrieve the information.

Basically, it runs an async task to make a shell command using sqlite and retrieve data from one of the catalog tables, as I am sure you know.

From my readings, I saw various information about risks related to possible database corruption when accessing it like this, since this seems to ignore any locks Lightroom puts on the catalog. I can't say I understand the purpose of the lock and whether it matters if no write operations are made to the database directly. 

What I would like to know is there a considerable risk about this approach? Are there additional precautions I should take codewise to make things better regarding the queries?

Thanks.

[johnrellis · Accepted solution]

"...to make a shell command using sqlite and retrieve data from one of the catalog tables, as I am sure you know."

Very interesting.  In older versions, LR put an operating-system file lock on the .lrcat file, preventing plugins from using sqlite3 from accessing the catalog while LR was running. So plugins, including my Any Comment plugin, would do a messy song-and-dance of exiting LR, having a background shell script run sqlite3 and then restart LR when done.  

But I just tested versions 6, 8, 9, 10, and 11, and it appears that some version between 6.14 and 8.4.1 stopped placing the file lock on the .lrcat file.

"From my readings, I saw various information about risks related to possible database corruption when accessing it like this, since this seems to ignore any locks Lightroom puts on the catalog. I can't say I understand the purpose of the lock and whether it matters if no write operations are made to the database directly. 

"What I would like to know is there a considerable risk about this approach? Are there additional precautions I should take codewise to make things better regarding the queries?"

There are different kinds of locks involved:

- LR creates a .lock file in the catalog folder while it's running and deletes it on exit. When LR starts, it checks for the presence of that file and exits immediately with an error if it exists.  This prevents application-level confusion if two instances of LR try to access the catalog at the same time.  It has nothing to do with consistent access to the sqlite3 catalog database.

- As mentioned above, older versions of LR put an operating-system file lock on the .lrcat file that would prevent sqlite3 from accessing it while LR was running.  It was never clear why LR did that, but at least since 8.4.1 it no longer does so!

- LR relies on normal Sqlite transaction-level locking, which implements the normal database transaction consistency semantics.   Sqlite uses operating-system advisory locks to allow concurrent access by multiple threads within a process and by multiple processes.  This ensures that apps that use the database transactions properly will get the typical database ACID properties (Atomicity, Consistency, Isolation, Durability).   Sqlite itself is extremely robust open-software, used extensively by dozens of leading vendors (Apple, Cisco, etc.).  

I don't have much insight into how correctly LR uses transactions, but the very small number of database "corruptions" over the years tend to be either hardware failures or application-level programming errors.

All that being said, I don't see any risk with a plugin using sqlite3 to read the contents of the database. It will put read locks on the data records/blocks it reads, ensuring that it will read a transactionally consistent snapshot of the catalog data, and those will play nicely with the locks placed by LR's use of Sqlite.  Of course, you'd want to make sure that it doesn't do any "long running" read operations, otherwise catalog writes by LR proper might time out (I think -- I'm more than a little rusty on database transaction implementations).  Regardless, there's no risk of your plugin corrupting catalog data.

You might have seen discussions about LR's restriction on placing a catalog on a network volume.  There's a lot of misinformed folklore about this. But without going into gory details, Sqlite3 doesn't recommend placing databases on network volumes because of blatant bugs in the implementation of filesystem locking on some common file systems, NFS in particular, on which Sqlite3 relies for transactions.   So after preliminary testing some 13-14 years ago, the developers implemented that restriction on LR catalogs.

This isn't relevant to you, for two reasons: The LR application doesn't allow catalogs to be placed on network volumes, and since LR 7 or 8 LR uses Sqlite3's more efficient "write ahead logging" option for database updates that isn't implemented for network volumes.  

So for your purposes, Sqlite3 locking on catalogs on network volumes is a moot issue.

[27shutterclicks]

Thank you for the detailed feedback, John.

"In older versions, LR put an operating-system file lock on the .lrcat file, preventing plugins from using sqlite3 from accessing the catalog while LR was running."

Ok, so the .lrcat.lock file that still exists in the folder is more for preventing multiple copies of Lightroom from accessing the catalog at the same time and is not the operating-system file lock that used to exist? I thought .lock was the OS file lock, which was probably the source of my worries.

"Of course, you'd want to make sure that it doesn't do any "long running" read operations..."

There is code in there that sets a timeout of 15 seconds for the async task/sql query, but that's just for the rest of the logic, I don't think it limits the task itself. None of the queries of the plugin are intended to be long running, but I did come across a scenario where the image ID was used as a pattern instead of an exact value, causing results in the 30k+.

This happened when an (old) image had a 2 digit ID (like 22) which matched other image IDs (like 122, 224, 32212, etc.). But the query still executed reasonably quickly, without reaching the timeout. I would assume SQLite or even Lightroom itself would have their own built-in timeouts maybe? Is there a way to implement this as part of the query/asynctask?

--

Overall, your feedback does make me feel more relieved about all this locking considerations, probably enough to warrant removing all the warnings about possible data corruption from the documentation. 

Thanks.

[johnrellis]

"But the query still executed reasonably quickly, without reaching the timeout. I would assume SQLite or even Lightroom itself would have their own built-in timeouts maybe? Is there a way to implement this as part of the query/asynctask?"

LR does have timeouts that I think are fairly long (e.g. 30 seconds) for its database accesses.  All my plugins specify a timeout of 30 seconds when doing catalog:withWriteAccessDo(), and I've never had a problem with it -- timeouts occur only when the plugin is buggy or another plugin is misbehaving.

Your queries should all execute well under 1 second.

With the "sqlite3" program, you can specify a timeout with the ".timeout" command:

sqlite> .help
...
.timeout MS              Try opening locked tables for MS milliseconds

[johnrellis]

After your discovery that LR no longer puts an operating-system lock on the .lrcat file, I've been enhancing my Any Filter plugin to provide access to more information not accessible via the SDK. And I made the following important discovery:

Short version: Make sure that any plugin use of "sqlite3" opens the catalog database with "-readonly" or ".filectrl persist_wal true". Otherwise, the plugin won't see any catalog updates after the first time it is run in a session, and if LR terminates abnormally, all catalog changes made in that session will be lost.

Long version

If you run "sqlite3" to query the catalog while LR is running, when it exits, "sqlite3" will delete the transaction-log files (the -wal and -shm files) from the directory containing the .lrcat files. LR still has open file handles on them and will continue to operate normally, except in one circumstance: If LR exits abnormally (crashes or is force-quit), and then restarts, it won't find the previous -wal and -shm files (because they were deleted by "sqlite3"), and all changes (transactions) made in the previous session that were recording in the -wal file will be lost.

Normally, SQLite allows robust concurrent access by multiple client apps.  I think LR must be doing something non-standard/non-default in the way it opens the catalog database that's fooling "sqlite3" into thinking that when it exits, it has the last open connection on the database, so it truncates the transaction log and deletes it.

It's easy to demonstrate this bad behavior on Mac (haven't tested the details on Windows):

1. Start LR.  Observe the presence of -wal/shm files in the catalog folder.

2. Run "sqlite3" and select the history steps for an image.  Observe they are all returned. Also observe that the -wal/shm files are now gone.

2b. (Optional) Run "iosnoop" and observe that LR still writes to the -wal/shm files as you make changes in LR.

3. Change a Develop setting for the image.

4. Run "sqlite3" again and observe that it doesn't return the new history step.

5. Force-quit (kill) LR and restart it.

6. Observe that the change to the image made in step 3 has been lost.

The workaround is easy: Always have "sqlite3" open the catalog database in readonly mode with "-readonly".  Alternatively, use the command ".filectrl persist_wal true" before exiting "sqlite3", which tells it to not delete the -wal/shm transaction log on exit if if it thinks it's the last open connection.