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