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