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