# 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.23 2009/07/22 07:27:57 danielk1977 Exp $

#

set testdir [file dirname $argv0]

source $testdir/tester.tcl

set testprefix capi3c

# Do not use a codec for tests in this file, as the database file is

# manipulated directly using tcl scripts (using the [hexio_write] command).

#

do_not_use_codec

# 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.1 {

sqlite3_errcode $DB

} {SQLITE_OK}

do_test capi3c-1.2.2 {

sqlite3_extended_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.1 {

sqlite3_errcode $DB

} {SQLITE_ERROR}

do_test capi3c-1.6.2 {

sqlite3_extended_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.1 {

sqlite3_errcode $DB

} {SQLITE_ERROR}

do_test capi3c-2.4.2 {

sqlite3_extended_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}

if {[clang_sanitize_address]==0} {

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]

if {[sqlite3 -has-codec]==0} { 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}

if {[clang_sanitize_address]==0} {

do_test capi3c-6.4 {

db cache flush

sqlite3_close $DB

} {SQLITE_OK}

do_test capi3c-6.99-misuse {

db close

} {}

} else {

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 {

catch { 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 {

forcedelete 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 {

catch { 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

forcedelete 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 {

catch { sqlite3 db test.db }

catchsql {

SELECT * FROM sqlite_master;

}

} {1 {malformed database schema (?)}}

db close

}

forcedelete test.db

forcedelete 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.

if { [permutation] != "nofaultsim" } {

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}

# As of 3.6.5 a COMMIT is OK during while a query is still running -

# as long as it is a read-only query and not an incremental BLOB write.

#

do_test capi3-11.3.1 {

catchsql {

COMMIT;

}

} {0 {}}

do_test capi3-11.3.2 {

sqlite3_extended_errcode $DB

} {SQLITE_OK}

do_test capi3-11.3.3 {

sqlite3_get_autocommit $DB

} 1

do_test capi3-11.3.4 {

db eval {PRAGMA lock_status}

} {main shared temp closed}

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.7 {

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;

}

} {0 {}}

do_test capi3c-11.9.3 {

sqlite3_get_autocommit $DB

} 1

do_test capi3c-11.10 {

sqlite3_step $STMT

} {SQLITE_ROW}

ifcapable !autoreset {

# If SQLITE_OMIT_AUTORESET is defined, then the statement must be

# reset() before it can be passed to step() again.

do_test capi3-11.11a { sqlite3_step $STMT } {SQLITE_MISUSE}

do_test capi3-11.11b { sqlite3_reset $STMT } {SQLITE_ABORT}

}

do_test capi3c-11.11 {

sqlite3_step $STMT

} {SQLITE_DONE}

do_test capi3c-11.12 {

sqlite3_step $STMT

sqlite3_step $STMT

} {SQLITE_ROW}

do_test capi3c-11.13 {

sqlite3_finalize $STMT

} {SQLITE_OK}

do_test capi3c-11.14 {

execsql {

SELECT a FROM t2;

}

} {1 2}

do_test capi3c-11.14.1 {

sqlite3_get_autocommit $DB

} 1

do_test capi3c-11.15 {

catchsql {

ROLLBACK;

}

} {1 {cannot rollback - no transaction is active}}

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;

}

} {0 {}}

do_test capi3c-11.20 {

sqlite3_reset $STMT

catchsql {

COMMIT;

}

} {1 {cannot commit - no transaction is active}}

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 {