Subject: 30.0.50; Support keyword-based substitutions in sqlite

From: "J.P." <[email-masked]>
Date: Thu, 22 Feb 2024 23:43:41 -0800

Severity: wishlist
Tags: patch

SQLite integrations on other platforms tend to recognize keyword-like
replacement specifiers in SQL statements and queries. For example, a
statement like

  INSERT INTO Messages(target, time, sender, text)
  SELECT t.id, :time, :sender, :text From Targets t
  JOIN Networks n ON t.network = n.id
  WHERE n.name = :network and t.name = :sender

would correspond to keyword parameters like

  (:text "Hi"
   :sender "Bob"
   :network "MyNet"
   :time "2012-06-30T23:59:60.419Z")

In Emacs, we currently use positional substitutions only, like

  INSERT INTO Messages(target, time, sender, text)
  SELECT t.id,?, ?, ? From Targets t
  JOIN Networks n ON t.network = n.id
  WHERE n.name = ? and t.name = ?

which are somewhat easy to flub, IMO. Keywords also have the added
benefit of requiring only a single evaluation for repeated
substitutions.

I've made a simplistic attempt at implementing this, but as a perennial
SQL novice (and a certified Emacs dummy), I'd prefer someone more in the
know to adopt this or at least review it carefully.

Thanks.

In GNU Emacs 30.0.50 (build 1, x86_64-pc-linux-gnu, GTK+ Version
 3.24.41, cairo version 1.18.0) of 2024-02-22 built on localhost
Repository revision: b868690feff44c7242c942490d1d8bc6d2811fa2
Repository branch: master
Windowing system distributor 'The X.Org Foundation', version 11.0.12014000
System Description: Fedora Linux 39 (Workstation Edition)

Configured using:
 'configure --enable-check-lisp-object-type --enable-checking=yes,glyphs
 'CFLAGS=-O0 -g3'
 PKG_CONFIG_PATH=:/usr/lib64/pkgconfig:/usr/share/pkgconfig'

Configured features:
ACL CAIRO DBUS FREETYPE GIF GLIB GMP GNUTLS GPM GSETTINGS HARFBUZZ JPEG
JSON LCMS2 LIBOTF LIBSELINUX LIBSYSTEMD LIBXML2 M17N_FLT MODULES
NATIVE_COMP NOTIFY INOTIFY PDUMPER PNG RSVG SECCOMP SOUND SQLITE3
THREADS TIFF TOOLKIT_SCROLL_BARS WEBP X11 XDBE XIM XINPUT2 XPM GTK3 ZLIB

Important settings:
  value of $LANG: en_US.UTF-8
  value of $XMODIFIERS: @im=ibus
  locale-coding-system: utf-8-unix

Major mode: Lisp Interaction

Minor modes in effect:
  tooltip-mode: t
  global-eldoc-mode: t
  eldoc-mode: t
  show-paren-mode: t
  electric-indent-mode: t
  mouse-wheel-mode: t
  tool-bar-mode: t
  menu-bar-mode: t
  file-name-shadow-mode: t
  global-font-lock-mode: t
  font-lock-mode: t
  blink-cursor-mode: t
  minibuffer-regexp-mode: t
  line-number-mode: t
  indent-tabs-mode: t
  transient-mark-mode: t
  auto-composition-mode: t
  auto-encryption-mode: t
  auto-compression-mode: t

Load-path shadows:
None found.

