* bug#69320: 30.0.50; Support keyword-based substitutions in sqlite
@ 2024-02-23 7:43 J.P.
2024-02-24 0:09 ` J.P.
0 siblings, 1 reply; 3+ messages in thread
From: J.P. @ 2024-02-23 7:43 UTC (permalink / raw)
To: 69320
[-- Attachment #1: Type: text/plain, Size: 4390 bytes --]
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))
[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: 0001-Recognize-keyword-template-specifiers-in-sqlite.patch --]
[-- Type: text/x-patch, Size: 7778 bytes --]
From 036c363c7e2dee51e37c71bf6f5e436fee2f751a Mon Sep 17 00:00:00 2001
From: "F. Jason Park" <jp@neverwas.me>
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
^ permalink raw reply related [flat|nested] 3+ messages in thread
* bug#69320: 30.0.50; Support keyword-based substitutions in sqlite
2024-02-23 7:43 bug#69320: 30.0.50; Support keyword-based substitutions in sqlite J.P.
@ 2024-02-24 0:09 ` J.P.
2024-02-24 15:04 ` J.P.
0 siblings, 1 reply; 3+ messages in thread
From: J.P. @ 2024-02-24 0:09 UTC (permalink / raw)
To: 69320
"J.P." <jp@neverwas.me> writes:
> 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 come to discover that positional arguments can be reused as well
(see below), which makes that last point somewhat less compelling.
>
> I've made a simplistic attempt at implementing this,
Too simplistic, actually. Apparently, the SQL compiler is meant to
support the mixing and matching of positional and named parameters [1],
which my initial version doesn't comply with (but see below).
[1] https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg05313.html
> 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.
>
[...]
> @@ -333,6 +338,7 @@ bind_values (sqlite3 *db, sqlite3_stmt *stmt, Lisp_Object values)
> {
> sqlite3_reset (stmt);
> int len;
> + int kw_dex = 0;
I think one way to support interspersed param types would be to maintain
two separate indexes, e.g,
int pos_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);
which would mean changing this to something like
int j = (kw_dex ? kw_dex : ++pos_dex);
> Lisp_Object value;
> if (VECTORP (values))
[...]
> --- 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"
^
Here, replacing the :c with a ?
> + " 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
and removing the corresponding :c in this values list is meant to be
supported (but isn't in my initial patch). I believe something like the
changes mentioned above would fix this.
> + (should
> + (equal
> + (sqlite-select db "SELECT * FROM test4b WHERE v = :a AND w = :a" '(:a 1))
Re repeated positionals: the following query should be equivalent to the
last case just above:
(sqlite-select db "SELECT * FROM test4b WHERE v = ? AND w = ?1" '(1))
> + '(("foo" 1 1 0 3.14159 nil))))))
> +
> (ert-deftest sqlite-binary ()
> (skip-unless (sqlite-available-p))
> (let (db)
^ permalink raw reply [flat|nested] 3+ messages in thread
* bug#69320: 30.0.50; Support keyword-based substitutions in sqlite
2024-02-24 0:09 ` J.P.
@ 2024-02-24 15:04 ` J.P.
0 siblings, 0 replies; 3+ messages in thread
From: J.P. @ 2024-02-24 15:04 UTC (permalink / raw)
To: 69320
[-- Attachment #1: Type: text/plain, Size: 2076 bytes --]
"J.P." <jp@neverwas.me> writes:
>> @@ -333,6 +338,7 @@ bind_values (sqlite3 *db, sqlite3_stmt *stmt, Lisp_Object values)
>> {
>> sqlite3_reset (stmt);
>> int len;
>> + int kw_dex = 0;
>
> I think one way to support interspersed param types would be to maintain
> two separate indexes, e.g,
>
> int pos_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);
>
> which would mean changing this to something like
>
> int j = (kw_dex ? kw_dex : ++pos_dex);
Actually, nah. This is bogus in many cases. It seems my mental model of
how these specifiers map to value-binding indices was based mostly on
magical thinking. I didn't realize, for one, that anonymous (positional)
specifiers and named ones share the same index space. AFAICT, this means
iterating over supplied parameters twice is unavoidable if we want to
support this feature in full. I've attached an updated version that
demos this approach even though I'm not super keen on it. It's somewhat
wasteful and definitely more complex. If anyone out there knows of a
smarter way, please do indulge me.
If a less ugly solution doesn't come about, I suppose we could impose an
artificial limitation saying that an argument list must either be
entirely one style or the other (positional or named), and never the two
shall meet. On the one hand, there seems to be some historical precedent
treating this as the recommended usage [1]. On the other hand, opting
for such a "nerfed" implementation (like my initial patch) may feel like
a cop out. If so, then it's probably best to stick with the status quo
and not support named parameters at all.
[1] "While all these forms are allowed, it is expected that different
users will use different styles at different times."
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg05313.html
[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: 0000-v1-v2.diff --]
[-- Type: text/x-patch, Size: 9050 bytes --]
From 8b00d4a7b4af152aea0d308d1c79f0ff97e21dbd Mon Sep 17 00:00:00 2001
From: "F. Jason Park" <jp@neverwas.me>
Date: Sat, 24 Feb 2024 06:58:25 -0800
Subject: [PATCH 0/1] *** NOT A PATCH ***
*** BLURB HERE ***
F. Jason Park (1):
Recognize keyword template specifiers in sqlite
doc/lispref/text.texi | 9 +-
src/sqlite.c | 186 ++++++++++++++++++++++++++-------------
test/src/sqlite-tests.el | 74 ++++++++++++++++
3 files changed, 205 insertions(+), 64 deletions(-)
Interdiff:
diff --git a/src/sqlite.c b/src/sqlite.c
index 123182d241b..f446f35fe2a 100644
--- a/src/sqlite.c
+++ b/src/sqlite.c
@@ -331,93 +331,142 @@ DEFUN ("sqlite-close", Fsqlite_close, Ssqlite_close, 1, 1, 0,
return Qt;
}
+/* Return SQLITE_OK or an SQLite error code or, for a user error, -1. */
+static int
+bind_one_value (sqlite3_stmt *stmt, int dex, Lisp_Object value)
+{
+ Lisp_Object type = Ftype_of (value);
+
+ if (EQ (type, Qstring))
+ {
+ Lisp_Object encoded;
+ bool blob = false;
+
+ if (SBYTES (value) == 0)
+ encoded = value;
+ else
+ {
+ Lisp_Object coding_system =
+ Fget_text_property (make_fixnum (0), Qcoding_system, value);
+ if (NILP (coding_system))
+ /* Default to utf-8. */
+ encoded = encode_string (value);
+ else if (EQ (coding_system, Qbinary))
+ blob = true;
+ else
+ encoded = Fencode_coding_string (value, coding_system, Qnil, Qnil);
+ }
+
+ if (blob)
+ {
+ if (SBYTES (value) != SCHARS (value))
+ xsignal1 (Qsqlite_error,
+ build_string ("BLOB values must be unibyte"));
+ return sqlite3_bind_blob (stmt, dex, SSDATA (value),
+ SBYTES (value), NULL);
+ }
+ else
+ return sqlite3_bind_text (stmt, dex, SSDATA (encoded),
+ SBYTES (encoded), NULL);
+ }
+ else if (EQ (type, Qinteger))
+ {
+ if (BIGNUMP (value))
+ return sqlite3_bind_int64 (stmt, dex, bignum_to_intmax (value));
+ else
+ return sqlite3_bind_int64 (stmt, dex, XFIXNUM (value));
+ }
+ else if (EQ (type, Qfloat))
+ return sqlite3_bind_double (stmt, dex, XFLOAT_DATA (value));
+ else if (NILP (value))
+ return sqlite3_bind_null (stmt, dex);
+ else if (EQ (value, Qt))
+ return sqlite3_bind_int (stmt, dex, 1);
+ else if (EQ (value, Qfalse))
+ return sqlite3_bind_int (stmt, dex, 0);
+ else
+ return -1;
+}
+
+static Lisp_Object
+get_next_bind_candidate (int i, Lisp_Object *values)
+{
+ Lisp_Object value;
+ if (VECTORP (*values))
+ value = AREF (*values, i);
+ else
+ {
+ value = XCAR (*values);
+ *values = XCDR (*values);
+ }
+ return value;
+}
+
+/* Apparently, 999 was the max number of allowed params back in 2004
+ https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg05313.html */
+#define MAX_SQLITE_PARAMS 999
+
/* Bind values in a statement like
"insert into foo values (?, ?, ?)". */
static const char *
bind_values (sqlite3 *db, sqlite3_stmt *stmt, Lisp_Object values)
{
sqlite3_reset (stmt);
+ Lisp_Object orig_values = values;
+ int ret = SQLITE_MISMATCH;
int len;
- int kw_dex = 0;
if (VECTORP (values))
len = ASIZE (values);
else
len = list_length (values);
+ bool indexes[MAX_SQLITE_PARAMS] = {0};
+ memset (indexes, 0, sizeof (indexes));
+
+ /* Deal with all named parameters first. */
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);
- else
- {
- value = XCAR (values);
- values = XCDR (values);
- }
- Lisp_Object type = Ftype_of (value);
-
- if (EQ (type, Qstring))
- {
- Lisp_Object encoded;
- bool blob = false;
+ Lisp_Object value = get_next_bind_candidate (i, &values);
+ if (NILP (Fkeywordp (value)))
+ continue;
+ char *param_name = SSDATA (encode_string (SYMBOL_NAME (value)));
+ int dex = sqlite3_bind_parameter_index (stmt, param_name);
+ if (dex == 0)
+ return "invalid named parameter";
+ indexes[dex] = true;
+ value = get_next_bind_candidate (++i, &values);
+ ret = bind_one_value (stmt, dex, value);
+ if (ret)
+ goto failed;
+ }
- if (SBYTES (value) == 0)
- encoded = value;
- else
- {
- Lisp_Object coding_system =
- Fget_text_property (make_fixnum (0), Qcoding_system, value);
- if (NILP (coding_system))
- /* Default to utf-8. */
- encoded = encode_string (value);
- else if (EQ (coding_system, Qbinary))
- blob = true;
- else
- encoded = Fencode_coding_string (value, coding_system,
- Qnil, Qnil);
- }
-
- if (blob)
- {
- if (SBYTES (value) != SCHARS (value))
- xsignal1 (Qsqlite_error, build_string ("BLOB values must be unibyte"));
- ret = sqlite3_bind_blob (stmt, j,
- SSDATA (value), SBYTES (value),
- NULL);
- }
- else
- ret = sqlite3_bind_text (stmt, j,
- SSDATA (encoded), SBYTES (encoded),
- NULL);
- }
- else if (EQ (type, Qinteger))
+ /* Use unoccupied indexes for positional params. */
+ int pos_dex = 0;
+ values = orig_values;
+ for (int i = 0; i < len; ++i)
+ {
+ Lisp_Object value = get_next_bind_candidate (i, &values);
+ /* Skip keywords and their associated values. */
+ if (!NILP (Fkeywordp (value)))
{
- if (BIGNUMP (value))
- ret = sqlite3_bind_int64 (stmt, j, bignum_to_intmax (value));
- else
- ret = sqlite3_bind_int64 (stmt, j, XFIXNUM (value));
+ if (!(VECTORP (values)))
+ values = XCDR (values);
+ i++;
+ continue;
}
- else if (EQ (type, Qfloat))
- ret = sqlite3_bind_double (stmt, j, XFLOAT_DATA (value));
- else if (NILP (value))
- ret = sqlite3_bind_null (stmt, j);
- else if (EQ (value, Qt))
- ret = sqlite3_bind_int (stmt, j, 1);
- else if (EQ (value, Qfalse))
- 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);
+ ret = -1;
+ while ((indexes[++pos_dex]) && pos_dex < MAX_SQLITE_PARAMS);
+ if (pos_dex == MAX_SQLITE_PARAMS - 1)
+ goto failed;
+ ret = bind_one_value (stmt, pos_dex, value);
+ if (ret)
+ break;
}
-
+ failed:
+ if (ret == -1)
+ return "invalid argument";
+ if (ret != SQLITE_OK)
+ return sqlite3_errmsg (db);
return NULL;
}
diff --git a/test/src/sqlite-tests.el b/test/src/sqlite-tests.el
index 6c609962196..e7b03bdb5bf 100644
--- a/test/src/sqlite-tests.el
+++ b/test/src/sqlite-tests.el
@@ -173,6 +173,49 @@ sqlite-keyword-params
(sqlite-select db "SELECT * FROM test4b WHERE v = :a AND w = :a" '(:a 1))
'(("foo" 1 1 0 3.14159 nil))))))
+(ert-deftest sqlite-mixed-params ()
+ (skip-unless (sqlite-available-p))
+ (let ((db (sqlite-open)))
+ (sqlite-execute
+ db "CREATE TABLE IF NOT EXISTS test4c (col1 TEXT, col2 NUMBER)")
+ (sqlite-execute db "INSERT INTO test4c VALUES (:a, ?)" '(:a "foo" 1))
+ (sqlite-execute db "INSERT INTO test4c VALUES (:s, ?)" '(1 :s "spam"))
+ (sqlite-execute db "INSERT INTO test4c VALUES (?, :b)" '("bar" :b 1))
+ (sqlite-execute db "INSERT INTO test4c VALUES (?, :z)" '(:z 1 "baz"))
+ (should (equal (sqlite-select db "SELECT * FROM test4c WHERE col2 = 1")
+ '(("foo" 1) ("spam" 1) ("bar" 1) ("baz" 1))))
+ (should (equal (sqlite-select
+ db "SELECT * FROM test4c WHERE col1 = :a AND col2 = ?"
+ '(:a "foo" 1))
+ '(("foo" 1))))
+ (should (equal (sqlite-select
+ db "SELECT * FROM test4c WHERE col1 = :a AND col2 = ?"
+ '(1 :a "foo"))
+ '(("foo" 1))))
+ (should (equal (sqlite-select
+ db "SELECT * FROM test4c WHERE col1 = ? AND col2 = :b"
+ '("bar" :b 1))
+ '(("bar" 1))))
+ (should (equal (sqlite-select
+ db "SELECT * FROM test4c WHERE col1 = ? AND col2 = :b"
+ '("bar" :b 1))
+ '(("bar" 1))))
+
+ ;; Template comingled specifiers reused.
+ (sqlite-execute
+ db (concat "CREATE TABLE IF NOT EXISTS test4d ("
+ " u, v, w, x, y, z, FOREIGN KEY(v) REFERENCES test4c(rowid)"
+ ")"))
+ (sqlite-execute db (concat "INSERT INTO test4d(u, v, w, x, y, z)"
+ " SELECT :a, t.rowid, :b, ?, ?, :e"
+ " FROM test4c as t "
+ " WHERE t.col1 = :a AND t.col2 = :b")
+ '(:a "foo" :b t false 3.14159 :e nil)) ; e is NULL
+ (should
+ (equal
+ (sqlite-select db "SELECT * FROM test4d WHERE v = ? AND w = ?1" '(1))
+ '(("foo" 1 1 0 3.14159 nil))))))
+
(ert-deftest sqlite-binary ()
(skip-unless (sqlite-available-p))
(let (db)
--
2.43.0
[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #3: 0001-Recognize-keyword-template-specifiers-in-sqlite.patch --]
[-- Type: text/x-patch, Size: 13416 bytes --]
From 8b00d4a7b4af152aea0d308d1c79f0ff97e21dbd Mon Sep 17 00:00:00 2001
From: "F. Jason Park" <jp@neverwas.me>
Date: Thu, 22 Feb 2024 19:28:24 -0800
Subject: [PATCH 1/1] 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_one_value): New function containing most of the value-binding
logic from what was `bind_values'.
(get_next_bind_candidate): New helper function.
(bind_values): Refactor to handle named parameters resembling Emacs
keywords. Factor out the loop body with the various `sqlite3_bind_*'
functions and move them to `bind_one_value'.
* test/src/sqlite-tests.el (sqlite-keyword-params,
sqlite-mixed-params): New test.
---
doc/lispref/text.texi | 9 +-
src/sqlite.c | 186 ++++++++++++++++++++++++++-------------
test/src/sqlite-tests.el | 74 ++++++++++++++++
3 files changed, 205 insertions(+), 64 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..f446f35fe2a 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
@@ -326,87 +331,142 @@ DEFUN ("sqlite-close", Fsqlite_close, Ssqlite_close, 1, 1, 0,
return Qt;
}
+/* Return SQLITE_OK or an SQLite error code or, for a user error, -1. */
+static int
+bind_one_value (sqlite3_stmt *stmt, int dex, Lisp_Object value)
+{
+ Lisp_Object type = Ftype_of (value);
+
+ if (EQ (type, Qstring))
+ {
+ Lisp_Object encoded;
+ bool blob = false;
+
+ if (SBYTES (value) == 0)
+ encoded = value;
+ else
+ {
+ Lisp_Object coding_system =
+ Fget_text_property (make_fixnum (0), Qcoding_system, value);
+ if (NILP (coding_system))
+ /* Default to utf-8. */
+ encoded = encode_string (value);
+ else if (EQ (coding_system, Qbinary))
+ blob = true;
+ else
+ encoded = Fencode_coding_string (value, coding_system, Qnil, Qnil);
+ }
+
+ if (blob)
+ {
+ if (SBYTES (value) != SCHARS (value))
+ xsignal1 (Qsqlite_error,
+ build_string ("BLOB values must be unibyte"));
+ return sqlite3_bind_blob (stmt, dex, SSDATA (value),
+ SBYTES (value), NULL);
+ }
+ else
+ return sqlite3_bind_text (stmt, dex, SSDATA (encoded),
+ SBYTES (encoded), NULL);
+ }
+ else if (EQ (type, Qinteger))
+ {
+ if (BIGNUMP (value))
+ return sqlite3_bind_int64 (stmt, dex, bignum_to_intmax (value));
+ else
+ return sqlite3_bind_int64 (stmt, dex, XFIXNUM (value));
+ }
+ else if (EQ (type, Qfloat))
+ return sqlite3_bind_double (stmt, dex, XFLOAT_DATA (value));
+ else if (NILP (value))
+ return sqlite3_bind_null (stmt, dex);
+ else if (EQ (value, Qt))
+ return sqlite3_bind_int (stmt, dex, 1);
+ else if (EQ (value, Qfalse))
+ return sqlite3_bind_int (stmt, dex, 0);
+ else
+ return -1;
+}
+
+static Lisp_Object
+get_next_bind_candidate (int i, Lisp_Object *values)
+{
+ Lisp_Object value;
+ if (VECTORP (*values))
+ value = AREF (*values, i);
+ else
+ {
+ value = XCAR (*values);
+ *values = XCDR (*values);
+ }
+ return value;
+}
+
+/* Apparently, 999 was the max number of allowed params back in 2004
+ https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg05313.html */
+#define MAX_SQLITE_PARAMS 999
+
/* Bind values in a statement like
"insert into foo values (?, ?, ?)". */
static const char *
bind_values (sqlite3 *db, sqlite3_stmt *stmt, Lisp_Object values)
{
sqlite3_reset (stmt);
+ Lisp_Object orig_values = values;
+ int ret = SQLITE_MISMATCH;
int len;
if (VECTORP (values))
len = ASIZE (values);
else
len = list_length (values);
+ bool indexes[MAX_SQLITE_PARAMS] = {0};
+ memset (indexes, 0, sizeof (indexes));
+
+ /* Deal with all named parameters first. */
for (int i = 0; i < len; ++i)
{
- int ret = SQLITE_MISMATCH;
- Lisp_Object value;
- if (VECTORP (values))
- value = AREF (values, i);
- else
- {
- value = XCAR (values);
- values = XCDR (values);
- }
- Lisp_Object type = Ftype_of (value);
-
- if (EQ (type, Qstring))
- {
- Lisp_Object encoded;
- bool blob = false;
+ Lisp_Object value = get_next_bind_candidate (i, &values);
+ if (NILP (Fkeywordp (value)))
+ continue;
+ char *param_name = SSDATA (encode_string (SYMBOL_NAME (value)));
+ int dex = sqlite3_bind_parameter_index (stmt, param_name);
+ if (dex == 0)
+ return "invalid named parameter";
+ indexes[dex] = true;
+ value = get_next_bind_candidate (++i, &values);
+ ret = bind_one_value (stmt, dex, value);
+ if (ret)
+ goto failed;
+ }
- if (SBYTES (value) == 0)
- encoded = value;
- else
- {
- Lisp_Object coding_system =
- Fget_text_property (make_fixnum (0), Qcoding_system, value);
- if (NILP (coding_system))
- /* Default to utf-8. */
- encoded = encode_string (value);
- else if (EQ (coding_system, Qbinary))
- blob = true;
- else
- encoded = Fencode_coding_string (value, coding_system,
- Qnil, Qnil);
- }
-
- if (blob)
- {
- if (SBYTES (value) != SCHARS (value))
- xsignal1 (Qsqlite_error, build_string ("BLOB values must be unibyte"));
- ret = sqlite3_bind_blob (stmt, i + 1,
- SSDATA (value), SBYTES (value),
- NULL);
- }
- else
- ret = sqlite3_bind_text (stmt, i + 1,
- SSDATA (encoded), SBYTES (encoded),
- NULL);
- }
- else if (EQ (type, Qinteger))
+ /* Use unoccupied indexes for positional params. */
+ int pos_dex = 0;
+ values = orig_values;
+ for (int i = 0; i < len; ++i)
+ {
+ Lisp_Object value = get_next_bind_candidate (i, &values);
+ /* Skip keywords and their associated values. */
+ if (!NILP (Fkeywordp (value)))
{
- if (BIGNUMP (value))
- ret = sqlite3_bind_int64 (stmt, i + 1, bignum_to_intmax (value));
- else
- ret = sqlite3_bind_int64 (stmt, i + 1, XFIXNUM (value));
+ if (!(VECTORP (values)))
+ values = XCDR (values);
+ i++;
+ continue;
}
- else if (EQ (type, Qfloat))
- ret = sqlite3_bind_double (stmt, i + 1, XFLOAT_DATA (value));
- else if (NILP (value))
- ret = sqlite3_bind_null (stmt, i + 1);
- else if (EQ (value, Qt))
- ret = sqlite3_bind_int (stmt, i + 1, 1);
- else if (EQ (value, Qfalse))
- ret = sqlite3_bind_int (stmt, i + 1, 0);
- else
- return "invalid argument";
-
- if (ret != SQLITE_OK)
- return sqlite3_errmsg (db);
+ ret = -1;
+ while ((indexes[++pos_dex]) && pos_dex < MAX_SQLITE_PARAMS);
+ if (pos_dex == MAX_SQLITE_PARAMS - 1)
+ goto failed;
+ ret = bind_one_value (stmt, pos_dex, value);
+ if (ret)
+ break;
}
-
+ failed:
+ if (ret == -1)
+ return "invalid argument";
+ if (ret != SQLITE_OK)
+ return sqlite3_errmsg (db);
return NULL;
}
diff --git a/test/src/sqlite-tests.el b/test/src/sqlite-tests.el
index a10dca9a0c9..e7b03bdb5bf 100644
--- a/test/src/sqlite-tests.el
+++ b/test/src/sqlite-tests.el
@@ -142,6 +142,80 @@ 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-mixed-params ()
+ (skip-unless (sqlite-available-p))
+ (let ((db (sqlite-open)))
+ (sqlite-execute
+ db "CREATE TABLE IF NOT EXISTS test4c (col1 TEXT, col2 NUMBER)")
+ (sqlite-execute db "INSERT INTO test4c VALUES (:a, ?)" '(:a "foo" 1))
+ (sqlite-execute db "INSERT INTO test4c VALUES (:s, ?)" '(1 :s "spam"))
+ (sqlite-execute db "INSERT INTO test4c VALUES (?, :b)" '("bar" :b 1))
+ (sqlite-execute db "INSERT INTO test4c VALUES (?, :z)" '(:z 1 "baz"))
+ (should (equal (sqlite-select db "SELECT * FROM test4c WHERE col2 = 1")
+ '(("foo" 1) ("spam" 1) ("bar" 1) ("baz" 1))))
+ (should (equal (sqlite-select
+ db "SELECT * FROM test4c WHERE col1 = :a AND col2 = ?"
+ '(:a "foo" 1))
+ '(("foo" 1))))
+ (should (equal (sqlite-select
+ db "SELECT * FROM test4c WHERE col1 = :a AND col2 = ?"
+ '(1 :a "foo"))
+ '(("foo" 1))))
+ (should (equal (sqlite-select
+ db "SELECT * FROM test4c WHERE col1 = ? AND col2 = :b"
+ '("bar" :b 1))
+ '(("bar" 1))))
+ (should (equal (sqlite-select
+ db "SELECT * FROM test4c WHERE col1 = ? AND col2 = :b"
+ '("bar" :b 1))
+ '(("bar" 1))))
+
+ ;; Template comingled specifiers reused.
+ (sqlite-execute
+ db (concat "CREATE TABLE IF NOT EXISTS test4d ("
+ " u, v, w, x, y, z, FOREIGN KEY(v) REFERENCES test4c(rowid)"
+ ")"))
+ (sqlite-execute db (concat "INSERT INTO test4d(u, v, w, x, y, z)"
+ " SELECT :a, t.rowid, :b, ?, ?, :e"
+ " FROM test4c as t "
+ " WHERE t.col1 = :a AND t.col2 = :b")
+ '(:a "foo" :b t false 3.14159 :e nil)) ; e is NULL
+ (should
+ (equal
+ (sqlite-select db "SELECT * FROM test4d WHERE v = ? AND w = ?1" '(1))
+ '(("foo" 1 1 0 3.14159 nil))))))
+
(ert-deftest sqlite-binary ()
(skip-unless (sqlite-available-p))
(let (db)
--
2.43.0
^ permalink raw reply related [flat|nested] 3+ messages in thread
end of thread, other threads:[~2024-02-24 15:04 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2024-02-23 7:43 bug#69320: 30.0.50; Support keyword-based substitutions in sqlite J.P.
2024-02-24 0:09 ` J.P.
2024-02-24 15:04 ` J.P.
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/emacs.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).