SQLite Forum

Timeline
Login

50 most recent forum posts by user vman59

2021-11-28
12:58 Reply: Naminng indices during CREATE TABLE (artifact: 6bfbccfead user: vman59)

Seems to me the addition you want is to be able execute

ALTER TABLE t DROP CONSTRAINT idx1.

2021-10-15
14:33 Delete reply: (Deleted) (artifact: 0f099e8eb5 user: vman59)
Deleted
14:33 Reply: (Deleted) (artifact: 3ba4a9f758 user: vman59)
Where are you getting '1 day and 3 hours'? It should be '1 day minus 3 hours'.

   2-jan-2021 01:00:00
 - 1-jan-2021 04:00:00
------------------------
    (1 day) - 03:00:00  = 24:00:00 - 03:00:00 = 21:00:00
2021-10-08
16:35 Edit: Validating URI parameter names (artifact: b152e8b2d3 user: vman59)

If your VFS doesn't recognize a parameter name, what's the best way for it tell whether the name is recognized by the SQLIte core or invalid (i.e. a typo)? I'd rather fail then when there is a mis-typed parameter name than ignore the parameter.

There are the documented names (.e.g. nolock), but is there a way to discover at runtime the parameter used by the core?

16:34 Post: Validating URI parameter names (artifact: c4346bb2e3 user: vman59)

If your VFS doesn't recognize a parameter name, what's the best way for it tell whether the name is recognized by the SQLIte core or invalid (i.e. a typo)? I'd rather fail due to a mis-type parameter name than ignore the parameter.

There are the documented names (.e.g. nolock), but is there a way to discover at runtime the parameter used by the core?

2021-09-20
13:07 Edit reply: How to find out whether a table is STRICT? (artifact: 7dde86f827 user: vman59)

Maybe the pragma_table_info virtual table could implement some hidden columns, e.g.:

SELECT cid,name,strictType,affinity FROM pragma_table_info('employees');

The strictType value for traditional non-strict tables would always be null.

13:06 Reply: How to find out whether a table is STRICT? (artifact: 3a936cc227 user: vman59)

Maybe the pragma_table_info virtual table could implement some hidden columns, e.g.:

SELECT cid,name,strictType,affinity FROM pragma_table_info('employees');

The strictType value for non-strict table would always be null.

2021-09-12
12:52 Reply: Wishing CLI could be usefully embedded (artifact: a579e5532e user: vman59)

How is SIGINT dealt with? Can an application GUI simulate/generate one with a 'cancel' button?

Will the SQLITE_SHELL_DBNAME_PROC and SQLITE_SHELL_INIT_PROC hacks be made part of the formal interface?

I think a good test of the interface would be that all the Windows-related cruft in shell.c could be removed and replaced with a Windows application that calls the embedded CLI.

2021-09-10
13:43 Reply: reading databases using a customized virtual file-system (artifact: 5c21498bf8 user: vman59)

I use the undocumented SQLITE_EXTRA_INIT hack to make SQLite's OS initialization install a custom VFS and make it the default VFS.

2021-09-09
01:17 Reply: v3.36.0 fails to compile under Ubuntu (artifact: 102f6373ba user: vman59)

It looks like it is related to WAL file processing and your -DSQLITE_OMIT_WAL disables the creation of the function but not the call to it in unixFileControl().

2021-09-05
14:11 Reply: What happens if I define LONGDOUBLE_TYPE as double? (artifact: ced32e84da user: vman59)

I heard from an authoritative source that the compiler is incomplete and the goal is to use a software implementation of 128-bit IEEE.

I did some quick tests on the SQLite that came with my Mac Mini and Raspberry Pi4 and found that the expression "CAST(9223372036854774800 AS real)" displays as 9.22337203685477e+18 on the former and 9.22337203685478e+18 on the latter. The Pi shows the same rounding error that I get defining LONGDOUBLE_TYPE as double.

2021-09-02
19:58 Reply: What happens if I define LONGDOUBLE_TYPE as double? (artifact: f8463aab37 user: vman59)

It's broken in various, odd, ways (perhaps it's trying to software implement 128bit IEEE). One manifestation with SQLite is sometimes the number formats and sometimes it shows NaN, even repeating the same statement. If my workaround is serviceable for the time being, I don't want to let the perfect be the enemy of the good.

16:29 Post: What happens if I define LONGDOUBLE_TYPE as double? (artifact: cd14ce5ac8 user: vman59)

I'm building SQLite on a platform using a compiler whose support for the long double data type is broken. What's the ramifications of replacing the default definition of LONGDOUBLE_TYPE to just double? It looks like that data type is only used in a few places to get better precision on the 15th digit when converting numeric text strings to or from REAL data type.

