SQLite

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This header file defines the interface that the SQLite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.172 2006/06/13 14:16:59 danielk1977 Exp $
*/
#ifndef _SQLITE3_H_
#define _SQLITE3_H_
#include <stdarg.h>     /* Needed for the definition of va_list */

/*
** Make sure we can call this stuff from C++.

** The aConstraint[] array only reports WHERE clause terms in the correct
** form that refer to the particular virtual table being queried.
**
** Information about the ORDER BY clause is stored in aOrderBy[].
** Each term of aOrderBy records a column of the ORDER BY clause.
**
** The xBestIndex method must fill aConstraintUsage[] with information
** about what parameters to pass to xFilter.  If argvIndex>0 then
** the right-hand side of the corresponding aConstraint[] is evaluated
** and becomes the argvIndex-th entry in argv.  If aConstraintUsage[].omit
** is true, then the constraint is assumed to be fully handled by the
** virtual table and is not checked again by SQLite.
**
** The idxNum value is recorded and passed into xFilter.  
**

**    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.9 2006/06/13 14:16:59 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

typedef struct echo_vtab echo_vtab;
typedef struct echo_cursor echo_cursor;

/* 
** An echo virtual-table object 
**
** If it is not NULL, the aHasIndex array is allocated so that it has
** the same number of entries as there are columns in the underlying
** real table.
*/
struct echo_vtab {
  sqlite3_vtab base;
  Tcl_Interp *interp;
  sqlite3 *db;
  char *zStmt;                 /* "SELECT rowid, * FROM <real-table-name> " */

  int *aIndex;
  int nCol;
  char **aCol;
};

/* An echo cursor object */
struct echo_cursor {
  sqlite3_vtab_cursor base;
  sqlite3_stmt *pStmt;
  int errcode;                 /* Error code */
};

static int getColumnNames(
  sqlite3 *db, 
  const char *zTab,
  char ***paCol, 
  int *pnCol
){
  char **aCol = 0;
  char zBuf[1024];
  sqlite3_stmt *pStmt = 0;
  int rc = SQLITE_OK;
  int nCol;

  sprintf(zBuf, "SELECT * FROM %s", zTab);
  rc = sqlite3_prepare(db, zBuf, -1, &pStmt, 0);
  if( rc==SQLITE_OK ){
    int ii;
    nCol = sqlite3_column_count(pStmt);
    aCol = sqliteMalloc(sizeof(char *) * nCol);
    if( !aCol ){
      rc = SQLITE_NOMEM;
      goto fail;
    }
    for(ii=0; ii<nCol; ii++){
      aCol[ii] = sqlite3StrDup(sqlite3_column_name(pStmt, ii));
      if( !aCol[ii] ){
        rc = SQLITE_NOMEM;
        goto fail;
      }
    }
  }

  *paCol = aCol;
  *pnCol = nCol;

fail:
  sqlite3_finalize(pStmt);
  if( rc!=SQLITE_OK && aCol ){
    int ii;
    for(ii=0; ii<nCol; ii++){
      sqliteFree(aCol[ii]);
    }
    sqliteFree(aCol);
  }
  return rc;
}

static int getIndexArray(sqlite3 *db, const char *zTab, int **paIndex){
  char zBuf[1024];
  sqlite3_stmt *pStmt = 0;
  int nCol;
  int *aIndex = 0;
  int rc;

  sprintf(zBuf, "SELECT * FROM %s", zTab);
  rc = sqlite3_prepare(db, zBuf, -1, &pStmt, 0);
  nCol = sqlite3_column_count(pStmt);

  sqlite3_finalize(pStmt);
  pStmt = 0;
  if( rc!=SQLITE_OK ){
    goto get_index_array_out;
  }

  aIndex = (int *)sqliteMalloc(sizeof(int) * nCol);
  if( !aIndex ){
    rc = SQLITE_NOMEM;
    goto get_index_array_out;
  }

  sprintf(zBuf, "PRAGMA index_list(%s)", zTab);
  rc = sqlite3_prepare(db, zBuf, -1, &pStmt, 0);

  while( pStmt && sqlite3_step(pStmt)==SQLITE_ROW ){
    sqlite3_stmt *pStmt2 = 0;
    sprintf(zBuf, "PRAGMA index_info(%s)", sqlite3_column_text(pStmt, 1));
    rc = sqlite3_prepare(db, zBuf, -1, &pStmt2, 0);
    if( pStmt2 && sqlite3_step(pStmt2)==SQLITE_ROW ){
      int cid = sqlite3_column_int(pStmt2, 1);
      assert( cid>=0 && cid<nCol );
      aIndex[cid] = 1;
    }
    rc = sqlite3_finalize(pStmt2);
    if( rc!=SQLITE_OK ){
      sqlite3_finalize(pStmt);
      goto get_index_array_out;
    }
  }

  rc = sqlite3_finalize(pStmt);

get_index_array_out:
  if( rc!=SQLITE_OK ){
    sqliteFree(aIndex);
    aIndex = 0;
  }
  *paIndex = aIndex;
  return rc;
}

