SQLite

**     DROP TABLE
**     CREATE INDEX
**     DROP INDEX
**     creating expressions and ID lists
**     COPY
**     VACUUM
**
** $Id: build.c,v 1.15 2000/06/05 18:54:46 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the code to implement 
** the statement.  Prior action routines should have already

/*
** Recursively delete an expression tree.
*/
void sqliteExprDelete(Expr *p){
  if( p==0 ) return;
  if( p->pLeft ) sqliteExprDelete(p->pLeft);
  if( p->pRight ) sqliteExprDelete(p->pRight);
  if( p->pList ) sqliteExprListDelete(p->pList);
  if( p->pSelect ) sqliteSelectDelete(p->pSelect);
  sqliteFree(p);
}

/*
** Locate the in-memory structure that describes the
** format of a particular database table given the name
** of that table.  Return NULL if not found.

** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines used for processing expressions
**
** $Id: expr.c,v 1.6 2000/06/05 18:54:46 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine walks an expression tree and resolves references to
** table fields.  Nodes of the form ID.ID or ID resolve into an
** index to the table in the table list and a field offset.  The opcode
** for such nodes is changed to TK_FIELD.  The iTable value is changed
** to the index of the referenced table in pTabList plus the pParse->nTab
** value.  The iField value is changed to the index of the field of the 
** referenced table.
**
** This routine also looks for SELECTs that are part of an expression.
** If it finds any, it generates code to write the value of that select
** into a memory cell.
**
** Unknown fields or tables provoke an error.  The function returns
** the number of errors seen and leaves an error message on pParse->zErrMsg.
*/
int sqliteExprResolveIds(Parse *pParse, IdList *pTabList, Expr *pExpr){
  if( pExpr==0 ) return 0;
  switch( pExpr->op ){
    /* A lone identifier */
    case TK_ID: {
      int cnt = 0;   /* Number of matches */
      int i;         /* Loop counter */
      char *z = 0;
      sqliteSetNString(&z, pExpr->token.z, pExpr->token.n, 0);
      for(i=0; i<pTabList->nId; i++){
        int j;
        Table *pTab = pTabList->a[i].pTab;
        if( pTab==0 ) continue;
        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->aCol[j].zName, z)==0 ){
            cnt++;
            pExpr->iTable = i + pParse->nTab;
            pExpr->iField = j;
          }
        }
      }
      sqliteFree(z);
      if( cnt==0 ){
        sqliteSetNString(&pParse->zErrMsg, "no such field: ", -1,  

        }else{
          zTab = pTab->zName;
        }
        if( sqliteStrICmp(zTab, zLeft)!=0 ) continue;
        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->aCol[j].zName, zRight)==0 ){
            cnt++;
            pExpr->iTable = i + pParse->nTab;
            pExpr->iField = j;
          }
        }
      }
      sqliteFree(zLeft);
      sqliteFree(zRight);
      if( cnt==0 ){

      sqliteExprDelete(pLeft);
      pExpr->pLeft = 0;
      sqliteExprDelete(pRight);
      pExpr->pRight = 0;
      pExpr->op = TK_FIELD;
      break;
    }

    case TK_SELECT: {
      pExpr->iField = pParse->nMem++;
      if( sqliteSelect(pParse, pExpr->pSelect, -1, pExpr->iField) ){
        return 1;
      }
      break;
    }

    /* For all else, just recursively walk the tree */
    default: {
      if( pExpr->pLeft 
            && sqliteExprResolveIds(pParse, pTabList, pExpr->pLeft) ){
        return 1;
      }

      for(i=0; i<pList->nExpr; i++){
        sqliteExprCode(pParse, pList->a[i].pExpr);
        if( i>0 ){
          sqliteVdbeAddOp(v, op, 0, 0, 0, 0);
        }
      }
      break;
    }
    case TK_SELECT: {
      sqliteVdbeAddOp(v, OP_MemLoad, pExpr->iField, 0, 0, 0);
      break;
    }
  }
  return;
}

