1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/analyze.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,306 @@
1.4 +# 2005 July 22
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library.
1.15 +# This file implements tests for the ANALYZE command.
1.16 +#
1.17 +# $Id: analyze.test,v 1.9 2008/08/11 18:44:58 drh Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# There is nothing to test if ANALYZE is disable for this build.
1.23 +#
1.24 +ifcapable {!analyze} {
1.25 + finish_test
1.26 + return
1.27 +}
1.28 +
1.29 +# Basic sanity checks.
1.30 +#
1.31 +do_test analyze-1.1 {
1.32 + catchsql {
1.33 + ANALYZE no_such_table
1.34 + }
1.35 +} {1 {no such table: no_such_table}}
1.36 +do_test analyze-1.2 {
1.37 + execsql {
1.38 + SELECT count(*) FROM sqlite_master WHERE name='sqlite_stat1'
1.39 + }
1.40 +} {0}
1.41 +do_test analyze-1.3 {
1.42 + catchsql {
1.43 + ANALYZE no_such_db.no_such_table
1.44 + }
1.45 +} {1 {unknown database no_such_db}}
1.46 +do_test analyze-1.4 {
1.47 + execsql {
1.48 + SELECT count(*) FROM sqlite_master WHERE name='sqlite_stat1'
1.49 + }
1.50 +} {0}
1.51 +do_test analyze-1.5.1 {
1.52 + catchsql {
1.53 + ANALYZE
1.54 + }
1.55 +} {0 {}}
1.56 +do_test analyze-1.5.2 {
1.57 + catchsql {
1.58 + PRAGMA empty_result_callbacks=1;
1.59 + ANALYZE
1.60 + }
1.61 +} {0 {}}
1.62 +do_test analyze-1.6 {
1.63 + execsql {
1.64 + SELECT count(*) FROM sqlite_master WHERE name='sqlite_stat1'
1.65 + }
1.66 +} {1}
1.67 +do_test analyze-1.6.2 {
1.68 + catchsql {
1.69 + CREATE INDEX stat1idx ON sqlite_stat1(idx);
1.70 + }
1.71 +} {1 {table sqlite_stat1 may not be indexed}}
1.72 +do_test analyze-1.6.3 {
1.73 + catchsql {
1.74 + CREATE INDEX main.stat1idx ON SQLite_stat1(idx);
1.75 + }
1.76 +} {1 {table sqlite_stat1 may not be indexed}}
1.77 +do_test analyze-1.7 {
1.78 + execsql {
1.79 + SELECT * FROM sqlite_stat1
1.80 + }
1.81 +} {}
1.82 +do_test analyze-1.8 {
1.83 + catchsql {
1.84 + ANALYZE main
1.85 + }
1.86 +} {0 {}}
1.87 +do_test analyze-1.9 {
1.88 + execsql {
1.89 + SELECT * FROM sqlite_stat1
1.90 + }
1.91 +} {}
1.92 +do_test analyze-1.10 {
1.93 + catchsql {
1.94 + CREATE TABLE t1(a,b);
1.95 + ANALYZE main.t1;
1.96 + }
1.97 +} {0 {}}
1.98 +do_test analyze-1.11 {
1.99 + execsql {
1.100 + SELECT * FROM sqlite_stat1
1.101 + }
1.102 +} {}
1.103 +do_test analyze-1.12 {
1.104 + catchsql {
1.105 + ANALYZE t1;
1.106 + }
1.107 +} {0 {}}
1.108 +do_test analyze-1.13 {
1.109 + execsql {
1.110 + SELECT * FROM sqlite_stat1
1.111 + }
1.112 +} {}
1.113 +
1.114 +# Create some indices that can be analyzed. But do not yet add
1.115 +# data. Without data in the tables, no analysis is done.
1.116 +#
1.117 +do_test analyze-2.1 {
1.118 + execsql {
1.119 + CREATE INDEX t1i1 ON t1(a);
1.120 + ANALYZE main.t1;
1.121 + SELECT * FROM sqlite_stat1 ORDER BY idx;
1.122 + }
1.123 +} {}
1.124 +do_test analyze-2.2 {
1.125 + execsql {
1.126 + CREATE INDEX t1i2 ON t1(b);
1.127 + ANALYZE t1;
1.128 + SELECT * FROM sqlite_stat1 ORDER BY idx;
1.129 + }
1.130 +} {}
1.131 +do_test analyze-2.3 {
1.132 + execsql {
1.133 + CREATE INDEX t1i3 ON t1(a,b);
1.134 + ANALYZE main;
1.135 + SELECT * FROM sqlite_stat1 ORDER BY idx;
1.136 + }
1.137 +} {}
1.138 +
1.139 +# Start adding data to the table. Verify that the analysis
1.140 +# is done correctly.
1.141 +#
1.142 +do_test analyze-3.1 {
1.143 + execsql {
1.144 + INSERT INTO t1 VALUES(1,2);
1.145 + INSERT INTO t1 VALUES(1,3);
1.146 + ANALYZE main.t1;
1.147 + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
1.148 + }
1.149 +} {t1i1 {2 2} t1i2 {2 1} t1i3 {2 2 1}}
1.150 +do_test analyze-3.2 {
1.151 + execsql {
1.152 + INSERT INTO t1 VALUES(1,4);
1.153 + INSERT INTO t1 VALUES(1,5);
1.154 + ANALYZE t1;
1.155 + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
1.156 + }
1.157 +} {t1i1 {4 4} t1i2 {4 1} t1i3 {4 4 1}}
1.158 +do_test analyze-3.3 {
1.159 + execsql {
1.160 + INSERT INTO t1 VALUES(2,5);
1.161 + ANALYZE main;
1.162 + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
1.163 + }
1.164 +} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1}}
1.165 +do_test analyze-3.4 {
1.166 + execsql {
1.167 + CREATE TABLE t2 AS SELECT * FROM t1;
1.168 + CREATE INDEX t2i1 ON t2(a);
1.169 + CREATE INDEX t2i2 ON t2(b);
1.170 + CREATE INDEX t2i3 ON t2(a,b);
1.171 + ANALYZE;
1.172 + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
1.173 + }
1.174 +} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2} t2i3 {5 3 1}}
1.175 +do_test analyze-3.5 {
1.176 + execsql {
1.177 + DROP INDEX t2i3;
1.178 + ANALYZE t1;
1.179 + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
1.180 + }
1.181 +} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2}}
1.182 +do_test analyze-3.6 {
1.183 + execsql {
1.184 + ANALYZE t2;
1.185 + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
1.186 + }
1.187 +} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2}}
1.188 +do_test analyze-3.7 {
1.189 + execsql {
1.190 + DROP INDEX t2i2;
1.191 + ANALYZE t2;
1.192 + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
1.193 + }
1.194 +} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3}}
1.195 +do_test analyze-3.8 {
1.196 + execsql {
1.197 + CREATE TABLE t3 AS SELECT a, b, rowid AS c, 'hi' AS d FROM t1;
1.198 + CREATE INDEX t3i1 ON t3(a);
1.199 + CREATE INDEX t3i2 ON t3(a,b,c,d);
1.200 + CREATE INDEX t3i3 ON t3(d,b,c,a);
1.201 + DROP TABLE t1;
1.202 + DROP TABLE t2;
1.203 + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
1.204 + }
1.205 +} {}
1.206 +do_test analyze-3.9 {
1.207 + execsql {
1.208 + ANALYZE;
1.209 + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
1.210 + }
1.211 +} {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}
1.212 +
1.213 +do_test analyze-3.10 {
1.214 + execsql {
1.215 + CREATE TABLE [silly " name](a, b, c);
1.216 + CREATE INDEX 'foolish '' name' ON [silly " name](a, b);
1.217 + CREATE INDEX 'another foolish '' name' ON [silly " name](c);
1.218 + INSERT INTO [silly " name] VALUES(1, 2, 3);
1.219 + INSERT INTO [silly " name] VALUES(4, 5, 6);
1.220 + ANALYZE;
1.221 + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
1.222 + }
1.223 +} {{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}}
1.224 +do_test analyze-3.11 {
1.225 + execsql {
1.226 + DROP INDEX "foolish ' name";
1.227 + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
1.228 + }
1.229 +} {{another foolish ' name} {2 1} t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}
1.230 +do_test analyze-3.11 {
1.231 + execsql {
1.232 + DROP TABLE "silly "" name";
1.233 + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
1.234 + }
1.235 +} {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}
1.236 +
1.237 +# Try corrupting the sqlite_stat1 table and make sure the
1.238 +# database is still able to function.
1.239 +#
1.240 +do_test analyze-4.0 {
1.241 + sqlite3 db2 test.db
1.242 + db2 eval {
1.243 + CREATE TABLE t4(x,y,z);
1.244 + CREATE INDEX t4i1 ON t4(x);
1.245 + CREATE INDEX t4i2 ON t4(y);
1.246 + INSERT INTO t4 SELECT a,b,c FROM t3;
1.247 + }
1.248 + db2 close
1.249 + db close
1.250 + sqlite3 db test.db
1.251 + execsql {
1.252 + ANALYZE;
1.253 + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
1.254 + }
1.255 +} {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1} t4i1 {5 3} t4i2 {5 2}}
1.256 +do_test analyze-4.1 {
1.257 + execsql {
1.258 + PRAGMA writable_schema=on;
1.259 + INSERT INTO sqlite_stat1 VALUES(null,null,null);
1.260 + PRAGMA writable_schema=off;
1.261 + }
1.262 + db close
1.263 + sqlite3 db test.db
1.264 + execsql {
1.265 + SELECT * FROM t4 WHERE x=1234;
1.266 + }
1.267 +} {}
1.268 +do_test analyze-4.2 {
1.269 + execsql {
1.270 + PRAGMA writable_schema=on;
1.271 + DELETE FROM sqlite_stat1;
1.272 + INSERT INTO sqlite_stat1 VALUES('t4','t4i1','nonsense');
1.273 + INSERT INTO sqlite_stat1 VALUES('t4','t4i2','120897349817238741092873198273409187234918720394817209384710928374109827172901827349871928741910');
1.274 + PRAGMA writable_schema=off;
1.275 + }
1.276 + db close
1.277 + sqlite3 db test.db
1.278 + execsql {
1.279 + SELECT * FROM t4 WHERE x=1234;
1.280 + }
1.281 +} {}
1.282 +do_test analyze-4.3 {
1.283 + execsql {
1.284 + INSERT INTO sqlite_stat1 VALUES('t4','xyzzy','0 1 2 3');
1.285 + }
1.286 + db close
1.287 + sqlite3 db test.db
1.288 + execsql {
1.289 + SELECT * FROM t4 WHERE x=1234;
1.290 + }
1.291 +} {}
1.292 +
1.293 +# This test corrupts the database file so it must be the last test
1.294 +# in the series.
1.295 +#
1.296 +do_test analyze-99.1 {
1.297 + execsql {
1.298 + PRAGMA writable_schema=on;
1.299 + UPDATE sqlite_master SET sql='nonsense' WHERE name='sqlite_stat1';
1.300 + }
1.301 + db close
1.302 + sqlite3 db test.db
1.303 + catchsql {
1.304 + ANALYZE
1.305 + }
1.306 +} {1 {malformed database schema (sqlite_stat1) - near "nonsense": syntax error}}
1.307 +
1.308 +
1.309 +finish_test