Features:
(shadow sort mail-extr compile comint ansi-osc ansi-color ring comp-run
bytecomp byte-compile comp-common rx emacsbug message mailcap yank-media
puny dired dired-loaddefs rfc822 mml mml-sec password-cache epa derived
epg rfc6068 epg-config gnus-util text-property-search time-date subr-x
mm-decode mm-bodies mm-encode mail-parse rfc2231 mailabbrev gmm-utils
mailheader cl-loaddefs cl-lib sendmail rfc2047 rfc2045 ietf-drums
mm-util mail-prsvr mail-utils rmc iso-transl tooltip cconv eldoc paren
electric uniquify ediff-hook vc-hooks lisp-float-type elisp-mode mwheel
term/x-win x-win term/common-win x-dnd touch-screen tool-bar dnd fontset
image regexp-opt fringe tabulated-list replace newcomment text-mode
lisp-mode prog-mode register page tab-bar menu-bar rfn-eshadow isearch
easymenu timer select scroll-bar mouse jit-lock font-lock syntax
font-core term/tty-colors frame minibuffer nadvice seq simple cl-generic
indonesian philippine cham georgian utf-8-lang misc-lang vietnamese
tibetan thai tai-viet lao korean japanese eucjp-ms cp51932 hebrew greek
romanian slovak czech european ethiopic indian cyrillic chinese
composite emoji-zwj charscript charprop case-table epa-hook
jka-cmpr-hook help abbrev obarray oclosure cl-preloaded button loaddefs
theme-loaddefs faces cus-face macroexp files window text-properties
overlay sha1 md5 base64 format env code-pages mule custom widget keymap
hashtable-print-readable backquote threads dbusbind inotify lcms2
dynamic-setting system-font-setting font-render-setting cairo gtk
x-toolkit xinput2 x multi-tty move-toolbar make-network-process
native-compile emacs)

Memory information:
((conses 16 56024 9583) (symbols 48 6577 0) (strings 32 16325 3825)
 (string-bytes 1 430353) (vectors 16 11172)
 (vector-slots 8 131607 9630) (floats 8 21 13) (intervals 56 247 0)
 (buffers 976 11))
>From 036c363c7e2dee51e37c71bf6f5e436fee2f751a Mon Sep 17 00:00:00 2001
From: "F. Jason Park" <[email-masked]>
Date: Thu, 22 Feb 2024 19:28:24 -0800
Subject: [PATCH] Recognize keyword template specifiers in sqlite

