bug#59346: Adding sqlite-backup

[Jean Louis <bugs@gnu.support>]

* Eli Zaretskii <eliz@gnu.org> [2022-11-18 10:48]:
> > From: Andrew Hyatt <ahyatt@gmail.com>
> > Date: Thu, 17 Nov 2022 23:40:11 -0300
> > 
> > Currently there's no great way to perform a proper backup of a sqlite database if you use the built-in sqlite in
> > emacs 29.  If you just copy the file, there's a chance another database user is in the middle of something,
> > and the database could be corrupted.
> > 
> > I've included a patch that fixes this. I would the sqlite-backup function to exist so I can use it in the ELPA
> > triples package.
> 
> Thanks, but I'm not sure Emacs should support DB administration
> functions of sqlite.  Aren't there utilities out there which can be
> used for this?  Why should we have this built-in in Emacs? why not
> simply use shell-command or somesuch to invoke the necessary external
> program?

sqlite functions in Emacs ARE database administration. Not only here
mentioned backup functions. I can read in the patch that it uses
proper way of backup for the SQLite.

And backup function is part of SQLite internals.

https://www.sqlite.org/backup.html

Using the SQLite Online Backup API

Historically, backups (copies) of SQLite databases have been created using the following method:

    Establish a shared lock on the database file using the SQLite API (i.e. the shell tool).
    Copy the database file using an external tool (for example the unix 'cp' utility or the DOS 'copy' command).
    Relinquish the shared lock on the database file obtained in step 1. 

This procedure works well in many scenarios and is usually very fast. However, this technique has the following shortcomings:

    Any database clients wishing to write to the database file while a backup is being created must wait until the shared lock is relinquished.
    It cannot be used to copy data to or from in-memory databases.
    If a power failure or operating system failure occurs while copying the database file the backup database may be corrupted following system recovery. 

The Online Backup API was created to address these concerns. The
online backup API allows the contents of one database to be copied
into another database file, replacing any original contents of the
target database. The copy operation may be done incrementally, in
which case the source database does not need to be locked for the
duration of the copy, only for the brief periods of time when it is
actually being read from. This allows other database users to continue
without excessive delays while a backup of an online database is made.

https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupinit

--
Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/