gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/wal6.test (about) 1 # 2010 December 1 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 file is testing the operation of the library in 13 # "PRAGMA journal_mode=WAL" mode. 14 # 15 16 set testdir [file dirname $argv0] 17 set testprefix wal6 18 source $testdir/tester.tcl 19 source $testdir/lock_common.tcl 20 source $testdir/wal_common.tcl 21 source $testdir/malloc_common.tcl 22 ifcapable !wal {finish_test ; return } 23 24 #------------------------------------------------------------------------- 25 # Changing to WAL mode in one connection forces the change in others. 26 # 27 db close 28 forcedelete test.db 29 30 set all_journal_modes {delete persist truncate memory off} 31 foreach jmode $all_journal_modes { 32 33 do_test wal6-1.0.$jmode { 34 sqlite3 db test.db 35 execsql "PRAGMA journal_mode = $jmode;" 36 } $jmode 37 38 do_test wal6-1.1.$jmode { 39 execsql { 40 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 41 INSERT INTO t1 VALUES(1,2); 42 SELECT * FROM t1; 43 } 44 } {1 2} 45 46 # Under Windows, you'll get an error trying to delete 47 # a file this is already opened. Close the first connection 48 # so the other tests work. 49 if {$tcl_platform(platform)=="windows"} { 50 if {$jmode=="persist" || $jmode=="truncate"} { 51 db close 52 } 53 } 54 55 do_test wal6-1.2.$jmode { 56 sqlite3 db2 test.db 57 execsql { 58 PRAGMA journal_mode=WAL; 59 INSERT INTO t1 VALUES(3,4); 60 SELECT * FROM t1 ORDER BY a; 61 } db2 62 } {wal 1 2 3 4} 63 64 if {$tcl_platform(platform)=="windows"} { 65 if {$jmode=="persist" || $jmode=="truncate"} { 66 sqlite3 db test.db 67 } 68 } 69 70 do_test wal6-1.3.$jmode { 71 execsql { 72 SELECT * FROM t1 ORDER BY a; 73 } 74 } {1 2 3 4} 75 76 db close 77 db2 close 78 forcedelete test.db 79 80 } 81 82 #------------------------------------------------------------------------- 83 # Test that SQLITE_BUSY_SNAPSHOT is returned as expected. 84 # 85 reset_db 86 sqlite3 db2 test.db 87 88 do_execsql_test 2.1 { 89 PRAGMA journal_mode = WAL; 90 CREATE TABLE t1(a PRIMARY KEY, b TEXT); 91 INSERT INTO t1 VALUES(1, 'one'); 92 INSERT INTO t1 VALUES(2, 'two'); 93 BEGIN; 94 SELECT * FROM t1; 95 } {wal 1 one 2 two} 96 97 do_test 2.2 { 98 execsql { 99 SELECT * FROM t1; 100 INSERT INTO t1 VALUES(3, 'three'); 101 } db2 102 } {1 one 2 two} 103 104 do_catchsql_test 2.3 { 105 INSERT INTO t1 VALUES('x', 'x') 106 } {1 {database is locked}} 107 108 do_test 2.4 { 109 list [sqlite3_errcode db] [sqlite3_extended_errcode db] 110 } {SQLITE_BUSY SQLITE_BUSY_SNAPSHOT} 111 112 do_execsql_test 2.5 { 113 SELECT * FROM t1; 114 COMMIT; 115 INSERT INTO t1 VALUES('x', 'x') 116 } {1 one 2 two} 117 118 proc test3 {prefix} { 119 do_test $prefix.1 { 120 execsql { SELECT count(*) FROM t1 } 121 } {0} 122 do_test $prefix.2 { 123 execsql { INSERT INTO t1 VALUES('x', 'x') } db2 124 } {} 125 do_test $prefix.3 { 126 execsql { INSERT INTO t1 VALUES('y', 'y') } 127 } {} 128 do_test $prefix.4 { 129 execsql { SELECT count(*) FROM t1 } 130 } {2} 131 } 132 133 do_execsql_test 2.6.1 { DELETE FROM t1 } 134 test3 2.6.2 135 136 db func test3 test3 137 do_execsql_test 2.6.3 { DELETE FROM t1 } 138 db eval {SELECT test3('2.6.4')} 139 140 do_test 2.x { 141 db2 close 142 } {} 143 144 #------------------------------------------------------------------------- 145 # Check that if BEGIN IMMEDIATE fails, it does not leave the user with 146 # an open read-transaction (unless one was already open before the BEGIN 147 # IMMEDIATE). Even if there are other active VMs. 148 # 149 150 proc test4 {prefix} { 151 do_test $prefix.1 { 152 catchsql { BEGIN IMMEDIATE } 153 } {1 {database is locked}} 154 155 do_test $prefix.2 { 156 execsql { COMMIT } db2 157 } {} 158 159 do_test $prefix.3 { 160 execsql { BEGIN IMMEDIATE } 161 } {} 162 do_test $prefix.4 { 163 execsql { COMMIT } 164 } {} 165 } 166 167 reset_db 168 sqlite3 db2 test.db 169 do_execsql_test 3.1 { 170 PRAGMA journal_mode = WAL; 171 CREATE TABLE ab(a PRIMARY KEY, b); 172 } {wal} 173 174 do_test 3.2.1 { 175 execsql { 176 BEGIN; 177 INSERT INTO ab VALUES(1, 2); 178 } db2 179 } {} 180 test4 3.2.2 181 182 db func test4 test4 183 do_test 3.3.1 { 184 execsql { 185 BEGIN; 186 INSERT INTO ab VALUES(3, 4); 187 } db2 188 } {} 189 190 db eval {SELECT test4('3.3.2')} 191 192 do_test 3.x { 193 db2 close 194 } {} 195 196 #------------------------------------------------------------------------- 197 # Check that if a wal file has been partially checkpointed, no frames are 198 # read from the checkpointed part. 199 # 200 reset_db 201 do_execsql_test 4.1 { 202 PRAGMA page_size = 1024; 203 PRAGMA journal_mode = wal; 204 CREATE TABLE t1(a, b); 205 CREATE TABLE t2(a, b); 206 PRAGMA wal_checkpoint = truncate; 207 } {wal 0 0 0} 208 209 do_test 4.2 { 210 execsql { INSERT INTO t1 VALUES(1, 2) } 211 file size test.db-wal 212 } [wal_file_size 1 1024] 213 214 do_test 4.3 { 215 sqlite3 db2 test.db 216 execsql { 217 BEGIN; 218 INSERT INTO t2 VALUES(3, 4); 219 } 220 execsql { PRAGMA wal_checkpoint = passive } db2 221 } {0 1 1} 222 223 do_test 4.3 { 224 execsql { COMMIT } 225 db2 close 226 hexio_write test.db-wal 0 [string repeat 00 2000] 227 sqlite3 db2 test.db 228 } {} 229 230 do_test 4.4.1 { 231 catchsql { SELECT * FROM t1 } db2 232 } {0 {1 2}} 233 do_test 4.4.2 { 234 catchsql { SELECT * FROM t2 } db2 235 } {1 {database disk image is malformed}} 236 237 #------------------------------------------------------------------------- 238 # Confirm that it is possible to get an SQLITE_BUSY_SNAPSHOT error from 239 # "BEGIN EXCLUSIVE" if the connection already has an open read-transaction. 240 # 241 db close 242 db2 close 243 reset_db 244 sqlite3 db2 test.db 245 do_execsql_test 5.1 { 246 PRAGMA journal_mode = wal; 247 CREATE TABLE t1(x, y); 248 INSERT INTO t1 VALUES(1, 2); 249 INSERT INTO t1 VALUES(3, 4); 250 } {wal} 251 do_test 5.2 { 252 set res [list] 253 db eval { 254 SELECT * FROM t1 255 } { 256 if {$x==1} { 257 db2 eval { INSERT INTO t1 VALUES(5, 6) } 258 } 259 if {$x==3} { 260 set res [catchsql {BEGIN EXCLUSIVE}] 261 lappend res [sqlite3_extended_errcode db] 262 } 263 } 264 set res 265 } {1 {database is locked} SQLITE_BUSY_SNAPSHOT} 266 267 268 269 finish_test