SQLite

** CAPI3REF: Virtual Table Constraint Operator Codes
**
** These macros defined the allowed values for the
** [sqlite3_index_info].aConstraint[].op field.  Each value represents
** an operator that is part of a constraint term in the wHERE clause of
** a query that uses a [virtual table].
*/
#define SQLITE_INDEX_CONSTRAINT_EQ      2
#define SQLITE_INDEX_CONSTRAINT_GT      4
#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

/*
** 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

          zOp = ">"; break;
        case SQLITE_INDEX_CONSTRAINT_LE:
          zOp = "<="; break;
        case SQLITE_INDEX_CONSTRAINT_GE:
          zOp = ">="; break;
        case SQLITE_INDEX_CONSTRAINT_MATCH:
          zOp = "LIKE"; 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;
      }
      if( zOp[0]=='L' ){
        zNew = sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')", 
                               zSep, zCol);
      } else {
        zNew = sqlite3_mprintf(" %s %s %s ?", zSep, zCol, zOp);
      }

    if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
    if( pTerm->wtFlags & TERM_VNULL ) continue;
    assert( pTerm->u.leftColumn>=(-1) );
    pIdxCons[j].iColumn = pTerm->u.leftColumn;
    pIdxCons[j].iTermOffset = i;
    op = (u8)pTerm->eOperator & WO_ALL;
    if( op==WO_IN ) op = WO_EQ;
    if( op==WO_MATCH ){
      op = pTerm->eMatchOp;
    }
    pIdxCons[j].op = 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 );

    WhereOrInfo *pOrInfo;   /* Extra information if (eOperator & WO_OR)!=0 */
    WhereAndInfo *pAndInfo; /* Extra information if (eOperator& WO_AND)!=0 */
  } u;
  LogEst truthProb;       /* Probability of truth for this expression */
  u16 eOperator;          /* A WO_xx value describing <op> */
  u16 wtFlags;            /* TERM_xxx bit flags.  See below */
  u8 nChild;              /* Number of children that must disable us */
  u8 eMatchOp;            /* Op for vtab MATCH/LIKE/GLOB/REGEXP terms */
  WhereClause *pWC;       /* The clause this term is part of */
  Bitmask prereqRight;    /* Bitmask of tables used by pExpr->pRight */
  Bitmask prereqAll;      /* Bitmask of tables referenced by pExpr */
};

/*
** Allowed values of WhereTerm.wtFlags

** Check to see if the given expression is of the form
**
**         column MATCH expr
**
** If it is then return TRUE.  If not, return FALSE.
*/
static int isMatchOfColumn(
  Expr *pExpr,                    /* Test this expression */
  unsigned char *peOp2            /* OUT: 0 for MATCH, or else an op2 value */
){
  struct Op2 {
    const char *zOp;
    unsigned char eOp2;
  } aOp[] = {
    { "match", SQLITE_INDEX_CONSTRAINT_MATCH },
    { "glob",  SQLITE_INDEX_CONSTRAINT_GLOB },
    { "like",  SQLITE_INDEX_CONSTRAINT_LIKE },
    { "regex", SQLITE_INDEX_CONSTRAINT_REGEXP }
  };
  ExprList *pList;
  int i;

  if( pExpr->op!=TK_FUNCTION ){
    return 0;
  }



  pList = pExpr->x.pList;
  if( pList->nExpr!=2 ){
    return 0;
  }
  if( pList->a[1].pExpr->op != TK_COLUMN ){
    return 0;
  }
  for(i=0; i<ArraySize(aOp); i++){
    if( sqlite3StrICmp(pExpr->u.zToken, aOp[i].zOp)==0 ){
      *peOp2 = aOp[i].eOp2;
      return 1;
    }
  }
  return 0;
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */

/*
** If the pBase expression originated in the ON or USING clause of
** a join, then transfer the appropriate markings over to derived.
*/

  Bitmask extraRight = 0;          /* Extra dependencies on LEFT JOIN */
  Expr *pStr1 = 0;                 /* RHS of LIKE/GLOB operator */
  int isComplete = 0;              /* RHS of LIKE/GLOB ends with wildcard */
  int noCase = 0;                  /* uppercase equivalent to lowercase */
  int op;                          /* Top-level operator.  pExpr->op */
  Parse *pParse = pWInfo->pParse;  /* Parsing context */
  sqlite3 *db = pParse->db;        /* Database connection */
  unsigned char eOp2;              /* op2 value for LIKE/REGEXP/GLOB */

  if( db->mallocFailed ){
    return;
  }
  pTerm = &pWC->a[idxTerm];
  pMaskSet = &pWInfo->sMaskSet;
  pExpr = pTerm->pExpr;

#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( isMatchOfColumn(pExpr, &eOp2) ){
    int idxNew;
    Expr *pRight, *pLeft;
    WhereTerm *pNewTerm;
    Bitmask prereqColumn, prereqExpr;

    pRight = pExpr->x.pList->a[0].pExpr;
    pLeft = pExpr->x.pList->a[1].pExpr;
    prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight);
    prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft);
    if( (prereqExpr & prereqColumn)==0 ){
      Expr *pNewExpr;
      pNewExpr = sqlite3PExpr(pParse, TK_MATCH, 
                              0, sqlite3ExprDup(db, pRight, 0), 0);
      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;
    }
  }
#endif /* SQLITE_OMIT_VIRTUALTABLE */

  1.1 "SELECT a FROM e6 WHERE b>'James'" {4 1 5}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b > ?} James}

  1.2 "SELECT a FROM e6 WHERE b>='J' AND b<'K'" {3 4}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b >= ? AND b < ?} J K}

  1.3 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} J%}

  1.4 "SELECT a FROM e6 WHERE b LIKE 'j%'" {3 4}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} j%}
} {
  set echo_module {}
  do_execsql_test 18.$tn.1 $sql $res
  do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
}

do_execsql_test 18.2.0 {  PRAGMA case_sensitive_like = ON }
foreach {tn sql res filter} {
  2.1 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} J%}

  2.2 "SELECT a FROM e6 WHERE b LIKE 'j%'" {}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} j%}
} {
  set echo_module {}
  do_execsql_test 18.$tn.1 $sql $res
  do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
}
do_execsql_test 18.2.x {  PRAGMA case_sensitive_like = OFF }

# 2015 Nov 24
#
# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#

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

ifcapable !vtab {
  finish_test
  return
}

register_echo_module db

do_execsql_test 1.0 {
  CREATE TABLE t6(a, b TEXT);
  CREATE INDEX i6 ON t6(b, a);
  CREATE VIRTUAL TABLE e6 USING echo(t6);
}

foreach {tn sql expect} {
  1 "SELECT * FROM e6 WHERE b LIKE 'abc'" {
    xBestIndex {SELECT rowid, * FROM 't6' WHERE b like ?} 
    xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} abc
  }

  2 "SELECT * FROM e6 WHERE b GLOB 'abc'" {
    xBestIndex {SELECT rowid, * FROM 't6' WHERE b glob ?} 
    xFilter {SELECT rowid, * FROM 't6' WHERE b glob ?} abc
  }
} {
  do_test 1.$tn {
    set echo_module {}
    execsql $sql
    set ::echo_module
  } [list {*}$expect]
}

finish_test