Debian Codegraph @

test/vacuum3.test

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