AryaWu/sqlite

1# 2011 May 062#3# The author disclaims copyright to this source code.  In place of4# 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 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set testprefix e_wal16 17db close18forcedelete test.db-shm 19testvfs oldvfs -iversion 120 21 22# EVIDENCE-OF: R-58297-14483 WAL databases can be created, read, and23# written even if shared memory is unavailable as long as the24# locking_mode is set to EXCLUSIVE before the first attempted access.25#26# EVIDENCE-OF: R-00449-33772 This feature allows WAL databases to be27# created, read, and written by legacy VFSes that lack the "version 2"28# shared-memory methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on29# the sqlite3_io_methods object.30#31# 1.1: "create" tests.32# 1.2: "read" tests.33# 1.3: "write" tests.34#35# All three done with VFS "oldvfs", which has iVersion==1 and so does36# not support shared memory.37# 38sqlite3 db test.db -vfs oldvfs39do_execsql_test 1.1.1 {40  PRAGMA journal_mode = WAL;41} {delete}42do_execsql_test 1.1.2 {43  PRAGMA locking_mode = EXCLUSIVE;44  PRAGMA journal_mode = WAL;45} {exclusive wal}46do_execsql_test 1.1.3 {47  CREATE TABLE t1(x, y);48  INSERT INTO t1 VALUES(1, 2);49} {}50do_test 1.1.4 {51  list [file exists test.db-shm] [file exists test.db-wal]52} {0 1}53 54do_test 1.2.1 {55  db close56  sqlite3 db test.db -vfs oldvfs57  catchsql { SELECT * FROM t1 }58} {1 {unable to open database file}}59do_test 1.2.2 {60  execsql { PRAGMA locking_mode = EXCLUSIVE }61  execsql { SELECT * FROM t1 }62} {1 2}63do_test 1.2.3 {64  list [file exists test.db-shm] [file exists test.db-wal]65} {0 1}66 67do_test 1.3.1 {68  db close69  sqlite3 db test.db -vfs oldvfs70  catchsql { INSERT INTO t1 VALUES(3, 4) }71} {1 {unable to open database file}}72do_test 1.3.2 {73  execsql { PRAGMA locking_mode = EXCLUSIVE }74  execsql { INSERT INTO t1 VALUES(3, 4) }75  execsql { SELECT * FROM t1 }76} {1 2 3 4}77do_test 1.3.3 {78  list [file exists test.db-shm] [file exists test.db-wal]79} {0 1}80 81# EVIDENCE-OF: R-31969-57825 If EXCLUSIVE locking mode is set prior to82# the first WAL-mode database access, then SQLite never attempts to call83# any of the shared-memory methods and hence no shared-memory wal-index84# is ever created.85#86db close87sqlite3 db test.db88do_execsql_test 2.1.1 {89  PRAGMA locking_mode = EXCLUSIVE;90  SELECT * FROM t1;91} {exclusive 1 2 3 4}92do_test 2.1.2 {93  list [file exists test.db-shm] [file exists test.db-wal]94} {0 1}95 96# EVIDENCE-OF: R-36328-16367 In that case, the database connection97# remains in EXCLUSIVE mode as long as the journal mode is WAL; attempts98# to change the locking mode using "PRAGMA locking_mode=NORMAL;" are99# no-ops.100#101do_execsql_test 2.2.1 {102  PRAGMA locking_mode = NORMAL;103  SELECT * FROM t1;104} {exclusive 1 2 3 4}105do_test 2.2.2 {106  sqlite3 db2 test.db107  catchsql {SELECT * FROM t1} db2108} {1 {database is locked}}109db2 close110 111# EVIDENCE-OF: R-63522-46088 The only way to change out of EXCLUSIVE112# locking mode is to first change out of WAL journal mode.113#114do_execsql_test 2.3.1 {115  PRAGMA journal_mode = DELETE;116  SELECT * FROM t1;117} {delete 1 2 3 4}118do_test 2.3.2 {119  sqlite3 db2 test.db120  catchsql {SELECT * FROM t1} db2121} {1 {database is locked}}122do_execsql_test 2.3.3 {123  PRAGMA locking_mode = NORMAL;124  SELECT * FROM t1;125} {normal 1 2 3 4}126do_test 2.3.4 {127  sqlite3 db2 test.db128  catchsql {SELECT * FROM t1} db2129} {0 {1 2 3 4}}130db2 close131db close132 133 134# EVIDENCE-OF: R-57239-11845 If NORMAL locking mode is in effect for the135# first WAL-mode database access, then the shared-memory wal-index is136# created.137#138do_test 3.0 {139  sqlite3 db test.db140  execsql { PRAGMA journal_mode = WAL }141  db close142} {}143do_test 3.1 {144  sqlite3 db test.db145  execsql { SELECT * FROM t1 }146  list [file exists test.db-shm] [file exists test.db-wal]147} {1 1}148 149# EVIDENCE-OF: R-13779-07711 As long as exactly one connection is using150# a shared-memory wal-index, the locking mode can be changed freely151# between NORMAL and EXCLUSIVE.152#153do_execsql_test 3.2.1 {154  PRAGMA locking_mode = EXCLUSIVE;155  PRAGMA locking_mode = NORMAL;156  PRAGMA locking_mode = EXCLUSIVE;157  INSERT INTO t1 VALUES(5, 6);158} {exclusive normal exclusive}159do_test 3.2.2 {160  sqlite3 db2 test.db161  catchsql { SELECT * FROM t1 } db2162} {1 {database is locked}}163 164# EVIDENCE-OF: R-10993-11647 It is only when the shared-memory wal-index165# is omitted, when the locking mode is EXCLUSIVE prior to the first166# WAL-mode database access, that the locking mode is stuck in EXCLUSIVE.167#168do_execsql_test 3.2.3 {169  PRAGMA locking_mode = NORMAL;170  SELECT * FROM t1;171} {normal 1 2 3 4 5 6}172do_test 3.2.4 {173  catchsql { SELECT * FROM t1 } db2174} {0 {1 2 3 4 5 6}}175 176do_catchsql_test 3.2.5 {177  PRAGMA locking_mode = EXCLUSIVE;178  INSERT INTO t1 VALUES(7, 8);179} {1 {database is locked}}180 181db2 close182 183# EVIDENCE-OF: R-46197-42811 This means that the underlying VFS must184# support the "version 2" shared-memory.185#186# EVIDENCE-OF: R-55316-21772 If the VFS does not support shared-memory187# methods, then the attempt to open a database that is already in WAL188# mode, or the attempt convert a database into WAL mode, will fail.189#190db close191do_test 3.4.1 {192  sqlite3 db test.db -vfs oldvfs193  catchsql { SELECT * FROM t1 }194} {1 {unable to open database file}}195db close196do_test 3.4.2 {197  forcedelete test.db2198  sqlite3 db test.db2 -vfs oldvfs199  catchsql { PRAGMA journal_mode = WAL }200} {0 delete}201db close202 203 204# EVIDENCE-OF: R-45540-25505 To prevent older versions of SQLite (prior205# to version 3.7.0, 2010-07-22) from trying to recover a WAL-mode206# database (and making matters worse) the database file format version207# numbers (bytes 18 and 19 in the database header) are increased from 1208# to 2 in WAL mode.209#210reset_db211do_execsql_test 4.1.1 { CREATE TABLE t1(x, y) }212do_test 4.1.2 { hexio_read test.db 18 2 } {0101}213do_execsql_test 4.1.3 { PRAGMA journal_mode = wAL } {wal}214do_test 4.1.4 { hexio_read test.db 18 2 } {0202}215 216 217# EVIDENCE-OF: R-02535-05811 One can explicitly change out of WAL mode218# using a pragma such as this: PRAGMA journal_mode=DELETE;219#220do_execsql_test 4.2.1 { INSERT INTO t1 VALUES(1, 1); } {}221do_test 4.2.2 { file exists test.db-wal } {1}222do_execsql_test 4.2.3 { PRAGMA journal_mode = delete } {delete}223do_test 4.2.4 { file exists test.db-wal } {0}224 225# EVIDENCE-OF: R-60175-02388 Deliberately changing out of WAL mode226# changes the database file format version numbers back to 1 so that227# older versions of SQLite can once again access the database file.228#229do_test 4.3 { hexio_read test.db 18 2 } {0101}230 231finish_test232