Debian Codegraph @

test/qrf01.test

raw
1 # 2025-11-05
2 #
3 # The author disclaims copyright to this source code. In place of
4 # 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
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix qrf01
18
19 do_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
24 do_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 }
34 do_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 }
42 do_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 }
52 do_test 1.11c {
53 set result "\n[db format -text sql -border off {SELECT * FROM t1}]"
54 } {
55 a │ b │ c
56 ═════════════╪═════╪═════════
57 1 │ 2.5 │ 'three'
58 x'424c4f42' │ │ 'Ἀμήν'
59 }
60 do_test 1.11d {
61 set result "\n[db format -text relaxed -blob sql -border off \
62 {SELECT * FROM t1}]"
63 } {
64 a │ b │ c
65 ═════════════╪═════╪═══════
66 1 │ 2.5 │ three
67 x'424c4f42' │ │ Ἀμήν
68 }
69 do_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 }
79 do_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 }
89 do_test 1.14 {
90 catch {db format -text unk -blob hex {SELECT * FROM t1}} res
91 set res
92 } {bad -text "unk": must be auto, csv, html, json, plain, relaxed, sql, or tcl}
93 do_test 1.15 {
94 catch {db format -text sql -blob unk {SELECT * FROM t1}} res
95 set res
96 } {bad BLOB encoding (-blob) "unk": must be auto, hex, json, tcl, text, sql, or size}
97 do_test 1.16 {
98 catch {db format -text sql -style unk {SELECT * FROM t1}} res
99 set res
100 } {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
103 do_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
114 do_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 }
124 do_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 }
132 do_test 1.32 {
133 set result "\n[db format -style table -border off {SELECT * FROM t1}]"
134 } {
135 a | b | c
136 ------+-----+-------
137 1 | 2.5 | three
138 BLOB | | Ἀμήν
139 }
140 do_test 1.33 {
141 set result "\n[db format -style table -border off \
142 -screenwidth 15 \
143 {SELECT * FROM t1}]"
144 } {
145 a | b | c
146 ----+---+-----
147 1|2.5|three
148 BLOB| |Ἀμήν
149 }
150 do_test 1.34 {
151 set result "\n[db format -style box -border off \
152 -screenwidth 30 \
153 {SELECT * FROM t1}]"
154 } {
155 a │ b │ c
156 ══════╪═════╪═══════
157 1 │ 2.5 │ three
158 BLOB │ │ Ἀμήν
159 }
160 do_test 1.35 {
161 set result "\n[db format -style box -border off \
162 -screenwidth 15 \
163 {SELECT * FROM t1}]"
164 } {
165 a │ b │ c
166 ════╪═══╪═════
167 1│2.5│three
168 BLOB│ │Ἀμήν
169 }
170
171 do_test 1.40 {
172 set result "\n[db format -style column {SELECT * FROM t1}]"
173 } {
174 a b c
175 ---- --- -----
176 1 2.5 three
177 BLOB Ἀμήν
178 }
179 do_test 1.41 {
180 set result "\n[db format -style column -title off {SELECT * FROM t1}]"
181 } {
182 1 2.5 three
183 BLOB Ἀμήν
184 }
185
186 do_test 1.50 {
187 db format -style count {SELECT * FROM t1}
188 } 2
189
190 do_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"
193 do_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"
196 do_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"
199 do_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"
202 do_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"
205 do_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
209 do_test 1.70 {
210 set result "\n[db format -style html {SELECT * FROM t1}]"
211 } {
212 <TR>
213 <TD>1
214 <TD>2.5
215 <TD>three
216 </TR>
217 <TR>
218 <TD>BLOB
219 <TD>null
220 <TD>Ἀμήν
221 </TR>
222 }
223 do_test 1.71 {
224 set result "\n[db format -style html -title auto {SELECT * FROM t1}]"
225 } {
226 <TR>
227 <TH>a
228 <TH>b
229 <TH>c
230 </TR>
231 <TR>
232 <TD>1
233 <TD>2.5
234 <TD>three
235 </TR>
236 <TR>
237 <TD>BLOB
238 <TD>null
239 <TD>Ἀμήν
240 </TR>
241 }
242 do_test 1.72 {
243 set result "\n[db format -style html {
244 SELECT 'ab<cd' AS a, 'ab&cd' as b,
245 'ab>cd' AS c, 'ab"cd' AS d,
246 'xy''z' AS e}]"
247 } {
248 <TR>
249 <TD>ab&lt;cd
250 <TD>ab&amp;cd
251 <TD>ab&gt;cd
252 <TD>ab&quot;cd
253 <TD>xy&#39;z
254 </TR>
255 }
256
257 do_test 1.80 {
258 set result "\n[db format -style insert {SELECT * FROM t1}]"
259 } {
260 INSERT INTO tab VALUES(1,2.5,'three');
261 INSERT INTO tab VALUES(x'424c4f42',NULL,'Ἀμήν');
262 }
263 do_test 1.81 {
264 set result "\n[db format -style insert -tablename t1 {SELECT * FROM t1}]"
265 } {
266 INSERT INTO t1 VALUES(1,2.5,'three');
267 INSERT INTO t1 VALUES(x'424c4f42',NULL,'Ἀμήν');
268 }
269 do_test 1.82 {
270 set result "\n[db format -style insert -tablename t1 -title auto \
271 {SELECT * FROM t1}]"
272 } {
273 INSERT INTO t1(a,b,c) VALUES(1,2.5,'three');
274 INSERT INTO t1(a,b,c) VALUES(x'424c4f42',NULL,'Ἀμήν');
275 }
276 do_test 1.83 {
277 set result "\n[db format -style insert -tablename drop -title on \
278 {SELECT a AS "a-b", b, c AS "123" FROM t1}]"
279 } {
280 INSERT INTO "drop"("a-b",b,"123") VALUES(1,2.5,'three');
281 INSERT INTO "drop"("a-b",b,"123") VALUES(x'424c4f42',NULL,'Ἀμήν');
282 }
283 do_test 1.84 {
284 set result "\n[db format -style insert {SELECT * FROM t1} -multiinsert 2000]"
285 } {
286 INSERT INTO tab VALUES(1,2.5,'three'),
287 (x'424c4f42',NULL,'Ἀμήν');
288 }
289 do_test 1.85 {
290 set result "\n[db format -style insert {
291 WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<7)
292 SELECT n, n*10 AS m FROM c;} -multiinsert 70]"
293 } {
294 INSERT INTO tab VALUES(1,10),
295 (2,20),
296 (3,30),
297 (4,40),
298 (5,50);
299 INSERT INTO tab VALUES(6,60),
300 (7,70);
301 }
302
303 do_test 1.90 {
304 set result "\n[db format -style json {SELECT * FROM t1}]"
305 } {
306 [{"a":1,"b":2.5,"c":"three"},
307 {"a":"\u0042\u004c\u004f\u0042","b":null,"c":"Ἀμήν"}]
308 }
309 do_test 1.91 {
310 set result "\n[db format -style jobject {SELECT * FROM t1}]"
311 } {
312 {"a":1,"b":2.5,"c":"three"}
313 {"a":"\u0042\u004c\u004f\u0042","b":null,"c":"Ἀμήν"}
314 }
315 do_test 1.92 {
316 set result "\n[db format -style jobject {SELECT *, unistr('abc\u000a123\u000d\u000axyz') AS xyz FROM t1}]"
317 } {
318 {"a":1,"b":2.5,"c":"three","xyz":"abc\n123\r\nxyz"}
319 {"a":"\u0042\u004c\u004f\u0042","b":null,"c":"Ἀμήν","xyz":"abc\n123\r\nxyz"}
320 }
321
322 do_test 1.100 {
323 set result "\n[db format -style line {SELECT * FROM t1}]"
324 } {
325 a: 1
326 b: 2.5
327 c: three
328
329 a: BLOB
330 b:
331 c: Ἀμήν
332 }
333 do_test 1.101 {
334 set result "\n[db format -style line -null (NULL) {SELECT * FROM t1}]"
335 } {
336 a: 1
337 b: 2.5
338 c: three
339
340 a: BLOB
341 b: (NULL)
342 c: Ἀμήν
343 }
344 do_test 1.102 {
345 set result "\n[db format -style line -null (NULL) -columnsep { = } \
346 -text sql {SELECT * FROM t1}]"
347 } {
348 a = 1
349 b = 2.5
350 c = 'three'
351
352 a = x'424c4f42'
353 b = (NULL)
354 c = 'Ἀμήν'
355 }
356
357 do_test 1.110 {
358 set result "\n[db format -style list {SELECT * FROM t1}]"
359 } {
360 1|2.5|three
361 BLOB||Ἀμήν
362 }
363 do_test 1.111 {
364 set result "\n[db format -style list -title on {SELECT * FROM t1}]"
365 } {
366 a|b|c
367 1|2.5|three
368 BLOB||Ἀμήν
369 }
370 do_test 1.112 {
371 set result "\n[db format -style list -title on -text sql -null NULL \
372 -title plain {SELECT * FROM t1}]"
373 } {
374 a|b|c
375 1|2.5|'three'
376 x'424c4f42'|NULL|'Ἀμήν'
377 }
378 do_test 1.118 {
379 set rc [catch {db format -style list -title unk {SELECT * FROM t1}} res]
380 lappend rc $res
381 } {1 {bad -title "unk": must be off, on, auto, csv, html, json, plain, relaxed, sql, or tcl}}
382
383
384 do_test 1.120 {
385 set result "\n[db format -style markdown {SELECT * FROM t1}]"
386 } {
387 | a | b | c |
388 |------|-----|-------|
389 | 1 | 2.5 | three |
390 | BLOB | | Ἀμήν |
391 }
392 do_test 1.121 {
393 set result "\n[db format -style markdown -title off {SELECT * FROM t1}]"
394 } {
395 | 1 | 2.5 | three |
396 | BLOB | | Ἀμήν |
397 }
398
399 do_test 1.130 {
400 set result "\n[db format -style quote {SELECT * FROM t1}]"
401 } {
402 1,2.5,'three'
403 x'424c4f42',NULL,'Ἀμήν'
404 }
405 do_test 1.131 {
406 set result "\n[db format -style quote -title on {SELECT * FROM t1}]"
407 } {
408 'a','b','c'
409 1,2.5,'three'
410 x'424c4f42',NULL,'Ἀμήν'
411 }
412
413
414 do_execsql_test 2.0 {
415 DELETE FROM t1;
416 INSERT INTO t1 VALUES(1,2,'The quick fox jumps over the lazy brown dog.');
417 }
418 do_test 2.1 {
419 set result "\n[db format -widths {5 -5 19} -wordwrap on \
420 {SELECT * FROM t1}]"
421 } {
422 ╭───────┬───────┬─────────────────────╮
423 │ a │ b │ c │
424 ╞═══════╪═══════╪═════════════════════╡
425 │ 1 │ 2 │ The quick fox jumps │
426 │ │ │ over the lazy brown │
427 │ │ │ dog. │
428 ╰───────┴───────┴─────────────────────╯
429 }
430 do_test 2.2 {
431 set result "\n[db format -widths {5 -5 19} -wordwrap off \
432 {SELECT * FROM t1}]"
433 } {
434 ╭───────┬───────┬─────────────────────╮
435 │ a │ b │ c │
436 ╞═══════╪═══════╪═════════════════════╡
437 │ 1 │ 2 │ The quick fox jumps │
438 │ │ │ over the lazy brown │
439 │ │ │ dog. │
440 ╰───────┴───────┴─────────────────────╯
441 }
442 do_test 2.3 {
443 set result "\n[db format -widths {5 -5 18} -wordwrap on \
444 {SELECT * FROM t1}]"
445 } {
446 ╭───────┬───────┬────────────────────╮
447 │ a │ b │ c │
448 ╞═══════╪═══════╪════════════════════╡
449 │ 1 │ 2 │ The quick fox │
450 │ │ │ jumps over the │
451 │ │ │ lazy brown dog. │
452 ╰───────┴───────┴────────────────────╯
453 }
454 do_test 2.4 {
455 set result "\n[db format -widths {5 -5 -18} -wordwrap on \
456 {SELECT * FROM t1}]"
457 } {
458 ╭───────┬───────┬────────────────────╮
459 │ a │ b │ c │
460 ╞═══════╪═══════╪════════════════════╡
461 │ 1 │ 2 │ The quick fox │
462 │ │ │ jumps over the │
463 │ │ │ lazy brown dog. │
464 ╰───────┴───────┴────────────────────╯
465 }
466 do_test 2.5 {
467 set result "\n[db format -widths {5 -5 19} -wordwrap off \
468 {SELECT * FROM t1}]"
469 } {
470 ╭───────┬───────┬─────────────────────╮
471 │ a │ b │ c │
472 ╞═══════╪═══════╪═════════════════════╡
473 │ 1 │ 2 │ The quick fox jumps │
474 │ │ │ over the lazy brown │
475 │ │ │ dog. │
476 ╰───────┴───────┴─────────────────────╯
477 }
478 do_test 2.6 {
479 set result "\n[db format -widths {5 -5 18} -wordwrap off \
480 {SELECT * FROM t1}]"
481 } {
482 ╭───────┬───────┬────────────────────╮
483 │ a │ b │ c │
484 ╞═══════╪═══════╪════════════════════╡
485 │ 1 │ 2 │ The quick fox jump │
486 │ │ │ s over the lazy br │
487 │ │ │ own dog. │
488 ╰───────┴───────┴────────────────────╯
489 }
490 do_test 2.7 {
491 set result "\n[db format -widths {5 5 18} -wordwrap yes \
492 -align {left center right} -titlealign right \
493 {SELECT * FROM t1}]"
494 } {
495 ╭───────┬───────┬────────────────────╮
496 │ a │ b │ c │
497 ╞═══════╪═══════╪════════════════════╡
498 │ 1 │ 2 │ The quick fox │
499 │ │ │ jumps over the │
500 │ │ │ lazy brown dog. │
501 ╰───────┴───────┴────────────────────╯
502 }
503 do_test 2.8 {
504 set result "\n[db format -widths {5 8 11} -wordwrap yes \
505 -align {auto auto center} -titlealign left \
506 -defaultalign right \
507 {SELECT * FROM t1}]"
508 } {
509 ╭───────┬──────────┬─────────────╮
510 │ a │ b │ c │
511 ╞═══════╪══════════╪═════════════╡
512 │ 1 │ 2 │ The quick │
513 │ │ │ fox jumps │
514 │ │ │ over the │
515 │ │ │ lazy brown │
516 │ │ │ dog. │
517 ╰───────┴──────────┴─────────────╯
518 }
519 do_test 2.9 {
520 catch {db format -align {auto xyz 123} {SELECT * FROM t1}} res
521 set res
522 } {bad column alignment (-align) "xyz": must be auto, bottom, c, center, e, left, middle, n, ne, nw, right, s, se, sw, top, or w}
523 do_test 2.10 {
524 catch {db format -defaultalign xyz {SELECT * FROM t1}} res
525 set res
526 } {bad default alignment (-defaultalign) "xyz": must be auto, bottom, c, center, e, left, middle, n, ne, nw, right, s, se, sw, top, or w}
527 do_test 2.11 {
528 catch {db format -titlealign xyz {SELECT * FROM t1}} res
529 set res
530 } {bad title alignment (-titlealign) "xyz": must be auto, bottom, c, center, e, left, middle, n, ne, nw, right, s, se, sw, top, or w}
531
532
533 do_execsql_test 2.30 {
534 UPDATE t1 SET c='Η γρήγορη αλεπού πηδάει πάνω από το τεμπέλικο καφέ σκυλί';
535 SELECT hex(c) FROM t1;
536 } {CE9720CEB3CF81CEAECEB3CEBFCF81CEB720CEB1CEBBCEB5CF80CEBFCF8D20CF80CEB7CEB4CEACCEB5CEB920CF80CEACCEBDCF8920CEB1CF80CF8C20CF84CEBF20CF84CEB5CEBCCF80CEADCEBBCEB9CEBACEBF20CEBACEB1CF86CEAD20CF83CEBACF85CEBBCEAF}
537 do_test 2.31 {
538 set result "\n[db format -widths {5 -5 18} -wordwrap on \
539 {SELECT * FROM t1}]"
540 } {
541 ╭───────┬───────┬────────────────────╮
542 │ a │ b │ c │
543 ╞═══════╪═══════╪════════════════════╡
544 │ 1 │ 2 │ Η γρήγορη αλεπού │
545 │ │ │ πηδάει πάνω από το │
546 │ │ │ τεμπέλικο καφέ │
547 │ │ │ σκυλί │
548 ╰───────┴───────┴────────────────────╯
549 }
550 do_test 2.32 {
551 set result "\n[db format -widths {5 5 18} -align {left center center} -wordwrap on \
552 {SELECT * FROM t1}]"
553 } {
554 ╭───────┬───────┬────────────────────╮
555 │ a │ b │ c │
556 ╞═══════╪═══════╪════════════════════╡
557 │ 1 │ 2 │ Η γρήγορη αλεπού │
558 │ │ │ πηδάει πάνω από το │
559 │ │ │ τεμπέλικο καφέ │
560 │ │ │ σκυλί │
561 ╰───────┴───────┴────────────────────╯
562 }
563
564
565 do_execsql_test 3.0 {
566 DELETE FROM t1;
567 INSERT INTO t1 VALUES(1,2,unistr('abc\u001b[1;31m123\u001b[0mxyz'));
568 }
569 do_test 3.1 {
570 set result "\n[db format {SELECT * FROM t1}]"
571 } {
572 ╭───┬───┬────────────────────────╮
573 │ a │ b │ c │
574 ╞═══╪═══╪════════════════════════╡
575 │ 1 │ 2 │ abc^[[1;31m123^[[0mxyz │
576 ╰───┴───┴────────────────────────╯
577 }
578 do_test 3.2 {
579 set result "\n[db format -esc off {SELECT * FROM t1}]"
580 string map [list \033 X] $result
581 } {
582 ╭───┬───┬───────────╮
583 │ a │ b │ c │
584 ╞═══╪═══╪═══════════╡
585 │ 1 │ 2 │ abcX[1;31m123X[0mxyz │
586 ╰───┴───┴───────────╯
587 }
588 do_test 3.3 {
589 set result "\n[db format -esc symbol {SELECT * FROM t1}]"
590 } {
591 ╭───┬───┬──────────────────────╮
592 │ a │ b │ c │
593 ╞═══╪═══╪══════════════════════╡
594 │ 1 │ 2 │ abc␛[1;31m123␛[0mxyz │
595 ╰───┴───┴──────────────────────╯
596 }
597 do_test 3.4 {
598 set result "\n[db format -esc ascii {SELECT * FROM t1}]"
599 } {
600 ╭───┬───┬────────────────────────╮
601 │ a │ b │ c │
602 ╞═══╪═══╪════════════════════════╡
603 │ 1 │ 2 │ abc^[[1;31m123^[[0mxyz │
604 ╰───┴───┴────────────────────────╯
605 }
606 do_test 3.5 {
607 catch {db format -esc unk {SELECT * FROM t1}} res
608 set res
609 } {bad control character escape (-esc) "unk": must be ascii, auto, off, or symbol}
610
611 do_execsql_test 4.0 {
612 DELETE FROM t1;
613 INSERT INTO t1 VALUES(json('{a:5,b:6}'), jsonb('{c:1,d:2}'), 99);
614 }
615 do_test 4.1 {
616 set result "\n[db format -text sql {SELECT * FROM t1}]"
617 } {
618 ╭─────────────────┬───────────────────────┬────╮
619 │ a │ b │ c │
620 ╞═════════════════╪═══════════════════════╪════╡
621 │ '{"a":5,"b":6}' │ x'8c1763133117641332' │ 99 │
622 ╰─────────────────┴───────────────────────┴────╯
623 }
624 do_test 4.2 {
625 set result "\n[db format -text sql -textjsonb on {SELECT * FROM t1}]"
626 } {
627 ╭─────────────────┬────────────────────────┬────╮
628 │ a │ b │ c │
629 ╞═════════════════╪════════════════════════╪════╡
630 │ '{"a":5,"b":6}' │ jsonb('{"c":1,"d":2}') │ 99 │
631 ╰─────────────────┴────────────────────────┴────╯
632 }
633 do_test 4.3 {
634 set result "\n[db format -text plain -textjsonb on -wrap 11 \
635 {SELECT a AS json, b AS jsonb, c AS num FROM t1}]"
636 } {
637 ╭─────────────┬─────────────┬─────╮
638 │ json │ jsonb │ num │
639 ╞═════════════╪═════════════╪═════╡
640 │ {"a":5,"b": │ {"c":1,"d": │ 99 │
641 │ 6} │ 2} │ │
642 ╰─────────────┴─────────────┴─────╯
643 }
644
645 do_execsql_test 5.0 {
646 DROP TABLE t1;
647 CREATE TABLE t1(name, mtime, value);
648 INSERT INTO t1 VALUES
649 ('entry-one',1708791504,zeroblob(300)),
650 (unistr('one\u000atwo\u000athree'),1333206973,NULL),
651 ('sample-jsonb',1333101221,jsonb('{
652 "alpha":53.11688723,
653 "beta":"qrfWidthPrint(p, p->pOut, -p->u.sLine.mxColWth);",
654 "zeta":[15,null,1333206973,"fd8ffe000104a46494600010101"]}'));
655 }
656 do_test 5.1 {
657 set sql {SELECT name, mtime, datetime(mtime,'unixepoch') AS time,
658 value FROM t1 ORDER BY mtime}
659 set result "\n[db format -style line -screenwidth 60 -blob sql \
660 -text sql -wordwrap off -linelimit 77 \
661 -columnsep { = } $sql]"
662 } {
663 name = 'sample-jsonb'
664 mtime = 1333101221
665 time = '2012-03-30 09:53:41'
666 value = x'cc7c57616c706861b535332e31313638383732334762657461
667 c73071726657696474685072696e7428702c20702d3e704f7574
668 2c202d702d3e752e734c696e652e6d78436f6c577468293b477a
669 657461cb2c23313500a331333333323036393733c71b66643866
670 6665303030313034613436343934363030303130313031'
671
672 name = unistr('one\u000atwo\u000athree')
673 mtime = 1333206973
674 time = '2012-03-31 15:16:13'
675 value =
676
677 name = 'entry-one'
678 mtime = 1708791504
679 time = '2024-02-24 16:18:24'
680 value = x'00000000000000000000000000000000000000000000000000
681 0000000000000000000000000000000000000000000000000000
682 0000000000000000000000000000000000000000000000000000
683 0000000000000000000000000000000000000000000000000000
684 0000000000000000000000000000000000000000000000000000
685 0000000000000000000000000000000000000000000000000000
686 0000000000000000000000000000000000000000000000000000
687 0000000000000000000000000000000000000000000000000000
688 0000000000000000000000000000000000000000000000000000
689 0000000000000000000000000000000000000000000000000000
690 0000000000000000000000000000000000000000000000000000
691 000000000000000000000000000000'
692 }
693 do_test 5.2a {
694 set sql {SELECT name, mtime, datetime(mtime,'unixepoch') AS time,
695 value FROM t1 ORDER BY mtime}
696 set result "\n[db format -style line -screenwidth 60 -blob sql \
697 -text plain -esc off -textjsonb yes -columnsep { = }\
698 -wordwrap yes -linelimit 3 $sql]"
699 } {
700 name = sample-jsonb
701 mtime = 1333101221
702 time = 2012-03-30 09:53:41
703 value = {"alpha":53.11688723,"beta":"qrfWidthPrint(p,
704 p->pOut, -p->u.sLine.mxColWth);","zeta":[15,null,
705 1333206973,"fd8ffe000104a46494600010101"]}
706
707 name = one
708 two
709 three
710 mtime = 1333206973
711 time = 2012-03-31 15:16:13
712 value =
713
714 name = entry-one
715 mtime = 1708791504
716 time = 2024-02-24 16:18:24
717 value = x'00000000000000000000000000000000000000000000000000
718 0000000000000000000000000000000000000000000000000000
719 0000000000000000000000000000000000000000000000000000
720 ...
721 }
722 set sqlnolabel "SELECT name, mtime, datetime(mtime,'unixepoch'),\
723 value FROM t1 ORDER BY mtime"
724 do_test 5.2b {
725 set result "\n[db format -style line -screenwidth 60 -blob sql \
726 -text plain -esc off -textjsonb no -titlelimit 12 \
727 -wordwrap yes -linelimit 3 $sqlnolabel ]"
728 } {
729 name: sample-jsonb
730 mtime: 1333101221
731 datetime(...: 2012-03-30 09:53:41
732 value: x'cc7c57616c706861b535332e31313638383732334762
733 657461c73071726657696474685072696e7428702c2070
734 2d3e704f75742c202d702d3e752e734c696e652e6d7843
735 ...
736
737 name: one
738 two
739 three
740 mtime: 1333206973
741 datetime(...: 2012-03-31 15:16:13
742 value:
743
744 name: entry-one
745 mtime: 1708791504
746 datetime(...: 2024-02-24 16:18:24
747 value: x'00000000000000000000000000000000000000000000
748 0000000000000000000000000000000000000000000000
749 0000000000000000000000000000000000000000000000
750 ...
751 }
752 set sql "SELECT name, mtime, datetime(mtime,'unixepoch') AS time,\
753 value FROM t1 ORDER BY mtime"
754 do_test 5.3a {
755 set result "\n[db format -style box -widths {0 10 10 14}\
756 -align {left right right center} \
757 -blob sql \
758 -text plain -esc off -textjsonb no \
759 -wordwrap yes -linelimit 2 $sql]"
760 } {
761 ╭──────────────┬────────────┬────────────┬────────────────╮
762 │ name │ mtime │ time │ value │
763 ╞══════════════╪════════════╪════════════╪════════════════╡
764 │ sample-jsonb │ 1333101221 │ 2012-03-30 │ x'cc7c57616c70 │
765 │ │ │ 09:53:41 │ 6861b535332e31 │
766 │ │ │ │ ... │
767 ├──────────────┼────────────┼────────────┼────────────────┤
768 │ one │ 1333206973 │ 2012-03-31 │ │
769 │ two │ │ 15:16:13 │ │
770 │ ... │ │ │ │
771 ├──────────────┼────────────┼────────────┼────────────────┤
772 │ entry-one │ 1708791504 │ 2024-02-24 │ x'000000000000 │
773 │ │ │ 16:18:24 │ 00000000000000 │
774 │ │ │ │ ... │
775 ╰──────────────┴────────────┴────────────┴────────────────╯
776 }
777 do_test 5.3b {
778 set result "\n[db format -style box -widths {0 10 0 14} \
779 -align {left right right center} \
780 -blob sql -titlelimit 12 \
781 -text plain -esc off -textjsonb no \
782 -wordwrap yes -linelimit 2 $sqlnolabel ]"
783 } {
784 ╭──────────────┬────────────┬─────────────────────┬────────────────╮
785 │ name │ mtime │ datetime(... │ value │
786 ╞══════════════╪════════════╪═════════════════════╪════════════════╡
787 │ sample-jsonb │ 1333101221 │ 2012-03-30 09:53:41 │ x'cc7c57616c70 │
788 │ │ │ │ 6861b535332e31 │
789 │ │ │ │ ... │
790 ├──────────────┼────────────┼─────────────────────┼────────────────┤
791 │ one │ 1333206973 │ 2012-03-31 15:16:13 │ │
792 │ two │ │ │ │
793 │ ... │ │ │ │
794 ├──────────────┼────────────┼─────────────────────┼────────────────┤
795 │ entry-one │ 1708791504 │ 2024-02-24 16:18:24 │ x'000000000000 │
796 │ │ │ │ 00000000000000 │
797 │ │ │ │ ... │
798 ╰──────────────┴────────────┴─────────────────────┴────────────────╯
799 }
800 do_test 5.3c {
801 set result "\n[db format -style table -widths {0 10 10 14}\
802 -align {center right right right} \
803 -blob sql \
804 -text plain -esc off -textjsonb no \
805 -wordwrap yes -linelimit 2 $sql]"
806 } {
807 +--------------+------------+------------+----------------+
808 | name | mtime | time | value |
809 +--------------+------------+------------+----------------+
810 | sample-jsonb | 1333101221 | 2012-03-30 | x'cc7c57616c70 |
811 | | | 09:53:41 | 6861b535332e31 |
812 | | | | ... |
813 +--------------+------------+------------+----------------+
814 | one | 1333206973 | 2012-03-31 | |
815 | two | | 15:16:13 | |
816 | ... | | | |
817 +--------------+------------+------------+----------------+
818 | entry-one | 1708791504 | 2024-02-24 | x'000000000000 |
819 | | | 16:18:24 | 00000000000000 |
820 | | | | ... |
821 +--------------+------------+------------+----------------+
822 }
823 do_test 5.3c {
824 set result "\n[db format -style column -widths {0 10 10 14}\
825 -align {center right right right} \
826 -blob sql \
827 -text plain -esc off -textjsonb no \
828 -wordwrap yes -linelimit 2 $sql]"
829 } {
830 name mtime time value
831 ------------ ---------- ---------- --------------
832 sample-jsonb 1333101221 2012-03-30 x'cc7c57616c70
833 09:53:41 6861b535332e31
834 ...
835
836 one 1333206973 2012-03-31
837 two 15:16:13
838 ...
839
840 entry-one 1708791504 2024-02-24 x'000000000000
841 16:18:24 00000000000000
842 ...
843 }
844 do_test 5.4 {
845 db eval {
846 CREATE TABLE t2(a,b,c,d,e);
847 WITH v(x) AS (SELECT 'abcdefghijklmnopqrstuvwxyz')
848 INSERT INTO t2 SELECT x,x,x,x,x FROM v;
849 }
850 set sql {SELECT char(0x61,0xa,0x62,0xa,0x63,0xa,0x64) a,
851 mtime b, mtime c, mtime d, mtime e FROM t1}
852 set result "\n[db format -style box -widths {1 2 3 4 5}\
853 -linelimit 3 -wordwrap off {SELECT *, 'x' AS x FROM t2}]"
854 } {
855 ╭────┬────┬─────┬──────┬───────┬───╮
856 │ a │ b │ c │ d │ e │ x │
857 ╞════╪════╪═════╪══════╪═══════╪═══╡
858 │ ab │ ab │ abc │ abcd │ abcde │ x │
859 │ cd │ cd │ def │ efgh │ fghij │ │
860 │ ef │ ef │ ghi │ ijkl │ klmno │ │
861 │ .. │ .. │ ... │ ... │ ... │ │
862 ╰────┴────┴─────┴──────┴───────┴───╯
863 }
864
865 do_execsql_test 6.0 {
866 DELETE FROM t2;
867 INSERT INTO t2 VALUES
868 (1, 2.5, 'three', x'342028666f757229', null);
869 }
870 do_test 6.1a {
871 set result "\n[db format -style list -null NULL \
872 -text tcl -columnsep , \
873 {SELECT * FROM t2}]"
874 } {
875 1,2.5,"three","\064\040\050\146\157\165\162\051",NULL
876 }
877
878 do_execsql_test 7.0 {
879 CREATE TABLE t7(a,b);
880 INSERT INTO t7 VALUES('abcdefghijklmnop',
881 'abcぁdefかghiのjklはmnop');
882 }
883 do_test 7.1 {
884 set result "\n[db format -style list -charlimit 13 \
885 {SELECT * FROM t7}]"
886 } {
887 abcdefghijklm...|abcぁdefかghi...
888 }
889 do_test 7.2 {
890 set result "\n[db format -style list -charlimit 14 \
891 {SELECT * FROM t7}]"
892 } {
893 abcdefghijklmn...|abcぁdefかghi...
894 }
895 do_test 7.3 {
896 set result "\n[db format -style list -charlimit 15 \
897 {SELECT * FROM t7}]"
898 } {
899 abcdefghijklmno...|abcぁdefかghiの...
900 }
901 do_test 7.4 {
902 set result "\n[db format -style list -charlimit 16 \
903 {SELECT * FROM t7}]"
904 } {
905 abcdefghijklmnop|abcぁdefかghiのj...
906 }
907
908 do_test 8.0 {
909 set result "\n[db format -style table {
910 WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<10)
911 SELECT 'aaa' AS 'a', format('%.*c',n,'b')||char(9)||'xx' AS x FROM c}]"
912 } {
913 +-----+--------------------+
914 | a | x |
915 +-----+--------------------+
916 | aaa | b xx |
917 | aaa | bb xx |
918 | aaa | bbb xx |
919 | aaa | bbbb xx |
920 | aaa | bbbbb xx |
921 | aaa | bbbbbb xx |
922 | aaa | bbbbbbb xx |
923 | aaa | bbbbbbbb xx |
924 | aaa | bbbbbbbbb xx |
925 | aaa | bbbbbbbbbb xx |
926 +-----+--------------------+
927 }
928 do_test 8.1 {
929 set result "\n[db format -style table {
930 WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<10)
931 SELECT 'aaaa' AS 'a', format('%.*c',n,'b')||char(9)||'xx' AS x FROM c}]"
932 } {
933 +------+--------------------+
934 | a | x |
935 +------+--------------------+
936 | aaaa | b xx |
937 | aaaa | bb xx |
938 | aaaa | bbb xx |
939 | aaaa | bbbb xx |
940 | aaaa | bbbbb xx |
941 | aaaa | bbbbbb xx |
942 | aaaa | bbbbbbb xx |
943 | aaaa | bbbbbbbb xx |
944 | aaaa | bbbbbbbbb xx |
945 | aaaa | bbbbbbbbbb xx |
946 +------+--------------------+
947 }
948 do_test 8.3 {
949 set result "\n[db format -style table -esc off {
950 WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<15)
951 SELECT 'aaa' AS 'a',n, 'xx'||char(n)||'yy' AS xy FROM c
952 WHERE n NOT IN (8,10,13,14)}]"
953 } {
954 +-----+----+------------+
955 | a | n | xy |
956 +-----+----+------------+
957 | aaa | 1 | xx␁yy |
958 | aaa | 2 | xx␂yy |
959 | aaa | 3 | xx␃yy |
960 | aaa | 4 | xx␄yy |
961 | aaa | 5 | xx␅yy |
962 | aaa | 6 | xx␆yy |
963 | aaa | 7 | xx␇yy |
964 | aaa | 9 | xx yy |
965 | aaa | 11 | xx␋yy |
966 | aaa | 12 | xx␌yy |
967 | aaa | 15 | xx␏yy |
968 +-----+----+------------+
969 }
970 do_test 8.4 {
971 set result "\n[db format -style table -esc off {
972 WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<15)
973 SELECT 'aaa' AS 'a',n, 'xx'||char(n)||'yy'||char(9)||'zz' AS xyz FROM c
974 WHERE n NOT IN (8,10,13,14)}]"
975 } {
976 +-----+----+--------------------+
977 | a | n | xyz |
978 +-----+----+--------------------+
979 | aaa | 1 | xx␁yy zz |
980 | aaa | 2 | xx␂yy zz |
981 | aaa | 3 | xx␃yy zz |
982 | aaa | 4 | xx␄yy zz |
983 | aaa | 5 | xx␅yy zz |
984 | aaa | 6 | xx␆yy zz |
985 | aaa | 7 | xx␇yy zz |
986 | aaa | 9 | xx yy zz |
987 | aaa | 11 | xx␋yy zz |
988 | aaa | 12 | xx␌yy zz |
989 | aaa | 15 | xx␏yy zz |
990 +-----+----+--------------------+
991 }
992
993 do_test 9.1 {
994 db eval {
995 CREATE TABLE t9(x);
996 INSERT INTO t9 VALUES
997 (x'4331323334'),
998 (x'c30431323334'),
999 (x'd3000431323334'),
1000 (x'e30000000431323334'),
1001 (x'f3000000000000000431323334');
1002 }
1003 db format -style list -text plain -rowsep , -textjsonb on \
1004 {SELECT * FROM t9}
1005 } {1234,1234,1234,1234,1234,}
1006 do_test 9.2 {
1007 db format -style list -text sql -rowsep , -textjsonb on \
1008 {SELECT * FROM t9}
1009 } {jsonb('1234'),jsonb('1234'),jsonb('1234'),jsonb('1234'),jsonb('1234'),}
1010 do_test 9.3 {
1011 db format -style json {SELECT * FROM t9 WHERE rowid<0}
1012 } {}
1013 do_test 9.4 {
1014 db format -style jobject {SELECT * FROM t9 WHERE rowid<0}
1015 } {}
1016
1017 do_test 10.1 {
1018 db eval {
1019 DROP TABLE IF EXISTS t1;
1020 CREATE TABLE t1(x);
1021 INSERT INTO t1(x) VALUES
1022 ('alice'),
1023 ('bob'),
1024 ('cinderella-cinderella'),
1025 ('daniel'),
1026 ('emma'),
1027 ('fred'),
1028 ('gertrude'),
1029 ('harold'),
1030 ('ingrid'),
1031 ('jake'),
1032 ('lisa'),
1033 ('mike'),
1034 ('nina'),
1035 ('octavian'),
1036 ('paula'),
1037 ('quintus'),
1038 ('rita'),
1039 ('sam'),
1040 ('tammy'),
1041 ('ulysses'),
1042 ('violet'),
1043 ('william'),
1044 ('xanthippe'),
1045 ('yates'),
1046 ('zoe');
1047 }
1048 set result "\n[db format -style column -title off -screenwidth 41 -splitcolumn on \
1049 {SELECT x FROM t1}]"
1050 } {
1051 alice octavian
1052 bob paula
1053 cinderella-cinderella quintus
1054 daniel rita
1055 emma sam
1056 fred tammy
1057 gertrude ulysses
1058 harold violet
1059 ingrid william
1060 jake xanthippe
1061 lisa yates
1062 mike zoe
1063 nina
1064 }
1065 do_test 10.2 {
1066 set result "\n[db format -style column -title off -screenwidth 42 -splitcolumn on \
1067 {SELECT x FROM t1}]"
1068 } {
1069 alice jake tammy
1070 bob lisa ulysses
1071 cinderella-cinderella mike violet
1072 daniel nina william
1073 emma octavian xanthippe
1074 fred paula yates
1075 gertrude quintus zoe
1076 harold rita
1077 ingrid sam
1078 }
1079 do_test 10.3 {
1080 set result "\n[db format -style column -title off -screenwidth 51 -splitcolumn on \
1081 {SELECT x FROM t1}]"
1082 } {
1083 alice harold paula william
1084 bob ingrid quintus xanthippe
1085 cinderella-cinderella jake rita yates
1086 daniel lisa sam zoe
1087 emma mike tammy
1088 fred nina ulysses
1089 gertrude octavian violet
1090 }
1091 do_test 10.4 {
1092 set result "\n[db format -style column -title off -screenwidth 61 -splitcolumn on \
1093 {SELECT x FROM t1}]"
1094 } {
1095 alice fred lisa quintus violet
1096 bob gertrude mike rita william
1097 cinderella-cinderella harold nina sam xanthippe
1098 daniel ingrid octavian tammy yates
1099 emma jake paula ulysses zoe
1100 }
1101 do_test 10.5 {
1102 set result "\n[db format -style column -title off -screenwidth 74 -splitcolumn on \
1103 {SELECT x FROM t1}]"
1104 } {
1105 alice emma ingrid nina rita violet zoe
1106 bob fred jake octavian sam william
1107 cinderella-cinderella gertrude lisa paula tammy xanthippe
1108 daniel harold mike quintus ulysses yates
1109 }
1110
1111 do_test 11.1 {
1112 set result "\n[db format -style table -blob size {SELECT randomblob(1234)}]"
1113 } {
1114 +------------------+
1115 | randomblob(1234) |
1116 +------------------+
1117 | (1234-byte blob) |
1118 +------------------+
1119 }
1120
1121 do_test 12.1 {
1122 set result "\n[db format -style box -text html \
1123 {SELECT 'abc','','xyz'}]"
1124 } {
1125 ╭───────┬────┬───────╮
1126 │ 'abc' │ '' │ 'xyz' │
1127 ╞═══════╪════╪═══════╡
1128 │ abc │ │ xyz │
1129 ╰───────┴────┴───────╯
1130 }
1131
1132 # Tests for "relaxed" quoting
1133 #
1134 do_test 13.2 {
1135 db eval {
1136 CREATE TABLE t13(a,b);
1137 INSERT INTO t13(a,b) VALUES
1138 (1,'NULL'),
1139 (0,'-NULL-'),
1140 (0,''),
1141 (1,'''abcde'),
1142 (1,'abcde'''),
1143 (0,'abcde'),
1144 (1,' abcde'),
1145 (1,'abcde '),
1146 (1,'+0'),
1147 (1,'-0'),
1148 (1,'012345'),
1149 (0,'012xyz345'),
1150 (1,'0123.45'),
1151 (0,'12.34.56'),
1152 (0,'12.3e'),
1153 (1,'12.3e+123'),
1154 (1,'12.3e-34'),
1155 (1,'12.3E56'),
1156 (1,'12E56'),
1157 (0,'12.5E5.6'),
1158 (0,'12.5e+'),
1159 (0,'12.5e-'),
1160 (1,'+Inf'),(1,'-Inf'),(1,'Inf');
1161 }
1162 set result \n[db format -style box -text relaxed -null NULL \
1163 -align {center left} \
1164 {SELECT if(a,'yes','') AS 'quoted?', b AS string
1165 FROM t13 ORDER BY rowid}]
1166 } {
1167 ╭─────────┬─────────────╮
1168 │ quoted? │ string │
1169 ╞═════════╪═════════════╡
1170 │ yes │ 'NULL' │
1171 │ │ -NULL- │
1172 │ │ │
1173 │ yes │ '''abcde' │
1174 │ yes │ 'abcde''' │
1175 │ │ abcde │
1176 │ yes │ ' abcde' │
1177 │ yes │ 'abcde ' │
1178 │ yes │ '+0' │
1179 │ yes │ '-0' │
1180 │ yes │ '012345' │
1181 │ │ 012xyz345 │
1182 │ yes │ '0123.45' │
1183 │ │ 12.34.56 │
1184 │ │ 12.3e │
1185 │ yes │ '12.3e+123' │
1186 │ yes │ '12.3e-34' │
1187 │ yes │ '12.3E56' │
1188 │ yes │ '12E56' │
1189 │ │ 12.5E5.6 │
1190 │ │ 12.5e+ │
1191 │ │ 12.5e- │
1192 │ yes │ '+Inf' │
1193 │ yes │ '-Inf' │
1194 │ yes │ 'Inf' │
1195 ╰─────────┴─────────────╯
1196 }
1197
1198 db close
1199
1200 finish_test