wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2007 March 19
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 changing the database page size using a
13 # VACUUM statement.
14 #
15 # $Id: vacuum3.test,v 1.9 2008/08/26 21:07:27 drh Exp $
16  
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19  
20 # If the VACUUM statement is disabled in the current build, skip all
21 # the tests in this file.
22 #
23 ifcapable !vacuum {
24 finish_test
25 return
26 }
27  
28 # Do not use a codec for tests in this file, as it interferes
29 # with the initial size tests
30 do_not_use_codec
31  
32  
33 #-------------------------------------------------------------------
34 # Test cases vacuum3-1.* convert a simple 2-page database between a
35 # few different page sizes.
36 #
37 do_test vacuum3-1.1 {
38 execsql {
39 PRAGMA auto_vacuum=OFF;
40 PRAGMA page_size = 1024;
41 CREATE TABLE t1(a, b, c);
42 INSERT INTO t1 VALUES(1, 2, 3);
43 }
44 } {}
45 do_test vacuum3-1.2 {
46 execsql { PRAGMA page_size }
47 } {1024}
48 do_test vacuum3-1.3 {
49 file size test.db
50 } {2048}
51  
52 set I 4
53 foreach {request actual database} [list \
54 2048 2048 4096 \
55 1024 1024 2048 \
56 1170 1024 2048 \
57 256 1024 2048 \
58 512 512 1024 \
59 4096 4096 8192 \
60 1024 1024 2048 \
61 ] {
62 do_test vacuum3-1.$I.1 {
63 execsql "
64 PRAGMA page_size = $request;
65 VACUUM;
66 "
67 execsql { PRAGMA page_size }
68 } $actual
69 do_test vacuum3-1.$I.2 {
70 file size test.db
71 } $database
72 do_test vacuum3-1.$I.3 {
73 execsql { SELECT * FROM t1 }
74 } {1 2 3}
75 integrity_check vacuum3-1.$I.4
76  
77 incr I
78 }
79  
80 #-------------------------------------------------------------------
81 # Test cases vacuum3-2.* convert a simple 3-page database between a
82 # few different page sizes.
83 #
84 do_test vacuum3-2.1 {
85 execsql {
86 PRAGMA page_size = 1024;
87 VACUUM;
88 ALTER TABLE t1 ADD COLUMN d;
89 UPDATE t1 SET d = randomblob(1000);
90 }
91 file size test.db
92 } {3072}
93 do_test vacuum3-2.2 {
94 execsql { PRAGMA page_size }
95 } {1024}
96 do_test vacuum3-2.3 {
97 set blob [db one {select d from t1}]
98 string length $blob
99 } {1000}
100  
101 set I 4
102 foreach {request actual database} [list \
103 2048 2048 4096 \
104 1024 1024 3072 \
105 1170 1024 3072 \
106 256 1024 3072 \
107 512 512 2048 \
108 4096 4096 8192 \
109 1024 1024 3072 \
110 ] {
111 do_test vacuum3-2.$I.1 {
112 execsql "
113 PRAGMA page_size = $request;
114 VACUUM;
115 "
116 execsql { PRAGMA page_size }
117 } $actual
118 do_test vacuum3-2.$I.2 {
119 file size test.db
120 } $database
121 do_test vacuum3-2.$I.3 {
122 execsql { SELECT * FROM t1 }
123 } [list 1 2 3 $blob]
124 integrity_check vacuum3-1.$I.4
125  
126 incr I
127 }
128  
129 #-------------------------------------------------------------------
130 # Test cases vacuum3-3.* converts a database large enough to include
131 # the locking page (in a test environment) between few different
132 # page sizes.
133 #
134 proc signature {} {
135 return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc}]
136 }
137 do_test vacuum3-3.1 {
138 execsql "
139 PRAGMA page_size = 1024;
140 BEGIN;
141 CREATE TABLE abc(a PRIMARY KEY, b, c);
142 INSERT INTO abc VALUES(randomblob(100), randomblob(200), randomblob(1000));
143 INSERT INTO abc
144 SELECT randomblob(1000), randomblob(200), randomblob(100)
145 FROM abc;
146 INSERT INTO abc
147 SELECT randomblob(100), randomblob(200), randomblob(1000)
148 FROM abc;
149 INSERT INTO abc
150 SELECT randomblob(100), randomblob(200), randomblob(1000)
151 FROM abc;
152 INSERT INTO abc
153 SELECT randomblob(100), randomblob(200), randomblob(1000)
154 FROM abc;
155 INSERT INTO abc
156 SELECT randomblob(100), randomblob(200), randomblob(1000)
157 FROM abc;
158 INSERT INTO abc
159 SELECT randomblob(25), randomblob(45), randomblob(9456)
160 FROM abc;
161 INSERT INTO abc
162 SELECT randomblob(100), randomblob(200), randomblob(1000)
163 FROM abc;
164 INSERT INTO abc
165 SELECT randomblob(25), randomblob(45), randomblob(9456)
166 FROM abc;
167 COMMIT;
168 "
169 } {}
170 do_test vacuum3-3.2 {
171 execsql { PRAGMA page_size }
172 } {1024}
173  
174 set ::sig [signature]
175  
176 set I 3
177 foreach {request actual} [list \
178 2048 2048 \
179 1024 1024 \
180 1170 1024 \
181 256 1024 \
182 512 512 \
183 4096 4096 \
184 1024 1024 \
185 ] {
186 do_test vacuum3-3.$I.1 {
187 execsql "
188 PRAGMA page_size = $request;
189 VACUUM;
190 "
191 execsql { PRAGMA page_size }
192 } $actual
193 do_test vacuum3-3.$I.2 {
194 signature
195 } $::sig
196 integrity_check vacuum3-3.$I.3
197  
198 incr I
199 }
200  
201 do_test vacuum3-4.1 {
202 db close
203 file delete test.db
204 sqlite3 db test.db
205 execsql {
206 PRAGMA page_size=1024;
207 CREATE TABLE abc(a, b, c);
208 INSERT INTO abc VALUES(1, 2, 3);
209 INSERT INTO abc VALUES(4, 5, 6);
210 }
211 execsql { SELECT * FROM abc }
212 } {1 2 3 4 5 6}
213 do_test vacuum3-4.2 {
214 sqlite3 db2 test.db
215 execsql { SELECT * FROM abc } db2
216 } {1 2 3 4 5 6}
217 do_test vacuum3-4.3 {
218 execsql {
219 PRAGMA page_size = 2048;
220 VACUUM;
221 }
222 execsql { SELECT * FROM abc }
223 } {1 2 3 4 5 6}
224 do_test vacuum3-4.4 {
225 execsql { SELECT * FROM abc } db2
226 } {1 2 3 4 5 6}
227 do_test vacuum3-4.5 {
228 execsql {
229 PRAGMA page_size=16384;
230 VACUUM;
231 } db2
232 execsql { SELECT * FROM abc } db2
233 } {1 2 3 4 5 6}
234 do_test vacuum3-4.6 {
235 execsql {
236 PRAGMA page_size=1024;
237 VACUUM;
238 }
239 execsql { SELECT * FROM abc } db2
240 } {1 2 3 4 5 6}
241  
242 # Unable to change the page-size of an in-memory using vacuum.
243 db2 close
244 sqlite3 db2 :memory:
245 do_test vacuum3-5.1 {
246 db2 eval {
247 CREATE TABLE t1(x);
248 INSERT INTO t1 VALUES(1234);
249 PRAGMA page_size=4096;
250 VACUUM;
251 SELECT * FROM t1;
252 }
253 } {1234}
254 do_test vacuum3-5.2 {
255 db2 eval {
256 PRAGMA page_size
257 }
258 } {1024}
259  
260 set create_database_sql {
261 BEGIN;
262 CREATE TABLE t1(a, b, c);
263 INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50));
264 INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1;
265 INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1;
266 INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1;
267 INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1;
268 INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1;
269 INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1;
270 INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1;
271 INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600));
272 CREATE TABLE t2 AS SELECT * FROM t1;
273 CREATE TABLE t3 AS SELECT * FROM t1;
274 COMMIT;
275 DROP TABLE t2;
276 }
277  
278 do_ioerr_test vacuum3-ioerr-1 -cksum true -sqlprep "
279 PRAGMA page_size = 1024;
280 $create_database_sql
281 " -sqlbody {
282 PRAGMA page_size = 4096;
283 VACUUM;
284 }
285 do_ioerr_test vacuum3-ioerr-2 -cksum true -sqlprep "
286 PRAGMA page_size = 2048;
287 $create_database_sql
288 " -sqlbody {
289 PRAGMA page_size = 512;
290 VACUUM;
291 }
292  
293 ifcapable autovacuum {
294 do_ioerr_test vacuum3-ioerr-3 -cksum true -sqlprep "
295 PRAGMA auto_vacuum = 0;
296 $create_database_sql
297 " -sqlbody {
298 PRAGMA auto_vacuum = 1;
299 VACUUM;
300 }
301 do_ioerr_test vacuum3-ioerr-4 -cksum true -sqlprep "
302 PRAGMA auto_vacuum = 1;
303 $create_database_sql
304 " -sqlbody {
305 PRAGMA auto_vacuum = 0;
306 VACUUM;
307 }
308 }
309  
310 source $testdir/malloc_common.tcl
311 if {$MEMDEBUG} {
312 do_malloc_test vacuum3-malloc-1 -sqlprep {
313 PRAGMA page_size = 2048;
314 BEGIN;
315 CREATE TABLE t1(a, b, c);
316 INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50));
317 INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1;
318 INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1;
319 INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1;
320 INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1;
321 INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1;
322 INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1;
323 INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1;
324 INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600));
325 CREATE TABLE t2 AS SELECT * FROM t1;
326 CREATE TABLE t3 AS SELECT * FROM t1;
327 COMMIT;
328 DROP TABLE t2;
329 } -sqlbody {
330 PRAGMA page_size = 512;
331 VACUUM;
332 }
333 do_malloc_test vacuum3-malloc-2 -sqlprep {
334 PRAGMA encoding=UTF16;
335 CREATE TABLE t1(a, b, c);
336 INSERT INTO t1 VALUES(1, 2, 3);
337 CREATE TABLE t2(x,y,z);
338 INSERT INTO t2 SELECT * FROM t1;
339 } -sqlbody {
340 VACUUM;
341 }
342 }
343  
344 finish_test