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 22package require sqlite3 23 24set CLI "./sqlite3" 25 26proc 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 39proc execsql {sql} { 40 uplevel [list db eval $sql] 41} 42 43proc catchsql {sql} { 44 set rc [catch {uplevel [list db eval $sql]} msg] 45 list $rc $msg 46} 47 48proc 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 58file delete -force test.db test.db.journal 59sqlite3 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]. 69do_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]. 78do_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# 88do_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 108do_test shell2-1.4.1 { 109 file delete -force foo.db 110 catchcmd "foo.db" {CREATE TABLE foo(a); 111INSERT INTO foo(a) VALUES(1); 112SELECT * FROM foo;} 113} {0 1} 114 115# Test with echo on using command line option 116# NB. whitespace is important 117do_test shell2-1.4.2 { 118 file delete -force foo.db 119 catchcmd "-echo foo.db" {CREATE TABLE foo(a); 120INSERT INTO foo(a) VALUES(1); 121SELECT * FROM foo;} 122} {0 {CREATE TABLE foo(a); 123INSERT INTO foo(a) VALUES(1); 124SELECT * FROM foo; 1251}} 126 127# Test with echo on using dot command 128# NB. whitespace is important 129do_test shell2-1.4.3 { 130 file delete -force foo.db 131 catchcmd "foo.db" {.echo ON 132CREATE TABLE foo(a); 133INSERT INTO foo(a) VALUES(1); 134SELECT * FROM foo;} 135} {0 {CREATE TABLE foo(a); 136INSERT INTO foo(a) VALUES(1); 137SELECT * FROM foo; 1381}} 139 140# Test with echo on using dot command and 141# turning off mid- processing. 142# NB. whitespace is important 143do_test shell2-1.4.4 { 144 file delete -force foo.db 145 catchcmd "foo.db" {.echo ON 146CREATE TABLE foo(a); 147.echo OFF 148INSERT INTO foo(a) VALUES(1); 149SELECT * FROM foo;} 150} {0 {CREATE TABLE foo(a); 151.echo OFF 1521}} 153 154# Test with echo on using dot command and 155# multiple commands per line. 156# NB. whitespace is important 157do_test shell2-1.4.5 { 158 file delete -force foo.db 159 catchcmd "foo.db" {.echo ON 160CREATE TABLE foo1(a); 161INSERT INTO foo1(a) VALUES(1); 162CREATE TABLE foo2(b); 163INSERT INTO foo2(b) VALUES(1); 164SELECT * FROM foo1; SELECT * FROM foo2; 165INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2); 166SELECT * FROM foo1; SELECT * FROM foo2; 167} 168} {0 {CREATE TABLE foo1(a); 169INSERT INTO foo1(a) VALUES(1); 170CREATE TABLE foo2(b); 171INSERT INTO foo2(b) VALUES(1); 172SELECT * FROM foo1; 1731 174SELECT * FROM foo2; 1751 176INSERT INTO foo1(a) VALUES(2); 177INSERT INTO foo2(b) VALUES(2); 178SELECT * FROM foo1; 1791 1802 181SELECT * FROM foo2; 1821 1832}} 184 185# Test with echo on and headers on using dot command and 186# multiple commands per line. 187# NB. whitespace is important 188do_test shell2-1.4.6 { 189 file delete -force foo.db 190 catchcmd "foo.db" {.echo ON 191.headers ON 192CREATE TABLE foo1(a); 193INSERT INTO foo1(a) VALUES(1); 194CREATE TABLE foo2(b); 195INSERT INTO foo2(b) VALUES(1); 196SELECT * FROM foo1; SELECT * FROM foo2; 197INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2); 198SELECT * FROM foo1; SELECT * FROM foo2; 199} 200} {0 {.headers ON 201CREATE TABLE foo1(a); 202INSERT INTO foo1(a) VALUES(1); 203CREATE TABLE foo2(b); 204INSERT INTO foo2(b) VALUES(1); 205SELECT * FROM foo1; 206a 2071 208SELECT * FROM foo2; 209b 2101 211INSERT INTO foo1(a) VALUES(2); 212INSERT INTO foo2(b) VALUES(2); 213SELECT * FROM foo1; 214a 2151 2162 217SELECT * FROM foo2; 218b 2191 2202}} 221 222puts "CLI tests completed successfully" 223