# 2003 January 29 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this script testing the callback-free C/C++ API. # # $Id: capi3.test,v 1.55 2007/08/29 19:15:09 drh Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl # Return the UTF-16 representation of the supplied UTF-8 string $str. # If $nt is true, append two 0x00 bytes as a nul terminator. proc utf16 {str {nt 1}} { set r [encoding convertto unicode $str] if {$nt} { append r "\x00\x00" } return $r } # Return the UTF-8 representation of the supplied UTF-16 string $str. proc utf8 {str} { # If $str ends in two 0x00 0x00 bytes, knock these off before # converting to UTF-8 using TCL. binary scan $str \c* vals if {[lindex $vals end]==0 && [lindex $vals end-1]==0} { set str [binary format \c* [lrange $vals 0 end-2]] } set r [encoding convertfrom unicode $str] return $r } # These tests complement those in capi2.test. They are organized # as follows: # # capi3-1.*: Test sqlite3_prepare # capi3-2.*: Test sqlite3_prepare16 # capi3-3.*: Test sqlite3_open # capi3-4.*: Test sqlite3_open16 # capi3-5.*: Test the various sqlite3_result_* APIs # capi3-6.*: Test that sqlite3_close fails if there are outstanding VMs. # set DB [sqlite3_connection_pointer db] do_test capi3-1.0 { sqlite3_get_autocommit $DB } 1 do_test capi3-1.1 { set STMT [sqlite3_prepare $DB {SELECT name FROM sqlite_master} -1 TAIL] sqlite3_finalize $STMT set TAIL } {} do_test capi3-1.2 { sqlite3_errcode $DB } {SQLITE_OK} do_test capi3-1.3 { sqlite3_errmsg $DB } {not an error} do_test capi3-1.4 { set sql {SELECT name FROM sqlite_master;SELECT 10} set STMT [sqlite3_prepare $DB $sql -1 TAIL] sqlite3_finalize $STMT set TAIL } {SELECT 10} do_test capi3-1.5 { set sql {SELECT namex FROM sqlite_master} catch { set STMT [sqlite3_prepare $DB $sql -1 TAIL] } } {1} do_test capi3-1.6 { sqlite3_errcode $DB } {SQLITE_ERROR} do_test capi3-1.7 { sqlite3_errmsg $DB } {no such column: namex} ifcapable {utf16} { do_test capi3-2.1 { set sql16 [utf16 {SELECT name FROM sqlite_master}] set STMT [sqlite3_prepare16 $DB $sql16 -1 ::TAIL] sqlite3_finalize $STMT utf8 $::TAIL } {} do_test capi3-2.2 { set sql [utf16 {SELECT name FROM sqlite_master;SELECT 10}] set STMT [sqlite3_prepare16 $DB $sql -1 TAIL] sqlite3_finalize $STMT utf8 $TAIL } {SELECT 10} do_test capi3-2.3 { set sql [utf16 {SELECT namex FROM sqlite_master}] catch { set STMT [sqlite3_prepare16 $DB $sql -1 TAIL] } } {1} do_test capi3-2.4 { sqlite3_errcode $DB } {SQLITE_ERROR} do_test capi3-2.5 { sqlite3_errmsg $DB } {no such column: namex} ifcapable schema_pragmas { do_test capi3-2.6 { execsql {CREATE TABLE tablename(x)} set sql16 [utf16 {PRAGMA table_info("TableName")}] set STMT [sqlite3_prepare16 $DB $sql16 -1 TAIL] sqlite3_step $STMT } SQLITE_ROW do_test capi3-2.7 { sqlite3_step $STMT } SQLITE_DONE do_test capi3-2.8 { sqlite3_finalize $STMT } SQLITE_OK } } ;# endif utf16 # rename sqlite3_open sqlite3_open_old # proc sqlite3_open {fname options} {sqlite3_open_new $fname $options} do_test capi3-3.1 { set db2 [sqlite3_open test.db {}] sqlite3_errcode $db2 } {SQLITE_OK} # FIX ME: Should test the db handle works. do_test capi3-3.2 { sqlite3_close $db2 } {SQLITE_OK} do_test capi3-3.3 { catch { set db2 [sqlite3_open /bogus/path/test.db {}] } sqlite3_errcode $db2 } {SQLITE_CANTOPEN} do_test capi3-3.4 { sqlite3_errmsg $db2 } {unable to open database file} do_test capi3-3.5 { sqlite3_close $db2 } {SQLITE_OK} do_test capi3-3.6.1-misuse { sqlite3_close $db2 } {SQLITE_MISUSE} do_test capi3-3.6.2-misuse { sqlite3_errmsg $db2 } {library routine called out of sequence} ifcapable {utf16} { do_test capi3-3.6.3-misuse { utf8 [sqlite3_errmsg16 $db2] } {library routine called out of sequence} } # rename sqlite3_open "" # rename sqlite3_open_old sqlite3_open ifcapable {utf16} { do_test capi3-4.1 { set db2 [sqlite3_open16 [utf16 test.db] {}] sqlite3_errcode $db2 } {SQLITE_OK} # FIX ME: Should test the db handle works. do_test capi3-4.2 { sqlite3_close $db2 } {SQLITE_OK} do_test capi3-4.3 { catch { set db2 [sqlite3_open16 [utf16 /bogus/path/test.db] {}] } sqlite3_errcode $db2 } {SQLITE_CANTOPEN} do_test capi3-4.4 { utf8 [sqlite3_errmsg16 $db2] } {unable to open database file} do_test capi3-4.5 { sqlite3_close $db2 } {SQLITE_OK} } ;# utf16 # This proc is used to test the following API calls: # # sqlite3_column_count # sqlite3_column_name # sqlite3_column_name16 # sqlite3_column_decltype # sqlite3_column_decltype16 # # $STMT is a compiled SQL statement. $test is a prefix # to use for test names within this proc. $names is a list # of the column names that should be returned by $STMT. # $decltypes is a list of column declaration types for $STMT. # # Example: # # set STMT [sqlite3_prepare "SELECT 1, 2, 2;" -1 DUMMY] # check_header test1.1 {1 2 3} {"" "" ""} # proc check_header {STMT test names decltypes} { # Use the return value of sqlite3_column_count() to build # a list of column indexes. i.e. If sqlite3_column_count # is 3, build the list {0 1 2}. set ::idxlist [list] set ::numcols [sqlite3_column_count $STMT] for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i} # Column names in UTF-8 do_test $test.1 { set cnamelist [list] foreach i $idxlist {lappend cnamelist [sqlite3_column_name $STMT $i]} set cnamelist } $names # Column names in UTF-16 ifcapable {utf16} { do_test $test.2 { set cnamelist [list] foreach i $idxlist { lappend cnamelist [utf8 [sqlite3_column_name16 $STMT $i]] } set cnamelist } $names } # Column names in UTF-8 do_test $test.3 { set cnamelist [list] foreach i $idxlist {lappend cnamelist [sqlite3_column_name $STMT $i]} set cnamelist } $names # Column names in UTF-16 ifcapable {utf16} { do_test $test.4 { set cnamelist [list] foreach i $idxlist { lappend cnamelist [utf8 [sqlite3_column_name16 $STMT $i]] } set cnamelist } $names } # Column names in UTF-8 do_test $test.5 { set cnamelist [list] foreach i $idxlist {lappend cnamelist [sqlite3_column_decltype $STMT $i]} set cnamelist } $decltypes # Column declaration types in UTF-16 ifcapable {utf16} { do_test $test.6 { set cnamelist [list] foreach i $idxlist { lappend cnamelist [utf8 [sqlite3_column_decltype16 $STMT $i]] } set cnamelist } $decltypes } # Test some out of range conditions: ifcapable {utf16} { do_test $test.7 { list \ [sqlite3_column_name $STMT -1] \ [sqlite3_column_name16 $STMT -1] \ [sqlite3_column_decltype $STMT -1] \ [sqlite3_column_decltype16 $STMT -1] \ [sqlite3_column_name $STMT $numcols] \ [sqlite3_column_name16 $STMT $numcols] \ [sqlite3_column_decltype $STMT $numcols] \ [sqlite3_column_decltype16 $STMT $numcols] } {{} {} {} {} {} {} {} {}} } } # This proc is used to test the following API calls: # # sqlite3_column_origin_name # sqlite3_column_origin_name16 # sqlite3_column_table_name # sqlite3_column_table_name16 # sqlite3_column_database_name # sqlite3_column_database_name16 # # $STMT is a compiled SQL statement. $test is a prefix # to use for test names within this proc. $names is a list # of the column names that should be returned by $STMT. # $decltypes is a list of column declaration types for $STMT. # # Example: # # set STMT [sqlite3_prepare "SELECT 1, 2, 2;" -1 DUMMY] # check_header test1.1 {1 2 3} {"" "" ""} # proc check_origin_header {STMT test dbs tables cols} { # If sqlite3_column_origin_name() and friends are not compiled into # this build, this proc is a no-op. ifcapable columnmetadata { # Use the return value of sqlite3_column_count() to build # a list of column indexes. i.e. If sqlite3_column_count # is 3, build the list {0 1 2}. set ::idxlist [list] set ::numcols [sqlite3_column_count $STMT] for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i} # Database names in UTF-8 do_test $test.8 { set cnamelist [list] foreach i $idxlist { lappend cnamelist [sqlite3_column_database_name $STMT $i] } set cnamelist } $dbs # Database names in UTF-16 ifcapable {utf16} { do_test $test.9 { set cnamelist [list] foreach i $idxlist { lappend cnamelist [utf8 [sqlite3_column_database_name16 $STMT $i]] } set cnamelist } $dbs } # Table names in UTF-8 do_test $test.10 { set cnamelist [list] foreach i $idxlist { lappend cnamelist [sqlite3_column_table_name $STMT $i] } set cnamelist } $tables # Table names in UTF-16 ifcapable {utf16} { do_test $test.11 { set cnamelist [list] foreach i $idxlist { lappend cnamelist [utf8 [sqlite3_column_table_name16 $STMT $i]] } set cnamelist } $tables } # Origin names in UTF-8 do_test $test.12 { set cnamelist [list] foreach i $idxlist { lappend cnamelist [sqlite3_column_origin_name $STMT $i] } set cnamelist } $cols # Origin declaration types in UTF-16 ifcapable {utf16} { do_test $test.13 { set cnamelist [list] foreach i $idxlist { lappend cnamelist [utf8 [sqlite3_column_origin_name16 $STMT $i]] } set cnamelist } $cols } } } # This proc is used to test the following APIs: # # sqlite3_data_count # sqlite3_column_type # sqlite3_column_int # sqlite3_column_text # sqlite3_column_text16 # sqlite3_column_double # # $STMT is a compiled SQL statement for which the previous call # to sqlite3_step returned SQLITE_ROW. $test is a prefix to use # for test names within this proc. $types is a list of the # manifest types for the current row. $ints, $doubles and $strings # are lists of the integer, real and string representations of # the values in the current row. # # Example: # # set STMT [sqlite3_prepare "SELECT 'hello', 1.1, NULL" -1 DUMMY] # sqlite3_step $STMT # check_data test1.2 {TEXT REAL NULL} {0 1 0} {0 1.1 0} {hello 1.1 {}} # proc check_data {STMT test types ints doubles strings} { # Use the return value of sqlite3_column_count() to build # a list of column indexes. i.e. If sqlite3_column_count # is 3, build the list {0 1 2}. set ::idxlist [list] set numcols [sqlite3_data_count $STMT] for {set i 0} {$i < $numcols} {incr i} {lappend ::idxlist $i} # types do_test $test.1 { set types [list] foreach i $idxlist {lappend types [sqlite3_column_type $STMT $i]} set types } $types # Integers do_test $test.2 { set ints [list] foreach i $idxlist {lappend ints [sqlite3_column_int64 $STMT $i]} set ints } $ints # bytes set lens [list] foreach i $::idxlist { lappend lens [string length [lindex $strings $i]] } do_test $test.3 { set bytes [list] set lens [list] foreach i $idxlist { lappend bytes [sqlite3_column_bytes $STMT $i] } set bytes } $lens # bytes16 ifcapable {utf16} { set lens [list] foreach i $::idxlist { lappend lens [expr 2 * [string length [lindex $strings $i]]] } do_test $test.4 { set bytes [list] set lens [list] foreach i $idxlist { lappend bytes [sqlite3_column_bytes16 $STMT $i] } set bytes } $lens } # Blob do_test $test.5 { set utf8 [list] foreach i $idxlist {lappend utf8 [sqlite3_column_blob $STMT $i]} set utf8 } $strings # UTF-8 do_test $test.6 { set utf8 [list] foreach i $idxlist {lappend utf8 [sqlite3_column_text $STMT $i]} set utf8 } $strings # Floats do_test $test.7 { set utf8 [list] foreach i $idxlist {lappend utf8 [sqlite3_column_double $STMT $i]} set utf8 } $doubles # UTF-16 ifcapable {utf16} { do_test $test.8 { set utf8 [list] foreach i $idxlist {lappend utf8 [utf8 [sqlite3_column_text16 $STMT $i]]} set utf8 } $strings } # Integers do_test $test.9 { set ints [list] foreach i $idxlist {lappend ints [sqlite3_column_int $STMT $i]} set ints } $ints # Floats do_test $test.10 { set utf8 [list] foreach i $idxlist {lappend utf8 [sqlite3_column_double $STMT $i]} set utf8 } $doubles # UTF-8 do_test $test.11 { set utf8 [list] foreach i $idxlist {lappend utf8 [sqlite3_column_text $STMT $i]} set utf8 } $strings # Types do_test $test.12 { set types [list] foreach i $idxlist {lappend types [sqlite3_column_type $STMT $i]} set types } $types # Test that an out of range request returns the equivalent of NULL do_test $test.13 { sqlite3_column_int $STMT -1 } {0} do_test $test.13 { sqlite3_column_text $STMT -1 } {} } ifcapable !floatingpoint { finish_test return } do_test capi3-5.0 { execsql { CREATE TABLE t1(a VARINT, b BLOB, c VARCHAR(16)); INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES('one', 'two', NULL); INSERT INTO t1 VALUES(1.2, 1.3, 1.4); } set sql "SELECT * FROM t1" set STMT [sqlite3_prepare $DB $sql -1 TAIL] sqlite3_column_count $STMT } 3 check_header $STMT capi3-5.1 {a b c} {VARINT BLOB VARCHAR(16)} check_origin_header $STMT capi3-5.1 {main main main} {t1 t1 t1} {a b c} do_test capi3-5.2 { sqlite3_step $STMT } SQLITE_ROW check_header $STMT capi3-5.3 {a b c} {VARINT BLOB VARCHAR(16)} check_origin_header $STMT capi3-5.3 {main main main} {t1 t1 t1} {a b c} check_data $STMT capi3-5.4 {INTEGER INTEGER TEXT} {1 2 3} {1.0 2.0 3.0} {1 2 3} do_test capi3-5.5 { sqlite3_step $STMT } SQLITE_ROW check_header $STMT capi3-5.6 {a b c} {VARINT BLOB VARCHAR(16)} check_origin_header $STMT capi3-5.6 {main main main} {t1 t1 t1} {a b c} check_data $STMT capi3-5.7 {TEXT TEXT NULL} {0 0 0} {0.0 0.0 0.0} {one two {}} do_test capi3-5.8 { sqlite3_step $STMT } SQLITE_ROW check_header $STMT capi3-5.9 {a b c} {VARINT BLOB VARCHAR(16)} check_origin_header $STMT capi3-5.9 {main main main} {t1 t1 t1} {a b c} check_data $STMT capi3-5.10 {FLOAT FLOAT TEXT} {1 1 1} {1.2 1.3 1.4} {1.2 1.3 1.4} do_test capi3-5.11 { sqlite3_step $STMT } SQLITE_DONE do_test capi3-5.12 { sqlite3_finalize $STMT } SQLITE_OK do_test capi3-5.20 { set sql "SELECT a, sum(b), max(c) FROM t1 GROUP BY a" set STMT [sqlite3_prepare $DB $sql -1 TAIL] sqlite3_column_count $STMT } 3 check_header $STMT capi3-5.21 {a sum(b) max(c)} {VARINT {} {}} check_origin_header $STMT capi3-5.22 {main {} {}} {t1 {} {}} {a {} {}} do_test capi3-5.23 { sqlite3_finalize $STMT } SQLITE_OK do_test capi3-5.30 { set sql "SELECT a AS x, sum(b) AS y, max(c) AS z FROM t1 AS m GROUP BY x" set STMT [sqlite3_prepare $DB $sql -1 TAIL] sqlite3_column_count $STMT } 3 check_header $STMT capi3-5.31 {x y z} {VARINT {} {}} check_origin_header $STMT capi3-5.32 {main {} {}} {t1 {} {}} {a {} {}} do_test capi3-5.33 { sqlite3_finalize $STMT } SQLITE_OK set ::ENC [execsql {pragma encoding}] db close do_test capi3-6.0 { sqlite3 db test.db set DB [sqlite3_connection_pointer db] sqlite3_key $DB xyzzy set sql {SELECT a FROM t1 order by rowid} set STMT [sqlite3_prepare $DB $sql -1 TAIL] expr 0 } {0} do_test capi3-6.1 { db cache flush sqlite3_close $DB } {SQLITE_BUSY} do_test capi3-6.2 { sqlite3_step $STMT } {SQLITE_ROW} check_data $STMT capi3-6.3 {INTEGER} {1} {1.0} {1} do_test capi3-6.3 { sqlite3_finalize $STMT } {SQLITE_OK} do_test capi3-6.4-misuse { db cache flush sqlite3_close $DB } {SQLITE_OK} db close if {![sqlite3 -has-codec]} { # Test what happens when the library encounters a newer file format. # Do this by updating the file format via the btree layer. do_test capi3-7.1 { set ::bt [btree_open test.db 10 0] btree_begin_transaction $::bt set meta [btree_get_meta $::bt] lset meta 2 5 eval [concat btree_update_meta $::bt [lrange $meta 0 end]] btree_commit $::bt btree_close $::bt } {} do_test capi3-7.2 { sqlite3 db test.db catchsql { SELECT * FROM sqlite_master; } } {1 {unsupported file format}} db close } if {![sqlite3 -has-codec]} { # Now test that the library correctly handles bogus entries in the # sqlite_master table (schema corruption). do_test capi3-8.1 { file delete -force test.db file delete -force test.db-journal sqlite3 db test.db execsql { CREATE TABLE t1(a); } db close } {} do_test capi3-8.2 { set ::bt [btree_open test.db 10 0] btree_begin_transaction $::bt set ::bc [btree_cursor $::bt 1 1] # Build a 5-field row record consisting of 5 null records. This is # officially black magic. catch {unset data} set data [binary format c6 {6 0 0 0 0 0}] btree_insert $::bc 5 $data btree_close_cursor $::bc btree_commit $::bt btree_close $::bt } {} do_test capi3-8.3 { sqlite3 db test.db catchsql { SELECT * FROM sqlite_master; } } {1 {malformed database schema}} do_test capi3-8.4 { set ::bt [btree_open test.db 10 0] btree_begin_transaction $::bt set ::bc [btree_cursor $::bt 1 1] # Build a 5-field row record. The first field is a string 'table', and # subsequent fields are all NULL. Replace the other broken record with # this one and try to read the schema again. The broken record uses # either UTF-8 or native UTF-16 (if this file is being run by # utf16.test). if { [string match UTF-16* $::ENC] } { set data [binary format c6a10 {6 33 0 0 0 0} [utf16 table]] } else { set data [binary format c6a5 {6 23 0 0 0 0} table] } btree_insert $::bc 5 $data btree_close_cursor $::bc btree_commit $::bt btree_close $::bt } {}; do_test capi3-8.5 { db close sqlite3 db test.db catchsql { SELECT * FROM sqlite_master; } } {1 {malformed database schema}} db close } file delete -force test.db file delete -force test.db-journal # Test the english language string equivalents for sqlite error codes set code2english [list \ SQLITE_OK {not an error} \ SQLITE_ERROR {SQL logic error or missing database} \ SQLITE_PERM {access permission denied} \ SQLITE_ABORT {callback requested query abort} \ SQLITE_BUSY {database is locked} \ SQLITE_LOCKED {database table is locked} \ SQLITE_NOMEM {out of memory} \ SQLITE_READONLY {attempt to write a readonly database} \ SQLITE_INTERRUPT {interrupted} \ SQLITE_IOERR {disk I/O error} \ SQLITE_CORRUPT {database disk image is malformed} \ SQLITE_FULL {database or disk is full} \ SQLITE_CANTOPEN {unable to open database file} \ SQLITE_EMPTY {table contains no data} \ SQLITE_SCHEMA {database schema has changed} \ SQLITE_CONSTRAINT {constraint failed} \ SQLITE_MISMATCH {datatype mismatch} \ SQLITE_MISUSE {library routine called out of sequence} \ SQLITE_NOLFS {kernel lacks large file support} \ SQLITE_AUTH {authorization denied} \ SQLITE_FORMAT {auxiliary database format error} \ SQLITE_RANGE {bind or column index out of range} \ SQLITE_NOTADB {file is encrypted or is not a database} \ unknownerror {unknown error} \ ] set test_number 1 foreach {code english} $code2english { do_test capi3-9.$test_number "sqlite3_test_errstr $code" $english incr test_number } # Test the error message when a "real" out of memory occurs. ifcapable memdebug { do_test capi3-10-1 { sqlite3 db test.db set DB [sqlite3_connection_pointer db] sqlite3_memdebug_fail 1 catchsql { select * from sqlite_master; } } {1 {out of memory}} do_test capi3-10-2 { sqlite3_errmsg $::DB } {out of memory} ifcapable {utf16} { do_test capi3-10-3 { utf8 [sqlite3_errmsg16 $::DB] } {out of memory} } db close sqlite3_memdebug_fail -1 } # The following tests - capi3-11.* - test that a COMMIT or ROLLBACK # statement issued while there are still outstanding VMs that are part of # the transaction fails. sqlite3 db test.db set DB [sqlite3_connection_pointer db] sqlite_register_test_function $DB func do_test capi3-11.1 { execsql { BEGIN; CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 'int'); INSERT INTO t1 VALUES(2, 'notatype'); } } {} do_test capi3-11.1.1 { sqlite3_get_autocommit $DB } 0 do_test capi3-11.2 { set STMT [sqlite3_prepare $DB "SELECT func(b, a) FROM t1" -1 TAIL] sqlite3_step $STMT } {SQLITE_ROW} do_test capi3-11.3 { catchsql { COMMIT; } } {1 {cannot commit transaction - SQL statements in progress}} do_test capi3-11.3.1 { sqlite3_get_autocommit $DB } 0 do_test capi3-11.4 { sqlite3_step $STMT } {SQLITE_ERROR} do_test capi3-11.5 { sqlite3_finalize $STMT } {SQLITE_ERROR} do_test capi3-11.6 { catchsql { SELECT * FROM t1; } } {0 {1 int 2 notatype}} do_test capi3-11.6.1 { sqlite3_get_autocommit $DB } 0 do_test capi3-11.7 { catchsql { COMMIT; } } {0 {}} do_test capi3-11.7.1 { sqlite3_get_autocommit $DB } 1 do_test capi3-11.8 { execsql { CREATE TABLE t2(a); INSERT INTO t2 VALUES(1); INSERT INTO t2 VALUES(2); BEGIN; INSERT INTO t2 VALUES(3); } } {} do_test capi3-11.8.1 { sqlite3_get_autocommit $DB } 0 do_test capi3-11.9 { set STMT [sqlite3_prepare $DB "SELECT a FROM t2" -1 TAIL] sqlite3_step $STMT } {SQLITE_ROW} do_test capi3-11.9.1 { sqlite3_get_autocommit $DB } 0 do_test capi3-11.9.2 { catchsql { ROLLBACK; } } {1 {cannot rollback transaction - SQL statements in progress}} do_test capi3-11.9.3 { sqlite3_get_autocommit $DB } 0 do_test capi3-11.10 { sqlite3_step $STMT } {SQLITE_ROW} do_test capi3-11.11 { sqlite3_step $STMT } {SQLITE_ROW} do_test capi3-11.12 { sqlite3_step $STMT } {SQLITE_DONE} do_test capi3-11.13 { sqlite3_finalize $STMT } {SQLITE_OK} do_test capi3-11.14 { execsql { SELECT a FROM t2; } } {1 2 3} do_test capi3-11.14.1 { sqlite3_get_autocommit $DB } 0 do_test capi3-11.15 { catchsql { ROLLBACK; } } {0 {}} do_test capi3-11.15.1 { sqlite3_get_autocommit $DB } 1 do_test capi3-11.16 { execsql { SELECT a FROM t2; } } {1 2} # Sanity check on the definition of 'outstanding VM'. This means any VM # that has had sqlite3_step() called more recently than sqlite3_finalize() or # sqlite3_reset(). So a VM that has just been prepared or reset does not # count as an active VM. do_test capi3-11.17 { execsql { BEGIN; } } {} do_test capi3-11.18 { set STMT [sqlite3_prepare $DB "SELECT a FROM t1" -1 TAIL] catchsql { COMMIT; } } {0 {}} do_test capi3-11.19 { sqlite3_step $STMT } {SQLITE_ROW} do_test capi3-11.20 { catchsql { BEGIN; COMMIT; } } {1 {cannot commit transaction - SQL statements in progress}} do_test capi3-11.20 { sqlite3_reset $STMT catchsql { COMMIT; } } {0 {}} do_test capi3-11.21 { sqlite3_finalize $STMT } {SQLITE_OK} # The following tests - capi3-12.* - check that it's Ok to start a # transaction while other VMs are active, and that it's Ok to execute # atomic updates in the same situation # do_test capi3-12.1 { set STMT [sqlite3_prepare $DB "SELECT a FROM t2" -1 TAIL] sqlite3_step $STMT } {SQLITE_ROW} do_test capi3-12.2 { catchsql { INSERT INTO t1 VALUES(3, NULL); } } {0 {}} do_test capi3-12.3 { catchsql { INSERT INTO t2 VALUES(4); } } {0 {}} do_test capi3-12.4 { catchsql { BEGIN; INSERT INTO t1 VALUES(4, NULL); } } {0 {}} do_test capi3-12.5 { sqlite3_step $STMT } {SQLITE_ROW} do_test capi3-12.5.1 { sqlite3_step $STMT } {SQLITE_ROW} do_test capi3-12.6 { sqlite3_step $STMT } {SQLITE_DONE} do_test capi3-12.7 { sqlite3_finalize $STMT } {SQLITE_OK} do_test capi3-12.8 { execsql { COMMIT; SELECT a FROM t1; } } {1 2 3 4} # Test cases capi3-13.* test the sqlite3_clear_bindings() and # sqlite3_sleep APIs. # if {[llength [info commands sqlite3_clear_bindings]]>0} { do_test capi3-13.1 { execsql { DELETE FROM t1; } set STMT [sqlite3_prepare $DB "INSERT INTO t1 VALUES(?, ?)" -1 TAIL] sqlite3_step $STMT } {SQLITE_DONE} do_test capi3-13.2 { sqlite3_reset $STMT sqlite3_bind_text $STMT 1 hello 5 sqlite3_bind_text $STMT 2 world 5 sqlite3_step $STMT } {SQLITE_DONE} do_test capi3-13.3 { sqlite3_reset $STMT sqlite3_clear_bindings $STMT sqlite3_step $STMT } {SQLITE_DONE} do_test capi3-13-4 { sqlite3_finalize $STMT execsql { SELECT * FROM t1; } } {{} {} hello world {} {}} } if {[llength [info commands sqlite3_sleep]]>0} { do_test capi3-13-5 { set ms [sqlite3_sleep 80] expr {$ms==80 || $ms==1000} } {1} } # Ticket #1219: Make sure binding APIs can handle a NULL pointer. # do_test capi3-14.1-misuse { set rc [catch {sqlite3_bind_text 0 1 hello 5} msg] lappend rc $msg } {1 SQLITE_MISUSE} # Ticket #1650: Honor the nBytes parameter to sqlite3_prepare. # do_test capi3-15.1 { set sql {SELECT * FROM t2} set nbytes [string length $sql] append sql { WHERE a==1} set STMT [sqlite3_prepare $DB $sql $nbytes TAIL] sqlite3_step $STMT sqlite3_column_int $STMT 0 } {1} do_test capi3-15.2 { sqlite3_step $STMT sqlite3_column_int $STMT 0 } {2} do_test capi3-15.3 { sqlite3_finalize $STMT } {SQLITE_OK} # Make sure code is always generated even if an IF EXISTS or # IF NOT EXISTS clause is present that the table does not or # does exists. That way we will always have a prepared statement # to expire when the schema changes. # do_test capi3-16.1 { set sql {DROP TABLE IF EXISTS t3} set STMT [sqlite3_prepare $DB $sql -1 TAIL] sqlite3_finalize $STMT expr {$STMT!=""} } {1} do_test capi3-16.2 { set sql {CREATE TABLE IF NOT EXISTS t1(x,y)} set STMT [sqlite3_prepare $DB $sql -1 TAIL] sqlite3_finalize $STMT expr {$STMT!=""} } {1} # But still we do not generate code if there is no SQL # do_test capi3-16.3 { set STMT [sqlite3_prepare $DB {} -1 TAIL] sqlite3_finalize $STMT expr {$STMT==""} } {1} do_test capi3-16.4 { set STMT [sqlite3_prepare $DB {;} -1 TAIL] sqlite3_finalize $STMT expr {$STMT==""} } {1} # Ticket #2426: Misuse of sqlite3_column_* by calling it after # a sqlite3_reset should be harmless. # do_test capi3-17.1 { set STMT [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL] sqlite3_step $STMT sqlite3_column_int $STMT 0 } {1} do_test capi3-17.2 { sqlite3_reset $STMT sqlite3_column_int $STMT 0 } {0} do_test capi3-17.3 { sqlite3_finalize $STMT } {SQLITE_OK} finish_test