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# 20if {![info exists testdir]} { 21 set testdir [file join [file dirname [info script]] .. .. test] 22} 23source $testdir/tester.tcl 24set testprefix expert1 25 26if {[info commands sqlite3_expert_new]==""} { 27 finish_test 28 return 29} 30 31 32set CLI [test_binary_name sqlite3] 33set CMD [test_binary_name sqlite3_expert] 34 35proc squish {txt} { 36 regsub -all {[[:space:]]+} $txt { } 37} 38 39proc do_setup_rec_test {tn setup sql res} { 40 reset_db 41 if {[info exists ::set_main_db_name]} { 42 dbconfig_maindbname_icecube db 43 } 44 db eval $setup 45 uplevel [list do_rec_test $tn $sql $res] 46} 47 48foreach {tn setup} { 49 1 { 50 if {![file executable $CMD]} { continue } 51 52 proc do_rec_test {tn sql res} { 53 set res [squish [string trim $res]] 54 set tst [subst -nocommands { 55 squish [string trim [exec $::CMD -verbose 0 -sql {$sql;} test.db]] 56 }] 57 uplevel [list do_test $tn $tst $res] 58 } 59 } 60 2 { 61 if {[info commands sqlite3_expert_new]==""} { continue } 62 63 proc do_rec_test {tn sql res} { 64 set expert [sqlite3_expert_new db] 65 $expert sql $sql 66 $expert analyze 67 68 set result [list] 69 for {set i 0} {$i < [$expert count]} {incr i} { 70 set idx [string trim [$expert report $i indexes]] 71 if {$idx==""} {set idx "(no new indexes)"} 72 lappend result $idx 73 lappend result [string trim [$expert report $i plan]] 74 } 75 76 $expert destroy 77 78 set tst [subst -nocommands {set {} [squish [join {$result}]]}] 79 uplevel [list do_test $tn $tst [string trim [squish $res]]] 80 } 81 } 82 3 { 83 if {[info commands sqlite3_expert_new]==""} { continue } 84 set ::set_main_db_name 1 85 } 86 4 { 87 if {![file executable $CLI]} { continue } 88 89 proc do_rec_test {tn sql res} { 90 set res [squish [string trim $res]] 91 set tst [subst -nocommands { 92 squish [string trim [exec $::CLI test.db ".expert" {$sql;}]] 93 }] 94 uplevel [list do_test $tn $tst $res] 95 } 96 } 97} { 98 99 eval $setup 100 101 102do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } { 103 SELECT * FROM t1 104} { 105 (no new indexes) 106 SCAN TABLE t1 107} 108 109do_setup_rec_test $tn.2 { 110 CREATE TABLE t1(a, b, c); 111} { 112 SELECT * FROM t1 WHERE b>?; 113} { 114 CREATE INDEX t1_idx_00000062 ON t1(b); 115 SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?) 116} 117 118do_setup_rec_test $tn.3 { 119 CREATE TABLE t1(a, b, c); 120} { 121 SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ? 122} { 123 CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE); 124 SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?) 125} 126 127do_setup_rec_test $tn.4 { 128 CREATE TABLE t1(a, b, c); 129} { 130 SELECT a FROM t1 ORDER BY b; 131} { 132 CREATE INDEX t1_idx_00000062 ON t1(b); 133 SCAN TABLE t1 USING INDEX t1_idx_00000062 134} 135 136do_setup_rec_test $tn.5 { 137 CREATE TABLE t1(a, b, c); 138} { 139 SELECT a FROM t1 WHERE a=? ORDER BY b; 140} { 141 CREATE INDEX t1_idx_000123a7 ON t1(a, b); 142 SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?) 143} 144 145if 0 { 146do_setup_rec_test $tn.6 { 147 CREATE TABLE t1(a, b, c); 148} { 149 SELECT min(a) FROM t1 150} { 151 CREATE INDEX t1_idx_00000061 ON t1(a); 152 SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061 153} 154} 155 156do_setup_rec_test $tn.7 { 157 CREATE TABLE t1(a, b, c); 158} { 159 SELECT * FROM t1 ORDER BY a, b, c; 160} { 161 CREATE INDEX t1_idx_033e95fe ON t1(a, b, c); 162 SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe 163} 164 165#do_setup_rec_test $tn.1.8 { 166# CREATE TABLE t1(a, b, c); 167#} { 168# SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC; 169#} { 170# CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c); 171# 0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222 172#} 173 174do_setup_rec_test $tn.8.1 { 175 CREATE TABLE t1(a COLLATE NOCase, b, c); 176} { 177 SELECT * FROM t1 WHERE a=? 178} { 179 CREATE INDEX t1_idx_00000061 ON t1(a); 180 SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?) 181} 182do_setup_rec_test $tn.8.2 { 183 CREATE TABLE t1(a, b COLLATE nocase, c); 184} { 185 SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC; 186} { 187 CREATE INDEX t1_idx_5cb97285 ON t1(a, b DESC, c); 188 SCAN TABLE t1 USING COVERING INDEX t1_idx_5cb97285 189} 190 191 192# Tables with names that require quotes. 193# 194do_setup_rec_test $tn.9.1 { 195 CREATE TABLE "t t"(a, b, c); 196} { 197 SELECT * FROM "t t" WHERE a=? 198} { 199 CREATE INDEX 't t_idx_00000061' ON 't t'(a); 200 SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?) 201} 202 203do_setup_rec_test $tn.9.2 { 204 CREATE TABLE "t t"(a, b, c); 205} { 206 SELECT * FROM "t t" WHERE b BETWEEN ? AND ? 207} { 208 CREATE INDEX 't t_idx_00000062' ON 't t'(b); 209 SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?) 210} 211 212# Columns with names that require quotes. 213# 214do_setup_rec_test $tn.10.1 { 215 CREATE TABLE t3(a, "b b", c); 216} { 217 SELECT * FROM t3 WHERE "b b" = ? 218} { 219 CREATE INDEX t3_idx_00050c52 ON t3('b b'); 220 SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?) 221} 222 223do_setup_rec_test $tn.10.2 { 224 CREATE TABLE t3(a, "b b", c); 225} { 226 SELECT * FROM t3 ORDER BY "b b" 227} { 228 CREATE INDEX t3_idx_00050c52 ON t3('b b'); 229 SCAN TABLE t3 USING INDEX t3_idx_00050c52 230} 231 232# Transitive constraints 233# 234do_setup_rec_test $tn.11.1 { 235 CREATE TABLE t5(a, b); 236 CREATE TABLE t6(c, d); 237} { 238 SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=? 239} { 240 CREATE INDEX t5_idx_000123a7 ON t5(a, b); 241 CREATE INDEX t6_idx_00000063 ON t6(c); 242 SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) 243 SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?) 244} 245 246# OR terms. 247# 248do_setup_rec_test $tn.12.1 { 249 CREATE TABLE t7(a, b); 250} { 251 SELECT * FROM t7 WHERE a=? OR b=? 252} { 253 CREATE INDEX t7_idx_00000062 ON t7(b); 254 CREATE INDEX t7_idx_00000061 ON t7(a); 255 MULTI-INDEX OR 256 INDEX 1 257 SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) 258 INDEX 2 259 SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?) 260} 261 262# rowid terms. 263# 264do_setup_rec_test $tn.13.1 { 265 CREATE TABLE t8(a, b); 266} { 267 SELECT * FROM t8 WHERE rowid=? 268} { 269 (no new indexes) 270 SEARCH TABLE t8 USING INTEGER PRIMARY KEY (rowid=?) 271} 272do_setup_rec_test $tn.13.2 { 273 CREATE TABLE t8(a, b); 274} { 275 SELECT * FROM t8 ORDER BY rowid 276} { 277 (no new indexes) 278 SCAN TABLE t8 279} 280do_setup_rec_test $tn.13.3 { 281 CREATE TABLE t8(a, b); 282} { 283 SELECT * FROM t8 WHERE a=? ORDER BY rowid 284} { 285 CREATE INDEX t8_idx_00000061 ON t8(a); 286 SEARCH TABLE t8 USING INDEX t8_idx_00000061 (a=?) 287} 288 289# Triggers 290# 291do_setup_rec_test $tn.14 { 292 CREATE TABLE t9(a, b, c); 293 CREATE TABLE t10(a, b, c); 294 CREATE TRIGGER t9t AFTER INSERT ON t9 BEGIN 295 UPDATE t10 SET a=new.a WHERE b = new.b; 296 END; 297} { 298 INSERT INTO t9 VALUES(?, ?, ?); 299} { 300 CREATE INDEX t10_idx_00000062 ON t10(b); 301 SEARCH TABLE t10 USING INDEX t10_idx_00000062 (b=?) 302} 303 304do_setup_rec_test $tn.15 { 305 CREATE TABLE t1(a, b); 306 CREATE TABLE t2(c, d); 307 308 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) 309 INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s; 310 311 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) 312 INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s; 313} { 314 SELECT * FROM t2, t1 WHERE b=? AND d=? AND t2.rowid=t1.rowid 315} { 316 CREATE INDEX t2_idx_00000064 ON t2(d); 317 SEARCH TABLE t2 USING INDEX t2_idx_00000064 (d=?) 318 SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) 319} 320 321do_setup_rec_test $tn.16 { 322 CREATE TABLE t1(a, b); 323} { 324 SELECT * FROM t1 WHERE b IS NOT NULL; 325} { 326 (no new indexes) 327 SCAN TABLE t1 328} 329 330do_setup_rec_test $tn.17.1 { 331 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B)); 332} { 333 SELECT * FROM example WHERE a=? 334} { 335 (no new indexes) 336 SEARCH TABLE example USING INDEX sqlite_autoindex_example_1 (A=?) 337} 338do_setup_rec_test $tn.17.2 { 339 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B)); 340} { 341 SELECT * FROM example WHERE b=? 342} { 343 CREATE INDEX example_idx_00000042 ON example(B); 344 SEARCH TABLE example USING INDEX example_idx_00000042 (B=?) 345} 346do_setup_rec_test $tn.17.3 { 347 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B)); 348} { 349 SELECT * FROM example WHERE a=? AND b=? 350} { 351 (no new indexes) 352 SEARCH TABLE example USING INDEX sqlite_autoindex_example_1 (A=? AND B=?) 353} 354do_setup_rec_test $tn.17.4 { 355 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B)); 356} { 357 SELECT * FROM example WHERE a=? AND b>? 358} { 359 (no new indexes) 360 SEARCH TABLE example USING INDEX sqlite_autoindex_example_1 (A=? AND B>?) 361} 362do_setup_rec_test $tn.17.5 { 363 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B)); 364} { 365 SELECT * FROM example WHERE a>? AND b=? 366} { 367 CREATE INDEX example_idx_0000cb3f ON example(B, A); 368 SEARCH TABLE example USING INDEX example_idx_0000cb3f (B=? AND A>?) 369} 370 371do_setup_rec_test $tn.18.0 { 372 CREATE TABLE SomeObject ( 373 a INTEGER PRIMARY KEY, 374 x TEXT GENERATED ALWAYS AS(HEX(a)) VIRTUAL 375 ); 376} { 377 SELECT x FROM SomeObject; 378} { 379 (no new indexes) 380 SCAN TABLE SomeObject 381} 382do_setup_rec_test $tn.18.1 { 383 CREATE TABLE SomeObject ( 384 a INTEGER PRIMARY KEY, 385 x TEXT GENERATED ALWAYS AS(HEX(a)) VIRTUAL 386 ); 387} { 388 SELECT * FROM SomeObject WHERE x=?; 389} { 390 CREATE INDEX SomeObject_idx_00000078 ON SomeObject(x); 391 SEARCH TABLE SomeObject USING COVERING INDEX SomeObject_idx_00000078 (x=?) 392} 393 394} 395 396proc do_candidates_test {tn sql res} { 397 set res [squish [string trim $res]] 398 399 set expert [sqlite3_expert_new db] 400 $expert sql $sql 401 $expert analyze 402 403 set candidates [squish [string trim [$expert report 0 candidates]]] 404 $expert destroy 405 406 uplevel [list do_test $tn [list set {} $candidates] $res] 407} 408 409 410reset_db 411do_execsql_test 5.0 { 412 CREATE TABLE t1(a, b); 413 CREATE TABLE t2(c, d); 414 415 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) 416 INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s; 417 418 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) 419 INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s; 420} 421do_candidates_test 5.1 { 422 SELECT * FROM t1,t2 WHERE (b=? OR a=?) AND (c=? OR d=?) 423} { 424 CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20 425 CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50 426 CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20 427 CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5 428} 429 430do_candidates_test 5.2 { 431 SELECT * FROM t1,t2 WHERE a=? AND b=? AND c=? AND d=? 432} { 433 CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 17 434 CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5 435} 436 437do_execsql_test 5.3 { 438 CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50 439 CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20 440 CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 16 441 442 CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20 443 CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5 444 CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5 445 446 ANALYZE; 447 SELECT * FROM sqlite_stat1 ORDER BY 1, 2; 448} { 449 t1 t1_idx_00000061 {100 50} 450 t1 t1_idx_00000062 {100 20} 451 t1 t1_idx_000123a7 {100 50 17} 452 t2 t2_idx_00000063 {100 20} 453 t2 t2_idx_00000064 {100 5} 454 t2 t2_idx_0001295b {100 20 5} 455} 456 457finish_test 458 459