/*
** Global Tcl variable $echo_module is a list. This routine appends
** the string element zArg to that list in interpreter interp.
*/
static void appendToEchoModule(Tcl_Interp *interp, const char *zArg){
  int flags = (TCL_APPEND_VALUE | TCL_LIST_ELEMENT | TCL_GLOBAL_ONLY);
  Tcl_SetVar(interp, "echo_module", (zArg?zArg:""), flags);
}

/*
** This function is called from within the echo-modules xCreate and
** xConnect methods. The argc and argv arguments are copies of those 
** passed to the calling method. This function is responsible for
** calling sqlite3_declare_vtab() to declare the schema of the virtual

      sqlite3_declare_vtab(db, zCreateTable);
#endif
    } else {
      rc = SQLITE_ERROR;
    }
    sqlite3_finalize(pStmt);
    pVtab->zStmt = sqlite3MPrintf("SELECT rowid, * FROM %s ", argv[1]);
    if( rc==SQLITE_OK ){
      rc = getIndexArray(db, argv[1], &pVtab->aIndex);
    }
    if( rc==SQLITE_OK ){
      rc = getColumnNames(db, argv[1], &pVtab->aCol, &pVtab->nCol);
    }
  }

  return rc;
}

static int echoConstructor(
  sqlite3 *db,

  int argc, char **argv,
  sqlite3_vtab **ppVtab
){
  appendToEchoModule((Tcl_Interp *)(pModule->pAux), "xConnect");
  return echoConstructor(db, pModule, argc, argv, ppVtab);
}

static int echoDestructor(sqlite3_vtab *pVtab){
  int ii;
  echo_vtab *p = (echo_vtab*)pVtab;

  sqliteFree(p->zStmt);
  sqliteFree(p->aIndex);
  for(ii=0; ii<p->nCol; ii++){
    sqliteFree(p->aCol[ii]);
  }



  sqliteFree(p->aCol);
  sqliteFree(p);
  return 0;
}

static int echoDisconnect(sqlite3_vtab *pVtab){
  appendToEchoModule(((echo_vtab *)pVtab)->interp, "xDisconnect");
  return echoDestructor(pVtab);
}
static int echoDestroy(sqlite3_vtab *pVtab){
  appendToEchoModule(((echo_vtab *)pVtab)->interp, "xDestroy");
  return echoDestructor(pVtab);
}

static int echoOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
  echo_cursor *pCur;
  pCur = sqliteMalloc(sizeof(echo_cursor));
  *ppCursor = (sqlite3_vtab_cursor *)pCur;
  return SQLITE_OK;
}

static int echoClose(sqlite3_vtab_cursor *cur){

  echo_cursor *pCur = (echo_cursor *)cur;
  sqlite3_finalize(pCur->pStmt);
  sqliteFree(pCur);
  return SQLITE_OK;
}

static int echoNext(sqlite3_vtab_cursor *cur){

  int rc;
  echo_cursor *pCur = (echo_cursor *)cur;

  rc = sqlite3_step(pCur->pStmt);

  if( rc==SQLITE_ROW ){
    rc = 1;
  } else {
    pCur->errcode = sqlite3_finalize(pCur->pStmt);
    pCur->pStmt = 0;
    rc = 0;
  }

  return rc;
}
























static int echoColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){

  int iCol = i + 1;
  sqlite3_stmt *pStmt = ((echo_cursor *)cur)->pStmt;

  assert( sqlite3_data_count(pStmt)>iCol );
  switch( sqlite3_column_type(pStmt, iCol) ){
    case SQLITE_INTEGER:
      sqlite3_result_int64(ctx, sqlite3_column_int64(pStmt, iCol));

          SQLITE_TRANSIENT
      );
      break;
  }
  return SQLITE_OK;
}

static int echoRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){

  sqlite3_stmt *pStmt = ((echo_cursor *)cur)->pStmt;
  *pRowid = sqlite3_column_int64(pStmt, 0);
  return SQLITE_OK;
}


static int echoFilter(
  sqlite3_vtab_cursor *pVtabCursor, 
  int idx,
  int argc, 
  sqlite3_value **argv
){
  int rc;
  char zBuf[32];
  int ii;

  echo_cursor *pCur = (echo_cursor *)pVtabCursor;
  echo_vtab *pVtab = (echo_vtab *)pVtabCursor->pVtab;
  sqlite3 *db = pVtab->db;

  sprintf(zBuf, "%d", idx);
  appendToEchoModule(pVtab->interp, "xFilter");
  appendToEchoModule(pVtab->interp, zBuf);
  for(ii=0; ii<argc; ii++){
    appendToEchoModule(pVtab->interp, sqlite3_value_text(argv[ii]));
  }

  sqlite3_finalize(pCur->pStmt);
  pCur->pStmt = 0;
  rc = sqlite3_prepare(db, pVtab->zStmt, -1, &pCur->pStmt, 0);

  if( rc==SQLITE_OK ){
    rc = echoNext(pVtabCursor);
  }

  return rc;
}

/*
** The echo module implements the subset of query constraints and sort
** orders that may take advantage of SQLite indices on the underlying
** real table. For example, if the real table is declared as:
**
**     CREATE TABLE real(a, b, c);
**     CREATE INDEX real_index ON real(b);
**
** then the echo module handles WHERE or ORDER BY clauses that refer
** to the column "b", but not "a" or "c". If a multi-column index is
** present, only it's left most column is considered. 
*/
static int echoBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  int ii;
  char *zWhere = 0;
  char *zOrder = 0;
  int nArg = 0;
  echo_vtab *pVtab = (echo_vtab *)tab;

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

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

    int iCol = pConstraint->iColumn;
    if( pVtab->aIndex[iCol] ){
      char *zCol = pVtab->aCol[iCol];
      char *zOp = 0;
      switch( pConstraint->op ){
        case SQLITE_INDEX_CONSTRAINT_EQ:
          zOp = "="; break;
        case SQLITE_INDEX_CONSTRAINT_LT:
          zOp = "<"; break;
        case SQLITE_INDEX_CONSTRAINT_GT:
          zOp = ">"; break;
        case SQLITE_INDEX_CONSTRAINT_LE:
          zOp = "<="; break;
        case SQLITE_INDEX_CONSTRAINT_GE:
          zOp = ">="; break;
        case SQLITE_INDEX_CONSTRAINT_MATCH:
          zOp = "MATCH"; break;
      }
      if( zWhere ){
        char *zTmp = zWhere;
        zWhere = sqlite3MPrintf("%s AND %s %s ?", zWhere, zCol, zOp);
        sqliteFree(zTmp);
      } else {
        zWhere = sqlite3MPrintf("WHERE %s %s ?", zCol, zOp);
      }

      pUsage->argvIndex = ++nArg;
      pUsage->omit = 1;
    }
  }

  appendToEchoModule(pVtab->interp, "xBestIndex");;
  appendToEchoModule(pVtab->interp, zWhere);
  appendToEchoModule(pVtab->interp, zOrder);

  sqliteFree(zWhere);
  sqliteFree(zOrder);

  pIdxInfo->idxNum = 123;
  return SQLITE_OK;
}

