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