SQLite Forum

Timeline
Login

18 forum posts by user mbenningfield1

2021-10-05
23:20 Edit reply: Utility extensions (Windows-only) (artifact: a5e702a756 user: mbenningfield1)

Sorry, I'm fighting a dodgy internet connection today, and I can't seem to edit the original post.

Here's the link

Finally, original post edited.

23:19 Edit: Utility extensions (Windows-only) (artifact: 57dbad1573 user: mbenningfield1)

I've been persuaded to publish an in-house extension library that's proven to be very useful. Hopefully, someone else may find it useful as well.

It has extension functions for decimal math, Unicode string utilities, among others. The link has a pretty extensive README file.

The code is in the Public Domain, so do with it as you will.

17:52 Reply: Utility extensions (Windows-only) (artifact: 71fee2b654 user: mbenningfield1)

Sorry, I'm fighting a dodgy internet connection today, and I can't seem to edit the original post.

Here's the link

17:29 Post: Utility extensions (Windows-only) (artifact: bc6442b648 user: mbenningfield1)

I've been persuaded to publish an in-house extension library that's proven to be very useful. Hopefully, someone else may find it useful as well.

It has extension functions for decimal math, Unicode string utilities, among others. The link has a pretty extensive README file.

The code is in the Public Domain, so do with it as you will.

2021-09-30
16:15 Reply: sqlite3_exec: Any way to validate the first argument? (artifact: 6a54247657 user: mbenningfield1)

Well, the first arg is an open database handle, which you would have had to call sqlite3_open() to get, so presumably you know if it's valid or not??

2021-09-29
21:26 Reply: Possible bug in group_concat() ? (artifact: f3eb24a6c0 user: mbenningfield1)

Since the group_concat() function was converted to an aggregate window function, then it should inverse what was actually accumulated. It looks like the new behavior does that. I'm not set up to build the amalgamation from source (if I need to build sqlite, I just build the amalgamation, because that's easy, and I'm lazy :) ).

Maintaining the state necessary to be aware of the lengths of each added separator naturally leads to inversing the Nth separator instead of just the first one. However, with the "off-by-1" problem fixed, if the separators are the same length, like in the query I used, it shouldn't matter really with regard to the old behavior versus the new, because the result will be truncated by the correct amount in either case.

I submit that, to the extent that the old behavior conflicts with the new, the old behavior is not acting correctly as a window function, in that it is not removing exactly what was added.

02:25 Reply: Possible bug in group_concat() ? (artifact: deea7867a1 user: mbenningfield1)

Thanks for the fast fix.

v3.37.0 is due out in a couple of weeks isn't it?

2021-09-28
01:25 Post: Possible bug in group_concat() ? (artifact: ccf3b5673b user: mbenningfield1)

While testing my own table-valued function, I got some rather weird results when running a window-function test. Try as I might, I couldn't account for it in my own code. The debugger showed the correct output being assigned on each call to the xColumn() function. So, I ran the same test with the generate_series() function as a sanity check, using the following query:

