SQLite

#define SQLITE_INDEX_CONSTRAINT_LE      8
#define SQLITE_INDEX_CONSTRAINT_LT     16
#define SQLITE_INDEX_CONSTRAINT_GE     32
#define SQLITE_INDEX_CONSTRAINT_MATCH  64
#define SQLITE_INDEX_CONSTRAINT_LIKE   65
#define SQLITE_INDEX_CONSTRAINT_GLOB   66
#define SQLITE_INDEX_CONSTRAINT_REGEXP 67
#define SQLITE_INDEX_CONSTRAINT_NE        68
#define SQLITE_INDEX_CONSTRAINT_ISNOT     69
#define SQLITE_INDEX_CONSTRAINT_ISNOTNULL 70
#define SQLITE_INDEX_CONSTRAINT_ISNULL    71
#define SQLITE_INDEX_CONSTRAINT_IS        72

/*
** CAPI3REF: Register A Virtual Table Implementation
** METHOD: sqlite3
**
** ^These routines are used to register a new [virtual table module] name.
** ^Module names must be registered before

        case SQLITE_INDEX_CONSTRAINT_LIKE:
          zOp = "like"; break;
        case SQLITE_INDEX_CONSTRAINT_GLOB:
          zOp = "glob"; break;
        case SQLITE_INDEX_CONSTRAINT_REGEXP:
          zOp = "regexp"; break;
      }
      if( zOp ){
        if( zOp[0]=='L' ){
          zNew = sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')", 
              zSep, zNewCol);
        } else {
          zNew = sqlite3_mprintf(" %s %s %s ?", zSep, zNewCol, zOp);
        }
        string_concat(&zQuery, zNew, 1, &rc);

        zSep = "AND";
        pUsage->argvIndex = ++nArg;
        pUsage->omit = 1;
      }
    }
  }

  /* If there is only one term in the ORDER BY clause, and it is
  ** on a column that this virtual table has an index for, then consume 
  ** the ORDER BY clause.
  */

        zOp = "match"; break;
      case SQLITE_INDEX_CONSTRAINT_LIKE:
        zOp = "like"; break;
      case SQLITE_INDEX_CONSTRAINT_GLOB:
        zOp = "glob"; break;
      case SQLITE_INDEX_CONSTRAINT_REGEXP:
        zOp = "regexp"; break;
      case SQLITE_INDEX_CONSTRAINT_NE:
        zOp = "ne"; break;
      case SQLITE_INDEX_CONSTRAINT_ISNOT:
        zOp = "isnot"; break;
      case SQLITE_INDEX_CONSTRAINT_ISNOTNULL:
        zOp = "isnotnull"; break;
      case SQLITE_INDEX_CONSTRAINT_ISNULL:
        zOp = "isnull"; break;
      case SQLITE_INDEX_CONSTRAINT_IS:
        zOp = "is"; break;
    }

    Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("op", -1));
    Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj(zOp, -1));
    Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("column", -1));
    Tcl_ListObjAppendElement(0, pElem, Tcl_NewIntObj(pCons->iColumn));
    Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("usable", -1));

    if( pTerm->leftCursor != pSrc->iCursor ) continue;
    if( pTerm->prereqRight & mUnusable ) continue;
    assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
    testcase( pTerm->eOperator & WO_IN );
    testcase( pTerm->eOperator & WO_ISNULL );
    testcase( pTerm->eOperator & WO_IS );
    testcase( pTerm->eOperator & WO_ALL );
    if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue;
    if( pTerm->wtFlags & TERM_VNULL ) continue;
    assert( pTerm->u.leftColumn>=(-1) );
    nTerm++;
  }

  /* If the ORDER BY clause contains only columns in the current 
  ** virtual table then allocate space for the aOrderBy part of

  *(int*)&pIdxInfo->nOrderBy = nOrderBy;
  *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint = pIdxCons;
  *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy;
  *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage =
                                                                   pUsage;

  for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
    u16 op;
    if( pTerm->leftCursor != pSrc->iCursor ) continue;
    if( pTerm->prereqRight & mUnusable ) continue;
    assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
    testcase( pTerm->eOperator & WO_IN );
    testcase( pTerm->eOperator & WO_IS );
    testcase( pTerm->eOperator & WO_ISNULL );
    testcase( pTerm->eOperator & WO_ALL );
    if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue;
    if( pTerm->wtFlags & TERM_VNULL ) continue;
    assert( pTerm->u.leftColumn>=(-1) );
    pIdxCons[j].iColumn = pTerm->u.leftColumn;
    pIdxCons[j].iTermOffset = i;
    op = pTerm->eOperator & WO_ALL;
    if( op==WO_IN ) op = WO_EQ;
    if( op==WO_MATCH ){
      pIdxCons[j].op = pTerm->eMatchOp;
    }else if( op & (WO_ISNULL|WO_IS) ){
      if( op==WO_ISNULL ){
        pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_ISNULL;
      }else{
        pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_IS;
      }
    }else{
      pIdxCons[j].op = (u8)op;
      /* The direct assignment in the previous line is possible only because
      ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical.  The
      ** following asserts verify this fact. */
      assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ );
      assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT );
      assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE );
      assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT );
      assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE );
      assert( WO_MATCH==SQLITE_INDEX_CONSTRAINT_MATCH );
      assert( pTerm->eOperator&(WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_MATCH) );

      if( op & (WO_LT|WO_LE|WO_GT|WO_GE)
          && sqlite3ExprIsVector(pTerm->pExpr->pRight) 
        ){
        if( i<16 ) mNoOmit |= (1 << i);
        if( op==WO_LT ) pIdxCons[j].op = WO_LE;
        if( op==WO_GT ) pIdxCons[j].op = WO_GE;
      }
    }

    j++;
  }
  for(i=0; i<nOrderBy; i++){
    Expr *pExpr = pOrderBy->a[i].pExpr;
    pIdxOrderBy[i].iColumn = pExpr->iColumn;

**
** If the expression is not a vector, then nReg must be passed 1. In
** this case, generate code to evaluate the expression and leave the
** result in register iReg.
*/
static void codeExprOrVector(Parse *pParse, Expr *p, int iReg, int nReg){
  assert( nReg>0 );
  if( p && sqlite3ExprIsVector(p) ){
#ifndef SQLITE_OMIT_SUBQUERY
    if( (p->flags & EP_xIsSelect) ){
      Vdbe *v = pParse->pVdbe;
      int iSelect = sqlite3CodeSubselect(pParse, p, 0, 0);
      sqlite3VdbeAddOp3(v, OP_Copy, iSelect, iReg, nReg-1);
    }else
#endif

#ifndef SQLITE_OMIT_VIRTUALTABLE
/*
** Check to see if the given expression is of the form
**
**         column OP expr
**
** where OP is one of MATCH, GLOB, LIKE or REGEXP and "column" is a 
** column of a virtual table. If so, set *ppLeft to point to the
** expression for "column", *ppRight to "expr" and return 1.
**
** Also check if the expression is one of:
**
**         column != expr
**         column IS NOT expr
**         column IS NOT NULL
**
** where "column" is a column of a virtual table. If so, set *ppLeft
** to point to "column", *ppRight to "expr" and return 1. Or, if "expr"
** is also a column of a virtual table, return 2.
**
** If the expression matches none of the patterns above, return 0.
*/
static int isMatchOfColumn(
  Expr *pExpr,                    /* Test this expression */
  unsigned char *peOp2,           /* OUT: 0 for MATCH, or else an op2 value */
  Expr **ppLeft,                  /* Column expression to left of MATCH/op2 */
  Expr **ppRight                  /* Expression to left of MATCH/op2 */
){
  if( pExpr->op==TK_FUNCTION ){
    static const struct Op2 {
      const char *zOp;
      unsigned char eOp2;
    } aOp[] = {
      { "match",  SQLITE_INDEX_CONSTRAINT_MATCH },
      { "glob",   SQLITE_INDEX_CONSTRAINT_GLOB },
      { "like",   SQLITE_INDEX_CONSTRAINT_LIKE },
      { "regexp", SQLITE_INDEX_CONSTRAINT_REGEXP }
    };
    ExprList *pList;
    Expr *pCol;                     /* Column reference */
    int i;

    if( pExpr->op!=TK_FUNCTION ){
      return 0;
    }
    pList = pExpr->x.pList;
    if( pList==0 || pList->nExpr!=2 ){
      return 0;
    }
    pCol = pList->a[1].pExpr;
    if( pCol->op!=TK_COLUMN || !IsVirtual(pCol->pTab) ){
      return 0;
    }
    for(i=0; i<ArraySize(aOp); i++){
      if( sqlite3StrICmp(pExpr->u.zToken, aOp[i].zOp)==0 ){
        *peOp2 = aOp[i].eOp2;
        *ppRight = pList->a[0].pExpr;
        *ppLeft = pCol;
        return 1;
      }
    }
  }else if( pExpr->op==TK_NE || pExpr->op==TK_ISNOT || pExpr->op==TK_NOTNULL ){
    int res = 0;
    Expr *pLeft = pExpr->pLeft;
    Expr *pRight = pExpr->pRight;
    if( pLeft->op==TK_COLUMN && IsVirtual(pLeft->pTab) ){
      res++;
    }
    if( pRight && pRight->op==TK_COLUMN && IsVirtual(pRight->pTab) ){
      res++;
      SWAP(Expr*, pLeft, pRight);
    }
    *ppLeft = pLeft;
    *ppRight = pRight;
    if( pExpr->op==TK_NE ) *peOp2 = SQLITE_INDEX_CONSTRAINT_NE;
    if( pExpr->op==TK_ISNOT ) *peOp2 = SQLITE_INDEX_CONSTRAINT_ISNOT;
    if( pExpr->op==TK_NOTNULL ) *peOp2 = SQLITE_INDEX_CONSTRAINT_ISNOTNULL;
    return res;
  }
  return 0;
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */

/*
** If the pBase expression originated in the ON or USING clause of

#ifndef SQLITE_OMIT_VIRTUALTABLE
  /* Add a WO_MATCH auxiliary term to the constraint set if the
  ** current expression is of the form:  column MATCH expr.
  ** This information is used by the xBestIndex methods of
  ** virtual tables.  The native query optimizer does not attempt
  ** to do anything with MATCH functions.
  */
  if( pWC->op==TK_AND ){

    Expr *pRight, *pLeft;
    int i;
    int res = isMatchOfColumn(pExpr, &eOp2, &pLeft, &pRight);
    for(i=0; i<res; i++){
      int idxNew;
      WhereTerm *pNewTerm;
      Bitmask prereqColumn, prereqExpr;



      prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight);
      prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft);
      if( (prereqExpr & prereqColumn)==0 ){
        Expr *pNewExpr;
        pNewExpr = sqlite3PExpr(pParse, TK_MATCH, 
            0, sqlite3ExprDup(db, pRight, 0));
        if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){
          ExprSetProperty(pNewExpr, EP_FromJoin);
        }
        idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
        testcase( idxNew==0 );
        pNewTerm = &pWC->a[idxNew];
        pNewTerm->prereqRight = prereqExpr;
        pNewTerm->leftCursor = pLeft->iTable;
        pNewTerm->u.leftColumn = pLeft->iColumn;
        pNewTerm->eOperator = WO_MATCH;
        pNewTerm->eMatchOp = eOp2;
        markTermAsChild(pWC, idxNew, idxTerm);
        pTerm = &pWC->a[idxTerm];
        pTerm->wtFlags |= TERM_COPIED;
        pNewTerm->prereqAll = pTerm->prereqAll;
      }
      SWAP(Expr*, pLeft, pRight);
    }
  }
#endif /* SQLITE_OMIT_VIRTUALTABLE */

  /* If there is a vector == or IS term - e.g. "(a, b) == (?, ?)" - create
  ** new terms for each component comparison - "a = ?" and "b = ?".  The
  ** new terms completely replace the original vector comparison, which is

# 2017 September 10
#
# 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.
#
#***********************************************************************
# Test the virtual table interface. In particular the xBestIndex
# method.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix bestindex4

ifcapable !vtab {
  finish_test
  return
}

#-------------------------------------------------------------------------
# Virtual table callback for a virtual table named $tbl.
#  
proc vtab_cmd {method args} {

  set binops(ne)    !=
  set binops(eq)    =
  set binops(isnot) "IS NOT"
  set binops(is)    "IS"

  set unops(isnotnull) "IS NOT NULL"
  set unops(isnull)    "IS NULL"

  set cols(0) a
  set cols(1) b
  set cols(2) c

  switch -- $method {
    xConnect {
      return "CREATE TABLE t1(a, b, c)"
    }

    xBestIndex {
      foreach {clist orderby mask} $args {}

      set cost 1000000.0
      set ret [list]
      set str [list]

      set v 0
      for {set i 0} {$i < [llength $clist]} {incr i} {
        array unset C
        array set C [lindex $clist $i]
        if {$C(usable)} {
          if {[info exists binops($C(op))]} {
            lappend ret omit $i
            lappend str "$cols($C(column)) $binops($C(op)) %$v%"
            incr v
            set cost [expr $cost / 2]
          }
          if {[info exists unops($C(op))]} {
            lappend ret omit $i
            lappend str "$cols($C(column)) $unops($C(op))"
            incr v
            set cost [expr $cost / 2]
          }
        }
      }

      lappend ret idxstr [join $str " AND "]
      lappend ret cost $cost
      return $ret
    }

    xFilter {
      set q [lindex $args 1]
      set a [lindex $args 2]
      for {set v 0} {$v < [llength $a]} {incr v} {
        set val [lindex $a $v]
        set q [string map [list %$v% '$val'] $q]
      }
      if {$q==""} { set q 1 }
      lappend ::xFilterQueries "WHERE $q"
      return [list sql "SELECT rowid, * FROM t1x WHERE $q"]
    }
  }
  return ""
}

proc vtab_simple {method args} {
  switch -- $method {
    xConnect {
      return "CREATE TABLE t2(x)"
    }
    xBestIndex {
      return [list cost 999999.0]
    }
    xFilter {
      return [list sql "SELECT rowid, * FROM t2x"]
    }
  }
  return ""
}

register_tcl_module db

proc do_vtab_query_test {tn query result} {
  set ::xFilterQueries [list]
  uplevel [list
    do_test $tn [string map [list %QUERY% $query] {
      set r [execsql {%QUERY%}]
      set r [concat $::xFilterQueries $r]
      set r
    }] [list {*}$result]
  ]
}

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd');
  CREATE TABLE t1x(a INTEGER, b TEXT, c REAL);
  INSERT INTO t1x VALUES(1, 2, 3);
  INSERT INTO t1x VALUES(4, 5, 6);
  INSERT INTO t1x VALUES(7, 8, 9);

  CREATE VIRTUAL TABLE t2 USING tcl('vtab_simple');
  CREATE TABLE t2x(x INTEGER);
  INSERT INTO t2x VALUES(1);
}

do_vtab_query_test 1.1 { SELECT * FROM t1 WHERE a!='hello'; } {
  "WHERE a != 'hello'"
  1 2 3.0 4 5 6.0 7 8 9.0
}

do_vtab_query_test 1.2.1 { SELECT * FROM t1 WHERE b!=8 } {
  "WHERE b != '8'"
  1 2 3.0 4 5 6.0
}
do_vtab_query_test 1.2.2 { SELECT * FROM t1 WHERE 8!=b } {
  "WHERE b != '8'"
  1 2 3.0 4 5 6.0
}

do_vtab_query_test 1.3 { SELECT * FROM t1 WHERE c IS NOT 3 } {
  "WHERE c IS NOT '3'"
  4 5 6.0 7 8 9.0
}
do_vtab_query_test 1.3.2 { SELECT * FROM t1 WHERE 3 IS NOT c } {
  "WHERE c IS NOT '3'"
  4 5 6.0 7 8 9.0
}

do_vtab_query_test 1.4.1 { SELECT * FROM t1, t2 WHERE x != a } {
  "WHERE a != '1'"
  4 5 6.0 1   7 8 9.0 1
}
do_vtab_query_test 1.4.2 { SELECT * FROM t1, t2 WHERE a != x } {
  "WHERE a != '1'"
  4 5 6.0 1   7 8 9.0 1
}

do_vtab_query_test 1.5.1 { SELECT * FROM t1 WHERE a IS NOT NULL } {
  "WHERE a IS NOT NULL"
  1 2 3.0 4 5 6.0 7 8 9.0
}
do_vtab_query_test 1.5.2 { SELECT * FROM t1 WHERE NULL IS NOT a } {
  "WHERE a IS NOT ''"
  1 2 3.0 4 5 6.0 7 8 9.0
}

do_vtab_query_test 1.6.1 { SELECT * FROM t1 WHERE a IS NULL } {
  "WHERE a IS NULL"
}

do_vtab_query_test 1.6.2 { SELECT * FROM t1 WHERE NULL IS a } {
  "WHERE a IS ''"
}

do_vtab_query_test 1.7.1 { SELECT * FROM t1 WHERE (a, b) IS (1, 2) } {
  "WHERE a IS '1' AND b IS '2'"
  1 2 3.0
}
do_vtab_query_test 1.7.2 { SELECT * FROM t1 WHERE (5, 4) IS (b, a) } {
  {WHERE b IS '5' AND a IS '4'} 
  4 5 6.0
}

finish_test