SQLite

#include "sqliteInt.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

#ifndef SQLITE_OMIT_VIRTUALTABLE

/*
** Characters that make up the idxStr created by xBestIndex for xFilter.
*/
#define TCLVAR_NAME_EQ      'e'
#define TCLVAR_NAME_MATCH   'm'
#define TCLVAR_VALUE_GLOB   'g'
#define TCLVAR_VALUE_REGEXP 'r'
#define TCLVAR_VALUE_LIKE   'l'

typedef struct tclvar_vtab tclvar_vtab;
typedef struct tclvar_cursor tclvar_cursor;

/* 
** A tclvar virtual-table object 
*/
struct tclvar_vtab {

static int tclvarFilter(
  sqlite3_vtab_cursor *pVtabCursor, 
  int idxNum, const char *idxStr,
  int argc, sqlite3_value **argv
){
  tclvar_cursor *pCur = (tclvar_cursor *)pVtabCursor;
  Tcl_Interp *interp = ((tclvar_vtab *)(pVtabCursor->pVtab))->interp;
  Tcl_Obj *p = Tcl_NewStringObj("tclvar_filter_cmd", -1);

  const char *zEq = "";
  const char *zMatch = "";
  const char *zGlob = "";
  const char *zRegexp = "";
  const char *zLike = "";
  int i;

  for(i=0; idxStr[i]; i++){
    switch( idxStr[i] ){
      case TCLVAR_NAME_EQ:
        zEq = sqlite3_value_text(argv[i]);
        break;
      case TCLVAR_NAME_MATCH:
        zMatch = sqlite3_value_text(argv[i]);
        break;
      case TCLVAR_VALUE_GLOB:
        zGlob = sqlite3_value_text(argv[i]);
        break;
      case TCLVAR_VALUE_REGEXP:
        zRegexp = sqlite3_value_text(argv[i]);
        break;
      case TCLVAR_VALUE_LIKE:
        zLike = sqlite3_value_text(argv[i]);
        break;
      default:
        assert( 0 );
    }
  }

  Tcl_IncrRefCount(p);
  Tcl_ListObjAppendElement(0, p, Tcl_NewStringObj(zEq, -1));
  Tcl_ListObjAppendElement(0, p, Tcl_NewStringObj(zMatch, -1));
  Tcl_ListObjAppendElement(0, p, Tcl_NewStringObj(zGlob, -1));
  Tcl_ListObjAppendElement(0, p, Tcl_NewStringObj(zRegexp, -1));
  Tcl_ListObjAppendElement(0, p, Tcl_NewStringObj(zLike, -1));

  Tcl_EvalObjEx(interp, p, TCL_EVAL_GLOBAL);
  if( pCur->pList1 ){
    Tcl_DecrRefCount(pCur->pList1);
  }
  if( pCur->pList2 ){
    Tcl_DecrRefCount(pCur->pList2);
    pCur->pList2 = 0;
  }
  pCur->i1 = 0;
  pCur->i2 = 0;
  pCur->pList1 = Tcl_GetObjResult(interp);
  Tcl_IncrRefCount(pCur->pList1);


  Tcl_DecrRefCount(p);
  return tclvarNext(pVtabCursor);
}

static int tclvarColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){
  Tcl_Obj *p1;

}

static int tclvarEof(sqlite3_vtab_cursor *cur){
  tclvar_cursor *pCur = (tclvar_cursor*)cur;
  return (pCur->pList2?0:1);
}

/*
** If nul-terminated string zStr does not already contain the character 
** passed as the second argument, append it and return 0. Or, if there is
** already an instance of x in zStr, do nothing return 1;
**
** There is guaranteed to be enough room in the buffer pointed to by zStr
** for the new character and nul-terminator.
*/
static int tclvarAddToIdxstr(char *zStr, char x){
  int i;
  for(i=0; zStr[i]; i++){
    if( zStr[i]==x ) return 1;
  }
  zStr[i] = x;
  zStr[i+1] = '\0';
  return 0;
}





