AryaWu/sqlite
0
1# 2009 Nov 112#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# The focus of this file is testing the CLI shell tool.13#14# TESTRUNNER: shell15#16 17# Test plan:18#19# shell1-1.*: Basic command line option handling.20# shell1-2.*: Basic "dot" command token parsing.21# shell1-3.*: Basic test that "dot" command can be called.22# shell1-{4-8}.*: Test various "dot" commands's functionality.23# shell1-9.*: Basic test that "dot" commands and SQL intermix ok.24#25set testdir [file dirname $argv0]26source $testdir/tester.tcl27set CLI [test_cli_invocation]28db close29forcedelete test.db test.db-journal test.db-wal30sqlite3 db test.db31 32#----------------------------------------------------------------------------33# Test cases shell1-1.*: Basic command line option handling.34#35 36# invalid option37do_test shell1-1.1.1 {38 set res [catchcmd "-bad test.db" ""]39 set rc [lindex $res 0]40 list $rc \41 [regexp {Error: unknown option: -bad} $res]42} {1 1}43do_test shell1-1.1.1b {44 set res [catchcmd "test.db -bad" ""]45 set rc [lindex $res 0]46 list $rc \47 [regexp {Error: unknown option: -bad} $res]48} {1 1}49# error on extra options50do_test shell1-1.1.2 {51 catchcmd "test.db \"select+3\" \"select+4\"" ""52} {0 {3534}}54# error on extra options55do_test shell1-1.1.3 {56 catchcmd "test.db FOO test.db BAD" ".quit"57} {/1 .Error: in prepare, near "FOO": syntax error*/}58 59# -help60do_test shell1-1.2.1 {61 set res [catchcmd "-help test.db" ""]62 set rc [lindex $res 0]63 list $rc \64 [regexp {Usage} $res] \65 [regexp {\-init} $res] \66 [regexp {\-version} $res]67} {1 1 1 1}68 69# -init filename read/process named file70forcedelete FOO71set out [open FOO w]72puts $out ""73close $out74do_test shell1-1.3.1 {75 catchcmd "-init FOO test.db" ""76} {0 {}}77do_test shell1-1.3.2 {78 catchcmd "-init FOO test.db .quit BAD" ""79} {0 {}}80do_test shell1-1.3.3 {81 catchcmd "-init FOO test.db BAD .quit" ""82} {/1 .Error: in prepare, near "BAD": syntax error*/}83 84# -echo print commands before execution85do_test shell1-1.4.1 {86 catchcmd "-echo test.db" ""87} {0 {}}88 89# -[no]header turn headers on or off90do_test shell1-1.5.1 {91 catchcmd "-header test.db" ""92} {0 {}}93do_test shell1-1.5.2 {94 catchcmd "-noheader test.db" ""95} {0 {}}96 97# -bail stop after hitting an error98do_test shell1-1.6.1 {99 catchcmd "-bail test.db" ""100} {0 {}}101 102# -interactive force interactive I/O103do_test shell1-1.7.1 {104 set res [catchcmd "-interactive test.db" ".quit"]105 set rc [lindex $res 0]106 list $rc \107 [regexp {SQLite version} $res] \108 [regexp {Enter ".help" for usage hints} $res]109} {0 1 1}110 111# -batch force batch I/O112do_test shell1-1.8.1 {113 catchcmd "-batch test.db" ""114} {0 {}}115 116# -column set output mode to 'column'117do_test shell1-1.9.1 {118 catchcmd "-column test.db" ""119} {0 {}}120 121# -csv set output mode to 'csv'122do_test shell1-1.10.1 {123 catchcmd "-csv test.db" ""124} {0 {}}125 126# -html set output mode to HTML127do_test shell1-1.11.1 {128 catchcmd "-html test.db" ""129} {0 {}}130 131# -line set output mode to 'line'132do_test shell1-1.12.1 {133 catchcmd "-line test.db" ""134} {0 {}}135 136# -list set output mode to 'list'137do_test shell1-1.13.1 {138 catchcmd "-list test.db" ""139} {0 {}}140 141# -separator 'x' set output field separator (|)142do_test shell1-1.14.1 {143 catchcmd "-separator 'x' test.db" ""144} {0 {}}145do_test shell1-1.14.2 {146 catchcmd "-separator x test.db" ""147} {0 {}}148do_test shell1-1.14.3 {149 set res [catchcmd "-separator" ""]150 set rc [lindex $res 0]151 list $rc \152 [regexp {Error: missing argument to -separator} $res]153} {1 1}154 155# -stats print memory stats before each finalize156do_test shell1-1.14b.1 {157 catchcmd "-stats test.db" ""158} {0 {}}159 160# -nullvalue 'text' set text string for NULL values161do_test shell1-1.15.1 {162 catchcmd "-nullvalue 'x' test.db" ""163} {0 {}}164do_test shell1-1.15.2 {165 catchcmd "-nullvalue x test.db" ""166} {0 {}}167do_test shell1-1.15.3 {168 set res [catchcmd "-nullvalue" ""]169 set rc [lindex $res 0]170 list $rc \171 [regexp {Error: missing argument to -nullvalue} $res]172} {1 1}173 174# -version show SQLite version175do_test shell1-1.16.1 {176 set x [catchcmd "-version test.db" ""]177} {/3.[0-9.]+ 20\d\d-[01]\d-\d\d \d\d:\d\d:\d\d [0-9a-f]+/}178 179# Handle no-more-options option180forcedelete ./--db181do_test shell1-1.17.1 {182 catchcmd {-- --db "CREATE TABLE T(c1);"}183} {0 {}}184do_test shell1-1.17.2 {185 catchcmd {-- --db "SELECT name from sqlite_schema;"}186} {0 T}187forcedelete ./--db188 189#----------------------------------------------------------------------------190# Test cases shell1-2.*: Basic "dot" command token parsing.191#192 193# check first token handling194do_test shell1-2.1.1 {195 catchcmd "test.db" ".foo"196} {1 {Error: unknown command or invalid arguments: "foo". Enter ".help" for help}}197do_test shell1-2.1.2 {198 catchcmd "test.db" ".\"foo OFF\""199} {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}}200do_test shell1-2.1.3 {201 catchcmd "test.db" ".\'foo OFF\'"202} {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}}203 204# unbalanced quotes205do_test shell1-2.2.1 {206 catchcmd "test.db" ".\"foo OFF"207} {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}}208do_test shell1-2.2.2 {209 catchcmd "test.db" ".\'foo OFF"210} {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}}211do_test shell1-2.2.3 {212 catchcmd "test.db" ".explain \"OFF"213} {0 {}}214do_test shell1-2.2.4 {215 catchcmd "test.db" ".explain \'OFF"216} {0 {}}217do_test shell1-2.2.5 {218 catchcmd "test.db" ".mode \"insert FOO"219} {1 {line 1: .mode "insert FOO220line 1: ^--- unknown mode221line 1: Use ".help .mode" for more info}}222do_test shell1-2.2.6 {223 catchcmd "test.db" ".mode \'insert FOO"224} {1 {line 1: .mode 'insert FOO225line 1: ^--- unknown mode226line 1: Use ".help .mode" for more info}}227 228# check multiple tokens, and quoted tokens229do_test shell1-2.3.1 {230 catchcmd "test.db" ".explain 1"231} {0 {}}232do_test shell1-2.3.2 {233 catchcmd "test.db" ".explain on"234} {0 {}}235do_test shell1-2.3.3 {236 catchcmd "test.db" ".explain \"1 2 3\""237} {1 {ERROR: Not a boolean value: "1 2 3". Assuming "no".}}238do_test shell1-2.3.4 {239 catchcmd "test.db" ".explain \"OFF\""240} {0 {}}241do_test shell1-2.3.5 {242 catchcmd "test.db" ".\'explain\' \'OFF\'"243} {0 {}}244do_test shell1-2.3.6 {245 catchcmd "test.db" ".explain \'OFF\'"246} {0 {}}247do_test shell1-2.3.7 {248 catchcmd "test.db" ".\'explain\' \'OFF\'"249} {0 {}}250 251# check quoted args are unquoted252do_test shell1-2.4.1 {253 catchcmd "test.db" ".mode FOO"254} {1 {line 1: .mode FOO255line 1: ^--- unknown mode256line 1: Use ".help .mode" for more info}}257do_test shell1-2.4.2 {258 catchcmd "test.db" ".mode csv"259} {0 {}}260do_test shell1-2.4.2 {261 catchcmd "test.db" ".mode \"csv\""262} {0 {}}263 264# check that certain quoted arg escapes work265do_test shell1-2.5.1 {266 catchcmd ":memory:" ".print \"\\060\\077 \\x3f\\x30 \\a\\t\""267} [list 0 "0? ?0 \a\t"]268 269 270#----------------------------------------------------------------------------271# Test cases shell1-3.*: Basic test that "dot" command can be called.272#273 274# .backup ?DB? FILE Backup DB (default "main") to FILE275do_test shell1-3.1.1 {276 catchcmd "test.db" ".backup"277} {1 {missing FILENAME argument on .backup}}278forcedelete FOO279do_test shell1-3.1.2 {280 catchcmd "test.db" ".backup FOO"281} {0 {}}282do_test shell1-3.1.3 {283 catchcmd "test.db" ".backup FOO BAR"284} {1 {Error: unknown database FOO}}285do_test shell1-3.1.4 {286 # too many arguments287 catchcmd "test.db" ".backup FOO BAR BAD"288} {1 {Usage: .backup ?DB? ?OPTIONS? FILENAME}}289 290# .bail ON|OFF Stop after hitting an error. Default OFF291do_test shell1-3.2.1 {292 catchcmd "test.db" ".bail"293} {1 {Usage: .bail on|off}}294do_test shell1-3.2.2 {295 catchcmd "test.db" ".bail ON"296} {0 {}}297do_test shell1-3.2.3 {298 catchcmd "test.db" ".bail OFF"299} {0 {}}300do_test shell1-3.2.4 {301 # too many arguments302 catchcmd "test.db" ".bail OFF BAD"303} {1 {Usage: .bail on|off}}304 305ifcapable vtab {306# .databases List names and files of attached databases307do_test shell1-3.3.1 {308 catchcmd "-csv test.db" ".databases"309} "/0.+main.+[string map {/ ".{1,2}"} [string range [get_pwd] 0 10]].*/"310do_test shell1-3.3.2 {311 # extra arguments ignored312 catchcmd "test.db" ".databases BAD"313} "/0.+main.+[string map {/ ".{1,2}"} [string range [get_pwd] 0 10]].*/"314}315 316# .dump ?TABLE? ... Dump the database in an SQL text format317# If TABLE specified, only dump tables matching318# LIKE pattern TABLE.319do_test shell1-3.4.1 {320 set res [catchcmd "test.db" ".dump"]321 list [regexp {BEGIN TRANSACTION;} $res] \322 [regexp {COMMIT;} $res]323} {1 1}324do_test shell1-3.4.2 {325 set res [catchcmd "test.db" ".dump FOO"]326 list [regexp {BEGIN TRANSACTION;} $res] \327 [regexp {COMMIT;} $res]328} {1 1}329# The .dump command now accepts multiple arguments330#do_test shell1-3.4.3 {331# # too many arguments332# catchcmd "test.db" ".dump FOO BAD"333#} {1 {Usage: .dump ?--preserve-rowids? ?--newlines? ?LIKE-PATTERN?}}334 335# .echo ON|OFF Turn command echo on or off336do_test shell1-3.5.1 {337 catchcmd "test.db" ".echo"338} {1 {Usage: .echo on|off}}339do_test shell1-3.5.2 {340 catchcmd "test.db" ".echo ON"341} {0 {}}342do_test shell1-3.5.3 {343 catchcmd "test.db" ".echo OFF"344} {0 {}}345do_test shell1-3.5.4 {346 # too many arguments347 catchcmd "test.db" ".echo OFF BAD"348} {1 {Usage: .echo on|off}}349 350# .exit Exit this program351do_test shell1-3.6.1 {352 catchcmd "test.db" ".exit"353} {0 {}}354 355# .explain ON|OFF Turn output mode suitable for EXPLAIN on or off.356do_test shell1-3.7.1 {357 catchcmd "test.db" ".explain"358 # explain is the exception to the booleans. without an option, it turns it on.359} {0 {}}360do_test shell1-3.7.2 {361 catchcmd "test.db" ".explain ON"362} {0 {}}363do_test shell1-3.7.3 {364 catchcmd "test.db" ".explain OFF"365} {0 {}}366do_test shell1-3.7.4 {367 # extra arguments ignored368 catchcmd "test.db" ".explain OFF BAD"369} {0 {}}370 371# .header(s) ON|OFF Turn display of headers on or off372do_test shell1-3.9.1 {373 catchcmd "test.db" ".header"374} {1 {Usage: .headers on|off}}375do_test shell1-3.9.2 {376 catchcmd "test.db" ".header ON"377} {0 {}}378do_test shell1-3.9.3 {379 catchcmd "test.db" ".header OFF"380} {0 {}}381do_test shell1-3.9.4 {382 # too many arguments383 catchcmd "test.db" ".header OFF BAD"384} {1 {Usage: .headers on|off}}385 386do_test shell1-3.9.5 {387 catchcmd "test.db" ".headers"388} {1 {Usage: .headers on|off}}389do_test shell1-3.9.6 {390 catchcmd "test.db" ".headers ON"391} {0 {}}392do_test shell1-3.9.7 {393 catchcmd "test.db" ".headers OFF"394} {0 {}}395do_test shell1-3.9.8 {396 # too many arguments397 catchcmd "test.db" ".headers OFF BAD"398} {1 {Usage: .headers on|off}}399 400# .help Show this message401do_test shell1-3.10.1 {402 set res [catchcmd "test.db" ".help"]403 # look for a few of the possible help commands404 list [regexp {.help} $res] \405 [regexp {.quit} $res] \406 [regexp {.mode} $res]407} {1 1 1}408do_test shell1-3.10.2 {409 # we allow .help to take extra args (it is help after all)410 set res [catchcmd "test.db" ".help *"]411 # look for a few of the possible help commands412 list [regexp {.help} $res] \413 [regexp {.quit} $res] \414 [regexp {.mode} $res]415} {1 1 1}416 417# .import FILE TABLE Import data from FILE into TABLE418do_test shell1-3.11.1 {419 catchcmd "test.db" ".import"420} {/1 .ERROR: missing FILE argument.*/}421do_test shell1-3.11.2 {422 catchcmd "test.db" ".import FOO"423} {/1 .ERROR: missing TABLE argument.*/}424do_test shell1-3.11.3 {425 # too many arguments426 catchcmd "test.db" ".import FOO BAR BAD"427} {1 {line 1: .import FOO BAR BAD428line 1: ^--- unknown argument}}429 430# .indexes ?TABLE? Show names of all indexes431# If TABLE specified, only show indexes for tables432# matching LIKE pattern TABLE.433do_test shell1-3.12.1 {434 catchcmd "test.db" ".indexes"435} {0 {}}436do_test shell1-3.12.2 {437 catchcmd "test.db" ".indexes FOO"438} {0 {}}439do_test shell1-3.12.2-legacy {440 catchcmd "test.db" ".indices FOO"441} {0 {}}442do_test shell1-3.12.3 {443 # too many arguments444 catchcmd "test.db" ".indexes FOO BAD"445} {1 {Usage: .indexes ?LIKE-PATTERN?}}446 447# .mode MODE ?TABLE? Set output mode where MODE is one of:448# ascii Columns/rows delimited by 0x1F and 0x1E449# csv Comma-separated values450# column Left-aligned columns. (See .width)451# html HTML <table> code452# insert SQL insert statements for TABLE453# line One value per line454# list Values delimited by .separator strings455# tabs Tab-separated values456# tcl TCL list elements457do_test shell1-3.13.1 {458 catchcmd "test.db" ".mode batch\n.mode"459} {0 {.mode list}}460do_test shell1-3.13.2 {461 catchcmd "test.db" ".mode FOO"462} {1 {line 1: .mode FOO463line 1: ^--- unknown mode464line 1: Use ".help .mode" for more info}}465do_test shell1-3.13.3 {466 catchcmd "test.db" ".mode csv"467} {0 {}}468do_test shell1-3.13.4 {469 catchcmd "test.db" ".mode column"470} {0 {}}471do_test shell1-3.13.5 {472 catchcmd "test.db" ".mode html"473} {0 {}}474do_test shell1-3.13.6 {475 catchcmd "test.db" ".mode insert"476} {0 {}}477do_test shell1-3.13.7 {478 catchcmd "test.db" ".mode line"479} {0 {}}480do_test shell1-3.13.8 {481 catchcmd "test.db" ".mode list"482} {0 {}}483do_test shell1-3.13.9 {484 catchcmd "test.db" ".mode tabs"485} {0 {}}486do_test shell1-3.13.10 {487 catchcmd "test.db" ".mode tcl"488} {0 {}}489do_test shell1-3.13.11 {490 # extra arguments ignored491 catchcmd "test.db" ".mode tcl BAD"492} {0 {}}493 494# .nullvalue STRING Print STRING in place of NULL values495do_test shell1-3.14.1 {496 catchcmd "test.db" ".nullvalue"497} {1 {Usage: .nullvalue STRING}}498do_test shell1-3.14.2 {499 catchcmd "test.db" ".nullvalue FOO"500} {0 {}}501do_test shell1-3.14.3 {502 # too many arguments503 catchcmd "test.db" ".nullvalue FOO BAD"504} {1 {Usage: .nullvalue STRING}}505 506# .output FILENAME Send output to FILENAME507do_test shell1-3.15.1 {508 catchcmd "test.db" ".output509.print x"510} {0 x}511do_test shell1-3.15.2 {512 catchcmd "test.db" ".mode batch\n.output FOO513.print x514.output515SELECT readfile('FOO');"516} {0 {x517}}518do_test shell1-3.15.3 {519 # too many arguments520 catchcmd "test.db" ".output FOO BAD"521} {1 {line 1: .output FOO BAD522line 1: ^--- surplus argument}}523 524# .output stdout Send output to the screen525do_test shell1-3.16.1 {526 catchcmd "test.db" ".output stdout"527} {0 {}}528do_test shell1-3.16.2 {529 # too many arguments530 catchcmd "test.db" ".output stdout BAD"531} {1 {line 1: .output stdout BAD532line 1: ^--- surplus argument}}533 534# .prompt MAIN CONTINUE Replace the standard prompts535do_test shell1-3.17.1 {536 catchcmd "test.db" ".prompt"537} {0 {}}538do_test shell1-3.17.2 {539 catchcmd "test.db" ".prompt FOO"540} {0 {}}541do_test shell1-3.17.3 {542 catchcmd "test.db" ".prompt FOO BAR"543} {0 {}}544do_test shell1-3.17.4 {545 # too many arguments546 catchcmd "test.db" ".prompt FOO BAR BAD"547} {0 {}}548 549# .quit Exit this program550do_test shell1-3.18.1 {551 catchcmd "test.db" ".quit"552} {0 {}}553do_test shell1-3.18.2 {554 # too many arguments555 catchcmd "test.db" ".quit BAD"556} {0 {}}557 558# .read FILENAME Execute SQL in FILENAME559do_test shell1-3.19.1 {560 catchcmd "test.db" ".read"561} {1 {Usage: .read FILE}}562do_test shell1-3.19.2 {563 forcedelete FOO564 catchcmd "test.db" ".read FOO"565} {1 {Error: cannot open "FOO"}}566do_test shell1-3.19.3 {567 # too many arguments568 catchcmd "test.db" ".read FOO BAD"569} {1 {Usage: .read FILE}}570 571# .restore ?DB? FILE Restore content of DB (default "main") from FILE572do_test shell1-3.20.1 {573 catchcmd "test.db" ".restore"574} {1 {Usage: .restore ?DB? FILE}}575do_test shell1-3.20.2 {576 catchcmd "test.db" ".restore FOO"577} {0 {}}578do_test shell1-3.20.3 {579 catchcmd "test.db" ".restore FOO BAR"580} {1 {Error: unknown database FOO}}581do_test shell1-3.20.4 {582 # too many arguments583 catchcmd "test.db" ".restore FOO BAR BAD"584} {1 {Usage: .restore ?DB? FILE}}585 586ifcapable vtab {587# .schema ?TABLE? Show the CREATE statements588# If TABLE specified, only show tables matching589# LIKE pattern TABLE.590do_test shell1-3.21.1 {591 catchcmd "test.db" ".schema"592} {0 {}}593do_test shell1-3.21.2 {594 catchcmd "test.db" ".schema FOO"595} {0 {}}596do_test shell1-3.21.3 {597 # too many arguments598 catchcmd "test.db" ".schema FOO BAD"599} {1 {Usage: .schema ?--indent? ?--nosys? ?LIKE-PATTERN?}}600 601do_test shell1-3.21.4 {602 catchcmd "test.db" {603 CREATE TABLE t1(x);604 CREATE VIEW v2 AS SELECT x+1 AS y FROM t1;605 CREATE VIEW v1 AS SELECT y+1 FROM v2;606 }607 catchcmd "test.db" ".schema"608} {0 {CREATE TABLE t1(x);609CREATE VIEW v2 AS SELECT x+1 AS y FROM t1610/* v2(y) */;611CREATE VIEW v1 AS SELECT y+1 FROM v2612/* v1("y+1") */;}}613 614 catch {db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;}}615}616do_test shell1-3.21.5 {617 exec {*}$CLI -noinit test.db \618 {CREATE TABLE t2(a INTEGER PRIMARY KEY, b BLOB DEFAULT(jsonb('[]')),c TEXT NOT NULL)STRICT;} \619 {.schema -indent t2}620} {CREATE TABLE t2(621 a INTEGER PRIMARY KEY,622 b BLOB DEFAULT(jsonb('[]')),623 c TEXT NOT NULL624)STRICT;}625do_test shell1-3.21.6 {626 exec {*}$CLI -noinit test.db \627 {DROP TABLE t2;} \628 {.schema -indent t2}629} {}630 631# .separator STRING Change column separator used by output and .import632do_test shell1-3.22.1 {633 catchcmd "test.db" ".separator"634} {1 {Usage: .separator COL ?ROW?}}635do_test shell1-3.22.2 {636 catchcmd "test.db" ".separator FOO"637} {0 {}}638do_test shell1-3.22.3 {639 catchcmd "test.db" ".separator ABC XYZ"640} {0 {}}641do_test shell1-3.22.4 {642 # too many arguments643 catchcmd "test.db" ".separator FOO BAD BAD2"644} {1 {Usage: .separator COL ?ROW?}}645 646# .show Show the current values for various settings647do_test shell1-3.23.1 {648 set res [catchcmd "test.db" ".mode batch\n.show"]649 list [regexp {echo:} $res] \650 [regexp {explain:} $res] \651 [regexp {headers:} $res] \652 [regexp {mode:} $res] \653 [regexp {nullvalue:} $res] \654 [regexp {output:} $res] \655 [regexp {colseparator:} $res] \656 [regexp {rowseparator:} $res] \657 [regexp {stats:} $res] \658 [regexp {width:} $res]659} {1 1 1 1 1 1 1 1 1 1}660do_test shell1-3.23.2 {661 # too many arguments662 catchcmd "test.db" ".show BAD"663} {1 {Usage: .show}}664 665# .stats ON|OFF Turn stats on or off666#do_test shell1-3.23b.1 {667# catchcmd "test.db" ".stats"668#} {1 {Usage: .stats on|off|stmt|vmstep}}669do_test shell1-3.23b.2 {670 catchcmd "test.db" ".stats ON"671} {0 {}}672do_test shell1-3.23b.3 {673 catchcmd "test.db" ".stats OFF"674} {0 {}}675do_test shell1-3.23b.4 {676 # too many arguments677 catchcmd "test.db" ".stats OFF BAD"678} {1 {Usage: .stats ?on|off|stmt|vmstep?}}679 680# Ticket 7be932dfa60a8a6b3b26bcf7623ec46e0a403ddb 2018-06-07681# Adverse interaction between .stats and .eqp682#683do_test shell1-3.23b.5 {684 catchcmd "test.db" [string map {"\n " "\n"} {.mode batch685 CREATE TEMP TABLE t1(x);686 INSERT INTO t1 VALUES(1),(2);687 .stats on688 .eqp full689 SELECT * FROM t1;690 }]691} {/1\n2\n/}692 693# .tables ?TABLE? List names of tables694# If TABLE specified, only list tables matching695# LIKE pattern TABLE.696do_test shell1-3.24.1 {697 catchcmd "test.db" ".tables"698} {0 {}}699do_test shell1-3.24.2 {700 catchcmd "test.db" ".tables FOO"701} {0 {}}702do_test shell1-3.24.3 {703 # too many arguments704 catchcmd "test.db" ".tables FOO BAD"705} {0 {}}706 707# .timeout MS Try opening locked tables for MS milliseconds708do_test shell1-3.25.1 {709 catchcmd "test.db" ".timeout"710} {0 {}}711do_test shell1-3.25.2 {712 catchcmd "test.db" ".timeout zzz"713 # this should be treated the same as a '0' timeout714} {0 {}}715do_test shell1-3.25.3 {716 catchcmd "test.db" ".timeout 1"717} {0 {}}718do_test shell1-3.25.4 {719 # too many arguments720 catchcmd "test.db" ".timeout 1 BAD"721} {0 {}}722 723# .width NUM NUM ... Set column widths for "column" mode724do_test shell1-3.26.1 {725 catchcmd "test.db" ".width"726} {0 {}}727do_test shell1-3.26.2 {728 catchcmd "test.db" ".width xxx"729 # this should be treated the same as a '0' width for col 1730} {0 {}}731do_test shell1-3.26.3 {732 catchcmd "test.db" ".width xxx yyy"733 # this should be treated the same as a '0' width for col 1 and 2734} {0 {}}735do_test shell1-3.26.4 {736 catchcmd "test.db" ".width 1 1"737 # this should be treated the same as a '1' width for col 1 and 2738} {0 {}}739do_test shell1-3.26.5 {740 catchcmd "test.db" ".mode column\n.header off\n.width 10 -10\nSELECT 'abcdefg', 123456;"741 # this should be treated the same as a '1' width for col 1 and 2742} {0 {abcdefg 123456}}743do_test shell1-3.26.6 {744 catchcmd "test.db" ".mode column\n.header off\n.width -10 10\nSELECT 'abcdefg', 123456;"745 # this should be treated the same as a '1' width for col 1 and 2746} {0 { abcdefg 123456}}747 748 749# .timer ON|OFF Turn the CPU timer measurement on or off750do_test shell1-3.27.1 {751 catchcmd "test.db" ".timer"752} {1 {Usage: .timer on|off}}753do_test shell1-3.27.2 {754 catchcmd "test.db" ".timer ON"755} {0 {}}756do_test shell1-3.27.3 {757 catchcmd "test.db" ".timer OFF"758} {0 {}}759do_test shell1-3.27.4 {760 # too many arguments761 catchcmd "test.db" ".timer OFF BAD"762} {1 {Usage: .timer on|off}}763 764do_test shell1-3.28.1 {765 catchcmd test.db \766 ".mode batch\n.log stdout\nSELECT coalesce(sqlite_log(123,'hello'),'456');"767} "0 {(123) hello\n456}"768 769do_test shell1-3-29.1 {770 catchcmd "test.db" ".print this is a test"771} {0 {this is a test}}772 773# dot-command argument quoting774do_test shell1-3-30.1 {775 catchcmd {test.db} {.print "this\"is'a\055test" 'this\"is\\a\055test'}776} {0 {this"is'a-test this\"is\\a\055test}}777do_test shell1-3-31.1 {778 catchcmd {test.db} {.print "this\nis\ta\\test" 'this\nis\ta\\test'}779} [list 0 "this\nis\ta\\test this\\nis\\ta\\\\test"]780 781 782# Test the output of the ".dump" command783#784do_test shell1-4.1 {785 db close786 forcedelete test.db787 sqlite3 db test.db788 db eval {789 PRAGMA encoding=UTF16;790 CREATE TABLE t1(x);791 INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f');792 CREATE TABLE t3(x,y);793 INSERT INTO t3 VALUES(1,null), (2,''), (3,1),794 (4,2.25), (5,'hello'), (6,x'807f');795 }796 catchcmd test.db {.dump}797} {0 {PRAGMA foreign_keys=OFF;798BEGIN TRANSACTION;799CREATE TABLE t1(x);800INSERT INTO t1 VALUES(NULL);801INSERT INTO t1 VALUES('');802INSERT INTO t1 VALUES(1);803INSERT INTO t1 VALUES(2.25);804INSERT INTO t1 VALUES('hello');805INSERT INTO t1 VALUES(x'807f');806CREATE TABLE t3(x,y);807INSERT INTO t3 VALUES(1,NULL);808INSERT INTO t3 VALUES(2,'');809INSERT INTO t3 VALUES(3,1);810INSERT INTO t3 VALUES(4,2.25);811INSERT INTO t3 VALUES(5,'hello');812INSERT INTO t3 VALUES(6,x'807f');813COMMIT;}}814 815 816ifcapable vtab {817 818# The --preserve-rowids option to .dump819#820do_test shell1-4.1.1 {821 catchcmd test.db {.dump --preserve-rowids}822} {0 {PRAGMA foreign_keys=OFF;823BEGIN TRANSACTION;824CREATE TABLE t1(x);825INSERT INTO t1(rowid,x) VALUES(1,NULL);826INSERT INTO t1(rowid,x) VALUES(2,'');827INSERT INTO t1(rowid,x) VALUES(3,1);828INSERT INTO t1(rowid,x) VALUES(4,2.25);829INSERT INTO t1(rowid,x) VALUES(5,'hello');830INSERT INTO t1(rowid,x) VALUES(6,x'807f');831CREATE TABLE t3(x,y);832INSERT INTO t3(rowid,x,y) VALUES(1,1,NULL);833INSERT INTO t3(rowid,x,y) VALUES(2,2,'');834INSERT INTO t3(rowid,x,y) VALUES(3,3,1);835INSERT INTO t3(rowid,x,y) VALUES(4,4,2.25);836INSERT INTO t3(rowid,x,y) VALUES(5,5,'hello');837INSERT INTO t3(rowid,x,y) VALUES(6,6,x'807f');838COMMIT;}}839 840# If the table contains an INTEGER PRIMARY KEY, do not record a separate841# rowid column in the output.842#843do_test shell1-4.1.2 {844 db close845 forcedelete test2.db846 sqlite3 db test2.db847 db eval {848 CREATE TABLE t1(x INTEGER PRIMARY KEY, y);849 INSERT INTO t1 VALUES(1,null), (2,''), (3,1),850 (4,2.25), (5,'hello'), (6,x'807f');851 }852 catchcmd test2.db {.dump --preserve-rowids}853} {0 {PRAGMA foreign_keys=OFF;854BEGIN TRANSACTION;855CREATE TABLE t1(x INTEGER PRIMARY KEY, y);856INSERT INTO t1(x,y) VALUES(1,NULL);857INSERT INTO t1(x,y) VALUES(2,'');858INSERT INTO t1(x,y) VALUES(3,1);859INSERT INTO t1(x,y) VALUES(4,2.25);860INSERT INTO t1(x,y) VALUES(5,'hello');861INSERT INTO t1(x,y) VALUES(6,x'807f');862COMMIT;}}863 864# Verify that the table named [table] is correctly quoted and that865# an INTEGER PRIMARY KEY DESC is not an alias for the rowid.866#867do_test shell1-4.1.3 {868 db close869 forcedelete test2.db870 sqlite3 db test2.db871 db eval {872 CREATE TABLE [table](x INTEGER PRIMARY KEY DESC, y);873 INSERT INTO [table] VALUES(1,null), (12,''), (23,1),874 (34,2.25), (45,'hello'), (56,x'807f');875 }876 catchcmd test2.db {.dump --preserve-rowids}877} {0 {PRAGMA foreign_keys=OFF;878BEGIN TRANSACTION;879CREATE TABLE [table](x INTEGER PRIMARY KEY DESC, y);880INSERT INTO "table"(rowid,x,y) VALUES(1,1,NULL);881INSERT INTO "table"(rowid,x,y) VALUES(2,12,'');882INSERT INTO "table"(rowid,x,y) VALUES(3,23,1);883INSERT INTO "table"(rowid,x,y) VALUES(4,34,2.25);884INSERT INTO "table"(rowid,x,y) VALUES(5,45,'hello');885INSERT INTO "table"(rowid,x,y) VALUES(6,56,x'807f');886COMMIT;}}887 888# Do not record rowids for a WITHOUT ROWID table. Also check correct quoting889# of table names that contain odd characters.890#891do_test shell1-4.1.4 {892 db close893 forcedelete test2.db894 sqlite3 db test2.db895 db eval {896 CREATE TABLE [ta<>ble](x INTEGER PRIMARY KEY, y) WITHOUT ROWID;897 INSERT INTO [ta<>ble] VALUES(1,null), (12,''), (23,1),898 (34,2.25), (45,'hello'), (56,x'807f');899 }900 catchcmd test2.db {.dump --preserve-rowids}901} {0 {PRAGMA foreign_keys=OFF;902BEGIN TRANSACTION;903CREATE TABLE [ta<>ble](x INTEGER PRIMARY KEY, y) WITHOUT ROWID;904INSERT INTO "ta<>ble"(x,y) VALUES(1,NULL);905INSERT INTO "ta<>ble"(x,y) VALUES(12,'');906INSERT INTO "ta<>ble"(x,y) VALUES(23,1);907INSERT INTO "ta<>ble"(x,y) VALUES(34,2.25);908INSERT INTO "ta<>ble"(x,y) VALUES(45,'hello');909INSERT INTO "ta<>ble"(x,y) VALUES(56,x'807f');910COMMIT;}}911 912# Do not record rowids if the rowid is inaccessible913#914do_test shell1-4.1.5 {915 db close916 forcedelete test2.db917 sqlite3 db test2.db918 db eval {919 CREATE TABLE t1(_ROWID_,rowid,oid);920 INSERT INTO t1 VALUES(1,null,'alpha'), (12,'',99), (23,1,x'b0b1b2');921 }922 catchcmd test2.db {.dump --preserve-rowids}923} {0 {PRAGMA foreign_keys=OFF;924BEGIN TRANSACTION;925CREATE TABLE t1(_ROWID_,rowid,oid);926INSERT INTO t1(_ROWID_,rowid,oid) VALUES(1,NULL,'alpha');927INSERT INTO t1(_ROWID_,rowid,oid) VALUES(12,'',99);928INSERT INTO t1(_ROWID_,rowid,oid) VALUES(23,1,x'b0b1b2');929COMMIT;}}930 931} else {932 933do_test shell1-4.1.6 {934 db close935 forcedelete test2.db936 sqlite3 db test2.db937 db eval {938 CREATE TABLE t1(x INTEGER PRIMARY KEY, y);939 INSERT INTO t1 VALUES(1,null), (2,''), (3,1),940 (4,2.25), (5,'hello'), (6,x'807f');941 }942 catchcmd test2.db {.dump --preserve-rowids}943} {/.* --preserve-rowids option is not compatible with SQLITE_OMIT_VIRTUALTABLE/}944 945}946 947# DELETE content of sqlite_sequence prior to repopulating,948# but only if the sqlite_sequence table is non-empty.949# Forum: 2024-10-13T17:10:01z and 2025-10-29T19:38:43z950#951do_test shell1-4.1.7 {952 db close953 forcedelete test2.db954 sqlite3 db test2.db955 db eval {956 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b);957 INSERT INTO t1 VALUES(1,2),(20,21),(15,16);958 }959 catchcmd test2.db {.dump}960} {0 {PRAGMA foreign_keys=OFF;961BEGIN TRANSACTION;962CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b);963INSERT INTO t1 VALUES(1,2);964INSERT INTO t1 VALUES(15,16);965INSERT INTO t1 VALUES(20,21);966PRAGMA writable_schema=ON;967CREATE TABLE IF NOT EXISTS sqlite_sequence(name,seq);968DELETE FROM sqlite_sequence;969INSERT INTO sqlite_sequence VALUES('t1',20);970PRAGMA writable_schema=OFF;971COMMIT;}}972do_test shell1-4.1.8 {973 db close974 forcedelete test2.db975 sqlite3 db test2.db976 db eval {977 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b);978 INSERT INTO t1 VALUES(1,2),(20,21),(15,16);979 CREATE TABLE t2(x,y);980 INSERT INTO t2 VALUES(99,88);981 DROP TABLE t1;982 }983 catchcmd test2.db {.dump}984} {0 {PRAGMA foreign_keys=OFF;985BEGIN TRANSACTION;986CREATE TABLE t2(x,y);987INSERT INTO t2 VALUES(99,88);988COMMIT;}}989do_test shell1-4.1.9 {990 db close991 forcedelete test2.db992 sqlite3 db test2.db993 db eval {994 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b);995 INSERT INTO t1 VALUES(1,2),(20,21),(15,16);996 CREATE TABLE t2(x,y);997 INSERT INTO t2 VALUES(99,88);998 INSERT INTO sqlite_sequence VALUES('extra',999);999 DROP TABLE t1;1000 }1001 catchcmd test2.db {.dump}1002} {0 {PRAGMA foreign_keys=OFF;1003BEGIN TRANSACTION;1004CREATE TABLE t2(x,y);1005INSERT INTO t2 VALUES(99,88);1006PRAGMA writable_schema=ON;1007CREATE TABLE IF NOT EXISTS sqlite_sequence(name,seq);1008DELETE FROM sqlite_sequence;1009INSERT INTO sqlite_sequence VALUES('extra',999);1010PRAGMA writable_schema=OFF;1011COMMIT;}}1012 1013# Test the output of ".mode insert"1014#1015do_test shell1-4.2.1 {1016 catchcmd test.db ".mode insert t1\nselect * from t1;"1017} {0 {INSERT INTO t1 VALUES(NULL);1018INSERT INTO t1 VALUES('');1019INSERT INTO t1 VALUES(1);1020INSERT INTO t1 VALUES(2.25);1021INSERT INTO t1 VALUES('hello');1022INSERT INTO t1 VALUES(x'807f');}}1023 1024# Test the output of ".mode insert" with headers1025#1026do_test shell1-4.2.2 {1027 catchcmd test.db ".mode insert t1\n.headers on\nselect * from t1;"1028} {0 {INSERT INTO t1(x) VALUES(NULL);1029INSERT INTO t1(x) VALUES('');1030INSERT INTO t1(x) VALUES(1);1031INSERT INTO t1(x) VALUES(2.25);1032INSERT INTO t1(x) VALUES('hello');1033INSERT INTO t1(x) VALUES(x'807f');}}1034 1035# Test the output of ".mode insert"1036#1037do_test shell1-4.2.3 {1038 catchcmd test.db ".mode insert t3\nselect * from t3;"1039} {0 {INSERT INTO t3 VALUES(1,NULL);1040INSERT INTO t3 VALUES(2,'');1041INSERT INTO t3 VALUES(3,1);1042INSERT INTO t3 VALUES(4,2.25);1043INSERT INTO t3 VALUES(5,'hello');1044INSERT INTO t3 VALUES(6,x'807f');}}1045 1046# Test the output of ".mode insert" with headers1047#1048do_test shell1-4.2.4 {1049 catchcmd test.db ".mode insert t3\n.headers on\nselect * from t3;"1050} {0 {INSERT INTO t3(x,y) VALUES(1,NULL);1051INSERT INTO t3(x,y) VALUES(2,'');1052INSERT INTO t3(x,y) VALUES(3,1);1053INSERT INTO t3(x,y) VALUES(4,2.25);1054INSERT INTO t3(x,y) VALUES(5,'hello');1055INSERT INTO t3(x,y) VALUES(6,x'807f');}}1056 1057# Test the output of ".mode tcl"1058#1059do_test shell1-4.3 {1060 db close1061 forcedelete test.db1062 sqlite3 db test.db1063 db eval {1064 PRAGMA encoding=UTF8;1065 CREATE TABLE t1(x);1066 INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f');1067 }1068 catchcmd test.db ".mode tcl\nselect * from t1;"1069} {0 {""1070""1071110722.251073"hello"1074"\200\177"}}1075 1076# Test the output of ".mode tcl" with multiple columns1077#1078do_test shell1-4.4 {1079 db eval {1080 CREATE TABLE t2(x,y);1081 INSERT INTO t2 VALUES(null, ''), (1, 2.25), ('hello', x'807f');1082 }1083 catchcmd test.db ".mode tcl\nselect * from t2;"1084} {0 {"" ""10851 2.251086"hello" "\200\177"}}1087 1088# Test the output of ".mode tcl" with ".nullvalue"1089#1090do_test shell1-4.5 {1091 catchcmd test.db ".mode tcl\n.nullvalue NULL\nselect * from t2;"1092} {0 {NULL ""10931 2.251094"hello" "\200\177"}}1095 1096# Test the output of ".mode tcl" with Tcl reserved characters1097#1098do_test shell1-4.6 {1099 db eval {1100 CREATE TABLE tcl1(x);1101 INSERT INTO tcl1 VALUES('"'), ('['), (']'), ('\{'), ('\}'), (';'), ('$');1102 }1103 foreach {x y} [catchcmd test.db ".mode tcl\nselect * from tcl1;"] break1104 list $x $y [llength $y]1105} {0 {"\""1106"["1107"]"1108"\\{"1109"\\}"1110";"1111"$"} 7}1112 1113# Test the output of ".mode quote"1114#1115do_test shell1-4.7 {1116 catchcmd test.db ".mode quote\nselect x'0123456789ABCDEF';"1117} {0 x'0123456789abcdef'}1118 1119# Test using arbitrary byte data with the shell via standard input/output.1120#1121do_test shell1-5.0 {1122 #1123 # NOTE: Skip NUL byte because it appears to be incompatible with command1124 # shell argument parsing.1125 #1126 for {set i 1} {$i < 256} {incr i} {1127 #1128 # NOTE: Due to how the Tcl [exec] command works (i.e. where it treats1129 # command channels opened for it as textual ones), the carriage1130 # return character (and on Windows, the end-of-file character)1131 # cannot be used here.1132 #1133 if {$i==0x0D || ($tcl_platform(platform) eq "windows" && $i==0x1A)} {1134 continue1135 }1136 # Tcl 8.7 maps 0x80 through 0x9f into valid UTF8. So skip those tests.1137 if {$i>=0x80} {1138 if {$i<=0x9F || $tcl_version>=9.0} continue1139 if {$tcl_platform(platform) eq "windows"} continue1140 }1141 if {$i>=0xE0 && $tcl_platform(os) eq "OpenBSD"} continue1142 if {$i>=0xE0 && $i<=0xEF && $tcl_platform(os) eq "Linux"} continue1143 set hex [format %02X $i]1144 set char [subst \\x$hex]; set oldChar $char1145 set escapes [list]1146 if {$tcl_platform(platform) eq "windows"} {1147 #1148 # NOTE: On Windows, we need to escape all the whitespace characters,1149 # the alarm (\a) character, and those with special meaning to1150 # the SQLite shell itself.1151 #1152 set escapes [list \1153 \a \\a \b \\b \t \\t \n \\n \v \\v \f \\f \r \\r \1154 " " "\" \"" \" \\\" \\ \\\\]1155 } else {1156 #1157 # NOTE: On Unix, we need to escape most of the whitespace characters1158 # and those with special meaning to the SQLite shell itself.1159 # The alarm (\a), backspace (\b), and carriage-return (\r)1160 # characters do not appear to require escaping on Unix. For1161 # the alarm and backspace characters, this is probably due to1162 # differences in the command shell. For the carriage-return,1163 # it is probably due to differences in how Tcl handles command1164 # channel end-of-line translations.1165 #1166 set escapes [list \1167 \t \\t \n \\n \v \\v \f \\f \1168 " " "\" \"" \" \\\" \\ \\\\]1169 }1170 set char [string map $escapes $char]1171 set x [catchcmdex test.db ".print \"$char\"\n"]1172 set code [lindex $x 0]1173 set res [lindex $x 1]1174 if {$code ne "0"} {1175 error "failed with error: $res"1176 }1177 if {$res ne "$oldChar\n"} {1178 if {[llength $res] > 0} {1179 set got [format %02X [scan $res %c]]1180 } else {1181 set got <empty>1182 }1183 error "failed with byte $hex mismatch, got $got"1184 }1185 }1186} {}1187 1188# These test cases do not work on MinGW1189if 0 {1190 1191# The string used here is the word "test" in Chinese.1192# In UTF-8, it is encoded as: \xE6\xB5\x8B\xE8\xAF\x951193set test \u6D4B\u8BD51194 1195do_test shell1-6.0 {1196 set fileName $test; append fileName .db1197 catch {forcedelete $fileName}1198 set x [catchcmdex $fileName "CREATE TABLE t1(x);\n.schema\n"]1199 set code [lindex $x 0]1200 set res [string trim [lindex $x 1]]