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