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