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