wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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 file delete -force 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