Re: sqlite3 usage for multisession variable storage

[Robin Tarsiger <rtt@dasyatidae.com>]

Lars Ingebrigtsen wrote:
> Óscar Fuentes <ofv@wanadoo.es> writes:
> 
>> Forgot to mention that modern databases usually offer several strategies
>> for persisting data. A quick look at SQLite documentation shows that it
>> can be configured to use a write ahead log, which is faster than the
>> default rollback journal:
>>
>> https://sqlite.org/wal.html
> 
> Yes, I used the WAL in my tests.  (The implementation is still on the
> scratch/sqlite branch, for those who are curious.)
> 

I'm curious about some of these pragma settings more generally.

   - Why journal_mode WAL rather than TRUNCATE or MEMORY? WAL makes the file
     less easily moveable and introduces the shm requirement, which feels weird
     in an Emacs context. Since Emacs itself often works along the lines of "write
     new file and replace" or "copy to backup and then overwrite", DELETE is
     natural, but TRUNCATE feels slightly better for managed data stores. MEMORY
     would expose the DB to corruption on application crashes, but if you're
     outside a transaction almost all the time, it might be fine... and I don't
     see the files backend currently using backup files (or even locking) anyway,
     yes? (Or is that intended to be added later?)

   - Doesn't that say "bsynchronous" with an extra b? (Unrecognized pragmas are
     normally no-ops.) Also, if you're using synchronous NORMAL rather than OFF
     for SQLite, then presumably make sure write-region-inhibit-fsync is nil while
     comparing...

   - Having auto_vacuum be FULL similarly seems like a waste, adding a bunch of
     extra work to each transaction commit.

   - Given that people have expressed concerns over in-place modification leaving
     unwanted traces of old data, setting secure_delete to ON may be useful here.

   - Setting trusted_schema to OFF is recommended for new applications.

   - You might consider using application_id to make the file identifiable as a
     specific format rather than probing for the existence of the main table.

   - Doesn't the current code in multisession.el not execute the pragmas on subsequent
     opens? Only some of these are persistent.

(As a reminder for anyone following, the documentation for SQLite pragmas is
available at <https://www.sqlite.org/pragma.html>.)

-RTT