gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/exclusive2.test (about) 1 # 2007 March 24 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 # $Id: exclusive2.test,v 1.10 2008/11/27 02:22:11 drh Exp $ 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 18 # Do not use a codec for tests in this file, as the database file is 19 # manipulated directly using tcl scripts (using the [hexio_write] command). 20 # 21 do_not_use_codec 22 23 ifcapable {!pager_pragmas} { 24 finish_test 25 return 26 } 27 28 # Tests in this file verify that locking_mode=exclusive causes SQLite to 29 # use cached pages even if the database is changed on disk. This doesn't 30 # work with mmap. 31 if {[permutation]=="mmap"} { 32 finish_test 33 return 34 } 35 36 # This module does not work right if the cache spills at unexpected 37 # moments. So disable the soft-heap-limit. 38 # 39 sqlite3_soft_heap_limit 0 40 41 proc pagerChangeCounter {filename new {fd ""}} { 42 if {$fd==""} { 43 set fd [open $filename RDWR] 44 fconfigure $fd -translation binary -encoding binary 45 set needClose 1 46 } else { 47 set needClose 0 48 } 49 if {$new ne ""} { 50 seek $fd 24 51 set a [expr {($new&0xFF000000)>>24}] 52 set b [expr {($new&0x00FF0000)>>16}] 53 set c [expr {($new&0x0000FF00)>>8}] 54 set d [expr {($new&0x000000FF)}] 55 puts -nonewline $fd [binary format cccc $a $b $c $d] 56 flush $fd 57 } 58 59 seek $fd 24 60 foreach {a b c d} [list 0 0 0 0] {} 61 binary scan [read $fd 4] cccc a b c d 62 set ret [expr ($a&0x000000FF)<<24] 63 incr ret [expr ($b&0x000000FF)<<16] 64 incr ret [expr ($c&0x000000FF)<<8] 65 incr ret [expr ($d&0x000000FF)<<0] 66 67 if {$needClose} {close $fd} 68 return $ret 69 } 70 71 proc readPagerChangeCounter {filename} { 72 set fd [open $filename RDONLY] 73 fconfigure $fd -translation binary -encoding binary 74 75 seek $fd 24 76 foreach {a b c d} [list 0 0 0 0] {} 77 binary scan [read $fd 4] cccc a b c d 78 set ret [expr ($a&0x000000FF)<<24] 79 incr ret [expr ($b&0x000000FF)<<16] 80 incr ret [expr ($c&0x000000FF)<<8] 81 incr ret [expr ($d&0x000000FF)<<0] 82 83 close $fd 84 return $ret 85 } 86 87 88 proc t1sig {{db db}} { 89 execsql {SELECT count(*), md5sum(a) FROM t1} $db 90 } 91 do_test exclusive2-1.0 { 92 readPagerChangeCounter test.db 93 } {0} 94 95 #----------------------------------------------------------------------- 96 # The following tests - exclusive2-1.X - check that: 97 # 98 # 1-3: Build a database with connection 1, calculate a signature. 99 # 4-7: Modify the database using a second connection in a way that 100 # does not modify the freelist, then reset the pager change-counter 101 # to the value it had before the modifications. 102 # 8: Check that using the first connection, the database signature 103 # is still the same. This is because it uses the in-memory cache. 104 # It can't tell the db has changed because we reset the change-counter. 105 # 9: Increment the change-counter. 106 # 10: Ensure that the first connection now sees the updated database. It 107 # sees the change-counter has been incremented and discards the 108 # invalid in-memory cache. 109 # 110 # This will only work if the database cache is large enough to hold 111 # the entire database. In the case of 1024 byte pages, this means 112 # the cache size must be at least 17. Otherwise, some pages will be 113 # loaded from the database file in step 8. 114 # 115 # For similar reasons, this test does not work with the memsubsys1 permutation. 116 # Permutation memsubsys1 configures the pcache subsystem to use a static 117 # allocation of 24 pages (shared between all pagers). This is not enough for 118 # this test. 119 # 120 do_test exclusive2-1.1 { 121 execsql { 122 BEGIN; 123 CREATE TABLE t1(a, b); 124 INSERT INTO t1(a, b) VALUES(randstr(10, 400), 0); 125 INSERT INTO t1(a, b) VALUES(randstr(10, 400), 0); 126 INSERT INTO t1(a, b) SELECT randstr(10, 400), 0 FROM t1; 127 INSERT INTO t1(a, b) SELECT randstr(10, 400), 0 FROM t1; 128 INSERT INTO t1(a, b) SELECT randstr(10, 400), 0 FROM t1; 129 INSERT INTO t1(a, b) SELECT randstr(10, 400), 0 FROM t1; 130 INSERT INTO t1(a, b) SELECT randstr(10, 400), 0 FROM t1; 131 COMMIT; 132 SELECT count(*) FROM t1; 133 } 134 } {64} 135 do_test exclusive2-1.2.1 { 136 # Make sure the pager cache is large enough to store the 137 # entire database. 138 set nPage [expr [file size test.db]/1024] 139 if {$::SQLITE_DEFAULT_CACHE_SIZE < $nPage} { 140 execsql "PRAGMA cache_size = $nPage" 141 } 142 expr {[execsql {PRAGMA cache_size}] >= $nPage} 143 } {1} 144 do_test exclusive2-1.2 { 145 set ::sig [t1sig] 146 readPagerChangeCounter test.db 147 } {1} 148 do_test exclusive2-1.3 { 149 t1sig 150 } $::sig 151 do_test exclusive2-1.4 { 152 sqlite3 db2 test.db 153 t1sig db2 154 } $::sig 155 do_test exclusive2-1.5 { 156 execsql { 157 UPDATE t1 SET b=a, a=0; 158 } db2 159 expr {[t1sig db2] eq $::sig} 160 } 0 161 do_test exclusive2-1.6 { 162 readPagerChangeCounter test.db 163 } {2} 164 do_test exclusive2-1.7 { 165 pagerChangeCounter test.db 1 166 } {1} 167 if {[permutation] != "memsubsys1"} { 168 do_test exclusive2-1.9 { 169 t1sig 170 expr {[t1sig] eq $::sig} 171 } {1} 172 } 173 do_test exclusive2-1.10 { 174 pagerChangeCounter test.db 2 175 } {2} 176 do_test exclusive2-1.11 { 177 expr {[t1sig] eq $::sig} 178 } {0} 179 db2 close 180 181 #-------------------------------------------------------------------- 182 # These tests - exclusive2-2.X - are similar to exclusive2-1.X, 183 # except that they are run with locking_mode=EXCLUSIVE. 184 # 185 # 1-3: Build a database with exclusive-access connection 1, 186 # calculate a signature. 187 # 4: Corrupt the database by writing 10000 bytes of garbage 188 # starting at the beginning of page 2. Check that connection 1 189 # still works. It should be accessing the in-memory cache. 190 # 5-6: Modify the dataase change-counter. Connection 1 still works 191 # entirely from in-memory cache, because it doesn't check the 192 # change-counter. 193 # 7-8 Set the locking-mode back to normal. After the db is unlocked, 194 # SQLite detects the modified change-counter and discards the 195 # in-memory cache. Then it finds the corruption caused in step 4.... 196 # 197 # As above, this test is only applicable if the pager cache is 198 # large enough to hold the entire database. With 1024 byte pages, 199 # this means 19 pages. We also need to disable the soft-heap-limit 200 # to prevent memory-induced cache spills. 201 # 202 do_test exclusive2-2.1 { 203 execsql {PRAGMA cache_size=1000;} 204 execsql {PRAGMA locking_mode = exclusive;} 205 execsql { 206 BEGIN; 207 DELETE FROM t1; 208 INSERT INTO t1(a) VALUES(randstr(10, 400)); 209 INSERT INTO t1(a) VALUES(randstr(10, 400)); 210 INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1; 211 INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1; 212 INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1; 213 INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1; 214 INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1; 215 COMMIT; 216 SELECT count(*) FROM t1; 217 } 218 } {64} 219 do_test exclusive2-2.2.1 { 220 # Make sure the pager cache is large enough to store the 221 # entire database. 222 set nPage [expr [file size test.db]/1024] 223 if {$::SQLITE_DEFAULT_CACHE_SIZE < $nPage} { 224 execsql "PRAGMA cache_size = $nPage" 225 } 226 expr {[execsql {PRAGMA cache_size}] >= $nPage} 227 } {1} 228 do_test exclusive2-2.2 { 229 set ::sig [t1sig] 230 readPagerChangeCounter test.db 231 } {3} 232 do_test exclusive2-2.3 { 233 t1sig 234 } $::sig 235 236 do_test exclusive2-2.4 { 237 set ::fd [open test.db RDWR] 238 fconfigure $::fd -translation binary 239 seek $::fd 1024 240 puts -nonewline $::fd [string repeat [binary format c 0] 10000] 241 flush $::fd 242 t1sig 243 } $::sig 244 245 do_test exclusive2-2.5 { 246 pagerChangeCounter test.db 5 $::fd 247 } {5} 248 do_test exclusive2-2.6 { 249 t1sig 250 } $::sig 251 do_test exclusive2-2.7 { 252 execsql {PRAGMA locking_mode = normal} 253 t1sig 254 } $::sig 255 256 do_test exclusive2-2.8 { 257 set rc [catch {t1sig} msg] 258 list $rc $msg 259 } {1 {database disk image is malformed}} 260 261 #-------------------------------------------------------------------- 262 # These tests - exclusive2-3.X - verify that the pager change-counter 263 # is only incremented by the first change when in exclusive access 264 # mode. In normal mode, the change-counter is incremented once 265 # per write-transaction. 266 # 267 268 db close 269 catch {close $::fd} 270 forcedelete test.db 271 forcedelete test.db-journal 272 273 do_test exclusive2-3.0 { 274 sqlite3 db test.db 275 execsql { 276 BEGIN; 277 CREATE TABLE t1(a UNIQUE); 278 INSERT INTO t1 VALUES(randstr(200, 200)); 279 INSERT INTO t1 VALUES(randstr(200, 200)); 280 COMMIT; 281 } 282 readPagerChangeCounter test.db 283 } {1} 284 do_test exclusive2-3.1 { 285 execsql { 286 INSERT INTO t1 VALUES(randstr(200, 200)); 287 } 288 readPagerChangeCounter test.db 289 } {2} 290 do_test exclusive2-3.2 { 291 execsql { 292 INSERT INTO t1 VALUES(randstr(200, 200)); 293 } 294 readPagerChangeCounter test.db 295 } {3} 296 do_test exclusive2-3.3 { 297 execsql { 298 PRAGMA locking_mode = exclusive; 299 INSERT INTO t1 VALUES(randstr(200, 200)); 300 } 301 readPagerChangeCounter test.db 302 } {4} 303 do_test exclusive2-3.4 { 304 execsql { 305 INSERT INTO t1 VALUES(randstr(200, 200)); 306 } 307 readPagerChangeCounter test.db 308 } {4} 309 do_test exclusive2-3.5 { 310 execsql { 311 PRAGMA locking_mode = normal; 312 INSERT INTO t1 VALUES(randstr(200, 200)); 313 } 314 readPagerChangeCounter test.db 315 } {4} 316 do_test exclusive2-3.6 { 317 execsql { 318 INSERT INTO t1 VALUES(randstr(200, 200)); 319 } 320 readPagerChangeCounter test.db 321 } {5} 322 sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit) 323 324 finish_test