| # 2009 Nov 11 |
| # |
| # 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. |
| # |
| #*********************************************************************** |
| # TESTRUNNER: shell |
| # |
| # The focus of this file is testing the CLI shell tool. Specifically, |
| # the ".recommend" command. |
| # |
| # |
| |
| # Test plan: |
| # |
| # |
| if {![info exists testdir]} { |
| set testdir [file join [file dirname [info script]] .. .. test] |
| } |
| source $testdir/tester.tcl |
| set testprefix expert1 |
| |
| if {[info commands sqlite3_expert_new]==""} { |
| finish_test |
| return |
| } |
| |
| |
| set CLI [test_binary_name sqlite3] |
| set CMD [test_binary_name sqlite3_expert] |
| |
| proc squish {txt} { |
| regsub -all {[[:space:]]+} $txt { } |
| } |
| |
| proc do_setup_rec_test {tn setup sql res} { |
| reset_db |
| if {[info exists ::set_main_db_name]} { |
| dbconfig_maindbname_icecube db |
| } |
| db eval $setup |
| uplevel [list do_rec_test $tn $sql $res] |
| } |
| |
| foreach {tn setup} { |
| 1 { |
| if {![file executable $CMD]} { continue } |
| |
| proc do_rec_test {tn sql res} { |
| set res [squish [string trim $res]] |
| set tst [subst -nocommands { |
| squish [string trim [exec $::CMD -verbose 0 -sql {$sql;} test.db]] |
| }] |
| uplevel [list do_test $tn $tst $res] |
| } |
| } |
| 2 { |
| if {[info commands sqlite3_expert_new]==""} { continue } |
| |
| proc do_rec_test {tn sql res} { |
| set expert [sqlite3_expert_new db] |
| $expert sql $sql |
| $expert analyze |
| |
| set result [list] |
| for {set i 0} {$i < [$expert count]} {incr i} { |
| set idx [string trim [$expert report $i indexes]] |
| if {$idx==""} {set idx "(no new indexes)"} |
| lappend result $idx |
| lappend result [string trim [$expert report $i plan]] |
| } |
| |
| $expert destroy |
| |
| set tst [subst -nocommands {set {} [squish [join {$result}]]}] |
| uplevel [list do_test $tn $tst [string trim [squish $res]]] |
| } |
| } |
| 3 { |
| if {[info commands sqlite3_expert_new]==""} { continue } |
| set ::set_main_db_name 1 |
| } |
| 4 { |
| if {![file executable $CLI]} { continue } |
| |
| proc do_rec_test {tn sql res} { |
| set res [squish [string trim $res]] |
| set tst [subst -nocommands { |
| squish [string trim [exec $::CLI test.db ".expert" {$sql;}]] |
| }] |
| uplevel [list do_test $tn $tst $res] |
| } |
| } |
| } { |
| |
| eval $setup |
| |
| |
| do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } { |
| SELECT * FROM t1 |
| } { |
| (no new indexes) |
| SCAN t1 |
| } |
| |
| do_setup_rec_test $tn.2 { |
| CREATE TABLE t1(a, b, c); |
| } { |
| SELECT * FROM t1 WHERE b>?; |
| } { |
| CREATE INDEX t1_idx_00000062 ON t1(b); |
| SEARCH t1 USING INDEX t1_idx_00000062 (b>?) |
| } |
| |
| do_setup_rec_test $tn.3 { |
| CREATE TABLE t1(a, b, c); |
| } { |
| SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ? |
| } { |
| CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE); |
| SEARCH t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?) |
| } |
| |
| do_setup_rec_test $tn.4 { |
| CREATE TABLE t1(a, b, c); |
| } { |
| SELECT a FROM t1 ORDER BY b; |
| } { |
| CREATE INDEX t1_idx_00000062 ON t1(b); |
| SCAN t1 USING INDEX t1_idx_00000062 |
| } |
| |
| do_setup_rec_test $tn.5 { |
| CREATE TABLE t1(a, b, c); |
| } { |
| SELECT a FROM t1 WHERE a=? ORDER BY b; |
| } { |
| CREATE INDEX t1_idx_000123a7 ON t1(a, b); |
| SEARCH t1 USING COVERING INDEX t1_idx_000123a7 (a=?) |
| } |
| |
| if 0 { |
| do_setup_rec_test $tn.6 { |
| CREATE TABLE t1(a, b, c); |
| } { |
| SELECT min(a) FROM t1 |
| } { |
| CREATE INDEX t1_idx_00000061 ON t1(a); |
| SEARCH t1 USING COVERING INDEX t1_idx_00000061 |
| } |
| } |
| |
| do_setup_rec_test $tn.7 { |
| CREATE TABLE t1(a, b, c); |
| } { |
| SELECT * FROM t1 ORDER BY a, b, c; |
| } { |
| CREATE INDEX t1_idx_033e95fe ON t1(a, b, c); |
| SCAN t1 USING COVERING INDEX t1_idx_033e95fe |
| } |
| |
| #do_setup_rec_test $tn.1.8 { |
| # CREATE TABLE t1(a, b, c); |
| #} { |
| # SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC; |
| #} { |
| # CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c); |
| # 0|0|0|SCAN t1 USING COVERING INDEX t1_idx_5be6e222 |
| #} |
| |
| do_setup_rec_test $tn.8.1 { |
| CREATE TABLE t1(a COLLATE NOCase, b, c); |
| } { |
| SELECT * FROM t1 WHERE a=? |
| } { |
| CREATE INDEX t1_idx_00000061 ON t1(a); |
| SEARCH t1 USING INDEX t1_idx_00000061 (a=?) |
| } |
| do_setup_rec_test $tn.8.2 { |
| CREATE TABLE t1(a, b COLLATE nocase, c); |
| } { |
| SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC; |
| } { |
| CREATE INDEX t1_idx_5cb97285 ON t1(a, b DESC, c); |
| SCAN t1 USING COVERING INDEX t1_idx_5cb97285 |
| } |
| |
| |
| # Tables with names that require quotes. |
| # |
| do_setup_rec_test $tn.9.1 { |
| CREATE TABLE "t t"(a, b, c); |
| } { |
| SELECT * FROM "t t" WHERE a=? |
| } { |
| CREATE INDEX "t t_idx_00000061" ON "t t"(a); |
| SEARCH t t USING INDEX t t_idx_00000061 (a=?) |
| } |
| |
| do_setup_rec_test $tn.9.2 { |
| CREATE TABLE "t t"(a, b, c); |
| } { |
| SELECT * FROM "t t" WHERE b BETWEEN ? AND ? |
| } { |
| CREATE INDEX "t t_idx_00000062" ON "t t"(b); |
| SEARCH t t USING INDEX t t_idx_00000062 (b>? AND b<?) |
| } |
| |
| # Columns with names that require quotes. |
| # |
| do_setup_rec_test $tn.10.1 { |
| CREATE TABLE t3(a, "b b", c); |
| } { |
| SELECT * FROM t3 WHERE "b b" = ? |
| } { |
| CREATE INDEX t3_idx_00050c52 ON t3('b b'); |
| SEARCH t3 USING INDEX t3_idx_00050c52 (b b=?) |
| } |
| |
| do_setup_rec_test $tn.10.2 { |
| CREATE TABLE t3(a, "b b", c); |
| } { |
| SELECT * FROM t3 ORDER BY "b b" |
| } { |
| CREATE INDEX t3_idx_00050c52 ON t3('b b'); |
| SCAN t3 USING INDEX t3_idx_00050c52 |
| } |
| |
| # Transitive constraints |
| # |
| do_setup_rec_test $tn.11.1 { |
| CREATE TABLE t5(a, b); |
| CREATE TABLE t6(c, d); |
| } { |
| SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=? |
| } { |
| CREATE INDEX t5_idx_000123a7 ON t5(a, b); |
| CREATE INDEX t6_idx_00000063 ON t6(c); |
| SEARCH t6 USING INDEX t6_idx_00000063 (c=?) |
| SEARCH t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?) |
| } |
| |
| # OR terms. |
| # |
| do_setup_rec_test $tn.12.1 { |
| CREATE TABLE t7(a, b); |
| } { |
| SELECT * FROM t7 WHERE a=? OR b=? |
| } { |
| CREATE INDEX t7_idx_00000062 ON t7(b); |
| CREATE INDEX t7_idx_00000061 ON t7(a); |
| MULTI-INDEX OR |
| INDEX 1 |
| SEARCH t7 USING INDEX t7_idx_00000061 (a=?) |
| INDEX 2 |
| SEARCH t7 USING INDEX t7_idx_00000062 (b=?) |
| } |
| |
| # rowid terms. |
| # |
| do_setup_rec_test $tn.13.1 { |
| CREATE TABLE t8(a, b); |
| } { |
| SELECT * FROM t8 WHERE rowid=? |
| } { |
| (no new indexes) |
| SEARCH t8 USING INTEGER PRIMARY KEY (rowid=?) |
| } |
| do_setup_rec_test $tn.13.2 { |
| CREATE TABLE t8(a, b); |
| } { |
| SELECT * FROM t8 ORDER BY rowid |
| } { |
| (no new indexes) |
| SCAN t8 |
| } |
| do_setup_rec_test $tn.13.3 { |
| CREATE TABLE t8(a, b); |
| } { |
| SELECT * FROM t8 WHERE a=? ORDER BY rowid |
| } { |
| CREATE INDEX t8_idx_00000061 ON t8(a); |
| SEARCH t8 USING INDEX t8_idx_00000061 (a=?) |
| } |
| |
| # Triggers |
| # |
| do_setup_rec_test $tn.14 { |
| CREATE TABLE t9(a, b, c); |
| CREATE TABLE t10(a, b, c); |
| CREATE TRIGGER t9t AFTER INSERT ON t9 BEGIN |
| UPDATE t10 SET a=new.a WHERE b = new.b; |
| END; |
| } { |
| INSERT INTO t9 VALUES(?, ?, ?); |
| } { |
| CREATE INDEX t10_idx_00000062 ON t10(b); |
| SEARCH t10 USING INDEX t10_idx_00000062 (b=?) |
| } |
| |
| do_setup_rec_test $tn.15 { |
| CREATE TABLE t1(a, b); |
| CREATE TABLE t2(c, d); |
| |
| WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) |
| INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s; |
| |
| WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) |
| INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s; |
| } { |
| SELECT * FROM t2, t1 WHERE b=? AND d=? AND t2.rowid=t1.rowid |
| } { |
| CREATE INDEX t2_idx_00000064 ON t2(d); |
| SEARCH t2 USING INDEX t2_idx_00000064 (d=?) |
| SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) |
| } |
| |
| do_setup_rec_test $tn.16 { |
| CREATE TABLE t1(a, b); |
| } { |
| SELECT * FROM t1 WHERE b IS NOT NULL; |
| } { |
| (no new indexes) |
| SCAN t1 |
| } |
| |
| do_setup_rec_test $tn.17.1 { |
| CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B)); |
| } { |
| SELECT * FROM example WHERE a=? |
| } { |
| (no new indexes) |
| SEARCH example USING INDEX sqlite_autoindex_example_1 (A=?) |
| } |
| do_setup_rec_test $tn.17.2 { |
| CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B)); |
| } { |
| SELECT * FROM example WHERE b=? |
| } { |
| CREATE INDEX example_idx_00000042 ON example(B); |
| SEARCH example USING INDEX example_idx_00000042 (B=?) |
| } |
| do_setup_rec_test $tn.17.3 { |
| CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B)); |
| } { |
| SELECT * FROM example WHERE a=? AND b=? |
| } { |
| (no new indexes) |
| SEARCH example USING INDEX sqlite_autoindex_example_1 (A=? AND B=?) |
| } |
| do_setup_rec_test $tn.17.4 { |
| CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B)); |
| } { |
| SELECT * FROM example WHERE a=? AND b>? |
| } { |
| (no new indexes) |
| SEARCH example USING INDEX sqlite_autoindex_example_1 (A=? AND B>?) |
| } |
| do_setup_rec_test $tn.17.5 { |
| CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B)); |
| } { |
| SELECT * FROM example WHERE a>? AND b=? |
| } { |
| CREATE INDEX example_idx_0000cb3f ON example(B, A); |
| SEARCH example USING INDEX example_idx_0000cb3f (B=? AND A>?) |
| } |
| |
| do_setup_rec_test $tn.18.0 { |
| CREATE TABLE SomeObject ( |
| a INTEGER PRIMARY KEY, |
| x TEXT GENERATED ALWAYS AS(HEX(a)) VIRTUAL |
| ); |
| } { |
| SELECT x FROM SomeObject; |
| } { |
| (no new indexes) |
| SCAN SomeObject |
| } |
| do_setup_rec_test $tn.18.1 { |
| CREATE TABLE SomeObject ( |
| a INTEGER PRIMARY KEY, |
| x TEXT GENERATED ALWAYS AS(HEX(a)) VIRTUAL |
| ); |
| } { |
| SELECT * FROM SomeObject WHERE x=?; |
| } { |
| CREATE INDEX SomeObject_idx_00000078 ON SomeObject(x); |
| SEARCH SomeObject USING COVERING INDEX SomeObject_idx_00000078 (x=?) |
| } |
| |
| |
| do_setup_rec_test $tn.19.0 { |
| CREATE TABLE t1("index"); |
| } { |
| SELECT * FROM t1 ORDER BY "index"; |
| } { |
| CREATE INDEX t1_idx_01a7214e ON t1('index'); |
| SCAN t1 USING COVERING INDEX t1_idx_01a7214e |
| } |
| |
| ifcapable fts5 { |
| do_setup_rec_test $tn.20.0 { |
| CREATE VIRTUAL TABLE ft USING fts5(a); |
| CREATE TABLE t1(x, y); |
| } { |
| SELECT * FROM ft, t1 WHERE a=x |
| } { |
| CREATE INDEX t1_idx_00000078 ON t1(x); |
| SCAN ft VIRTUAL TABLE INDEX 0: |
| SEARCH t1 USING INDEX t1_idx_00000078 (x=?) |
| } |
| } |
| |
| } |
| |
| proc do_candidates_test {tn sql res} { |
| set res [squish [string trim $res]] |
| |
| set expert [sqlite3_expert_new db] |
| $expert sql $sql |
| $expert analyze |
| |
| set candidates [squish [string trim [$expert report 0 candidates]]] |
| $expert destroy |
| |
| uplevel [list do_test $tn [list set {} $candidates] $res] |
| } |
| |
| |
| reset_db |
| do_execsql_test 5.0 { |
| CREATE TABLE t1(a, b); |
| CREATE TABLE t2(c, d); |
| |
| WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) |
| INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s; |
| |
| WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) |
| INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s; |
| |
| CREATE INDEX i1 ON t1( lower(a) ); |
| } |
| do_candidates_test 5.1 { |
| SELECT * FROM t1,t2 WHERE (b=? OR a=?) AND (c=? OR d=?) |
| } { |
| CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20 |
| CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50 |
| CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20 |
| CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5 |
| } |
| |
| do_candidates_test 5.2 { |
| SELECT * FROM t1,t2 WHERE a=? AND b=? AND c=? AND d=? |
| } { |
| CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 17 |
| CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5 |
| } |
| |
| do_execsql_test 5.3 { |
| CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50 |
| CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20 |
| CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 16 |
| |
| CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20 |
| CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5 |
| CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5 |
| |
| ANALYZE; |
| SELECT * FROM sqlite_stat1 ORDER BY 1, 2; |
| } { |
| t1 i1 {100 50} |
| t1 t1_idx_00000061 {100 50} |
| t1 t1_idx_00000062 {100 20} |
| t1 t1_idx_000123a7 {100 50 17} |
| t2 t2_idx_00000063 {100 20} |
| t2 t2_idx_00000064 {100 5} |
| t2 t2_idx_0001295b {100 20 5} |
| } |
| |
| do_catchsql_test 5.4 { |
| SELECT sqlite_expert_rem(123, 123); |
| } {1 {no such function: sqlite_expert_rem}} |
| do_catchsql_test 5.5 { |
| SELECT sqlite_expert_sample(); |
| } {1 {no such function: sqlite_expert_sample}} |
| |
| if 0 { |
| do_test expert1-6.0 { |
| catchcmd :memory: { |
| .expert |
| select base64(''); |
| .expert |
| select name from pragma_collation_list order by name collate uint; |
| } |
| } {0 {(no new indexes) |
| |
| SCAN CONSTANT ROW |
| |
| (no new indexes) |
| |
| SCAN pragma_collation_list VIRTUAL TABLE INDEX 0: |
| USE TEMP B-TREE FOR ORDER BY |
| }} |
| } |
| |
| do_execsql_test 6.0 { |
| CREATE TABLE x1(a, b, c, d); |
| CREATE INDEX x1ab ON x1(a, lower(b)); |
| CREATE INDEX x1dcba ON x1(d, b+c, a); |
| } |
| |
| do_candidates_test 6.1 { |
| SELECT * FROM x1 WHERE b=? ORDER BY a; |
| } { |
| CREATE INDEX x1_idx_0001267f ON x1(b, a); |
| CREATE INDEX x1_idx_00000062 ON x1(b); |
| } |
| |
| #------------------------------------------------------------------------- |
| ifcapable fts5 { |
| reset_db |
| do_execsql_test 7.0 { |
| CREATE VIRTUAL TABLE ft USING fts5(a); |
| CREATE TABLE t1(x, y); |
| } |
| |
| do_candidates_test 7.1 { |
| SELECT * FROM ft, t1 WHERE a=x |
| } { |
| CREATE INDEX t1_idx_00000078 ON t1(x); |
| } |
| |
| register_tcl_module db |
| proc vtab_command {method args} { |
| global G |
| |
| switch -- $method { |
| xConnect { |
| return "CREATE TABLE t1(a, b, c);" |
| } |
| |
| xBestIndex { |
| return [list] |
| } |
| |
| xFilter { |
| return [list sql "SELECT rowid, * FROM t0"] |
| } |
| } |
| |
| return {} |
| } |
| |
| do_execsql_test 7.2 { |
| CREATE TABLE t0(a, b, c); |
| INSERT INTO t0 VALUES(1, 2, 3), (11, 22, 33); |
| CREATE VIRTUAL TABLE t2 USING tcl(vtab_command); |
| } |
| |
| do_execsql_test 7.3 { |
| SELECT * FROM t2 |
| } { |
| 1 2 3 |
| 11 22 33 |
| } |
| |
| do_candidates_test 7.4 { |
| SELECT * FROM ft, t1 WHERE a=x |
| } { |
| CREATE INDEX t1_idx_00000078 ON t1(x); |
| } |
| |
| do_test 7.5 { |
| set expert [sqlite3_expert_new db] |
| list [catch { $expert sql "SELECT * FROM ft, t2 WHERE b=1" } msg] $msg |
| } {1 {no such table: t2}} |
| $expert destroy |
| |
| reset_db |
| do_execsql_test 7.6 { |
| BEGIN TRANSACTION; |
| CREATE TABLE IF NOT EXISTS 'bfts_idx_data'(id INTEGER PRIMARY KEY, block BLOB); |
| CREATE TABLE IF NOT EXISTS 'fts_idx_data'(id INTEGER PRIMARY KEY, block BLOB); |
| INSERT INTO fts_idx_data VALUES(1,X''); |
| INSERT INTO fts_idx_data VALUES(10,X'00000000ff000001000000'); |
| CREATE TABLE IF NOT EXISTS 'fts_idx_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID; |
| CREATE TABLE IF NOT EXISTS 'fts_idx_docsize'(id INTEGER PRIMARY KEY, sz BLOB, origin INTEGER); |
| CREATE TABLE IF NOT EXISTS 'fts_idx_config'(k PRIMARY KEY, v) WITHOUT ROWID; |
| INSERT INTO fts_idx_config VALUES('version',4); |
| PRAGMA writable_schema=ON; |
| INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','fts_idx','fts_idx',0,'CREATE VIRTUAL TABLE fts_idx USING fts5(Title, Description, Channel, Tags, content='''', contentless_delete=1)'); |
| |
| CREATE TABLE f(x BLOB, y); |
| COMMIT; |
| PRAGMA writable_schema = RESET; |
| } |
| |
| do_candidates_test 7.4 { |
| SELECT * FROM fts_idx, f WHERE x = fts_idx.Channel |
| } { |
| CREATE INDEX f_idx_00000078 ON f(x); |
| } |
| } |
| |
| finish_test |