sqlite/test/vtabH.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 {
  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]
}


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

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