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