.mode box does not format multi-line text nice
(1) By Andreas Kupries (andreas-kupries) on 2022-01-30 14:51:49 [link] [source]
Just noted, for 3.36. Box display goes bonkers for column values which are multi-line text
> sqlite3 /media/akis/heap/management.sqlite
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> .mode box
sqlite> select * from sqlite_master ;
┌───────┬─────────────────────────┬──────────┬──────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ type │ name │ tbl_name │ rootpage │ sql │
├───────┼─────────────────────────┼──────────┼──────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ table │ meta │ meta │ 2 │ CREATE TABLE meta -- general space meta data
( id INTEGER PRIMARY KEY
, key INTEGER NOT NULL UNIQUE
, value TEXT NOT NULL ) │
│ index │ sqlite_autoindex_meta_1 │ meta │ 3 │ │
└───────┴─────────────────────────┴──────────┴──────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
sqlite> .version
SQLite 3.36.0 2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafa66e5
zlib version 1.2.11
gcc-9.3.0
sqlite>
I suspect that the latest versions behave the same.
(2) By Larry Brasfield (larrybr) on 2022-01-30 15:27:11 in reply to 1 [link] [source]
I suspect that the latest versions behave the same.
So do I. Suggested code changes will be gladly considered, particularly if they do not disrupt the existing scheme for handling similar modes.
I intend no snark with this: The guarantees for box mode were always quite limited. It is intended for demos rather than print-ready presentation for arbitrary content.
You might want to use html mode with suitable wrapping to allow display with a browser. It won't preserve newlines, but it won't break up so badly.
For example, something very basic for use on Windows, with no style:
.mode html
.output mytable.html
.print <!doctype html><html><head><title>My Table</title></head><body><table>
select * from sqlite_schema;
.print </table></body></html>
.output
.shell start mytable.html
P.S. The example is not to imply the OP needs such.
(3) By Andreas Kupries (andreas-kupries) on 2022-01-30 16:04:32 in reply to 2 [link] [source]
The guarantees for box mode were always quite limited. It is intended for demos rather than print-ready presentation for arbitrary content.
Fair points.
Thanks.
(4) By Richard Hipp (drh) on 2022-01-30 21:14:35 in reply to 1 [source]
Please try again with the latest trunk check-in and report back any problems you find, preferably with test cases. The new code is only lightly tested.
(5) By Richard Hipp (drh) on 2022-01-30 21:22:05 in reply to 4 [link] [source]
Additional hint: The ".width" command now specifies both the minimum and the maximum width for each column. A value of 0 for the width of any column means "undefined". So a reasonable way to display the schema might be:
.width 0 0 0 0 80 .mode box SELECT * FROM sqlite_schema
The ".width" command above causes the "sql" column to wrap at the 80th column for CREATE statements that are (for example) all on one line.
For a Fossil repository, you might show recent check-ins like this:
.width 0 0 80 .mode box SELECT datetime(mtime) AS 'date', user, comment FROM event WHERE type='ci' ORDER BY mtime DESC LIMIT 50;
(6) By Larry Brasfield (larrybr) on 2022-01-30 22:35:29 in reply to 4 [link] [source]
It works fairly well, and as intended.
The wrapping is abrupt when any line lengths exceed the allotted width. It makes me want a SQL filter function that can do break-at-word boundaries. With that, one could have quite readable text flowing within the boxes.
(7) By Andreas Kupries (andreas-kupries) on 2022-01-31 08:02:04 in reply to 4 [link] [source]
Please try again with the latest trunk check-in and report back any problems you find, preferably with test cases. The new code is only lightly tested.
Oh. Wow. I honestly did not expect you to jump on that, and that quickly. Thank you.
I will see to get it tested here sometime today.
(8) By ddevienne on 2022-01-31 09:07:53 in reply to 4 [link] [source]
Hi Richard. Just curious why the CLI code is mixing free and sqlite3_free.
Just because of the use of strdup? There's no SQLite equivalent?
I'd have thought all memory to be obtained from the same SQLite-managed API.
(9) By Larry Brasfield (larrybr) on 2022-01-31 11:15:25 in reply to 8 [link] [source]
(I'm answering as someone quite familiar with the CLI code.)
On "mixing free and sqlite3_free":
Just because of the use of strdup? There's no SQLite equivalent?
Yes. True, there is no equivalent.
I'd have thought all memory to be obtained from the same SQLite-managed API.
The SQLite library is certainly bound to such a restriction, but the CLI shell is not. Exceptions are made for places where the most expeditious handling of an allocated object is to get it from somethinga that has used malloc(), then free() it later.
a. Such places include getting input lines from readline() or its ilk.
(10) By ddevienne on 2022-01-31 12:11:27 in reply to 9 [link] [source]
Yes. True, there is no equivalent.
Modulo sqlite3_mprintf("%s", arg) :)
Although that's longer than strdup(arg) of course.
The SQLite library is certainly bound to such a restriction, but the CLI shell is not
Fair enough. Thanks Larry.
Although you know I'd really wish for the CLI to be made available as an embedded libary component of its own, on top of the SQLite itself.
It's just too valuable and should be reusable and reused IMHO.
And it could have more leeway in breaking its API than SQLite has,
to not inhibit DRH's liberty to evolve the code base.
(11) By Larry Brasfield (larrybr) on 2022-01-31 12:33:03 in reply to 10 [link] [source]
Yes, that's a strdup() moral equivalent (and I've used it that way.)
Although you know I'd really wish for the CLI to be made available as an embedded libary component of its own, on top of the SQLite itself.
I believe we have another thread going on this topic, which is a fine place to discuss that. Suffice it to say here that embeddability is something that I and Richard support, with certain limitations, and work is progressing on it in parallel with some extensions.
(12) By Richard Hipp (drh) on 2022-01-31 14:13:09 in reply to 1 [link] [source]
Further enhancements to the ".mode" command:
You can now use the "--wrap N" option to specify a width of N characters beyond which the columnar modes wrap. The --wrap only applies to columns that have a ".width" of 0 (meaning no width specified). --wrap is different from .width in that with .width, the value is both the minimum and the maximum width, but with --warp the value is just the maximum width. .width takes precedence over --wrap.
New options "--quote" and "--noquote". The --quote option causes the output values in columnar modes to be SQL literals, as if with ".mode quote".
New ".mode qbox" which is shorthand for ".mode box --wrap 60 --quote". I made this a shortcut in as much as I expect it to be very useful.
The new output options are shown with the ".show" command and with ".mode" with no arguments.
With these changes, it is now quite reasonable to do things like this:
.mode qbox SELECT * FROM sqlite_schema;
Please report any problems.
(13) By Andreas Kupries (andreas-kupries) on 2022-01-31 17:28:35 in reply to 12 [link] [source]
First, thanks again for doing this work.
Did a scratch build from a checkout now and it works as advertised.
That said the new qbox looks to have memory issues.
Example output:
hephaistos:(521) ~/Development/Other/RichardHipp/Sqlite/scratchbuild > ./sqlite3 /media/akis/heap/management.sqlite
SQLite version 3.38.0 2022-01-31 15:59:43
Enter ".help" for usage hints.
sqlite> .version
SQLite 3.38.0 2022-01-31 15:59:43 f8766231d2a77bb8b95726b514736d4c2d20b056f7fe60bdbc98ebf5e5b15ae9
zlib version 1.2.11
gcc-9.3.0
sqlite> .mode qbox
sqlite> select * from sqlite_schema;
┌─────────┬───────────────────────────┬──────────┬──────────┬────────────────────────────────────────────────────────────┐
│ type │ name │ tbl_name │ rootpage │ sql │
├─────────┼───────────────────────────┼──────────┼──────────┼────────────────────────────────────────────────────────────┤
│ 'table' │ 'meta' │ 'meta' │ 2 │ 'CREATE TABLE meta -- general space meta data │
│ │ │ │ │ pace meta data │
│ │ │ │ │ ( id INTEGER PRIMARY KEY │
│ │ │ │ │ , key INTEGER NOT NULL UNIQUE │
│ │ │ │ │ QUE │
│ │ │ │ │ , value TEXT NOT NULL )' │
├─────────┼───────────────────────────┼──────────┼──────────┼────────────────────────────────────────────────────────────┤
│ 'index' │ 'sqlite_autoindex_meta_1' │ 'meta' │ 3 │ NULL │
└─────────┴───────────────────────────┴──────────┴──────────┴────────────────────────────────────────────────────────────┘
sqlite> .q
Segmentation fault (core dumped)
When wrapping a line it seems to repeat the end of the wrapped line on the next line. In the above the examples are
general space meta data // pace meta data
and
UNIQUE // QUE
Given the seg.fault at the end I suspect some kind of pointer misuse.
The .mode box does not exhibit the issue.
Trying the individual options in the qbox, i.e. --quote and --wrap 60 I find the same behaviour as above for --wrap 60 alone.
(14) By Richard Hipp (drh) on 2022-01-31 18:20:56 in reply to 13 [link] [source]
I'm not able to reproduce the problem. Valgrind and ASAN both come up clean for me. Do you have a test case that you can send?
(15) By Andreas Kupries (andreas-kupries) on 2022-01-31 20:10:22 in reply to 14 [link] [source]
Mail sent with attached archive containing a bash script going from compilation to crash for my box, with supporting files (table definition, crashing sql).
Script log and generated binaries (shell, shared lib) also provided. Ditto notes on things I thought unusual (like the use of the tclsh8.7 I have around).
(16) By Richard Hipp (drh) on 2022-01-31 20:44:09 in reply to 15 [link] [source]
I may have already checked in a fix. Please try the latest and let me know.
(18) By Andreas Kupries (andreas-kupries) on 2022-01-31 21:09:06 in reply to 16 [link] [source]
Thank you
Checking ... It works now. Result looks good, and no crash any more.
Yeah, TABs galore in that text.
The origin text is part of Tcl code and quite indented.