bug#59346: Adding sqlite-backup

bug#59346: Adding sqlite-backup

[Andrew Hyatt]

[-- Attachment #1.1: Type: text/plain, Size: 387 bytes --]

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.

[-- Attachment #1.2: Type: text/html, Size: 463 bytes --]

[-- Attachment #2: 0001-Add-sqlite-backup-a-function-to-backup-sqlite-databa.patch --]
[-- Type: application/octet-stream, Size: 4324 bytes --]

From 2d8289e34ee770a08c4f26969aa5fb7a386898d9 Mon Sep 17 00:00:00 2001
From: Andrew Hyatt <ahyatt@gmail.com>
Date: Thu, 17 Nov 2022 23:31:53 -0300
Subject: [PATCH] Add `sqlite-backup', a function to backup sqlite database
 files

    * src/sqlite.c: create new defun sqlite-backup, and add the
    necessary function imports from sqlite.
---
 src/sqlite.c | 57 ++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 57 insertions(+)

diff --git a/src/sqlite.c b/src/sqlite.c
index 08bf696b8c..7714e0529a 100644
--- a/src/sqlite.c
+++ b/src/sqlite.c
@@ -41,6 +41,9 @@ DEF_DLL_FN (SQLITE_API int, sqlite3_close, (sqlite3*));
 DEF_DLL_FN (SQLITE_API int, sqlite3_open_v2,
 	    (const char*, sqlite3**, int, const char*));
 DEF_DLL_FN (SQLITE_API int, sqlite3_reset, (sqlite3_stmt*));
+DEF_DLL_FN (SQLITE_API int, sqlite3_backup_init, (sqlite3*, const char*, sqlite3*, const char*));
+DEF_DLL_FN (SQLITE_API int, sqlite3_backup_step,(sqlite3_backup *p, int nPage));
+DEF_DLL_FN (SQLITE_API int, sqlite3_backup_finish,(sqlite3_backup *p));
 DEF_DLL_FN (SQLITE_API int, sqlite3_bind_text,
 	    (sqlite3_stmt*, int, const char*, int, void(*)(void*)));
 DEF_DLL_FN (SQLITE_API int, sqlite3_bind_blob,
@@ -83,6 +86,9 @@ DEF_DLL_FN (SQLITE_API int, sqlite3_load_extension,
 # undef sqlite3_close
 # undef sqlite3_open_v2
 # undef sqlite3_reset
+# undef sqlite_backup_init
+# undef sqlite_backup_step
+# undef sqlite_backup_finish
 # undef sqlite3_bind_text
 # undef sqlite3_bind_blob
 # undef sqlite3_bind_int64
@@ -109,6 +115,9 @@ DEF_DLL_FN (SQLITE_API int, sqlite3_load_extension,
 # define sqlite3_close fn_sqlite3_close
 # define sqlite3_open_v2 fn_sqlite3_open_v2
 # define sqlite3_reset fn_sqlite3_reset
+# define sqlite3_backup_init fn_sqlite3_backup_init
+# define sqlite3_backup_step fn_sqlite3_backup_step
+# define sqlite3_backup_finish fn_sqlite3_backup_finish
 # define sqlite3_bind_text fn_sqlite3_bind_text
 # define sqlite3_bind_blob fn_sqlite3_bind_blob
 # define sqlite3_bind_int64 fn_sqlite3_bind_int64
@@ -138,6 +147,9 @@ load_dll_functions (HMODULE library)
   LOAD_DLL_FN (library, sqlite3_close);
   LOAD_DLL_FN (library, sqlite3_open_v2);
   LOAD_DLL_FN (library, sqlite3_reset);
+  LOAD_DLL_FN (library, sqlite3_backup_init);
+  LOAD_DLL_FN (library, sqlite3_backup_step);
+  LOAD_DLL_FN (library, sqlite3_backup_finish);
   LOAD_DLL_FN (library, sqlite3_bind_text);
   LOAD_DLL_FN (library, sqlite3_bind_blob);
   LOAD_DLL_FN (library, sqlite3_bind_int64);
@@ -643,6 +655,50 @@ DEFUN ("sqlite-pragma", Fsqlite_pragma, Ssqlite_pragma, 2, 2, 0,
 		      SSDATA (concat2 (build_string ("PRAGMA "), pragma)));
 }
 
+
+DEFUN ("sqlite-backup", Fsqlite_backup, Ssqlite_backup, 2, 2, 0,
+       doc: /* Backup the sqlite database DB to FILENAME.
+This does a safe database backup that will either succeed or signal an
+error.
+
+FILENAME does not have to exist, but if it does exist, it will be
+overwritten.*/)
+  (Lisp_Object db, Lisp_Object filename)
+{
+  sqlite3 *dest_db;
+  int rc;
+  Lisp_Object backup_name;
+
+  if (!NILP (filename))
+    backup_name = ENCODE_FILE (Fexpand_file_name (filename, Qnil));
+  else
+    xsignal1 (Qsqlite_error, build_string ("sqlite-backup must be called with a non-nil filename."));
+  check_sqlite (db, false);
+  rc = sqlite3_open_v2(SSDATA (backup_name), &dest_db,
+		       (SQLITE_OPEN_CREATE  | SQLITE_OPEN_READWRITE), NULL);
+
+  if (rc == SQLITE_OK ) {
+    sqlite3_backup *bk;
+    bk = sqlite3_backup_init(dest_db, "main", XSQLITE (db)->db, "main");
+    if(bk)
+      {
+	rc = sqlite3_backup_step(bk, -1);
+	if (rc == SQLITE_DONE) {
+	  sqlite3_backup_finish(bk);
+	}
+      }
+    sqlite3_close (dest_db);
+  }
+
+  if (rc == SQLITE_DONE) {
+    return Qnil;
+  } else {
+    xsignal1(rc == SQLITE_LOCKED || rc == SQLITE_BUSY?
+	     Qsqlite_locked_error: Qsqlite_error,
+	     sqlite_prepare_errdata(rc, XSQLITE (db)->db));
+  }
+}
+
 #ifdef HAVE_SQLITE3_LOAD_EXTENSION
 DEFUN ("sqlite-load-extension", Fsqlite_load_extension,
        Ssqlite_load_extension, 2, 2, 0,
@@ -785,6 +841,7 @@ syms_of_sqlite (void)
   defsubr (&Ssqlite_commit);
   defsubr (&Ssqlite_rollback);
   defsubr (&Ssqlite_pragma);
+  defsubr (&Ssqlite_backup);
 #ifdef HAVE_SQLITE3_LOAD_EXTENSION
   defsubr (&Ssqlite_load_extension);
 #endif
-- 
2.37.1 (Apple Git-137.1)

bug#59346: Adding sqlite-backup

[Eli Zaretskii]

> 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?

bug#59346: Adding sqlite-backup

[Andrew Hyatt]

[-- Attachment #1: Type: text/plain, Size: 2039 bytes --]

On Fri, Nov 18, 2022 at 4:46 AM Eli Zaretskii <eliz@gnu.org> wrote:

> > 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?
>

Good questions. Let me respond to your two questions: why does emacs need
to be involved in backups, and why can't it control backups some other way?

Emacs already has a file backup facility, but if emacs is primarily working
with data in sqlite, there is no mechanism for backups, which seems scary
to me.  I'd like modules that rely on sqlite for data to be able to back up
their data, because the user would like to have some security, knowing if
something goes wrong they can always restore a recent backup.

But why can't we do this by invoking backup via the binary?  Certainly a
possibility, and in fact that's how I would implement it if the sqlite
library was instead emacsql-sqlite.  However, I have no idea how to locate
the binary; that isn't part of the current built-in sqlite implementation.
For good reason, too, it's a built-in implementation, including a variable
holding the executable path seems odd.  I could add one, but this solution
seems better to me, since it doubles down on the built-in sqlite, and
doesn't provide an alternative mechanism for doing things with sqlite.

Hope this clarifies things, let me know if you have any other questions.

[-- Attachment #2: Type: text/html, Size: 2585 bytes --]

bug#59346: Adding sqlite-backup

[Eli Zaretskii]

> From: Andrew Hyatt <ahyatt@gmail.com>
> Date: Fri, 18 Nov 2022 09:05:58 -0300
> Cc: 59346@debbugs.gnu.org
> 
>  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?
> 
> Good questions. Let me respond to your two questions: why does emacs need to be involved in backups,
> and why can't it control backups some other way?
> 
> Emacs already has a file backup facility, but if emacs is primarily working with data in sqlite, there is no
> mechanism for backups, which seems scary to me.  I'd like modules that rely on sqlite for data to be able to
> back up their data, because the user would like to have some security, knowing if something goes wrong
> they can always restore a recent backup.

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.

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.

So I don't think we should extend the Emacs sqlite3 support in this
direction.

> But why can't we do this by invoking backup via the binary?  Certainly a possibility, and in fact that's how I
> would implement it if the sqlite library was instead emacsql-sqlite.  However, I have no idea how to locate the
> binary; that isn't part of the current built-in sqlite implementation.  For good reason, too, it's a built-in
> implementation, including a variable holding the executable path seems odd.  I could add one, but this
> solution seems better to me, since it doubles down on the built-in sqlite, and doesn't provide an alternative
> mechanism for doing things with sqlite.

This SO article:

  https://stackoverflow.com/questions/25675314/how-to-backup-sqlite-database

(which is the first hit I get if I "how to backup sqlite database"
into the browser search box) says that 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 (and other purposes, as they need).  I do have this executable
here, FWIW.

So I think these needs should be fulfilled "by other means", not by
Emacs Lisp programs.

bug#59346: Adding sqlite-backup

[Andrew Hyatt]

[-- Attachment #1: Type: text/plain, Size: 4112 bytes --]

On Fri, Nov 18, 2022 at 9:17 AM Eli Zaretskii <eliz@gnu.org> wrote:

> > From: Andrew Hyatt <ahyatt@gmail.com>
> > Date: Fri, 18 Nov 2022 09:05:58 -0300
> > Cc: 59346@debbugs.gnu.org
> >
> >  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?
> >
> > Good questions. Let me respond to your two questions: why does emacs
> need to be involved in backups,
> > and why can't it control backups some other way?
> >
> > Emacs already has a file backup facility, but if emacs is primarily
> working with data in sqlite, there is no
> > mechanism for backups, which seems scary to me.  I'd like modules that
> rely on sqlite for data to be able to
> > back up their data, because the user would like to have some security,
> knowing if something goes wrong
> > they can always restore a recent backup.
>
> 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.


> 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.
>
> So I don't think we should extend the Emacs sqlite3 support in this
> direction.
>

I disagree, but I understand this is just a difference of opinion, so I'm
happy to let this patch sit here until there's more demand for this sort of
thing.

But if you are interested in furthering the conversation, which has a lot
to do with what I'm talking about in my upcoming (and already recorded)
Emacs conference talk, I don't think the distinction you are making between
files and databases is important.  Let's say I wanted to store, use and
otherwise manipulate contact information with emacs.  Right now that sort
of thing happens with a file with emacs-readable lists inside, but it's
really more natural to do it with a database.  The fact that it's done via
a file now seems historical, especially now that we have sqlite.  I expect
that sqlite will be used for this sort of thing more often.  However, if
there's no database option, then maybe not, since it won't be seen as
reliable as the filesystem.  I don't think it is reasonable to expect users
to figure out how to do this, even if the info is easy to find.  I'd like
us to be able to encourage the use of sqlite for data, and give the user a
good experience when sqlite is the backend.


>
> > But why can't we do this by invoking backup via the binary?  Certainly a
> possibility, and in fact that's how I
> > would implement it if the sqlite library was instead emacsql-sqlite.
> However, I have no idea how to locate the
> > binary; that isn't part of the current built-in sqlite implementation.
> For good reason, too, it's a built-in
> > implementation, including a variable holding the executable path seems
> odd.  I could add one, but this
> > solution seems better to me, since it doubles down on the built-in
> sqlite, and doesn't provide an alternative
> > mechanism for doing things with sqlite.
>
> This SO article:
>
>
> https://stackoverflow.com/questions/25675314/how-to-backup-sqlite-database
>
> (which is the first hit I get if I "how to backup sqlite database"
> into the browser search box) says that 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 (and other purposes, as they need).  I do have this executable
> here, FWIW.
>
> So I think these needs should be fulfilled "by other means", not by
> Emacs Lisp programs.
>

[-- Attachment #2: Type: text/html, Size: 5293 bytes --]

bug#59346: Adding sqlite-backup

[Jean Louis]

* Andrew Hyatt <ahyatt@gmail.com> [2022-11-18 05:42]:
> 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.

How I see those functions, that shall be included in Emacs as they are
standard part of SQLite.

SQLite Backup API:
https://www.sqlite.org/backup.html


-- 
Jean

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

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

bug#59346: Adding sqlite-backup

[Jean Louis]

* 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/

bug#59346: Adding sqlite-backup

[Jean Louis]

* Eli Zaretskii <eliz@gnu.org> [2022-11-18 15:18]:
> > From: Andrew Hyatt <ahyatt@gmail.com>
> > Date: Fri, 18 Nov 2022 09:05:58 -0300
> > Cc: 59346@debbugs.gnu.org
> > 
> >  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?
> > 
> > Good questions. Let me respond to your two questions: why does emacs need to be involved in backups,
> > and why can't it control backups some other way?
> > 
> > Emacs already has a file backup facility, but if emacs is primarily working with data in sqlite, there is no
> > mechanism for backups, which seems scary to me.  I'd like modules that rely on sqlite for data to be able to
> > back up their data, because the user would like to have some security, knowing if something goes wrong
> > they can always restore a recent backup.
> 
> 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.

In this case SQLite database is normal file. It is not text. But it is
definitely a file.

It requires usage of internal functions:

SQLite Backup API:
https://www.sqlite.org/backup.html

> 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.

You mean it is not text file.

> 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.

When there are sqlite functions included then so much of database
administration automatically becomes available to Emacs. Statement is
contradictory to the inclusion of SQLite.

> This SO article:
> 
>   https://stackoverflow.com/questions/25675314/how-to-backup-sqlite-database
> 
> (which is the first hit I get if I "how to backup sqlite database"
> into the browser search box) says that 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 (and other purposes, as they need).  I do have this executable
> here, FWIW.
> 
> So I think these needs should be fulfilled "by other means", not by
> Emacs Lisp programs.

That patch uses SQLite internals, without those, Emacs Lisp can't
properly backup the database. Without that patch, Emacs which does
support now SQLite would ask users to default to using external
program, like:

`sqlite3 currentDB.sqlite ".backup backuDB.sqlite"'

but how sure are you that you will have that external program
`sqlite3' in existence? 

When SQLite is already included in Emacs, it should not expect then
that external SQLite related software is available on the system.

It is logical that Emacs with SQLite included should have proper way
of doing backup without relying on external tools.

--
Jean

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

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

bug#59346: Adding sqlite-backup

[Jean Louis]

* Andrew Hyatt <ahyatt@gmail.com> [2022-11-19 05:07]:
> But if you are interested in furthering the conversation, which has a lot
> to do with what I'm talking about in my upcoming (and already recorded)
> Emacs conference talk, I don't think the distinction you are making between
> files and databases is important.  Let's say I wanted to store, use and
> otherwise manipulate contact information with emacs.  Right now that sort
> of thing happens with a file with emacs-readable lists inside, but it's
> really more natural to do it with a database.  The fact that it's done via
> a file now seems historical, especially now that we have sqlite.  I expect
> that sqlite will be used for this sort of thing more often.  However, if
> there's no database option, then maybe not, since it won't be seen as
> reliable as the filesystem.  I don't think it is reasonable to expect users
> to figure out how to do this, even if the info is easy to find.  I'd like
> us to be able to encourage the use of sqlite for data, and give the user a
> good experience when sqlite is the backend.

I totally agree on that.

I have already made SQLite version of CRM for Emacs by using SQLite,
but it is not polished for public, it will be soon.

Backing up database which does not run on the file system is
trivial. That often needs human interaction.

Though there are use cases when programs run continuously without
human interaction and where there are too many changes to the database
that one cannot just control by human.

Thus program itself should be able to reliably backup the database
while it is running without human supervision.

There are programs like sending of mailing list, sending thousands of
SMS messages, and such programs may run autonomously over days. That
is how my programs are designed. They may accept the sales lead and
re-check if any new person has subscribed, then dispatch e-mail and
SMS to that person.

Having reliable backup functions thus is necessary and useful in those
cases of running programs.

--
Jean

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

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

bug#59346: Adding sqlite-backup

[Eli Zaretskii]

> Date: Sat, 19 Nov 2022 08:17:21 +0300
> From: Jean Louis <bugs@gnu.support>
> Cc: Andrew Hyatt <ahyatt@gmail.com>, 59346@debbugs.gnu.org
> 
> When SQLite is already included in Emacs, it should not expect then
> that external SQLite related software is available on the system.

I disagree.  We included sqlite support in Emacs to let Lisp programs
gain access to databases, but we didn't intend to make Emacs a
platform for developing full-fledged sqlite applications.  And I see
nothing wrong with using external applications for DB administration,
especially since the tools to do that come with the same sqlite
installation as the library.

> It is logical that Emacs with SQLite included should have proper way
> of doing backup without relying on external tools.

You take that logic too far beyond our intent.

bug#59346: Adding sqlite-backup

[Stefan Kangas]

Eli Zaretskii <eliz@gnu.org> 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

bug#59346: Adding sqlite-backup

[Andrew Hyatt]

[-- Attachment #1: Type: text/plain, Size: 1474 bytes --]

On Sat, Nov 19, 2022 at 4:52 AM Eli Zaretskii <eliz@gnu.org> wrote:

> > Date: Sat, 19 Nov 2022 08:17:21 +0300
> > From: Jean Louis <bugs@gnu.support>
> > Cc: Andrew Hyatt <ahyatt@gmail.com>, 59346@debbugs.gnu.org
> >
> > When SQLite is already included in Emacs, it should not expect then
> > that external SQLite related software is available on the system.
>
> I disagree.  We included sqlite support in Emacs to let Lisp programs
> gain access to databases, but we didn't intend to make Emacs a
> platform for developing full-fledged sqlite applications.


I now see where you are coming from.  But I never knew your intentions and
have been working on SQLite-based applications. It seems I’m not the only
one. I think this represents a natural evolution where users are pushing
the system beyond what is intended. That’s not a bad thing, and I think
emacs should follow the desire paths carved out be users here.

It’s fine to wait until the trends are clearer, but I think this is a very
natural direction for module development, so its just a matter of time.

  And I see
> nothing wrong with using external applications for DB administration,
> especially since the tools to do that come with the same sqlite
> installation as the library.


>
> > It is logical that Emacs with SQLite included should have proper way
> > of doing backup without relying on external tools.
>
> You take that logic too far beyond our intent.
>

[-- Attachment #2: Type: text/html, Size: 2417 bytes --]

bug#59346: Adding sqlite-backup

[Eli Zaretskii]

> From: Andrew Hyatt <ahyatt@gmail.com>
> Date: Sat, 19 Nov 2022 10:36:39 -0300
> Cc: 59346@debbugs.gnu.org, Jean Louis <bugs@gnu.support>
> 
>  I disagree.  We included sqlite support in Emacs to let Lisp programs
>  gain access to databases, but we didn't intend to make Emacs a
>  platform for developing full-fledged sqlite applications.
> 
> I now see where you are coming from.  But I never knew your intentions and have been working on
> SQLite-based applications.

This is perfectly fine.  Emacs is Free Software: you can always patch
it to have the additional primitives you need, and develop any
applications you want based on that.  The issue being discussed here
is what should we provide in Emacs by default for all our users.
There's no intent to tell you what to do in your own projects.

> It seems I’m not the only one. I think this represents a natural evolution where
> users are pushing the system beyond what is intended. That’s not a bad thing, and I think emacs should
> follow the desire paths carved out be users here. 
> 
> It’s fine to wait until the trends are clearer, but I think this is a very natural direction for module development,
> so its just a matter of time.  

I'd rather wait for now.