SQLite

# Generate the file "last_change" which contains the date of change
# of the most recently modified source code file
#
last_change:	$(SRC)
	cat $(SRC) | grep '$$Id: ' | sort +4 | tail -1 \
          | awk '{print $$5,$$6}' >last_change

libsqlite.a:	$(LIBOBJ)
	$(AR) libsqlite.a $(LIBOBJ)
	$(RANLIB) libsqlite.a

sqlite$(EXE):	$(TOP)/src/shell.c libsqlite.a sqlite.h
	$(TCCX) $(READLINE_FLAGS) -o sqlite$(EXE) $(TOP)/src/shell.c \
		libsqlite.a $(LIBREADLINE) $(THREADLIB)

# This target creates a directory named "tsrc" and fills it with

**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.110 2002/08/24 18:24:53 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs

void sqliteCreateView(
  Parse *pParse,     /* The parsing context */
  Token *pBegin,     /* The CREATE token that begins the statement */
  Token *pName,      /* The token that holds the name of the view */
  Select *pSelect,   /* A SELECT statement that will become the new view */
  int isTemp         /* TRUE for a TEMPORARY view */
){

  Table *p;
  int n;
  const char *z;
  Token sEnd;

  sqliteStartTable(pParse, pBegin, pName, isTemp);
  p = pParse->pNewTable;
  if( p==0 ){
    sqliteSelectDelete(pSelect);
    return;
  }
  /* Ignore ORDER BY clauses on a SELECT */
  if( pSelect->pOrderBy ){
    sqliteExprListDelete(pSelect->pOrderBy);
    pSelect->pOrderBy = 0;
  }
  /* Make a copy of the entire SELECT statement that defines the view.
  ** This will force all the Expr.token.z values to be dynamically
  ** allocated rather than point to the input string - which means that
  ** they will persist after the current sqlite_exec() call returns.
  */
  p->pSelect = sqliteSelectDup(pSelect);
  sqliteSelectDelete(pSelect);
  if( !pParse->initFlag ){
    sqliteViewGetColumnNames(pParse, p);

    }

  /* Locate the end of the CREATE VIEW statement.  Make sEnd point to
  ** the end.
  */
  sEnd = pParse->sLastToken;
  if( sEnd.z[0]!=0 && sEnd.z[0]!=';' ){
    sEnd.z += sEnd.n;
  }
  sEnd.n = 0;
  n = ((int)sEnd.z) - (int)pBegin->z;
  z = pBegin->z;
  while( n>0 && (z[n-1]==';' || isspace(z[n-1])) ){ n--; }
  sEnd.z = &z[n-1];
  sEnd.n = 1;



  /* Use sqliteEndTable() to add the view to the SQLITE_MASTER table */
    sqliteEndTable(pParse, &sEnd, 0);

  return;
}

/*
** The Table structure pTable is really a VIEW.  Fill in the names of
** the columns of the view in the pTable structure.  Return the number
** of errors.  If an error is seen leave an error message in pPare->zErrMsg.

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.80 2002/08/24 18:24:54 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function

    sqliteExprDelete(pRight);
    return 0;
  }
  pNew->op = op;
  pNew->pLeft = pLeft;
  pNew->pRight = pRight;
  if( pToken ){
    assert( pToken->dyn==0 );
    pNew->token = *pToken;
    pNew->token.base = 1;
  }else if( pLeft && pRight ){
    sqliteExprSpan(pNew, &pLeft->token, &pRight->token);
  }else{
    pNew->token.dyn = 0;
    pNew->token.base = 1;
    pNew->token.z = 0;
    pNew->token.n = 0;
  }





  return pNew;
}

/*
** Set the Expr.token field of the given expression to span all
** text between the two given tokens.
*/
void sqliteExprSpan(Expr *pExpr, Token *pLeft, Token *pRight){
  if( pExpr ){
    assert( pExpr->token.dyn==0 );
    if( pLeft->dyn==0 && pRight->dyn==0 ){
      pExpr->token.z = pLeft->z;
      pExpr->token.n = pRight->n + Addr(pRight->z) - Addr(pLeft->z);
      pExpr->token.base = 0;
    }else{
      pExpr->token.z = 0;
      pExpr->token.n = 0;
      pExpr->token.dyn = 0;
      pExpr->token.base = 0;
    }
  }
}

/*
** Construct a new expression node for a function with multiple
** arguments.
*/
Expr *sqliteExprFunction(ExprList *pList, Token *pToken){
  Expr *pNew;
  pNew = sqliteMalloc( sizeof(Expr) );
  if( pNew==0 ){
    sqliteExprListDelete(pList);
    return 0;
  }
  pNew->op = TK_FUNCTION;
  pNew->pList = pList;

  /* Expr.token.n is the length of the entire function
  ** call, including the function arguments.  The parser
  ** will extend token.n to cover the either length of the string.
  ** Expr.nFuncName is the length of just the function name.
  */
  pNew->token.dyn = 0;
  pNew->token.base = 1;
  if( pToken ){
    assert( pToken->dyn==0 );
    pNew->token = *pToken;
    pNew->nFuncName = pToken->n>255 ? 255 : pToken->n;
  }else{
    pNew->token.z = 0;
    pNew->token.n = 0;
  }
  return pNew;
}

/*
** Recursively delete an expression tree.
*/
void sqliteExprDelete(Expr *p){
  if( p==0 ) return;
  if( p->token.dyn && p->token.z ) sqliteFree((char*)p->token.z);
  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);
}

































































/*
** The following group of routines make deep copies of expressions,
** expression lists, ID lists, and select statements.  The copies can
** be deleted (by being passed to their respective ...Delete() routines)
** without effecting the originals.
**




** The expression list, ID, and source lists return by sqliteExprListDup(),
** sqliteIdListDup(), and sqliteSrcListDup() can not be further expanded 
** by subsequent calls to sqlite*ListAppend() routines.
**
** Any tables that the SrcList might point to are not duplicated.
*/
Expr *sqliteExprDup(Expr *p){
  Expr *pNew;
  if( p==0 ) return 0;
  pNew = sqliteMalloc( sizeof(*p) );
  if( pNew==0 ) return 0;
  memcpy(pNew, p, sizeof(*pNew));
  /* Only make a copy of the token if it is a base token (meaning that
  ** it covers a single term of an expression - not two or more terms)
  ** or if it is already dynamically allocated.  So, for example, in
  ** a complex expression like "a+b+c", the token "b" would be duplicated
  ** but "a+b" would not be. */
  if( p->token.z!=0 && (p->token.base || p->token.dyn) ){
    pNew->token.z = sqliteStrDup(p->token.z);
    pNew->token.dyn = 1;
  }else{
    pNew->token.z = 0;
    pNew->token.n = 0;
    pNew->token.dyn = 0;
  }
  pNew->pLeft = sqliteExprDup(p->pLeft);
  pNew->pRight = sqliteExprDup(p->pRight);
  pNew->pList = sqliteExprListDup(p->pList);
  pNew->pSelect = sqliteSelectDup(p->pSelect);
  return pNew;
}
void sqliteTokenCopy(Token *pTo, Token *pFrom){
  if( pTo->dyn ) sqliteFree((char*)pTo->z);
  pTo->base = pFrom->base;
  if( pFrom->z ){
    pTo->n = pFrom->n;
    pTo->z = sqliteStrNDup(pFrom->z, pFrom->n);
    pTo->dyn = 1;
  }else{
    pTo->n = 0;
    pTo->z = 0;
    pTo->dyn = 0;
  }
}
ExprList *sqliteExprListDup(ExprList *p){
  ExprList *pNew;
  int i;
  if( p==0 ) return 0;
  pNew = sqliteMalloc( sizeof(*pNew) );
  if( pNew==0 ) return 0;
  pNew->nExpr = p->nExpr;
  pNew->a = sqliteMalloc( p->nExpr*sizeof(p->a[0]) );
  if( pNew->a==0 ) return 0;
  for(i=0; i<p->nExpr; i++){
    Expr *pNewExpr, *pOldExpr;
    pNew->a[i].pExpr = pNewExpr = sqliteExprDup(pOldExpr = p->a[i].pExpr);
    if( pOldExpr->token.z!=0 && pNewExpr && pNewExpr->token.z==0 ){
      /* Always make a copy of the token for top-level expressions in the
      ** expression list.  The logic in SELECT processing that determines
      ** the names of columns in the result set needs this information */
      sqliteTokenCopy(&pNew->a[i].pExpr->token, &p->a[i].pExpr->token);
    }
    pNew->a[i].zName = sqliteStrDup(p->a[i].zName);
    pNew->a[i].sortOrder = p->a[i].sortOrder;
    pNew->a[i].isAgg = p->a[i].isAgg;
    pNew->a[i].done = 0;
  }
  return pNew;
}

      while( n>0 && *z && isdigit(*z) ){ z++; n--; }
      if( n==0 ){
        *pValue = atoi(p->token.z);
        return 1;
      }
      break;
    }
    case TK_UPLUS: {
      return sqliteExprIsInteger(p->pLeft, pValue);
    }
    case TK_UMINUS: {
      int v;
      if( sqliteExprIsInteger(p->pLeft, &v) ){
        *pValue = -v;
        return 1;
      }
      break;

          }
        }
      }
    }
  }
  return 0;
}

