3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
12 # The focus of this file is testing the CLI shell tool.
18 # shell1-1.*: Basic command line option handling.
19 # shell1-2.*: Basic "dot" command token parsing.
20 # shell1-3.*: Basic test that "dot" command can be called.
21 # shell1-{4-8}.*: Test various "dot" commands's functionality.
22 # shell1-9.*: Basic test that "dot" commands and SQL intermix ok.
24 set testdir [file dirname $argv0]
25 source $testdir/tester.tcl
26 set CLI [test_cli_invocation]
28 forcedelete test.db test.db-journal test.db-wal
31 #----------------------------------------------------------------------------
32 # Test cases shell1-1.*: Basic command line option handling.
36 do_test shell1-1.1.1 {
37 set res [catchcmd "-bad test.db" ""]
38 set rc [lindex $res 0]
40 [regexp {Error: unknown option: -bad} $res]
42 do_test shell1-1.1.1b {
43 set res [catchcmd "test.db -bad" ""]
44 set rc [lindex $res 0]
46 [regexp {Error: unknown option: -bad} $res]
48 # error on extra options
49 do_test shell1-1.1.2 {
50 catchcmd "test.db \"select+3\" \"select+4\"" ""
53 # error on extra options
54 do_test shell1-1.1.3 {
55 catchcmd "test.db FOO test.db BAD" ".quit"
56 } {/1 .Error: in prepare, near "FOO": syntax error*/}
59 do_test shell1-1.2.1 {
60 set res [catchcmd "-help test.db" ""]
61 set rc [lindex $res 0]
63 [regexp {Usage} $res] \
64 [regexp {\-init} $res] \
65 [regexp {\-version} $res]
68 # -init filename read/process named file
73 do_test shell1-1.3.1 {
74 catchcmd "-init FOO test.db" ""
76 do_test shell1-1.3.2 {
77 catchcmd "-init FOO test.db .quit BAD" ""
79 do_test shell1-1.3.3 {
80 catchcmd "-init FOO test.db BAD .quit" ""
81 } {/1 .Error: in prepare, near "BAD": syntax error*/}
83 # -echo print commands before execution
84 do_test shell1-1.4.1 {
85 catchcmd "-echo test.db" ""
88 # -[no]header turn headers on or off
89 do_test shell1-1.5.1 {
90 catchcmd "-header test.db" ""
92 do_test shell1-1.5.2 {
93 catchcmd "-noheader test.db" ""
96 # -bail stop after hitting an error
97 do_test shell1-1.6.1 {
98 catchcmd "-bail test.db" ""
101 # -interactive force interactive I/O
102 do_test shell1-1.7.1 {
103 set res [catchcmd "-interactive test.db" ".quit"]
104 set rc [lindex $res 0]
106 [regexp {SQLite version} $res] \
107 [regexp {Enter ".help" for usage hints} $res]
110 # -batch force batch I/O
111 do_test shell1-1.8.1 {
112 catchcmd "-batch test.db" ""
115 # -column set output mode to 'column'
116 do_test shell1-1.9.1 {
117 catchcmd "-column test.db" ""
120 # -csv set output mode to 'csv'
121 do_test shell1-1.10.1 {
122 catchcmd "-csv test.db" ""
125 # -html set output mode to HTML
126 do_test shell1-1.11.1 {
127 catchcmd "-html test.db" ""
130 # -line set output mode to 'line'
131 do_test shell1-1.12.1 {
132 catchcmd "-line test.db" ""
135 # -list set output mode to 'list'
136 do_test shell1-1.13.1 {
137 catchcmd "-list test.db" ""
140 # -separator 'x' set output field separator (|)
141 do_test shell1-1.14.1 {
142 catchcmd "-separator 'x' test.db" ""
144 do_test shell1-1.14.2 {
145 catchcmd "-separator x test.db" ""
147 do_test shell1-1.14.3 {
148 set res [catchcmd "-separator" ""]
149 set rc [lindex $res 0]
151 [regexp {Error: missing argument to -separator} $res]
154 # -stats print memory stats before each finalize
155 do_test shell1-1.14b.1 {
156 catchcmd "-stats test.db" ""
159 # -nullvalue 'text' set text string for NULL values
160 do_test shell1-1.15.1 {
161 catchcmd "-nullvalue 'x' test.db" ""
163 do_test shell1-1.15.2 {
164 catchcmd "-nullvalue x test.db" ""
166 do_test shell1-1.15.3 {
167 set res [catchcmd "-nullvalue" ""]
168 set rc [lindex $res 0]
170 [regexp {Error: missing argument to -nullvalue} $res]
173 # -version show SQLite version
174 do_test shell1-1.16.1 {
175 set x [catchcmd "-version test.db" ""]
176 } {/3.[0-9.]+ 20\d\d-[01]\d-\d\d \d\d:\d\d:\d\d [0-9a-f]+/}
178 # Handle no-more-options option
180 do_test shell1-1.17.1 {
181 catchcmd {-- --db "CREATE TABLE T(c1);"}
183 do_test shell1-1.17.2 {
184 catchcmd {-- --db "SELECT name from sqlite_schema;"}
188 #----------------------------------------------------------------------------
189 # Test cases shell1-2.*: Basic "dot" command token parsing.
192 # check first token handling
193 do_test shell1-2.1.1 {
194 catchcmd "test.db" ".foo"
195 } {1 {Error: unknown command or invalid arguments: "foo". Enter ".help" for help}}
196 do_test shell1-2.1.2 {
197 catchcmd "test.db" ".\"foo OFF\""
198 } {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}}
199 do_test shell1-2.1.3 {
200 catchcmd "test.db" ".\'foo OFF\'"
201 } {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}}
204 do_test shell1-2.2.1 {
205 catchcmd "test.db" ".\"foo OFF"
206 } {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}}
207 do_test shell1-2.2.2 {
208 catchcmd "test.db" ".\'foo OFF"
209 } {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}}
210 do_test shell1-2.2.3 {
211 catchcmd "test.db" ".explain \"OFF"
213 do_test shell1-2.2.4 {
214 catchcmd "test.db" ".explain \'OFF"
216 do_test shell1-2.2.5 {
217 catchcmd "test.db" ".mode \"insert FOO"
218 } {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown qbox quote table tabs tcl}}
219 do_test shell1-2.2.6 {
220 catchcmd "test.db" ".mode \'insert FOO"
221 } {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown qbox quote table tabs tcl}}
223 # check multiple tokens, and quoted tokens
224 do_test shell1-2.3.1 {
225 catchcmd "test.db" ".explain 1"
227 do_test shell1-2.3.2 {
228 catchcmd "test.db" ".explain on"
230 do_test shell1-2.3.3 {
231 catchcmd "test.db" ".explain \"1 2 3\""
232 } {1 {ERROR: Not a boolean value: "1 2 3". Assuming "no".}}
233 do_test shell1-2.3.4 {
234 catchcmd "test.db" ".explain \"OFF\""
236 do_test shell1-2.3.5 {
237 catchcmd "test.db" ".\'explain\' \'OFF\'"
239 do_test shell1-2.3.6 {
240 catchcmd "test.db" ".explain \'OFF\'"
242 do_test shell1-2.3.7 {
243 catchcmd "test.db" ".\'explain\' \'OFF\'"
246 # check quoted args are unquoted
247 do_test shell1-2.4.1 {
248 catchcmd "test.db" ".mode FOO"
249 } {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown qbox quote table tabs tcl}}
250 do_test shell1-2.4.2 {
251 catchcmd "test.db" ".mode csv"
253 do_test shell1-2.4.2 {
254 catchcmd "test.db" ".mode \"csv\""
257 # check that certain quoted arg escapes work
258 do_test shell1-2.5.1 {
259 catchcmd ":memory:" ".print \"\\060\\077 \\x3f\\x30 \\a\\t\""
260 } [list 0 "0? ?0 \a\t"]
263 #----------------------------------------------------------------------------
264 # Test cases shell1-3.*: Basic test that "dot" command can be called.
267 # .backup ?DB? FILE Backup DB (default "main") to FILE
268 do_test shell1-3.1.1 {
269 catchcmd "test.db" ".backup"
270 } {1 {missing FILENAME argument on .backup}}
272 do_test shell1-3.1.2 {
273 catchcmd "test.db" ".backup FOO"
275 do_test shell1-3.1.3 {
276 catchcmd "test.db" ".backup FOO BAR"
277 } {1 {Error: unknown database FOO}}
278 do_test shell1-3.1.4 {
280 catchcmd "test.db" ".backup FOO BAR BAD"
281 } {1 {Usage: .backup ?DB? ?OPTIONS? FILENAME}}
283 # .bail ON|OFF Stop after hitting an error. Default OFF
284 do_test shell1-3.2.1 {
285 catchcmd "test.db" ".bail"
286 } {1 {Usage: .bail on|off}}
287 do_test shell1-3.2.2 {
288 catchcmd "test.db" ".bail ON"
290 do_test shell1-3.2.3 {
291 catchcmd "test.db" ".bail OFF"
293 do_test shell1-3.2.4 {
295 catchcmd "test.db" ".bail OFF BAD"
296 } {1 {Usage: .bail on|off}}
299 # .databases List names and files of attached databases
300 do_test shell1-3.3.1 {
301 catchcmd "-csv test.db" ".databases"
302 } "/0.+main.+[string map {/ ".{1,2}"} [string range [get_pwd] 0 10]].*/"
303 do_test shell1-3.3.2 {
304 # extra arguments ignored
305 catchcmd "test.db" ".databases BAD"
306 } "/0.+main.+[string map {/ ".{1,2}"} [string range [get_pwd] 0 10]].*/"
309 # .dump ?TABLE? ... Dump the database in an SQL text format
310 # If TABLE specified, only dump tables matching
311 # LIKE pattern TABLE.
312 do_test shell1-3.4.1 {
313 set res [catchcmd "test.db" ".dump"]
314 list [regexp {BEGIN TRANSACTION;} $res] \
315 [regexp {COMMIT;} $res]
317 do_test shell1-3.4.2 {
318 set res [catchcmd "test.db" ".dump FOO"]
319 list [regexp {BEGIN TRANSACTION;} $res] \
320 [regexp {COMMIT;} $res]
322 # The .dump command now accepts multiple arguments
323 #do_test shell1-3.4.3 {
324 # # too many arguments
325 # catchcmd "test.db" ".dump FOO BAD"
326 #} {1 {Usage: .dump ?--preserve-rowids? ?--newlines? ?LIKE-PATTERN?}}
328 # .echo ON|OFF Turn command echo on or off
329 do_test shell1-3.5.1 {
330 catchcmd "test.db" ".echo"
331 } {1 {Usage: .echo on|off}}
332 do_test shell1-3.5.2 {
333 catchcmd "test.db" ".echo ON"
335 do_test shell1-3.5.3 {
336 catchcmd "test.db" ".echo OFF"
338 do_test shell1-3.5.4 {
340 catchcmd "test.db" ".echo OFF BAD"
341 } {1 {Usage: .echo on|off}}
343 # .exit Exit this program
344 do_test shell1-3.6.1 {
345 catchcmd "test.db" ".exit"
348 # .explain ON|OFF Turn output mode suitable for EXPLAIN on or off.
349 do_test shell1-3.7.1 {
350 catchcmd "test.db" ".explain"
351 # explain is the exception to the booleans. without an option, it turns it on.
353 do_test shell1-3.7.2 {
354 catchcmd "test.db" ".explain ON"
356 do_test shell1-3.7.3 {
357 catchcmd "test.db" ".explain OFF"
359 do_test shell1-3.7.4 {
360 # extra arguments ignored
361 catchcmd "test.db" ".explain OFF BAD"
365 # .header(s) ON|OFF Turn display of headers on or off
366 do_test shell1-3.9.1 {
367 catchcmd "test.db" ".header"
368 } {1 {Usage: .headers on|off}}
369 do_test shell1-3.9.2 {
370 catchcmd "test.db" ".header ON"
372 do_test shell1-3.9.3 {
373 catchcmd "test.db" ".header OFF"
375 do_test shell1-3.9.4 {
377 catchcmd "test.db" ".header OFF BAD"
378 } {1 {Usage: .headers on|off}}
380 do_test shell1-3.9.5 {
381 catchcmd "test.db" ".headers"
382 } {1 {Usage: .headers on|off}}
383 do_test shell1-3.9.6 {
384 catchcmd "test.db" ".headers ON"
386 do_test shell1-3.9.7 {
387 catchcmd "test.db" ".headers OFF"
389 do_test shell1-3.9.8 {
391 catchcmd "test.db" ".headers OFF BAD"
392 } {1 {Usage: .headers on|off}}
394 # .help Show this message
395 do_test shell1-3.10.1 {
396 set res [catchcmd "test.db" ".help"]
397 # look for a few of the possible help commands
398 list [regexp {.help} $res] \
399 [regexp {.quit} $res] \
400 [regexp {.show} $res]
402 do_test shell1-3.10.2 {
403 # we allow .help to take extra args (it is help after all)
404 set res [catchcmd "test.db" ".help *"]
405 # look for a few of the possible help commands
406 list [regexp {.help} $res] \
407 [regexp {.quit} $res] \
408 [regexp {.show} $res]
411 # .import FILE TABLE Import data from FILE into TABLE
412 do_test shell1-3.11.1 {
413 catchcmd "test.db" ".import"
414 } {/1 .ERROR: missing FILE argument.*/}
415 do_test shell1-3.11.2 {
416 catchcmd "test.db" ".import FOO"
417 } {/1 .ERROR: missing TABLE argument.*/}
418 do_test shell1-3.11.3 {
420 catchcmd "test.db" ".import FOO BAR BAD"
421 } {/1 .ERROR: extra argument: "BAD".*./}
423 # .indexes ?TABLE? Show names of all indexes
424 # If TABLE specified, only show indexes for tables
425 # matching LIKE pattern TABLE.
426 do_test shell1-3.12.1 {
427 catchcmd "test.db" ".indexes"
429 do_test shell1-3.12.2 {
430 catchcmd "test.db" ".indexes FOO"
432 do_test shell1-3.12.2-legacy {
433 catchcmd "test.db" ".indices FOO"
435 do_test shell1-3.12.3 {
437 catchcmd "test.db" ".indexes FOO BAD"
438 } {1 {Usage: .indexes ?LIKE-PATTERN?}}
440 # .mode MODE ?TABLE? Set output mode where MODE is one of:
441 # ascii Columns/rows delimited by 0x1F and 0x1E
442 # csv Comma-separated values
443 # column Left-aligned columns. (See .width)
444 # html HTML <table> code
445 # insert SQL insert statements for TABLE
446 # line One value per line
447 # list Values delimited by .separator strings
448 # tabs Tab-separated values
449 # tcl TCL list elements
450 do_test shell1-3.13.1 {
451 catchcmd "test.db" ".mode"
452 } {0 {current output mode: list}}
453 do_test shell1-3.13.2 {
454 catchcmd "test.db" ".mode FOO"
455 } {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown qbox quote table tabs tcl}}
456 do_test shell1-3.13.3 {
457 catchcmd "test.db" ".mode csv"
459 do_test shell1-3.13.4 {
460 catchcmd "test.db" ".mode column"
462 do_test shell1-3.13.5 {
463 catchcmd "test.db" ".mode html"
465 do_test shell1-3.13.6 {
466 catchcmd "test.db" ".mode insert"
468 do_test shell1-3.13.7 {
469 catchcmd "test.db" ".mode line"
471 do_test shell1-3.13.8 {
472 catchcmd "test.db" ".mode list"
474 do_test shell1-3.13.9 {
475 catchcmd "test.db" ".mode tabs"
477 do_test shell1-3.13.10 {
478 catchcmd "test.db" ".mode tcl"
480 do_test shell1-3.13.11 {
481 # extra arguments ignored
482 catchcmd "test.db" ".mode tcl BAD"
485 # .nullvalue STRING Print STRING in place of NULL values
486 do_test shell1-3.14.1 {
487 catchcmd "test.db" ".nullvalue"
488 } {1 {Usage: .nullvalue STRING}}
489 do_test shell1-3.14.2 {
490 catchcmd "test.db" ".nullvalue FOO"
492 do_test shell1-3.14.3 {
494 catchcmd "test.db" ".nullvalue FOO BAD"
495 } {1 {Usage: .nullvalue STRING}}
497 # .output FILENAME Send output to FILENAME
498 do_test shell1-3.15.1 {
499 catchcmd "test.db" ".output"
501 do_test shell1-3.15.2 {
502 catchcmd "test.db" ".output FOO"
504 do_test shell1-3.15.3 {
506 catchcmd "test.db" ".output FOO BAD"
507 } {1 {ERROR: extra parameter: "BAD". Usage:
508 .output ?FILE? Send output to FILE or stdout if FILE is omitted
509 If FILE begins with '|' then open it as a pipe.
511 --bom Prefix output with a UTF8 byte-order mark
512 -e Send output to the system text editor
513 -x Send output as CSV to a spreadsheet
514 child process exited abnormally}}
516 # .output stdout Send output to the screen
517 do_test shell1-3.16.1 {
518 catchcmd "test.db" ".output stdout"
520 do_test shell1-3.16.2 {
522 catchcmd "test.db" ".output stdout BAD"
523 } {1 {ERROR: extra parameter: "BAD". Usage:
524 .output ?FILE? Send output to FILE or stdout if FILE is omitted
525 If FILE begins with '|' then open it as a pipe.
527 --bom Prefix output with a UTF8 byte-order mark
528 -e Send output to the system text editor
529 -x Send output as CSV to a spreadsheet
530 child process exited abnormally}}
532 # .prompt MAIN CONTINUE Replace the standard prompts
533 do_test shell1-3.17.1 {
534 catchcmd "test.db" ".prompt"
536 do_test shell1-3.17.2 {
537 catchcmd "test.db" ".prompt FOO"
539 do_test shell1-3.17.3 {
540 catchcmd "test.db" ".prompt FOO BAR"
542 do_test shell1-3.17.4 {
544 catchcmd "test.db" ".prompt FOO BAR BAD"
547 # .quit Exit this program
548 do_test shell1-3.18.1 {
549 catchcmd "test.db" ".quit"
551 do_test shell1-3.18.2 {
553 catchcmd "test.db" ".quit BAD"
556 # .read FILENAME Execute SQL in FILENAME
557 do_test shell1-3.19.1 {
558 catchcmd "test.db" ".read"
559 } {1 {Usage: .read FILE}}
560 do_test shell1-3.19.2 {
562 catchcmd "test.db" ".read FOO"
563 } {1 {Error: cannot open "FOO"}}
564 do_test shell1-3.19.3 {
566 catchcmd "test.db" ".read FOO BAD"
567 } {1 {Usage: .read FILE}}
569 # .restore ?DB? FILE Restore content of DB (default "main") from FILE
570 do_test shell1-3.20.1 {
571 catchcmd "test.db" ".restore"
572 } {1 {Usage: .restore ?DB? FILE}}
573 do_test shell1-3.20.2 {
574 catchcmd "test.db" ".restore FOO"
576 do_test shell1-3.20.3 {
577 catchcmd "test.db" ".restore FOO BAR"
578 } {1 {Error: unknown database FOO}}
579 do_test shell1-3.20.4 {
581 catchcmd "test.db" ".restore FOO BAR BAD"
582 } {1 {Usage: .restore ?DB? FILE}}
585 # .schema ?TABLE? Show the CREATE statements
586 # If TABLE specified, only show tables matching
587 # LIKE pattern TABLE.
588 do_test shell1-3.21.1 {
589 catchcmd "test.db" ".schema"
591 do_test shell1-3.21.2 {
592 catchcmd "test.db" ".schema FOO"
594 do_test shell1-3.21.3 {
596 catchcmd "test.db" ".schema FOO BAD"
597 } {1 {Usage: .schema ?--indent? ?--nosys? ?LIKE-PATTERN?}}
599 do_test shell1-3.21.4 {
602 CREATE VIEW v2 AS SELECT x+1 AS y FROM t1;
603 CREATE VIEW v1 AS SELECT y+1 FROM v2;
605 catchcmd "test.db" ".schema"
606 } {0 {CREATE TABLE t1(x);
607 CREATE VIEW v2 AS SELECT x+1 AS y FROM t1
609 CREATE VIEW v1 AS SELECT y+1 FROM v2
612 catch {db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;}}
615 # .separator STRING Change column separator used by output and .import
616 do_test shell1-3.22.1 {
617 catchcmd "test.db" ".separator"
618 } {1 {Usage: .separator COL ?ROW?}}
619 do_test shell1-3.22.2 {
620 catchcmd "test.db" ".separator FOO"
622 do_test shell1-3.22.3 {
623 catchcmd "test.db" ".separator ABC XYZ"
625 do_test shell1-3.22.4 {
627 catchcmd "test.db" ".separator FOO BAD BAD2"
628 } {1 {Usage: .separator COL ?ROW?}}
630 # .show Show the current values for various settings
631 do_test shell1-3.23.1 {
632 set res [catchcmd "test.db" ".show"]
633 list [regexp {echo:} $res] \
634 [regexp {explain:} $res] \
635 [regexp {headers:} $res] \
636 [regexp {mode:} $res] \
637 [regexp {nullvalue:} $res] \
638 [regexp {output:} $res] \
639 [regexp {colseparator:} $res] \
640 [regexp {rowseparator:} $res] \
641 [regexp {stats:} $res] \
642 [regexp {width:} $res]
643 } {1 1 1 1 1 1 1 1 1 1}
644 do_test shell1-3.23.2 {
646 catchcmd "test.db" ".show BAD"
649 # .stats ON|OFF Turn stats on or off
650 #do_test shell1-3.23b.1 {
651 # catchcmd "test.db" ".stats"
652 #} {1 {Usage: .stats on|off|stmt|vmstep}}
653 do_test shell1-3.23b.2 {
654 catchcmd "test.db" ".stats ON"
656 do_test shell1-3.23b.3 {
657 catchcmd "test.db" ".stats OFF"
659 do_test shell1-3.23b.4 {
661 catchcmd "test.db" ".stats OFF BAD"
662 } {1 {Usage: .stats ?on|off|stmt|vmstep?}}
664 # Ticket 7be932dfa60a8a6b3b26bcf7623ec46e0a403ddb 2018-06-07
665 # Adverse interaction between .stats and .eqp
667 do_test shell1-3.23b.5 {
668 catchcmd "test.db" [string map {"\n " "\n"} {
669 CREATE TEMP TABLE t1(x);
670 INSERT INTO t1 VALUES(1),(2);
677 # .tables ?TABLE? List names of tables
678 # If TABLE specified, only list tables matching
679 # LIKE pattern TABLE.
680 do_test shell1-3.24.1 {
681 catchcmd "test.db" ".tables"
683 do_test shell1-3.24.2 {
684 catchcmd "test.db" ".tables FOO"
686 do_test shell1-3.24.3 {
688 catchcmd "test.db" ".tables FOO BAD"
691 # .timeout MS Try opening locked tables for MS milliseconds
692 do_test shell1-3.25.1 {
693 catchcmd "test.db" ".timeout"
695 do_test shell1-3.25.2 {
696 catchcmd "test.db" ".timeout zzz"
697 # this should be treated the same as a '0' timeout
699 do_test shell1-3.25.3 {
700 catchcmd "test.db" ".timeout 1"
702 do_test shell1-3.25.4 {
704 catchcmd "test.db" ".timeout 1 BAD"
707 # .width NUM NUM ... Set column widths for "column" mode
708 do_test shell1-3.26.1 {
709 catchcmd "test.db" ".width"
711 do_test shell1-3.26.2 {
712 catchcmd "test.db" ".width xxx"
713 # this should be treated the same as a '0' width for col 1
715 do_test shell1-3.26.3 {
716 catchcmd "test.db" ".width xxx yyy"
717 # this should be treated the same as a '0' width for col 1 and 2
719 do_test shell1-3.26.4 {
720 catchcmd "test.db" ".width 1 1"
721 # this should be treated the same as a '1' width for col 1 and 2
723 do_test shell1-3.26.5 {
724 catchcmd "test.db" ".mode column\n.header off\n.width 10 -10\nSELECT 'abcdefg', 123456;"
725 # this should be treated the same as a '1' width for col 1 and 2
726 } {0 {abcdefg 123456}}
727 do_test shell1-3.26.6 {
728 catchcmd "test.db" ".mode column\n.header off\n.width -10 10\nSELECT 'abcdefg', 123456;"
729 # this should be treated the same as a '1' width for col 1 and 2
730 } {0 { abcdefg 123456 }}
733 # .timer ON|OFF Turn the CPU timer measurement on or off
734 do_test shell1-3.27.1 {
735 catchcmd "test.db" ".timer"
736 } {1 {Usage: .timer on|off}}
737 do_test shell1-3.27.2 {
738 catchcmd "test.db" ".timer ON"
740 do_test shell1-3.27.3 {
741 catchcmd "test.db" ".timer OFF"
743 do_test shell1-3.27.4 {
745 catchcmd "test.db" ".timer OFF BAD"
746 } {1 {Usage: .timer on|off}}
748 do_test shell1-3-28.1 {
750 ".log stdout\nSELECT coalesce(sqlite_log(123,'hello'),'456');"
751 } "0 {(123) hello\n456}"
753 do_test shell1-3-29.1 {
754 catchcmd "test.db" ".print this is a test"
755 } {0 {this is a test}}
757 # dot-command argument quoting
758 do_test shell1-3-30.1 {
759 catchcmd {test.db} {.print "this\"is'a\055test" 'this\"is\\a\055test'}
760 } {0 {this"is'a-test this\"is\\a\055test}}
761 do_test shell1-3-31.1 {
762 catchcmd {test.db} {.print "this\nis\ta\\test" 'this\nis\ta\\test'}
763 } [list 0 "this\nis\ta\\test this\\nis\\ta\\\\test"]
766 # Test the output of the ".dump" command
773 PRAGMA encoding=UTF16;
775 INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f');
776 CREATE TABLE t3(x,y);
777 INSERT INTO t3 VALUES(1,null), (2,''), (3,1),
778 (4,2.25), (5,'hello'), (6,x'807f');
780 catchcmd test.db {.dump}
781 } {0 {PRAGMA foreign_keys=OFF;
784 INSERT INTO t1 VALUES(NULL);
785 INSERT INTO t1 VALUES('');
786 INSERT INTO t1 VALUES(1);
787 INSERT INTO t1 VALUES(2.25);
788 INSERT INTO t1 VALUES('hello');
789 INSERT INTO t1 VALUES(X'807f');
790 CREATE TABLE t3(x,y);
791 INSERT INTO t3 VALUES(1,NULL);
792 INSERT INTO t3 VALUES(2,'');
793 INSERT INTO t3 VALUES(3,1);
794 INSERT INTO t3 VALUES(4,2.25);
795 INSERT INTO t3 VALUES(5,'hello');
796 INSERT INTO t3 VALUES(6,X'807f');
802 # The --preserve-rowids option to .dump
804 do_test shell1-4.1.1 {
805 catchcmd test.db {.dump --preserve-rowids}
806 } {0 {PRAGMA foreign_keys=OFF;
809 INSERT INTO t1(rowid,x) VALUES(1,NULL);
810 INSERT INTO t1(rowid,x) VALUES(2,'');
811 INSERT INTO t1(rowid,x) VALUES(3,1);
812 INSERT INTO t1(rowid,x) VALUES(4,2.25);
813 INSERT INTO t1(rowid,x) VALUES(5,'hello');
814 INSERT INTO t1(rowid,x) VALUES(6,X'807f');
815 CREATE TABLE t3(x,y);
816 INSERT INTO t3(rowid,x,y) VALUES(1,1,NULL);
817 INSERT INTO t3(rowid,x,y) VALUES(2,2,'');
818 INSERT INTO t3(rowid,x,y) VALUES(3,3,1);
819 INSERT INTO t3(rowid,x,y) VALUES(4,4,2.25);
820 INSERT INTO t3(rowid,x,y) VALUES(5,5,'hello');
821 INSERT INTO t3(rowid,x,y) VALUES(6,6,X'807f');
824 # If the table contains an INTEGER PRIMARY KEY, do not record a separate
825 # rowid column in the output.
827 do_test shell1-4.1.2 {
832 CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
833 INSERT INTO t1 VALUES(1,null), (2,''), (3,1),
834 (4,2.25), (5,'hello'), (6,x'807f');
836 catchcmd test2.db {.dump --preserve-rowids}
837 } {0 {PRAGMA foreign_keys=OFF;
839 CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
840 INSERT INTO t1 VALUES(1,NULL);
841 INSERT INTO t1 VALUES(2,'');
842 INSERT INTO t1 VALUES(3,1);
843 INSERT INTO t1 VALUES(4,2.25);
844 INSERT INTO t1 VALUES(5,'hello');
845 INSERT INTO t1 VALUES(6,X'807f');
848 # Verify that the table named [table] is correctly quoted and that
849 # an INTEGER PRIMARY KEY DESC is not an alias for the rowid.
851 do_test shell1-4.1.3 {
856 CREATE TABLE [table](x INTEGER PRIMARY KEY DESC, y);
857 INSERT INTO [table] VALUES(1,null), (12,''), (23,1),
858 (34,2.25), (45,'hello'), (56,x'807f');
860 catchcmd test2.db {.dump --preserve-rowids}
861 } {0 {PRAGMA foreign_keys=OFF;
863 CREATE TABLE [table](x INTEGER PRIMARY KEY DESC, y);
864 INSERT INTO "table"(rowid,x,y) VALUES(1,1,NULL);
865 INSERT INTO "table"(rowid,x,y) VALUES(2,12,'');
866 INSERT INTO "table"(rowid,x,y) VALUES(3,23,1);
867 INSERT INTO "table"(rowid,x,y) VALUES(4,34,2.25);
868 INSERT INTO "table"(rowid,x,y) VALUES(5,45,'hello');
869 INSERT INTO "table"(rowid,x,y) VALUES(6,56,X'807f');
872 # Do not record rowids for a WITHOUT ROWID table. Also check correct quoting
873 # of table names that contain odd characters.
875 do_test shell1-4.1.4 {
880 CREATE TABLE [ta<>ble](x INTEGER PRIMARY KEY, y) WITHOUT ROWID;
881 INSERT INTO [ta<>ble] VALUES(1,null), (12,''), (23,1),
882 (34,2.25), (45,'hello'), (56,x'807f');
884 catchcmd test2.db {.dump --preserve-rowids}
885 } {0 {PRAGMA foreign_keys=OFF;
887 CREATE TABLE [ta<>ble](x INTEGER PRIMARY KEY, y) WITHOUT ROWID;
888 INSERT INTO "ta<>ble" VALUES(1,NULL);
889 INSERT INTO "ta<>ble" VALUES(12,'');
890 INSERT INTO "ta<>ble" VALUES(23,1);
891 INSERT INTO "ta<>ble" VALUES(34,2.25);
892 INSERT INTO "ta<>ble" VALUES(45,'hello');
893 INSERT INTO "ta<>ble" VALUES(56,X'807f');
896 # Do not record rowids if the rowid is inaccessible
898 do_test shell1-4.1.5 {
903 CREATE TABLE t1(_ROWID_,rowid,oid);
904 INSERT INTO t1 VALUES(1,null,'alpha'), (12,'',99), (23,1,x'b0b1b2');
906 catchcmd test2.db {.dump --preserve-rowids}
907 } {0 {PRAGMA foreign_keys=OFF;
909 CREATE TABLE t1(_ROWID_,rowid,oid);
910 INSERT INTO t1 VALUES(1,NULL,'alpha');
911 INSERT INTO t1 VALUES(12,'',99);
912 INSERT INTO t1 VALUES(23,1,X'b0b1b2');
917 do_test shell1-4.1.6 {
922 CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
923 INSERT INTO t1 VALUES(1,null), (2,''), (3,1),
924 (4,2.25), (5,'hello'), (6,x'807f');
926 catchcmd test2.db {.dump --preserve-rowids}
927 } {1 {The --preserve-rowids option is not compatible with SQLITE_OMIT_VIRTUALTABLE}}
932 # Test the output of ".mode insert"
934 do_test shell1-4.2.1 {
935 catchcmd test.db ".mode insert t1\nselect * from t1;"
936 } {0 {INSERT INTO t1 VALUES(NULL);
937 INSERT INTO t1 VALUES('');
938 INSERT INTO t1 VALUES(1);
939 INSERT INTO t1 VALUES(2.25);
940 INSERT INTO t1 VALUES('hello');
941 INSERT INTO t1 VALUES(X'807f');}}
943 # Test the output of ".mode insert" with headers
945 do_test shell1-4.2.2 {
946 catchcmd test.db ".mode insert t1\n.headers on\nselect * from t1;"
947 } {0 {INSERT INTO t1(x) VALUES(NULL);
948 INSERT INTO t1(x) VALUES('');
949 INSERT INTO t1(x) VALUES(1);
950 INSERT INTO t1(x) VALUES(2.25);
951 INSERT INTO t1(x) VALUES('hello');
952 INSERT INTO t1(x) VALUES(X'807f');}}
954 # Test the output of ".mode insert"
956 do_test shell1-4.2.3 {
957 catchcmd test.db ".mode insert t3\nselect * from t3;"
958 } {0 {INSERT INTO t3 VALUES(1,NULL);
959 INSERT INTO t3 VALUES(2,'');
960 INSERT INTO t3 VALUES(3,1);
961 INSERT INTO t3 VALUES(4,2.25);
962 INSERT INTO t3 VALUES(5,'hello');
963 INSERT INTO t3 VALUES(6,X'807f');}}
965 # Test the output of ".mode insert" with headers
967 do_test shell1-4.2.4 {
968 catchcmd test.db ".mode insert t3\n.headers on\nselect * from t3;"
969 } {0 {INSERT INTO t3(x,y) VALUES(1,NULL);
970 INSERT INTO t3(x,y) VALUES(2,'');
971 INSERT INTO t3(x,y) VALUES(3,1);
972 INSERT INTO t3(x,y) VALUES(4,2.25);
973 INSERT INTO t3(x,y) VALUES(5,'hello');
974 INSERT INTO t3(x,y) VALUES(6,X'807f');}}
976 # Test the output of ".mode tcl"
983 PRAGMA encoding=UTF8;
985 INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f');
987 catchcmd test.db ".mode tcl\nselect * from t1;"
995 # Test the output of ".mode tcl" with multiple columns
999 CREATE TABLE t2(x,y);
1000 INSERT INTO t2 VALUES(null, ''), (1, 2.25), ('hello', x'807f');
1002 catchcmd test.db ".mode tcl\nselect * from t2;"
1005 "hello" "\200\177"}}
1007 # Test the output of ".mode tcl" with ".nullvalue"
1009 do_test shell1-4.5 {
1010 catchcmd test.db ".mode tcl\n.nullvalue NULL\nselect * from t2;"
1013 "hello" "\200\177"}}
1015 # Test the output of ".mode tcl" with Tcl reserved characters
1017 do_test shell1-4.6 {
1019 CREATE TABLE tcl1(x);
1020 INSERT INTO tcl1 VALUES('"'), ('['), (']'), ('\{'), ('\}'), (';'), ('$');
1022 foreach {x y} [catchcmd test.db ".mode tcl\nselect * from tcl1;"] break
1023 list $x $y [llength $y]
1032 # Test the output of ".mode quote"
1034 do_test shell1-4.7 {
1035 catchcmd test.db ".mode quote\nselect x'0123456789ABCDEF';"
1036 } {0 X'0123456789abcdef'}
1038 # Test using arbitrary byte data with the shell via standard input/output.
1040 do_test shell1-5.0 {
1042 # NOTE: Skip NUL byte because it appears to be incompatible with command
1043 # shell argument parsing.
1045 for {set i 1} {$i < 256} {incr i} {
1047 # NOTE: Due to how the Tcl [exec] command works (i.e. where it treats
1048 # command channels opened for it as textual ones), the carriage
1049 # return character (and on Windows, the end-of-file character)
1050 # cannot be used here.
1052 if {$i==0x0D || ($tcl_platform(platform)=="windows" && $i==0x1A)} {
1055 # Tcl 8.7 maps 0x80 through 0x9f into valid UTF8. So skip those tests.
1056 if {$i>=0x80 && $i<=0x9f} continue
1057 if {$i>=0xE0 && $tcl_platform(os)=="OpenBSD"} continue
1058 if {$i>=0xE0 && $i<=0xEF && $tcl_platform(os)=="Linux"} continue
1059 set hex [format %02X $i]
1060 set char [subst \\x$hex]; set oldChar $char
1062 if {$tcl_platform(platform)=="windows"} {
1064 # NOTE: On Windows, we need to escape all the whitespace characters,
1065 # the alarm (\a) character, and those with special meaning to
1066 # the SQLite shell itself.
1069 \a \\a \b \\b \t \\t \n \\n \v \\v \f \\f \r \\r \
1070 " " "\" \"" \" \\\" ' \"'\" \\ \\\\]
1073 # NOTE: On Unix, we need to escape most of the whitespace characters
1074 # and those with special meaning to the SQLite shell itself.
1075 # The alarm (\a), backspace (\b), and carriage-return (\r)
1076 # characters do not appear to require escaping on Unix. For
1077 # the alarm and backspace characters, this is probably due to
1078 # differences in the command shell. For the carriage-return,
1079 # it is probably due to differences in how Tcl handles command
1080 # channel end-of-line translations.
1083 \t \\t \n \\n \v \\v \f \\f \
1084 " " "\" \"" \" \\\" ' \"'\" \\ \\\\]
1086 set char [string map $escapes $char]
1087 set x [catchcmdex test.db ".print $char\n"]
1088 set code [lindex $x 0]
1089 set res [lindex $x 1]
1091 error "failed with error: $res"
1093 if {$res ne "$oldChar\n"} {
1094 if {[llength $res] > 0} {
1095 set got [format %02X [scan $res %c]]
1099 error "failed with byte $hex mismatch, got $got"
1104 # These test cases do not work on MinGW
1107 # The string used here is the word "test" in Chinese.
1108 # In UTF-8, it is encoded as: \xE6\xB5\x8B\xE8\xAF\x95
1109 set test \u6D4B\u8BD5
1111 do_test shell1-6.0 {
1112 set fileName $test; append fileName .db
1113 catch {forcedelete $fileName}
1114 set x [catchcmdex $fileName "CREATE TABLE t1(x);\n.schema\n"]
1115 set code [lindex $x 0]
1116 set res [string trim [lindex $x 1]]
1118 error "failed with error: $res"
1120 if {$res ne "CREATE TABLE t1(x);"} {
1121 error "failed with mismatch: $res"
1123 if {![file exists $fileName]} {
1124 error "file \"$fileName\" (Unicode) does not exist"
1126 forcedelete $fileName
1129 do_test shell1-6.1 {
1130 catch {forcedelete test3.db}
1131 set x [catchcmdex test3.db \
1132 "CREATE TABLE [encoding convertto utf-8 $test](x);\n.schema\n"]
1133 set code [lindex $x 0]
1134 set res [string trim [lindex $x 1]]
1136 error "failed with error: $res"
1138 if {$res ne "CREATE TABLE ${test}(x);"} {
1139 error "failed with mismatch: $res"
1141 forcedelete test3.db
1146 forcedelete test.db test.db-journal test.db-wal
1149 # The shell tool ".schema" command uses virtual table "pragma_database_list"
1153 do_test shell1-7.1.1 {
1155 CREATE TABLE Z (x TEXT PRIMARY KEY);
1156 CREATE TABLE _ (x TEXT PRIMARY KEY);
1157 CREATE TABLE YY (x TEXT PRIMARY KEY);
1158 CREATE TABLE __ (x TEXT PRIMARY KEY);
1159 CREATE TABLE WWW (x TEXT PRIMARY KEY);
1160 CREATE TABLE ___ (x TEXT PRIMARY KEY);
1163 do_test shell1-7.1.2 {
1164 catchcmd "test.db" ".schema _"
1165 } {0 {CREATE TABLE Z (x TEXT PRIMARY KEY);
1166 CREATE TABLE _ (x TEXT PRIMARY KEY);}}
1167 do_test shell1-7.1.3 {
1168 catchcmd "test.db" ".schema \\\\_"
1169 } {0 {CREATE TABLE _ (x TEXT PRIMARY KEY);}}
1170 do_test shell1-7.1.4 {
1171 catchcmd "test.db" ".schema __"
1172 } {0 {CREATE TABLE YY (x TEXT PRIMARY KEY);
1173 CREATE TABLE __ (x TEXT PRIMARY KEY);}}
1174 do_test shell1-7.1.5 {
1175 catchcmd "test.db" ".schema \\\\_\\\\_"
1176 } {0 {CREATE TABLE __ (x TEXT PRIMARY KEY);}}
1177 do_test shell1-7.1.6 {
1178 catchcmd "test.db" ".schema ___"
1179 } {0 {CREATE TABLE WWW (x TEXT PRIMARY KEY);
1180 CREATE TABLE ___ (x TEXT PRIMARY KEY);}}
1181 do_test shell1-7.1.7 {
1182 catchcmd "test.db" ".schema \\\\_\\\\_\\\\_"
1183 } {0 {CREATE TABLE ___ (x TEXT PRIMARY KEY);}}
1187 # Test case for the ieee754 and decimal extensions in the shell.
1188 # See the "floatingpoint.html" file in the documentation for more
1191 do_test shell1-8.1 {
1192 catchcmd ":memory:" {
1193 -- The pow2 table will hold all the necessary powers of two.
1194 CREATE TABLE pow2(x INTEGER PRIMARY KEY, v TEXT);
1195 WITH RECURSIVE c(x,v) AS (
1198 SELECT x+1, decimal_mul(v,'2') FROM c WHERE x+1<=971
1199 ) INSERT INTO pow2(x,v) SELECT x, v FROM c;
1200 WITH RECURSIVE c(x,v) AS (
1203 SELECT x-1, decimal_mul(v,'0.5') FROM c WHERE x-1>=-1075
1204 ) INSERT INTO pow2(x,v) SELECT x, v FROM c;
1206 -- This query finds the decimal representation of each value in the "c" table.
1207 WITH c(n) AS (VALUES(47.49))
1208 ----XXXXX----------- Replace with whatever you want
1209 SELECT decimal_mul(ieee754_mantissa(c.n),pow2.v)
1210 FROM pow2, c WHERE pow2.x=ieee754_exponent(c.n);
1212 } {0 47.49000000000000198951966012828052043914794921875}
1213 do_test shell1-8.2 {
1216 SELECT ieee754(47.49) AS x;
1218 } {0 {┌───────────────────────────────┐
1220 ├───────────────────────────────┤
1221 │ ieee754(6683623321994527,-47) │
1222 └───────────────────────────────┘}}
1223 do_test shell1-8.3 {
1224 catchcmd ":memory: --box" {
1225 select ieee754(6683623321994527,-47) as x;
1232 do_test shell1-8.4 {
1233 catchcmd ":memory: --table" {SELECT ieee754_mantissa(47.49) AS M, ieee754_exponent(47.49) AS E;}
1234 } {0 {+------------------+-----+
1236 +------------------+-----+
1237 | 6683623321994527 | -47 |
1238 +------------------+-----+}}
1239 do_test shell1-8.5 {
1240 catchcmd ":memory: --box" {
1241 create table t(a text, b int);
1242 insert into t values ('too long for one line', 1), ('shorter', NULL);
1247 } {0 {┌────────────┬────────────┐
1249 ├────────────┼────────────┤
1253 ├────────────┼────────────┤
1255 └────────────┴────────────┘}}
1257 #----------------------------------------------------------------------------
1258 # Test cases shell1-9.*: Basic test that "dot" commands and SQL intermix ok.
1260 do_test shell1-9.1 {
1264 x */ select 1,2; --x
1269 select 2,1; select 3,4;
1275 #----------------------------------------------------------------------------
1276 # Test cases shell1-10.*: Test that certain static extensions are there.
1278 do_test shell1-10.1 {
1282 select base64(base64(cast('digity-doo' as blob))),
1283 base85(base85(cast('digity-doo' as blob)));
1285 } {0 digity-doo|digity-doo}