SQLite

  int nFts3;                      /* Result of strlen(zFts3) */
  int nByte;                      /* Bytes of space to allocate here */
  int rc;                         /* value returned by declare_vtab() */
  Fts3auxTable *p;                /* Virtual table object to return */

  /* The user should specify a single argument - the name of an fts3 table. */
  if( argc!=4 ){
    *pzErr = sqlite3_mprintf(
        "wrong number of arguments to fts4aux constructor"
    );
    return SQLITE_ERROR;
  }

  zDb = argv[1]; 
  nDb = strlen(zDb);
  zFts3 = argv[3];
  nFts3 = strlen(zFts3);

      if( op==SQLITE_INDEX_CONSTRAINT_GE ) iGe = i;
    }
  }

  if( iEq>=0 ){
    pInfo->idxNum = FTS4AUX_EQ_CONSTRAINT;
    pInfo->aConstraintUsage[iEq].argvIndex = 1;
    pInfo->estimatedCost = 5;
  }else{
    pInfo->idxNum = 0;
    pInfo->estimatedCost = 20000;
    if( iGe>=0 ){
      pInfo->idxNum += FTS4AUX_GE_CONSTRAINT;
      pInfo->aConstraintUsage[iGe].argvIndex = 1;
      pInfo->estimatedCost /= 2;
    }
    if( iLe>=0 ){
      pInfo->idxNum += FTS4AUX_LE_CONSTRAINT;
      pInfo->aConstraintUsage[iLe].argvIndex = 1 + (iGe>=0);
      pInfo->estimatedCost /= 2;
    }
  }


  return SQLITE_OK;
}

/*
** xOpen - Open a cursor.
*/
static int fts3auxOpenMethod(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCsr){

  sqlite3_free((void *)pCsr->filter.zTerm);
  memset(&pCsr->csr, 0, ((u8*)&pCsr[1]) - (u8*)&pCsr->csr);

  pCsr->filter.flags = FTS3_SEGMENT_REQUIRE_POS|FTS3_SEGMENT_IGNORE_EMPTY;
  if( isScan ) pCsr->filter.flags |= FTS3_SEGMENT_SCAN;

  if( idxNum&(FTS4AUX_EQ_CONSTRAINT|FTS4AUX_GE_CONSTRAINT) ){
    const unsigned char *zStr = sqlite3_value_text(apVal[0]);
    if( zStr ){
      pCsr->filter.zTerm = sqlite3_mprintf("%s", zStr);
      pCsr->filter.nTerm = sqlite3_value_bytes(apVal[0]);
      if( pCsr->filter.zTerm==0 ) return SQLITE_NOMEM;
    }
  }
  if( idxNum&FTS4AUX_LE_CONSTRAINT ){

do_execsql_test 2.1.4.4 { SELECT * FROM terms WHERE +term='breakfast' } {}

do_execsql_test 2.1.4.5 { SELECT * FROM terms WHERE term='cba'  } {}
do_execsql_test 2.1.4.6 { SELECT * FROM terms WHERE +term='cba' } {}
do_execsql_test 2.1.4.7 { SELECT * FROM terms WHERE term='abc'  } {}
do_execsql_test 2.1.4.8 { SELECT * FROM terms WHERE +term='abc' } {}

# Special case: term=NULL
#
do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {}

do_execsql_test 2.2.1.1 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain'
} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2: (~0 rows)} }
do_execsql_test 2.2.1.2 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain'
} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }

do_execsql_test 2.2.4.6 {
  SELECT * FROM terms 
  WHERE rec('cnt', term) AND +term > 'brags' AND +term < 'brain' 
} {
  braid 1 1 braided 1 1 braiding 1 1 braids 1 1
}

# Check that "ORDER BY term ASC" and equivalents are sorted by the
# virtual table implementation. Any other ORDER BY clause requires
# SQLite to sort results using a temporary b-tree.
#
foreach {tn sort orderby} {
  1    0    "ORDER BY term ASC"
  2    0    "ORDER BY term"
  3    1    "ORDER BY term DESC"
  4    1    "ORDER BY documents ASC"
  5    1    "ORDER BY documents"
  6    1    "ORDER BY documents DESC"
  7    1    "ORDER BY occurrences ASC"
  8    1    "ORDER BY occurrences"
  9    1    "ORDER BY occurrences DESC"
} {

  set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}]
  if {$sort} { lappend res 0 0 0 {USE TEMP B-TREE FOR ORDER BY} }

  set sql "SELECT * FROM terms $orderby"
  do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res
}

