wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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