gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/capi2.test (about) 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 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 # Return the text values from the current row pointed at by STMT as a list. 21 proc 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} 37 proc 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 # 50 do_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 } {} 56 do_test capi2-1.2 { 57 sqlite3_step $VM 58 } {SQLITE_ROW} 59 do_test capi2-1.3 { 60 sqlite3_data_count $VM 61 } {2} 62 do_test capi2-1.4 { 63 get_row_values $VM 64 } {t1 1} 65 do_test capi2-1.5 { 66 get_column_names $VM 67 } {name rowid TEXT INTEGER} 68 do_test capi2-1.6 { 69 sqlite3_step $VM 70 } {SQLITE_DONE} 71 do_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. 77 ifcapable 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. 91 do_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}} 95 do_test capi2-1.10 { 96 sqlite3_data_count $VM 97 } {0} 98 99 do_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 # 106 do_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 } 118 do_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}} 124 do_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}} 130 do_test capi2-2.4 { 131 sqlite3_finalize $VM 132 } {SQLITE_OK} 133 do_test capi2-2.5 { 134 set VM [sqlite3_prepare $DB $SQL -1 SQL] 135 set SQL 136 } { 137 -- A comment at the end 138 } 139 do_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}} 145 do_test capi2-2.7 { 146 sqlite3_finalize $VM 147 } {SQLITE_OK} 148 do_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 # 155 do_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} {}} 161 do_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) incomplete input} {}} 167 do_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} {}} 173 do_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;}} 179 do_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;}} 185 do_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 {}} 191 do_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 {}}} 197 do_test capi2-3.8 { 198 sqlite3_finalize $VM 199 } {SQLITE_OK} 200 do_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 } {} 205 do_test capi2-3.9b {db changes} {0} 206 do_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. 216 do_test capi2-3.10b {db changes} {1} 217 do_test capi2-3.11 { 218 sqlite3_finalize $VM 219 } {SQLITE_OK} 220 do_test capi2-3.11b {db changes} {1} 221 #do_test capi2-3.12-misuse { 222 # sqlite3_finalize $VM 223 #} {SQLITE_MISUSE} 224 do_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. 235 do_test capi2-3.13b {db changes} {0} 236 237 do_test capi2-3.14 { 238 list [sqlite3_finalize $VM] [sqlite3_errmsg $DB] \ 239 [sqlite3_extended_errcode $DB] 240 } {SQLITE_CONSTRAINT {UNIQUE constraint failed: t1.a} SQLITE_CONSTRAINT_UNIQUE} 241 do_test capi2-3.15 { 242 set VM [sqlite3_prepare $DB {CREATE TABLE t2(a NOT NULL, b)} -1 TAIL] 243 set TAIL 244 } {} 245 do_test capi2-3.16 { 246 list [sqlite3_step $VM] \ 247 [sqlite3_column_count $VM] \ 248 [get_row_values $VM] \ 249 [get_column_names $VM] 250 } {SQLITE_DONE 0 {} {}} 251 do_test capi2-3.17 { 252 list [sqlite3_finalize $VM] [sqlite3_errmsg $DB] 253 } {SQLITE_OK {not an error}} 254 do_test capi2-3.18 { 255 set VM [sqlite3_prepare $DB {INSERT INTO t2 VALUES(NULL,2)} -1 TAIL] 256 list [sqlite3_step $VM] \ 257 [sqlite3_column_count $VM] \ 258 [get_row_values $VM] \ 259 [get_column_names $VM] 260 } {SQLITE_ERROR 0 {} {}} 261 do_test capi2-3.19 { 262 list [sqlite3_finalize $VM] [sqlite3_errmsg $DB] \ 263 [sqlite3_extended_errcode $DB] 264 } {SQLITE_CONSTRAINT {NOT NULL constraint failed: t2.a} SQLITE_CONSTRAINT_NOTNULL} 265 266 do_test capi2-3.20 { 267 execsql { 268 CREATE TABLE a1(message_id, name , UNIQUE(message_id, name) ); 269 INSERT INTO a1 VALUES(1, 1); 270 } 271 } {} 272 do_test capi2-3.21 { 273 set VM [sqlite3_prepare $DB {INSERT INTO a1 VALUES(1, 1)} -1 TAIL] 274 sqlite3_step $VM 275 } {SQLITE_ERROR} 276 do_test capi2-3.22 { 277 sqlite3_errcode $DB 278 } {SQLITE_ERROR} 279 do_test capi2-3.23 { 280 sqlite3_finalize $VM 281 } {SQLITE_CONSTRAINT} 282 do_test capi2-3.24 { 283 list [sqlite3_errcode $DB] [sqlite3_extended_errcode $DB] 284 } {SQLITE_CONSTRAINT SQLITE_CONSTRAINT_UNIQUE} 285 286 # Two or more virtual machines exists at the same time. 287 # 288 do_test capi2-4.1 { 289 set VM1 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(1,2)} -1 TAIL] 290 set TAIL 291 } {} 292 do_test capi2-4.2 { 293 set VM2 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(2,3)} -1 TAIL] 294 set TAIL 295 } {} 296 do_test capi2-4.3 { 297 set VM3 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(3,4)} -1 TAIL] 298 set TAIL 299 } {} 300 do_test capi2-4.4 { 301 list [sqlite3_step $VM2] \ 302 [sqlite3_column_count $VM2] \ 303 [get_row_values $VM2] \ 304 [get_column_names $VM2] 305 } {SQLITE_DONE 0 {} {}} 306 do_test capi2-4.5 { 307 execsql {SELECT * FROM t2 ORDER BY a} 308 } {2 3} 309 do_test capi2-4.6 { 310 sqlite3_finalize $VM2 311 } {SQLITE_OK} 312 do_test capi2-4.7 { 313 list [sqlite3_step $VM3] \ 314 [sqlite3_column_count $VM3] \ 315 [get_row_values $VM3] \ 316 [get_column_names $VM3] 317 } {SQLITE_DONE 0 {} {}} 318 do_test capi2-4.8 { 319 execsql {SELECT * FROM t2 ORDER BY a} 320 } {2 3 3 4} 321 do_test capi2-4.9 { 322 sqlite3_finalize $VM3 323 } {SQLITE_OK} 324 do_test capi2-4.10 { 325 list [sqlite3_step $VM1] \ 326 [sqlite3_column_count $VM1] \ 327 [get_row_values $VM1] \ 328 [get_column_names $VM1] 329 } {SQLITE_DONE 0 {} {}} 330 do_test capi2-4.11 { 331 execsql {SELECT * FROM t2 ORDER BY a} 332 } {1 2 2 3 3 4} 333 do_test capi2-4.12 { 334 sqlite3_finalize $VM1 335 } {SQLITE_OK} 336 337 # Interleaved SELECTs 338 # 339 do_test capi2-5.1 { 340 set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL] 341 set VM2 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL] 342 set VM3 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL] 343 list [sqlite3_step $VM1] \ 344 [sqlite3_column_count $VM1] \ 345 [get_row_values $VM1] \ 346 [get_column_names $VM1] 347 } {SQLITE_ROW 2 {2 3} {a b {} {}}} 348 do_test capi2-5.2 { 349 list [sqlite3_step $VM2] \ 350 [sqlite3_column_count $VM2] \ 351 [get_row_values $VM2] \ 352 [get_column_names $VM2] 353 } {SQLITE_ROW 2 {2 3} {a b {} {}}} 354 do_test capi2-5.3 { 355 list [sqlite3_step $VM1] \ 356 [sqlite3_column_count $VM1] \ 357 [get_row_values $VM1] \ 358 [get_column_names $VM1] 359 } {SQLITE_ROW 2 {3 4} {a b {} {}}} 360 do_test capi2-5.4 { 361 list [sqlite3_step $VM3] \ 362 [sqlite3_column_count $VM3] \ 363 [get_row_values $VM3] \ 364 [get_column_names $VM3] 365 } {SQLITE_ROW 2 {2 3} {a b {} {}}} 366 do_test capi2-5.5 { 367 list [sqlite3_step $VM3] \ 368 [sqlite3_column_count $VM3] \ 369 [get_row_values $VM3] \ 370 [get_column_names $VM3] 371 } {SQLITE_ROW 2 {3 4} {a b {} {}}} 372 do_test capi2-5.6 { 373 list [sqlite3_step $VM3] \ 374 [sqlite3_column_count $VM3] \ 375 [get_row_values $VM3] \ 376 [get_column_names $VM3] 377 } {SQLITE_ROW 2 {1 2} {a b {} {}}} 378 do_test capi2-5.7 { 379 list [sqlite3_step $VM3] \ 380 [sqlite3_column_count $VM3] \ 381 [get_row_values $VM3] \ 382 [get_column_names $VM3] 383 } {SQLITE_DONE 2 {} {a b {} {}}} 384 do_test capi2-5.8 { 385 sqlite3_finalize $VM3 386 } {SQLITE_OK} 387 do_test capi2-5.9 { 388 list [sqlite3_step $VM1] \ 389 [sqlite3_column_count $VM1] \ 390 [get_row_values $VM1] \ 391 [get_column_names $VM1] 392 } {SQLITE_ROW 2 {1 2} {a b {} {}}} 393 do_test capi2-5.10 { 394 sqlite3_finalize $VM1 395 } {SQLITE_OK} 396 do_test capi2-5.11 { 397 list [sqlite3_step $VM2] \ 398 [sqlite3_column_count $VM2] \ 399 [get_row_values $VM2] \ 400 [get_column_names $VM2] 401 } {SQLITE_ROW 2 {3 4} {a b {} {}}} 402 do_test capi2-5.12 { 403 list [sqlite3_step $VM2] \ 404 [sqlite3_column_count $VM2] \ 405 [get_row_values $VM2] \ 406 [get_column_names $VM2] 407 } {SQLITE_ROW 2 {1 2} {a b {} {}}} 408 do_test capi2-5.11 { 409 sqlite3_finalize $VM2 410 } {SQLITE_OK} 411 412 # Check for proper SQLITE_BUSY returns. 413 # 414 do_test capi2-6.1 { 415 execsql { 416 BEGIN; 417 CREATE TABLE t3(x counter); 418 INSERT INTO t3 VALUES(1); 419 INSERT INTO t3 VALUES(2); 420 INSERT INTO t3 SELECT x+2 FROM t3; 421 INSERT INTO t3 SELECT x+4 FROM t3; 422 INSERT INTO t3 SELECT x+8 FROM t3; 423 COMMIT; 424 } 425 set VM1 [sqlite3_prepare $DB {SELECT * FROM t3} -1 TAIL] 426 sqlite3 db2 test.db 427 execsql {BEGIN} db2 428 } {} 429 # Update for v3: BEGIN doesn't write-lock the database. It is quite 430 # difficult to get v3 to write-lock the database, which causes a few 431 # problems for test scripts. 432 # 433 # do_test capi2-6.2 { 434 # list [sqlite3_step $VM1] \ 435 # [sqlite3_column_count $VM1] \ 436 # [get_row_values $VM1] \ 437 # [get_column_names $VM1] 438 # } {SQLITE_BUSY 0 {} {}} 439 do_test capi2-6.3 { 440 execsql {COMMIT} db2 441 } {} 442 do_test capi2-6.4 { 443 list [sqlite3_step $VM1] \ 444 [sqlite3_column_count $VM1] \ 445 [get_row_values $VM1] \ 446 [get_column_names $VM1] 447 } {SQLITE_ROW 1 1 {x counter}} 448 do_test capi2-6.5 { 449 catchsql {INSERT INTO t3 VALUES(10);} db2 450 } {1 {database is locked}} 451 do_test capi2-6.6 { 452 list [sqlite3_step $VM1] \ 453 [sqlite3_column_count $VM1] \ 454 [get_row_values $VM1] \ 455 [get_column_names $VM1] 456 } {SQLITE_ROW 1 2 {x counter}} 457 do_test capi2-6.7 { 458 execsql {SELECT * FROM t2} db2 459 } {2 3 3 4 1 2} 460 do_test capi2-6.8 { 461 list [sqlite3_step $VM1] \ 462 [sqlite3_column_count $VM1] \ 463 [get_row_values $VM1] \ 464 [get_column_names $VM1] 465 } {SQLITE_ROW 1 3 {x counter}} 466 do_test capi2-6.9 { 467 execsql {SELECT * FROM t2} 468 } {2 3 3 4 1 2} 469 do_test capi2-6.10 { 470 list [sqlite3_step $VM1] \ 471 [sqlite3_column_count $VM1] \ 472 [get_row_values $VM1] \ 473 [get_column_names $VM1] 474 } {SQLITE_ROW 1 4 {x counter}} 475 do_test capi2-6.11 { 476 execsql {BEGIN} 477 } {} 478 do_test capi2-6.12 { 479 list [sqlite3_step $VM1] \ 480 [sqlite3_column_count $VM1] \ 481 [get_row_values $VM1] \ 482 [get_column_names $VM1] 483 } {SQLITE_ROW 1 5 {x counter}} 484 485 # A read no longer blocks a write in the same connection. 486 #do_test capi2-6.13 { 487 # catchsql {UPDATE t3 SET x=x+1} 488 #} {1 {database table is locked}} 489 490 do_test capi2-6.14 { 491 list [sqlite3_step $VM1] \ 492 [sqlite3_column_count $VM1] \ 493 [get_row_values $VM1] \ 494 [get_column_names $VM1] 495 } {SQLITE_ROW 1 6 {x counter}} 496 do_test capi2-6.15 { 497 execsql {SELECT * FROM t1} 498 } {1 2 3} 499 do_test capi2-6.16 { 500 list [sqlite3_step $VM1] \ 501 [sqlite3_column_count $VM1] \ 502 [get_row_values $VM1] \ 503 [get_column_names $VM1] 504 } {SQLITE_ROW 1 7 {x counter}} 505 do_test capi2-6.17 { 506 catchsql {UPDATE t1 SET b=b+1} 507 } {0 {}} 508 do_test capi2-6.18 { 509 list [sqlite3_step $VM1] \ 510 [sqlite3_column_count $VM1] \ 511 [get_row_values $VM1] \ 512 [get_column_names $VM1] 513 } {SQLITE_ROW 1 8 {x counter}} 514 do_test capi2-6.19 { 515 execsql {SELECT * FROM t1} 516 } {1 3 3} 517 do_test capi2-6.20 { 518 list [sqlite3_step $VM1] \ 519 [sqlite3_column_count $VM1] \ 520 [get_row_values $VM1] \ 521 [get_column_names $VM1] 522 } {SQLITE_ROW 1 9 {x counter}} 523 #do_test capi2-6.21 { 524 # execsql {ROLLBACK; SELECT * FROM t1} 525 #} {1 2 3} 526 do_test capi2-6.22 { 527 list [sqlite3_step $VM1] \ 528 [sqlite3_column_count $VM1] \ 529 [get_row_values $VM1] \ 530 [get_column_names $VM1] 531 } {SQLITE_ROW 1 10 {x counter}} 532 #do_test capi2-6.23 { 533 # execsql {BEGIN TRANSACTION;} 534 #} {} 535 do_test capi2-6.24 { 536 list [sqlite3_step $VM1] \ 537 [sqlite3_column_count $VM1] \ 538 [get_row_values $VM1] \ 539 [get_column_names $VM1] 540 } {SQLITE_ROW 1 11 {x counter}} 541 do_test capi2-6.25 { 542 execsql { 543 INSERT INTO t1 VALUES(2,3,4); 544 SELECT * FROM t1; 545 } 546 } {1 3 3 2 3 4} 547 do_test capi2-6.26 { 548 list [sqlite3_step $VM1] \ 549 [sqlite3_column_count $VM1] \ 550 [get_row_values $VM1] \ 551 [get_column_names $VM1] 552 } {SQLITE_ROW 1 12 {x counter}} 553 do_test capi2-6.27 { 554 catchsql { 555 INSERT INTO t1 VALUES(2,4,5); 556 SELECT * FROM t1; 557 } 558 } {1 {UNIQUE constraint failed: t1.a}} 559 do_test capi2-6.28 { 560 list [sqlite3_step $VM1] \ 561 [sqlite3_column_count $VM1] \ 562 [get_row_values $VM1] \ 563 [get_column_names $VM1] 564 } {SQLITE_ROW 1 13 {x counter}} 565 do_test capi2-6.99 { 566 sqlite3_finalize $VM1 567 } {SQLITE_OK} 568 catchsql {ROLLBACK} 569 570 do_test capi2-7.1 { 571 stepsql $DB { 572 SELECT * FROM t1 573 } 574 } {0 1 2 3} 575 do_test capi2-7.2 { 576 stepsql $DB { 577 PRAGMA count_changes=on 578 } 579 } {0} 580 do_test capi2-7.3 { 581 stepsql $DB { 582 UPDATE t1 SET a=a+10; 583 } 584 } {0 1} 585 do_test capi2-7.4 { 586 stepsql $DB { 587 INSERT INTO t1 SELECT a+1,b+1,c+1 FROM t1; 588 } 589 } {0 1} 590 do_test capi2-7.4b {sqlite3_changes $DB} {1} 591 do_test capi2-7.5 { 592 stepsql $DB { 593 UPDATE t1 SET a=a+10; 594 } 595 } {0 2} 596 do_test capi2-7.5b {sqlite3_changes $DB} {2} 597 do_test capi2-7.6 { 598 stepsql $DB { 599 SELECT * FROM t1; 600 } 601 } {0 21 2 3 22 3 4} 602 do_test capi2-7.7 { 603 stepsql $DB { 604 INSERT INTO t1 SELECT a+2,b+2,c+2 FROM t1; 605 } 606 } {0 2} 607 do_test capi2-7.8 { 608 sqlite3_changes $DB 609 } {2} 610 do_test capi2-7.9 { 611 stepsql $DB { 612 SELECT * FROM t1; 613 } 614 } {0 21 2 3 22 3 4 23 4 5 24 5 6} 615 do_test capi2-7.10 { 616 stepsql $DB { 617 UPDATE t1 SET a=a-20; 618 SELECT * FROM t1; 619 } 620 } {0 4 1 2 3 2 3 4 3 4 5 4 5 6} 621 622 # Update for version 3: A SELECT statement no longer resets the change 623 # counter (Test result changes from 0 to 4). 624 do_test capi2-7.11 { 625 sqlite3_changes $DB 626 } {4} 627 do_test capi2-7.11a { 628 execsql {SELECT count(*) FROM t1} 629 } {4} 630 631 ifcapable {explain} { 632 do_test capi2-7.12 { 633 set x [stepsql $DB {EXPLAIN SELECT * FROM t1}] 634 lindex $x 0 635 } {0} 636 } 637 638 # Ticket #261 - make sure we can finalize before the end of a query. 639 # 640 do_test capi2-8.1 { 641 set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL] 642 sqlite3_finalize $VM1 643 } {SQLITE_OK} 644 645 # Tickets #384 and #385 - make sure the TAIL argument to sqlite3_prepare 646 # and all of the return pointers in sqlite_step can be null. 647 # 648 do_test capi2-9.1 { 649 set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 DUMMY] 650 sqlite3_step $VM1 651 sqlite3_finalize $VM1 652 } {SQLITE_OK} 653 654 # Test that passing a NULL pointer to sqlite3_finalize() or sqlite3_reset 655 # does not cause an error. 656 do_test capi2-10.1 { 657 sqlite3_finalize 0 658 } {SQLITE_OK} 659 do_test capi2-10.2 { 660 sqlite3_reset 0 661 } {SQLITE_OK} 662 663 #--------------------------------------------------------------------------- 664 # The following tests - capi2-11.* - test the "column origin" APIs. 665 # 666 # sqlite3_column_origin_name() 667 # sqlite3_column_database_name() 668 # sqlite3_column_table_name() 669 # 670 671 ifcapable columnmetadata { 672 673 # This proc uses the database handle $::DB to compile the SQL statement passed 674 # as a parameter. The return value of this procedure is a list with one 675 # element for each column returned by the compiled statement. Each element of 676 # this list is itself a list of length three, consisting of the origin 677 # database, table and column for the corresponding returned column. 678 proc check_origins {sql} { 679 set ret [list] 680 set ::STMT [sqlite3_prepare $::DB $sql -1 dummy] 681 for {set i 0} {$i < [sqlite3_column_count $::STMT]} {incr i} { 682 lappend ret [list \ 683 [sqlite3_column_database_name $::STMT $i] \ 684 [sqlite3_column_table_name $::STMT $i] \ 685 [sqlite3_column_origin_name $::STMT $i] \ 686 ] 687 } 688 sqlite3_finalize $::STMT 689 return $ret 690 } 691 do_test capi2-11.1 { 692 execsql { 693 CREATE TABLE tab1(col1, col2); 694 } 695 } {} 696 do_test capi2-11.2 { 697 check_origins {SELECT col2, col1 FROM tab1} 698 } [list {main tab1 col2} {main tab1 col1}] 699 do_test capi2-11.3 { 700 check_origins {SELECT col2 AS hello, col1 AS world FROM tab1} 701 } [list {main tab1 col2} {main tab1 col1}] 702 703 ifcapable subquery { 704 do_test capi2-11.4 { 705 check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM tab1)} 706 } [list {main tab1 col2} {main tab1 col1}] 707 do_test capi2-11.5 { 708 check_origins {SELECT (SELECT col2 FROM tab1), (SELECT col1 FROM tab1)} 709 } [list {main tab1 col2} {main tab1 col1}] 710 do_test capi2-11.6 { 711 check_origins {SELECT (SELECT col2), (SELECT col1) FROM tab1} 712 } [list {main tab1 col2} {main tab1 col1}] 713 do_test capi2-11.7 { 714 check_origins {SELECT * FROM tab1} 715 } [list {main tab1 col1} {main tab1 col2}] 716 do_test capi2-11.8 { 717 check_origins {SELECT * FROM (SELECT * FROM tab1)} 718 } [list {main tab1 col1} {main tab1 col2}] 719 } 720 721 ifcapable view&&subquery { 722 do_test capi2-12.1 { 723 execsql { 724 CREATE VIEW view1 AS SELECT * FROM tab1; 725 } 726 } {} 727 do_test capi2-12.2 { 728 check_origins {SELECT col2, col1 FROM view1} 729 } [list {main tab1 col2} {main tab1 col1}] 730 do_test capi2-12.3 { 731 check_origins {SELECT col2 AS hello, col1 AS world FROM view1} 732 } [list {main tab1 col2} {main tab1 col1}] 733 do_test capi2-12.4 { 734 check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM view1)} 735 } [list {main tab1 col2} {main tab1 col1}] 736 do_test capi2-12.5 { 737 check_origins {SELECT (SELECT col2 FROM view1), (SELECT col1 FROM view1)} 738 } [list {main tab1 col2} {main tab1 col1}] 739 do_test capi2-12.6 { 740 check_origins {SELECT (SELECT col2), (SELECT col1) FROM view1} 741 } [list {main tab1 col2} {main tab1 col1}] 742 do_test capi2-12.7 { 743 check_origins {SELECT * FROM view1} 744 } [list {main tab1 col1} {main tab1 col2}] 745 do_test capi2-12.8 { 746 check_origins {select * from (select * from view1)} 747 } [list {main tab1 col1} {main tab1 col2}] 748 do_test capi2-12.9 { 749 check_origins {select * from (select * from (select * from view1))} 750 } [list {main tab1 col1} {main tab1 col2}] 751 do_test capi2-12.10 { 752 db close 753 sqlite3 db test.db 754 set ::DB [sqlite3_connection_pointer db] 755 check_origins {select * from (select * from (select * from view1))} 756 } [list {main tab1 col1} {main tab1 col2}] 757 758 # This view will thwart the flattening optimization. 759 do_test capi2-13.1 { 760 execsql { 761 CREATE VIEW view2 AS SELECT * FROM tab1 limit 10 offset 10; 762 } 763 } {} 764 do_test capi2-13.2 { 765 check_origins {SELECT col2, col1 FROM view2} 766 } [list {main tab1 col2} {main tab1 col1}] 767 do_test capi2-13.3 { 768 check_origins {SELECT col2 AS hello, col1 AS world FROM view2} 769 } [list {main tab1 col2} {main tab1 col1}] 770 do_test capi2-13.4 { 771 check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM view2)} 772 } [list {main tab1 col2} {main tab1 col1}] 773 do_test capi2-13.5 { 774 check_origins {SELECT (SELECT col2 FROM view2), (SELECT col1 FROM view2)} 775 } [list {main tab1 col2} {main tab1 col1}] 776 do_test capi2-13.6 { 777 check_origins {SELECT (SELECT col2), (SELECT col1) FROM view2} 778 } [list {main tab1 col2} {main tab1 col1}] 779 do_test capi2-13.7 { 780 check_origins {SELECT * FROM view2} 781 } [list {main tab1 col1} {main tab1 col2}] 782 do_test capi2-13.8 { 783 check_origins {select * from (select * from view2)} 784 } [list {main tab1 col1} {main tab1 col2}] 785 do_test capi2-13.9 { 786 check_origins {select * from (select * from (select * from view2))} 787 } [list {main tab1 col1} {main tab1 col2}] 788 do_test capi2-13.10 { 789 db close 790 sqlite3 db test.db 791 set ::DB [sqlite3_connection_pointer db] 792 check_origins {select * from (select * from (select * from view2))} 793 } [list {main tab1 col1} {main tab1 col2}] 794 do_test capi2-13.11 { 795 check_origins {select * from (select * from tab1 limit 10 offset 10)} 796 } [list {main tab1 col1} {main tab1 col2}] 797 } 798 799 800 } ;# ifcapable columnmetadata 801 802 db2 close 803 finish_test