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: capi2.test,v 1.37 2008/12/30 17:55:00 drh Exp $ 15# 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# Return the text values from the current row pointed at by STMT as a list. 21proc get_row_values {STMT} { 22 set VALUES [list] 23 for {set i 0} {$i < [sqlite3_data_count $STMT]} {incr i} { 24 lappend VALUES [sqlite3_column_text $STMT $i] 25 } 26 return $VALUES 27} 28 29# Return the column names followed by declaration types for the result set 30# of the SQL statement STMT. 31# 32# i.e. for: 33# CREATE TABLE abc(a text, b integer); 34# SELECT * FROM abc; 35# 36# The result is {a b text integer} 37proc get_column_names {STMT} { 38 set VALUES [list] 39 for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} { 40 lappend VALUES [sqlite3_column_name $STMT $i] 41 } 42 for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} { 43 lappend VALUES [sqlite3_column_decltype $STMT $i] 44 } 45 return $VALUES 46} 47 48# Check basic functionality 49# 50do_test capi2-1.1 { 51 set DB [sqlite3_connection_pointer db] 52 execsql {CREATE TABLE t1(a,b,c)} 53 set VM [sqlite3_prepare $DB {SELECT name, rowid FROM sqlite_master} -1 TAIL] 54 set TAIL 55} {} 56do_test capi2-1.2 { 57 sqlite3_step $VM 58} {SQLITE_ROW} 59do_test capi2-1.3 { 60 sqlite3_data_count $VM 61} {2} 62do_test capi2-1.4 { 63 get_row_values $VM 64} {t1 1} 65do_test capi2-1.5 { 66 get_column_names $VM 67} {name rowid text INTEGER} 68do_test capi2-1.6 { 69 sqlite3_step $VM 70} {SQLITE_DONE} 71do_test capi2-1.7 { 72 list [sqlite3_column_count $VM] [get_row_values $VM] [get_column_names $VM] 73} {2 {} {name rowid text INTEGER}} 74 75# This used to be SQLITE_MISUSE. But now we automatically reset prepared 76# statements. 77ifcapable autoreset { 78 do_test capi2-1.8 { 79 sqlite3_step $VM 80 } {SQLITE_ROW} 81} else { 82 do_test capi2-1.8 { 83 sqlite3_step $VM 84 } {SQLITE_MISUSE} 85} 86 87# Update: In v2, once SQLITE_MISUSE is returned the statement handle cannot 88# be interrogated for more information. However in v3, since the column 89# count, names and types are determined at compile time, these are still 90# accessible after an SQLITE_MISUSE error. 91do_test capi2-1.9 { 92 sqlite3_reset $VM 93 list [sqlite3_column_count $VM] [get_row_values $VM] [get_column_names $VM] 94} {2 {} {name rowid text INTEGER}} 95do_test capi2-1.10 { 96 sqlite3_data_count $VM 97} {0} 98 99do_test capi2-1.11 { 100 sqlite3_finalize $VM 101} {SQLITE_OK} 102 103# Check to make sure that the "tail" of a multi-statement SQL script 104# is returned by sqlite3_prepare. 105# 106do_test capi2-2.1 { 107 set SQL { 108 SELECT name, rowid FROM sqlite_master; 109 SELECT name, rowid FROM sqlite_master WHERE 0; 110 -- A comment at the end 111 } 112 set VM [sqlite3_prepare $DB $SQL -1 SQL] 113 set SQL 114} { 115 SELECT name, rowid FROM sqlite_master WHERE 0; 116 -- A comment at the end 117 } 118do_test capi2-2.2 { 119 set r [sqlite3_step $VM] 120 lappend r [sqlite3_column_count $VM] \ 121 [get_row_values $VM] \ 122 [get_column_names $VM] 123} {SQLITE_ROW 2 {t1 1} {name rowid text INTEGER}} 124do_test capi2-2.3 { 125 set r [sqlite3_step $VM] 126 lappend r [sqlite3_column_count $VM] \ 127 [get_row_values $VM] \ 128 [get_column_names $VM] 129} {SQLITE_DONE 2 {} {name rowid text INTEGER}} 130do_test capi2-2.4 { 131 sqlite3_finalize $VM 132} {SQLITE_OK} 133do_test capi2-2.5 { 134 set VM [sqlite3_prepare $DB $SQL -1 SQL] 135 set SQL 136} { 137 -- A comment at the end 138 } 139do_test capi2-2.6 { 140 set r [sqlite3_step $VM] 141 lappend r [sqlite3_column_count $VM] \ 142 [get_row_values $VM] \ 143 [get_column_names $VM] 144} {SQLITE_DONE 2 {} {name rowid text INTEGER}} 145do_test capi2-2.7 { 146 sqlite3_finalize $VM 147} {SQLITE_OK} 148do_test capi2-2.8 { 149 set VM [sqlite3_prepare $DB $SQL -1 SQL] 150 list $SQL $VM 151} {{} {}} 152 153# Check the error handling. 154# 155do_test capi2-3.1 { 156 set rc [catch { 157 sqlite3_prepare $DB {select bogus from sqlite_master} -1 TAIL 158 } msg] 159 lappend rc $msg $TAIL 160} {1 {(1) no such column: bogus} {}} 161do_test capi2-3.2 { 162 set rc [catch { 163 sqlite3_prepare $DB {select bogus from } -1 TAIL 164 } msg] 165 lappend rc $msg $TAIL 166} {1 {(1) near " ": syntax error} {}} 167do_test capi2-3.3 { 168 set rc [catch { 169 sqlite3_prepare $DB {;;;;select bogus from sqlite_master} -1 TAIL 170 } msg] 171 lappend rc $msg $TAIL 172} {1 {(1) no such column: bogus} {}} 173do_test capi2-3.4 { 174 set rc [catch { 175 sqlite3_prepare $DB {select bogus from sqlite_master;x;} -1 TAIL 176 } msg] 177 lappend rc $msg $TAIL 178} {1 {(1) no such column: bogus} {x;}} 179do_test capi2-3.5 { 180 set rc [catch { 181 sqlite3_prepare $DB {select bogus from sqlite_master;;;x;} -1 TAIL 182 } msg] 183 lappend rc $msg $TAIL 184} {1 {(1) no such column: bogus} {;;x;}} 185do_test capi2-3.6 { 186 set rc [catch { 187 sqlite3_prepare $DB {select 5/0} -1 TAIL 188 } VM] 189 lappend rc $TAIL 190} {0 {}} 191do_test capi2-3.7 { 192 list [sqlite3_step $VM] \ 193 [sqlite3_column_count $VM] \ 194 [get_row_values $VM] \ 195 [get_column_names $VM] 196} {SQLITE_ROW 1 {{}} {5/0 {}}} 197do_test capi2-3.8 { 198 sqlite3_finalize $VM 199} {SQLITE_OK} 200do_test capi2-3.9 { 201 execsql {CREATE UNIQUE INDEX i1 ON t1(a)} 202 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(1,2,3)} -1 TAIL] 203 set TAIL 204} {} 205do_test capi2-3.9b {db changes} {0} 206do_test capi2-3.10 { 207 list [sqlite3_step $VM] \ 208 [sqlite3_column_count $VM] \ 209 [get_row_values $VM] \ 210 [get_column_names $VM] 211} {SQLITE_DONE 0 {} {}} 212 213# Update for v3 - the change has not actually happened until the query is 214# finalized. Is this going to cause trouble for anyone? Lee Nelson maybe? 215# (Later:) The change now happens just before SQLITE_DONE is returned. 216do_test capi2-3.10b {db changes} {1} 217do_test capi2-3.11 { 218 sqlite3_finalize $VM 219} {SQLITE_OK} 220do_test capi2-3.11b {db changes} {1} 221#do_test capi2-3.12-misuse { 222# sqlite3_finalize $VM 223#} {SQLITE_MISUSE} 224do_test capi2-3.13 { 225 set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(1,3,4)} -1 TAIL] 226 list [sqlite3_step $VM] \ 227 [sqlite3_column_count $VM] \ 228 [get_row_values $VM] \ 229 [get_column_names $VM] 230} {SQLITE_ERROR 0 {} {}} 231 232# Update for v3: Preparing a statement does not affect the change counter. 233# (Test result changes from 0 to 1). (Later:) change counter updates occur 234# when sqlite3_step returns, not at finalize time. 235do_test capi2-3.13b {db changes} {0} 236 237do_test capi2-3.14 { 238 list [sqlite3_finalize $VM] [sqlite3_errmsg $DB] 239} {SQLITE_CONSTRAINT {column a is not unique}} 240do_test capi2-3.15 { 241 set VM [sqlite3_prepare $DB {CREATE TABLE t2(a NOT NULL, b)} -1 TAIL] 242 set TAIL 243} {} 244do_test capi2-3.16 { 245 list [sqlite3_step $VM] \ 246 [sqlite3_column_count $VM] \ 247 [get_row_values $VM] \ 248 [get_column_names $VM] 249} {SQLITE_DONE 0 {} {}} 250do_test capi2-3.17 { 251 list [sqlite3_finalize $VM] [sqlite3_errmsg $DB] 252} {SQLITE_OK {not an error}} 253do_test capi2-3.18 { 254 set VM [sqlite3_prepare $DB {INSERT INTO t2 VALUES(NULL,2)} -1 TAIL] 255 list [sqlite3_step $VM] \ 256 [sqlite3_column_count $VM] \ 257 [get_row_values $VM] \ 258 [get_column_names $VM] 259} {SQLITE_ERROR 0 {} {}} 260do_test capi2-3.19 { 261 list [sqlite3_finalize $VM] [sqlite3_errmsg $DB] 262} {SQLITE_CONSTRAINT {t2.a may not be NULL}} 263 264do_test capi2-3.20 { 265 execsql { 266 CREATE TABLE a1(message_id, name , UNIQUE(message_id, name) ); 267 INSERT INTO a1 VALUES(1, 1); 268 } 269} {} 270do_test capi2-3.21 { 271 set VM [sqlite3_prepare $DB {INSERT INTO a1 VALUES(1, 1)} -1 TAIL] 272 sqlite3_step $VM 273} {SQLITE_ERROR} 274do_test capi2-3.22 { 275 sqlite3_errcode $DB 276} {SQLITE_ERROR} 277do_test capi2-3.23 { 278 sqlite3_finalize $VM 279} {SQLITE_CONSTRAINT} 280do_test capi2-3.24 { 281 sqlite3_errcode $DB 282} {SQLITE_CONSTRAINT} 283 284# Two or more virtual machines exists at the same time. 285# 286do_test capi2-4.1 { 287 set VM1 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(1,2)} -1 TAIL] 288 set TAIL 289} {} 290do_test capi2-4.2 { 291 set VM2 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(2,3)} -1 TAIL] 292 set TAIL 293} {} 294do_test capi2-4.3 { 295 set VM3 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(3,4)} -1 TAIL] 296 set TAIL 297} {} 298do_test capi2-4.4 { 299 list [sqlite3_step $VM2] \ 300 [sqlite3_column_count $VM2] \ 301 [get_row_values $VM2] \ 302 [get_column_names $VM2] 303} {SQLITE_DONE 0 {} {}} 304do_test capi2-4.5 { 305 execsql {SELECT * FROM t2 ORDER BY a} 306} {2 3} 307do_test capi2-4.6 { 308 sqlite3_finalize $VM2 309} {SQLITE_OK} 310do_test capi2-4.7 { 311 list [sqlite3_step $VM3] \ 312 [sqlite3_column_count $VM3] \ 313 [get_row_values $VM3] \ 314 [get_column_names $VM3] 315} {SQLITE_DONE 0 {} {}} 316do_test capi2-4.8 { 317 execsql {SELECT * FROM t2 ORDER BY a} 318} {2 3 3 4} 319do_test capi2-4.9 { 320 sqlite3_finalize $VM3 321} {SQLITE_OK} 322do_test capi2-4.10 { 323 list [sqlite3_step $VM1] \ 324 [sqlite3_column_count $VM1] \ 325 [get_row_values $VM1] \ 326 [get_column_names $VM1] 327} {SQLITE_DONE 0 {} {}} 328do_test capi2-4.11 { 329 execsql {SELECT * FROM t2 ORDER BY a} 330} {1 2 2 3 3 4} 331do_test capi2-4.12 { 332 sqlite3_finalize $VM1 333} {SQLITE_OK} 334 335# Interleaved SELECTs 336# 337do_test capi2-5.1 { 338 set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL] 339 set VM2 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL] 340 set VM3 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL] 341 list [sqlite3_step $VM1] \ 342 [sqlite3_column_count $VM1] \ 343 [get_row_values $VM1] \ 344 [get_column_names $VM1] 345} {SQLITE_ROW 2 {2 3} {a b {} {}}} 346do_test capi2-5.2 { 347 list [sqlite3_step $VM2] \ 348 [sqlite3_column_count $VM2] \ 349 [get_row_values $VM2] \ 350 [get_column_names $VM2] 351} {SQLITE_ROW 2 {2 3} {a b {} {}}} 352do_test capi2-5.3 { 353 list [sqlite3_step $VM1] \ 354 [sqlite3_column_count $VM1] \ 355 [get_row_values $VM1] \ 356 [get_column_names $VM1] 357} {SQLITE_ROW 2 {3 4} {a b {} {}}} 358do_test capi2-5.4 { 359 list [sqlite3_step $VM3] \ 360 [sqlite3_column_count $VM3] \ 361 [get_row_values $VM3] \ 362 [get_column_names $VM3] 363} {SQLITE_ROW 2 {2 3} {a b {} {}}} 364do_test capi2-5.5 { 365 list [sqlite3_step $VM3] \ 366 [sqlite3_column_count $VM3] \ 367 [get_row_values $VM3] \ 368 [get_column_names $VM3] 369} {SQLITE_ROW 2 {3 4} {a b {} {}}} 370do_test capi2-5.6 { 371 list [sqlite3_step $VM3] \ 372 [sqlite3_column_count $VM3] \ 373 [get_row_values $VM3] \ 374 [get_column_names $VM3] 375} {SQLITE_ROW 2 {1 2} {a b {} {}}} 376do_test capi2-5.7 { 377 list [sqlite3_step $VM3] \ 378 [sqlite3_column_count $VM3] \ 379 [get_row_values $VM3] \ 380 [get_column_names $VM3] 381} {SQLITE_DONE 2 {} {a b {} {}}} 382do_test capi2-5.8 { 383 sqlite3_finalize $VM3 384} {SQLITE_OK} 385do_test capi2-5.9 { 386 list [sqlite3_step $VM1] \ 387 [sqlite3_column_count $VM1] \ 388 [get_row_values $VM1] \ 389 [get_column_names $VM1] 390} {SQLITE_ROW 2 {1 2} {a b {} {}}} 391do_test capi2-5.10 { 392 sqlite3_finalize $VM1 393} {SQLITE_OK} 394do_test capi2-5.11 { 395 list [sqlite3_step $VM2] \ 396 [sqlite3_column_count $VM2] \ 397 [get_row_values $VM2] \ 398 [get_column_names $VM2] 399} {SQLITE_ROW 2 {3 4} {a b {} {}}} 400do_test capi2-5.12 { 401 list [sqlite3_step $VM2] \ 402 [sqlite3_column_count $VM2] \ 403 [get_row_values $VM2] \ 404 [get_column_names $VM2] 405} {SQLITE_ROW 2 {1 2} {a b {} {}}} 406do_test capi2-5.11 { 407 sqlite3_finalize $VM2 408} {SQLITE_OK} 409 410# Check for proper SQLITE_BUSY returns. 411# 412do_test capi2-6.1 { 413 execsql { 414 BEGIN; 415 CREATE TABLE t3(x counter); 416 INSERT INTO t3 VALUES(1); 417 INSERT INTO t3 VALUES(2); 418 INSERT INTO t3 SELECT x+2 FROM t3; 419 INSERT INTO t3 SELECT x+4 FROM t3; 420 INSERT INTO t3 SELECT x+8 FROM t3; 421 COMMIT; 422 } 423 set VM1 [sqlite3_prepare $DB {SELECT * FROM t3} -1 TAIL] 424 sqlite3 db2 test.db 425 execsql {BEGIN} db2 426} {} 427# Update for v3: BEGIN doesn't write-lock the database. It is quite 428# difficult to get v3 to write-lock the database, which causes a few 429# problems for test scripts. 430# 431# do_test capi2-6.2 { 432# list [sqlite3_step $VM1] \ 433# [sqlite3_column_count $VM1] \ 434# [get_row_values $VM1] \ 435# [get_column_names $VM1] 436# } {SQLITE_BUSY 0 {} {}} 437do_test capi2-6.3 { 438 execsql {COMMIT} db2 439} {} 440do_test capi2-6.4 { 441 list [sqlite3_step $VM1] \ 442 [sqlite3_column_count $VM1] \ 443 [get_row_values $VM1] \ 444 [get_column_names $VM1] 445} {SQLITE_ROW 1 1 {x counter}} 446do_test capi2-6.5 { 447 catchsql {INSERT INTO t3 VALUES(10);} db2 448} {1 {database is locked}} 449do_test capi2-6.6 { 450 list [sqlite3_step $VM1] \ 451 [sqlite3_column_count $VM1] \ 452 [get_row_values $VM1] \ 453 [get_column_names $VM1] 454} {SQLITE_ROW 1 2 {x counter}} 455do_test capi2-6.7 { 456 execsql {SELECT * FROM t2} db2 457} {2 3 3 4 1 2} 458do_test capi2-6.8 { 459 list [sqlite3_step $VM1] \ 460 [sqlite3_column_count $VM1] \ 461 [get_row_values $VM1] \ 462 [get_column_names $VM1] 463} {SQLITE_ROW 1 3 {x counter}} 464do_test capi2-6.9 { 465 execsql {SELECT * FROM t2} 466} {2 3 3 4 1 2} 467do_test capi2-6.10 { 468 list [sqlite3_step $VM1] \ 469 [sqlite3_column_count $VM1] \ 470 [get_row_values $VM1] \ 471 [get_column_names $VM1] 472} {SQLITE_ROW 1 4 {x counter}} 473do_test capi2-6.11 { 474 execsql {BEGIN} 475} {} 476do_test capi2-6.12 { 477 list [sqlite3_step $VM1] \ 478 [sqlite3_column_count $VM1] \ 479 [get_row_values $VM1] \ 480 [get_column_names $VM1] 481} {SQLITE_ROW 1 5 {x counter}} 482 483# A read no longer blocks a write in the same connection. 484#do_test capi2-6.13 { 485# catchsql {UPDATE t3 SET x=x+1} 486#} {1 {database table is locked}} 487 488do_test capi2-6.14 { 489 list [sqlite3_step $VM1] \ 490 [sqlite3_column_count $VM1] \ 491 [get_row_values $VM1] \ 492 [get_column_names $VM1] 493} {SQLITE_ROW 1 6 {x counter}} 494do_test capi2-6.15 { 495 execsql {SELECT * FROM t1} 496} {1 2 3} 497do_test capi2-6.16 { 498 list [sqlite3_step $VM1] \ 499 [sqlite3_column_count $VM1] \ 500 [get_row_values $VM1] \ 501 [get_column_names $VM1] 502} {SQLITE_ROW 1 7 {x counter}} 503do_test capi2-6.17 { 504 catchsql {UPDATE t1 SET b=b+1} 505} {0 {}} 506do_test capi2-6.18 { 507 list [sqlite3_step $VM1] \ 508 [sqlite3_column_count $VM1] \ 509 [get_row_values $VM1] \ 510 [get_column_names $VM1] 511} {SQLITE_ROW 1 8 {x counter}} 512do_test capi2-6.19 { 513 execsql {SELECT * FROM t1} 514} {1 3 3} 515do_test capi2-6.20 { 516 list [sqlite3_step $VM1] \ 517 [sqlite3_column_count $VM1] \ 518 [get_row_values $VM1] \ 519 [get_column_names $VM1] 520} {SQLITE_ROW 1 9 {x counter}} 521#do_test capi2-6.21 { 522# execsql {ROLLBACK; SELECT * FROM t1} 523#} {1 2 3} 524do_test capi2-6.22 { 525 list [sqlite3_step $VM1] \ 526 [sqlite3_column_count $VM1] \ 527 [get_row_values $VM1] \ 528 [get_column_names $VM1] 529} {SQLITE_ROW 1 10 {x counter}} 530#do_test capi2-6.23 { 531# execsql {BEGIN TRANSACTION;} 532#} {} 533do_test capi2-6.24 { 534 list [sqlite3_step $VM1] \ 535 [sqlite3_column_count $VM1] \ 536 [get_row_values $VM1] \ 537 [get_column_names $VM1] 538} {SQLITE_ROW 1 11 {x counter}} 539do_test capi2-6.25 { 540 execsql { 541 INSERT INTO t1 VALUES(2,3,4); 542 SELECT * FROM t1; 543 } 544} {1 3 3 2 3 4} 545do_test capi2-6.26 { 546 list [sqlite3_step $VM1] \ 547 [sqlite3_column_count $VM1] \ 548 [get_row_values $VM1] \ 549 [get_column_names $VM1] 550} {SQLITE_ROW 1 12 {x counter}} 551do_test capi2-6.27 { 552 catchsql { 553 INSERT INTO t1 VALUES(2,4,5); 554 SELECT * FROM t1; 555 } 556} {1 {column a is not unique}} 557do_test capi2-6.28 { 558 list [sqlite3_step $VM1] \ 559 [sqlite3_column_count $VM1] \ 560 [get_row_values $VM1] \ 561 [get_column_names $VM1] 562} {SQLITE_ROW 1 13 {x counter}} 563do_test capi2-6.99 { 564 sqlite3_finalize $VM1 565} {SQLITE_OK} 566catchsql {ROLLBACK} 567 568do_test capi2-7.1 { 569 stepsql $DB { 570 SELECT * FROM t1 571 } 572} {0 1 2 3} 573do_test capi2-7.2 { 574 stepsql $DB { 575 PRAGMA count_changes=on 576 } 577} {0} 578do_test capi2-7.3 { 579 stepsql $DB { 580 UPDATE t1 SET a=a+10; 581 } 582} {0 1} 583do_test capi2-7.4 { 584 stepsql $DB { 585 INSERT INTO t1 SELECT a+1,b+1,c+1 FROM t1; 586 } 587} {0 1} 588do_test capi2-7.4b {sqlite3_changes $DB} {1} 589do_test capi2-7.5 { 590 stepsql $DB { 591 UPDATE t1 SET a=a+10; 592 } 593} {0 2} 594do_test capi2-7.5b {sqlite3_changes $DB} {2} 595do_test capi2-7.6 { 596 stepsql $DB { 597 SELECT * FROM t1; 598 } 599} {0 21 2 3 22 3 4} 600do_test capi2-7.7 { 601 stepsql $DB { 602 INSERT INTO t1 SELECT a+2,b+2,c+2 FROM t1; 603 } 604} {0 2} 605do_test capi2-7.8 { 606 sqlite3_changes $DB 607} {2} 608do_test capi2-7.9 { 609 stepsql $DB { 610 SELECT * FROM t1; 611 } 612} {0 21 2 3 22 3 4 23 4 5 24 5 6} 613do_test capi2-7.10 { 614 stepsql $DB { 615 UPDATE t1 SET a=a-20; 616 SELECT * FROM t1; 617 } 618} {0 4 1 2 3 2 3 4 3 4 5 4 5 6} 619 620# Update for version 3: A SELECT statement no longer resets the change 621# counter (Test result changes from 0 to 4). 622do_test capi2-7.11 { 623 sqlite3_changes $DB 624} {4} 625do_test capi2-7.11a { 626 execsql {SELECT count(*) FROM t1} 627} {4} 628 629ifcapable {explain} { 630 do_test capi2-7.12 { 631 set x [stepsql $DB {EXPLAIN SELECT * FROM t1}] 632 lindex $x 0 633 } {0} 634} 635 636# Ticket #261 - make sure we can finalize before the end of a query. 637# 638do_test capi2-8.1 { 639 set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL] 640 sqlite3_finalize $VM1 641} {SQLITE_OK} 642 643# Tickets #384 and #385 - make sure the TAIL argument to sqlite3_prepare 644# and all of the return pointers in sqlite_step can be null. 645# 646do_test capi2-9.1 { 647 set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 DUMMY] 648 sqlite3_step $VM1 649 sqlite3_finalize $VM1 650} {SQLITE_OK} 651 652# Test that passing a NULL pointer to sqlite3_finalize() or sqlite3_reset 653# does not cause an error. 654do_test capi2-10.1 { 655 sqlite3_finalize 0 656} {SQLITE_OK} 657do_test capi2-10.2 { 658 sqlite3_reset 0 659} {SQLITE_OK} 660 661#--------------------------------------------------------------------------- 662# The following tests - capi2-11.* - test the "column origin" APIs. 663# 664# sqlite3_column_origin_name() 665# sqlite3_column_database_name() 666# sqlite3_column_table_name() 667# 668 669ifcapable columnmetadata { 670 671# This proc uses the database handle $::DB to compile the SQL statement passed 672# as a parameter. The return value of this procedure is a list with one 673# element for each column returned by the compiled statement. Each element of 674# this list is itself a list of length three, consisting of the origin 675# database, table and column for the corresponding returned column. 676proc check_origins {sql} { 677 set ret [list] 678 set ::STMT [sqlite3_prepare $::DB $sql -1 dummy] 679 for {set i 0} {$i < [sqlite3_column_count $::STMT]} {incr i} { 680 lappend ret [list \ 681 [sqlite3_column_database_name $::STMT $i] \ 682 [sqlite3_column_table_name $::STMT $i] \ 683 [sqlite3_column_origin_name $::STMT $i] \ 684 ] 685 } 686 sqlite3_finalize $::STMT 687 return $ret 688} 689do_test capi2-11.1 { 690 execsql { 691 CREATE TABLE tab1(col1, col2); 692 } 693} {} 694do_test capi2-11.2 { 695 check_origins {SELECT col2, col1 FROM tab1} 696} [list {main tab1 col2} {main tab1 col1}] 697do_test capi2-11.3 { 698 check_origins {SELECT col2 AS hello, col1 AS world FROM tab1} 699} [list {main tab1 col2} {main tab1 col1}] 700 701ifcapable subquery { 702 do_test capi2-11.4 { 703 check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM tab1)} 704 } [list {main tab1 col2} {main tab1 col1}] 705 do_test capi2-11.5 { 706 check_origins {SELECT (SELECT col2 FROM tab1), (SELECT col1 FROM tab1)} 707 } [list {main tab1 col2} {main tab1 col1}] 708 do_test capi2-11.6 { 709 check_origins {SELECT (SELECT col2), (SELECT col1) FROM tab1} 710 } [list {main tab1 col2} {main tab1 col1}] 711 do_test capi2-11.7 { 712 check_origins {SELECT * FROM tab1} 713 } [list {main tab1 col1} {main tab1 col2}] 714 do_test capi2-11.8 { 715 check_origins {SELECT * FROM (SELECT * FROM tab1)} 716 } [list {main tab1 col1} {main tab1 col2}] 717} 718 719ifcapable view&&subquery { 720 do_test capi2-12.1 { 721 execsql { 722 CREATE VIEW view1 AS SELECT * FROM tab1; 723 } 724 } {} 725 do_test capi2-12.2 { 726 check_origins {SELECT col2, col1 FROM view1} 727 } [list {main tab1 col2} {main tab1 col1}] 728 do_test capi2-12.3 { 729 check_origins {SELECT col2 AS hello, col1 AS world FROM view1} 730 } [list {main tab1 col2} {main tab1 col1}] 731 do_test capi2-12.4 { 732 check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM view1)} 733 } [list {main tab1 col2} {main tab1 col1}] 734 do_test capi2-12.5 { 735 check_origins {SELECT (SELECT col2 FROM view1), (SELECT col1 FROM view1)} 736 } [list {main tab1 col2} {main tab1 col1}] 737 do_test capi2-12.6 { 738 check_origins {SELECT (SELECT col2), (SELECT col1) FROM view1} 739 } [list {main tab1 col2} {main tab1 col1}] 740 do_test capi2-12.7 { 741 check_origins {SELECT * FROM view1} 742 } [list {main tab1 col1} {main tab1 col2}] 743 do_test capi2-12.8 { 744 check_origins {select * from (select * from view1)} 745 } [list {main tab1 col1} {main tab1 col2}] 746 do_test capi2-12.9 { 747 check_origins {select * from (select * from (select * from view1))} 748 } [list {main tab1 col1} {main tab1 col2}] 749 do_test capi2-12.10 { 750 db close 751 sqlite3 db test.db 752 set ::DB [sqlite3_connection_pointer db] 753 check_origins {select * from (select * from (select * from view1))} 754 } [list {main tab1 col1} {main tab1 col2}] 755 756 # This view will thwart the flattening optimization. 757 do_test capi2-13.1 { 758 execsql { 759 CREATE VIEW view2 AS SELECT * FROM tab1 limit 10 offset 10; 760 } 761 } {} 762 do_test capi2-13.2 { 763 check_origins {SELECT col2, col1 FROM view2} 764 } [list {main tab1 col2} {main tab1 col1}] 765 do_test capi2-13.3 { 766 check_origins {SELECT col2 AS hello, col1 AS world FROM view2} 767 } [list {main tab1 col2} {main tab1 col1}] 768 do_test capi2-13.4 { 769 check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM view2)} 770 } [list {main tab1 col2} {main tab1 col1}] 771 do_test capi2-13.5 { 772 check_origins {SELECT (SELECT col2 FROM view2), (SELECT col1 FROM view2)} 773 } [list {main tab1 col2} {main tab1 col1}] 774 do_test capi2-13.6 { 775 check_origins {SELECT (SELECT col2), (SELECT col1) FROM view2} 776 } [list {main tab1 col2} {main tab1 col1}] 777 do_test capi2-13.7 { 778 check_origins {SELECT * FROM view2} 779 } [list {main tab1 col1} {main tab1 col2}] 780 do_test capi2-13.8 { 781 check_origins {select * from (select * from view2)} 782 } [list {main tab1 col1} {main tab1 col2}] 783 do_test capi2-13.9 { 784 check_origins {select * from (select * from (select * from view2))} 785 } [list {main tab1 col1} {main tab1 col2}] 786 do_test capi2-13.10 { 787 db close 788 sqlite3 db test.db 789 set ::DB [sqlite3_connection_pointer db] 790 check_origins {select * from (select * from (select * from view2))} 791 } [list {main tab1 col1} {main tab1 col2}] 792 do_test capi2-13.11 { 793 check_origins {select * from (select * from tab1 limit 10 offset 10)} 794 } [list {main tab1 col1} {main tab1 col2}] 795} 796 797 798} ;# ifcapable columnmetadata 799 800db2 close 801finish_test 802