1# 2009 Nov 11 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# The focus of this file is testing the CLI shell tool. 13# 14# 15 16# Test plan: 17# 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# 22set testdir [file dirname $argv0] 23source $testdir/tester.tcl 24set CLI [test_find_cli] 25db close 26forcedelete test.db test.db-journal test.db-wal 27sqlite3 db test.db 28 29#---------------------------------------------------------------------------- 30# Test cases shell1-1.*: Basic command line option handling. 31# 32 33# invalid option 34do_test shell1-1.1.1 { 35 set res [catchcmd "-bad test.db" ""] 36 set rc [lindex $res 0] 37 list $rc \ 38 [regexp {Error: unknown option: -bad} $res] 39} {1 1} 40do_test shell1-1.1.1b { 41 set res [catchcmd "test.db -bad" ""] 42 set rc [lindex $res 0] 43 list $rc \ 44 [regexp {Error: unknown option: -bad} $res] 45} {1 1} 46# error on extra options 47do_test shell1-1.1.2 { 48 catchcmd "test.db \"select+3\" \"select+4\"" "" 49} {0 {3 504}} 51# error on extra options 52do_test shell1-1.1.3 { 53 catchcmd "test.db FOO test.db BAD" ".quit" 54} {1 {Error: near "FOO": syntax error}} 55 56# -help 57do_test shell1-1.2.1 { 58 set res [catchcmd "-help test.db" ""] 59 set rc [lindex $res 0] 60 list $rc \ 61 [regexp {Usage} $res] \ 62 [regexp {\-init} $res] \ 63 [regexp {\-version} $res] 64} {1 1 1 1} 65 66# -init filename read/process named file 67do_test shell1-1.3.1 { 68 catchcmd "-init FOO test.db" "" 69} {0 {}} 70do_test shell1-1.3.2 { 71 catchcmd "-init FOO test.db .quit BAD" "" 72} {0 {}} 73do_test shell1-1.3.3 { 74 catchcmd "-init FOO test.db BAD .quit" "" 75} {1 {Error: near "BAD": syntax error}} 76 77# -echo print commands before execution 78do_test shell1-1.4.1 { 79 catchcmd "-echo test.db" "" 80} {0 {}} 81 82# -[no]header turn headers on or off 83do_test shell1-1.5.1 { 84 catchcmd "-header test.db" "" 85} {0 {}} 86do_test shell1-1.5.2 { 87 catchcmd "-noheader test.db" "" 88} {0 {}} 89 90# -bail stop after hitting an error 91do_test shell1-1.6.1 { 92 catchcmd "-bail test.db" "" 93} {0 {}} 94 95# -interactive force interactive I/O 96do_test shell1-1.7.1 { 97 set res [catchcmd "-interactive test.db" ".quit"] 98 set rc [lindex $res 0] 99 list $rc \ 100 [regexp {SQLite version} $res] \ 101 [regexp {Enter ".help" for usage hints} $res] 102} {0 1 1} 103 104# -batch force batch I/O 105do_test shell1-1.8.1 { 106 catchcmd "-batch test.db" "" 107} {0 {}} 108 109# -column set output mode to 'column' 110do_test shell1-1.9.1 { 111 catchcmd "-column test.db" "" 112} {0 {}} 113 114# -csv set output mode to 'csv' 115do_test shell1-1.10.1 { 116 catchcmd "-csv test.db" "" 117} {0 {}} 118 119# -html set output mode to HTML 120do_test shell1-1.11.1 { 121 catchcmd "-html test.db" "" 122} {0 {}} 123 124# -line set output mode to 'line' 125do_test shell1-1.12.1 { 126 catchcmd "-line test.db" "" 127} {0 {}} 128 129# -list set output mode to 'list' 130do_test shell1-1.13.1 { 131 catchcmd "-list test.db" "" 132} {0 {}} 133 134# -separator 'x' set output field separator (|) 135do_test shell1-1.14.1 { 136 catchcmd "-separator 'x' test.db" "" 137} {0 {}} 138do_test shell1-1.14.2 { 139 catchcmd "-separator x test.db" "" 140} {0 {}} 141do_test shell1-1.14.3 { 142 set res [catchcmd "-separator" ""] 143 set rc [lindex $res 0] 144 list $rc \ 145 [regexp {Error: missing argument to -separator} $res] 146} {1 1} 147 148# -stats print memory stats before each finalize 149do_test shell1-1.14b.1 { 150 catchcmd "-stats test.db" "" 151} {0 {}} 152 153# -nullvalue 'text' set text string for NULL values 154do_test shell1-1.15.1 { 155 catchcmd "-nullvalue 'x' test.db" "" 156} {0 {}} 157do_test shell1-1.15.2 { 158 catchcmd "-nullvalue x test.db" "" 159} {0 {}} 160do_test shell1-1.15.3 { 161 set res [catchcmd "-nullvalue" ""] 162 set rc [lindex $res 0] 163 list $rc \ 164 [regexp {Error: missing argument to -nullvalue} $res] 165} {1 1} 166 167# -version show SQLite version 168do_test shell1-1.16.1 { 169 set x [catchcmd "-version test.db" ""] 170} {/3.[0-9.]+ 20\d\d-[01]\d-\d\d \d\d:\d\d:\d\d [0-9a-f]+/} 171 172#---------------------------------------------------------------------------- 173# Test cases shell1-2.*: Basic "dot" command token parsing. 174# 175 176# check first token handling 177do_test shell1-2.1.1 { 178 catchcmd "test.db" ".foo" 179} {1 {Error: unknown command or invalid arguments: "foo". Enter ".help" for help}} 180do_test shell1-2.1.2 { 181 catchcmd "test.db" ".\"foo OFF\"" 182} {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}} 183do_test shell1-2.1.3 { 184 catchcmd "test.db" ".\'foo OFF\'" 185} {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}} 186 187# unbalanced quotes 188do_test shell1-2.2.1 { 189 catchcmd "test.db" ".\"foo OFF" 190} {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}} 191do_test shell1-2.2.2 { 192 catchcmd "test.db" ".\'foo OFF" 193} {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}} 194do_test shell1-2.2.3 { 195 catchcmd "test.db" ".explain \"OFF" 196} {0 {}} 197do_test shell1-2.2.4 { 198 catchcmd "test.db" ".explain \'OFF" 199} {0 {}} 200do_test shell1-2.2.5 { 201 catchcmd "test.db" ".mode \"insert FOO" 202} {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}} 203do_test shell1-2.2.6 { 204 catchcmd "test.db" ".mode \'insert FOO" 205} {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}} 206 207# check multiple tokens, and quoted tokens 208do_test shell1-2.3.1 { 209 catchcmd "test.db" ".explain 1" 210} {0 {}} 211do_test shell1-2.3.2 { 212 catchcmd "test.db" ".explain on" 213} {0 {}} 214do_test shell1-2.3.3 { 215 catchcmd "test.db" ".explain \"1 2 3\"" 216} {1 {ERROR: Not a boolean value: "1 2 3". Assuming "no".}} 217do_test shell1-2.3.4 { 218 catchcmd "test.db" ".explain \"OFF\"" 219} {0 {}} 220do_test shell1-2.3.5 { 221 catchcmd "test.db" ".\'explain\' \'OFF\'" 222} {0 {}} 223do_test shell1-2.3.6 { 224 catchcmd "test.db" ".explain \'OFF\'" 225} {0 {}} 226do_test shell1-2.3.7 { 227 catchcmd "test.db" ".\'explain\' \'OFF\'" 228} {0 {}} 229 230# check quoted args are unquoted 231do_test shell1-2.4.1 { 232 catchcmd "test.db" ".mode FOO" 233} {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}} 234do_test shell1-2.4.2 { 235 catchcmd "test.db" ".mode csv" 236} {0 {}} 237do_test shell1-2.4.2 { 238 catchcmd "test.db" ".mode \"csv\"" 239} {0 {}} 240 241 242#---------------------------------------------------------------------------- 243# Test cases shell1-3.*: Basic test that "dot" command can be called. 244# 245 246# .backup ?DB? FILE Backup DB (default "main") to FILE 247do_test shell1-3.1.1 { 248 catchcmd "test.db" ".backup" 249} {1 {missing FILENAME argument on .backup}} 250do_test shell1-3.1.2 { 251 catchcmd "test.db" ".backup FOO" 252} {0 {}} 253do_test shell1-3.1.3 { 254 catchcmd "test.db" ".backup FOO BAR" 255} {1 {Error: unknown database FOO}} 256do_test shell1-3.1.4 { 257 # too many arguments 258 catchcmd "test.db" ".backup FOO BAR BAD" 259} {1 {Usage: .backup ?DB? ?OPTIONS? FILENAME}} 260 261# .bail ON|OFF Stop after hitting an error. Default OFF 262do_test shell1-3.2.1 { 263 catchcmd "test.db" ".bail" 264} {1 {Usage: .bail on|off}} 265do_test shell1-3.2.2 { 266 catchcmd "test.db" ".bail ON" 267} {0 {}} 268do_test shell1-3.2.3 { 269 catchcmd "test.db" ".bail OFF" 270} {0 {}} 271do_test shell1-3.2.4 { 272 # too many arguments 273 catchcmd "test.db" ".bail OFF BAD" 274} {1 {Usage: .bail on|off}} 275 276ifcapable vtab { 277# .databases List names and files of attached databases 278do_test shell1-3.3.1 { 279 catchcmd "-csv test.db" ".databases" 280} "/0.+main.+[string map {/ ".{1,2}"} [string range [get_pwd] 0 10]].*/" 281do_test shell1-3.3.2 { 282 # extra arguments ignored 283 catchcmd "test.db" ".databases BAD" 284} "/0.+main.+[string map {/ ".{1,2}"} [string range [get_pwd] 0 10]].*/" 285} 286 287# .dump ?TABLE? ... Dump the database in an SQL text format 288# If TABLE specified, only dump tables matching 289# LIKE pattern TABLE. 290do_test shell1-3.4.1 { 291 set res [catchcmd "test.db" ".dump"] 292 list [regexp {BEGIN TRANSACTION;} $res] \ 293 [regexp {COMMIT;} $res] 294} {1 1} 295do_test shell1-3.4.2 { 296 set res [catchcmd "test.db" ".dump FOO"] 297 list [regexp {BEGIN TRANSACTION;} $res] \ 298 [regexp {COMMIT;} $res] 299} {1 1} 300# The .dump command now accepts multiple arguments 301#do_test shell1-3.4.3 { 302# # too many arguments 303# catchcmd "test.db" ".dump FOO BAD" 304#} {1 {Usage: .dump ?--preserve-rowids? ?--newlines? ?LIKE-PATTERN?}} 305 306# .echo ON|OFF Turn command echo on or off 307do_test shell1-3.5.1 { 308 catchcmd "test.db" ".echo" 309} {1 {Usage: .echo on|off}} 310do_test shell1-3.5.2 { 311 catchcmd "test.db" ".echo ON" 312} {0 {}} 313do_test shell1-3.5.3 { 314 catchcmd "test.db" ".echo OFF" 315} {0 {}} 316do_test shell1-3.5.4 { 317 # too many arguments 318 catchcmd "test.db" ".echo OFF BAD" 319} {1 {Usage: .echo on|off}} 320 321# .exit Exit this program 322do_test shell1-3.6.1 { 323 catchcmd "test.db" ".exit" 324} {0 {}} 325 326# .explain ON|OFF Turn output mode suitable for EXPLAIN on or off. 327do_test shell1-3.7.1 { 328 catchcmd "test.db" ".explain" 329 # explain is the exception to the booleans. without an option, it turns it on. 330} {0 {}} 331do_test shell1-3.7.2 { 332 catchcmd "test.db" ".explain ON" 333} {0 {}} 334do_test shell1-3.7.3 { 335 catchcmd "test.db" ".explain OFF" 336} {0 {}} 337do_test shell1-3.7.4 { 338 # extra arguments ignored 339 catchcmd "test.db" ".explain OFF BAD" 340} {0 {}} 341 342 343# .header(s) ON|OFF Turn display of headers on or off 344do_test shell1-3.9.1 { 345 catchcmd "test.db" ".header" 346} {1 {Usage: .headers on|off}} 347do_test shell1-3.9.2 { 348 catchcmd "test.db" ".header ON" 349} {0 {}} 350do_test shell1-3.9.3 { 351 catchcmd "test.db" ".header OFF" 352} {0 {}} 353do_test shell1-3.9.4 { 354 # too many arguments 355 catchcmd "test.db" ".header OFF BAD" 356} {1 {Usage: .headers on|off}} 357 358do_test shell1-3.9.5 { 359 catchcmd "test.db" ".headers" 360} {1 {Usage: .headers on|off}} 361do_test shell1-3.9.6 { 362 catchcmd "test.db" ".headers ON" 363} {0 {}} 364do_test shell1-3.9.7 { 365 catchcmd "test.db" ".headers OFF" 366} {0 {}} 367do_test shell1-3.9.8 { 368 # too many arguments 369 catchcmd "test.db" ".headers OFF BAD" 370} {1 {Usage: .headers on|off}} 371 372# .help Show this message 373do_test shell1-3.10.1 { 374 set res [catchcmd "test.db" ".help"] 375 # look for a few of the possible help commands 376 list [regexp {.help} $res] \ 377 [regexp {.quit} $res] \ 378 [regexp {.show} $res] 379} {1 1 1} 380do_test shell1-3.10.2 { 381 # we allow .help to take extra args (it is help after all) 382 set res [catchcmd "test.db" ".help *"] 383 # look for a few of the possible help commands 384 list [regexp {.help} $res] \ 385 [regexp {.quit} $res] \ 386 [regexp {.show} $res] 387} {1 1 1} 388 389# .import FILE TABLE Import data from FILE into TABLE 390do_test shell1-3.11.1 { 391 catchcmd "test.db" ".import" 392} {/1 .ERROR: missing FILE argument.*/} 393do_test shell1-3.11.2 { 394 catchcmd "test.db" ".import FOO" 395} {/1 .ERROR: missing TABLE argument.*/} 396do_test shell1-3.11.3 { 397 # too many arguments 398 catchcmd "test.db" ".import FOO BAR BAD" 399} {/1 .ERROR: extra argument: "BAD".*./} 400 401# .indexes ?TABLE? Show names of all indexes 402# If TABLE specified, only show indexes for tables 403# matching LIKE pattern TABLE. 404do_test shell1-3.12.1 { 405 catchcmd "test.db" ".indexes" 406} {0 {}} 407do_test shell1-3.12.2 { 408 catchcmd "test.db" ".indexes FOO" 409} {0 {}} 410do_test shell1-3.12.2-legacy { 411 catchcmd "test.db" ".indices FOO" 412} {0 {}} 413do_test shell1-3.12.3 { 414 # too many arguments 415 catchcmd "test.db" ".indexes FOO BAD" 416} {1 {Usage: .indexes ?LIKE-PATTERN?}} 417 418# .mode MODE ?TABLE? Set output mode where MODE is one of: 419# ascii Columns/rows delimited by 0x1F and 0x1E 420# csv Comma-separated values 421# column Left-aligned columns. (See .width) 422# html HTML <table> code 423# insert SQL insert statements for TABLE 424# line One value per line 425# list Values delimited by .separator strings 426# tabs Tab-separated values 427# tcl TCL list elements 428do_test shell1-3.13.1 { 429 catchcmd "test.db" ".mode" 430} {0 {current output mode: list}} 431do_test shell1-3.13.2 { 432 catchcmd "test.db" ".mode FOO" 433} {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}} 434do_test shell1-3.13.3 { 435 catchcmd "test.db" ".mode csv" 436} {0 {}} 437do_test shell1-3.13.4 { 438 catchcmd "test.db" ".mode column" 439} {0 {}} 440do_test shell1-3.13.5 { 441 catchcmd "test.db" ".mode html" 442} {0 {}} 443do_test shell1-3.13.6 { 444 catchcmd "test.db" ".mode insert" 445} {0 {}} 446do_test shell1-3.13.7 { 447 catchcmd "test.db" ".mode line" 448} {0 {}} 449do_test shell1-3.13.8 { 450 catchcmd "test.db" ".mode list" 451} {0 {}} 452do_test shell1-3.13.9 { 453 catchcmd "test.db" ".mode tabs" 454} {0 {}} 455do_test shell1-3.13.10 { 456 catchcmd "test.db" ".mode tcl" 457} {0 {}} 458do_test shell1-3.13.11 { 459 # extra arguments ignored 460 catchcmd "test.db" ".mode tcl BAD" 461} {0 {}} 462 463# don't allow partial mode type matches 464do_test shell1-3.13.12 { 465 catchcmd "test.db" ".mode l" 466} {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}} 467do_test shell1-3.13.13 { 468 catchcmd "test.db" ".mode li" 469} {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}} 470do_test shell1-3.13.14 { 471 catchcmd "test.db" ".mode lin" 472} {0 {}} 473 474# .nullvalue STRING Print STRING in place of NULL values 475do_test shell1-3.14.1 { 476 catchcmd "test.db" ".nullvalue" 477} {1 {Usage: .nullvalue STRING}} 478do_test shell1-3.14.2 { 479 catchcmd "test.db" ".nullvalue FOO" 480} {0 {}} 481do_test shell1-3.14.3 { 482 # too many arguments 483 catchcmd "test.db" ".nullvalue FOO BAD" 484} {1 {Usage: .nullvalue STRING}} 485 486# .output FILENAME Send output to FILENAME 487do_test shell1-3.15.1 { 488 catchcmd "test.db" ".output" 489} {0 {}} 490do_test shell1-3.15.2 { 491 catchcmd "test.db" ".output FOO" 492} {0 {}} 493do_test shell1-3.15.3 { 494 # too many arguments 495 catchcmd "test.db" ".output FOO BAD" 496} {1 {ERROR: extra parameter: "BAD". Usage: 497.output ?FILE? Send output to FILE or stdout if FILE is omitted 498 If FILE begins with '|' then open it as a pipe. 499 Options: 500 --bom Prefix output with a UTF8 byte-order mark 501 -e Send output to the system text editor 502 -x Send output as CSV to a spreadsheet 503child process exited abnormally}} 504 505# .output stdout Send output to the screen 506do_test shell1-3.16.1 { 507 catchcmd "test.db" ".output stdout" 508} {0 {}} 509do_test shell1-3.16.2 { 510 # too many arguments 511 catchcmd "test.db" ".output stdout BAD" 512} {1 {ERROR: extra parameter: "BAD". Usage: 513.output ?FILE? Send output to FILE or stdout if FILE is omitted 514 If FILE begins with '|' then open it as a pipe. 515 Options: 516 --bom Prefix output with a UTF8 byte-order mark 517 -e Send output to the system text editor 518 -x Send output as CSV to a spreadsheet 519child process exited abnormally}} 520 521# .prompt MAIN CONTINUE Replace the standard prompts 522do_test shell1-3.17.1 { 523 catchcmd "test.db" ".prompt" 524} {0 {}} 525do_test shell1-3.17.2 { 526 catchcmd "test.db" ".prompt FOO" 527} {0 {}} 528do_test shell1-3.17.3 { 529 catchcmd "test.db" ".prompt FOO BAR" 530} {0 {}} 531do_test shell1-3.17.4 { 532 # too many arguments 533 catchcmd "test.db" ".prompt FOO BAR BAD" 534} {0 {}} 535 536# .quit Exit this program 537do_test shell1-3.18.1 { 538 catchcmd "test.db" ".quit" 539} {0 {}} 540do_test shell1-3.18.2 { 541 # too many arguments 542 catchcmd "test.db" ".quit BAD" 543} {0 {}} 544 545# .read FILENAME Execute SQL in FILENAME 546do_test shell1-3.19.1 { 547 catchcmd "test.db" ".read" 548} {1 {Usage: .read FILE}} 549do_test shell1-3.19.2 { 550 forcedelete FOO 551 catchcmd "test.db" ".read FOO" 552} {1 {Error: cannot open "FOO"}} 553do_test shell1-3.19.3 { 554 # too many arguments 555 catchcmd "test.db" ".read FOO BAD" 556} {1 {Usage: .read FILE}} 557 558# .restore ?DB? FILE Restore content of DB (default "main") from FILE 559do_test shell1-3.20.1 { 560 catchcmd "test.db" ".restore" 561} {1 {Usage: .restore ?DB? FILE}} 562do_test shell1-3.20.2 { 563 catchcmd "test.db" ".restore FOO" 564} {0 {}} 565do_test shell1-3.20.3 { 566 catchcmd "test.db" ".restore FOO BAR" 567} {1 {Error: unknown database FOO}} 568do_test shell1-3.20.4 { 569 # too many arguments 570 catchcmd "test.db" ".restore FOO BAR BAD" 571} {1 {Usage: .restore ?DB? FILE}} 572 573ifcapable vtab { 574# .schema ?TABLE? Show the CREATE statements 575# If TABLE specified, only show tables matching 576# LIKE pattern TABLE. 577do_test shell1-3.21.1 { 578 catchcmd "test.db" ".schema" 579} {0 {}} 580do_test shell1-3.21.2 { 581 catchcmd "test.db" ".schema FOO" 582} {0 {}} 583do_test shell1-3.21.3 { 584 # too many arguments 585 catchcmd "test.db" ".schema FOO BAD" 586} {1 {Usage: .schema ?--indent? ?LIKE-PATTERN?}} 587 588do_test shell1-3.21.4 { 589 catchcmd "test.db" { 590 CREATE TABLE t1(x); 591 CREATE VIEW v2 AS SELECT x+1 AS y FROM t1; 592 CREATE VIEW v1 AS SELECT y+1 FROM v2; 593 } 594 catchcmd "test.db" ".schema" 595} {0 {CREATE TABLE t1(x); 596CREATE VIEW v2 AS SELECT x+1 AS y FROM t1 597/* v2(y) */; 598CREATE VIEW v1 AS SELECT y+1 FROM v2 599/* v1("y+1") */;}} 600db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;} 601} 602 603# .separator STRING Change column separator used by output and .import 604do_test shell1-3.22.1 { 605 catchcmd "test.db" ".separator" 606} {1 {Usage: .separator COL ?ROW?}} 607do_test shell1-3.22.2 { 608 catchcmd "test.db" ".separator FOO" 609} {0 {}} 610do_test shell1-3.22.3 { 611 catchcmd "test.db" ".separator ABC XYZ" 612} {0 {}} 613do_test shell1-3.22.4 { 614 # too many arguments 615 catchcmd "test.db" ".separator FOO BAD BAD2" 616} {1 {Usage: .separator COL ?ROW?}} 617 618# .show Show the current values for various settings 619do_test shell1-3.23.1 { 620 set res [catchcmd "test.db" ".show"] 621 list [regexp {echo:} $res] \ 622 [regexp {explain:} $res] \ 623 [regexp {headers:} $res] \ 624 [regexp {mode:} $res] \ 625 [regexp {nullvalue:} $res] \ 626 [regexp {output:} $res] \ 627 [regexp {colseparator:} $res] \ 628 [regexp {rowseparator:} $res] \ 629 [regexp {stats:} $res] \ 630 [regexp {width:} $res] 631} {1 1 1 1 1 1 1 1 1 1} 632do_test shell1-3.23.2 { 633 # too many arguments 634 catchcmd "test.db" ".show BAD" 635} {1 {Usage: .show}} 636 637# .stats ON|OFF Turn stats on or off 638#do_test shell1-3.23b.1 { 639# catchcmd "test.db" ".stats" 640#} {1 {Usage: .stats on|off}} 641do_test shell1-3.23b.2 { 642 catchcmd "test.db" ".stats ON" 643} {0 {}} 644do_test shell1-3.23b.3 { 645 catchcmd "test.db" ".stats OFF" 646} {0 {}} 647do_test shell1-3.23b.4 { 648 # too many arguments 649 catchcmd "test.db" ".stats OFF BAD" 650} {1 {Usage: .stats ?on|off?}} 651 652# Ticket 7be932dfa60a8a6b3b26bcf7623ec46e0a403ddb 2018-06-07 653# Adverse interaction between .stats and .eqp 654# 655do_test shell1-3.23b.5 { 656 catchcmd "test.db" [string map {"\n " "\n"} { 657 CREATE TEMP TABLE t1(x); 658 INSERT INTO t1 VALUES(1),(2); 659 .stats on 660 .eqp full 661 SELECT * FROM t1; 662 }] 663} {/1\n2\n/} 664 665# .tables ?TABLE? List names of tables 666# If TABLE specified, only list tables matching 667# LIKE pattern TABLE. 668do_test shell1-3.24.1 { 669 catchcmd "test.db" ".tables" 670} {0 {}} 671do_test shell1-3.24.2 { 672 catchcmd "test.db" ".tables FOO" 673} {0 {}} 674do_test shell1-3.24.3 { 675 # too many arguments 676 catchcmd "test.db" ".tables FOO BAD" 677} {0 {}} 678 679# .timeout MS Try opening locked tables for MS milliseconds 680do_test shell1-3.25.1 { 681 catchcmd "test.db" ".timeout" 682} {0 {}} 683do_test shell1-3.25.2 { 684 catchcmd "test.db" ".timeout zzz" 685 # this should be treated the same as a '0' timeout 686} {0 {}} 687do_test shell1-3.25.3 { 688 catchcmd "test.db" ".timeout 1" 689} {0 {}} 690do_test shell1-3.25.4 { 691 # too many arguments 692 catchcmd "test.db" ".timeout 1 BAD" 693} {0 {}} 694 695# .width NUM NUM ... Set column widths for "column" mode 696do_test shell1-3.26.1 { 697 catchcmd "test.db" ".width" 698} {0 {}} 699do_test shell1-3.26.2 { 700 catchcmd "test.db" ".width xxx" 701 # this should be treated the same as a '0' width for col 1 702} {0 {}} 703do_test shell1-3.26.3 { 704 catchcmd "test.db" ".width xxx yyy" 705 # this should be treated the same as a '0' width for col 1 and 2 706} {0 {}} 707do_test shell1-3.26.4 { 708 catchcmd "test.db" ".width 1 1" 709 # this should be treated the same as a '1' width for col 1 and 2 710} {0 {}} 711do_test shell1-3.26.5 { 712 catchcmd "test.db" ".mode column\n.header off\n.width 10 -10\nSELECT 'abcdefg', 123456;" 713 # this should be treated the same as a '1' width for col 1 and 2 714} {0 {abcdefg 123456}} 715do_test shell1-3.26.6 { 716 catchcmd "test.db" ".mode column\n.header off\n.width -10 10\nSELECT 'abcdefg', 123456;" 717 # this should be treated the same as a '1' width for col 1 and 2 718} {0 { abcdefg 123456 }} 719 720 721# .timer ON|OFF Turn the CPU timer measurement on or off 722do_test shell1-3.27.1 { 723 catchcmd "test.db" ".timer" 724} {1 {Usage: .timer on|off}} 725do_test shell1-3.27.2 { 726 catchcmd "test.db" ".timer ON" 727} {0 {}} 728do_test shell1-3.27.3 { 729 catchcmd "test.db" ".timer OFF" 730} {0 {}} 731do_test shell1-3.27.4 { 732 # too many arguments 733 catchcmd "test.db" ".timer OFF BAD" 734} {1 {Usage: .timer on|off}} 735 736do_test shell1-3-28.1 { 737 catchcmd test.db \ 738 ".log stdout\nSELECT coalesce(sqlite_log(123,'hello'),'456');" 739} "0 {(123) hello\n456}" 740 741do_test shell1-3-29.1 { 742 catchcmd "test.db" ".print this is a test" 743} {0 {this is a test}} 744 745# dot-command argument quoting 746do_test shell1-3-30.1 { 747 catchcmd {test.db} {.print "this\"is'a\055test" 'this\"is\\a\055test'} 748} {0 {this"is'a-test this\"is\\a\055test}} 749do_test shell1-3-31.1 { 750 catchcmd {test.db} {.print "this\nis\ta\\test" 'this\nis\ta\\test'} 751} [list 0 "this\nis\ta\\test this\\nis\\ta\\\\test"] 752 753 754# Test the output of the ".dump" command 755# 756do_test shell1-4.1 { 757 db close 758 forcedelete test.db 759 sqlite3 db test.db 760 db eval { 761 PRAGMA encoding=UTF16; 762 CREATE TABLE t1(x); 763 INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f'); 764 CREATE TABLE t3(x,y); 765 INSERT INTO t3 VALUES(1,null), (2,''), (3,1), 766 (4,2.25), (5,'hello'), (6,x'807f'); 767 } 768 catchcmd test.db {.dump} 769} {0 {PRAGMA foreign_keys=OFF; 770BEGIN TRANSACTION; 771CREATE TABLE t1(x); 772INSERT INTO t1 VALUES(NULL); 773INSERT INTO t1 VALUES(''); 774INSERT INTO t1 VALUES(1); 775INSERT INTO t1 VALUES(2.25); 776INSERT INTO t1 VALUES('hello'); 777INSERT INTO t1 VALUES(X'807f'); 778CREATE TABLE t3(x,y); 779INSERT INTO t3 VALUES(1,NULL); 780INSERT INTO t3 VALUES(2,''); 781INSERT INTO t3 VALUES(3,1); 782INSERT INTO t3 VALUES(4,2.25); 783INSERT INTO t3 VALUES(5,'hello'); 784INSERT INTO t3 VALUES(6,X'807f'); 785COMMIT;}} 786 787 788ifcapable vtab { 789 790# The --preserve-rowids option to .dump 791# 792do_test shell1-4.1.1 { 793 catchcmd test.db {.dump --preserve-rowids} 794} {0 {PRAGMA foreign_keys=OFF; 795BEGIN TRANSACTION; 796CREATE TABLE t1(x); 797INSERT INTO t1(rowid,x) VALUES(1,NULL); 798INSERT INTO t1(rowid,x) VALUES(2,''); 799INSERT INTO t1(rowid,x) VALUES(3,1); 800INSERT INTO t1(rowid,x) VALUES(4,2.25); 801INSERT INTO t1(rowid,x) VALUES(5,'hello'); 802INSERT INTO t1(rowid,x) VALUES(6,X'807f'); 803CREATE TABLE t3(x,y); 804INSERT INTO t3(rowid,x,y) VALUES(1,1,NULL); 805INSERT INTO t3(rowid,x,y) VALUES(2,2,''); 806INSERT INTO t3(rowid,x,y) VALUES(3,3,1); 807INSERT INTO t3(rowid,x,y) VALUES(4,4,2.25); 808INSERT INTO t3(rowid,x,y) VALUES(5,5,'hello'); 809INSERT INTO t3(rowid,x,y) VALUES(6,6,X'807f'); 810COMMIT;}} 811 812# If the table contains an INTEGER PRIMARY KEY, do not record a separate 813# rowid column in the output. 814# 815do_test shell1-4.1.2 { 816 db close 817 forcedelete test2.db 818 sqlite3 db test2.db 819 db eval { 820 CREATE TABLE t1(x INTEGER PRIMARY KEY, y); 821 INSERT INTO t1 VALUES(1,null), (2,''), (3,1), 822 (4,2.25), (5,'hello'), (6,x'807f'); 823 } 824 catchcmd test2.db {.dump --preserve-rowids} 825} {0 {PRAGMA foreign_keys=OFF; 826BEGIN TRANSACTION; 827CREATE TABLE t1(x INTEGER PRIMARY KEY, y); 828INSERT INTO t1 VALUES(1,NULL); 829INSERT INTO t1 VALUES(2,''); 830INSERT INTO t1 VALUES(3,1); 831INSERT INTO t1 VALUES(4,2.25); 832INSERT INTO t1 VALUES(5,'hello'); 833INSERT INTO t1 VALUES(6,X'807f'); 834COMMIT;}} 835 836# Verify that the table named [table] is correctly quoted and that 837# an INTEGER PRIMARY KEY DESC is not an alias for the rowid. 838# 839do_test shell1-4.1.3 { 840 db close 841 forcedelete test2.db 842 sqlite3 db test2.db 843 db eval { 844 CREATE TABLE [table](x INTEGER PRIMARY KEY DESC, y); 845 INSERT INTO [table] VALUES(1,null), (12,''), (23,1), 846 (34,2.25), (45,'hello'), (56,x'807f'); 847 } 848 catchcmd test2.db {.dump --preserve-rowids} 849} {0 {PRAGMA foreign_keys=OFF; 850BEGIN TRANSACTION; 851CREATE TABLE [table](x INTEGER PRIMARY KEY DESC, y); 852INSERT INTO "table"(rowid,x,y) VALUES(1,1,NULL); 853INSERT INTO "table"(rowid,x,y) VALUES(2,12,''); 854INSERT INTO "table"(rowid,x,y) VALUES(3,23,1); 855INSERT INTO "table"(rowid,x,y) VALUES(4,34,2.25); 856INSERT INTO "table"(rowid,x,y) VALUES(5,45,'hello'); 857INSERT INTO "table"(rowid,x,y) VALUES(6,56,X'807f'); 858COMMIT;}} 859 860# Do not record rowids for a WITHOUT ROWID table. Also check correct quoting 861# of table names that contain odd characters. 862# 863do_test shell1-4.1.4 { 864 db close 865 forcedelete test2.db 866 sqlite3 db test2.db 867 db eval { 868 CREATE TABLE [ta<>ble](x INTEGER PRIMARY KEY, y) WITHOUT ROWID; 869 INSERT INTO [ta<>ble] VALUES(1,null), (12,''), (23,1), 870 (34,2.25), (45,'hello'), (56,x'807f'); 871 } 872 catchcmd test2.db {.dump --preserve-rowids} 873} {0 {PRAGMA foreign_keys=OFF; 874BEGIN TRANSACTION; 875CREATE TABLE [ta<>ble](x INTEGER PRIMARY KEY, y) WITHOUT ROWID; 876INSERT INTO "ta<>ble" VALUES(1,NULL); 877INSERT INTO "ta<>ble" VALUES(12,''); 878INSERT INTO "ta<>ble" VALUES(23,1); 879INSERT INTO "ta<>ble" VALUES(34,2.25); 880INSERT INTO "ta<>ble" VALUES(45,'hello'); 881INSERT INTO "ta<>ble" VALUES(56,X'807f'); 882COMMIT;}} 883 884# Do not record rowids if the rowid is inaccessible 885# 886do_test shell1-4.1.5 { 887 db close 888 forcedelete test2.db 889 sqlite3 db test2.db 890 db eval { 891 CREATE TABLE t1(_ROWID_,rowid,oid); 892 INSERT INTO t1 VALUES(1,null,'alpha'), (12,'',99), (23,1,x'b0b1b2'); 893 } 894 catchcmd test2.db {.dump --preserve-rowids} 895} {0 {PRAGMA foreign_keys=OFF; 896BEGIN TRANSACTION; 897CREATE TABLE t1(_ROWID_,rowid,oid); 898INSERT INTO t1 VALUES(1,NULL,'alpha'); 899INSERT INTO t1 VALUES(12,'',99); 900INSERT INTO t1 VALUES(23,1,X'b0b1b2'); 901COMMIT;}} 902 903} else { 904 905do_test shell1-4.1.6 { 906 db close 907 forcedelete test2.db 908 sqlite3 db test2.db 909 db eval { 910 CREATE TABLE t1(x INTEGER PRIMARY KEY, y); 911 INSERT INTO t1 VALUES(1,null), (2,''), (3,1), 912 (4,2.25), (5,'hello'), (6,x'807f'); 913 } 914 catchcmd test2.db {.dump --preserve-rowids} 915} {1 {The --preserve-rowids option is not compatible with SQLITE_OMIT_VIRTUALTABLE}} 916 917} 918 919 920# Test the output of ".mode insert" 921# 922do_test shell1-4.2.1 { 923 catchcmd test.db ".mode insert t1\nselect * from t1;" 924} {0 {INSERT INTO t1 VALUES(NULL); 925INSERT INTO t1 VALUES(''); 926INSERT INTO t1 VALUES(1); 927INSERT INTO t1 VALUES(2.25); 928INSERT INTO t1 VALUES('hello'); 929INSERT INTO t1 VALUES(X'807f');}} 930 931# Test the output of ".mode insert" with headers 932# 933do_test shell1-4.2.2 { 934 catchcmd test.db ".mode insert t1\n.headers on\nselect * from t1;" 935} {0 {INSERT INTO t1(x) VALUES(NULL); 936INSERT INTO t1(x) VALUES(''); 937INSERT INTO t1(x) VALUES(1); 938INSERT INTO t1(x) VALUES(2.25); 939INSERT INTO t1(x) VALUES('hello'); 940INSERT INTO t1(x) VALUES(X'807f');}} 941 942# Test the output of ".mode insert" 943# 944do_test shell1-4.2.3 { 945 catchcmd test.db ".mode insert t3\nselect * from t3;" 946} {0 {INSERT INTO t3 VALUES(1,NULL); 947INSERT INTO t3 VALUES(2,''); 948INSERT INTO t3 VALUES(3,1); 949INSERT INTO t3 VALUES(4,2.25); 950INSERT INTO t3 VALUES(5,'hello'); 951INSERT INTO t3 VALUES(6,X'807f');}} 952 953# Test the output of ".mode insert" with headers 954# 955do_test shell1-4.2.4 { 956 catchcmd test.db ".mode insert t3\n.headers on\nselect * from t3;" 957} {0 {INSERT INTO t3(x,y) VALUES(1,NULL); 958INSERT INTO t3(x,y) VALUES(2,''); 959INSERT INTO t3(x,y) VALUES(3,1); 960INSERT INTO t3(x,y) VALUES(4,2.25); 961INSERT INTO t3(x,y) VALUES(5,'hello'); 962INSERT INTO t3(x,y) VALUES(6,X'807f');}} 963 964# Test the output of ".mode tcl" 965# 966do_test shell1-4.3 { 967 db close 968 forcedelete test.db 969 sqlite3 db test.db 970 db eval { 971 PRAGMA encoding=UTF8; 972 CREATE TABLE t1(x); 973 INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f'); 974 } 975 catchcmd test.db ".mode tcl\nselect * from t1;" 976} {0 {"" 977"" 978"1" 979"2.25" 980"hello" 981"\200\177"}} 982 983# Test the output of ".mode tcl" with multiple columns 984# 985do_test shell1-4.4 { 986 db eval { 987 CREATE TABLE t2(x,y); 988 INSERT INTO t2 VALUES(null, ''), (1, 2.25), ('hello', x'807f'); 989 } 990 catchcmd test.db ".mode tcl\nselect * from t2;" 991} {0 {"" "" 992"1" "2.25" 993"hello" "\200\177"}} 994 995# Test the output of ".mode tcl" with ".nullvalue" 996# 997do_test shell1-4.5 { 998 catchcmd test.db ".mode tcl\n.nullvalue NULL\nselect * from t2;" 999} {0 {"NULL" "" 1000"1" "2.25" 1001"hello" "\200\177"}} 1002 1003# Test the output of ".mode tcl" with Tcl reserved characters 1004# 1005do_test shell1-4.6 { 1006 db eval { 1007 CREATE TABLE tcl1(x); 1008 INSERT INTO tcl1 VALUES('"'), ('['), (']'), ('\{'), ('\}'), (';'), ('$'); 1009 } 1010 foreach {x y} [catchcmd test.db ".mode tcl\nselect * from tcl1;"] break 1011 list $x $y [llength $y] 1012} {0 {"\"" 1013"[" 1014"]" 1015"\\{" 1016"\\}" 1017";" 1018"$"} 7} 1019 1020# Test using arbitrary byte data with the shell via standard input/output. 1021# 1022do_test shell1-5.0 { 1023 # 1024 # NOTE: Skip NUL byte because it appears to be incompatible with command 1025 # shell argument parsing. 1026 # 1027 for {set i 1} {$i < 256} {incr i} { 1028 # 1029 # NOTE: Due to how the Tcl [exec] command works (i.e. where it treats 1030 # command channels opened for it as textual ones), the carriage 1031 # return character (and on Windows, the end-of-file character) 1032 # cannot be used here. 1033 # 1034 if {$i==0x0D || ($tcl_platform(platform)=="windows" && $i==0x1A)} { 1035 continue 1036 } 1037 # Tcl 8.7 maps 0x80 through 0x9f into valid UTF8. So skip those tests. 1038 if {$i>=0x80 && $i<=0x9f} continue 1039 if {$i>=0xE0 && $tcl_platform(os)=="OpenBSD"} continue 1040 if {$i>=0xE0 && $i<=0xEF && $tcl_platform(os)=="Linux"} continue 1041 set hex [format %02X $i] 1042 set char [subst \\x$hex]; set oldChar $char 1043 set escapes [list] 1044 if {$tcl_platform(platform)=="windows"} { 1045 # 1046 # NOTE: On Windows, we need to escape all the whitespace characters, 1047 # the alarm (\a) character, and those with special meaning to 1048 # the SQLite shell itself. 1049 # 1050 set escapes [list \ 1051 \a \\a \b \\b \t \\t \n \\n \v \\v \f \\f \r \\r \ 1052 " " "\" \"" \" \\\" ' \"'\" \\ \\\\] 1053 } else { 1054 # 1055 # NOTE: On Unix, we need to escape most of the whitespace characters 1056 # and those with special meaning to the SQLite shell itself. 1057 # The alarm (\a), backspace (\b), and carriage-return (\r) 1058 # characters do not appear to require escaping on Unix. For 1059 # the alarm and backspace characters, this is probably due to 1060 # differences in the command shell. For the carriage-return, 1061 # it is probably due to differences in how Tcl handles command 1062 # channel end-of-line translations. 1063 # 1064 set escapes [list \ 1065 \t \\t \n \\n \v \\v \f \\f \ 1066 " " "\" \"" \" \\\" ' \"'\" \\ \\\\] 1067 } 1068 set char [string map $escapes $char] 1069 set x [catchcmdex test.db ".print $char\n"] 1070 set code [lindex $x 0] 1071 set res [lindex $x 1] 1072 if {$code ne "0"} { 1073 error "failed with error: $res" 1074 } 1075 if {$res ne "$oldChar\n"} { 1076 if {[llength $res] > 0} { 1077 set got [format %02X [scan $res %c]] 1078 } else { 1079 set got <empty> 1080 } 1081 error "failed with byte $hex mismatch, got $got" 1082 } 1083 } 1084} {} 1085 1086# These test cases do not work on MinGW 1087if 0 { 1088 1089# The string used here is the word "test" in Chinese. 1090# In UTF-8, it is encoded as: \xE6\xB5\x8B\xE8\xAF\x95 1091set test \u6D4B\u8BD5 1092 1093do_test shell1-6.0 { 1094 set fileName $test; append fileName .db 1095 catch {forcedelete $fileName} 1096 set x [catchcmdex $fileName "CREATE TABLE t1(x);\n.schema\n"] 1097 set code [lindex $x 0] 1098 set res [string trim [lindex $x 1]] 1099 if {$code ne "0"} { 1100 error "failed with error: $res" 1101 } 1102 if {$res ne "CREATE TABLE t1(x);"} { 1103 error "failed with mismatch: $res" 1104 } 1105 if {![file exists $fileName]} { 1106 error "file \"$fileName\" (Unicode) does not exist" 1107 } 1108 forcedelete $fileName 1109} {} 1110 1111do_test shell1-6.1 { 1112 catch {forcedelete test3.db} 1113 set x [catchcmdex test3.db \ 1114 "CREATE TABLE [encoding convertto utf-8 $test](x);\n.schema\n"] 1115 set code [lindex $x 0] 1116 set res [string trim [lindex $x 1]] 1117 if {$code ne "0"} { 1118 error "failed with error: $res" 1119 } 1120 if {$res ne "CREATE TABLE ${test}(x);"} { 1121 error "failed with mismatch: $res" 1122 } 1123 forcedelete test3.db 1124} {} 1125} 1126 1127db close 1128forcedelete test.db test.db-journal test.db-wal 1129sqlite3 db test.db 1130 1131# The shell tool ".schema" command uses virtual table "pragma_database_list" 1132# 1133ifcapable vtab { 1134 1135do_test shell1-7.1.1 { 1136 db eval { 1137 CREATE TABLE Z (x TEXT PRIMARY KEY); 1138 CREATE TABLE _ (x TEXT PRIMARY KEY); 1139 CREATE TABLE YY (x TEXT PRIMARY KEY); 1140 CREATE TABLE __ (x TEXT PRIMARY KEY); 1141 CREATE TABLE WWW (x TEXT PRIMARY KEY); 1142 CREATE TABLE ___ (x TEXT PRIMARY KEY); 1143 } 1144} {} 1145do_test shell1-7.1.2 { 1146 catchcmd "test.db" ".schema _" 1147} {0 {CREATE TABLE Z (x TEXT PRIMARY KEY); 1148CREATE TABLE _ (x TEXT PRIMARY KEY);}} 1149do_test shell1-7.1.3 { 1150 catchcmd "test.db" ".schema \\\\_" 1151} {0 {CREATE TABLE _ (x TEXT PRIMARY KEY);}} 1152do_test shell1-7.1.4 { 1153 catchcmd "test.db" ".schema __" 1154} {0 {CREATE TABLE YY (x TEXT PRIMARY KEY); 1155CREATE TABLE __ (x TEXT PRIMARY KEY);}} 1156do_test shell1-7.1.5 { 1157 catchcmd "test.db" ".schema \\\\_\\\\_" 1158} {0 {CREATE TABLE __ (x TEXT PRIMARY KEY);}} 1159do_test shell1-7.1.6 { 1160 catchcmd "test.db" ".schema ___" 1161} {0 {CREATE TABLE WWW (x TEXT PRIMARY KEY); 1162CREATE TABLE ___ (x TEXT PRIMARY KEY);}} 1163do_test shell1-7.1.7 { 1164 catchcmd "test.db" ".schema \\\\_\\\\_\\\\_" 1165} {0 {CREATE TABLE ___ (x TEXT PRIMARY KEY);}} 1166 1167} 1168 1169finish_test 1170