SQLite

** This file defines the interface to the database backend (Dbbe).
**
** The database backend is designed to be as general as possible
** so that it can easily be replaced by a different backend.
** This library was originally designed to support the following
** backends: GDBM, NDBM, SDBM, Berkeley DB.
**
** $Id: dbbe.h,v 1.14 2001/08/19 18:19:46 drh Exp $
*/
#ifndef _SQLITE_DBBE_H_
#define _SQLITE_DBBE_H_
#include <stdio.h>

/*
** The database backend supports two opaque structures.  A Dbbe is

  int (*BeginTransaction)(Dbbe*);

  /* Commit a transaction. */
  int (*Commit)(Dbbe*);

  /* Rollback a transaction. */
  int (*Rollback)(Dbbe*);

  /* Begin searching an index where the key is given. */
  int (*BeginIndex)(DbbeCursor*, int nKey, char *pKey);

  /* Return the integer key for the next index entry, or return 0 if
  ** there are no more index entries. */
  int (*NextIndex)(DbbeCursor*);

  /* Add a new index entry to the file.  The key and record number are
  ** given. */
  int (*PutIndex)(DbbeCursor*, int nKey, char *pKey, int recno);

  /* Delete an index entry from the file.  The key and record number are
  ** given. */
  int (*DeleteIndex)(DbbeCursor*, int nKey, char *pKey, int recno);
};

/*
** This is the structure returned by sqliteDbbeOpen().  It contains
** information common to all the different backend drivers.
**
** The information in this structure (with the exception the method

** sqlite and the code that does the actually reading and writing
** of information to the disk.
**
** This file uses GDBM as the database backend.  It should be
** relatively simple to convert to a different database such
** as NDBM, SDBM, or BerkeleyDB.
**
** $Id: dbbegdbm.c,v 1.9 2001/08/19 18:19:46 drh Exp $
*/
#ifndef DISABLE_GDBM
#include "sqliteInt.h"
#include <gdbm.h>
#include <sys/stat.h>
#include <unistd.h>
#include <ctype.h>

** associated with the same disk file.
*/
struct DbbeCursor {
  Dbbex *pBe;        /* The database of which this record is a part */
  BeFile *pFile;     /* The database file for this table */
  datum key;         /* Most recently used key */
  datum data;        /* Most recent data */
  int nextIndex;     /* Next index entry to search */
  int needRewind;    /* Next key should be the first */
  int readPending;   /* The fetch hasn't actually been done yet */
};

/*
** The "mkdir()" function only takes one argument under Windows.
*/

  sqliteFree(pCursr);
}

/*
** Reorganize a table to reduce search times and disk usage.
*/
static int sqliteGdbmReorganizeTable(Dbbe *pBe, const char *zTable){
  DbbeCursor *pCursr;
  int rc;

  rc = sqliteGdbmOpenCursor(pBe, zTable, 1, 0, &pCursr);
  if( rc!=SQLITE_OK ){
    return rc;
  }
  if( pCursr && pCursr->pFile && pCursr->pFile->dbf ){
    gdbm_reorganize(pCursr->pFile->dbf);
  }
  if( pCursr ){
    sqliteGdbmCloseCursor(pCursr);
  }
  return SQLITE_OK;
}

/*
** Clear the given datum
*/

      sqliteUnlinkFile(pBe, pFile);
    }
  }
  pBe->inTrans = 0;
  return SQLITE_OK;  
}

/*
** Begin scanning an index for the given key.  Return 1 on success and
** 0 on failure.
*/
static int sqliteGdbmBeginIndex(DbbeCursor *pCursr, int nKey, char *pKey){
  if( !sqliteGdbmFetch(pCursr, nKey, pKey) ) return 0;
  pCursr->nextIndex = 0;
  return 1;
}

