Debian Codegraph @

test/shell2.test

raw
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 # TESTRUNNER: shell
12 #
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 #
22 set testdir [file dirname $argv0]
23 source $testdir/tester.tcl
24 set CLI [test_find_cli]
25 db close
26 forcedelete test.db test.db-journal test.db-wal
27 sqlite3 db test.db
28
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].
37 do_test shell2-1.1.1 {
38 forcedelete foo.db
39 set rc [ catchcmd "-batch foo.db" "CREATE TABLE t1(a);" ]
40 set fexist [file exist foo.db]
41 list $rc $fexist
42 } {{0 {}} 1}
43
44 # Shell silently ignores extra parameters.
45 # Ticket [f5cb008a65].
46 do_test shell2-1.2.1 {
47 catchcmdex {:memory: -list "select+3" "select+4"}
48 } {0 {3
49 4
50 }}
51
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.
55 #
56 do_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 BEGIN
62 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 off
75 # NB. whitespace is important
76 do_test shell2-1.4.1 {
77 forcedelete foo.db
78 catchcmd "foo.db" {.mode batch
79 CREATE TABLE foo(a);
80 INSERT INTO foo(a) VALUES(1);
81 SELECT * FROM foo;}
82 } {0 1}
83
84 # Test with echo on using command line option
85 # NB. whitespace is important
86 do_test shell2-1.4.2 {
87 forcedelete foo.db
88 catchcmd "-echo foo.db" {CREATE TABLE foo(a);
89 INSERT INTO foo(a) VALUES(1);
90 SELECT * FROM foo;}
91 } {0 {CREATE TABLE foo(a);
92 INSERT INTO foo(a) VALUES(1);
93 SELECT * FROM foo;
94 1}}
95
96 # Test with echo on using dot command
97 # NB. whitespace is important
98 do_test shell2-1.4.3 {
99 forcedelete foo.db
100 catchcmd "foo.db" {
101 .mode batch
102 .echo ON
103 CREATE TABLE foo(a);
104 INSERT INTO foo(a) VALUES(1);
105 SELECT * FROM foo;}
106 } {0 {CREATE TABLE foo(a);
107 INSERT INTO foo(a) VALUES(1);
108 SELECT * FROM foo;
109 1}}
110
111 # Test with echo on using dot command and
112 # turning off mid- processing.
113 # NB. whitespace is important
114 do_test shell2-1.4.4 {
115 forcedelete foo.db
116 catchcmd "foo.db" {
117 .mode batch
118 .echo ON
119 CREATE TABLE foo(a);
120 .echo OFF
121 INSERT INTO foo(a) VALUES(1);
122 SELECT * FROM foo;}
123 } {0 {CREATE TABLE foo(a);
124 .echo OFF
125 1}}
126
127 # Test with echo on using dot command and
128 # multiple commands per line.
129 # NB. whitespace is important
130 do_test shell2-1.4.5 {
131 forcedelete foo.db
132 catchcmdex "foo.db" {
133 .mode batch
134 .echo ON
135 CREATE TABLE foo1(a);
136 INSERT INTO foo1(a) VALUES(1);
137 CREATE TABLE foo2(b);
138 INSERT INTO foo2(b) VALUES(1);
139 SELECT * FROM foo1; SELECT * FROM foo2;
140 INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);
141 SELECT * FROM foo1; SELECT * FROM foo2;
142 }
143 } {0 {CREATE TABLE foo1(a);
144 INSERT INTO foo1(a) VALUES(1);
145 CREATE TABLE foo2(b);
146 INSERT INTO foo2(b) VALUES(1);
147 SELECT * FROM foo1; SELECT * FROM foo2;
148 1
149 1
150 INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);
151 SELECT * FROM foo1; SELECT * FROM foo2;
152 1
153 2
154 1
155 2
156 }}
157
158 # Test with echo on and headers on using dot command and
159 # multiple commands per line.
160 # NB. whitespace is important
161 do_test shell2-1.4.6 {
162 forcedelete foo.db
163 catchcmdex "foo.db" {
164 .mode batch
165 .echo ON
166 .headers ON
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;
172 INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);
173 SELECT * FROM foo1; SELECT * FROM foo2;
174 }
175 } {0 {.headers ON
176 CREATE TABLE foo1(a);
177 INSERT INTO foo1(a) VALUES(1);
178 CREATE TABLE foo2(b);
179 INSERT INTO foo2(b) VALUES(1);
180 SELECT * FROM foo1; SELECT * FROM foo2;
181 a
182 1
183 b
184 1
185 INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);
186 SELECT * FROM foo1; SELECT * FROM foo2;
187 a
188 1
189 2
190 b
191 1
192 2
193 }}
194
195 # Test for rejection of incomplete input at EOF.
196 # Reported at https://sqlite.org/forum/forumpost/718f489a43be3197
197 do_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 UDFs
205 # Reported at https://sqlite.org/forum/forumpost/07beac8056151b2f
206 do_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}}
210 do_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/71ff9e6c4c
217 do_test shell2-1.4.9 {
218 forcedelete clone.db
219 set res [catchcmd :memory: [string trim {
220 .mode batch
221 CREATE TABLE t(id INTEGER PRIMARY KEY AUTOINCREMENT);
222 INSERT INTO t VALUES (1),(2);
223 .clone clone.db
224 .open clone.db
225 SELECT max(seq) FROM sqlite_sequence;}]]
226 } {0 {t... done
227 done
228 2}}
229
230 ifcapable vtab {
231 # Verify that generate_series stays sane near 64-bit range boundaries.
232 # See overflow report at https://sqlite.org/forum/forumpost/5d34ce5280
233 do_test shell2-1.4.10 {
234 set res [catchcmd :memory: [string trim {
235 .mode batch
236 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 {9223372036854775807
248 9223372036854775807
249 -0.5|-9223372036854775808|9223372036854775807
250 -9223372036854775808
251 -1
252 9223372036854775806
253 4611686018427387904
254 0
255 -4611686018427387904
256 0
257 -1
258 -2
259 0
260 1
261 2}}
262 do_test shell2-1.4.10b {
263 set res [catchcmd :memory: [string trim {
264 .mode tty
265 .print
266 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 vtab
322
323 ifcapable vtab {
324 # Bug discovered while messing around, .import hangs with
325 # bit 7 set in column separator.
326 do_test shell2-1.4.11 {
327 forcedelete dummy.csv
328 set df [open dummy.csv w]
329 puts $df dog,cat
330 close $df
331 set res [catchcmd :memory: [string trim {
332 CREATE TABLE t(line text);
333 .mode ascii -colsep "\377" -rowsep "\n"
334 .import dummy.csv t
335 SELECT count(*) FROM t;}]]
336 } {1 {0
337 Error: .import column separator must be ASCII}}
338 } ;# ifcapable vtab
339
340 # Bug from forum post 7cbe081746dd3803
341 # Keywords as column names were producing an error message.
342 do_test shell2-1.4.12 {
343 set res [catchcmd :memory: [string trim {.mode batch
344 CREATE TABLE "group"("order" text);
345 INSERT INTO "group" VALUES ('ABC');
346 .sha3sum}]]
347 } {0 ca08bc02b7e95c7df431a3a4b1cc0f8d8743914793473f55b5558e03}
348
349 #-------------------------------------------------------------------------
350
351 foreach {tn hexdump expect} {
352 0 {
353 | size 8192 pagesize 4096 filename my.db
354 | page 1 offset 0
355 | 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.C
363 | 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 4096
365 | 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....hello
368 | end my.db
369 }
370 {0 {}}
371
372 1 {
373 | size 2147483647 pagesize 4096 filename my.db
374 | page 1 offset 0
375 | 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.C
383 | 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 4096
385 | 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....hello
388 | end my.db
389 }
390 {1 {Error: out of memory}}
391
392 2 {
393 | size 8192 pagesize 4096 filename my.db
394 | page 1 offset 2147483647
395 | 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.C
403 | 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 4096
405 | 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....hello
408 | end my.db
409 }
410 {0 {}}
411
412 3 {
413 | size 8192 pagesize 4096 filename my.db
414 | page 1 offset 0
415 | 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.C
423 | 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 4096
425 | 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....hello
428 | end my.db
429 }
430 {0 {}}
431
432 } {
433 set fd [open dump.txt w]
434 puts $fd [string trim $hexdump]
435 close $fd
436 do_test shell2-2.$tn.1 {
437 set rc [ catchcmd "" ".open --hexdb dump.txt"]
438 } $expect
439 }
440
441
442 finish_test