GNU bug report logs - #69320
30.0.50; Support keyword-based substitutions in sqlite

Previous Next

To reply to this bug, email your comments to 69320 AT debbugs.gnu.org.

Toggle the display of automated, internal messages from the tracker.

Message #5 received at submit <at> debbugs.gnu.org (full text, mbox):

From: "J.P." <jp <at> neverwas.me>
To: bug-gnu-emacs <at> gnu.org
Subject: 30.0.50; Support keyword-based substitutions in sqlite
Date: Thu, 22 Feb 2024 23:43:41 -0800

[Message part 1 (text/plain, inline)]

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

[0001-Recognize-keyword-template-specifiers-in-sqlite.patch (text/x-patch, attachment)]

Message #8 received at 69320 <at> debbugs.gnu.org (full text, mbox):

From: "J.P." <jp <at> neverwas.me>
To: 69320 <at> debbugs.gnu.org
Subject: Re: bug#69320: 30.0.50; Support keyword-based substitutions in sqlite
Date: Fri, 23 Feb 2024 16:09:06 -0800

"J.P." <jp <at> 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 <at> 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)

Message #11 received at 69320 <at> debbugs.gnu.org (full text, mbox):

From: "J.P." <jp <at> neverwas.me>
To: 69320 <at> debbugs.gnu.org
Subject: Re: bug#69320: 30.0.50; Support keyword-based substitutions in sqlite
Date: Sat, 24 Feb 2024 07:04:32 -0800

[Message part 1 (text/plain, inline)]

"J.P." <jp <at> 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 <at> mailinglists.sqlite.org/msg05313.html

[0000-v1-v2.diff (text/x-patch, attachment)]

[0001-Recognize-keyword-template-specifiers-in-sqlite.patch (text/x-patch, attachment)]

Previous Next

GNU bug tracking system
Copyright (C) 1999 Darren O. Benham, 1997,2003 nCipher Corporation Ltd, 1994-97 Ian Jackson.