sqlite/ext/expert/expert1.test

# 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