* doc/lispref/text.texi (Database): Mention keyword-based
substitutions for SQLite statements and queries.
* src/sqlite.c: Add Windows boilerplate to support the dynamic loading
of library function `sqlite3_bind_parameter_index', which first
appeared in SQLite 3.0.7, released 2004.
(bind_values): On encountering a keyword symbol in the values list,
arrange in the subsequent iteration for overriding the second, "index"
parameter of the various `sqlite3_bind_*' functions with the keyword's
index position, as determined by `sqlite3_bind_parameter_index'.  Do
this in an alternating, pairwise fashion befitting plists.
* test/src/sqlite-tests.el (sqlite-keyword-params): New test.
---
 doc/lispref/text.texi    |  9 ++++++++-
 src/sqlite.c             | 29 ++++++++++++++++++++---------
 test/src/sqlite-tests.el | 31 +++++++++++++++++++++++++++++++
 3 files changed, 59 insertions(+), 10 deletions(-)

diff --git a/doc/lispref/text.texi b/doc/lispref/text.texi
index 3d14a5ad8be..c3b2226e082 100644
--- a/doc/lispref/text.texi
+++ b/doc/lispref/text.texi
@@ -5376,7 +5376,12 @@ Database
 
 This has exactly the same effect as the previous example, but is more
 efficient and safer (because it doesn't involve any string parsing or
-interpolation).
+interpolation).  In addition to positional substitutions, you can also
+use keyword-based replacements, for example:
+
+@lisp
+(sqlite-execute db "insert into foo values (:a, :b)" '(:a "bar" :b 2))
+@end lisp
 
 @code{sqlite-execute} usually returns the number of affected rows.
 For instance, an @samp{insert} statement will typically return
@@ -5418,6 +5423,8 @@ Database
 @lisp
 (sqlite-select db "select * from foo where key = ?" [2])
   @result{} (("bar" 2))
+(sqlite-select db "select * from foo where key = :a" [:a 2])
+  @result{} (("bar" 2))
 @end lisp
 
 This is usually more efficient and safer than the method used by the
diff --git a/src/sqlite.c b/src/sqlite.c
index 7a018b28aa4..123182d241b 100644
--- a/src/sqlite.c
+++ b/src/sqlite.c
@@ -63,6 +63,8 @@ DEF_DLL_FN (SQLITE_API int, sqlite3_bind_int64,
 DEF_DLL_FN (SQLITE_API int, sqlite3_bind_double, (sqlite3_stmt*, int, double));
 DEF_DLL_FN (SQLITE_API int, sqlite3_bind_null, (sqlite3_stmt*, int));
 DEF_DLL_FN (SQLITE_API int, sqlite3_bind_int, (sqlite3_stmt*, int, int));
+DEF_DLL_FN (SQLITE_API int, sqlite3_bind_parameter_index,
+	    (sqlite3_stmt*, const char*));
 DEF_DLL_FN (SQLITE_API int, sqlite3_extended_errcode, (sqlite3*));
 DEF_DLL_FN (SQLITE_API const char*, sqlite3_errmsg, (sqlite3*));
 #if SQLITE_VERSION_NUMBER >= 3007015
@@ -108,6 +110,7 @@ DEF_DLL_FN (SQLITE_API int, sqlite3_db_config, (sqlite3*, int, ...));
 # undef sqlite3_bind_double
 # undef sqlite3_bind_null
 # undef sqlite3_bind_int
+# undef sqlite3_bind_parameter_index
 # undef sqlite3_extended_errcode
 # undef sqlite3_errmsg
 # if SQLITE_VERSION_NUMBER >= 3007015
@@ -137,6 +140,7 @@ DEF_DLL_FN (SQLITE_API int, sqlite3_db_config, (sqlite3*, int, ...));
 # define sqlite3_bind_double fn_sqlite3_bind_double
 # define sqlite3_bind_null fn_sqlite3_bind_null
 # define sqlite3_bind_int fn_sqlite3_bind_int
+# define sqlite3_bind_parameter_index fn_sqlite3_bind_parameter_index
 # define sqlite3_extended_errcode fn_sqlite3_extended_errcode
 # define sqlite3_errmsg fn_sqlite3_errmsg
 # if SQLITE_VERSION_NUMBER >= 3007015
@@ -169,6 +173,7 @@ load_dll_functions (HMODULE library)
   LOAD_DLL_FN (library, sqlite3_bind_double);
   LOAD_DLL_FN (library, sqlite3_bind_null);
   LOAD_DLL_FN (library, sqlite3_bind_int);
+  LOAD_DLL_FN (library, sqlite3_bind_parameter_index);
   LOAD_DLL_FN (library, sqlite3_extended_errcode);
   LOAD_DLL_FN (library, sqlite3_errmsg);
 #if SQLITE_VERSION_NUMBER >= 3007015
@@ -333,6 +338,7 @@ bind_values (sqlite3 *db, sqlite3_stmt *stmt, Lisp_Object values)
 {
   sqlite3_reset (stmt);
   int len;
+  int kw_dex = 0;
   if (VECTORP (values))
     len = ASIZE (values);
   else
@@ -341,6 +347,7 @@ bind_values (sqlite3 *db, sqlite3_stmt *stmt, Lisp_Object values)
   for (int i = 0; i < len; ++i)
     {
       int ret = SQLITE_MISMATCH;
+      int j = (kw_dex ? kw_dex : i + 1);
       Lisp_Object value;
       if (VECTORP (values))
 	value = AREF (values, i);
@@ -376,33 +383,37 @@ bind_values (sqlite3 *db, sqlite3_stmt *stmt, Lisp_Object values)
 	    {
 	      if (SBYTES (value) != SCHARS (value))
 		xsignal1 (Qsqlite_error, build_string ("BLOB values must be unibyte"));
-	    ret = sqlite3_bind_blob (stmt, i + 1,
+	      ret = sqlite3_bind_blob (stmt, j,
 				       SSDATA (value), SBYTES (value),
 				       NULL);
 	    }
 	    else
-	      ret = sqlite3_bind_text (stmt, i + 1,
+	      ret = sqlite3_bind_text (stmt, j,
 				       SSDATA (encoded), SBYTES (encoded),
 				       NULL);
 	}
       else if (EQ (type, Qinteger))
 	{
 	  if (BIGNUMP (value))
-	    ret = sqlite3_bind_int64 (stmt, i + 1, bignum_to_intmax (value));
+	    ret = sqlite3_bind_int64 (stmt, j, bignum_to_intmax (value));
 	  else
-	    ret = sqlite3_bind_int64 (stmt, i + 1, XFIXNUM (value));
+	    ret = sqlite3_bind_int64 (stmt, j, XFIXNUM (value));
 	}
       else if (EQ (type, Qfloat))
-	ret = sqlite3_bind_double (stmt, i + 1, XFLOAT_DATA (value));
+	ret = sqlite3_bind_double (stmt, j, XFLOAT_DATA (value));
       else if (NILP (value))
-	ret = sqlite3_bind_null (stmt, i + 1);
+	ret = sqlite3_bind_null (stmt, j);
       else if (EQ (value, Qt))
-	ret = sqlite3_bind_int (stmt, i + 1, 1);
+	ret = sqlite3_bind_int (stmt, j, 1);
       else if (EQ (value, Qfalse))
-	ret = sqlite3_bind_int (stmt, i + 1, 0);
+	ret = sqlite3_bind_int (stmt, j, 0);
+      else if (!NILP (Fkeywordp (value))
+	       && (kw_dex = sqlite3_bind_parameter_index
+		   (stmt, SSDATA (encode_string (SYMBOL_NAME (value))))))
+	continue;
       else
 	return "invalid argument";
-
+      kw_dex = 0;
       if (ret != SQLITE_OK)
 	return sqlite3_errmsg (db);
     }
diff --git a/test/src/sqlite-tests.el b/test/src/sqlite-tests.el
index a10dca9a0c9..6c609962196 100644
--- a/test/src/sqlite-tests.el
+++ b/test/src/sqlite-tests.el
@@ -142,6 +142,37 @@ sqlite-param
       (sqlite-select db "select * from test4 where col2 = ?" [1])
       '(("foo" 1))))))
 
+(ert-deftest sqlite-keyword-params ()
+  (skip-unless (sqlite-available-p))
+  (let ((db (sqlite-open)))
+    (sqlite-execute
+     db "CREATE TABLE IF NOT EXISTS test4a (col1 TEXT, col2 NUMBER)")
+    (sqlite-execute db "INSERT INTO test4a VALUES (:a, :b)" '(:a "foo" :b 1))
+    (should
+     (equal
+      (sqlite-select db "SELECT * FROM test4a WHERE col2 = :a" '(:a 1))
+      '(("foo" 1))))
+    (should
+     (equal
+      (sqlite-select db "SELECT * FROM test4a WHERE col1 = :b" [:b "foo"])
+      '(("foo" 1))))
+
+    ;; Template specifiers reused.
+    (sqlite-execute
+     db (concat "CREATE TABLE IF NOT EXISTS test4b ("
+                " u, v, w, x, y, z, FOREIGN KEY(v) REFERENCES test4a(rowid)"
+                ")"))
+    ;; Here, t matches `col2' because it's a boolean and is coerced to 1.
+    (sqlite-execute db (concat "INSERT INTO test4b(u, v, w, x, y, z)"
+                               " SELECT :a, t.rowid, :b, :c, :d, :e"
+                               " FROM test4a as t "
+                               " WHERE t.col1 = :a AND t.col2 = :b")
+                    '(:a "foo" :b t :c false :d 3.14159 :e nil)) ; e is NULL
+    (should
+     (equal
+      (sqlite-select db "SELECT * FROM test4b WHERE v = :a AND w = :a" '(:a 1))
+      '(("foo" 1 1 0 3.14159 nil))))))
+
 (ert-deftest sqlite-binary ()
   (skip-unless (sqlite-available-p))
   (let (db)
-- 
2.43.0