/*
** Return an integer key which is the next record number in the index search
** that was started by a prior call to BeginIndex.  Return 0 if all records
** have already been searched.
*/
static int sqliteGdbmNextIndex(DbbeCursor *pCursr){
  int *aIdx;
  int nIdx;
  int k;
  nIdx = pCursr->data.dsize/sizeof(int);
  aIdx = (int*)pCursr->data.dptr;
  if( nIdx>1 ){
    k = *(aIdx++);
    if( k>nIdx-1 ) k = nIdx-1;
  }else{
    k = nIdx;
  }
  while( pCursr->nextIndex < k ){
    int recno = aIdx[pCursr->nextIndex++];
    if( recno!=0 ) return recno;
  }
  pCursr->nextIndex = 0;
  return 0;
}

/*
** Write a new record number and key into an index table.  Return a status
** code.
*/
static int sqliteGdbmPutIndex(DbbeCursor *pCursr, int nKey, char *pKey, int N){
  int r = sqliteGdbmFetch(pCursr, nKey, pKey);
  if( r==0 ){
    /* Create a new record for this index */
    sqliteGdbmPut(pCursr, nKey, pKey, sizeof(int), (char*)&N);
  }else{
    /* Extend the existing record */
    int nIdx;
    int *aIdx;
    int k;
            
    nIdx = pCursr->data.dsize/sizeof(int);
    if( nIdx==1 ){
      aIdx = sqliteMalloc( sizeof(int)*4 );
      if( aIdx==0 ) return SQLITE_NOMEM;
      aIdx[0] = 2;
      sqliteGdbmCopyData(pCursr, 0, sizeof(int), (char*)&aIdx[1]);
      aIdx[2] = N;
      sqliteGdbmPut(pCursr, nKey, pKey, sizeof(int)*4, (char*)aIdx);
      sqliteFree(aIdx);
    }else{
      aIdx = (int*)sqliteGdbmReadData(pCursr, 0);
      k = aIdx[0];
      if( k<nIdx-1 ){
        aIdx[k+1] = N;
        aIdx[0]++;
        sqliteGdbmPut(pCursr, nKey, pKey, sizeof(int)*nIdx, (char*)aIdx);
      }else{
        nIdx *= 2;
        aIdx = sqliteMalloc( sizeof(int)*nIdx );
        if( aIdx==0 ) return SQLITE_NOMEM;
        sqliteGdbmCopyData(pCursr, 0, sizeof(int)*(k+1), (char*)aIdx);
        aIdx[k+1] = N;
        aIdx[0]++;
        sqliteGdbmPut(pCursr, nKey, pKey, sizeof(int)*nIdx, (char*)aIdx);
        sqliteFree(aIdx);
      }
    }
  }
  return SQLITE_OK;
}

/*
** Delete an index entry.  Return a status code.
*/
static 
int sqliteGdbmDeleteIndex(DbbeCursor *pCursr, int nKey, char *pKey, int N){
  int *aIdx;
  int nIdx;
  int j, k;
  int rc;
  rc = sqliteGdbmFetch(pCursr, nKey, pKey);
  if( !rc ) return SQLITE_OK;
  nIdx = pCursr->data.dsize/sizeof(int);
  aIdx = (int*)sqliteGdbmReadData(pCursr, 0);
  if( (nIdx==1 && aIdx[0]==N) || (aIdx[0]==1 && aIdx[1]==N) ){
    sqliteGdbmDelete(pCursr, nKey, pKey);
  }else{
    k = aIdx[0];
    for(j=1; j<=k && aIdx[j]!=N; j++){}
    if( j>k ) return SQLITE_OK;
    aIdx[j] = aIdx[k];
    aIdx[k] = 0;
    aIdx[0]--;
    if( aIdx[0]*3 + 1 < nIdx ){
      nIdx /= 2;
    }
    sqliteGdbmPut(pCursr, nKey, pKey, sizeof(int)*nIdx, (char*)aIdx);
  }
  return SQLITE_OK;
}

/*
** This variable contains pointers to all of the access methods
** used to implement the GDBM backend.
*/
static struct DbbeMethods gdbmMethods = {
  /*           Close */   sqliteGdbmClose,

