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