1 # 2010 April 19 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 source $testdir/tester.tcl 18 source $testdir/malloc_common.tcl 19 20 # If the library was compiled without WAL support, check that the 21 # "PRAGMA journal_mode=WAL" treats "WAL" as an unrecognized mode. 22 # 23 ifcapable !wal { 24 25 do_test walmode-0.1 { 26 execsql { PRAGMA journal_mode = wal } 27 } {delete} 28 do_test walmode-0.2 { 29 execsql { PRAGMA main.journal_mode = wal } 30 } {delete} 31 do_test walmode-0.3 { 32 execsql { PRAGMA main.journal_mode } 33 } {delete} 34 35 finish_test 36 return 37 } 38 39 do_test walmode-1.1 { 40 set sqlite_sync_count 0 41 execsql { PRAGMA page_size = 1024 } 42 execsql { PRAGMA journal_mode = wal } 43 } {wal} 44 do_test walmode-1.2 { 45 file size test.db 46 } {1024} 47 48 set expected_sync_count 3 49 if {$::tcl_platform(platform)!="windows"} { 50 ifcapable dirsync { 51 incr expected_sync_count 52 } 53 } 54 do_test walmode-1.3 { 55 set sqlite_sync_count 56 } $expected_sync_count 57 58 do_test walmode-1.4 { 59 file exists test.db-wal 60 } {0} 61 do_test walmode-1.5 { 62 execsql { CREATE TABLE t1(a, b) } 63 file size test.db 64 } {1024} 65 do_test walmode-1.6 { 66 file exists test.db-wal 67 } {1} 68 do_test walmode-1.7 { 69 db close 70 file exists test.db-wal 71 } {0} 72 73 # There is now a database file with the read and write versions set to 2 74 # in the file system. This file should default to WAL mode. 75 # 76 do_test walmode-2.1 { 77 sqlite3 db test.db 78 file exists test.db-wal 79 } {0} 80 do_test walmode-2.2 { 81 execsql { SELECT * FROM sqlite_master } 82 file exists test.db-wal 83 } {1} 84 do_test walmode-2.3 { 85 db close 86 file exists test.db-wal 87 } {0} 88 89 # If the first statement executed is "PRAGMA journal_mode = wal", and 90 # the file is already configured for WAL (read and write versions set 91 # to 2), then there should be no need to write the database. The 92 # statement should cause the client to connect to the log file. 93 # 94 set sqlite_sync_count 0 95 do_test walmode-3.1 { 96 sqlite3 db test.db 97 execsql { PRAGMA journal_mode = wal } 98 } {wal} 99 do_test walmode-3.2 { 100 list $sqlite_sync_count [file exists test.db-wal] [file size test.db-wal] 101 } {0 1 0} 102 103 # Test that changing back to journal_mode=persist works. 104 # 105 do_test walmode-4.1 { 106 execsql { INSERT INTO t1 VALUES(1, 2) } 107 execsql { PRAGMA journal_mode = persist } 108 } {persist} 109 do_test walmode-4.2 { 110 list [file exists test.db-journal] [file exists test.db-wal] 111 } {1 0} 112 do_test walmode-4.3 { 113 execsql { SELECT * FROM t1 } 114 } {1 2} 115 do_test walmode-4.4 { 116 db close 117 sqlite3 db test.db 118 execsql { SELECT * FROM t1 } 119 } {1 2} 120 do_test walmode-4.5 { 121 list [file exists test.db-journal] [file exists test.db-wal] 122 } {1 0} 123 124 # Test that nothing goes wrong if a connection is prevented from changing 125 # from WAL to rollback mode because a second connection has the database 126 # open. Or from rollback to WAL. 127 # 128 do_test walmode-4.6 { 129 sqlite3 db2 test.db 130 execsql { PRAGMA main.journal_mode } db2 131 } {delete} 132 do_test walmode-4.7 { 133 execsql { PRAGMA main.journal_mode = wal } db 134 } {wal} 135 do_test walmode-4.8 { 136 execsql { SELECT * FROM t1 } db2 137 } {1 2} 138 do_test walmode-4.9 { 139 catchsql { PRAGMA journal_mode = delete } db 140 } {1 {database is locked}} 141 do_test walmode-4.10 { 142 execsql { PRAGMA main.journal_mode } db 143 } {wal} 144 145 do_test walmode-4.11 { 146 db2 close 147 execsql { PRAGMA journal_mode = delete } db 148 } {delete} 149 do_test walmode-4.12 { 150 execsql { PRAGMA main.journal_mode } db 151 } {delete} 152 do_test walmode-4.13 { 153 list [file exists test.db-journal] [file exists test.db-wal] 154 } {0 0} 155 do_test walmode-4.14 { 156 sqlite3 db2 test.db 157 execsql { 158 BEGIN; 159 SELECT * FROM t1; 160 } db2 161 } {1 2} 162 163 do_test walmode-4.16 { execsql { PRAGMA main.journal_mode } db } {delete} 164 do_test walmode-4.17 { execsql { PRAGMA main.journal_mode } db2 } {delete} 165 166 do_test walmode-4.17 { 167 catchsql { PRAGMA main.journal_mode = wal } db 168 } {1 {database is locked}} 169 do_test walmode-4.18 { 170 execsql { PRAGMA main.journal_mode } db 171 } {delete} 172 catch { db close } 173 catch { db2 close } 174 175 # Test that it is not possible to change a temporary or in-memory database 176 # to WAL mode. WAL mode is for persistent file-backed databases only. 177 # 178 # walmode-5.1.*: Try to set journal_mode=WAL on [sqlite3 db :memory:] database. 179 # walmode-5.2.*: Try to set journal_mode=WAL on [sqlite3 db ""] database. 180 # walmode-5.3.*: Try to set temp.journal_mode=WAL. 181 # 182 do_test walmode-5.1.1 { 183 sqlite3 db :memory: 184 execsql { PRAGMA main.journal_mode } 185 } {memory} 186 do_test walmode-5.1.2 { 187 execsql { PRAGMA main.journal_mode = wal } 188 } {memory} 189 do_test walmode-5.1.3 { 190 execsql { 191 BEGIN; 192 CREATE TABLE t1(a, b); 193 INSERT INTO t1 VALUES(1, 2); 194 COMMIT; 195 SELECT * FROM t1; 196 PRAGMA main.journal_mode; 197 } 198 } {1 2 memory} 199 do_test walmode-5.1.4 { 200 execsql { PRAGMA main.journal_mode = wal } 201 } {memory} 202 do_test walmode-5.1.5 { 203 execsql { 204 INSERT INTO t1 VALUES(3, 4); 205 SELECT * FROM t1; 206 PRAGMA main.journal_mode; 207 } 208 } {1 2 3 4 memory} 209 210 if {$TEMP_STORE>=2} { 211 set tempJrnlMode memory 212 } else { 213 set tempJrnlMode delete 214 } 215 do_test walmode-5.2.1 { 216 sqlite3 db "" 217 execsql { PRAGMA main.journal_mode } 218 } $tempJrnlMode 219 do_test walmode-5.2.2 { 220 execsql { PRAGMA main.journal_mode = wal } 221 } $tempJrnlMode 222 do_test walmode-5.2.3 { 223 execsql { 224 BEGIN; 225 CREATE TABLE t1(a, b); 226 INSERT INTO t1 VALUES(1, 2); 227 COMMIT; 228 SELECT * FROM t1; 229 PRAGMA main.journal_mode; 230 } 231 } [list 1 2 $tempJrnlMode] 232 do_test walmode-5.2.4 { 233 execsql { PRAGMA main.journal_mode = wal } 234 } $tempJrnlMode 235 do_test walmode-5.2.5 { 236 execsql { 237 INSERT INTO t1 VALUES(3, 4); 238 SELECT * FROM t1; 239 PRAGMA main.journal_mode; 240 } 241 } [list 1 2 3 4 $tempJrnlMode] 242 243 do_test walmode-5.3.1 { 244 sqlite3 db test.db 245 execsql { PRAGMA temp.journal_mode } 246 } $tempJrnlMode 247 do_test walmode-5.3.2 { 248 execsql { PRAGMA temp.journal_mode = wal } 249 } $tempJrnlMode 250 do_test walmode-5.3.3 { 251 execsql { 252 BEGIN; 253 CREATE TEMP TABLE t1(a, b); 254 INSERT INTO t1 VALUES(1, 2); 255 COMMIT; 256 SELECT * FROM t1; 257 PRAGMA temp.journal_mode; 258 } 259 } [list 1 2 $tempJrnlMode] 260 do_test walmode-5.3.4 { 261 execsql { PRAGMA temp.journal_mode = wal } 262 } $tempJrnlMode 263 do_test walmode-5.3.5 { 264 execsql { 265 INSERT INTO t1 VALUES(3, 4); 266 SELECT * FROM t1; 267 PRAGMA temp.journal_mode; 268 } 269 } [list 1 2 3 4 $tempJrnlMode] 270 271 272 #------------------------------------------------------------------------- 273 # Test changing to WAL mode from journal_mode=off or journal_mode=memory 274 # 275 foreach {tn mode} { 276 1 off 277 2 memory 278 3 persist 279 4 delete 280 5 truncate 281 } { 282 do_test walmode-6.$tn { 283 faultsim_delete_and_reopen 284 execsql " 285 PRAGMA journal_mode = $mode; 286 PRAGMA journal_mode = wal; 287 " 288 } [list $mode wal] 289 } 290 db close 291 292 #------------------------------------------------------------------------- 293 # Test the effect of a "PRAGMA journal_mode" command being the first 294 # thing executed by a new connection. This means that the schema is not 295 # loaded when sqlite3_prepare_v2() is called to compile the statement. 296 # 297 do_test walmode-7.0 { 298 file delete -force test.db 299 sqlite3 db test.db 300 execsql { 301 PRAGMA journal_mode = WAL; 302 CREATE TABLE t1(a, b); 303 } 304 } {wal} 305 foreach {tn sql result} { 306 1 "PRAGMA journal_mode" wal 307 2 "PRAGMA main.journal_mode" wal 308 3 "PRAGMA journal_mode = delete" delete 309 4 "PRAGMA journal_mode" delete 310 5 "PRAGMA main.journal_mode" delete 311 6 "PRAGMA journal_mode = wal" wal 312 7 "PRAGMA journal_mode" wal 313 8 "PRAGMA main.journal_mode" wal 314 315 9 "PRAGMA journal_mode" wal 316 10 "PRAGMA main.journal_mode" wal 317 11 "PRAGMA main.journal_mode = delete" delete 318 12 "PRAGMA journal_mode" delete 319 13 "PRAGMA main.journal_mode" delete 320 14 "PRAGMA main.journal_mode = wal" wal 321 15 "PRAGMA journal_mode" wal 322 16 "PRAGMA main.journal_mode" wal 323 } { 324 do_test walmode-7.$tn { 325 db close 326 sqlite3 db test.db 327 execsql $sql 328 } $result 329 } 330 db close 331 332 #------------------------------------------------------------------------- 333 # Test the effect of a "PRAGMA journal_mode" command on an attached 334 # database. 335 # 336 faultsim_delete_and_reopen 337 do_execsql_test walmode-8.1 { 338 CREATE TABLE t1(a, b); 339 PRAGMA journal_mode = WAL; 340 ATTACH 'test.db2' AS two; 341 CREATE TABLE two.t2(a, b); 342 } {wal} 343 do_execsql_test walmode-8.2 { PRAGMA main.journal_mode } {wal} 344 do_execsql_test walmode-8.3 { PRAGMA two.journal_mode } {delete} 345 do_execsql_test walmode-8.4 { PRAGMA two.journal_mode = DELETE } {delete} 346 347 db close 348 sqlite3 db test.db 349 do_execsql_test walmode-8.5 { ATTACH 'test.db2' AS two } {} 350 do_execsql_test walmode-8.6 { PRAGMA main.journal_mode } {wal} 351 do_execsql_test walmode-8.7 { PRAGMA two.journal_mode } {delete} 352 do_execsql_test walmode-8.8 { INSERT INTO two.t2 DEFAULT VALUES } {} 353 do_execsql_test walmode-8.9 { PRAGMA two.journal_mode } {delete} 354 do_execsql_test walmode-8.10 { INSERT INTO t1 DEFAULT VALUES } {} 355 do_execsql_test walmode-8.11 { PRAGMA main.journal_mode } {wal} 356 do_execsql_test walmode-8.12 { PRAGMA journal_mode } {wal} 357 358 # Change to WAL mode on test2.db and make sure (in the tests that follow) 359 # that this mode change persists. 360 do_test walmode-8.x1 { 361 execsql { 362 PRAGMA two.journal_mode=WAL; 363 PRAGMA two.journal_mode; 364 } 365 } {wal wal} 366 367 db close 368 sqlite3 db test.db 369 do_execsql_test walmode-8.13 { PRAGMA journal_mode = WAL } {wal} 370 do_execsql_test walmode-8.14 { ATTACH 'test.db2' AS two } {} 371 do_execsql_test walmode-8.15 { PRAGMA main.journal_mode } {wal} 372 do_execsql_test walmode-8.16 { PRAGMA two.journal_mode } {wal} 373 do_execsql_test walmode-8.17 { INSERT INTO two.t2 DEFAULT VALUES } {} 374 do_execsql_test walmode-8.18 { PRAGMA two.journal_mode } {wal} 375 376 sqlite3 db2 test.db2 377 do_test walmode-8.19 { execsql { PRAGMA main.journal_mode } db2 } {wal} 378 db2 close 379 380 do_execsql_test walmode-8.20 { PRAGMA journal_mode = DELETE } {delete} 381 do_execsql_test walmode-8.21 { PRAGMA main.journal_mode } {delete} 382 do_execsql_test walmode-8.22 { PRAGMA two.journal_mode } {delete} 383 do_execsql_test walmode-8.21 { PRAGMA journal_mode = WAL } {wal} 384 do_execsql_test walmode-8.21 { PRAGMA main.journal_mode } {wal} 385 do_execsql_test walmode-8.22 { PRAGMA two.journal_mode } {wal} 386 387 finish_test 388
