wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2007 May 04 |
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 incremental vacuum feature. |
||
13 | # |
||
14 | # $Id: incrvacuum2.test,v 1.6 2009/07/25 13:42:50 danielk1977 Exp $ |
||
15 | |||
16 | set testdir [file dirname $argv0] |
||
17 | source $testdir/tester.tcl |
||
18 | |||
19 | # If this build of the library does not support auto-vacuum, omit this |
||
20 | # whole file. |
||
21 | ifcapable {!autovacuum || !pragma} { |
||
22 | finish_test |
||
23 | return |
||
24 | } |
||
25 | |||
26 | set testprefix incrvacuum2 |
||
27 | |||
28 | # Create a database in incremental vacuum mode that has many |
||
29 | # pages on the freelist. |
||
30 | # |
||
31 | do_test incrvacuum2-1.1 { |
||
32 | execsql { |
||
33 | PRAGMA page_size=1024; |
||
34 | PRAGMA auto_vacuum=incremental; |
||
35 | CREATE TABLE t1(x); |
||
36 | INSERT INTO t1 VALUES(zeroblob(30000)); |
||
37 | DELETE FROM t1; |
||
38 | } |
||
39 | file size test.db |
||
40 | } {32768} |
||
41 | |||
42 | # Vacuum off a single page. |
||
43 | # |
||
44 | do_test incrvacuum2-1.2 { |
||
45 | execsql { |
||
46 | PRAGMA incremental_vacuum(1); |
||
47 | } |
||
48 | file size test.db |
||
49 | } {31744} |
||
50 | |||
51 | # Vacuum off five pages |
||
52 | # |
||
53 | do_test incrvacuum2-1.3 { |
||
54 | execsql { |
||
55 | PRAGMA incremental_vacuum(5); |
||
56 | } |
||
57 | file size test.db |
||
58 | } {26624} |
||
59 | |||
60 | # Vacuum off all the rest |
||
61 | # |
||
62 | do_test incrvacuum2-1.4 { |
||
63 | execsql { |
||
64 | PRAGMA incremental_vacuum(1000); |
||
65 | } |
||
66 | file size test.db |
||
67 | } {3072} |
||
68 | |||
69 | # Make sure incremental vacuum works on attached databases. |
||
70 | # |
||
71 | ifcapable attach { |
||
72 | do_test incrvacuum2-2.1 { |
||
73 | file delete -force test2.db test2.db-journal |
||
74 | execsql { |
||
75 | ATTACH DATABASE 'test2.db' AS aux; |
||
76 | PRAGMA aux.auto_vacuum=incremental; |
||
77 | CREATE TABLE aux.t2(x); |
||
78 | INSERT INTO t2 VALUES(zeroblob(30000)); |
||
79 | INSERT INTO t1 SELECT * FROM t2; |
||
80 | DELETE FROM t2; |
||
81 | DELETE FROM t1; |
||
82 | } |
||
83 | list [file size test.db] [file size test2.db] |
||
84 | } {32768 32768} |
||
85 | do_test incrvacuum2-2.2 { |
||
86 | execsql { |
||
87 | PRAGMA aux.incremental_vacuum(1) |
||
88 | } |
||
89 | list [file size test.db] [file size test2.db] |
||
90 | } {32768 31744} |
||
91 | do_test incrvacuum2-2.3 { |
||
92 | execsql { |
||
93 | PRAGMA aux.incremental_vacuum(5) |
||
94 | } |
||
95 | list [file size test.db] [file size test2.db] |
||
96 | } {32768 26624} |
||
97 | do_test incrvacuum2-2.4 { |
||
98 | execsql { |
||
99 | PRAGMA main.incremental_vacuum(5) |
||
100 | } |
||
101 | list [file size test.db] [file size test2.db] |
||
102 | } {27648 26624} |
||
103 | do_test incrvacuum2-2.5 { |
||
104 | execsql { |
||
105 | PRAGMA aux.incremental_vacuum |
||
106 | } |
||
107 | list [file size test.db] [file size test2.db] |
||
108 | } {27648 3072} |
||
109 | do_test incrvacuum2-2.6 { |
||
110 | execsql { |
||
111 | PRAGMA incremental_vacuum(1) |
||
112 | } |
||
113 | list [file size test.db] [file size test2.db] |
||
114 | } {26624 3072} |
||
115 | } |
||
116 | |||
117 | do_test incrvacuum2-3.1 { |
||
118 | execsql { |
||
119 | PRAGMA auto_vacuum = 'full'; |
||
120 | BEGIN; |
||
121 | CREATE TABLE abc(a); |
||
122 | INSERT INTO abc VALUES(randstr(1500,1500)); |
||
123 | COMMIT; |
||
124 | } |
||
125 | } {} |
||
126 | do_test incrvacuum2-3.2 { |
||
127 | execsql { |
||
128 | BEGIN; |
||
129 | DELETE FROM abc; |
||
130 | PRAGMA incremental_vacuum; |
||
131 | COMMIT; |
||
132 | } |
||
133 | } {} |
||
134 | |||
135 | integrity_check incrvacuum2-3.3 |
||
136 | |||
137 | ifcapable wal { |
||
138 | # At one point, when a specific page was being extracted from the b-tree |
||
139 | # free-list (e.g. during an incremental-vacuum), all trunk pages that |
||
140 | # occurred before the specific page in the free-list trunk were being |
||
141 | # written to the journal or wal file. This is not necessary. Only the |
||
142 | # extracted page and the page that contains the pointer to it need to |
||
143 | # be journalled. |
||
144 | # |
||
145 | # This problem was fixed by [d03d63d77e] (just before 3.7.6 release). |
||
146 | # |
||
147 | # This test case builds a database containing many free pages. Then runs |
||
148 | # "PRAGMA incremental_vacuum(1)" until the db contains zero free pages. |
||
149 | # Each "PRAGMA incremental_vacuum(1)" should modify at most 4 pages. The |
||
150 | # worst case is when a trunk page is removed from the end of the db file. |
||
151 | # In this case pages written are: |
||
152 | # |
||
153 | # 1. The previous trunk page (that contains a pointer to the recycled |
||
154 | # trunk page), and |
||
155 | # 2. The leaf page transformed into a trunk page to replace the recycled |
||
156 | # page, and |
||
157 | # 3. The trunk page that contained a pointer to the leaf page used |
||
158 | # in (2), and |
||
159 | # 4. Page 1. Page 1 is always updated, even in WAL mode, since it contains |
||
160 | # the "number of free-list pages" field. |
||
161 | # |
||
162 | db close |
||
163 | forcedelete test.db |
||
164 | sqlite3 db test.db |
||
165 | |||
166 | do_execsql_test 4.1 { |
||
167 | PRAGMA page_size = 512; |
||
168 | PRAGMA auto_vacuum = 2; |
||
169 | CREATE TABLE t1(x); |
||
170 | INSERT INTO t1 VALUES(randomblob(400)); |
||
171 | INSERT INTO t1 SELECT * FROM t1; -- 2 |
||
172 | INSERT INTO t1 SELECT * FROM t1; -- 4 |
||
173 | INSERT INTO t1 SELECT * FROM t1; -- 8 |
||
174 | INSERT INTO t1 SELECT * FROM t1; -- 16 |
||
175 | INSERT INTO t1 SELECT * FROM t1; -- 32 |
||
176 | INSERT INTO t1 SELECT * FROM t1; -- 128 |
||
177 | INSERT INTO t1 SELECT * FROM t1; -- 256 |
||
178 | INSERT INTO t1 SELECT * FROM t1; -- 512 |
||
179 | INSERT INTO t1 SELECT * FROM t1; -- 1024 |
||
180 | INSERT INTO t1 SELECT * FROM t1; -- 2048 |
||
181 | INSERT INTO t1 SELECT * FROM t1; -- 4096 |
||
182 | INSERT INTO t1 SELECT * FROM t1; -- 8192 |
||
183 | DELETE FROM t1 WHERE oid>512; |
||
184 | DELETE FROM t1; |
||
185 | } |
||
186 | |||
187 | do_test 4.2 { |
||
188 | execsql { |
||
189 | PRAGMA journal_mode = WAL; |
||
190 | PRAGMA incremental_vacuum(1); |
||
191 | PRAGMA wal_checkpoint; |
||
192 | } |
||
193 | file size test.db-wal |
||
194 | } {1640} |
||
195 | |||
196 | do_test 4.3 { |
||
197 | db close |
||
198 | sqlite3 db test.db |
||
199 | set maxsz 0 |
||
200 | while {[file size test.db] > [expr 512*3]} { |
||
201 | execsql { PRAGMA journal_mode = WAL } |
||
202 | execsql { PRAGMA wal_checkpoint } |
||
203 | execsql { PRAGMA incremental_vacuum(1) } |
||
204 | set newsz [file size test.db-wal] |
||
205 | if {$newsz>$maxsz} {set maxsz $newsz} |
||
206 | } |
||
207 | set maxsz |
||
208 | } {2176} |
||
209 | } |
||
210 | |||
211 | finish_test |