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