/*
** Return true if variable $::tclvar_set_omit exists and is set to true.
** False otherwise.
*/
static int tclvarSetOmit(Tcl_Interp *interp){
  int rc;
  int res = 0;
  Tcl_Obj *pRes;
  rc = Tcl_Eval(interp,
    "expr {[info exists ::tclvar_set_omit] && $::tclvar_set_omit}"
  );
  if( rc==TCL_OK ){
    pRes = Tcl_GetObjResult(interp);
    rc = Tcl_GetBooleanFromObj(0, pRes, &res);
  }
  return (rc==TCL_OK && res);
}

/*
** The xBestIndex() method. This virtual table supports the following
** operators:
**
**     name = ?                    (omit flag clear)
**     name MATCH ?                (omit flag set)
**     value GLOB ?                (omit flag set iff $::tclvar_set_omit)
**     value REGEXP ?              (omit flag set iff $::tclvar_set_omit)
**     value LIKE ?                (omit flag set iff $::tclvar_set_omit)
**
** For each constraint present, the corresponding TCLVAR_XXX character is
** appended to the idxStr value. 
*/
static int tclvarBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  tclvar_vtab *pTab = (tclvar_vtab*)tab;
  int ii;
  char *zStr = sqlite3_malloc(32);
  int iStr = 0;

  if( zStr==0 ) return SQLITE_NOMEM;
  zStr[0] = '\0';

  for(ii=0; ii<pIdxInfo->nConstraint; ii++){
    struct sqlite3_index_constraint const *pCons = &pIdxInfo->aConstraint[ii];


    struct sqlite3_index_constraint_usage *pUsage;
    
    pUsage = &pIdxInfo->aConstraintUsage[ii];
    if( pCons->usable ){
      /* name = ? */
      if( pCons->op==SQLITE_INDEX_CONSTRAINT_EQ && pCons->iColumn==0 ){
        if( 0==tclvarAddToIdxstr(zStr, TCLVAR_NAME_EQ) ){
          pUsage->argvIndex = ++iStr;
          pUsage->omit = 0;
        }
      }

      /* name MATCH ? */
      if( pCons->op==SQLITE_INDEX_CONSTRAINT_MATCH && pCons->iColumn==0 ){
        if( 0==tclvarAddToIdxstr(zStr, TCLVAR_NAME_MATCH) ){
          pUsage->argvIndex = ++iStr;
          pUsage->omit = 1;
        }
      }

      /* value GLOB ? */
      if( pCons->op==SQLITE_INDEX_CONSTRAINT_GLOB && pCons->iColumn==2 ){
        if( 0==tclvarAddToIdxstr(zStr, TCLVAR_VALUE_GLOB) ){
          pUsage->argvIndex = ++iStr;
          pUsage->omit = tclvarSetOmit(pTab->interp);
        }
      }

      /* value REGEXP ? */
      if( pCons->op==SQLITE_INDEX_CONSTRAINT_REGEXP && pCons->iColumn==2 ){
        if( 0==tclvarAddToIdxstr(zStr, TCLVAR_VALUE_REGEXP) ){
          pUsage->argvIndex = ++iStr;
          pUsage->omit = tclvarSetOmit(pTab->interp);
        }
      }

      /* value LIKE ? */
      if( pCons->op==SQLITE_INDEX_CONSTRAINT_LIKE && pCons->iColumn==2 ){
        if( 0==tclvarAddToIdxstr(zStr, TCLVAR_VALUE_LIKE) ){
          pUsage->argvIndex = ++iStr;
          pUsage->omit = tclvarSetOmit(pTab->interp);
        }
      }
    }
  }
  pIdxInfo->idxStr = zStr;
  pIdxInfo->needToFreeIdxStr = 1;

  return SQLITE_OK;
}

