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