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