AryaWu/sqlite
0
1# 2003 January 292#3# The author disclaims copyright to this source code. In place of4# 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. The12# 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.tcl19 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 $VALUES27}28 29# Return the column names followed by declaration types for the result set30# 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 $VALUES46}47 48# Check basic functionality49#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 TAIL55} {}56do_test capi2-1.2 {57 sqlite3_step $VM58} {SQLITE_ROW}59do_test capi2-1.3 {60 sqlite3_data_count $VM61} {2}62do_test capi2-1.4 {63 get_row_values $VM64} {t1 1}65do_test capi2-1.5 {66 get_column_names $VM67} {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 prepared76# statements.77ifcapable autoreset {78 do_test capi2-1.8 {79 sqlite3_step $VM80 } {SQLITE_ROW}81} else {82 do_test capi2-1.8 {83 sqlite3_step $VM84 } {SQLITE_MISUSE}85}86 87# Update: In v2, once SQLITE_MISUSE is returned the statement handle cannot88# be interrogated for more information. However in v3, since the column89# count, names and types are determined at compile time, these are still90# accessible after an SQLITE_MISUSE error.91do_test capi2-1.9 {92 sqlite3_reset $VM93 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 $VM97} {0}98 99do_test capi2-1.11 {100 sqlite3_finalize $VM101} {SQLITE_OK}102 103# Check to make sure that the "tail" of a multi-statement SQL script104# 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 end111 }112 set VM [sqlite3_prepare $DB $SQL -1 SQL]113 set SQL114} {115 SELECT name, rowid FROM sqlite_master WHERE 0;116 -- A comment at the end117 }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 $VM132} {SQLITE_OK}133do_test capi2-2.5 {134 set VM [sqlite3_prepare $DB $SQL -1 SQL]135 set SQL136} {137 -- A comment at the end138 }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 $VM147} {SQLITE_OK}148do_test capi2-2.8 {149 set VM [sqlite3_prepare $DB $SQL -1 SQL]150 list $SQL $VM151} {{} {}}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 TAIL158 } msg]159 lappend rc $msg $TAIL160} {1 {(1) no such column: bogus} {}}161do_test capi2-3.2 {162 set rc [catch {163 sqlite3_prepare $DB {select bogus from } -1 TAIL164 } msg]165 lappend rc $msg $TAIL166} {1 {(1) incomplete input} {}}167do_test capi2-3.3 {168 set rc [catch {169 sqlite3_prepare $DB {;;;;select bogus from sqlite_master} -1 TAIL170 } msg]171 lappend rc $msg $TAIL172} {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 TAIL176 } msg]177 lappend rc $msg $TAIL178} {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 TAIL182 } msg]183 lappend rc $msg $TAIL184} {1 {(1) no such column: bogus} {;;x;}}185do_test capi2-3.6 {186 set rc [catch {187 sqlite3_prepare $DB {select 5/0;} -1 TAIL188 } VM]189 lappend rc $TAIL190} {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 $VM199} {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 TAIL204} {}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 is214# 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 $VM219} {SQLITE_OK}220do_test capi2-3.11b {db changes} {1}221#do_test capi2-3.12-misuse {222# sqlite3_finalize $VM223#} {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 occur234# 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 [sqlite3_extended_errcode $DB]240} {SQLITE_CONSTRAINT {UNIQUE constraint failed: t1.a} SQLITE_CONSTRAINT_UNIQUE}241do_test capi2-3.15 {242 set VM [sqlite3_prepare $DB {CREATE TABLE t2(a NOT NULL, b)} -1 TAIL]243 set TAIL244} {}245do_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 {} {}}251do_test capi2-3.17 {252 list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]253} {SQLITE_OK {not an error}}254do_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 {} {}}261do_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 266do_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} {}272do_test capi2-3.21 {273 set VM [sqlite3_prepare $DB {INSERT INTO a1 VALUES(1, 1)} -1 TAIL]274 sqlite3_step $VM275} {SQLITE_ERROR}276do_test capi2-3.22 {277 sqlite3_errcode $DB278} {SQLITE_ERROR}279do_test capi2-3.23 {280 sqlite3_finalize $VM281} {SQLITE_CONSTRAINT}282do_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#288do_test capi2-4.1 {289 set VM1 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(1,2)} -1 TAIL]290 set TAIL291} {}292do_test capi2-4.2 {293 set VM2 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(2,3)} -1 TAIL]294 set TAIL295} {}296do_test capi2-4.3 {297 set VM3 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(3,4)} -1 TAIL]298 set TAIL299} {}300do_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 {} {}}306do_test capi2-4.5 {307 execsql {SELECT * FROM t2 ORDER BY a}308} {2 3}309do_test capi2-4.6 {310 sqlite3_finalize $VM2311} {SQLITE_OK}312do_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 {} {}}318do_test capi2-4.8 {319 execsql {SELECT * FROM t2 ORDER BY a}320} {2 3 3 4}321do_test capi2-4.9 {322 sqlite3_finalize $VM3323} {SQLITE_OK}324do_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 {} {}}330do_test capi2-4.11 {331 execsql {SELECT * FROM t2 ORDER BY a}332} {1 2 2 3 3 4}333do_test capi2-4.12 {334 sqlite3_finalize $VM1335} {SQLITE_OK}336 337# Interleaved SELECTs338#339do_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 {} {}}}348do_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 {} {}}}354do_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 {} {}}}360do_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 {} {}}}366do_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 {} {}}}372do_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 {} {}}}378do_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 {} {}}}384do_test capi2-5.8 {385 sqlite3_finalize $VM3386} {SQLITE_OK}387do_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 {} {}}}393do_test capi2-5.10 {394 sqlite3_finalize $VM1395} {SQLITE_OK}396do_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 {} {}}}402do_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 {} {}}}408do_test capi2-5.11 {409 sqlite3_finalize $VM2410} {SQLITE_OK}411 412# Check for proper SQLITE_BUSY returns.413#414do_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.db427 execsql {BEGIN} db2428} {}429# Update for v3: BEGIN doesn't write-lock the database. It is quite430# difficult to get v3 to write-lock the database, which causes a few431# 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 {} {}}439do_test capi2-6.3 {440 execsql {COMMIT} db2441} {}442do_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}}448do_test capi2-6.5 {449 catchsql {INSERT INTO t3 VALUES(10);} db2450} {1 {database is locked}}451do_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}}457do_test capi2-6.7 {458 execsql {SELECT * FROM t2} db2459} {2 3 3 4 1 2}460do_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}}466do_test capi2-6.9 {467 execsql {SELECT * FROM t2} 468} {2 3 3 4 1 2}469do_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}}475do_test capi2-6.11 {476 execsql {BEGIN}477} {}478do_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 490do_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}}496do_test capi2-6.15 {497 execsql {SELECT * FROM t1}498} {1 2 3}499do_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}}505do_test capi2-6.17 {506 catchsql {UPDATE t1 SET b=b+1}507} {0 {}}508do_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}}514do_test capi2-6.19 {515 execsql {SELECT * FROM t1}516} {1 3 3}517do_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}526do_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#} {}535do_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}}541do_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}547do_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}}553do_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}}559do_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}}565do_test capi2-6.99 {566 sqlite3_finalize $VM1567} {SQLITE_OK}568catchsql {ROLLBACK}569 570do_test capi2-7.1 {571 stepsql $DB {572 SELECT * FROM t1573 }574} {0 1 2 3}575do_test capi2-7.2 {576 stepsql $DB {577 PRAGMA count_changes=on578 }579} {0}580do_test capi2-7.3 {581 stepsql $DB {582 UPDATE t1 SET a=a+10;583 }584} {0 1}585do_test capi2-7.4 {586 stepsql $DB {587 INSERT INTO t1 SELECT a+1,b+1,c+1 FROM t1;588 }589} {0 1}590do_test capi2-7.4b {sqlite3_changes $DB} {1}591do_test capi2-7.5 {592 stepsql $DB {593 UPDATE t1 SET a=a+10;594 }595} {0 2}596do_test capi2-7.5b {sqlite3_changes $DB} {2}597do_test capi2-7.6 {598 stepsql $DB {599 SELECT * FROM t1;600 }601} {0 21 2 3 22 3 4}602do_test capi2-7.7 {603 stepsql $DB {604 INSERT INTO t1 SELECT a+2,b+2,c+2 FROM t1;605 }606} {0 2}607do_test capi2-7.8 {608 sqlite3_changes $DB609} {2}610do_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}615do_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 change623# counter (Test result changes from 0 to 4).624do_test capi2-7.11 {625 sqlite3_changes $DB626} {4}627do_test capi2-7.11a {628 execsql {SELECT count(*) FROM t1}629} {4}630 631ifcapable {explain} {632 do_test capi2-7.12 {633 set x [stepsql $DB {EXPLAIN SELECT * FROM t1}]634 lindex $x 0635 } {0}636}637 638# Ticket #261 - make sure we can finalize before the end of a query.639#640do_test capi2-8.1 {641 set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]642 sqlite3_finalize $VM1643} {SQLITE_OK}644 645# Tickets #384 and #385 - make sure the TAIL argument to sqlite3_prepare646# and all of the return pointers in sqlite_step can be null.647#648do_test capi2-9.1 {649 set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 DUMMY]650 sqlite3_step $VM1651 sqlite3_finalize $VM1652} {SQLITE_OK}653 654# Test that passing a NULL pointer to sqlite3_finalize() or sqlite3_reset655# does not cause an error.656do_test capi2-10.1 {657 sqlite3_finalize 0658} {SQLITE_OK}659do_test capi2-10.2 {660 sqlite3_reset 0661} {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 671ifcapable columnmetadata {672 673# This proc uses the database handle $::DB to compile the SQL statement passed674# as a parameter. The return value of this procedure is a list with one675# element for each column returned by the compiled statement. Each element of676# this list is itself a list of length three, consisting of the origin677# database, table and column for the corresponding returned column.678proc 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 $::STMT689 return $ret690}691do_test capi2-11.1 {692 execsql {693 CREATE TABLE tab1(col1, col2);694 }695} {}696do_test capi2-11.2 {697 check_origins {SELECT col2, col1 FROM tab1}698} [list {main tab1 col2} {main tab1 col1}]699do_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 703ifcapable 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 721ifcapable 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 close753 sqlite3 db test.db754 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 close790 sqlite3 db test.db791 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 columnmetadata801 802db2 close803finish_test804