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.31 2004/11/22 08:43:32 danielk1977 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 94 SELECT 1; 95 END; 96 COMMIT; 97 DROP TABLE t2; 98 } 99 # If the library was compiled to omit view support, comment out the 100 # create view in the script $sql_script before executing it. 101 ifcapable !view { 102 regsub {CREATE VIEW} $sql_script {-- CREATE VIEW} sql_script 103 } 104 execsql $sql_script 105 set ::size1 [file size test.db] 106 set ::cksum [cksum] 107 expr {$::cksum!=""} 108} {1} 109do_test vacuum-1.5 { 110 execsql { 111 VACUUM; 112 } 113 cksum 114} $cksum 115 116ifcapable vacuum { 117 do_test vacuum-1.6 { 118 expr {[file size test.db]<$::size1} 119 } {1} 120} 121ifcapable vacuum { 122 do_test vacuum-2.1 { 123 catchsql { 124 BEGIN; 125 VACUUM; 126 COMMIT; 127 } 128 } {1 {cannot VACUUM from within a transaction}} 129 catch {db eval COMMIT} 130} 131do_test vacuum-2.2 { 132 sqlite3 db2 test.db 133 execsql { 134 BEGIN; 135 CREATE TABLE t4 AS SELECT * FROM t1; 136 CREATE TABLE t5 AS SELECT * FROM t1; 137 COMMIT; 138 DROP TABLE t4; 139 DROP TABLE t5; 140 } db2 141 set ::cksum [cksum db2] 142 catchsql { 143 VACUUM 144 } 145} {0 {}} 146do_test vacuum-2.3 { 147 cksum 148} $cksum 149do_test vacuum-2.4 { 150 catch {db2 eval {SELECT count(*) FROM sqlite_master}} 151 cksum db2 152} $cksum 153 154# Ticket #427. Make sure VACUUM works when the EMPTY_RESULT_CALLBACKS 155# pragma is turned on. 156# 157do_test vacuum-3.1 { 158 db close 159 db2 close 160 file delete test.db 161 sqlite3 db test.db 162 execsql { 163 PRAGMA empty_result_callbacks=on; 164 VACUUM; 165 } 166} {} 167 168# Ticket #464. Make sure VACUUM works with the sqlite3_prepare() API. 169# 170do_test vacuum-4.1 { 171 db close 172 set DB [sqlite3 db test.db] 173 set VM [sqlite3_prepare $DB {VACUUM} -1 TAIL] 174 sqlite3_step $VM 175} {SQLITE_DONE} 176do_test vacuum-4.2 { 177 sqlite3_finalize $VM 178} SQLITE_OK 179 180# Ticket #515. VACUUM after deleting and recreating the table that 181# a view refers to. Omit this test if the library is not view-enabled. 182# 183ifcapable view { 184do_test vacuum-5.1 { 185 db close 186 file delete -force test.db 187 sqlite3 db test.db 188 catchsql { 189 CREATE TABLE Test (TestID int primary key); 190 INSERT INTO Test VALUES (NULL); 191 CREATE VIEW viewTest AS SELECT * FROM Test; 192 193 BEGIN; 194 CREATE TEMP TABLE tempTest (TestID int primary key, Test2 int NULL); 195 INSERT INTO tempTest SELECT TestID, 1 FROM Test; 196 DROP TABLE Test; 197 CREATE TABLE Test(TestID int primary key, Test2 int NULL); 198 INSERT INTO Test SELECT * FROM tempTest; 199 COMMIT; 200 VACUUM; 201 } 202} {0 {}} 203do_test vacuum-5.2 { 204 catchsql { 205 VACUUM; 206 } 207} {0 {}} 208} ;# ifcapable view 209 210# Ensure vacuum works with complicated tables names. 211do_test vacuum-6.1 { 212 execsql { 213 CREATE TABLE "abc abc"(a, b, c); 214 INSERT INTO "abc abc" VALUES(1, 2, 3); 215 VACUUM; 216 } 217} {} 218do_test vacuum-6.2 { 219 execsql { 220 select * from "abc abc"; 221 } 222} {1 2 3} 223 224# Also ensure that blobs survive a vacuum. 225ifcapable {bloblit} { 226 do_test vacuum-6.3 { 227 execsql { 228 DELETE FROM "abc abc"; 229 INSERT INTO "abc abc" VALUES(X'00112233', NULL, NULL); 230 VACUUM; 231 } 232 } {} 233 do_test vacuum-6.4 { 234 execsql { 235 select count(*) from "abc abc" WHERE a = X'00112233'; 236 } 237 } {1} 238} 239 240# Check what happens when an in-memory database is vacuumed. The 241# [file delete] command covers us in case the library was compiled 242# without in-memory database support. 243# 244file delete -force :memory: 245do_test vacuum-7.0 { 246 sqlite3 db2 :memory: 247 execsql { 248 CREATE TABLE t1(t); 249 VACUUM; 250 } db2 251} {} 252db2 close 253 254# Ticket #873. VACUUM a database that has ' in its name. 255# 256do_test vacuum-8.1 { 257 file delete -force a'z.db 258 file delete -force a'z.db-journal 259 sqlite3 db2 a'z.db 260 execsql { 261 CREATE TABLE t1(t); 262 VACUUM; 263 } db2 264} {} 265db2 close 266 267# finish_test 268