Command Line Shell set parameter as date
(1.1) Originally by Bob C (rchapman) with edits by Richard Hipp (drh) on 2021-04-02 16:30:45 from 1.0 [link] [source]
Windows 10 Sqlite 3.35.3 2021-03-26
This is probably an artifact of my ignorance but how can you set a parameter's value to a date string (e.g. '2021-03-01') without having it converted to a number (e.g. '2017')
sqlite> .param init sqlite> .param set :date 2021-03-01 sqlite> .param list :date 2017 sqlite> .param set :date "2021-03-01" sqlite> .param list :date 2017 sqlite> .param set :date '2021-03-01' sqlite> .param list :date 2017
How about this:
.param set :date "'2021-03-01'"
Yes! Shame on me -- blush! I should have tried a few more combinations myself and avoided 'noise' on the forum. ;o However it does give me a chance to thank you for Sqlite and express my admiration for your many contributions and my appreciation of the mentorship you provide through this forum and your presentations. Thanks Dr Hipp!
I couldn't help experimenting a little:
▶▶▶ .param init ▶▶▶ .param set :bond "'007'" ▶▶▶ .param list :bond 7
Uh-oh. What happened to the double-oh in double-oh seven? Well, it seems the answer lies here:
▶▶▶ .schema sqlite_parameters CREATE TABLE temp.sqlite_parameters( key TEXT PRIMARY KEY, value ANY ) WITHOUT ROWID;
Which raises the question: Why was the type of the value here specified as
ANY, which by the rules given in section 3.1 of the Datatypes doc means it has
INTEGER affinity? Was it intentional?
If no type is provided (that is, use BLOB, or no affinity conversion) then it works more properly.
That's an excellent question. I suspect the value's type affinity was intended to actually be "any". It works better that way (as Keith says.)
It would be helpful if you and/or others would make the best argument for retaining the current behavior for the sake of backward compatibility. I am inclined to think that where anybody has somehow come to rely on the current behavior, it is only by accident or extremely contrived usage, and that avoiding anomalies such as your "Uh-oh" will, overall, produce fewer problems for shell users. (It helps that .parameter is a relatively recent addition.)
Honestly, I can't think of any reason to retain the current behaviour, nor how anybody could be relying on it. Sorry to disappoint. ;-)
Anyhow, I compiled a version patched as follows:
--- src/shell.c.in +++ src/shell.c.in @@ -2913,11 +2913,11 @@ sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, -1, &wrSchema); sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, 1, 0); sqlite3_exec(p->db, "CREATE TABLE IF NOT EXISTS temp.sqlite_parameters(\n" " key TEXT PRIMARY KEY,\n" - " value ANY\n" + " value\n" ") WITHOUT ROWID;", 0, 0, 0); sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, wrSchema, 0); sqlite3_db_config(p->db, SQLITE_DBCONFIG_DEFENSIVE, defensiveMode, 0); }
And it does not show the anomaly.
Marginally related: Since I haven't found any documentation for how dot commands are interpreted, I consulted the source. As far as I can figure it out, the rules are as follows:
You may quote an argument with single or double quotes. If double quotes, backslash escapes are interpreted. If not quoted, the argument ends at the next space. If quoted, the quotes are stripped, and the argument is the result.
If the command is
.parameters set, the value argument is first put verbatim into a
REPLACE INTO sqlite_parameters statement. If that fails, the statement is run again, but now with the value argument quoted as a string. As a result the following dot commands will all insert the integer 7:
.param set :x 007 .param set :x '007' .param set :x "007"
while the command
will indeed insert the string
.param set :x "'007'"
'007'(assuming the patch above). This is pretty hard to guess, so it might be good to document it.
Since many of the parameters I want to use are TEXT I would really like to see this changed(fixed? ;o). Is there a workaround?
[I really value this forum. It provides so many opportunities to learn!!]
One workaround would be to execute this:
PRAGMA writable_schema = 1;
CREATE TABLE temp.sqlite_parameters(key TEXT PRIMARY KEY, value) WITHOUT ROWID;
before the same table is created by use of .parameter init or set. This could be done in a file named .sqliterc in one's home directory.
Another is to delete the word "ANY" from the table creation statement in the shell source and (re)build the shell.
Another is to build the shell from the current trunk tip, or wait until the next release.
Already fixed before I wrote my previous reply, I notice. Very good. I should have pulled before running my experiment.
(But the documentation for how the arguments to dot commands are parsed could still be improved. See my reply above.)
I agree regarding the quoting rules. Those should always be clear, IMHO. We'll see some improvement soon.
Note that in the CLI you will also have to turn off
defensive mode if it is turned on before you can do this because
defensive mode provides additional suspenders to prevent illicit modification of the database.
SQLite version 3.36.0 2021-04-03 23:27:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> PRAGMA writable_schema = 1; sqlite> CREATE TABLE temp.sqlite_parameters(key TEXT PRIMARY KEY, value) WITHOUT ROWID; Error: object name reserved for internal use: sqlite_parameters sqlite> .dbconfig defensive on dqs_ddl off dqs_dml off enable_fkey on enable_qpsg off enable_trigger on enable_view on fts3_tokenizer off legacy_alter_table off legacy_file_format off load_extension on no_ckpt_on_close off reset_database off trigger_eqp off trusted_schema off writable_schema on sqlite> .dbconfig defensive off defensive off sqlite> CREATE TABLE temp.sqlite_parameters(key TEXT PRIMARY KEY, value) WITHOUT ROWID; sqlite> .dbconfig defensive on defensive on sqlite> PRAGMA writable_schema = 0; sqlite>
Note that the
.param init basically follows the above sequence internally. It enables
writable_schema, turns off
defensive mode, creates the table, then restores
defensive mode to the state they were in before executing the command.