/*
** Generate code for a boolean expression such that a jump is made

**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements.
**
** $Id: insert.c,v 1.6 2000/06/05 18:54:46 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following form:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)

        sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
           " has no column named ", pField->a[i].zName, 0);
        pParse->nErr++;
        goto insert_cleanup;
      }
    }
  }
  v = pParse->pVdbe;
  if( v==0 ){
    v = pParse->pVdbe = sqliteVdbeCreate(pParse->db->pBe);
  }
  if( v ){
    Index *pIdx;
    sqliteVdbeAddOp(v, OP_Open, 0, 1, pTab->zName, 0);
    for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
      sqliteVdbeAddOp(v, OP_Open, idx, 1, pIdx->zName, 0);
    }
    sqliteVdbeAddOp(v, OP_New, 0, 0, 0, 0);

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.10 2000/06/05 18:54:46 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetNString(&pParse->zErrMsg,"syntax error near \"",0,TOKEN.z,TOKEN.n,
                   "\"", 1, 0);

// The next command format is dropping tables.
//
cmd ::= DROP TABLE id(X).          {sqliteDropTable(pParse,&X);}

// The select statement
//
cmd ::= select(X).  {
  sqliteSelect(pParse, X, -1, -1);
  sqliteSelectDelete(X);
}

%type select {Select*}
%destructor select {sqliteSelectDelete($$);}

select(A) ::= SELECT distinct(D) selcollist(W) from(X) where_opt(Y)

expr(A) ::= expr(X) STAR expr(Y).  {A = sqliteExpr(TK_STAR, X, Y, 0);}
expr(A) ::= expr(X) SLASH expr(Y). {A = sqliteExpr(TK_SLASH, X, Y, 0);}
expr(A) ::= expr(X) ISNULL.        {A = sqliteExpr(TK_ISNULL, X, 0, 0);}
expr(A) ::= expr(X) NOTNULL.       {A = sqliteExpr(TK_NOTNULL, X, 0, 0);}
expr(A) ::= NOT expr(X).           {A = sqliteExpr(TK_NOT, X, 0, 0);}
expr(A) ::= MINUS expr(X). [UMINUS]   {A = sqliteExpr(TK_UMINUS, X, 0, 0);}
expr(A) ::= PLUS expr(X). [UMINUS]    {A = X;}
expr(A) ::= LP select(X) RP. {
  A = sqliteExpr(TK_SELECT, 0, 0, 0);
  A->pSelect = X;
}

%type exprlist {ExprList*}
%destructor exprlist {sqliteExprListDelete($$);}
%type expritem {Expr*}
%destructor expritem {sqliteExprDelete($$);}

exprlist(A) ::= exprlist(X) COMMA expritem(Y). 

**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements.
**
** $Id: select.c,v 1.8 2000/06/05 18:54:46 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.

  sqliteExprListDelete(p->pOrderBy);
  sqliteFree(p);
}

/*
** Generate code for the given SELECT statement.
**
** If iDest<0 and iMem<0, then the results of the query are sent to
** the callback function.  If iDest>=0 then the results are written to
** an open cursor with the index iDest.  The calling function is
** responsible for having that cursor open.  If iDest<0 and iMem>=0 
** then the result should be a single value which is then stored in 
** memory location iMem of the virtual machine.
**
** This routine returns the number of errors.  If any errors are
** encountered, then an appropriate error message is left in
** pParse->zErrMsg.
**
** This routine does NOT free the Select structure passed in.  The
** calling function needs to do that.
*/
int sqliteSelect(
  Parse *pParse,         /* The parser context */
  Select *p,             /* The SELECT statement being coded. */
  int iDest,             /* Write results to this cursor */
  int iMem               /* Save result in this memory location, if >=0 */
){
  int i, j;
  WhereInfo *pWInfo;
  Vdbe *v;
  int isAgg = 0;         /* True for select lists like "count(*)" */
  ExprList *pEList;      /* List of fields to extract.  NULL means "*" */
  IdList *pTabList;      /* List of tables to select from */
  Expr *pWhere;          /* The WHERE clause.  May be NULL */
  ExprList *pOrderBy;    /* The ORDER BY clause.  May be NULL */
  int isDistinct;        /* True if the DISTINCT keyword is present */
  int distinct;          /* Table to use for the distinct set */

  pEList = p->pEList;
  pTabList = p->pSrc;
  pWhere = p->pWhere;
  pOrderBy = p->pOrderBy;
  isDistinct = p->isDistinct;

  /* 
  ** Do not even attempt to generate any code if we have already seen
  ** errors before this routine starts.
  */
  if( pParse->nErr>0 ) return 0;

  /* Look up every table in the table list.
  */
  for(i=0; i<pTabList->nId; i++){
    pTabList->a[i].pTab = sqliteFindTable(pParse->db, pTabList->a[i].zName);
    if( pTabList->a[i].pTab==0 ){
      sqliteSetString(&pParse->zErrMsg, "no such table: ", 
         pTabList->a[i].zName, 0);
      pParse->nErr++;
      return 1;
    }
  }

  /* Allocate a temporary table to use for the DISTINCT set, if
  ** necessary.
  */
  if( isDistinct ){
    distinct = pParse->nTab++;
  }

  /* If the list of fields to retrieve is "*" then replace it with
  ** a list of all fields from all tables.
  */
  if( pEList==0 ){
    for(i=0; i<pTabList->nId; i++){
      Table *pTab = pTabList->a[i].pTab;
      for(j=0; j<pTab->nCol; j++){
        Expr *pExpr = sqliteExpr(TK_FIELD, 0, 0, 0);
        pExpr->iTable = i + pParse->nTab;
        pExpr->iField = j;
        pEList = sqliteExprListAppend(pEList, pExpr, 0);
      }
    }
  }

  /* If writing to memory, only a single column may be output.
  */
  if( iMem>=0 && pEList->nExpr>1 ){
    sqliteSetString(&pParse->zErrMsg, "only a single result allowed for "
       "a SELECT that is part of an expression", 0);
    pParse->nErr++;
    return 1;
  }

  /* Resolve the field names and do a semantics check on all the expressions.
  */
  for(i=0; i<pEList->nExpr; i++){
    if( sqliteExprResolveIds(pParse, pTabList, pEList->a[i].pExpr) ){
      return 1;
    }

      }
      if( sqliteExprCheck(pParse, pOrderBy->a[i].pExpr, 0, 0) ){
        return 1;
      }
    }
  }

  /* ORDER BY is ignored if 
  **
  **   (1) this is an aggregate query like count(*)
  **       since only one row will be returned.
  **
  **   (2) We are writing the result to another table, since the
  **       order will get scrambled again after inserting.
  **
  **   (3) We are writing to a memory cell, since there is only
  **       one result.
  */
  if( isAgg || iDest>=0 || iMem>=0 ){
    pOrderBy = 0;
  }

  /* Turn off distinct if this is an aggregate or writing to memory.
  */
  if( isAgg || iMem>=0 ){
    isDistinct = 0;
  }

  /* Begin generating code.
  */
  v = pParse->pVdbe;
  if( v==0 ){
    v = pParse->pVdbe = sqliteVdbeCreate(pParse->db->pBe);
  }
  if( v==0 ){
    sqliteSetString(&pParse->zErrMsg, "out of memory", 0);
    pParse->nErr++;
    return 1;
  }
  if( pOrderBy ){
    sqliteVdbeAddOp(v, OP_SortOpen, 0, 0, 0, 0);
  }

  /* Identify column names if we will be using a callback.  This
  ** step is skipped if the output is going to a table or a memory cell.
  */
  if( iDest<0 && iMem<0 ){
    sqliteVdbeAddOp(v, OP_ColumnCount, pEList->nExpr, 0, 0, 0);
    for(i=0; i<pEList->nExpr; i++){
      Expr *p;
      if( pEList->a[i].zName ){
        char *zName = pEList->a[i].zName;
        int addr = sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
        if( zName[0]=='\'' || zName[0]=='"' ){
          sqliteVdbeDequoteP3(v, addr);
        }
        continue;
      }
      p = pEList->a[i].pExpr;
      if( p->op!=TK_FIELD ){
        char zName[30];
        sprintf(zName, "field%d", i+1);
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
      }else{
        if( pTabList->nId>1 ){
          char *zName = 0;
          Table *pTab = pTabList->a[p->iTable].pTab;
          char *zTab;
  
          zTab = pTabList->a[p->iTable].zAlias;
          if( zTab==0 ) zTab = pTab->zName;
          sqliteSetString(&zName, zTab, ".", pTab->aCol[p->iField].zName, 0);
          sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
          sqliteFree(zName);
        }else{
          Table *pTab = pTabList->a[0].pTab;
          char *zName = pTab->aCol[p->iField].zName;
          sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
        }
      }
    }
  }

  /* Initialize the stack to contain aggregate seed values
  */
  if( isAgg ){

        default: {
          sqliteVdbeAddOp(v, OP_Integer, 0, 0, 0, 0);
          break;
        }
      }
    }
  }

  /* Initialize the memory cell to NULL
  */
  if( iMem>=0 ){
    sqliteVdbeAddOp(v, OP_Null, 0, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_MemStore, iMem, 0, 0, 0);
  }

  /* Begin the database scan
  */
  if( isDistinct ){

    sqliteVdbeAddOp(v, OP_Open, distinct, 1, 0, 0);
  }
  pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 0);
  if( pWInfo==0 ) return 1;

  /* Pull the requested fields.
  */
  if( !isAgg ){
    for(i=0; i<pEList->nExpr; i++){
      sqliteExprCode(pParse, pEList->a[i].pExpr);
    }
  }

  /* If the current result is not distinct, script the remainder
  ** of this processing.
  */
  if( isDistinct ){
    int lbl = sqliteVdbeMakeLabel(v);
    sqliteVdbeAddOp(v, OP_MakeKey, pEList->nExpr, 1, 0, 0);
    sqliteVdbeAddOp(v, OP_Distinct, distinct, lbl, 0, 0);
    sqliteVdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Goto, 0, pWInfo->iContinue, 0, 0);
    sqliteVdbeAddOp(v, OP_String, 0, 0, "", lbl);
    sqliteVdbeAddOp(v, OP_Put, distinct, 0, 0, 0);
  }
  
  /* If there is no ORDER BY clause, then we can invoke the callback
  ** right away.  If there is an ORDER BY, then we need to put the
  ** data into an appropriate sorter record.
  */

        case FN_Count: op = OP_AddImm; p1 = 1; break;
        case FN_Sum:   op = OP_Add;    p1 = 0; break;
        case FN_Min:   op = OP_Min;    p1 = 1; break;
        case FN_Max:   op = OP_Max;    p1 = 0; break;
      }
      sqliteVdbeAddOp(v, op, p1, 0, 0, 0);
    }
  }else if( iDest>=0 ){
    sqliteVdbeAddOp(v, OP_MakeRecord, pEList->nExpr, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_New, iDest, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Pull, 1, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Put, iDest, 0, 0, 0);
  }else if( iMem>=0 ){
    sqliteVdbeAddOp(v, OP_MemStore, iMem, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Goto, 0, pWInfo->iBreak, 0, 0);
  }else{
    sqliteVdbeAddOp(v, OP_Callback, pEList->nExpr, 0, 0, 0);
  }

  /* End the database scan loop.
  */
  sqliteWhereEnd(pWInfo);

  /* If there is an ORDER BY clause, then we need to sort the results
  ** and send them to the callback one by one.
  */
  if( pOrderBy ){
    int end = sqliteVdbeMakeLabel(v);
    int addr;
    sqliteVdbeAddOp(v, OP_Sort, 0, 0, 0, 0);
    addr = sqliteVdbeAddOp(v, OP_SortNext, 0, end, 0, 0);
    sqliteVdbeAddOp(v, OP_SortCallback, pEList->nExpr, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Goto, 0, addr, 0, 0);
    sqliteVdbeAddOp(v, OP_SortClose, 0, 0, 0, end);
  }

  /* If this is an aggregate, then we need to invoke the callback
  ** exactly once.
  */
  if( isAgg ){
    if( iDest>=0 ){
      sqliteVdbeAddOp(v, OP_MakeRecord, pEList->nExpr, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_New, iDest, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_Pull, 1, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_Put, iDest, 0, 0, 0);
    }else if( iMem>=0 ){
      sqliteVdbeAddOp(v, OP_MemStore, iMem, 0, 0, 0);
    }else{
      sqliteVdbeAddOp(v, OP_Callback, pEList->nExpr, 0, 0, 0);
    }
  }
  return 0;
}

** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.15 2000/06/05 18:54:46 drh Exp $
*/
#include "sqlite.h"
#include "dbbe.h"
#include "vdbe.h"
#include "parse.h"
#include <gdbm.h>
#include <stdio.h>

struct Expr {
  int op;                /* Operation performed by this node */
  Expr *pLeft, *pRight;  /* Left and right subnodes */
  ExprList *pList;       /* A list of expressions used as a function argument */
  Token token;           /* An operand token */
  int iTable, iField;    /* When op==TK_FIELD, then this node means the
                         ** iField-th field of the iTable-th table */
  Select *pSelect;       /* When the expression is a sub-select */
};

/*
** A list of expressions.  Each expression may optionally have a
** name.  An expr/name combination can be used in several ways, such
** as the list of "expr AS ID" fields following a "SELECT" or in the
** list of "ID = expr" items in an UPDATE.  A list of expressions can

*/
struct IdList {
  int nId;         /* Number of identifiers on the list */
  struct {
    char *zName;      /* Text of the identifier. */
    char *zAlias;     /* The "B" part of a "A AS B" phrase.  zName is the "A" */
    Table *pTab;      /* Table corresponding to zName */
    int idx;          /* Index of a field named zName in a table */
  } *a;            /* One entry for each identifier on the list */
};

