AryaWu/sqlite

1# 2018-09-092#3# The author disclaims copyright to this source code.  In place of4# 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 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set testprefix bestindex616 17ifcapable !vtab {18  finish_test19  return20}21 22register_tcl_module db23 24proc vtab_command {src method args} {25  switch -- $method {26    xConnect {27      return [db one {SELECT sql FROM sqlite_master where name = $src}]28    }29 30    xBestIndex {31      set hdl [lindex $args 0]32      set clist [$hdl constraints]33      set orderby [$hdl orderby]34      set mask [$hdl mask]35 36      set wlist 137 38      set iCons 039      set ret [list]40      foreach cons $clist {41        catch { array unset C }42        array set C $cons43 44        if {$C(usable)} {45          set col [db one {46            SELECT name FROM pragma_table_info($src) WHERE cid=$C(column)47          }]48          switch $C(op) {49            isnull {50              lappend wlist "$col IS NULL"51              lappend ret omit $iCons52            }53            eq {54              lappend wlist "$col = %$iCons%"55              lappend ret omit $iCons56            }57          }58        }59        incr iCons60      }61      #puts "xBestIndex: $ret"62      lappend ret idxStr [join $wlist " AND "]63      return $ret64    }65 66    xFilter {67      foreach {idxnum idxstr aa} $args {}68      set map [list]69      for {set iCons 0} {$iCons < [llength $aa]} {incr iCons} {70        lappend map %$iCons% [lindex $aa $iCons]71      }72      set ret [list sql \73          "SELECT rowid, * FROM $src WHERE [string map $map $idxstr]"74      ]75      # puts "xFilter: $ret"76      return $ret77    }78 79  }80 81  return {}82}83 84do_execsql_test 1.0 {85  CREATE TABLE t1(id int, value text);86  CREATE TABLE t2(ctx int, id int, value text); 87 88  INSERT INTO t1 VALUES(1,'try');89  INSERT INTO t2 VALUES(1,1,'good');90  INSERT INTO t2 VALUES(2,2,'evil');91 92  CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1);93  CREATE VIRTUAL TABLE vt2 USING tcl(vtab_command t2);94}95 96do_execsql_test 1.1 {97  select * from t2 left join t1 on t1.id=t2.ctx where t1.value is null;98} {2 2 evil {} {}}99 100do_execsql_test 1.2 {101  select * from vt2 left join vt1 on vt1.id=vt2.ctx where vt1.value is null; 102} {2 2 evil {} {}}103 104unset -nocomplain xxx105do_execsql_test 1.3 {106  select * from vt2 left join vt1 on vt1.id=vt2.ctx where vt1.value is $xxx; 107} {2 2 evil {} {}}108 109do_execsql_test 1.4 {110  select * from t2 left join vt1 on vt1.id=t2.ctx where vt1.value = 3111} {}112 113finish_test114