# 2016-03-01 # # 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. # #*********************************************************************** # # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix bestindex1 ifcapable !vtab { finish_test return } register_tcl_module db proc vtab_command {method args} { switch -- $method { xConnect { return "CREATE TABLE t1(a, b, c)" } xBestIndex { set clist [lindex $args 0] if {[llength $clist]!=1} { error "unexpected constraint list" } catch { array unset C } array set C [lindex $clist 0] if {$C(usable)} { return "omit 0 cost 0 rows 1 idxnum 555 idxstr eq!" } else { return "cost 1000000 rows 0 idxnum 0 idxstr scan..." } } } return {} } do_execsql_test 1.0 { CREATE VIRTUAL TABLE x1 USING tcl(vtab_command); } {} do_eqp_test 1.1 { SELECT * FROM x1 WHERE a = 'abc' } { 0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!} } do_eqp_test 1.2 { SELECT * FROM x1 WHERE a IN ('abc', 'def'); } { 0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!} 0 0 0 {EXECUTE LIST SUBQUERY 1} } #------------------------------------------------------------------------- # reset_db register_tcl_module db # Parameter $mode may be one of: # # "omit" - Implement filtering. Set the omit flag. # "use" - Implement filtering. Use the constraint, but do not set omit. # "use2" - Do not implement filtering. Use the constraint anyway. # # proc t1_vtab {mode method args} { switch -- $method { xConnect { return "CREATE TABLE t1(a, b)" } xBestIndex { set SQL_FILTER {SELECT * FROM t1x WHERE a='%1%'} set SQL_SCAN {SELECT * FROM t1x} set clist [lindex $args 0] set idx 0 for {set idx 0} {$idx < [llength $clist]} {incr idx} { array unset C array set C [lindex $clist $idx] if {$C(column)==0 && $C(op)=="eq" && $C(usable)} { switch -- $mode { "omit" { return [list omit $idx rows 10 cost 10 idxstr $SQL_FILTER] } "use" { return [list use $idx rows 10 cost 10 idxstr $SQL_FILTER] } "use2" { return [list use $idx rows 10 cost 10 idxstr $SQL_SCAN] } default { error "Bad mode - $mode" } } } } return [list idxstr {SELECT * FROM t1x}] } xFilter { set map [list %1% [lindex $args 2 0]] set sql [string map $map [lindex $args 1]] return [list sql $sql] } } return {} } do_execsql_test 2.1 { CREATE TABLE t1x(i INTEGER PRIMARY KEY, a, b); INSERT INTO t1x VALUES(1, 'one', 1); INSERT INTO t1x VALUES(2, 'two', 2); INSERT INTO t1x VALUES(3, 'three', 3); INSERT INTO t1x VALUES(4, 'four', 4); } foreach {tn mode} { 1 use 2 omit 3 use2 } { do_execsql_test 2.2.$mode.1 " DROP TABLE IF EXISTS t1; CREATE VIRTUAL TABLE t1 USING tcl(t1_vtab $mode); " do_execsql_test 2.2.$mode.2 {SELECT * FROM t1} {one 1 two 2 three 3 four 4} do_execsql_test 2.2.$mode.3 {SELECT rowid FROM t1} {1 2 3 4} do_execsql_test 2.2.$mode.4 {SELECT rowid FROM t1 WHERE a='two'} {2} do_execsql_test 2.2.$mode.5 { SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid } {1 4} set plan(use) { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'} 0 0 0 {EXECUTE LIST SUBQUERY 1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } set plan(omit) { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'} 0 0 0 {EXECUTE LIST SUBQUERY 1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } set plan(use2) { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x} 0 0 0 {EXECUTE LIST SUBQUERY 1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 2.2.$mode.6 { SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid } $plan($mode) } finish_test