/*
** The WHERE clause processing routine has two halves.  The
** first part does the start of the WHERE loop and the second
** half does the tail of the WHERE loop.  An instance of
** this structure is returned by the first half and passed
** into the second half to give some continuity.
*/
struct WhereInfo {
  Parse *pParse;
  IdList *pTabList;    /* List of tables in the join */
  int iContinue;       /* Jump here to continue with next record */
  int iBreak;          /* Jump here to break out of the loop */
  int base;            /* Index of first Open opcode */
  Index *aIdx[32];     /* Indices used for each table */
};

/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
*/
struct Select {

  Token sFirstToken;   /* The first token parsed */
  Token sLastToken;    /* The last token parsed */
  Table *pNewTable;    /* A table being constructed by CREATE TABLE */
  Vdbe *pVdbe;         /* An engine for executing database bytecode */
  int explain;         /* True if the EXPLAIN flag is found on the query */
  int initFlag;        /* True if reparsing CREATE TABLEs */
  int nErr;            /* Number of errors seen */
  int nTab;            /* Number of previously allocated cursors */
  int nMem;            /* Number of memory cells used so far */
};

/*
** Internal function prototypes
*/
int sqliteStrICmp(const char *, const char *);
int sqliteStrNICmp(const char *, const char *, int);

void sqliteDeleteTable(sqlite*, Table*);
void sqliteInsert(Parse*, Token*, ExprList*, IdList*);
IdList *sqliteIdListAppend(IdList*, Token*);
void sqliteIdListAddAlias(IdList*, Token*);
void sqliteIdListDelete(IdList*);
void sqliteCreateIndex(Parse*, Token*, Token*, IdList*, Token*, Token*);
void sqliteDropIndex(Parse*, Token*);
int sqliteSelect(Parse*, Select*, int, int);
Select *sqliteSelectNew(ExprList*,IdList*,Expr*,ExprList*,Expr*,ExprList*,int);
void sqliteSelectDelete(Select*);
void sqliteDeleteFrom(Parse*, Token*, Expr*);
void sqliteUpdate(Parse*, Token*, ExprList*, Expr*);
WhereInfo *sqliteWhereBegin(Parse*, IdList*, Expr*, int);
void sqliteWhereEnd(WhereInfo*);
void sqliteExprCode(Parse*, Expr*);

** 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.16 2000/06/05 18:54:47 drh Exp $
*/
#include "sqliteInt.h"
#include <unistd.h>

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

