AryaWu/sqlite
0
1# 2008 June 182#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. 12#13# This file is devoted to testing the sqlite3_next_stmt and14# sqlite3_stmt_readonly and sqlite3_stmt_busy interfaces.15#16# $Id: capi3d.test,v 1.2 2008/07/14 15:11:20 drh Exp $17#18 19set testdir [file dirname $argv0]20source $testdir/tester.tcl21 22# Create N prepared statements against database connection db23# and return a list of all the generated prepared statements.24#25proc make_prepared_statements {N} {26 set plist {}27 for {set i 0} {$i<$N} {incr i} {28 set sql "SELECT $i FROM sqlite_master WHERE name LIKE '%$i%'"29 if {rand()<0.33} { 30 set s [sqlite3_prepare_v2 db $sql -1 notused]31 } else {32 ifcapable utf16 {33 if {rand()<0.5} {34 set sql [encoding convertto unicode $sql]\x00\x0035 set s [sqlite3_prepare16 db $sql -1 notused]36 } else {37 set s [sqlite3_prepare db $sql -1 notused]38 }39 }40 ifcapable !utf16 {41 set s [sqlite3_prepare db $sql -1 notused]42 }43 }44 lappend plist $s45 }46 return $plist47}48 49 50# Scramble the $inlist into a random order.51#52proc scramble {inlist} {53 set y {}54 foreach x $inlist {55 lappend y [list [expr {rand()}] $x]56 }57 set y [lsort $y]58 set outlist {}59 foreach x $y {60 lappend outlist [lindex $x 1]61 }62 return $outlist63}64 65# Database initially has no prepared statements.66#67do_test capi3d-1.1 {68 db cache flush69 sqlite3_next_stmt db 070} {}71 72# Run the following tests for between 1 and 100 prepared statements.73#74for {set i 1} {$i<=100} {incr i} {75 set stmtlist [make_prepared_statements $i]76 do_test capi3d-1.2.$i.1 {77 set p [sqlite3_next_stmt db 0]78 set x {}79 while {$p!=""} {80 lappend x $p81 set p [sqlite3_next_stmt db $p]82 }83 lsort $x84 } [lsort $stmtlist]85 do_test capi3-1.2.$i.2 {86 foreach p [scramble $::stmtlist] {87 sqlite3_finalize $p88 }89 sqlite3_next_stmt db 090 } {}91}92 93# Tests for the is-read-only interface.94#95proc test_is_readonly {testname sql truth} {96 do_test $testname [format {97 set DB [sqlite3_connection_pointer db]98 set STMT [sqlite3_prepare $DB {%s} -1 TAIL]99 set rc [sqlite3_stmt_readonly $STMT]100 sqlite3_finalize $STMT101 set rc102 } $sql] $truth103 104 # EVIDENCE-OF: R-61212-30018 If prepared statement X is an EXPLAIN or105 # EXPLAIN QUERY PLAN statement, then sqlite3_stmt_readonly(X) returns106 # the same value as if the EXPLAIN or EXPLAIN QUERY PLAN prefix were107 # omitted.108 #109 do_test $testname.explain [format {110 set DB [sqlite3_connection_pointer db]111 set STMT [sqlite3_prepare $DB {EXPLAIN %s} -1 TAIL]112 set rc [sqlite3_stmt_readonly $STMT]113 sqlite3_finalize $STMT114 set rc115 } $sql] $truth116 do_test $testname.eqp [format {117 set DB [sqlite3_connection_pointer db]118 set STMT [sqlite3_prepare $DB {EXPLAIN QUERY PLAN %s} -1 TAIL]119 set rc [sqlite3_stmt_readonly $STMT]120 sqlite3_finalize $STMT121 set rc122 } $sql] $truth123}124 125# EVIDENCE-OF: R-23332-64992 The sqlite3_stmt_readonly(X) interface126# returns true (non-zero) if and only if the prepared statement X makes127# no direct changes to the content of the database file.128#129test_is_readonly capi3d-2.1 {SELECT * FROM sqlite_master} 1130test_is_readonly capi3d-2.2 {CREATE TABLE t1(x)} 0131db eval {CREATE TABLE t1(x)}132test_is_readonly capi3d-2.3 {INSERT INTO t1 VALUES(5)} 0133test_is_readonly capi3d-2.4 {UPDATE t1 SET x=x+1 WHERE x<0} 0134test_is_readonly capi3d-2.5 {SELECT * FROM t1} 1135ifcapable wal {136 test_is_readonly capi3d-2.6 {PRAGMA journal_mode=WAL} 0137 test_is_readonly capi3d-2.7 {PRAGMA wal_checkpoint} 0138}139test_is_readonly capi3d-2.8 {PRAGMA application_id=1234} 0140test_is_readonly capi3d-2.9 {VACUUM} 0141test_is_readonly capi3d-2.10 {PRAGMA integrity_check} 1142do_test capi3-2.49 {143 sqlite3_stmt_readonly 0144} 1145 146 147# EVIDENCE-OF: R-04929-09147 This routine returns false if there is any148# possibility that the statement might change the database file.149#150# EVIDENCE-OF: R-13288-53765 A false return does not guarantee that the151# statement will change the database file.152#153# EVIDENCE-OF: R-22182-18548 For example, an UPDATE statement might have154# a WHERE clause that makes it a no-op, but the sqlite3_stmt_readonly()155# result would still be false.156#157# EVIDENCE-OF: R-50998-48593 Similarly, a CREATE TABLE IF NOT EXISTS158# statement is a read-only no-op if the table already exists, but159# sqlite3_stmt_readonly() still returns false for such a statement.160#161db eval {162 CREATE TABLE t2(a,b,c);163 INSERT INTO t2 VALUES(1,2,3);164}165test_is_readonly capi3d-2.11 {UPDATE t2 SET a=a+1 WHERE false} 0166test_is_readonly capi3d-2.12 {CREATE TABLE IF NOT EXISTS t2(x,y)} 0167 168 169# EVIDENCE-OF: R-37014-01401 The ATTACH and DETACH statements also cause170# sqlite3_stmt_readonly() to return true since, while those statements171# change the configuration of a database connection, they do not make172# changes to the content of the database files on disk.173#174test_is_readonly capi3d-2.13 {ATTACH ':memory:' AS mem1} 1175db eval {ATTACH ':memory:' AS mem1}176test_is_readonly capi3d-2.14 {DETACH mem1} 1177db eval {DETACH mem1}178 179# EVIDENCE-OF: R-07474-04783 Transaction control statements such as180# BEGIN, COMMIT, ROLLBACK, SAVEPOINT, and RELEASE cause181# sqlite3_stmt_readonly() to return true, since the statements182# themselves do not actually modify the database but rather they control183# the timing of when other statements modify the database.184#185test_is_readonly capi3d-2.15 {BEGIN} 1186test_is_readonly capi3d-2.16 {COMMIT} 1187test_is_readonly capi3d-2.17 {SAVEPOINT one} 1188test_is_readonly capi3d-2.18 {RELEASE one} 1189 190# EVIDENCE-OF: R-36961-63052 The sqlite3_stmt_readonly() interface191# returns true for BEGIN since BEGIN merely sets internal flags, but the192# BEGIN IMMEDIATE and BEGIN EXCLUSIVE commands do touch the database and193# so sqlite3_stmt_readonly() returns false for those commands.194#195test_is_readonly capi3d-2.19 {BEGIN IMMEDIATE} 0196test_is_readonly capi3d-2.20 {BEGIN EXCLUSIVE} 0197 198# EVIDENCE-OF: R-21769-42523 For example, if an application defines a199# function "eval()" that calls sqlite3_exec(), then the following SQL200# statement would change the database file through side-effects: SELECT201# eval('DELETE FROM t1') FROM t2; But because the SELECT statement does202# not change the database file directly, sqlite3_stmt_readonly() would203# still return true.204#205proc evalsql {sql} {db eval $sql}206db func eval evalsql207test_is_readonly capi3d-2.21 {SELECT eval('DELETE FROM t1') FROM t2} 1208 209# Tests for the is-explain interface.210#211proc test_is_explain {testname sql truth} {212 do_test $testname [format {213 set DB [sqlite3_connection_pointer db]214 set STMT [sqlite3_prepare $DB {%s} -1 TAIL]215 set rc [sqlite3_stmt_isexplain $STMT]216 sqlite3_finalize $STMT217 set rc218 } $sql] $truth219}220 221test_is_explain capi3d-2.51 {SELECT * FROM sqlite_master} 0222test_is_explain capi3d-2.52 { explain SELECT * FROM sqlite_master} 1223test_is_explain capi3d-2.53 { Explain Query Plan select * FROM sqlite_master} 2224do_test capi3-2.99 {225 sqlite3_stmt_isexplain 0226} 0227 228# Tests for sqlite3_stmt_busy229#230do_test capi3d-3.1 {231 db eval {INSERT INTO t1 VALUES(6); INSERT INTO t1 VALUES(7);}232 set STMT [sqlite3_prepare db {SELECT * FROM t1} -1 TAIL]233 sqlite3_stmt_busy $STMT234} {0}235do_test capi3d-3.2 {236 sqlite3_step $STMT237 sqlite3_stmt_busy $STMT238} {1}239do_test capi3d-3.3 {240 sqlite3_step $STMT241 sqlite3_stmt_busy $STMT242} {1}243do_test capi3d-3.4 {244 sqlite3_reset $STMT245 sqlite3_stmt_busy $STMT246} {0}247 248do_test capi3d-3.99 {249 sqlite3_finalize $STMT250 sqlite3_stmt_busy 0251} {0}252 253#--------------------------------------------------------------------------254# Test the sqlite3_stmt_busy() function with ROLLBACK statements.255#256reset_db257 258do_execsql_test capi3d-4.1 {259 CREATE TABLE t4(x,y);260 BEGIN;261}262 263do_test capi3d-4.2.1 {264 set ::s1 [sqlite3_prepare_v2 db "ROLLBACK" -1 notused]265 sqlite3_step $::s1266} {SQLITE_DONE}267 268do_test capi3d-4.2.2 {269 sqlite3_stmt_busy $::s1270} {0}271 272do_catchsql_test capi3d-4.2.3 {273 VACUUM274} {0 {}}275 276do_test capi3d-4.2.4 {277 sqlite3_reset $::s1278} {SQLITE_OK}279 280do_catchsql_test capi3d-4.2.5 {281 VACUUM282} {0 {}}283 284do_test capi3d-4.2.6 {285 sqlite3_finalize $::s1286} {SQLITE_OK}287 288 289finish_test290