/*
** pExpr is a node that defines a function of some kind.  It might
** be a syntactic function like "count(x)" or it might be a function
** that implements an operator, like "a LIKE b".  
**
** This routine makes *pzName point to the name of the function and 
** *pnName hold the number of characters in the function name.
*/
static void getFunctionName(Expr *pExpr, const char **pzName, int *pnName){
  switch( pExpr->op ){
    case TK_FUNCTION: {
      *pzName = pExpr->token.z;
      *pnName = pExpr->nFuncName;
      break;
    }
    case TK_LIKE: {
      *pzName = "like";
      *pnName = 4;
      break;
    }
    case TK_GLOB: {
      *pzName = "glob";
      *pnName = 4;
      break;
    }
    default: {
      *pzName = "can't happen";
      *pnName = 12;
      break;
    }
  }
}

/*
** Error check the functions in an expression.  Make sure all
** function names are recognized and all functions have the correct
** number of arguments.  Leave an error message in pParse->zErrMsg
** if anything is amiss.  Return the number of errors.
**
** if pIsAgg is not null and this expression is an aggregate function
** (like count(*) or max(value)) then write a 1 into *pIsAgg.
*/
int sqliteExprCheck(Parse *pParse, Expr *pExpr, int allowAgg, int *pIsAgg){
  int nErr = 0;
  if( pExpr==0 ) return 0;
  switch( pExpr->op ){
    case TK_GLOB:
    case TK_LIKE:
    case TK_FUNCTION: {
      int n = pExpr->pList ? pExpr->pList->nExpr : 0;  /* Number of arguments */
      int no_such_func = 0;       /* True if no such function exists */
      int is_type_of = 0;         /* True if is the special TypeOf() function */
      int wrong_num_args = 0;     /* True if wrong number of arguments */
      int is_agg = 0;             /* True if is an aggregate function */
      int i;
      int nId;                    /* Number of characters in function name */
      const char *zId;            /* The function name. */
      FuncDef *pDef;

      getFunctionName(pExpr, &zId, &nId);
      pDef = sqliteFindFunction(pParse->db, zId, nId, n, 0);

      if( pDef==0 ){
        pDef = sqliteFindFunction(pParse->db, zId, nId, -1, 0);

        if( pDef==0 ){

          if( n==1 && nId==6 && sqliteStrNICmp(zId, "typeof", 6)==0 ){
            is_type_of = 1;
          }else {
            no_such_func = 1;
          }
        }else{
          wrong_num_args = 1;
        }
      }else{
        is_agg = pDef->xFunc==0;
      }
      if( is_agg && !allowAgg ){
        sqliteSetNString(&pParse->zErrMsg, "misuse of aggregate function ", -1,
           zId, nId, "()", 2, 0);
        pParse->nErr++;
        nErr++;
        is_agg = 0;
      }else if( no_such_func ){
        sqliteSetNString(&pParse->zErrMsg, "no such function: ", -1, zId,nId,0);

        pParse->nErr++;
        nErr++;
      }else if( wrong_num_args ){
        sqliteSetNString(&pParse->zErrMsg, 
           "wrong number of arguments to function ", -1, zId, nId, "()", 2, 0);

        pParse->nErr++;
        nErr++;
      }
      if( is_agg ) pExpr->op = TK_AGG_FUNCTION;
      if( is_agg && pIsAgg ) *pIsAgg = 1;
      for(i=0; nErr==0 && i<n; i++){
        nErr = sqliteExprCheck(pParse, pExpr->pList->a[i].pExpr,

    case TK_SLASH:
    case TK_AND:
    case TK_OR:
    case TK_ISNULL:
    case TK_NOTNULL:
    case TK_NOT:
    case TK_UMINUS:
    case TK_UPLUS:
    case TK_BITAND:
    case TK_BITOR:
    case TK_BITNOT:
    case TK_LSHIFT:
    case TK_RSHIFT:
    case TK_REM:
    case TK_INTEGER:
    case TK_FLOAT:
    case TK_IN:
    case TK_BETWEEN:
    case TK_GLOB:
    case TK_LIKE:
      return SQLITE_SO_NUM;

    case TK_STRING:
    case TK_NULL:
    case TK_CONCAT:
      return SQLITE_SO_TEXT;

      break;
    }
    case TK_CONCAT: {
      sqliteExprCode(pParse, pExpr->pLeft);
      sqliteExprCode(pParse, pExpr->pRight);
      sqliteVdbeAddOp(v, OP_Concat, 2, 0);
      break;
    }
    case TK_UPLUS: {
      Expr *pLeft = pExpr->pLeft;
      if( pLeft && pLeft->op==TK_INTEGER ){
        sqliteVdbeAddOp(v, OP_Integer, atoi(pLeft->token.z), 0);
        sqliteVdbeChangeP3(v, -1, pLeft->token.z, pLeft->token.n);
      }else if( pLeft && pLeft->op==TK_FLOAT ){
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        sqliteVdbeChangeP3(v, -1, pLeft->token.z, pLeft->token.n);
      }else{
        sqliteExprCode(pParse, pExpr->pLeft);
      }
      break;
    }
    case TK_UMINUS: {
      assert( pExpr->pLeft );
      if( pExpr->pLeft->op==TK_FLOAT || pExpr->pLeft->op==TK_INTEGER ){
        Token *p = &pExpr->pLeft->token;
        char *z = sqliteMalloc( p->n + 2 );
        sprintf(z, "-%.*s", p->n, p->z);

      sqliteVdbeAddOp(v, OP_AddImm, -1, 0);
      break;
    }
    case TK_AGG_FUNCTION: {
      sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg);
      break;
    }
    case TK_GLOB:
    case TK_LIKE:
    case TK_FUNCTION: {
      int i;
      ExprList *pList = pExpr->pList;
      int nExpr = pList ? pList->nExpr : 0;
      FuncDef *pDef;
      int nId;
      const char *zId;
      getFunctionName(pExpr, &zId, &nId);
      pDef = sqliteFindFunction(pParse->db, zId, nId, nExpr, 0);

      assert( pDef!=0 );
      for(i=0; i<nExpr; i++){
        sqliteExprCode(pParse, pList->a[i].pExpr);
      }
      sqliteVdbeAddOp(v, OP_Function, nExpr, 0);
      sqliteVdbeChangeP3(v, -1, (char*)pDef, P3_POINTER);
      break;

    }
  }else if( pB->pList ){
    return 0;
  }
  if( pA->pSelect || pB->pSelect ) return 0;
  if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 0;
  if( pA->token.z ){
    int n;
    if( pB->token.z==0 ) return 0;
    if( pA->op==TK_FUNCTION || pA->op==TK_AGG_FUNCTION ){
      n = pA->nFuncName;
      if( pB->nFuncName!=n ) return 0;
    }else{
      n = pA->token.n;
      if( pB->token.n!=n ) return 0;
    }
    if( sqliteStrNICmp(pA->token.z, pB->token.z, n)!=0 ) return 0;
  }
  return 1;
}

/*
** Add a new element to the pParse->aAgg[] array and return its index.
*/

      }
      if( i>=pParse->nAgg ){
        i = appendAggInfo(pParse);
        if( i<0 ) return 1;
        pParse->aAgg[i].isAgg = 1;
        pParse->aAgg[i].pExpr = pExpr;
        pParse->aAgg[i].pFunc = sqliteFindFunction(pParse->db,
             pExpr->token.z, pExpr->nFuncName,
             pExpr->pList ? pExpr->pList->nExpr : 0, 0);
      }
      pExpr->iAgg = i;
      break;
    }
    default: {
      if( pExpr->pLeft ){

**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.98 2002/08/24 18:24:54 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** A pointer to this structure is used to communicate information

  sqlite *db,          /* Add the function to this database connection */
  const char *zName,   /* Name of the function to add */
  int nArg,            /* Number of arguments */
  void (*xFunc)(sqlite_func*,int,const char**),  /* The implementation */
  void *pUserData      /* User data */
){
  FuncDef *p;
  int nName;
  if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1;
  nName = strlen(zName);
  if( nName>255 ) return 1;
  p = sqliteFindFunction(db, zName, nName, nArg, 1);
  if( p==0 ) return 1;
  p->xFunc = xFunc;
  p->xStep = 0;
  p->xFinalize = 0;
  p->pUserData = pUserData;
  return 0;
}
int sqlite_create_aggregate(
  sqlite *db,          /* Add the function to this database connection */
  const char *zName,   /* Name of the function to add */
  int nArg,            /* Number of arguments */
  void (*xStep)(sqlite_func*,int,const char**), /* The step function */
  void (*xFinalize)(sqlite_func*),              /* The finalizer */
  void *pUserData      /* User data */
){
  FuncDef *p;
  int nName;
  if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1;
  nName = strlen(zName);
  if( nName>255 ) return 1;
  p = sqliteFindFunction(db, zName, nName, nArg, 1);
  if( p==0 ) return 1;
  p->xFunc = 0;
  p->xStep = xStep;
  p->xFinalize = xFinalize;
  p->pUserData = pUserData;
  return 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.82 2002/08/24 18:24:54 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);

%right NOT.
%left EQ NE ISNULL NOTNULL IS LIKE GLOB BETWEEN IN.
%left GT GE LT LE.
%left BITAND BITOR LSHIFT RSHIFT.
%left PLUS MINUS.
%left STAR SLASH REM.
%left CONCAT.
%right UMINUS UPLUS BITNOT.

%type expr {Expr*}
%destructor expr {sqliteExprDelete($$);}

expr(A) ::= LP(B) expr(X) RP(E). {A = X; sqliteExprSpan(A,&B,&E);}
expr(A) ::= NULL(X).             {A = sqliteExpr(TK_NULL, 0, 0, &X);}
expr(A) ::= ID(X).               {A = sqliteExpr(TK_ID, 0, 0, &X);}
expr(A) ::= JOIN_KW(X).          {A = sqliteExpr(TK_ID, 0, 0, &X);}
expr(A) ::= nm(X) DOT nm(Y). {
  Expr *temp1 = sqliteExpr(TK_ID, 0, 0, &X);
  Expr *temp2 = sqliteExpr(TK_ID, 0, 0, &Y);
  A = sqliteExpr(TK_DOT, temp1, temp2, 0);
}
expr(A) ::= INTEGER(X).      {A = sqliteExpr(TK_INTEGER, 0, 0, &X);}
expr(A) ::= FLOAT(X).        {A = sqliteExpr(TK_FLOAT, 0, 0, &X);}
expr(A) ::= STRING(X).       {A = sqliteExpr(TK_STRING, 0, 0, &X);}
expr(A) ::= ID(X) LP exprlist(Y) RP(E). {
  A = sqliteExprFunction(Y, &X);
  sqliteExprSpan(A,&X,&E);
  if( A ) A->token.base = 1;
}
expr(A) ::= ID(X) LP STAR RP(E). {
  A = sqliteExprFunction(0, &X);
  sqliteExprSpan(A,&X,&E);
  if( A ) A->token.base = 1;
}
expr(A) ::= expr(X) AND expr(Y).   {A = sqliteExpr(TK_AND, X, Y, 0);}
expr(A) ::= expr(X) OR expr(Y).    {A = sqliteExpr(TK_OR, X, Y, 0);}
expr(A) ::= expr(X) LT expr(Y).    {A = sqliteExpr(TK_LT, X, Y, 0);}
expr(A) ::= expr(X) GT expr(Y).    {A = sqliteExpr(TK_GT, X, Y, 0);}
expr(A) ::= expr(X) LE expr(Y).    {A = sqliteExpr(TK_LE, X, Y, 0);}
expr(A) ::= expr(X) GE expr(Y).    {A = sqliteExpr(TK_GE, X, Y, 0);}
expr(A) ::= expr(X) NE expr(Y).    {A = sqliteExpr(TK_NE, X, Y, 0);}
expr(A) ::= expr(X) EQ expr(Y).    {A = sqliteExpr(TK_EQ, X, Y, 0);}
expr(A) ::= expr(X) BITAND expr(Y). {A = sqliteExpr(TK_BITAND, X, Y, 0);}
expr(A) ::= expr(X) BITOR expr(Y).  {A = sqliteExpr(TK_BITOR, X, Y, 0);}
expr(A) ::= expr(X) LSHIFT expr(Y). {A = sqliteExpr(TK_LSHIFT, X, Y, 0);}
expr(A) ::= expr(X) RSHIFT expr(Y). {A = sqliteExpr(TK_RSHIFT, X, Y, 0);}
expr(A) ::= expr(X) likeop(OP) expr(Y).  [LIKE]  {
  ExprList *pList = sqliteExprListAppend(0, Y, 0);
  pList = sqliteExprListAppend(pList, X, 0);
  A = sqliteExprFunction(pList, 0);
  if( A ) A->op = OP;
  sqliteExprSpan(A, &X->token, &Y->token);
}
expr(A) ::= expr(X) NOT likeop(OP) expr(Y). [LIKE] {
  ExprList *pList = sqliteExprListAppend(0, Y, 0);
  pList = sqliteExprListAppend(pList, X, 0);
  A = sqliteExprFunction(pList, 0);
  if( A ) A->op = OP;
  A = sqliteExpr(TK_NOT, A, 0, 0);
  sqliteExprSpan(A,&X->token,&Y->token);
}
%type likeop {int}
likeop(A) ::= LIKE. {A = TK_LIKE;}
likeop(A) ::= GLOB. {A = TK_GLOB;}
expr(A) ::= expr(X) PLUS expr(Y).  {A = sqliteExpr(TK_PLUS, X, Y, 0);}
expr(A) ::= expr(X) MINUS expr(Y). {A = sqliteExpr(TK_MINUS, X, Y, 0);}
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) REM expr(Y).   {A = sqliteExpr(TK_REM, X, Y, 0);}
expr(A) ::= expr(X) CONCAT expr(Y). {A = sqliteExpr(TK_CONCAT, X, Y, 0);}
expr(A) ::= expr(X) ISNULL(E). {
  A = sqliteExpr(TK_ISNULL, X, 0, 0);
  sqliteExprSpan(A,&X->token,&E);
}
expr(A) ::= expr(X) IS NULL(E). {
  A = sqliteExpr(TK_ISNULL, X, 0, 0);
  sqliteExprSpan(A,&X->token,&E);
}
expr(A) ::= expr(X) NOTNULL(E). {
  A = sqliteExpr(TK_NOTNULL, X, 0, 0);
  sqliteExprSpan(A,&X->token,&E);
}
expr(A) ::= expr(X) NOT NULL(E). {
  A = sqliteExpr(TK_NOTNULL, X, 0, 0);
  sqliteExprSpan(A,&X->token,&E);
}
expr(A) ::= expr(X) IS NOT NULL(E). {
  A = sqliteExpr(TK_NOTNULL, X, 0, 0);
  sqliteExprSpan(A,&X->token,&E);
}
expr(A) ::= NOT(B) expr(X). {
  A = sqliteExpr(TK_NOT, X, 0, 0);
  sqliteExprSpan(A,&B,&X->token);
}
expr(A) ::= BITNOT(B) expr(X). {
  A = sqliteExpr(TK_BITNOT, X, 0, 0);
  sqliteExprSpan(A,&B,&X->token);
}
expr(A) ::= MINUS(B) expr(X). [UMINUS] {
  A = sqliteExpr(TK_UMINUS, X, 0, 0);
  sqliteExprSpan(A,&B,&X->token);
}
expr(A) ::= PLUS(B) expr(X). [UPLUS] {
  A = sqliteExpr(TK_UPLUS, X, 0, 0);
  sqliteExprSpan(A,&B,&X->token);
}
expr(A) ::= LP(B) select(X) RP(E). {
  A = sqliteExpr(TK_SELECT, 0, 0, 0);
  if( A ) A->pSelect = X;
  sqliteExprSpan(A,&B,&E);
}
expr(A) ::= expr(W) BETWEEN expr(X) AND expr(Y). {
  ExprList *pList = sqliteExprListAppend(0, X, 0);
  pList = sqliteExprListAppend(pList, Y, 0);
  A = sqliteExpr(TK_BETWEEN, W, 0, 0);
  if( A ) A->pList = pList;
  sqliteExprSpan(A,&W->token,&Y->token);
}
expr(A) ::= expr(W) NOT BETWEEN expr(X) AND expr(Y). {
  ExprList *pList = sqliteExprListAppend(0, X, 0);
  pList = sqliteExprListAppend(pList, Y, 0);
  A = sqliteExpr(TK_BETWEEN, W, 0, 0);
  if( A ) A->pList = pList;
  A = sqliteExpr(TK_NOT, A, 0, 0);
  sqliteExprSpan(A,&W->token,&Y->token);
}
expr(A) ::= expr(X) IN LP exprlist(Y) RP(E).  {
  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pList = Y;
  sqliteExprSpan(A,&X->token,&E);
}
expr(A) ::= expr(X) IN LP select(Y) RP(E).  {
  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pSelect = Y;
  sqliteExprSpan(A,&X->token,&E);
}
expr(A) ::= expr(X) NOT IN LP exprlist(Y) RP(E).  {
  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pList = Y;
  A = sqliteExpr(TK_NOT, A, 0, 0);
  sqliteExprSpan(A,&X->token,&E);
}
expr(A) ::= expr(X) NOT IN LP select(Y) RP(E).  {
  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pSelect = Y;
  A = sqliteExpr(TK_NOT, A, 0, 0);
  sqliteExprSpan(A,&X->token,&E);
}

