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