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