bug#70145: [PATCH] Add sqlite-execute-batch command

[Javier Olaechea <pirata@gmail.com>]

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

Ok, updated the patch to include the test

On Tue, Apr 2, 2024 at 1:56 PM Eli Zaretskii <eliz@gnu.org> wrote:

> > From: Javier Olaechea <pirata@gmail.com>
> > Date: Tue, 2 Apr 2024 13:52:12 -0500
> > Cc: 70145@debbugs.gnu.org
> >
> > Thanks, I've updated the patch according to the feedback.
>
> Thanks, will review soon.
>
> > Btw I have a basic test for the functionality which I used to validate
> the code before submitting the patch.
> > Should I include it in test/sqlite-tests.el?
>
> Yes, more tests are always welcome.
>


-- 
"I object to doing things that computers can do." — Olin Shivers

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

[-- Attachment #2: 0001-Add-sqlite-execute-batch-command.patch --]
[-- Type: text/x-patch, Size: 3630 bytes --]

From b0cbe05c7da8abb49b45a799c1b066e788cf1c6d Mon Sep 17 00:00:00 2001
From: Javier Olaechea <pirata@gmail.com>
Date: Sun, 31 Mar 2024 23:07:10 -0500
Subject: [PATCH] Add sqlite-execute-batch command

This command is similar to sqlite-execute except that it executes
multiple statements in exchange for not accepting any arguments.

* doc/lispref/text.texi (Database): Document it.
* src/sqlite.c (Fsqlite_execute_batch): Add sqlite_execute_batch
command.  It is similar to sqlite-execute but it executes all the
statements in the query.  Unlike sqlite-execute the command doesn't take
any arguments to pass down to the statements.
* test/lisp/sqlite-tests.el (with-sqlite-execute-batch-test): Test it.
---
 doc/lispref/text.texi     |  8 ++++++++
 src/sqlite.c              | 12 ++++++++++++
 test/lisp/sqlite-tests.el | 25 +++++++++++++++++++++++++
 3 files changed, 45 insertions(+)

diff --git a/doc/lispref/text.texi b/doc/lispref/text.texi
index 90e2c6ce882..f1f8f813981 100644
--- a/doc/lispref/text.texi
+++ b/doc/lispref/text.texi
@@ -5404,6 +5404,14 @@ Database
 
 @end defun
 
+@defun sqlite-execute-batch db statements
+Execute the @acronym{SQL} @var{statements}.  @var{statements} is a
+string containing 0 or more @acronym{SQL} statements.  This command
+might be useful when we want to execute multiple @acronym{DDL}
+statements.
+
+@end defun
+
 @defun sqlite-select db query &optional values return-type
 Select some data from @var{db} and return them.  For instance:
 
diff --git a/src/sqlite.c b/src/sqlite.c
index 261080da673..c606fa5f831 100644
--- a/src/sqlite.c
+++ b/src/sqlite.c
@@ -646,6 +646,17 @@ sqlite_exec (sqlite3 *sdb, const char *query)
   return Qt;
 }
 
+DEFUN ("sqlite-execute-batch", Fsqlite_execute_batch, Ssqlite_execute_batch, 2, 2, 0,
+       doc: /* Execute multiple SQL statements in DB.
+Query is a string containing 0 or more SQL statements.  */)
+  (Lisp_Object db, Lisp_Object query)
+{
+  check_sqlite (db, false);
+  CHECK_STRING (query);
+  Lisp_Object encoded = encode_string(query);
+  return sqlite_exec (XSQLITE (db)->db, SSDATA (encoded));
+}
+
 DEFUN ("sqlite-transaction", Fsqlite_transaction, Ssqlite_transaction, 1, 1, 0,
        doc: /* Start a transaction in DB.  */)
   (Lisp_Object db)
@@ -866,6 +877,7 @@ syms_of_sqlite (void)
   defsubr (&Ssqlite_close);
   defsubr (&Ssqlite_execute);
   defsubr (&Ssqlite_select);
+  defsubr (&Ssqlite_execute_batch);
   defsubr (&Ssqlite_transaction);
   defsubr (&Ssqlite_commit);
   defsubr (&Ssqlite_rollback);
diff --git a/test/lisp/sqlite-tests.el b/test/lisp/sqlite-tests.el
index d4892a27efc..7053026eb82 100644
--- a/test/lisp/sqlite-tests.el
+++ b/test/lisp/sqlite-tests.el
@@ -48,4 +48,29 @@ with-sqlite-transaction/rollback
     ;; First insertion (a=1) rolled back.
     (should-not (sqlite-select db "select * from test"))))
 
+(ert-deftest with-sqlite-execute-batch-test ()
+  (let ((db (sqlite-open nil))
+        (query (with-temp-buffer
+                 (insert "-- -*- sql-product: sqlite -*-
+
+-- I 💘 emojis
+
+CREATE TABLE settings (
+  name TEXT NOT NULL,
+  value TEXT,
+  section TEXT NOT NULL,
+  PRIMARY KEY (section, name)
+);
+
+CREATE TABLE tags📎 (
+  name TEXT PRIMARY KEY NOT NULL
+);
+
+-- CREATE TABLE todo_states (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
+")
+                 (buffer-string))))
+    (sqlite-execute-batch db query)
+    (should (equal '(("settings") ("tags📎"))
+                   (sqlite-select db "select name from sqlite_master where type = 'table' and name not like 'sqlite_%' order by name")))))
+
 ;;; sqlite-tests.el ends here
-- 
2.29.2.154.g7f7ebe054a