AryaWu/sqlite

1# 2017 September 102#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# Test the virtual table interface. In particular the xBestIndex12# method.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix bestindex518 19ifcapable !vtab {20  finish_test21  return22}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) a38  set cols(1) b39  set cols(2) c40 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.053      set ret [list]54      set str [list]55 56      set v 057      for {set i 0} {$i < [llength $clist]} {incr i} {58        array unset C59        array set C [lindex $clist $i]60        if {$C(usable)} {61          if {[info exists binops($C(op))]} {62            lappend ret omit $i63            lappend str "$cols($C(column)) $binops($C(op)) %$v%"64            incr v65            set cost [expr $cost / 2]66          }67          if {[info exists unops($C(op))]} {68            lappend ret omit $i69            lappend str "$cols($C(column)) $unops($C(op))"70            incr v71            set cost [expr $cost / 2]72          }73        }74      }75 76      lappend ret idxstr [join $str " AND "]77      lappend ret cost $cost78      return $ret79    }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 db112 113proc do_vtab_query_test {tn query result} {114  set ::xFilterQueries [list]115  uplevel [list116    do_test $tn [string map [list %QUERY% $query] {117      set r [execsql {%QUERY%}]118      set r [concat $::xFilterQueries $r]119      set r120    }] [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.0139}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.0144}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.0148}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.0153}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.0157}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 1162}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 1166}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.0171}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.0175}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.0188}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.0192}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_test253