github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/waloverwrite.test (about)

     1  # 2010 May 5
     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  # This file implements regression tests for SQLite library.  The
    12  # focus of this file is testing the operation of the library in
    13  # "PRAGMA journal_mode=WAL" mode.
    14  #
    15  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  source $testdir/wal_common.tcl
    19  set testprefix waloverwrite
    20  
    21  ifcapable !wal {finish_test ; return }
    22  
    23  # Simple test:
    24  #
    25  # Test cases *.1 - *.6:
    26  #
    27  #   + Create a database of blobs roughly 50 pages in size.
    28  #
    29  #   + Set the db cache size to something much smaller than this (5 pages)
    30  #
    31  #   + Within a transaction, loop through the set of blobs 5 times. Update
    32  #      each blob as it is visited.
    33  #
    34  #   + Test that the wal file is roughly 50 pages in size - even though many
    35  #      database pages have been written to it multiple times.
    36  #
    37  #   + Take a copy of the database and wal file. Test that recovery can
    38  #     be run on it.
    39  #
    40  # Test cases *.7 - *.9:
    41  #
    42  #   + Same thing, but before committing the statement transaction open
    43  #     a SAVEPOINT, update the blobs another 5 times, then roll it back.
    44  #
    45  #   + Check that if recovery is run on the resulting wal file, the rolled
    46  #     back changes from within the SAVEPOINT are not present in the db.
    47  #
    48  # The above is run twice - once where the wal file is empty at the start of
    49  # step 3 (tn==1) and once where it already contains a transaction (tn==2).
    50  #
    51  foreach {tn xtra} {
    52    1 {}
    53    2 { UPDATE t1 SET y = randomblob(799) WHERE x=4 }
    54  } {
    55    reset_db
    56    do_execsql_test 1.$tn.0 {
    57      CREATE TABLE t1(x, y);
    58      CREATE TABLE t2(x, y);
    59      CREATE INDEX i1y ON t1(y);
    60    
    61      WITH cnt(i) AS (
    62        SELECT 1 UNION ALL SELECT i+1 FROM cnt WHERE i<20
    63      )
    64      INSERT INTO t1 SELECT i, randomblob(800) FROM cnt;
    65    } {}
    66    
    67    do_test 1.$tn.1 {
    68      set nPg [db one { PRAGMA page_count } ]
    69      expr $nPg>40 && $nPg<50
    70    } {1}
    71    
    72    do_test 1.$tn.2 {
    73      db close
    74      sqlite3 db test.db
    75    
    76      execsql {PRAGMA journal_mode = wal}
    77      execsql {PRAGMA cache_size = 5}
    78      execsql $xtra
    79    
    80      db transaction {
    81        for {set i 0} {$i < 5} {incr i} {
    82          foreach x [db eval {SELECT x FROM t1}] {
    83            execsql { UPDATE t1 SET y = randomblob(799) WHERE x=$x }
    84          }
    85        }
    86      }
    87    
    88      set nPg [wal_frame_count test.db-wal 1024]
    89      expr $nPg>40 && $nPg<60
    90    } {1}
    91    
    92    do_execsql_test 1.$tn.3 { PRAGMA integrity_check } ok
    93    
    94    do_test 1.$tn.4 {
    95      forcedelete test.db2 test.db2-wal
    96      forcecopy test.db test.db2
    97      sqlite3 db2 test.db2
    98      execsql { SELECT sum(length(y)) FROM t1 } db2
    99    } [expr 20*800]
   100    
   101    do_test 1.$tn.5 {
   102      db2 close
   103      forcecopy test.db test.db2
   104      forcecopy test.db-wal test.db2-wal
   105      sqlite3 db2 test.db2
   106      execsql { SELECT sum(length(y)) FROM t1 } db2
   107    } [expr 20*799]
   108    
   109    do_test 1.$tn.6 {
   110      execsql { PRAGMA integrity_check } db2
   111    } ok
   112    db2 close
   113  
   114    do_test 1.$tn.7 {
   115      execsql { PRAGMA wal_checkpoint }
   116      db transaction {
   117        for {set i 0} {$i < 1} {incr i} {
   118          foreach x [db eval {SELECT x FROM t1}] {
   119            execsql { UPDATE t1 SET y = randomblob(798) WHERE x=$x }
   120          }
   121        }
   122  
   123        execsql {
   124          WITH cnt(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM cnt WHERE i<20)
   125          INSERT INTO t2 SELECT i, randomblob(800) FROM cnt;
   126        }
   127  
   128        execsql {SAVEPOINT abc}
   129        for {set i 0} {$i < 5} {incr i} {
   130          foreach x [db eval {SELECT x FROM t1}] {
   131            execsql { UPDATE t1 SET y = randomblob(797) WHERE x=$x }
   132          }
   133        }
   134        execsql {ROLLBACK TO abc}
   135  
   136      }
   137  
   138      set nPg [wal_frame_count test.db-wal 1024]
   139      expr $nPg>55 && $nPg<75
   140    } {1}
   141  
   142    do_test 1.$tn.8 {
   143      forcedelete test.db2 test.db2-wal
   144      forcecopy test.db test.db2
   145      sqlite3 db2 test.db2
   146      execsql { SELECT sum(length(y)) FROM t1 } db2
   147    } [expr 20*799]
   148  
   149    do_test 1.$tn.9 {
   150      db2 close
   151      forcecopy test.db-wal test.db2-wal
   152      sqlite3 db2 test.db2
   153      execsql { SELECT sum(length(y)) FROM t1 } db2
   154    } [expr 20*798]
   155  
   156    do_test 1.$tn.10 {
   157      execsql { PRAGMA integrity_check } db2
   158    } ok
   159    db2 close
   160  }
   161  
   162  finish_test