000001 # 2011 May 06
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 #
000012
000013 set testdir [file dirname $argv0]
000014 source $testdir/tester.tcl
000015 set testprefix e_wal
000016
000017 db close
000018 testvfs oldvfs -iversion 1
000019
000020
000021 # EVIDENCE-OF: R-58297-14483 WAL databases can be created, read, and
000022 # written even if shared memory is unavailable as long as the
000023 # locking_mode is set to EXCLUSIVE before the first attempted access.
000024 #
000025 # EVIDENCE-OF: R-00449-33772 This feature allows WAL databases to be
000026 # created, read, and written by legacy VFSes that lack the "version 2"
000027 # shared-memory methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on
000028 # the sqlite3_io_methods object.
000029 #
000030 # 1.1: "create" tests.
000031 # 1.2: "read" tests.
000032 # 1.3: "write" tests.
000033 #
000034 # All three done with VFS "oldvfs", which has iVersion==1 and so does
000035 # not support shared memory.
000036 #
000037 sqlite3 db test.db -vfs oldvfs
000038 do_execsql_test 1.1.1 {
000039 PRAGMA journal_mode = WAL;
000040 } {delete}
000041 do_execsql_test 1.1.2 {
000042 PRAGMA locking_mode = EXCLUSIVE;
000043 PRAGMA journal_mode = WAL;
000044 } {exclusive wal}
000045 do_execsql_test 1.1.3 {
000046 CREATE TABLE t1(x, y);
000047 INSERT INTO t1 VALUES(1, 2);
000048 } {}
000049 do_test 1.1.4 {
000050 list [file exists test.db-shm] [file exists test.db-wal]
000051 } {0 1}
000052
000053 do_test 1.2.1 {
000054 db close
000055 sqlite3 db test.db -vfs oldvfs
000056 catchsql { SELECT * FROM t1 }
000057 } {1 {unable to open database file}}
000058 do_test 1.2.2 {
000059 execsql { PRAGMA locking_mode = EXCLUSIVE }
000060 execsql { SELECT * FROM t1 }
000061 } {1 2}
000062 do_test 1.2.3 {
000063 list [file exists test.db-shm] [file exists test.db-wal]
000064 } {0 1}
000065
000066 do_test 1.3.1 {
000067 db close
000068 sqlite3 db test.db -vfs oldvfs
000069 catchsql { INSERT INTO t1 VALUES(3, 4) }
000070 } {1 {unable to open database file}}
000071 do_test 1.3.2 {
000072 execsql { PRAGMA locking_mode = EXCLUSIVE }
000073 execsql { INSERT INTO t1 VALUES(3, 4) }
000074 execsql { SELECT * FROM t1 }
000075 } {1 2 3 4}
000076 do_test 1.3.3 {
000077 list [file exists test.db-shm] [file exists test.db-wal]
000078 } {0 1}
000079
000080 # EVIDENCE-OF: R-31969-57825 If EXCLUSIVE locking mode is set prior to
000081 # the first WAL-mode database access, then SQLite never attempts to call
000082 # any of the shared-memory methods and hence no shared-memory wal-index
000083 # is ever created.
000084 #
000085 db close
000086 sqlite3 db test.db
000087 do_execsql_test 2.1.1 {
000088 PRAGMA locking_mode = EXCLUSIVE;
000089 SELECT * FROM t1;
000090 } {exclusive 1 2 3 4}
000091 do_test 2.1.2 {
000092 list [file exists test.db-shm] [file exists test.db-wal]
000093 } {0 1}
000094
000095 # EVIDENCE-OF: R-36328-16367 In that case, the database connection
000096 # remains in EXCLUSIVE mode as long as the journal mode is WAL; attempts
000097 # to change the locking mode using "PRAGMA locking_mode=NORMAL;" are
000098 # no-ops.
000099 #
000100 do_execsql_test 2.2.1 {
000101 PRAGMA locking_mode = NORMAL;
000102 SELECT * FROM t1;
000103 } {exclusive 1 2 3 4}
000104 do_test 2.2.2 {
000105 sqlite3 db2 test.db
000106 catchsql {SELECT * FROM t1} db2
000107 } {1 {database is locked}}
000108 db2 close
000109
000110 # EVIDENCE-OF: R-63522-46088 The only way to change out of EXCLUSIVE
000111 # locking mode is to first change out of WAL journal mode.
000112 #
000113 do_execsql_test 2.3.1 {
000114 PRAGMA journal_mode = DELETE;
000115 SELECT * FROM t1;
000116 } {delete 1 2 3 4}
000117 do_test 2.3.2 {
000118 sqlite3 db2 test.db
000119 catchsql {SELECT * FROM t1} db2
000120 } {1 {database is locked}}
000121 do_execsql_test 2.3.3 {
000122 PRAGMA locking_mode = NORMAL;
000123 SELECT * FROM t1;
000124 } {normal 1 2 3 4}
000125 do_test 2.3.4 {
000126 sqlite3 db2 test.db
000127 catchsql {SELECT * FROM t1} db2
000128 } {0 {1 2 3 4}}
000129 db2 close
000130 db close
000131
000132
000133 # EVIDENCE-OF: R-57239-11845 If NORMAL locking mode is in effect for the
000134 # first WAL-mode database access, then the shared-memory wal-index is
000135 # created.
000136 #
000137 do_test 3.0 {
000138 sqlite3 db test.db
000139 execsql { PRAGMA journal_mode = WAL }
000140 db close
000141 } {}
000142 do_test 3.1 {
000143 sqlite3 db test.db
000144 execsql { SELECT * FROM t1 }
000145 list [file exists test.db-shm] [file exists test.db-wal]
000146 } {1 1}
000147
000148 # EVIDENCE-OF: R-13779-07711 As long as exactly one connection is using
000149 # a shared-memory wal-index, the locking mode can be changed freely
000150 # between NORMAL and EXCLUSIVE.
000151 #
000152 do_execsql_test 3.2.1 {
000153 PRAGMA locking_mode = EXCLUSIVE;
000154 PRAGMA locking_mode = NORMAL;
000155 PRAGMA locking_mode = EXCLUSIVE;
000156 INSERT INTO t1 VALUES(5, 6);
000157 } {exclusive normal exclusive}
000158 do_test 3.2.2 {
000159 sqlite3 db2 test.db
000160 catchsql { SELECT * FROM t1 } db2
000161 } {1 {database is locked}}
000162
000163 # EVIDENCE-OF: R-10993-11647 It is only when the shared-memory wal-index
000164 # is omitted, when the locking mode is EXCLUSIVE prior to the first
000165 # WAL-mode database access, that the locking mode is stuck in EXCLUSIVE.
000166 #
000167 do_execsql_test 3.2.3 {
000168 PRAGMA locking_mode = NORMAL;
000169 SELECT * FROM t1;
000170 } {normal 1 2 3 4 5 6}
000171 do_test 3.2.4 {
000172 catchsql { SELECT * FROM t1 } db2
000173 } {0 {1 2 3 4 5 6}}
000174
000175 do_catchsql_test 3.2.5 {
000176 PRAGMA locking_mode = EXCLUSIVE;
000177 INSERT INTO t1 VALUES(7, 8);
000178 } {1 {database is locked}}
000179
000180 db2 close
000181
000182 # EVIDENCE-OF: R-46197-42811 This means that the underlying VFS must
000183 # support the "version 2" shared-memory.
000184 #
000185 # EVIDENCE-OF: R-55316-21772 If the VFS does not support shared-memory
000186 # methods, then the attempt to open a database that is already in WAL
000187 # mode, or the attempt convert a database into WAL mode, will fail.
000188 #
000189 db close
000190 do_test 3.4.1 {
000191 sqlite3 db test.db -vfs oldvfs
000192 catchsql { SELECT * FROM t1 }
000193 } {1 {unable to open database file}}
000194 db close
000195 do_test 3.4.2 {
000196 forcedelete test.db2
000197 sqlite3 db test.db2 -vfs oldvfs
000198 catchsql { PRAGMA journal_mode = WAL }
000199 } {0 delete}
000200 db close
000201
000202
000203 # EVIDENCE-OF: R-45540-25505 To prevent older versions of SQLite (prior
000204 # to version 3.7.0, 2010-07-22) from trying to recover a WAL-mode
000205 # database (and making matters worse) the database file format version
000206 # numbers (bytes 18 and 19 in the database header) are increased from 1
000207 # to 2 in WAL mode.
000208 #
000209 reset_db
000210 do_execsql_test 4.1.1 { CREATE TABLE t1(x, y) }
000211 do_test 4.1.2 { hexio_read test.db 18 2 } {0101}
000212 do_execsql_test 4.1.3 { PRAGMA journal_mode = wAL } {wal}
000213 do_test 4.1.4 { hexio_read test.db 18 2 } {0202}
000214
000215
000216 # EVIDENCE-OF: R-02535-05811 One can explicitly change out of WAL mode
000217 # using a pragma such as this: PRAGMA journal_mode=DELETE;
000218 #
000219 do_execsql_test 4.2.1 { INSERT INTO t1 VALUES(1, 1); } {}
000220 do_test 4.2.2 { file exists test.db-wal } {1}
000221 do_execsql_test 4.2.3 { PRAGMA journal_mode = delete } {delete}
000222 do_test 4.2.4 { file exists test.db-wal } {0}
000223
000224 # EVIDENCE-OF: R-60175-02388 Deliberately changing out of WAL mode
000225 # changes the database file format version numbers back to 1 so that
000226 # older versions of SQLite can once again access the database file.
000227 #
000228 do_test 4.3 { hexio_read test.db 18 2 } {0101}
000229
000230 finish_test