/* CASE expressions */
expr(A) ::= CASE(C) case_operand(X) case_exprlist(Y) case_else(Z) END(E). {
  A = sqliteExpr(TK_CASE, X, Z, 0);
  if( A ) A->pList = Y;
  sqliteExprSpan(A, &C, &E);

plus_opt ::= PLUS.
plus_opt ::= .

//////////////////////////// The CREATE TRIGGER command /////////////////////
cmd ::= CREATE(A) TRIGGER nm(B) trigger_time(C) trigger_event(D) ON nm(E) 
                  foreach_clause(F) when_clause(G)
                  BEGIN trigger_cmd_list(S) END(Z). {
  Token all;
  all.z = A.z;
  all.n = (Z.z - A.z) + Z.n;
  sqliteCreateTrigger(pParse, &B, C, D.a, D.b, &E, F, G, S, &all);

}

%type trigger_time  {int}
trigger_time(A) ::= BEFORE.      { A = TK_BEFORE; }
trigger_time(A) ::= AFTER.       { A = TK_AFTER;  }
trigger_time(A) ::= INSTEAD OF.  { A = TK_INSTEAD;}
trigger_time(A) ::= .            { A = TK_BEFORE; }

trigger_cmd(A) ::= DELETE FROM nm(X) where_opt(Y).
               {A = sqliteTriggerDeleteStep(&X, Y);}

// SELECT
trigger_cmd(A) ::= select(X).  {A = sqliteTriggerSelectStep(X); }

// The special RAISE expression that may occur in trigger programs
expr(A) ::= RAISE(X) LP IGNORE RP(Y).  {
  A = sqliteExpr(TK_RAISE, 0, 0, 0); 
  A->iColumn = OE_Ignore;
  /* sqliteExprSpan(A, &X, &Y); */
}
expr(A) ::= RAISE(X) LP ROLLBACK COMMA nm(Z) RP(Y).  {
  A = sqliteExpr(TK_RAISE, 0, 0, &Z); 
  A->iColumn = OE_Rollback;
  /* sqliteExprSpan(A, &X, &Y); */
}
expr(A) ::= RAISE(X) LP ABORT COMMA nm(Z) RP(Y).  {
  A = sqliteExpr(TK_RAISE, 0, 0, &Z); 
  A->iColumn = OE_Abort;
  /* sqliteExprSpan(A, &X, &Y); */
}
expr(A) ::= RAISE(X) LP FAIL COMMA nm(Z) RP(Y).  {
  A = sqliteExpr(TK_RAISE, 0, 0, &Z); 
  A->iColumn = OE_Fail;
  /* sqliteExprSpan(A, &X, &Y); */
}

////////////////////////  DROP TRIGGER statement //////////////////////////////
cmd ::= DROP TRIGGER nm(X). {
    sqliteDropTrigger(pParse,&X,0);
}

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.108 2002/08/24 18:24:54 drh Exp $
*/
#include "sqliteInt.h"

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

  Token dummy;
  Expr *pE1a, *pE1b, *pE1c;
  Expr *pE2a, *pE2b, *pE2c;
  Expr *pE;

  dummy.z = zCol;
  dummy.n = strlen(zCol);
  dummy.base = 1;
  dummy.dyn = 0;
  pE1a = sqliteExpr(TK_ID, 0, 0, &dummy);

  pE2a = sqliteExpr(TK_ID, 0, 0, &dummy);

  dummy.z = pTab1->zName;
  dummy.n = strlen(dummy.z);
  pE1b = sqliteExpr(TK_ID, 0, 0, &dummy);

  dummy.z = pTab2->zName;
  dummy.n = strlen(dummy.z);
  pE2b = sqliteExpr(TK_ID, 0, 0, &dummy);

  pE1c = sqliteExpr(TK_DOT, pE1b, pE1a, 0);
  pE2c = sqliteExpr(TK_DOT, pE2b, pE2a, 0);
  pE = sqliteExpr(TK_EQ, pE1c, pE2c, 0);
  pE->isJoinExpr = 1;
  if( *ppExpr ){
    *ppExpr = sqliteExpr(TK_AND, *ppExpr, pE, 0);
  }else{

      if( iCol<0 ){
        zCol = "_ROWID_";
        zType = "INTEGER";
      }else{
        zCol = pTab->aCol[iCol].zName;
        zType = pTab->aCol[iCol].zType;
      }
      if( p->token.z && p->token.z[0] && !showFullNames ){
        int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, p->token.z, p->token.n);
        sqliteVdbeCompressSpace(v, addr);
      }else if( pTabList->nSrc>1 || showFullNames ){
        char *zName = 0;
        char *zTab;
 
        zTab = pTabList->a[p->iTable - base].zAlias;
        if( showFullNames || zTab==0 ) zTab = pTab->zName;
        sqliteSetString(&zName, zTab, ".", zCol, 0);
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
        sqliteFree(zName);
      }else{
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, zCol, 0);
      }
    }else if( p->token.z && p->token.z[0] && !showFullNames ){
      int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, p->token.z, p->token.n);
      sqliteVdbeCompressSpace(v, addr);
    }else if( p->token.z && p->token.z[0] ){
      int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, p->token.z, p->token.n);
      sqliteVdbeCompressSpace(v, addr);
    }else{
      char zName[30];
      assert( p->op!=TK_COLUMN || pTabList==0 );
      sprintf(zName, "column%d", i+1);
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, zName, strlen(zName));

  pTab->nCol = pEList->nExpr;
  assert( pTab->nCol>0 );
  pTab->aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
  for(i=0; i<pTab->nCol; i++){
    Expr *p;
    if( pEList->a[i].zName ){
      pTab->aCol[i].zName = sqliteStrDup(pEList->a[i].zName);
    }else if( (p=pEList->a[i].pExpr)->token.z && p->token.z[0] ){
      sqliteSetNString(&pTab->aCol[i].zName, p->token.z, p->token.n, 0);
    }else if( p->op==TK_DOT && p->pRight && p->pRight->token.z &&
           p->pRight->token.z[0] ){
      sqliteSetNString(&pTab->aCol[i].zName, 
           p->pRight->token.z, p->pRight->token.n, 0);
    }else{
      char zBuf[30];
      sprintf(zBuf, "column%d", i+1);

              ** using clause from the table on the right. */
              continue;
            }
            pRight = sqliteExpr(TK_ID, 0, 0, 0);
            if( pRight==0 ) break;
            pRight->token.z = zName;
            pRight->token.n = strlen(zName);
            pRight->token.dyn = 0;
            pRight->token.base = 1;
            if( zTabName && pTabList->nSrc>1 ){
              pLeft = sqliteExpr(TK_ID, 0, 0, 0);
              pExpr = sqliteExpr(TK_DOT, pLeft, pRight, 0);
              if( pExpr==0 ) break;
              pLeft->token.z = zTabName;
              pLeft->token.n = strlen(zTabName);
              pLeft->token.dyn = 0;
              pLeft->token.base = 1;
              sqliteSetString((char**)&pExpr->token.z, zTabName, ".", zName, 0);
              pExpr->token.n = strlen(pExpr->token.z);
              pExpr->token.base = 0;
              pExpr->token.dyn = 1;
            }else{
              pExpr = pRight;

            }
            pNew = sqliteExprListAppend(pNew, pExpr, 0);
          }
        }
        if( !tableSeen ){
          if( pName ){
            sqliteSetNString(&pParse->zErrMsg, "no such table: ", -1, 

  SrcList *pSrc = p->pSrc;
  Table *pTab;
  if( p==0 ) return;
  for(i=0; i<pSrc->nSrc; i++){
    if( (pTab = pSrc->a[i].pTab)!=0 ){
      if( pTab->isTransient ){
        sqliteDeleteTable(0, pTab);
#if 0
        sqliteSelectDelete(pSrc->a[i].pSelect);
        pSrc->a[i].pSelect = 0;
#endif
      }
      pSrc->a[i].pTab = 0;
      if( pSrc->a[i].pSelect ){
        sqliteSelectUnbind(pSrc->a[i].pSelect);
      }
    }
  }

    pExpr->op = pNew->op;
    pExpr->pLeft = sqliteExprDup(pNew->pLeft);
    pExpr->pRight = sqliteExprDup(pNew->pRight);
    pExpr->pList = sqliteExprListDup(pNew->pList);
    pExpr->iTable = pNew->iTable;
    pExpr->iColumn = pNew->iColumn;
    pExpr->iAgg = pNew->iAgg;
    pExpr->nFuncName = pNew->nFuncName;
    sqliteTokenCopy(&pExpr->token, &pNew->token);
    if( iSub!=iTable ){
      changeTables(pExpr, iSub, iTable);
    }
  }else{
    static void substExprList(ExprList*,int,ExprList*,int);
    substExpr(pExpr->pLeft, iTable, pEList, iSub);
    substExpr(pExpr->pRight, iTable, pEList, iSub);

  iParent = p->base + iFrom;
  iSub = pSub->base;
  substExprList(p->pEList, iParent, pSub->pEList, iSub);
  pList = p->pEList;
  for(i=0; i<pList->nExpr; i++){
    if( pList->a[i].zName==0 ){
      Expr *pExpr = pList->a[i].pExpr;
      assert( pExpr->token.z!=0 );
      pList->a[i].zName = sqliteStrNDup(pExpr->token.z, pExpr->token.n);
    }
  }
  if( isAgg ){
    substExprList(p->pGroupBy, iParent, pSub->pEList, iSub);
    substExpr(p->pHaving, iParent, pSub->pEList, iSub);
  }
  substExprList(p->pOrderBy, iParent, pSub->pEList, iSub);

  */
  if( p->pGroupBy || p->pHaving || p->pWhere ) return 0;
  if( p->pSrc->nSrc!=1 ) return 0;
  if( p->pEList->nExpr!=1 ) return 0;
  pExpr = p->pEList->a[0].pExpr;
  if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
  if( pExpr->pList==0 || pExpr->pList->nExpr!=1 ) return 0;
  if( pExpr->nFuncName!=3 ) return 0;
  if( sqliteStrNICmp(pExpr->token.z,"min",3)==0 ){
    seekOp = OP_Rewind;
  }else if( sqliteStrNICmp(pExpr->token.z,"max",3)==0 ){
    seekOp = OP_Last;
  }else{
    return 0;
  }

/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.143 2002/08/24 18:24:55 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>

  u8 onError;      /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  u8 autoIndex;    /* True if is automatically created (ex: by UNIQUE) */
  Index *pNext;    /* The next index associated with the same table */
};

/*
** Each token coming out of the lexer is an instance of
** this structure.  Tokens are also used as part of an expression.
**
** A "base" token is a real single token such as would come out of the
** lexer.  There are also compound tokens which are aggregates of one
** or more base tokens.  Compound tokens are used to name columns in the
** result set of a SELECT statement.  In the expression "a+b+c", "b"
** is a base token but "a+b" is a compound token.
*/
struct Token {
  const char *z;      /* Text of the token.  Not NULL-terminated! */
  unsigned dyn  : 1;  /* True for malloced memory, false for static */
  unsigned base : 1;  /* True for a base token, false for compounds */
  unsigned n    : 30; /* Number of characters in this token */
};

/*
** Each node of an expression in the parse tree is an instance
** of this structure.
**
** Expr.op is the opcode.  The integer parser token codes are reused
** as opcodes here.  For example, the parser defines TK_GE to be an integer
** code representing the ">=" operator.  This same integer code is reused
** to represent the greater-than-or-equal-to operator in the expression
** tree.
**
** Expr.pRight and Expr.pLeft are subexpressions.  Expr.pList is a list
** of argument if the expression is a function.
**
** Expr.token is the operator token for this node.  For some expressions
** that have subexpressions, Expr.token can be the complete text that gave
** rise to the Expr.  In the latter case, the token is marked as being
** a compound token.
**
** An expression of the form ID or ID.ID refers to a column in a table.
** For such expressions, Expr.op is set to TK_COLUMN and Expr.iTable is
** the integer cursor number of a VDBE cursor pointing to that table and
** Expr.iColumn is the column number for the specific column.  If the
** expression is used as a result in an aggregate SELECT, then the
** value is also stored in the Expr.iAgg column in the aggregate so that
** it can be accessed after all aggregates are computed.
**
** If the expression is a function, the Expr.iTable is an integer code
** representing which function.
**
** The Expr.pSelect field points to a SELECT statement.  The SELECT might
** be the right operand of an IN operator.  Or, if a scalar SELECT appears
** in an expression the opcode is TK_SELECT and Expr.pSelect is the only
** operand.
*/
struct Expr {
  u8 op;                 /* Operation performed by this node */
  u8 dataType;           /* Either SQLITE_SO_TEXT or SQLITE_SO_NUM */
  u8 isJoinExpr;         /* Origin is the ON or USING phrase of a join */
  u8 nFuncName;          /* Number of characters in a function name */
  Expr *pLeft, *pRight;  /* Left and right subnodes */
  ExprList *pList;       /* A list of expressions used as function arguments
                         ** or in "<expr> IN (<expr-list)" */
  Token token;           /* An operand token */

  int iTable, iColumn;   /* When op==TK_COLUMN, then this expr node means the
                         ** iColumn-th field of the iTable-th table. */
  int iAgg;              /* When op==TK_COLUMN and pParse->useAgg==TRUE, pull
                         ** result from the iAgg-th element of the aggregator */
  Select *pSelect;       /* When the expression is a sub-select.  Also the
                         ** right side of "<expr> IN (<select>)" */
};

 * 1. In the "trigHash" hash table (part of the sqlite* that represents the 
 *    database). This allows Trigger structures to be retrieved by name.
 * 2. All triggers associated with a single table form a linked list, using the
 *    pNext member of struct Trigger. A pointer to the first element of the
 *    linked list is stored as the "pTrigger" member of the associated
 *    struct Table.
 *





 * The "step_list" member points to the first element of a linked list
 * containing the SQL statements specified as the trigger program.
 *
 * When a trigger is initially created, the "isCommit" member is set to FALSE.
 * When a transaction is rolled back, any Trigger structures with "isCommit" set
 * to FALSE are deleted by the logic in sqliteRollbackInternalChanges(). When
 * a transaction is commited, the "isCommit" member is set to TRUE for any

  int tr_tm;              /* One of TK_BEFORE, TK_AFTER */
  Expr *pWhen;            /* The WHEN clause of the expresion (may be NULL) */
  IdList *pColumns;       /* If this is an UPDATE OF <column-list> trigger,
                             the <column-list> is stored here */
  int foreach;            /* One of TK_ROW or TK_STATEMENT */

  TriggerStep *step_list; /* Link list of trigger program steps             */

  Trigger *pNext;         /* Next trigger associated with the table */
};

/*
 * An instance of struct TriggerStep is used to store a single SQL statement
 * that is a part of a trigger-program. 
 *

int sqliteIsRowid(const char*);
void sqliteGenerateRowDelete(sqlite*, Vdbe*, Table*, int, int);
void sqliteGenerateRowIndexDelete(sqlite*, Vdbe*, Table*, int, char*);
void sqliteGenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int);
void sqliteCompleteInsertion(Parse*, Table*, int, char*, int, int);
void sqliteBeginWriteOperation(Parse*, int);
void sqliteEndWriteOperation(Parse*);



Expr *sqliteExprDup(Expr*);
void sqliteTokenCopy(Token*, Token*);
ExprList *sqliteExprListDup(ExprList*);
SrcList *sqliteSrcListDup(SrcList*);
IdList *sqliteIdListDup(IdList*);
Select *sqliteSelectDup(Select*);
FuncDef *sqliteFindFunction(sqlite*,const char*,int,int,int);
void sqliteRegisterBuiltinFunctions(sqlite*);
int sqliteSafetyOn(sqlite*);
int sqliteSafetyOff(sqlite*);
int sqliteSafetyCheck(sqlite*);
void sqliteChangeCookie(sqlite*, Vdbe*);
void sqliteCreateTrigger(Parse*, Token*, int, int, IdList*, Token*, 
                         int, Expr*, TriggerStep*, Token*);
void sqliteDropTrigger(Parse*, Token*, int);
int sqliteTriggersExist(Parse* , Trigger* , int , int , int, ExprList*);
int sqliteCodeRowTrigger(Parse*, int, ExprList*, int, Table *, int, int, 
                         int, int);
void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*);
TriggerStep *sqliteTriggerSelectStep(Select*);
TriggerStep *sqliteTriggerInsertStep(Token*, IdList*, ExprList*, Select*, int);
TriggerStep *sqliteTriggerUpdateStep(Token*, ExprList*, Expr*, int);
TriggerStep *sqliteTriggerDeleteStep(Token*, Expr*);
void sqliteDeleteTrigger(Trigger*);
int sqliteJoinType(Parse*, Token*, Token*, Token*);

*************************************************************************
** An tokenizer for SQL
**
** This file contains C code that splits an SQL input string up into
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
** $Id: tokenize.c,v 1.48 2002/08/24 18:24:56 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include <stdlib.h>

/*

    
    if( (db->flags & SQLITE_Interrupt)!=0 ){
      pParse->rc = SQLITE_INTERRUPT;
      sqliteSetString(pzErrMsg, "interrupt", 0);
      break;
    }
    pParse->sLastToken.z = &zSql[i];
    pParse->sLastToken.base = 1;
    pParse->sLastToken.dyn = 0;
    pParse->sLastToken.n = sqliteGetToken((unsigned char*)&zSql[i], &tokenType);
    i += pParse->sLastToken.n;
    if( once ){
      pParse->sFirstToken = pParse->sLastToken;
      once = 0;
    }
    switch( tokenType ){

/*
**
** 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.
**
*************************************************************************
*
*/
#include "sqliteInt.h"

/*
** Delete a linked list of TriggerStep structures.
*/
static void sqliteDeleteTriggerStep(TriggerStep *pTriggerStep){
  while( pTriggerStep ){
    TriggerStep * pTmp = pTriggerStep;
    pTriggerStep = pTriggerStep->pNext;

    if( pTmp->target.dyn ) sqliteFree(pTmp->target.z);
    sqliteExprDelete(pTmp->pWhere);
    sqliteExprListDelete(pTmp->pExprList);
    sqliteSelectDelete(pTmp->pSelect);
    sqliteIdListDelete(pTmp->pIdList);

    sqliteFree(pTmp);
  }
}

/*
** This is called by the parser when it sees a CREATE TRIGGER statement. See
** comments surrounding struct Trigger in sqliteInt.h for a description of 
** how triggers are stored.
*/
void sqliteCreateTrigger(
  Parse *pParse,      /* The parse context of the CREATE TRIGGER statement */
  Token *pName,       /* The name of the trigger */
  int tr_tm,          /* One of TK_BEFORE, TK_AFTER , TK_INSTEAD */
  int op,             /* One of TK_INSERT, TK_UPDATE, TK_DELETE */
  IdList *pColumns,   /* column list if this is an UPDATE OF trigger */
  Token *pTableName,  /* The name of the table/view the trigger applies to */
  int foreach,        /* One of TK_ROW or TK_STATEMENT */
  Expr *pWhen,        /* WHEN clause */
  TriggerStep *pStepList, /* The triggered program */
  Token *pAll             /* Token that describes the complete CREATE TRIGGER */

){
  Trigger *nt;
  Table   *tab;



  /* Check that: 
  ** 1. the trigger name does not already exist.
  ** 2. the table (or view) does exist.
  ** 3. that we are not trying to create a trigger on the sqlite_master table
  ** 4. That we are not trying to create an INSTEAD OF trigger on a table.
  ** 5. That we are not trying to create a BEFORE or AFTER trigger on a view.

  }

  /* Build the Trigger object */
  nt = (Trigger*)sqliteMalloc(sizeof(Trigger));
  if( nt==0 ) goto trigger_cleanup;
  nt->name = sqliteStrNDup(pName->z, pName->n);
  nt->table = sqliteStrNDup(pTableName->z, pTableName->n);

  if( sqlite_malloc_failed ) goto trigger_cleanup;
  nt->op = op;
  nt->tr_tm = tr_tm;
  nt->pWhen = sqliteExprDup(pWhen);
  sqliteExprDelete(pWhen);
  nt->pColumns = sqliteIdListDup(pColumns);
  sqliteIdListDelete(pColumns);
  nt->foreach = foreach;
  nt->step_list = pStepList;















  /* if we are not initializing, and this trigger is not on a TEMP table, 
  ** build the sqlite_master entry
  */
  if( !pParse->initFlag ){
    static VdbeOp insertTrig[] = {
      { OP_NewRecno,   0, 0,  0          },

    sqliteBeginWriteOperation(pParse, 0);
    sqliteOpenMasterTable(v, tab->isTemp);
    addr = sqliteVdbeAddOpList(v, ArraySize(insertTrig), insertTrig);
    sqliteVdbeChangeP3(v, addr, tab->isTemp ? TEMP_MASTER_NAME : MASTER_NAME,
                       P3_STATIC);
    sqliteVdbeChangeP3(v, addr+2, nt->name, 0); 
    sqliteVdbeChangeP3(v, addr+3, nt->table, 0); 
    sqliteVdbeChangeP3(v, addr+5, pAll->z, pAll->n);
    if( !tab->isTemp ){
      sqliteChangeCookie(pParse->db, v);
    }
    sqliteVdbeAddOp(v, OP_Close, 0, 0);
    sqliteEndWriteOperation(pParse);
  }

  if( !pParse->explain ){
    /* Stick it in the hash-table */
    sqliteHashInsert(&(pParse->db->trigHash), nt->name, pName->n + 1, nt);

    /* Attach it to the table object */
    nt->pNext = tab->pTrigger;
    tab->pTrigger = nt;
    return;
  }else{

    sqliteFree(nt->name);
    sqliteFree(nt->table);
    sqliteFree(nt);
  }

trigger_cleanup:

  sqliteIdListDelete(pColumns);
  sqliteExprDelete(pWhen);
  sqliteDeleteTriggerStep(pStepList);
}

/*
** Make a copy of all components of the given trigger step.  This has
** the effect of copying all Expr.token.z values into memory obtained
** from sqliteMalloc().  As initially created, the Expr.token.z values
** all point to the input string that was fed to the parser.  But that
** string is ephemeral - it will go away as soon as the sqlite_exec()
** call that started the parser exits.  This routine makes a persistent
** copy of all the Expr.token.z strings so that the TriggerStep structure
** will be valid even after the sqlite_exec() call returns.
*/
static void sqlitePersistTriggerStep(TriggerStep *p){
  if( p->target.z ){
    p->target.z = sqliteStrNDup(p->target.z, p->target.n);
    p->target.dyn = 1;
  }
  if( p->pSelect ){
    Select *pNew = sqliteSelectDup(p->pSelect);
    sqliteSelectDelete(p->pSelect);
    p->pSelect = pNew;
  }
  if( p->pWhere ){
    Expr *pNew = sqliteExprDup(p->pWhere);
    sqliteExprDelete(p->pWhere);
    p->pWhere = pNew;
  }
  if( p->pExprList ){
    ExprList *pNew = sqliteExprListDup(p->pExprList);
    sqliteExprListDelete(p->pExprList);
    p->pExprList = pNew;
  }
  if( p->pIdList ){
    IdList *pNew = sqliteIdListDup(p->pIdList);
    sqliteIdListDelete(p->pIdList);

    p->pIdList = pNew;

  }
}

/*
** Turn a SELECT statement (that the pSelect parameter points to) into
** a trigger step.  Return a pointer to a TriggerStep structure.
**
** The parser calls this routine when it finds a SELECT statement in
** body of a TRIGGER.  
*/
TriggerStep *sqliteTriggerSelectStep(Select *pSelect){
  TriggerStep *pTriggerStep = sqliteMalloc(sizeof(TriggerStep));
  if( pTriggerStep==0 ) return 0;

  pTriggerStep->op = TK_SELECT;
  pTriggerStep->pSelect = pSelect;
  pTriggerStep->orconf = OE_Default;
  sqlitePersistTriggerStep(pTriggerStep);

  return pTriggerStep;
}

/*
** Build a trigger step out of an INSERT statement.  Return a pointer
** to the new trigger step.

  pTriggerStep->op = TK_INSERT;
  pTriggerStep->pSelect = pSelect;
  pTriggerStep->target  = *pTableName;
  pTriggerStep->pIdList = pColumn;
  pTriggerStep->pExprList = pEList;
  pTriggerStep->orconf = orconf;
  sqlitePersistTriggerStep(pTriggerStep);

  return pTriggerStep;
}

/*
** Construct a trigger step that implements an UPDATE statement and return
** a pointer to that trigger step.  The parser calls this routine when it

  if( pTriggerStep==0 ) return 0;

  pTriggerStep->op = TK_UPDATE;
  pTriggerStep->target  = *pTableName;
  pTriggerStep->pExprList = pEList;
  pTriggerStep->pWhere = pWhere;
  pTriggerStep->orconf = orconf;
  sqlitePersistTriggerStep(pTriggerStep);

  return pTriggerStep;
}

/*
** Construct a trigger step that implements a DELETE statement and return
** a pointer to that trigger step.  The parser calls this routine when it
** sees a DELETE statement inside the body of a CREATE TRIGGER.
*/
TriggerStep *sqliteTriggerDeleteStep(Token *pTableName, Expr *pWhere){
  TriggerStep *pTriggerStep = sqliteMalloc(sizeof(TriggerStep));
  if( pTriggerStep==0 ) return 0;

  pTriggerStep->op = TK_DELETE;
  pTriggerStep->target  = *pTableName;
  pTriggerStep->pWhere = pWhere;
  pTriggerStep->orconf = OE_Default;
  sqlitePersistTriggerStep(pTriggerStep);

  return pTriggerStep;
}

/* 
** Recursively delete a Trigger structure
*/
void sqliteDeleteTrigger(Trigger *pTrigger){
  TriggerStep *pTriggerStep;


  sqliteDeleteTriggerStep(pTrigger->step_list);











  sqliteFree(pTrigger->name);
  sqliteFree(pTrigger->table);
  sqliteExprDelete(pTrigger->pWhen);
  sqliteIdListDelete(pTrigger->pColumns);

  sqliteFree(pTrigger);
}

/*
 * This function is called to drop a trigger from the database schema. 
 *
 * This may be called directly from the parser, or from within 

# 2001 September 15
#
# 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 runs all tests.
#
# $Id: all.test,v 1.17 2002/08/24 18:24:57 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
rename finish_test really_finish_test
proc finish_test {} {memleak_check}

if {[file exists ./sqlite_test_count]} {

set LeakList {}

set EXCLUDE {
  all.test
  quick.test
  malloc.test
  misuse.test
  memleak.test
}
#  btree2.test

for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} {
  set btree_native_byte_order [expr {($Counter>>1)&0x1}]
  if {$Counter%2} {
    set ::SETUP_SQL {PRAGMA default_synchronous=off;}

# 2001 September 15
#
# 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 runs all tests.
#
# $Id: quick.test,v 1.4 2002/08/24 18:24:57 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
rename finish_test really_finish_test
proc finish_test {} {}
set ISQUICK 1

set EXCLUDE {
  all.test
  quick.test
  btree2.test
  malloc.test
  memleak.test
}

foreach testfile [lsort -dictionary [glob $testdir/*.test]] {
  set tail [file tail $testfile]
  if {[lsearch -exact $EXCLUDE $tail]>=0} continue
  source $testfile
}

# 2002 February 26
#
# 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 testing VIEW statements.
#
# $Id: view.test,v 1.9 2002/08/24 18:24:57 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test view-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);

do_test view-7.6 {
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM test;
  }
} {1 2 3}

do_test view-8.1 {
  execsql {
    CREATE VIEW v6 AS SELECT pqr, xyz FROM v1;
    SELECT * FROM v6 ORDER BY xyz;
  }
} {7 2 13 5 19 8 27 12}
if 0 {
do_test view-8.2 {
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM v6 ORDER BY xyz;
  }
} {7 2 13 5 19 8 27 12}
do_test view-8.3 {
  execsql {
    CREATE VIEW v7 AS SELECT pqr+xyz AS a FROM v6;
    SELECT * FROM v7 ORDER BY a;
  }
} {9 18 27 39}
do_test view-8.4 {
  execsql { PRAGMA vdbe_trace=on;
    CREATE VIEW v8 AS SELECT max(cnt) FROM
      (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo);
    SELECT * FROM v8;
  }
} 3
}

finish_test

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.35 2002/08/24 18:24:57 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>

from malloc() and returns a pointer to the malloced buffer.  
<b>sqlite_mprintf()</b> also understands the %q and %Q extensions described
above.  The <b>sqlite_vmprintf()</b> is a varargs version of the same
routine.  The string pointer that these routines return should be freed
by passing it to <b>sqlite_freemem()</b>.
</p>

<a name="cfunc">
<h2>Adding New SQL Functions</h2>

<p>Beginning with version 2.4.0, SQLite allows the SQL language to be
extended with new functions implemented as C code.  The following interface
is used:
</p>

The <b>sqlite_create_function()</b> interface is used to create 
regular functions and <b>sqlite_create_aggregate()</b> is used to
create new aggregate functions.  In both cases, the <b>db</b>
parameter is an open SQLite database on which the functions should
be registered, <b>zName</b> is the name of the new function,
<b>nArg</b> is the number of arguments, and <b>pUserData</b> is
a pointer which is passed through unchanged to the C implementation
of the function.  Both routines return 0 on success and non-zero
if there are any errors.
</p>

<p>
The length of a function name may not exceed 255 characters.
Any attempt to create a function whose name exceeds 255 characters
in length will result in an error.
</p>

<p>
For regular functions, the <b>xFunc</b> callback is invoked once
for each function call.  The implementation of xFunc should call
one of the <b>sqlite_set_result_...</b> interfaces to return its
result.  The <b>sqlite_user_data()</b> routine can be used to

#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.19 2002/08/24 18:24:57 drh Exp $}

puts {<html>
<head>
  <title>SQLite Frequently Asked Questions</title>
</head>
<body bgcolor="white">
<h1 align="center">Frequently Asked Questions</h1>

  tables and indices, but again it is not really possible to reach this
  limit due to the file size constraint.</p>

  <p>The name and "CREATE TABLE" statement for a table must fit entirely
  within a 1-megabyte row of the SQLITE_MASTER table.  Other than this,
  there are no constraints on the length of the name of a table, or on the
  number of columns, etc.  Indices are similarly unconstrained.</p>

  <p>The names of tables, indices, view, triggers, and columns can be
  as long as desired.  However, the names of SQL functions (as created
  by the <a href="c_interface.html#cfunc">sqlite_create_function()</a> API)
  may not exceed 255 characters in length.</p>
}

faq {
  What is the maximum size of a VARCHAR in SQLite?
} {
  <p>Remember, SQLite is typeless.  A VARCHAR column can hold as much
  data as any other column.  The total amount of data in a single row

#
# Run this script to generated a omitted.html output file
#
set rcsid {$Id: omitted.tcl,v 1.3 2002/08/24 18:24:58 drh Exp $}

puts {<html>
<head>
  <title>SQL Features That SQLite Does Not Implement</title>
</head>
<body bgcolor="white">
<h1 align="center">

feature {CHECK constraints} {
  CHECK constraints are parsed but they are not enforced.
  NOT NULL and UNIQUE constraints are enforced, however.
}

feature {Variable subqueries} {
  Subqueries must be static.  They are evaluated only once.  They may not,
  therefore, refer to variables in the main query.
}

feature {FOREIGN KEY constraints} {
  FOREIGN KEY constraints are parsed but are not enforced.
}

feature {Complete trigger support} {
  There is some support for triggers but it is not complete.  Missing
  subfeatures include FOR EACH STATEMENT triggers (currently all triggers
  must be FOR EACH ROW), INSTEAD OF triggers on tables (currently 
  INSTEAD OF triggers are only allowed on views), and recursive
  triggers - triggers that trigger themselves.
}

feature {ALTER TABLE} {
  To change a table you have to delete it (saving its contents to a temporary
  table) and recreate it from scratch.
}

feature {Nested transactions} {
  The current implementation only allows a single active transaction.
}

feature {The COUNT(DISTINCT X) function} {
  You can accomplish the same thing using a subquery, like this:<br />
  &nbsp;&nbsp;SELECT count(x) FROM (SELECT DISTINCT x FROM tbl);
}

feature {RIGHT and FULL OUTER JOIN} {

#
# Run this Tcl script to generate the speed.html file.
#
set rcsid {$Id: speed.tcl,v 1.8 2002/08/24 18:24:58 drh Exp $ }

puts {<html>
<head>
  <title>Database Speed Comparison: SQLite versus PostgreSQL</title>
</head>
<body bgcolor=white>
<h1 align=center>
Database Speed Comparison
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<h2>Executive Summary</h2>

<p>A series of tests were run to measure the relative performance of
SQLite 2.7.0, PostgreSQL 7.1.3, and MySQL 3.23.41.
The following are general
conclusions drawn from these experiments:
</p>

<ul>
<li><p>
  SQLite 2.7.0 is significantly faster than PostgreSQL 7.1.3
  for most common operations.
</p></li>
<li><p>
  The speed of SQLite 2.7.0 is similar to MySQL 3.23.41.
  This is true in spite of the
  fact that SQLite contains full transaction support whereas the
  version of MySQL tested did not.
</p></li>
<li><p>
  These tests did not attempt to measure multi-user performance or
  optimization of complex queries involving multiple joins and subqueries.
</ul>

<h2>Test Environment</h2>

<p>
The platform used for these tests is a 1.6GHz Athlon with 1GB or memory
and an IDE disk drive.  The operating system is RedHat Linux 7.2 with
a stock kernel.
</p>

<p>
The PostgreSQL and MySQL servers used were as delivered by default on
RedHat 7.2.  (PostgreSQL version 7.1.3 and MySQL version 3.23.41.)
No effort was made to tune these engines.  Note in particular
the the default MySQL configuration on RedHat 7.2 does not support
transactions.  Not having to support transactions gives MySQL a
big speed advantage, but SQLite is still able to hold its own on most
tests.  On the other hand, I am told that the default PostgreSQL
configuration is unnecessarily conservative (it is designed to
work on a machine with 8MB of RAM) and that PostgreSQL could
be made to run a lot faster with some knowledgable configuration
tuning.  I have not, however, been able to personally confirm
these reports.
</p>

<p>
SQLite was tested in the same configuration that it appears
on the website.  It was compiled with -O6 optimization and with
the -DNDEBUG=1 switch which disables the many "assert()" statements
in the SQLite code.  The -DNDEBUG=1 compiler option roughly doubles
the speed of SQLite.
</p>

<p>
All tests are conducted on an otherwise quiescent machine.

INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');<br>
<i>... 995 lines omitted</i><br>
INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');<br>
INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');<br>
INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.613</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.086</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;8.672</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.286</td></tr>
</table>

<p>SQLite must close and reopen the database file, and thus invalidate
its cache, for each SQL statement.  In spite of this, the asynchronous
version of SQLite is still nearly as fast as MySQL.  Notice how much slower
the synchronous version is, however.  This is due to the necessity of
calling <b>fsync()</b> after each SQL statement.</p>

<h2>Test 2: 25000 INSERTs in a transaction</h2>
<blockquote>
BEGIN;<br>
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));<br>
INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one');<br>
<i>... 24997 lines omitted</i><br>
INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty seven');<br>
INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred thirty');<br>
COMMIT;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.430</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.025</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;0.885</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.753</td></tr>
</table>

<p>
When all the INSERTs are put in a transaction, SQLite no longer has to
close and reopen the database between each statement.  It also does not
have to do any fsync()s until the very end.  When unshackled in
this way, SQLite is much faster than either PostgreSQL and MySQL.
</p>

<h2>Test 3: 100 SELECTs without an index</h2>
<blockquote>
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<1200;<br>
<i>... 94 lines omitted</i><br>
SELECT count(*), avg(b) FROM t2 WHERE b>=9700 AND b<10700;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.274</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.624</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;5.585</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;5.443</td></tr>
</table>

<p>
This test does 100 queries on a 25000 entry table without an index,
thus requiring a full table scan.  SQLite is about half the speed of
PostgreSQL and MySQL.  This is because SQLite stores all data as strings
and must therefore call <b>strtod()</b> 5 million times in the
course of evaluating the WHERE clauses.  Both PostgreSQL and MySQL
store data as binary values where appropriate and can forego
this conversion effort.
</p>


<h2>Test 4: 100 SELECTs on a string comparison</h2>
<blockquote>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';<br>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';<br>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%three%';<br>
<i>... 94 lines omitted</i><br>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety eight%';<br>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';<br>
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;14.511</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.616</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;5.966</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;5.918</td></tr>
</table>

<p>
This set of 100 queries uses string comparisons instead of
numerical comparisions.  As a result, the speed of SQLite is
compariable to or better then PostgreSQL and MySQL.
</p>

<h2>Test 5: Creating an index</h2>
<blockquote>
CREATE INDEX i2a ON t2(a);<br>CREATE INDEX i2b ON t2(b);
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.483</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.304</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;0.779</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.637</td></tr>
</table>

<p>
SQLite is slower at creating new indices.  But since creating
new indices is an uncommon operation, this is not seen as a
problem.
</p>

<h2>Test 6: 5000 SELECTs with an index</h2>
<blockquote>
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<200;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;<br>
<i>... 4994 lines omitted</i><br>
SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.939</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.335</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;1.165</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.144</td></tr>
</table>

<p>
This test runs a set of 5000 queries that are similar in form to
those in test 3.  But now instead of being half as fast, SQLite
is faster than both PostgreSQL and MySQL.
</p>

<h2>Test 7: 1000 UPDATEs without an index</h2>
<blockquote>
BEGIN;<br>
UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;<br>
UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;<br>
<i>... 996 lines omitted</i><br>
UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;<br>
UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;<br>
COMMIT;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.536</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;7.281</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;0.817</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.726</td></tr>
</table>

<p>
Here is a case where MySQL is over 10 times slower than SQLite.
The reason for this is unclear.
</p>

<h2>Test 8: 25000 UPDATEs with an index</h2>
<blockquote>
BEGIN;<br>
UPDATE t2 SET b=271822 WHERE a=1;<br>
UPDATE t2 SET b=28304 WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET b=442549 WHERE a=24999;<br>
UPDATE t2 SET b=423958 WHERE a=25000;<br>
COMMIT;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;29.318</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;7.514</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;7.681</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;7.852</td></tr>
</table>

<p>
In this case MySQL is slightly faster than SQLite, though not by much.
The difference is believed to have to do with the fact SQLite 
handles the integers as strings instead of binary numbers.
</p>

<h2>Test 9: 25000 text UPDATEs with an index</h2>
<blockquote>
BEGIN;<br>
UPDATE t2 SET c='four hundred sixty eight thousand twenty six' WHERE a=1;<br>
UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty eight' WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;<br>
UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' WHERE a=25000;<br>
COMMIT;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;50.020</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.841</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;5.346</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;5.393</td></tr>
</table>

<p>
When updating a text field instead of an integer field,
SQLite is slightly faster than MySQL.
</p>

<h2>Test 10: INSERTs from a SELECT</h2>
<blockquote>
BEGIN;<br>INSERT INTO t1 SELECT * FROM t2;<br>INSERT INTO t2 SELECT * FROM t1;<br>COMMIT;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;57.834</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.335</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;5.073</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;2.085</td></tr>
</table>

<p>
The poor performance of PostgreSQL in this case appears to be due to its
synchronous behavior.  The CPU was mostly idle the test run.  Presumably,
PostgreSQL was spending most of its time waiting on disk I/O to complete.
</p>

<p>
SQLite is slower than MySQL because it creates a temporary table to store
the result of the query, then does an insert from the temporary table.
A future enhancement that moves data directly from teh query into the
insert table should double the speed of SQLite.
</p>

<h2>Test 11: DELETE without an index</h2>
<blockquote>
DELETE FROM t2 WHERE c LIKE '%fifty%';
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.733</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.768</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;5.418</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.668</td></tr>
</table>


<h2>Test 12: DELETE with an index</h2>
<blockquote>
DELETE FROM t2 WHERE a>10 AND a<20000;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.867</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.068</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;1.453</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.745</td></tr>
</table>


<h2>Test 13: A big INSERT after a big DELETE</h2>
<blockquote>
INSERT INTO t2 SELECT * FROM t1;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;66.099</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.663</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;4.029</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.729</td></tr>
</table>

<p>
Earlier versions of SQLite would show decreasing performance after a
sequence DELETEs followed by new INSERTs.  As this test shows, the
problem has now been resolved.
</p>

<h2>Test 14: A big DELETE followed by many small INSERTs</h2>
<blockquote>
BEGIN;<br>
DELETE FROM t1;<br>
INSERT INTO t1 VALUES(1,29676,'twenty nine thousand six hundred seventy six');<br>
<i>... 2997 lines omitted</i><br>
INSERT INTO t1 VALUES(2999,37835,'thirty seven thousand eight hundred thirty five');<br>
INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred seventeen');<br>
COMMIT;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.168</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.866</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;0.288</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.155</td></tr>
</table>

<h2>Test 15: DROP TABLE</h2>
<blockquote>
DROP TABLE t1;<br>DROP TABLE t2;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.100</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.012</td></tr>
<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;0.572</td></tr>
<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.168</td></tr>
</table>

<p>
SQLite is slower than the other databases when it comes to dropping tables.
This is not seen as a big problem, however, since DROP TABLE is seldom
used in speed-critical situations.
</p>