gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/capi3d.test (about) 1 # 2008 June 18 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. 12 # 13 # This file is devoted to testing the sqlite3_next_stmt and 14 # 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 19 set testdir [file dirname $argv0] 20 source $testdir/tester.tcl 21 22 # Create N prepared statements against database connection db 23 # and return a list of all the generated prepared statements. 24 # 25 proc 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\x00 35 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 $s 45 } 46 return $plist 47 } 48 49 50 # Scramble the $inlist into a random order. 51 # 52 proc 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 $outlist 63 } 64 65 # Database initially has no prepared statements. 66 # 67 do_test capi3d-1.1 { 68 db cache flush 69 sqlite3_next_stmt db 0 70 } {} 71 72 # Run the following tests for between 1 and 100 prepared statements. 73 # 74 for {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 $p 81 set p [sqlite3_next_stmt db $p] 82 } 83 lsort $x 84 } [lsort $stmtlist] 85 do_test capi3-1.2.$i.2 { 86 foreach p [scramble $::stmtlist] { 87 sqlite3_finalize $p 88 } 89 sqlite3_next_stmt db 0 90 } {} 91 } 92 93 # Tests for the is-read-only interface. 94 # 95 proc 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 $STMT 101 set rc 102 } $sql] $truth 103 104 # EVIDENCE-OF: R-61212-30018 If prepared statement X is an EXPLAIN or 105 # EXPLAIN QUERY PLAN statement, then sqlite3_stmt_readonly(X) returns 106 # the same value as if the EXPLAIN or EXPLAIN QUERY PLAN prefix were 107 # 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 $STMT 114 set rc 115 } $sql] $truth 116 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 $STMT 121 set rc 122 } $sql] $truth 123 } 124 125 # EVIDENCE-OF: R-23332-64992 The sqlite3_stmt_readonly(X) interface 126 # returns true (non-zero) if and only if the prepared statement X makes 127 # no direct changes to the content of the database file. 128 # 129 test_is_readonly capi3d-2.1 {SELECT * FROM sqlite_master} 1 130 test_is_readonly capi3d-2.2 {CREATE TABLE t1(x)} 0 131 db eval {CREATE TABLE t1(x)} 132 test_is_readonly capi3d-2.3 {INSERT INTO t1 VALUES(5)} 0 133 test_is_readonly capi3d-2.4 {UPDATE t1 SET x=x+1 WHERE x<0} 0 134 test_is_readonly capi3d-2.5 {SELECT * FROM t1} 1 135 ifcapable wal { 136 test_is_readonly capi3d-2.6 {PRAGMA journal_mode=WAL} 0 137 test_is_readonly capi3d-2.7 {PRAGMA wal_checkpoint} 0 138 } 139 test_is_readonly capi3d-2.8 {PRAGMA application_id=1234} 0 140 test_is_readonly capi3d-2.9 {VACUUM} 0 141 test_is_readonly capi3d-2.10 {PRAGMA integrity_check} 1 142 do_test capi3-2.49 { 143 sqlite3_stmt_readonly 0 144 } 1 145 146 147 # EVIDENCE-OF: R-04929-09147 This routine returns false if there is any 148 # possibility that the statement might change the database file. 149 # 150 # EVIDENCE-OF: R-13288-53765 A false return does not guarantee that the 151 # statement will change the database file. 152 # 153 # EVIDENCE-OF: R-22182-18548 For example, an UPDATE statement might have 154 # 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 EXISTS 158 # statement is a read-only no-op if the table already exists, but 159 # sqlite3_stmt_readonly() still returns false for such a statement. 160 # 161 db eval { 162 CREATE TABLE t2(a,b,c); 163 INSERT INTO t2 VALUES(1,2,3); 164 } 165 test_is_readonly capi3d-2.11 {UPDATE t2 SET a=a+1 WHERE false} 0 166 test_is_readonly capi3d-2.12 {CREATE TABLE IF NOT EXISTS t2(x,y)} 0 167 168 169 # EVIDENCE-OF: R-37014-01401 The ATTACH and DETACH statements also cause 170 # sqlite3_stmt_readonly() to return true since, while those statements 171 # change the configuration of a database connection, they do not make 172 # changes to the content of the database files on disk. 173 # 174 test_is_readonly capi3d-2.13 {ATTACH ':memory:' AS mem1} 1 175 db eval {ATTACH ':memory:' AS mem1} 176 test_is_readonly capi3d-2.14 {DETACH mem1} 1 177 db eval {DETACH mem1} 178 179 # EVIDENCE-OF: R-07474-04783 Transaction control statements such as 180 # BEGIN, COMMIT, ROLLBACK, SAVEPOINT, and RELEASE cause 181 # sqlite3_stmt_readonly() to return true, since the statements 182 # themselves do not actually modify the database but rather they control 183 # the timing of when other statements modify the database. 184 # 185 test_is_readonly capi3d-2.15 {BEGIN} 1 186 test_is_readonly capi3d-2.16 {COMMIT} 1 187 test_is_readonly capi3d-2.17 {SAVEPOINT one} 1 188 test_is_readonly capi3d-2.18 {RELEASE one} 1 189 190 # EVIDENCE-OF: R-36961-63052 The sqlite3_stmt_readonly() interface 191 # returns true for BEGIN since BEGIN merely sets internal flags, but the 192 # BEGIN IMMEDIATE and BEGIN EXCLUSIVE commands do touch the database and 193 # so sqlite3_stmt_readonly() returns false for those commands. 194 # 195 test_is_readonly capi3d-2.19 {BEGIN IMMEDIATE} 0 196 test_is_readonly capi3d-2.20 {BEGIN EXCLUSIVE} 0 197 198 # EVIDENCE-OF: R-21769-42523 For example, if an application defines a 199 # function "eval()" that calls sqlite3_exec(), then the following SQL 200 # statement would change the database file through side-effects: SELECT 201 # eval('DELETE FROM t1') FROM t2; But because the SELECT statement does 202 # not change the database file directly, sqlite3_stmt_readonly() would 203 # still return true. 204 # 205 proc evalsql {sql} {db eval $sql} 206 db func eval evalsql 207 test_is_readonly capi3d-2.21 {SELECT eval('DELETE FROM t1') FROM t2} 1 208 209 # Tests for the is-explain interface. 210 # 211 proc 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 $STMT 217 set rc 218 } $sql] $truth 219 } 220 221 test_is_explain capi3d-2.51 {SELECT * FROM sqlite_master} 0 222 test_is_explain capi3d-2.52 { explain SELECT * FROM sqlite_master} 1 223 test_is_explain capi3d-2.53 { Explain Query Plan select * FROM sqlite_master} 2 224 do_test capi3-2.99 { 225 sqlite3_stmt_isexplain 0 226 } 0 227 228 # Tests for sqlite3_stmt_busy 229 # 230 do_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 $STMT 234 } {0} 235 do_test capi3d-3.2 { 236 sqlite3_step $STMT 237 sqlite3_stmt_busy $STMT 238 } {1} 239 do_test capi3d-3.3 { 240 sqlite3_step $STMT 241 sqlite3_stmt_busy $STMT 242 } {1} 243 do_test capi3d-3.4 { 244 sqlite3_reset $STMT 245 sqlite3_stmt_busy $STMT 246 } {0} 247 248 do_test capi3d-3.99 { 249 sqlite3_finalize $STMT 250 sqlite3_stmt_busy 0 251 } {0} 252 253 #-------------------------------------------------------------------------- 254 # Test the sqlite3_stmt_busy() function with ROLLBACK statements. 255 # 256 reset_db 257 258 do_execsql_test capi3d-4.1 { 259 CREATE TABLE t4(x,y); 260 BEGIN; 261 } 262 263 do_test capi3d-4.2.1 { 264 set ::s1 [sqlite3_prepare_v2 db "ROLLBACK" -1 notused] 265 sqlite3_step $::s1 266 } {SQLITE_DONE} 267 268 do_test capi3d-4.2.2 { 269 sqlite3_stmt_busy $::s1 270 } {0} 271 272 do_catchsql_test capi3d-4.2.3 { 273 VACUUM 274 } {0 {}} 275 276 do_test capi3d-4.2.4 { 277 sqlite3_reset $::s1 278 } {SQLITE_OK} 279 280 do_catchsql_test capi3d-4.2.5 { 281 VACUUM 282 } {0 {}} 283 284 do_test capi3d-4.2.6 { 285 sqlite3_finalize $::s1 286 } {SQLITE_OK} 287 288 289 finish_test