bug#59346: Adding sqlite-backup

[Andrew Hyatt <ahyatt@gmail.com>]

[-- 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)