1 2#pragma ident "%Z%%M% %I% %E% SMI" 3 4# 2001 September 15 5# 6# The author disclaims copyright to this source code. In place of 7# a legal notice, here is a blessing: 8# 9# May you do good and not evil. 10# May you find forgiveness for yourself and forgive others. 11# May you share freely, never taking more than you give. 12# 13#*********************************************************************** 14# This file implements regression tests for SQLite library. The 15# focus of this file is testing the VACUUM statement. 16# 17# $Id: vacuum.test,v 1.15 2004/02/14 16:31:04 drh Exp $ 18 19set testdir [file dirname $argv0] 20source $testdir/tester.tcl 21 22proc cksum {{db db}} { 23 set txt [$db eval {SELECT name, type, sql FROM sqlite_master}]\n 24 foreach tbl [$db eval {SELECT name FROM sqlite_master WHERE type='table'}] { 25 append txt [$db eval "SELECT * FROM $tbl"]\n 26 } 27 foreach prag {default_synchronous default_cache_size} { 28 append txt $prag-[$db eval "PRAGMA $prag"]\n 29 } 30 set cksum [string length $txt]-[md5 $txt] 31 # puts $cksum-[file size test.db] 32 return $cksum 33} 34do_test vacuum-1.1 { 35 execsql { 36 BEGIN; 37 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 38 INSERT INTO t1 VALUES(NULL,randstr(10,100),randstr(5,50)); 39 INSERT INTO t1 VALUES(123456,randstr(10,100),randstr(5,50)); 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 INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 46 INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 47 CREATE INDEX i1 ON t1(b,c); 48 CREATE TABLE t2 AS SELECT * FROM t1; 49 COMMIT; 50 DROP TABLE t2; 51 } 52 set ::size1 [file size test.db] 53 set ::cksum [cksum] 54 expr {$::cksum!=""} 55} {1} 56do_test vacuum-1.2 { 57 execsql { 58 VACUUM; 59 } 60 cksum 61} $cksum 62do_test vacuum-1.3 { 63 expr {[file size test.db]<$::size1} 64} {1} 65do_test vacuum-1.4 { 66 execsql { 67 BEGIN; 68 CREATE TABLE t2 AS SELECT * FROM t1; 69 CREATE TABLE t3 AS SELECT * FROM t1; 70 CREATE VIEW v1 AS SELECT b, c FROM t3; 71 CREATE TRIGGER r1 AFTER DELETE ON t2 BEGIN 72 SELECT 1; 73 END; 74 COMMIT; 75 DROP TABLE t2; 76 } 77 set ::size1 [file size test.db] 78 set ::cksum [cksum] 79 expr {$::cksum!=""} 80} {1} 81do_test vacuum-1.5 { 82 execsql { 83 VACUUM; 84 } 85 cksum 86} $cksum 87do_test vacuum-1.6 { 88 expr {[file size test.db]<$::size1} 89} {1} 90 91do_test vacuum-2.1 { 92 catchsql { 93 BEGIN; 94 VACUUM; 95 COMMIT; 96 } 97} {1 {cannot VACUUM from within a transaction}} 98catch {db eval COMMIT} 99do_test vacuum-2.2 { 100 sqlite db2 test.db 101 execsql { 102 BEGIN; 103 CREATE TABLE t4 AS SELECT * FROM t1; 104 CREATE TABLE t5 AS SELECT * FROM t1; 105 COMMIT; 106 DROP TABLE t4; 107 DROP TABLE t5; 108 } db2 109 set ::cksum [cksum db2] 110 catchsql { 111 VACUUM 112 } 113} {0 {}} 114do_test vacuum-2.3 { 115 cksum 116} $cksum 117do_test vacuum-2.4 { 118 catch {db2 eval {SELECT count(*) FROM sqlite_master}} 119 cksum db2 120} $cksum 121 122# Ticket #427. Make sure VACUUM works when the EMPTY_RESULT_CALLBACKS 123# pragma is turned on. 124# 125do_test vacuum-3.1 { 126 db close 127 db2 close 128 file delete test.db 129 sqlite db test.db 130 execsql { 131 PRAGMA empty_result_callbacks=on; 132 VACUUM; 133 } 134} {} 135 136# Ticket #464. Make sure VACUUM works with the sqlite_compile() API. 137# 138do_test vacuum-4.1 { 139 db close 140 set DB [sqlite db test.db] 141 set VM [sqlite_compile $DB {VACUUM} TAIL] 142 sqlite_step $VM N VALUES COLNAMES 143} {SQLITE_DONE} 144do_test vacuum-4.2 { 145 sqlite_finalize $VM 146} {} 147 148# Ticket #515. VACUUM after deleting and recreating the table that 149# a view refers to. 150# 151do_test vacuum-5.1 { 152 db close 153 file delete -force test.db 154 sqlite db test.db 155 catchsql { 156 CREATE TABLE Test (TestID int primary key); 157 INSERT INTO Test VALUES (NULL); 158 CREATE VIEW viewTest AS SELECT * FROM Test; 159 160 BEGIN; 161 CREATE TEMP TABLE tempTest (TestID int primary key, Test2 int NULL); 162 INSERT INTO tempTest SELECT TestID, 1 FROM Test; 163 DROP TABLE Test; 164 CREATE TABLE Test(TestID int primary key, Test2 int NULL); 165 INSERT INTO Test SELECT * FROM tempTest; 166 COMMIT; 167 VACUUM; 168 } 169} {0 {}} 170do_test vacuum-5.2 { 171 catchsql { 172 VACUUM; 173 } 174} {0 {}} 175 176# finish_test 177