| blob | c4e2ceb88b01e254e1296fc3fda755f9e27a0093 |
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 #
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_find_cli]
27 db close
28 forcedelete test.db test.db-journal test.db-wal
29 sqlite3 db test.db
31 #----------------------------------------------------------------------------
32 # Test cases shell1-1.*: Basic command line option handling.
33 #
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 (1)}}
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}
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 (1)}}
83 # -echo print commands before execution
84 do_test shell1-1.4.1 {
85 catchcmd "-echo test.db" ""
86 } {0 {}}
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 {}}
96 # -bail stop after hitting an error
97 do_test shell1-1.6.1 {
98 catchcmd "-bail test.db" ""
99 } {0 {}}
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}
110 # -batch force batch I/O
111 do_test shell1-1.8.1 {
112 catchcmd "-batch test.db" ""
113 } {0 {}}
115 # -column set output mode to 'column'
116 do_test shell1-1.9.1 {
117 catchcmd "-column test.db" ""
118 } {0 {}}
120 # -csv set output mode to 'csv'
121 do_test shell1-1.10.1 {
122 catchcmd "-csv test.db" ""
123 } {0 {}}
125 # -html set output mode to HTML
126 do_test shell1-1.11.1 {
127 catchcmd "-html test.db" ""
128 } {0 {}}
130 # -line set output mode to 'line'
131 do_test shell1-1.12.1 {
132 catchcmd "-line test.db" ""
133 } {0 {}}
135 # -list set output mode to 'list'
136 do_test shell1-1.13.1 {
137 catchcmd "-list test.db" ""
138 } {0 {}}
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}
154 # -stats print memory stats before each finalize
155 do_test shell1-1.14b.1 {
156 catchcmd "-stats test.db" ""
157 } {0 {}}
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}
173 # -version show SQLite version
174 do_test shell1-1.16.1 {
175 set x [catchcmd "-version test.db" ""]
176 } {/3.[0-9.]+ 20\d\d-[01]\d-\d\d \d\d:\d\d:\d\d [0-9a-f]+/}
178 #----------------------------------------------------------------------------
179 # Test cases shell1-2.*: Basic "dot" command token parsing.
180 #
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}}
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 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 quote table tabs tcl}}
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 {}}
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 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 {}}
248 #----------------------------------------------------------------------------
249 # Test cases shell1-3.*: Basic test that "dot" command can be called.
250 #
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}}
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}}
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 }
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?}}
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}}
328 # .exit Exit this program
329 do_test shell1-3.6.1 {
330 catchcmd "test.db" ".exit"
331 } {0 {}}
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 {}}
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}}
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}}
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}
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".*./}
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?}}
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 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 {}}
470 # don't allow partial mode type matches
471 do_test shell1-3.13.12 {
472 catchcmd "test.db" ".mode l"
473 } {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}}
474 do_test shell1-3.13.13 {
475 catchcmd "test.db" ".mode li"
476 } {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}}
477 do_test shell1-3.13.14 {
478 catchcmd "test.db" ".mode lin"
479 } {0 {}}
481 # .nullvalue STRING Print STRING in place of NULL values
482 do_test shell1-3.14.1 {
483 catchcmd "test.db" ".nullvalue"
484 } {1 {Usage: .nullvalue STRING}}
485 do_test shell1-3.14.2 {
486 catchcmd "test.db" ".nullvalue FOO"
487 } {0 {}}
488 do_test shell1-3.14.3 {
489 # too many arguments
490 catchcmd "test.db" ".nullvalue FOO BAD"
491 } {1 {Usage: .nullvalue STRING}}
493 # .output FILENAME Send output to FILENAME
494 do_test shell1-3.15.1 {
495 catchcmd "test.db" ".output"
496 } {0 {}}
497 do_test shell1-3.15.2 {
498 catchcmd "test.db" ".output FOO"
499 } {0 {}}
500 do_test shell1-3.15.3 {
501 # too many arguments
502 catchcmd "test.db" ".output FOO BAD"
503 } {1 {ERROR: extra parameter: "BAD". Usage:
504 .output ?FILE? Send output to FILE or stdout if FILE is omitted
505 If FILE begins with '|' then open it as a pipe.
506 Options:
507 --bom Prefix output with a UTF8 byte-order mark
508 -e Send output to the system text editor
509 -x Send output as CSV to a spreadsheet
510 child process exited abnormally}}
512 # .output stdout Send output to the screen
513 do_test shell1-3.16.1 {
514 catchcmd "test.db" ".output stdout"
515 } {0 {}}
516 do_test shell1-3.16.2 {
517 # too many arguments
518 catchcmd "test.db" ".output stdout BAD"
519 } {1 {ERROR: extra parameter: "BAD". Usage:
520 .output ?FILE? Send output to FILE or stdout if FILE is omitted
521 If FILE begins with '|' then open it as a pipe.
522 Options:
523 --bom Prefix output with a UTF8 byte-order mark
524 -e Send output to the system text editor
525 -x Send output as CSV to a spreadsheet
526 child process exited abnormally}}
528 # .prompt MAIN CONTINUE Replace the standard prompts
529 do_test shell1-3.17.1 {
530 catchcmd "test.db" ".prompt"
531 } {0 {}}
532 do_test shell1-3.17.2 {
533 catchcmd "test.db" ".prompt FOO"
534 } {0 {}}
535 do_test shell1-3.17.3 {
536 catchcmd "test.db" ".prompt FOO BAR"
537 } {0 {}}
538 do_test shell1-3.17.4 {
539 # too many arguments
540 catchcmd "test.db" ".prompt FOO BAR BAD"
541 } {0 {}}
543 # .quit Exit this program
544 do_test shell1-3.18.1 {
545 catchcmd "test.db" ".quit"
546 } {0 {}}
547 do_test shell1-3.18.2 {
548 # too many arguments
549 catchcmd "test.db" ".quit BAD"
550 } {0 {}}
552 # .read FILENAME Execute SQL in FILENAME
553 do_test shell1-3.19.1 {
554 catchcmd "test.db" ".read"
555 } {1 {Usage: .read FILE}}
556 do_test shell1-3.19.2 {
557 forcedelete FOO
558 catchcmd "test.db" ".read FOO"
559 } {1 {Error: cannot open "FOO"}}
560 do_test shell1-3.19.3 {
561 # too many arguments
562 catchcmd "test.db" ".read FOO BAD"
563 } {1 {Usage: .read FILE}}
565 # .restore ?DB? FILE Restore content of DB (default "main") from FILE
566 do_test shell1-3.20.1 {
567 catchcmd "test.db" ".restore"
568 } {1 {Usage: .restore ?DB? FILE}}
569 do_test shell1-3.20.2 {
570 catchcmd "test.db" ".restore FOO"
571 } {0 {}}
572 do_test shell1-3.20.3 {
573 catchcmd "test.db" ".restore FOO BAR"
574 } {1 {Error: unknown database FOO}}
575 do_test shell1-3.20.4 {
576 # too many arguments
577 catchcmd "test.db" ".restore FOO BAR BAD"
578 } {1 {Usage: .restore ?DB? FILE}}
580 ifcapable vtab {
581 # .schema ?TABLE? Show the CREATE statements
582 # If TABLE specified, only show tables matching
583 # LIKE pattern TABLE.
584 do_test shell1-3.21.1 {
585 catchcmd "test.db" ".schema"
586 } {0 {}}
587 do_test shell1-3.21.2 {
588 catchcmd "test.db" ".schema FOO"
589 } {0 {}}
590 do_test shell1-3.21.3 {
591 # too many arguments
592 catchcmd "test.db" ".schema FOO BAD"
593 } {1 {Usage: .schema ?--indent? ?--nosys? ?LIKE-PATTERN?}}
595 do_test shell1-3.21.4 {
596 catchcmd "test.db" {
597 CREATE TABLE t1(x);
598 CREATE VIEW v2 AS SELECT x+1 AS y FROM t1;
599 CREATE VIEW v1 AS SELECT y+1 FROM v2;
600 }
601 catchcmd "test.db" ".schema"
602 } {0 {CREATE TABLE t1(x);
603 CREATE VIEW v2 AS SELECT x+1 AS y FROM t1
604 /* v2(y) */;
605 CREATE VIEW v1 AS SELECT y+1 FROM v2
606 /* v1("y+1") */;}}
607 db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;}
608 }
610 # .separator STRING Change column separator used by output and .import
611 do_test shell1-3.22.1 {
612 catchcmd "test.db" ".separator"
613 } {1 {Usage: .separator COL ?ROW?}}
614 do_test shell1-3.22.2 {
615 catchcmd "test.db" ".separator FOO"
616 } {0 {}}
617 do_test shell1-3.22.3 {
618 catchcmd "test.db" ".separator ABC XYZ"
619 } {0 {}}
620 do_test shell1-3.22.4 {
621 # too many arguments
622 catchcmd "test.db" ".separator FOO BAD BAD2"
623 } {1 {Usage: .separator COL ?ROW?}}
625 # .show Show the current values for various settings
626 do_test shell1-3.23.1 {
627 set res [catchcmd "test.db" ".show"]
628 list [regexp {echo:} $res] \
629 [regexp {explain:} $res] \
630 [regexp {headers:} $res] \
631 [regexp {mode:} $res] \
632 [regexp {nullvalue:} $res] \
633 [regexp {output:} $res] \
634 [regexp {colseparator:} $res] \
635 [regexp {rowseparator:} $res] \
636 [regexp {stats:} $res] \
637 [regexp {width:} $res]
638 } {1 1 1 1 1 1 1 1 1 1}
639 do_test shell1-3.23.2 {
640 # too many arguments
641 catchcmd "test.db" ".show BAD"
642 } {1 {Usage: .show}}
644 # .stats ON|OFF Turn stats on or off
645 #do_test shell1-3.23b.1 {
646 # catchcmd "test.db" ".stats"
647 #} {1 {Usage: .stats on|off|stmt|vmstep}}
648 do_test shell1-3.23b.2 {
649 catchcmd "test.db" ".stats ON"
650 } {0 {}}
651 do_test shell1-3.23b.3 {
652 catchcmd "test.db" ".stats OFF"
653 } {0 {}}
654 do_test shell1-3.23b.4 {
655 # too many arguments
656 catchcmd "test.db" ".stats OFF BAD"
657 } {1 {Usage: .stats ?on|off|stmt|vmstep?}}
659 # Ticket 7be932dfa60a8a6b3b26bcf7623ec46e0a403ddb 2018-06-07
660 # Adverse interaction between .stats and .eqp
661 #
662 do_test shell1-3.23b.5 {
663 catchcmd "test.db" [string map {"\n " "\n"} {
664 CREATE TEMP TABLE t1(x);
665 INSERT INTO t1 VALUES(1),(2);
666 .stats on
667 .eqp full
668 SELECT * FROM t1;
669 }]
670 } {/1\n2\n/}
672 # .tables ?TABLE? List names of tables
673 # If TABLE specified, only list tables matching
674 # LIKE pattern TABLE.
675 do_test shell1-3.24.1 {
676 catchcmd "test.db" ".tables"
677 } {0 {}}
678 do_test shell1-3.24.2 {
679 catchcmd "test.db" ".tables FOO"
680 } {0 {}}
681 do_test shell1-3.24.3 {
682 # too many arguments
683 catchcmd "test.db" ".tables FOO BAD"
684 } {0 {}}
686 # .timeout MS Try opening locked tables for MS milliseconds
687 do_test shell1-3.25.1 {
688 catchcmd "test.db" ".timeout"
689 } {0 {}}
690 do_test shell1-3.25.2 {
691 catchcmd "test.db" ".timeout zzz"
692 # this should be treated the same as a '0' timeout
693 } {0 {}}
694 do_test shell1-3.25.3 {
695 catchcmd "test.db" ".timeout 1"
696 } {0 {}}
697 do_test shell1-3.25.4 {
698 # too many arguments
699 catchcmd "test.db" ".timeout 1 BAD"
700 } {0 {}}
702 # .width NUM NUM ... Set column widths for "column" mode
703 do_test shell1-3.26.1 {
704 catchcmd "test.db" ".width"
705 } {0 {}}
706 do_test shell1-3.26.2 {
707 catchcmd "test.db" ".width xxx"
708 # this should be treated the same as a '0' width for col 1
709 } {0 {}}
710 do_test shell1-3.26.3 {
711 catchcmd "test.db" ".width xxx yyy"
712 # this should be treated the same as a '0' width for col 1 and 2
713 } {0 {}}
714 do_test shell1-3.26.4 {
715 catchcmd "test.db" ".width 1 1"
716 # this should be treated the same as a '1' width for col 1 and 2
717 } {0 {}}
718 do_test shell1-3.26.5 {
719 catchcmd "test.db" ".mode column\n.header off\n.width 10 -10\nSELECT 'abcdefg', 123456;"
720 # this should be treated the same as a '1' width for col 1 and 2
721 } {0 {abcdefg 123456}}
722 do_test shell1-3.26.6 {
723 catchcmd "test.db" ".mode column\n.header off\n.width -10 10\nSELECT 'abcdefg', 123456;"
724 # this should be treated the same as a '1' width for col 1 and 2
725 } {0 { abcdefg 123456 }}
728 # .timer ON|OFF Turn the CPU timer measurement on or off
729 do_test shell1-3.27.1 {
730 catchcmd "test.db" ".timer"
731 } {1 {Usage: .timer on|off}}
732 do_test shell1-3.27.2 {
733 catchcmd "test.db" ".timer ON"
734 } {0 {}}
735 do_test shell1-3.27.3 {
736 catchcmd "test.db" ".timer OFF"
737 } {0 {}}
738 do_test shell1-3.27.4 {
739 # too many arguments
740 catchcmd "test.db" ".timer OFF BAD"
741 } {1 {Usage: .timer on|off}}
743 do_test shell1-3-28.1 {
744 catchcmd test.db \
745 ".log stdout\nSELECT coalesce(sqlite_log(123,'hello'),'456');"
746 } "0 {(123) hello\n456}"
748 do_test shell1-3-29.1 {
749 catchcmd "test.db" ".print this is a test"
750 } {0 {this is a test}}
752 # dot-command argument quoting
753 do_test shell1-3-30.1 {
754 catchcmd {test.db} {.print "this\"is'a\055test" 'this\"is\\a\055test'}
755 } {0 {this"is'a-test this\"is\\a\055test}}
756 do_test shell1-3-31.1 {
757 catchcmd {test.db} {.print "this\nis\ta\\test" 'this\nis\ta\\test'}
758 } [list 0 "this\nis\ta\\test this\\nis\\ta\\\\test"]
761 # Test the output of the ".dump" command
762 #
763 do_test shell1-4.1 {
764 db close
765 forcedelete test.db
766 sqlite3 db test.db
767 db eval {
768 PRAGMA encoding=UTF16;
769 CREATE TABLE t1(x);
770 INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f');
771 CREATE TABLE t3(x,y);
772 INSERT INTO t3 VALUES(1,null), (2,''), (3,1),
773 (4,2.25), (5,'hello'), (6,x'807f');
774 }
775 catchcmd test.db {.dump}
776 } {0 {PRAGMA foreign_keys=OFF;
777 BEGIN TRANSACTION;
778 CREATE TABLE t1(x);
779 INSERT INTO t1 VALUES(NULL);
780 INSERT INTO t1 VALUES('');
781 INSERT INTO t1 VALUES(1);
782 INSERT INTO t1 VALUES(2.25);
783 INSERT INTO t1 VALUES('hello');
784 INSERT INTO t1 VALUES(X'807f');
785 CREATE TABLE t3(x,y);
786 INSERT INTO t3 VALUES(1,NULL);
787 INSERT INTO t3 VALUES(2,'');
788 INSERT INTO t3 VALUES(3,1);
789 INSERT INTO t3 VALUES(4,2.25);
790 INSERT INTO t3 VALUES(5,'hello');
791 INSERT INTO t3 VALUES(6,X'807f');
792 COMMIT;}}
795 ifcapable vtab {
797 # The --preserve-rowids option to .dump
798 #
799 do_test shell1-4.1.1 {
800 catchcmd test.db {.dump --preserve-rowids}
801 } {0 {PRAGMA foreign_keys=OFF;
802 BEGIN TRANSACTION;
803 CREATE TABLE t1(x);
804 INSERT INTO t1(rowid,x) VALUES(1,NULL);
805 INSERT INTO t1(rowid,x) VALUES(2,'');
806 INSERT INTO t1(rowid,x) VALUES(3,1);
807 INSERT INTO t1(rowid,x) VALUES(4,2.25);
808 INSERT INTO t1(rowid,x) VALUES(5,'hello');
809 INSERT INTO t1(rowid,x) VALUES(6,X'807f');
810 CREATE TABLE t3(x,y);
811 INSERT INTO t3(rowid,x,y) VALUES(1,1,NULL);
812 INSERT INTO t3(rowid,x,y) VALUES(2,2,'');
813 INSERT INTO t3(rowid,x,y) VALUES(3,3,1);
814 INSERT INTO t3(rowid,x,y) VALUES(4,4,2.25);
815 INSERT INTO t3(rowid,x,y) VALUES(5,5,'hello');
816 INSERT INTO t3(rowid,x,y) VALUES(6,6,X'807f');
817 COMMIT;}}
819 # If the table contains an INTEGER PRIMARY KEY, do not record a separate
820 # rowid column in the output.
821 #
822 do_test shell1-4.1.2 {
823 db close
824 forcedelete test2.db
825 sqlite3 db test2.db
826 db eval {
827 CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
828 INSERT INTO t1 VALUES(1,null), (2,''), (3,1),
829 (4,2.25), (5,'hello'), (6,x'807f');
830 }
831 catchcmd test2.db {.dump --preserve-rowids}
832 } {0 {PRAGMA foreign_keys=OFF;
833 BEGIN TRANSACTION;
834 CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
835 INSERT INTO t1 VALUES(1,NULL);
836 INSERT INTO t1 VALUES(2,'');
837 INSERT INTO t1 VALUES(3,1);
838 INSERT INTO t1 VALUES(4,2.25);
839 INSERT INTO t1 VALUES(5,'hello');
840 INSERT INTO t1 VALUES(6,X'807f');
841 COMMIT;}}
843 # Verify that the table named [table] is correctly quoted and that
844 # an INTEGER PRIMARY KEY DESC is not an alias for the rowid.
845 #
846 do_test shell1-4.1.3 {
847 db close
848 forcedelete test2.db
849 sqlite3 db test2.db
850 db eval {
851 CREATE TABLE [table](x INTEGER PRIMARY KEY DESC, y);
852 INSERT INTO [table] VALUES(1,null), (12,''), (23,1),
853 (34,2.25), (45,'hello'), (56,x'807f');
854 }
855 catchcmd test2.db {.dump --preserve-rowids}
856 } {0 {PRAGMA foreign_keys=OFF;
857 BEGIN TRANSACTION;
858 CREATE TABLE [table](x INTEGER PRIMARY KEY DESC, y);
859 INSERT INTO "table"(rowid,x,y) VALUES(1,1,NULL);
860 INSERT INTO "table"(rowid,x,y) VALUES(2,12,'');
861 INSERT INTO "table"(rowid,x,y) VALUES(3,23,1);
862 INSERT INTO "table"(rowid,x,y) VALUES(4,34,2.25);
863 INSERT INTO "table"(rowid,x,y) VALUES(5,45,'hello');
864 INSERT INTO "table"(rowid,x,y) VALUES(6,56,X'807f');
865 COMMIT;}}
867 # Do not record rowids for a WITHOUT ROWID table. Also check correct quoting
868 # of table names that contain odd characters.
869 #
870 do_test shell1-4.1.4 {
871 db close
872 forcedelete test2.db
873 sqlite3 db test2.db
874 db eval {
875 CREATE TABLE [ta<>ble](x INTEGER PRIMARY KEY, y) WITHOUT ROWID;
876 INSERT INTO [ta<>ble] VALUES(1,null), (12,''), (23,1),
877 (34,2.25), (45,'hello'), (56,x'807f');
878 }
879 catchcmd test2.db {.dump --preserve-rowids}
880 } {0 {PRAGMA foreign_keys=OFF;
881 BEGIN TRANSACTION;
882 CREATE TABLE [ta<>ble](x INTEGER PRIMARY KEY, y) WITHOUT ROWID;
883 INSERT INTO "ta<>ble" VALUES(1,NULL);
884 INSERT INTO "ta<>ble" VALUES(12,'');
885 INSERT INTO "ta<>ble" VALUES(23,1);
886 INSERT INTO "ta<>ble" VALUES(34,2.25);
887 INSERT INTO "ta<>ble" VALUES(45,'hello');
888 INSERT INTO "ta<>ble" VALUES(56,X'807f');
889 COMMIT;}}
891 # Do not record rowids if the rowid is inaccessible
892 #
893 do_test shell1-4.1.5 {
894 db close
895 forcedelete test2.db
896 sqlite3 db test2.db
897 db eval {
898 CREATE TABLE t1(_ROWID_,rowid,oid);
899 INSERT INTO t1 VALUES(1,null,'alpha'), (12,'',99), (23,1,x'b0b1b2');
900 }
901 catchcmd test2.db {.dump --preserve-rowids}
902 } {0 {PRAGMA foreign_keys=OFF;
903 BEGIN TRANSACTION;
904 CREATE TABLE t1(_ROWID_,rowid,oid);
905 INSERT INTO t1 VALUES(1,NULL,'alpha');
906 INSERT INTO t1 VALUES(12,'',99);
907 INSERT INTO t1 VALUES(23,1,X'b0b1b2');
908 COMMIT;}}
910 } else {
912 do_test shell1-4.1.6 {
913 db close
914 forcedelete test2.db
915 sqlite3 db test2.db
916 db eval {
917 CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
918 INSERT INTO t1 VALUES(1,null), (2,''), (3,1),
919 (4,2.25), (5,'hello'), (6,x'807f');
920 }
921 catchcmd test2.db {.dump --preserve-rowids}
922 } {1 {The --preserve-rowids option is not compatible with SQLITE_OMIT_VIRTUALTABLE}}
924 }
927 # Test the output of ".mode insert"
928 #
929 do_test shell1-4.2.1 {
930 catchcmd test.db ".mode insert t1\nselect * from t1;"
931 } {0 {INSERT INTO t1 VALUES(NULL);
932 INSERT INTO t1 VALUES('');
933 INSERT INTO t1 VALUES(1);
934 INSERT INTO t1 VALUES(2.25);
935 INSERT INTO t1 VALUES('hello');
936 INSERT INTO t1 VALUES(X'807f');}}
938 # Test the output of ".mode insert" with headers
939 #
940 do_test shell1-4.2.2 {
941 catchcmd test.db ".mode insert t1\n.headers on\nselect * from t1;"
942 } {0 {INSERT INTO t1(x) VALUES(NULL);
943 INSERT INTO t1(x) VALUES('');
944 INSERT INTO t1(x) VALUES(1);
945 INSERT INTO t1(x) VALUES(2.25);
946 INSERT INTO t1(x) VALUES('hello');
947 INSERT INTO t1(x) VALUES(X'807f');}}
949 # Test the output of ".mode insert"
950 #
951 do_test shell1-4.2.3 {
952 catchcmd test.db ".mode insert t3\nselect * from t3;"
953 } {0 {INSERT INTO t3 VALUES(1,NULL);
954 INSERT INTO t3 VALUES(2,'');
955 INSERT INTO t3 VALUES(3,1);
956 INSERT INTO t3 VALUES(4,2.25);
957 INSERT INTO t3 VALUES(5,'hello');
958 INSERT INTO t3 VALUES(6,X'807f');}}
960 # Test the output of ".mode insert" with headers
961 #
962 do_test shell1-4.2.4 {
963 catchcmd test.db ".mode insert t3\n.headers on\nselect * from t3;"
964 } {0 {INSERT INTO t3(x,y) VALUES(1,NULL);
965 INSERT INTO t3(x,y) VALUES(2,'');
966 INSERT INTO t3(x,y) VALUES(3,1);
967 INSERT INTO t3(x,y) VALUES(4,2.25);
968 INSERT INTO t3(x,y) VALUES(5,'hello');
969 INSERT INTO t3(x,y) VALUES(6,X'807f');}}
971 # Test the output of ".mode tcl"
972 #
973 do_test shell1-4.3 {
974 db close
975 forcedelete test.db
976 sqlite3 db test.db
977 db eval {
978 PRAGMA encoding=UTF8;
979 CREATE TABLE t1(x);
980 INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f');
981 }
982 catchcmd test.db ".mode tcl\nselect * from t1;"
983 } {0 {""
984 ""
985 "1"
986 "2.25"
987 "hello"
988 "\200\177"}}
990 # Test the output of ".mode tcl" with multiple columns
991 #
992 do_test shell1-4.4 {
993 db eval {
994 CREATE TABLE t2(x,y);
995 INSERT INTO t2 VALUES(null, ''), (1, 2.25), ('hello', x'807f');
996 }
997 catchcmd test.db ".mode tcl\nselect * from t2;"
998 } {0 {"" ""
999 "1" "2.25"
1000 "hello" "\200\177"}}
1002 # Test the output of ".mode tcl" with ".nullvalue"
1003 #
1004 do_test shell1-4.5 {
1005 catchcmd test.db ".mode tcl\n.nullvalue NULL\nselect * from t2;"
1006 } {0 {"NULL" ""
1007 "1" "2.25"
1008 "hello" "\200\177"}}
1010 # Test the output of ".mode tcl" with Tcl reserved characters
1011 #
1012 do_test shell1-4.6 {
1013 db eval {
1014 CREATE TABLE tcl1(x);
1015 INSERT INTO tcl1 VALUES('"'), ('['), (']'), ('\{'), ('\}'), (';'), ('$');
1016 }
1017 foreach {x y} [catchcmd test.db ".mode tcl\nselect * from tcl1;"] break
1018 list $x $y [llength $y]
1019 } {0 {"\""
1020 "["
1021 "]"
1022 "\\{"
1023 "\\}"
1024 ";"
1025 "$"} 7}
1027 # Test using arbitrary byte data with the shell via standard input/output.
1028 #
1029 do_test shell1-5.0 {
1030 #
1031 # NOTE: Skip NUL byte because it appears to be incompatible with command
1032 # shell argument parsing.
1033 #
1034 for {set i 1} {$i < 256} {incr i} {
1035 #
1036 # NOTE: Due to how the Tcl [exec] command works (i.e. where it treats
1037 # command channels opened for it as textual ones), the carriage
1038 # return character (and on Windows, the end-of-file character)
1039 # cannot be used here.
1040 #
1041 if {$i==0x0D || ($tcl_platform(platform)=="windows" && $i==0x1A)} {
1042 continue
1043 }
1044 # Tcl 8.7 maps 0x80 through 0x9f into valid UTF8. So skip those tests.
1045 if {$i>=0x80 && $i<=0x9f} continue
1046 if {$i>=0xE0 && $tcl_platform(os)=="OpenBSD"} continue
1047 if {$i>=0xE0 && $i<=0xEF && $tcl_platform(os)=="Linux"} continue
1048 set hex [format %02X $i]
1049 set char [subst \\x$hex]; set oldChar $char
1050 set escapes [list]
1051 if {$tcl_platform(platform)=="windows"} {
1052 #
1053 # NOTE: On Windows, we need to escape all the whitespace characters,
1054 # the alarm (\a) character, and those with special meaning to
1055 # the SQLite shell itself.
1056 #
1057 set escapes [list \
1058 \a \\a \b \\b \t \\t \n \\n \v \\v \f \\f \r \\r \
1059 " " "\" \"" \" \\\" ' \"'\" \\ \\\\]
1060 } else {
1061 #
1062 # NOTE: On Unix, we need to escape most of the whitespace characters
1063 # and those with special meaning to the SQLite shell itself.
1064 # The alarm (\a), backspace (\b), and carriage-return (\r)
1065 # characters do not appear to require escaping on Unix. For
1066 # the alarm and backspace characters, this is probably due to
1067 # differences in the command shell. For the carriage-return,
1068 # it is probably due to differences in how Tcl handles command
1069 # channel end-of-line translations.
1070 #
1071 set escapes [list \
1072 \t \\t \n \\n \v \\v \f \\f \
1073 " " "\" \"" \" \\\" ' \"'\" \\ \\\\]
1074 }
1075 set char [string map $escapes $char]
1076 set x [catchcmdex test.db ".print $char\n"]
1077 set code [lindex $x 0]
1078 set res [lindex $x 1]
1079 if {$code ne "0"} {
1080 error "failed with error: $res"
1081 }
1082 if {$res ne "$oldChar\n"} {
1083 if {[llength $res] > 0} {
1084 set got [format %02X [scan $res %c]]
1085 } else {
1086 set got <empty>
1087 }
1088 error "failed with byte $hex mismatch, got $got"
1089 }
1090 }
1091 } {}
1093 # These test cases do not work on MinGW
1094 if 0 {
1096 # The string used here is the word "test" in Chinese.
1097 # In UTF-8, it is encoded as: \xE6\xB5\x8B\xE8\xAF\x95
1098 set test \u6D4B\u8BD5
1100 do_test shell1-6.0 {
1101 set fileName $test; append fileName .db
1102 catch {forcedelete $fileName}
1103 set x [catchcmdex $fileName "CREATE TABLE t1(x);\n.schema\n"]
1104 set code [lindex $x 0]
1105 set res [string trim [lindex $x 1]]
1106 if {$code ne "0"} {
1107 error "failed with error: $res"
1108 }
1109 if {$res ne "CREATE TABLE t1(x);"} {
1110 error "failed with mismatch: $res"
1111 }
1112 if {![file exists $fileName]} {
1113 error "file \"$fileName\" (Unicode) does not exist"
1114 }
1115 forcedelete $fileName
1116 } {}
1118 do_test shell1-6.1 {
1119 catch {forcedelete test3.db}
1120 set x [catchcmdex test3.db \
1121 "CREATE TABLE [encoding convertto utf-8 $test](x);\n.schema\n"]
1122 set code [lindex $x 0]
1123 set res [string trim [lindex $x 1]]
1124 if {$code ne "0"} {
1125 error "failed with error: $res"
1126 }
1127 if {$res ne "CREATE TABLE ${test}(x);"} {
1128 error "failed with mismatch: $res"
1129 }
1130 forcedelete test3.db
1131 } {}
1132 }
1134 db close
1135 forcedelete test.db test.db-journal test.db-wal
1136 sqlite3 db test.db
1138 # The shell tool ".schema" command uses virtual table "pragma_database_list"
1139 #
1140 ifcapable vtab {
1142 do_test shell1-7.1.1 {
1143 db eval {
1144 CREATE TABLE Z (x TEXT PRIMARY KEY);
1145 CREATE TABLE _ (x TEXT PRIMARY KEY);
1146 CREATE TABLE YY (x TEXT PRIMARY KEY);
1147 CREATE TABLE __ (x TEXT PRIMARY KEY);
1148 CREATE TABLE WWW (x TEXT PRIMARY KEY);
1149 CREATE TABLE ___ (x TEXT PRIMARY KEY);
1150 }
1151 } {}
1152 do_test shell1-7.1.2 {
1153 catchcmd "test.db" ".schema _"
1154 } {0 {CREATE TABLE Z (x TEXT PRIMARY KEY);
1155 CREATE TABLE _ (x TEXT PRIMARY KEY);}}
1156 do_test shell1-7.1.3 {
1157 catchcmd "test.db" ".schema \\\\_"
1158 } {0 {CREATE TABLE _ (x TEXT PRIMARY KEY);}}
1159 do_test shell1-7.1.4 {
1160 catchcmd "test.db" ".schema __"
1161 } {0 {CREATE TABLE YY (x TEXT PRIMARY KEY);
1162 CREATE TABLE __ (x TEXT PRIMARY KEY);}}
1163 do_test shell1-7.1.5 {
1164 catchcmd "test.db" ".schema \\\\_\\\\_"
1165 } {0 {CREATE TABLE __ (x TEXT PRIMARY KEY);}}
1166 do_test shell1-7.1.6 {
1167 catchcmd "test.db" ".schema ___"
1168 } {0 {CREATE TABLE WWW (x TEXT PRIMARY KEY);
1169 CREATE TABLE ___ (x TEXT PRIMARY KEY);}}
1170 do_test shell1-7.1.7 {
1171 catchcmd "test.db" ".schema \\\\_\\\\_\\\\_"
1172 } {0 {CREATE TABLE ___ (x TEXT PRIMARY KEY);}}
1174 }
1176 # Test case for the ieee754 and decimal extensions in the shell.
1177 # See the "floatingpoint.html" file in the documentation for more
1178 # information.
1179 #
1180 do_test shell1-8.1 {
1181 catchcmd ":memory:" {
1182 -- The pow2 table will hold all the necessary powers of two.
1183 CREATE TABLE pow2(x INTEGER PRIMARY KEY, v TEXT);
1184 WITH RECURSIVE c(x,v) AS (
1185 VALUES(0,'1')
1186 UNION ALL
1187 SELECT x+1, decimal_mul(v,'2') FROM c WHERE x+1<=971
1188 ) INSERT INTO pow2(x,v) SELECT x, v FROM c;
1189 WITH RECURSIVE c(x,v) AS (
1190 VALUES(-1,'0.5')
1191 UNION ALL
1192 SELECT x-1, decimal_mul(v,'0.5') FROM c WHERE x-1>=-1075
1193 ) INSERT INTO pow2(x,v) SELECT x, v FROM c;
1195 -- This query finds the decimal representation of each value in the "c" table.
1196 WITH c(n) AS (VALUES(47.49))
1197 ----XXXXX----------- Replace with whatever you want
1198 SELECT decimal_mul(ieee754_mantissa(c.n),pow2.v)
1199 FROM pow2, c WHERE pow2.x=ieee754_exponent(c.n);
1200 }
1201 } {0 47.49000000000000198951966012828052043914794921875}
1202 do_test shell1-8.2 {
1203 catchcmd :memory: {
1204 .mode box
1205 SELECT ieee754(47.49) AS x;
1206 }
1207 } {0 {┌───────────────────────────────┐
1208 │ x │
1209 ├───────────────────────────────┤
1210 │ ieee754(6683623321994527,-47) │
1211 └───────────────────────────────┘}}
1212 do_test shell1-8.3 {
1213 catchcmd ":memory: --box" {
1214 select ieee754(6683623321994527,-47) as x;
1215 }
1216 } {0 {┌───────┐
1217 │ x │
1218 ├───────┤
1219 │ 47.49 │
1220 └───────┘}}
1221 do_test shell1-8.4 {
1222 catchcmd ":memory: --table" {SELECT ieee754_mantissa(47.49) AS M, ieee754_exponent(47.49) AS E;}
1223 } {0 {+------------------+-----+
1224 | M | E |
1225 +------------------+-----+
1226 | 6683623321994527 | -47 |
1227 +------------------+-----+}}
1229 #----------------------------------------------------------------------------
1230 # Test cases shell1-9.*: Basic test that "dot" commands and SQL intermix ok.
1231 #
1232 do_test shell1-9.1 {
1233 catchcmd :memory: {
1234 .mode csv
1235 /*
1236 x */ select 1,2; --x
1237 -- .nada
1238 ;
1239 .mode csv
1240 --x
1241 select 2,1; select 3,4;
1242 }
1243 } {0 {1,2
1244 2,1
1245 3,4}}
1247 finish_test