blob: ff8bc5ab55f456d523f2c10fac3f4fa1677a0d63 (
plain)
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
|