  /*          Rewind */   sqliteGdbmRewind,
  /*             New */   sqliteGdbmNew,
  /*             Put */   sqliteGdbmPut,
  /*          Delete */   sqliteGdbmDelete,
  /*      BeginTrans */   sqliteGdbmBeginTrans,
  /*          Commit */   sqliteGdbmEndTrans,
  /*        Rollback */   sqliteGdbmEndTrans,
  /*      BeginIndex */   sqliteGdbmBeginIndex,
  /*       NextIndex */   sqliteGdbmNextIndex,
  /*        PutIndex */   sqliteGdbmPutIndex,
  /*     DeleteIndex */   sqliteGdbmDeleteIndex,
};


/*
** This routine opens a new database.  For the GDBM driver
** implemented here, the database name is the name of the directory
** containing all the files of the database.

** sqlite and the code that does the actually reading and writing
** of information to the disk.
**
** This file uses an in-memory hash table as the database backend. 
** Nothing is ever written to disk using this backend.  All information
** is forgotten when the program exits.
**
** $Id: dbbemem.c,v 1.16 2001/08/19 18:19:46 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>


typedef struct Array Array;
typedef struct ArrayElem ArrayElem;

** associated with the same disk file.
*/
struct DbbeCursor {
  Dbbex *pBe;        /* The database of which this record is a part */
  MTable *pTble;     /* The database file for this table */
  ArrayElem *elem;   /* Most recently accessed record */
  int needRewind;    /* Next key should be the first */
  int nextIndex;     /* Next recno in an index entry */
};

/*
** Forward declaration
*/
static void sqliteMemCloseCursor(DbbeCursor *pCursr);

  data.n = 0;
  data = ArrayInsert(&pCursr->pTble->data, key, data);
  if( data.p ){
    sqliteFree(data.p);
  }
  return SQLITE_OK;
}

/*
** Begin scanning an index for the given key.  Return 1 on success and
** 0 on failure.
*/
static int sqliteMemBeginIndex(DbbeCursor *pCursr, int nKey, char *pKey){
  if( !sqliteMemFetch(pCursr, nKey, pKey) ) return 0;
  pCursr->nextIndex = 0;
  return 1;
}

/*
** Return an integer key which is the next record number in the index search
** that was started by a prior call to BeginIndex.  Return 0 if all records
** have already been searched.
*/
static int sqliteMemNextIndex(DbbeCursor *pCursr){
  int *aIdx;
  int nIdx;
  int k;
  nIdx = sqliteMemDataLength(pCursr)/sizeof(int);
  aIdx = (int*)sqliteMemReadData(pCursr, 0);
  if( nIdx>1 ){
    k = *(aIdx++);
    if( k>nIdx-1 ) k = nIdx-1;
  }else{
    k = nIdx;
  }
  while( pCursr->nextIndex < k ){
    int recno = aIdx[pCursr->nextIndex++];
    if( recno!=0 ) return recno;
  }
  pCursr->nextIndex = 0;
  return 0;
}

/*
** Write a new record number and key into an index table.  Return a status
** code.
*/
static int sqliteMemPutIndex(DbbeCursor *pCursr, int nKey, char *pKey, int N){
  int r = sqliteMemFetch(pCursr, nKey, pKey);
  if( r==0 ){
    /* Create a new record for this index */
    sqliteMemPut(pCursr, nKey, pKey, sizeof(int), (char*)&N);
  }else{
    /* Extend the existing record */
    int nIdx;
    int *aIdx;
    int k;
            
    nIdx = sqliteMemDataLength(pCursr)/sizeof(int);
    if( nIdx==1 ){
      aIdx = sqliteMalloc( sizeof(int)*4 );
      if( aIdx==0 ) return SQLITE_NOMEM;
      aIdx[0] = 2;
      sqliteMemCopyData(pCursr, 0, sizeof(int), (char*)&aIdx[1]);
      aIdx[2] = N;
      sqliteMemPut(pCursr, nKey, pKey, sizeof(int)*4, (char*)aIdx);
      sqliteFree(aIdx);
    }else{
      aIdx = (int*)sqliteMemReadData(pCursr, 0);
      k = aIdx[0];
      if( k<nIdx-1 ){
        aIdx[k+1] = N;
        aIdx[0]++;
        sqliteMemPut(pCursr, nKey, pKey, sizeof(int)*nIdx, (char*)aIdx);
      }else{
        nIdx *= 2;
        aIdx = sqliteMalloc( sizeof(int)*nIdx );
        if( aIdx==0 ) return SQLITE_NOMEM;
        sqliteMemCopyData(pCursr, 0, sizeof(int)*(k+1), (char*)aIdx);
        aIdx[k+1] = N;
        aIdx[0]++;
        sqliteMemPut(pCursr, nKey, pKey, sizeof(int)*nIdx, (char*)aIdx);
        sqliteFree(aIdx);
      }
    }
  }
  return SQLITE_OK;
}

