wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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