Fossil sql: setting .separator has no effect (defaults to comma)
(1) By anonymous on 2020-05-28 19:04:25 [link] [source]
Here's the issue in action.
fossil sql
SQLite version 3.32.1 2020-05-25 16:19:56
Enter ".help" for usage hints.
sqlite> select * from vfile;
1,12,0,0,0,0,8,8,1587129377,'one.txt',NULL,NULL
2,12,0,0,0,0,11,11,1587401372,'three.txt',NULL,NULL
3,12,0,0,0,0,4,4,1587400832,'two.txt',NULL,NULL
sqlite> .separator |
sqlite> select * from vfile;
1,12,0,0,0,0,8,8,1587129377,'one.txt',NULL,NULL
2,12,0,0,0,0,11,11,1587401372,'three.txt',NULL,NULL
3,12,0,0,0,0,4,4,1587400832,'two.txt',NULL,NULL
fossil version
This is fossil version 2.11 [4df919803b] 2020-05-25 23:23:49 UTC
When using sqlite3 client to access the Fossil repo db-file, the .separator setting is respected properly.
(2) By Larry Brasfield (LarryBrasfield) on 2020-05-28 19:22:21 in reply to 1 [link] [source]
In addition to setting the column separator, you need to set the output mode.
For example,
> fossil sql
SQLite version 3.32.1 2020-05-25 16:19:56
Enter ".help" for usage hints.
sqlite> .mode list
sqlite> .separator |
sqlite> select * from localdb.vfile limit 3;
1|73|0|0|0|0|64|64|1587450659|Makefile.msc||
2|73|1|0|0|0|71|71|1587665537|shell.c||
3|73|0|0|0|0|9|9|1586463408|sqlar.c||
sqlite>
(3) By anonymous on 2020-05-28 19:34:50 in reply to 2 [link] [source]
Thanks!
Just for completeness, looks like with "fossil sql" the default mode is "quote".
fossil sql
SQLite version 3.32.1 2020-05-25 16:19:56
Enter ".help" for usage hints.
sqlite> .mode
current output mode: quote
sqlite> .help .mode
...
quote Escape answers as for SQL
What does this mean?
(4) By Larry Brasfield (LarryBrasfield) on 2020-05-28 19:47:01 in reply to 3 [link] [source]
See section 5 of "Command Line Shell For SQLite".
(5) By anonymous on 2020-05-28 19:58:49 in reply to 4 [link] [source]
Quoting from the doc link:
In "quote" mode... All columns are separated from each other by a comma (or whatever alternative character is selected using ".separator").
I guess, this may point to a SQLite bug. Clearly, the .separator setting is ignored in quote mode and defaults to comma.
(6.1) By Larry Brasfield (LarryBrasfield) on 2020-05-28 20:41:18 edited from 6.0 in reply to 5 [source]
Given that documentation claim, it certainly is a bug. The code implementing MODE_Quote output has no provision for using anything except a literal ',' as the separator.
For that reason, I think list mode is a better choice.
Answering an earlier question, ("What does [escape answers as for SQL] mean?"), with section 5's verbiage considered, it appears that quote mode is for producing sequences of literals such as might appear in a SQL VALUES clause. It would make little sense to do SQL-style quoting or other literal rendering with an arbitrary separator, so this "bug" is understandable and should be considered a doc bug rather than a code bug IMHO.
(7) By anonymous on 2020-05-28 21:00:45 in reply to 6.1 [link] [source]
with section 5's verbiage considered, it appears that quote mode is for producing sequences of literals such as might appear in a SQL VALUES clause.
Is it so actually? It does not seem forthcoming from the description. I see how this could be used in VALUES clause, but this describes just output value formatting, not the output sequencr formatting, as it may be heterogenous as well, mixing data-types.
(8) By anonymous on 2020-05-28 21:12:06 in reply to 6.1 [link] [source]
It seems that while the topic is discused, drh is working on fix, Enhance the ".quote" mode in the shell so that it honors .separator..
(9) By Larry Brasfield (LarryBrasfield) on 2020-05-28 21:12:34 in reply to 7 [link] [source]
I believe so. The code uses a switch to emit literals according to the type of the value being output: single-quoted text, '0x'-prefixed hex for blobs, unquoted numeric output for integers and reals, and NULL for nulls. This output, if comma-separated, is precisely what would appear in a VALUES(...) clause to load the same values in an insert statement.
True, this does not seem to be made plain in the documentation or .help output.