4 # The author disclaims copyright to this source code. In place of
5 # a legal notice, here is a blessing:
7 # May you do good and not evil.
8 # May you find forgiveness for yourself and forgive others.
9 # May you share freely, never taking more than you give.
11 #***********************************************************************
12 # This file implements regression tests for SQLite library. The
13 # focus of this script testing the callback-free C/C++ API.
15 # $Id: capi2.test,v 1.10 2003/08/05 13:13:38 drh Exp $
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
21 # Check basic functionality
25 set DB [sqlite db test.db]
26 execsql {CREATE TABLE t1(a,b,c)}
27 set VM [sqlite_compile $DB {SELECT name, rowid FROM sqlite_master} TAIL]
31 sqlite_step $VM N VALUES COLNAMES
41 } {name rowid text INTEGER}
46 sqlite_step $VM N VALUES COLNAMES
49 list $N $VALUES $COLNAMES
50 } {2 {} {name rowid text INTEGER}}
55 sqlite_step $VM N VALUES COLNAMES
58 list $N $VALUES $COLNAMES
64 # Check to make sure that the "tail" of a multi-statement SQL script
65 # is returned by sqlite_compile.
69 SELECT name, rowid FROM sqlite_master;
70 SELECT name, rowid FROM sqlite_temp_master;
71 -- A comment at the end
73 set VM [sqlite_compile $DB $SQL SQL]
76 SELECT name, rowid FROM sqlite_temp_master;
77 -- A comment at the end
80 set r [sqlite_step $VM n val colname]
81 lappend r $n $val $colname
82 } {SQLITE_ROW 2 {t1 1} {name rowid text INTEGER}}
84 set r [sqlite_step $VM n val colname]
85 lappend r $n $val $colname
86 } {SQLITE_DONE 2 {} {name rowid text INTEGER}}
91 set VM [sqlite_compile $DB $SQL SQL]
94 -- A comment at the end
97 set r [sqlite_step $VM n val colname]
98 lappend r $n $val $colname
99 } {SQLITE_DONE 2 {} {name rowid text INTEGER}}
104 set VM [sqlite_compile $DB $SQL SQL]
108 # Check the error handling.
112 sqlite_compile $DB {select bogus from sqlite_master} TAIL
114 lappend rc $msg $TAIL
115 } {1 {(1) no such column: bogus} {}}
118 sqlite_compile $DB {select bogus from } TAIL
120 lappend rc $msg $TAIL
121 } {1 {(1) near " ": syntax error} {}}
124 sqlite_compile $DB {;;;;select bogus from sqlite_master} TAIL
126 lappend rc $msg $TAIL
127 } {1 {(1) no such column: bogus} {}}
130 sqlite_compile $DB {select bogus from sqlite_master;x;} TAIL
132 lappend rc $msg $TAIL
133 } {1 {(1) no such column: bogus} {x;}}
136 sqlite_compile $DB {select bogus from sqlite_master;;;x;} TAIL
138 lappend rc $msg $TAIL
139 } {1 {(1) no such column: bogus} {;;x;}}
142 sqlite_compile $DB {select 5/0} TAIL
150 list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
151 } {SQLITE_ROW 1 {{}} {5/0 NUMERIC}}
156 execsql {CREATE UNIQUE INDEX i1 ON t1(a)}
157 set VM [sqlite_compile $DB {INSERT INTO t1 VALUES(1,2,3)} TAIL]
160 do_test capi2-3.9b {db changes} {0}
165 list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
166 } {SQLITE_DONE 0 {} {}}
167 do_test capi2-3.10b {db changes} {1}
171 do_test capi2-3.11b {db changes} {1}
173 list [catch {sqlite_finalize $VM} msg] [set msg]
174 } {1 {(21) library routine called out of sequence}}
176 set VM [sqlite_compile $DB {INSERT INTO t1 VALUES(1,3,4)} TAIL]
177 list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
178 } {SQLITE_ERROR 0 {} {}}
179 do_test capi2-3.13b {db changes} {0}
181 list [catch {sqlite_finalize $VM} msg] [set msg]
182 } {1 {(19) column a is not unique}}
184 set VM [sqlite_compile $DB {CREATE TABLE t2(a NOT NULL, b)} TAIL]
188 list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
189 } {SQLITE_DONE 0 {} {}}
191 list [catch {sqlite_finalize $VM} msg] [set msg]
194 set VM [sqlite_compile $DB {INSERT INTO t2 VALUES(NULL,2)} TAIL]
195 list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
196 } {SQLITE_ERROR 0 {} {}}
198 list [catch {sqlite_finalize $VM} msg] [set msg]
199 } {1 {(19) t2.a may not be NULL}}
201 # Two or more virtual machines exists at the same time.
204 set VM1 [sqlite_compile $DB {INSERT INTO t2 VALUES(1,2)} TAIL]
208 set VM2 [sqlite_compile $DB {INSERT INTO t2 VALUES(2,3)} TAIL]
212 set VM3 [sqlite_compile $DB {INSERT INTO t2 VALUES(3,4)} TAIL]
216 list [sqlite_step $VM2 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
217 } {SQLITE_DONE 0 {} {}}
219 execsql {SELECT * FROM t2 ORDER BY a}
222 list [catch {sqlite_finalize $VM2} msg] [set msg]
225 list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
226 } {SQLITE_DONE 0 {} {}}
228 execsql {SELECT * FROM t2 ORDER BY a}
231 list [catch {sqlite_finalize $VM3} msg] [set msg]
234 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
235 } {SQLITE_DONE 0 {} {}}
237 execsql {SELECT * FROM t2 ORDER BY a}
240 list [catch {sqlite_finalize $VM1} msg] [set msg]
243 # Interleaved SELECTs
246 set VM1 [sqlite_compile $DB {SELECT * FROM t2} TAIL]
247 set VM2 [sqlite_compile $DB {SELECT * FROM t2} TAIL]
248 set VM3 [sqlite_compile $DB {SELECT * FROM t2} TAIL]
249 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
250 } {SQLITE_ROW 2 {2 3} {a b {} {}}}
252 list [sqlite_step $VM2 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
253 } {SQLITE_ROW 2 {2 3} {a b {} {}}}
255 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
256 } {SQLITE_ROW 2 {3 4} {a b {} {}}}
258 list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
259 } {SQLITE_ROW 2 {2 3} {a b {} {}}}
261 list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
262 } {SQLITE_ROW 2 {3 4} {a b {} {}}}
264 list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
265 } {SQLITE_ROW 2 {1 2} {a b {} {}}}
267 list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
268 } {SQLITE_DONE 2 {} {a b {} {}}}
270 list [catch {sqlite_finalize $VM3} msg] [set msg]
273 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
274 } {SQLITE_ROW 2 {1 2} {a b {} {}}}
276 list [catch {sqlite_finalize $VM1} msg] [set msg]
279 list [sqlite_step $VM2 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
280 } {SQLITE_ROW 2 {3 4} {a b {} {}}}
282 list [sqlite_step $VM2 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
283 } {SQLITE_ROW 2 {1 2} {a b {} {}}}
285 list [catch {sqlite_finalize $VM2} msg] [set msg]
288 # Check for proper SQLITE_BUSY returns.
293 CREATE TABLE t3(x counter);
294 INSERT INTO t3 VALUES(1);
295 INSERT INTO t3 VALUES(2);
296 INSERT INTO t3 SELECT x+2 FROM t3;
297 INSERT INTO t3 SELECT x+4 FROM t3;
298 INSERT INTO t3 SELECT x+8 FROM t3;
301 set VM1 [sqlite_compile $DB {SELECT * FROM t3} TAIL]
306 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
307 } {SQLITE_BUSY 0 {} {}}
312 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
313 } {SQLITE_ROW 1 1 {x counter}}
316 } {1 {database is locked}}
318 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
319 } {SQLITE_ROW 1 2 {x counter}}
321 execsql {SELECT * FROM t2} db2
324 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
325 } {SQLITE_ROW 1 3 {x counter}}
327 execsql {SELECT * FROM t2}
330 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
331 } {SQLITE_ROW 1 4 {x counter}}
336 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
337 } {SQLITE_ROW 1 5 {x counter}}
338 # execsql {pragma vdbe_trace=on}
340 catchsql {UPDATE t3 SET x=x+1}
341 } {1 {database table is locked}}
343 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
344 } {SQLITE_ROW 1 6 {x counter}}
345 # puts [list [catch {sqlite_finalize $VM1} msg] [set msg]]; exit
347 execsql {SELECT * FROM t1}
350 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
351 } {SQLITE_ROW 1 7 {x counter}}
353 catchsql {UPDATE t1 SET b=b+1}
356 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
357 } {SQLITE_ROW 1 8 {x counter}}
359 execsql {SELECT * FROM t1}
362 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
363 } {SQLITE_ROW 1 9 {x counter}}
365 execsql {ROLLBACK; SELECT * FROM t1}
368 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
369 } {SQLITE_ROW 1 10 {x counter}}
371 execsql {BEGIN TRANSACTION ON CONFLICT ROLLBACK;}
374 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
375 } {SQLITE_ROW 1 11 {x counter}}
378 INSERT INTO t1 VALUES(2,3,4);
383 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
384 } {SQLITE_ROW 1 12 {x counter}}
387 INSERT INTO t1 VALUES(2,4,5);
390 } {1 {column a is not unique}}
392 list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME]
393 } {SQLITE_ROW 1 13 {x counter}}
395 list [catch {sqlite_finalize $VM1} msg] [set msg]
406 PRAGMA count_changes=on
411 UPDATE t1 SET a=a+10;
416 INSERT INTO t1 SELECT a+1,b+1,c+1 FROM t1;
419 do_test capi2-7.4b {db changes} {1}
422 UPDATE t1 SET a=a+10;
425 do_test capi2-7.5b {db changes} {2}
433 INSERT INTO t1 SELECT a+2,b+2,c+2 FROM t1;
443 } {0 21 2 3 22 3 4 23 4 5 24 5 6}
446 UPDATE t1 SET a=a-20;
449 } {0 4 1 2 3 2 3 4 3 4 5 4 5 6}
454 set x [stepsql $DB {EXPLAIN SELECT * FROM t1}]
458 # Ticket #261 - make sure we can finalize before the end of a query.
461 set VM1 [sqlite_compile $DB {SELECT * FROM t2} TAIL]
465 # Tickets #384 and #385 - make sure the TAIL argument to sqlite_compile
466 # and all of the return pointers in sqlite_step can be null.
469 set VM1 [sqlite_compile $DB {SELECT * FROM t2}]