#-------------------------------------------------------------------------
# The next set of tests, fts3aux1-3.*, test error conditions in the 
# fts4aux module. Except, fault injection testing (OOM, IO error etc.) is 
# done in fts3fault2.test
#

do_execsql_test 3.1.1 {
  CREATE VIRTUAL TABLE t2 USING fts4;
}

do_catchsql_test 3.1.2 {
  CREATE VIRTUAL TABLE terms2 USING fts4aux;
} {1 {wrong number of arguments to fts4aux constructor}}
do_catchsql_test 3.1.3 {
  CREATE VIRTUAL TABLE terms2 USING fts4aux(t2, t2);
} {1 {wrong number of arguments to fts4aux constructor}}

do_execsql_test 3.2.1 {
  CREATE VIRTUAL TABLE terms3 USING fts4aux(does_not_exist)
}
do_catchsql_test 3.2.2 {
  SELECT * FROM terms3
} {1 {SQL logic error or missing database}}
do_catchsql_test 3.2.3 {
  SELECT * FROM terms3 WHERE term = 'abc'
} {1 {SQL logic error or missing database}}

do_catchsql_test 3.3.1 {
  INSERT INTO terms VALUES(1,2,3);
} {1 {table terms may not be modified}}
do_catchsql_test 3.3.2 {
  DELETE FROM terms
} {1 {table terms may not be modified}}
do_catchsql_test 3.3.3 {
  UPDATE terms set documents = documents+1;
} {1 {table terms may not be modified}}


#-------------------------------------------------------------------------
#
db close
forcedelete test.db
sqlite3 db test.db
do_execsql_test 4.1 {
  CREATE VIRTUAL TABLE x1 USING fts4(x);
  CREATE VIRTUAL TABLE terms USING fts4aux(x1);
  CREATE TABLE x2(y);
  CREATE TABLE x3(y);
  CREATE INDEX i1 ON x3(y);

  INSERT INTO x1 VALUES('a b c d e');
  INSERT INTO x1 VALUES('f g h i j');
  INSERT INTO x1 VALUES('k k l l a');

  INSERT INTO x2 SELECT term FROM terms;
  INSERT INTO x3 SELECT term FROM terms;
}

proc do_plansql_test {tn sql r} {
  uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r]
}


do_plansql_test 4.2 {
  SELECT y FROM x2, terms WHERE y = term
} {
  0 0 0 {SCAN TABLE x2 (~1000000 rows)} 
  0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} 
  a b c d e f g h i j k l
}

do_plansql_test 4.3 {
  SELECT y FROM terms, x2 WHERE y = term
} {
  0 0 1 {SCAN TABLE x2 (~1000000 rows)} 
  0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} 
  a b c d e f g h i j k l
}

do_plansql_test 4.4 {
  SELECT y FROM x3, terms WHERE y = term
} {
  0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 
  0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
  a b c d e f g h i j k l
}

do_plansql_test 4.5 {
  SELECT y FROM terms, x3 WHERE y = term AND occurrences>1
} {
  0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 
  0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
  a k l
}


finish_test

# 2011 February 3
#
# 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.
#
#***********************************************************************
#

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

do_test 1.0 {
  execsql {
    CREATE VIRTUAL TABLE t1 USING fts4(x);
    INSERT INTO t1 VALUES('a b c');
    INSERT INTO t1 VALUES('c d e');
    CREATE VIRTUAL TABLE terms USING fts4aux(t1);
  }
  faultsim_save_and_close
} {}

do_faultsim_test 1.1 -prep {
  faultsim_restore_and_reopen
  db eval {SELECT * FROM sqlite_master}
} -body {
  execsql "CREATE VIRTUAL TABLE terms2 USING fts4aux(t1)"
} -test {
  faultsim_test_result {0 {}}
}

do_faultsim_test 1.2 -prep {
  faultsim_restore_and_reopen
  db eval {SELECT * FROM sqlite_master}
} -body {
  execsql "SELECT * FROM terms"
} -test {
  faultsim_test_result {0 {a 1 1 b 1 1 c 2 2 d 1 1 e 1 1}}
}

do_faultsim_test 1.3 -prep {
  faultsim_restore_and_reopen
  db eval {SELECT * FROM sqlite_master}
} -body {
  execsql "SELECT * FROM terms WHERE term>'a' AND TERM < 'd'"
} -test {
  faultsim_test_result {0 {b 1 1 c 2 2}}
}

do_faultsim_test 1.4 -prep {
  faultsim_restore_and_reopen
  db eval {SELECT * FROM sqlite_master}
} -body {
  execsql "SELECT * FROM terms WHERE term='c'"
} -test {
  faultsim_test_result {0 {c 2 2}}
}



finish_test