• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
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
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# If the VACUUM statement is disabled in the current build, skip all
21# the tests in this file.
22#
23ifcapable !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#
33do_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} {}
41do_test vacuum3-1.2 {
42  execsql { PRAGMA page_size }
43} {1024}
44do_test vacuum3-1.3 {
45  file size test.db
46} {2048}
47
48set I 4
49foreach {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#
80do_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}
89do_test vacuum3-2.2 {
90  execsql { PRAGMA page_size }
91} {1024}
92do_test vacuum3-2.3 {
93  set blob [db one {select d from t1}]
94  string length $blob
95} {1000}
96
97set I 4
98foreach {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#
130proc signature {} {
131  return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc}]
132}
133do_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} {}
166do_test vacuum3-3.2 {
167  execsql { PRAGMA page_size }
168} {1024}
169
170set ::sig [signature]
171
172set I 3
173foreach {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
197do_test vacuum3-4.1 {
198  db close
199  file delete 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}
209do_test vacuum3-4.2 {
210  sqlite3 db2 test.db
211  execsql { SELECT * FROM abc } db2
212} {1 2 3 4 5 6}
213do_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}
220do_test vacuum3-4.4 {
221  execsql { SELECT * FROM abc } db2
222} {1 2 3 4 5 6}
223do_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}
230do_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.
239db2 close
240sqlite3 db2 :memory:
241do_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}
250do_test vacuum3-5.2 {
251  db2 eval {
252    PRAGMA page_size
253  }
254} {1024}
255
256set 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
274do_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}
281do_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
289ifcapable 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
306source $testdir/malloc_common.tcl
307if {$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
340finish_test
341