>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