1# 2001 September 15 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 testing the VACUUM statement. 13# 14# $Id: vacuum.test,v 1.40 2008/01/17 02:36:28 drh Exp $ 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19# If the VACUUM statement is disabled in the current build, skip all 20# the tests in this file. 21# 22ifcapable {!vacuum} { 23 finish_test 24 return 25} 26if $AUTOVACUUM { 27 finish_test 28 return 29} 30 31set fcnt 1 32do_test vacuum-1.1 { 33 execsql { 34 BEGIN; 35 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 36 INSERT INTO t1 VALUES(NULL,randstr(10,100),randstr(5,50)); 37 INSERT INTO t1 VALUES(123456,randstr(10,100),randstr(5,50)); 38 INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 39 INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 40 INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 41 INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 42 INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 43 INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 44 INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 45 CREATE INDEX i1 ON t1(b,c); 46 CREATE UNIQUE INDEX i2 ON t1(c,a); 47 CREATE TABLE t2 AS SELECT * FROM t1; 48 COMMIT; 49 DROP TABLE t2; 50 } 51 set ::size1 [file size test.db] 52 set ::cksum [cksum] 53 expr {$::cksum!=""} 54} {1} 55do_test vacuum-1.2 { 56 execsql { 57 VACUUM; 58 } 59 cksum 60} $cksum 61ifcapable vacuum { 62 do_test vacuum-1.3 { 63 expr {[file size test.db]<$::size1} 64 } {1} 65} 66do_test vacuum-1.4 { 67 set sql_script { 68 BEGIN; 69 CREATE TABLE t2 AS SELECT * FROM t1; 70 CREATE TABLE t3 AS SELECT * FROM t1; 71 CREATE VIEW v1 AS SELECT b, c FROM t3; 72 CREATE TRIGGER r1 AFTER DELETE ON t2 BEGIN SELECT 1; END; 73 COMMIT; 74 DROP TABLE t2; 75 } 76 # If the library was compiled to omit view support, comment out the 77 # create view in the script $sql_script before executing it. Similarly, 78 # if triggers are not supported, comment out the trigger definition. 79 ifcapable !view { 80 regsub {CREATE VIEW} $sql_script {-- CREATE VIEW} sql_script 81 } 82 ifcapable !trigger { 83 regsub {CREATE TRIGGER} $sql_script {-- CREATE TRIGGER} sql_script 84 } 85 execsql $sql_script 86 set ::size1 [file size test.db] 87 set ::cksum [cksum] 88 expr {$::cksum!=""} 89} {1} 90do_test vacuum-1.5 { 91 execsql { 92 VACUUM; 93 } 94 cksum 95} $cksum 96 97ifcapable vacuum { 98 do_test vacuum-1.6 { 99 expr {[file size test.db]<$::size1} 100 } {1} 101} 102ifcapable vacuum { 103 do_test vacuum-2.1 { 104 catchsql { 105 BEGIN; 106 VACUUM; 107 COMMIT; 108 } 109 } {1 {cannot VACUUM from within a transaction}} 110 catch {db eval COMMIT} 111} 112do_test vacuum-2.2 { 113 sqlite3 db2 test.db 114 execsql { 115 BEGIN; 116 CREATE TABLE t4 AS SELECT * FROM t1; 117 CREATE TABLE t5 AS SELECT * FROM t1; 118 COMMIT; 119 DROP TABLE t4; 120 DROP TABLE t5; 121 } db2 122 set ::cksum [cksum db2] 123 catchsql { 124 VACUUM 125 } 126} {0 {}} 127do_test vacuum-2.3 { 128 cksum 129} $cksum 130do_test vacuum-2.4 { 131 catch {db2 eval {SELECT count(*) FROM sqlite_master}} 132 cksum db2 133} $cksum 134 135# Make sure the schema cookie is incremented by vacuum. 136# 137do_test vacuum-2.5 { 138 execsql { 139 BEGIN; 140 CREATE TABLE t6 AS SELECT * FROM t1; 141 CREATE TABLE t7 AS SELECT * FROM t1; 142 COMMIT; 143 } 144 sqlite3 db3 test.db 145 execsql { 146 -- The "SELECT * FROM sqlite_master" statement ensures that this test 147 -- works when shared-cache is enabled. If shared-cache is enabled, then 148 -- db3 shares a cache with db2 (but not db - it was opened as 149 -- "./test.db"). 150 SELECT * FROM sqlite_master; 151 SELECT * FROM t7 LIMIT 1 152 } db3 153 execsql { 154 VACUUM; 155 } 156 execsql { 157 INSERT INTO t7 VALUES(1234567890,'hello','world'); 158 } db3 159 execsql { 160 SELECT * FROM t7 WHERE a=1234567890 161 } 162} {1234567890 hello world} 163integrity_check vacuum-2.6 164do_test vacuum-2.7 { 165 execsql { 166 SELECT * FROM t7 WHERE a=1234567890 167 } db3 168} {1234567890 hello world} 169do_test vacuum-2.8 { 170 execsql { 171 INSERT INTO t7 SELECT * FROM t6; 172 SELECT count(*) FROM t7; 173 } 174} 513 175integrity_check vacuum-2.9 176do_test vacuum-2.10 { 177 execsql { 178 DELETE FROM t7; 179 SELECT count(*) FROM t7; 180 } db3 181} 0 182integrity_check vacuum-2.11 183db3 close 184 185 186# Ticket #427. Make sure VACUUM works when the EMPTY_RESULT_CALLBACKS 187# pragma is turned on. 188# 189do_test vacuum-3.1 { 190 db close 191 db2 close 192 file delete test.db 193 sqlite3 db test.db 194 execsql { 195 PRAGMA empty_result_callbacks=on; 196 VACUUM; 197 } 198} {} 199 200# Ticket #464. Make sure VACUUM works with the sqlite3_prepare() API. 201# 202do_test vacuum-4.1 { 203 db close 204 sqlite3 db test.db; set DB [sqlite3_connection_pointer db] 205 set VM [sqlite3_prepare $DB {VACUUM} -1 TAIL] 206 sqlite3_step $VM 207} {SQLITE_DONE} 208do_test vacuum-4.2 { 209 sqlite3_finalize $VM 210} SQLITE_OK 211 212# Ticket #515. VACUUM after deleting and recreating the table that 213# a view refers to. Omit this test if the library is not view-enabled. 214# 215ifcapable view { 216do_test vacuum-5.1 { 217 db close 218 file delete -force test.db 219 sqlite3 db test.db 220 catchsql { 221 CREATE TABLE Test (TestID int primary key); 222 INSERT INTO Test VALUES (NULL); 223 CREATE VIEW viewTest AS SELECT * FROM Test; 224 225 BEGIN; 226 CREATE TABLE tempTest (TestID int primary key, Test2 int NULL); 227 INSERT INTO tempTest SELECT TestID, 1 FROM Test; 228 DROP TABLE Test; 229 CREATE TABLE Test(TestID int primary key, Test2 int NULL); 230 INSERT INTO Test SELECT * FROM tempTest; 231 DROP TABLE tempTest; 232 COMMIT; 233 VACUUM; 234 } 235} {0 {}} 236do_test vacuum-5.2 { 237 catchsql { 238 VACUUM; 239 } 240} {0 {}} 241} ;# ifcapable view 242 243# Ensure vacuum works with complicated tables names. 244do_test vacuum-6.1 { 245 execsql { 246 CREATE TABLE "abc abc"(a, b, c); 247 INSERT INTO "abc abc" VALUES(1, 2, 3); 248 VACUUM; 249 } 250} {} 251do_test vacuum-6.2 { 252 execsql { 253 select * from "abc abc"; 254 } 255} {1 2 3} 256 257# Also ensure that blobs survive a vacuum. 258ifcapable {bloblit} { 259 do_test vacuum-6.3 { 260 execsql { 261 DELETE FROM "abc abc"; 262 INSERT INTO "abc abc" VALUES(X'00112233', NULL, NULL); 263 VACUUM; 264 } 265 } {} 266 do_test vacuum-6.4 { 267 execsql { 268 select count(*) from "abc abc" WHERE a = X'00112233'; 269 } 270 } {1} 271} 272 273# Check what happens when an in-memory database is vacuumed. The 274# [file delete] command covers us in case the library was compiled 275# without in-memory database support. 276# 277file delete -force :memory: 278do_test vacuum-7.0 { 279 sqlite3 db2 :memory: 280 execsql { 281 CREATE TABLE t1(t); 282 VACUUM; 283 } db2 284} {} 285db2 close 286 287# Ticket #873. VACUUM a database that has ' in its name. 288# 289do_test vacuum-8.1 { 290 file delete -force a'z.db 291 file delete -force a'z.db-journal 292 sqlite3 db2 a'z.db 293 execsql { 294 CREATE TABLE t1(t); 295 VACUUM; 296 } db2 297} {} 298db2 close 299 300# Ticket #1095: Vacuum a table that uses AUTOINCREMENT 301# 302ifcapable {autoinc} { 303 do_test vacuum-9.1 { 304 execsql { 305 DROP TABLE 'abc abc'; 306 CREATE TABLE autoinc(a INTEGER PRIMARY KEY AUTOINCREMENT, b); 307 INSERT INTO autoinc(b) VALUES('hi'); 308 INSERT INTO autoinc(b) VALUES('there'); 309 DELETE FROM autoinc; 310 } 311 set ::cksum [cksum] 312 expr {$::cksum!=""} 313 } {1} 314 do_test vacuum-9.2 { 315 execsql { 316 VACUUM; 317 } 318 cksum 319 } $::cksum 320 do_test vacuum-9.3 { 321 execsql { 322 INSERT INTO autoinc(b) VALUES('one'); 323 INSERT INTO autoinc(b) VALUES('two'); 324 } 325 set ::cksum [cksum] 326 expr {$::cksum!=""} 327 } {1} 328 do_test vacuum-9.4 { 329 execsql { 330 VACUUM; 331 } 332 cksum 333 } $::cksum 334} 335 336file delete -force {a'z.db} 337 338finish_test 339