/*
** Delete an index entry.  Return a status code.
*/
static int sqliteMemDeleteIndex(DbbeCursor *pCursr,int nKey,char *pKey, int N){
  int *aIdx;
  int nIdx;
  int j, k;
  int rc;
  rc = sqliteMemFetch(pCursr, nKey, pKey);
  if( !rc ) return SQLITE_OK;
  nIdx = sqliteMemDataLength(pCursr)/sizeof(int);
  if( nIdx==0 ) return SQLITE_OK;
  aIdx = (int*)sqliteMemReadData(pCursr, 0);
  if( (nIdx==1 && aIdx[0]==N) || (aIdx[0]==1 && aIdx[1]==N) ){
    sqliteMemDelete(pCursr, nKey, pKey);
  }else{
    k = aIdx[0];
    for(j=1; j<=k && aIdx[j]!=N; j++){}
    if( j>k ) return SQLITE_OK;
    aIdx[j] = aIdx[k];
    aIdx[k] = 0;
    aIdx[0]--;
    if( aIdx[0]*3 + 1 < nIdx ){
      nIdx /= 2;
    }
    sqliteMemPut(pCursr, nKey, pKey, sizeof(int)*nIdx, (char*)aIdx);
  }
  return SQLITE_OK;
}

/*
** This variable contains pointers to all of the access methods
** used to implement the MEMORY backend.
*/
static struct DbbeMethods memoryMethods = {
  /*           Close */   sqliteMemClose,

  /*       KeyLength */   sqliteMemKeyLength,
  /*      DataLength */   sqliteMemDataLength,
  /*         NextKey */   sqliteMemNextKey,
  /*          Rewind */   sqliteMemRewind,
  /*             New */   sqliteMemNew,
  /*             Put */   sqliteMemPut,
  /*          Delete */   sqliteMemDelete,
  /*      BeginTrans */   0,
  /*          Commit */   0,
  /*        Rollback */   0,
  /*      BeginIndex */   sqliteMemBeginIndex,
  /*       NextIndex */   sqliteMemNextIndex,
  /*        PutIndex */   sqliteMemPutIndex,
  /*     DeleteIndex */   sqliteMemDeleteIndex,
};

