SQLite

**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the virtual table interfaces.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test8.c,v 1.61 2008/03/17 09:36:45 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

#ifndef SQLITE_OMIT_VIRTUALTABLE

  Tcl_Interp *interp = pVtab->interp;

  int nRow;
  int useIdx = 0;
  int rc = SQLITE_OK;
  int useCost = 0;
  double cost;

  int isIgnoreUsable = 0;
  if( Tcl_GetVar(interp, "echo_module_ignore_usable", TCL_GLOBAL_ONLY) ){
    isIgnoreUsable = 1;
  }

  /* Determine the number of rows in the table and store this value in local
  ** variable nRow. The 'estimated-cost' of the scan will be the number of
  ** rows in the table for a linear scan, or the log (base 2) of the 
  ** number of rows if the proposed scan uses an index.  
  */
  if( Tcl_GetVar(interp, "echo_module_cost", TCL_GLOBAL_ONLY) ){

  for(ii=0; ii<pIdxInfo->nConstraint; ii++){
    const struct sqlite3_index_constraint *pConstraint;
    struct sqlite3_index_constraint_usage *pUsage;
    int iCol;

    pConstraint = &pIdxInfo->aConstraint[ii];
    pUsage = &pIdxInfo->aConstraintUsage[ii];

    if( !isIgnoreUsable && !pConstraint->usable ) continue;

    iCol = pConstraint->iColumn;
    if( pVtab->aIndex[iCol] ){
      char *zCol = pVtab->aCol[iCol];
      char *zOp = 0;
      useIdx = 1;
      if( iCol<0 ){

** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.289 2008/03/17 09:36:45 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)

  }

  (void)sqlite3SafetyOff(pParse->db);
  WHERETRACE(("xBestIndex for %s\n", pTab->zName));
  TRACE_IDX_INPUTS(pIdxInfo);
  rc = pTab->pVtab->pModule->xBestIndex(pTab->pVtab, pIdxInfo);
  TRACE_IDX_OUTPUTS(pIdxInfo);
  (void)sqlite3SafetyOn(pParse->db);

  for(i=0; i<pIdxInfo->nConstraint; i++){
    if( !pIdxInfo->aConstraint[i].usable && pUsage[i].argvIndex>0 ){
      sqlite3ErrorMsg(pParse, 
          "table %s: xBestIndex returned an invalid plan", pTab->zName);
      return 0.0;
    }
  }

  if( rc!=SQLITE_OK ){
    if( rc==SQLITE_NOMEM ){
      pParse->db->mallocFailed = 1;
    }else {
      sqlite3ErrorMsg(pParse, "%s", sqlite3ErrStr(rc));
    }
  }

  *(int*)&pIdxInfo->nOrderBy = nOrderBy;

  return pIdxInfo->estimatedCost;
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */

/*

#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for joins, including outer joins involving
# virtual tables. The test cases in this file are copied from the file
# join.test, and some of the comments still reflect that.
#
# $Id: vtab6.test,v 1.3 2008/03/17 09:36:45 danielk1977 Exp $

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

ifcapable !vtab {
  finish_test
  return

    CREATE INDEX i22 ON real_t22(q);
    SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q=
       (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1);
  }  
} {}
} ;# ifcapable subquery

do_test vtab6-11.1.0 {
  execsql {
    CREATE TABLE ab_r(a, b);
    CREATE TABLE bc_r(b, c);

    CREATE VIRTUAL TABLE ab USING echo(ab_r); 
    CREATE VIRTUAL TABLE bc USING echo(bc_r); 

    INSERT INTO ab VALUES(1, 2);
    INSERT INTO bc VALUES(2, 3);
  }
} {}

do_test vtab6-11.1.1 {
  execsql {
    SELECT a, b, c FROM ab NATURAL JOIN bc;
  }
} {1 2 3}
do_test vtab6-11.1.2 {
  execsql {
    SELECT a, b, c FROM bc NATURAL JOIN ab;
  }
} {1 2 3}

set ::echo_module_cost 1.0

do_test vtab6-11.1.3 {
  execsql {
    SELECT a, b, c FROM ab NATURAL JOIN bc;
  }
} {1 2 3}
do_test vtab6-11.1.4 {
  execsql {
    SELECT a, b, c FROM bc NATURAL JOIN ab;
  }
} {1 2 3}


do_test vtab6-11.2.0 {
  execsql {
    CREATE INDEX ab_i ON ab_r(b);
  }
} {}

unset ::echo_module_cost

do_test vtab6-11.2.1 {
  execsql {
    SELECT a, b, c FROM ab NATURAL JOIN bc;
  }
} {1 2 3}
do_test vtab6-11.2.2 {
  execsql {
    SELECT a, b, c FROM bc NATURAL JOIN ab;
  }
} {1 2 3}

set ::echo_module_cost 1.0

do_test vtab6-11.2.3 {
  execsql {
    SELECT a, b, c FROM ab NATURAL JOIN bc;
  }
} {1 2 3}
do_test vtab6-11.2.4 {
  execsql {
    SELECT a, b, c FROM bc NATURAL JOIN ab;
  }
} {1 2 3}

unset ::echo_module_cost
db close
sqlite3 db test.db
register_echo_module [sqlite3_connection_pointer db]

do_test vtab6-11.3.1 {
  execsql {
    SELECT a, b, c FROM ab NATURAL JOIN bc;
  }
} {1 2 3}

do_test vtab6-11.3.2 {
  execsql {
    SELECT a, b, c FROM bc NATURAL JOIN ab;
  }
} {1 2 3}

set ::echo_module_cost 1.0

do_test vtab6-11.3.3 {
  execsql {
    SELECT a, b, c FROM ab NATURAL JOIN bc;
  }
} {1 2 3}
do_test vtab6-11.3.4 {
  execsql {
    SELECT a, b, c FROM bc NATURAL JOIN ab;
  }
} {1 2 3}

unset ::echo_module_cost

set ::echo_module_ignore_usable 1
db cache flush

do_test vtab6-11.4.1 {
  catchsql {
    SELECT a, b, c FROM ab NATURAL JOIN bc;
  }
} {1 {table ab: xBestIndex returned an invalid plan}}
do_test vtab6-11.4.2 {
  catchsql {
    SELECT a, b, c FROM bc NATURAL JOIN ab;
  }
} {1 {table ab: xBestIndex returned an invalid plan}}

unset ::echo_module_ignore_usable

finish_test