CREATE TABLE persons(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO persons (name) VALUES('John'), ('Paul'), ('George'), ('Ringo');

SELECT group_concat(value,name)
OVER (
  ORDER BY name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)
AS result
FROM persons, generate_series(4450,4455);

Granted, the query is nonsense, but the issue is not that the results don't make any sense; it's that information is missing:

result
-------------------------
4450George4451
4450George4451George4452
4451George4452George4453
4452George4453George4454
4453George4454George4455
4454George4455John4450
4455John4450John4451
50John4451John4452   <-- initial values begin to be truncated
51John4452John4453
52John4453John4454
53John4454John4455
54John4455Paul4450
55Paul4450Paul4451
50Paul4451Paul4452
51Paul4452Paul4453
52Paul4453Paul4454
53Paul4454Paul4455
54Paul4455Ringo4450
55Ringo4450Ringo4451
450Ringo4451Ringo4452
451Ringo4452Ringo4453
452Ringo4453Ringo4454
453Ringo4454Ringo4455
454Ringo4455

shell .version command:

SQLite 3.36.0 2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafa66e5
zlib version 1.2.11
gcc-5.2.0

I compiled series.c as a 32-bit dll using Visual Studio 2015 (_MSC_VER 1900)

Regrettably, I'm not set up for testing sqlite itself, and besides, the amalgamation is apparently too large for the VS debugger (it can never seem to break on the correct source line).

2021-05-14
11:36 Reply: Special characters * and ? are not supported in Database Names on Windows? (artifact: cfeccfcacd user: mbenningfield1)

Well, no. You can't create a file on Windows with illegal filename characters.

See this list

2021-04-15
11:15 Post: Proposed addition to the sqlite3_result_XX API (artifact: e97e1e5a91 user: mbenningfield1)

As it stands, you can use sqlite3_result_error() to return an error message from an extension, with the result code of SQLITE_ERROR.

Or, you can use sqlite3_result_error_code() to return a specific error code, but with the stock error message.

The first way is good if you want your extension functions to be useful for SQL developers, who are concerned that the error message makes sense for what they are trying to do.

The second way is good if you want your extension functions to be useful for application developers, since branching decisions are much easier based on error codes, and the error message is usually generated later in the application workflow anyway.

Unless I'm having one of my not-unheard-of brain farts, I can't find any way of returning a specific error code with a specific error message.

So my proposal is to add a function to the vdbeapi.c source along the lines of:

SQLITE_API sqlite3_result_error_info(sqlite3_context *pCtx, const char *zError, int cbError, int errCode);

Ditto of course, for the UTF-16 version.

2021-03-18
16:47 Reply: Proper set up for working with SQLite, C, Tcl/Tk? (artifact: 171410dc21 user: mbenningfield1)

The primary reason to prefer a Linux distro is the fact that you get to use Valgrind.

Period. End of story.

Aside from that, there's the whole question of target platforms and portability (Linux vs. Windows vs. Mac).

2021-01-18
13:45 Reply: Tcl limit API (artifact: 5c0fe5e0b3 user: mbenningfield1)

Since I only needed the limit command, and since the test1.c extension is ~8600 LOC, I just added the code from the test_limit() function as a new switch case in the tclsqlite extension.

Here's a patch file for anyone that wants to use it:

--- C:/Differ/old/tclsqlite.c	Fri Nov 27 06:59:17 2020
+++ C:/Differ/new/tclsqlite.c	Mon Jan 18 06:51:47 2021
@@ -26,0 +27,14 @@
+
+/*==============================================================================
+ * 2021 January 16
+
+ * Modified to include the code in the test_limit() function from the test1.c
+ * file in the SQLite source. This adds the "limit" command to a database
+ * connection object, which invokes the "sqlite3_limit()" API.
+ *
+ * Changes are marked with /* EDIT: ...
+ *
+ * The author of these changes further disclaims copyright.
+ *
+ *============================================================================*/
+
@@ -1916 +1930 @@
-  static const char *DB_strs[] = {
+  static const char *DB_strs[] = { /* EDIT: Added "limit" and DB_LIMIT */
@@ -1930 +1944,2 @@
-    "version",                "wal_hook",              0
+    "version",                "wal_hook",              "limit",
+    0
@@ -1946 +1961 @@
-    DB_VERSION,               DB_WAL_HOOK             
+    DB_VERSION,               DB_WAL_HOOK,             DB_LIMIT
@@ -3636,0 +3652,65 @@
+  /* EDIT: Added a case for DB_LIMIT
+  **
+  **    $db limit ID VALUE
+  **
+  ** Set or query the SQLITE_LIMIT_XX value
+  */
+  case DB_LIMIT: {
+  static const struct {
+     char *zName;
+     int id;
+  } aId[] = { /* EDIT: Permit exclusion of SQLITE_ prefix */
+    { "SQLITE_LIMIT_LENGTH",              SQLITE_LIMIT_LENGTH               },
+    { "SQLITE_LIMIT_SQL_LENGTH",          SQLITE_LIMIT_SQL_LENGTH           },
+    { "SQLITE_LIMIT_COLUMN",              SQLITE_LIMIT_COLUMN               },
+    { "SQLITE_LIMIT_EXPR_DEPTH",          SQLITE_LIMIT_EXPR_DEPTH           },
+    { "SQLITE_LIMIT_COMPOUND_SELECT",     SQLITE_LIMIT_COMPOUND_SELECT      },
+    { "SQLITE_LIMIT_VDBE_OP",             SQLITE_LIMIT_VDBE_OP              },
+    { "SQLITE_LIMIT_FUNCTION_ARG",        SQLITE_LIMIT_FUNCTION_ARG         },
+    { "SQLITE_LIMIT_ATTACHED",            SQLITE_LIMIT_ATTACHED             },
+    { "SQLITE_LIMIT_LIKE_PATTERN_LENGTH", SQLITE_LIMIT_LIKE_PATTERN_LENGTH  },
+    { "SQLITE_LIMIT_VARIABLE_NUMBER",     SQLITE_LIMIT_VARIABLE_NUMBER      },
+    { "SQLITE_LIMIT_TRIGGER_DEPTH",       SQLITE_LIMIT_TRIGGER_DEPTH        },
+    { "SQLITE_LIMIT_WORKER_THREADS",      SQLITE_LIMIT_WORKER_THREADS       },
+    { "LIMIT_LENGTH",                     SQLITE_LIMIT_LENGTH               },
+    { "LIMIT_SQL_LENGTH",                 SQLITE_LIMIT_SQL_LENGTH           },
+    { "LIMIT_COLUMN",                     SQLITE_LIMIT_COLUMN               },
+    { "LIMIT_EXPR_DEPTH",                 SQLITE_LIMIT_EXPR_DEPTH           },
+    { "LIMIT_COMPOUND_SELECT",            SQLITE_LIMIT_COMPOUND_SELECT      },
+    { "LIMIT_VDBE_OP",                    SQLITE_LIMIT_VDBE_OP              },
+    { "LIMIT_FUNCTION_ARG",               SQLITE_LIMIT_FUNCTION_ARG         },
+    { "LIMIT_ATTACHED",                   SQLITE_LIMIT_ATTACHED             },
+    { "LIMIT_LIKE_PATTERN_LENGTH",        SQLITE_LIMIT_LIKE_PATTERN_LENGTH  },
+    { "LIMIT_VARIABLE_NUMBER",            SQLITE_LIMIT_VARIABLE_NUMBER      },
+    { "LIMIT_TRIGGER_DEPTH",              SQLITE_LIMIT_TRIGGER_DEPTH        },
+    { "LIMIT_WORKER_THREADS",             SQLITE_LIMIT_WORKER_THREADS       },
+
+    /* Out of range test cases */
+    { "SQLITE_LIMIT_TOOSMALL",            -1,                               },
+    { "SQLITE_LIMIT_TOOBIG",              SQLITE_LIMIT_WORKER_THREADS + 1   },
+  };
+    int i, id = 0;
+    int val;
+    const char *zId;
+
+    if( objc!=4 ) { /* EDIT: Added mising end-quote and changed db to limit */
+      Tcl_AppendResult(interp, "wrong # args: should be \"",
+                       Tcl_GetStringFromObj(objv[0], 0), " limit ID VALUE\"", 0);
+      return TCL_ERROR;
+    }
+    zId = Tcl_GetString(objv[2]);
+    for(i=0; i<sizeof(aId)/sizeof(aId[0]); i++){
+      if( _stricmp(zId, aId[i].zName)==0 ){ /* EDIT: Permit case-insensitive */
+        id = aId[i].id;
+        break;
+      }
+    }
+    if( i>=sizeof(aId)/sizeof(aId[0]) ){
+      Tcl_AppendResult(interp, "unknown limit type: ", zId, (char*)0);
+      return TCL_ERROR;
+    }
+    if( Tcl_GetIntFromObj(interp, objv[3], &val) ) return TCL_ERROR;
+    rc = sqlite3_limit(pDb->db, id, val);
+    Tcl_SetObjResult(interp, Tcl_NewIntObj(rc));
+    return TCL_OK; 
+  }
2021-01-11
13:26 Reply: Tcl limit API (artifact: 2bcd12715b user: mbenningfield1)

Okay, thanks. I've just started out with Tcl, and I haven't really gotten around to the TEA aspects yet. Hopefully the books I've ordered will arrive this week and I can spend the weekend in a deep dip.

11:21 Post: Tcl limit API (artifact: 6f37e2897b user: mbenningfield1)

I'm working on updating a SQLite extension and I thought this would be a good time to learn Tcl and convert the (zillion) unit tests to Tcl. Does the Tcl interface for SQLite not contain a command for the sqlite3_limit() API?

2020-11-27
23:43 Reply: NULL values for collation sequence (artifact: 26b37054c7 user: mbenningfield1)

Outstanding. Thank you.

23:42 Reply: NULL values for collation sequence (artifact: d68fb6dfda user: mbenningfield1)

In the old Nabble archive post, DRH states that NULLS always sort first, which is not specified in the SQL92 standard, but is left up to implementations.

I'm not asking how to collate NULL values, I'm asking if there is an explicit guarantee in the API that SQLite will always take care of sorting the NULLS first, and that a collation sequence will not have make sure to do so. That certainly appears to be the case.

23:17 Post: NULL values for collation sequence (artifact: ef27189724 user: mbenningfield1)

I am doing some testing on a custom collation sequence, and I find that no NULL values are passed to the collation sequence as arguments. After a little looking I found this post on the old Nabble archive.

My testing confirms that no NULL values are passed to my custom collation sequence. Can I positively assert that no NULL values will ever be passed to the collation sequence?

2020-11-16
15:49 Post: Query Lookaside Allocation for Current Connection (artifact: fb99eef4b8 user: mbenningfield1)

Is there any way to query the lookaside allocation for a connection? All of the other DB_CONFIG verbs are queryable (well, except for MAINDBNAME).

Granted, I only really need this for testing purposes, and I have a workaround with a bit of add-on code for the SQLite build. But as you can imagine, it is brittle as hell.