/*
** A virtual table module that provides read-only access to a
** Tcl global variable namespace.

*/
static int register_tclvar_module(
  ClientData clientData, /* Pointer to sqlite3_enable_XXX function */
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int objc,              /* Number of arguments */
  Tcl_Obj *CONST objv[]  /* Command arguments */
){
  int rc = TCL_OK;
  sqlite3 *db;
  if( objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
#ifndef SQLITE_OMIT_VIRTUALTABLE
  sqlite3_create_module(db, "tclvar", &tclvarModule, (void*)interp);
  rc = Tcl_Eval(interp, 
      "proc like {pattern str} {\n"
      "  set p [string map {% * _ ?} $pattern]\n"
      "  string match $p $str\n"
      "}\n"
      "proc tclvar_filter_cmd {eq match glob regexp like} {\n"
      "  set res {}\n"
      "  set pattern $eq\n"
      "  if {$pattern=={}} { set pattern $match }\n"
      "  if {$pattern=={}} { set pattern * }\n"
      "  foreach v [uplevel #0 info vars $pattern] {\n"
      "    if {($glob=={} || [string match $glob [uplevel #0 set $v]])\n"
      "     && ($like=={} || [like $like [uplevel #0 set $v]])\n"
      "     && ($regexp=={} || [regexp $regexp [uplevel #0 set $v]])\n"
      "    } {\n"
      "      lappend res $v\n"
      "    }\n"
      "  }\n"
      "  set res\n"
      "}\n"
  );
#endif
  return rc;
}

#endif


/*
** Register commands with the TCL interpreter.

#endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */


#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 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 },
    { "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->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;
      return 1;
    }

    SELECT t1.*, t2.*, abs(t3.b + abs(t2.value + abs(t1.value)))
      FROM t1 LEFT JOIN t2 ON t2.name = t1.arrayname
           LEFT JOIN t3 ON t3.a=t2.value
     WHERE t1.name = 'vtabE'
     ORDER BY t1.value, t2.value;
  }
} {vtabE vtabE1 11 vtabE1 w x {} vtabE vtabE1 11 vtabE1 y z {} vtabE vtabE2 22 vtabE2 a b {} vtabE vtabE2 22 vtabE2 c d {}}

finish_test

# 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. Specifically,
# it tests that the GLOB, LIKE and REGEXP operators are correctly exposed
# to virtual table implementations.
#

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

ifcapable !vtab {

  do_test 1.$tn {
    set echo_module {}
    execsql $sql
    set ::echo_module
  } [list {*}$expect]
}


#--------------------------------------------------------------------------

register_tclvar_module db
set ::xyz 10
do_execsql_test 2.0 {
  CREATE VIRTUAL TABLE vars USING tclvar;
  SELECT * FROM vars WHERE name = 'xyz';
} {xyz {} 10}

set x1 aback
set x2 abaft
set x3 abandon
set x4 abandonint
set x5 babble
set x6 baboon
set x7 backbone
set x8 backarrow
set x9 castle

db func glob gfunc
proc gfunc {a b} {
  incr ::gfunc
  return 1
}

db func like lfunc
proc lfunc {a b} {
  incr ::gfunc 100
  return 1
}

db func regexp rfunc
proc rfunc {a b} {
  incr ::gfunc 10000
  return 1
}

foreach ::tclvar_set_omit {0 1} {
  foreach {tn expr res cnt} {
    1 {value GLOB 'aban*'} {x3 abandon x4 abandonint} 2
    2 {value LIKE '%ac%'}  {x1 aback x7 backbone x8 backarrow} 300
    3 {value REGEXP '^......$'}  {x5 babble x6 baboon x9 castle} 30000
  } {
  if {$tn==3} breakpoint
    db cache flush
    set ::gfunc 0
    if {$::tclvar_set_omit} {set cnt 0}

    do_test 2.$tclvar_set_omit.$tn.1 {
      execsql "SELECT name, value FROM vars WHERE name MATCH 'x*' AND $expr"
    } $res

    do_test 2.$tclvar_set_omit.$tn.2 {
      set ::gfunc
    } $cnt
  }
}

finish_test