wasCSharpSQLite – Blame information for rev 7
?pathlinks?
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 |