wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2005 July 22
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.
12 # This file implements tests for the ANALYZE command.
13 #
14 # $Id: analyze.test,v 1.9 2008/08/11 18:44:58 drh Exp $
15  
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18  
19 # There is nothing to test if ANALYZE is disable for this build.
20 #
21 ifcapable {!analyze} {
22 finish_test
23 return
24 }
25  
26 # Basic sanity checks.
27 #
28 do_test analyze-1.1 {
29 catchsql {
30 ANALYZE no_such_table
31 }
32 } {1 {no such table: no_such_table}}
33 do_test analyze-1.2 {
34 execsql {
35 SELECT count(*) FROM sqlite_master WHERE name='sqlite_stat1'
36 }
37 } {0}
38 do_test analyze-1.3 {
39 catchsql {
40 ANALYZE no_such_db.no_such_table
41 }
42 } {1 {unknown database no_such_db}}
43 do_test analyze-1.4 {
44 execsql {
45 SELECT count(*) FROM sqlite_master WHERE name='sqlite_stat1'
46 }
47 } {0}
48 do_test analyze-1.5.1 {
49 catchsql {
50 ANALYZE
51 }
52 } {0 {}}
53 do_test analyze-1.5.2 {
54 catchsql {
55 PRAGMA empty_result_callbacks=1;
56 ANALYZE
57 }
58 } {0 {}}
59 do_test analyze-1.6 {
60 execsql {
61 SELECT count(*) FROM sqlite_master WHERE name='sqlite_stat1'
62 }
63 } {1}
64 do_test analyze-1.6.2 {
65 catchsql {
66 CREATE INDEX stat1idx ON sqlite_stat1(idx);
67 }
68 } {1 {table sqlite_stat1 may not be indexed}}
69 do_test analyze-1.6.3 {
70 catchsql {
71 CREATE INDEX main.stat1idx ON SQLite_stat1(idx);
72 }
73 } {1 {table sqlite_stat1 may not be indexed}}
74 do_test analyze-1.7 {
75 execsql {
76 SELECT * FROM sqlite_stat1 WHERE idx NOT NULL
77 }
78 } {}
79 do_test analyze-1.8 {
80 catchsql {
81 ANALYZE main
82 }
83 } {0 {}}
84 do_test analyze-1.9 {
85 execsql {
86 SELECT * FROM sqlite_stat1 WHERE idx NOT NULL
87 }
88 } {}
89 do_test analyze-1.10 {
90 catchsql {
91 CREATE TABLE t1(a,b);
92 ANALYZE main.t1;
93 }
94 } {0 {}}
95 do_test analyze-1.11 {
96 execsql {
97 SELECT * FROM sqlite_stat1
98 }
99 } {}
100 do_test analyze-1.12 {
101 catchsql {
102 ANALYZE t1;
103 }
104 } {0 {}}
105 do_test analyze-1.13 {
106 execsql {
107 SELECT * FROM sqlite_stat1
108 }
109 } {}
110  
111 # Create some indices that can be analyzed. But do not yet add
112 # data. Without data in the tables, no analysis is done.
113 #
114 do_test analyze-2.1 {
115 execsql {
116 CREATE INDEX t1i1 ON t1(a);
117 ANALYZE main.t1;
118 SELECT * FROM sqlite_stat1 ORDER BY idx;
119 }
120 } {}
121 do_test analyze-2.2 {
122 execsql {
123 CREATE INDEX t1i2 ON t1(b);
124 ANALYZE t1;
125 SELECT * FROM sqlite_stat1 ORDER BY idx;
126 }
127 } {}
128 do_test analyze-2.3 {
129 execsql {
130 CREATE INDEX t1i3 ON t1(a,b);
131 ANALYZE main;
132 SELECT * FROM sqlite_stat1 ORDER BY idx;
133 }
134 } {}
135  
136 # Start adding data to the table. Verify that the analysis
137 # is done correctly.
138 #
139 do_test analyze-3.1 {
140 execsql {
141 INSERT INTO t1 VALUES(1,2);
142 INSERT INTO t1 VALUES(1,3);
143 ANALYZE main.t1;
144 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
145 }
146 } {t1i1 {2 2} t1i2 {2 1} t1i3 {2 2 1}}
147 do_test analyze-3.2 {
148 execsql {
149 INSERT INTO t1 VALUES(1,4);
150 INSERT INTO t1 VALUES(1,5);
151 ANALYZE t1;
152 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
153 }
154 } {t1i1 {4 4} t1i2 {4 1} t1i3 {4 4 1}}
155 do_test analyze-3.3 {
156 execsql {
157 INSERT INTO t1 VALUES(2,5);
158 ANALYZE main;
159 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
160 }
161 } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1}}
162 do_test analyze-3.4 {
163 execsql {
164 CREATE TABLE t2 AS SELECT * FROM t1;
165 CREATE INDEX t2i1 ON t2(a);
166 CREATE INDEX t2i2 ON t2(b);
167 CREATE INDEX t2i3 ON t2(a,b);
168 ANALYZE;
169 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
170 }
171 } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2} t2i3 {5 3 1}}
172 do_test analyze-3.5 {
173 execsql {
174 DROP INDEX t2i3;
175 ANALYZE t1;
176 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
177 }
178 } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2}}
179 do_test analyze-3.6 {
180 execsql {
181 ANALYZE t2;
182 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
183 }
184 } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2}}
185 do_test analyze-3.7 {
186 execsql {
187 DROP INDEX t2i2;
188 ANALYZE t2;
189 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
190 }
191 } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3}}
192 do_test analyze-3.8 {
193 execsql {
194 CREATE TABLE t3 AS SELECT a, b, rowid AS c, 'hi' AS d FROM t1;
195 CREATE INDEX t3i1 ON t3(a);
196 CREATE INDEX t3i2 ON t3(a,b,c,d);
197 CREATE INDEX t3i3 ON t3(d,b,c,a);
198 DROP TABLE t1;
199 DROP TABLE t2;
200 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
201 }
202 } {}
203 do_test analyze-3.9 {
204 execsql {
205 ANALYZE;
206 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
207 }
208 } {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}
209  
210 do_test analyze-3.10 {
211 execsql {
212 CREATE TABLE [silly " name](a, b, c);
213 CREATE INDEX 'foolish '' name' ON [silly " name](a, b);
214 CREATE INDEX 'another foolish '' name' ON [silly " name](c);
215 INSERT INTO [silly " name] VALUES(1, 2, 3);
216 INSERT INTO [silly " name] VALUES(4, 5, 6);
217 ANALYZE;
218 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
219 }
220 } {{another foolish ' name} {2 1} {foolish ' name} {2 1 1} t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}
221 do_test analyze-3.11 {
222 execsql {
223 DROP INDEX "foolish ' name";
224 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
225 }
226 } {{another foolish ' name} {2 1} t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}
227 do_test analyze-3.11 {
228 execsql {
229 DROP TABLE "silly "" name";
230 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
231 }
232 } {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}
233  
234 # Try corrupting the sqlite_stat1 table and make sure the
235 # database is still able to function.
236 #
237 do_test analyze-4.0 {
238 sqlite3 db2 test.db
239 db2 eval {
240 CREATE TABLE t4(x,y,z);
241 CREATE INDEX t4i1 ON t4(x);
242 CREATE INDEX t4i2 ON t4(y);
243 INSERT INTO t4 SELECT a,b,c FROM t3;
244 }
245 db2 close
246 db close
247 sqlite3 db test.db
248 execsql {
249 ANALYZE;
250 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
251 }
252 } {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1} t4i1 {5 3} t4i2 {5 2}}
253 do_test analyze-4.1 {
254 execsql {
255 PRAGMA writable_schema=on;
256 INSERT INTO sqlite_stat1 VALUES(null,null,null);
257 PRAGMA writable_schema=off;
258 }
259 db close
260 sqlite3 db test.db
261 execsql {
262 SELECT * FROM t4 WHERE x=1234;
263 }
264 } {}
265 do_test analyze-4.2 {
266 execsql {
267 PRAGMA writable_schema=on;
268 DELETE FROM sqlite_stat1;
269 INSERT INTO sqlite_stat1 VALUES('t4','t4i1','nonsense');
270 INSERT INTO sqlite_stat1 VALUES('t4','t4i2','120897349817238741092873198273409187234918720394817209384710928374109827172901827349871928741910');
271 PRAGMA writable_schema=off;
272 }
273 db close
274 sqlite3 db test.db
275 execsql {
276 SELECT * FROM t4 WHERE x=1234;
277 }
278 } {}
279 do_test analyze-4.3 {
280 execsql {
281 INSERT INTO sqlite_stat1 VALUES('t4','xyzzy','0 1 2 3');
282 }
283 db close
284 sqlite3 db test.db
285 execsql {
286 SELECT * FROM t4 WHERE x=1234;
287 }
288 } {}
289  
290 # This test corrupts the database file so it must be the last test
291 # in the series.
292 #
293 do_test analyze-99.1 {
294 execsql {
295 PRAGMA writable_schema=on;
296 UPDATE sqlite_master SET sql='nonsense' WHERE name='sqlite_stat1';
297 }
298 db close
299 catch { sqlite3 db test.db }
300 catchsql {
301 ANALYZE
302 }
303 } {1 {malformed database schema (sqlite_stat1) - near "nonsense": syntax error}}
304  
305  
306 finish_test