# 2006 November 08 # # 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. # # This is a copy of the capi3.test file that has been adapted to # test the new sqlite3_prepare_v2 interface. # # $Id: capi3c.test,v 1.19 2008/05/05 16:56:35 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: # # capi3c-1.*: Test sqlite3_prepare_v2 # capi3c-2.*: Test sqlite3_prepare16_v2 # capi3c-3.*: Test sqlite3_open # capi3c-4.*: Test sqlite3_open16 # capi3c-5.*: Test the various sqlite3_result_* APIs # capi3c-6.*: Test that sqlite3_close fails if there are outstanding VMs. # set DB [sqlite3_connection_pointer db] do_test capi3c-1.0 { sqlite3_get_autocommit $DB } 1 do_test capi3c-1.1 { set STMT [sqlite3_prepare_v2 $DB {SELECT name FROM sqlite_master} -1 TAIL] sqlite3_finalize $STMT set TAIL } {} do_test capi3c-1.2 { sqlite3_errcode $DB } {SQLITE_OK} do_test capi3c-1.3 { sqlite3_errmsg $DB } {not an error} do_test capi3c-1.4 { set sql {SELECT name FROM sqlite_master;SELECT 10} set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL] sqlite3_finalize $STMT set TAIL } {SELECT 10} do_test capi3c-1.5 { set sql {SELECT namex FROM sqlite_master} catch { set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL] } } {1} do_test capi3c-1.6 { sqlite3_errcode $DB } {SQLITE_ERROR} do_test capi3c-1.7 { sqlite3_errmsg $DB } {no such column: namex} ifcapable {utf16} { do_test capi3c-2.1 { set sql16 [utf16 {SELECT name FROM sqlite_master}] set STMT [sqlite3_prepare16_v2 $DB $sql16 -1 ::TAIL] sqlite3_finalize $STMT utf8 $::TAIL } {} do_test capi3c-2.2 { set sql [utf16 {SELECT name FROM sqlite_master;SELECT 10}] set STMT [sqlite3_prepare16_v2 $DB $sql -1 TAIL] sqlite3_finalize $STMT utf8 $TAIL } {SELECT 10} do_test capi3c-2.3 { set sql [utf16 {SELECT namex FROM sqlite_master}] catch { set STMT [sqlite3_prepare16_v2 $DB $sql -1 TAIL] } } {1} do_test capi3c-2.4 { sqlite3_errcode $DB } {SQLITE_ERROR} do_test capi3c-2.5 { sqlite3_errmsg $DB } {no such column: namex} ifcapable schema_pragmas { do_test capi3c-2.6 { execsql {CREATE TABLE tablename(x)} set sql16 [utf16 {PRAGMA table_info("TableName")}] set STMT [sqlite3_prepare16_v2 $DB $sql16 -1 TAIL] sqlite3_step $STMT } SQLITE_ROW do_test capi3c-2.7 { sqlite3_step $STMT } SQLITE_DONE do_test capi3c-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 capi3c-3.1 { set db2 [sqlite3_open test.db {}] sqlite3_errcode $db2 } {SQLITE_OK} # FIX ME: Should test the db handle works. do_test capi3c-3.2 { sqlite3_close $db2 } {SQLITE_OK} do_test capi3c-3.3 { catch { set db2 [sqlite3_open /bogus/path/test.db {}] } sqlite3_errcode $db2 } {SQLITE_CANTOPEN} do_test capi3c-3.4 { sqlite3_errmsg $db2 } {unable to open database file} do_test capi3c-3.5 { sqlite3_close $db2 } {SQLITE_OK} do_test capi3c-3.6.1-misuse { sqlite3_close $db2 } {SQLITE_MISUSE} do_test capi3c-3.6.2-misuse { sqlite3_errmsg $db2 } {library routine called out of sequence} ifcapable {utf16} { do_test capi3c-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 capi3c-4.1 { set db2 [sqlite3_open16 [utf16 test.db] {}] sqlite3_errcode $db2 } {SQLITE_OK} # FIX ME: Should test the db handle works. do_test capi3c-4.2 { sqlite3_close $db2 } {SQLITE_OK} do_test capi3c-4.3 { catch { set db2 [sqlite3_open16 [utf16 /bogus/path/test.db] {}] } sqlite3_errcode $db2 } {SQLITE_CANTOPEN} do_test capi3c-4.4 { utf8 [sqlite3_errmsg16 $db2] } {unable to open database file} do_test capi3c-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_v2 "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_v2 "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_v2 "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 capi3c-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_v2 $DB $sql -1 TAIL] sqlite3_column_count $STMT } 3 check_header $STMT capi3c-5.1 {a b c} {VARINT BLOB VARCHAR(16)} check_origin_header $STMT capi3c-5.1 {main main main} {t1 t1 t1} {a b c} do_test capi3c-5.2 { sqlite3_step $STMT } SQLITE_ROW check_header $STMT capi3c-5.3 {a b c} {VARINT BLOB VARCHAR(16)} check_origin_header $STMT capi3c-5.3 {main main main} {t1 t1 t1} {a b c} check_data $STMT capi3c-5.4 {INTEGER INTEGER TEXT} {1 2 3} {1.0 2.0 3.0} {1 2 3} do_test capi3c-5.5 { sqlite3_step $STMT } SQLITE_ROW check_header $STMT capi3c-5.6 {a b c} {VARINT BLOB VARCHAR(16)} check_origin_header $STMT capi3c-5.6 {main main main} {t1 t1 t1} {a b c} check_data $STMT capi3c-5.7 {TEXT TEXT NULL} {0 0 0} {0.0 0.0 0.0} {one two {}} do_test capi3c-5.8 { sqlite3_step $STMT } SQLITE_ROW check_header $STMT capi3c-5.9 {a b c} {VARINT BLOB VARCHAR(16)} check_origin_header $STMT capi3c-5.9 {main main main} {t1 t1 t1} {a b c} check_data $STMT capi3c-5.10 {FLOAT FLOAT TEXT} {1 1 1} {1.2 1.3 1.4} {1.2 1.3 1.4} do_test capi3c-5.11 { sqlite3_step $STMT } SQLITE_DONE do_test capi3c-5.12 { sqlite3_finalize $STMT } SQLITE_OK do_test capi3c-5.20 { set sql "SELECT a, sum(b), max(c) FROM t1 GROUP BY a" set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL] sqlite3_column_count $STMT } 3 check_header $STMT capi3c-5.21 {a sum(b) max(c)} {VARINT {} {}} check_origin_header $STMT capi3c-5.22 {main {} {}} {t1 {} {}} {a {} {}} do_test capi3c-5.23 { sqlite3_finalize $STMT } SQLITE_OK set ::ENC [execsql {pragma encoding}] db close do_test capi3c-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_v2 $DB $sql -1 TAIL] expr 0 } {0} do_test capi3c-6.1 { db cache flush sqlite3_close $DB } {SQLITE_BUSY} do_test capi3c-6.2 { sqlite3_step $STMT } {SQLITE_ROW} check_data $STMT capi3c-6.3 {INTEGER} {1} {1.0} {1} do_test capi3c-6.3 { sqlite3_finalize $STMT } {SQLITE_OK} do_test capi3c-6.4 { db cache flush sqlite3_close $DB } {SQLITE_OK} do_test capi3c-6.99-misuse { db close } {} # This procedure sets the value of the file-format in file 'test.db' # to $newval. Also, the schema cookie is incremented. # proc set_file_format {newval} { hexio_write test.db 44 [hexio_render_int32 $newval] set schemacookie [hexio_get_int [hexio_read test.db 40 4]] incr schemacookie hexio_write test.db 40 [hexio_render_int32 $schemacookie] return {} } # This procedure returns the value of the file-format in file 'test.db'. # proc get_file_format {{fname test.db}} { return [hexio_get_int [hexio_read $fname 44 4]] } if {![sqlite3 -has-codec]} { # Test what happens when the library encounters a newer file format. do_test capi3c-7.1 { set_file_format 5 } {} do_test capi3c-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 capi3c-8.1 { file delete -force test.db test.db-journal sqlite3 db test.db execsql { CREATE TABLE t1(a); } db close } {} do_test capi3c-8.2 { sqlite3 db test.db execsql { PRAGMA writable_schema=ON; INSERT INTO sqlite_master VALUES(NULL,NULL,NULL,NULL,NULL); } db close } {} do_test capi3c-8.3 { sqlite3 db test.db catchsql { SELECT * FROM sqlite_master; } } {1 {malformed database schema (?)}} do_test capi3c-8.4 { # Build a 5-field row record. The first field is a string 'table', and # subsequent fields are all NULL. db close file delete -force test.db test.db-journal sqlite3 db test.db execsql { CREATE TABLE t1(a); PRAGMA writable_schema=ON; INSERT INTO sqlite_master VALUES('table',NULL,NULL,NULL,NULL); } db close } {}; do_test capi3c-8.5 { 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 {large file support is disabled} \ 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 capi3c-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 capi3c-10-1 { sqlite3 db test.db set DB [sqlite3_connection_pointer db] sqlite3_memdebug_fail 0 catchsql { select * from sqlite_master; } } {1 {out of memory}} do_test capi3c-10-2 { sqlite3_errmsg $::DB } {out of memory} ifcapable {utf16} { do_test capi3c-10-3 { utf8 [sqlite3_errmsg16 $::DB] } {out of memory} } db close sqlite3_memdebug_fail -1 } # The following tests - capi3c-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 capi3c-11.1 { execsql { BEGIN; CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 'int'); INSERT INTO t1 VALUES(2, 'notatype'); } } {} do_test capi3c-11.1.1 { sqlite3_get_autocommit $DB } 0 do_test capi3c-11.2 { set STMT [sqlite3_prepare_v2 $DB "SELECT func(b, a) FROM t1" -1 TAIL] sqlite3_step $STMT } {SQLITE_ROW} do_test capi3c-11.3 { catchsql { COMMIT; } } {1 {cannot commit transaction - SQL statements in progress}} do_test capi3c-11.3.1 { sqlite3_get_autocommit $DB } 0 do_test capi3c-11.4 { sqlite3_step $STMT } {SQLITE_ERROR} do_test capi3c-11.5 { sqlite3_finalize $STMT } {SQLITE_ERROR} do_test capi3c-11.6 { catchsql { SELECT * FROM t1; } } {0 {1 int 2 notatype}} do_test capi3c-11.6.1 { sqlite3_get_autocommit $DB } 0 do_test capi3c-11.7 { catchsql { COMMIT; } } {0 {}} do_test capi3c-11.7.1 { sqlite3_get_autocommit $DB } 1 do_test capi3c-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 capi3c-11.8.1 { sqlite3_get_autocommit $DB } 0 do_test capi3c-11.9 { set STMT [sqlite3_prepare_v2 $DB "SELECT a FROM t2" -1 TAIL] sqlite3_step $STMT } {SQLITE_ROW} do_test capi3c-11.9.1 { sqlite3_get_autocommit $DB } 0 do_test capi3c-11.9.2 { catchsql { ROLLBACK; } } {1 {cannot rollback transaction - SQL statements in progress}} do_test capi3c-11.9.3 { sqlite3_get_autocommit $DB } 0 do_test capi3c-11.10 { sqlite3_step $STMT } {SQLITE_ROW} do_test capi3c-11.11 { sqlite3_step $STMT } {SQLITE_ROW} do_test capi3c-11.12 { sqlite3_step $STMT } {SQLITE_DONE} do_test capi3c-11.13 { sqlite3_finalize $STMT } {SQLITE_OK} do_test capi3c-11.14 { execsql { SELECT a FROM t2; } } {1 2 3} do_test capi3c-11.14.1 { sqlite3_get_autocommit $DB } 0 do_test capi3c-11.15 { catchsql { ROLLBACK; } } {0 {}} do_test capi3c-11.15.1 { sqlite3_get_autocommit $DB } 1 do_test capi3c-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 capi3c-11.17 { execsql { BEGIN; } } {} do_test capi3c-11.18 { set STMT [sqlite3_prepare_v2 $DB "SELECT a FROM t1" -1 TAIL] catchsql { COMMIT; } } {0 {}} do_test capi3c-11.19 { sqlite3_step $STMT } {SQLITE_ROW} do_test capi3c-11.20 { catchsql { BEGIN; COMMIT; } } {1 {cannot commit transaction - SQL statements in progress}} do_test capi3c-11.20 { sqlite3_reset $STMT catchsql { COMMIT; } } {0 {}} do_test capi3c-11.21 { sqlite3_finalize $STMT } {SQLITE_OK} # The following tests - capi3c-12.* - check that its Ok to start a # transaction while other VMs are active, and that its Ok to execute # atomic updates in the same situation # do_test capi3c-12.1 { set STMT [sqlite3_prepare_v2 $DB "SELECT a FROM t2" -1 TAIL] sqlite3_step $STMT } {SQLITE_ROW} do_test capi3c-12.2 { catchsql { INSERT INTO t1 VALUES(3, NULL); } } {0 {}} do_test capi3c-12.3 { catchsql { INSERT INTO t2 VALUES(4); } } {0 {}} do_test capi3c-12.4 { catchsql { BEGIN; INSERT INTO t1 VALUES(4, NULL); } } {0 {}} do_test capi3c-12.5 { sqlite3_step $STMT } {SQLITE_ROW} do_test capi3c-12.5.1 { sqlite3_step $STMT } {SQLITE_ROW} do_test capi3c-12.6 { sqlite3_step $STMT } {SQLITE_DONE} do_test capi3c-12.7 { sqlite3_finalize $STMT } {SQLITE_OK} do_test capi3c-12.8 { execsql { COMMIT; SELECT a FROM t1; } } {1 2 3 4} # Test cases capi3c-13.* test the sqlite3_clear_bindings() and # sqlite3_sleep APIs. # if {[llength [info commands sqlite3_clear_bindings]]>0} { do_test capi3c-13.1 { execsql { DELETE FROM t1; } set STMT [sqlite3_prepare_v2 $DB "INSERT INTO t1 VALUES(?, ?)" -1 TAIL] sqlite3_step $STMT } {SQLITE_DONE} do_test capi3c-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 capi3c-13.3 { sqlite3_reset $STMT sqlite3_clear_bindings $STMT sqlite3_step $STMT } {SQLITE_DONE} do_test capi3c-13-4 { sqlite3_finalize $STMT execsql { SELECT * FROM t1; } } {{} {} hello world {} {}} } if {[llength [info commands sqlite3_sleep]]>0} { do_test capi3c-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 capi3c-14.1 { 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 capi3c-15.1 { set sql {SELECT * FROM t2} set nbytes [string length $sql] append sql { WHERE a==1} set STMT [sqlite3_prepare_v2 $DB $sql $nbytes TAIL] sqlite3_step $STMT sqlite3_column_int $STMT 0 } {1} do_test capi3c-15.2 { sqlite3_step $STMT sqlite3_column_int $STMT 0 } {2} do_test capi3c-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 capi3c-16.1 { set sql {DROP TABLE IF EXISTS t3} set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL] sqlite3_finalize $STMT expr {$STMT!=""} } {1} do_test capi3c-16.2 { set sql {CREATE TABLE IF NOT EXISTS t1(x,y)} set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL] sqlite3_finalize $STMT expr {$STMT!=""} } {1} # But still we do not generate code if there is no SQL # do_test capi3c-16.3 { set STMT [sqlite3_prepare_v2 $DB {} -1 TAIL] sqlite3_finalize $STMT expr {$STMT==""} } {1} do_test capi3c-16.4 { set STMT [sqlite3_prepare_v2 $DB {;} -1 TAIL] sqlite3_finalize $STMT expr {$STMT==""} } {1} # Ticket #2154. # do_test capi3c-17.1 { set STMT [sqlite3_prepare_v2 $DB {SELECT max(a) FROM t2} -1 TAIL] sqlite3_step $STMT } SQLITE_ROW do_test capi3c-17.2 { sqlite3_column_int $STMT 0 } 4 do_test capi3c-17.3 { sqlite3_step $STMT } SQLITE_DONE do_test capi3c-17.4 { sqlite3_reset $STMT db eval {CREATE INDEX i2 ON t2(a)} sqlite3_step $STMT } SQLITE_ROW do_test capi3c-17.5 { sqlite3_column_int $STMT 0 } 4 do_test capi3c-17.6 { sqlite3_step $STMT } SQLITE_DONE do_test capi3c-17.7 { sqlite3_reset $STMT db eval {DROP INDEX i2} sqlite3_step $STMT } SQLITE_ROW do_test capi3c-17.8 { sqlite3_column_int $STMT 0 } 4 do_test capi3c-17.9 { sqlite3_step $STMT } SQLITE_DONE do_test capi3c-17.10 { sqlite3_finalize $STMT set STMT [sqlite3_prepare_v2 $DB {SELECT b FROM t1 WHERE a=?} -1 TAIL] sqlite3_bind_int $STMT 1 2 db eval { DELETE FROM t1; INSERT INTO t1 VALUES(1,'one'); INSERT INTO t1 VALUES(2,'two'); INSERT INTO t1 VALUES(3,'three'); INSERT INTO t1 VALUES(4,'four'); } sqlite3_step $STMT } SQLITE_ROW do_test capi3c-17.11 { sqlite3_column_text $STMT 0 } two do_test capi3c-17.12 { sqlite3_step $STMT } SQLITE_DONE do_test capi3c-17.13 { sqlite3_reset $STMT db eval {CREATE INDEX i1 ON t1(a)} sqlite3_step $STMT } SQLITE_ROW do_test capi3c-17.14 { sqlite3_column_text $STMT 0 } two do_test capi3c-17.15 { sqlite3_step $STMT } SQLITE_DONE do_test capi3c-17.16 { sqlite3_reset $STMT db eval {DROP INDEX i1} sqlite3_step $STMT } SQLITE_ROW do_test capi3c-17.17 { sqlite3_column_text $STMT 0 } two do_test capi3c-17.18 { sqlite3_step $STMT } SQLITE_DONE do_test capi3c-17.99 { sqlite3_finalize $STMT } SQLITE_OK # On the mailing list it has been reported that finalizing after # an SQLITE_BUSY return leads to a segfault. Here we test that case. # do_test capi3c-18.1 { sqlite3 db2 test.db set STMT [sqlite3_prepare_v2 $DB {SELECT max(a) FROM t1} -1 TAIL] sqlite3_step $STMT } SQLITE_ROW do_test capi3c-18.2 { sqlite3_column_int $STMT 0 } 4 do_test capi3c-18.3 { sqlite3_reset $STMT db2 eval {BEGIN EXCLUSIVE} sqlite3_step $STMT } SQLITE_BUSY do_test capi3c-18.4 { sqlite3_finalize $STMT } SQLITE_BUSY do_test capi3c-18.5 { db2 eval {COMMIT} db2 close } {} # Ticket #2158. The sqlite3_step() will still return SQLITE_SCHEMA # if the database schema changes in a way that makes the statement # no longer valid. # do_test capi3c-19.1 { db eval { CREATE TABLE t3(x,y); INSERT INTO t3 VALUES(1,2); } set STMT [sqlite3_prepare_v2 $DB {SELECT * FROM t3} -1 TAIL] sqlite3_step $STMT } SQLITE_ROW do_test capi3c-19.2 { sqlite3_column_int $STMT 0 } 1 do_test capi3c-19.3 { sqlite3_step $STMT } SQLITE_DONE do_test capi3c-19.4 { sqlite3_reset $STMT db eval {DROP TABLE t3} sqlite3_step $STMT } SQLITE_SCHEMA do_test capi3c-19.4.1 { sqlite3_errmsg $DB } {no such table: t3} do_test capi3c-19.4.2 { sqlite3_expired $STMT } 1 do_test capi3c-19.4.3 { sqlite3_errmsg $DB } {no such table: t3} do_test capi3c-19.4.4 { sqlite3_expired 0 } 1 do_test capi3c-19.5 { sqlite3_reset $STMT db eval { CREATE TABLE t3(x,y); INSERT INTO t3 VALUES(1,2); } sqlite3_step $STMT } SQLITE_ROW do_test capi3c-19.5.2 { sqlite3_expired $STMT } 0 do_test capi3c-19.6 { sqlite3_column_int $STMT 1 } 2 do_test capi3c-19.99 { sqlite3_finalize $STMT } SQLITE_OK # Make sure a change in a separate database connection does not # cause an SQLITE_SCHEMA return. # do_test capi3c-20.1 { set STMT [sqlite3_prepare_v2 $DB {SELECT * FROM t3} -1 TAIL] sqlite3 db2 test.db db2 eval {CREATE TABLE t4(x)} sqlite3_step $STMT } SQLITE_ROW do_test capi3c-20.2 { sqlite3_column_int $STMT 1 } 2 do_test capi3c-20.3 { sqlite3_step $STMT } SQLITE_DONE do_test capi3c-20.4 { db2 close sqlite3_finalize $STMT } SQLITE_OK # Test that sqlite3_step() sets the database error code correctly. # See ticket #2497. # ifcapable progress { do_test capi3c-21.1 { set STMT [sqlite3_prepare_v2 $DB {SELECT * FROM t3} -1 TAIL] db progress 5 "expr 1" sqlite3_step $STMT } {SQLITE_INTERRUPT} do_test capi3c-21.2 { sqlite3_errcode $DB } {SQLITE_INTERRUPT} do_test capi3c-21.3 { sqlite3_finalize $STMT } {SQLITE_INTERRUPT} do_test capi3c-21.4 { set STMT [sqlite3_prepare $DB {SELECT * FROM t3} -1 TAIL] db progress 5 "expr 1" sqlite3_step $STMT } {SQLITE_ERROR} do_test capi3c-21.5 { sqlite3_errcode $DB } {SQLITE_ERROR} do_test capi3c-21.6 { sqlite3_finalize $STMT } {SQLITE_INTERRUPT} do_test capi3c-21.7 { sqlite3_errcode $DB } {SQLITE_INTERRUPT} } # Make sure sqlite3_result_error_code() returns the correct error code. # See ticket #2940 # do_test capi3c-22.1 { db progress 0 {} set STMT [sqlite3_prepare_v2 db {SELECT test_error('the message',3)} -1 TAIL] sqlite3_step $STMT } {SQLITE_PERM} sqlite3_finalize $STMT do_test capi3c-22.2 { set STMT [sqlite3_prepare_v2 db {SELECT test_error('the message',4)} -1 TAIL] sqlite3_step $STMT } {SQLITE_ABORT} sqlite3_finalize $STMT do_test capi3c-22.3 { set STMT [sqlite3_prepare_v2 db {SELECT test_error('the message',16)} -1 TAIL] sqlite3_step $STMT } {SQLITE_EMPTY} sqlite3_finalize $STMT # For a multi-column result set where the same table column is repeated # in multiple columns of the output, verify that doing a UTF-8 to UTF-16 # conversion (or vice versa) on one column does not change the value of # the second. # do_test capi3c-23.1 { set STMT [sqlite3_prepare_v2 db {SELECT b,b,b,b FROM t1} -1 TAIL] sqlite3_step $STMT } {SQLITE_ROW} do_test capi3c-23.2 { sqlite3_column_text16 $STMT 0 sqlite3_column_text $STMT 1 } {one} do_test capi3c-23.3 { sqlite3_column_text16 $STMT 2 sqlite3_column_text $STMT 3 } {one} sqlite3_finalize $STMT do_test capi3c-23.4 { set STMT [sqlite3_prepare_v2 db {SELECT b||'x',b,b,b FROM t1} -1 TAIL] sqlite3_step $STMT } {SQLITE_ROW} do_test capi3c-23.5 { sqlite3_column_text16 $STMT 0 sqlite3_column_text $STMT 1 } {one} do_test capi3c-23.6 { sqlite3_column_text16 $STMT 2 sqlite3_column_text $STMT 3 } {one} sqlite3_finalize $STMT finish_test