2021-08-23
00:04 Reply: about "strict" mode (artifact: 453afc7726 user: vman59)

How about being able to specify NOT STRICT in a column definition (analogous to NOT NULL)? With NOT STRICT, a column can have a type affinity without requiring that type. NOT STRICT without an accompanying type would operate the same as ANY. This may the defeat the objective of STRICT tables in the first place, I haven't thought deeply about it.

2021-08-22
18:13 Reply: About STRICT tables (artifact: 92a5665bba user: vman59)

The draft page has INT and INTEGER as separate types while the wiki implies they are synonyms. The draft page also lacks ANY as an allowed type.

14:11 Reply: vtable module - pass state from xColumn to callback (artifact: 1f1cae1341 user: vman59)

https://sourceforge.net/projects/vms-ports/files/SQLITE3/excel_convert_003c.zip/download

It was written in an OpenVMS/DECC environment, so I had to add a Makefile and a couple tweaks to let it build under Linux/gcc (gcc will still complain about a few "#pragma message" directives that mean something different in DECC).

2021-08-21
00:16 Reply: vtable module - pass state from xColumn to callback (artifact: 9f086986f9 user: vman59)

Some more information: The xcolumn() method uses sqlite3_result_subtype() to tag the value with the column index (1-255) of the column being returned. The attr() function then uses sqlite3_value_subtype() to identify the column data for the current row and retrieve the its metadata according to the second argument.

The 8-bit limitation for the subtype code means it only works for virtual tables with less than 256 columns.

2021-08-20
18:10 Edit reply: vtable module - pass state from xColumn to callback (artifact: 545f73c11e user: vman59)

For an xlsx virtual table module I wrote, it also defines an attr function to return metadata. e.g.:

CREATE VIRTUAL TABLE orders USING xlsxvtab('orders.xlsx','Sheet 1'); SELECT a,b,c,attr(c,'f') AS 'formula',attr(c,6) AS 'format' FROM orders;

The module generates an error if the first argument to attr() does not reference a cell in the virtual table. This is done by having a global attr() function defined at module load and a local scope attr() that overrides it for the table create.

18:08 Reply: vtable module - pass state from xColumn to callback (artifact: 0ddf59d070 user: vman59)

For an xlsx virtual table module I wrote, it also defines an attr function to return metadata. e.g.:

CREATE VIRTUAL TABLE orders USING xlsxvtab('orders.xlsx','Sheet 1'); SELECT a,b,c,attr(c,'f') AS 'formula',attr(c,6) AS 'format' FROM orders;

The module generates an error if the first argument to attr() does not reference a cell in the virtual table. This is done by having a global attr() function defined at module load and a local scope attr() that overrides it for the table create.

2021-07-18
14:56 Reply: Inserting One Billion Rows in SQLite Under A Minute (artifact: 18a5f0491f user: vman59)

I've done a couple of those things and seen moderate results. My experience with making the page cache very large is it adds a big delay when you close the DB (presumably due to the I/O flushing the cache).

If the schema has several indices, is it better to defer adding them until after the tables are loaded? I don't know if an index build/rebuild can optimize page cache management while walking a large table for an index build/rebuild.

2021-07-13
23:26 Reply: Loading bundled extensions in dotnet on linux (artifact: 7c8dbf05ff user: vman59)

I'd probably add a table to the database (e.g. interop(init_func,OS,arch,path)) and if the select for init_func,OS,arch fails report a configuration error with steps needed to correct. Populating the table can be deferred until deployment.

2021-07-08
22:12 Reply: Build Error: sqlite3.c(206932): error C2061: syntax error: identifier 'sqlite3_session' (artifact: c5a80b53eb user: vman59)

I call my forced include file platform.h, logically it could be 2 separate files:

  • platform.h: #defines required to inform SQLite source of the build environment, so that compiling is possible.
  • features.h: #defines that enable/disable optional components of SQLite or optimize behavior.

