1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# 2003 January 29 37c478bd9Sstevel@tonic-gate# 47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 67c478bd9Sstevel@tonic-gate# 77c478bd9Sstevel@tonic-gate# May you do good and not evil. 87c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 97c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate#*********************************************************************** 127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library. The 137c478bd9Sstevel@tonic-gate# focus of this script testing the callback-free C/C++ API. 147c478bd9Sstevel@tonic-gate# 157c478bd9Sstevel@tonic-gate# $Id: capi2.test,v 1.10 2003/08/05 13:13:38 drh Exp $ 167c478bd9Sstevel@tonic-gate# 177c478bd9Sstevel@tonic-gate 187c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 197c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 207c478bd9Sstevel@tonic-gate 217c478bd9Sstevel@tonic-gate# Check basic functionality 227c478bd9Sstevel@tonic-gate# 237c478bd9Sstevel@tonic-gatedo_test capi2-1.1 { 247c478bd9Sstevel@tonic-gate db close 257c478bd9Sstevel@tonic-gate set DB [sqlite db test.db] 267c478bd9Sstevel@tonic-gate execsql {CREATE TABLE t1(a,b,c)} 277c478bd9Sstevel@tonic-gate set VM [sqlite_compile $DB {SELECT name, rowid FROM sqlite_master} TAIL] 287c478bd9Sstevel@tonic-gate set TAIL 297c478bd9Sstevel@tonic-gate} {} 307c478bd9Sstevel@tonic-gatedo_test capi2-1.2 { 317c478bd9Sstevel@tonic-gate sqlite_step $VM N VALUES COLNAMES 327c478bd9Sstevel@tonic-gate} {SQLITE_ROW} 337c478bd9Sstevel@tonic-gatedo_test capi2-1.3 { 347c478bd9Sstevel@tonic-gate set N 357c478bd9Sstevel@tonic-gate} {2} 367c478bd9Sstevel@tonic-gatedo_test capi2-1.4 { 377c478bd9Sstevel@tonic-gate set VALUES 387c478bd9Sstevel@tonic-gate} {t1 1} 397c478bd9Sstevel@tonic-gatedo_test capi2-1.5 { 407c478bd9Sstevel@tonic-gate set COLNAMES 417c478bd9Sstevel@tonic-gate} {name rowid text INTEGER} 427c478bd9Sstevel@tonic-gatedo_test capi2-1.6 { 437c478bd9Sstevel@tonic-gate set N x 447c478bd9Sstevel@tonic-gate set VALUES y 457c478bd9Sstevel@tonic-gate set COLNAMES z 467c478bd9Sstevel@tonic-gate sqlite_step $VM N VALUES COLNAMES 477c478bd9Sstevel@tonic-gate} {SQLITE_DONE} 487c478bd9Sstevel@tonic-gatedo_test capi2-1.7 { 497c478bd9Sstevel@tonic-gate list $N $VALUES $COLNAMES 507c478bd9Sstevel@tonic-gate} {2 {} {name rowid text INTEGER}} 517c478bd9Sstevel@tonic-gatedo_test capi2-1.8 { 527c478bd9Sstevel@tonic-gate set N x 537c478bd9Sstevel@tonic-gate set VALUES y 547c478bd9Sstevel@tonic-gate set COLNAMES z 557c478bd9Sstevel@tonic-gate sqlite_step $VM N VALUES COLNAMES 567c478bd9Sstevel@tonic-gate} {SQLITE_MISUSE} 577c478bd9Sstevel@tonic-gatedo_test capi2-1.9 { 587c478bd9Sstevel@tonic-gate list $N $VALUES $COLNAMES 597c478bd9Sstevel@tonic-gate} {0 {} {}} 607c478bd9Sstevel@tonic-gatedo_test capi2-1.10 { 617c478bd9Sstevel@tonic-gate sqlite_finalize $VM 627c478bd9Sstevel@tonic-gate} {} 637c478bd9Sstevel@tonic-gate 647c478bd9Sstevel@tonic-gate# Check to make sure that the "tail" of a multi-statement SQL script 657c478bd9Sstevel@tonic-gate# is returned by sqlite_compile. 667c478bd9Sstevel@tonic-gate# 677c478bd9Sstevel@tonic-gatedo_test capi2-2.1 { 687c478bd9Sstevel@tonic-gate set SQL { 697c478bd9Sstevel@tonic-gate SELECT name, rowid FROM sqlite_master; 707c478bd9Sstevel@tonic-gate SELECT name, rowid FROM sqlite_temp_master; 717c478bd9Sstevel@tonic-gate -- A comment at the end 727c478bd9Sstevel@tonic-gate } 737c478bd9Sstevel@tonic-gate set VM [sqlite_compile $DB $SQL SQL] 747c478bd9Sstevel@tonic-gate set SQL 757c478bd9Sstevel@tonic-gate} { 767c478bd9Sstevel@tonic-gate SELECT name, rowid FROM sqlite_temp_master; 777c478bd9Sstevel@tonic-gate -- A comment at the end 787c478bd9Sstevel@tonic-gate } 797c478bd9Sstevel@tonic-gatedo_test capi2-2.2 { 807c478bd9Sstevel@tonic-gate set r [sqlite_step $VM n val colname] 817c478bd9Sstevel@tonic-gate lappend r $n $val $colname 827c478bd9Sstevel@tonic-gate} {SQLITE_ROW 2 {t1 1} {name rowid text INTEGER}} 837c478bd9Sstevel@tonic-gatedo_test capi2-2.3 { 847c478bd9Sstevel@tonic-gate set r [sqlite_step $VM n val colname] 857c478bd9Sstevel@tonic-gate lappend r $n $val $colname 867c478bd9Sstevel@tonic-gate} {SQLITE_DONE 2 {} {name rowid text INTEGER}} 877c478bd9Sstevel@tonic-gatedo_test capi2-2.4 { 887c478bd9Sstevel@tonic-gate sqlite_finalize $VM 897c478bd9Sstevel@tonic-gate} {} 907c478bd9Sstevel@tonic-gatedo_test capi2-2.5 { 917c478bd9Sstevel@tonic-gate set VM [sqlite_compile $DB $SQL SQL] 927c478bd9Sstevel@tonic-gate set SQL 937c478bd9Sstevel@tonic-gate} { 947c478bd9Sstevel@tonic-gate -- A comment at the end 957c478bd9Sstevel@tonic-gate } 967c478bd9Sstevel@tonic-gatedo_test capi2-2.6 { 977c478bd9Sstevel@tonic-gate set r [sqlite_step $VM n val colname] 987c478bd9Sstevel@tonic-gate lappend r $n $val $colname 997c478bd9Sstevel@tonic-gate} {SQLITE_DONE 2 {} {name rowid text INTEGER}} 1007c478bd9Sstevel@tonic-gatedo_test capi2-2.7 { 1017c478bd9Sstevel@tonic-gate sqlite_finalize $VM 1027c478bd9Sstevel@tonic-gate} {} 1037c478bd9Sstevel@tonic-gatedo_test capi2-2.8 { 1047c478bd9Sstevel@tonic-gate set VM [sqlite_compile $DB $SQL SQL] 1057c478bd9Sstevel@tonic-gate list $SQL $VM 1067c478bd9Sstevel@tonic-gate} {{} {}} 1077c478bd9Sstevel@tonic-gate 1087c478bd9Sstevel@tonic-gate# Check the error handling. 1097c478bd9Sstevel@tonic-gate# 1107c478bd9Sstevel@tonic-gatedo_test capi2-3.1 { 1117c478bd9Sstevel@tonic-gate set rc [catch { 1127c478bd9Sstevel@tonic-gate sqlite_compile $DB {select bogus from sqlite_master} TAIL 1137c478bd9Sstevel@tonic-gate } msg] 1147c478bd9Sstevel@tonic-gate lappend rc $msg $TAIL 1157c478bd9Sstevel@tonic-gate} {1 {(1) no such column: bogus} {}} 1167c478bd9Sstevel@tonic-gatedo_test capi2-3.2 { 1177c478bd9Sstevel@tonic-gate set rc [catch { 1187c478bd9Sstevel@tonic-gate sqlite_compile $DB {select bogus from } TAIL 1197c478bd9Sstevel@tonic-gate } msg] 1207c478bd9Sstevel@tonic-gate lappend rc $msg $TAIL 1217c478bd9Sstevel@tonic-gate} {1 {(1) near " ": syntax error} {}} 1227c478bd9Sstevel@tonic-gatedo_test capi2-3.3 { 1237c478bd9Sstevel@tonic-gate set rc [catch { 1247c478bd9Sstevel@tonic-gate sqlite_compile $DB {;;;;select bogus from sqlite_master} TAIL 1257c478bd9Sstevel@tonic-gate } msg] 1267c478bd9Sstevel@tonic-gate lappend rc $msg $TAIL 1277c478bd9Sstevel@tonic-gate} {1 {(1) no such column: bogus} {}} 1287c478bd9Sstevel@tonic-gatedo_test capi2-3.4 { 1297c478bd9Sstevel@tonic-gate set rc [catch { 1307c478bd9Sstevel@tonic-gate sqlite_compile $DB {select bogus from sqlite_master;x;} TAIL 1317c478bd9Sstevel@tonic-gate } msg] 1327c478bd9Sstevel@tonic-gate lappend rc $msg $TAIL 1337c478bd9Sstevel@tonic-gate} {1 {(1) no such column: bogus} {x;}} 1347c478bd9Sstevel@tonic-gatedo_test capi2-3.5 { 1357c478bd9Sstevel@tonic-gate set rc [catch { 1367c478bd9Sstevel@tonic-gate sqlite_compile $DB {select bogus from sqlite_master;;;x;} TAIL 1377c478bd9Sstevel@tonic-gate } msg] 1387c478bd9Sstevel@tonic-gate lappend rc $msg $TAIL 1397c478bd9Sstevel@tonic-gate} {1 {(1) no such column: bogus} {;;x;}} 1407c478bd9Sstevel@tonic-gatedo_test capi2-3.6 { 1417c478bd9Sstevel@tonic-gate set rc [catch { 1427c478bd9Sstevel@tonic-gate sqlite_compile $DB {select 5/0} TAIL 1437c478bd9Sstevel@tonic-gate } VM] 1447c478bd9Sstevel@tonic-gate lappend rc $TAIL 1457c478bd9Sstevel@tonic-gate} {0 {}} 1467c478bd9Sstevel@tonic-gatedo_test capi2-3.7 { 1477c478bd9Sstevel@tonic-gate set N {} 1487c478bd9Sstevel@tonic-gate set VALUE {} 1497c478bd9Sstevel@tonic-gate set COLNAME {} 1507c478bd9Sstevel@tonic-gate list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 1517c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 {{}} {5/0 NUMERIC}} 1527c478bd9Sstevel@tonic-gatedo_test capi2-3.8 { 1537c478bd9Sstevel@tonic-gate sqlite_finalize $VM 1547c478bd9Sstevel@tonic-gate} {} 1557c478bd9Sstevel@tonic-gatedo_test capi2-3.9 { 1567c478bd9Sstevel@tonic-gate execsql {CREATE UNIQUE INDEX i1 ON t1(a)} 1577c478bd9Sstevel@tonic-gate set VM [sqlite_compile $DB {INSERT INTO t1 VALUES(1,2,3)} TAIL] 1587c478bd9Sstevel@tonic-gate set TAIL 1597c478bd9Sstevel@tonic-gate} {} 1607c478bd9Sstevel@tonic-gatedo_test capi2-3.9b {db changes} {0} 1617c478bd9Sstevel@tonic-gatedo_test capi2-3.10 { 1627c478bd9Sstevel@tonic-gate set N {} 1637c478bd9Sstevel@tonic-gate set VALUE {} 1647c478bd9Sstevel@tonic-gate set COLNAME {} 1657c478bd9Sstevel@tonic-gate list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 1667c478bd9Sstevel@tonic-gate} {SQLITE_DONE 0 {} {}} 1677c478bd9Sstevel@tonic-gatedo_test capi2-3.10b {db changes} {1} 1687c478bd9Sstevel@tonic-gatedo_test capi2-3.11 { 1697c478bd9Sstevel@tonic-gate sqlite_finalize $VM 1707c478bd9Sstevel@tonic-gate} {} 1717c478bd9Sstevel@tonic-gatedo_test capi2-3.11b {db changes} {1} 1727c478bd9Sstevel@tonic-gatedo_test capi2-3.12 { 1737c478bd9Sstevel@tonic-gate list [catch {sqlite_finalize $VM} msg] [set msg] 1747c478bd9Sstevel@tonic-gate} {1 {(21) library routine called out of sequence}} 1757c478bd9Sstevel@tonic-gatedo_test capi2-3.13 { 1767c478bd9Sstevel@tonic-gate set VM [sqlite_compile $DB {INSERT INTO t1 VALUES(1,3,4)} TAIL] 1777c478bd9Sstevel@tonic-gate list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 1787c478bd9Sstevel@tonic-gate} {SQLITE_ERROR 0 {} {}} 1797c478bd9Sstevel@tonic-gatedo_test capi2-3.13b {db changes} {0} 1807c478bd9Sstevel@tonic-gatedo_test capi2-3.14 { 1817c478bd9Sstevel@tonic-gate list [catch {sqlite_finalize $VM} msg] [set msg] 1827c478bd9Sstevel@tonic-gate} {1 {(19) column a is not unique}} 1837c478bd9Sstevel@tonic-gatedo_test capi2-3.15 { 1847c478bd9Sstevel@tonic-gate set VM [sqlite_compile $DB {CREATE TABLE t2(a NOT NULL, b)} TAIL] 1857c478bd9Sstevel@tonic-gate set TAIL 1867c478bd9Sstevel@tonic-gate} {} 1877c478bd9Sstevel@tonic-gatedo_test capi2-3.16 { 1887c478bd9Sstevel@tonic-gate list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 1897c478bd9Sstevel@tonic-gate} {SQLITE_DONE 0 {} {}} 1907c478bd9Sstevel@tonic-gatedo_test capi2-3.17 { 1917c478bd9Sstevel@tonic-gate list [catch {sqlite_finalize $VM} msg] [set msg] 1927c478bd9Sstevel@tonic-gate} {0 {}} 1937c478bd9Sstevel@tonic-gatedo_test capi2-3.18 { 1947c478bd9Sstevel@tonic-gate set VM [sqlite_compile $DB {INSERT INTO t2 VALUES(NULL,2)} TAIL] 1957c478bd9Sstevel@tonic-gate list [sqlite_step $VM N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 1967c478bd9Sstevel@tonic-gate} {SQLITE_ERROR 0 {} {}} 1977c478bd9Sstevel@tonic-gatedo_test capi2-3.19 { 1987c478bd9Sstevel@tonic-gate list [catch {sqlite_finalize $VM} msg] [set msg] 1997c478bd9Sstevel@tonic-gate} {1 {(19) t2.a may not be NULL}} 2007c478bd9Sstevel@tonic-gate 2017c478bd9Sstevel@tonic-gate# Two or more virtual machines exists at the same time. 2027c478bd9Sstevel@tonic-gate# 2037c478bd9Sstevel@tonic-gatedo_test capi2-4.1 { 2047c478bd9Sstevel@tonic-gate set VM1 [sqlite_compile $DB {INSERT INTO t2 VALUES(1,2)} TAIL] 2057c478bd9Sstevel@tonic-gate set TAIL 2067c478bd9Sstevel@tonic-gate} {} 2077c478bd9Sstevel@tonic-gatedo_test capi2-4.2 { 2087c478bd9Sstevel@tonic-gate set VM2 [sqlite_compile $DB {INSERT INTO t2 VALUES(2,3)} TAIL] 2097c478bd9Sstevel@tonic-gate set TAIL 2107c478bd9Sstevel@tonic-gate} {} 2117c478bd9Sstevel@tonic-gatedo_test capi2-4.3 { 2127c478bd9Sstevel@tonic-gate set VM3 [sqlite_compile $DB {INSERT INTO t2 VALUES(3,4)} TAIL] 2137c478bd9Sstevel@tonic-gate set TAIL 2147c478bd9Sstevel@tonic-gate} {} 2157c478bd9Sstevel@tonic-gatedo_test capi2-4.4 { 2167c478bd9Sstevel@tonic-gate list [sqlite_step $VM2 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 2177c478bd9Sstevel@tonic-gate} {SQLITE_DONE 0 {} {}} 2187c478bd9Sstevel@tonic-gatedo_test capi2-4.5 { 2197c478bd9Sstevel@tonic-gate execsql {SELECT * FROM t2 ORDER BY a} 2207c478bd9Sstevel@tonic-gate} {2 3} 2217c478bd9Sstevel@tonic-gatedo_test capi2-4.6 { 2227c478bd9Sstevel@tonic-gate list [catch {sqlite_finalize $VM2} msg] [set msg] 223*1da57d55SToomas Soome} {0 {}} 2247c478bd9Sstevel@tonic-gatedo_test capi2-4.7 { 2257c478bd9Sstevel@tonic-gate list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 2267c478bd9Sstevel@tonic-gate} {SQLITE_DONE 0 {} {}} 2277c478bd9Sstevel@tonic-gatedo_test capi2-4.8 { 2287c478bd9Sstevel@tonic-gate execsql {SELECT * FROM t2 ORDER BY a} 2297c478bd9Sstevel@tonic-gate} {2 3 3 4} 2307c478bd9Sstevel@tonic-gatedo_test capi2-4.9 { 2317c478bd9Sstevel@tonic-gate list [catch {sqlite_finalize $VM3} msg] [set msg] 232*1da57d55SToomas Soome} {0 {}} 2337c478bd9Sstevel@tonic-gatedo_test capi2-4.10 { 2347c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 2357c478bd9Sstevel@tonic-gate} {SQLITE_DONE 0 {} {}} 2367c478bd9Sstevel@tonic-gatedo_test capi2-4.11 { 2377c478bd9Sstevel@tonic-gate execsql {SELECT * FROM t2 ORDER BY a} 2387c478bd9Sstevel@tonic-gate} {1 2 2 3 3 4} 2397c478bd9Sstevel@tonic-gatedo_test capi2-4.12 { 2407c478bd9Sstevel@tonic-gate list [catch {sqlite_finalize $VM1} msg] [set msg] 241*1da57d55SToomas Soome} {0 {}} 2427c478bd9Sstevel@tonic-gate 2437c478bd9Sstevel@tonic-gate# Interleaved SELECTs 2447c478bd9Sstevel@tonic-gate# 2457c478bd9Sstevel@tonic-gatedo_test capi2-5.1 { 2467c478bd9Sstevel@tonic-gate set VM1 [sqlite_compile $DB {SELECT * FROM t2} TAIL] 2477c478bd9Sstevel@tonic-gate set VM2 [sqlite_compile $DB {SELECT * FROM t2} TAIL] 2487c478bd9Sstevel@tonic-gate set VM3 [sqlite_compile $DB {SELECT * FROM t2} TAIL] 2497c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 2507c478bd9Sstevel@tonic-gate} {SQLITE_ROW 2 {2 3} {a b {} {}}} 2517c478bd9Sstevel@tonic-gatedo_test capi2-5.2 { 2527c478bd9Sstevel@tonic-gate list [sqlite_step $VM2 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 2537c478bd9Sstevel@tonic-gate} {SQLITE_ROW 2 {2 3} {a b {} {}}} 2547c478bd9Sstevel@tonic-gatedo_test capi2-5.3 { 2557c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 2567c478bd9Sstevel@tonic-gate} {SQLITE_ROW 2 {3 4} {a b {} {}}} 2577c478bd9Sstevel@tonic-gatedo_test capi2-5.4 { 2587c478bd9Sstevel@tonic-gate list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 2597c478bd9Sstevel@tonic-gate} {SQLITE_ROW 2 {2 3} {a b {} {}}} 2607c478bd9Sstevel@tonic-gatedo_test capi2-5.5 { 2617c478bd9Sstevel@tonic-gate list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 2627c478bd9Sstevel@tonic-gate} {SQLITE_ROW 2 {3 4} {a b {} {}}} 2637c478bd9Sstevel@tonic-gatedo_test capi2-5.6 { 2647c478bd9Sstevel@tonic-gate list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 2657c478bd9Sstevel@tonic-gate} {SQLITE_ROW 2 {1 2} {a b {} {}}} 2667c478bd9Sstevel@tonic-gatedo_test capi2-5.7 { 2677c478bd9Sstevel@tonic-gate list [sqlite_step $VM3 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 2687c478bd9Sstevel@tonic-gate} {SQLITE_DONE 2 {} {a b {} {}}} 2697c478bd9Sstevel@tonic-gatedo_test capi2-5.8 { 2707c478bd9Sstevel@tonic-gate list [catch {sqlite_finalize $VM3} msg] [set msg] 271*1da57d55SToomas Soome} {0 {}} 2727c478bd9Sstevel@tonic-gatedo_test capi2-5.9 { 2737c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 2747c478bd9Sstevel@tonic-gate} {SQLITE_ROW 2 {1 2} {a b {} {}}} 2757c478bd9Sstevel@tonic-gatedo_test capi2-5.10 { 2767c478bd9Sstevel@tonic-gate list [catch {sqlite_finalize $VM1} msg] [set msg] 277*1da57d55SToomas Soome} {0 {}} 2787c478bd9Sstevel@tonic-gatedo_test capi2-5.11 { 2797c478bd9Sstevel@tonic-gate list [sqlite_step $VM2 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 2807c478bd9Sstevel@tonic-gate} {SQLITE_ROW 2 {3 4} {a b {} {}}} 2817c478bd9Sstevel@tonic-gatedo_test capi2-5.12 { 2827c478bd9Sstevel@tonic-gate list [sqlite_step $VM2 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 2837c478bd9Sstevel@tonic-gate} {SQLITE_ROW 2 {1 2} {a b {} {}}} 2847c478bd9Sstevel@tonic-gatedo_test capi2-5.11 { 2857c478bd9Sstevel@tonic-gate list [catch {sqlite_finalize $VM2} msg] [set msg] 286*1da57d55SToomas Soome} {0 {}} 2877c478bd9Sstevel@tonic-gate 2887c478bd9Sstevel@tonic-gate# Check for proper SQLITE_BUSY returns. 2897c478bd9Sstevel@tonic-gate# 2907c478bd9Sstevel@tonic-gatedo_test capi2-6.1 { 2917c478bd9Sstevel@tonic-gate execsql { 2927c478bd9Sstevel@tonic-gate BEGIN; 2937c478bd9Sstevel@tonic-gate CREATE TABLE t3(x counter); 2947c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(1); 2957c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(2); 2967c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT x+2 FROM t3; 2977c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT x+4 FROM t3; 2987c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT x+8 FROM t3; 2997c478bd9Sstevel@tonic-gate COMMIT; 3007c478bd9Sstevel@tonic-gate } 3017c478bd9Sstevel@tonic-gate set VM1 [sqlite_compile $DB {SELECT * FROM t3} TAIL] 3027c478bd9Sstevel@tonic-gate sqlite db2 test.db 3037c478bd9Sstevel@tonic-gate execsql {BEGIN} db2 3047c478bd9Sstevel@tonic-gate} {} 3057c478bd9Sstevel@tonic-gatedo_test capi2-6.2 { 3067c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3077c478bd9Sstevel@tonic-gate} {SQLITE_BUSY 0 {} {}} 3087c478bd9Sstevel@tonic-gatedo_test capi2-6.3 { 3097c478bd9Sstevel@tonic-gate execsql {COMMIT} db2 3107c478bd9Sstevel@tonic-gate} {} 3117c478bd9Sstevel@tonic-gatedo_test capi2-6.4 { 3127c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3137c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 1 {x counter}} 3147c478bd9Sstevel@tonic-gatedo_test capi2-6.5 { 3157c478bd9Sstevel@tonic-gate catchsql {BEGIN} db2 3167c478bd9Sstevel@tonic-gate} {1 {database is locked}} 3177c478bd9Sstevel@tonic-gatedo_test capi2-6.6 { 3187c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3197c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 2 {x counter}} 3207c478bd9Sstevel@tonic-gatedo_test capi2-6.7 { 3217c478bd9Sstevel@tonic-gate execsql {SELECT * FROM t2} db2 3227c478bd9Sstevel@tonic-gate} {2 3 3 4 1 2} 3237c478bd9Sstevel@tonic-gatedo_test capi2-6.8 { 3247c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3257c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 3 {x counter}} 3267c478bd9Sstevel@tonic-gatedo_test capi2-6.9 { 327*1da57d55SToomas Soome execsql {SELECT * FROM t2} 3287c478bd9Sstevel@tonic-gate} {2 3 3 4 1 2} 3297c478bd9Sstevel@tonic-gatedo_test capi2-6.10 { 3307c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3317c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 4 {x counter}} 3327c478bd9Sstevel@tonic-gatedo_test capi2-6.11 { 3337c478bd9Sstevel@tonic-gate execsql {BEGIN} 3347c478bd9Sstevel@tonic-gate} {} 3357c478bd9Sstevel@tonic-gatedo_test capi2-6.12 { 3367c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3377c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 5 {x counter}} 3387c478bd9Sstevel@tonic-gate# execsql {pragma vdbe_trace=on} 3397c478bd9Sstevel@tonic-gatedo_test capi2-6.13 { 3407c478bd9Sstevel@tonic-gate catchsql {UPDATE t3 SET x=x+1} 3417c478bd9Sstevel@tonic-gate} {1 {database table is locked}} 3427c478bd9Sstevel@tonic-gatedo_test capi2-6.14 { 3437c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3447c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 6 {x counter}} 3457c478bd9Sstevel@tonic-gate# puts [list [catch {sqlite_finalize $VM1} msg] [set msg]]; exit 3467c478bd9Sstevel@tonic-gatedo_test capi2-6.15 { 3477c478bd9Sstevel@tonic-gate execsql {SELECT * FROM t1} 3487c478bd9Sstevel@tonic-gate} {1 2 3} 3497c478bd9Sstevel@tonic-gatedo_test capi2-6.16 { 3507c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3517c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 7 {x counter}} 3527c478bd9Sstevel@tonic-gatedo_test capi2-6.17 { 3537c478bd9Sstevel@tonic-gate catchsql {UPDATE t1 SET b=b+1} 3547c478bd9Sstevel@tonic-gate} {0 {}} 3557c478bd9Sstevel@tonic-gatedo_test capi2-6.18 { 3567c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3577c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 8 {x counter}} 3587c478bd9Sstevel@tonic-gatedo_test capi2-6.19 { 3597c478bd9Sstevel@tonic-gate execsql {SELECT * FROM t1} 3607c478bd9Sstevel@tonic-gate} {1 3 3} 3617c478bd9Sstevel@tonic-gatedo_test capi2-6.20 { 3627c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3637c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 9 {x counter}} 3647c478bd9Sstevel@tonic-gatedo_test capi2-6.21 { 3657c478bd9Sstevel@tonic-gate execsql {ROLLBACK; SELECT * FROM t1} 3667c478bd9Sstevel@tonic-gate} {1 2 3} 3677c478bd9Sstevel@tonic-gatedo_test capi2-6.22 { 3687c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3697c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 10 {x counter}} 3707c478bd9Sstevel@tonic-gatedo_test capi2-6.23 { 3717c478bd9Sstevel@tonic-gate execsql {BEGIN TRANSACTION ON CONFLICT ROLLBACK;} 3727c478bd9Sstevel@tonic-gate} {} 3737c478bd9Sstevel@tonic-gatedo_test capi2-6.24 { 3747c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3757c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 11 {x counter}} 3767c478bd9Sstevel@tonic-gatedo_test capi2-6.25 { 3777c478bd9Sstevel@tonic-gate execsql { 3787c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(2,3,4); 3797c478bd9Sstevel@tonic-gate SELECT * FROM t1; 3807c478bd9Sstevel@tonic-gate } 3817c478bd9Sstevel@tonic-gate} {1 2 3 2 3 4} 3827c478bd9Sstevel@tonic-gatedo_test capi2-6.26 { 3837c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3847c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 12 {x counter}} 3857c478bd9Sstevel@tonic-gatedo_test capi2-6.27 { 3867c478bd9Sstevel@tonic-gate catchsql { 3877c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(2,4,5); 3887c478bd9Sstevel@tonic-gate SELECT * FROM t1; 3897c478bd9Sstevel@tonic-gate } 3907c478bd9Sstevel@tonic-gate} {1 {column a is not unique}} 3917c478bd9Sstevel@tonic-gatedo_test capi2-6.28 { 3927c478bd9Sstevel@tonic-gate list [sqlite_step $VM1 N VALUE COLNAME] [set N] [set VALUE] [set COLNAME] 3937c478bd9Sstevel@tonic-gate} {SQLITE_ROW 1 13 {x counter}} 3947c478bd9Sstevel@tonic-gatedo_test capi2-6.99 { 3957c478bd9Sstevel@tonic-gate list [catch {sqlite_finalize $VM1} msg] [set msg] 3967c478bd9Sstevel@tonic-gate} {0 {}} 3977c478bd9Sstevel@tonic-gatecatchsql {ROLLBACK} 3987c478bd9Sstevel@tonic-gate 3997c478bd9Sstevel@tonic-gatedo_test capi2-7.1 { 4007c478bd9Sstevel@tonic-gate stepsql $DB { 4017c478bd9Sstevel@tonic-gate SELECT * FROM t1 4027c478bd9Sstevel@tonic-gate } 4037c478bd9Sstevel@tonic-gate} {0 1 2 3} 4047c478bd9Sstevel@tonic-gatedo_test capi2-7.2 { 4057c478bd9Sstevel@tonic-gate stepsql $DB { 4067c478bd9Sstevel@tonic-gate PRAGMA count_changes=on 4077c478bd9Sstevel@tonic-gate } 4087c478bd9Sstevel@tonic-gate} {0} 4097c478bd9Sstevel@tonic-gatedo_test capi2-7.3 { 4107c478bd9Sstevel@tonic-gate stepsql $DB { 4117c478bd9Sstevel@tonic-gate UPDATE t1 SET a=a+10; 4127c478bd9Sstevel@tonic-gate } 4137c478bd9Sstevel@tonic-gate} {0 1} 4147c478bd9Sstevel@tonic-gatedo_test capi2-7.4 { 4157c478bd9Sstevel@tonic-gate stepsql $DB { 4167c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT a+1,b+1,c+1 FROM t1; 4177c478bd9Sstevel@tonic-gate } 4187c478bd9Sstevel@tonic-gate} {0 1} 4197c478bd9Sstevel@tonic-gatedo_test capi2-7.4b {db changes} {1} 4207c478bd9Sstevel@tonic-gatedo_test capi2-7.5 { 4217c478bd9Sstevel@tonic-gate stepsql $DB { 4227c478bd9Sstevel@tonic-gate UPDATE t1 SET a=a+10; 4237c478bd9Sstevel@tonic-gate } 4247c478bd9Sstevel@tonic-gate} {0 2} 4257c478bd9Sstevel@tonic-gatedo_test capi2-7.5b {db changes} {2} 4267c478bd9Sstevel@tonic-gatedo_test capi2-7.6 { 4277c478bd9Sstevel@tonic-gate stepsql $DB { 4287c478bd9Sstevel@tonic-gate SELECT * FROM t1; 4297c478bd9Sstevel@tonic-gate } 4307c478bd9Sstevel@tonic-gate} {0 21 2 3 22 3 4} 4317c478bd9Sstevel@tonic-gatedo_test capi2-7.7 { 4327c478bd9Sstevel@tonic-gate stepsql $DB { 4337c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT a+2,b+2,c+2 FROM t1; 4347c478bd9Sstevel@tonic-gate } 4357c478bd9Sstevel@tonic-gate} {0 2} 4367c478bd9Sstevel@tonic-gatedo_test capi2-7.8 { 4377c478bd9Sstevel@tonic-gate db changes 4387c478bd9Sstevel@tonic-gate} {2} 4397c478bd9Sstevel@tonic-gatedo_test capi2-7.9 { 4407c478bd9Sstevel@tonic-gate stepsql $DB { 4417c478bd9Sstevel@tonic-gate SELECT * FROM t1; 4427c478bd9Sstevel@tonic-gate } 4437c478bd9Sstevel@tonic-gate} {0 21 2 3 22 3 4 23 4 5 24 5 6} 4447c478bd9Sstevel@tonic-gatedo_test capi2-7.10 { 4457c478bd9Sstevel@tonic-gate stepsql $DB { 4467c478bd9Sstevel@tonic-gate UPDATE t1 SET a=a-20; 4477c478bd9Sstevel@tonic-gate SELECT * FROM t1; 4487c478bd9Sstevel@tonic-gate } 4497c478bd9Sstevel@tonic-gate} {0 4 1 2 3 2 3 4 3 4 5 4 5 6} 4507c478bd9Sstevel@tonic-gatedo_test capi2-7.11 { 4517c478bd9Sstevel@tonic-gate db changes 4527c478bd9Sstevel@tonic-gate} {0} 4537c478bd9Sstevel@tonic-gatedo_test capi2-7.12 { 4547c478bd9Sstevel@tonic-gate set x [stepsql $DB {EXPLAIN SELECT * FROM t1}] 4557c478bd9Sstevel@tonic-gate lindex $x 0 4567c478bd9Sstevel@tonic-gate} {0} 4577c478bd9Sstevel@tonic-gate 4587c478bd9Sstevel@tonic-gate# Ticket #261 - make sure we can finalize before the end of a query. 4597c478bd9Sstevel@tonic-gate# 4607c478bd9Sstevel@tonic-gatedo_test capi2-8.1 { 4617c478bd9Sstevel@tonic-gate set VM1 [sqlite_compile $DB {SELECT * FROM t2} TAIL] 4627c478bd9Sstevel@tonic-gate sqlite_finalize $VM1 4637c478bd9Sstevel@tonic-gate} {} 464*1da57d55SToomas Soome 4657c478bd9Sstevel@tonic-gate# Tickets #384 and #385 - make sure the TAIL argument to sqlite_compile 4667c478bd9Sstevel@tonic-gate# and all of the return pointers in sqlite_step can be null. 4677c478bd9Sstevel@tonic-gate# 4687c478bd9Sstevel@tonic-gatedo_test capi2-9.1 { 4697c478bd9Sstevel@tonic-gate set VM1 [sqlite_compile $DB {SELECT * FROM t2}] 4707c478bd9Sstevel@tonic-gate sqlite_step $VM1 4717c478bd9Sstevel@tonic-gate sqlite_finalize $VM1 4727c478bd9Sstevel@tonic-gate} {} 4737c478bd9Sstevel@tonic-gate 4747c478bd9Sstevel@tonic-gatedb2 close 4757c478bd9Sstevel@tonic-gate 4767c478bd9Sstevel@tonic-gatefinish_test 477