1# 2006 November 08 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. 12# 13# This is a copy of the capi3.test file that has been adapted to 14# test the new sqlite3_prepare_v2 interface. 15# 16# $Id: capi3c.test,v 1.23 2009/07/22 07:27:57 danielk1977 Exp $ 17# 18 19set testdir [file dirname $argv0] 20source $testdir/tester.tcl 21 22# Do not use a codec for tests in this file, as the database file is 23# manipulated directly using tcl scripts (using the [hexio_write] command). 24# 25do_not_use_codec 26 27# Return the UTF-16 representation of the supplied UTF-8 string $str. 28# If $nt is true, append two 0x00 bytes as a nul terminator. 29proc utf16 {str {nt 1}} { 30 set r [encoding convertto unicode $str] 31 if {$nt} { 32 append r "\x00\x00" 33 } 34 return $r 35} 36 37# Return the UTF-8 representation of the supplied UTF-16 string $str. 38proc utf8 {str} { 39 # If $str ends in two 0x00 0x00 bytes, knock these off before 40 # converting to UTF-8 using TCL. 41 binary scan $str \c* vals 42 if {[lindex $vals end]==0 && [lindex $vals end-1]==0} { 43 set str [binary format \c* [lrange $vals 0 end-2]] 44 } 45 46 set r [encoding convertfrom unicode $str] 47 return $r 48} 49 50# These tests complement those in capi2.test. They are organized 51# as follows: 52# 53# capi3c-1.*: Test sqlite3_prepare_v2 54# capi3c-2.*: Test sqlite3_prepare16_v2 55# capi3c-3.*: Test sqlite3_open 56# capi3c-4.*: Test sqlite3_open16 57# capi3c-5.*: Test the various sqlite3_result_* APIs 58# capi3c-6.*: Test that sqlite3_close fails if there are outstanding VMs. 59# 60 61set DB [sqlite3_connection_pointer db] 62 63do_test capi3c-1.0 { 64 sqlite3_get_autocommit $DB 65} 1 66do_test capi3c-1.1 { 67 set STMT [sqlite3_prepare_v2 $DB {SELECT name FROM sqlite_master} -1 TAIL] 68 sqlite3_finalize $STMT 69 set TAIL 70} {} 71do_test capi3c-1.2.1 { 72 sqlite3_errcode $DB 73} {SQLITE_OK} 74do_test capi3c-1.2.2 { 75 sqlite3_extended_errcode $DB 76} {SQLITE_OK} 77do_test capi3c-1.3 { 78 sqlite3_errmsg $DB 79} {not an error} 80do_test capi3c-1.4 { 81 set sql {SELECT name FROM sqlite_master;SELECT 10} 82 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL] 83 sqlite3_finalize $STMT 84 set TAIL 85} {SELECT 10} 86do_test capi3c-1.5 { 87 set sql {SELECT namex FROM sqlite_master} 88 catch { 89 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL] 90 } 91} {1} 92do_test capi3c-1.6.1 { 93 sqlite3_errcode $DB 94} {SQLITE_ERROR} 95do_test capi3c-1.6.2 { 96 sqlite3_extended_errcode $DB 97} {SQLITE_ERROR} 98do_test capi3c-1.7 { 99 sqlite3_errmsg $DB 100} {no such column: namex} 101 102 103ifcapable {utf16} { 104 do_test capi3c-2.1 { 105 set sql16 [utf16 {SELECT name FROM sqlite_master}] 106 set STMT [sqlite3_prepare16_v2 $DB $sql16 -1 ::TAIL] 107 sqlite3_finalize $STMT 108 utf8 $::TAIL 109 } {} 110 do_test capi3c-2.2 { 111 set sql [utf16 {SELECT name FROM sqlite_master;SELECT 10}] 112 set STMT [sqlite3_prepare16_v2 $DB $sql -1 TAIL] 113 sqlite3_finalize $STMT 114 utf8 $TAIL 115 } {SELECT 10} 116 do_test capi3c-2.3 { 117 set sql [utf16 {SELECT namex FROM sqlite_master}] 118 catch { 119 set STMT [sqlite3_prepare16_v2 $DB $sql -1 TAIL] 120 } 121 } {1} 122 do_test capi3c-2.4.1 { 123 sqlite3_errcode $DB 124 } {SQLITE_ERROR} 125 do_test capi3c-2.4.2 { 126 sqlite3_extended_errcode $DB 127 } {SQLITE_ERROR} 128 do_test capi3c-2.5 { 129 sqlite3_errmsg $DB 130 } {no such column: namex} 131 132 ifcapable schema_pragmas { 133 do_test capi3c-2.6 { 134 execsql {CREATE TABLE tablename(x)} 135 set sql16 [utf16 {PRAGMA table_info("TableName")}] 136 set STMT [sqlite3_prepare16_v2 $DB $sql16 -1 TAIL] 137 sqlite3_step $STMT 138 } SQLITE_ROW 139 do_test capi3c-2.7 { 140 sqlite3_step $STMT 141 } SQLITE_DONE 142 do_test capi3c-2.8 { 143 sqlite3_finalize $STMT 144 } SQLITE_OK 145 } 146 147} ;# endif utf16 148 149# rename sqlite3_open sqlite3_open_old 150# proc sqlite3_open {fname options} {sqlite3_open_new $fname $options} 151 152do_test capi3c-3.1 { 153 set db2 [sqlite3_open test.db {}] 154 sqlite3_errcode $db2 155} {SQLITE_OK} 156# FIX ME: Should test the db handle works. 157do_test capi3c-3.2 { 158 sqlite3_close $db2 159} {SQLITE_OK} 160do_test capi3c-3.3 { 161 catch { 162 set db2 [sqlite3_open /bogus/path/test.db {}] 163 } 164 sqlite3_errcode $db2 165} {SQLITE_CANTOPEN} 166do_test capi3c-3.4 { 167 sqlite3_errmsg $db2 168} {unable to open database file} 169do_test capi3c-3.5 { 170 sqlite3_close $db2 171} {SQLITE_OK} 172do_test capi3c-3.6.1-misuse { 173 sqlite3_close $db2 174} {SQLITE_MISUSE} 175do_test capi3c-3.6.2-misuse { 176 sqlite3_errmsg $db2 177} {library routine called out of sequence} 178ifcapable {utf16} { 179 do_test capi3c-3.6.3-misuse { 180 utf8 [sqlite3_errmsg16 $db2] 181 } {library routine called out of sequence} 182} 183 184# rename sqlite3_open "" 185# rename sqlite3_open_old sqlite3_open 186 187ifcapable {utf16} { 188do_test capi3c-4.1 { 189 set db2 [sqlite3_open16 [utf16 test.db] {}] 190 sqlite3_errcode $db2 191} {SQLITE_OK} 192# FIX ME: Should test the db handle works. 193do_test capi3c-4.2 { 194 sqlite3_close $db2 195} {SQLITE_OK} 196do_test capi3c-4.3 { 197 catch { 198 set db2 [sqlite3_open16 [utf16 /bogus/path/test.db] {}] 199 } 200 sqlite3_errcode $db2 201} {SQLITE_CANTOPEN} 202do_test capi3c-4.4 { 203 utf8 [sqlite3_errmsg16 $db2] 204} {unable to open database file} 205do_test capi3c-4.5 { 206 sqlite3_close $db2 207} {SQLITE_OK} 208} ;# utf16 209 210# This proc is used to test the following API calls: 211# 212# sqlite3_column_count 213# sqlite3_column_name 214# sqlite3_column_name16 215# sqlite3_column_decltype 216# sqlite3_column_decltype16 217# 218# $STMT is a compiled SQL statement. $test is a prefix 219# to use for test names within this proc. $names is a list 220# of the column names that should be returned by $STMT. 221# $decltypes is a list of column declaration types for $STMT. 222# 223# Example: 224# 225# set STMT [sqlite3_prepare_v2 "SELECT 1, 2, 2;" -1 DUMMY] 226# check_header test1.1 {1 2 3} {"" "" ""} 227# 228proc check_header {STMT test names decltypes} { 229 230 # Use the return value of sqlite3_column_count() to build 231 # a list of column indexes. i.e. If sqlite3_column_count 232 # is 3, build the list {0 1 2}. 233 set ::idxlist [list] 234 set ::numcols [sqlite3_column_count $STMT] 235 for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i} 236 237 # Column names in UTF-8 238 do_test $test.1 { 239 set cnamelist [list] 240 foreach i $idxlist {lappend cnamelist [sqlite3_column_name $STMT $i]} 241 set cnamelist 242 } $names 243 244 # Column names in UTF-16 245 ifcapable {utf16} { 246 do_test $test.2 { 247 set cnamelist [list] 248 foreach i $idxlist { 249 lappend cnamelist [utf8 [sqlite3_column_name16 $STMT $i]] 250 } 251 set cnamelist 252 } $names 253 } 254 255 # Column names in UTF-8 256 do_test $test.3 { 257 set cnamelist [list] 258 foreach i $idxlist {lappend cnamelist [sqlite3_column_name $STMT $i]} 259 set cnamelist 260 } $names 261 262 # Column names in UTF-16 263 ifcapable {utf16} { 264 do_test $test.4 { 265 set cnamelist [list] 266 foreach i $idxlist { 267 lappend cnamelist [utf8 [sqlite3_column_name16 $STMT $i]] 268 } 269 set cnamelist 270 } $names 271 } 272 273 # Column names in UTF-8 274 do_test $test.5 { 275 set cnamelist [list] 276 foreach i $idxlist {lappend cnamelist [sqlite3_column_decltype $STMT $i]} 277 set cnamelist 278 } $decltypes 279 280 # Column declaration types in UTF-16 281 ifcapable {utf16} { 282 do_test $test.6 { 283 set cnamelist [list] 284 foreach i $idxlist { 285 lappend cnamelist [utf8 [sqlite3_column_decltype16 $STMT $i]] 286 } 287 set cnamelist 288 } $decltypes 289 } 290 291 292 # Test some out of range conditions: 293 ifcapable {utf16} { 294 do_test $test.7 { 295 list \ 296 [sqlite3_column_name $STMT -1] \ 297 [sqlite3_column_name16 $STMT -1] \ 298 [sqlite3_column_decltype $STMT -1] \ 299 [sqlite3_column_decltype16 $STMT -1] \ 300 [sqlite3_column_name $STMT $numcols] \ 301 [sqlite3_column_name16 $STMT $numcols] \ 302 [sqlite3_column_decltype $STMT $numcols] \ 303 [sqlite3_column_decltype16 $STMT $numcols] 304 } {{} {} {} {} {} {} {} {}} 305 } 306} 307 308# This proc is used to test the following API calls: 309# 310# sqlite3_column_origin_name 311# sqlite3_column_origin_name16 312# sqlite3_column_table_name 313# sqlite3_column_table_name16 314# sqlite3_column_database_name 315# sqlite3_column_database_name16 316# 317# $STMT is a compiled SQL statement. $test is a prefix 318# to use for test names within this proc. $names is a list 319# of the column names that should be returned by $STMT. 320# $decltypes is a list of column declaration types for $STMT. 321# 322# Example: 323# 324# set STMT [sqlite3_prepare_v2 "SELECT 1, 2, 2;" -1 DUMMY] 325# check_header test1.1 {1 2 3} {"" "" ""} 326# 327proc check_origin_header {STMT test dbs tables cols} { 328 # If sqlite3_column_origin_name() and friends are not compiled into 329 # this build, this proc is a no-op. 330ifcapable columnmetadata { 331 332 # Use the return value of sqlite3_column_count() to build 333 # a list of column indexes. i.e. If sqlite3_column_count 334 # is 3, build the list {0 1 2}. 335 set ::idxlist [list] 336 set ::numcols [sqlite3_column_count $STMT] 337 for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i} 338 339 # Database names in UTF-8 340 do_test $test.8 { 341 set cnamelist [list] 342 foreach i $idxlist { 343 lappend cnamelist [sqlite3_column_database_name $STMT $i] 344 } 345 set cnamelist 346 } $dbs 347 348 # Database names in UTF-16 349 ifcapable {utf16} { 350 do_test $test.9 { 351 set cnamelist [list] 352 foreach i $idxlist { 353 lappend cnamelist [utf8 [sqlite3_column_database_name16 $STMT $i]] 354 } 355 set cnamelist 356 } $dbs 357 } 358 359 # Table names in UTF-8 360 do_test $test.10 { 361 set cnamelist [list] 362 foreach i $idxlist { 363 lappend cnamelist [sqlite3_column_table_name $STMT $i] 364 } 365 set cnamelist 366 } $tables 367 368 # Table names in UTF-16 369 ifcapable {utf16} { 370 do_test $test.11 { 371 set cnamelist [list] 372 foreach i $idxlist { 373 lappend cnamelist [utf8 [sqlite3_column_table_name16 $STMT $i]] 374 } 375 set cnamelist 376 } $tables 377 } 378 379 # Origin names in UTF-8 380 do_test $test.12 { 381 set cnamelist [list] 382 foreach i $idxlist { 383 lappend cnamelist [sqlite3_column_origin_name $STMT $i] 384 } 385 set cnamelist 386 } $cols 387 388 # Origin declaration types in UTF-16 389 ifcapable {utf16} { 390 do_test $test.13 { 391 set cnamelist [list] 392 foreach i $idxlist { 393 lappend cnamelist [utf8 [sqlite3_column_origin_name16 $STMT $i]] 394 } 395 set cnamelist 396 } $cols 397 } 398 } 399} 400 401# This proc is used to test the following APIs: 402# 403# sqlite3_data_count 404# sqlite3_column_type 405# sqlite3_column_int 406# sqlite3_column_text 407# sqlite3_column_text16 408# sqlite3_column_double 409# 410# $STMT is a compiled SQL statement for which the previous call 411# to sqlite3_step returned SQLITE_ROW. $test is a prefix to use 412# for test names within this proc. $types is a list of the 413# manifest types for the current row. $ints, $doubles and $strings 414# are lists of the integer, real and string representations of 415# the values in the current row. 416# 417# Example: 418# 419# set STMT [sqlite3_prepare_v2 "SELECT 'hello', 1.1, NULL" -1 DUMMY] 420# sqlite3_step $STMT 421# check_data test1.2 {TEXT REAL NULL} {0 1 0} {0 1.1 0} {hello 1.1 {}} 422# 423proc check_data {STMT test types ints doubles strings} { 424 425 # Use the return value of sqlite3_column_count() to build 426 # a list of column indexes. i.e. If sqlite3_column_count 427 # is 3, build the list {0 1 2}. 428 set ::idxlist [list] 429 set numcols [sqlite3_data_count $STMT] 430 for {set i 0} {$i < $numcols} {incr i} {lappend ::idxlist $i} 431 432# types 433do_test $test.1 { 434 set types [list] 435 foreach i $idxlist {lappend types [sqlite3_column_type $STMT $i]} 436 set types 437} $types 438 439# Integers 440do_test $test.2 { 441 set ints [list] 442 foreach i $idxlist {lappend ints [sqlite3_column_int64 $STMT $i]} 443 set ints 444} $ints 445 446# bytes 447set lens [list] 448foreach i $::idxlist { 449 lappend lens [string length [lindex $strings $i]] 450} 451do_test $test.3 { 452 set bytes [list] 453 set lens [list] 454 foreach i $idxlist { 455 lappend bytes [sqlite3_column_bytes $STMT $i] 456 } 457 set bytes 458} $lens 459 460# bytes16 461ifcapable {utf16} { 462 set lens [list] 463 foreach i $::idxlist { 464 lappend lens [expr 2 * [string length [lindex $strings $i]]] 465 } 466 do_test $test.4 { 467 set bytes [list] 468 set lens [list] 469 foreach i $idxlist { 470 lappend bytes [sqlite3_column_bytes16 $STMT $i] 471 } 472 set bytes 473 } $lens 474} 475 476# Blob 477do_test $test.5 { 478 set utf8 [list] 479 foreach i $idxlist {lappend utf8 [sqlite3_column_blob $STMT $i]} 480 set utf8 481} $strings 482 483# UTF-8 484do_test $test.6 { 485 set utf8 [list] 486 foreach i $idxlist {lappend utf8 [sqlite3_column_text $STMT $i]} 487 set utf8 488} $strings 489 490# Floats 491do_test $test.7 { 492 set utf8 [list] 493 foreach i $idxlist {lappend utf8 [sqlite3_column_double $STMT $i]} 494 set utf8 495} $doubles 496 497# UTF-16 498ifcapable {utf16} { 499 do_test $test.8 { 500 set utf8 [list] 501 foreach i $idxlist {lappend utf8 [utf8 [sqlite3_column_text16 $STMT $i]]} 502 set utf8 503 } $strings 504} 505 506# Integers 507do_test $test.9 { 508 set ints [list] 509 foreach i $idxlist {lappend ints [sqlite3_column_int $STMT $i]} 510 set ints 511} $ints 512 513# Floats 514do_test $test.10 { 515 set utf8 [list] 516 foreach i $idxlist {lappend utf8 [sqlite3_column_double $STMT $i]} 517 set utf8 518} $doubles 519 520# UTF-8 521do_test $test.11 { 522 set utf8 [list] 523 foreach i $idxlist {lappend utf8 [sqlite3_column_text $STMT $i]} 524 set utf8 525} $strings 526 527# Types 528do_test $test.12 { 529 set types [list] 530 foreach i $idxlist {lappend types [sqlite3_column_type $STMT $i]} 531 set types 532} $types 533 534# Test that an out of range request returns the equivalent of NULL 535do_test $test.13 { 536 sqlite3_column_int $STMT -1 537} {0} 538do_test $test.13 { 539 sqlite3_column_text $STMT -1 540} {} 541 542} 543 544ifcapable !floatingpoint { 545 finish_test 546 return 547} 548 549do_test capi3c-5.0 { 550 execsql { 551 CREATE TABLE t1(a VARINT, b BLOB, c VARCHAR(16)); 552 INSERT INTO t1 VALUES(1, 2, 3); 553 INSERT INTO t1 VALUES('one', 'two', NULL); 554 INSERT INTO t1 VALUES(1.2, 1.3, 1.4); 555 } 556 set sql "SELECT * FROM t1" 557 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL] 558 sqlite3_column_count $STMT 559} 3 560 561check_header $STMT capi3c-5.1 {a b c} {VARINT BLOB VARCHAR(16)} 562check_origin_header $STMT capi3c-5.1 {main main main} {t1 t1 t1} {a b c} 563do_test capi3c-5.2 { 564 sqlite3_step $STMT 565} SQLITE_ROW 566 567check_header $STMT capi3c-5.3 {a b c} {VARINT BLOB VARCHAR(16)} 568check_origin_header $STMT capi3c-5.3 {main main main} {t1 t1 t1} {a b c} 569check_data $STMT capi3c-5.4 {INTEGER INTEGER TEXT} {1 2 3} {1.0 2.0 3.0} {1 2 3} 570 571do_test capi3c-5.5 { 572 sqlite3_step $STMT 573} SQLITE_ROW 574 575check_header $STMT capi3c-5.6 {a b c} {VARINT BLOB VARCHAR(16)} 576check_origin_header $STMT capi3c-5.6 {main main main} {t1 t1 t1} {a b c} 577check_data $STMT capi3c-5.7 {TEXT TEXT NULL} {0 0 0} {0.0 0.0 0.0} {one two {}} 578 579do_test capi3c-5.8 { 580 sqlite3_step $STMT 581} SQLITE_ROW 582 583check_header $STMT capi3c-5.9 {a b c} {VARINT BLOB VARCHAR(16)} 584check_origin_header $STMT capi3c-5.9 {main main main} {t1 t1 t1} {a b c} 585check_data $STMT capi3c-5.10 {FLOAT FLOAT TEXT} {1 1 1} {1.2 1.3 1.4} {1.2 1.3 1.4} 586 587do_test capi3c-5.11 { 588 sqlite3_step $STMT 589} SQLITE_DONE 590 591do_test capi3c-5.12 { 592 sqlite3_finalize $STMT 593} SQLITE_OK 594 595do_test capi3c-5.20 { 596 set sql "SELECT a, sum(b), max(c) FROM t1 GROUP BY a" 597 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL] 598 sqlite3_column_count $STMT 599} 3 600 601check_header $STMT capi3c-5.21 {a sum(b) max(c)} {VARINT {} {}} 602check_origin_header $STMT capi3c-5.22 {main {} {}} {t1 {} {}} {a {} {}} 603do_test capi3c-5.23 { 604 sqlite3_finalize $STMT 605} SQLITE_OK 606 607 608set ::ENC [execsql {pragma encoding}] 609db close 610 611do_test capi3c-6.0 { 612 sqlite3 db test.db 613 set DB [sqlite3_connection_pointer db] 614 if {[sqlite3 -has-codec]==0} { sqlite3_key $DB xyzzy } 615 set sql {SELECT a FROM t1 order by rowid} 616 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL] 617 expr 0 618} {0} 619do_test capi3c-6.1 { 620 db cache flush 621 sqlite3_close $DB 622} {SQLITE_BUSY} 623do_test capi3c-6.2 { 624 sqlite3_step $STMT 625} {SQLITE_ROW} 626check_data $STMT capi3c-6.3 {INTEGER} {1} {1.0} {1} 627do_test capi3c-6.3 { 628 sqlite3_finalize $STMT 629} {SQLITE_OK} 630do_test capi3c-6.4 { 631 db cache flush 632 sqlite3_close $DB 633} {SQLITE_OK} 634do_test capi3c-6.99-misuse { 635 db close 636} {} 637 638# This procedure sets the value of the file-format in file 'test.db' 639# to $newval. Also, the schema cookie is incremented. 640# 641proc set_file_format {newval} { 642 hexio_write test.db 44 [hexio_render_int32 $newval] 643 set schemacookie [hexio_get_int [hexio_read test.db 40 4]] 644 incr schemacookie 645 hexio_write test.db 40 [hexio_render_int32 $schemacookie] 646 return {} 647} 648 649# This procedure returns the value of the file-format in file 'test.db'. 650# 651proc get_file_format {{fname test.db}} { 652 return [hexio_get_int [hexio_read $fname 44 4]] 653} 654 655if {![sqlite3 -has-codec]} { 656 # Test what happens when the library encounters a newer file format. 657 do_test capi3c-7.1 { 658 set_file_format 5 659 } {} 660 do_test capi3c-7.2 { 661 catch { sqlite3 db test.db } 662 catchsql { 663 SELECT * FROM sqlite_master; 664 } 665 } {1 {unsupported file format}} 666 db close 667} 668 669if {![sqlite3 -has-codec]} { 670 # Now test that the library correctly handles bogus entries in the 671 # sqlite_master table (schema corruption). 672 do_test capi3c-8.1 { 673 file delete -force test.db test.db-journal 674 sqlite3 db test.db 675 execsql { 676 CREATE TABLE t1(a); 677 } 678 db close 679 } {} 680 do_test capi3c-8.2 { 681 sqlite3 db test.db 682 execsql { 683 PRAGMA writable_schema=ON; 684 INSERT INTO sqlite_master VALUES(NULL,NULL,NULL,NULL,NULL); 685 } 686 db close 687 } {} 688 do_test capi3c-8.3 { 689 catch { sqlite3 db test.db } 690 catchsql { 691 SELECT * FROM sqlite_master; 692 } 693 } {1 {malformed database schema (?)}} 694 do_test capi3c-8.4 { 695 # Build a 5-field row record. The first field is a string 'table', and 696 # subsequent fields are all NULL. 697 db close 698 file delete -force test.db test.db-journal 699 sqlite3 db test.db 700 execsql { 701 CREATE TABLE t1(a); 702 PRAGMA writable_schema=ON; 703 INSERT INTO sqlite_master VALUES('table',NULL,NULL,NULL,NULL); 704 } 705 db close 706 } {}; 707 do_test capi3c-8.5 { 708 catch { sqlite3 db test.db } 709 catchsql { 710 SELECT * FROM sqlite_master; 711 } 712 } {1 {malformed database schema (?)}} 713 db close 714} 715file delete -force test.db 716file delete -force test.db-journal 717 718 719# Test the english language string equivalents for sqlite error codes 720set code2english [list \ 721SQLITE_OK {not an error} \ 722SQLITE_ERROR {SQL logic error or missing database} \ 723SQLITE_PERM {access permission denied} \ 724SQLITE_ABORT {callback requested query abort} \ 725SQLITE_BUSY {database is locked} \ 726SQLITE_LOCKED {database table is locked} \ 727SQLITE_NOMEM {out of memory} \ 728SQLITE_READONLY {attempt to write a readonly database} \ 729SQLITE_INTERRUPT {interrupted} \ 730SQLITE_IOERR {disk I/O error} \ 731SQLITE_CORRUPT {database disk image is malformed} \ 732SQLITE_FULL {database or disk is full} \ 733SQLITE_CANTOPEN {unable to open database file} \ 734SQLITE_EMPTY {table contains no data} \ 735SQLITE_SCHEMA {database schema has changed} \ 736SQLITE_CONSTRAINT {constraint failed} \ 737SQLITE_MISMATCH {datatype mismatch} \ 738SQLITE_MISUSE {library routine called out of sequence} \ 739SQLITE_NOLFS {large file support is disabled} \ 740SQLITE_AUTH {authorization denied} \ 741SQLITE_FORMAT {auxiliary database format error} \ 742SQLITE_RANGE {bind or column index out of range} \ 743SQLITE_NOTADB {file is encrypted or is not a database} \ 744unknownerror {unknown error} \ 745] 746 747set test_number 1 748foreach {code english} $code2english { 749 do_test capi3c-9.$test_number "sqlite3_test_errstr $code" $english 750 incr test_number 751} 752 753# Test the error message when a "real" out of memory occurs. 754ifcapable memdebug { 755 do_test capi3c-10-1 { 756 sqlite3 db test.db 757 set DB [sqlite3_connection_pointer db] 758 sqlite3_memdebug_fail 0 759 catchsql { 760 select * from sqlite_master; 761 } 762 } {1 {out of memory}} 763 do_test capi3c-10-2 { 764 sqlite3_errmsg $::DB 765 } {out of memory} 766 ifcapable {utf16} { 767 do_test capi3c-10-3 { 768 utf8 [sqlite3_errmsg16 $::DB] 769 } {out of memory} 770 } 771 db close 772 sqlite3_memdebug_fail -1 773} 774 775# The following tests - capi3c-11.* - test that a COMMIT or ROLLBACK 776# statement issued while there are still outstanding VMs that are part of 777# the transaction fails. 778sqlite3 db test.db 779set DB [sqlite3_connection_pointer db] 780sqlite_register_test_function $DB func 781do_test capi3c-11.1 { 782 execsql { 783 BEGIN; 784 CREATE TABLE t1(a, b); 785 INSERT INTO t1 VALUES(1, 'int'); 786 INSERT INTO t1 VALUES(2, 'notatype'); 787 } 788} {} 789do_test capi3c-11.1.1 { 790 sqlite3_get_autocommit $DB 791} 0 792do_test capi3c-11.2 { 793 set STMT [sqlite3_prepare_v2 $DB "SELECT func(b, a) FROM t1" -1 TAIL] 794 sqlite3_step $STMT 795} {SQLITE_ROW} 796 797# As of 3.6.5 a COMMIT is OK during while a query is still running - 798# as long as it is a read-only query and not an incremental BLOB write. 799# 800do_test capi3-11.3.1 { 801 catchsql { 802 COMMIT; 803 } 804} {0 {}} 805do_test capi3-11.3.2 { 806 sqlite3_extended_errcode $DB 807} {SQLITE_OK} 808do_test capi3-11.3.3 { 809 sqlite3_get_autocommit $DB 810} 1 811do_test capi3-11.3.4 { 812 db eval {PRAGMA lock_status} 813} {main shared temp closed} 814 815do_test capi3c-11.4 { 816 sqlite3_step $STMT 817} {SQLITE_ERROR} 818do_test capi3c-11.5 { 819 sqlite3_finalize $STMT 820} {SQLITE_ERROR} 821do_test capi3c-11.6 { 822 catchsql { 823 SELECT * FROM t1; 824 } 825} {0 {1 int 2 notatype}} 826do_test capi3c-11.7 { 827 sqlite3_get_autocommit $DB 828} 1 829do_test capi3c-11.8 { 830 execsql { 831 CREATE TABLE t2(a); 832 INSERT INTO t2 VALUES(1); 833 INSERT INTO t2 VALUES(2); 834 BEGIN; 835 INSERT INTO t2 VALUES(3); 836 } 837} {} 838do_test capi3c-11.8.1 { 839 sqlite3_get_autocommit $DB 840} 0 841do_test capi3c-11.9 { 842 set STMT [sqlite3_prepare_v2 $DB "SELECT a FROM t2" -1 TAIL] 843 sqlite3_step $STMT 844} {SQLITE_ROW} 845do_test capi3c-11.9.1 { 846 sqlite3_get_autocommit $DB 847} 0 848do_test capi3c-11.9.2 { 849 catchsql { 850 ROLLBACK; 851 } 852} {1 {cannot rollback transaction - SQL statements in progress}} 853do_test capi3c-11.9.3 { 854 sqlite3_get_autocommit $DB 855} 0 856do_test capi3c-11.10 { 857 sqlite3_step $STMT 858} {SQLITE_ROW} 859do_test capi3c-11.11 { 860 sqlite3_step $STMT 861} {SQLITE_ROW} 862do_test capi3c-11.12 { 863 sqlite3_step $STMT 864} {SQLITE_DONE} 865do_test capi3c-11.13 { 866 sqlite3_finalize $STMT 867} {SQLITE_OK} 868do_test capi3c-11.14 { 869 execsql { 870 SELECT a FROM t2; 871 } 872} {1 2 3} 873do_test capi3c-11.14.1 { 874 sqlite3_get_autocommit $DB 875} 0 876do_test capi3c-11.15 { 877 catchsql { 878 ROLLBACK; 879 } 880} {0 {}} 881do_test capi3c-11.15.1 { 882 sqlite3_get_autocommit $DB 883} 1 884do_test capi3c-11.16 { 885 execsql { 886 SELECT a FROM t2; 887 } 888} {1 2} 889 890# Sanity check on the definition of 'outstanding VM'. This means any VM 891# that has had sqlite3_step() called more recently than sqlite3_finalize() or 892# sqlite3_reset(). So a VM that has just been prepared or reset does not 893# count as an active VM. 894do_test capi3c-11.17 { 895 execsql { 896 BEGIN; 897 } 898} {} 899do_test capi3c-11.18 { 900 set STMT [sqlite3_prepare_v2 $DB "SELECT a FROM t1" -1 TAIL] 901 catchsql { 902 COMMIT; 903 } 904} {0 {}} 905do_test capi3c-11.19 { 906 sqlite3_step $STMT 907} {SQLITE_ROW} 908do_test capi3c-11.20 { 909 catchsql { 910 BEGIN; 911 COMMIT; 912 } 913} {0 {}} 914do_test capi3c-11.20 { 915 sqlite3_reset $STMT 916 catchsql { 917 COMMIT; 918 } 919} {1 {cannot commit - no transaction is active}} 920do_test capi3c-11.21 { 921 sqlite3_finalize $STMT 922} {SQLITE_OK} 923 924# The following tests - capi3c-12.* - check that its Ok to start a 925# transaction while other VMs are active, and that its Ok to execute 926# atomic updates in the same situation 927# 928do_test capi3c-12.1 { 929 set STMT [sqlite3_prepare_v2 $DB "SELECT a FROM t2" -1 TAIL] 930 sqlite3_step $STMT 931} {SQLITE_ROW} 932do_test capi3c-12.2 { 933 catchsql { 934 INSERT INTO t1 VALUES(3, NULL); 935 } 936} {0 {}} 937do_test capi3c-12.3 { 938 catchsql { 939 INSERT INTO t2 VALUES(4); 940 } 941} {0 {}} 942do_test capi3c-12.4 { 943 catchsql { 944 BEGIN; 945 INSERT INTO t1 VALUES(4, NULL); 946 } 947} {0 {}} 948do_test capi3c-12.5 { 949 sqlite3_step $STMT 950} {SQLITE_ROW} 951do_test capi3c-12.5.1 { 952 sqlite3_step $STMT 953} {SQLITE_ROW} 954do_test capi3c-12.6 { 955 sqlite3_step $STMT 956} {SQLITE_DONE} 957do_test capi3c-12.7 { 958 sqlite3_finalize $STMT 959} {SQLITE_OK} 960do_test capi3c-12.8 { 961 execsql { 962 COMMIT; 963 SELECT a FROM t1; 964 } 965} {1 2 3 4} 966 967# Test cases capi3c-13.* test the sqlite3_clear_bindings() and 968# sqlite3_sleep APIs. 969# 970if {[llength [info commands sqlite3_clear_bindings]]>0} { 971 do_test capi3c-13.1 { 972 execsql { 973 DELETE FROM t1; 974 } 975 set STMT [sqlite3_prepare_v2 $DB "INSERT INTO t1 VALUES(?, ?)" -1 TAIL] 976 sqlite3_step $STMT 977 } {SQLITE_DONE} 978 do_test capi3c-13.2 { 979 sqlite3_reset $STMT 980 sqlite3_bind_text $STMT 1 hello 5 981 sqlite3_bind_text $STMT 2 world 5 982 sqlite3_step $STMT 983 } {SQLITE_DONE} 984 do_test capi3c-13.3 { 985 sqlite3_reset $STMT 986 sqlite3_clear_bindings $STMT 987 sqlite3_step $STMT 988 } {SQLITE_DONE} 989 do_test capi3c-13-4 { 990 sqlite3_finalize $STMT 991 execsql { 992 SELECT * FROM t1; 993 } 994 } {{} {} hello world {} {}} 995} 996if {[llength [info commands sqlite3_sleep]]>0} { 997 do_test capi3c-13-5 { 998 set ms [sqlite3_sleep 80] 999 expr {$ms==80 || $ms==1000} 1000 } {1} 1001} 1002 1003# Ticket #1219: Make sure binding APIs can handle a NULL pointer. 1004# 1005do_test capi3c-14.1 { 1006 set rc [catch {sqlite3_bind_text 0 1 hello 5} msg] 1007 lappend rc $msg 1008} {1 SQLITE_MISUSE} 1009 1010# Ticket #1650: Honor the nBytes parameter to sqlite3_prepare. 1011# 1012do_test capi3c-15.1 { 1013 set sql {SELECT * FROM t2} 1014 set nbytes [string length $sql] 1015 append sql { WHERE a==1} 1016 set STMT [sqlite3_prepare_v2 $DB $sql $nbytes TAIL] 1017 sqlite3_step $STMT 1018 sqlite3_column_int $STMT 0 1019} {1} 1020do_test capi3c-15.2 { 1021 sqlite3_step $STMT 1022 sqlite3_column_int $STMT 0 1023} {2} 1024do_test capi3c-15.3 { 1025 sqlite3_finalize $STMT 1026} {SQLITE_OK} 1027 1028# Make sure code is always generated even if an IF EXISTS or 1029# IF NOT EXISTS clause is present that the table does not or 1030# does exists. That way we will always have a prepared statement 1031# to expire when the schema changes. 1032# 1033do_test capi3c-16.1 { 1034 set sql {DROP TABLE IF EXISTS t3} 1035 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL] 1036 sqlite3_finalize $STMT 1037 expr {$STMT!=""} 1038} {1} 1039do_test capi3c-16.2 { 1040 set sql {CREATE TABLE IF NOT EXISTS t1(x,y)} 1041 set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL] 1042 sqlite3_finalize $STMT 1043 expr {$STMT!=""} 1044} {1} 1045 1046# But still we do not generate code if there is no SQL 1047# 1048do_test capi3c-16.3 { 1049 set STMT [sqlite3_prepare_v2 $DB {} -1 TAIL] 1050 sqlite3_finalize $STMT 1051 expr {$STMT==""} 1052} {1} 1053do_test capi3c-16.4 { 1054 set STMT [sqlite3_prepare_v2 $DB {;} -1 TAIL] 1055 sqlite3_finalize $STMT 1056 expr {$STMT==""} 1057} {1} 1058 1059# Ticket #2154. 1060# 1061do_test capi3c-17.1 { 1062 set STMT [sqlite3_prepare_v2 $DB {SELECT max(a) FROM t2} -1 TAIL] 1063 sqlite3_step $STMT 1064} SQLITE_ROW 1065do_test capi3c-17.2 { 1066 sqlite3_column_int $STMT 0 1067} 4 1068do_test capi3c-17.3 { 1069 sqlite3_step $STMT 1070} SQLITE_DONE 1071do_test capi3c-17.4 { 1072 sqlite3_reset $STMT 1073 db eval {CREATE INDEX i2 ON t2(a)} 1074 sqlite3_step $STMT 1075} SQLITE_ROW 1076do_test capi3c-17.5 { 1077 sqlite3_column_int $STMT 0 1078} 4 1079do_test capi3c-17.6 { 1080 sqlite3_step $STMT 1081} SQLITE_DONE 1082do_test capi3c-17.7 { 1083 sqlite3_reset $STMT 1084 db eval {DROP INDEX i2} 1085 sqlite3_step $STMT 1086} SQLITE_ROW 1087do_test capi3c-17.8 { 1088 sqlite3_column_int $STMT 0 1089} 4 1090do_test capi3c-17.9 { 1091 sqlite3_step $STMT 1092} SQLITE_DONE 1093do_test capi3c-17.10 { 1094 sqlite3_finalize $STMT 1095 set STMT [sqlite3_prepare_v2 $DB {SELECT b FROM t1 WHERE a=?} -1 TAIL] 1096 sqlite3_bind_int $STMT 1 2 1097 db eval { 1098 DELETE FROM t1; 1099 INSERT INTO t1 VALUES(1,'one'); 1100 INSERT INTO t1 VALUES(2,'two'); 1101 INSERT INTO t1 VALUES(3,'three'); 1102 INSERT INTO t1 VALUES(4,'four'); 1103 } 1104 sqlite3_step $STMT 1105} SQLITE_ROW 1106do_test capi3c-17.11 { 1107 sqlite3_column_text $STMT 0 1108} two 1109do_test capi3c-17.12 { 1110 sqlite3_step $STMT 1111} SQLITE_DONE 1112do_test capi3c-17.13 { 1113 sqlite3_reset $STMT 1114 db eval {CREATE INDEX i1 ON t1(a)} 1115 sqlite3_step $STMT 1116} SQLITE_ROW 1117do_test capi3c-17.14 { 1118 sqlite3_column_text $STMT 0 1119} two 1120do_test capi3c-17.15 { 1121 sqlite3_step $STMT 1122} SQLITE_DONE 1123do_test capi3c-17.16 { 1124 sqlite3_reset $STMT 1125 db eval {DROP INDEX i1} 1126 sqlite3_step $STMT 1127} SQLITE_ROW 1128do_test capi3c-17.17 { 1129 sqlite3_column_text $STMT 0 1130} two 1131do_test capi3c-17.18 { 1132 sqlite3_step $STMT 1133} SQLITE_DONE 1134do_test capi3c-17.99 { 1135 sqlite3_finalize $STMT 1136} SQLITE_OK 1137 1138# On the mailing list it has been reported that finalizing after 1139# an SQLITE_BUSY return leads to a segfault. Here we test that case. 1140# 1141do_test capi3c-18.1 { 1142 sqlite3 db2 test.db 1143 set STMT [sqlite3_prepare_v2 $DB {SELECT max(a) FROM t1} -1 TAIL] 1144 sqlite3_step $STMT 1145} SQLITE_ROW 1146do_test capi3c-18.2 { 1147 sqlite3_column_int $STMT 0 1148} 4 1149do_test capi3c-18.3 { 1150 sqlite3_reset $STMT 1151 db2 eval {BEGIN EXCLUSIVE} 1152 sqlite3_step $STMT 1153} SQLITE_BUSY 1154do_test capi3c-18.4 { 1155 sqlite3_finalize $STMT 1156} SQLITE_BUSY 1157do_test capi3c-18.5 { 1158 db2 eval {COMMIT} 1159 db2 close 1160} {} 1161 1162# Ticket #2158. The sqlite3_step() will still return SQLITE_SCHEMA 1163# if the database schema changes in a way that makes the statement 1164# no longer valid. 1165# 1166do_test capi3c-19.1 { 1167 db eval { 1168 CREATE TABLE t3(x,y); 1169 INSERT INTO t3 VALUES(1,2); 1170 } 1171 set STMT [sqlite3_prepare_v2 $DB {SELECT * FROM t3} -1 TAIL] 1172 sqlite3_step $STMT 1173} SQLITE_ROW 1174do_test capi3c-19.2 { 1175 sqlite3_column_int $STMT 0 1176} 1 1177do_test capi3c-19.3 { 1178 sqlite3_step $STMT 1179} SQLITE_DONE 1180do_test capi3c-19.4 { 1181 sqlite3_reset $STMT 1182 db eval {DROP TABLE t3} 1183 sqlite3_step $STMT 1184} SQLITE_ERROR 1185do_test capi3c-19.4.1 { 1186 sqlite3_errmsg $DB 1187} {no such table: t3} 1188ifcapable deprecated { 1189 do_test capi3c-19.4.2 { 1190 sqlite3_expired $STMT 1191 } 1 1192} 1193do_test capi3c-19.4.3 { 1194 sqlite3_errmsg $DB 1195} {no such table: t3} 1196ifcapable deprecated { 1197 do_test capi3c-19.4.4 { 1198 sqlite3_expired 0 1199 } 1 1200} 1201do_test capi3c-19.5 { 1202 sqlite3_reset $STMT 1203 db eval { 1204 CREATE TABLE t3(x,y); 1205 INSERT INTO t3 VALUES(1,2); 1206 } 1207 sqlite3_step $STMT 1208} SQLITE_ROW 1209ifcapable deprecated { 1210 do_test capi3c-19.5.2 { 1211 sqlite3_expired $STMT 1212 } 0 1213} 1214do_test capi3c-19.6 { 1215 sqlite3_column_int $STMT 1 1216} 2 1217do_test capi3c-19.99 { 1218 sqlite3_finalize $STMT 1219} SQLITE_OK 1220 1221# Make sure a change in a separate database connection does not 1222# cause an SQLITE_SCHEMA return. 1223# 1224do_test capi3c-20.1 { 1225 set STMT [sqlite3_prepare_v2 $DB {SELECT * FROM t3} -1 TAIL] 1226 sqlite3 db2 test.db 1227 db2 eval {CREATE TABLE t4(x)} 1228 sqlite3_step $STMT 1229} SQLITE_ROW 1230do_test capi3c-20.2 { 1231 sqlite3_column_int $STMT 1 1232} 2 1233do_test capi3c-20.3 { 1234 sqlite3_step $STMT 1235} SQLITE_DONE 1236do_test capi3c-20.4 { 1237 db2 close 1238 sqlite3_finalize $STMT 1239} SQLITE_OK 1240 1241# Test that sqlite3_step() sets the database error code correctly. 1242# See ticket #2497. 1243# 1244ifcapable progress { 1245 do_test capi3c-21.1 { 1246 set STMT [sqlite3_prepare_v2 $DB {SELECT * FROM t3} -1 TAIL] 1247 db progress 5 "expr 1" 1248 sqlite3_step $STMT 1249 } {SQLITE_INTERRUPT} 1250 do_test capi3c-21.2 { 1251 sqlite3_extended_errcode $DB 1252 } {SQLITE_INTERRUPT} 1253 do_test capi3c-21.3 { 1254 sqlite3_finalize $STMT 1255 } {SQLITE_INTERRUPT} 1256 do_test capi3c-21.4 { 1257 set STMT [sqlite3_prepare $DB {SELECT * FROM t3} -1 TAIL] 1258 db progress 5 "expr 1" 1259 sqlite3_step $STMT 1260 } {SQLITE_ERROR} 1261 do_test capi3c-21.5 { 1262 sqlite3_errcode $DB 1263 } {SQLITE_ERROR} 1264 do_test capi3c-21.6 { 1265 sqlite3_finalize $STMT 1266 } {SQLITE_INTERRUPT} 1267 do_test capi3c-21.7 { 1268 sqlite3_errcode $DB 1269 } {SQLITE_INTERRUPT} 1270 do_test capi3c-21.8 { 1271 sqlite3_extended_errcode $DB 1272 } {SQLITE_INTERRUPT} 1273} 1274 1275# Make sure sqlite3_result_error_code() returns the correct error code. 1276# See ticket #2940 1277# 1278do_test capi3c-22.1 { 1279 db progress 0 {} 1280 set STMT [sqlite3_prepare_v2 db {SELECT test_error('the message',3)} -1 TAIL] 1281 sqlite3_step $STMT 1282} {SQLITE_PERM} 1283sqlite3_finalize $STMT 1284do_test capi3c-22.2 { 1285 set STMT [sqlite3_prepare_v2 db {SELECT test_error('the message',4)} -1 TAIL] 1286 sqlite3_step $STMT 1287} {SQLITE_ABORT} 1288sqlite3_finalize $STMT 1289do_test capi3c-22.3 { 1290 set STMT [sqlite3_prepare_v2 db {SELECT test_error('the message',16)} -1 TAIL] 1291 sqlite3_step $STMT 1292} {SQLITE_EMPTY} 1293sqlite3_finalize $STMT 1294 1295# For a multi-column result set where the same table column is repeated 1296# in multiple columns of the output, verify that doing a UTF-8 to UTF-16 1297# conversion (or vice versa) on one column does not change the value of 1298# the second. 1299# 1300ifcapable utf16 { 1301 do_test capi3c-23.1 { 1302 set STMT [sqlite3_prepare_v2 db {SELECT b,b,b,b FROM t1} -1 TAIL] 1303 sqlite3_step $STMT 1304 } {SQLITE_ROW} 1305 do_test capi3c-23.2 { 1306 sqlite3_column_text16 $STMT 0 1307 sqlite3_column_text $STMT 1 1308 } {one} 1309 do_test capi3c-23.3 { 1310 sqlite3_column_text16 $STMT 2 1311 sqlite3_column_text $STMT 3 1312 } {one} 1313 sqlite3_finalize $STMT 1314 do_test capi3c-23.4 { 1315 set STMT [sqlite3_prepare_v2 db {SELECT b||'x',b,b,b FROM t1} -1 TAIL] 1316 sqlite3_step $STMT 1317 } {SQLITE_ROW} 1318 do_test capi3c-23.5 { 1319 sqlite3_column_text16 $STMT 0 1320 sqlite3_column_text $STMT 1 1321 } {one} 1322 do_test capi3c-23.6 { 1323 sqlite3_column_text16 $STMT 2 1324 sqlite3_column_text $STMT 3 1325 } {one} 1326 sqlite3_finalize $STMT 1327} 1328 1329# Test decltype on some SELECT statements that contain sub-selects. 1330# 1331proc decltype {zSql} { 1332 set ret [list] 1333 set STMT [sqlite3_prepare_v2 db $zSql -1 TAIL] 1334 for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} { 1335 lappend ret [sqlite3_column_decltype $STMT $i] 1336 } 1337 sqlite3_finalize $STMT 1338 return $ret 1339} 1340do_test capi3c-24.1 { 1341 execsql { CREATE TABLE t5(a INTEGER, b STRING, c DATETIME) } 1342 decltype {SELECT * FROM t5} 1343} {INTEGER STRING DATETIME} 1344do_test capi3c-24.2 { 1345 decltype {SELECT (SELECT c) FROM t5} 1346} {DATETIME} 1347do_test capi3c-24.3 { 1348 decltype {SELECT (SELECT * FROM (SELECT c)) FROM t5} 1349} {DATETIME} 1350do_test capi3c-24.4 { 1351 decltype {SELECT * FROM (SELECT * FROM t5 ORDER BY c LIMIT 1) ORDER BY b} 1352} {INTEGER STRING DATETIME} 1353do_test capi3c-24.5 { 1354 decltype { 1355 SELECT (SELECT x FROM (SELECT c AS x)) 1356 FROM (SELECT * FROM t5 ORDER BY c LIMIT 1) ORDER BY b 1357 } 1358} {DATETIME} 1359do_test capi3c-24.3 { 1360 decltype {SELECT (SELECT x FROM (SELECT t5.a AS x)) FROM t5} 1361} {INTEGER} 1362 1363finish_test 1364