AryaWu/sqlite
0
1# 2009 Nov 112#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11# TESTRUNNER: shell12#13# The focus of this file is testing the CLI shell tool.14#15# $Id: shell2.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $16#17 18# Test plan:19#20# shell2-1.*: Misc. test of various tickets and reported errors.21#22set testdir [file dirname $argv0]23source $testdir/tester.tcl24set CLI [test_find_cli]25db close26forcedelete test.db test.db-journal test.db-wal27sqlite3 db test.db28 29 30#----------------------------------------------------------------------------31# shell2-1.*: Misc. test of various tickets and reported errors.32#33 34# Batch mode not creating databases. 35# Reported on mailing list by Ken Zalewski.36# Ticket [aeff892c57].37do_test shell2-1.1.1 {38 forcedelete foo.db39 set rc [ catchcmd "-batch foo.db" "CREATE TABLE t1(a);" ]40 set fexist [file exist foo.db]41 list $rc $fexist42} {{0 {}} 1}43 44# Shell silently ignores extra parameters.45# Ticket [f5cb008a65].46do_test shell2-1.2.1 {47 catchcmdex {:memory: -list "select+3" "select+4"}48} {0 {349450}}51 52# Test a problem reported on the mailing list. The shell was at one point53# returning the generic SQLITE_ERROR message ("SQL error or missing database")54# instead of the "too many levels..." message in the test below.55#56do_test shell2-1.3 {57 catchcmd "-batch test.db" {58 PRAGMA recursive_triggers = ON;59 CREATE TABLE t5(a PRIMARY KEY, b, c);60 INSERT INTO t5 VALUES(1, 2, 3);61 CREATE TRIGGER au_tble AFTER UPDATE ON t5 BEGIN62 UPDATE OR IGNORE t5 SET a = new.a, c = 10;63 END;64 65 UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1;66 }67} {1 {Error near line 9: too many levels of trigger recursion}}68 69 70 71# Shell not echoing all commands with echo on.72# Ticket [eb620916be].73 74# Test with echo off75# NB. whitespace is important76do_test shell2-1.4.1 {77 forcedelete foo.db78 catchcmd "foo.db" {.mode batch79CREATE TABLE foo(a);80INSERT INTO foo(a) VALUES(1);81SELECT * FROM foo;}82} {0 1}83 84# Test with echo on using command line option85# NB. whitespace is important86do_test shell2-1.4.2 {87 forcedelete foo.db88 catchcmd "-echo foo.db" {CREATE TABLE foo(a);89INSERT INTO foo(a) VALUES(1);90SELECT * FROM foo;}91} {0 {CREATE TABLE foo(a);92INSERT INTO foo(a) VALUES(1);93SELECT * FROM foo;941}}95 96# Test with echo on using dot command97# NB. whitespace is important98do_test shell2-1.4.3 {99 forcedelete foo.db100 catchcmd "foo.db" {101.mode batch102.echo ON103CREATE TABLE foo(a);104INSERT INTO foo(a) VALUES(1);105SELECT * FROM foo;}106} {0 {CREATE TABLE foo(a);107INSERT INTO foo(a) VALUES(1);108SELECT * FROM foo;1091}}110 111# Test with echo on using dot command and 112# turning off mid- processing.113# NB. whitespace is important114do_test shell2-1.4.4 {115 forcedelete foo.db116 catchcmd "foo.db" {117.mode batch118.echo ON119CREATE TABLE foo(a);120.echo OFF121INSERT INTO foo(a) VALUES(1);122SELECT * FROM foo;}123} {0 {CREATE TABLE foo(a);124.echo OFF1251}}126 127# Test with echo on using dot command and 128# multiple commands per line.129# NB. whitespace is important130do_test shell2-1.4.5 {131 forcedelete foo.db132 catchcmdex "foo.db" {133.mode batch134.echo ON135CREATE TABLE foo1(a);136INSERT INTO foo1(a) VALUES(1);137CREATE TABLE foo2(b);138INSERT INTO foo2(b) VALUES(1);139SELECT * FROM foo1; SELECT * FROM foo2;140INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);141SELECT * FROM foo1; SELECT * FROM foo2;142}143} {0 {CREATE TABLE foo1(a);144INSERT INTO foo1(a) VALUES(1);145CREATE TABLE foo2(b);146INSERT INTO foo2(b) VALUES(1);147SELECT * FROM foo1; SELECT * FROM foo2;14811491150INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);151SELECT * FROM foo1; SELECT * FROM foo2;1521153215411552156}}157 158# Test with echo on and headers on using dot command and 159# multiple commands per line.160# NB. whitespace is important161do_test shell2-1.4.6 {162 forcedelete foo.db163 catchcmdex "foo.db" {164.mode batch165.echo ON166.headers ON167CREATE TABLE foo1(a);168INSERT INTO foo1(a) VALUES(1);169CREATE TABLE foo2(b);170INSERT INTO foo2(b) VALUES(1);171SELECT * FROM foo1; SELECT * FROM foo2;172INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);173SELECT * FROM foo1; SELECT * FROM foo2;174}175} {0 {.headers ON176CREATE TABLE foo1(a);177INSERT INTO foo1(a) VALUES(1);178CREATE TABLE foo2(b);179INSERT INTO foo2(b) VALUES(1);180SELECT * FROM foo1; SELECT * FROM foo2;181a1821183b1841185INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);186SELECT * FROM foo1; SELECT * FROM foo2;187a18811892190b19111922193}}194 195# Test for rejection of incomplete input at EOF.196# Reported at https://sqlite.org/forum/forumpost/718f489a43be3197197do_test shell2-1.4.7 {198 catchcmd ":memory:" {199 SELECT 'unclosed;}200} {1 {Parse error near line 2: unrecognized token: "'unclosed;"201 SELECT 'unclosed;202 ^--- error here}}203 204# Verify that safe mode rejects certain UDFs205# Reported at https://sqlite.org/forum/forumpost/07beac8056151b2f206do_test shell2-1.4.8 {207 catchcmd "-safe :memory:" {208 SELECT edit('DoNotCare');}209} {1 {line 2: cannot use the edit() function in safe mode}}210do_test shell2-1.4.9 {211 catchcmd "-safe :memory:" {212 SELECT writefile('DoNotCare', x'');}213} {1 {line 2: cannot use the writefile() function in safe mode}}214 215# Verify that .clone handles sequence table.216# See https://sqlite.org/forum/forumpost/71ff9e6c4c217do_test shell2-1.4.9 {218 forcedelete clone.db219 set res [catchcmd :memory: [string trim {220.mode batch221 CREATE TABLE t(id INTEGER PRIMARY KEY AUTOINCREMENT);222 INSERT INTO t VALUES (1),(2);223.clone clone.db224.open clone.db225 SELECT max(seq) FROM sqlite_sequence;}]]226} {0 {t... done227done2282}}229 230ifcapable vtab {231# Verify that generate_series stays sane near 64-bit range boundaries.232# See overflow report at https://sqlite.org/forum/forumpost/5d34ce5280233do_test shell2-1.4.10 {234 set res [catchcmd :memory: [string trim {235 .mode batch236 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,1);237 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,-1);238 SELECT avg(value),min(value),max(value) FROM generate_series(239 -9223372036854775808,9223372036854775807,1085102592571150095);240 SELECT * FROM generate_series(-9223372036854775808,9223372036854775807,241 9223372036854775807);242 SELECT value FROM generate_series(-4611686018427387904,243 4611686018427387904, 4611686018427387904) ORDER BY value DESC;244 SELECT * FROM generate_series(0,-2,-1);245 SELECT * FROM generate_series(0,-2);246 SELECT * FROM generate_series(0,2) LIMIT 3;}]]247} {0 {92233720368547758072489223372036854775807249-0.5|-9223372036854775808|9223372036854775807250-9223372036854775808251-1252922337203685477580625346116860184273879042540255-46116860184273879042560257-1258-2259026012612}}262do_test shell2-1.4.10b {263 set res [catchcmd :memory: [string trim {264 .mode tty265.print266 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,1);267 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,-1);268 SELECT avg(value),min(value),max(value) FROM generate_series(269 -9223372036854775808,9223372036854775807,1085102592571150095);270 SELECT * FROM generate_series(-9223372036854775808,9223372036854775807,271 9223372036854775807);272 SELECT value FROM generate_series(-4611686018427387904,273 4611686018427387904, 4611686018427387904) ORDER BY value DESC;274 SELECT * FROM generate_series(0,-2,-1);275 SELECT * FROM generate_series(0,-2);276 SELECT * FROM generate_series(0,2) LIMIT 3;}]]277} {0 {278╭─────────────────────╮279│ value │280╞═════════════════════╡281│ 9223372036854775807 │282╰─────────────────────╯283╭─────────────────────╮284│ value │285╞═════════════════════╡286│ 9223372036854775807 │287╰─────────────────────╯288╭────────────┬──────────────────────┬─────────────────────╮289│ avg(value) │ min(value) │ max(value) │290╞════════════╪══════════════════════╪═════════════════════╡291│ -0.5 │ -9223372036854775808 │ 9223372036854775807 │292╰────────────┴──────────────────────┴─────────────────────╯293╭──────────────────────╮294│ value │295╞══════════════════════╡296│ -9223372036854775808 │297│ -1 │298│ 9223372036854775806 │299╰──────────────────────╯300╭──────────────────────╮301│ value │302╞══════════════════════╡303│ 4611686018427387904 │304│ 0 │305│ -4611686018427387904 │306╰──────────────────────╯307╭───────╮308│ value │309╞═══════╡310│ 0 │311│ -1 │312│ -2 │313╰───────╯314╭───────╮315│ value │316╞═══════╡317│ 0 │318│ 1 │319│ 2 │320╰───────╯}}321} ;# ifcapable vtab322 323ifcapable vtab {324# Bug discovered while messing around, .import hangs with325# bit 7 set in column separator.326do_test shell2-1.4.11 {327 forcedelete dummy.csv328 set df [open dummy.csv w]329 puts $df dog,cat330 close $df331 set res [catchcmd :memory: [string trim {332 CREATE TABLE t(line text);333.mode ascii334.separator "\377" "\n"335.import dummy.csv t336 SELECT count(*) FROM t;}]]337} {0 1}338} ;# ifcapable vtab339 340# Bug from forum post 7cbe081746dd3803341# Keywords as column names were producing an error message.342do_test shell2-1.4.12 {343 set res [catchcmd :memory: [string trim {.mode batch344 CREATE TABLE "group"("order" text);345 INSERT INTO "group" VALUES ('ABC');346.sha3sum}]]347} {0 ca08bc02b7e95c7df431a3a4b1cc0f8d8743914793473f55b5558e03}348 349#-------------------------------------------------------------------------350 351foreach {tn hexdump expect} {352 0 {353| size 8192 pagesize 4096 filename my.db354| page 1 offset 0355| 0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 SQLite format 3.356| 16: 10 00 01 01 00 40 20 20 00 00 00 03 00 00 00 02 .....@ ........357| 32: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04 ................358| 48: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 ................359| 80: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 03 ................360| 96: 00 2e 8d f8 0d 00 00 00 01 0f df 00 0f df 00 00 ................361| 4048: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 1f ................362| 4064: 01 06 17 0f 0f 01 2f 74 61 62 6c 65 74 74 02 43 ....../tablett.C363| 4080: 52 45 41 54 45 20 54 41 42 4c 45 20 74 28 78 29 REATE TABLE t(x)364| page 2 offset 4096365| 0: 0d 00 00 00 02 0f ee 00 0f f7 0f ee 00 00 00 00 ................366| 4064: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 07 02 ................367| 4080: 02 17 77 6f 72 6c 64 07 01 02 17 68 65 6c 6c 6f ..world....hello368| end my.db369 }370 {0 {}}371 372 1 {373| size 2147483647 pagesize 4096 filename my.db374| page 1 offset 0375| 0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 SQLite format 3.376| 16: 10 00 01 01 00 40 20 20 00 00 00 03 00 00 00 02 .....@ ........377| 32: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04 ................378| 48: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 ................379| 80: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 03 ................380| 96: 00 2e 8d f8 0d 00 00 00 01 0f df 00 0f df 00 00 ................381| 4048: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 1f ................382| 4064: 01 06 17 0f 0f 01 2f 74 61 62 6c 65 74 74 02 43 ....../tablett.C383| 4080: 52 45 41 54 45 20 54 41 42 4c 45 20 74 28 78 29 REATE TABLE t(x)384| page 2 offset 4096385| 0: 0d 00 00 00 02 0f ee 00 0f f7 0f ee 00 00 00 00 ................386| 4064: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 07 02 ................387| 4080: 02 17 77 6f 72 6c 64 07 01 02 17 68 65 6c 6c 6f ..world....hello388| end my.db389 }390 {1 {Error: out of memory}}391 392 2 {393| size 8192 pagesize 4096 filename my.db394| page 1 offset 2147483647395| 0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 SQLite format 3.396| 16: 10 00 01 01 00 40 20 20 00 00 00 03 00 00 00 02 .....@ ........397| 32: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04 ................398| 48: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 ................399| 80: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 03 ................400| 96: 00 2e 8d f8 0d 00 00 00 01 0f df 00 0f df 00 00 ................401| 4048: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 1f ................402| 4064: 01 06 17 0f 0f 01 2f 74 61 62 6c 65 74 74 02 43 ....../tablett.C403| 4080: 52 45 41 54 45 20 54 41 42 4c 45 20 74 28 78 29 REATE TABLE t(x)404| page 2 offset 4096405| 0: 0d 00 00 00 02 0f ee 00 0f f7 0f ee 00 00 00 00 ................406| 4064: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 07 02 ................407| 4080: 02 17 77 6f 72 6c 64 07 01 02 17 68 65 6c 6c 6f ..world....hello408| end my.db409 }410 {0 {}}411 412 3 {413| size 8192 pagesize 4096 filename my.db414| page 1 offset 0415| 0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 SQLite format 3.416| 16: 10 00 01 01 00 40 20 20 00 00 00 03 00 00 00 02 .....@ ........417| 32: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04 ................418| 48: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 ................419| 80: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 03 ................420| 96: 00 2e 8d f8 0d 00 00 00 01 0f df 00 0f df 00 00 ................421| 4048: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 1f ................422| 4064: 01 06 17 0f 0f 01 2f 74 61 62 6c 65 74 74 02 43 ....../tablett.C423| 4080: 52 45 41 54 45 20 54 41 42 4c 45 20 74 28 78 29 REATE TABLE t(x)424| page 2 offset 4096425| 2147483647: 0d 00 00 00 02 0f ee 00 0f f7 0f ee 00 00 00 00 ................426| 4064: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 07 02 ................427| 4080: 02 17 77 6f 72 6c 64 07 01 02 17 68 65 6c 6c 6f ..world....hello428| end my.db429}430 {0 {}}431 432} {433 set fd [open dump.txt w]434 puts $fd [string trim $hexdump]435 close $fd436 do_test shell2-2.$tn.1 {437 set rc [ catchcmd "" ".open --hexdb dump.txt"]438 } $expect439}440 441 442finish_test443