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 307
