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 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this script testing the callback-free C/C++ API.
14 # $Id: capi2.test,v 1.26 2005/03/29 03:11:00 danielk1977 Exp $
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 # Return the text values from the current row pointed at by STMT as a list.
21 proc get_row_values {STMT} {
23 for {set i 0} {$i < [sqlite3_data_count $STMT]} {incr i} {
24 lappend VALUES [sqlite3_column_text $STMT $i]
29 # Return the column names followed by declaration types for the result set
30 # of the SQL statement STMT.
33 # CREATE TABLE abc(a text, b integer);
36 # The result is {a b text integer}
37 proc get_column_names {STMT} {
39 for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
40 lappend VALUES [sqlite3_column_name $STMT $i]
42 for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
43 lappend VALUES [sqlite3_column_decltype $STMT $i]
48 # Check basic functionality
52 set DB [sqlite3 db test.db]
53 execsql {CREATE TABLE t1(a,b,c)}
54 set VM [sqlite3_prepare $DB {SELECT name, rowid FROM sqlite_master} -1 TAIL]
61 sqlite3_data_count $VM
68 } {name rowid text INTEGER}
73 list [sqlite3_column_count $VM] [get_row_values $VM] [get_column_names $VM]
74 } {2 {} {name rowid text INTEGER}}
79 # Update: In v2, once SQLITE_MISUSE is returned the statement handle cannot
80 # be interrogated for more information. However in v3, since the column
81 # count, names and types are determined at compile time, these are still
82 # accessible after an SQLITE_MISUSE error.
84 list [sqlite3_column_count $VM] [get_row_values $VM] [get_column_names $VM]
85 } {2 {} {name rowid text INTEGER}}
87 sqlite3_data_count $VM
94 # Check to make sure that the "tail" of a multi-statement SQL script
95 # is returned by sqlite3_prepare.
99 SELECT name, rowid FROM sqlite_master;
100 SELECT name, rowid FROM sqlite_master WHERE 0;
101 -- A comment at the end
103 set VM [sqlite3_prepare $DB $SQL -1 SQL]
106 SELECT name, rowid FROM sqlite_master WHERE 0;
107 -- A comment at the end
110 set r [sqlite3_step $VM]
111 lappend r [sqlite3_column_count $VM] \
112 [get_row_values $VM] \
113 [get_column_names $VM]
114 } {SQLITE_ROW 2 {t1 1} {name rowid text INTEGER}}
116 set r [sqlite3_step $VM]
117 lappend r [sqlite3_column_count $VM] \
118 [get_row_values $VM] \
119 [get_column_names $VM]
120 } {SQLITE_DONE 2 {} {name rowid text INTEGER}}
125 set VM [sqlite3_prepare $DB $SQL -1 SQL]
128 -- A comment at the end
131 set r [sqlite3_step $VM]
132 lappend r [sqlite3_column_count $VM] \
133 [get_row_values $VM] \
134 [get_column_names $VM]
135 } {SQLITE_DONE 2 {} {name rowid text INTEGER}}
140 set VM [sqlite3_prepare $DB $SQL -1 SQL]
144 # Check the error handling.
148 sqlite3_prepare $DB {select bogus from sqlite_master} -1 TAIL
150 lappend rc $msg $TAIL
151 } {1 {(1) no such column: bogus} {}}
154 sqlite3_prepare $DB {select bogus from } -1 TAIL
156 lappend rc $msg $TAIL
157 } {1 {(1) near " ": syntax error} {}}
160 sqlite3_prepare $DB {;;;;select bogus from sqlite_master} -1 TAIL
162 lappend rc $msg $TAIL
163 } {1 {(1) no such column: bogus} {}}
166 sqlite3_prepare $DB {select bogus from sqlite_master;x;} -1 TAIL
168 lappend rc $msg $TAIL
169 } {1 {(1) no such column: bogus} {x;}}
172 sqlite3_prepare $DB {select bogus from sqlite_master;;;x;} -1 TAIL
174 lappend rc $msg $TAIL
175 } {1 {(1) no such column: bogus} {;;x;}}
178 sqlite3_prepare $DB {select 5/0} -1 TAIL
183 list [sqlite3_step $VM] \
184 [sqlite3_column_count $VM] \
185 [get_row_values $VM] \
186 [get_column_names $VM]
187 } {SQLITE_ROW 1 {{}} {5/0 {}}}
192 execsql {CREATE UNIQUE INDEX i1 ON t1(a)}
193 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(1,2,3)} -1 TAIL]
196 do_test capi2-3.9b {db changes} {0}
198 list [sqlite3_step $VM] \
199 [sqlite3_column_count $VM] \
200 [get_row_values $VM] \
201 [get_column_names $VM]
202 } {SQLITE_DONE 0 {} {}}
204 # Update for v3 - the change has not actually happened until the query is
205 # finalized. Is this going to cause trouble for anyone? Lee Nelson maybe?
206 # (Later:) The change now happens just before SQLITE_DONE is returned.
207 do_test capi2-3.10b {db changes} {1}
211 do_test capi2-3.11b {db changes} {1}
216 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(1,3,4)} -1 TAIL]
217 list [sqlite3_step $VM] \
218 [sqlite3_column_count $VM] \
219 [get_row_values $VM] \
220 [get_column_names $VM]
221 } {SQLITE_ERROR 0 {} {}}
223 # Update for v3: Preparing a statement does not affect the change counter.
224 # (Test result changes from 0 to 1). (Later:) change counter updates occur
225 # when sqlite3_step returns, not at finalize time.
226 do_test capi2-3.13b {db changes} {0}
229 list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]
230 } {SQLITE_CONSTRAINT {column a is not unique}}
232 set VM [sqlite3_prepare $DB {CREATE TABLE t2(a NOT NULL, b)} -1 TAIL]
236 list [sqlite3_step $VM] \
237 [sqlite3_column_count $VM] \
238 [get_row_values $VM] \
239 [get_column_names $VM]
240 } {SQLITE_DONE 0 {} {}}
242 list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]
243 } {SQLITE_OK {not an error}}
245 set VM [sqlite3_prepare $DB {INSERT INTO t2 VALUES(NULL,2)} -1 TAIL]
246 list [sqlite3_step $VM] \
247 [sqlite3_column_count $VM] \
248 [get_row_values $VM] \
249 [get_column_names $VM]
250 } {SQLITE_ERROR 0 {} {}}
252 list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]
253 } {SQLITE_CONSTRAINT {t2.a may not be NULL}}
257 CREATE TABLE a1(message_id, name , UNIQUE(message_id, name) );
258 INSERT INTO a1 VALUES(1, 1);
262 set VM [sqlite3_prepare $DB {INSERT INTO a1 VALUES(1, 1)} -1 TAIL]
270 } {SQLITE_CONSTRAINT}
273 } {SQLITE_CONSTRAINT}
275 # Two or more virtual machines exists at the same time.
278 set VM1 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(1,2)} -1 TAIL]
282 set VM2 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(2,3)} -1 TAIL]
286 set VM3 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(3,4)} -1 TAIL]
290 list [sqlite3_step $VM2] \
291 [sqlite3_column_count $VM2] \
292 [get_row_values $VM2] \
293 [get_column_names $VM2]
294 } {SQLITE_DONE 0 {} {}}
296 execsql {SELECT * FROM t2 ORDER BY a}
299 sqlite3_finalize $VM2
302 list [sqlite3_step $VM3] \
303 [sqlite3_column_count $VM3] \
304 [get_row_values $VM3] \
305 [get_column_names $VM3]
306 } {SQLITE_DONE 0 {} {}}
308 execsql {SELECT * FROM t2 ORDER BY a}
311 sqlite3_finalize $VM3
314 list [sqlite3_step $VM1] \
315 [sqlite3_column_count $VM1] \
316 [get_row_values $VM1] \
317 [get_column_names $VM1]
318 } {SQLITE_DONE 0 {} {}}
320 execsql {SELECT * FROM t2 ORDER BY a}
323 sqlite3_finalize $VM1
326 # Interleaved SELECTs
329 set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
330 set VM2 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
331 set VM3 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
332 list [sqlite3_step $VM1] \
333 [sqlite3_column_count $VM1] \
334 [get_row_values $VM1] \
335 [get_column_names $VM1]
336 } {SQLITE_ROW 2 {2 3} {a b {} {}}}
338 list [sqlite3_step $VM2] \
339 [sqlite3_column_count $VM2] \
340 [get_row_values $VM2] \
341 [get_column_names $VM2]
342 } {SQLITE_ROW 2 {2 3} {a b {} {}}}
344 list [sqlite3_step $VM1] \
345 [sqlite3_column_count $VM1] \
346 [get_row_values $VM1] \
347 [get_column_names $VM1]
348 } {SQLITE_ROW 2 {3 4} {a b {} {}}}
350 list [sqlite3_step $VM3] \
351 [sqlite3_column_count $VM3] \
352 [get_row_values $VM3] \
353 [get_column_names $VM3]
354 } {SQLITE_ROW 2 {2 3} {a b {} {}}}
356 list [sqlite3_step $VM3] \
357 [sqlite3_column_count $VM3] \
358 [get_row_values $VM3] \
359 [get_column_names $VM3]
360 } {SQLITE_ROW 2 {3 4} {a b {} {}}}
362 list [sqlite3_step $VM3] \
363 [sqlite3_column_count $VM3] \
364 [get_row_values $VM3] \
365 [get_column_names $VM3]
366 } {SQLITE_ROW 2 {1 2} {a b {} {}}}
368 list [sqlite3_step $VM3] \
369 [sqlite3_column_count $VM3] \
370 [get_row_values $VM3] \
371 [get_column_names $VM3]
372 } {SQLITE_DONE 2 {} {a b {} {}}}
374 sqlite3_finalize $VM3
377 list [sqlite3_step $VM1] \
378 [sqlite3_column_count $VM1] \
379 [get_row_values $VM1] \
380 [get_column_names $VM1]
381 } {SQLITE_ROW 2 {1 2} {a b {} {}}}
383 sqlite3_finalize $VM1
386 list [sqlite3_step $VM2] \
387 [sqlite3_column_count $VM2] \
388 [get_row_values $VM2] \
389 [get_column_names $VM2]
390 } {SQLITE_ROW 2 {3 4} {a b {} {}}}
392 list [sqlite3_step $VM2] \
393 [sqlite3_column_count $VM2] \
394 [get_row_values $VM2] \
395 [get_column_names $VM2]
396 } {SQLITE_ROW 2 {1 2} {a b {} {}}}
398 sqlite3_finalize $VM2
401 # Check for proper SQLITE_BUSY returns.
406 CREATE TABLE t3(x counter);
407 INSERT INTO t3 VALUES(1);
408 INSERT INTO t3 VALUES(2);
409 INSERT INTO t3 SELECT x+2 FROM t3;
410 INSERT INTO t3 SELECT x+4 FROM t3;
411 INSERT INTO t3 SELECT x+8 FROM t3;
414 set VM1 [sqlite3_prepare $DB {SELECT * FROM t3} -1 TAIL]
418 # Update for v3: BEGIN doesn't write-lock the database. It is quite
419 # difficult to get v3 to write-lock the database, which causes a few
420 # problems for test scripts.
422 # do_test capi2-6.2 {
423 # list [sqlite3_step $VM1] \
424 # [sqlite3_column_count $VM1] \
425 # [get_row_values $VM1] \
426 # [get_column_names $VM1]
427 # } {SQLITE_BUSY 0 {} {}}
432 list [sqlite3_step $VM1] \
433 [sqlite3_column_count $VM1] \
434 [get_row_values $VM1] \
435 [get_column_names $VM1]
436 } {SQLITE_ROW 1 1 {x counter}}
438 catchsql {INSERT INTO t3 VALUES(10);} db2
439 } {1 {database is locked}}
441 list [sqlite3_step $VM1] \
442 [sqlite3_column_count $VM1] \
443 [get_row_values $VM1] \
444 [get_column_names $VM1]
445 } {SQLITE_ROW 1 2 {x counter}}
447 execsql {SELECT * FROM t2} db2
450 list [sqlite3_step $VM1] \
451 [sqlite3_column_count $VM1] \
452 [get_row_values $VM1] \
453 [get_column_names $VM1]
454 } {SQLITE_ROW 1 3 {x counter}}
456 execsql {SELECT * FROM t2}
459 list [sqlite3_step $VM1] \
460 [sqlite3_column_count $VM1] \
461 [get_row_values $VM1] \
462 [get_column_names $VM1]
463 } {SQLITE_ROW 1 4 {x counter}}
468 list [sqlite3_step $VM1] \
469 [sqlite3_column_count $VM1] \
470 [get_row_values $VM1] \
471 [get_column_names $VM1]
472 } {SQLITE_ROW 1 5 {x counter}}
475 catchsql {UPDATE t3 SET x=x+1}
476 } {1 {database table is locked}}
478 list [sqlite3_step $VM1] \
479 [sqlite3_column_count $VM1] \
480 [get_row_values $VM1] \
481 [get_column_names $VM1]
482 } {SQLITE_ROW 1 6 {x counter}}
484 execsql {SELECT * FROM t1}
487 list [sqlite3_step $VM1] \
488 [sqlite3_column_count $VM1] \
489 [get_row_values $VM1] \
490 [get_column_names $VM1]
491 } {SQLITE_ROW 1 7 {x counter}}
493 catchsql {UPDATE t1 SET b=b+1}
496 list [sqlite3_step $VM1] \
497 [sqlite3_column_count $VM1] \
498 [get_row_values $VM1] \
499 [get_column_names $VM1]
500 } {SQLITE_ROW 1 8 {x counter}}
502 execsql {SELECT * FROM t1}
505 list [sqlite3_step $VM1] \
506 [sqlite3_column_count $VM1] \
507 [get_row_values $VM1] \
508 [get_column_names $VM1]
509 } {SQLITE_ROW 1 9 {x counter}}
510 #do_test capi2-6.21 {
511 # execsql {ROLLBACK; SELECT * FROM t1}
514 list [sqlite3_step $VM1] \
515 [sqlite3_column_count $VM1] \
516 [get_row_values $VM1] \
517 [get_column_names $VM1]
518 } {SQLITE_ROW 1 10 {x counter}}
519 #do_test capi2-6.23 {
520 # execsql {BEGIN TRANSACTION;}
523 list [sqlite3_step $VM1] \
524 [sqlite3_column_count $VM1] \
525 [get_row_values $VM1] \
526 [get_column_names $VM1]
527 } {SQLITE_ROW 1 11 {x counter}}
530 INSERT INTO t1 VALUES(2,3,4);
535 list [sqlite3_step $VM1] \
536 [sqlite3_column_count $VM1] \
537 [get_row_values $VM1] \
538 [get_column_names $VM1]
539 } {SQLITE_ROW 1 12 {x counter}}
542 INSERT INTO t1 VALUES(2,4,5);
545 } {1 {column a is not unique}}
547 list [sqlite3_step $VM1] \
548 [sqlite3_column_count $VM1] \
549 [get_row_values $VM1] \
550 [get_column_names $VM1]
551 } {SQLITE_ROW 1 13 {x counter}}
553 sqlite3_finalize $VM1
564 PRAGMA count_changes=on
569 UPDATE t1 SET a=a+10;
574 INSERT INTO t1 SELECT a+1,b+1,c+1 FROM t1;
577 do_test capi2-7.4b {sqlite3_changes $DB} {1}
580 UPDATE t1 SET a=a+10;
583 do_test capi2-7.5b {sqlite3_changes $DB} {2}
591 INSERT INTO t1 SELECT a+2,b+2,c+2 FROM t1;
601 } {0 21 2 3 22 3 4 23 4 5 24 5 6}
604 UPDATE t1 SET a=a-20;
607 } {0 4 1 2 3 2 3 4 3 4 5 4 5 6}
609 # Update for version 3: A SELECT statement no longer resets the change
610 # counter (Test result changes from 0 to 4).
614 do_test capi2-7.11a {
615 execsql {SELECT count(*) FROM t1}
618 ifcapable {explain} {
621 set x [stepsql $DB {EXPLAIN SELECT * FROM t1}]
626 # Ticket #261 - make sure we can finalize before the end of a query.
629 set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
630 sqlite3_finalize $VM1
633 # Tickets #384 and #385 - make sure the TAIL argument to sqlite3_prepare
634 # and all of the return pointers in sqlite_step can be null.
637 set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 DUMMY]
639 sqlite3_finalize $VM1