(These are general categories, it's not always a hard line).

03:02 Reply: Build Error: sqlite3.c(206932): error C2061: syntax error: identifier 'sqlite3_session' (artifact: f62ba383c2 user: vman59)

If you're using config.h as a way to make an otherwise lengthy command line for the compiler invocation shorter, you'd want the include to happen first thing.

2021-06-16
14:33 Reply: Opening a DB with SQLITE_OPEN_EXCLUSIVE (artifact: 71fef4772c user: vman59)

Why not just open() the file with the desired flags, save the result, and then do the SQLite open? Of course, you still have to have the same transaction to initialize the database in the case of a new file to deal with the same ultimate race conditions.

2021-05-25
01:18 Edit reply: Any examples of virtual tables that use idxInfo->idxStr? (artifact: 853f5b586e user: vman59)

Yes, that was my mis-reading. "Any desired meaning" doesn't automatically imply 'text' to some people.

01:14 Reply: Any examples of virtual tables that use idxInfo->idxStr? (artifact: 1d36131976 user: vman59)

Yes, that was my mis-reading.

2021-05-24
20:48 Post: Any examples of virtual tables that use idxInfo->idxStr? (artifact: a91a5a7be9 user: vman59)

For it's described usage (an opaque pointer for conveying information between xBestIndex() and xFilter()), having idxStr be a char * rather than void * would seem to make things awkward in the general case. What did the designer of the interface have in mind?

2021-05-14
21:20 Reply: Feedback on writing a SQLite VFS for a distributed object store (artifact: 79d193f9c8 user: vman59)

For my custom VFS, I just patched sqlite3_os_init function to do some additional setup. I also patched sqlite3Pragma() so the VFS can return a table value in response to the pragma file control.

2021-05-13
10:26 Reply: How to convert am/pm time to 24hrs time format (artifact: 93a8482df6 user: vman59)

Logically, 1:00 A.M. is 1 hour before noon, or 11:00 on a 24-hour clock. Since we don't count backwards on the clock face, A.M/P.M. designates which cycle on a 12-hour clock we are in. 12:00:01 A.M. is 1 second after midnight.

2021-05-11
23:23 Reply: Place in the code where can understand that the file was created (artifact: 653d7b5e5c user: vman59)

Look for the code around the string 'Only allow sensible combinations" in sqlite3.c, it does not consider SQLITE_OPEN_CREATE without an accompanying SQLITE_OPEN_READWRITE to be a sensible combination. This check is in the common open function before the VFS gets involved.

2021-05-08
16:21 Reply: Opening a DB with SQLITE_OPEN_EXCLUSIVE (artifact: eab539b5ce user: vman59)

If you are trying to guarantee you're creating a new database file, just open the database file readwrite and execute your "CREATE TABLE ..." SQL statement. If the file already existed and wasn't an SQLite database, the open will fail. If a prior invocation created the database first (i.e. race condition), the SQL will fail. If you have different applications using the same database filename, that may or may not be a problem.

2021-05-06
14:40 Reply: User Defined Functions (artifact: dc709ce7ad user: vman59)

Your example is using a .net interface presumably provided by Microsoft, the underlying sqlite3_create_function() is a C interface and requires the user to process the argument list built by the SQLite VDBE at a somewhat low level.

2021-04-24
17:56 Reply: Solved OPEN, ATTACH, CREATE VIEW, SELECT - possible? (artifact: 4c92c5dead user: vman59)

And yet you can create a view referencing a virtual table which is only valid if the extension is loaded.

2021-03-17
23:05 Edit reply: SQL to return list of PRAGMAs (artifact: 42a42397df user: vman59)

It would be nice if there was an eponymous virtual table that would let you do the pragma operation as part of an SQL select statement, e.g.:

SQL select name,type from sqlite_pragma('function_list') where builtin=0;

22:56 Reply: SQL to return list of PRAGMAs (artifact: 1953aeea9e user: vman59)

It would be nice if there was an eponymous virtual table that would let you do the pragma operation as part of an SQL select statement, e.g.:

SQL select * from sqlite_pragma('pragma_list');

2021-03-14
19:45 Reply: Feature request: pragma for changing the precision / format of CURRENT_TIMESTAMP to YYYY-MM-SSTHH:MM:SS.SSS (artifact: a48605104a user: vman59)

How would you express 1/86400th of a day in binary form, or even 1/24th of a day?

2021-03-03
14:26 Reply: encoding on new DB-File (artifact: 81664396e4 user: vman59)

The same conflict exists at the API level. To execute a pragma statement you need a database connection, which you get by opening the database. Logically, the encoding should be flags on the open call.

2021-02-10
19:00 Reply: Can parts of a database be protected for distribution? (artifact: 3bd6a1d5db user: vman59)

Have the checksum field be an encrypted blob that consists of a random salt used to initialize the hash of all the other columns of the row. That does mean the queries have to return the entire row and have discipline to avoid unexpected type conversions.

2021-01-25
16:48 Reply: external pages or resources returned a 403 HTTP status code (artifact: ca195dc93c user: vman59)

My web server can directly serve a zip file as a document branch so I do minimal processing of it. The direct references to sqlite.org seem to be to files not included in the doc bundle so there are no local files to rewrite them to.

12:16 Reply: external pages or resources returned a 403 HTTP status code (artifact: 2150c79bde user: vman59)

I download the documentation bundle (e.g. sqlite3-doc-3340100.zip) and serve the files it contains with my own web server for local consumption. There only seem to be a few omissions from what's on the sqlite.org site and few of the links are hard coded to sqlite.org or external links.

2021-01-08
14:18 Reply: Asynchronous API for standalone LSM extension (artifact: 1eeab9d70a user: vman59)

Wasn't SQLite 4 supposed to use LSM but they really didn't find significant improvement?

2020-12-20
10:57 Reply: multiple queries (artifact: 1aa65114ef user: vman59)

sps_execute() binds the parameter values and returns a context for retrieving the result rows of a query, no rows have been returned yet by the SQLite VDBE. The sps_next_row() loop retrieves at most one row each call and increments ctx->count if successful.

My library has another function, sps_execute_rowless(), which does run the statement 'fully' before returning, but that's used where you expect sqlite3_step() to immediately return SQLITE_DONE rather than one or more SQLITE_ROW return values first (e.g. "COMMIT;").

2020-12-19
21:23 Reply: multiple queries (artifact: 2979aab599 user: vman59)

I wrote my own support library that lets my applications deal with SQLite in a style in between the
simplicity of sqlite3_exec() and tediousness of the standard prepare,bind,step,column_xxx loop.

The basic pattern for this library is:


   #include "statement_store.h"
   sps_store sps;
   struct sps_context *ctx;
   int emp_num, dept_num;
   char *first, *last;

   sps_define1 (sps, "emp-by-last",
              "SELECT * FROM employee WHERE last LIKE ?1", "s");

   ctx = sps_execute2 (sps, "emp-by-last","%son");
   while (sps_next_row3(ctx,"itti",&emp_num,&first,&last,&dept_num)) 
     {
        printf("%8d %-12s %-15s %5d\n", emp_num, first, last, dept_num);
        free ( first );
        free ( last );
     }
   if (ctx->rc != SQLITE_DONE) printf ("Error retrieving data");
   printf ("Rows retrieved: %d\n", ctx->count);

   rc = sps_rundown4(ctx);
Notes:
  1. Pepares the SQL statement and saves the resulting sqlite3_stmt object in the sps_store object,
    associating it with a tag ("emp-by-last").
  2. Lookup the statement object and bind the callers arguments to the statement's parameters. The
    function uses the bind map passed to sps_define() ("s") to determine the number and data types
    of these arguments.
  3. Retrieve the next row in the result set and convert the column values to the caller's arguments
    based on the conversion codes specified in the second argument ("itti" -> int, text, text, int).
  4. Reset the statement and free the sps_context object. The statement is not finalized and may be
    reused by subsequent calls to sps_execute.

2020-12-13
16:52 Reply: Incorrect conversion from unixepoch (artifact: 3a59acf40d user: vman59)

The utc modifier is doing what it's documented to do, but I can see how it is confusing to some people if they've never encountered a system where timestamps are stored as seconds from 1970 in the local time zone (i.e. unix-like, but not UTC). Any source of 'unixepoch' time values these days can be assumed to be UTC, so the 'utc' modifier is a fix for a very rare problem.

2020-11-22
23:38 Reply: sqlite3 '-batch' option appears undocumented (artifact: 87087d2330 user: vman59)

Browsing the source for the shell program, the setting of stdin_is_interactive cause numerous miscellaneous effects. Among the things affected are error message generation, whether the input line is saved in the history file, and whether an interrupt signal causes program exit or just aborts the current command.

2020-10-15
11:53 Reply: Add option for a process exclusive mode to open files on Windows (artifact: 7895679360 user: vman59)

Couldn't the Windows VFS use the hook into the PRAGMA interface to set windows-specific options?

2020-09-02
18:05 Reply: 'vfs' (artifact: 48936928b6 user: vman59)

The VFS object in SQLite has a broader scope than the *ix usage of VFS. I'd rather it had been called something along the lines of Virtual Operating Environment (VOE) or System Interfaces Translator (SIT). Too late now.

2020-08-14
18:28 Edit reply: URL for downloading latest version (artifact: f69e472999 user: vman59)

I have a script that fetches the /download.html page and extracts the version and hashes from the HTML. It then downloads the files and uses the hashes to verify the downloads completed successfully. A link to an XML file with that information would be nice.

18:27 Reply: URL for downloading latest version (artifact: b9bae997d1 user: vman59)

I have a script that goes to fetches the /download.html page and extracts the version and hashes from the HTML. It then downloads the files and uses the hashes to verify the downloads completed successfully. A link to an XML file with that information would be nice.

2020-08-02
13:45 Reply: Suggestion: Use strong hashes on the download page (artifact: a50fc18b7f user: vman59)

If verifying the hash with OpenSSL, specify sha3-256 as the digest algorithm.

More ↓