SQLite Forum

Timeline
Login

44 forum posts by user lexfiend

2021-12-01
04:58 Reply: Compile for Apple Silicon (artifact: 34aa909516 user: lexfiend)

Others have already pointed out where you went wrong, but I think you should probably use the autoconf tarball instead, then:

CC=clang CFLAGS="-arch x86_64 -arch arm64" ./configure
make
gets you the universal CLI binary and libs in a single step.

2021-11-25
12:31 Reply: How to rerank one row in a big set of rows? (artifact: 704fd587bd user: lexfiend)

I also wonder if there is a bidirectional pointer approach of ranking items can be used in SQLite

You mean like a doubly-linked list? That's orthogonal to maintaining a rank number, and almost certainly a 200,000-deep recursive CTE. If you're even considering this approach, it begs the question: Why did you start with "maintaining a strictly contiguous integer rank column between 1 and 200,000" as the appropriate solution?

If (as in many cases I've seen) you just need the top or bottom n rows in ranked order, then Dominique's floating-point rank suggestion would work just fine in the general case, because you'd be doing an ORDER BY rank LIMIT n regardless of the actual type of rank.

In any event, more information about your actual use case would help generate more appropriate solutions.

2021-11-14
09:06 Reply: A newbie's question about database structure design (artifact: 0ad0af6a51 user: lexfiend)

I have a 3yeaars large number of stock historical data, they are stored in directories according to different dates, and the name of each directory is like 20180102, 20180103...There are about 750 directories so far, and there are more than 4000 CSV files stored in each directory, and file name corresponds to a stock code. Each CSV file has 50000-100000 rows and 7 columns.

I'm guessing the following:

  1. Each CSV contains tick data, i.e. all individual trades and/or bid/ask quotes for a single counter on a single trading day
  2. You're re-summarizing each counter's tick data every day before doing cross-counter or time-based analysis.

To be frank, [2] is stupid. There's absolutely no reason to re-process the raw historical data for, say, 20180102 every single day, when you're not changing the operations you do each time. Far better to run your summary calculations once for each day/counter and cache that summary, so instead of a 50000-100000 row CSV, you have a CSV with as few as one row (EOD summary).

Once you have that, then the most flexible database design is actually ONE database with ONE table contains ONE row for each day/counter combo. Having done some work in this area, I can assure you that 4 million records encompassing an exchange's entire historical EOD data is far more tractable and useful, than 400 billion records at a data resolution (tick) that isn't even practical for analysis.

2021-08-13
06:00 Reply: LEFT AND RIGHT OUTER JOINS problem (artifact: 54e08d727e user: lexfiend)
  1. Determine a minimum set of records from your dataset that, combined with your query, will show the problem you're describing.
  2. .dump your DB
  3. Pick out the INSERTs from [2] that cover the records from [1].
  4. Post these INSERT statements.

That way, anyone can paste all these commands directly into their own test DBs to verify your issue, and figure out what's going on.

2021-07-31
02:10 Reply: Round function returning -0.0 (artifact: 21d92f32ba user: lexfiend)

Can you also show us the actual code that started this whole thread?

02:08 Reply: Round function returning -0.0 (artifact: 5bcd98f4d7 user: lexfiend)

While true, getting results that no one else can replicate may be a hint that other aspects of SQLite on the OP's machine may also not work as expected.

Or perhaps something in the OP's problem description is off. (The actual code that caused the error was never posted, for instance.)

Either way, it may strictly be nobody's business but the OP's, but it's certainly an intriguing situation...and an excuse for me to post one of my favorite "guilty pleasure" songs. :)

01:59 Reply: Round function returning -0.0 (artifact: b1d6b7b179 user: lexfiend)

Better than "nattering nabob of negativism". :)

2021-07-30
15:10 Reply: Round function returning -0.0 (artifact: 91dc2197b9 user: lexfiend)

Did you try the query I mentioned from a Windows sqlite3 CLI?

select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1);

05:34 Reply: Round function returning -0.0 (artifact: 2fa5fa85c0 user: lexfiend)

Run the sqlite3 CLI and try the following query:

select round(0.0), round(0.0, 1), round(-0.0), round(-0.0, 1);

05:31 Reply: Can SQLITE_INTERNAL be a transient error ? (artifact: 224c846428 user: lexfiend)

Given that the scope of SQLITE_INTERNAL is so ill-defined, I don't see how blanket conclusions like "it's transient, retry", or "the DB's b0rked, time to recreate" can be made.

The only reasonable step to take is in fact "we need to investigate further, and we can't be sure the data's intact".

2021-07-21
11:25 Reply: Calculating duration in ISO8601 timestamp (artifact: 551dd42140 user: lexfiend)

Do you mean like this?

2021-07-19T08:43:46.956+0800

You're missing a colon:

$ sqlite3
sqlite> select julianday('2021-07-19T08:43:46.956+0800');

sqlite> select julianday('2021-07-19T08:43:46.956+08:00');
2459414.53040458

The timezone format is specified in https://sqlite.org/lang_datefunc.html#time_values.

2021-06-30
05:29 Reply: php sqlite3_create_window_function (artifact: b6077e33be user: lexfiend)

That's how it would have to be done, certainly, but I guess my point wasn't clear. When I asked the OP (presumably Mar Zama):

What exactly are the parameters you're passing to $db->createWindowFunction()?

I meant that literally: How are you using this hypothetical function? If the answer is "I haven't really thought that far yet", then how would Mar Zama implement sumint() in PHP with createWindowFunction()?

