AryaWu/sqlite
0
1# 2025-11-052#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11#12# Test cases for the Query Result Formatter (QRF)13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix qrf0118 19do_execsql_test 1.0 {20 CREATE TABLE t1(a, b, c);21 INSERT INTO t1 VALUES(1,2.5,'three'),(x'424c4f42',NULL,'Ἀμήν');22}23 24do_test 1.10 {25 set result "\n[db format {SELECT * FROM t1}]"26} {27╭──────┬─────┬───────╮28│ a │ b │ c │29╞══════╪═════╪═══════╡30│ 1 │ 2.5 │ three │31│ BLOB │ │ Ἀμήν │32╰──────┴─────┴───────╯33}34do_test 1.11a {35 set result "\n[db format -title off {SELECT * FROM t1}]"36} {37╭──────┬─────┬───────╮38│ 1 │ 2.5 │ three │39│ BLOB │ │ Ἀμήν │40╰──────┴─────┴───────╯41}42do_test 1.11b {43 set result "\n[db format -text sql {SELECT * FROM t1}]"44} {45╭─────────────┬─────┬─────────╮46│ a │ b │ c │47╞═════════════╪═════╪═════════╡48│ 1 │ 2.5 │ 'three' │49│ x'424c4f42' │ │ 'Ἀμήν' │50╰─────────────┴─────┴─────────╯51}52do_test 1.11c {53 set result "\n[db format -text sql -border off {SELECT * FROM t1}]"54} {55 a │ b │ c56═════════════╪═════╪═════════57 1 │ 2.5 │ 'three'58 x'424c4f42' │ │ 'Ἀμήν'59}60do_test 1.11d {61 set result "\n[db format -text relaxed -blob sql -border off \62 {SELECT * FROM t1}]"63} {64 a │ b │ c65═════════════╪═════╪═══════66 1 │ 2.5 │ three67 x'424c4f42' │ │ Ἀμήν68}69do_test 1.12 {70 set result "\n[db format -text csv {SELECT * FROM t1}]"71} {72╭────────────────────┬─────┬────────╮73│ a │ b │ c │74╞════════════════════╪═════╪════════╡75│ 1 │ 2.5 │ three │76│ "\102\114\117\102" │ │ "Ἀμήν" │77╰────────────────────┴─────┴────────╯78}79do_test 1.13 {80 set result "\n[db format -text csv -blob hex {SELECT * FROM t1}]"81} {82╭──────────┬─────┬────────╮83│ a │ b │ c │84╞══════════╪═════╪════════╡85│ 1 │ 2.5 │ three │86│ 424c4f42 │ │ "Ἀμήν" │87╰──────────┴─────┴────────╯88}89do_test 1.14 {90 catch {db format -text unk -blob hex {SELECT * FROM t1}} res91 set res92} {bad -text "unk": must be auto, csv, html, json, plain, relaxed, sql, or tcl}93do_test 1.15 {94 catch {db format -text sql -blob unk {SELECT * FROM t1}} res95 set res96} {bad BLOB encoding (-blob) "unk": must be auto, hex, json, tcl, text, sql, or size}97do_test 1.16 {98 catch {db format -text sql -style unk {SELECT * FROM t1}} res99 set res100} {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}101 102 103do_test 1.20 {104 set result "\n[db format -style box {SELECT * FROM t1}]"105} {106╭──────┬─────┬───────╮107│ a │ b │ c │108╞══════╪═════╪═══════╡109│ 1 │ 2.5 │ three │110│ BLOB │ │ Ἀμήν │111╰──────┴─────┴───────╯112}113 114do_test 1.30 {115 set result "\n[db format -style table {SELECT * FROM t1}]"116} {117+------+-----+-------+118| a | b | c |119+------+-----+-------+120| 1 | 2.5 | three |121| BLOB | | Ἀμήν |122+------+-----+-------+123}124do_test 1.31 {125 set result "\n[db format -style table -title off {SELECT * FROM t1}]"126} {127+------+-----+-------+128| 1 | 2.5 | three |129| BLOB | | Ἀμήν |130+------+-----+-------+131}132do_test 1.32 {133 set result "\n[db format -style table -border off {SELECT * FROM t1}]"134} {135 a | b | c136------+-----+-------137 1 | 2.5 | three138 BLOB | | Ἀμήν139}140do_test 1.33 {141 set result "\n[db format -style table -border off \142 -screenwidth 15 \143 {SELECT * FROM t1}]"144} {145 a | b | c146----+---+-----147 1|2.5|three148BLOB| |Ἀμήν149}150do_test 1.34 {151 set result "\n[db format -style box -border off \152 -screenwidth 30 \153 {SELECT * FROM t1}]"154} {155 a │ b │ c156══════╪═════╪═══════157 1 │ 2.5 │ three158 BLOB │ │ Ἀμήν159}160do_test 1.35 {161 set result "\n[db format -style box -border off \162 -screenwidth 15 \163 {SELECT * FROM t1}]"164} {165 a │ b │ c166════╪═══╪═════167 1│2.5│three168BLOB│ │Ἀμήν169}170 171do_test 1.40 {172 set result "\n[db format -style column {SELECT * FROM t1}]"173} {174a b c175---- --- -----1761 2.5 three177BLOB Ἀμήν178}179do_test 1.41 {180 set result "\n[db format -style column -title off {SELECT * FROM t1}]"181} {1821 2.5 three183BLOB Ἀμήν184}185 186do_test 1.50 {187 db format -style count {SELECT * FROM t1}188} 2189 190do_test 1.60a {191 db format -style list -columnsep , -rowsep \r\n -text csv -blob tcl {SELECT * FROM t1}192} "1,2.5,three\r\n\"\\102\\114\\117\\102\",,\"Ἀμήν\"\r\n"193do_test 1.60b {194 db format -style csv -columnsep xyz -rowsep pqr -text sql -blob sql {SELECT * FROM t1}195} "1,2.5,three\r\nx'424c4f42',,\"Ἀμήν\"\r\n"196do_test 1.61a {197 db format -style list -columnsep , -rowsep \r\n -text csv -title auto -blob tcl {SELECT * FROM t1}198} "a,b,c\r\n1,2.5,three\r\n\"\\102\\114\\117\\102\",,\"Ἀμήν\"\r\n"199do_test 1.61b {200 db format -style csv -title auto -blob tcl {SELECT * FROM t1}201} "a,b,c\r\n1,2.5,three\r\n\"\\102\\114\\117\\102\",,\"Ἀμήν\"\r\n"202do_test 1.62a {203 db format -style list -columnsep , -rowsep \r\n -text csv -title csv -blob tcl {SELECT a AS 'a x y', b, c FROM t1}204} "\"a x y\",b,c\r\n1,2.5,three\r\n\"\\102\\114\\117\\102\",,\"Ἀμήν\"\r\n"205do_test 1.62b {206 db format -style csv -title csv -blob tcl {SELECT a AS 'a x y', b, c FROM t1}207} "\"a x y\",b,c\r\n1,2.5,three\r\n\"\\102\\114\\117\\102\",,\"Ἀμήν\"\r\n"208 209do_test 1.70 {210 set result "\n[db format -style html {SELECT * FROM t1}]"211} {212<TR>213<TD>1214<TD>2.5215<TD>three216</TR>217<TR>218<TD>BLOB219<TD>null220<TD>Ἀμήν221</TR>222}223do_test 1.71 {224 set result "\n[db format -style html -title auto {SELECT * FROM t1}]"225} {226<TR>227<TH>a228<TH>b229<TH>c230</TR>231<TR>232<TD>1233<TD>2.5234<TD>three235</TR>236<TR>237<TD>BLOB238<TD>null239<TD>Ἀμήν240</TR>241}242 243do_test 1.80 {244 set result "\n[db format -style insert {SELECT * FROM t1}]"245} {246INSERT INTO tab VALUES(1,2.5,'three');247INSERT INTO tab VALUES(x'424c4f42',NULL,'Ἀμήν');248}249do_test 1.81 {250 set result "\n[db format -style insert -tablename t1 {SELECT * FROM t1}]"251} {252INSERT INTO t1 VALUES(1,2.5,'three');253INSERT INTO t1 VALUES(x'424c4f42',NULL,'Ἀμήν');254}255do_test 1.82 {256 set result "\n[db format -style insert -tablename t1 -title auto \257 {SELECT * FROM t1}]"258} {259INSERT INTO t1(a,b,c) VALUES(1,2.5,'three');260INSERT INTO t1(a,b,c) VALUES(x'424c4f42',NULL,'Ἀμήν');261}262do_test 1.83 {263 set result "\n[db format -style insert -tablename drop -title on \264 {SELECT a AS "a-b", b, c AS "123" FROM t1}]"265} {266INSERT INTO "drop"("a-b",b,"123") VALUES(1,2.5,'three');267INSERT INTO "drop"("a-b",b,"123") VALUES(x'424c4f42',NULL,'Ἀμήν');268}269 270do_test 1.90 {271 set result "\n[db format -style json {SELECT * FROM t1}]"272} {273[{"a":1,"b":2.5,"c":"three"},274{"a":"\u0042\u004c\u004f\u0042","b":null,"c":"Ἀμήν"}]275}276do_test 1.91 {277 set result "\n[db format -style jobject {SELECT * FROM t1}]"278} {279{"a":1,"b":2.5,"c":"three"}280{"a":"\u0042\u004c\u004f\u0042","b":null,"c":"Ἀμήν"}281}282do_test 1.92 {283 set result "\n[db format -style jobject {SELECT *, unistr('abc\u000a123\u000d\u000axyz') AS xyz FROM t1}]"284} {285{"a":1,"b":2.5,"c":"three","xyz":"abc\n123\r\nxyz"}286{"a":"\u0042\u004c\u004f\u0042","b":null,"c":"Ἀμήν","xyz":"abc\n123\r\nxyz"}287}288 289do_test 1.100 {290 set result "\n[db format -style line {SELECT * FROM t1}]"291} {292a: 1293b: 2.5294c: three295 296a: BLOB297b: 298c: Ἀμήν299}300do_test 1.101 {301 set result "\n[db format -style line -null (NULL) {SELECT * FROM t1}]"302} {303a: 1304b: 2.5305c: three306 307a: BLOB308b: (NULL)309c: Ἀμήν310}311do_test 1.102 {312 set result "\n[db format -style line -null (NULL) -columnsep { = } \313 -text sql {SELECT * FROM t1}]"314} {315a = 1316b = 2.5317c = 'three'318 319a = x'424c4f42'320b = (NULL)321c = 'Ἀμήν'322}323 324do_test 1.110 {325 set result "\n[db format -style list {SELECT * FROM t1}]"326} {3271|2.5|three328BLOB||Ἀμήν329}330do_test 1.111 {331 set result "\n[db format -style list -title on {SELECT * FROM t1}]"332} {333a|b|c3341|2.5|three335BLOB||Ἀμήν336}337do_test 1.112 {338 set result "\n[db format -style list -title on -text sql -null NULL \339 -title plain {SELECT * FROM t1}]"340} {341a|b|c3421|2.5|'three'343x'424c4f42'|NULL|'Ἀμήν'344}345do_test 1.118 {346 set rc [catch {db format -style list -title unk {SELECT * FROM t1}} res]347 lappend rc $res348} {1 {bad -title "unk": must be off, on, auto, csv, html, json, plain, relaxed, sql, or tcl}}349 350 351do_test 1.120 {352 set result "\n[db format -style markdown {SELECT * FROM t1}]"353} {354| a | b | c |355|------|-----|-------|356| 1 | 2.5 | three |357| BLOB | | Ἀμήν |358}359do_test 1.121 {360 set result "\n[db format -style markdown -title off {SELECT * FROM t1}]"361} {362| 1 | 2.5 | three |363| BLOB | | Ἀμήν |364}365 366do_test 1.130 {367 set result "\n[db format -style quote {SELECT * FROM t1}]"368} {3691,2.5,'three'370x'424c4f42',NULL,'Ἀμήν'371}372do_test 1.131 {373 set result "\n[db format -style quote -title on {SELECT * FROM t1}]"374} {375'a','b','c'3761,2.5,'three'377x'424c4f42',NULL,'Ἀμήν'378}379 380 381do_execsql_test 2.0 {382 DELETE FROM t1;383 INSERT INTO t1 VALUES(1,2,'The quick fox jumps over the lazy brown dog.');384}385do_test 2.1 {386 set result "\n[db format -widths {5 -5 19} -wordwrap on \387 {SELECT * FROM t1}]"388} {389╭───────┬───────┬─────────────────────╮390│ a │ b │ c │391╞═══════╪═══════╪═════════════════════╡392│ 1 │ 2 │ The quick fox jumps │393│ │ │ over the lazy brown │394│ │ │ dog. │395╰───────┴───────┴─────────────────────╯396}397do_test 2.2 {398 set result "\n[db format -widths {5 -5 19} -wordwrap off \399 {SELECT * FROM t1}]"400} {401╭───────┬───────┬─────────────────────╮402│ a │ b │ c │403╞═══════╪═══════╪═════════════════════╡404│ 1 │ 2 │ The quick fox jumps │405│ │ │ over the lazy brown │406│ │ │ dog. │407╰───────┴───────┴─────────────────────╯408}409do_test 2.3 {410 set result "\n[db format -widths {5 -5 18} -wordwrap on \411 {SELECT * FROM t1}]"412} {413╭───────┬───────┬────────────────────╮414│ a │ b │ c │415╞═══════╪═══════╪════════════════════╡416│ 1 │ 2 │ The quick fox │417│ │ │ jumps over the │418│ │ │ lazy brown dog. │419╰───────┴───────┴────────────────────╯420}421do_test 2.4 {422 set result "\n[db format -widths {5 -5 -18} -wordwrap on \423 {SELECT * FROM t1}]"424} {425╭───────┬───────┬────────────────────╮426│ a │ b │ c │427╞═══════╪═══════╪════════════════════╡428│ 1 │ 2 │ The quick fox │429│ │ │ jumps over the │430│ │ │ lazy brown dog. │431╰───────┴───────┴────────────────────╯432}433do_test 2.5 {434 set result "\n[db format -widths {5 -5 19} -wordwrap off \435 {SELECT * FROM t1}]"436} {437╭───────┬───────┬─────────────────────╮438│ a │ b │ c │439╞═══════╪═══════╪═════════════════════╡440│ 1 │ 2 │ The quick fox jumps │441│ │ │ over the lazy brown │442│ │ │ dog. │443╰───────┴───────┴─────────────────────╯444}445do_test 2.6 {446 set result "\n[db format -widths {5 -5 18} -wordwrap off \447 {SELECT * FROM t1}]"448} {449╭───────┬───────┬────────────────────╮450│ a │ b │ c │451╞═══════╪═══════╪════════════════════╡452│ 1 │ 2 │ The quick fox jump │453│ │ │ s over the lazy br │454│ │ │ own dog. │455╰───────┴───────┴────────────────────╯456}457do_test 2.7 {458 set result "\n[db format -widths {5 5 18} -wordwrap yes \459 -align {left center right} -titlealign right \460 {SELECT * FROM t1}]"461} {462╭───────┬───────┬────────────────────╮463│ a │ b │ c │464╞═══════╪═══════╪════════════════════╡465│ 1 │ 2 │ The quick fox │466│ │ │ jumps over the │467│ │ │ lazy brown dog. │468╰───────┴───────┴────────────────────╯469}470do_test 2.8 {471 set result "\n[db format -widths {5 8 11} -wordwrap yes \472 -align {auto auto center} -titlealign left \473 -defaultalign right \474 {SELECT * FROM t1}]"475} {476╭───────┬──────────┬─────────────╮477│ a │ b │ c │478╞═══════╪══════════╪═════════════╡479│ 1 │ 2 │ The quick │480│ │ │ fox jumps │481│ │ │ over the │482│ │ │ lazy brown │483│ │ │ dog. │484╰───────┴──────────┴─────────────╯485}486do_test 2.9 {487 catch {db format -align {auto xyz 123} {SELECT * FROM t1}} res488 set res489} {bad column alignment (-align) "xyz": must be auto, bottom, c, center, e, left, middle, n, ne, nw, right, s, se, sw, top, or w}490do_test 2.10 {491 catch {db format -defaultalign xyz {SELECT * FROM t1}} res492 set res493} {bad default alignment (-defaultalign) "xyz": must be auto, bottom, c, center, e, left, middle, n, ne, nw, right, s, se, sw, top, or w}494do_test 2.11 {495 catch {db format -titlealign xyz {SELECT * FROM t1}} res496 set res497} {bad title alignment (-titlealign) "xyz": must be auto, bottom, c, center, e, left, middle, n, ne, nw, right, s, se, sw, top, or w}498 499 500do_execsql_test 2.30 {501 UPDATE t1 SET c='Η γρήγορη αλεπού πηδάει πάνω από το τεμπέλικο καφέ σκυλί';502 SELECT hex(c) FROM t1;503} {CE9720CEB3CF81CEAECEB3CEBFCF81CEB720CEB1CEBBCEB5CF80CEBFCF8D20CF80CEB7CEB4CEACCEB5CEB920CF80CEACCEBDCF8920CEB1CF80CF8C20CF84CEBF20CF84CEB5CEBCCF80CEADCEBBCEB9CEBACEBF20CEBACEB1CF86CEAD20CF83CEBACF85CEBBCEAF}504do_test 2.31 {505 set result "\n[db format -widths {5 -5 18} -wordwrap on \506 {SELECT * FROM t1}]"507} {508╭───────┬───────┬────────────────────╮509│ a │ b │ c │510╞═══════╪═══════╪════════════════════╡511│ 1 │ 2 │ Η γρήγορη αλεπού │512│ │ │ πηδάει πάνω από το │513│ │ │ τεμπέλικο καφέ │514│ │ │ σκυλί │515╰───────┴───────┴────────────────────╯516}517do_test 2.32 {518 set result "\n[db format -widths {5 5 18} -align {left center center} -wordwrap on \519 {SELECT * FROM t1}]"520} {521╭───────┬───────┬────────────────────╮522│ a │ b │ c │523╞═══════╪═══════╪════════════════════╡524│ 1 │ 2 │ Η γρήγορη αλεπού │525│ │ │ πηδάει πάνω από το │526│ │ │ τεμπέλικο καφέ │527│ │ │ σκυλί │528╰───────┴───────┴────────────────────╯529}530 531 532do_execsql_test 3.0 {533 DELETE FROM t1;534 INSERT INTO t1 VALUES(1,2,unistr('abc\u001b[1;31m123\u001b[0mxyz'));535}536do_test 3.1 {537 set result "\n[db format {SELECT * FROM t1}]"538} {539╭───┬───┬────────────────────────╮540│ a │ b │ c │541╞═══╪═══╪════════════════════════╡542│ 1 │ 2 │ abc^[[1;31m123^[[0mxyz │543╰───┴───┴────────────────────────╯544}545do_test 3.2 {546 set result "\n[db format -esc off {SELECT * FROM t1}]"547 string map [list \033 X] $result548} {549╭───┬───┬───────────╮550│ a │ b │ c │551╞═══╪═══╪═══════════╡552│ 1 │ 2 │ abcX[1;31m123X[0mxyz │553╰───┴───┴───────────╯554}555do_test 3.3 {556 set result "\n[db format -esc symbol {SELECT * FROM t1}]"557} {558╭───┬───┬──────────────────────╮559│ a │ b │ c │560╞═══╪═══╪══════════════════════╡561│ 1 │ 2 │ abc␛[1;31m123␛[0mxyz │562╰───┴───┴──────────────────────╯563}564do_test 3.4 {565 set result "\n[db format -esc ascii {SELECT * FROM t1}]"566} {567╭───┬───┬────────────────────────╮568│ a │ b │ c │569╞═══╪═══╪════════════════════════╡570│ 1 │ 2 │ abc^[[1;31m123^[[0mxyz │571╰───┴───┴────────────────────────╯572}573do_test 3.5 {574 catch {db format -esc unk {SELECT * FROM t1}} res575 set res576} {bad control character escape (-esc) "unk": must be ascii, auto, off, or symbol}577 578do_execsql_test 4.0 {579 DELETE FROM t1;580 INSERT INTO t1 VALUES(json('{a:5,b:6}'), jsonb('{c:1,d:2}'), 99);581}582do_test 4.1 {583 set result "\n[db format -text sql {SELECT * FROM t1}]"584} {585╭─────────────────┬───────────────────────┬────╮586│ a │ b │ c │587╞═════════════════╪═══════════════════════╪════╡588│ '{"a":5,"b":6}' │ x'8c1763133117641332' │ 99 │589╰─────────────────┴───────────────────────┴────╯590}591do_test 4.2 {592 set result "\n[db format -text sql -textjsonb on {SELECT * FROM t1}]"593} {594╭─────────────────┬────────────────────────┬────╮595│ a │ b │ c │596╞═════════════════╪════════════════════════╪════╡597│ '{"a":5,"b":6}' │ jsonb('{"c":1,"d":2}') │ 99 │598╰─────────────────┴────────────────────────┴────╯599}600do_test 4.3 {601 set result "\n[db format -text plain -textjsonb on -wrap 11 \602 {SELECT a AS json, b AS jsonb, c AS num FROM t1}]"603} {604╭─────────────┬─────────────┬─────╮605│ json │ jsonb │ num │606╞═════════════╪═════════════╪═════╡607│ {"a":5,"b": │ {"c":1,"d": │ 99 │608│ 6} │ 2} │ │609╰─────────────┴─────────────┴─────╯610}611 612do_execsql_test 5.0 {613 DROP TABLE t1;614 CREATE TABLE t1(name, mtime, value);615 INSERT INTO t1 VALUES616 ('entry-one',1708791504,zeroblob(300)),617 (unistr('one\u000atwo\u000athree'),1333206973,NULL),618 ('sample-jsonb',1333101221,jsonb('{619 "alpha":53.11688723,620 "beta":"qrfWidthPrint(p, p->pOut, -p->u.sLine.mxColWth);",621 "zeta":[15,null,1333206973,"fd8ffe000104a46494600010101"]}'));622}623do_test 5.1 {624 set sql {SELECT name, mtime, datetime(mtime,'unixepoch') AS time,625 value FROM t1 ORDER BY mtime}626 set result "\n[db format -style line -screenwidth 60 -blob sql \627 -text sql -wordwrap off -linelimit 77 \628 -columnsep { = } $sql]"629} {630 name = 'sample-jsonb'631mtime = 1333101221632 time = '2012-03-30 09:53:41'633value = x'cc7c57616c706861b535332e31313638383732334762657461634 c73071726657696474685072696e7428702c20702d3e704f7574635 2c202d702d3e752e734c696e652e6d78436f6c577468293b477a636 657461cb2c23313500a331333333323036393733c71b66643866637 6665303030313034613436343934363030303130313031'638 639 name = unistr('one\u000atwo\u000athree')640mtime = 1333206973641 time = '2012-03-31 15:16:13'642value = 643 644 name = 'entry-one'645mtime = 1708791504646 time = '2024-02-24 16:18:24'647value = x'00000000000000000000000000000000000000000000000000648 0000000000000000000000000000000000000000000000000000649 0000000000000000000000000000000000000000000000000000650 0000000000000000000000000000000000000000000000000000651 0000000000000000000000000000000000000000000000000000652 0000000000000000000000000000000000000000000000000000653 0000000000000000000000000000000000000000000000000000654 0000000000000000000000000000000000000000000000000000655 0000000000000000000000000000000000000000000000000000656 0000000000000000000000000000000000000000000000000000657 0000000000000000000000000000000000000000000000000000658 000000000000000000000000000000'659}660do_test 5.2a {661 set sql {SELECT name, mtime, datetime(mtime,'unixepoch') AS time,662 value FROM t1 ORDER BY mtime}663 set result "\n[db format -style line -screenwidth 60 -blob sql \664 -text plain -esc off -textjsonb yes -columnsep { = }\665 -wordwrap yes -linelimit 3 $sql]"666} {667 name = sample-jsonb668mtime = 1333101221669 time = 2012-03-30 09:53:41670value = {"alpha":53.11688723,"beta":"qrfWidthPrint(p,671 p->pOut, -p->u.sLine.mxColWth);","zeta":[15,null,672 1333206973,"fd8ffe000104a46494600010101"]}673 674 name = one675 two676 three677mtime = 1333206973678 time = 2012-03-31 15:16:13679value = 680 681 name = entry-one682mtime = 1708791504683 time = 2024-02-24 16:18:24684value = x'00000000000000000000000000000000000000000000000000685 0000000000000000000000000000000000000000000000000000686 0000000000000000000000000000000000000000000000000000687 ...688}689set sqlnolabel "SELECT name, mtime, datetime(mtime,'unixepoch'),\690 value FROM t1 ORDER BY mtime"691do_test 5.2b {692 set result "\n[db format -style line -screenwidth 60 -blob sql \693 -text plain -esc off -textjsonb no -titlelimit 12 \694 -wordwrap yes -linelimit 3 $sqlnolabel ]"695} {696 name: sample-jsonb697 mtime: 1333101221698datetime(...: 2012-03-30 09:53:41699 value: x'cc7c57616c706861b535332e31313638383732334762700 657461c73071726657696474685072696e7428702c2070701 2d3e704f75742c202d702d3e752e734c696e652e6d7843702 ...703 704 name: one705 two706 three707 mtime: 1333206973708datetime(...: 2012-03-31 15:16:13709 value: 710 711 name: entry-one712 mtime: 1708791504713datetime(...: 2024-02-24 16:18:24714 value: x'00000000000000000000000000000000000000000000715 0000000000000000000000000000000000000000000000716 0000000000000000000000000000000000000000000000717 ...718}719set sql "SELECT name, mtime, datetime(mtime,'unixepoch') AS time,\720 value FROM t1 ORDER BY mtime"721do_test 5.3a {722 set result "\n[db format -style box -widths {0 10 10 14}\723 -align {left right right center} \724 -blob sql \725 -text plain -esc off -textjsonb no \726 -wordwrap yes -linelimit 2 $sql]"727} {728╭──────────────┬────────────┬────────────┬────────────────╮729│ name │ mtime │ time │ value │730╞══════════════╪════════════╪════════════╪════════════════╡731│ sample-jsonb │ 1333101221 │ 2012-03-30 │ x'cc7c57616c70 │732│ │ │ 09:53:41 │ 6861b535332e31 │733│ │ │ │ ... │734├──────────────┼────────────┼────────────┼────────────────┤735│ one │ 1333206973 │ 2012-03-31 │ │736│ two │ │ 15:16:13 │ │737│ ... │ │ │ │738├──────────────┼────────────┼────────────┼────────────────┤739│ entry-one │ 1708791504 │ 2024-02-24 │ x'000000000000 │740│ │ │ 16:18:24 │ 00000000000000 │741│ │ │ │ ... │742╰──────────────┴────────────┴────────────┴────────────────╯743}744do_test 5.3b {745 set result "\n[db format -style box -widths {0 10 0 14} \746 -align {left right right center} \747 -blob sql -titlelimit 12 \748 -text plain -esc off -textjsonb no \749 -wordwrap yes -linelimit 2 $sqlnolabel ]"750} {751╭──────────────┬────────────┬─────────────────────┬────────────────╮752│ name │ mtime │ datetime(... │ value │753╞══════════════╪════════════╪═════════════════════╪════════════════╡754│ sample-jsonb │ 1333101221 │ 2012-03-30 09:53:41 │ x'cc7c57616c70 │755│ │ │ │ 6861b535332e31 │756│ │ │ │ ... │757├──────────────┼────────────┼─────────────────────┼────────────────┤758│ one │ 1333206973 │ 2012-03-31 15:16:13 │ │759│ two │ │ │ │760│ ... │ │ │ │761├──────────────┼────────────┼─────────────────────┼────────────────┤762│ entry-one │ 1708791504 │ 2024-02-24 16:18:24 │ x'000000000000 │763│ │ │ │ 00000000000000 │764│ │ │ │ ... │765╰──────────────┴────────────┴─────────────────────┴────────────────╯766}767do_test 5.3c {768 set result "\n[db format -style table -widths {0 10 10 14}\769 -align {center right right right} \770 -blob sql \771 -text plain -esc off -textjsonb no \772 -wordwrap yes -linelimit 2 $sql]"773} {774+--------------+------------+------------+----------------+775| name | mtime | time | value |776+--------------+------------+------------+----------------+777| sample-jsonb | 1333101221 | 2012-03-30 | x'cc7c57616c70 |778| | | 09:53:41 | 6861b535332e31 |779| | | | ... |780+--------------+------------+------------+----------------+781| one | 1333206973 | 2012-03-31 | |782| two | | 15:16:13 | |783| ... | | | |784+--------------+------------+------------+----------------+785| entry-one | 1708791504 | 2024-02-24 | x'000000000000 |786| | | 16:18:24 | 00000000000000 |787| | | | ... |788+--------------+------------+------------+----------------+789}790do_test 5.3c {791 set result "\n[db format -style column -widths {0 10 10 14}\792 -align {center right right right} \793 -blob sql \794 -text plain -esc off -textjsonb no \795 -wordwrap yes -linelimit 2 $sql]"796} {797 name mtime time value798------------ ---------- ---------- --------------799sample-jsonb 1333101221 2012-03-30 x'cc7c57616c70800 09:53:41 6861b535332e31801 ...802 803 one 1333206973 2012-03-31804 two 15:16:13805 ...806 807 entry-one 1708791504 2024-02-24 x'000000000000808 16:18:24 00000000000000809 ...810}811do_test 5.4 {812 db eval {813 CREATE TABLE t2(a,b,c,d,e);814 WITH v(x) AS (SELECT 'abcdefghijklmnopqrstuvwxyz')815 INSERT INTO t2 SELECT x,x,x,x,x FROM v;816 }817 set sql {SELECT char(0x61,0xa,0x62,0xa,0x63,0xa,0x64) a,818 mtime b, mtime c, mtime d, mtime e FROM t1}819 set result "\n[db format -style box -widths {1 2 3 4 5}\820 -linelimit 3 -wordwrap off {SELECT *, 'x' AS x FROM t2}]"821} {822╭────┬────┬─────┬──────┬───────┬───╮823│ a │ b │ c │ d │ e │ x │824╞════╪════╪═════╪══════╪═══════╪═══╡825│ ab │ ab │ abc │ abcd │ abcde │ x │826│ cd │ cd │ def │ efgh │ fghij │ │827│ ef │ ef │ ghi │ ijkl │ klmno │ │828│ .. │ .. │ ... │ ... │ ... │ │829╰────┴────┴─────┴──────┴───────┴───╯830}831 832do_execsql_test 6.0 {833 DELETE FROM t2;834 INSERT INTO t2 VALUES835 (1, 2.5, 'three', x'342028666f757229', null);836}837do_test 6.1a {838 set result "\n[db format -style list -null NULL \839 -text tcl -columnsep , \840 {SELECT * FROM t2}]"841} {8421,2.5,"three","\064\040\050\146\157\165\162\051",NULL843}844 845do_execsql_test 7.0 {846 CREATE TABLE t7(a,b);847 INSERT INTO t7 VALUES('abcdefghijklmnop',848 'abcぁdefかghiのjklはmnop');849}850do_test 7.1 {851 set result "\n[db format -style list -charlimit 13 \852 {SELECT * FROM t7}]"853} {854abcdefghijklm...|abcぁdefかghi...855}856do_test 7.2 {857 set result "\n[db format -style list -charlimit 14 \858 {SELECT * FROM t7}]"859} {860abcdefghijklmn...|abcぁdefかghi...861}862do_test 7.3 {863 set result "\n[db format -style list -charlimit 15 \864 {SELECT * FROM t7}]"865} {866abcdefghijklmno...|abcぁdefかghiの...867}868do_test 7.4 {869 set result "\n[db format -style list -charlimit 16 \870 {SELECT * FROM t7}]"871} {872abcdefghijklmnop|abcぁdefかghiのj...873}874 875do_test 8.0 {876 set result "\n[db format -style table {877 WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<10)878 SELECT 'aaa' AS 'a', format('%.*c',n,'b')||char(9)||'xx' AS x FROM c}]"879} {880+-----+--------------------+881| a | x |882+-----+--------------------+883| aaa | b xx |884| aaa | bb xx |885| aaa | bbb xx |886| aaa | bbbb xx |887| aaa | bbbbb xx |888| aaa | bbbbbb xx |889| aaa | bbbbbbb xx |890| aaa | bbbbbbbb xx |891| aaa | bbbbbbbbb xx |892| aaa | bbbbbbbbbb xx |893+-----+--------------------+894}895do_test 8.1 {896 set result "\n[db format -style table {897 WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<10)898 SELECT 'aaaa' AS 'a', format('%.*c',n,'b')||char(9)||'xx' AS x FROM c}]"899} {900+------+--------------------+901| a | x |902+------+--------------------+903| aaaa | b xx |904| aaaa | bb xx |905| aaaa | bbb xx |906| aaaa | bbbb xx |907| aaaa | bbbbb xx |908| aaaa | bbbbbb xx |909| aaaa | bbbbbbb xx |910| aaaa | bbbbbbbb xx |911| aaaa | bbbbbbbbb xx |912| aaaa | bbbbbbbbbb xx |913+------+--------------------+914}915do_test 8.3 {916 set result "\n[db format -style table -esc off {917 WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<15)918 SELECT 'aaa' AS 'a',n, 'xx'||char(n)||'yy' AS xy FROM c919 WHERE n NOT IN (8,10,13,14)}]"920} {921+-----+----+------------+922| a | n | xy |923+-----+----+------------+924| aaa | 1 | xx␁yy |925| aaa | 2 | xx␂yy |926| aaa | 3 | xx␃yy |927| aaa | 4 | xx␄yy |928| aaa | 5 | xx␅yy |929| aaa | 6 | xx␆yy |930| aaa | 7 | xx␇yy |931| aaa | 9 | xx yy |932| aaa | 11 | xx␋yy |933| aaa | 12 | xx␌yy |934| aaa | 15 | xx␏yy |935+-----+----+------------+936}937do_test 8.4 {938 set result "\n[db format -style table -esc off {939 WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<15)940 SELECT 'aaa' AS 'a',n, 'xx'||char(n)||'yy'||char(9)||'zz' AS xyz FROM c941 WHERE n NOT IN (8,10,13,14)}]"942} {943+-----+----+--------------------+944| a | n | xyz |945+-----+----+--------------------+946| aaa | 1 | xx␁yy zz |947| aaa | 2 | xx␂yy zz |948| aaa | 3 | xx␃yy zz |949| aaa | 4 | xx␄yy zz |950| aaa | 5 | xx␅yy zz |951| aaa | 6 | xx␆yy zz |952| aaa | 7 | xx␇yy zz |953| aaa | 9 | xx yy zz |954| aaa | 11 | xx␋yy zz |955| aaa | 12 | xx␌yy zz |956| aaa | 15 | xx␏yy zz |957+-----+----+--------------------+958}959 960do_test 9.1 {961 db eval {962 CREATE TABLE t9(x);963 INSERT INTO t9 VALUES964 (x'4331323334'),965 (x'c30431323334'),966 (x'd3000431323334'),967 (x'e30000000431323334'),968 (x'f3000000000000000431323334');969 }970 db format -style list -text plain -rowsep , -textjsonb on \971 {SELECT * FROM t9}972} {1234,1234,1234,1234,1234,}973do_test 9.2 {974 db format -style list -text sql -rowsep , -textjsonb on \975 {SELECT * FROM t9}976} {jsonb('1234'),jsonb('1234'),jsonb('1234'),jsonb('1234'),jsonb('1234'),}977do_test 9.3 {978 db format -style json {SELECT * FROM t9 WHERE rowid<0}979} {}980do_test 9.4 {981 db format -style jobject {SELECT * FROM t9 WHERE rowid<0}982} {}983 984do_test 10.1 {985 db eval {986 DROP TABLE IF EXISTS t1;987 CREATE TABLE t1(x);988 INSERT INTO t1(x) VALUES989 ('alice'),990 ('bob'),991 ('cinderella-cinderella'),992 ('daniel'),993 ('emma'),994 ('fred'),995 ('gertrude'),996 ('harold'),997 ('ingrid'),998 ('jake'),999 ('lisa'),1000 ('mike'),1001 ('nina'),1002 ('octavian'),1003 ('paula'),1004 ('quintus'),1005 ('rita'),1006 ('sam'),1007 ('tammy'),1008 ('ulysses'),1009 ('violet'),1010 ('william'),1011 ('xanthippe'),1012 ('yates'),1013 ('zoe');1014 }1015 set result "\n[db format -style column -title off -screenwidth 41 -splitcolumn on \1016 {SELECT x FROM t1}]"1017} {1018alice octavian1019bob paula1020cinderella-cinderella quintus1021daniel rita1022emma sam1023fred tammy1024gertrude ulysses1025harold violet1026ingrid william1027jake xanthippe1028lisa yates1029mike zoe1030nina1031}1032do_test 10.2 {1033 set result "\n[db format -style column -title off -screenwidth 42 -splitcolumn on \1034 {SELECT x FROM t1}]"1035} {1036alice jake tammy1037bob lisa ulysses1038cinderella-cinderella mike violet1039daniel nina william1040emma octavian xanthippe1041fred paula yates1042gertrude quintus zoe1043harold rita1044ingrid sam1045}1046do_test 10.3 {1047 set result "\n[db format -style column -title off -screenwidth 51 -splitcolumn on \1048 {SELECT x FROM t1}]"1049} {1050alice harold paula william1051bob ingrid quintus xanthippe1052cinderella-cinderella jake rita yates1053daniel lisa sam zoe1054emma mike tammy1055fred nina ulysses1056gertrude octavian violet1057}1058do_test 10.4 {1059 set result "\n[db format -style column -title off -screenwidth 61 -splitcolumn on \1060 {SELECT x FROM t1}]"1061} {1062alice fred lisa quintus violet1063bob gertrude mike rita william1064cinderella-cinderella harold nina sam xanthippe1065daniel ingrid octavian tammy yates1066emma jake paula ulysses zoe1067}1068do_test 10.5 {1069 set result "\n[db format -style column -title off -screenwidth 74 -splitcolumn on \1070 {SELECT x FROM t1}]"1071} {1072alice emma ingrid nina rita violet zoe1073bob fred jake octavian sam william1074cinderella-cinderella gertrude lisa paula tammy xanthippe1075daniel harold mike quintus ulysses yates1076}1077 1078do_test 11.1 {1079 set result "\n[db format -style table -blob size {SELECT randomblob(1234)}]"1080} {1081+------------------+1082| randomblob(1234) |1083+------------------+1084| (1234-byte blob) |1085+------------------+1086}1087 1088do_test 12.1 {1089 set result "\n[db format -style box -text html \1090 {SELECT 'abc','','xyz'}]"1091} {1092╭───────┬────┬───────╮1093│ 'abc' │ '' │ 'xyz' │1094╞═══════╪════╪═══════╡1095│ abc │ │ xyz │1096╰───────┴────┴───────╯1097}1098 1099# Tests for "relaxed" quoting1100#1101do_test 13.2 {1102 db eval {1103 CREATE TABLE t13(a,b);1104 INSERT INTO t13(a,b) VALUES1105 (1,'NULL'),1106 (0,'-NULL-'),1107 (0,''),1108 (1,'''abcde'),1109 (1,'abcde'''),1110 (0,'abcde'),1111 (1,' abcde'),1112 (1,'abcde '),1113 (1,'+0'),1114 (1,'-0'),1115 (1,'012345'),1116 (0,'012xyz345'),1117 (1,'0123.45'),1118 (0,'12.34.56'),1119 (0,'12.3e'),1120 (1,'12.3e+123'),1121 (1,'12.3e-34'),1122 (1,'12.3E56'),1123 (1,'12E56'),1124 (0,'12.5E5.6'),1125 (0,'12.5e+'),1126 (0,'12.5e-'),1127 (1,'+Inf'),(1,'-Inf'),(1,'Inf');1128 }1129 set result \n[db format -style box -text relaxed -null NULL \1130 -align {center left} \1131 {SELECT if(a,'yes','') AS 'quoted?', b AS string1132 FROM t13 ORDER BY rowid}]1133} {1134╭─────────┬─────────────╮1135│ quoted? │ string │1136╞═════════╪═════════════╡1137│ yes │ 'NULL' │1138│ │ -NULL- │1139│ │ │1140│ yes │ '''abcde' │1141│ yes │ 'abcde''' │1142│ │ abcde │1143│ yes │ ' abcde' │1144│ yes │ 'abcde ' │1145│ yes │ '+0' │1146│ yes │ '-0' │1147│ yes │ '012345' │1148│ │ 012xyz345 │1149│ yes │ '0123.45' │1150│ │ 12.34.56 │1151│ │ 12.3e │1152│ yes │ '12.3e+123' │1153│ yes │ '12.3e-34' │1154│ yes │ '12.3E56' │1155│ yes │ '12E56' │1156│ │ 12.5E5.6 │1157│ │ 12.5e+ │1158│ │ 12.5e- │1159│ yes │ '+Inf' │1160│ yes │ '-Inf' │1161│ yes │ 'Inf' │1162╰─────────┴─────────────╯1163}1164 1165db close1166 1167finish_test1168