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.6 { 128 sqlite3 db2 test.db 129 execsql { PRAGMA main.journal_mode } db2 130} {delete} 131do_test walmode-4.7 { 132 execsql { PRAGMA main.journal_mode = wal } db 133} {wal} 134do_test walmode-4.8 { 135 execsql { SELECT * FROM t1 } db2 136} {1 2} 137do_test walmode-4.9 { 138 catchsql { PRAGMA journal_mode = delete } db 139} {1 {database is locked}} 140do_test walmode-4.10 { 141 execsql { PRAGMA main.journal_mode } db 142} {wal} 143 144do_test walmode-4.11 { 145 db2 close 146 execsql { PRAGMA journal_mode = delete } db 147} {delete} 148do_test walmode-4.12 { 149 execsql { PRAGMA main.journal_mode } db 150} {delete} 151do_test walmode-4.13 { 152 list [file exists test.db-journal] [file exists test.db-wal] 153} {0 0} 154do_test walmode-4.14 { 155 sqlite3 db2 test.db 156 execsql { 157 BEGIN; 158 SELECT * FROM t1; 159 } db2 160} {1 2} 161 162do_test walmode-4.16 { execsql { PRAGMA main.journal_mode } db } {delete} 163do_test walmode-4.17 { execsql { PRAGMA main.journal_mode } db2 } {delete} 164 165do_test walmode-4.17 { 166 catchsql { PRAGMA main.journal_mode = wal } db 167} {1 {database is locked}} 168do_test walmode-4.18 { 169 execsql { PRAGMA main.journal_mode } db 170} {delete} 171catch { db close } 172catch { db2 close } 173 174# Test that it is not possible to change a temporary or in-memory database 175# to WAL mode. WAL mode is for persistent file-backed databases only. 176# 177# walmode-5.1.*: Try to set journal_mode=WAL on [sqlite3 db :memory:] database. 178# walmode-5.2.*: Try to set journal_mode=WAL on [sqlite3 db ""] database. 179# walmode-5.3.*: Try to set temp.journal_mode=WAL. 180# 181do_test walmode-5.1.1 { 182 sqlite3 db :memory: 183 execsql { PRAGMA main.journal_mode } 184} {memory} 185do_test walmode-5.1.2 { 186 execsql { PRAGMA main.journal_mode = wal } 187} {memory} 188do_test walmode-5.1.3 { 189 execsql { 190 BEGIN; 191 CREATE TABLE t1(a, b); 192 INSERT INTO t1 VALUES(1, 2); 193 COMMIT; 194 SELECT * FROM t1; 195 PRAGMA main.journal_mode; 196 } 197} {1 2 memory} 198do_test walmode-5.1.4 { 199 execsql { PRAGMA main.journal_mode = wal } 200} {memory} 201do_test walmode-5.1.5 { 202 execsql { 203 INSERT INTO t1 VALUES(3, 4); 204 SELECT * FROM t1; 205 PRAGMA main.journal_mode; 206 } 207} {1 2 3 4 memory} 208 209do_test walmode-5.2.1 { 210 sqlite3 db "" 211 execsql { PRAGMA main.journal_mode } 212} {delete} 213do_test walmode-5.2.2 { 214 execsql { PRAGMA main.journal_mode = wal } 215} {delete} 216do_test walmode-5.2.3 { 217 execsql { 218 BEGIN; 219 CREATE TABLE t1(a, b); 220 INSERT INTO t1 VALUES(1, 2); 221 COMMIT; 222 SELECT * FROM t1; 223 PRAGMA main.journal_mode; 224 } 225} {1 2 delete} 226do_test walmode-5.2.4 { 227 execsql { PRAGMA main.journal_mode = wal } 228} {delete} 229do_test walmode-5.2.5 { 230 execsql { 231 INSERT INTO t1 VALUES(3, 4); 232 SELECT * FROM t1; 233 PRAGMA main.journal_mode; 234 } 235} {1 2 3 4 delete} 236 237if {$TEMP_STORE>=2} { 238 set tempJrnlMode memory 239} else { 240 set tempJrnlMode delete 241} 242do_test walmode-5.3.1 { 243 sqlite3 db test.db 244 execsql { PRAGMA temp.journal_mode } 245} $tempJrnlMode 246do_test walmode-5.3.2 { 247 execsql { PRAGMA temp.journal_mode = wal } 248} $tempJrnlMode 249do_test walmode-5.3.3 { 250 execsql { 251 BEGIN; 252 CREATE TEMP TABLE t1(a, b); 253 INSERT INTO t1 VALUES(1, 2); 254 COMMIT; 255 SELECT * FROM t1; 256 PRAGMA temp.journal_mode; 257 } 258} [list 1 2 $tempJrnlMode] 259do_test walmode-5.3.4 { 260 execsql { PRAGMA temp.journal_mode = wal } 261} $tempJrnlMode 262do_test walmode-5.3.5 { 263 execsql { 264 INSERT INTO t1 VALUES(3, 4); 265 SELECT * FROM t1; 266 PRAGMA temp.journal_mode; 267 } 268} [list 1 2 3 4 $tempJrnlMode] 269 270finish_test 271