Re: sqlite3 usage for multisession variable storage

[Robin Tarsiger]

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