/ Check-in [fc6920b5]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add test file e_wal.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fc6920b5483eeeb06a474ff399a21afa51dc4859
User & Date: dan 2014-10-27 11:25:28
Context
2014-10-27
14:26
Optimizations aimed at reducing the number of memcpy() operations required by balance_nonroot(). check-in: face33be user: dan tags: trunk
11:25
Add test file e_wal.test. check-in: fc6920b5 user: dan tags: trunk
2014-10-25
13:42
Increase the resolution of the second parameter to the likelihood() SQL function (the probability value) so that it can handle probabilities as small as 0.00000001. Formerly, it ran out of precision at 0.001. check-in: 0f08924f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added test/e_wal.test.

            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-22428-28959 To prevent older versions of SQLite from
          204  +# trying to recover a WAL-mode database (and making matters worse) the
          205  +# database file format version numbers (bytes 18 and 19 in the database
          206  +# header) are increased from 1 to 2 in WAL mode.
          207  +#
          208  +reset_db
          209  +do_execsql_test 4.1.1 { CREATE TABLE t1(x, y) }
          210  +do_test 4.1.2 { hexio_read test.db 18 2 } {0101}
          211  +do_execsql_test 4.1.3 { PRAGMA journal_mode = wAL } {wal}
          212  +do_test 4.1.4 { hexio_read test.db 18 2 } {0202}
          213  +
          214  +
          215  +# EVIDENCE-OF: R-02535-05811 One can explicitly change out of WAL mode
          216  +# using a pragma such as this: PRAGMA journal_mode=DELETE;
          217  +#
          218  +do_execsql_test 4.2.1 { INSERT INTO t1 VALUES(1, 1); } {}
          219  +do_test 4.2.2 { file exists test.db-wal } {1}
          220  +do_execsql_test 4.2.3 { PRAGMA journal_mode = delete } {delete}
          221  +do_test 4.2.4 { file exists test.db-wal } {0}
          222  +
          223  +# EVIDENCE-OF: R-60175-02388 Deliberately changing out of WAL mode
          224  +# changes the database file format version numbers back to 1 so that
          225  +# older versions of SQLite can once again access the database file.
          226  +#
          227  +do_test 4.3 { hexio_read test.db 18 2 } {0101}
          228  +
          229  +finish_test