summaryrefslogtreecommitdiffstats
path: root/ext/expert/expert1.test
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--ext/expert/expert1.test467
1 files changed, 467 insertions, 0 deletions
diff --git a/ext/expert/expert1.test b/ext/expert/expert1.test
new file mode 100644
index 0000000..dee4eb9
--- /dev/null
+++ b/ext/expert/expert1.test
@@ -0,0 +1,467 @@
+# 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.
+#
+#***********************************************************************
+#
+# 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
+}
+
+}
+
+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;
+}
+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 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}
+}
+
+finish_test
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-26 07:58:19 +0000