** strings separately is so that they can be easily passed
** to the callback function.
*/
struct Stack {
  int i;         /* Integer value */
  int n;         /* Number of characters in string value, including '\0' */
  int flags;     /* Some combination of STK_Null, STK_Str, STK_Dyn, etc. */
  double r;      /* Real value */
};
typedef struct Stack Stack;

/*
** Memory cells use the same structure as the stack except that space
** for an arbitrary string is added.
*/
struct Mem {
  Stack s;       /* All values of the memory cell besides string */
  char *z;       /* String value for this memory cell */
};
typedef struct Mem Mem;

/*
** Allowed values for Stack.flags
*/
#define STK_Null      0x0001   /* Value is NULL */
#define STK_Str       0x0002   /* Value is a string */
#define STK_Int       0x0004   /* Value is an integer */
#define STK_Real      0x0008   /* Value is a real number */

  int nSort;          /* Number of slots in apSort[] */
  Sorter **apSort;    /* An open sorter list */
  FILE *pFile;        /* At most one open file handler */
  int nField;         /* Number of file fields */
  char **azField;     /* Data for each file field */
  char *zLine;        /* A single line from the input file */
  int nLineAlloc;     /* Number of spaces allocated for zLine */
  int nMem;           /* Number of memory locations currently allocated */
  Mem *aMem;          /* The memory locations */
};

/*
** Create a new virtual database engine.
*/
Vdbe *sqliteVdbeCreate(Dbbe *pBe){
  Vdbe *p;

      sqliteDbbeCloseTable(p->aTab[i].pTable);
      p->aTab[i].pTable = 0;
    }
  }
  sqliteFree(p->aTab);
  p->aTab = 0;
  p->nTable = 0;
  for(i=0; i<p->nMem; i++){
    if( p->aMem[i].s.flags & STK_Dyn ){
      sqliteFree(p->aMem[i].z);
    }
  }
  sqliteFree(p->aMem);
  p->aMem = 0;
  p->nMem = 0;
  for(i=0; i<p->nList; i++){
    if( p->apList[i] ){
      sqliteDbbeCloseTempFile(p->pBe, p->apList[i]);
      p->apList[i] = 0;
    }
  }
  sqliteFree(p->apList);

** this array, then copy and paste it into this file, if you want.
*/
static char *zOpName[] = { 0,
  "Open",           "Close",          "Fetch",          "New",
  "Put",            "Distinct",       "Delete",         "Field",
  "Key",            "Rewind",         "Next",           "Destroy",
  "Reorganize",     "ResetIdx",       "NextIdx",        "PutIdx",
  "DeleteIdx",      "MemLoad",        "MemStore",       "ListOpen",
  "ListWrite",      "ListRewind",     "ListRead",       "ListClose",
  "SortOpen",       "SortPut",        "SortMakeRec",    "SortMakeKey",
  "Sort",           "SortNext",       "SortKey",        "SortCallback",
  "SortClose",      "FileOpen",       "FileRead",       "FileField",
  "FileClose",      "MakeRecord",     "MakeKey",        "Goto",
  "If",             "Halt",           "ColumnCount",    "ColumnName",
  "Callback",       "Integer",        "String",         "Null",
  "Pop",            "Dup",            "Pull",           "Add",
  "AddImm",         "Subtract",       "Multiply",       "Divide",
  "Min",            "Max",            "Like",           "Glob",
  "Eq",             "Ne",             "Lt",             "Le",
  "Gt",             "Ge",             "IsNull",         "NotNull",
  "Negative",       "And",            "Or",             "Not",
  "Concat",         "Noop",         
};

