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  
000653  set ::ENC [execsql {pragma encoding}]
000654  db close
000655  
000656  do_test capi3-6.0 {
000657    sqlite3 db test.db
000658    set DB [sqlite3_connection_pointer db]
000659    if {[sqlite3 -has-codec]==0} { sqlite3_key $DB xyzzy }
000660    set sql {SELECT a FROM t1 order by rowid}
000661    set STMT [sqlite3_prepare $DB $sql -1 TAIL]
000662    expr 0
000663  } {0}
000664  do_test capi3-6.1 {
000665    db cache flush
000666    sqlite3_close $DB
000667  } {SQLITE_BUSY}
000668  
000669  # 6.2 and 6.3 used to return SQLITE_ERROR and SQLITE_SCHEMA, respectively.
000670  # But since attempting to close a connection no longer resets the internal
000671  # schema and expires all statements, this is no longer the case.
000672  do_test capi3-6.2 {
000673    sqlite3_step $STMT
000674  } {SQLITE_ROW}
000675  #check_data $STMT capi3-6.3 {INTEGER} {1} {1.0} {1}
000676  do_test capi3-6.3 {
000677    sqlite3_finalize $STMT
000678  } {SQLITE_OK}
000679  
000680  if {[clang_sanitize_address]==0} {
000681    do_test capi3-6.4-misuse {
000682      db cache flush
000683      sqlite3_close $DB
000684    } {SQLITE_OK}
000685  }
000686  db close
000687  
000688  # This procedure sets the value of the file-format in file 'test.db'
000689  # to $newval. Also, the schema cookie is incremented.
000690  # 
000691  proc set_file_format {newval} {
000692    hexio_write test.db 44 [hexio_render_int32 $newval]
000693    set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
000694    incr schemacookie
000695    hexio_write test.db 40 [hexio_render_int32 $schemacookie]
000696    return {}
000697  }
000698  
000699  # This procedure returns the value of the file-format in file 'test.db'.
000700  # 
000701  proc get_file_format {{fname test.db}} {
000702    return [hexio_get_int [hexio_read $fname 44 4]]
000703  }
000704  
000705  if {![sqlite3 -has-codec]} {
000706    # Test what happens when the library encounters a newer file format.
000707    do_test capi3-7.1 {
000708      set_file_format 5
000709    } {}
000710    do_test capi3-7.2 {
000711      catch { sqlite3 db test.db }
000712      catchsql {
000713        SELECT * FROM sqlite_master;
000714      }
000715    } {1 {unsupported file format}}
000716    db close
000717  }
000718  
000719  if {![sqlite3 -has-codec]} {
000720    # Now test that the library correctly handles bogus entries in the
000721    # sqlite_master table (schema corruption).
000722    do_test capi3-8.1 {
000723      forcedelete test.db test.db-journal
000724      sqlite3 db test.db
000725      execsql {
000726        CREATE TABLE t1(a);
000727      }
000728      db close
000729    } {}
000730    do_test capi3-8.2 {
000731      sqlite3 db test.db
000732      execsql {
000733        PRAGMA writable_schema=ON;
000734        INSERT INTO sqlite_master VALUES(NULL,NULL,NULL,NULL,NULL);
000735      }
000736      db close
000737    } {}
000738    do_test capi3-8.3 {
000739      catch { sqlite3 db test.db }
000740      catchsql {
000741        SELECT * FROM sqlite_master;
000742      }
000743    } {1 {malformed database schema (?)}}
000744    do_test capi3-8.4 {
000745      # Build a 5-field row record. The first field is a string 'table', and
000746      # subsequent fields are all NULL.
000747      db close
000748      forcedelete test.db test.db-journal
000749      sqlite3 db test.db
000750      execsql {
000751        CREATE TABLE t1(a);
000752        PRAGMA writable_schema=ON;
000753        INSERT INTO sqlite_master VALUES('table',NULL,NULL,NULL,NULL);
000754      }
000755      db close
000756    } {};
000757    do_test capi3-8.5 {
000758      catch { sqlite3 db test.db }
000759      catchsql {
000760        SELECT * FROM sqlite_master;
000761      }
000762    } {1 {malformed database schema (?)}}
000763    db close
000764  }
000765  forcedelete test.db
000766  forcedelete test.db-journal
000767  
000768  
000769  # Test the english language string equivalents for sqlite error codes
000770  set code2english [list \
000771  SQLITE_OK         {not an error} \
000772  SQLITE_ERROR      {SQL logic error} \
000773  SQLITE_PERM       {access permission denied} \
000774  SQLITE_ABORT      {query aborted} \
000775  SQLITE_BUSY       {database is locked} \
000776  SQLITE_LOCKED     {database table is locked} \
000777  SQLITE_NOMEM      {out of memory} \
000778  SQLITE_READONLY   {attempt to write a readonly database} \
000779  SQLITE_INTERRUPT  {interrupted} \
000780  SQLITE_IOERR      {disk I/O error} \
000781  SQLITE_CORRUPT    {database disk image is malformed} \
000782  SQLITE_FULL       {database or disk is full} \
000783  SQLITE_CANTOPEN   {unable to open database file} \
000784  SQLITE_SCHEMA     {database schema has changed} \
000785  SQLITE_CONSTRAINT {constraint failed} \
000786  SQLITE_MISMATCH   {datatype mismatch} \
000787  SQLITE_MISUSE     {bad parameter or other API misuse} \
000788  SQLITE_AUTH       {authorization denied} \
000789  SQLITE_RANGE      {column index out of range} \
000790  SQLITE_NOTADB     {file is not a database} \
000791  unknownerror      {unknown error} \
000792  ]
000793  
000794  set test_number 1
000795  foreach {code english} $code2english {
000796    do_test capi3-9.$test_number "sqlite3_test_errstr $code" $english
000797    incr test_number
000798  }
000799  
000800  # Test the error message when a "real" out of memory occurs.
000801  if { [permutation] != "nofaultsim" } {
000802  ifcapable memdebug {
000803    do_test capi3-10-1 {
000804      sqlite3 db test.db
000805      set DB [sqlite3_connection_pointer db]
000806      sqlite3_memdebug_fail 1
000807      catchsql {
000808        select * from sqlite_master;
000809      }
000810    } {1 {out of memory}}
000811    do_test capi3-10-2 {
000812      sqlite3_errmsg $::DB
000813    } {out of memory}
000814    ifcapable {utf16} {
000815      do_test capi3-10-3 {
000816        utf8 [sqlite3_errmsg16 $::DB]
000817      } {out of memory}
000818    }
000819    db close
000820    sqlite3_memdebug_fail -1
000821    do_test capi3-10-4 {
000822      sqlite3 db test.db
000823      set DB [sqlite3_connection_pointer db]
000824      sqlite3_memdebug_fail 1
000825      catchsql {
000826        select * from sqlite_master where rowid>5;
000827      }
000828    } {1 {out of memory}}
000829    do_test capi3-10-5 {
000830      sqlite3_errmsg $::DB
000831    } {out of memory}
000832    ifcapable {utf16} {
000833      do_test capi3-10-6 {
000834        utf8 [sqlite3_errmsg16 $::DB]
000835      } {out of memory}
000836    }
000837    db close
000838    sqlite3_memdebug_fail -1
000839  }
000840  }
000841  
000842  # The following tests - capi3-11.* - test that a COMMIT or ROLLBACK
000843  # statement issued while there are still outstanding VMs that are part of
000844  # the transaction fails.
000845  sqlite3 db test.db
000846  set DB [sqlite3_connection_pointer db]
000847  sqlite_register_test_function $DB func
000848  do_test capi3-11.1 {
000849    execsql {
000850      BEGIN;
000851      CREATE TABLE t1(a, b);
000852      INSERT INTO t1 VALUES(1, 'int');
000853      INSERT INTO t1 VALUES(2, 'notatype');
000854    }
000855  } {}
000856  do_test capi3-11.1.1 {
000857    sqlite3_get_autocommit $DB
000858  } 0
000859  do_test capi3-11.2 {
000860    set STMT [sqlite3_prepare $DB "SELECT func(b, a) FROM t1" -1 TAIL]
000861    sqlite3_step $STMT
000862  } {SQLITE_ROW}
000863  
000864  # As of 3.6.5 a COMMIT is OK during while a query is still running -
000865  # as long as it is a read-only query and not an incremental BLOB write.
000866  #
000867  do_test capi3-11.3.1 {
000868    catchsql {
000869      COMMIT;
000870    }
000871  } {0 {}}
000872  do_test capi3-11.3.2 {
000873    sqlite3_extended_errcode $DB
000874  } {SQLITE_OK}
000875  do_test capi3-11.3.3 {
000876    sqlite3_get_autocommit $DB
000877  } 1
000878  do_test capi3-11.3.4 {
000879    db eval {PRAGMA lock_status}
000880  } {main shared temp closed}
000881  
000882  do_test capi3-11.4 {
000883    sqlite3_step $STMT
000884  } {SQLITE_ERROR}
000885  do_test capi3-11.5 {
000886    sqlite3_finalize $STMT
000887  } {SQLITE_ERROR}
000888  do_test capi3-11.6 {
000889    catchsql {
000890      SELECT * FROM t1;
000891    }
000892  } {0 {1 int 2 notatype}}
000893  do_test capi3-11.7 {
000894    sqlite3_get_autocommit $DB
000895  } 1
000896  do_test capi3-11.8 {
000897    execsql {
000898      CREATE TABLE t2(a);
000899      INSERT INTO t2 VALUES(1);
000900      INSERT INTO t2 VALUES(2);
000901      BEGIN;
000902      INSERT INTO t2 VALUES(3);
000903    }
000904  } {}
000905  do_test capi3-11.8.1 {
000906    sqlite3_get_autocommit $DB
000907  } 0
000908  do_test capi3-11.9 {
000909    set STMT [sqlite3_prepare $DB "SELECT a FROM t2" -1 TAIL]
000910    sqlite3_step $STMT
000911  } {SQLITE_ROW}
000912  do_test capi3-11.9.1 {
000913    sqlite3_get_autocommit $DB
000914  } 0
000915  do_test capi3-11.9.2 {
000916    catchsql {
000917      ROLLBACK;
000918    }
000919  } {0 {}}
000920  do_test capi3-11.9.3 {
000921    sqlite3_get_autocommit $DB
000922  } 1
000923  do_test capi3-11.10 {
000924    sqlite3_step $STMT
000925  } {SQLITE_ROW}
000926  do_test capi3-11.11 {
000927    sqlite3_step $STMT
000928  } {SQLITE_DONE}
000929  ifcapable !autoreset {
000930    do_test capi3-11.12armor {
000931      sqlite3_step $STMT
000932      sqlite3_step $STMT
000933    } {SQLITE_MISUSE}
000934  } else {
000935    do_test capi3-11.12 {
000936      sqlite3_step $STMT
000937      sqlite3_step $STMT
000938    } {SQLITE_ROW}
000939  }
000940  do_test capi3-11.13 {
000941    sqlite3_finalize $STMT
000942  } {SQLITE_OK}
000943  do_test capi3-11.14 {
000944    execsql {
000945      SELECT a FROM t2;
000946    }
000947  } {1 2}
000948  do_test capi3-11.14.1 {
000949    sqlite3_get_autocommit $DB
000950  } 1
000951  do_test capi3-11.15 {
000952    catchsql {
000953      ROLLBACK;
000954    }
000955  } {1 {cannot rollback - no transaction is active}}
000956  do_test capi3-11.15.1 {
000957    sqlite3_get_autocommit $DB
000958  } 1
000959  do_test capi3-11.16 {
000960    execsql {
000961      SELECT a FROM t2;
000962    }
000963  } {1 2}
000964  
000965  # Sanity check on the definition of 'outstanding VM'. This means any VM
000966  # that has had sqlite3_step() called more recently than sqlite3_finalize() or
000967  # sqlite3_reset(). So a VM that has just been prepared or reset does not
000968  # count as an active VM.
000969  do_test capi3-11.17 {
000970    execsql {
000971      BEGIN;
000972    }
000973  } {}
000974  do_test capi3-11.18 {
000975    set STMT [sqlite3_prepare $DB "SELECT a FROM t1" -1 TAIL]
000976    catchsql {
000977      COMMIT;
000978    }
000979  } {0 {}}
000980  do_test capi3-11.19 {
000981    sqlite3_step $STMT
000982  } {SQLITE_ROW}
000983  do_test capi3-11.20 {
000984    catchsql {
000985      BEGIN;
000986      COMMIT;
000987    }
000988  } {0 {}}
000989  do_test capi3-11.20 {
000990    sqlite3_reset $STMT
000991    catchsql {
000992      COMMIT;
000993    }
000994  } {1 {cannot commit - no transaction is active}}
000995  do_test capi3-11.21 {
000996    sqlite3_finalize $STMT
000997  } {SQLITE_OK}
000998  
000999  # The following tests - capi3-12.* - check that its Ok to start a
001000  # transaction while other VMs are active, and that its Ok to execute
001001  # atomic updates in the same situation 
001002  #
001003  do_test capi3-12.1 {
001004    set STMT [sqlite3_prepare $DB "SELECT a FROM t2" -1 TAIL]
001005    sqlite3_step $STMT
001006  } {SQLITE_ROW}
001007  do_test capi3-12.2 {
001008    catchsql {
001009      INSERT INTO t1 VALUES(3, NULL);
001010    }
001011  } {0 {}}
001012  do_test capi3-12.3 {
001013    catchsql {
001014      INSERT INTO t2 VALUES(4);
001015    }
001016  } {0 {}}
001017  do_test capi3-12.4 {
001018    catchsql {
001019      BEGIN;
001020      INSERT INTO t1 VALUES(4, NULL);
001021    }
001022  } {0 {}}
001023  do_test capi3-12.5 {
001024    sqlite3_step $STMT
001025  } {SQLITE_ROW}
001026  do_test capi3-12.5.1 {
001027    sqlite3_step $STMT
001028  } {SQLITE_ROW}
001029  do_test capi3-12.6 {
001030    sqlite3_step $STMT
001031  } {SQLITE_DONE}
001032  do_test capi3-12.7 {
001033    sqlite3_finalize $STMT
001034  } {SQLITE_OK}
001035  do_test capi3-12.8 {
001036    execsql {
001037      COMMIT;
001038      SELECT a FROM t1;
001039    }
001040  } {1 2 3 4}
001041  
001042  # Test cases capi3-13.* test the sqlite3_clear_bindings() and 
001043  # sqlite3_sleep APIs.
001044  #
001045  if {[llength [info commands sqlite3_clear_bindings]]>0} {
001046    do_test capi3-13.1 {
001047      execsql {
001048        DELETE FROM t1;
001049      }
001050      set STMT [sqlite3_prepare $DB "INSERT INTO t1 VALUES(?, ?)" -1 TAIL]
001051      sqlite3_step $STMT
001052    } {SQLITE_DONE}
001053    do_test capi3-13.2 {
001054      sqlite3_reset $STMT
001055      sqlite3_bind_text $STMT 1 hello 5
001056      sqlite3_bind_text $STMT 2 world 5
001057      sqlite3_step $STMT
001058    } {SQLITE_DONE}
001059    do_test capi3-13.3 {
001060      sqlite3_reset $STMT
001061      sqlite3_clear_bindings $STMT
001062      sqlite3_step $STMT
001063    } {SQLITE_DONE}
001064    do_test capi3-13-4 {
001065      sqlite3_finalize $STMT
001066      execsql {
001067        SELECT * FROM t1;
001068      }
001069    } {{} {} hello world {} {}}
001070  }
001071  if {[llength [info commands sqlite3_sleep]]>0} {
001072    do_test capi3-13-5 {
001073      set ms [sqlite3_sleep 80]
001074      expr {$ms==80 || $ms==1000}
001075    } {1}
001076  }
001077  
001078  # Ticket #1219:  Make sure binding APIs can handle a NULL pointer.
001079  # 
001080  if {[clang_sanitize_address]==0} {
001081    do_test capi3-14.1-misuse {
001082      set rc [catch {sqlite3_bind_text 0 1 hello 5} msg]
001083        lappend rc $msg
001084    } {1 SQLITE_MISUSE}
001085  }
001086  
001087  # Ticket #1650:  Honor the nBytes parameter to sqlite3_prepare.
001088  #
001089  do_test capi3-15.1 {
001090    set sql {SELECT * FROM t2}
001091    set nbytes [string length $sql]
001092    append sql { WHERE a==1}
001093    set STMT [sqlite3_prepare $DB $sql $nbytes TAIL]
001094    sqlite3_step $STMT
001095    sqlite3_column_int $STMT 0
001096  } {1}
001097  do_test capi3-15.2 {
001098    sqlite3_step $STMT
001099    sqlite3_column_int $STMT 0
001100  } {2}
001101  do_test capi3-15.3 {
001102    sqlite3_finalize $STMT
001103  } {SQLITE_OK}
001104  do_test capi3-15.4 {
001105    #        123456789 1234567
001106    set sql {SELECT 1234567890}
001107    set STMT [sqlite3_prepare $DB $sql 8 TAIL]
001108    sqlite3_step $STMT
001109    set v1 [sqlite3_column_int $STMT 0]
001110    sqlite3_finalize $STMT
001111    set v1
001112  } {1}
001113  do_test capi3-15.5 {
001114    #        123456789 1234567
001115    set sql {SELECT 1234567890}
001116    set STMT [sqlite3_prepare $DB $sql 9 TAIL]
001117    sqlite3_step $STMT
001118    set v1 [sqlite3_column_int $STMT 0]
001119    sqlite3_finalize $STMT
001120    set v1
001121  } {12}
001122  do_test capi3-15.6 {
001123    #        123456789 1234567
001124    set sql {SELECT 1234567890}
001125    set STMT [sqlite3_prepare $DB $sql 12 TAIL]
001126    sqlite3_step $STMT
001127    set v1 [sqlite3_column_int $STMT 0]
001128    sqlite3_finalize $STMT
001129    set v1
001130  } {12345}
001131  do_test capi3-15.7 {
001132    #        123456789 1234567
001133    set sql {SELECT 12.34567890}
001134    set STMT [sqlite3_prepare $DB $sql 12 TAIL]
001135    sqlite3_step $STMT
001136    set v1 [sqlite3_column_double $STMT 0]
001137    sqlite3_finalize $STMT
001138    set v1
001139  } {12.34}
001140  do_test capi3-15.8 {
001141    #        123456789 1234567
001142    set sql {SELECT 12.34567890}
001143    set STMT [sqlite3_prepare $DB $sql 14 TAIL]
001144    sqlite3_step $STMT
001145    set v1 [sqlite3_column_double $STMT 0]
001146    sqlite3_finalize $STMT
001147    set v1
001148  } {12.3456}
001149  
001150  # Make sure code is always generated even if an IF EXISTS or 
001151  # IF NOT EXISTS clause is present that the table does not or
001152  # does exists.  That way we will always have a prepared statement
001153  # to expire when the schema changes.
001154  #
001155  do_test capi3-16.1 {
001156    set sql {DROP TABLE IF EXISTS t3}
001157    set STMT [sqlite3_prepare $DB $sql -1 TAIL]
001158    sqlite3_finalize $STMT
001159    expr {$STMT!=""}
001160  } {1}
001161  do_test capi3-16.2 {
001162    set sql {CREATE TABLE IF NOT EXISTS t1(x,y)}
001163    set STMT [sqlite3_prepare $DB $sql -1 TAIL]
001164    sqlite3_finalize $STMT
001165    expr {$STMT!=""}
001166  } {1}
001167  
001168  # But still we do not generate code if there is no SQL
001169  #
001170  do_test capi3-16.3 {
001171    set STMT [sqlite3_prepare $DB {} -1 TAIL]
001172    sqlite3_finalize $STMT
001173    expr {$STMT==""}
001174  } {1}
001175  do_test capi3-16.4 {
001176    set STMT [sqlite3_prepare $DB {;} -1 TAIL]
001177    sqlite3_finalize $STMT
001178    expr {$STMT==""}
001179  } {1}
001180  
001181  # Ticket #2426:  Misuse of sqlite3_column_* by calling it after
001182  # a sqlite3_reset should be harmless.
001183  #
001184  do_test capi3-17.1 {
001185    set STMT [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
001186    sqlite3_step $STMT
001187    sqlite3_column_int $STMT 0
001188  } {1}
001189  do_test capi3-17.2 {
001190    sqlite3_reset $STMT
001191    sqlite3_column_int $STMT 0
001192  } {0}
001193  do_test capi3-17.3 {
001194    sqlite3_finalize $STMT
001195  } {SQLITE_OK}
001196  
001197  # Verify that sqlite3_step() fails with an SQLITE_SCHEMA error
001198  # when the statement is prepared with sqlite3_prepare() (not
001199  # sqlite3_prepare_v2()) and the schema has changed.
001200  #
001201  do_test capi3-18.1 {
001202    set STMT [sqlite3_prepare db {SELECT * FROM t2} -1 TAIL]
001203    sqlite3 db2 test.db
001204    db2 eval {CREATE TABLE t3(x)}
001205    db2 close
001206    sqlite3_step $STMT
001207  } {SQLITE_ERROR}
001208  do_test capi3-18.2 {
001209    sqlite3_reset $STMT
001210    sqlite3_errcode db
001211  } {SQLITE_SCHEMA}
001212  do_test capi3-18.3 {
001213    sqlite3_errmsg db
001214  } {database schema has changed}
001215  # The error persist on retry when sqlite3_prepare() has been used.
001216  do_test capi3-18.4 {
001217    sqlite3_step $STMT
001218  } {SQLITE_ERROR}
001219  do_test capi3-18.5 {
001220    sqlite3_reset $STMT
001221    sqlite3_errcode db
001222  } {SQLITE_SCHEMA}
001223  do_test capi3-18.6 {
001224    sqlite3_errmsg db
001225  } {database schema has changed}
001226  sqlite3_finalize $STMT
001227  
001228  # Ticket #3134.  Prepare a statement with an nBytes parameter of 0.
001229  # Make sure this works correctly and does not reference memory out of
001230  # range.
001231  #
001232  do_test capi3-19.1 {
001233    sqlite3_prepare_tkt3134 db
001234  } {}
001235  
001236  # Test that calling sqlite3_column_blob() on a TEXT value does not change
001237  # the return type of subsequent calls to sqlite3_column_type().
001238  #
001239  do_execsql_test 20.1 {
001240    CREATE TABLE t4(x);
001241    INSERT INTO t4 VALUES('abcdefghij');
001242  }
001243  do_test 20.2 {
001244    set stmt [sqlite3_prepare db "SELECT * FROM t4" -1 dummy]
001245    sqlite3_step $stmt
001246  } {SQLITE_ROW}
001247  do_test 20.3 { sqlite3_column_type $stmt 0 } {TEXT}
001248  do_test 20.4 { sqlite3_column_blob $stmt 0 } {abcdefghij}
001249  do_test 20.5 { sqlite3_column_type $stmt 0 } {TEXT}
001250  do_test 20.6 { sqlite3_finalize $stmt } SQLITE_OK
001251  
001252  
001253  # Tests of the interface when no VFS is registered.
001254  #
001255  if {![info exists tester_do_binarylog]} {
001256    db close
001257    vfs_unregister_all
001258    do_test capi3-20.1 {
001259      sqlite3_sleep 100
001260    } {0}
001261    vfs_reregister_all
001262  }
001263  
001264  finish_test