1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/vacuum3.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,340 @@
1.4 +# 2007 March 19
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is changing the database page size using a
1.16 +# VACUUM statement.
1.17 +#
1.18 +# $Id: vacuum3.test,v 1.9 2008/08/26 21:07:27 drh Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# If the VACUUM statement is disabled in the current build, skip all
1.24 +# the tests in this file.
1.25 +#
1.26 +ifcapable !vacuum {
1.27 + finish_test
1.28 + return
1.29 +}
1.30 +
1.31 +
1.32 +#-------------------------------------------------------------------
1.33 +# Test cases vacuum3-1.* convert a simple 2-page database between a
1.34 +# few different page sizes.
1.35 +#
1.36 +do_test vacuum3-1.1 {
1.37 + execsql {
1.38 + PRAGMA auto_vacuum=OFF;
1.39 + PRAGMA page_size = 1024;
1.40 + CREATE TABLE t1(a, b, c);
1.41 + INSERT INTO t1 VALUES(1, 2, 3);
1.42 + }
1.43 +} {}
1.44 +do_test vacuum3-1.2 {
1.45 + execsql { PRAGMA page_size }
1.46 +} {1024}
1.47 +do_test vacuum3-1.3 {
1.48 + file size test.db
1.49 +} {2048}
1.50 +
1.51 +set I 4
1.52 +foreach {request actual database} [list \
1.53 + 2048 2048 4096 \
1.54 + 1024 1024 2048 \
1.55 + 1170 1024 2048 \
1.56 + 256 1024 2048 \
1.57 + 512 512 1024 \
1.58 + 4096 4096 8192 \
1.59 + 1024 1024 2048 \
1.60 +] {
1.61 + do_test vacuum3-1.$I.1 {
1.62 + execsql "
1.63 + PRAGMA page_size = $request;
1.64 + VACUUM;
1.65 + "
1.66 + execsql { PRAGMA page_size }
1.67 + } $actual
1.68 + do_test vacuum3-1.$I.2 {
1.69 + file size test.db
1.70 + } $database
1.71 + do_test vacuum3-1.$I.3 {
1.72 + execsql { SELECT * FROM t1 }
1.73 + } {1 2 3}
1.74 + integrity_check vacuum3-1.$I.4
1.75 +
1.76 + incr I
1.77 +}
1.78 +
1.79 +#-------------------------------------------------------------------
1.80 +# Test cases vacuum3-2.* convert a simple 3-page database between a
1.81 +# few different page sizes.
1.82 +#
1.83 +do_test vacuum3-2.1 {
1.84 + execsql {
1.85 + PRAGMA page_size = 1024;
1.86 + VACUUM;
1.87 + ALTER TABLE t1 ADD COLUMN d;
1.88 + UPDATE t1 SET d = randomblob(1000);
1.89 + }
1.90 + file size test.db
1.91 +} {3072}
1.92 +do_test vacuum3-2.2 {
1.93 + execsql { PRAGMA page_size }
1.94 +} {1024}
1.95 +do_test vacuum3-2.3 {
1.96 + set blob [db one {select d from t1}]
1.97 + string length $blob
1.98 +} {1000}
1.99 +
1.100 +set I 4
1.101 +foreach {request actual database} [list \
1.102 + 2048 2048 4096 \
1.103 + 1024 1024 3072 \
1.104 + 1170 1024 3072 \
1.105 + 256 1024 3072 \
1.106 + 512 512 2048 \
1.107 + 4096 4096 8192 \
1.108 + 1024 1024 3072 \
1.109 +] {
1.110 + do_test vacuum3-2.$I.1 {
1.111 + execsql "
1.112 + PRAGMA page_size = $request;
1.113 + VACUUM;
1.114 + "
1.115 + execsql { PRAGMA page_size }
1.116 + } $actual
1.117 + do_test vacuum3-2.$I.2 {
1.118 + file size test.db
1.119 + } $database
1.120 + do_test vacuum3-2.$I.3 {
1.121 + execsql { SELECT * FROM t1 }
1.122 + } [list 1 2 3 $blob]
1.123 + integrity_check vacuum3-1.$I.4
1.124 +
1.125 + incr I
1.126 +}
1.127 +
1.128 +#-------------------------------------------------------------------
1.129 +# Test cases vacuum3-3.* converts a database large enough to include
1.130 +# the locking page (in a test environment) between few different
1.131 +# page sizes.
1.132 +#
1.133 +proc signature {} {
1.134 + return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc}]
1.135 +}
1.136 +do_test vacuum3-3.1 {
1.137 + execsql "
1.138 + PRAGMA page_size = 1024;
1.139 + BEGIN;
1.140 + CREATE TABLE abc(a PRIMARY KEY, b, c);
1.141 + INSERT INTO abc VALUES(randomblob(100), randomblob(200), randomblob(1000));
1.142 + INSERT INTO abc
1.143 + SELECT randomblob(1000), randomblob(200), randomblob(100)
1.144 + FROM abc;
1.145 + INSERT INTO abc
1.146 + SELECT randomblob(100), randomblob(200), randomblob(1000)
1.147 + FROM abc;
1.148 + INSERT INTO abc
1.149 + SELECT randomblob(100), randomblob(200), randomblob(1000)
1.150 + FROM abc;
1.151 + INSERT INTO abc
1.152 + SELECT randomblob(100), randomblob(200), randomblob(1000)
1.153 + FROM abc;
1.154 + INSERT INTO abc
1.155 + SELECT randomblob(100), randomblob(200), randomblob(1000)
1.156 + FROM abc;
1.157 + INSERT INTO abc
1.158 + SELECT randomblob(25), randomblob(45), randomblob(9456)
1.159 + FROM abc;
1.160 + INSERT INTO abc
1.161 + SELECT randomblob(100), randomblob(200), randomblob(1000)
1.162 + FROM abc;
1.163 + INSERT INTO abc
1.164 + SELECT randomblob(25), randomblob(45), randomblob(9456)
1.165 + FROM abc;
1.166 + COMMIT;
1.167 + "
1.168 +} {}
1.169 +do_test vacuum3-3.2 {
1.170 + execsql { PRAGMA page_size }
1.171 +} {1024}
1.172 +
1.173 +set ::sig [signature]
1.174 +
1.175 +set I 3
1.176 +foreach {request actual} [list \
1.177 + 2048 2048 \
1.178 + 1024 1024 \
1.179 + 1170 1024 \
1.180 + 256 1024 \
1.181 + 512 512 \
1.182 + 4096 4096 \
1.183 + 1024 1024 \
1.184 +] {
1.185 + do_test vacuum3-3.$I.1 {
1.186 + execsql "
1.187 + PRAGMA page_size = $request;
1.188 + VACUUM;
1.189 + "
1.190 + execsql { PRAGMA page_size }
1.191 + } $actual
1.192 + do_test vacuum3-3.$I.2 {
1.193 + signature
1.194 + } $::sig
1.195 + integrity_check vacuum3-3.$I.3
1.196 +
1.197 + incr I
1.198 +}
1.199 +
1.200 +do_test vacuum3-4.1 {
1.201 + db close
1.202 + file delete test.db
1.203 + sqlite3 db test.db
1.204 + execsql {
1.205 + PRAGMA page_size=1024;
1.206 + CREATE TABLE abc(a, b, c);
1.207 + INSERT INTO abc VALUES(1, 2, 3);
1.208 + INSERT INTO abc VALUES(4, 5, 6);
1.209 + }
1.210 + execsql { SELECT * FROM abc }
1.211 +} {1 2 3 4 5 6}
1.212 +do_test vacuum3-4.2 {
1.213 + sqlite3 db2 test.db
1.214 + execsql { SELECT * FROM abc } db2
1.215 +} {1 2 3 4 5 6}
1.216 +do_test vacuum3-4.3 {
1.217 + execsql {
1.218 + PRAGMA page_size = 2048;
1.219 + VACUUM;
1.220 + }
1.221 + execsql { SELECT * FROM abc }
1.222 +} {1 2 3 4 5 6}
1.223 +do_test vacuum3-4.4 {
1.224 + execsql { SELECT * FROM abc } db2
1.225 +} {1 2 3 4 5 6}
1.226 +do_test vacuum3-4.5 {
1.227 + execsql {
1.228 + PRAGMA page_size=16384;
1.229 + VACUUM;
1.230 + } db2
1.231 + execsql { SELECT * FROM abc } db2
1.232 +} {1 2 3 4 5 6}
1.233 +do_test vacuum3-4.6 {
1.234 + execsql {
1.235 + PRAGMA page_size=1024;
1.236 + VACUUM;
1.237 + }
1.238 + execsql { SELECT * FROM abc } db2
1.239 +} {1 2 3 4 5 6}
1.240 +
1.241 +# Unable to change the page-size of an in-memory using vacuum.
1.242 +db2 close
1.243 +sqlite3 db2 :memory:
1.244 +do_test vacuum3-5.1 {
1.245 + db2 eval {
1.246 + CREATE TABLE t1(x);
1.247 + INSERT INTO t1 VALUES(1234);
1.248 + PRAGMA page_size=4096;
1.249 + VACUUM;
1.250 + SELECT * FROM t1;
1.251 + }
1.252 +} {1234}
1.253 +do_test vacuum3-5.2 {
1.254 + db2 eval {
1.255 + PRAGMA page_size
1.256 + }
1.257 +} {1024}
1.258 +
1.259 +set create_database_sql {
1.260 + BEGIN;
1.261 + CREATE TABLE t1(a, b, c);
1.262 + INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50));
1.263 + INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1;
1.264 + INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1;
1.265 + INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1;
1.266 + INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1;
1.267 + INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1;
1.268 + INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1;
1.269 + INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1;
1.270 + INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600));
1.271 + CREATE TABLE t2 AS SELECT * FROM t1;
1.272 + CREATE TABLE t3 AS SELECT * FROM t1;
1.273 + COMMIT;
1.274 + DROP TABLE t2;
1.275 +}
1.276 +
1.277 +do_ioerr_test vacuum3-ioerr-1 -cksum true -sqlprep "
1.278 + PRAGMA page_size = 1024;
1.279 + $create_database_sql
1.280 +" -sqlbody {
1.281 + PRAGMA page_size = 4096;
1.282 + VACUUM;
1.283 +}
1.284 +do_ioerr_test vacuum3-ioerr-2 -cksum true -sqlprep "
1.285 + PRAGMA page_size = 2048;
1.286 + $create_database_sql
1.287 +" -sqlbody {
1.288 + PRAGMA page_size = 512;
1.289 + VACUUM;
1.290 +}
1.291 +
1.292 +ifcapable autovacuum {
1.293 + do_ioerr_test vacuum3-ioerr-3 -cksum true -sqlprep "
1.294 + PRAGMA auto_vacuum = 0;
1.295 + $create_database_sql
1.296 + " -sqlbody {
1.297 + PRAGMA auto_vacuum = 1;
1.298 + VACUUM;
1.299 + }
1.300 + do_ioerr_test vacuum3-ioerr-4 -cksum true -sqlprep "
1.301 + PRAGMA auto_vacuum = 1;
1.302 + $create_database_sql
1.303 + " -sqlbody {
1.304 + PRAGMA auto_vacuum = 0;
1.305 + VACUUM;
1.306 + }
1.307 +}
1.308 +
1.309 +source $testdir/malloc_common.tcl
1.310 +if {$MEMDEBUG} {
1.311 + do_malloc_test vacuum3-malloc-1 -sqlprep {
1.312 + PRAGMA page_size = 2048;
1.313 + BEGIN;
1.314 + CREATE TABLE t1(a, b, c);
1.315 + INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50));
1.316 + INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1;
1.317 + INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1;
1.318 + INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1;
1.319 + INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1;
1.320 + INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1;
1.321 + INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1;
1.322 + INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1;
1.323 + INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600));
1.324 + CREATE TABLE t2 AS SELECT * FROM t1;
1.325 + CREATE TABLE t3 AS SELECT * FROM t1;
1.326 + COMMIT;
1.327 + DROP TABLE t2;
1.328 + } -sqlbody {
1.329 + PRAGMA page_size = 512;
1.330 + VACUUM;
1.331 + }
1.332 + do_malloc_test vacuum3-malloc-2 -sqlprep {
1.333 + PRAGMA encoding=UTF16;
1.334 + CREATE TABLE t1(a, b, c);
1.335 + INSERT INTO t1 VALUES(1, 2, 3);
1.336 + CREATE TABLE t2(x,y,z);
1.337 + INSERT INTO t2 SELECT * FROM t1;
1.338 + } -sqlbody {
1.339 + VACUUM;
1.340 + }
1.341 +}
1.342 +
1.343 +finish_test