/*
** Given the name of an opcode, return its number.  Return 0 if
** there is no match.
**
** This routine is used for testing and debugging.

        if( z==0 ) z = "";
        p->tos++;
        p->aStack[p->tos].n = strlen(z) + 1;
        p->zStack[p->tos] = z;
        p->aStack[p->tos].flags = STK_Str;
        break;
      }

      /* Opcode: MemStore P1 * *
      **
      ** Pop a single value of the stack and store that value into memory
      ** location P1.  P1 should be a small integer since space is allocated
      ** for all memory locations between 0 and P1 inclusive.
      */
      case OP_MemStore: {
        int i = pOp->p1;
        int tos = p->tos;
        Mem *pMem;
        if( tos<0 ) goto not_enough_stack;
        if( i>=p->nMem ){
          int nOld = p->nMem;
          p->nMem = i + 5;
          p->aMem = sqliteRealloc(p->aMem, p->nMem*sizeof(p->aMem[0]));
          if( p->aMem==0 ) goto no_mem;
          if( nOld<p->nMem ){
            memset(&p->aMem[nOld], 0, sizeof(p->aMem[0])*(p->nMem-nOld));
          }
        }
        pMem = &p->aMem[i];
        if( pMem->s.flags & STK_Dyn ){
          sqliteFree(pMem->z);
        }
        pMem->s = p->aStack[tos];
        if( pMem->s.flags & STK_Str ){
          pMem->z = 0;
          sqliteSetString(&pMem->z, p->zStack[tos], 0);
          pMem->s.flags |= STK_Dyn;
        }
        PopStack(p, 1);
        break;
      }

      /* Opcode: MemLoad P1 * *
      **
      ** Push a copy of the value in memory location P1 onto the stack.
      */
      case OP_MemLoad: {
        int tos = ++p->tos;
        int i = pOp->p1;
        if( NeedStack(p, tos) ) goto no_mem;
        if( i<0 || i>=p->nMem ){
          p->aStack[tos].flags = STK_Null;
          p->zStack[tos] = 0;
        }else{
          p->aStack[tos] = p->aMem[i].s;
          if( p->aStack[tos].flags & STK_Str ){
            char *z = sqliteMalloc(p->aStack[tos].n);
            if( z==0 ) goto no_mem;
            memcpy(z, p->aMem[i].z, p->aStack[tos].n);
            p->zStack[tos] = z;
            p->aStack[tos].flags |= STK_Dyn;
          }
        }
        break;
      }

      /* An other opcode is illegal...
      */
      default: {
        sprintf(zBuf,"%d",pOp->opcode);
        sqliteSetString(pzErrMsg, "unknown opcode ", zBuf, 0);
        rc = SQLITE_INTERNAL;

*************************************************************************
** 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.6 2000/06/05 18:54:47 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_Reorganize         13

#define OP_ResetIdx           14
#define OP_NextIdx            15
#define OP_PutIdx             16
#define OP_DeleteIdx          17

#define OP_MemLoad            18
#define OP_MemStore           19

#define OP_ListOpen           20
#define OP_ListWrite          21
#define OP_ListRewind         22
#define OP_ListRead           23
#define OP_ListClose          24

#define OP_SortOpen           25
#define OP_SortPut            26
#define OP_SortMakeRec        27
#define OP_SortMakeKey        28
#define OP_Sort               29
#define OP_SortNext           30
#define OP_SortKey            31
#define OP_SortCallback       32
#define OP_SortClose          33

#define OP_FileOpen           34
#define OP_FileRead           35
#define OP_FileField          36
#define OP_FileClose          37

#define OP_MakeRecord         38
#define OP_MakeKey            39

#define OP_Goto               40
#define OP_If                 41
#define OP_Halt               42

#define OP_ColumnCount        43
#define OP_ColumnName         44
#define OP_Callback           45

#define OP_Integer            46
#define OP_String             47
#define OP_Null               48
#define OP_Pop                49
#define OP_Dup                50
#define OP_Pull               51

#define OP_Add                52
#define OP_AddImm             53
#define OP_Subtract           54
#define OP_Multiply           55
#define OP_Divide             56
#define OP_Min                57
#define OP_Max                58
#define OP_Like               59
#define OP_Glob               60
#define OP_Eq                 61
#define OP_Ne                 62
#define OP_Lt                 63
#define OP_Le                 64
#define OP_Gt                 65
#define OP_Ge                 66
#define OP_IsNull             67
#define OP_NotNull            68
#define OP_Negative           69
#define OP_And                70
#define OP_Or                 71
#define OP_Not                72
#define OP_Concat             73
#define OP_Noop               74

#define OP_MAX                74

/*
** Prototypes for the VDBE interface.  See comments on the implementation
** for a description of what each of these routines does.
*/
Vdbe *sqliteVdbeCreate(Dbbe*);
int sqliteVdbeAddOp(Vdbe*,int,int,int,const char*,int);

**   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.6 2000/06/05 18:54:47 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.

** a bitmask indicating which tables are used in that expression
** tree.  Bit 0 of the mask is set if table 0 is used.  But 1 is set
** if table 1 is used.  And so forth.
**
** In order for this routine to work, the calling function must have
** previously invoked sqliteExprResolveIds() on the expression.  See
** the header comment on that routine for additional information.
**
** "base" is the cursor number (the value of the iTable field) that
** corresponds to the first entry in the table list.  This is the
** same as pParse->nTab.
*/
static int exprTableUsage(int base, Expr *p){
  unsigned int mask = 0;
  if( p==0 ) return 0;
  if( p->op==TK_FIELD ){
    return 1<< (p->iTable - base);
  }
  if( p->pRight ){
    mask = exprTableUsage(base, p->pRight);
  }
  if( p->pLeft ){
    mask |= exprTableUsage(base, p->pLeft);
  }
  return mask;
}

/*
** The input to this routine is an ExprInfo structure with only the
** "p" field filled in.  The job of this routine is to analyze the
** subexpression and populate all the other fields of the ExprInfo
** structure.
**
** "base" is the cursor number (the value of the iTable field) that
** corresponds to the first entyr in the table list.  This is the
** same as pParse->nTab.
*/
static void exprAnalyze(int base, ExprInfo *pInfo){
  Expr *pExpr = pInfo->p;
  pInfo->prereqLeft = exprTableUsage(base, pExpr->pLeft);
  pInfo->prereqRight = exprTableUsage(base, pExpr->pRight);
  pInfo->indexable = 0;
  pInfo->idxLeft = -1;
  pInfo->idxRight = -1;
  if( pExpr->op==TK_EQ && (pInfo->prereqRight & pInfo->prereqLeft)==0 ){
    if( pExpr->pRight->op==TK_FIELD ){
      pInfo->idxRight = pExpr->pRight->iTable - base;
      pInfo->indexable = 1;
    }
    if( pExpr->pLeft->op==TK_FIELD ){
      pInfo->idxLeft = pExpr->pLeft->iTable - base;
      pInfo->indexable = 1;
    }
  }
}

/*
** Generating the beginning of the loop used for WHERE clause processing.

  WhereInfo *pWInfo;         /* Will become the return value of this function */
  Vdbe *v = pParse->pVdbe;   /* The virtual database engine */
  int brk, cont;             /* Addresses used during code generation */
  int *aOrder;         /* Order in which pTabList entries are searched */
  int nExpr;           /* Number of subexpressions in the WHERE clause */
  int loopMask;        /* One bit set for each outer loop */
  int haveKey;         /* True if KEY is on the stack */
  int base;            /* First available index for OP_Open opcodes */
  Index *aIdx[32];     /* Index to use on each nested loop.  */
  ExprInfo aExpr[50];  /* The WHERE clause is divided into these expressions */

  /* Allocate space for aOrder[]. */
  aOrder = sqliteMalloc( sizeof(int) * pTabList->nId );

  /* Allocate and initialize the WhereInfo structure that will become the
  ** return value.
  */
  pWInfo = sqliteMalloc( sizeof(WhereInfo) );
  if( pWInfo==0 ){
    sqliteFree(aOrder);
    return 0;
  }
  pWInfo->pParse = pParse;
  pWInfo->pTabList = pTabList;
  base = pWInfo->base = pParse->nTab;

  /* Split the WHERE clause into as many as 32 separate subexpressions
  ** where each subexpression is separated by an AND operator.  Any additional
  ** subexpressions are attached in the aExpr[32] and will not enter
  ** into the query optimizer computations.  32 is chosen as the cutoff
  ** since that is the number of bits in an integer that we use for an
  ** expression-used mask.  
  */
  memset(aExpr, 0, sizeof(aExpr));
  nExpr = exprSplit(ARRAYSIZE(aExpr), aExpr, pWhere);

  /* Analyze all of the subexpressions.
  */
  for(i=0; i<nExpr; i++){
    exprAnalyze(pParse->nTab, &aExpr[i]);
  }

  /* Figure out a good nesting order for the tables.  aOrder[0] will
  ** be the index in pTabList of the outermost table.  aOrder[1] will
  ** be the first nested loop and so on.  aOrder[pTabList->nId-1] will
  ** be the innermost loop.
  **

    aIdx[i] = pBestIdx;
    loopMask |= 1<<idx;
  }

  /* Open all tables in the pTabList and all indices in aIdx[].
  */
  for(i=0; i<pTabList->nId; i++){
    sqliteVdbeAddOp(v, OP_Open, base+i, 0, pTabList->a[i].pTab->zName, 0);
    if( i<ARRAYSIZE(aIdx) && aIdx[i]!=0 ){
      sqliteVdbeAddOp(v, OP_Open, base+pTabList->nId+i, 0, aIdx[i]->zName, 0);
    }
  }
  memcpy(pWInfo->aIdx, aIdx, sizeof(aIdx));

  /* Generate the code to do the search
  */
  pWInfo->iBreak = brk = sqliteVdbeMakeLabel(v);
  loopMask = 0;
  for(i=0; i<pTabList->nId; i++){
    int j, k;
    int idx = aOrder[i];
    Index *pIdx = i<ARRAYSIZE(aIdx) ? aIdx[i] : 0;

    cont = sqliteVdbeMakeLabel(v);
    if( pIdx==0 ){
      /* Case 1:  There was no usable index.  We must do a complete
      ** scan of the table.
      */
      sqliteVdbeAddOp(v, OP_Next, base+idx, brk, 0, cont);
      haveKey = 0;
    }else{
      /* Case 2:  We do have a usable index in pIdx.
      */
      for(j=0; j<pIdx->nField; j++){
        for(k=0; k<nExpr; k++){
          if( aExpr[k].p==0 ) continue;

            sqliteExprCode(pParse, aExpr[k].p->pLeft);
            aExpr[k].p = 0;
            break;
          }
        }
      }
      sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nField, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_Fetch, 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, idx, 0, 0, 0);
        haveKey = 0;
      }
    }
    loopMask |= 1<<idx;

    /* Insert code to test every subexpression that can be completely
    ** computed using the current set of tables.
    */
    for(j=0; j<nExpr; j++){
      if( aExpr[j].p==0 ) continue;
      if( (aExpr[j].prereqRight & loopMask)!=aExpr[j].prereqRight ) continue;
      if( (aExpr[j].prereqLeft & loopMask)!=aExpr[j].prereqLeft ) continue;
      if( haveKey ){
        sqliteVdbeAddOp(v, OP_Fetch, base+idx, 0, 0, 0);
        haveKey = 0;
      }
      sqliteExprIfFalse(pParse, aExpr[j].p, cont);
      aExpr[j].p = 0;
    }
    brk = cont;
  }
  pWInfo->iContinue = cont;
  if( pushKey && !haveKey ){
    sqliteVdbeAddOp(v, OP_Key, 0, 0, 0, 0);
  }
  sqliteFree(aOrder);
  return pWInfo;
}

