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