/*
** A virtual table module that merely echos method calls into TCL
** variables.

**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.556 2006/06/13 14:16:59 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*

    pCur->pVtabCursor = pVtabCursor;
  }
  break;
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Opcode: VFilter P1 P2 P3
**
** P1 is a cursor opened using VOpen.  P2 is an address to jump to if
** the filtered result set is empty.
**
** P3 points to enough free space to use to marshall the arguments.
**
** This opcode invokes the xFilter method on the virtual table specified
** by P1.  The index number parameter to xFilter is the top of the stack.
** Next down on the stack is the argc parameter.  Beneath the
** next of stack are argc additional parameters which are passed to
** xFilter as argv. The topmost parameter (i.e. 3rd element popped from
** the stack) becomes argv[argc-1] when passed to xFilter.

  assert( pTos[0].flags==MEM_Int && pTos[-1].flags==MEM_Int );
  iIndex = pTos[0].i;
  nArg = pTos[-1].i;

  /* Invoke the xFilter method if one is defined. */
  if( pModule->xFilter ){
    int res;
    int ii;
    Mem **apArg = pOp->p3;
    for(ii = 0; ii<nArg; ii++){
      apArg[ii] = &pTos[ii+1-2-nArg];
    }

    if( sqlite3SafetyOff(db) ) goto abort_due_to_misuse;
    res = pModule->xFilter(pCur->pVtabCursor, iIndex, nArg, apArg);
    if( sqlite3SafetyOn(db) ) goto abort_due_to_misuse;

    if( res==0 ){
      pc = pOp->p2 - 1;
    }
  }

