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 # $Id: shell2.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $ 15 # 16 17 # Test plan: 18 # 19 # shell2-1.*: Misc. test of various tickets and reported errors. 20 # 21 22 package require sqlite3 23 24 set CLI "./sqlite3" 25 26 proc do_test {name cmd expected} { 27 puts -nonewline "$name ..." 28 set res [uplevel $cmd] 29 if {$res eq $expected} { 30 puts Ok 31 } else { 32 puts Error 33 puts " Got: $res" 34 puts " Expected: $expected" 35 exit 36 } 37 } 38 39 proc execsql {sql} { 40 uplevel [list db eval $sql] 41 } 42 43 proc catchsql {sql} { 44 set rc [catch {uplevel [list db eval $sql]} msg] 45 list $rc $msg 46 } 47 48 proc catchcmd {db {cmd ""}} { 49 global CLI 50 set out [open cmds.txt w] 51 puts $out $cmd 52 close $out 53 set line "exec $CLI $db < cmds.txt" 54 set rc [catch { eval $line } msg] 55 list $rc $msg 56 } 57 58 file delete -force test.db test.db.journal 59 sqlite3 db test.db 60 61 62 #---------------------------------------------------------------------------- 63 # shell2-1.*: Misc. test of various tickets and reported errors. 64 # 65 66 # Batch mode not creating databases. 67 # Reported on mailing list by Ken Zalewski. 68 # Ticket [aeff892c57]. 69 do_test shell2-1.1.1 { 70 file delete -force foo.db 71 set rc [ catchcmd "-batch foo.db" "CREATE TABLE t1(a);" ] 72 set fexist [file exist foo.db] 73 list $rc $fexist 74 } {{0 {}} 1} 75 76 # Shell silently ignores extra parameters. 77 # Ticket [f5cb008a65]. 78 do_test shell2-1.2.1 { 79 set rc [catch { eval exec $CLI \":memory:\" \"select 3\" \"select 4\" } msg] 80 list $rc \ 81 [regexp {Error: too many options: "select 4"} $msg] 82 } {1 1} 83 84 # Test a problem reported on the mailing list. The shell was at one point 85 # returning the generic SQLITE_ERROR message ("SQL error or missing database") 86 # instead of the "too many levels..." message in the test below. 87 # 88 do_test shell2-1.3 { 89 catchcmd "-batch test.db" { 90 PRAGMA recursive_triggers = ON; 91 CREATE TABLE t5(a PRIMARY KEY, b, c); 92 INSERT INTO t5 VALUES(1, 2, 3); 93 CREATE TRIGGER au_tble AFTER UPDATE ON t5 BEGIN 94 UPDATE OR IGNORE t5 SET a = new.a, c = 10; 95 END; 96 97 UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1; 98 } 99 } {1 {Error: near line 9: too many levels of trigger recursion}} 100 101 102 103 # Shell not echoing all commands with echo on. 104 # Ticket [eb620916be]. 105 106 # Test with echo off 107 # NB. whitespace is important 108 do_test shell2-1.4.1 { 109 file delete -force foo.db 110 catchcmd "foo.db" {CREATE TABLE foo(a); 111 INSERT INTO foo(a) VALUES(1); 112 SELECT * FROM foo;} 113 } {0 1} 114 115 # Test with echo on using command line option 116 # NB. whitespace is important 117 do_test shell2-1.4.2 { 118 file delete -force foo.db 119 catchcmd "-echo foo.db" {CREATE TABLE foo(a); 120 INSERT INTO foo(a) VALUES(1); 121 SELECT * FROM foo;} 122 } {0 {CREATE TABLE foo(a); 123 INSERT INTO foo(a) VALUES(1); 124 SELECT * FROM foo; 125 1}} 126 127 # Test with echo on using dot command 128 # NB. whitespace is important 129 do_test shell2-1.4.3 { 130 file delete -force foo.db 131 catchcmd "foo.db" {.echo ON 132 CREATE TABLE foo(a); 133 INSERT INTO foo(a) VALUES(1); 134 SELECT * FROM foo;} 135 } {0 {CREATE TABLE foo(a); 136 INSERT INTO foo(a) VALUES(1); 137 SELECT * FROM foo; 138 1}} 139 140 # Test with echo on using dot command and 141 # turning off mid- processing. 142 # NB. whitespace is important 143 do_test shell2-1.4.4 { 144 file delete -force foo.db 145 catchcmd "foo.db" {.echo ON 146 CREATE TABLE foo(a); 147 .echo OFF 148 INSERT INTO foo(a) VALUES(1); 149 SELECT * FROM foo;} 150 } {0 {CREATE TABLE foo(a); 151 .echo OFF 152 1}} 153 154 # Test with echo on using dot command and 155 # multiple commands per line. 156 # NB. whitespace is important 157 do_test shell2-1.4.5 { 158 file delete -force foo.db 159 catchcmd "foo.db" {.echo ON 160 CREATE TABLE foo1(a); 161 INSERT INTO foo1(a) VALUES(1); 162 CREATE TABLE foo2(b); 163 INSERT INTO foo2(b) VALUES(1); 164 SELECT * FROM foo1; SELECT * FROM foo2; 165 INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2); 166 SELECT * FROM foo1; SELECT * FROM foo2; 167 } 168 } {0 {CREATE TABLE foo1(a); 169 INSERT INTO foo1(a) VALUES(1); 170 CREATE TABLE foo2(b); 171 INSERT INTO foo2(b) VALUES(1); 172 SELECT * FROM foo1; 173 1 174 SELECT * FROM foo2; 175 1 176 INSERT INTO foo1(a) VALUES(2); 177 INSERT INTO foo2(b) VALUES(2); 178 SELECT * FROM foo1; 179 1 180 2 181 SELECT * FROM foo2; 182 1 183 2}} 184 185 # Test with echo on and headers on using dot command and 186 # multiple commands per line. 187 # NB. whitespace is important 188 do_test shell2-1.4.6 { 189 file delete -force foo.db 190 catchcmd "foo.db" {.echo ON 191 .headers ON 192 CREATE TABLE foo1(a); 193 INSERT INTO foo1(a) VALUES(1); 194 CREATE TABLE foo2(b); 195 INSERT INTO foo2(b) VALUES(1); 196 SELECT * FROM foo1; SELECT * FROM foo2; 197 INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2); 198 SELECT * FROM foo1; SELECT * FROM foo2; 199 } 200 } {0 {.headers ON 201 CREATE TABLE foo1(a); 202 INSERT INTO foo1(a) VALUES(1); 203 CREATE TABLE foo2(b); 204 INSERT INTO foo2(b) VALUES(1); 205 SELECT * FROM foo1; 206 a 207 1 208 SELECT * FROM foo2; 209 b 210 1 211 INSERT INTO foo1(a) VALUES(2); 212 INSERT INTO foo2(b) VALUES(2); 213 SELECT * FROM foo1; 214 a 215 1 216 2 217 SELECT * FROM foo2; 218 b 219 1 220 2}} 221 222 puts "CLI tests completed successfully" 223
