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

# Symbian OS: '/' in the file name replaced with '\\'

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}

# Symbian OS: '/' in the file name replaced with '\\'

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}