wasCSharpSQLite – Blame information for rev 1

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