** 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.213 2006/06/13 14:16:59 danielk1977 Exp $
*/
#include "sqliteInt.h"

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

      pLevel->iLeftJoin = pParse->nMem++;
      sqlite3VdbeAddOp(v, OP_MemInt, 0, pLevel->iLeftJoin);
      VdbeComment((v, "# init LEFT JOIN no-match flag"));
    }

#ifndef SQLITE_OMIT_VIRTUALTABLE
    if( pLevel->pIdxInfo ){
      char *zSpace;     /* Space for OP_VFilter to marshall it's arguments */

      /* Case 0:  That table is a virtual-table.  Use the VFilter and VNext.
      */
      sqlite3_index_info *pIdxInfo = pLevel->pIdxInfo;
      for(i=1; i<=pIdxInfo->nConstraint; i++){
        int j;
        for(j=0; j<pIdxInfo->nConstraint; j++){
          if( pIdxInfo->aConstraintUsage[j].argvIndex==i ){
            sqlite3ExprCode(pParse, wc.a[j].pExpr->pRight);
            break;
          }
        }
        if( j==pIdxInfo->nConstraint ) break;
      }
      sqlite3VdbeAddOp(v, OP_Integer, i-1, 0);
      sqlite3VdbeAddOp(v, OP_Integer, pIdxInfo->idxNum, 0);
      sqlite3VdbeAddOp(v, OP_VFilter, iCur, brk);
      zSpace = (char *)sqliteMalloc(sizeof(sqlite3_value*)*(i-1));
      sqlite3VdbeChangeP3(v, -1, zSpace, P3_DYNAMIC);
      for(i=0; i<pIdxInfo->nConstraint; i++){
        if( pIdxInfo->aConstraintUsage[i].omit ){
          disableTerm(pLevel, &wc.a[i]);
        }
      }
      pLevel->op = OP_VNext;
      pLevel->p1 = iCur;

# 2006 June 10
#
# 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.  The
# focus of this file is creating and dropping virtual tables.
#
# $Id: vtab1.test,v 1.9 2006/06/13 14:16:59 danielk1977 Exp $

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

ifcapable !vtab {
  finish_test
  return

  1 b {} 0 {} 0 \
  2 c {} 0 {} 0 \
]

# Test that the database can be unloaded. This should invoke 
# the xDisconnect() callback each of the two virtual tables - t1 and t2.
do_test vtab1-2.3 {
breakpoint
  set echo_module [list]
  db close
  set echo_module
} [list xDisconnect xDisconnect]

  set echo_module [list]
# Re-open the database. Check that the schema of the virtual
# table is still correct.
do_test vtab1-2.4 {
  sqlite3 db test.db
  db cache size 0
  register_echo_module [sqlite3_connection_pointer db]
  execsql { PRAGMA table_info(t2); }
} [list         \
  0 a {} 0 {} 0 \
  1 b {} 0 {} 0 \
  2 c {} 0 {} 0 \
]

#----------------------------------------------------------------------
# Test case vtab1-3 tests simple linear scans (no filter conditions) of 
# virtual table modules.
do_test vtab1-3.1 {
  set echo_module ""
  execsql {
    CREATE TABLE treal(a INTEGER, b VARCHAR(32), c); 
    CREATE INDEX treal_idx ON treal(b);
    CREATE VIRTUAL TABLE t1 USING echo(treal);
  }
  set echo_module
} [list xCreate echo treal]
do_test vtab1-3.2 {
  # Test that a SELECT on t2 doesn't crash. No rows are returned
  # because the underlying real table, is currently empty.

} {1 4}
do_test vtab1-3.5 {
  execsql {
    SELECT rowid FROM t1;
  }
} {1 2}

do_test vtab1-3.6 {
  set echo_module ""
  execsql {
    SELECT * FROM t1;
  }
  set echo_module
} {xBestIndex {} {} xFilter 123}
do_test vtab1-3.7 {
  set echo_module ""
  execsql {
    SELECT * FROM t1 WHERE b = 10;
  }
  set echo_module
} {xBestIndex {WHERE b = ?} {} xFilter 123 10}
do_test vtab1-3.8 {
  set echo_module ""
  execsql {
    SELECT * FROM t1 WHERE b >= 5 AND b <= 10;
  }
  set echo_module
} {xBestIndex {WHERE b >= ? AND b <= ?} {} xFilter 123 5 10}
do_test vtab1-3.9 {
  set echo_module ""
  execsql {
    SELECT * FROM t1 WHERE b BETWEEN 5 AND 10;
  }
  set echo_module
} {xBestIndex {WHERE b >= ? AND b <= ?} {} xFilter 123 5 10}

finish_test