Subject: bug#59346: Adding sqlite-backup

From: Stefan Kangas <[email-masked]>
Date: Sat, 19 Nov 2022 00:07:33 -0800

Eli Zaretskii <[email-masked]> writes:

> A database is not like a normal file, and so the fact that Emacs has
> backups doesn't seem to be a reason good enough to extend the backups
> to DB operations.  We don't bother with this when we send email or do
> other operations.  We also have auto-save and file-locks for normal
> file, but not for DBs.  And a DB is not a file, it is a (large)
> collection of tables and records, and Emacs deals with at most a
> single table at a time, AFAIU.

IME, one key difference between sqlite and a more fully-featured RDBMS
like MySQL or PostgreSQL is that it is much easier to induce a
corruption.  It is true that, as long as we don't have bugs in Emacs or
sqlite, we should rarely see any issues.  (If anyone does run into any
bugs, they will thank us for providing a backup though.)

But since this is Emacs, and users are used to be able to just copy and
move files around inside of ~/.emacs.d, it is not unlikely that they
will treat the sqlite database file in the same way.  Especially if they
are not familiar with how sqlite works.  As we know, copying the file
around as its being written is one way of corrupting the database.[1]
This is just one example of how a corruption could happen.

Now, we could call this a "pilot error" and be done with it, or we could
try to provide some safety net.  I think the latter is preferable.

> More generally, DB administration is outside of the Emacs scope, and
> how to do that properly is outside our expertise.  There's more to it
> than just backing up the DB.

AFAIU, the concern here is data safety, for which backups should be good
enough.  But I don't know what other types of DB administration tasks
you have in mind.

> you can backup the DB by using the sqlite3 executable.  I think it is
> reasonable to expect users who want to backup their DB to have this
> executable and use it for that purpose

If it can be avoided, it would be better to not put that burden on
individual users.  For the same reasons we provide autosave files,
backup files, etc.

Footnotes:
[1]  https://www.sqlite.org/howtocorrupt.html