/*
** This routine opens a new database.  For the GDBM driver
** implemented here, the database name is the name of the directory
** containing all the files of the database.
**

** inplicit conversion from one type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.59 2001/08/19 18:19:46 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** SQL is translated into a sequence of instructions to be
** executed by a virtual machine.  Each instruction is an instance

** this array, then copy and paste it into this file, if you want.
*/
static char *zOpName[] = { 0,
  "OpenIdx",           "OpenTbl",           "Close",             "Fetch",
  "Fcnt",              "New",               "Put",               "Distinct",
  "Found",             "NotFound",          "Delete",            "Field",
  "KeyAsData",         "Key",               "FullKey",           "Rewind",
  "Next",              "Destroy",           "Reorganize",        "BeginIdx",
  "NextIdx",           "PutIdx",            "DeleteIdx",         "MemLoad",
  "MemStore",          "ListOpen",          "ListWrite",         "ListRewind",
  "ListRead",          "ListClose",         "SortOpen",          "SortPut",
  "SortMakeRec",       "SortMakeKey",       "Sort",              "SortNext",
  "SortKey",           "SortCallback",      "SortClose",         "FileOpen",
  "FileRead",          "FileField",         "FileClose",         "AggReset",
  "AggFocus",          "AggIncr",           "AggNext",           "AggSet",

            p->nFetch++;
          }
        }
        p->aCsr[i].keyIsValid = 0;
        break;
      }

      /* Opcode: BeginIdx P1 * *
      **
      ** Begin searching an index for records with the key found on the
      ** top of the stack.  The stack is popped once.  Subsequent calls
      ** to NextIdx will push record numbers onto the stack until all
      ** records with the same key have been returned.
      */
      case OP_BeginIdx: {
        int i = pOp->p1;
        int tos = p->tos;
        VERIFY( if( tos<0 ) goto not_enough_stack; )
        if( i>=0 && i<p->nCursor && p->aCsr[i].pCursor ){
          if( Stringify(p, tos) ) goto no_mem;
          pBex->BeginIndex(p->aCsr[i].pCursor, aStack[tos].n, zStack[tos]);
          p->aCsr[i].keyIsValid = 0;
        }
        POPSTACK;
        break;
      }

      /* Opcode: NextIdx P1 P2 *
      **
      ** The P1 cursor points to an SQL index for which a BeginIdx operation
      ** has been issued.  This operation retrieves the next record number and







      ** pushes that record number onto the stack.  Or, if there are no more

      ** record numbers for the given key, this opcode pushes nothing onto the
      ** stack but instead jumps to instruction P2.
      */
      case OP_NextIdx: {
        int i = pOp->p1;
        int tos = ++p->tos;
        DbbeCursor *pCrsr;

        VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
        zStack[tos] = 0;
        if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){



          int recno = pBex->NextIndex(pCrsr);









          if( recno!=0 ){
            p->aCsr[i].lastKey = aStack[tos].i = recno;
            p->aCsr[i].keyIsValid = 1;
            aStack[tos].flags = STK_Int;

          }else{



            pc = pOp->p2 - 1;
            POPSTACK;
          }

        }
        break;
      }

      /* Opcode: PutIdx P1 * *
      **
      ** The top of the stack hold an SQL index key (probably made using the
      ** MakeKey instruction) and next on stack holds an integer which
      ** the record number for an SQL table entry.  This opcode makes an entry
      ** in the index table P1 that associates the key with the record number.

      ** But the record number and the key are popped from the stack.
      */
      case OP_PutIdx: {
        int i = pOp->p1;
        int tos = p->tos;
        int nos = tos - 1;
        DbbeCursor *pCrsr;
        VERIFY( if( nos<0 ) goto not_enough_stack; )
        if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){


          Integerify(p, nos);

          if( Stringify(p, tos) ) goto no_mem;



          pBex->PutIndex(pCrsr, aStack[tos].n, zStack[tos], aStack[nos].i);






































        }
        POPSTACK;
        POPSTACK;
        break;
      }

      /* Opcode: DeleteIdx P1 * *
      **
      ** The top of the stack is a key and next on stack is integer
      ** which is a record number for an SQL table.  The operation removes




      ** any entry to the index table P1 that associates the key with the
      ** record number.


      */
      case OP_DeleteIdx: {
        int i = pOp->p1;
        int tos = p->tos;
        int nos = tos - 1;
        DbbeCursor *pCrsr;
        VERIFY( if( nos<0 ) goto not_enough_stack; )
        if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){





          Integerify(p, nos);

          if( Stringify(p, tos) ) goto no_mem;
















          pBex->DeleteIndex(pCrsr, aStack[tos].n, zStack[tos], aStack[nos].i);


        }
        POPSTACK;
        POPSTACK;
        break;
      }

      /* Opcode: Destroy * * P3

*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.18 2001/08/19 18:19:46 drh Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines

#define OP_FullKey            15
#define OP_Rewind             16
#define OP_Next               17

#define OP_Destroy            18
#define OP_Reorganize         19

#define OP_BeginIdx           20
#define OP_NextIdx            21
#define OP_PutIdx             22
#define OP_DeleteIdx          23

#define OP_MemLoad            24
#define OP_MemStore           25

**   http://www.hwaci.com/drh/
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.15 2001/08/19 18:19:46 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.

            sqliteExprCode(pParse, aExpr[k].p->pLeft);
            aExpr[k].p = 0;
            break;
          }
        }
      }
      sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nColumn, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_BeginIdx, base+pTabList->nId+i, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_NextIdx, base+pTabList->nId+i, brk, 0, cont);
      if( i==pTabList->nId-1 && pushKey ){
        haveKey = 1;
      }else{
        sqliteVdbeAddOp(v, OP_Fetch, base+idx, 0, 0, 0);
        haveKey = 0;
      }

#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.10 2001/08/19 18:19:46 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {

      PRIMARY KEY(b)
    );
  }
  for {set i 1} {$i<=50} {incr i} {
    execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
  }
  execsql {SELECT c, fcnt() FROM t3 WHERE b==10}
} {0.10 1}

finish_test

#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.2 2001/08/19 18:19:46 drh Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {

} {256}
do_test rowid-4.5 {
  execsql {CREATE INDEX idxt2 ON t2(y)}
  execsql {
    SELECT t1.x, fcnt() FROM t2, t1 
    WHERE t2.y==256 AND t1.rowid==t2.rowid
  }
} {4 2}
do_test rowid-4.5.1 {
  execsql {
    SELECT t1.x, fcnt() FROM t2, t1 
    WHERE t1.OID==t2.rowid AND t2.y==81
  }
} {3 2}
do_test rowid-4.6 {
  execsql {
    SELECT t1.x FROM t1, t2
    WHERE t2.y==256 AND t1.rowid==t2.rowid
  }
} {4}

do_test rowid-5.1 {
  execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
  execsql {SELECT max(x) FROM t1}
} {8}

#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select2.test,v 1.11 2001/08/19 18:19:46 drh Exp $

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

# Create a table with some data
#
execsql {CREATE TABLE tbl1(f1 int, f2 int)}

  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
} {500}
do_test select2-3.2c {
  execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
} {500}
do_test select2-3.2d {
  execsql {SELECT fcnt() FROM tbl2 WHERE 1000=f2}
} {1}
do_test select2-3.2e {
  execsql {SELECT fcnt() FROM tbl2 WHERE f2=1000}
} {1}

# omit the time-dependent tests
#
testif gdbm:
do_probtest select2-3.2f {
  set t1 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}} 1] 0]
  set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2=1000}} 1] 0]

#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.2 2001/08/19 18:19:46 drh Exp $

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

# Build some test data
#
do_test where-1.0 {

# function to verify the results.  fcnt(*) returns the number of Fetch
# operations that have occurred up to the point where fcnt(*) is invoked.
# By verifing that fcnt(*) returns a small number we know that an index
# was used instead of an exhaustive search.
#
do_test where-1.1 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=10}
} {3 121 1}
do_test where-1.2 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=11}
} {3 144 1}
do_test where-1.3 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE 11=w}
} {3 144 1}
do_test where-1.4 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE 11=w AND x>2}
} {3 144 1}
do_test where-1.5 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 1}
do_test where-1.6 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y<200 AND x>2 AND w=11}
} {3 144 1}
do_test where-1.7 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=11 AND y<200 AND x>2}
} {3 144 1}
do_test where-1.8 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w>10 AND y=144 AND x=3}
} {3 144 1}
do_test where-1.9 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y=144 AND w>10 AND x=3}
} {3 144 1}
do_test where-1.10 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 1}
do_test where-1.11 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE x=3 AND y=100 AND w<10}
} {3 100 1}

# Do the same kind of thing except use a join as the data source.
#
do_test where-2.1 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND y=s AND r=8977
  }
} {34 67 2}
do_test where-2.2 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND s=y AND r=8977
  }
} {34 67 2}
do_test where-2.3 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 2}
do_test where-2.4 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 2}
do_test where-2.5 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
  }
} {34 67 2}
do_test where-2.6 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND p=77 AND s=y AND w>5
  }
} {24 77 2}
do_test where-2.7 {
  execsql {
    SELECT w, p, fcnt(*) FROM t1, t2
    WHERE x=q AND p>77 AND s=y AND w=5
  }
} {5 96 2}

# Lets do a 3-way join.
#
do_test where-3.1 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
  }
} {11 90 11 3}
do_test where-3.2 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
  }
} {12 89 12 3}
do_test where-3.3 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
  }
} {15 86 86 3}

finish_test