3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
13 # The focus of this file is testing the CLI shell tool.
15 # $Id: shell2.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $
20 # shell2-1.*: Misc. test of various tickets and reported errors.
22 set testdir [file dirname $argv0]
23 source $testdir/tester.tcl
24 set CLI [test_find_cli]
26 forcedelete test.db test.db-journal test.db-wal
30 #----------------------------------------------------------------------------
31 # shell2-1.*: Misc. test of various tickets and reported errors.
34 # Batch mode not creating databases.
35 # Reported on mailing list by Ken Zalewski.
36 # Ticket [aeff892c57].
37 do_test shell2-1.1.1 {
39 set rc [ catchcmd "-batch foo.db" "CREATE TABLE t1(a);" ]
40 set fexist [file exist foo.db]
44 # Shell silently ignores extra parameters.
45 # Ticket [f5cb008a65].
46 do_test shell2-1.2.1 {
47 catchcmdex {:memory: "select+3" "select+4"}
52 # Test a problem reported on the mailing list. The shell was at one point
53 # returning the generic SQLITE_ERROR message ("SQL error or missing database")
54 # instead of the "too many levels..." message in the test below.
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 BEGIN
62 UPDATE OR IGNORE t5 SET a = new.a, c = 10;
65 UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1;
67 } {1 {Runtime error near line 9: too many levels of trigger recursion}}
71 # Shell not echoing all commands with echo on.
72 # Ticket [eb620916be].
75 # NB. whitespace is important
76 do_test shell2-1.4.1 {
78 catchcmd "foo.db" {CREATE TABLE foo(a);
79 INSERT INTO foo(a) VALUES(1);
83 # Test with echo on using command line option
84 # NB. whitespace is important
85 do_test shell2-1.4.2 {
87 catchcmd "-echo foo.db" {CREATE TABLE foo(a);
88 INSERT INTO foo(a) VALUES(1);
90 } {0 {CREATE TABLE foo(a);
91 INSERT INTO foo(a) VALUES(1);
95 # Test with echo on using dot command
96 # NB. whitespace is important
97 do_test shell2-1.4.3 {
99 catchcmd "foo.db" {.echo ON
101 INSERT INTO foo(a) VALUES(1);
103 } {0 {CREATE TABLE foo(a);
104 INSERT INTO foo(a) VALUES(1);
108 # Test with echo on using dot command and
109 # turning off mid- processing.
110 # NB. whitespace is important
111 do_test shell2-1.4.4 {
113 catchcmd "foo.db" {.echo ON
116 INSERT INTO foo(a) VALUES(1);
118 } {0 {CREATE TABLE foo(a);
122 # Test with echo on using dot command and
123 # multiple commands per line.
124 # NB. whitespace is important
125 do_test shell2-1.4.5 {
127 catchcmdex "foo.db" {.echo ON
128 CREATE TABLE foo1(a);
129 INSERT INTO foo1(a) VALUES(1);
130 CREATE TABLE foo2(b);
131 INSERT INTO foo2(b) VALUES(1);
132 SELECT * FROM foo1; SELECT * FROM foo2;
133 INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);
134 SELECT * FROM foo1; SELECT * FROM foo2;
136 } {0 {CREATE TABLE foo1(a);
137 INSERT INTO foo1(a) VALUES(1);
138 CREATE TABLE foo2(b);
139 INSERT INTO foo2(b) VALUES(1);
140 SELECT * FROM foo1; SELECT * FROM foo2;
143 INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);
144 SELECT * FROM foo1; SELECT * FROM foo2;
151 # Test with echo on and headers on using dot command and
152 # multiple commands per line.
153 # NB. whitespace is important
154 do_test shell2-1.4.6 {
156 catchcmdex "foo.db" {.echo ON
158 CREATE TABLE foo1(a);
159 INSERT INTO foo1(a) VALUES(1);
160 CREATE TABLE foo2(b);
161 INSERT INTO foo2(b) VALUES(1);
162 SELECT * FROM foo1; SELECT * FROM foo2;
163 INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);
164 SELECT * FROM foo1; SELECT * FROM foo2;
167 CREATE TABLE foo1(a);
168 INSERT INTO foo1(a) VALUES(1);
169 CREATE TABLE foo2(b);
170 INSERT INTO foo2(b) VALUES(1);
171 SELECT * FROM foo1; SELECT * FROM foo2;
176 INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);
177 SELECT * FROM foo1; SELECT * FROM foo2;
186 # Test for rejection of incomplete input at EOF.
187 # Reported at https://sqlite.org/forum/forumpost/718f489a43be3197
188 do_test shell2-1.4.7 {
189 catchcmd ":memory:" {
191 } {1 {Parse error near line 2: unrecognized token: "'unclosed;"
195 # Verify that safe mode rejects certain UDFs
196 # Reported at https://sqlite.org/forum/forumpost/07beac8056151b2f
197 do_test shell2-1.4.8 {
198 catchcmd "-safe :memory:" {
199 SELECT edit('DoNotCare');}
200 } {1 {line 2: cannot use the edit() function in safe mode}}
201 do_test shell2-1.4.9 {
202 catchcmd "-safe :memory:" {
203 SELECT writefile('DoNotCare', x'');}
204 } {1 {line 2: cannot use the writefile() function in safe mode}}
206 # Verify that .clone handles sequence table.
207 # See https://sqlite.org/forum/forumpost/71ff9e6c4c
208 do_test shell2-1.4.9 {
210 set res [catchcmd :memory: [string trim {
211 CREATE TABLE t(id INTEGER PRIMARY KEY AUTOINCREMENT);
212 INSERT INTO t VALUES (1),(2);
215 SELECT max(seq) FROM sqlite_sequence;}]]
220 # Verify that generate_series stays sane near 64-bit range boundaries.
221 # See overflow report at https://sqlite.org/forum/forumpost/5d34ce5280
222 do_test shell2-1.4.10 {
223 set res [catchcmd :memory: [string trim {
224 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,1);
225 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,-1);
226 SELECT avg(rowid),min(value),max(value) FROM generate_series(
227 -9223372036854775808,9223372036854775807,1085102592571150095);
228 SELECT * FROM generate_series(-9223372036854775808,9223372036854775807,
229 9223372036854775807);
230 SELECT value,rowid FROM generate_series(-4611686018427387904,
231 4611686018427387904, 4611686018427387904) ORDER BY value DESC;
232 SELECT * FROM generate_series(0,-2,-1);
233 SELECT * FROM generate_series(0,-2);
234 SELECT * FROM generate_series(0,2) LIMIT 3;}]]
235 } {0 {9223372036854775807
237 9.5|-9223372036854775808|9223372036854775807
241 4611686018427387904|3
243 -4611686018427387904|1
251 # Bug discovered while messing around, .import hangs with
252 # bit 7 set in column separator.
253 do_test shell2-1.4.11 {
254 forcedelete dummy.csv
255 set df [open dummy.csv w]
258 set res [catchcmd :memory: [string trim {
259 CREATE TABLE t(line text);
261 .separator "\377" "\n"
263 SELECT count(*) FROM t;}]]
266 # Bug from forum post 7cbe081746dd3803
267 # Keywords as column names were producing an error message.
268 do_test shell2-1.4.12 {
269 set res [catchcmd :memory: [string trim {
270 CREATE TABLE "group"("order" text);
271 INSERT INTO "group" VALUES ('ABC');
273 } {0 ca08bc02b7e95c7df431a3a4b1cc0f8d8743914793473f55b5558e03}