1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/vacuum.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,340 @@
1.4 +# 2001 September 15
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 testing the VACUUM statement.
1.16 +#
1.17 +# $Id: vacuum.test,v 1.41 2008/04/15 02:36:34 drh Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# If the VACUUM statement is disabled in the current build, skip all
1.23 +# the tests in this file.
1.24 +#
1.25 +ifcapable {!vacuum} {
1.26 + omit_test vacuum.test {Compiled with SQLITE_OMIT_VACUUM}
1.27 + finish_test
1.28 + return
1.29 +}
1.30 +if $AUTOVACUUM {
1.31 + omit_test vacuum.test {Auto-vacuum is enabled}
1.32 + finish_test
1.33 + return
1.34 +}
1.35 +
1.36 +set fcnt 1
1.37 +do_test vacuum-1.1 {
1.38 + execsql {
1.39 + BEGIN;
1.40 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
1.41 + INSERT INTO t1 VALUES(NULL,randstr(10,100),randstr(5,50));
1.42 + INSERT INTO t1 VALUES(123456,randstr(10,100),randstr(5,50));
1.43 + INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
1.44 + INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
1.45 + INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
1.46 + INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
1.47 + INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
1.48 + INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
1.49 + INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
1.50 + CREATE INDEX i1 ON t1(b,c);
1.51 + CREATE UNIQUE INDEX i2 ON t1(c,a);
1.52 + CREATE TABLE t2 AS SELECT * FROM t1;
1.53 + COMMIT;
1.54 + DROP TABLE t2;
1.55 + }
1.56 + set ::size1 [file size test.db]
1.57 + set ::cksum [cksum]
1.58 + expr {$::cksum!=""}
1.59 +} {1}
1.60 +do_test vacuum-1.2 {
1.61 + execsql {
1.62 + VACUUM;
1.63 + }
1.64 + cksum
1.65 +} $cksum
1.66 +ifcapable vacuum {
1.67 + do_test vacuum-1.3 {
1.68 + expr {[file size test.db]<$::size1}
1.69 + } {1}
1.70 +}
1.71 +do_test vacuum-1.4 {
1.72 + set sql_script {
1.73 + BEGIN;
1.74 + CREATE TABLE t2 AS SELECT * FROM t1;
1.75 + CREATE TABLE t3 AS SELECT * FROM t1;
1.76 + CREATE VIEW v1 AS SELECT b, c FROM t3;
1.77 + CREATE TRIGGER r1 AFTER DELETE ON t2 BEGIN SELECT 1; END;
1.78 + COMMIT;
1.79 + DROP TABLE t2;
1.80 + }
1.81 + # If the library was compiled to omit view support, comment out the
1.82 + # create view in the script $sql_script before executing it. Similarly,
1.83 + # if triggers are not supported, comment out the trigger definition.
1.84 + ifcapable !view {
1.85 + regsub {CREATE VIEW} $sql_script {-- CREATE VIEW} sql_script
1.86 + }
1.87 + ifcapable !trigger {
1.88 + regsub {CREATE TRIGGER} $sql_script {-- CREATE TRIGGER} sql_script
1.89 + }
1.90 + execsql $sql_script
1.91 + set ::size1 [file size test.db]
1.92 + set ::cksum [cksum]
1.93 + expr {$::cksum!=""}
1.94 +} {1}
1.95 +do_test vacuum-1.5 {
1.96 + execsql {
1.97 + VACUUM;
1.98 + }
1.99 + cksum
1.100 +} $cksum
1.101 +
1.102 +ifcapable vacuum {
1.103 + do_test vacuum-1.6 {
1.104 + expr {[file size test.db]<$::size1}
1.105 + } {1}
1.106 +}
1.107 +ifcapable vacuum {
1.108 + do_test vacuum-2.1 {
1.109 + catchsql {
1.110 + BEGIN;
1.111 + VACUUM;
1.112 + COMMIT;
1.113 + }
1.114 + } {1 {cannot VACUUM from within a transaction}}
1.115 + catch {db eval COMMIT}
1.116 +}
1.117 +do_test vacuum-2.2 {
1.118 + sqlite3 db2 test.db
1.119 + execsql {
1.120 + BEGIN;
1.121 + CREATE TABLE t4 AS SELECT * FROM t1;
1.122 + CREATE TABLE t5 AS SELECT * FROM t1;
1.123 + COMMIT;
1.124 + DROP TABLE t4;
1.125 + DROP TABLE t5;
1.126 + } db2
1.127 + set ::cksum [cksum db2]
1.128 + catchsql {
1.129 + VACUUM
1.130 + }
1.131 +} {0 {}}
1.132 +do_test vacuum-2.3 {
1.133 + cksum
1.134 +} $cksum
1.135 +do_test vacuum-2.4 {
1.136 + catch {db2 eval {SELECT count(*) FROM sqlite_master}}
1.137 + cksum db2
1.138 +} $cksum
1.139 +
1.140 +# Make sure the schema cookie is incremented by vacuum.
1.141 +#
1.142 +do_test vacuum-2.5 {
1.143 + execsql {
1.144 + BEGIN;
1.145 + CREATE TABLE t6 AS SELECT * FROM t1;
1.146 + CREATE TABLE t7 AS SELECT * FROM t1;
1.147 + COMMIT;
1.148 + }
1.149 + sqlite3 db3 test.db
1.150 + execsql {
1.151 + -- The "SELECT * FROM sqlite_master" statement ensures that this test
1.152 + -- works when shared-cache is enabled. If shared-cache is enabled, then
1.153 + -- db3 shares a cache with db2 (but not db - it was opened as
1.154 + -- "./test.db").
1.155 + SELECT * FROM sqlite_master;
1.156 + SELECT * FROM t7 LIMIT 1
1.157 + } db3
1.158 + execsql {
1.159 + VACUUM;
1.160 + }
1.161 + execsql {
1.162 + INSERT INTO t7 VALUES(1234567890,'hello','world');
1.163 + } db3
1.164 + execsql {
1.165 + SELECT * FROM t7 WHERE a=1234567890
1.166 + }
1.167 +} {1234567890 hello world}
1.168 +integrity_check vacuum-2.6
1.169 +do_test vacuum-2.7 {
1.170 + execsql {
1.171 + SELECT * FROM t7 WHERE a=1234567890
1.172 + } db3
1.173 +} {1234567890 hello world}
1.174 +do_test vacuum-2.8 {
1.175 + execsql {
1.176 + INSERT INTO t7 SELECT * FROM t6;
1.177 + SELECT count(*) FROM t7;
1.178 + }
1.179 +} 513
1.180 +integrity_check vacuum-2.9
1.181 +do_test vacuum-2.10 {
1.182 + execsql {
1.183 + DELETE FROM t7;
1.184 + SELECT count(*) FROM t7;
1.185 + } db3
1.186 +} 0
1.187 +integrity_check vacuum-2.11
1.188 +db3 close
1.189 +
1.190 +
1.191 +# Ticket #427. Make sure VACUUM works when the EMPTY_RESULT_CALLBACKS
1.192 +# pragma is turned on.
1.193 +#
1.194 +do_test vacuum-3.1 {
1.195 + db close
1.196 + db2 close
1.197 + file delete test.db
1.198 + sqlite3 db test.db
1.199 + execsql {
1.200 + PRAGMA empty_result_callbacks=on;
1.201 + VACUUM;
1.202 + }
1.203 +} {}
1.204 +
1.205 +# Ticket #464. Make sure VACUUM works with the sqlite3_prepare() API.
1.206 +#
1.207 +do_test vacuum-4.1 {
1.208 + db close
1.209 + sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
1.210 + set VM [sqlite3_prepare $DB {VACUUM} -1 TAIL]
1.211 + sqlite3_step $VM
1.212 +} {SQLITE_DONE}
1.213 +do_test vacuum-4.2 {
1.214 + sqlite3_finalize $VM
1.215 +} SQLITE_OK
1.216 +
1.217 +# Ticket #515. VACUUM after deleting and recreating the table that
1.218 +# a view refers to. Omit this test if the library is not view-enabled.
1.219 +#
1.220 +ifcapable view {
1.221 +do_test vacuum-5.1 {
1.222 + db close
1.223 + file delete -force test.db
1.224 + sqlite3 db test.db
1.225 + catchsql {
1.226 + CREATE TABLE Test (TestID int primary key);
1.227 + INSERT INTO Test VALUES (NULL);
1.228 + CREATE VIEW viewTest AS SELECT * FROM Test;
1.229 +
1.230 + BEGIN;
1.231 + CREATE TABLE tempTest (TestID int primary key, Test2 int NULL);
1.232 + INSERT INTO tempTest SELECT TestID, 1 FROM Test;
1.233 + DROP TABLE Test;
1.234 + CREATE TABLE Test(TestID int primary key, Test2 int NULL);
1.235 + INSERT INTO Test SELECT * FROM tempTest;
1.236 + DROP TABLE tempTest;
1.237 + COMMIT;
1.238 + VACUUM;
1.239 + }
1.240 +} {0 {}}
1.241 +do_test vacuum-5.2 {
1.242 + catchsql {
1.243 + VACUUM;
1.244 + }
1.245 +} {0 {}}
1.246 +} ;# ifcapable view
1.247 +
1.248 +# Ensure vacuum works with complicated tables names.
1.249 +do_test vacuum-6.1 {
1.250 + execsql {
1.251 + CREATE TABLE "abc abc"(a, b, c);
1.252 + INSERT INTO "abc abc" VALUES(1, 2, 3);
1.253 + VACUUM;
1.254 + }
1.255 +} {}
1.256 +do_test vacuum-6.2 {
1.257 + execsql {
1.258 + select * from "abc abc";
1.259 + }
1.260 +} {1 2 3}
1.261 +
1.262 +# Also ensure that blobs survive a vacuum.
1.263 +ifcapable {bloblit} {
1.264 + do_test vacuum-6.3 {
1.265 + execsql {
1.266 + DELETE FROM "abc abc";
1.267 + INSERT INTO "abc abc" VALUES(X'00112233', NULL, NULL);
1.268 + VACUUM;
1.269 + }
1.270 + } {}
1.271 + do_test vacuum-6.4 {
1.272 + execsql {
1.273 + select count(*) from "abc abc" WHERE a = X'00112233';
1.274 + }
1.275 + } {1}
1.276 +}
1.277 +
1.278 +# Check what happens when an in-memory database is vacuumed. The
1.279 +# [file delete] command covers us in case the library was compiled
1.280 +# without in-memory database support.
1.281 +#
1.282 +file delete -force :memory:
1.283 +do_test vacuum-7.0 {
1.284 + sqlite3 db2 :memory:
1.285 + execsql {
1.286 + CREATE TABLE t1(t);
1.287 + VACUUM;
1.288 + } db2
1.289 +} {}
1.290 +db2 close
1.291 +
1.292 +# Ticket #873. VACUUM a database that has ' in its name.
1.293 +#
1.294 +do_test vacuum-8.1 {
1.295 + file delete -force a'z.db
1.296 + file delete -force a'z.db-journal
1.297 + sqlite3 db2 a'z.db
1.298 + execsql {
1.299 + CREATE TABLE t1(t);
1.300 + VACUUM;
1.301 + } db2
1.302 +} {}
1.303 +db2 close
1.304 +
1.305 +# Ticket #1095: Vacuum a table that uses AUTOINCREMENT
1.306 +#
1.307 +ifcapable {autoinc} {
1.308 + do_test vacuum-9.1 {
1.309 + execsql {
1.310 + DROP TABLE 'abc abc';
1.311 + CREATE TABLE autoinc(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
1.312 + INSERT INTO autoinc(b) VALUES('hi');
1.313 + INSERT INTO autoinc(b) VALUES('there');
1.314 + DELETE FROM autoinc;
1.315 + }
1.316 + set ::cksum [cksum]
1.317 + expr {$::cksum!=""}
1.318 + } {1}
1.319 + do_test vacuum-9.2 {
1.320 + execsql {
1.321 + VACUUM;
1.322 + }
1.323 + cksum
1.324 + } $::cksum
1.325 + do_test vacuum-9.3 {
1.326 + execsql {
1.327 + INSERT INTO autoinc(b) VALUES('one');
1.328 + INSERT INTO autoinc(b) VALUES('two');
1.329 + }
1.330 + set ::cksum [cksum]
1.331 + expr {$::cksum!=""}
1.332 + } {1}
1.333 + do_test vacuum-9.4 {
1.334 + execsql {
1.335 + VACUUM;
1.336 + }
1.337 + cksum
1.338 + } $::cksum
1.339 +}
1.340 +
1.341 +file delete -force {a'z.db}
1.342 +
1.343 +finish_test