From unknown Thu Aug 14 22:23:52 2025 X-Loop: help-debbugs@gnu.org Subject: bug#69320: 30.0.50; Support keyword-based substitutions in sqlite Resent-From: "J.P." Original-Sender: "Debbugs-submit" Resent-CC: bug-gnu-emacs@gnu.org Resent-Date: Fri, 23 Feb 2024 07:45:01 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: report 69320 X-GNU-PR-Package: emacs X-GNU-PR-Keywords: patch To: 69320@debbugs.gnu.org X-Debbugs-Original-To: bug-gnu-emacs@gnu.org Received: via spool by submit@debbugs.gnu.org id=B.17086742607802 (code B ref -1); Fri, 23 Feb 2024 07:45:01 +0000 Received: (at submit) by debbugs.gnu.org; 23 Feb 2024 07:44:20 +0000 Received: from localhost ([127.0.0.1]:47030 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1rdQEF-00021k-UC for submit@debbugs.gnu.org; Fri, 23 Feb 2024 02:44:20 -0500 Received: from lists.gnu.org ([209.51.188.17]:48518) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1rdQEC-00021Y-0L for submit@debbugs.gnu.org; Fri, 23 Feb 2024 02:44:18 -0500 Received: from eggs.gnu.org ([2001:470:142:3::10]) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1rdQDo-00009L-6x for bug-gnu-emacs@gnu.org; Fri, 23 Feb 2024 02:43:52 -0500 Received: from mail-108-mta105.mxroute.com ([136.175.108.105]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1rdQDl-0004es-Li for bug-gnu-emacs@gnu.org; Fri, 23 Feb 2024 02:43:51 -0500 Received: from filter006.mxroute.com ([136.175.111.2] filter006.mxroute.com) (Authenticated sender: mN4UYu2MZsgR) by mail-108-mta105.mxroute.com (ZoneMTA) with ESMTPSA id 18dd4eb91780000466.001 for (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384); Fri, 23 Feb 2024 07:43:44 +0000 X-Zone-Loop: 5022dcbe22a6e2e276b5366ebdc32f059913d9a7400f X-Originating-IP: [136.175.111.2] DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=neverwas.me ; s=x; h=Content-Type:MIME-Version:Message-ID:Date:Subject:To:From:Sender: Reply-To:Cc:Content-Transfer-Encoding:Content-ID:Content-Description: Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID: In-Reply-To:References:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=KBEOF4Ge37+y8LCej5Ex18Y4B6nV1tHEp/d4UdE37Ws=; b=JQx5FBtLMnwtCWkLh0za9czNDC PTtXMA+PyepmynoOA8e0RXivm7tsbGF7j9KN0Y28FnhPoNSV98b6UB4GUfGE9E0MrlAN6CqdtxSq3 zVk4JFmsCUwlDMihVAvOPSpEbTDxp2MtcLUDbQb9spK5DWPWBANa/nVL1S6hhL+DBumes1irYLkjN 0j4T2o2V04kSWWqJ+tpuiCL4lHhCfClkG0l3PVPKA4w57oeZb3U1JJJvS9OwmLPJaIJUKvEs3Q8WU HE8WYAwgVbxi5jcOF/owXqBK2gw4q+H51xX16VuLhWgIijqh0NscSMCIm048bG0e9heWyEHogx2Lg Dl9g2w2w==; From: "J.P." Date: Thu, 22 Feb 2024 23:43:41 -0800 Message-ID: <87sf1jaamq.fsf@neverwas.me> User-Agent: Gnus/5.13 (Gnus v5.13) MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" X-Authenticated-Id: masked@neverwas.me Received-SPF: pass client-ip=136.175.108.105; envelope-from=jp@neverwas.me; helo=mail-108-mta105.mxroute.com X-Spam_score_int: -20 X-Spam_score: -2.1 X-Spam_bar: -- X-Spam_report: (-2.1 / 5.0 requ) BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, SPF_HELO_NONE=0.001, SPF_PASS=-0.001, T_SCC_BODY_TEXT_LINE=-0.01 autolearn=ham autolearn_force=no X-Spam_action: no action X-Spam-Score: -1.4 (-) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -2.4 (--) --=-=-= Content-Type: text/plain 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)) --=-=-= Content-Type: text/x-patch Content-Disposition: attachment; filename=0001-Recognize-keyword-template-specifiers-in-sqlite.patch >From 036c363c7e2dee51e37c71bf6f5e436fee2f751a Mon Sep 17 00:00:00 2001 From: "F. Jason Park" 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 --=-=-=-- From unknown Thu Aug 14 22:23:52 2025 X-Loop: help-debbugs@gnu.org Subject: bug#69320: 30.0.50; Support keyword-based substitutions in sqlite Resent-From: "J.P." Original-Sender: "Debbugs-submit" Resent-CC: bug-gnu-emacs@gnu.org Resent-Date: Sat, 24 Feb 2024 00:10:01 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 69320 X-GNU-PR-Package: emacs X-GNU-PR-Keywords: patch To: 69320@debbugs.gnu.org Received: via spool by 69320-submit@debbugs.gnu.org id=B69320.170873337925210 (code B ref 69320); Sat, 24 Feb 2024 00:10:01 +0000 Received: (at 69320) by debbugs.gnu.org; 24 Feb 2024 00:09:39 +0000 Received: from localhost ([127.0.0.1]:56309 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1rdfbm-0006YX-R9 for submit@debbugs.gnu.org; Fri, 23 Feb 2024 19:09:39 -0500 Received: from mail-108-mta31.mxroute.com ([136.175.108.31]:46059) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1rdfbk-0006YF-Lz for 69320@debbugs.gnu.org; Fri, 23 Feb 2024 19:09:38 -0500 Received: from filter006.mxroute.com ([136.175.111.2] filter006.mxroute.com) (Authenticated sender: mN4UYu2MZsgR) by mail-108-mta31.mxroute.com (ZoneMTA) with ESMTPSA id 18dd871be570000466.001 for <69320@debbugs.gnu.org> (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384); Sat, 24 Feb 2024 00:09:09 +0000 X-Zone-Loop: 37b3b048082f2483fac4b1524fb1516bad7fa9e86e63 X-Originating-IP: [136.175.111.2] DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=neverwas.me ; s=x; h=Content-Type:MIME-Version:Message-ID:Date:References:In-Reply-To: Subject:To:From:Sender:Reply-To:Cc:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=wErGYd6qfzv/PbLrixV9M1HAjibqw4CmXQvRcwNACco=; b=a5bEVoOFd85+stRyn9yCj4z9sD mvAVa7kqMcfHbNE13hYSm2cwYXkLA7Qdkg/veYZjGSmwITWc+zZBO5Sqvn8N8odxTbGcFQgry5wET ltm+gwS4a3Hj+bKSDaZ9IZBiPmxWymiIrk8hcFtCH9XyrzdBTC0oSIed8gYkybqR63raPnPUNOCo4 Ib92b5wlZC/90aZxR+aiiVUvBkQ4yeln+akR8GVgJd8ZtVDpjT1L8ZfZUzi7YAu03ubSVb7e8zoGP fhLPA146gyXoNTqlV24AL7yao+77GWdXJz7a4QwWDd18YxZQ5tVbJY5ErVQ+a8tfVVOxSrheLcVVR MM4wbGRA==; From: "J.P." In-Reply-To: <87sf1jaamq.fsf@neverwas.me> (J. P.'s message of "Thu, 22 Feb 2024 23:43:41 -0800") References: <87sf1jaamq.fsf@neverwas.me> Date: Fri, 23 Feb 2024 16:09:06 -0800 Message-ID: <878r3a910d.fsf@neverwas.me> User-Agent: Gnus/5.13 (Gnus v5.13) MIME-Version: 1.0 Content-Type: text/plain X-Authenticated-Id: masked@neverwas.me X-Spam-Score: -0.0 (/) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.0 (-) "J.P." 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) From unknown Thu Aug 14 22:23:52 2025 X-Loop: help-debbugs@gnu.org Subject: bug#69320: 30.0.50; Support keyword-based substitutions in sqlite Resent-From: "J.P." Original-Sender: "Debbugs-submit" Resent-CC: bug-gnu-emacs@gnu.org Resent-Date: Sat, 24 Feb 2024 15:11:01 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 69320 X-GNU-PR-Package: emacs X-GNU-PR-Keywords: patch To: 69320@debbugs.gnu.org Received: via spool by 69320-submit@debbugs.gnu.org id=B69320.170878742114886 (code B ref 69320); Sat, 24 Feb 2024 15:11:01 +0000 Received: (at 69320) by debbugs.gnu.org; 24 Feb 2024 15:10:21 +0000 Received: from localhost ([127.0.0.1]:36437 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1rdtfO-0003rq-AN for submit@debbugs.gnu.org; Sat, 24 Feb 2024 10:10:20 -0500 Received: from mail-108-mta162.mxroute.com ([136.175.108.162]:43765) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1rdtaJ-0003d9-LQ for 69320@debbugs.gnu.org; Sat, 24 Feb 2024 10:05:05 -0500 Received: from filter006.mxroute.com ([136.175.111.2] filter006.mxroute.com) (Authenticated sender: mN4UYu2MZsgR) by mail-108-mta162.mxroute.com (ZoneMTA) with ESMTPSA id 18ddba58aaf0000466.001 for <69320@debbugs.gnu.org> (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384); Sat, 24 Feb 2024 15:04:35 +0000 X-Zone-Loop: 54d8bfb502ef8adfc64d5e359607875b362ec812483a X-Originating-IP: [136.175.111.2] DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=neverwas.me ; s=x; h=Content-Type:MIME-Version:Message-ID:Date:References:In-Reply-To: Subject:To:From:Sender:Reply-To:Cc:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=kuO1ESiVch/L7xcFmVieK3h+P3Omzznwpfo0tFBeA2c=; b=YwnRTR4xkySiOo8ElB40XXIntc gQtp/4zrjPceKsnwAU/w55OKunM91V99cK9jzACq4gBYdkG+8jRjGwuuHOOspAyr6Y83WiKqaajzu 4J5Dil1R+wMZQIkFVbD8HgwhWwET5OcfoahZTkb/4y1mH2YRLNEzhtK8ZzK63yGPW18sjn/UuuGk4 TwK6viZtBfoXvtCJOg1ZBtMV/1kzYEj3zfBsy7TqwwJcLUXLntBxoeIe32lF4Wxye1IIxvZL15xSa AIp0RZJQ9VD1+YyblDKEnJRlG3GiEbGMLThm/WMmZfIoSfxjWDny7whjoMNaLJHWOMQIx0Z1QW3lp cA9FqLWg==; From: "J.P." In-Reply-To: <878r3a910d.fsf@neverwas.me> (J. P.'s message of "Fri, 23 Feb 2024 16:09:06 -0800") References: <87sf1jaamq.fsf@neverwas.me> <878r3a910d.fsf@neverwas.me> Date: Sat, 24 Feb 2024 07:04:32 -0800 Message-ID: <8734th3nun.fsf@neverwas.me> User-Agent: Gnus/5.13 (Gnus v5.13) MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" X-Authenticated-Id: masked@neverwas.me X-Spam-Score: -0.0 (/) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.0 (-) --=-=-= Content-Type: text/plain "J.P." 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 --=-=-= Content-Type: text/x-patch Content-Disposition: attachment; filename=0000-v1-v2.diff >From 8b00d4a7b4af152aea0d308d1c79f0ff97e21dbd Mon Sep 17 00:00:00 2001 From: "F. Jason Park" 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 --=-=-= Content-Type: text/x-patch Content-Disposition: attachment; filename=0001-Recognize-keyword-template-specifiers-in-sqlite.patch >From 8b00d4a7b4af152aea0d308d1c79f0ff97e21dbd Mon Sep 17 00:00:00 2001 From: "F. Jason Park" 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 --=-=-=--