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: #
15:
16: set testdir [file dirname $argv0]
17: source $testdir/tester.tcl
18:
19: sqlite3_test_control_pending_byte 0x1000000
20:
21: proc create_db {{sql ""}} {
22: catch { db close }
23: forcedelete test.db
24: sqlite3 db test.db
25:
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;
39:
40: CREATE TABLE t2(a PRIMARY KEY, b UNIQUE);
41: INSERT INTO t2 SELECT * FROM t1;
42: }
43: }
44:
45: return [expr {[file size test.db] / 1024}]
46: }
47:
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: }
66:
67:
68: # EVIDENCE-OF: R-45173-45977 -- syntax diagram vacuum-stmt
69: #
70: do_execsql_test e_vacuum-0.1 { VACUUM } {}
71:
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"]
86:
87: do_execsql_test e_vacuum-1.1.$tn.1 {
88: DELETE FROM t1;
89: DELETE FROM t2;
90: } {}
91:
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: }
108:
109: do_test e_vacuum-1.1.$tn.5 { expr {[file size test.db] / 1024} } $sz
110: }
111:
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 {
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: } {}
133:
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
137:
138: do_execsql_test e_vacuum-1.2.3 { VACUUM } {}
139:
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: }
147:
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}
161:
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}
174:
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: }
190:
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]
202:
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' } {}
207:
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
210:
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}
231:
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}
244:
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 } {}
261:
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}}
270:
271:
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}
280:
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}
302:
303: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to e_vacuum.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test