1# 2017 September 10 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# Test the virtual table interface. In particular the xBestIndex 12# method. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix bestindex5 18 19ifcapable !vtab { 20 finish_test 21 return 22} 23 24#------------------------------------------------------------------------- 25# Virtual table callback for a virtual table named $tbl. 26# 27proc vtab_cmd {method args} { 28 29 set binops(ne) != 30 set binops(eq) = 31 set binops(isnot) "IS NOT" 32 set binops(is) "IS" 33 34 set unops(isnotnull) "IS NOT NULL" 35 set unops(isnull) "IS NULL" 36 37 set cols(0) a 38 set cols(1) b 39 set cols(2) c 40 41 switch -- $method { 42 xConnect { 43 return "CREATE TABLE t1(a, b, c)" 44 } 45 46 xBestIndex { 47 set hdl [lindex $args 0] 48 set clist [$hdl constraints] 49 set orderby [$hdl orderby] 50 set mask [$hdl mask] 51 52 set cost 1000000.0 53 set ret [list] 54 set str [list] 55 56 set v 0 57 for {set i 0} {$i < [llength $clist]} {incr i} { 58 array unset C 59 array set C [lindex $clist $i] 60 if {$C(usable)} { 61 if {[info exists binops($C(op))]} { 62 lappend ret omit $i 63 lappend str "$cols($C(column)) $binops($C(op)) %$v%" 64 incr v 65 set cost [expr $cost / 2] 66 } 67 if {[info exists unops($C(op))]} { 68 lappend ret omit $i 69 lappend str "$cols($C(column)) $unops($C(op))" 70 incr v 71 set cost [expr $cost / 2] 72 } 73 } 74 } 75 76 lappend ret idxstr [join $str " AND "] 77 lappend ret cost $cost 78 return $ret 79 } 80 81 xFilter { 82 set q [lindex $args 1] 83 set a [lindex $args 2] 84 for {set v 0} {$v < [llength $a]} {incr v} { 85 set val [lindex $a $v] 86 set q [string map [list %$v% '$val'] $q] 87 } 88 if {$q==""} { set q 1 } 89 lappend ::xFilterQueries "WHERE $q" 90 return [list sql "SELECT rowid, * FROM t1x WHERE $q"] 91 } 92 } 93 return "" 94} 95 96proc vtab_simple {method args} { 97 switch -- $method { 98 xConnect { 99 return "CREATE TABLE t2(x)" 100 } 101 xBestIndex { 102 return [list cost 999999.0] 103 } 104 xFilter { 105 return [list sql "SELECT rowid, * FROM t2x"] 106 } 107 } 108 return "" 109} 110 111register_tcl_module db 112 113proc do_vtab_query_test {tn query result} { 114 set ::xFilterQueries [list] 115 uplevel [list 116 do_test $tn [string map [list %QUERY% $query] { 117 set r [execsql {%QUERY%}] 118 set r [concat $::xFilterQueries $r] 119 set r 120 }] [list {*}$result] 121 ] 122} 123 124do_execsql_test 1.0 { 125 CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd'); 126 CREATE TABLE t1x(a INTEGER, b TEXT, c REAL); 127 INSERT INTO t1x VALUES(1, 2, 3); 128 INSERT INTO t1x VALUES(4, 5, 6); 129 INSERT INTO t1x VALUES(7, 8, 9); 130 131 CREATE VIRTUAL TABLE t2 USING tcl('vtab_simple'); 132 CREATE TABLE t2x(x INTEGER); 133 INSERT INTO t2x VALUES(1); 134} 135 136do_vtab_query_test 1.1 { SELECT * FROM t1 WHERE a!='hello'; } { 137 "WHERE a != 'hello'" 138 1 2 3.0 4 5 6.0 7 8 9.0 139} 140 141do_vtab_query_test 1.2.1 { SELECT * FROM t1 WHERE b!=8 } { 142 "WHERE b != '8'" 143 1 2 3.0 4 5 6.0 144} 145do_vtab_query_test 1.2.2 { SELECT * FROM t1 WHERE 8!=b } { 146 "WHERE b != '8'" 147 1 2 3.0 4 5 6.0 148} 149 150do_vtab_query_test 1.3 { SELECT * FROM t1 WHERE c IS NOT 3 } { 151 "WHERE c IS NOT '3'" 152 4 5 6.0 7 8 9.0 153} 154do_vtab_query_test 1.3.2 { SELECT * FROM t1 WHERE 3 IS NOT c } { 155 "WHERE c IS NOT '3'" 156 4 5 6.0 7 8 9.0 157} 158 159do_vtab_query_test 1.4.1 { SELECT * FROM t1, t2 WHERE x != a } { 160 "WHERE a != '1'" 161 4 5 6.0 1 7 8 9.0 1 162} 163do_vtab_query_test 1.4.2 { SELECT * FROM t1, t2 WHERE a != x } { 164 "WHERE a != '1'" 165 4 5 6.0 1 7 8 9.0 1 166} 167 168do_vtab_query_test 1.5.1 { SELECT * FROM t1 WHERE a IS NOT NULL } { 169 "WHERE a IS NOT NULL" 170 1 2 3.0 4 5 6.0 7 8 9.0 171} 172do_vtab_query_test 1.5.2 { SELECT * FROM t1 WHERE NULL IS NOT a } { 173 "WHERE a IS NOT ''" 174 1 2 3.0 4 5 6.0 7 8 9.0 175} 176 177do_vtab_query_test 1.6.1 { SELECT * FROM t1 WHERE a IS NULL } { 178 "WHERE a IS NULL" 179} 180 181do_vtab_query_test 1.6.2 { SELECT * FROM t1 WHERE NULL IS a } { 182 "WHERE a IS ''" 183} 184 185do_vtab_query_test 1.7.1 { SELECT * FROM t1 WHERE (a, b) IS (1, 2) } { 186 "WHERE a IS '1' AND b IS '2'" 187 1 2 3.0 188} 189do_vtab_query_test 1.7.2 { SELECT * FROM t1 WHERE (5, 4) IS (b, a) } { 190 {WHERE b IS '5' AND a IS '4'} 191 4 5 6.0 192} 193 194#--------------------------------------------------------------------- 195do_execsql_test 2.0.0 { 196 DELETE FROM t1x; 197 INSERT INTO t1x VALUES('a', 'b', 'c'); 198} 199do_execsql_test 2.0.1 { SELECT * FROM t1 } {a b c} 200do_execsql_test 2.0.2 { SELECT * FROM t1 WHERE (a, b) != ('a', 'b'); } {} 201 202do_execsql_test 2.1.0 { 203 DELETE FROM t1x; 204 INSERT INTO t1x VALUES(7, 8, 9); 205} 206do_execsql_test 2.1.1 { SELECT * FROM t1 } {7 8 9.0} 207do_execsql_test 2.1.2 { SELECT * FROM t1 WHERE (a, b) != (7, '8') } {} 208do_execsql_test 2.1.3 { SELECT * FROM t1 WHERE a!=7 OR b!='8' } 209do_execsql_test 2.1.4 { SELECT * FROM t1 WHERE a!=7 OR b!='8' } 210 211 212do_execsql_test 2.2.1 { 213 CREATE TABLE t3(a INTEGER, b TEXT); 214 INSERT INTO t3 VALUES(45, 46); 215} 216do_execsql_test 2.2.2 { SELECT * FROM t3 WHERE (a, b) != (45, 46); } 217do_execsql_test 2.2.3 { SELECT * FROM t3 WHERE (a, b) != ('45', '46'); } 218do_execsql_test 2.2.4 { SELECT * FROM t3 WHERE (a, b) == (45, 46); } {45 46} 219do_execsql_test 2.2.5 { SELECT * FROM t3 WHERE (a, b) == ('45', '46'); } {45 46} 220 221#--------------------------------------------------------------------- 222# Test the != operator on a virtual table with column affinities. 223# 224proc vtab_simple_integer {method args} { 225 switch -- $method { 226 xConnect { 227 return "CREATE TABLE t4(x INTEGER)" 228 } 229 xBestIndex { 230 return [list cost 999999.0] 231 } 232 xFilter { 233 return [list sql "SELECT rowid, * FROM t4x"] 234 } 235 } 236 return "" 237} 238 239do_execsql_test 3.0 { 240 CREATE TABLE t4x(a INTEGER); 241 INSERT INTO t4x VALUES(245); 242 CREATE VIRTUAL TABLE t4 USING tcl('vtab_simple_integer'); 243} 244do_execsql_test 3.1 { SELECT rowid, * FROM t4 WHERE x=245; } {1 245} 245do_execsql_test 3.2 { SELECT rowid, * FROM t4 WHERE x='245'; } {1 245} 246do_execsql_test 3.3 { SELECT rowid, * FROM t4 WHERE x!=245; } {} 247do_execsql_test 3.4 { SELECT rowid, * FROM t4 WHERE x!='245'; } {} 248 249do_execsql_test 3.5 { SELECT rowid, * FROM t4 WHERE rowid!=1 OR x!='245'; } {} 250 251 252finish_test 253