xref: /sqlite-3.40.0/test/vacuum.test (revision cced337e) 
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.24 2004/07/26 23:07:52 drh Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19set fcnt 1
20proc cksum {{db db}} {
21  set sql "SELECT name, type, sql FROM sqlite_master ORDER BY name, type"
22  set txt [$db eval $sql]\n
23  set sql "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
24  foreach tbl [$db eval $sql] {
25    append txt [$db eval "SELECT * FROM $tbl"]\n
26  }
27  foreach prag {default_cache_size} {
28    append txt $prag-[$db eval "PRAGMA $prag"]\n
29  }
30  if 1 {
31    global fcnt
32    set fd [open dump$fcnt.txt w]
33    puts -nonewline $fd $txt
34    close $fd
35    incr fcnt
36  }
37  set cksum [string length $txt]-[md5 $txt]
38  # puts $cksum-[file size test.db]
39  return $cksum
40}
41do_test vacuum-1.1 {
42  execsql {
43    BEGIN;
44    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
45    INSERT INTO t1 VALUES(NULL,randstr(10,100),randstr(5,50));
46    INSERT INTO t1 VALUES(123456,randstr(10,100),randstr(5,50));
47    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
48    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
49    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
50    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
51    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
52    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
53    INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1;
54    CREATE INDEX i1 ON t1(b,c);
55    CREATE UNIQUE INDEX i2 ON t1(c,a);
56    CREATE TABLE t2 AS SELECT * FROM t1;
57    COMMIT;
58    DROP TABLE t2;
59  }
60  set ::size1 [file size test.db]
61  set ::cksum [cksum]
62  expr {$::cksum!=""}
63} {1}
64do_test vacuum-1.2 {
65  execsql {
66    VACUUM;
67  }
68  cksum
69} $cksum
70do_test vacuum-1.3 {
71  expr {[file size test.db]<$::size1}
72} {1}
73do_test vacuum-1.4 {
74  execsql {
75    BEGIN;
76    CREATE TABLE t2 AS SELECT * FROM t1;
77    CREATE TABLE t3 AS SELECT * FROM t1;
78    CREATE VIEW v1 AS SELECT b, c FROM t3;
79    CREATE TRIGGER r1 AFTER DELETE ON t2 BEGIN
80      SELECT 1;
81    END;
82    COMMIT;
83    DROP TABLE t2;
84  }
85  set ::size1 [file size test.db]
86  set ::cksum [cksum]
87  expr {$::cksum!=""}
88} {1}
89do_test vacuum-1.5 {
90  execsql {
91    VACUUM;
92  }
93  cksum
94} $cksum
95do_test vacuum-1.6 {
96  expr {[file size test.db]<$::size1}
97} {1}
98
99do_test vacuum-2.1 {
100  catchsql {
101    BEGIN;
102    VACUUM;
103    COMMIT;
104  }
105} {1 {cannot VACUUM from within a transaction}}
106catch {db eval COMMIT}
107do_test vacuum-2.2 {
108  sqlite3 db2 test.db
109  execsql {
110    BEGIN;
111    CREATE TABLE t4 AS SELECT * FROM t1;
112    CREATE TABLE t5 AS SELECT * FROM t1;
113    COMMIT;
114    DROP TABLE t4;
115    DROP TABLE t5;
116  } db2
117  set ::cksum [cksum db2]
118  catchsql {
119    VACUUM
120  }
121} {0 {}}
122do_test vacuum-2.3 {
123  cksum
124} $cksum
125do_test vacuum-2.4 {
126  catch {db2 eval {SELECT count(*) FROM sqlite_master}}
127  cksum db2
128} $cksum
129
130# Ticket #427.  Make sure VACUUM works when the EMPTY_RESULT_CALLBACKS
131# pragma is turned on.
132#
133do_test vacuum-3.1 {
134  db close
135  db2 close
136  file delete test.db
137  sqlite3 db test.db
138  execsql {
139    PRAGMA empty_result_callbacks=on;
140    VACUUM;
141  }
142} {}
143
144# Ticket #464.  Make sure VACUUM works with the sqlite3_prepare() API.
145#
146do_test vacuum-4.1 {
147  db close
148  set DB [sqlite3 db test.db]
149  set VM [sqlite3_prepare $DB {VACUUM} -1 TAIL]
150  sqlite3_step $VM
151} {SQLITE_DONE}
152do_test vacuum-4.2 {
153  sqlite3_finalize $VM
154} SQLITE_OK
155
156# Ticket #515.  VACUUM after deleting and recreating the table that
157# a view refers to.
158#
159do_test vacuum-5.1 {
160  db close
161  file delete -force test.db
162  sqlite3 db test.db
163  catchsql {
164    CREATE TABLE Test (TestID int primary key);
165    INSERT INTO Test VALUES (NULL);
166    CREATE VIEW viewTest AS SELECT * FROM Test;
167
168    BEGIN;
169    CREATE TEMP TABLE tempTest (TestID int primary key, Test2 int NULL);
170    INSERT INTO tempTest SELECT TestID, 1 FROM Test;
171    DROP TABLE Test;
172    CREATE TABLE Test(TestID int primary key, Test2 int NULL);
173    INSERT INTO Test SELECT * FROM tempTest;
174    COMMIT;
175    VACUUM;
176  }
177} {0 {}}
178do_test vacuum-5.2 {
179  catchsql {
180    VACUUM;
181  }
182} {0 {}}
183
184# Ensure vacuum works with complicated tables names.
185do_test vacuum-6.1 {
186  execsql {
187    CREATE TABLE "abc abc"(a, b, c);
188    INSERT INTO "abc abc" VALUES(1, 2, 3);
189    VACUUM;
190  }
191} {}
192do_test vacuum-6.2 {
193  execsql {
194    select * from "abc abc";
195  }
196} {1 2 3}
197
198# Also ensure that blobs survive a vacuum.
199do_test vacuum-6.3 {
200  execsql {
201    DELETE FROM "abc abc";
202    INSERT INTO "abc abc" VALUES(X'00112233', NULL, NULL);
203    VACUUM;
204  }
205} {}
206do_test vacuum-6.4 {
207  execsql {
208    select count(*) from "abc abc" WHERE a = X'00112233';
209  }
210} {1}
211
212# Check what happens when an in-memory database is vacuumed.
213do_test vacuum-7.0 {
214  sqlite3 db2 :memory:
215  execsql {
216    CREATE TABLE t1(t);
217    VACUUM;
218  } db2
219} {}
220db2 close
221
222# finish_test
223