Add DB Lock feature to the Client side

[Suika]

Sicne the Server has the feature to lock the DB for backups and other operations, via the API.
It would be nice to have the function in the client, too. To create backup or have other application interact with the DB while the client is running. Without closing it.

Automatic Backup would need the feature.
https://github.com/imtbl/hyve heavily relies on the DB not working, for it to process the data. Which can take hours.
BTFS snapshot of the db, etc.

[bbappserver]

Setting the lock mode of the database is normally part of the API request, if you need to perform a specific manual operation on the database (other than online backup which I agree should be a feature), you can just perform it as part of your own database queries.

A lock will be obtained automatically when a transaction is initiated

For example BEGIN EXCLUSIVE will obtain an exclusive lock.

If hyve needs a consistent database state it should either trigger the sqlite backup protocol and then operate on the result or it can open a (non exclusive so others can still read) transaction. Note that this will cause hydrus to freeze the moment a non async write needs to happen (e.g. changing tags, or possibly even the autosave).

It seems all we really want here is a UI for this, note however that hydrus does not need to initiate this, the external utility could
https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.backup

Considerations for bulk concurrent locking of an sqlite database
https://www.sqlite.org/wal.html#concurrency

The fastest way is probably to make hyve operate on a reflink (COW copy on COW filesystems) if your OS supports such a thing. The backup API needs to make a "true copy"

[Mengmoshu]

API support for this seems like a good idea, regardless of how achievable it is through other systems. I know I definitely would like the running client to tell me the DB was locked by the API. My understanding is also that the API as implemented is fairly reasonable to interface with using minimal libraries and a language at the scripting end of things like Python, enabling a broader swath of users to do things like build custom backup scripts or integrate high uptime Hydrus instances into their existing automated backup solution.

[Suika]

It's not whether you can do something via the DB or not. The DB is an underlying layer that is used and is expected to be functional for the software to be able to perform whatever it needs to. The "DB Lock" is not a literall DB Lock but a lock in the softare, to stop any kind of activity with the DB and get the DB into a consistent state.

The "DB Lock" is not limited to creatin backups of the DB, but for example to work on the downloaded data. Move it somewhere else, do something that is might mess with the software in that short time that the data is not available. Or maybe there are workload that are so hard hitting, that hydrus working at that time, would slow the whole system further down.

DB on a CoW system is bad, unless you exclude the that folder/file from being CoW. But that is not the point of this whole thing. It's to replecate what the server already does, onto the client.

Also, triggering somethong on the DB level, while the client is still working, would cause the DB to stay or be caught in a state of work or a incomplete process.
Triggering the software lock, can allow the system to perform the last actions it needs to do to be in a consistent state.

[Suika]

Added with minimal support as of 74b9304