xref: /sqlite-3.40.0/test/vacuum2.test (revision ebbf3687) 
1# 2005 February 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: vacuum2.test,v 1.10 2009/02/18 20:31:18 drh Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18set testprefix vacuum2
19
20# Do not use a codec for tests in this file, as the database file is
21# manipulated directly using tcl scripts (using the [hexio_write] command).
22#
23do_not_use_codec
24
25# If the VACUUM statement is disabled in the current build, skip all
26# the tests in this file.
27#
28ifcapable {!vacuum||!autoinc} {
29  finish_test
30  return
31}
32if $AUTOVACUUM {
33  finish_test
34  return
35}
36
37# Ticket #1121 - make sure vacuum works if all autoincrement tables
38# have been deleted.
39#
40do_test vacuum2-1.1 {
41  execsql {
42    CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
43    DROP TABLE t1;
44    VACUUM;
45  }
46} {}
47
48# Ticket #2518.  Make sure vacuum increments the change counter
49# in the database header.
50#
51do_test vacuum2-2.1 {
52  execsql {
53    CREATE TABLE t1(x);
54    CREATE TABLE t2(y);
55    INSERT INTO t1 VALUES(1);
56  }
57  hexio_get_int [hexio_read test.db 24 4]
58} [expr {[hexio_get_int [hexio_read test.db 24 4]]+3}]
59do_test vacuum2-2.2 {
60  execsql {
61    VACUUM
62  }
63  hexio_get_int [hexio_read test.db 24 4]
64} [expr {[hexio_get_int [hexio_read test.db 24 4]]+1}]
65
66############################################################################
67# Verify that we can use the auto_vacuum pragma to request a new
68# autovacuum setting, do a VACUUM, and the new setting takes effect.
69# Make sure this happens correctly even if there are multiple open
70# connections to the same database file.
71#
72sqlite3 db2 test.db
73set pageSize [db eval {pragma page_size}]
74
75# We are currently not autovacuuming so the database should be 3 pages
76# in size.  1 page for each of sqlite_master, t1, and t2.
77#
78do_test vacuum2-3.1 {
79  execsql {
80    INSERT INTO t1 VALUES('hello');
81    INSERT INTO t2 VALUES('out there');
82  }
83  expr {[file size test.db]/$pageSize}
84} {3}
85set cksum [cksum]
86do_test vacuum2-3.2 {
87  cksum db2
88} $cksum
89
90# Convert the database to an autovacuumed database.
91ifcapable autovacuum {
92  do_test vacuum2-3.3 {
93    execsql {
94      PRAGMA auto_vacuum=FULL;
95      VACUUM;
96    }
97    expr {[file size test.db]/$pageSize}
98  } {4}
99}
100do_test vacuum2-3.4 {
101  cksum db2
102} $cksum
103do_test vacuum2-3.5 {
104  cksum
105} $cksum
106do_test vacuum2-3.6 {
107  execsql {PRAGMA integrity_check} db2
108} {ok}
109do_test vacuum2-3.7 {
110  execsql {PRAGMA integrity_check} db
111} {ok}
112
113# Convert the database back to a non-autovacuumed database.
114do_test vacuum2-3.13 {
115  execsql {
116    PRAGMA auto_vacuum=NONE;
117    VACUUM;
118  }
119  expr {[file size test.db]/$pageSize}
120} {3}
121do_test vacuum2-3.14 {
122  cksum db2
123} $cksum
124do_test vacuum2-3.15 {
125  cksum
126} $cksum
127do_test vacuum2-3.16 {
128  execsql {PRAGMA integrity_check} db2
129} {ok}
130do_test vacuum2-3.17 {
131  execsql {PRAGMA integrity_check} db
132} {ok}
133
134db2 close
135
136ifcapable autovacuum {
137  do_test vacuum2-4.1 {
138    db close
139    forcedelete test.db
140    sqlite3 db test.db
141    execsql {
142      pragma auto_vacuum=1;
143      create table t(a, b);
144      insert into t values(1, 2);
145      insert into t values(1, 2);
146      pragma auto_vacuum=0;
147      vacuum;
148      pragma auto_vacuum;
149    }
150  } {0}
151  do_test vacuum2-4.2 {
152    execsql {
153      pragma auto_vacuum=1;
154      vacuum;
155      pragma auto_vacuum;
156    }
157  } {1}
158  do_test vacuum2-4.3 {
159    execsql {
160      pragma integrity_check
161    }
162  } {ok}
163  do_test vacuum2-4.4 {
164    db close
165    sqlite3 db test.db
166    execsql {
167      pragma auto_vacuum;
168    }
169  } {1}
170  do_test vacuum2-4.5 {  # Ticket #3663
171    execsql {
172      pragma auto_vacuum=2;
173      vacuum;
174      pragma auto_vacuum;
175    }
176  } {2}
177  do_test vacuum2-4.6 {
178    execsql {
179      pragma integrity_check
180    }
181  } {ok}
182  do_test vacuum2-4.7 {
183    db close
184    sqlite3 db test.db
185    execsql {
186      pragma auto_vacuum;
187    }
188  } {2}
189}
190
191
192#-------------------------------------------------------------------------
193# The following block of tests verify the behaviour of the library when
194# a database is VACUUMed when there are one or more unfinalized SQL
195# statements reading the same database using the same db handle.
196#
197db close
198forcedelete test.db
199sqlite3 db test.db
200do_execsql_test vacuum2-5.1 {
201  CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
202  INSERT INTO t1 VALUES(1, randomblob(500));
203  INSERT INTO t1 SELECT a+1, randomblob(500) FROM t1;      -- 2
204  INSERT INTO t1 SELECT a+2, randomblob(500) FROM t1;      -- 4
205  INSERT INTO t1 SELECT a+4, randomblob(500) FROM t1;      -- 8
206  INSERT INTO t1 SELECT a+8, randomblob(500) FROM t1;      -- 16
207} {}
208
209do_test vacuum2-5.2 {
210  list [catch {
211    db eval {SELECT a, b FROM t1} { if {$a == 8} { execsql VACUUM } }
212  } msg] $msg
213} {1 {cannot VACUUM - SQL statements in progress}}
214
215do_test vacuum2-5.3 {
216  list [catch {
217    db eval {SELECT 1, 2, 3} { execsql VACUUM }
218  } msg] $msg
219} {1 {cannot VACUUM - SQL statements in progress}}
220
221do_test vacuum2-5.4 {
222  set res ""
223  set res2 ""
224  db eval {SELECT a, b FROM t1 WHERE a<=10} {
225    if {$a==6} { set res [catchsql VACUUM] }
226    lappend res2 $a
227  }
228  lappend res2 $res
229} {1 2 3 4 5 6 7 8 9 10 {1 {cannot VACUUM - SQL statements in progress}}}
230
231#-------------------------------------------------------------------------
232# Check that if the definition of a collation sequence is changed and
233# VACUUM run, records are store in the (new) correct order following the
234# VACUUM. Even if the modified collation is attached to a PK of a WITHOUT
235# ROWID table.
236
237proc cmp {lhs rhs} { string compare $lhs $rhs }
238db collate cmp cmp
239do_execsql_test 6.0 {
240  CREATE TABLE t6(x PRIMARY KEY COLLATE cmp, y) WITHOUT ROWID;
241  CREATE INDEX t6y ON t6(y);
242  INSERT INTO t6 VALUES('i', 'one');
243  INSERT INTO t6 VALUES('ii', 'one');
244  INSERT INTO t6 VALUES('iii', 'one');
245}
246integrity_check 6.1
247proc cmp {lhs rhs} { string compare $rhs $lhs }
248do_execsql_test 6.2 VACUUM
249integrity_check 6.3
250
251finish_test
252