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 column csv html insert line list tabs tcl}} 203do_test shell1-2.2.6 { 204 catchcmd "test.db" ".mode \'insert FOO" 205} {1 {Error: mode should be one of: ascii column csv html insert line list 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 column csv html insert line list 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 {too many arguments to .backup}} 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 276# .databases List names and files of attached databases 277do_test shell1-3.3.1 { 278 catchcmd "-csv test.db" ".databases" 279} "/0.+main.+[string map {/ ".{1,2}"} [string range [get_pwd] 0 10]].*/" 280do_test shell1-3.3.2 { 281 # extra arguments ignored 282 catchcmd "test.db" ".databases BAD" 283} "/0.+main.+[string map {/ ".{1,2}"} [string range [get_pwd] 0 10]].*/" 284 285# .dump ?TABLE? ... Dump the database in an SQL text format 286# If TABLE specified, only dump tables matching 287# LIKE pattern TABLE. 288do_test shell1-3.4.1 { 289 set res [catchcmd "test.db" ".dump"] 290 list [regexp {BEGIN TRANSACTION;} $res] \ 291 [regexp {COMMIT;} $res] 292} {1 1} 293do_test shell1-3.4.2 { 294 set res [catchcmd "test.db" ".dump FOO"] 295 list [regexp {BEGIN TRANSACTION;} $res] \ 296 [regexp {COMMIT;} $res] 297} {1 1} 298do_test shell1-3.4.3 { 299 # too many arguments 300 catchcmd "test.db" ".dump FOO BAD" 301} {1 {Usage: .dump ?LIKE-PATTERN?}} 302 303# .echo ON|OFF Turn command echo on or off 304do_test shell1-3.5.1 { 305 catchcmd "test.db" ".echo" 306} {1 {Usage: .echo on|off}} 307do_test shell1-3.5.2 { 308 catchcmd "test.db" ".echo ON" 309} {0 {}} 310do_test shell1-3.5.3 { 311 catchcmd "test.db" ".echo OFF" 312} {0 {}} 313do_test shell1-3.5.4 { 314 # too many arguments 315 catchcmd "test.db" ".echo OFF BAD" 316} {1 {Usage: .echo on|off}} 317 318# .exit Exit this program 319do_test shell1-3.6.1 { 320 catchcmd "test.db" ".exit" 321} {0 {}} 322 323# .explain ON|OFF Turn output mode suitable for EXPLAIN on or off. 324do_test shell1-3.7.1 { 325 catchcmd "test.db" ".explain" 326 # explain is the exception to the booleans. without an option, it turns it on. 327} {0 {}} 328do_test shell1-3.7.2 { 329 catchcmd "test.db" ".explain ON" 330} {0 {}} 331do_test shell1-3.7.3 { 332 catchcmd "test.db" ".explain OFF" 333} {0 {}} 334do_test shell1-3.7.4 { 335 # extra arguments ignored 336 catchcmd "test.db" ".explain OFF BAD" 337} {0 {}} 338 339 340# .header(s) ON|OFF Turn display of headers on or off 341do_test shell1-3.9.1 { 342 catchcmd "test.db" ".header" 343} {1 {Usage: .headers on|off}} 344do_test shell1-3.9.2 { 345 catchcmd "test.db" ".header ON" 346} {0 {}} 347do_test shell1-3.9.3 { 348 catchcmd "test.db" ".header OFF" 349} {0 {}} 350do_test shell1-3.9.4 { 351 # too many arguments 352 catchcmd "test.db" ".header OFF BAD" 353} {1 {Usage: .headers on|off}} 354 355do_test shell1-3.9.5 { 356 catchcmd "test.db" ".headers" 357} {1 {Usage: .headers on|off}} 358do_test shell1-3.9.6 { 359 catchcmd "test.db" ".headers ON" 360} {0 {}} 361do_test shell1-3.9.7 { 362 catchcmd "test.db" ".headers OFF" 363} {0 {}} 364do_test shell1-3.9.8 { 365 # too many arguments 366 catchcmd "test.db" ".headers OFF BAD" 367} {1 {Usage: .headers on|off}} 368 369# .help Show this message 370do_test shell1-3.10.1 { 371 set res [catchcmd "test.db" ".help"] 372 # look for a few of the possible help commands 373 list [regexp {.help} $res] \ 374 [regexp {.quit} $res] \ 375 [regexp {.show} $res] 376} {1 1 1} 377do_test shell1-3.10.2 { 378 # we allow .help to take extra args (it is help after all) 379 set res [catchcmd "test.db" ".help BAD"] 380 # look for a few of the possible help commands 381 list [regexp {.help} $res] \ 382 [regexp {.quit} $res] \ 383 [regexp {.show} $res] 384} {1 1 1} 385 386# .import FILE TABLE Import data from FILE into TABLE 387do_test shell1-3.11.1 { 388 catchcmd "test.db" ".import" 389} {1 {Usage: .import FILE TABLE}} 390do_test shell1-3.11.2 { 391 catchcmd "test.db" ".import FOO" 392} {1 {Usage: .import FILE TABLE}} 393#do_test shell1-3.11.2 { 394# catchcmd "test.db" ".import FOO BAR" 395#} {1 {Error: no such table: BAR}} 396do_test shell1-3.11.3 { 397 # too many arguments 398 catchcmd "test.db" ".import FOO BAR BAD" 399} {1 {Usage: .import FILE TABLE}} 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} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}} 431do_test shell1-3.13.2 { 432 catchcmd "test.db" ".mode FOO" 433} {1 {Error: mode should be one of: ascii column csv html insert line list 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 column csv html insert line list tabs tcl}} 467do_test shell1-3.13.13 { 468 catchcmd "test.db" ".mode li" 469} {1 {Error: mode should be one of: ascii column csv html insert line list 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 {Usage: .output FILE}} 497 498# .output stdout Send output to the screen 499do_test shell1-3.16.1 { 500 catchcmd "test.db" ".output stdout" 501} {0 {}} 502do_test shell1-3.16.2 { 503 # too many arguments 504 catchcmd "test.db" ".output stdout BAD" 505} {1 {Usage: .output FILE}} 506 507# .prompt MAIN CONTINUE Replace the standard prompts 508do_test shell1-3.17.1 { 509 catchcmd "test.db" ".prompt" 510} {0 {}} 511do_test shell1-3.17.2 { 512 catchcmd "test.db" ".prompt FOO" 513} {0 {}} 514do_test shell1-3.17.3 { 515 catchcmd "test.db" ".prompt FOO BAR" 516} {0 {}} 517do_test shell1-3.17.4 { 518 # too many arguments 519 catchcmd "test.db" ".prompt FOO BAR BAD" 520} {0 {}} 521 522# .quit Exit this program 523do_test shell1-3.18.1 { 524 catchcmd "test.db" ".quit" 525} {0 {}} 526do_test shell1-3.18.2 { 527 # too many arguments 528 catchcmd "test.db" ".quit BAD" 529} {0 {}} 530 531# .read FILENAME Execute SQL in FILENAME 532do_test shell1-3.19.1 { 533 catchcmd "test.db" ".read" 534} {1 {Usage: .read FILE}} 535do_test shell1-3.19.2 { 536 forcedelete FOO 537 catchcmd "test.db" ".read FOO" 538} {1 {Error: cannot open "FOO"}} 539do_test shell1-3.19.3 { 540 # too many arguments 541 catchcmd "test.db" ".read FOO BAD" 542} {1 {Usage: .read FILE}} 543 544# .restore ?DB? FILE Restore content of DB (default "main") from FILE 545do_test shell1-3.20.1 { 546 catchcmd "test.db" ".restore" 547} {1 {Usage: .restore ?DB? FILE}} 548do_test shell1-3.20.2 { 549 catchcmd "test.db" ".restore FOO" 550} {0 {}} 551do_test shell1-3.20.3 { 552 catchcmd "test.db" ".restore FOO BAR" 553} {1 {Error: unknown database FOO}} 554do_test shell1-3.20.4 { 555 # too many arguments 556 catchcmd "test.db" ".restore FOO BAR BAD" 557} {1 {Usage: .restore ?DB? FILE}} 558 559# .schema ?TABLE? Show the CREATE statements 560# If TABLE specified, only show tables matching 561# LIKE pattern TABLE. 562do_test shell1-3.21.1 { 563 catchcmd "test.db" ".schema" 564} {0 {}} 565do_test shell1-3.21.2 { 566 catchcmd "test.db" ".schema FOO" 567} {0 {}} 568do_test shell1-3.21.3 { 569 # too many arguments 570 catchcmd "test.db" ".schema FOO BAD" 571} {1 {Usage: .schema ?--indent? ?LIKE-PATTERN?}} 572 573do_test shell1-3.21.4 { 574 catchcmd "test.db" { 575 CREATE TABLE t1(x); 576 CREATE VIEW v2 AS SELECT x+1 AS y FROM t1; 577 CREATE VIEW v1 AS SELECT y+1 FROM v2; 578 } 579 catchcmd "test.db" ".schema" 580} {0 {CREATE TABLE t1(x); 581CREATE VIEW v2 AS SELECT x+1 AS y FROM t1; 582CREATE VIEW v1 AS SELECT y+1 FROM v2;}} 583db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;} 584 585# .separator STRING Change column separator used by output and .import 586do_test shell1-3.22.1 { 587 catchcmd "test.db" ".separator" 588} {1 {Usage: .separator COL ?ROW?}} 589do_test shell1-3.22.2 { 590 catchcmd "test.db" ".separator FOO" 591} {0 {}} 592do_test shell1-3.22.3 { 593 catchcmd "test.db" ".separator ABC XYZ" 594} {0 {}} 595do_test shell1-3.22.4 { 596 # too many arguments 597 catchcmd "test.db" ".separator FOO BAD BAD2" 598} {1 {Usage: .separator COL ?ROW?}} 599 600# .show Show the current values for various settings 601do_test shell1-3.23.1 { 602 set res [catchcmd "test.db" ".show"] 603 list [regexp {echo:} $res] \ 604 [regexp {explain:} $res] \ 605 [regexp {headers:} $res] \ 606 [regexp {mode:} $res] \ 607 [regexp {nullvalue:} $res] \ 608 [regexp {output:} $res] \ 609 [regexp {colseparator:} $res] \ 610 [regexp {rowseparator:} $res] \ 611 [regexp {stats:} $res] \ 612 [regexp {width:} $res] 613} {1 1 1 1 1 1 1 1 1 1} 614do_test shell1-3.23.2 { 615 # too many arguments 616 catchcmd "test.db" ".show BAD" 617} {1 {Usage: .show}} 618 619# .stats ON|OFF Turn stats on or off 620#do_test shell1-3.23b.1 { 621# catchcmd "test.db" ".stats" 622#} {1 {Usage: .stats on|off}} 623do_test shell1-3.23b.2 { 624 catchcmd "test.db" ".stats ON" 625} {0 {}} 626do_test shell1-3.23b.3 { 627 catchcmd "test.db" ".stats OFF" 628} {0 {}} 629do_test shell1-3.23b.4 { 630 # too many arguments 631 catchcmd "test.db" ".stats OFF BAD" 632} {1 {Usage: .stats ?on|off?}} 633 634# .tables ?TABLE? List names of tables 635# If TABLE specified, only list tables matching 636# LIKE pattern TABLE. 637do_test shell1-3.24.1 { 638 catchcmd "test.db" ".tables" 639} {0 {}} 640do_test shell1-3.24.2 { 641 catchcmd "test.db" ".tables FOO" 642} {0 {}} 643do_test shell1-3.24.3 { 644 # too many arguments 645 catchcmd "test.db" ".tables FOO BAD" 646} {0 {}} 647 648# .timeout MS Try opening locked tables for MS milliseconds 649do_test shell1-3.25.1 { 650 catchcmd "test.db" ".timeout" 651} {0 {}} 652do_test shell1-3.25.2 { 653 catchcmd "test.db" ".timeout zzz" 654 # this should be treated the same as a '0' timeout 655} {0 {}} 656do_test shell1-3.25.3 { 657 catchcmd "test.db" ".timeout 1" 658} {0 {}} 659do_test shell1-3.25.4 { 660 # too many arguments 661 catchcmd "test.db" ".timeout 1 BAD" 662} {0 {}} 663 664# .width NUM NUM ... Set column widths for "column" mode 665do_test shell1-3.26.1 { 666 catchcmd "test.db" ".width" 667} {0 {}} 668do_test shell1-3.26.2 { 669 catchcmd "test.db" ".width xxx" 670 # this should be treated the same as a '0' width for col 1 671} {0 {}} 672do_test shell1-3.26.3 { 673 catchcmd "test.db" ".width xxx yyy" 674 # this should be treated the same as a '0' width for col 1 and 2 675} {0 {}} 676do_test shell1-3.26.4 { 677 catchcmd "test.db" ".width 1 1" 678 # this should be treated the same as a '1' width for col 1 and 2 679} {0 {}} 680do_test shell1-3.26.5 { 681 catchcmd "test.db" ".mode column\n.width 10 -10\nSELECT 'abcdefg', 123456;" 682 # this should be treated the same as a '1' width for col 1 and 2 683} {0 {abcdefg 123456}} 684do_test shell1-3.26.6 { 685 catchcmd "test.db" ".mode column\n.width -10 10\nSELECT 'abcdefg', 123456;" 686 # this should be treated the same as a '1' width for col 1 and 2 687} {0 { abcdefg 123456 }} 688 689 690# .timer ON|OFF Turn the CPU timer measurement on or off 691do_test shell1-3.27.1 { 692 catchcmd "test.db" ".timer" 693} {1 {Usage: .timer on|off}} 694do_test shell1-3.27.2 { 695 catchcmd "test.db" ".timer ON" 696} {0 {}} 697do_test shell1-3.27.3 { 698 catchcmd "test.db" ".timer OFF" 699} {0 {}} 700do_test shell1-3.27.4 { 701 # too many arguments 702 catchcmd "test.db" ".timer OFF BAD" 703} {1 {Usage: .timer on|off}} 704 705do_test shell1-3-28.1 { 706 catchcmd test.db \ 707 ".log stdout\nSELECT coalesce(sqlite_log(123,'hello'),'456');" 708} "0 {(123) hello\n456}" 709 710do_test shell1-3-29.1 { 711 catchcmd "test.db" ".print this is a test" 712} {0 {this is a test}} 713 714# dot-command argument quoting 715do_test shell1-3-30.1 { 716 catchcmd {test.db} {.print "this\"is'a\055test" 'this\"is\\a\055test'} 717} {0 {this"is'a-test this\"is\\a\055test}} 718do_test shell1-3-31.1 { 719 catchcmd {test.db} {.print "this\nis\ta\\test" 'this\nis\ta\\test'} 720} [list 0 "this\nis\ta\\test this\\nis\\ta\\\\test"] 721 722 723# Test the output of the ".dump" command 724# 725do_test shell1-4.1 { 726 db close 727 forcedelete test.db 728 sqlite3 db test.db 729 db eval { 730 PRAGMA encoding=UTF16; 731 CREATE TABLE t1(x); 732 INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f'); 733 CREATE TABLE t3(x,y); 734 INSERT INTO t3 VALUES(1,null), (2,''), (3,1), 735 (4,2.25), (5,'hello'), (6,x'807f'); 736 } 737 catchcmd test.db {.dump} 738} {0 {PRAGMA foreign_keys=OFF; 739BEGIN TRANSACTION; 740CREATE TABLE t1(x); 741INSERT INTO "t1" VALUES(NULL); 742INSERT INTO "t1" VALUES(''); 743INSERT INTO "t1" VALUES(1); 744INSERT INTO "t1" VALUES(2.25); 745INSERT INTO "t1" VALUES('hello'); 746INSERT INTO "t1" VALUES(X'807F'); 747CREATE TABLE t3(x,y); 748INSERT INTO "t3" VALUES(1,NULL); 749INSERT INTO "t3" VALUES(2,''); 750INSERT INTO "t3" VALUES(3,1); 751INSERT INTO "t3" VALUES(4,2.25); 752INSERT INTO "t3" VALUES(5,'hello'); 753INSERT INTO "t3" VALUES(6,X'807F'); 754COMMIT;}} 755 756# Test the output of ".mode insert" 757# 758do_test shell1-4.2.1 { 759 catchcmd test.db ".mode insert t1\nselect * from t1;" 760} {0 {INSERT INTO t1 VALUES(NULL); 761INSERT INTO t1 VALUES(''); 762INSERT INTO t1 VALUES(1); 763INSERT INTO t1 VALUES(2.25); 764INSERT INTO t1 VALUES('hello'); 765INSERT INTO t1 VALUES(X'807f');}} 766 767# Test the output of ".mode insert" with headers 768# 769do_test shell1-4.2.2 { 770 catchcmd test.db ".mode insert t1\n.headers on\nselect * from t1;" 771} {0 {INSERT INTO t1(x) VALUES(NULL); 772INSERT INTO t1(x) VALUES(''); 773INSERT INTO t1(x) VALUES(1); 774INSERT INTO t1(x) VALUES(2.25); 775INSERT INTO t1(x) VALUES('hello'); 776INSERT INTO t1(x) VALUES(X'807f');}} 777 778# Test the output of ".mode insert" 779# 780do_test shell1-4.2.3 { 781 catchcmd test.db ".mode insert t3\nselect * from t3;" 782} {0 {INSERT INTO t3 VALUES(1,NULL); 783INSERT INTO t3 VALUES(2,''); 784INSERT INTO t3 VALUES(3,1); 785INSERT INTO t3 VALUES(4,2.25); 786INSERT INTO t3 VALUES(5,'hello'); 787INSERT INTO t3 VALUES(6,X'807f');}} 788 789# Test the output of ".mode insert" with headers 790# 791do_test shell1-4.2.4 { 792 catchcmd test.db ".mode insert t3\n.headers on\nselect * from t3;" 793} {0 {INSERT INTO t3(x,y) VALUES(1,NULL); 794INSERT INTO t3(x,y) VALUES(2,''); 795INSERT INTO t3(x,y) VALUES(3,1); 796INSERT INTO t3(x,y) VALUES(4,2.25); 797INSERT INTO t3(x,y) VALUES(5,'hello'); 798INSERT INTO t3(x,y) VALUES(6,X'807f');}} 799 800# Test the output of ".mode tcl" 801# 802do_test shell1-4.3 { 803 db close 804 forcedelete test.db 805 sqlite3 db test.db 806 db eval { 807 PRAGMA encoding=UTF8; 808 CREATE TABLE t1(x); 809 INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f'); 810 } 811 catchcmd test.db ".mode tcl\nselect * from t1;" 812} {0 {"" 813"" 814"1" 815"2.25" 816"hello" 817"\200\177"}} 818 819# Test the output of ".mode tcl" with multiple columns 820# 821do_test shell1-4.4 { 822 db eval { 823 CREATE TABLE t2(x,y); 824 INSERT INTO t2 VALUES(null, ''), (1, 2.25), ('hello', x'807f'); 825 } 826 catchcmd test.db ".mode tcl\nselect * from t2;" 827} {0 {"" "" 828"1" "2.25" 829"hello" "\200\177"}} 830 831# Test the output of ".mode tcl" with ".nullvalue" 832# 833do_test shell1-4.5 { 834 catchcmd test.db ".mode tcl\n.nullvalue NULL\nselect * from t2;" 835} {0 {"NULL" "" 836"1" "2.25" 837"hello" "\200\177"}} 838 839# Test the output of ".mode tcl" with Tcl reserved characters 840# 841do_test shell1-4.6 { 842 db eval { 843 CREATE TABLE tcl1(x); 844 INSERT INTO tcl1 VALUES('"'), ('['), (']'), ('\{'), ('\}'), (';'), ('$'); 845 } 846 foreach {x y} [catchcmd test.db ".mode tcl\nselect * from tcl1;"] break 847 list $x $y [llength $y] 848} {0 {"\"" 849"[" 850"]" 851"\\{" 852"\\}" 853";" 854"$"} 7} 855 856# Test using arbitrary byte data with the shell via standard input/output. 857# 858do_test shell1-5.0 { 859 # 860 # NOTE: Skip NUL byte because it appears to be incompatible with command 861 # shell argument parsing. 862 # 863 for {set i 1} {$i < 256} {incr i} { 864 # 865 # NOTE: Due to how the Tcl [exec] command works (i.e. where it treats 866 # command channels opened for it as textual ones), the carriage 867 # return character (and on Windows, the end-of-file character) 868 # cannot be used here. 869 # 870 if {$i==0x0D || ($tcl_platform(platform)=="windows" && $i==0x1A)} { 871 continue 872 } 873 if {$i>=0xE0 && $tcl_platform(os)=="OpenBSD"} continue 874 if {$i>=0xE0 && $i<=0xEF && $tcl_platform(os)=="Linux"} continue 875 set hex [format %02X $i] 876 set char [subst \\x$hex]; set oldChar $char 877 set escapes [list] 878 if {$tcl_platform(platform)=="windows"} { 879 # 880 # NOTE: On Windows, we need to escape all the whitespace characters, 881 # the alarm (\a) character, and those with special meaning to 882 # the SQLite shell itself. 883 # 884 set escapes [list \ 885 \a \\a \b \\b \t \\t \n \\n \v \\v \f \\f \r \\r \ 886 " " "\" \"" \" \\\" ' \"'\" \\ \\\\] 887 } else { 888 # 889 # NOTE: On Unix, we need to escape most of the whitespace characters 890 # and those with special meaning to the SQLite shell itself. 891 # The alarm (\a), backspace (\b), and carriage-return (\r) 892 # characters do not appear to require escaping on Unix. For 893 # the alarm and backspace characters, this is probably due to 894 # differences in the command shell. For the carriage-return, 895 # it is probably due to differences in how Tcl handles command 896 # channel end-of-line translations. 897 # 898 set escapes [list \ 899 \t \\t \n \\n \v \\v \f \\f \ 900 " " "\" \"" \" \\\" ' \"'\" \\ \\\\] 901 } 902 set char [string map $escapes $char] 903 set x [catchcmdex test.db ".print $char\n"] 904 set code [lindex $x 0] 905 set res [lindex $x 1] 906 if {$code ne "0"} { 907 error "failed with error: $res" 908 } 909 if {$res ne "$oldChar\n"} { 910 if {[llength $res] > 0} { 911 set got [format %02X [scan $res %c]] 912 } else { 913 set got <empty> 914 } 915 error "failed with byte $hex mismatch, got $got" 916 } 917 } 918} {} 919 920# These test cases do not work on MinGW 921if 0 { 922 923# The string used here is the word "test" in Chinese. 924# In UTF-8, it is encoded as: \xE6\xB5\x8B\xE8\xAF\x95 925set test \u6D4B\u8BD5 926 927do_test shell1-6.0 { 928 set fileName $test; append fileName .db 929 catch {forcedelete $fileName} 930 set x [catchcmdex $fileName "CREATE TABLE t1(x);\n.schema\n"] 931 set code [lindex $x 0] 932 set res [string trim [lindex $x 1]] 933 if {$code ne "0"} { 934 error "failed with error: $res" 935 } 936 if {$res ne "CREATE TABLE t1(x);"} { 937 error "failed with mismatch: $res" 938 } 939 if {![file exists $fileName]} { 940 error "file \"$fileName\" (Unicode) does not exist" 941 } 942 forcedelete $fileName 943} {} 944 945do_test shell1-6.1 { 946 catch {forcedelete test3.db} 947 set x [catchcmdex test3.db \ 948 "CREATE TABLE [encoding convertto utf-8 $test](x);\n.schema\n"] 949 set code [lindex $x 0] 950 set res [string trim [lindex $x 1]] 951 if {$code ne "0"} { 952 error "failed with error: $res" 953 } 954 if {$res ne "CREATE TABLE ${test}(x);"} { 955 error "failed with mismatch: $res" 956 } 957 forcedelete test3.db 958} {} 959} 960 961finish_test 962