xref: /sqlite-3.40.0/test/waloverwrite.test (revision d6f7c979) 
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
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18source $testdir/wal_common.tcl
19set testprefix waloverwrite
20
21ifcapable !wal {finish_test ; return }
22
23# Simple test:
24#
25#   1. Create a database of blobs roughly 50 pages in size.
26#
27#   2. Set the db cache size to something much smaller than this (5 pages)
28#
29#   3. Within a transaction, loop through the set of blobs 5 times. Update
30#      each blob as it is visited.
31#
32#   4. Test that the wal file is roughly 50 pages in size - even though many
33#      database pages have been written to it multiple times.
34#
35#   5. Take a copy of the database and wal file. Test that recovery can
36#      be run on it.
37#
38# The above is run twice - once where the wal file is empty at the start of
39# step 3 (tn==1) and once where it already contains a transaction (tn==2).
40#
41foreach {tn xtra} {
42  1 {}
43  2 { UPDATE t1 SET y = randomblob(799) WHERE x=4 }
44} {
45  reset_db
46  do_execsql_test 1.$tn.0 {
47    CREATE TABLE t1(x, y);
48    CREATE INDEX i1y ON t1(y);
49
50    WITH cnt(i) AS (
51      SELECT 1 UNION ALL SELECT i+1 FROM cnt WHERE i<20
52    )
53    INSERT INTO t1 SELECT i, randomblob(800) FROM cnt;
54  } {}
55
56  do_test 1.$tn.1 {
57    set nPg [db one { PRAGMA page_count } ]
58    expr $nPg>40 && $nPg<50
59  } {1}
60
61  do_test 1.$tn.2 {
62    db close
63    sqlite3 db test.db
64
65    execsql {PRAGMA journal_mode = wal}
66    execsql {PRAGMA cache_size = 5}
67    execsql $xtra
68
69    db transaction {
70      for {set i 0} {$i < 5} {incr i} {
71        foreach x [db eval {SELECT x FROM t1}] {
72          execsql { UPDATE t1 SET y = randomblob(799) WHERE x=$x }
73        }
74      }
75    }
76
77    set nPg [wal_frame_count test.db-wal 1024]
78    expr $nPg>40 && $nPg<60
79  } {1}
80
81  do_execsql_test 1.$tn.3 { PRAGMA integrity_check } ok
82
83  do_test 1.$tn.4 {
84    forcedelete test.db2 test.db2-wal
85    forcecopy test.db test.db2
86    sqlite3 db2 test.db2
87    execsql { SELECT sum(length(y)) FROM t1 } db2
88  } [expr 20*800]
89
90  do_test 1.$tn.5 {
91    db2 close
92    forcecopy test.db test.db2
93    forcecopy test.db-wal test.db2-wal
94    sqlite3 db2 test.db2
95    execsql { SELECT sum(length(y)) FROM t1 } db2
96  } [expr 20*799]
97
98  do_test 1.$tn.6 {
99    execsql { PRAGMA integrity_check } db2
100  } ok
101}
102
103finish_test
104
105