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 18 19# If the library was compiled without WAL support, check that the 20# "PRAGMA journal_mode=WAL" treats "WAL" as an unrecognized mode. 21# 22ifcapable !wal { 23 24 do_test walmode-0.1 { 25 execsql { PRAGMA journal_mode = wal } 26 } {delete} 27 do_test walmode-0.2 { 28 execsql { PRAGMA main.journal_mode = wal } 29 } {delete} 30 do_test walmode-0.3 { 31 execsql { PRAGMA main.journal_mode } 32 } {delete} 33 34 finish_test 35 return 36} 37 38do_test walmode-1.1 { 39 set sqlite_sync_count 0 40 execsql { PRAGMA page_size = 1024 } 41 execsql { PRAGMA journal_mode = wal } 42} {wal} 43do_test walmode-1.2 { 44 file size test.db 45} {1024} 46 47set expected_sync_count 3 48if {$::tcl_platform(platform)!="windows"} { 49 ifcapable dirsync { 50 incr expected_sync_count 51 } 52} 53do_test walmode-1.3 { 54 set sqlite_sync_count 55} $expected_sync_count 56 57do_test walmode-1.4 { 58 file exists test.db-wal 59} {0} 60do_test walmode-1.5 { 61 execsql { CREATE TABLE t1(a, b) } 62 file size test.db 63} {1024} 64do_test walmode-1.6 { 65 file exists test.db-wal 66} {1} 67do_test walmode-1.7 { 68 db close 69 file exists test.db-wal 70} {0} 71 72# There is now a database file with the read and write versions set to 2 73# in the file system. This file should default to WAL mode. 74# 75do_test walmode-2.1 { 76 sqlite3 db test.db 77 file exists test.db-wal 78} {0} 79do_test walmode-2.2 { 80 execsql { SELECT * FROM sqlite_master } 81 file exists test.db-wal 82} {1} 83do_test walmode-2.3 { 84 db close 85 file exists test.db-wal 86} {0} 87 88# If the first statement executed is "PRAGMA journal_mode = wal", and 89# the file is already configured for WAL (read and write versions set 90# to 2), then there should be no need to write the database. The 91# statement should cause the client to connect to the log file. 92# 93set sqlite_sync_count 0 94do_test walmode-3.1 { 95 sqlite3 db test.db 96 execsql { PRAGMA journal_mode = wal } 97} {wal} 98do_test walmode-3.2 { 99 list $sqlite_sync_count [file exists test.db-wal] [file size test.db-wal] 100} {0 1 0} 101 102# Test that changing back to journal_mode=persist works. 103# 104do_test walmode-4.1 { 105 execsql { INSERT INTO t1 VALUES(1, 2) } 106 execsql { PRAGMA journal_mode = persist } 107} {persist} 108do_test walmode-4.2 { 109 list [file exists test.db-journal] [file exists test.db-wal] 110} {1 0} 111do_test walmode-4.3 { 112 execsql { SELECT * FROM t1 } 113} {1 2} 114do_test walmode-4.4 { 115 db close 116 sqlite3 db test.db 117 execsql { SELECT * FROM t1 } 118} {1 2} 119do_test walmode-4.5 { 120 list [file exists test.db-journal] [file exists test.db-wal] 121} {1 0} 122 123# Test that nothing goes wrong if a connection is prevented from changing 124# from WAL to rollback mode because a second connection has the database 125# open. Or from rollback to WAL. 126# 127do_test walmode-4.1 { 128 sqlite3 db2 test.db 129 execsql { PRAGMA main.journal_mode } db2 130} {delete} 131do_test walmode-4.2 { 132 execsql { PRAGMA main.journal_mode = wal } db 133} {wal} 134do_test walmode-4.3 { 135 execsql { SELECT * FROM t1 } db2 136} {1 2} 137do_test walmode-4.4 { 138 catchsql { PRAGMA journal_mode = delete } db 139} {1 {database is locked}} 140do_test walmode-4.5 { 141 execsql { PRAGMA main.journal_mode } db 142} {wal} 143do_test walmode-4.6 { 144 db2 close 145 execsql { PRAGMA journal_mode = delete } db 146} {delete} 147do_test walmode-4.7 { 148 execsql { PRAGMA main.journal_mode } db 149} {delete} 150do_test walmode-4.8 { 151 list [file exists test.db-journal] [file exists test.db-wal] 152} {0 0} 153do_test walmode-4.9 { 154 sqlite3 db2 test.db 155 execsql { 156 BEGIN; 157 SELECT * FROM t1; 158 } db2 159} {1 2} 160do_test walmode-4.11 { 161 execsql { PRAGMA main.journal_mode } db 162} {delete} 163do_test walmode-4.10 { 164 catchsql { PRAGMA main.journal_mode = wal } db 165} {1 {database is locked}} 166do_test walmode-4.11 { 167 execsql { PRAGMA main.journal_mode } db 168} {delete} 169catch { db close } 170catch { db2 close } 171 172# Test that it is not possible to change a temporary or in-memory database 173# to WAL mode. WAL mode is for persistent file-backed databases only. 174# 175# walmode-5.1.*: Try to set journal_mode=WAL on [sqlite3 db :memory:] database. 176# walmode-5.2.*: Try to set journal_mode=WAL on [sqlite3 db ""] database. 177# walmode-5.3.*: Try to set temp.journal_mode=WAL. 178# 179do_test walmode-5.1.1 { 180 sqlite3 db :memory: 181 execsql { PRAGMA main.journal_mode } 182} {memory} 183breakpoint 184do_test walmode-5.1.2 { 185 execsql { PRAGMA main.journal_mode = wal } 186} {memory} 187do_test walmode-5.1.3 { 188 execsql { 189 BEGIN; 190 CREATE TABLE t1(a, b); 191 INSERT INTO t1 VALUES(1, 2); 192 COMMIT; 193 SELECT * FROM t1; 194 PRAGMA main.journal_mode; 195 } 196} {1 2 memory} 197do_test walmode-5.1.4 { 198 execsql { PRAGMA main.journal_mode = wal } 199} {memory} 200do_test walmode-5.1.5 { 201 execsql { 202 INSERT INTO t1 VALUES(3, 4); 203 SELECT * FROM t1; 204 PRAGMA main.journal_mode; 205 } 206} {1 2 3 4 memory} 207 208do_test walmode-5.2.1 { 209 sqlite3 db "" 210 execsql { PRAGMA main.journal_mode } 211} {delete} 212do_test walmode-5.2.2 { 213 execsql { PRAGMA main.journal_mode = wal } 214} {delete} 215do_test walmode-5.2.3 { 216 execsql { 217 BEGIN; 218 CREATE TABLE t1(a, b); 219 INSERT INTO t1 VALUES(1, 2); 220 COMMIT; 221 SELECT * FROM t1; 222 PRAGMA main.journal_mode; 223 } 224} {1 2 delete} 225do_test walmode-5.2.4 { 226 execsql { PRAGMA main.journal_mode = wal } 227} {delete} 228do_test walmode-5.2.5 { 229 execsql { 230 INSERT INTO t1 VALUES(3, 4); 231 SELECT * FROM t1; 232 PRAGMA main.journal_mode; 233 } 234} {1 2 3 4 delete} 235 236if {$TEMP_STORE>=2} { 237 set tempJrnlMode memory 238} else { 239 set tempJrnlMode delete 240} 241do_test walmode-5.3.1 { 242 sqlite3 db test.db 243 execsql { PRAGMA temp.journal_mode } 244} $tempJrnlMode 245do_test walmode-5.3.2 { 246 execsql { PRAGMA temp.journal_mode = wal } 247} $tempJrnlMode 248do_test walmode-5.3.3 { 249 execsql { 250 BEGIN; 251 CREATE TEMP TABLE t1(a, b); 252 INSERT INTO t1 VALUES(1, 2); 253 COMMIT; 254 SELECT * FROM t1; 255 PRAGMA temp.journal_mode; 256 } 257} [list 1 2 $tempJrnlMode] 258do_test walmode-5.3.4 { 259 execsql { PRAGMA temp.journal_mode = wal } 260} $tempJrnlMode 261do_test walmode-5.3.5 { 262 execsql { 263 INSERT INTO t1 VALUES(3, 4); 264 SELECT * FROM t1; 265 PRAGMA temp.journal_mode; 266 } 267} [list 1 2 3 4 $tempJrnlMode] 268 269finish_test 270