/*
** Generate the end of the WHERE loop.
*/
void sqliteWhereEnd(WhereInfo *pWInfo){
  Vdbe *v = pWInfo->pParse->pVdbe;
  int i;
  int brk = pWInfo->iBreak;
  int base = pWInfo->base;

  sqliteVdbeAddOp(v, OP_Goto, 0, pWInfo->iContinue, 0, 0);
  for(i=0; i<pWInfo->pTabList->nId; i++){
    sqliteVdbeAddOp(v, OP_Close, base+i, 0, 0, brk);
    brk = 0;
    if( i<ARRAYSIZE(pWInfo->aIdx) && pWInfo->aIdx[i]!=0 ){
      sqliteVdbeAddOp(v, OP_Close, base+pWInfo->pTabList->nId+i, 0, 0, 0);
    }
  }
  if( brk!=0 ){
    sqliteVdbeAddOp(v, OP_Noop, 0, 0, 0, brk);
  }
  sqliteFree(pWInfo);
  return;
}

#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the COPY statement.
#
# $Id: copy.test,v 1.4 2000/06/05 18:54:47 drh Exp $

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

# Create a file of data from which to copy.
#
set f [open data1.txt w]

# Make sure input terminates at \.
#
do_test copy-1.4 {
  execsql {DELETE FROM test1}
  execsql {COPY test1 FROM 'data2.txt'}
  execsql {SELECT * FROM test1 ORDER BY one}
} {11 22 33}


