wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2001 September 15 |
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 VACUUM statement. |
||
13 | # |
||
14 | # $Id: vacuum.test,v 1.43 2009/01/31 14:54:07 danielk1977 Exp $ |
||
15 | |||
16 | set testdir [file dirname $argv0] |
||
17 | source $testdir/tester.tcl |
||
18 | |||
19 | # If the VACUUM statement is disabled in the current build, skip all |
||
20 | # the tests in this file. |
||
21 | # |
||
22 | ifcapable {!vacuum} { |
||
23 | omit_test vacuum.test {Compiled with SQLITE_OMIT_VACUUM} |
||
24 | finish_test |
||
25 | return |
||
26 | } |
||
27 | if $AUTOVACUUM { |
||
28 | omit_test vacuum.test {Auto-vacuum is enabled} |
||
29 | finish_test |
||
30 | return |
||
31 | } |
||
32 | |||
33 | set fcnt 1 |
||
34 | do_test vacuum-1.1 { |
||
35 | execsql { |
||
36 | BEGIN; |
||
37 | CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); |
||
38 | INSERT INTO t1 VALUES(NULL,randstr(10,100),randstr(5,50)); |
||
39 | INSERT INTO t1 VALUES(123456,randstr(10,100),randstr(5,50)); |
||
40 | INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; |
||
41 | INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; |
||
42 | INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; |
||
43 | INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; |
||
44 | INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; |
||
45 | INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; |
||
46 | INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; |
||
47 | CREATE INDEX i1 ON t1(b,c); |
||
48 | CREATE UNIQUE INDEX i2 ON t1(c,a); |
||
49 | CREATE TABLE t2 AS SELECT * FROM t1; |
||
50 | COMMIT; |
||
51 | DROP TABLE t2; |
||
52 | } |
||
53 | set ::size1 [file size test.db] |
||
54 | set ::cksum [cksum] |
||
55 | expr {$::cksum!=""} |
||
56 | } {1} |
||
57 | |||
58 | # Create bogus application-defined functions for functions used |
||
59 | # internally by VACUUM, to ensure that VACUUM falls back |
||
60 | # to the built-in functions. |
||
61 | # |
||
62 | proc failing_app_func {args} {error "bad function"} |
||
63 | do_test vacuum-1.1b { |
||
64 | db func substr failing_app_func |
||
65 | db func like failing_app_func |
||
66 | db func quote failing_app_func |
||
67 | catchsql {SELECT substr(name,1,3) FROM sqlite_master} |
||
68 | } {1 {bad function}} |
||
69 | |||
70 | do_test vacuum-1.2 { |
||
71 | execsql { |
||
72 | VACUUM; |
||
73 | } |
||
74 | cksum |
||
75 | } $cksum |
||
76 | ifcapable vacuum { |
||
77 | do_test vacuum-1.3 { |
||
78 | expr {[file size test.db]<$::size1} |
||
79 | } {1} |
||
80 | } |
||
81 | do_test vacuum-1.4 { |
||
82 | set sql_script { |
||
83 | BEGIN; |
||
84 | CREATE TABLE t2 AS SELECT * FROM t1; |
||
85 | CREATE TABLE t3 AS SELECT * FROM t1; |
||
86 | CREATE VIEW v1 AS SELECT b, c FROM t3; |
||
87 | CREATE TRIGGER r1 AFTER DELETE ON t2 BEGIN SELECT 1; END; |
||
88 | COMMIT; |
||
89 | DROP TABLE t2; |
||
90 | } |
||
91 | # If the library was compiled to omit view support, comment out the |
||
92 | # create view in the script $sql_script before executing it. Similarly, |
||
93 | # if triggers are not supported, comment out the trigger definition. |
||
94 | ifcapable !view { |
||
95 | regsub {CREATE VIEW} $sql_script {-- CREATE VIEW} sql_script |
||
96 | } |
||
97 | ifcapable !trigger { |
||
98 | regsub {CREATE TRIGGER} $sql_script {-- CREATE TRIGGER} sql_script |
||
99 | } |
||
100 | execsql $sql_script |
||
101 | set ::size1 [file size test.db] |
||
102 | set ::cksum [cksum] |
||
103 | expr {$::cksum!=""} |
||
104 | } {1} |
||
105 | do_test vacuum-1.5 { |
||
106 | execsql { |
||
107 | VACUUM; |
||
108 | } |
||
109 | cksum |
||
110 | } $cksum |
||
111 | |||
112 | ifcapable vacuum { |
||
113 | do_test vacuum-1.6 { |
||
114 | expr {[file size test.db]<$::size1} |
||
115 | } {1} |
||
116 | } |
||
117 | ifcapable vacuum { |
||
118 | do_test vacuum-2.1.1 { |
||
119 | catchsql { |
||
120 | BEGIN; |
||
121 | VACUUM; |
||
122 | } |
||
123 | } {1 {cannot VACUUM from within a transaction}} |
||
124 | do_test vacuum-2.1.2 { |
||
125 | sqlite3_get_autocommit db |
||
126 | } {0} |
||
127 | do_test vacuum-2.1.3 { |
||
128 | db eval {COMMIT} |
||
129 | } {} |
||
130 | } |
||
131 | do_test vacuum-2.2 { |
||
132 | sqlite3 db2 test.db |
||
133 | execsql { |
||
134 | BEGIN; |
||
135 | CREATE TABLE t4 AS SELECT * FROM t1; |
||
136 | CREATE TABLE t5 AS SELECT * FROM t1; |
||
137 | COMMIT; |
||
138 | DROP TABLE t4; |
||
139 | DROP TABLE t5; |
||
140 | } db2 |
||
141 | set ::cksum [cksum db2] |
||
142 | catchsql { |
||
143 | VACUUM |
||
144 | } |
||
145 | } {0 {}} |
||
146 | do_test vacuum-2.3 { |
||
147 | cksum |
||
148 | } $cksum |
||
149 | do_test vacuum-2.4 { |
||
150 | catch {db2 eval {SELECT count(*) FROM sqlite_master}} |
||
151 | cksum db2 |
||
152 | } $cksum |
||
153 | |||
154 | # Make sure the schema cookie is incremented by vacuum. |
||
155 | # |
||
156 | do_test vacuum-2.5 { |
||
157 | execsql { |
||
158 | BEGIN; |
||
159 | CREATE TABLE t6 AS SELECT * FROM t1; |
||
160 | CREATE TABLE t7 AS SELECT * FROM t1; |
||
161 | COMMIT; |
||
162 | } |
||
163 | sqlite3 db3 test.db |
||
164 | execsql { |
||
165 | -- The "SELECT * FROM sqlite_master" statement ensures that this test |
||
166 | -- works when shared-cache is enabled. If shared-cache is enabled, then |
||
167 | -- db3 shares a cache with db2 (but not db - it was opened as |
||
168 | -- "./test.db"). |
||
169 | SELECT * FROM sqlite_master; |
||
170 | SELECT * FROM t7 LIMIT 1 |
||
171 | } db3 |
||
172 | execsql { |
||
173 | VACUUM; |
||
174 | } |
||
175 | execsql { |
||
176 | INSERT INTO t7 VALUES(1234567890,'hello','world'); |
||
177 | } db3 |
||
178 | execsql { |
||
179 | SELECT * FROM t7 WHERE a=1234567890 |
||
180 | } |
||
181 | } {1234567890 hello world} |
||
182 | integrity_check vacuum-2.6 |
||
183 | do_test vacuum-2.7 { |
||
184 | execsql { |
||
185 | SELECT * FROM t7 WHERE a=1234567890 |
||
186 | } db3 |
||
187 | } {1234567890 hello world} |
||
188 | do_test vacuum-2.8 { |
||
189 | execsql { |
||
190 | INSERT INTO t7 SELECT * FROM t6; |
||
191 | SELECT count(*) FROM t7; |
||
192 | } |
||
193 | } 513 |
||
194 | integrity_check vacuum-2.9 |
||
195 | do_test vacuum-2.10 { |
||
196 | execsql { |
||
197 | DELETE FROM t7; |
||
198 | SELECT count(*) FROM t7; |
||
199 | } db3 |
||
200 | } 0 |
||
201 | integrity_check vacuum-2.11 |
||
202 | db3 close |
||
203 | |||
204 | |||
205 | # Ticket #427. Make sure VACUUM works when the EMPTY_RESULT_CALLBACKS |
||
206 | # pragma is turned on. |
||
207 | # |
||
208 | do_test vacuum-3.1 { |
||
209 | db close |
||
210 | db2 close |
||
211 | file delete test.db |
||
212 | sqlite3 db test.db |
||
213 | execsql { |
||
214 | PRAGMA empty_result_callbacks=on; |
||
215 | VACUUM; |
||
216 | } |
||
217 | } {} |
||
218 | |||
219 | # Ticket #464. Make sure VACUUM works with the sqlite3_prepare() API. |
||
220 | # |
||
221 | do_test vacuum-4.1 { |
||
222 | db close |
||
223 | sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
||
224 | set VM [sqlite3_prepare $DB {VACUUM} -1 TAIL] |
||
225 | sqlite3_step $VM |
||
226 | } {SQLITE_DONE} |
||
227 | do_test vacuum-4.2 { |
||
228 | sqlite3_finalize $VM |
||
229 | } SQLITE_OK |
||
230 | |||
231 | # Ticket #515. VACUUM after deleting and recreating the table that |
||
232 | # a view refers to. Omit this test if the library is not view-enabled. |
||
233 | # |
||
234 | ifcapable view { |
||
235 | do_test vacuum-5.1 { |
||
236 | db close |
||
237 | file delete -force test.db |
||
238 | sqlite3 db test.db |
||
239 | catchsql { |
||
240 | CREATE TABLE Test (TestID int primary key); |
||
241 | INSERT INTO Test VALUES (NULL); |
||
242 | CREATE VIEW viewTest AS SELECT * FROM Test; |
||
243 | |||
244 | BEGIN; |
||
245 | CREATE TABLE tempTest (TestID int primary key, Test2 int NULL); |
||
246 | INSERT INTO tempTest SELECT TestID, 1 FROM Test; |
||
247 | DROP TABLE Test; |
||
248 | CREATE TABLE Test(TestID int primary key, Test2 int NULL); |
||
249 | INSERT INTO Test SELECT * FROM tempTest; |
||
250 | DROP TABLE tempTest; |
||
251 | COMMIT; |
||
252 | VACUUM; |
||
253 | } |
||
254 | } {0 {}} |
||
255 | do_test vacuum-5.2 { |
||
256 | catchsql { |
||
257 | VACUUM; |
||
258 | } |
||
259 | } {0 {}} |
||
260 | } ;# ifcapable view |
||
261 | |||
262 | # Ensure vacuum works with complicated tables names. |
||
263 | do_test vacuum-6.1 { |
||
264 | execsql { |
||
265 | CREATE TABLE "abc abc"(a, b, c); |
||
266 | INSERT INTO "abc abc" VALUES(1, 2, 3); |
||
267 | VACUUM; |
||
268 | } |
||
269 | } {} |
||
270 | do_test vacuum-6.2 { |
||
271 | execsql { |
||
272 | select * from "abc abc"; |
||
273 | } |
||
274 | } {1 2 3} |
||
275 | |||
276 | # Also ensure that blobs survive a vacuum. |
||
277 | ifcapable {bloblit} { |
||
278 | do_test vacuum-6.3 { |
||
279 | execsql { |
||
280 | DELETE FROM "abc abc"; |
||
281 | INSERT INTO "abc abc" VALUES(X'00112233', NULL, NULL); |
||
282 | VACUUM; |
||
283 | } |
||
284 | } {} |
||
285 | do_test vacuum-6.4 { |
||
286 | execsql { |
||
287 | select count(*) from "abc abc" WHERE a = X'00112233'; |
||
288 | } |
||
289 | } {1} |
||
290 | } |
||
291 | |||
292 | # Check what happens when an in-memory database is vacuumed. The |
||
293 | # [file delete] command covers us in case the library was compiled |
||
294 | # without in-memory database support. |
||
295 | # |
||
296 | file delete -force :memory: |
||
297 | do_test vacuum-7.0 { |
||
298 | sqlite3 db2 :memory: |
||
299 | execsql { |
||
300 | CREATE TABLE t1(t); |
||
301 | VACUUM; |
||
302 | } db2 |
||
303 | } {} |
||
304 | do_test vacuum-7.1 { |
||
305 | execsql { |
||
306 | CREATE TABLE t2(t); |
||
307 | CREATE TABLE t3(t); |
||
308 | DROP TABLE t2; |
||
309 | PRAGMA freelist_count; |
||
310 | } |
||
311 | } {1} |
||
312 | do_test vacuum-7.2 { |
||
313 | execsql { |
||
314 | VACUUM; |
||
315 | pragma integrity_check; |
||
316 | } db2 |
||
317 | } {ok} |
||
318 | do_test vacuum-7.3 { |
||
319 | execsql { PRAGMA freelist_count; } db2 |
||
320 | } {0} |
||
321 | ifcapable autovacuum { |
||
322 | do_test vacuum-7.4 { |
||
323 | execsql { PRAGMA auto_vacuum } db2 |
||
324 | } {0} |
||
325 | do_test vacuum-7.5 { |
||
326 | execsql { PRAGMA auto_vacuum = 1} db2 |
||
327 | execsql { PRAGMA auto_vacuum } db2 |
||
328 | } {0} |
||
329 | do_test vacuum-7.6 { |
||
330 | execsql { PRAGMA auto_vacuum = 1} db2 |
||
331 | execsql { VACUUM } db2 |
||
332 | execsql { PRAGMA auto_vacuum } db2 |
||
333 | } {1} |
||
334 | } |
||
335 | db2 close |
||
336 | |||
337 | # Ticket #873. VACUUM a database that has ' in its name. |
||
338 | # |
||
339 | do_test vacuum-8.1 { |
||
340 | file delete -force a'z.db |
||
341 | file delete -force a'z.db-journal |
||
342 | sqlite3 db2 a'z.db |
||
343 | execsql { |
||
344 | CREATE TABLE t1(t); |
||
345 | VACUUM; |
||
346 | } db2 |
||
347 | } {} |
||
348 | db2 close |
||
349 | |||
350 | # Ticket #1095: Vacuum a table that uses AUTOINCREMENT |
||
351 | # |
||
352 | ifcapable {autoinc} { |
||
353 | do_test vacuum-9.1 { |
||
354 | execsql { |
||
355 | DROP TABLE 'abc abc'; |
||
356 | CREATE TABLE autoinc(a INTEGER PRIMARY KEY AUTOINCREMENT, b); |
||
357 | INSERT INTO autoinc(b) VALUES('hi'); |
||
358 | INSERT INTO autoinc(b) VALUES('there'); |
||
359 | DELETE FROM autoinc; |
||
360 | } |
||
361 | set ::cksum [cksum] |
||
362 | expr {$::cksum!=""} |
||
363 | } {1} |
||
364 | do_test vacuum-9.2 { |
||
365 | execsql { |
||
366 | VACUUM; |
||
367 | } |
||
368 | cksum |
||
369 | } $::cksum |
||
370 | do_test vacuum-9.3 { |
||
371 | execsql { |
||
372 | INSERT INTO autoinc(b) VALUES('one'); |
||
373 | INSERT INTO autoinc(b) VALUES('two'); |
||
374 | } |
||
375 | set ::cksum [cksum] |
||
376 | expr {$::cksum!=""} |
||
377 | } {1} |
||
378 | do_test vacuum-9.4 { |
||
379 | execsql { |
||
380 | VACUUM; |
||
381 | } |
||
382 | cksum |
||
383 | } $::cksum |
||
384 | } |
||
385 | |||
386 | file delete -force {a'z.db} |
||
387 | |||
388 | finish_test |