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.1.1.1 2012/02/21 17:04:16 misho 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: ALTER TABLE t1 ADD COLUMN d;
85: UPDATE t1 SET d = randomblob(1000);
86: }
87: file size test.db
88: } {3072}
89: do_test vacuum3-2.2 {
90: execsql { PRAGMA page_size }
91: } {1024}
92: do_test vacuum3-2.3 {
93: set blob [db one {select d from t1}]
94: string length $blob
95: } {1000}
96:
97: set I 4
98: foreach {request actual database} [list \
99: 2048 2048 4096 \
100: 1024 1024 3072 \
101: 1170 1024 3072 \
102: 256 1024 3072 \
103: 512 512 2048 \
104: 4096 4096 8192 \
105: 1024 1024 3072 \
106: ] {
107: do_test vacuum3-2.$I.1 {
108: execsql "
109: PRAGMA page_size = $request;
110: VACUUM;
111: "
112: execsql { PRAGMA page_size }
113: } $actual
114: do_test vacuum3-2.$I.2 {
115: file size test.db
116: } $database
117: do_test vacuum3-2.$I.3 {
118: execsql { SELECT * FROM t1 }
119: } [list 1 2 3 $blob]
120: integrity_check vacuum3-1.$I.4
121:
122: incr I
123: }
124:
125: #-------------------------------------------------------------------
126: # Test cases vacuum3-3.* converts a database large enough to include
127: # the locking page (in a test environment) between few different
128: # page sizes.
129: #
130: proc signature {} {
131: return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc}]
132: }
133: do_test vacuum3-3.1 {
134: execsql "
135: PRAGMA page_size = 1024;
136: BEGIN;
137: CREATE TABLE abc(a PRIMARY KEY, b, c);
138: INSERT INTO abc VALUES(randomblob(100), randomblob(200), randomblob(1000));
139: INSERT INTO abc
140: SELECT randomblob(1000), randomblob(200), randomblob(100)
141: FROM abc;
142: INSERT INTO abc
143: SELECT randomblob(100), randomblob(200), randomblob(1000)
144: FROM abc;
145: INSERT INTO abc
146: SELECT randomblob(100), randomblob(200), randomblob(1000)
147: FROM abc;
148: INSERT INTO abc
149: SELECT randomblob(100), randomblob(200), randomblob(1000)
150: FROM abc;
151: INSERT INTO abc
152: SELECT randomblob(100), randomblob(200), randomblob(1000)
153: FROM abc;
154: INSERT INTO abc
155: SELECT randomblob(25), randomblob(45), randomblob(9456)
156: FROM abc;
157: INSERT INTO abc
158: SELECT randomblob(100), randomblob(200), randomblob(1000)
159: FROM abc;
160: INSERT INTO abc
161: SELECT randomblob(25), randomblob(45), randomblob(9456)
162: FROM abc;
163: COMMIT;
164: "
165: } {}
166: do_test vacuum3-3.2 {
167: execsql { PRAGMA page_size }
168: } {1024}
169:
170: set ::sig [signature]
171:
172: set I 3
173: foreach {request actual} [list \
174: 2048 2048 \
175: 1024 1024 \
176: 1170 1024 \
177: 256 1024 \
178: 512 512 \
179: 4096 4096 \
180: 1024 1024 \
181: ] {
182: do_test vacuum3-3.$I.1 {
183: execsql "
184: PRAGMA page_size = $request;
185: VACUUM;
186: "
187: execsql { PRAGMA page_size }
188: } $actual
189: do_test vacuum3-3.$I.2 {
190: signature
191: } $::sig
192: integrity_check vacuum3-3.$I.3
193:
194: incr I
195: }
196:
197: do_test vacuum3-4.1 {
198: db close
199: delete_file test.db
200: sqlite3 db test.db
201: execsql {
202: PRAGMA page_size=1024;
203: CREATE TABLE abc(a, b, c);
204: INSERT INTO abc VALUES(1, 2, 3);
205: INSERT INTO abc VALUES(4, 5, 6);
206: }
207: execsql { SELECT * FROM abc }
208: } {1 2 3 4 5 6}
209: do_test vacuum3-4.2 {
210: sqlite3 db2 test.db
211: execsql { SELECT * FROM abc } db2
212: } {1 2 3 4 5 6}
213: do_test vacuum3-4.3 {
214: execsql {
215: PRAGMA page_size = 2048;
216: VACUUM;
217: }
218: execsql { SELECT * FROM abc }
219: } {1 2 3 4 5 6}
220: do_test vacuum3-4.4 {
221: execsql { SELECT * FROM abc } db2
222: } {1 2 3 4 5 6}
223: do_test vacuum3-4.5 {
224: execsql {
225: PRAGMA page_size=16384;
226: VACUUM;
227: } db2
228: execsql { SELECT * FROM abc } db2
229: } {1 2 3 4 5 6}
230: do_test vacuum3-4.6 {
231: execsql {
232: PRAGMA page_size=1024;
233: VACUUM;
234: }
235: execsql { SELECT * FROM abc } db2
236: } {1 2 3 4 5 6}
237:
238: # Unable to change the page-size of an in-memory using vacuum.
239: db2 close
240: sqlite3 db2 :memory:
241: do_test vacuum3-5.1 {
242: db2 eval {
243: CREATE TABLE t1(x);
244: INSERT INTO t1 VALUES(1234);
245: PRAGMA page_size=4096;
246: VACUUM;
247: SELECT * FROM t1;
248: }
249: } {1234}
250: do_test vacuum3-5.2 {
251: db2 eval {
252: PRAGMA page_size
253: }
254: } {1024}
255:
256: set create_database_sql {
257: BEGIN;
258: CREATE TABLE t1(a, b, c);
259: INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50));
260: INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1;
261: INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1;
262: INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1;
263: INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1;
264: INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1;
265: INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1;
266: INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1;
267: INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600));
268: CREATE TABLE t2 AS SELECT * FROM t1;
269: CREATE TABLE t3 AS SELECT * FROM t1;
270: COMMIT;
271: DROP TABLE t2;
272: }
273:
274: do_ioerr_test vacuum3-ioerr-1 -cksum true -sqlprep "
275: PRAGMA page_size = 1024;
276: $create_database_sql
277: " -sqlbody {
278: PRAGMA page_size = 4096;
279: VACUUM;
280: }
281: do_ioerr_test vacuum3-ioerr-2 -cksum true -sqlprep "
282: PRAGMA page_size = 2048;
283: $create_database_sql
284: " -sqlbody {
285: PRAGMA page_size = 512;
286: VACUUM;
287: }
288:
289: ifcapable autovacuum {
290: do_ioerr_test vacuum3-ioerr-3 -cksum true -sqlprep "
291: PRAGMA auto_vacuum = 0;
292: $create_database_sql
293: " -sqlbody {
294: PRAGMA auto_vacuum = 1;
295: VACUUM;
296: }
297: do_ioerr_test vacuum3-ioerr-4 -cksum true -sqlprep "
298: PRAGMA auto_vacuum = 1;
299: $create_database_sql
300: " -sqlbody {
301: PRAGMA auto_vacuum = 0;
302: VACUUM;
303: }
304: }
305:
306: source $testdir/malloc_common.tcl
307: if {$MEMDEBUG} {
308: do_malloc_test vacuum3-malloc-1 -sqlprep {
309: PRAGMA page_size = 2048;
310: BEGIN;
311: CREATE TABLE t1(a, b, c);
312: INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50));
313: INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1;
314: INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1;
315: INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1;
316: INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1;
317: INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1;
318: INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1;
319: INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1;
320: INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600));
321: CREATE TABLE t2 AS SELECT * FROM t1;
322: CREATE TABLE t3 AS SELECT * FROM t1;
323: COMMIT;
324: DROP TABLE t2;
325: } -sqlbody {
326: PRAGMA page_size = 512;
327: VACUUM;
328: }
329: do_malloc_test vacuum3-malloc-2 -sqlprep {
330: PRAGMA encoding=UTF16;
331: CREATE TABLE t1(a, b, c);
332: INSERT INTO t1 VALUES(1, 2, 3);
333: CREATE TABLE t2(x,y,z);
334: INSERT INTO t2 SELECT * FROM t1;
335: } -sqlbody {
336: VACUUM;
337: }
338: }
339:
340: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to vacuum3.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test