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.1.1.1 2012/02/21 17:04:16 misho 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: # Verify that DROP TABLE and DROP INDEX remove entries from the
291: # sqlite_stat1 and sqlite_stat3 tables.
292: #
293: do_test analyze-5.0 {
294: execsql {
295: DELETE FROM t3;
296: DELETE FROM t4;
297: INSERT INTO t3 VALUES(1,2,3,4);
298: INSERT INTO t3 VALUES(5,6,7,8);
299: INSERT INTO t3 SELECT a+8, b+8, c+8, d+8 FROM t3;
300: INSERT INTO t3 SELECT a+16, b+16, c+16, d+16 FROM t3;
301: INSERT INTO t3 SELECT a+32, b+32, c+32, d+32 FROM t3;
302: INSERT INTO t3 SELECT a+64, b+64, c+64, d+64 FROM t3;
303: INSERT INTO t4 SELECT a, b, c FROM t3;
304: ANALYZE;
305: SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
306: SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
307: }
308: } {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4}
309: ifcapable stat3 {
310: do_test analyze-5.1 {
311: execsql {
312: SELECT DISTINCT idx FROM sqlite_stat3 ORDER BY 1;
313: SELECT DISTINCT tbl FROM sqlite_stat3 ORDER BY 1;
314: }
315: } {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4}
316: }
317: do_test analyze-5.2 {
318: execsql {
319: DROP INDEX t3i2;
320: SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
321: SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
322: }
323: } {t3i1 t3i3 t4i1 t4i2 t3 t4}
324: ifcapable stat3 {
325: do_test analyze-5.3 {
326: execsql {
327: SELECT DISTINCT idx FROM sqlite_stat3 ORDER BY 1;
328: SELECT DISTINCT tbl FROM sqlite_stat3 ORDER BY 1;
329: }
330: } {t3i1 t3i3 t4i1 t4i2 t3 t4}
331: }
332: do_test analyze-5.4 {
333: execsql {
334: DROP TABLE t3;
335: SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
336: SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
337: }
338: } {t4i1 t4i2 t4}
339: ifcapable stat3 {
340: do_test analyze-5.5 {
341: execsql {
342: SELECT DISTINCT idx FROM sqlite_stat3 ORDER BY 1;
343: SELECT DISTINCT tbl FROM sqlite_stat3 ORDER BY 1;
344: }
345: } {t4i1 t4i2 t4}
346: }
347:
348: # This test corrupts the database file so it must be the last test
349: # in the series.
350: #
351: do_test analyze-99.1 {
352: execsql {
353: PRAGMA writable_schema=on;
354: UPDATE sqlite_master SET sql='nonsense' WHERE name='sqlite_stat1';
355: }
356: db close
357: catch { sqlite3 db test.db }
358: catchsql {
359: ANALYZE
360: }
361: } {1 {malformed database schema (sqlite_stat1) - near "nonsense": syntax error}}
362:
363:
364: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to analyze.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