| blob | 99b31aaca427dcd47d88ac040ace7a229733e52c |
1 # 2010 September 24
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 #
12 # This file implements tests to verify that the "testable statements" in
13 # the lang_vacuum.html document are correct.
14 #
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 sqlite3_test_control_pending_byte 0x1000000
21 proc create_db {{sql ""}} {
22 catch { db close }
23 forcedelete test.db
24 sqlite3 db test.db
26 db transaction {
27 execsql { PRAGMA page_size = 1024; }
28 execsql $sql
29 execsql {
30 CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
31 INSERT INTO t1 VALUES(1, randomblob(400));
32 INSERT INTO t1 SELECT a+1, randomblob(400) FROM t1;
33 INSERT INTO t1 SELECT a+2, randomblob(400) FROM t1;
34 INSERT INTO t1 SELECT a+4, randomblob(400) FROM t1;
35 INSERT INTO t1 SELECT a+8, randomblob(400) FROM t1;
36 INSERT INTO t1 SELECT a+16, randomblob(400) FROM t1;
37 INSERT INTO t1 SELECT a+32, randomblob(400) FROM t1;
38 INSERT INTO t1 SELECT a+64, randomblob(400) FROM t1;
40 CREATE TABLE t2(a PRIMARY KEY, b UNIQUE);
41 INSERT INTO t2 SELECT * FROM t1;
42 }
43 }
45 return [expr {[file size test.db] / 1024}]
46 }
48 # This proc returns the number of contiguous blocks of pages that make up
49 # the table or index named by the only argument. For example, if the table
50 # occupies database pages 3, 4, 8 and 9, then this command returns 2 (there
51 # are 2 fragments - one consisting of pages 3 and 4, the other of fragments
52 # 8 and 9).
53 #
54 proc fragment_count {name} {
55 execsql { CREATE VIRTUAL TABLE temp.stat USING dbstat }
56 set nFrag 1
57 db eval {SELECT pageno FROM stat WHERE name = 't1' ORDER BY pageno} {
58 if {[info exists prevpageno] && $prevpageno != $pageno-1} {
59 incr nFrag
60 }
61 set prevpageno $pageno
62 }
63 execsql { DROP TABLE temp.stat }
64 set nFrag
65 }
68 # -- syntax diagram vacuum-stmt
69 #
70 do_execsql_test e_vacuum-0.1 { VACUUM } {}
72 # EVIDENCE-OF: R-51469-36013 Unless SQLite is running in
73 # "auto_vacuum=FULL" mode, when a large amount of data is deleted from
74 # the database file it leaves behind empty space, or "free" database
75 # pages.
76 #
77 # EVIDENCE-OF: R-60541-63059 Running VACUUM to rebuild the database
78 # reclaims this space and reduces the size of the database file.
79 #
80 foreach {tn avmode sz} {
81 1 none 7
82 2 full 8
83 3 incremental 8
84 } {
85 set nPage [create_db "PRAGMA auto_vacuum = $avmode"]
87 do_execsql_test e_vacuum-1.1.$tn.1 {
88 DELETE FROM t1;
89 DELETE FROM t2;
90 } {}
92 if {$avmode == "full"} {
93 # This branch tests the "unless ... auto_vacuum=FULL" in the requirement
94 # above. If auto_vacuum is set to FULL, then no empty space is left in
95 # the database file.
96 do_execsql_test e_vacuum-1.1.$tn.2 {PRAGMA freelist_count} 0
97 } else {
98 set freelist [expr {$nPage - $sz}]
99 if {$avmode == "incremental"} {
100 # The page size is 1024 bytes. Therefore, assuming the database contains
101 # somewhere between 207 and 411 pages (it does), there are 2 pointer-map
102 # pages.
103 incr freelist -2
104 }
105 do_execsql_test e_vacuum-1.1.$tn.3 {PRAGMA freelist_count} $freelist
106 do_execsql_test e_vacuum-1.1.$tn.4 {VACUUM} {}
107 }
109 do_test e_vacuum-1.1.$tn.5 { expr {[file size test.db] / 1024} } $sz
110 }
112 # EVIDENCE-OF: R-50943-18433 Frequent inserts, updates, and deletes can
113 # cause the database file to become fragmented - where data for a single
114 # table or index is scattered around the database file.
115 #
116 # EVIDENCE-OF: R-05791-54928 Running VACUUM ensures that each table and
117 # index is largely stored contiguously within the database file.
118 #
119 # e_vacuum-1.2.1 - Perform many INSERT, UPDATE and DELETE ops on table t1.
120 # e_vacuum-1.2.2 - Verify that t1 and its indexes are now quite fragmented.
121 # e_vacuum-1.2.3 - Run VACUUM.
122 # e_vacuum-1.2.4 - Verify that t1 and its indexes are now much
123 # less fragmented.
124 #
125 ifcapable vtab&&compound {
126 create_db
127 register_dbstat_vtab db
128 do_execsql_test e_vacuum-1.2.1 {
129 DELETE FROM t1 WHERE a%2;
130 INSERT INTO t1 SELECT b, a FROM t2 WHERE a%2;
131 UPDATE t1 SET b=randomblob(600) WHERE (a%2)==0;
132 } {}
134 do_test e_vacuum-1.2.2.1 { expr [fragment_count t1]>100 } 1
135 do_test e_vacuum-1.2.2.2 { expr [fragment_count sqlite_autoindex_t1_1]>100 } 1
136 do_test e_vacuum-1.2.2.3 { expr [fragment_count sqlite_autoindex_t1_2]>100 } 1
138 do_execsql_test e_vacuum-1.2.3 { VACUUM } {}
140 # In practice, the tables and indexes each end up stored as two fragments -
141 # one containing the root page and another containing all other pages.
142 #
143 do_test e_vacuum-1.2.4.1 { fragment_count t1 } 2
144 do_test e_vacuum-1.2.4.2 { fragment_count sqlite_autoindex_t1_1 } 2
145 do_test e_vacuum-1.2.4.3 { fragment_count sqlite_autoindex_t1_2 } 2
146 }
148 # EVIDENCE-OF: R-20474-44465 Normally, the database page_size and
149 # whether or not the database supports auto_vacuum must be configured
150 # before the database file is actually created.
151 #
152 do_test e_vacuum-1.3.1.1 {
153 create_db "PRAGMA page_size = 1024 ; PRAGMA auto_vacuum = FULL"
154 execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
155 } {1024 1}
156 do_test e_vacuum-1.3.1.2 {
157 execsql { PRAGMA page_size = 2048 }
158 execsql { PRAGMA auto_vacuum = NONE }
159 execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
160 } {1024 1}
162 # EVIDENCE-OF: R-08570-19916 However, when not in write-ahead log mode,
163 # the page_size and/or auto_vacuum properties of an existing database
164 # may be changed by using the page_size and/or pragma auto_vacuum
165 # pragmas and then immediately VACUUMing the database.
166 #
167 do_test e_vacuum-1.3.2.1 {
168 execsql { PRAGMA journal_mode = delete }
169 execsql { PRAGMA page_size = 2048 }
170 execsql { PRAGMA auto_vacuum = NONE }
171 execsql VACUUM
172 execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
173 } {2048 0}
175 # EVIDENCE-OF: R-48521-51450 When in write-ahead log mode, only the
176 # auto_vacuum support property can be changed using VACUUM.
177 #
178 ifcapable wal {
179 do_test e_vacuum-1.3.3.1 {
180 execsql { PRAGMA journal_mode = wal }
181 execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
182 } {2048 0}
183 do_test e_vacuum-1.3.3.2 {
184 execsql { PRAGMA page_size = 1024 }
185 execsql { PRAGMA auto_vacuum = FULL }
186 execsql VACUUM
187 execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
188 } {2048 1}
189 }
191 # EVIDENCE-OF: R-38001-03952 VACUUM only works on the main database. It
192 # is not possible to VACUUM an attached database file.
193 forcedelete test.db2
194 create_db { PRAGMA auto_vacuum = NONE }
195 do_execsql_test e_vacuum-2.1.1 {
196 ATTACH 'test.db2' AS aux;
197 PRAGMA aux.page_size = 1024;
198 CREATE TABLE aux.t3 AS SELECT * FROM t1;
199 DELETE FROM t3;
200 } {}
201 set original_size [file size test.db2]
203 # Try everything we can think of to get the aux database vacuumed:
204 do_execsql_test e_vacuum-2.1.3 { VACUUM } {}
205 do_execsql_test e_vacuum-2.1.4 { VACUUM aux } {}
206 do_execsql_test e_vacuum-2.1.5 { VACUUM 'test.db2' } {}
208 # Despite our efforts, space in the aux database has not been reclaimed:
209 do_test e_vacuum-2.1.6 { expr {[file size test.db2]==$::original_size} } 1
211 # EVIDENCE-OF: R-17495-17419 The VACUUM command may change the ROWIDs of
212 # entries in any tables that do not have an explicit INTEGER PRIMARY
213 # KEY.
214 #
215 # Tests e_vacuum-3.1.1 - 3.1.2 demonstrate that rowids can change when
216 # a database is VACUUMed. Tests e_vacuum-3.1.3 - 3.1.4 show that adding
217 # an INTEGER PRIMARY KEY column to a table stops this from happening.
218 #
219 do_execsql_test e_vacuum-3.1.1 {
220 CREATE TABLE t4(x);
221 INSERT INTO t4(x) VALUES('x');
222 INSERT INTO t4(x) VALUES('y');
223 INSERT INTO t4(x) VALUES('z');
224 DELETE FROM t4 WHERE x = 'y';
225 SELECT rowid, x FROM t4;
226 } {1 x 3 z}
227 do_execsql_test e_vacuum-3.1.2 {
228 VACUUM;
229 SELECT rowid, x FROM t4;
230 } {1 x 2 z}
232 do_execsql_test e_vacuum-3.1.3 {
233 CREATE TABLE t5(x, y INTEGER PRIMARY KEY);
234 INSERT INTO t5(x) VALUES('x');
235 INSERT INTO t5(x) VALUES('y');
236 INSERT INTO t5(x) VALUES('z');
237 DELETE FROM t5 WHERE x = 'y';
238 SELECT rowid, x FROM t5;
239 } {1 x 3 z}
240 do_execsql_test e_vacuum-3.1.4 {
241 VACUUM;
242 SELECT rowid, x FROM t5;
243 } {1 x 3 z}
245 # EVIDENCE-OF: R-49563-33883 A VACUUM will fail if there is an open
246 # transaction, or if there are one or more active SQL statements when it
247 # is run.
248 #
249 do_execsql_test e_vacuum-3.2.1.1 { BEGIN } {}
250 do_catchsql_test e_vacuum-3.2.1.2 {
251 VACUUM
252 } {1 {cannot VACUUM from within a transaction}}
253 do_execsql_test e_vacuum-3.2.1.3 { COMMIT } {}
254 do_execsql_test e_vacuum-3.2.1.4 { VACUUM } {}
255 do_execsql_test e_vacuum-3.2.1.5 { SAVEPOINT x } {}
256 do_catchsql_test e_vacuum-3.2.1.6 {
257 VACUUM
258 } {1 {cannot VACUUM from within a transaction}}
259 do_execsql_test e_vacuum-3.2.1.7 { COMMIT } {}
260 do_execsql_test e_vacuum-3.2.1.8 { VACUUM } {}
262 create_db
263 do_test e_vacuum-3.2.2.1 {
264 set res ""
265 db eval { SELECT a FROM t1 } {
266 if {$a == 10} { set res [catchsql VACUUM] }
267 }
268 set res
269 } {1 {cannot VACUUM - SQL statements in progress}}
272 # EVIDENCE-OF: R-38735-12540 As of SQLite version 3.1, an alternative to
273 # using the VACUUM command to reclaim space after data has been deleted
274 # is auto-vacuum mode, enabled using the auto_vacuum pragma.
275 #
276 do_test e_vacuum-3.3.1 {
277 create_db { PRAGMA auto_vacuum = FULL }
278 execsql { PRAGMA auto_vacuum }
279 } {1}
281 # EVIDENCE-OF: R-64844-34873 When auto_vacuum is enabled for a database
282 # free pages may be reclaimed after deleting data, causing the file to
283 # shrink, without rebuilding the entire database using VACUUM.
284 #
285 do_test e_vacuum-3.3.2.1 {
286 create_db { PRAGMA auto_vacuum = FULL }
287 execsql {
288 DELETE FROM t1;
289 DELETE FROM t2;
290 }
291 expr {[file size test.db] / 1024}
292 } {8}
293 do_test e_vacuum-3.3.2.2 {
294 create_db { PRAGMA auto_vacuum = INCREMENTAL }
295 execsql {
296 DELETE FROM t1;
297 DELETE FROM t2;
298 PRAGMA incremental_vacuum;
299 }
300 expr {[file size test.db] / 1024}
301 } {8}
303 finish_test