"...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.
AdChoices