ext/expert/expert1.test

   1 # 2009 Nov 11
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 #
  12 # The focus of this file is testing the CLI shell tool. Specifically,
  13 # the ".recommend" command.
  14 #
  15 #
  16
  17 # Test plan:
  18 #
  19 #
  20 if {![info exists testdir]} {
  21   set testdir [file join [file dirname [info script]] .. .. test]
  22 }
  23 source $testdir/tester.tcl
  24 set testprefix expert1
  25
  26 if {[info commands sqlite3_expert_new]==""} {
  27   finish_test
  28   return
  29 }
  30
  31 set CLI [test_binary_name sqlite3]
  32 set CMD [test_binary_name sqlite3_expert]
  33
  34 proc squish {txt} {
  35   regsub -all {[[:space:]]+} $txt { }
  36 }
  37
  38 proc do_setup_rec_test {tn setup sql res} {
  39   reset_db
  40   db eval $setup
  41   uplevel [list do_rec_test $tn $sql $res]
  42 }
  43
  44 foreach {tn setup} {
  45   1 {
  46     if {![file executable $CMD]} { continue }
  47
  48     proc do_rec_test {tn sql res} {
  49       set res [squish [string trim $res]]
  50       set tst [subst -nocommands {
  51         squish [string trim [exec $::CMD -verbose 0 -sql {$sql;} test.db]]
  52       }]
  53       uplevel [list do_test $tn $tst $res]
  54     }
  55   }
  56   2 {
  57     if {[info commands sqlite3_expert_new]==""} { continue }
  58
  59     proc do_rec_test {tn sql res} {
  60       set expert [sqlite3_expert_new db]
  61       $expert sql $sql
  62       $expert analyze
  63
  64       set result [list]
  65       for {set i 0} {$i < [$expert count]} {incr i} {
  66         set idx [string trim [$expert report $i indexes]]
  67         if {$idx==""} {set idx "(no new indexes)"}
  68         lappend result $idx
  69         lappend result [string trim [$expert report $i plan]]
  70       }
  71
  72       $expert destroy
  73
  74       set tst [subst -nocommands {set {} [squish [join {$result}]]}]
  75       uplevel [list do_test $tn $tst [string trim [squish $res]]]
  76     }
  77   }
  78   3 {
  79     if {![file executable $CLI]} { continue }
  80
  81     proc do_rec_test {tn sql res} {
  82       set res [squish [string trim $res]]
  83       set tst [subst -nocommands {
  84         squish [string trim [exec $::CLI test.db ".expert" {$sql;}]]
  85       }]
  86       uplevel [list do_test $tn $tst $res]
  87     }
  88   }
  89 } {
  90
  91   eval $setup
  92
  93
  94 do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } {
  95   SELECT * FROM t1
  96 } {
  97   (no new indexes)
  98   SCAN TABLE t1
  99 }
 100
 101 do_setup_rec_test $tn.2 {
 102   CREATE TABLE t1(a, b, c);
 103 } {
 104   SELECT * FROM t1 WHERE b>?;
 105 } {
 106   CREATE INDEX t1_idx_00000062 ON t1(b);
 107   SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?)
 108 }
 109
 110 do_setup_rec_test $tn.3 {
 111   CREATE TABLE t1(a, b, c);
 112 } {
 113   SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
 114 } {
 115   CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE);
 116   SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?)
 117 }
 118
 119 do_setup_rec_test $tn.4 {
 120   CREATE TABLE t1(a, b, c);
 121 } {
 122   SELECT a FROM t1 ORDER BY b;
 123 } {
 124   CREATE INDEX t1_idx_00000062 ON t1(b);
 125   SCAN TABLE t1 USING INDEX t1_idx_00000062
 126 }
 127
 128 do_setup_rec_test $tn.5 {
 129   CREATE TABLE t1(a, b, c);
 130 } {
 131   SELECT a FROM t1 WHERE a=? ORDER BY b;
 132 } {
 133   CREATE INDEX t1_idx_000123a7 ON t1(a, b);
 134   SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?)
 135 }
 136
 137 do_setup_rec_test $tn.6 {
 138   CREATE TABLE t1(a, b, c);
 139 } {
 140   SELECT min(a) FROM t1
 141 } {
 142   CREATE INDEX t1_idx_00000061 ON t1(a);
 143   SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061
 144 }
 145
 146 do_setup_rec_test $tn.7 {
 147   CREATE TABLE t1(a, b, c);
 148 } {
 149   SELECT * FROM t1 ORDER BY a, b, c;
 150 } {
 151   CREATE INDEX t1_idx_033e95fe ON t1(a, b, c);
 152   SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe
 153 }
 154
 155 #do_setup_rec_test $tn.1.8 {
 156 #  CREATE TABLE t1(a, b, c);
 157 #} {
 158 #  SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
 159 #} {
 160 #  CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c);
 161 #  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222
 162 #}
 163
 164 do_setup_rec_test $tn.8.1 {
 165   CREATE TABLE t1(a COLLATE NOCase, b, c);
 166 } {
 167   SELECT * FROM t1 WHERE a=?
 168 } {
 169   CREATE INDEX t1_idx_00000061 ON t1(a);
 170   SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?)
 171 }
 172 do_setup_rec_test $tn.8.2 {
 173   CREATE TABLE t1(a, b COLLATE nocase, c);
 174 } {
 175   SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC;
 176 } {
 177   CREATE INDEX t1_idx_5cb97285 ON t1(a, b DESC, c);
 178   SCAN TABLE t1 USING COVERING INDEX t1_idx_5cb97285
 179 }
 180
 181
 182 # Tables with names that require quotes.
 183 #
 184 do_setup_rec_test $tn.9.1 {
 185   CREATE TABLE "t t"(a, b, c);
 186 } {
 187   SELECT * FROM "t t" WHERE a=?
 188 } {
 189   CREATE INDEX 't t_idx_00000061' ON 't t'(a);
 190   SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?)
 191 }
 192
 193 do_setup_rec_test $tn.9.2 {
 194   CREATE TABLE "t t"(a, b, c);
 195 } {
 196   SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
 197 } {
 198   CREATE INDEX 't t_idx_00000062' ON 't t'(b);
 199   SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?)
 200 }
 201
 202 # Columns with names that require quotes.
 203 #
 204 do_setup_rec_test $tn.10.1 {
 205   CREATE TABLE t3(a, "b b", c);
 206 } {
 207   SELECT * FROM t3 WHERE "b b" = ?
 208 } {
 209   CREATE INDEX t3_idx_00050c52 ON t3('b b');
 210   SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?)
 211 }
 212
 213 do_setup_rec_test $tn.10.2 {
 214   CREATE TABLE t3(a, "b b", c);
 215 } {
 216   SELECT * FROM t3 ORDER BY "b b"
 217 } {
 218   CREATE INDEX t3_idx_00050c52 ON t3('b b');
 219   SCAN TABLE t3 USING INDEX t3_idx_00050c52
 220 }
 221
 222 # Transitive constraints
 223 #
 224 do_setup_rec_test $tn.11.1 {
 225   CREATE TABLE t5(a, b);
 226   CREATE TABLE t6(c, d);
 227 } {
 228   SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
 229 } {
 230   CREATE INDEX t5_idx_000123a7 ON t5(a, b);
 231   CREATE INDEX t6_idx_00000063 ON t6(c);
 232   SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?)
 233   SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
 234 }
 235
 236 # OR terms.
 237 #
 238 do_setup_rec_test $tn.12.1 {
 239   CREATE TABLE t7(a, b);
 240 } {
 241   SELECT * FROM t7 WHERE a=? OR b=?
 242 } {
 243   CREATE INDEX t7_idx_00000062 ON t7(b);
 244   CREATE INDEX t7_idx_00000061 ON t7(a);
 245   MULTI-INDEX OR
 246     SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?)
 247     SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
 248 }
 249
 250 # rowid terms.
 251 #
 252 do_setup_rec_test $tn.13.1 {
 253   CREATE TABLE t8(a, b);
 254 } {
 255   SELECT * FROM t8 WHERE rowid=?
 256 } {
 257   (no new indexes)
 258   SEARCH TABLE t8 USING INTEGER PRIMARY KEY (rowid=?)
 259 }
 260 do_setup_rec_test $tn.13.2 {
 261   CREATE TABLE t8(a, b);
 262 } {
 263   SELECT * FROM t8 ORDER BY rowid
 264 } {
 265   (no new indexes)
 266   SCAN TABLE t8
 267 }
 268 do_setup_rec_test $tn.13.3 {
 269   CREATE TABLE t8(a, b);
 270 } {
 271   SELECT * FROM t8 WHERE a=? ORDER BY rowid
 272 } {
 273   CREATE INDEX t8_idx_00000061 ON t8(a);
 274   SEARCH TABLE t8 USING INDEX t8_idx_00000061 (a=?)
 275 }
 276
 277 # Triggers
 278 #
 279 do_setup_rec_test $tn.14 {
 280   CREATE TABLE t9(a, b, c);
 281   CREATE TABLE t10(a, b, c);
 282   CREATE TRIGGER t9t AFTER INSERT ON t9 BEGIN
 283     UPDATE t10 SET a=new.a WHERE b = new.b;
 284   END;
 285 } {
 286   INSERT INTO t9 VALUES(?, ?, ?);
 287 } {
 288   CREATE INDEX t10_idx_00000062 ON t10(b);
 289   -- TRIGGER t9t
 290   SEARCH TABLE t10 USING INDEX t10_idx_00000062 (b=?)
 291 }
 292
 293 do_setup_rec_test $tn.15 {
 294   CREATE TABLE t1(a, b);
 295   CREATE TABLE t2(c, d);
 296
 297   WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
 298   INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s;
 299
 300   WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
 301   INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s;
 302 } {
 303   SELECT * FROM t2, t1 WHERE b=? AND d=? AND t2.rowid=t1.rowid
 304 } {
 305   CREATE INDEX t2_idx_00000064 ON t2(d);
 306   SEARCH TABLE t2 USING INDEX t2_idx_00000064 (d=?)
 307   SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
 308 }
 309
 310 do_setup_rec_test $tn.16 {
 311   CREATE TABLE t1(a, b);
 312 } {
 313   SELECT * FROM t1 WHERE b IS NOT NULL;
 314 } {
 315   (no new indexes)
 316   SCAN TABLE t1
 317 }
 318
 319 }
 320
 321 proc do_candidates_test {tn sql res} {
 322   set res [squish [string trim $res]]
 323
 324   set expert [sqlite3_expert_new db]
 325   $expert sql $sql
 326   $expert analyze
 327
 328   set candidates [squish [string trim [$expert report 0 candidates]]]
 329   $expert destroy
 330
 331   uplevel [list do_test $tn [list set {} $candidates] $res]
 332 }
 333
 334
 335 reset_db
 336 do_execsql_test 3.0 {
 337   CREATE TABLE t1(a, b);
 338   CREATE TABLE t2(c, d);
 339
 340   WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
 341   INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s;
 342
 343   WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
 344   INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s;
 345 }
 346 do_candidates_test 3.1 {
 347   SELECT * FROM t1,t2 WHERE (b=? OR a=?) AND (c=? OR d=?)
 348 } {
 349   CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20
 350   CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50
 351   CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20
 352   CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5
 353 }
 354
 355 do_candidates_test 3.2 {
 356   SELECT * FROM t1,t2 WHERE a=? AND b=? AND c=? AND d=?
 357 } {
 358   CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 17
 359   CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5
 360 }
 361
 362 do_execsql_test 3.2 {
 363   CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50
 364   CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20
 365   CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 16
 366
 367   CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20
 368   CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5
 369   CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5
 370
 371   ANALYZE;
 372   SELECT * FROM sqlite_stat1 ORDER BY 1, 2;
 373 } {
 374   t1 t1_idx_00000061 {100 50}
 375   t1 t1_idx_00000062 {100 20}
 376   t1 t1_idx_000123a7 {100 50 17}
 377   t2 t2_idx_00000063 {100 20}
 378   t2 t2_idx_00000064 {100 5}
 379   t2 t2_idx_0001295b {100 20 5}
 380 }
 381
 382
 383 finish_test