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