diff options
Diffstat (limited to 'test/analyzeA.test')
| -rw-r--r-- | test/analyzeA.test | 167 |
1 files changed, 167 insertions, 0 deletions
diff --git a/test/analyzeA.test b/test/analyzeA.test new file mode 100644 index 0000000..d9ca2c0 --- /dev/null +++ b/test/analyzeA.test @@ -0,0 +1,167 @@ +# 2013 August 3 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# This file contains automated tests used to verify that the current build +# (which must be either ENABLE_STAT3 or ENABLE_STAT4) works with both stat3 +# and stat4 data. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix analyzeA + +ifcapable !stat4&&!stat3 { + finish_test + return +} + +# Populate the stat3 table according to the current contents of the db +# +proc populate_stat3 {{bDropTable 1}} { + # Open a second connection on database "test.db" and run ANALYZE. If this + # is an ENABLE_STAT3 build, this is all that is required to create and + # populate the sqlite_stat3 table. + # + sqlite3 db2 test.db + execsql { ANALYZE } + + # Now, if this is an ENABLE_STAT4 build, create and populate the + # sqlite_stat3 table based on the stat4 data gathered by the ANALYZE + # above. Then drop the sqlite_stat4 table. + # + ifcapable stat4 { + db2 func lindex lindex + execsql { + PRAGMA writable_schema = on; + CREATE TABLE sqlite_stat3(tbl,idx,neq,nlt,ndlt,sample); + INSERT INTO sqlite_stat3 + SELECT DISTINCT tbl, idx, + lindex(neq,0), lindex(nlt,0), lindex(ndlt,0), test_extract(sample, 0) + FROM sqlite_stat4; + } db2 + if {$bDropTable} { execsql {DROP TABLE sqlite_stat4} db2 } + execsql { PRAGMA writable_schema = off } + } + + # Modify the database schema cookie to ensure that the other connection + # reloads the schema. + # + execsql { + CREATE TABLE obscure_tbl_nm(x); + DROP TABLE obscure_tbl_nm; + } db2 + db2 close +} + +# Populate the stat4 table according to the current contents of the db +# +proc populate_stat4 {{bDropTable 1}} { + sqlite3 db2 test.db + execsql { ANALYZE } + + ifcapable stat3 { + execsql { + PRAGMA writable_schema = on; + CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample); + INSERT INTO sqlite_stat4 + SELECT tbl, idx, neq, nlt, ndlt, sqlite_record(sample) + FROM sqlite_stat3; + } db2 + if {$bDropTable} { execsql {DROP TABLE sqlite_stat3} db2 } + execsql { PRAGMA writable_schema = off } + } + + # Modify the database schema cookie to ensure that the other connection + # reloads the schema. + # + execsql { + CREATE TABLE obscure_tbl_nm(x); + DROP TABLE obscure_tbl_nm; + } db2 + db2 close +} + +# Populate the stat4 table according to the current contents of the db. +# Leave deceptive data in the stat3 table. This data should be ignored +# in favour of that from the stat4 table. +# +proc populate_both {} { + ifcapable stat4 { populate_stat3 0 } + ifcapable stat3 { populate_stat4 0 } + + sqlite3 db2 test.db + execsql { + PRAGMA writable_schema = on; + UPDATE sqlite_stat3 SET idx = + CASE idx WHEN 't1b' THEN 't1c' ELSE 't1b' + END; + PRAGMA writable_schema = off; + CREATE TABLE obscure_tbl_nm(x); + DROP TABLE obscure_tbl_nm; + } db2 + db2 close +} + +foreach {tn analyze_cmd} { + 1 populate_stat4 + 2 populate_stat3 + 3 populate_both +} { + reset_db + do_test 1.$tn.1 { + execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c) } + for {set i 0} {$i < 100} {incr i} { + set c [expr int(pow(1.1,$i)/100)] + set b [expr 125 - int(pow(1.1,99-$i))/100] + execsql {INSERT INTO t1 VALUES($i, $b, $c)} + } + } {} + + execsql { CREATE INDEX t1b ON t1(b) } + execsql { CREATE INDEX t1c ON t1(c) } + $analyze_cmd + + do_execsql_test 1.$tn.2.1 { SELECT count(*) FROM t1 WHERE b=31 } 1 + do_execsql_test 1.$tn.2.2 { SELECT count(*) FROM t1 WHERE c=0 } 49 + do_execsql_test 1.$tn.2.3 { SELECT count(*) FROM t1 WHERE b=125 } 49 + do_execsql_test 1.$tn.2.4 { SELECT count(*) FROM t1 WHERE c=16 } 1 + + do_eqp_test 1.$tn.2.5 { + SELECT * FROM t1 WHERE b = 31 AND c = 0; + } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} + do_eqp_test 1.$tn.2.6 { + SELECT * FROM t1 WHERE b = 125 AND c = 16; + } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)}} + + do_execsql_test 1.$tn.3.1 { + SELECT count(*) FROM t1 WHERE b BETWEEN 0 AND 50 + } {6} + do_execsql_test 1.$tn.3.2 { + SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 50 + } {90} + do_execsql_test 1.$tn.3.3 { + SELECT count(*) FROM t1 WHERE b BETWEEN 75 AND 125 + } {90} + do_execsql_test 1.$tn.3.4 { + SELECT count(*) FROM t1 WHERE c BETWEEN 75 AND 125 + } {6} + + do_eqp_test 1.$tn.3.5 { + SELECT * FROM t1 WHERE b BETWEEN 0 AND 50 AND c BETWEEN 0 AND 50 + } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}} + + do_eqp_test 1.$tn.3.6 { + SELECT * FROM t1 WHERE b BETWEEN 75 AND 125 AND c BETWEEN 75 AND 125 + } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}} +} + +finish_test + |