000001  # 2003 January 29
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  # This file implements regression tests for SQLite library.  The
000012  # focus of this script testing the callback-free C/C++ API.
000013  #
000014  # $Id: capi3.test,v 1.70 2009/01/09 02:49:32 drh Exp $
000015  #
000016  
000017  set testdir [file dirname $argv0]
000018  source $testdir/tester.tcl
000019  set ::testprefix capi3
000020  
000021  # Do not use a codec for tests in this file, as the database file is
000022  # manipulated directly using tcl scripts (using the [hexio_write] command).
000023  #
000024  do_not_use_codec
000025  
000026  # Return the UTF-16 representation of the supplied UTF-8 string $str.
000027  # If $nt is true, append two 0x00 bytes as a nul terminator.
000028  proc utf16 {str {nt 1}} {
000029    set r [encoding convertto unicode $str]
000030    if {$nt} {
000031      append r "\x00\x00"
000032    }
000033    return $r
000034  }
000035  
000036  # Return the UTF-8 representation of the supplied UTF-16 string $str. 
000037  proc utf8 {str} {
000038    # If $str ends in two 0x00 0x00 bytes, knock these off before
000039    # converting to UTF-8 using TCL.
000040    binary scan $str \c* vals
000041    if {[lindex $vals end]==0 && [lindex $vals end-1]==0} {
000042      set str [binary format \c* [lrange $vals 0 end-2]]
000043    }
000044  
000045    set r [encoding convertfrom unicode $str]
000046    return $r
000047  }
000048  
000049  # These tests complement those in capi2.test. They are organized
000050  # as follows:
000051  #
000052  # capi3-1.*: Test sqlite3_prepare 
000053  # capi3-2.*: Test sqlite3_prepare16
000054  # capi3-3.*: Test sqlite3_open
000055  # capi3-4.*: Test sqlite3_open16
000056  # capi3-5.*: Test the various sqlite3_result_* APIs
000057  # capi3-6.*: Test that sqlite3_close fails if there are outstanding VMs.
000058  #
000059  
000060  set DB [sqlite3_connection_pointer db]
000061  
000062  do_test capi3-1.0 {
000063    sqlite3_get_autocommit $DB
000064  } 1
000065  do_test capi3-1.1 {
000066    set STMT [sqlite3_prepare $DB {SELECT name FROM sqlite_master} -1 TAIL]
000067    sqlite3_finalize $STMT
000068    set TAIL
000069  } {}
000070  do_test capi3-1.2.1 {
000071    sqlite3_errcode $DB
000072  } {SQLITE_OK}
000073  do_test capi3-1.2.2 {
000074    sqlite3_extended_errcode $DB
000075  } {SQLITE_OK}
000076  do_test capi3-1.3 {
000077    sqlite3_errmsg $DB
000078  } {not an error}
000079  do_test capi3-1.4 {
000080    set sql {SELECT name FROM sqlite_master;SELECT 10}
000081    set STMT [sqlite3_prepare $DB $sql -1 TAIL]
000082    sqlite3_finalize $STMT
000083    set TAIL
000084  } {SELECT 10}
000085  do_test capi3-1.5 {
000086    set sql {SELECT name FROM sqlite_master;SELECT 10}
000087    set STMT [sqlite3_prepare $DB $sql [string length $sql] TAIL]
000088    sqlite3_finalize $STMT
000089    set TAIL
000090  } {SELECT 10}
000091  do_test capi3-1.6 {
000092    set sql {SELECT name FROM sqlite_master;SELECT 10}
000093    set STMT [sqlite3_prepare $DB $sql [expr [string length $sql]+1] TAIL]
000094    sqlite3_finalize $STMT
000095    set TAIL
000096  } {SELECT 10}
000097  
000098  do_test capi3-1.7 {
000099    set sql {SELECT namex FROM sqlite_master}
000100    catch {
000101      set STMT [sqlite3_prepare $DB $sql -1 TAIL]
000102    }
000103  } {1}
000104  do_test capi3-1.8.1 {
000105    sqlite3_errcode $DB
000106  } {SQLITE_ERROR}
000107  do_test capi3-1.8.2 {
000108    sqlite3_extended_errcode $DB
000109  } {SQLITE_ERROR}
000110  do_test capi3-1.9 {
000111    sqlite3_errmsg $DB
000112  } {no such column: namex}
000113  
000114  ifcapable {utf16} {
000115    do_test capi3-2.1 {
000116      set sql16 [utf16 {SELECT name FROM sqlite_master}]
000117      set STMT [sqlite3_prepare16 $DB $sql16 -1 ::TAIL]
000118      sqlite3_finalize $STMT
000119      utf8 $::TAIL
000120    } {}
000121    do_test capi3-2.2 {
000122      set sql [utf16 {SELECT name FROM sqlite_master;SELECT 10}]
000123      set STMT [sqlite3_prepare16 $DB $sql -1 TAIL]
000124      sqlite3_finalize $STMT
000125      utf8 $TAIL
000126    } {SELECT 10}
000127    do_test capi3-2.3 {
000128      set sql [utf16 {SELECT namex FROM sqlite_master}]
000129      catch {
000130        set STMT [sqlite3_prepare16 $DB $sql -1]
000131      }
000132    } {1}
000133    do_test capi3-2.4.1 {
000134      sqlite3_errcode $DB
000135    } {SQLITE_ERROR}
000136    do_test capi3-2.4.2 {
000137      sqlite3_extended_errcode $DB
000138    } {SQLITE_ERROR}
000139    do_test capi3-2.5 {
000140      sqlite3_errmsg $DB
000141    } {no such column: namex}
000142  
000143    ifcapable schema_pragmas {
000144      do_test capi3-2.6 {
000145        execsql {CREATE TABLE tablename(x)}
000146        set sql16 [utf16 {PRAGMA table_info("TableName"); --excess text}]
000147        set STMT [sqlite3_prepare16 $DB $sql16 -1]
000148        sqlite3_step $STMT
000149      } SQLITE_ROW
000150      do_test capi3-2.7 {
000151        sqlite3_step $STMT
000152      } SQLITE_DONE
000153      do_test capi3-2.8 {
000154        sqlite3_finalize $STMT
000155      } SQLITE_OK
000156    }
000157  
000158  } ;# endif utf16
000159  
000160  # rename sqlite3_open sqlite3_open_old
000161  # proc sqlite3_open {fname options} {sqlite3_open_new $fname $options}
000162  
000163  do_test capi3-3.1 {
000164    set db2 [sqlite3_open test.db {}]
000165    sqlite3_errcode $db2
000166  } {SQLITE_OK}
000167  # FIX ME: Should test the db handle works.
000168  do_test capi3-3.2 {
000169    sqlite3_close $db2
000170  } {SQLITE_OK}
000171  do_test capi3-3.3 {
000172    catch {
000173      set db2 [sqlite3_open /bogus/path/test.db {}]
000174    }
000175    set ::capi3_errno [sqlite3_system_errno $db2]
000176    list [sqlite3_extended_errcode $db2] [expr {$::capi3_errno!=0}]
000177  } {SQLITE_CANTOPEN 1}
000178  do_test capi3-3.4 {
000179    sqlite3_errmsg $db2
000180  } {unable to open database file}
000181  do_test capi3-3.5 {
000182    list [sqlite3_system_errno $db2] [sqlite3_close $db2]
000183  } [list $::capi3_errno SQLITE_OK]
000184  if {[clang_sanitize_address]==0} {
000185    do_test capi3-3.6.1-misuse {
000186      sqlite3_close $db2
000187    } {SQLITE_MISUSE}
000188    do_test capi3-3.6.2-misuse {
000189      sqlite3_errmsg $db2
000190    } {bad parameter or other API misuse}
000191    ifcapable {utf16} {
000192      do_test capi3-3.6.3-misuse {
000193        utf8 [sqlite3_errmsg16 $db2]
000194      } {bad parameter or other API misuse}
000195    }
000196  }
000197  
000198  do_test capi3-3.7 {
000199    set db2 [sqlite3_open]
000200    sqlite3_errcode $db2
000201  } {SQLITE_OK}
000202  do_test capi3-3.8 {
000203    sqlite3_close $db2
000204  } {SQLITE_OK}
000205  
000206  # rename sqlite3_open ""
000207  # rename sqlite3_open_old sqlite3_open
000208  
000209  ifcapable {utf16} {
000210  do_test capi3-4.1 {
000211    set db2 [sqlite3_open16 [utf16 test.db] {}]
000212    sqlite3_errcode $db2
000213  } {SQLITE_OK}
000214  # FIX ME: Should test the db handle works.
000215  do_test capi3-4.2 {
000216    sqlite3_close $db2
000217  } {SQLITE_OK}
000218  do_test capi3-4.3 {
000219    catch {
000220      set db2 [sqlite3_open16 [utf16 /bogus/path/test.db] {}]
000221    }
000222    sqlite3_errcode $db2
000223  } {SQLITE_CANTOPEN}
000224  do_test capi3-4.4 {
000225    utf8 [sqlite3_errmsg16 $db2]
000226  } {unable to open database file}
000227  do_test capi3-4.5 {
000228    sqlite3_close $db2
000229  } {SQLITE_OK}
000230  } ;# utf16
000231  
000232  # This proc is used to test the following API calls:
000233  #
000234  # sqlite3_column_count
000235  # sqlite3_column_name
000236  # sqlite3_column_name16
000237  # sqlite3_column_decltype
000238  # sqlite3_column_decltype16
000239  #
000240  # $STMT is a compiled SQL statement. $test is a prefix
000241  # to use for test names within this proc. $names is a list
000242  # of the column names that should be returned by $STMT.
000243  # $decltypes is a list of column declaration types for $STMT.
000244  #
000245  # Example:
000246  #
000247  # set STMT [sqlite3_prepare "SELECT 1, 2, 2;" -1 DUMMY]
000248  # check_header test1.1 {1 2 3} {"" "" ""}
000249  #
000250  proc check_header {STMT test names decltypes} {
000251  
000252    # Use the return value of sqlite3_column_count() to build
000253    # a list of column indexes. i.e. If sqlite3_column_count
000254    # is 3, build the list {0 1 2}.
000255    set ::idxlist [list]
000256    set ::numcols [sqlite3_column_count $STMT]
000257    for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i}
000258  
000259    # Column names in UTF-8
000260    do_test $test.1 {
000261      set cnamelist [list]
000262      foreach i $idxlist {lappend cnamelist [sqlite3_column_name $STMT $i]} 
000263      set cnamelist
000264    } $names
000265  
000266    # Column names in UTF-16
000267    ifcapable {utf16} {
000268      do_test $test.2 {
000269        set cnamelist [list]
000270        foreach i $idxlist {
000271          lappend cnamelist [utf8 [sqlite3_column_name16 $STMT $i]]
000272        }
000273        set cnamelist
000274      } $names
000275    }
000276  
000277    # Column names in UTF-8
000278    do_test $test.3 {
000279      set cnamelist [list]
000280      foreach i $idxlist {lappend cnamelist [sqlite3_column_name $STMT $i]} 
000281      set cnamelist
000282    } $names
000283  
000284    # Column names in UTF-16
000285    ifcapable {utf16} {
000286      do_test $test.4 {
000287        set cnamelist [list]
000288        foreach i $idxlist {
000289          lappend cnamelist [utf8 [sqlite3_column_name16 $STMT $i]]
000290        }
000291        set cnamelist
000292      } $names
000293    }
000294  
000295    # Column names in UTF-8
000296    do_test $test.5 {
000297      set cnamelist [list]
000298      foreach i $idxlist {lappend cnamelist [sqlite3_column_decltype $STMT $i]} 
000299      set cnamelist
000300    } $decltypes
000301  
000302    # Column declaration types in UTF-16
000303    ifcapable {utf16} {
000304      do_test $test.6 {
000305        set cnamelist [list]
000306        foreach i $idxlist {
000307          lappend cnamelist [utf8 [sqlite3_column_decltype16 $STMT $i]]
000308        }
000309        set cnamelist
000310      } $decltypes
000311    }
000312  
000313  
000314    # Test some out of range conditions:
000315    ifcapable {utf16} {
000316      do_test $test.7 {
000317        list \
000318          [sqlite3_column_name $STMT -1] \
000319          [sqlite3_column_name16 $STMT -1] \
000320          [sqlite3_column_decltype $STMT -1] \
000321          [sqlite3_column_decltype16 $STMT -1] \
000322          [sqlite3_column_name $STMT $numcols] \
000323          [sqlite3_column_name16 $STMT $numcols] \
000324          [sqlite3_column_decltype $STMT $numcols] \
000325          [sqlite3_column_decltype16 $STMT $numcols]
000326      } {{} {} {} {} {} {} {} {}}
000327    }
000328  } 
000329  
000330  # This proc is used to test the following API calls:
000331  #
000332  # sqlite3_column_origin_name
000333  # sqlite3_column_origin_name16
000334  # sqlite3_column_table_name
000335  # sqlite3_column_table_name16
000336  # sqlite3_column_database_name
000337  # sqlite3_column_database_name16
000338  #
000339  # $STMT is a compiled SQL statement. $test is a prefix
000340  # to use for test names within this proc. $names is a list
000341  # of the column names that should be returned by $STMT.
000342  # $decltypes is a list of column declaration types for $STMT.
000343  #
000344  # Example:
000345  #
000346  # set STMT [sqlite3_prepare "SELECT 1, 2, 2;" -1 DUMMY]
000347  # check_header test1.1 {1 2 3} {"" "" ""}
000348  #
000349  proc check_origin_header {STMT test dbs tables cols} {
000350    # If sqlite3_column_origin_name() and friends are not compiled into
000351    # this build, this proc is a no-op.
000352    ifcapable columnmetadata {
000353      # Use the return value of sqlite3_column_count() to build
000354      # a list of column indexes. i.e. If sqlite3_column_count
000355      # is 3, build the list {0 1 2}.
000356      set ::idxlist [list]
000357      set ::numcols [sqlite3_column_count $STMT]
000358      for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i}
000359    
000360      # Database names in UTF-8
000361      do_test $test.8 {
000362        set cnamelist [list]
000363        foreach i $idxlist {
000364          lappend cnamelist [sqlite3_column_database_name $STMT $i]
000365        } 
000366        set cnamelist
000367      } $dbs
000368    
000369      # Database names in UTF-16
000370      ifcapable {utf16} {
000371        do_test $test.9 {
000372          set cnamelist [list]
000373          foreach i $idxlist {
000374            lappend cnamelist [utf8 [sqlite3_column_database_name16 $STMT $i]]
000375          }
000376          set cnamelist
000377        } $dbs
000378      }
000379    
000380      # Table names in UTF-8
000381      do_test $test.10 {
000382        set cnamelist [list]
000383        foreach i $idxlist {
000384          lappend cnamelist [sqlite3_column_table_name $STMT $i]
000385        } 
000386        set cnamelist
000387      } $tables
000388    
000389      # Table names in UTF-16
000390      ifcapable {utf16} {
000391        do_test $test.11 {
000392          set cnamelist [list]
000393          foreach i $idxlist {
000394            lappend cnamelist [utf8 [sqlite3_column_table_name16 $STMT $i]]
000395          }
000396          set cnamelist
000397        } $tables
000398      }
000399    
000400      # Origin names in UTF-8
000401      do_test $test.12 {
000402        set cnamelist [list]
000403        foreach i $idxlist {
000404          lappend cnamelist [sqlite3_column_origin_name $STMT $i]
000405        } 
000406        set cnamelist
000407      } $cols
000408    
000409      # Origin declaration types in UTF-16
000410      ifcapable {utf16} {
000411        do_test $test.13 {
000412          set cnamelist [list]
000413          foreach i $idxlist {
000414            lappend cnamelist [utf8 [sqlite3_column_origin_name16 $STMT $i]]
000415          }
000416          set cnamelist
000417        } $cols
000418      }
000419    }
000420  }
000421  
000422  # This proc is used to test the following APIs:
000423  #
000424  # sqlite3_data_count
000425  # sqlite3_column_type
000426  # sqlite3_column_int
000427  # sqlite3_column_text
000428  # sqlite3_column_text16
000429  # sqlite3_column_double
000430  #
000431  # $STMT is a compiled SQL statement for which the previous call 
000432  # to sqlite3_step returned SQLITE_ROW. $test is a prefix to use 
000433  # for test names within this proc. $types is a list of the 
000434  # manifest types for the current row. $ints, $doubles and $strings
000435  # are lists of the integer, real and string representations of
000436  # the values in the current row.
000437  #
000438  # Example:
000439  #
000440  # set STMT [sqlite3_prepare "SELECT 'hello', 1.1, NULL" -1 DUMMY]
000441  # sqlite3_step $STMT
000442  # check_data test1.2 {TEXT REAL NULL} {0 1 0} {0 1.1 0} {hello 1.1 {}}
000443  #
000444  proc check_data {STMT test types ints doubles strings} {
000445  
000446    # Use the return value of sqlite3_column_count() to build
000447    # a list of column indexes. i.e. If sqlite3_column_count
000448    # is 3, build the list {0 1 2}.
000449    set ::idxlist [list]
000450    set numcols [sqlite3_data_count $STMT]
000451    for {set i 0} {$i < $numcols} {incr i} {lappend ::idxlist $i}
000452  
000453  # types
000454  do_test $test.1 {
000455    set types [list]
000456    foreach i $idxlist {
000457      set x [sqlite3_column_type $STMT $i]
000458      # EVIDENCE-OF: R-12793-43283 Every value in SQLite has one of five
000459      # fundamental datatypes: 64-bit signed integer 64-bit IEEE floating
000460      # point number string BLOB NULL
000461      if {[lsearch {INTEGER FLOAT TEXT BLOB NULL} $x]<0} {
000462        set types ERROR
000463        break
000464      } else {
000465        lappend types $x
000466      }
000467    }
000468    set types
000469  } $types
000470   
000471  
000472  # Integers
000473  do_test $test.2 {
000474    set ints [list]
000475    foreach i $idxlist {lappend ints [sqlite3_column_int64 $STMT $i]}
000476    set ints
000477  } $ints
000478  
000479  # bytes
000480  set lens [list]
000481  foreach i $::idxlist {
000482    lappend lens [string length [lindex $strings $i]]
000483  }
000484  do_test $test.3 {
000485    set bytes [list]
000486    set lens [list]
000487    foreach i $idxlist {
000488      lappend bytes [sqlite3_column_bytes $STMT $i]
000489    }
000490    set bytes
000491  } $lens
000492  
000493  # bytes16
000494  ifcapable {utf16} {
000495    set lens [list]
000496    foreach i $::idxlist {
000497      lappend lens [expr 2 * [string length [lindex $strings $i]]]
000498    }
000499    do_test $test.4 {
000500      set bytes [list]
000501      set lens [list]
000502      foreach i $idxlist {
000503        lappend bytes [sqlite3_column_bytes16 $STMT $i]
000504      }
000505      set bytes
000506    } $lens
000507  }
000508  
000509  # Blob
000510  do_test $test.5 {
000511    set utf8 [list]
000512    foreach i $idxlist {lappend utf8 [sqlite3_column_blob $STMT $i]}
000513    set utf8
000514  } $strings
000515  
000516  # UTF-8
000517  do_test $test.6 {
000518    set utf8 [list]
000519    foreach i $idxlist {lappend utf8 [sqlite3_column_text $STMT $i]}
000520    set utf8
000521  } $strings
000522  
000523  # Floats
000524  do_test $test.7 {
000525    set utf8 [list]
000526    foreach i $idxlist {lappend utf8 [sqlite3_column_double $STMT $i]}
000527    set utf8
000528  } $doubles
000529  
000530  # UTF-16
000531  ifcapable {utf16} {
000532    do_test $test.8 {
000533      set utf8 [list]
000534      foreach i $idxlist {lappend utf8 [utf8 [sqlite3_column_text16 $STMT $i]]}
000535      set utf8
000536    } $strings
000537  }
000538  
000539  # Integers
000540  do_test $test.9 {
000541    set ints [list]
000542    foreach i $idxlist {lappend ints [sqlite3_column_int $STMT $i]}
000543    set ints
000544  } $ints
000545  
000546  # Floats
000547  do_test $test.10 {
000548    set utf8 [list]
000549    foreach i $idxlist {lappend utf8 [sqlite3_column_double $STMT $i]}
000550    set utf8
000551  } $doubles
000552  
000553  # UTF-8
000554  do_test $test.11 {
000555    set utf8 [list]
000556    foreach i $idxlist {lappend utf8 [sqlite3_column_text $STMT $i]}
000557    set utf8
000558  } $strings
000559  
000560  # Types
000561  do_test $test.12 {
000562    set types [list]
000563    foreach i $idxlist {lappend types [sqlite3_column_type $STMT $i]}
000564    set types
000565  } $types
000566  
000567  # Test that an out of range request returns the equivalent of NULL
000568  do_test $test.13 {
000569    sqlite3_column_int $STMT -1
000570  } {0}
000571  do_test $test.13 {
000572    sqlite3_column_text $STMT -1
000573  } {}
000574  
000575  }
000576  
000577  ifcapable !floatingpoint {
000578    finish_test
000579    return
000580  }
000581  
000582  do_test capi3-5.0 {
000583    execsql {
000584      CREATE TABLE t1(a VARINT, b BLOB, c VARCHAR(16));
000585      INSERT INTO t1 VALUES(1, 2, 3);
000586      INSERT INTO t1 VALUES('one', 'two', NULL);
000587      INSERT INTO t1 VALUES(1.2, 1.3, 1.4);
000588    }
000589    set sql "SELECT * FROM t1"
000590    set STMT [sqlite3_prepare $DB $sql -1 TAIL]
000591    sqlite3_column_count $STMT
000592  } 3
000593  
000594  check_header $STMT capi3-5.1 {a b c} {VARINT BLOB VARCHAR(16)}
000595  check_origin_header $STMT capi3-5.1 {main main main} {t1 t1 t1} {a b c}
000596  do_test capi3-5.2 {
000597    sqlite3_step $STMT
000598  } SQLITE_ROW
000599  
000600  check_header $STMT capi3-5.3 {a b c} {VARINT BLOB VARCHAR(16)}
000601  check_origin_header $STMT capi3-5.3 {main main main} {t1 t1 t1} {a b c}
000602  check_data $STMT capi3-5.4 {INTEGER INTEGER TEXT} {1 2 3} {1.0 2.0 3.0} {1 2 3}
000603  
000604  do_test capi3-5.5 {
000605    sqlite3_step $STMT
000606  } SQLITE_ROW
000607  
000608  check_header $STMT capi3-5.6 {a b c} {VARINT BLOB VARCHAR(16)}
000609  check_origin_header $STMT capi3-5.6 {main main main} {t1 t1 t1} {a b c}
000610  check_data $STMT capi3-5.7 {TEXT TEXT NULL} {0 0 0} {0.0 0.0 0.0} {one two {}}
000611  
000612  do_test capi3-5.8 {
000613    sqlite3_step $STMT
000614  } SQLITE_ROW
000615  
000616  check_header $STMT capi3-5.9 {a b c} {VARINT BLOB VARCHAR(16)}
000617  check_origin_header $STMT capi3-5.9 {main main main} {t1 t1 t1} {a b c}
000618  check_data $STMT capi3-5.10 {FLOAT FLOAT TEXT} {1 1 1} {1.2 1.3 1.4} {1.2 1.3 1.4}
000619  
000620  do_test capi3-5.11 {
000621    sqlite3_step $STMT
000622  } SQLITE_DONE
000623  
000624  do_test capi3-5.12 {
000625    sqlite3_finalize $STMT
000626  } SQLITE_OK
000627  
000628  do_test capi3-5.20 {
000629    set sql "SELECT a, sum(b), max(c) FROM t1 GROUP BY a"
000630    set STMT [sqlite3_prepare $DB $sql -1 TAIL]
000631    sqlite3_column_count $STMT
000632  } 3
000633  
000634  check_header $STMT capi3-5.21 {a sum(b) max(c)} {VARINT {} {}}
000635  check_origin_header $STMT capi3-5.22 {main {} {}} {t1 {} {}} {a {} {}}
000636  do_test capi3-5.23 {
000637    sqlite3_finalize $STMT
000638  } SQLITE_OK
000639  
000640  do_test capi3-5.30 {
000641    set sql "SELECT a AS x, sum(b) AS y, max(c) AS z FROM t1 AS m GROUP BY x"
000642    set STMT [sqlite3_prepare $DB $sql -1 TAIL]
000643    sqlite3_column_count $STMT
000644  } 3
000645  
000646  check_header $STMT capi3-5.31 {x y z} {VARINT {} {}}
000647  check_origin_header $STMT capi3-5.32 {main {} {}} {t1 {} {}} {a {} {}}
000648  do_test capi3-5.33 {
000649    sqlite3_finalize $STMT
000650  } SQLITE_OK
000651  
000652  # 2018-01-09:  If a column is the last token if a string, the column name
000653  # was not being set correctly, due to changes in check-in
000654  # https://sqlite.org/src/info/0fdf97efe5df7455
000655  #
000656  # This problem was detected by the community during beta-testing.
000657  #
000658  do_test capi3-5.34 {
000659    set STMT [sqlite3_prepare $DB {SELECT :a, :b} -1 TAIL]
000660    sqlite3_column_count $STMT
000661  } 2
000662  check_header $STMT capi-5.35 {:a :b} {{} {}}
000663  sqlite3_finalize $STMT
000664  
000665  set ::ENC [execsql {pragma encoding}]
000666  db close
000667  
000668  do_test capi3-6.0 {
000669    sqlite3 db test.db
000670    set DB [sqlite3_connection_pointer db]
000671    if {[sqlite3 -has-codec]==0} { sqlite3_key $DB xyzzy }
000672    set sql {SELECT a FROM t1 order by rowid}
000673    set STMT [sqlite3_prepare $DB $sql -1 TAIL]
000674    expr 0
000675  } {0}
000676  do_test capi3-6.1 {
000677    db cache flush
000678    sqlite3_close $DB
000679  } {SQLITE_BUSY}
000680  
000681  # 6.2 and 6.3 used to return SQLITE_ERROR and SQLITE_SCHEMA, respectively.
000682  # But since attempting to close a connection no longer resets the internal
000683  # schema and expires all statements, this is no longer the case.
000684  do_test capi3-6.2 {
000685    sqlite3_step $STMT
000686  } {SQLITE_ROW}
000687  #check_data $STMT capi3-6.3 {INTEGER} {1} {1.0} {1}
000688  do_test capi3-6.3 {
000689    sqlite3_finalize $STMT
000690  } {SQLITE_OK}
000691  
000692  if {[clang_sanitize_address]==0} {
000693    do_test capi3-6.4-misuse {
000694      db cache flush
000695      sqlite3_close $DB
000696    } {SQLITE_OK}
000697  }
000698  db close
000699  
000700  # This procedure sets the value of the file-format in file 'test.db'
000701  # to $newval. Also, the schema cookie is incremented.
000702  # 
000703  proc set_file_format {newval} {
000704    hexio_write test.db 44 [hexio_render_int32 $newval]
000705    set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
000706    incr schemacookie
000707    hexio_write test.db 40 [hexio_render_int32 $schemacookie]
000708    return {}
000709  }
000710  
000711  # This procedure returns the value of the file-format in file 'test.db'.
000712  # 
000713  proc get_file_format {{fname test.db}} {
000714    return [hexio_get_int [hexio_read $fname 44 4]]
000715  }
000716  
000717  if {![sqlite3 -has-codec]} {
000718    # Test what happens when the library encounters a newer file format.
000719    do_test capi3-7.1 {
000720      set_file_format 5
000721    } {}
000722    do_test capi3-7.2 {
000723      catch { sqlite3 db test.db }
000724      catchsql {
000725        SELECT * FROM sqlite_master;
000726      }
000727    } {1 {unsupported file format}}
000728    db close
000729  }
000730  
000731  if {![sqlite3 -has-codec]} {
000732    # Now test that the library correctly handles bogus entries in the
000733    # sqlite_master table (schema corruption).
000734    do_test capi3-8.1 {
000735      forcedelete test.db test.db-journal
000736      sqlite3 db test.db
000737      execsql {
000738        CREATE TABLE t1(a);
000739      }
000740      db close
000741    } {}
000742    do_test capi3-8.2 {
000743      sqlite3 db test.db
000744      sqlite3_db_config db DEFENSIVE 0
000745      execsql {
000746        PRAGMA writable_schema=ON;
000747        INSERT INTO sqlite_master VALUES(NULL,NULL,NULL,NULL,NULL);
000748      }
000749      db close
000750    } {}
000751    do_test capi3-8.3 {
000752      catch { sqlite3 db test.db }
000753      catchsql {
000754        SELECT * FROM sqlite_master;
000755      }
000756    } {1 {malformed database schema (?)}}
000757    do_test capi3-8.4 {
000758      # Build a 5-field row record. The first field is a string 'table', and
000759      # subsequent fields are all NULL.
000760      db close
000761      forcedelete test.db test.db-journal
000762      sqlite3 db test.db
000763      sqlite3_db_config db DEFENSIVE 0
000764      execsql {
000765        CREATE TABLE t1(a);
000766        PRAGMA writable_schema=ON;
000767        INSERT INTO sqlite_master VALUES('table',NULL,NULL,NULL,NULL);
000768      }
000769      db close
000770    } {};
000771    do_test capi3-8.5 {
000772      catch { sqlite3 db test.db }
000773      catchsql {
000774        SELECT * FROM sqlite_master;
000775      }
000776    } {1 {malformed database schema (?)}}
000777    db close
000778  }
000779  forcedelete test.db
000780  forcedelete test.db-journal
000781  
000782  
000783  # Test the english language string equivalents for sqlite error codes
000784  set code2english [list \
000785  SQLITE_OK         {not an error} \
000786  SQLITE_ERROR      {SQL logic error} \
000787  SQLITE_PERM       {access permission denied} \
000788  SQLITE_ABORT      {query aborted} \
000789  SQLITE_BUSY       {database is locked} \
000790  SQLITE_LOCKED     {database table is locked} \
000791  SQLITE_NOMEM      {out of memory} \
000792  SQLITE_READONLY   {attempt to write a readonly database} \
000793  SQLITE_INTERRUPT  {interrupted} \
000794  SQLITE_IOERR      {disk I/O error} \
000795  SQLITE_CORRUPT    {database disk image is malformed} \
000796  SQLITE_FULL       {database or disk is full} \
000797  SQLITE_CANTOPEN   {unable to open database file} \
000798  SQLITE_SCHEMA     {database schema has changed} \
000799  SQLITE_CONSTRAINT {constraint failed} \
000800  SQLITE_MISMATCH   {datatype mismatch} \
000801  SQLITE_MISUSE     {bad parameter or other API misuse} \
000802  SQLITE_AUTH       {authorization denied} \
000803  SQLITE_RANGE      {column index out of range} \
000804  SQLITE_NOTADB     {file is not a database} \
000805  unknownerror      {unknown error} \
000806  ]
000807  
000808  set test_number 1
000809  foreach {code english} $code2english {
000810    do_test capi3-9.$test_number "sqlite3_test_errstr $code" $english
000811    incr test_number
000812  }
000813  
000814  # Test the error message when a "real" out of memory occurs.
000815  if { [permutation] != "nofaultsim" } {
000816    do_test capi3-10-1 {
000817      sqlite3 db test.db
000818      set DB [sqlite3_connection_pointer db]
000819      sqlite3_memdebug_fail 1
000820      catchsql {
000821        select * from sqlite_master;
000822      }
000823    } {1 {out of memory}}
000824    do_test capi3-10-2 {
000825      sqlite3_errmsg $::DB
000826    } {out of memory}
000827    ifcapable {utf16} {
000828      do_test capi3-10-3 {
000829        utf8 [sqlite3_errmsg16 $::DB]
000830      } {out of memory}
000831    }
000832    db close
000833    sqlite3_memdebug_fail -1
000834    do_test capi3-10-4 {
000835      sqlite3 db test.db
000836      set DB [sqlite3_connection_pointer db]
000837      sqlite3_memdebug_fail 1
000838      catchsql {
000839        select * from sqlite_master where rowid>5;
000840      }
000841    } {1 {out of memory}}
000842    do_test capi3-10-5 {
000843      sqlite3_errmsg $::DB
000844    } {out of memory}
000845    ifcapable {utf16} {
000846      do_test capi3-10-6 {
000847        utf8 [sqlite3_errmsg16 $::DB]
000848      } {out of memory}
000849    }
000850    db close
000851    sqlite3_memdebug_fail -1
000852  }
000853  
000854  # The following tests - capi3-11.* - test that a COMMIT or ROLLBACK
000855  # statement issued while there are still outstanding VMs that are part of
000856  # the transaction fails.
000857  sqlite3 db test.db
000858  set DB [sqlite3_connection_pointer db]
000859  sqlite_register_test_function $DB func
000860  do_test capi3-11.1 {
000861    execsql {
000862      BEGIN;
000863      CREATE TABLE t1(a, b);
000864      INSERT INTO t1 VALUES(1, 'int');
000865      INSERT INTO t1 VALUES(2, 'notatype');
000866    }
000867  } {}
000868  do_test capi3-11.1.1 {
000869    sqlite3_get_autocommit $DB
000870  } 0
000871  do_test capi3-11.2 {
000872    set STMT [sqlite3_prepare $DB "SELECT func(b, a) FROM t1" -1 TAIL]
000873    sqlite3_step $STMT
000874  } {SQLITE_ROW}
000875  
000876  # As of 3.6.5 a COMMIT is OK during while a query is still running -
000877  # as long as it is a read-only query and not an incremental BLOB write.
000878  #
000879  do_test capi3-11.3.1 {
000880    catchsql {
000881      COMMIT;
000882    }
000883  } {0 {}}
000884  do_test capi3-11.3.2 {
000885    sqlite3_extended_errcode $DB
000886  } {SQLITE_OK}
000887  do_test capi3-11.3.3 {
000888    sqlite3_get_autocommit $DB
000889  } 1
000890  do_test capi3-11.3.4 {
000891    db eval {PRAGMA lock_status}
000892  } {main shared temp closed}
000893  
000894  do_test capi3-11.4 {
000895    sqlite3_step $STMT
000896  } {SQLITE_ERROR}
000897  do_test capi3-11.5 {
000898    sqlite3_finalize $STMT
000899  } {SQLITE_ERROR}
000900  do_test capi3-11.6 {
000901    catchsql {
000902      SELECT * FROM t1;
000903    }
000904  } {0 {1 int 2 notatype}}
000905  do_test capi3-11.7 {
000906    sqlite3_get_autocommit $DB
000907  } 1
000908  do_test capi3-11.8 {
000909    execsql {
000910      CREATE TABLE t2(a);
000911      INSERT INTO t2 VALUES(1);
000912      INSERT INTO t2 VALUES(2);
000913      BEGIN;
000914      INSERT INTO t2 VALUES(3);
000915    }
000916  } {}
000917  do_test capi3-11.8.1 {
000918    sqlite3_get_autocommit $DB
000919  } 0
000920  do_test capi3-11.9 {
000921    set STMT [sqlite3_prepare $DB "SELECT a FROM t2" -1 TAIL]
000922    sqlite3_step $STMT
000923  } {SQLITE_ROW}
000924  do_test capi3-11.9.1 {
000925    sqlite3_get_autocommit $DB
000926  } 0
000927  do_test capi3-11.9.2 {
000928    catchsql {
000929      ROLLBACK;
000930    }
000931  } {0 {}}
000932  do_test capi3-11.9.3 {
000933    sqlite3_get_autocommit $DB
000934  } 1
000935  do_test capi3-11.10 {
000936    sqlite3_step $STMT
000937  } {SQLITE_ROW}
000938  do_test capi3-11.11 {
000939    sqlite3_step $STMT
000940  } {SQLITE_DONE}
000941  ifcapable !autoreset {
000942    do_test capi3-11.12armor {
000943      sqlite3_step $STMT
000944      sqlite3_step $STMT
000945    } {SQLITE_MISUSE}
000946  } else {
000947    do_test capi3-11.12 {
000948      sqlite3_step $STMT
000949      sqlite3_step $STMT
000950    } {SQLITE_ROW}
000951  }
000952  do_test capi3-11.13 {
000953    sqlite3_finalize $STMT
000954  } {SQLITE_OK}
000955  do_test capi3-11.14 {
000956    execsql {
000957      SELECT a FROM t2;
000958    }
000959  } {1 2}
000960  do_test capi3-11.14.1 {
000961    sqlite3_get_autocommit $DB
000962  } 1
000963  do_test capi3-11.15 {
000964    catchsql {
000965      ROLLBACK;
000966    }
000967  } {1 {cannot rollback - no transaction is active}}
000968  do_test capi3-11.15.1 {
000969    sqlite3_get_autocommit $DB
000970  } 1
000971  do_test capi3-11.16 {
000972    execsql {
000973      SELECT a FROM t2;
000974    }
000975  } {1 2}
000976  
000977  # Sanity check on the definition of 'outstanding VM'. This means any VM
000978  # that has had sqlite3_step() called more recently than sqlite3_finalize() or
000979  # sqlite3_reset(). So a VM that has just been prepared or reset does not
000980  # count as an active VM.
000981  do_test capi3-11.17 {
000982    execsql {
000983      BEGIN;
000984    }
000985  } {}
000986  do_test capi3-11.18 {
000987    set STMT [sqlite3_prepare $DB "SELECT a FROM t1" -1 TAIL]
000988    catchsql {
000989      COMMIT;
000990    }
000991  } {0 {}}
000992  do_test capi3-11.19 {
000993    sqlite3_step $STMT
000994  } {SQLITE_ROW}
000995  do_test capi3-11.20 {
000996    catchsql {
000997      BEGIN;
000998      COMMIT;
000999    }
001000  } {0 {}}
001001  do_test capi3-11.20 {
001002    sqlite3_reset $STMT
001003    catchsql {
001004      COMMIT;
001005    }
001006  } {1 {cannot commit - no transaction is active}}
001007  do_test capi3-11.21 {
001008    sqlite3_finalize $STMT
001009  } {SQLITE_OK}
001010  
001011  # The following tests - capi3-12.* - check that its Ok to start a
001012  # transaction while other VMs are active, and that its Ok to execute
001013  # atomic updates in the same situation 
001014  #
001015  do_test capi3-12.1 {
001016    set STMT [sqlite3_prepare $DB "SELECT a FROM t2" -1 TAIL]
001017    sqlite3_step $STMT
001018  } {SQLITE_ROW}
001019  do_test capi3-12.2 {
001020    catchsql {
001021      INSERT INTO t1 VALUES(3, NULL);
001022    }
001023  } {0 {}}
001024  do_test capi3-12.3 {
001025    catchsql {
001026      INSERT INTO t2 VALUES(4);
001027    }
001028  } {0 {}}
001029  do_test capi3-12.4 {
001030    catchsql {
001031      BEGIN;
001032      INSERT INTO t1 VALUES(4, NULL);
001033    }
001034  } {0 {}}
001035  do_test capi3-12.5 {
001036    sqlite3_step $STMT
001037  } {SQLITE_ROW}
001038  do_test capi3-12.5.1 {
001039    sqlite3_step $STMT
001040  } {SQLITE_ROW}
001041  do_test capi3-12.6 {
001042    sqlite3_step $STMT
001043  } {SQLITE_DONE}
001044  do_test capi3-12.7 {
001045    sqlite3_finalize $STMT
001046  } {SQLITE_OK}
001047  do_test capi3-12.8 {
001048    execsql {
001049      COMMIT;
001050      SELECT a FROM t1;
001051    }
001052  } {1 2 3 4}
001053  
001054  # Test cases capi3-13.* test the sqlite3_clear_bindings() and 
001055  # sqlite3_sleep APIs.
001056  #
001057  if {[llength [info commands sqlite3_clear_bindings]]>0} {
001058    do_test capi3-13.1 {
001059      execsql {
001060        DELETE FROM t1;
001061      }
001062      set STMT [sqlite3_prepare $DB "INSERT INTO t1 VALUES(?, ?)" -1 TAIL]
001063      sqlite3_step $STMT
001064    } {SQLITE_DONE}
001065    do_test capi3-13.2 {
001066      sqlite3_reset $STMT
001067      sqlite3_bind_text $STMT 1 hello 5
001068      sqlite3_bind_text $STMT 2 world 5
001069      sqlite3_step $STMT
001070    } {SQLITE_DONE}
001071    do_test capi3-13.3 {
001072      sqlite3_reset $STMT
001073      sqlite3_clear_bindings $STMT
001074      sqlite3_step $STMT
001075    } {SQLITE_DONE}
001076    do_test capi3-13-4 {
001077      sqlite3_finalize $STMT
001078      execsql {
001079        SELECT * FROM t1;
001080      }
001081    } {{} {} hello world {} {}}
001082  }
001083  if {[llength [info commands sqlite3_sleep]]>0} {
001084    do_test capi3-13-5 {
001085      set ms [sqlite3_sleep 80]
001086      expr {$ms==80 || $ms==1000}
001087    } {1}
001088  }
001089  
001090  # Ticket #1219:  Make sure binding APIs can handle a NULL pointer.
001091  # 
001092  if {[clang_sanitize_address]==0} {
001093    do_test capi3-14.1-misuse {
001094      set rc [catch {sqlite3_bind_text 0 1 hello 5} msg]
001095        lappend rc $msg
001096    } {1 SQLITE_MISUSE}
001097  }
001098  
001099  # Ticket #1650:  Honor the nBytes parameter to sqlite3_prepare.
001100  #
001101  do_test capi3-15.1 {
001102    set sql {SELECT * FROM t2}
001103    set nbytes [string length $sql]
001104    append sql { WHERE a==1}
001105    set STMT [sqlite3_prepare $DB $sql $nbytes TAIL]
001106    sqlite3_step $STMT
001107    sqlite3_column_int $STMT 0
001108  } {1}
001109  do_test capi3-15.2 {
001110    sqlite3_step $STMT
001111    sqlite3_column_int $STMT 0
001112  } {2}
001113  do_test capi3-15.3 {
001114    sqlite3_finalize $STMT
001115  } {SQLITE_OK}
001116  do_test capi3-15.4 {
001117    #        123456789 1234567
001118    set sql {SELECT 1234567890}
001119    set STMT [sqlite3_prepare $DB $sql 8 TAIL]
001120    sqlite3_step $STMT
001121    set v1 [sqlite3_column_int $STMT 0]
001122    sqlite3_finalize $STMT
001123    set v1
001124  } {1}
001125  do_test capi3-15.5 {
001126    #        123456789 1234567
001127    set sql {SELECT 1234567890}
001128    set STMT [sqlite3_prepare $DB $sql 9 TAIL]
001129    sqlite3_step $STMT
001130    set v1 [sqlite3_column_int $STMT 0]
001131    sqlite3_finalize $STMT
001132    set v1
001133  } {12}
001134  do_test capi3-15.6 {
001135    #        123456789 1234567
001136    set sql {SELECT 1234567890}
001137    set STMT [sqlite3_prepare $DB $sql 12 TAIL]
001138    sqlite3_step $STMT
001139    set v1 [sqlite3_column_int $STMT 0]
001140    sqlite3_finalize $STMT
001141    set v1
001142  } {12345}
001143  do_test capi3-15.7 {
001144    #        123456789 1234567
001145    set sql {SELECT 12.34567890}
001146    set STMT [sqlite3_prepare $DB $sql 12 TAIL]
001147    sqlite3_step $STMT
001148    set v1 [sqlite3_column_double $STMT 0]
001149    sqlite3_finalize $STMT
001150    set v1
001151  } {12.34}
001152  do_test capi3-15.8 {
001153    #        123456789 1234567
001154    set sql {SELECT 12.34567890}
001155    set STMT [sqlite3_prepare $DB $sql 14 TAIL]
001156    sqlite3_step $STMT
001157    set v1 [sqlite3_column_double $STMT 0]
001158    sqlite3_finalize $STMT
001159    set v1
001160  } {12.3456}
001161  
001162  # Make sure code is always generated even if an IF EXISTS or 
001163  # IF NOT EXISTS clause is present that the table does not or
001164  # does exists.  That way we will always have a prepared statement
001165  # to expire when the schema changes.
001166  #
001167  do_test capi3-16.1 {
001168    set sql {DROP TABLE IF EXISTS t3}
001169    set STMT [sqlite3_prepare $DB $sql -1 TAIL]
001170    sqlite3_finalize $STMT
001171    expr {$STMT!=""}
001172  } {1}
001173  do_test capi3-16.2 {
001174    set sql {CREATE TABLE IF NOT EXISTS t1(x,y)}
001175    set STMT [sqlite3_prepare $DB $sql -1 TAIL]
001176    sqlite3_finalize $STMT
001177    expr {$STMT!=""}
001178  } {1}
001179  
001180  # But still we do not generate code if there is no SQL
001181  #
001182  do_test capi3-16.3 {
001183    set STMT [sqlite3_prepare $DB {} -1 TAIL]
001184    sqlite3_finalize $STMT
001185    expr {$STMT==""}
001186  } {1}
001187  do_test capi3-16.4 {
001188    set STMT [sqlite3_prepare $DB {;} -1 TAIL]
001189    sqlite3_finalize $STMT
001190    expr {$STMT==""}
001191  } {1}
001192  
001193  # Ticket #2426:  Misuse of sqlite3_column_* by calling it after
001194  # a sqlite3_reset should be harmless.
001195  #
001196  do_test capi3-17.1 {
001197    set STMT [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
001198    sqlite3_step $STMT
001199    sqlite3_column_int $STMT 0
001200  } {1}
001201  do_test capi3-17.2 {
001202    sqlite3_reset $STMT
001203    sqlite3_column_int $STMT 0
001204  } {0}
001205  do_test capi3-17.3 {
001206    sqlite3_finalize $STMT
001207  } {SQLITE_OK}
001208  
001209  # Verify that sqlite3_step() fails with an SQLITE_SCHEMA error
001210  # when the statement is prepared with sqlite3_prepare() (not
001211  # sqlite3_prepare_v2()) and the schema has changed.
001212  #
001213  do_test capi3-18.1 {
001214    set STMT [sqlite3_prepare db {SELECT * FROM t2} -1 TAIL]
001215    sqlite3 db2 test.db
001216    db2 eval {CREATE TABLE t3(x)}
001217    db2 close
001218    sqlite3_step $STMT
001219  } {SQLITE_ERROR}
001220  do_test capi3-18.2 {
001221    sqlite3_reset $STMT
001222    sqlite3_errcode db
001223  } {SQLITE_SCHEMA}
001224  do_test capi3-18.3 {
001225    sqlite3_errmsg db
001226  } {database schema has changed}
001227  # The error persist on retry when sqlite3_prepare() has been used.
001228  do_test capi3-18.4 {
001229    sqlite3_step $STMT
001230  } {SQLITE_ERROR}
001231  do_test capi3-18.5 {
001232    sqlite3_reset $STMT
001233    sqlite3_errcode db
001234  } {SQLITE_SCHEMA}
001235  do_test capi3-18.6 {
001236    sqlite3_errmsg db
001237  } {database schema has changed}
001238  sqlite3_finalize $STMT
001239  
001240  # Ticket #3134.  Prepare a statement with an nBytes parameter of 0.
001241  # Make sure this works correctly and does not reference memory out of
001242  # range.
001243  #
001244  do_test capi3-19.1 {
001245    sqlite3_prepare_tkt3134 db
001246  } {}
001247  
001248  # Test that calling sqlite3_column_blob() on a TEXT value does not change
001249  # the return type of subsequent calls to sqlite3_column_type().
001250  #
001251  do_execsql_test 20.1 {
001252    CREATE TABLE t4(x);
001253    INSERT INTO t4 VALUES('abcdefghij');
001254  }
001255  do_test 20.2 {
001256    set stmt [sqlite3_prepare db "SELECT * FROM t4" -1 dummy]
001257    sqlite3_step $stmt
001258  } {SQLITE_ROW}
001259  do_test 20.3 { sqlite3_column_type $stmt 0 } {TEXT}
001260  do_test 20.4 { sqlite3_column_blob $stmt 0 } {abcdefghij}
001261  do_test 20.5 { sqlite3_column_type $stmt 0 } {TEXT}
001262  do_test 20.6 { sqlite3_finalize $stmt } SQLITE_OK
001263  
001264  
001265  # Tests of the interface when no VFS is registered.
001266  #
001267  if {![info exists tester_do_binarylog]} {
001268    db close
001269    vfs_unregister_all
001270    do_test capi3-20.1 {
001271      sqlite3_sleep 100
001272    } {0}
001273    vfs_reregister_all
001274  }
001275  
001276  finish_test