# 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.67 2008/07/12 15:55:55 danielk1977 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 name FROM sqlite_master;SELECT 10}

   set STMT [sqlite3_prepare $DB $sql [string length $sql] TAIL]

   sqlite3_finalize $STMT

   set TAIL

 } {SELECT 10}

 do_test capi3-1.6 {

   set sql {SELECT name FROM sqlite_master;SELECT 10}

   set STMT [sqlite3_prepare $DB $sql [expr [string length $sql]+1] TAIL]

   sqlite3_finalize $STMT

   set TAIL

 } {SELECT 10}

 do_test capi3-1.7 {

   set sql {SELECT namex FROM sqlite_master}

   catch {

     set STMT [sqlite3_prepare $DB $sql -1 TAIL]

   }

 } {1}

 do_test capi3-1.8 {

   sqlite3_errcode $DB

 } {SQLITE_ERROR}

 do_test capi3-1.9 {

   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}

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

 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}

 }

 do_test capi3-3.7 {

   set db2 [sqlite3_open]

   sqlite3_errcode $db2

 } {SQLITE_OK}

 do_test capi3-3.8 {

   sqlite3_close $db2

 } {SQLITE_OK}

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

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

 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

 # 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 capi3-7.1 {

     set_file_format 5

   } {}

   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 test.db-journal

     sqlite3 db test.db

     execsql {

       CREATE TABLE t1(a);

     }

     db close

   } {}

   do_test capi3-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 capi3-8.3 {

     sqlite3 db test.db

     catchsql {

       SELECT * FROM sqlite_master;

     }

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

   do_test capi3-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 capi3-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 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

   do_test capi3-10-4 {

     sqlite3 db test.db

     set DB [sqlite3_connection_pointer db]

     sqlite3_memdebug_fail 1

     catchsql {

       select * from sqlite_master where rowid>5;

     }

   } {1 {out of memory}}

   do_test capi3-10-5 {

     sqlite3_errmsg $::DB

   } {out of memory}

   ifcapable {utf16} {

     do_test capi3-10-6 {

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

 do_test capi3-15.4 {

   #        123456789 1234567

   set sql {SELECT 1234567890}

   set STMT [sqlite3_prepare $DB $sql 8 TAIL]

   sqlite3_step $STMT

   set v1 [sqlite3_column_int $STMT 0]

   sqlite3_finalize $STMT

   set v1

 } {1}

 do_test capi3-15.5 {

   #        123456789 1234567

   set sql {SELECT 1234567890}

   set STMT [sqlite3_prepare $DB $sql 9 TAIL]

   sqlite3_step $STMT

   set v1 [sqlite3_column_int $STMT 0]

   sqlite3_finalize $STMT

   set v1

 } {12}

 do_test capi3-15.6 {

   #        123456789 1234567

   set sql {SELECT 1234567890}

   set STMT [sqlite3_prepare $DB $sql 12 TAIL]

   sqlite3_step $STMT

   set v1 [sqlite3_column_int $STMT 0]

   sqlite3_finalize $STMT

   set v1

 } {12345}

 do_test capi3-15.7 {

   #        123456789 1234567

   set sql {SELECT 12.34567890}

   set STMT [sqlite3_prepare $DB $sql 12 TAIL]

   sqlite3_step $STMT

   set v1 [sqlite3_column_double $STMT 0]

   sqlite3_finalize $STMT

   set v1

 } {12.34}

 do_test capi3-15.8 {

   #        123456789 1234567

   set sql {SELECT 12.34567890}

   set STMT [sqlite3_prepare $DB $sql 14 TAIL]

   sqlite3_step $STMT

   set v1 [sqlite3_column_double $STMT 0]

   sqlite3_finalize $STMT

   set v1

 } {12.3456}

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

 # Verify that sqlite3_step() fails with an SQLITE_SCHEMA error

 # when the statement is prepared with sqlite3_prepare() (not

 # sqlite3_prepare_v2()) and the schema has changed.

 #

 do_test capi3-18.1 {

   set STMT [sqlite3_prepare db {SELECT * FROM t2} -1 TAIL]

   sqlite3 db2 test.db

   db2 eval {CREATE TABLE t3(x)}

   db2 close

   sqlite3_step $STMT

 } {SQLITE_ERROR}

 do_test capi3-18.2 {

   sqlite3_reset $STMT

   sqlite3_errcode db

 } {SQLITE_SCHEMA}

 do_test capi3-18.3 {

   sqlite3_errmsg db

 } {database schema has changed}

 # The error persist on retry when sqlite3_prepare() has been used.

 do_test capi3-18.4 {

   sqlite3_step $STMT

 } {SQLITE_ERROR}

 do_test capi3-18.5 {

   sqlite3_reset $STMT

   sqlite3_errcode db

 } {SQLITE_SCHEMA}

 do_test capi3-18.6 {

   sqlite3_errmsg db

 } {database schema has changed}

 sqlite3_finalize $STMT

 # Ticket #3134.  Prepare a statement with an nBytes parameter of 0.

 # Make sure this works correctly and does not reference memory out of

 # range.

 #

 do_test capi3-19.1 {

   sqlite3_prepare_tkt3134 db

 } {}

 # Tests of the interface when no VFS is registered.

 #

 if {![info exists tester_do_binarylog]} {

   db close

   vfs_unregister_all

   do_test capi3-20.1 {

     sqlite3_sleep 100

   } {0}

   vfs_reregister_all

 }

 finish_test