paulhorn/sqlite
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Files
session-fixes
sqlite/test/qrf01.test
T
drh abc82c49c2 Revamp the QRF insert-mode optimization of [659ff6ab55802507]. The 
control value is now nMultiInsert.  Multiple rows are added to each INSERT
statement until the number of bytes in that statement exceeds the
nMultiInsert value.  The CLI uses a default value of 3000, which provides
a good balance between speed and prepare-statement size.  The output
from ".dump" now loads nearly 2x faster in some cases.

FossilOrigin-Name: 55b0ce9f93d68b17a18423f0f751b778573127743a2bac67abfea074ac32d41a
2026-03-10 18:13:13 +00:00

1187 lines
43 KiB
Plaintext
Raw Permalink Blame History

# 2025-11-05
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test cases for the Query Result Formatter (QRF)
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix qrf01
do_execsql_test 1.0 {
CREATE TABLE t1(a, b, c);
INSERT INTO t1 VALUES(1,2.5,'three'),(x'424c4f42',NULL,'Ἀμήν');
}
do_test 1.10 {
set result "\n[db format {SELECT * FROM t1}]"
} {
╭──────┬─────┬───────╮
│ a │ b │ c │
╞══════╪═════╪═══════╡
│ 1 │ 2.5 │ three │
│ BLOB │ │ Ἀμήν │
╰──────┴─────┴───────╯
}
do_test 1.11a {
set result "\n[db format -title off {SELECT * FROM t1}]"
} {
╭──────┬─────┬───────╮
│ 1 │ 2.5 │ three │
│ BLOB │ │ Ἀμήν │
╰──────┴─────┴───────╯
}
do_test 1.11b {
set result "\n[db format -text sql {SELECT * FROM t1}]"
} {
╭─────────────┬─────┬─────────╮
│ a │ b │ c │
╞═════════════╪═════╪═════════╡
│ 1 │ 2.5 │ 'three' │
│ x'424c4f42' │ │ 'Ἀμήν' │
╰─────────────┴─────┴─────────╯
}
do_test 1.11c {
set result "\n[db format -text sql -border off {SELECT * FROM t1}]"
} {
a │ b │ c
═════════════╪═════╪═════════
1 │ 2.5 │ 'three'
x'424c4f42' │ │ 'Ἀμήν'
}
do_test 1.11d {
set result "\n[db format -text relaxed -blob sql -border off \
{SELECT * FROM t1}]"
} {
a │ b │ c
═════════════╪═════╪═══════
1 │ 2.5 │ three
x'424c4f42' │ │ Ἀμήν
}
do_test 1.12 {
set result "\n[db format -text csv {SELECT * FROM t1}]"
} {
╭────────────────────┬─────┬────────╮
│ a │ b │ c │
╞════════════════════╪═════╪════════╡
│ 1 │ 2.5 │ three │
│ "\102\114\117\102" │ │ "Ἀμήν" │
╰────────────────────┴─────┴────────╯
}
do_test 1.13 {
set result "\n[db format -text csv -blob hex {SELECT * FROM t1}]"
} {
╭──────────┬─────┬────────╮
│ a │ b │ c │
╞══════════╪═════╪════════╡
│ 1 │ 2.5 │ three │
│ 424c4f42 │ │ "Ἀμήν" │
╰──────────┴─────┴────────╯
}
do_test 1.14 {
catch {db format -text unk -blob hex {SELECT * FROM t1}} res
set res
} {bad -text "unk": must be auto, csv, html, json, plain, relaxed, sql, or tcl}
do_test 1.15 {
catch {db format -text sql -blob unk {SELECT * FROM t1}} res
set res
} {bad BLOB encoding (-blob) "unk": must be auto, hex, json, tcl, text, sql, or size}
do_test 1.16 {
catch {db format -text sql -style unk {SELECT * FROM t1}} res
set res
} {bad format style (-style) "unk": must be auto, box, column, count, csv, eqp, explain, html, insert, jobject, json, line, list, markdown, quote, stats, stats-est, stats-vm, or table}
do_test 1.20 {
set result "\n[db format -style box {SELECT * FROM t1}]"
} {
╭──────┬─────┬───────╮
│ a │ b │ c │
╞══════╪═════╪═══════╡
│ 1 │ 2.5 │ three │
│ BLOB │ │ Ἀμήν │
╰──────┴─────┴───────╯
}
do_test 1.30 {
set result "\n[db format -style table {SELECT * FROM t1}]"
} {
+------+-----+-------+
| a | b | c |
+------+-----+-------+
| 1 | 2.5 | three |
| BLOB | | Ἀμήν |
+------+-----+-------+
}
do_test 1.31 {
set result "\n[db format -style table -title off {SELECT * FROM t1}]"
} {
+------+-----+-------+
| 1 | 2.5 | three |
| BLOB | | Ἀμήν |
+------+-----+-------+
}
do_test 1.32 {
set result "\n[db format -style table -border off {SELECT * FROM t1}]"
} {
a | b | c
------+-----+-------
1 | 2.5 | three
BLOB | | Ἀμήν
}
do_test 1.33 {
set result "\n[db format -style table -border off \
-screenwidth 15 \
{SELECT * FROM t1}]"
} {
a | b | c
----+---+-----
1|2.5|three
BLOB| |Ἀμήν
}
do_test 1.34 {
set result "\n[db format -style box -border off \
-screenwidth 30 \
{SELECT * FROM t1}]"
} {
a │ b │ c
══════╪═════╪═══════
1 │ 2.5 │ three
BLOB │ │ Ἀμήν
}
do_test 1.35 {
set result "\n[db format -style box -border off \
-screenwidth 15 \
{SELECT * FROM t1}]"
} {
a │ b │ c
════╪═══╪═════
1│2.5│three
BLOB│ │Ἀμήν
}
do_test 1.40 {
set result "\n[db format -style column {SELECT * FROM t1}]"
} {
a b c
---- --- -----
1 2.5 three
BLOB Ἀμήν
}
do_test 1.41 {
set result "\n[db format -style column -title off {SELECT * FROM t1}]"
} {
1 2.5 three
BLOB Ἀμήν
}
do_test 1.50 {
db format -style count {SELECT * FROM t1}
} 2
do_test 1.60a {
db format -style list -columnsep , -rowsep \r\n -text csv -blob tcl {SELECT * FROM t1}
} "1,2.5,three\r\n\"\\102\\114\\117\\102\",,\"Ἀμήν\"\r\n"
do_test 1.60b {
db format -style csv -columnsep xyz -rowsep pqr -text sql -blob sql {SELECT * FROM t1}
} "1,2.5,three\r\nx'424c4f42',,\"Ἀμήν\"\r\n"
do_test 1.61a {
db format -style list -columnsep , -rowsep \r\n -text csv -title auto -blob tcl {SELECT * FROM t1}
} "a,b,c\r\n1,2.5,three\r\n\"\\102\\114\\117\\102\",,\"Ἀμήν\"\r\n"
do_test 1.61b {
db format -style csv -title auto -blob tcl {SELECT * FROM t1}
} "a,b,c\r\n1,2.5,three\r\n\"\\102\\114\\117\\102\",,\"Ἀμήν\"\r\n"
do_test 1.62a {
db format -style list -columnsep , -rowsep \r\n -text csv -title csv -blob tcl {SELECT a AS 'a x y', b, c FROM t1}
} "\"a x y\",b,c\r\n1,2.5,three\r\n\"\\102\\114\\117\\102\",,\"Ἀμήν\"\r\n"
do_test 1.62b {
db format -style csv -title csv -blob tcl {SELECT a AS 'a x y', b, c FROM t1}
} "\"a x y\",b,c\r\n1,2.5,three\r\n\"\\102\\114\\117\\102\",,\"Ἀμήν\"\r\n"
do_test 1.70 {
set result "\n[db format -style html {SELECT * FROM t1}]"
} {
<TR>
<TD>1
<TD>2.5
<TD>three
</TR>
<TR>
<TD>BLOB
<TD>null
<TD>Ἀμήν
</TR>
}
do_test 1.71 {
set result "\n[db format -style html -title auto {SELECT * FROM t1}]"
} {
<TR>
<TH>a
<TH>b
<TH>c
</TR>
<TR>
<TD>1
<TD>2.5
<TD>three
</TR>
<TR>
<TD>BLOB
<TD>null
<TD>Ἀμήν
</TR>
}
do_test 1.80 {
set result "\n[db format -style insert {SELECT * FROM t1}]"
} {
INSERT INTO tab VALUES(1,2.5,'three');
INSERT INTO tab VALUES(x'424c4f42',NULL,'Ἀμήν');
}
do_test 1.81 {
set result "\n[db format -style insert -tablename t1 {SELECT * FROM t1}]"
} {
INSERT INTO t1 VALUES(1,2.5,'three');
INSERT INTO t1 VALUES(x'424c4f42',NULL,'Ἀμήν');
}
do_test 1.82 {
set result "\n[db format -style insert -tablename t1 -title auto \
{SELECT * FROM t1}]"
} {
INSERT INTO t1(a,b,c) VALUES(1,2.5,'three');
INSERT INTO t1(a,b,c) VALUES(x'424c4f42',NULL,'Ἀμήν');
}
do_test 1.83 {
set result "\n[db format -style insert -tablename drop -title on \
{SELECT a AS "a-b", b, c AS "123" FROM t1}]"
} {
INSERT INTO "drop"("a-b",b,"123") VALUES(1,2.5,'three');
INSERT INTO "drop"("a-b",b,"123") VALUES(x'424c4f42',NULL,'Ἀμήν');
}
do_test 1.84 {
set result "\n[db format -style insert {SELECT * FROM t1} -multiinsert 2000]"
} {
INSERT INTO tab VALUES(1,2.5,'three'),
(x'424c4f42',NULL,'Ἀμήν');
}
do_test 1.85 {
set result "\n[db format -style insert {
WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<7)
SELECT n, n*10 AS m FROM c;} -multiinsert 70]"
} {
INSERT INTO tab VALUES(1,10),
(2,20),
(3,30),
(4,40),
(5,50);
INSERT INTO tab VALUES(6,60),
(7,70);
}
do_test 1.90 {
set result "\n[db format -style json {SELECT * FROM t1}]"
} {
[{"a":1,"b":2.5,"c":"three"},
{"a":"\u0042\u004c\u004f\u0042","b":null,"c":"Ἀμήν"}]
}
do_test 1.91 {
set result "\n[db format -style jobject {SELECT * FROM t1}]"
} {
{"a":1,"b":2.5,"c":"three"}
{"a":"\u0042\u004c\u004f\u0042","b":null,"c":"Ἀμήν"}
}
do_test 1.92 {
set result "\n[db format -style jobject {SELECT *, unistr('abc\u000a123\u000d\u000axyz') AS xyz FROM t1}]"
} {
{"a":1,"b":2.5,"c":"three","xyz":"abc\n123\r\nxyz"}
{"a":"\u0042\u004c\u004f\u0042","b":null,"c":"Ἀμήν","xyz":"abc\n123\r\nxyz"}
}
do_test 1.100 {
set result "\n[db format -style line {SELECT * FROM t1}]"
} {
a: 1
b: 2.5
c: three
a: BLOB
b:
c: Ἀμήν
}
do_test 1.101 {
set result "\n[db format -style line -null (NULL) {SELECT * FROM t1}]"
} {
a: 1
b: 2.5
c: three
a: BLOB
b: (NULL)
c: Ἀμήν
}
do_test 1.102 {
set result "\n[db format -style line -null (NULL) -columnsep { = } \
-text sql {SELECT * FROM t1}]"
} {
a = 1
b = 2.5
c = 'three'
a = x'424c4f42'
b = (NULL)
c = 'Ἀμήν'
}
do_test 1.110 {
set result "\n[db format -style list {SELECT * FROM t1}]"
} {
1|2.5|three
BLOB||Ἀμήν
}
do_test 1.111 {
set result "\n[db format -style list -title on {SELECT * FROM t1}]"
} {
a|b|c
1|2.5|three
BLOB||Ἀμήν
}
do_test 1.112 {
set result "\n[db format -style list -title on -text sql -null NULL \
-title plain {SELECT * FROM t1}]"
} {
a|b|c
1|2.5|'three'
x'424c4f42'|NULL|'Ἀμήν'
}
do_test 1.118 {
set rc [catch {db format -style list -title unk {SELECT * FROM t1}} res]
lappend rc $res
} {1 {bad -title "unk": must be off, on, auto, csv, html, json, plain, relaxed, sql, or tcl}}
do_test 1.120 {
set result "\n[db format -style markdown {SELECT * FROM t1}]"
} {
| a | b | c |
|------|-----|-------|
| 1 | 2.5 | three |
| BLOB | | Ἀμήν |
}
do_test 1.121 {
set result "\n[db format -style markdown -title off {SELECT * FROM t1}]"
} {
| 1 | 2.5 | three |
| BLOB | | Ἀμήν |
}
do_test 1.130 {
set result "\n[db format -style quote {SELECT * FROM t1}]"
} {
1,2.5,'three'
x'424c4f42',NULL,'Ἀμήν'
}
do_test 1.131 {
set result "\n[db format -style quote -title on {SELECT * FROM t1}]"
} {
'a','b','c'
1,2.5,'three'
x'424c4f42',NULL,'Ἀμήν'
}
do_execsql_test 2.0 {
DELETE FROM t1;
INSERT INTO t1 VALUES(1,2,'The quick fox jumps over the lazy brown dog.');
}
do_test 2.1 {
set result "\n[db format -widths {5 -5 19} -wordwrap on \
{SELECT * FROM t1}]"
} {
╭───────┬───────┬─────────────────────╮
│ a │ b │ c │
╞═══════╪═══════╪═════════════════════╡
│ 1 │ 2 │ The quick fox jumps │
│ │ │ over the lazy brown │
│ │ │ dog. │
╰───────┴───────┴─────────────────────╯
}
do_test 2.2 {
set result "\n[db format -widths {5 -5 19} -wordwrap off \
{SELECT * FROM t1}]"
} {
╭───────┬───────┬─────────────────────╮
│ a │ b │ c │
╞═══════╪═══════╪═════════════════════╡
│ 1 │ 2 │ The quick fox jumps │
│ │ │ over the lazy brown │
│ │ │ dog. │
╰───────┴───────┴─────────────────────╯
}
do_test 2.3 {
set result "\n[db format -widths {5 -5 18} -wordwrap on \
{SELECT * FROM t1}]"
} {
╭───────┬───────┬────────────────────╮
│ a │ b │ c │
╞═══════╪═══════╪════════════════════╡
│ 1 │ 2 │ The quick fox │
│ │ │ jumps over the │
│ │ │ lazy brown dog. │
╰───────┴───────┴────────────────────╯
}
do_test 2.4 {
set result "\n[db format -widths {5 -5 -18} -wordwrap on \
{SELECT * FROM t1}]"
} {
╭───────┬───────┬────────────────────╮
│ a │ b │ c │
╞═══════╪═══════╪════════════════════╡
│ 1 │ 2 │ The quick fox │
│ │ │ jumps over the │
│ │ │ lazy brown dog. │
╰───────┴───────┴────────────────────╯
}
do_test 2.5 {
set result "\n[db format -widths {5 -5 19} -wordwrap off \
{SELECT * FROM t1}]"
} {
╭───────┬───────┬─────────────────────╮
│ a │ b │ c │
╞═══════╪═══════╪═════════════════════╡
│ 1 │ 2 │ The quick fox jumps │
│ │ │ over the lazy brown │
│ │ │ dog. │
╰───────┴───────┴─────────────────────╯
}
do_test 2.6 {
set result "\n[db format -widths {5 -5 18} -wordwrap off \
{SELECT * FROM t1}]"
} {
╭───────┬───────┬────────────────────╮
│ a │ b │ c │
╞═══════╪═══════╪════════════════════╡
│ 1 │ 2 │ The quick fox jump │
│ │ │ s over the lazy br │
│ │ │ own dog. │
╰───────┴───────┴────────────────────╯
}
do_test 2.7 {
set result "\n[db format -widths {5 5 18} -wordwrap yes \
-align {left center right} -titlealign right \
{SELECT * FROM t1}]"
} {
╭───────┬───────┬────────────────────╮
│ a │ b │ c │
╞═══════╪═══════╪════════════════════╡
│ 1 │ 2 │ The quick fox │
│ │ │ jumps over the │
│ │ │ lazy brown dog. │
╰───────┴───────┴────────────────────╯
}
do_test 2.8 {
set result "\n[db format -widths {5 8 11} -wordwrap yes \
-align {auto auto center} -titlealign left \
-defaultalign right \
{SELECT * FROM t1}]"
} {
╭───────┬──────────┬─────────────╮
│ a │ b │ c │
╞═══════╪══════════╪═════════════╡
│ 1 │ 2 │ The quick │
│ │ │ fox jumps │
│ │ │ over the │
│ │ │ lazy brown │
│ │ │ dog. │
╰───────┴──────────┴─────────────╯
}
do_test 2.9 {
catch {db format -align {auto xyz 123} {SELECT * FROM t1}} res
set res
} {bad column alignment (-align) "xyz": must be auto, bottom, c, center, e, left, middle, n, ne, nw, right, s, se, sw, top, or w}
do_test 2.10 {
catch {db format -defaultalign xyz {SELECT * FROM t1}} res
set res
} {bad default alignment (-defaultalign) "xyz": must be auto, bottom, c, center, e, left, middle, n, ne, nw, right, s, se, sw, top, or w}
do_test 2.11 {
catch {db format -titlealign xyz {SELECT * FROM t1}} res
set res
} {bad title alignment (-titlealign) "xyz": must be auto, bottom, c, center, e, left, middle, n, ne, nw, right, s, se, sw, top, or w}
do_execsql_test 2.30 {
UPDATE t1 SET c='Η γρήγορη αλεπού πηδάει πάνω από το τεμπέλικο καφέ σκυλί';
SELECT hex(c) FROM t1;
} {CE9720CEB3CF81CEAECEB3CEBFCF81CEB720CEB1CEBBCEB5CF80CEBFCF8D20CF80CEB7CEB4CEACCEB5CEB920CF80CEACCEBDCF8920CEB1CF80CF8C20CF84CEBF20CF84CEB5CEBCCF80CEADCEBBCEB9CEBACEBF20CEBACEB1CF86CEAD20CF83CEBACF85CEBBCEAF}
do_test 2.31 {
set result "\n[db format -widths {5 -5 18} -wordwrap on \
{SELECT * FROM t1}]"
} {
╭───────┬───────┬────────────────────╮
│ a │ b │ c │
╞═══════╪═══════╪════════════════════╡
│ 1 │ 2 │ Η γρήγορη αλεπού │
│ │ │ πηδάει πάνω από το │
│ │ │ τεμπέλικο καφέ │
│ │ │ σκυλί │
╰───────┴───────┴────────────────────╯
}
do_test 2.32 {
set result "\n[db format -widths {5 5 18} -align {left center center} -wordwrap on \
{SELECT * FROM t1}]"
} {
╭───────┬───────┬────────────────────╮
│ a │ b │ c │
╞═══════╪═══════╪════════════════════╡
│ 1 │ 2 │ Η γρήγορη αλεπού │
│ │ │ πηδάει πάνω από το │
│ │ │ τεμπέλικο καφέ │
│ │ │ σκυλί │
╰───────┴───────┴────────────────────╯
}
do_execsql_test 3.0 {
DELETE FROM t1;
INSERT INTO t1 VALUES(1,2,unistr('abc\u001b[1;31m123\u001b[0mxyz'));
}
do_test 3.1 {
set result "\n[db format {SELECT * FROM t1}]"
} {
╭───┬───┬────────────────────────╮
│ a │ b │ c │
╞═══╪═══╪════════════════════════╡
│ 1 │ 2 │ abc^[[1;31m123^[[0mxyz │
╰───┴───┴────────────────────────╯
}
do_test 3.2 {
set result "\n[db format -esc off {SELECT * FROM t1}]"
string map [list \033 X] $result
} {
╭───┬───┬───────────╮
│ a │ b │ c │
╞═══╪═══╪═══════════╡
│ 1 │ 2 │ abcX[1;31m123X[0mxyz │
╰───┴───┴───────────╯
}
do_test 3.3 {
set result "\n[db format -esc symbol {SELECT * FROM t1}]"
} {
╭───┬───┬──────────────────────╮
│ a │ b │ c │
╞═══╪═══╪══════════════════════╡
│ 1 │ 2 │ abc␛[1;31m123␛[0mxyz │
╰───┴───┴──────────────────────╯
}
do_test 3.4 {
set result "\n[db format -esc ascii {SELECT * FROM t1}]"
} {
╭───┬───┬────────────────────────╮
│ a │ b │ c │
╞═══╪═══╪════════════════════════╡
│ 1 │ 2 │ abc^[[1;31m123^[[0mxyz │
╰───┴───┴────────────────────────╯
}
do_test 3.5 {
catch {db format -esc unk {SELECT * FROM t1}} res
set res
} {bad control character escape (-esc) "unk": must be ascii, auto, off, or symbol}
do_execsql_test 4.0 {
DELETE FROM t1;
INSERT INTO t1 VALUES(json('{a:5,b:6}'), jsonb('{c:1,d:2}'), 99);
}
do_test 4.1 {
set result "\n[db format -text sql {SELECT * FROM t1}]"
} {
╭─────────────────┬───────────────────────┬────╮
│ a │ b │ c │
╞═════════════════╪═══════════════════════╪════╡
│ '{"a":5,"b":6}' │ x'8c1763133117641332' │ 99 │
╰─────────────────┴───────────────────────┴────╯
}
do_test 4.2 {
set result "\n[db format -text sql -textjsonb on {SELECT * FROM t1}]"
} {
╭─────────────────┬────────────────────────┬────╮
│ a │ b │ c │
╞═════════════════╪════════════════════════╪════╡
│ '{"a":5,"b":6}' │ jsonb('{"c":1,"d":2}') │ 99 │
╰─────────────────┴────────────────────────┴────╯
}
do_test 4.3 {
set result "\n[db format -text plain -textjsonb on -wrap 11 \
{SELECT a AS json, b AS jsonb, c AS num FROM t1}]"
} {
╭─────────────┬─────────────┬─────╮
│ json │ jsonb │ num │
╞═════════════╪═════════════╪═════╡
│ {"a":5,"b": │ {"c":1,"d": │ 99 │
│ 6} │ 2} │ │
╰─────────────┴─────────────┴─────╯
}
do_execsql_test 5.0 {
DROP TABLE t1;
CREATE TABLE t1(name, mtime, value);
INSERT INTO t1 VALUES
('entry-one',1708791504,zeroblob(300)),
(unistr('one\u000atwo\u000athree'),1333206973,NULL),
('sample-jsonb',1333101221,jsonb('{
"alpha":53.11688723,
"beta":"qrfWidthPrint(p, p->pOut, -p->u.sLine.mxColWth);",
"zeta":[15,null,1333206973,"fd8ffe000104a46494600010101"]}'));
}
do_test 5.1 {
set sql {SELECT name, mtime, datetime(mtime,'unixepoch') AS time,
value FROM t1 ORDER BY mtime}
set result "\n[db format -style line -screenwidth 60 -blob sql \
-text sql -wordwrap off -linelimit 77 \
-columnsep { = } $sql]"
} {
name = 'sample-jsonb'
mtime = 1333101221
time = '2012-03-30 09:53:41'
value = x'cc7c57616c706861b535332e31313638383732334762657461
c73071726657696474685072696e7428702c20702d3e704f7574
2c202d702d3e752e734c696e652e6d78436f6c577468293b477a
657461cb2c23313500a331333333323036393733c71b66643866
6665303030313034613436343934363030303130313031'
name = unistr('one\u000atwo\u000athree')
mtime = 1333206973
time = '2012-03-31 15:16:13'
value =
name = 'entry-one'
mtime = 1708791504
time = '2024-02-24 16:18:24'
value = x'00000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000
000000000000000000000000000000'
}
do_test 5.2a {
set sql {SELECT name, mtime, datetime(mtime,'unixepoch') AS time,
value FROM t1 ORDER BY mtime}
set result "\n[db format -style line -screenwidth 60 -blob sql \
-text plain -esc off -textjsonb yes -columnsep { = }\
-wordwrap yes -linelimit 3 $sql]"
} {
name = sample-jsonb
mtime = 1333101221
time = 2012-03-30 09:53:41
value = {"alpha":53.11688723,"beta":"qrfWidthPrint(p,
p->pOut, -p->u.sLine.mxColWth);","zeta":[15,null,
1333206973,"fd8ffe000104a46494600010101"]}
name = one
two
three
mtime = 1333206973
time = 2012-03-31 15:16:13
value =
name = entry-one
mtime = 1708791504
time = 2024-02-24 16:18:24
value = x'00000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000
...
}
set sqlnolabel "SELECT name, mtime, datetime(mtime,'unixepoch'),\
value FROM t1 ORDER BY mtime"
do_test 5.2b {
set result "\n[db format -style line -screenwidth 60 -blob sql \
-text plain -esc off -textjsonb no -titlelimit 12 \
-wordwrap yes -linelimit 3 $sqlnolabel ]"
} {
name: sample-jsonb
mtime: 1333101221
datetime(...: 2012-03-30 09:53:41
value: x'cc7c57616c706861b535332e31313638383732334762
657461c73071726657696474685072696e7428702c2070
2d3e704f75742c202d702d3e752e734c696e652e6d7843
...
name: one
two
three
mtime: 1333206973
datetime(...: 2012-03-31 15:16:13
value:
name: entry-one
mtime: 1708791504
datetime(...: 2024-02-24 16:18:24
value: x'00000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000
...
}
set sql "SELECT name, mtime, datetime(mtime,'unixepoch') AS time,\
value FROM t1 ORDER BY mtime"
do_test 5.3a {
set result "\n[db format -style box -widths {0 10 10 14}\
-align {left right right center} \
-blob sql \
-text plain -esc off -textjsonb no \
-wordwrap yes -linelimit 2 $sql]"
} {
╭──────────────┬────────────┬────────────┬────────────────╮
│ name │ mtime │ time │ value │
╞══════════════╪════════════╪════════════╪════════════════╡
│ sample-jsonb │ 1333101221 │ 2012-03-30 │ x'cc7c57616c70 │
│ │ │ 09:53:41 │ 6861b535332e31 │
│ │ │ │ ... │
├──────────────┼────────────┼────────────┼────────────────┤
│ one │ 1333206973 │ 2012-03-31 │ │
│ two │ │ 15:16:13 │ │
│ ... │ │ │ │
├──────────────┼────────────┼────────────┼────────────────┤
│ entry-one │ 1708791504 │ 2024-02-24 │ x'000000000000 │
│ │ │ 16:18:24 │ 00000000000000 │
│ │ │ │ ... │
╰──────────────┴────────────┴────────────┴────────────────╯
}
do_test 5.3b {
set result "\n[db format -style box -widths {0 10 0 14} \
-align {left right right center} \
-blob sql -titlelimit 12 \
-text plain -esc off -textjsonb no \
-wordwrap yes -linelimit 2 $sqlnolabel ]"
} {
╭──────────────┬────────────┬─────────────────────┬────────────────╮
│ name │ mtime │ datetime(... │ value │
╞══════════════╪════════════╪═════════════════════╪════════════════╡
│ sample-jsonb │ 1333101221 │ 2012-03-30 09:53:41 │ x'cc7c57616c70 │
│ │ │ │ 6861b535332e31 │
│ │ │ │ ... │
├──────────────┼────────────┼─────────────────────┼────────────────┤
│ one │ 1333206973 │ 2012-03-31 15:16:13 │ │
│ two │ │ │ │
│ ... │ │ │ │
├──────────────┼────────────┼─────────────────────┼────────────────┤
│ entry-one │ 1708791504 │ 2024-02-24 16:18:24 │ x'000000000000 │
│ │ │ │ 00000000000000 │
│ │ │ │ ... │
╰──────────────┴────────────┴─────────────────────┴────────────────╯
}
do_test 5.3c {
set result "\n[db format -style table -widths {0 10 10 14}\
-align {center right right right} \
-blob sql \
-text plain -esc off -textjsonb no \
-wordwrap yes -linelimit 2 $sql]"
} {
+--------------+------------+------------+----------------+
| name | mtime | time | value |
+--------------+------------+------------+----------------+
| sample-jsonb | 1333101221 | 2012-03-30 | x'cc7c57616c70 |
| | | 09:53:41 | 6861b535332e31 |
| | | | ... |
+--------------+------------+------------+----------------+
| one | 1333206973 | 2012-03-31 | |
| two | | 15:16:13 | |
| ... | | | |
+--------------+------------+------------+----------------+
| entry-one | 1708791504 | 2024-02-24 | x'000000000000 |
| | | 16:18:24 | 00000000000000 |
| | | | ... |
+--------------+------------+------------+----------------+
}
do_test 5.3c {
set result "\n[db format -style column -widths {0 10 10 14}\
-align {center right right right} \
-blob sql \
-text plain -esc off -textjsonb no \
-wordwrap yes -linelimit 2 $sql]"
} {
name mtime time value
------------ ---------- ---------- --------------
sample-jsonb 1333101221 2012-03-30 x'cc7c57616c70
09:53:41 6861b535332e31
...
one 1333206973 2012-03-31
two 15:16:13
...
entry-one 1708791504 2024-02-24 x'000000000000
16:18:24 00000000000000
...
}
do_test 5.4 {
db eval {
CREATE TABLE t2(a,b,c,d,e);
WITH v(x) AS (SELECT 'abcdefghijklmnopqrstuvwxyz')
INSERT INTO t2 SELECT x,x,x,x,x FROM v;
}
set sql {SELECT char(0x61,0xa,0x62,0xa,0x63,0xa,0x64) a,
mtime b, mtime c, mtime d, mtime e FROM t1}
set result "\n[db format -style box -widths {1 2 3 4 5}\
-linelimit 3 -wordwrap off {SELECT *, 'x' AS x FROM t2}]"
} {
╭────┬────┬─────┬──────┬───────┬───╮
│ a │ b │ c │ d │ e │ x │
╞════╪════╪═════╪══════╪═══════╪═══╡
│ ab │ ab │ abc │ abcd │ abcde │ x │
│ cd │ cd │ def │ efgh │ fghij │ │
│ ef │ ef │ ghi │ ijkl │ klmno │ │
│ .. │ .. │ ... │ ... │ ... │ │
╰────┴────┴─────┴──────┴───────┴───╯
}
do_execsql_test 6.0 {
DELETE FROM t2;
INSERT INTO t2 VALUES
(1, 2.5, 'three', x'342028666f757229', null);
}
do_test 6.1a {
set result "\n[db format -style list -null NULL \
-text tcl -columnsep , \
{SELECT * FROM t2}]"
} {
1,2.5,"three","\064\040\050\146\157\165\162\051",NULL
}
do_execsql_test 7.0 {
CREATE TABLE t7(a,b);
INSERT INTO t7 VALUES('abcdefghijklmnop',
'abcぁdefかghiのjklはmnop');
}
do_test 7.1 {
set result "\n[db format -style list -charlimit 13 \
{SELECT * FROM t7}]"
} {
abcdefghijklm...|abcぁdefかghi...
}
do_test 7.2 {
set result "\n[db format -style list -charlimit 14 \
{SELECT * FROM t7}]"
} {
abcdefghijklmn...|abcぁdefかghi...
}
do_test 7.3 {
set result "\n[db format -style list -charlimit 15 \
{SELECT * FROM t7}]"
} {
abcdefghijklmno...|abcぁdefかghiの...
}
do_test 7.4 {
set result "\n[db format -style list -charlimit 16 \
{SELECT * FROM t7}]"
} {
abcdefghijklmnop|abcぁdefかghiのj...
}
do_test 8.0 {
set result "\n[db format -style table {
WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<10)
SELECT 'aaa' AS 'a', format('%.*c',n,'b')||char(9)||'xx' AS x FROM c}]"
} {
+-----+--------------------+
| a | x |
+-----+--------------------+
| aaa | b xx |
| aaa | bb xx |
| aaa | bbb xx |
| aaa | bbbb xx |
| aaa | bbbbb xx |
| aaa | bbbbbb xx |
| aaa | bbbbbbb xx |
| aaa | bbbbbbbb xx |
| aaa | bbbbbbbbb xx |
| aaa | bbbbbbbbbb xx |
+-----+--------------------+
}
do_test 8.1 {
set result "\n[db format -style table {
WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<10)
SELECT 'aaaa' AS 'a', format('%.*c',n,'b')||char(9)||'xx' AS x FROM c}]"
} {
+------+--------------------+
| a | x |
+------+--------------------+
| aaaa | b xx |
| aaaa | bb xx |
| aaaa | bbb xx |
| aaaa | bbbb xx |
| aaaa | bbbbb xx |
| aaaa | bbbbbb xx |
| aaaa | bbbbbbb xx |
| aaaa | bbbbbbbb xx |
| aaaa | bbbbbbbbb xx |
| aaaa | bbbbbbbbbb xx |
+------+--------------------+
}
do_test 8.3 {
set result "\n[db format -style table -esc off {
WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<15)
SELECT 'aaa' AS 'a',n, 'xx'||char(n)||'yy' AS xy FROM c
WHERE n NOT IN (8,10,13,14)}]"
} {
+-----+----+------------+
| a | n | xy |
+-----+----+------------+
| aaa | 1 | xx␁yy |
| aaa | 2 | xx␂yy |
| aaa | 3 | xx␃yy |
| aaa | 4 | xx␄yy |
| aaa | 5 | xx␅yy |
| aaa | 6 | xx␆yy |
| aaa | 7 | xx␇yy |
| aaa | 9 | xx yy |
| aaa | 11 | xx␋yy |
| aaa | 12 | xx␌yy |
| aaa | 15 | xx␏yy |
+-----+----+------------+
}
do_test 8.4 {
set result "\n[db format -style table -esc off {
WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<15)
SELECT 'aaa' AS 'a',n, 'xx'||char(n)||'yy'||char(9)||'zz' AS xyz FROM c
WHERE n NOT IN (8,10,13,14)}]"
} {
+-----+----+--------------------+
| a | n | xyz |
+-----+----+--------------------+
| aaa | 1 | xx␁yy zz |
| aaa | 2 | xx␂yy zz |
| aaa | 3 | xx␃yy zz |
| aaa | 4 | xx␄yy zz |
| aaa | 5 | xx␅yy zz |
| aaa | 6 | xx␆yy zz |
| aaa | 7 | xx␇yy zz |
| aaa | 9 | xx yy zz |
| aaa | 11 | xx␋yy zz |
| aaa | 12 | xx␌yy zz |
| aaa | 15 | xx␏yy zz |
+-----+----+--------------------+
}
do_test 9.1 {
db eval {
CREATE TABLE t9(x);
INSERT INTO t9 VALUES
(x'4331323334'),
(x'c30431323334'),
(x'd3000431323334'),
(x'e30000000431323334'),
(x'f3000000000000000431323334');
}
db format -style list -text plain -rowsep , -textjsonb on \
{SELECT * FROM t9}
} {1234,1234,1234,1234,1234,}
do_test 9.2 {
db format -style list -text sql -rowsep , -textjsonb on \
{SELECT * FROM t9}
} {jsonb('1234'),jsonb('1234'),jsonb('1234'),jsonb('1234'),jsonb('1234'),}
do_test 9.3 {
db format -style json {SELECT * FROM t9 WHERE rowid<0}
} {}
do_test 9.4 {
db format -style jobject {SELECT * FROM t9 WHERE rowid<0}
} {}
do_test 10.1 {
db eval {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(x);
INSERT INTO t1(x) VALUES
('alice'),
('bob'),
('cinderella-cinderella'),
('daniel'),
('emma'),
('fred'),
('gertrude'),
('harold'),
('ingrid'),
('jake'),
('lisa'),
('mike'),
('nina'),
('octavian'),
('paula'),
('quintus'),
('rita'),
('sam'),
('tammy'),
('ulysses'),
('violet'),
('william'),
('xanthippe'),
('yates'),
('zoe');
}
set result "\n[db format -style column -title off -screenwidth 41 -splitcolumn on \
{SELECT x FROM t1}]"
} {
alice octavian
bob paula
cinderella-cinderella quintus
daniel rita
emma sam
fred tammy
gertrude ulysses
harold violet
ingrid william
jake xanthippe
lisa yates
mike zoe
nina
}
do_test 10.2 {
set result "\n[db format -style column -title off -screenwidth 42 -splitcolumn on \
{SELECT x FROM t1}]"
} {
alice jake tammy
bob lisa ulysses
cinderella-cinderella mike violet
daniel nina william
emma octavian xanthippe
fred paula yates
gertrude quintus zoe
harold rita
ingrid sam
}
do_test 10.3 {
set result "\n[db format -style column -title off -screenwidth 51 -splitcolumn on \
{SELECT x FROM t1}]"
} {
alice harold paula william
bob ingrid quintus xanthippe
cinderella-cinderella jake rita yates
daniel lisa sam zoe
emma mike tammy
fred nina ulysses
gertrude octavian violet
}
do_test 10.4 {
set result "\n[db format -style column -title off -screenwidth 61 -splitcolumn on \
{SELECT x FROM t1}]"
} {
alice fred lisa quintus violet
bob gertrude mike rita william
cinderella-cinderella harold nina sam xanthippe
daniel ingrid octavian tammy yates
emma jake paula ulysses zoe
}
do_test 10.5 {
set result "\n[db format -style column -title off -screenwidth 74 -splitcolumn on \
{SELECT x FROM t1}]"
} {
alice emma ingrid nina rita violet zoe
bob fred jake octavian sam william
cinderella-cinderella gertrude lisa paula tammy xanthippe
daniel harold mike quintus ulysses yates
}
do_test 11.1 {
set result "\n[db format -style table -blob size {SELECT randomblob(1234)}]"
} {
+------------------+
| randomblob(1234) |
+------------------+
| (1234-byte blob) |
+------------------+
}
do_test 12.1 {
set result "\n[db format -style box -text html \
{SELECT 'abc','','xyz'}]"
} {
╭───────┬────┬───────╮
│ 'abc' │ '' │ 'xyz' │
╞═══════╪════╪═══════╡
│ abc │ │ xyz │
╰───────┴────┴───────╯
}
# Tests for "relaxed" quoting
#
do_test 13.2 {
db eval {
CREATE TABLE t13(a,b);
INSERT INTO t13(a,b) VALUES
(1,'NULL'),
(0,'-NULL-'),
(0,''),
(1,'''abcde'),
(1,'abcde'''),
(0,'abcde'),
(1,' abcde'),
(1,'abcde '),
(1,'+0'),
(1,'-0'),
(1,'012345'),
(0,'012xyz345'),
(1,'0123.45'),
(0,'12.34.56'),
(0,'12.3e'),
(1,'12.3e+123'),
(1,'12.3e-34'),
(1,'12.3E56'),
(1,'12E56'),
(0,'12.5E5.6'),
(0,'12.5e+'),
(0,'12.5e-'),
(1,'+Inf'),(1,'-Inf'),(1,'Inf');
}
set result \n[db format -style box -text relaxed -null NULL \
-align {center left} \
{SELECT if(a,'yes','') AS 'quoted?', b AS string
FROM t13 ORDER BY rowid}]
} {
╭─────────┬─────────────╮
│ quoted? │ string │
╞═════════╪═════════════╡
│ yes │ 'NULL' │
│ │ -NULL- │
│ │ │
│ yes │ '''abcde' │
│ yes │ 'abcde''' │
│ │ abcde │
│ yes │ ' abcde' │
│ yes │ 'abcde ' │
│ yes │ '+0' │
│ yes │ '-0' │
│ yes │ '012345' │
│ │ 012xyz345 │
│ yes │ '0123.45' │
│ │ 12.34.56 │
│ │ 12.3e │
│ yes │ '12.3e+123' │
│ yes │ '12.3e-34' │
│ yes │ '12.3E56' │
│ yes │ '12E56' │
│ │ 12.5E5.6 │
│ │ 12.5e+ │
│ │ 12.5e- │
│ yes │ '+Inf' │
│ yes │ '-Inf' │
│ yes │ 'Inf' │
╰─────────┴─────────────╯
}
db close
finish_test
Reference in New Issue View Git Blame Copy Permalink