gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/e_wal.test (about) 1 # 2011 May 06 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 # 12 13 set testdir [file dirname $argv0] 14 source $testdir/tester.tcl 15 set testprefix e_wal 16 17 db close 18 testvfs oldvfs -iversion 1 19 20 21 # EVIDENCE-OF: R-58297-14483 WAL databases can be created, read, and 22 # written even if shared memory is unavailable as long as the 23 # locking_mode is set to EXCLUSIVE before the first attempted access. 24 # 25 # EVIDENCE-OF: R-00449-33772 This feature allows WAL databases to be 26 # created, read, and written by legacy VFSes that lack the "version 2" 27 # shared-memory methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on 28 # the sqlite3_io_methods object. 29 # 30 # 1.1: "create" tests. 31 # 1.2: "read" tests. 32 # 1.3: "write" tests. 33 # 34 # All three done with VFS "oldvfs", which has iVersion==1 and so does 35 # not support shared memory. 36 # 37 sqlite3 db test.db -vfs oldvfs 38 do_execsql_test 1.1.1 { 39 PRAGMA journal_mode = WAL; 40 } {delete} 41 do_execsql_test 1.1.2 { 42 PRAGMA locking_mode = EXCLUSIVE; 43 PRAGMA journal_mode = WAL; 44 } {exclusive wal} 45 do_execsql_test 1.1.3 { 46 CREATE TABLE t1(x, y); 47 INSERT INTO t1 VALUES(1, 2); 48 } {} 49 do_test 1.1.4 { 50 list [file exists test.db-shm] [file exists test.db-wal] 51 } {0 1} 52 53 do_test 1.2.1 { 54 db close 55 sqlite3 db test.db -vfs oldvfs 56 catchsql { SELECT * FROM t1 } 57 } {1 {unable to open database file}} 58 do_test 1.2.2 { 59 execsql { PRAGMA locking_mode = EXCLUSIVE } 60 execsql { SELECT * FROM t1 } 61 } {1 2} 62 do_test 1.2.3 { 63 list [file exists test.db-shm] [file exists test.db-wal] 64 } {0 1} 65 66 do_test 1.3.1 { 67 db close 68 sqlite3 db test.db -vfs oldvfs 69 catchsql { INSERT INTO t1 VALUES(3, 4) } 70 } {1 {unable to open database file}} 71 do_test 1.3.2 { 72 execsql { PRAGMA locking_mode = EXCLUSIVE } 73 execsql { INSERT INTO t1 VALUES(3, 4) } 74 execsql { SELECT * FROM t1 } 75 } {1 2 3 4} 76 do_test 1.3.3 { 77 list [file exists test.db-shm] [file exists test.db-wal] 78 } {0 1} 79 80 # EVIDENCE-OF: R-31969-57825 If EXCLUSIVE locking mode is set prior to 81 # the first WAL-mode database access, then SQLite never attempts to call 82 # any of the shared-memory methods and hence no shared-memory wal-index 83 # is ever created. 84 # 85 db close 86 sqlite3 db test.db 87 do_execsql_test 2.1.1 { 88 PRAGMA locking_mode = EXCLUSIVE; 89 SELECT * FROM t1; 90 } {exclusive 1 2 3 4} 91 do_test 2.1.2 { 92 list [file exists test.db-shm] [file exists test.db-wal] 93 } {0 1} 94 95 # EVIDENCE-OF: R-36328-16367 In that case, the database connection 96 # remains in EXCLUSIVE mode as long as the journal mode is WAL; attempts 97 # to change the locking mode using "PRAGMA locking_mode=NORMAL;" are 98 # no-ops. 99 # 100 do_execsql_test 2.2.1 { 101 PRAGMA locking_mode = NORMAL; 102 SELECT * FROM t1; 103 } {exclusive 1 2 3 4} 104 do_test 2.2.2 { 105 sqlite3 db2 test.db 106 catchsql {SELECT * FROM t1} db2 107 } {1 {database is locked}} 108 db2 close 109 110 # EVIDENCE-OF: R-63522-46088 The only way to change out of EXCLUSIVE 111 # locking mode is to first change out of WAL journal mode. 112 # 113 do_execsql_test 2.3.1 { 114 PRAGMA journal_mode = DELETE; 115 SELECT * FROM t1; 116 } {delete 1 2 3 4} 117 do_test 2.3.2 { 118 sqlite3 db2 test.db 119 catchsql {SELECT * FROM t1} db2 120 } {1 {database is locked}} 121 do_execsql_test 2.3.3 { 122 PRAGMA locking_mode = NORMAL; 123 SELECT * FROM t1; 124 } {normal 1 2 3 4} 125 do_test 2.3.4 { 126 sqlite3 db2 test.db 127 catchsql {SELECT * FROM t1} db2 128 } {0 {1 2 3 4}} 129 db2 close 130 db close 131 132 133 # EVIDENCE-OF: R-57239-11845 If NORMAL locking mode is in effect for the 134 # first WAL-mode database access, then the shared-memory wal-index is 135 # created. 136 # 137 do_test 3.0 { 138 sqlite3 db test.db 139 execsql { PRAGMA journal_mode = WAL } 140 db close 141 } {} 142 do_test 3.1 { 143 sqlite3 db test.db 144 execsql { SELECT * FROM t1 } 145 list [file exists test.db-shm] [file exists test.db-wal] 146 } {1 1} 147 148 # EVIDENCE-OF: R-13779-07711 As long as exactly one connection is using 149 # a shared-memory wal-index, the locking mode can be changed freely 150 # between NORMAL and EXCLUSIVE. 151 # 152 do_execsql_test 3.2.1 { 153 PRAGMA locking_mode = EXCLUSIVE; 154 PRAGMA locking_mode = NORMAL; 155 PRAGMA locking_mode = EXCLUSIVE; 156 INSERT INTO t1 VALUES(5, 6); 157 } {exclusive normal exclusive} 158 do_test 3.2.2 { 159 sqlite3 db2 test.db 160 catchsql { SELECT * FROM t1 } db2 161 } {1 {database is locked}} 162 163 # EVIDENCE-OF: R-10993-11647 It is only when the shared-memory wal-index 164 # is omitted, when the locking mode is EXCLUSIVE prior to the first 165 # WAL-mode database access, that the locking mode is stuck in EXCLUSIVE. 166 # 167 do_execsql_test 3.2.3 { 168 PRAGMA locking_mode = NORMAL; 169 SELECT * FROM t1; 170 } {normal 1 2 3 4 5 6} 171 do_test 3.2.4 { 172 catchsql { SELECT * FROM t1 } db2 173 } {0 {1 2 3 4 5 6}} 174 175 do_catchsql_test 3.2.5 { 176 PRAGMA locking_mode = EXCLUSIVE; 177 INSERT INTO t1 VALUES(7, 8); 178 } {1 {database is locked}} 179 180 db2 close 181 182 # EVIDENCE-OF: R-46197-42811 This means that the underlying VFS must 183 # support the "version 2" shared-memory. 184 # 185 # EVIDENCE-OF: R-55316-21772 If the VFS does not support shared-memory 186 # methods, then the attempt to open a database that is already in WAL 187 # mode, or the attempt convert a database into WAL mode, will fail. 188 # 189 db close 190 do_test 3.4.1 { 191 sqlite3 db test.db -vfs oldvfs 192 catchsql { SELECT * FROM t1 } 193 } {1 {unable to open database file}} 194 db close 195 do_test 3.4.2 { 196 forcedelete test.db2 197 sqlite3 db test.db2 -vfs oldvfs 198 catchsql { PRAGMA journal_mode = WAL } 199 } {0 delete} 200 db close 201 202 203 # EVIDENCE-OF: R-45540-25505 To prevent older versions of SQLite (prior 204 # to version 3.7.0, 2010-07-22) from trying to recover a WAL-mode 205 # database (and making matters worse) the database file format version 206 # numbers (bytes 18 and 19 in the database header) are increased from 1 207 # to 2 in WAL mode. 208 # 209 reset_db 210 do_execsql_test 4.1.1 { CREATE TABLE t1(x, y) } 211 do_test 4.1.2 { hexio_read test.db 18 2 } {0101} 212 do_execsql_test 4.1.3 { PRAGMA journal_mode = wAL } {wal} 213 do_test 4.1.4 { hexio_read test.db 18 2 } {0202} 214 215 216 # EVIDENCE-OF: R-02535-05811 One can explicitly change out of WAL mode 217 # using a pragma such as this: PRAGMA journal_mode=DELETE; 218 # 219 do_execsql_test 4.2.1 { INSERT INTO t1 VALUES(1, 1); } {} 220 do_test 4.2.2 { file exists test.db-wal } {1} 221 do_execsql_test 4.2.3 { PRAGMA journal_mode = delete } {delete} 222 do_test 4.2.4 { file exists test.db-wal } {0} 223 224 # EVIDENCE-OF: R-60175-02388 Deliberately changing out of WAL mode 225 # changes the database file format version numbers back to 1 so that 226 # older versions of SQLite can once again access the database file. 227 # 228 do_test 4.3 { hexio_read test.db 18 2 } {0101} 229 230 finish_test