Answering these questions goes a long way towards defining Mar Zama's requirements--and maybe suggest a alternative solution to a function that doesn't yet exist.

2021-06-29
01:52 Reply: php sqlite3_create_window_function (artifact: e39494b878 user: lexfiend)

Aside from what others have noted (that PHP doesn't currently support sqlite3_create_window_function), the function documentation also states (emphasis mine):

The sixth, seventh, eighth and ninth parameters (xStep, xFinal, xValue and xInverse) passed to sqlite3_create_window_function are pointers to C-language callbacks that implement the new function.

I'm curious as to how you're expecting the PHP equivalent to work. What exactly are the parameters you're passing to $db->createWindowFunction()?

2021-06-19
08:23 Post: PoC CLI `-param` option (artifact: b01049dca8 user: lexfiend)

As described in a previous comment, here are patches against v3.36.0 for a new -param NAME VALUE option, to bring shell support for bound parameters in line with pretty much every other SQLite binding. With this, one only has to deal with caller (shell) quoting issues when submitting parameter values, without having to also wrestle with SQL quoting that comes with .param set NAME VALUE.

That said, there are a couple of restrictions for -param over .param set:

  1. All values passed via -param are bound as strings, since that's the only data type the shell deals with. SQLite's dynamic typing makes this largely irrelevant when applying the parameters, but just so you know.

  2. All values passed via -param are literals, exactly as if it were passed via sqlite3_bind_XXX or its equivalent in other languages. If you need the value to be evaluated as an SQL expression (e.g. reading in a file as a BLOB), you want .param set.

Here's an example that illustrates the ramifications:

$ sqlite3 -table -header \
  -param :name Bob -cmd '.param set :nick Abe' \
  -param :age 52.2 -cmd '.param set :children 2.5' \
  -param :pc_str "readfile('sqlite3.pc')" -cmd ".param set :pc_blob readfile('sqlite3.pc')" \
  -param :greeting "\"Hi, I'm Bob,\" said he." -cmd '.param list' \
  :memory: \
  'select *,typeof(value) from sqlite_parameters; select (:age + 3), (:children - 1)'

:age      '52.2'
:children 2.5
:greeting '"Hi, I''m Bob," said he.'
:name     'Bob'
:nick     'Abe'
:pc_blob  X'23205061636B61676520496E666F726D6174696F6E20666F7220706B672D636F6E6669670A0A7072656669783D2F7573722F6C6F63616C0A657865635F7072656669783D247B7072656669787D0A6C69626469723D247B657865635F7072656669787D2F6C69620A696E636C7564656469723D247B7072656669787D2F696E636C7564650A0A4E616D653A2053514C6974650A4465736372697074696F6E3A2053514C20646174616261736520656E67696E650A56657273696F6E3A20332E33362E300A4C6962733A202D4C247B6C69626469727D202D6C73716C697465330A4C6962732E707269766174653A202D6C6D202D6C646C202D6C7A202D6C70746872656164200A43666C6167733A202D49247B696E636C7564656469727D0A'
:pc_str   'readfile(''sqlite3.pc'')'

+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
|    key    |                                                                                                                                             value                                                                                                                                              | typeof(value) |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
| :age      | 52.2                                                                                                                                                                                                                                                                                           | text          |
| :children | 2.5                                                                                                                                                                                                                                                                                            | real          |
| :greeting | "Hi, I'm Bob," said he.                                                                                                                                                                                                                                                                        | text          |
| :name     | Bob                                                                                                                                                                                                                                                                                            | text          |
| :nick     | Abe                                                                                                                                                                                                                                                                                            | text          |
| :pc_blob  | # Package Information for pkg-config

prefix=/usr/local
exec_prefix=${prefix}
libdir=${exec_prefix}/lib
includedir=${prefix}/include

Name: SQLite
Description: SQL database engine
Version: 3.36.0
Libs: -L${libdir} -lsqlite3
Libs.private: -lm -ldl -lz -lpthread 
Cflags: -I${includedir}
 | blob          |
| :pc_str   | readfile('sqlite3.pc')                                                                                                                                                                                                                                                                         | text          |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+

+------------+-----------------+
| (:age + 3) | (:children - 1) |
+------------+-----------------+
| 55.2       | 1.5             |
+------------+-----------------+

Critiques welcome.


Patch against sqlite-src-3360000:

diff --git a/src/shell.c.in b/src/shell.c.in
index 0f8de61..4c1ebfa 100644
--- a/src/shell.c.in
+++ b/src/shell.c.in
@@ -10780,6 +10780,7 @@ static const char zOptions[] =
   "   -nofollow            refuse to open symbolic links to database files\n"
   "   -nullvalue TEXT      set text string for NULL values. Default ''\n"
   "   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory\n"
+  "   -param NAME VALUE    bind parameter NAME to VALUE\n"
   "   -quote               set output mode to 'quote'\n"
   "   -readonly            open the database read-only\n"
   "   -separator SEP       set output column separator. Default: '|'\n"
@@ -11026,6 +11027,9 @@ int SQLITE_CDECL wmain(int argc, wchar_t **wargv){
      || strcmp(z,"-cmd")==0
     ){
       (void)cmdline_option_value(argc, argv, ++i);
+    }else if( strcmp(z,"-param")==0 ){
+      i += 2;
+      (void)cmdline_option_value(argc, argv, i);
     }else if( strcmp(z,"-init")==0 ){
       zInitFile = cmdline_option_value(argc, argv, ++i);
     }else if( strcmp(z,"-batch")==0 ){
@@ -11319,6 +11323,36 @@ int SQLITE_CDECL wmain(int argc, wchar_t **wargv){
           if( bail_on_error ) return rc;
         }
       }
+    }else if( strcmp(z,"-param")==0 ){
+      char *pname, *pval;
+      sqlite3_stmt *pStmt;
+      pname = cmdline_option_value(argc,argv,++i);
+      pval = cmdline_option_value(argc,argv,++i);
+      open_db(&data, 0);
+      bind_table_init(&data);
+      rc = sqlite3_prepare_v2(data.db,
+                   "REPLACE INTO temp.sqlite_parameters(key,value)"
+                   "VALUES(?,?);", -1, &pStmt, 0);
+      if( rc!=SQLITE_OK ){
+        utf8_printf(data.out, "/**** ERROR(param_prepare): (%d) %s *****/\n", rc,
+                    sqlite3_errmsg(data.db));
+      }else{
+        rc = sqlite3_bind_text(pStmt, 1, pname, -1, SQLITE_STATIC);
+        if( rc!=SQLITE_OK ){
+          utf8_printf(data.out, "/**** ERROR(param_bind_name): (%d) %s *****/\n", rc,
+                      sqlite3_errmsg(data.db));
+          goto param_cleanup;
+        }
+        rc = sqlite3_bind_text(pStmt, 2, pval, -1, SQLITE_STATIC);
+        if( rc!=SQLITE_OK ){
+          utf8_printf(data.out, "/**** ERROR(param_bind_value): (%d) %s *****/\n", rc,
+                      sqlite3_errmsg(data.db));
+          goto param_cleanup;
+        }
+        sqlite3_step(pStmt);
+param_cleanup:
+        sqlite3_finalize(pStmt);
+      }
 #if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB)
     }else if( strncmp(z, "-A", 2)==0 ){
       if( nCmd>0 ){

Patch against sqlite-amalgamation-3360000 or sqlite-autoconf-3360000:

diff --git a/shell.c b/shell.c
index bf5be5c..b983a92 100644
--- a/shell.c
+++ b/shell.c
@@ -21722,6 +21722,7 @@ static const char zOptions[] =
   "   -nofollow            refuse to open symbolic links to database files\n"
   "   -nullvalue TEXT      set text string for NULL values. Default ''\n"
   "   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory\n"
+  "   -param NAME VALUE    bind parameter NAME to VALUE\n"
   "   -quote               set output mode to 'quote'\n"
   "   -readonly            open the database read-only\n"
   "   -separator SEP       set output column separator. Default: '|'\n"
@@ -21968,6 +21969,9 @@ int SQLITE_CDECL wmain(int argc, wchar_t **wargv){
      || strcmp(z,"-cmd")==0
     ){
       (void)cmdline_option_value(argc, argv, ++i);
+    }else if( strcmp(z,"-param")==0 ){
+      i += 2;
+      (void)cmdline_option_value(argc, argv, i);
     }else if( strcmp(z,"-init")==0 ){
       zInitFile = cmdline_option_value(argc, argv, ++i);
     }else if( strcmp(z,"-batch")==0 ){
@@ -22261,6 +22265,36 @@ int SQLITE_CDECL wmain(int argc, wchar_t **wargv){
           if( bail_on_error ) return rc;
         }
       }
+    }else if( strcmp(z,"-param")==0 ){
+      char *pname, *pval;
+      sqlite3_stmt *pStmt;
+      pname = cmdline_option_value(argc,argv,++i);
+      pval = cmdline_option_value(argc,argv,++i);
+      open_db(&data, 0);
+      bind_table_init(&data);
+      rc = sqlite3_prepare_v2(data.db,
+                   "REPLACE INTO temp.sqlite_parameters(key,value)"
+                   "VALUES(?,?);", -1, &pStmt, 0);
+      if( rc!=SQLITE_OK ){
+        utf8_printf(data.out, "/**** ERROR(param_prepare): (%d) %s *****/\n", rc,
+                    sqlite3_errmsg(data.db));
+      }else{
+        rc = sqlite3_bind_text(pStmt, 1, pname, -1, SQLITE_STATIC);
+        if( rc!=SQLITE_OK ){
+          utf8_printf(data.out, "/**** ERROR(param_bind_name): (%d) %s *****/\n", rc,
+                      sqlite3_errmsg(data.db));
+          goto param_cleanup;
+        }
+        rc = sqlite3_bind_text(pStmt, 2, pval, -1, SQLITE_STATIC);
+        if( rc!=SQLITE_OK ){
+          utf8_printf(data.out, "/**** ERROR(param_bind_value): (%d) %s *****/\n", rc,
+                      sqlite3_errmsg(data.db));
+          goto param_cleanup;
+        }
+        sqlite3_step(pStmt);
+param_cleanup:
+        sqlite3_finalize(pStmt);
+      }
 #if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB)
     }else if( strncmp(z, "-A", 2)==0 ){
       if( nCmd>0 ){

2021-06-17
04:42 Edit reply: Extract files from sqlar with a directory prepended (artifact: 47d8ae0d9c user: lexfiend)

Yes, there's nothing magical about it...once you've figured out the quoting rules of both the shell and SQLite.

Take the last string I mentioned, for instance:

$ sqlite3
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode column
sqlite> .param set :quote "\"I'm full\", said Bob."
sqlite> select * from sqlite_parameters;
key     value                
------  ---------------------
:quote  "I'm full", said Bob.

Right off the bat, it's an exception to the "single-quote for strings (double up single quotes internally), double-quote for identifiers" rule of thumb that's been SQL lore since forever.

Setting that aside, it looks like this on the command line:

$ sqlite3 -cmd ".param set :quote \"\\\"I'm full\\\", said Bob.\"" :memory 'select * from sqlite_parameters'

which isn't entirely unmanageable, but then hard-coded parameters aren't all that useful. It's much more likely that the value comes from user input, or from a file:

$ read quote
# user types: "I'm full", said Bob.

# This absolutely will not work, but only experienced shell folks will know why...and how to fix it.
$ sqlite3 -cmd ".param set :quote \"${quote}\"" :memory 'select * from sqlite_parameters'

sqlite3_bind(), and similar facilities in almost every SQL language binding, largely remove the need to deal with SQL quoting issues. The fact that sqlite3 has parameter substitution facilities, but forces both string substitution and SQL parsing to populate them, seems like an "impedance mismatch" to me:

$ read quote
# user types: "I'm full", said Bob.

$ sqlite3 -param :quote "$quote" :memory: 'select * from sqlite_parameters'
# No muss, no fuss, no errors.

Two questions:

  1. Am I missing something here?
  2. If not, any objections to a patch to enable the above facility?
04:40 Reply: Extract files from sqlar with a directory prepended (artifact: 060af37a97 user: lexfiend)

Yes, there's nothing magical about it...once you've figured out the quoting rules of both the shell and SQLite.

Take the last string I mentioned, for instance:

$ sqlite3
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode column
sqlite> .param set :quote "\"I'm full\", said Bob."
sqlite> select * from sqlite_parameters;
key     value                
------  ---------------------
:quote  "I'm full", said Bob.

Right off the bat, it's an exception to the "single-quote for strings (double up single quotes internally), double-quote for identifiers" rule of thumb that's been SQL lore since forever.

Setting that aside, it looks like this on the command line:

$ sqlite3 -cmd ".param set :quote \"\\\"I'm full\\\", said Bob." :memory 'select * from sqlite_parameters'

which isn't entirely unmanageable, but then hard-coded parameters aren't all that useful. It's much more likely that the value comes from user input, or from a file:

$ read quote
# user types: "I'm full", said Bob.

# This absolutely will not work, but only experienced shell folks will know why...and how to fix it.
$ sqlite3 -cmd ".param set :quote \"${quote}\"" :memory 'select * from sqlite_parameters'

sqlite3_bind(), and similar facilities in almost every SQL language binding, largely remove the need to deal with SQL quoting issues. The fact that sqlite3 has parameter substitution facilities, but forces both string substitution and SQL parsing to populate them, seems like an "impedance mismatch" to me:

$ read quote
# user types: "I'm full", said Bob.

$ sqlite3 -param :quote "$quote" :memory: 'select * from sqlite_parameters'
# No muss, no fuss, no errors.

Two questions:

  1. Am I missing something here?
  2. If not, any objections to a patch to enable the above facility?
2021-06-16
16:10 Reply: Extract files from sqlar with a directory prepended (artifact: 3d8098ef3d user: lexfiend)

While technically true, note that using -cmd forces the same sort of string substitution that make security-conscious folks very nervous indeed, and can fail even if security were not a concern.

For instance, strings with embedded single quotes break .param if not passed in a somewhat non-obvious way:

$ sqlite3 -cmd ".param set :name 'Bobby's Tavern'" :memory: 'select * from sqlite_parameters'
.parameter CMD ...       Manage SQL parameter bindings
   clear                   Erase all bindings
   init                    Initialize the TEMP table that holds bindings
   list                    List the current parameter bindings
   set PARAMETER VALUE     Given SQL parameter PARAMETER a value of VALUE
                           PARAMETER should start with one of: $ : @ ?
   unset PARAMETER         Remove PARAMETER from the binding table
Error: no such table: sqlite_parameters

# How about the standard SQL practice of doubling internal quotes?
$ sqlite3 -cmd ".param set :name 'Bobby''s Tavern'" :memory: 'select * from sqlite_parameters'
.parameter CMD ...       Manage SQL parameter bindings
   clear                   Erase all bindings
   init                    Initialize the TEMP table that holds bindings
   list                    List the current parameter bindings
   set PARAMETER VALUE     Given SQL parameter PARAMETER a value of VALUE
                           PARAMETER should start with one of: $ : @ ?
   unset PARAMETER         Remove PARAMETER from the binding table
Error: no such table: sqlite_parameters

# OK, will double quotes work?
$ sqlite3 -cmd ".param set :name \"Bobby's Tavern\"" :memory: 'select * from sqlite_parameters'
:name|Bobby's Tavern

And if the string you want to substitute contains both single and double quotes? Have fun.

With my hypothetical -param, strings with pretty much any content can be passed safely to your SQLite queries:

sqlite3 -param :name "Bobby's Tavern" ...
sqlite3 -param '$quote' "\"I'm full,\" said Jane." ...
03:03 Reply: Extract files from sqlar with a directory prepended (artifact: 6c62ed45ba user: lexfiend)

If my understanding of https://sqlite.org/cli.html#sql_parameters is correct, I think it is to specify the parameters in a sqlite3 script or session. But I need the parameters in the command line just like the -C option of sqlite3 -A.

If you're saying "I need something like sqlite3 -Axf my.sqlar --param \$dir=/my/dir --param \$dirOnly=42 to work", then no, that's not currently a supported use case, and the SQLite shell's archive logic will substitute its own values anyway.

For the general (hypothetical) case of:

sqlite3 -param '$a' Bob -param '$b' 42 my.db 'SELECT * FROM t WHERE name = $a AND age = $b'

that's for Dr. Hipp and the other maintainers to decide if they want to support this. Until then, you'll have to write your own script that feeds sqlite3 with additional parameter table instructions, or fully-expanded queries.

2021-06-15
02:40 Reply: Extract files from sqlar with a directory prepended (artifact: ee2c06e599 user: lexfiend)

See https://sqlite.org/cli.html#sql_parameters. You'll have to generate more code to create and populate the necessary temp table just to support parameters, so I don't think it's worth the trouble.

Is there a reason you can't simply generate the fully-expanded sqlar query directly?

2021-06-14
16:44 Reply: Extract files from sqlar with a directory prepended (artifact: bbec44fa25 user: lexfiend)

What do you mean? I don't see how to print the first ($dir || name).

The use of --verbose is documented in the help text:

$ sqlite3 -A --help
.archive ...             Manage SQL archives
   Each command must have exactly one of the following options:
     -c, --create               Create a new archive
     -u, --update               Add or update files with changed mtime
     -i, --insert               Like -u but always add even if unchanged
     -t, --list                 List contents of archive
     -x, --extract              Extract files from archive
   Optional arguments:
     -v, --verbose              Print each filename as it is processed
     -f FILE, --file FILE       Use archive FILE (default is current db)
     -a FILE, --append FILE     Open FILE using the apndvfs VFS
     -C DIR, --directory DIR    Read/extract files from directory DIR
     -n, --dryrun               Show the SQL that would have occurred
   Examples:
     .ar -cf ARCHIVE foo bar  # Create ARCHIVE from files foo and bar
     .ar -tf ARCHIVE          # List members of ARCHIVE
     .ar -xvf ARCHIVE         # Verbosely extract files from ARCHIVE
   See also:
      http://sqlite.org/cli.html#sqlar_archive_support

So:

# Extract with long options
$ sqlite3 test.sqlar -A --extract --directory ~/tmp /etc/logrotate.conf
# nothing printed

$ ls -l ~/tmp/etc/logrotate.conf 
-rw-r--r-- 1 aho aho 533 Jan 21  2019 /home/aho/tmp/etc/logrotate.conf

# Let's clean up and try again in verbose mode, this time with short options
$ rm -f ~/tmp/etc/logrotate.conf

$ sqlite3 test.sqlar -AxvC ~/tmp /etc/logrotate.conf
/home/aho/tmp//etc/lo$ ls -l ~/tmp/etc/logrotate.conf 

$ ls -l ~/tmp/etc/logrotate.conf 
-rw-r--r-- 1 aho aho 533 Jan 21  2019 /home/aho/tmp/etc/logrotate.conf

How to run the SQL commands in an sqlite3 session without using -Ax. I tried the following. But no files are written.

SELECT ($dir || name), writefile(($dir || name), sqlar_uncompress(data, sz), mode, mtime) FROM sqlar WHERE (1) AND (data IS NULL OR $dirOnly = 0);

$dir and $dirOnly are substituted internally in the SQLite shell. I've already explained the semantics of both parameters in an earlier reply.

Here's the actual query issued by the above extract, as revealed by the arExtractCommand() source pointed to in my earlier reply, and confirmed via a --dryrun run:

$ rm -f ~/tmp/etc/logrotate.conf

$ sqlite test.sqlar

sqlite> SELECT
   ...> ('/home/aho/tmp/' || name),
   ...> writefile(('/home/aho/tmp/' || name), sqlar_uncompress(data, sz), mode, mtime)
   ...> FROM sqlar WHERE (name = '/etc/logrotate.conf' OR substr(name,1,20) = '/etc/logrotate.conf/') AND (data IS NULL OR 0 = 0)
   ...> AND name NOT GLOB '*..[/\]*';
/home/aho/tmp//etc/logrotate.conf|533

sqlite> .quit

$ ls -l ~/tmp/etc/logrotate.conf 
-rw-r--r-- 1 aho aho 533 Jan 21  2019 /home/aho/tmp/etc/logrotate.conf

$ sqlite3 test.sqlar -AxnC ~/tmp /etc/logrotate.conf
SELECT  ($dir || name), writefile(($dir || name), sqlar_uncompress(data, sz), mode, mtime) FROM sqlar WHERE ( name = '/etc/logrotate.conf' OR substr(name,1,20) = '/etc/logrotate.conf/') AND (data IS NULL OR $dirOnly = 0) AND name NOT GLOB '*..[/\]*'
SELECT  ($dir || name), writefile(($dir || name), sqlar_uncompress(data, sz), mode, mtime) FROM sqlar WHERE ( name = '/etc/logrotate.conf' OR substr(name,1,20) = '/etc/logrotate.conf/') AND (data IS NULL OR $dirOnly = 0) AND name NOT GLOB '*..[/\]*'

03:40 Post: Bug: incorrect filter expression in arExtractCommand() (artifact: 6e9aa98e62 user: lexfiend)

https://www.sqlite.org/src/info?name=586493be0d3a2fc1e6803577d683697dfefc0fb305cc966bb389ce4045cbc19d&ln=6384-6390 reads:

static int arExtractCommand(ArCommand *pAr){
  const char *zSql1 = 
    "SELECT "
    " ($dir || name),"
    " writefile(($dir || name), %s, mode, mtime) "
    "FROM %s WHERE (%s) AND (data IS NULL OR $dirOnly = 0)"
    " AND name NOT GLOB '*..[/\\]*'";
If that last filter condition is meant to block directory traversal attacks, it should probably be:
    " AND name NOT GLOB '..[/\\]*' AND name NOT GLOB '*[/\\]..[/\\]*'";
i.e. separately match ../* and */../*. Otherwise, valid paths like And so it begins.../script.txt will be blocked:

$ sqlite3
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE test(name TEXT);

sqlite> INSERT INTO test VALUES ('And so it begins.../script.txt');

sqlite> SELECT * FROM test WHERE name NOT GLOB '*..[/\]*';

sqlite> SELECT * FROM test WHERE name NOT GLOB '..[/\]*' AND name NOT GLOB '*[/\]..[/\]*';
And so it begins.../script.txt
03:22 Edit reply: Extract files from sqlar with a directory prepended (artifact: e213cc4c03 user: lexfiend)

See https://sqlite.org/cli.html#sqlite_archive_support, particularly the -C option. You also probably want to use the dry-run (-n) option to see the SQL queries that do the actual extraction.

03:17 Reply: Extract files from sqlar with a directory prepended (artifact: f71d541d71 user: lexfiend)

Does ($dir || name) right after SELECT do anything?

It gives you the actual paths extracted, to be printed with --verbose.

So GLOB '*..[/\]*' does match abc../? In other words, this glob can miss some legal paths?

It would seem so. Yes. A more correct filter might be:

name NOT GLOB '..[/\]*' AND name NOT GLOB '*[/\]..[/\]*'

Why is (1) needed?

It's part of the overall fixed-format query because the user can specify paths to extract. Basically, that part of the query means WHERE (<path_filter_condition>), and 1 (or any nonzero value) just says "match 'em all":

$ sqlite3 test.sqlar -Axn 
SELECT  ($dir || name), writefile(($dir || name), sqlar_uncompress(data, sz), mode, mtime) FROM sqlar WHERE (1) AND (data IS NULL OR $dirOnly = 0) AND name NOT GLOB '*..[/\]*'
SELECT  ($dir || name), writefile(($dir || name), sqlar_uncompress(data, sz), mode, mtime) FROM sqlar WHERE (1) AND (data IS NULL OR $dirOnly = 0) AND name NOT GLOB '*..[/\]*'

$ sqlite3 test.sqlar -Axn /etc/logrotate.conf
SELECT  ($dir || name), writefile(($dir || name), sqlar_uncompress(data, sz), mode, mtime) FROM sqlar WHERE ( name = '/etc/logrotate.conf' OR substr(name,1,20) = '/etc/logrotate.conf/') AND (data IS NULL OR $dirOnly = 0) AND name NOT GLOB '*..[/\]*'
SELECT  ($dir || name), writefile(($dir || name), sqlar_uncompress(data, sz), mode, mtime) FROM sqlar WHERE ( name = '/etc/logrotate.conf' OR substr(name,1,20) = '/etc/logrotate.conf/') AND (data IS NULL OR $dirOnly = 0) AND name NOT GLOB '*..[/\]*'

If I want to ignore abspaths, I should add AND NOT GLOB '/*' at the end?

That would be my guess, yes.

2021-06-13
15:42 Reply: INSERT with RETURNING causes duplicate rows (artifact: 78c4bc6148 user: lexfiend)

It's a known and VERY old issue, and the current thinking seems to be "CANTFIX due to fundamental design issues, use PDO_SQLite instead".

In a nutshell, it's fetchArray() that steps through the query again, so DML statements in query() will be re-executed. If you want to stick with the SQLite3 class, it looks like you'll need to use exec() for your INSERT instead, and forego the use of RETURNING.

14:49 Reply: Extract files from sqlar with a directory prepended (artifact: d034f7b1f3 user: lexfiend)

writefile() only has two arguments according to the above page.

That's just an example. The documentation continues:

Note that the readfile(X) and writefile(X,Y) functions are extension functions and are not built into the core SQLite library. These routines are available as a loadable extension in the ext/misc/fileio.c source file in the SQLite source code repositories.

Clicking on the fileio.c link gives you the source code with the full documentation of the writefile() function. Figuring out what those last two arguments do is a simple exercise for the reader.

where dirOnly and dir are defined

In arExtractCommand(). dir would appear to be the directory specified as the argument to the -C option I mentioned before (default: ""), while dirOnly is set to 0 and 1 successively, to first select the files in the archive, then the directories.

Why does the same select command appear twice below?

From the source:

    /* Run the SELECT statement twice. The first time, writefile() is called
    ** for all archive members that should be extracted. The second time,
    ** only for the directories. This is because the timestamps for
    ** extracted directories must be reset after they are populated (as
    ** populating them changes the timestamp).  */

What does name NOT GLOB '*..[/\]*' mean?

I'm pretty sure it's a cheap way to weed out directory traversal attacks like ../../../../../../../etc/passwd.

07:39 Post: Bug: Incorrect archive support URL in help text (artifact: 58cc3a68f6 user: lexfiend)
$ sqlite3 --version
3.35.5 2021-04-19 18:32:05 1b256d97b553a9611efca188a3d995a2fff712759044ba480f9a0c9e98fae886

$ sqlite3 -A --help
.archive ...             Manage SQL archives
   Each command must have exactly one of the following options:
     -c, --create               Create a new archive
     -u, --update               Add or update files with changed mtime
     -i, --insert               Like -u but always add even if unchanged
     -t, --list                 List contents of archive
     -x, --extract              Extract files from archive
   Optional arguments:
     -v, --verbose              Print each filename as it is processed
     -f FILE, --file FILE       Use archive FILE (default is current db)
     -a FILE, --append FILE     Open FILE using the apndvfs VFS
     -C DIR, --directory DIR    Read/extract files from directory DIR
     -n, --dryrun               Show the SQL that would have occurred
   Examples:
     .ar -cf ARCHIVE foo bar  # Create ARCHIVE from files foo and bar
     .ar -tf ARCHIVE          # List members of ARCHIVE
     .ar -xvf ARCHIVE         # Verbosely extract files from ARCHIVE
   See also:
      http://sqlite.org/cli.html#sqlar_archive_support

The correct URL is https://sqlite.org/cli.html#sqlite_archive_support

07:35 Reply: Extract files from sqlar with a directory prepended (artifact: ad0f5049b9 user: lexfiend)

See https://sqlite.org/cli.html#sqlite_archive_support , particularly the -C option. You also probably want to use the dry-run (-n) option to see what will be done when you extract for real.

2021-03-20
02:54 Post: FTS build docs inconsistency (artifact: 3b423b4adc user: lexfiend)

https://sqlite.org/fts3.html#compiling_and_enabling_fts3_and_fts4 says:

Note that enabling FTS3 also makes FTS4 available. There is not a separate SQLITE_ENABLE_FTS4 compile-time option. A build of SQLite either supports both FTS3 and FTS4 or it supports neither.

But https://sqlite.org/compile.html#_options_to_enable_features_normally_turned_off defines both SQLITE_ENABLE_FTS3 and SQLITE_ENABLE_FTS4, and the FTS4 macro doc mentions adding both versions to the build, while the FTS3 macro doc only mentions version 3.

And from the amalgamation:

/*
** FTS4 is really an extension for FTS3.  It is enabled using the
** SQLITE_ENABLE_FTS3 macro.  But to avoid confusion we also all
** the SQLITE_ENABLE_FTS4 macro to serve as an alisse for SQLITE_ENABLE_FTS3.
*/

I don't use FTS myself, but I'll assume that the two compile-time options in fact enable exactly the same functionality, so can I suggest the following edits for clarity?

https://sqlite.org/fts3.html#compiling_and_enabling_fts3_and_fts4:

Note that enabling FTS3 also makes FTS4 available. There is not a separate SQLITE_ENABLE_FTS4 compile-time option. A build of SQLite either supports both FTS3 and FTS4 or it supports neither.

https://sqlite.org/compile.html#_options_to_enable_features_normally_turned_off:

SQLITE_ENABLE_FTS3

When this option is defined in the amalgamation, versions 3 and 4 of the full-text search engine is added to the build automatically.

SQLITE_ENABLE_FTS4

This is an alias for SQLITE_ENABLE_FTS3.
2021-02-03
08:10 Reply: SQLite3 Array? (artifact: 0146dd5489 user: lexfiend)

I think ddevienne's point is that the official release of 3.34.0 was three months after your build, so it's technically not "real" 3.34.0.

2020-12-07
15:37 Reply: sqlite3_prepare() (artifact: 644894f652 user: lexfiend)

My reason for asking question 2 above is that ?v3() is enumerated before ?v2():

And sqlite3_prepare_v2() is enumerated before sqlite3_prepare16_v3(). I see no reason to ascribe an EOL differential based on either observation.

With the "v2" interface, any of the other result codes or extended result codes might be returned as well.

One thing about technical documentation in general: Context is key. The quote you cherry-picked from https://sqlite.org/c3ref/step.html actually states, in full:

In the legacy interface, the return value will be either SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE. With the "v2" interface, any of the other result codes or extended result codes might be returned as well.

which clearly contrasts the legacy interface with "v2", rather than v2" with "v3". (Though I guess a case could be made for adding "v3" to the latter statement for clarity.)

15:27 Reply: sqlite3_prepare() (artifact: d906af29cd user: lexfiend)
  1. What are the differences among the 4 recommended functions?

As the page you quoted says:

The second argument, "zSql", is the statement to be compiled, encoded as either UTF-8 or UTF-16. The sqlite3_prepare(), sqlite3_prepare_v2(), and sqlite3_prepare_v3() interfaces use UTF-8, and sqlite3_prepare16(), sqlite3_prepare16_v2(), and sqlite3_prepare16_v3() use UTF-16.

[...]

sqlite3_prepare_v3() differs from sqlite3_prepare_v2() only in having the extra prepFlags parameter, which is a bit array consisting of zero or more of the SQLITE_PREPARE_* flags. The sqlite3_prepare_v2() interface works exactly the same as sqlite3_prepare_v3() with a zero prepFlags parameter.

So, in a nutshell, UTF-8 vs UTF-16, and prepare flags or lack thereof.

  1. Should I just use sqlite3_prepare_v2() ... is it future proof?

It's about as future proof as anything in SQLite, I guess.

10:19 Edit reply: libreadline problem with Ubuntu 20.04 (artifact: f1715cb41d user: lexfiend)

I expect the binary to have been linked against libraries available in major flavors of Linux.

The latest Debian as I write this (10.7) uses libreadline7. So does CentOS 8. Most people would consider all these, along with Ubuntu 18.04 LTS, "major flavors of Linux".

Since it's not possible to link against multiple versions of the same library, a choice had to be made (more likely, forced upon Dr. Hipp by the build platform). That you were able to get the precompiled binaries working with libraries from a different OS instance was fortunate, but a better option would be to build the amalgamation from source.

09:50 Reply: libreadline problem with Ubuntu 20.04 (artifact: de28ca127f user: lexfiend)

I expect the binary to have been linked against libraries available in major flavors of Linux.

The latest Debian as I write this (10.7) uses libreadline7. So does CentOS 8. So, in fact, does Ubuntu 18.04 LTS, still supported as of this writing. Most people would consider all these "major flavors of Linux".

Since it's not possible to link against multiple versions of the same library, a choice had to be made (more likely, forced upon Dr. Hipp by the build platform). That you were able to get the precompiled binaries working with libraries from a different OS instance was fortunate, but a better option would be to build the amalgamation from source.

2020-10-29
02:14 Reply: Is `JSON` a valid `create table` type? (artifact: f4afdb0a81 user: lexfiend)

That's not necessarily true. Storage-wise, it only makes a difference when dealing with bare numbers -- being of NUMERIC affinity, JSON will trigger a numeric conversion, while TEXT will not. Otherwise, there is no difference with typical JSON objects.

OTOH, declaring a JSON column reminds the user/coder what sort of data is expected to go in there. That can be useful in the long term, especially if you're working in a team. ("You" and "you in six months" are one such team.)

2020-10-08
06:21 Reply: Performance Issue: How can I increase a performance? (artifact: 5580b3c884 user: lexfiend)

I'm pretty sure he literally meant ANALYZE. From https://sqlite.org/lang_analyze.html :

The ANALYZE command gathers statistics about tables and indices and stores the collected information in internal tables of the database where the query optimizer can access the information and use it to help make better query planning choices.

sqlite_analyzer only tells you how bloated your database is, while ANALYZE actually works to improve performance.

2020-09-14
12:57 Reply: Placeholder for table name when inserting data? (artifact: aef5f69a51 user: lexfiend)

Besides Gunter and anonymous' suggestions, you could also consider storing each counter's data in a separate SQLite file. On server-based DBMSs, this is roughly equivalent to "sharding on counter", and the same general considerations apply (e.g. you'd only ever process one counter's data at a time, and JOINS across counters are rare-to-nonexistent).

2020-08-23
11:09 Reply: Kind request: Please make SHA3/? part of built-in functions (artifact: 3565adead4 user: lexfiend)

Why then not incorporate this (SHA3) and all other 'official' extensions in the amalgamation code, disabled by default yet easily available to enable with a simple turn of a compile time define 'switch'?

Because then any bugfix/enhancement of any extension would require publishing an entirely new amalgamation. Would you want to have to track which releases affect the particular extensions you use, and which don't?

10:59 Reply: Weird warning on the Xcode/OSX (artifact: ec318a353d user: lexfiend)

That's likely the Xcode IDE trying to be helpful, and getting it wrong.

To be sure, run the following command to see if the compilation itself is tossing out any warnings:

clang -Wall -c sqlite3.c -o sqlite3.o
On macOS Catalina, the above compiles correctly with no warnings.

2020-08-13
12:21 Reply: Can you export multiple tables into ONE excel file on ONE tab? (artifact: 324188d06f user: lexfiend)

Maybe add selects from the column names before each table's data.

Or just add .headers on after .mode csv.

2020-08-12
04:23 Reply: How can I split a name column into first and last names? (artifact: 9ef4f7718e user: lexfiend)

Real world names are complicated, someone will inevitably write a long reply or post a link on the topic.

ASCII and ye shall receive.

This is just one of the many lists of falsehoods that programmers believe in.

2020-08-07
07:10 Reply: Need help building a custom amalgamation (artifact: 322f1ca6f7 user: lexfiend)

Yes, but there's no guarantee that a random 32-bit libsqlite.so would actually work on your remote machine. For starters, Linux runs on more 32-bit architectures than just Intel x86, especially in the embedded arena.

Even if it's the same architecture, a library built on your typical Linux distro probably won't work on a musl-based remote OS.

So unless you're planning to drop Yet Another SQLite Library on your remote machine, you really should be using what's already available. In fact, if your remote machine is significantly different architecturally from your local one, you should be building your program on the remote box for maximum success.

2020-07-10
02:06 Reply: .selecttrace unknown and strange errors (artifact: cc0ddb4d86 user: lexfiend)

Your script does this for non-debug builds:

        export CFLAGS="-fPIC \
but does this for debug builds:
        export SQLITE3_CFLAGS="-fPIC \
hence my original comment.

The C compiler expects CFLAGS.

Most (all?) C compilers ignore CFLAGS, as they expect build flags on the command line. It's the configure script that captures the value of CFLAGS and modifies the various build control files accordingly.

2020-07-09
09:44 Edit reply: .selecttrace unknown and strange errors (artifact: 36548b3f7f user: lexfiend)

By the way the -DSQLITE options does not work.

If you're talking about your debug build, your build script defines them in SQLITE3_CFLAGS, which isn't recognized.

08:31 Reply: .selecttrace unknown and strange errors (artifact: 1f193e7728 user: lexfiend)

By the way the -DSQLITE options does not work.

If you're talking about your debug build, your build script defines SQLITE3_CFLAGS, which isn't recognized.