# Test out the USING DELIMITERS clause
#
do_test copy-1.5 {
  execsql {DELETE FROM test1}
  execsql {COPY test1 FROM 'data4.txt' USING DELIMITERS ' | '}
  execsql {SELECT * FROM test1 ORDER BY one}

#   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.5 2000/06/05 18:54:47 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)}

    }
  }
  set r
} {4: 2 3 4}

# Create a largish table
#
do_test select2-2.0 {
  execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int)}
  set f [open ./testdata1.txt w]
  for {set i 1} {$i<=30000} {incr i} {
    puts $f "$i\t[expr {$i*2}]\t[expr {$i*3}]"
  }
  close $f
  execsql {COPY tbl2 FROM './testdata1.txt'}
  file delete -force ./testdata1.txt
} {}

do_test select2-2.1 {
  execsql {SELECT count(*) FROM tbl2}
} {30000}
do_test select2-2.2 {
  execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
} {29500}

do_test select2-3.1 {
  execsql {SELECT f1 FROM tbl2 WHERE f2==1000}
} {500}

do_test select2-3.2a {
  execsql {CREATE INDEX idx1 ON tbl2(f2)}
} {}

do_test select2-3.2b {
  execsql {SELECT f1 FROM tbl2 WHERE f2==1000}
} {500}

# Make sure queries run faster with an index than without
#
do_test select2-3.3 {
  set t1 [lindex [time {execsql {SELECT f1 from tbl2 WHERE f2==2000}} 1] 0]
  execsql {DROP INDEX idx1}
  set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2==2000}} 1] 0]
  expr {$t1*25 < $t2}
} {1}

finish_test

# Copyright (c) 1999, 2000 D. Richard Hipp
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
# 
# You should have received a copy of the GNU General Public
# License along with this library; if not, write to the
# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA  02111-1307, USA.
#
# Author contact information:
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that are part of
# expressions.
#
# $Id: subselect.test,v 1.1 2000/06/05 18:54:47 drh Exp $

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

# Basic sanity checking.  Try a simple subselect.
#
do_test subselect-1.1 {
  execsql {
    CREATE TABLE t1(a int, b int);
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t1 VALUES(3,4);
    INSERT INTO t1 VALUES(5,6);
  }
  execsql {SELECT * FROM t1 WHERE a = (SELECT count(*) FROM t1)}
} {3 4}

# Try a select with more than one result column.
#
do_test subselect-1.2 {
  set v [catch {execsql {SELECT * FROM t1 WHERE a = (SELECT * FROM t1)}} msg]
  lappend v $msg
} {1 {only a single result allowed for a SELECT this is part of an expression}}

# A subselect without an aggregate.
#
do_test subselect-1.3a {
  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=2)}
} {2}
do_test subselect-1.3b {
  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=4)}
} {4}
do_test subselect-1.3c {
  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=6)}
} {6}
do_test subselect-1.3c {
  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=8)}
} {}

# What if the subselect doesn't return any value.  We should get
# NULL as the result.  Check it out.
#
do_test subselect-1.4 {
  execsql {INSERT INTO t1 VALUES(NULL,8)}
  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=5)}
} {8}

# Try multiple subselects within a single expression.
#
do_test subselect-1.5 {
  execsql {
    CREATE TABLE t2(x int, y int);
    INSERT INTO t2 VALUES(1,2);
    INSERT INTO t2 VALUES(2,4);
    INSERT INTO t2 VALUES(3,8);
    INSERT INTO t2 VALUES(4,16);
  }
  execsql {
    SELECT y from t2 
    WHERE x = (SELECT sum(b) FROM t1 where a notnull) - (SELECT sum(a) FROM t1)
  }
} {8}

finish_test