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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18source $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# 23ifcapable !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 39do_test walmode-1.1 { 40 set sqlite_sync_count 0 41 execsql { PRAGMA page_size = 1024 } 42 execsql { PRAGMA journal_mode = wal } 43} {wal} 44do_test walmode-1.2 { 45 file size test.db 46} {1024} 47 48set expected_sync_count 3 49if {$::tcl_platform(platform)!="windows"} { 50 ifcapable dirsync { 51 incr expected_sync_count 52 } 53} 54do_test walmode-1.3 { 55 set sqlite_sync_count 56} $expected_sync_count 57 58do_test walmode-1.4 { 59 file exists test.db-wal 60} {0} 61do_test walmode-1.5 { 62 execsql { CREATE TABLE t1(a, b) } 63 file size test.db 64} {1024} 65do_test walmode-1.6 { 66 file exists test.db-wal 67} {1} 68do_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# 76do_test walmode-2.1 { 77 sqlite3 db test.db 78 file exists test.db-wal 79} {0} 80do_test walmode-2.2 { 81 execsql { SELECT * FROM sqlite_master } 82 file exists test.db-wal 83} {1} 84do_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# 94set sqlite_sync_count 0 95do_test walmode-3.1 { 96 sqlite3 db test.db 97 execsql { PRAGMA journal_mode = wal } 98} {wal} 99do_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# 105do_test walmode-4.1 { 106 execsql { INSERT INTO t1 VALUES(1, 2) } 107 execsql { PRAGMA journal_mode = persist } 108} {persist} 109do_test walmode-4.2 { 110 list [file exists test.db-journal] [file exists test.db-wal] 111} {1 0} 112do_test walmode-4.3 { 113 execsql { SELECT * FROM t1 } 114} {1 2} 115do_test walmode-4.4 { 116 db close 117 sqlite3 db test.db 118 execsql { SELECT * FROM t1 } 119} {1 2} 120do_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# 128do_test walmode-4.6 { 129 sqlite3 db2 test.db 130 execsql { PRAGMA main.journal_mode } db2 131} {delete} 132do_test walmode-4.7 { 133 execsql { PRAGMA main.journal_mode = wal } db 134} {wal} 135do_test walmode-4.8 { 136 execsql { SELECT * FROM t1 } db2 137} {1 2} 138do_test walmode-4.9 { 139 catchsql { PRAGMA journal_mode = delete } db 140} {1 {database is locked}} 141do_test walmode-4.10 { 142 execsql { PRAGMA main.journal_mode } db 143} {wal} 144 145do_test walmode-4.11 { 146 db2 close 147 execsql { PRAGMA journal_mode = delete } db 148} {delete} 149do_test walmode-4.12 { 150 execsql { PRAGMA main.journal_mode } db 151} {delete} 152do_test walmode-4.13 { 153 list [file exists test.db-journal] [file exists test.db-wal] 154} {0 0} 155do_test walmode-4.14 { 156 sqlite3 db2 test.db 157 execsql { 158 BEGIN; 159 SELECT * FROM t1; 160 } db2 161} {1 2} 162 163do_test walmode-4.16 { execsql { PRAGMA main.journal_mode } db } {delete} 164do_test walmode-4.17 { execsql { PRAGMA main.journal_mode } db2 } {delete} 165 166do_test walmode-4.17 { 167 catchsql { PRAGMA main.journal_mode = wal } db 168} {1 {database is locked}} 169do_test walmode-4.18 { 170 execsql { PRAGMA main.journal_mode } db 171} {delete} 172catch { db close } 173catch { 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# 182do_test walmode-5.1.1 { 183 sqlite3 db :memory: 184 execsql { PRAGMA main.journal_mode } 185} {memory} 186do_test walmode-5.1.2 { 187 execsql { PRAGMA main.journal_mode = wal } 188} {memory} 189do_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} 199do_test walmode-5.1.4 { 200 execsql { PRAGMA main.journal_mode = wal } 201} {memory} 202do_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 210if {$TEMP_STORE>=2} { 211 set tempJrnlMode memory 212} else { 213 set tempJrnlMode delete 214} 215do_test walmode-5.2.1 { 216 sqlite3 db "" 217 execsql { PRAGMA main.journal_mode } 218} $tempJrnlMode 219do_test walmode-5.2.2 { 220 execsql { PRAGMA main.journal_mode = wal } 221} $tempJrnlMode 222do_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] 232do_test walmode-5.2.4 { 233 execsql { PRAGMA main.journal_mode = wal } 234} $tempJrnlMode 235do_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 243do_test walmode-5.3.1 { 244 sqlite3 db test.db 245 execsql { PRAGMA temp.journal_mode } 246} $tempJrnlMode 247do_test walmode-5.3.2 { 248 execsql { PRAGMA temp.journal_mode = wal } 249} $tempJrnlMode 250do_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] 260do_test walmode-5.3.4 { 261 execsql { PRAGMA temp.journal_mode = wal } 262} $tempJrnlMode 263do_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# 275foreach {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} 290db 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# 297do_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} 305foreach {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} 330db close 331 332#------------------------------------------------------------------------- 333# Test the effect of a "PRAGMA journal_mode" command on an attached 334# database. 335# 336faultsim_delete_and_reopen 337do_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} 343do_execsql_test walmode-8.2 { PRAGMA main.journal_mode } {wal} 344do_execsql_test walmode-8.3 { PRAGMA two.journal_mode } {delete} 345do_execsql_test walmode-8.4 { PRAGMA two.journal_mode = DELETE } {delete} 346 347db close 348sqlite3 db test.db 349do_execsql_test walmode-8.5 { ATTACH 'test.db2' AS two } {} 350do_execsql_test walmode-8.6 { PRAGMA main.journal_mode } {wal} 351do_execsql_test walmode-8.7 { PRAGMA two.journal_mode } {delete} 352do_execsql_test walmode-8.8 { INSERT INTO two.t2 DEFAULT VALUES } {} 353do_execsql_test walmode-8.9 { PRAGMA two.journal_mode } {delete} 354do_execsql_test walmode-8.10 { INSERT INTO t1 DEFAULT VALUES } {} 355do_execsql_test walmode-8.11 { PRAGMA main.journal_mode } {wal} 356do_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. 360do_test walmode-8.x1 { 361 execsql { 362 PRAGMA two.journal_mode=WAL; 363 PRAGMA two.journal_mode; 364 } 365} {wal wal} 366 367db close 368sqlite3 db test.db 369do_execsql_test walmode-8.13 { PRAGMA journal_mode = WAL } {wal} 370do_execsql_test walmode-8.14 { ATTACH 'test.db2' AS two } {} 371do_execsql_test walmode-8.15 { PRAGMA main.journal_mode } {wal} 372do_execsql_test walmode-8.16 { PRAGMA two.journal_mode } {wal} 373do_execsql_test walmode-8.17 { INSERT INTO two.t2 DEFAULT VALUES } {} 374do_execsql_test walmode-8.18 { PRAGMA two.journal_mode } {wal} 375 376sqlite3 db2 test.db2 377do_test walmode-8.19 { execsql { PRAGMA main.journal_mode } db2 } {wal} 378db2 close 379 380do_execsql_test walmode-8.20 { PRAGMA journal_mode = DELETE } {delete} 381do_execsql_test walmode-8.21 { PRAGMA main.journal_mode } {delete} 382do_execsql_test walmode-8.22 { PRAGMA two.journal_mode } {delete} 383do_execsql_test walmode-8.21 { PRAGMA journal_mode = WAL } {wal} 384do_execsql_test walmode-8.21 { PRAGMA main.journal_mode } {wal} 385do_execsql_test walmode-8.22 { PRAGMA two.journal_mode } {wal} 386 387finish_test 388