/ Check-in [22d8726e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Change the way token memory is allocated in an effort to fix ticket #136. There is now a memory leak when using views of views. (CVS 725)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 22d8726e61eec0e53893f492cb2163824b87a23e
User & Date: drh 2002-08-24 18:24:52
Context
2002-08-25
18:29
Fix the memory leak introduced by check-in (725). (CVS 726) check-in: b957dafc user: drh tags: trunk
2002-08-24
18:24
Change the way token memory is allocated in an effort to fix ticket #136. There is now a memory leak when using views of views. (CVS 725) check-in: 22d8726e user: drh tags: trunk
2002-08-22
18:18
Fix for ticket #138: Makefile doesn't use exec_prefix, has some install problems (CVS 724) check-in: 97fc4a71 user: jadams tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to main.mk.

   122    122   # Generate the file "last_change" which contains the date of change
   123    123   # of the most recently modified source code file
   124    124   #
   125    125   last_change:	$(SRC)
   126    126   	cat $(SRC) | grep '$$Id: ' | sort +4 | tail -1 \
   127    127             | awk '{print $$5,$$6}' >last_change
   128    128   
   129         -libsqlite.a:	$(LIBOBJ) tclsqlite.o
   130         -	$(AR) libsqlite.a $(LIBOBJ) tclsqlite.o
          129  +libsqlite.a:	$(LIBOBJ)
          130  +	$(AR) libsqlite.a $(LIBOBJ)
   131    131   	$(RANLIB) libsqlite.a
   132    132   
   133    133   sqlite$(EXE):	$(TOP)/src/shell.c libsqlite.a sqlite.h
   134    134   	$(TCCX) $(READLINE_FLAGS) -o sqlite$(EXE) $(TOP)/src/shell.c \
   135    135   		libsqlite.a $(LIBREADLINE) $(THREADLIB)
   136    136   
   137    137   # This target creates a directory named "tsrc" and fills it with

Changes to src/build.c.

    21     21   **     COPY
    22     22   **     VACUUM
    23     23   **     BEGIN TRANSACTION
    24     24   **     COMMIT
    25     25   **     ROLLBACK
    26     26   **     PRAGMA
    27     27   **
    28         -** $Id: build.c,v 1.109 2002/08/18 20:28:07 drh Exp $
           28  +** $Id: build.c,v 1.110 2002/08/24 18:24:53 drh Exp $
    29     29   */
    30     30   #include "sqliteInt.h"
    31     31   #include <ctype.h>
    32     32   
    33     33   /*
    34     34   ** This routine is called when a new SQL statement is beginning to
    35     35   ** be parsed.  Check to see if the schema for the database needs
................................................................................
   840    840   void sqliteCreateView(
   841    841     Parse *pParse,     /* The parsing context */
   842    842     Token *pBegin,     /* The CREATE token that begins the statement */
   843    843     Token *pName,      /* The token that holds the name of the view */
   844    844     Select *pSelect,   /* A SELECT statement that will become the new view */
   845    845     int isTemp         /* TRUE for a TEMPORARY view */
   846    846   ){
   847         -  Token sEnd;
   848    847     Table *p;
          848  +  int n;
   849    849     const char *z;
   850         -  int n, offset;
          850  +  Token sEnd;
   851    851   
   852    852     sqliteStartTable(pParse, pBegin, pName, isTemp);
   853    853     p = pParse->pNewTable;
   854    854     if( p==0 ){
   855    855       sqliteSelectDelete(pSelect);
   856    856       return;
   857    857     }
   858    858     /* Ignore ORDER BY clauses on a SELECT */
   859    859     if( pSelect->pOrderBy ){
   860    860       sqliteExprListDelete(pSelect->pOrderBy);
   861    861       pSelect->pOrderBy = 0;
   862    862     }
   863         -  p->pSelect = pSelect;
          863  +  /* Make a copy of the entire SELECT statement that defines the view.
          864  +  ** This will force all the Expr.token.z values to be dynamically
          865  +  ** allocated rather than point to the input string - which means that
          866  +  ** they will persist after the current sqlite_exec() call returns.
          867  +  */
          868  +  p->pSelect = sqliteSelectDup(pSelect);
          869  +  sqliteSelectDelete(pSelect);
   864    870     if( !pParse->initFlag ){
   865         -    if( sqliteViewGetColumnNames(pParse, p) ){
   866         -      return;
          871  +    sqliteViewGetColumnNames(pParse, p);
   867    872       }
   868         -  }
          873  +
          874  +  /* Locate the end of the CREATE VIEW statement.  Make sEnd point to
          875  +  ** the end.
          876  +  */
   869    877     sEnd = pParse->sLastToken;
   870    878     if( sEnd.z[0]!=0 && sEnd.z[0]!=';' ){
   871    879       sEnd.z += sEnd.n;
   872    880     }
   873    881     sEnd.n = 0;
   874    882     n = ((int)sEnd.z) - (int)pBegin->z;
   875    883     z = pBegin->z;
   876    884     while( n>0 && (z[n-1]==';' || isspace(z[n-1])) ){ n--; }
   877    885     sEnd.z = &z[n-1];
   878    886     sEnd.n = 1;
   879         -  z = p->pSelect->zSelect = sqliteStrNDup(z, n);
   880         -  if( z ){
   881         -    offset = ((int)z) - (int)pBegin->z;
   882         -    sqliteSelectMoveStrings(p->pSelect, offset);
          887  +
          888  +  /* Use sqliteEndTable() to add the view to the SQLITE_MASTER table */
   883    889       sqliteEndTable(pParse, &sEnd, 0);
   884         -  }
   885    890     return;
   886    891   }
   887    892   
   888    893   /*
   889    894   ** The Table structure pTable is really a VIEW.  Fill in the names of
   890    895   ** the columns of the view in the pTable structure.  Return the number
   891    896   ** of errors.  If an error is seen leave an error message in pPare->zErrMsg.

Changes to src/expr.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains routines used for analyzing expressions and
    13     13   ** for generating VDBE code that evaluates expressions in SQLite.
    14     14   **
    15         -** $Id: expr.c,v 1.79 2002/07/18 00:34:12 drh Exp $
           15  +** $Id: expr.c,v 1.80 2002/08/24 18:24:54 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   #include <ctype.h>
    19     19   
    20     20   /*
    21     21   ** Construct a new expression node and return a pointer to it.  Memory
    22     22   ** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
    30     30       sqliteExprDelete(pRight);
    31     31       return 0;
    32     32     }
    33     33     pNew->op = op;
    34     34     pNew->pLeft = pLeft;
    35     35     pNew->pRight = pRight;
    36     36     if( pToken ){
           37  +    assert( pToken->dyn==0 );
    37     38       pNew->token = *pToken;
           39  +    pNew->token.base = 1;
           40  +  }else if( pLeft && pRight ){
           41  +    sqliteExprSpan(pNew, &pLeft->token, &pRight->token);
    38     42     }else{
           43  +    pNew->token.dyn = 0;
           44  +    pNew->token.base = 1;
    39     45       pNew->token.z = 0;
    40     46       pNew->token.n = 0;
    41     47     }
    42         -  if( pLeft && pRight ){
    43         -    sqliteExprSpan(pNew, &pLeft->span, &pRight->span);
    44         -  }else{
    45         -    pNew->span = pNew->token;
    46         -  }
    47     48     return pNew;
    48     49   }
    49     50   
    50     51   /*
    51     52   ** Set the Expr.token field of the given expression to span all
    52     53   ** text between the two given tokens.
    53     54   */
    54     55   void sqliteExprSpan(Expr *pExpr, Token *pLeft, Token *pRight){
    55     56     if( pExpr ){
    56         -    pExpr->span.z = pLeft->z;
    57         -    pExpr->span.n = pRight->n + Addr(pRight->z) - Addr(pLeft->z);
           57  +    assert( pExpr->token.dyn==0 );
           58  +    if( pLeft->dyn==0 && pRight->dyn==0 ){
           59  +      pExpr->token.z = pLeft->z;
           60  +      pExpr->token.n = pRight->n + Addr(pRight->z) - Addr(pLeft->z);
           61  +      pExpr->token.base = 0;
           62  +    }else{
           63  +      pExpr->token.z = 0;
           64  +      pExpr->token.n = 0;
           65  +      pExpr->token.dyn = 0;
           66  +      pExpr->token.base = 0;
           67  +    }
    58     68     }
    59     69   }
    60     70   
    61     71   /*
    62     72   ** Construct a new expression node for a function with multiple
    63     73   ** arguments.
    64     74   */
................................................................................
    67     77     pNew = sqliteMalloc( sizeof(Expr) );
    68     78     if( pNew==0 ){
    69     79       sqliteExprListDelete(pList);
    70     80       return 0;
    71     81     }
    72     82     pNew->op = TK_FUNCTION;
    73     83     pNew->pList = pList;
           84  +
           85  +  /* Expr.token.n is the length of the entire function
           86  +  ** call, including the function arguments.  The parser
           87  +  ** will extend token.n to cover the either length of the string.
           88  +  ** Expr.nFuncName is the length of just the function name.
           89  +  */
           90  +  pNew->token.dyn = 0;
           91  +  pNew->token.base = 1;
    74     92     if( pToken ){
           93  +    assert( pToken->dyn==0 );
    75     94       pNew->token = *pToken;
           95  +    pNew->nFuncName = pToken->n>255 ? 255 : pToken->n;
    76     96     }else{
    77     97       pNew->token.z = 0;
    78     98       pNew->token.n = 0;
    79     99     }
    80    100     return pNew;
    81    101   }
    82    102   
    83    103   /*
    84    104   ** Recursively delete an expression tree.
    85    105   */
    86    106   void sqliteExprDelete(Expr *p){
    87    107     if( p==0 ) return;
          108  +  if( p->token.dyn && p->token.z ) sqliteFree((char*)p->token.z);
    88    109     if( p->pLeft ) sqliteExprDelete(p->pLeft);
    89    110     if( p->pRight ) sqliteExprDelete(p->pRight);
    90    111     if( p->pList ) sqliteExprListDelete(p->pList);
    91    112     if( p->pSelect ) sqliteSelectDelete(p->pSelect);
    92    113     sqliteFree(p);
    93    114   }
    94    115   
    95         -/*
    96         -** The following group of functions are used to translate the string
    97         -** pointers of tokens in expression from one buffer to another.
    98         -**
    99         -** Normally, the Expr.token.z and Expr.span.z fields point into the
   100         -** original input buffer of an SQL statement.  This is usually OK
   101         -** since the SQL statement is executed and the expression is deleted
   102         -** before the input buffer is freed.  Making the tokens point to the
   103         -** original input buffer saves many calls to malloc() and thus helps
   104         -** the library to run faster. 
   105         -**
   106         -** But sometimes we need an expression to persist past the time when
   107         -** the input buffer is freed.  (Example: The SELECT clause of a
   108         -** CREATE VIEW statement contains expressions that must persist for
   109         -** the life of the view.)  When that happens we have to make a
   110         -** persistent copy of the input buffer and translate the Expr.token.z
   111         -** and Expr.span.z fields to point to the copy rather than the 
   112         -** original input buffer.  The following group of routines handle that
   113         -** translation.
   114         -**
   115         -** The "offset" parameter is the distance from the original input buffer
   116         -** to the persistent copy.  These routines recursively walk the entire
   117         -** expression tree and shift all tokens by "offset" amount.
   118         -**
   119         -** The work of figuring out the appropriate "offset" and making the
   120         -** presistent copy of the input buffer is done by the calling routine.
   121         -*/
   122         -void sqliteExprMoveStrings(Expr *p, int offset){
   123         -  if( p==0 ) return;
   124         -  if( !p->staticToken ){
   125         -    if( p->token.z ) p->token.z += offset;
   126         -    if( p->span.z ) p->span.z += offset;
   127         -  }
   128         -  if( p->pLeft ) sqliteExprMoveStrings(p->pLeft, offset);
   129         -  if( p->pRight ) sqliteExprMoveStrings(p->pRight, offset);
   130         -  if( p->pList ) sqliteExprListMoveStrings(p->pList, offset);
   131         -  if( p->pSelect ) sqliteSelectMoveStrings(p->pSelect, offset);
   132         -}
   133         -void sqliteExprListMoveStrings(ExprList *pList, int offset){
   134         -  int i;
   135         -  if( pList==0 ) return;
   136         -  for(i=0; i<pList->nExpr; i++){
   137         -    sqliteExprMoveStrings(pList->a[i].pExpr, offset);
   138         -  }
   139         -}
   140         -static void sqliteSrcListMoveStrings(SrcList *pSrc, int offset){
   141         -  int i;
   142         -  if( pSrc==0 ) return;
   143         -  for(i=0; i<pSrc->nSrc; i++){
   144         -    sqliteSelectMoveStrings(pSrc->a[i].pSelect, offset);
   145         -    sqliteExprMoveStrings(pSrc->a[i].pOn, offset);
   146         -  }
   147         -}
   148         -void sqliteSelectMoveStrings(Select *pSelect, int offset){
   149         -  if( pSelect==0 ) return;
   150         -  sqliteExprListMoveStrings(pSelect->pEList, offset);
   151         -  sqliteSrcListMoveStrings(pSelect->pSrc, offset);
   152         -  sqliteExprMoveStrings(pSelect->pWhere, offset);
   153         -  sqliteExprListMoveStrings(pSelect->pGroupBy, offset);
   154         -  sqliteExprMoveStrings(pSelect->pHaving, offset);
   155         -  sqliteExprListMoveStrings(pSelect->pOrderBy, offset);
   156         -  sqliteSelectMoveStrings(pSelect->pPrior, offset);
   157         -}
   158    116   
   159    117   /*
   160    118   ** The following group of routines make deep copies of expressions,
   161    119   ** expression lists, ID lists, and select statements.  The copies can
   162    120   ** be deleted (by being passed to their respective ...Delete() routines)
   163    121   ** without effecting the originals.
   164    122   **
   165         -** Note, however, that the Expr.token.z and Expr.span.z fields point to
   166         -** string space that is allocated separately from the expression tree
   167         -** itself.  These routines do NOT duplicate that string space.
   168         -**
   169    123   ** The expression list, ID, and source lists return by sqliteExprListDup(),
   170    124   ** sqliteIdListDup(), and sqliteSrcListDup() can not be further expanded 
   171    125   ** by subsequent calls to sqlite*ListAppend() routines.
   172    126   **
   173    127   ** Any tables that the SrcList might point to are not duplicated.
   174    128   */
   175    129   Expr *sqliteExprDup(Expr *p){
   176    130     Expr *pNew;
   177    131     if( p==0 ) return 0;
   178    132     pNew = sqliteMalloc( sizeof(*p) );
   179    133     if( pNew==0 ) return 0;
   180    134     memcpy(pNew, p, sizeof(*pNew));
          135  +  /* Only make a copy of the token if it is a base token (meaning that
          136  +  ** it covers a single term of an expression - not two or more terms)
          137  +  ** or if it is already dynamically allocated.  So, for example, in
          138  +  ** a complex expression like "a+b+c", the token "b" would be duplicated
          139  +  ** but "a+b" would not be. */
          140  +  if( p->token.z!=0 && (p->token.base || p->token.dyn) ){
          141  +    pNew->token.z = sqliteStrDup(p->token.z);
          142  +    pNew->token.dyn = 1;
          143  +  }else{
          144  +    pNew->token.z = 0;
          145  +    pNew->token.n = 0;
          146  +    pNew->token.dyn = 0;
          147  +  }
   181    148     pNew->pLeft = sqliteExprDup(p->pLeft);
   182    149     pNew->pRight = sqliteExprDup(p->pRight);
   183    150     pNew->pList = sqliteExprListDup(p->pList);
   184    151     pNew->pSelect = sqliteSelectDup(p->pSelect);
   185    152     return pNew;
          153  +}
          154  +void sqliteTokenCopy(Token *pTo, Token *pFrom){
          155  +  if( pTo->dyn ) sqliteFree((char*)pTo->z);
          156  +  pTo->base = pFrom->base;
          157  +  if( pFrom->z ){
          158  +    pTo->n = pFrom->n;
          159  +    pTo->z = sqliteStrNDup(pFrom->z, pFrom->n);
          160  +    pTo->dyn = 1;
          161  +  }else{
          162  +    pTo->n = 0;
          163  +    pTo->z = 0;
          164  +    pTo->dyn = 0;
          165  +  }
   186    166   }
   187    167   ExprList *sqliteExprListDup(ExprList *p){
   188    168     ExprList *pNew;
   189    169     int i;
   190    170     if( p==0 ) return 0;
   191    171     pNew = sqliteMalloc( sizeof(*pNew) );
   192    172     if( pNew==0 ) return 0;
   193    173     pNew->nExpr = p->nExpr;
   194    174     pNew->a = sqliteMalloc( p->nExpr*sizeof(p->a[0]) );
   195    175     if( pNew->a==0 ) return 0;
   196    176     for(i=0; i<p->nExpr; i++){
   197         -    pNew->a[i].pExpr = sqliteExprDup(p->a[i].pExpr);
          177  +    Expr *pNewExpr, *pOldExpr;
          178  +    pNew->a[i].pExpr = pNewExpr = sqliteExprDup(pOldExpr = p->a[i].pExpr);
          179  +    if( pOldExpr->token.z!=0 && pNewExpr && pNewExpr->token.z==0 ){
          180  +      /* Always make a copy of the token for top-level expressions in the
          181  +      ** expression list.  The logic in SELECT processing that determines
          182  +      ** the names of columns in the result set needs this information */
          183  +      sqliteTokenCopy(&pNew->a[i].pExpr->token, &p->a[i].pExpr->token);
          184  +    }
   198    185       pNew->a[i].zName = sqliteStrDup(p->a[i].zName);
   199    186       pNew->a[i].sortOrder = p->a[i].sortOrder;
   200    187       pNew->a[i].isAgg = p->a[i].isAgg;
   201    188       pNew->a[i].done = 0;
   202    189     }
   203    190     return pNew;
   204    191   }
................................................................................
   359    346         while( n>0 && *z && isdigit(*z) ){ z++; n--; }
   360    347         if( n==0 ){
   361    348           *pValue = atoi(p->token.z);
   362    349           return 1;
   363    350         }
   364    351         break;
   365    352       }
          353  +    case TK_UPLUS: {
          354  +      return sqliteExprIsInteger(p->pLeft, pValue);
          355  +    }
   366    356       case TK_UMINUS: {
   367    357         int v;
   368    358         if( sqliteExprIsInteger(p->pLeft, &v) ){
   369    359           *pValue = -v;
   370    360           return 1;
   371    361         }
   372    362         break;
................................................................................
   707    697             }
   708    698           }
   709    699         }
   710    700       }
   711    701     }
   712    702     return 0;
   713    703   }
          704  +
          705  +/*
          706  +** pExpr is a node that defines a function of some kind.  It might
          707  +** be a syntactic function like "count(x)" or it might be a function
          708  +** that implements an operator, like "a LIKE b".  
          709  +**
          710  +** This routine makes *pzName point to the name of the function and 
          711  +** *pnName hold the number of characters in the function name.
          712  +*/
          713  +static void getFunctionName(Expr *pExpr, const char **pzName, int *pnName){
          714  +  switch( pExpr->op ){
          715  +    case TK_FUNCTION: {
          716  +      *pzName = pExpr->token.z;
          717  +      *pnName = pExpr->nFuncName;
          718  +      break;
          719  +    }
          720  +    case TK_LIKE: {
          721  +      *pzName = "like";
          722  +      *pnName = 4;
          723  +      break;
          724  +    }
          725  +    case TK_GLOB: {
          726  +      *pzName = "glob";
          727  +      *pnName = 4;
          728  +      break;
          729  +    }
          730  +    default: {
          731  +      *pzName = "can't happen";
          732  +      *pnName = 12;
          733  +      break;
          734  +    }
          735  +  }
          736  +}
   714    737   
   715    738   /*
   716    739   ** Error check the functions in an expression.  Make sure all
   717    740   ** function names are recognized and all functions have the correct
   718    741   ** number of arguments.  Leave an error message in pParse->zErrMsg
   719    742   ** if anything is amiss.  Return the number of errors.
   720    743   **
................................................................................
   721    744   ** if pIsAgg is not null and this expression is an aggregate function
   722    745   ** (like count(*) or max(value)) then write a 1 into *pIsAgg.
   723    746   */
   724    747   int sqliteExprCheck(Parse *pParse, Expr *pExpr, int allowAgg, int *pIsAgg){
   725    748     int nErr = 0;
   726    749     if( pExpr==0 ) return 0;
   727    750     switch( pExpr->op ){
          751  +    case TK_GLOB:
          752  +    case TK_LIKE:
   728    753       case TK_FUNCTION: {
   729    754         int n = pExpr->pList ? pExpr->pList->nExpr : 0;  /* Number of arguments */
   730    755         int no_such_func = 0;       /* True if no such function exists */
   731    756         int is_type_of = 0;         /* True if is the special TypeOf() function */
   732    757         int wrong_num_args = 0;     /* True if wrong number of arguments */
   733    758         int is_agg = 0;             /* True if is an aggregate function */
   734    759         int i;
          760  +      int nId;                    /* Number of characters in function name */
          761  +      const char *zId;            /* The function name. */
   735    762         FuncDef *pDef;
   736    763   
   737         -      pDef = sqliteFindFunction(pParse->db,
   738         -         pExpr->token.z, pExpr->token.n, n, 0);
          764  +      getFunctionName(pExpr, &zId, &nId);
          765  +      pDef = sqliteFindFunction(pParse->db, zId, nId, n, 0);
   739    766         if( pDef==0 ){
   740         -        pDef = sqliteFindFunction(pParse->db,
   741         -           pExpr->token.z, pExpr->token.n, -1, 0);
          767  +        pDef = sqliteFindFunction(pParse->db, zId, nId, -1, 0);
   742    768           if( pDef==0 ){
   743         -          if( n==1 && pExpr->token.n==6
   744         -               && sqliteStrNICmp(pExpr->token.z, "typeof", 6)==0 ){
          769  +          if( n==1 && nId==6 && sqliteStrNICmp(zId, "typeof", 6)==0 ){
   745    770               is_type_of = 1;
   746    771             }else {
   747    772               no_such_func = 1;
   748    773             }
   749    774           }else{
   750    775             wrong_num_args = 1;
   751    776           }
   752    777         }else{
   753    778           is_agg = pDef->xFunc==0;
   754    779         }
   755    780         if( is_agg && !allowAgg ){
   756    781           sqliteSetNString(&pParse->zErrMsg, "misuse of aggregate function ", -1,
   757         -           pExpr->token.z, pExpr->token.n, "()", 2, 0);
          782  +           zId, nId, "()", 2, 0);
   758    783           pParse->nErr++;
   759    784           nErr++;
   760    785           is_agg = 0;
   761    786         }else if( no_such_func ){
   762         -        sqliteSetNString(&pParse->zErrMsg, "no such function: ", -1,
   763         -           pExpr->token.z, pExpr->token.n, 0);
          787  +        sqliteSetNString(&pParse->zErrMsg, "no such function: ", -1, zId,nId,0);
   764    788           pParse->nErr++;
   765    789           nErr++;
   766    790         }else if( wrong_num_args ){
   767    791           sqliteSetNString(&pParse->zErrMsg, 
   768         -           "wrong number of arguments to function ",-1,
   769         -           pExpr->token.z, pExpr->token.n, "()", 2, 0);
          792  +           "wrong number of arguments to function ", -1, zId, nId, "()", 2, 0);
   770    793           pParse->nErr++;
   771    794           nErr++;
   772    795         }
   773    796         if( is_agg ) pExpr->op = TK_AGG_FUNCTION;
   774    797         if( is_agg && pIsAgg ) *pIsAgg = 1;
   775    798         for(i=0; nErr==0 && i<n; i++){
   776    799           nErr = sqliteExprCheck(pParse, pExpr->pList->a[i].pExpr,
................................................................................
   845    868       case TK_SLASH:
   846    869       case TK_AND:
   847    870       case TK_OR:
   848    871       case TK_ISNULL:
   849    872       case TK_NOTNULL:
   850    873       case TK_NOT:
   851    874       case TK_UMINUS:
          875  +    case TK_UPLUS:
   852    876       case TK_BITAND:
   853    877       case TK_BITOR:
   854    878       case TK_BITNOT:
   855    879       case TK_LSHIFT:
   856    880       case TK_RSHIFT:
   857    881       case TK_REM:
   858    882       case TK_INTEGER:
   859    883       case TK_FLOAT:
   860    884       case TK_IN:
   861    885       case TK_BETWEEN:
          886  +    case TK_GLOB:
          887  +    case TK_LIKE:
   862    888         return SQLITE_SO_NUM;
   863    889   
   864    890       case TK_STRING:
   865    891       case TK_NULL:
   866    892       case TK_CONCAT:
   867    893         return SQLITE_SO_TEXT;
   868    894   
................................................................................
  1026   1052         break;
  1027   1053       }
  1028   1054       case TK_CONCAT: {
  1029   1055         sqliteExprCode(pParse, pExpr->pLeft);
  1030   1056         sqliteExprCode(pParse, pExpr->pRight);
  1031   1057         sqliteVdbeAddOp(v, OP_Concat, 2, 0);
  1032   1058         break;
         1059  +    }
         1060  +    case TK_UPLUS: {
         1061  +      Expr *pLeft = pExpr->pLeft;
         1062  +      if( pLeft && pLeft->op==TK_INTEGER ){
         1063  +        sqliteVdbeAddOp(v, OP_Integer, atoi(pLeft->token.z), 0);
         1064  +        sqliteVdbeChangeP3(v, -1, pLeft->token.z, pLeft->token.n);
         1065  +      }else if( pLeft && pLeft->op==TK_FLOAT ){
         1066  +        sqliteVdbeAddOp(v, OP_String, 0, 0);
         1067  +        sqliteVdbeChangeP3(v, -1, pLeft->token.z, pLeft->token.n);
         1068  +      }else{
         1069  +        sqliteExprCode(pParse, pExpr->pLeft);
         1070  +      }
         1071  +      break;
  1033   1072       }
  1034   1073       case TK_UMINUS: {
  1035   1074         assert( pExpr->pLeft );
  1036   1075         if( pExpr->pLeft->op==TK_FLOAT || pExpr->pLeft->op==TK_INTEGER ){
  1037   1076           Token *p = &pExpr->pLeft->token;
  1038   1077           char *z = sqliteMalloc( p->n + 2 );
  1039   1078           sprintf(z, "-%.*s", p->n, p->z);
................................................................................
  1064   1103         sqliteVdbeAddOp(v, OP_AddImm, -1, 0);
  1065   1104         break;
  1066   1105       }
  1067   1106       case TK_AGG_FUNCTION: {
  1068   1107         sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg);
  1069   1108         break;
  1070   1109       }
         1110  +    case TK_GLOB:
         1111  +    case TK_LIKE:
  1071   1112       case TK_FUNCTION: {
  1072   1113         int i;
  1073   1114         ExprList *pList = pExpr->pList;
  1074   1115         int nExpr = pList ? pList->nExpr : 0;
  1075   1116         FuncDef *pDef;
  1076         -      pDef = sqliteFindFunction(pParse->db,
  1077         -                      pExpr->token.z, pExpr->token.n, nExpr, 0);
         1117  +      int nId;
         1118  +      const char *zId;
         1119  +      getFunctionName(pExpr, &zId, &nId);
         1120  +      pDef = sqliteFindFunction(pParse->db, zId, nId, nExpr, 0);
  1078   1121         assert( pDef!=0 );
  1079   1122         for(i=0; i<nExpr; i++){
  1080   1123           sqliteExprCode(pParse, pList->a[i].pExpr);
  1081   1124         }
  1082   1125         sqliteVdbeAddOp(v, OP_Function, nExpr, 0);
  1083   1126         sqliteVdbeChangeP3(v, -1, (char*)pDef, P3_POINTER);
  1084   1127         break;
................................................................................
  1398   1441       }
  1399   1442     }else if( pB->pList ){
  1400   1443       return 0;
  1401   1444     }
  1402   1445     if( pA->pSelect || pB->pSelect ) return 0;
  1403   1446     if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 0;
  1404   1447     if( pA->token.z ){
         1448  +    int n;
  1405   1449       if( pB->token.z==0 ) return 0;
  1406         -    if( pB->token.n!=pA->token.n ) return 0;
  1407         -    if( sqliteStrNICmp(pA->token.z, pB->token.z, pA->token.n)!=0 ) return 0;
         1450  +    if( pA->op==TK_FUNCTION || pA->op==TK_AGG_FUNCTION ){
         1451  +      n = pA->nFuncName;
         1452  +      if( pB->nFuncName!=n ) return 0;
         1453  +    }else{
         1454  +      n = pA->token.n;
         1455  +      if( pB->token.n!=n ) return 0;
         1456  +    }
         1457  +    if( sqliteStrNICmp(pA->token.z, pB->token.z, n)!=0 ) return 0;
  1408   1458     }
  1409   1459     return 1;
  1410   1460   }
  1411   1461   
  1412   1462   /*
  1413   1463   ** Add a new element to the pParse->aAgg[] array and return its index.
  1414   1464   */
................................................................................
  1471   1521         }
  1472   1522         if( i>=pParse->nAgg ){
  1473   1523           i = appendAggInfo(pParse);
  1474   1524           if( i<0 ) return 1;
  1475   1525           pParse->aAgg[i].isAgg = 1;
  1476   1526           pParse->aAgg[i].pExpr = pExpr;
  1477   1527           pParse->aAgg[i].pFunc = sqliteFindFunction(pParse->db,
  1478         -             pExpr->token.z, pExpr->token.n,
         1528  +             pExpr->token.z, pExpr->nFuncName,
  1479   1529                pExpr->pList ? pExpr->pList->nExpr : 0, 0);
  1480   1530         }
  1481   1531         pExpr->iAgg = i;
  1482   1532         break;
  1483   1533       }
  1484   1534       default: {
  1485   1535         if( pExpr->pLeft ){

Changes to src/main.c.

    10     10   **
    11     11   *************************************************************************
    12     12   ** Main file for the SQLite library.  The routines in this file
    13     13   ** implement the programmer interface to the library.  Routines in
    14     14   ** other files are for internal use by SQLite and should not be
    15     15   ** accessed by users of the library.
    16     16   **
    17         -** $Id: main.c,v 1.97 2002/08/13 23:02:57 drh Exp $
           17  +** $Id: main.c,v 1.98 2002/08/24 18:24:54 drh Exp $
    18     18   */
    19     19   #include "sqliteInt.h"
    20     20   #include "os.h"
    21     21   #include <ctype.h>
    22     22   
    23     23   /*
    24     24   ** A pointer to this structure is used to communicate information
................................................................................
   791    791     sqlite *db,          /* Add the function to this database connection */
   792    792     const char *zName,   /* Name of the function to add */
   793    793     int nArg,            /* Number of arguments */
   794    794     void (*xFunc)(sqlite_func*,int,const char**),  /* The implementation */
   795    795     void *pUserData      /* User data */
   796    796   ){
   797    797     FuncDef *p;
          798  +  int nName;
   798    799     if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1;
   799         -  p = sqliteFindFunction(db, zName, strlen(zName), nArg, 1);
          800  +  nName = strlen(zName);
          801  +  if( nName>255 ) return 1;
          802  +  p = sqliteFindFunction(db, zName, nName, nArg, 1);
   800    803     if( p==0 ) return 1;
   801    804     p->xFunc = xFunc;
   802    805     p->xStep = 0;
   803    806     p->xFinalize = 0;
   804    807     p->pUserData = pUserData;
   805    808     return 0;
   806    809   }
................................................................................
   809    812     const char *zName,   /* Name of the function to add */
   810    813     int nArg,            /* Number of arguments */
   811    814     void (*xStep)(sqlite_func*,int,const char**), /* The step function */
   812    815     void (*xFinalize)(sqlite_func*),              /* The finalizer */
   813    816     void *pUserData      /* User data */
   814    817   ){
   815    818     FuncDef *p;
          819  +  int nName;
   816    820     if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1;
   817         -  p = sqliteFindFunction(db, zName, strlen(zName), nArg, 1);
          821  +  nName = strlen(zName);
          822  +  if( nName>255 ) return 1;
          823  +  p = sqliteFindFunction(db, zName, nName, nArg, 1);
   818    824     if( p==0 ) return 1;
   819    825     p->xFunc = 0;
   820    826     p->xStep = xStep;
   821    827     p->xFinalize = xFinalize;
   822    828     p->pUserData = pUserData;
   823    829     return 0;
   824    830   }

Changes to src/parse.y.

    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains SQLite's grammar for SQL.  Process this file
    13     13   ** using the lemon parser generator to generate C code that runs
    14     14   ** the parser.  Lemon will also generate a header file containing
    15     15   ** numeric codes for all of the tokens.
    16     16   **
    17         -** @(#) $Id: parse.y,v 1.81 2002/08/18 22:41:22 drh Exp $
           17  +** @(#) $Id: parse.y,v 1.82 2002/08/24 18:24:54 drh Exp $
    18     18   */
    19     19   %token_prefix TK_
    20     20   %token_type {Token}
    21     21   %default_type {Token}
    22     22   %extra_argument {Parse *pParse}
    23     23   %syntax_error {
    24     24     sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
   482    482   %right NOT.
   483    483   %left EQ NE ISNULL NOTNULL IS LIKE GLOB BETWEEN IN.
   484    484   %left GT GE LT LE.
   485    485   %left BITAND BITOR LSHIFT RSHIFT.
   486    486   %left PLUS MINUS.
   487    487   %left STAR SLASH REM.
   488    488   %left CONCAT.
   489         -%right UMINUS BITNOT.
          489  +%right UMINUS UPLUS BITNOT.
   490    490   
   491    491   %type expr {Expr*}
   492    492   %destructor expr {sqliteExprDelete($$);}
   493    493   
   494    494   expr(A) ::= LP(B) expr(X) RP(E). {A = X; sqliteExprSpan(A,&B,&E);}
   495    495   expr(A) ::= NULL(X).             {A = sqliteExpr(TK_NULL, 0, 0, &X);}
   496    496   expr(A) ::= ID(X).               {A = sqliteExpr(TK_ID, 0, 0, &X);}
................................................................................
   502    502   }
   503    503   expr(A) ::= INTEGER(X).      {A = sqliteExpr(TK_INTEGER, 0, 0, &X);}
   504    504   expr(A) ::= FLOAT(X).        {A = sqliteExpr(TK_FLOAT, 0, 0, &X);}
   505    505   expr(A) ::= STRING(X).       {A = sqliteExpr(TK_STRING, 0, 0, &X);}
   506    506   expr(A) ::= ID(X) LP exprlist(Y) RP(E). {
   507    507     A = sqliteExprFunction(Y, &X);
   508    508     sqliteExprSpan(A,&X,&E);
          509  +  if( A ) A->token.base = 1;
   509    510   }
   510    511   expr(A) ::= ID(X) LP STAR RP(E). {
   511    512     A = sqliteExprFunction(0, &X);
   512    513     sqliteExprSpan(A,&X,&E);
          514  +  if( A ) A->token.base = 1;
   513    515   }
   514    516   expr(A) ::= expr(X) AND expr(Y).   {A = sqliteExpr(TK_AND, X, Y, 0);}
   515    517   expr(A) ::= expr(X) OR expr(Y).    {A = sqliteExpr(TK_OR, X, Y, 0);}
   516    518   expr(A) ::= expr(X) LT expr(Y).    {A = sqliteExpr(TK_LT, X, Y, 0);}
   517    519   expr(A) ::= expr(X) GT expr(Y).    {A = sqliteExpr(TK_GT, X, Y, 0);}
   518    520   expr(A) ::= expr(X) LE expr(Y).    {A = sqliteExpr(TK_LE, X, Y, 0);}
   519    521   expr(A) ::= expr(X) GE expr(Y).    {A = sqliteExpr(TK_GE, X, Y, 0);}
................................................................................
   522    524   expr(A) ::= expr(X) BITAND expr(Y). {A = sqliteExpr(TK_BITAND, X, Y, 0);}
   523    525   expr(A) ::= expr(X) BITOR expr(Y).  {A = sqliteExpr(TK_BITOR, X, Y, 0);}
   524    526   expr(A) ::= expr(X) LSHIFT expr(Y). {A = sqliteExpr(TK_LSHIFT, X, Y, 0);}
   525    527   expr(A) ::= expr(X) RSHIFT expr(Y). {A = sqliteExpr(TK_RSHIFT, X, Y, 0);}
   526    528   expr(A) ::= expr(X) likeop(OP) expr(Y).  [LIKE]  {
   527    529     ExprList *pList = sqliteExprListAppend(0, Y, 0);
   528    530     pList = sqliteExprListAppend(pList, X, 0);
   529         -  A = sqliteExprFunction(pList, &OP);
   530         -  sqliteExprSpan(A, &X->span, &Y->span);
          531  +  A = sqliteExprFunction(pList, 0);
          532  +  if( A ) A->op = OP;
          533  +  sqliteExprSpan(A, &X->token, &Y->token);
   531    534   }
   532    535   expr(A) ::= expr(X) NOT likeop(OP) expr(Y). [LIKE] {
   533    536     ExprList *pList = sqliteExprListAppend(0, Y, 0);
   534    537     pList = sqliteExprListAppend(pList, X, 0);
   535         -  A = sqliteExprFunction(pList, &OP);
          538  +  A = sqliteExprFunction(pList, 0);
          539  +  if( A ) A->op = OP;
   536    540     A = sqliteExpr(TK_NOT, A, 0, 0);
   537         -  sqliteExprSpan(A,&X->span,&Y->span);
          541  +  sqliteExprSpan(A,&X->token,&Y->token);
   538    542   }
   539         -likeop(A) ::= LIKE(X). {A = X;}
   540         -likeop(A) ::= GLOB(X). {A = X;}
          543  +%type likeop {int}
          544  +likeop(A) ::= LIKE. {A = TK_LIKE;}
          545  +likeop(A) ::= GLOB. {A = TK_GLOB;}
   541    546   expr(A) ::= expr(X) PLUS expr(Y).  {A = sqliteExpr(TK_PLUS, X, Y, 0);}
   542    547   expr(A) ::= expr(X) MINUS expr(Y). {A = sqliteExpr(TK_MINUS, X, Y, 0);}
   543    548   expr(A) ::= expr(X) STAR expr(Y).  {A = sqliteExpr(TK_STAR, X, Y, 0);}
   544    549   expr(A) ::= expr(X) SLASH expr(Y). {A = sqliteExpr(TK_SLASH, X, Y, 0);}
   545    550   expr(A) ::= expr(X) REM expr(Y).   {A = sqliteExpr(TK_REM, X, Y, 0);}
   546    551   expr(A) ::= expr(X) CONCAT expr(Y). {A = sqliteExpr(TK_CONCAT, X, Y, 0);}
   547    552   expr(A) ::= expr(X) ISNULL(E). {
   548    553     A = sqliteExpr(TK_ISNULL, X, 0, 0);
   549         -  sqliteExprSpan(A,&X->span,&E);
          554  +  sqliteExprSpan(A,&X->token,&E);
   550    555   }
   551    556   expr(A) ::= expr(X) IS NULL(E). {
   552    557     A = sqliteExpr(TK_ISNULL, X, 0, 0);
   553         -  sqliteExprSpan(A,&X->span,&E);
          558  +  sqliteExprSpan(A,&X->token,&E);
   554    559   }
   555    560   expr(A) ::= expr(X) NOTNULL(E). {
   556    561     A = sqliteExpr(TK_NOTNULL, X, 0, 0);
   557         -  sqliteExprSpan(A,&X->span,&E);
          562  +  sqliteExprSpan(A,&X->token,&E);
   558    563   }
   559    564   expr(A) ::= expr(X) NOT NULL(E). {
   560    565     A = sqliteExpr(TK_NOTNULL, X, 0, 0);
   561         -  sqliteExprSpan(A,&X->span,&E);
          566  +  sqliteExprSpan(A,&X->token,&E);
   562    567   }
   563    568   expr(A) ::= expr(X) IS NOT NULL(E). {
   564    569     A = sqliteExpr(TK_NOTNULL, X, 0, 0);
   565         -  sqliteExprSpan(A,&X->span,&E);
          570  +  sqliteExprSpan(A,&X->token,&E);
   566    571   }
   567    572   expr(A) ::= NOT(B) expr(X). {
   568    573     A = sqliteExpr(TK_NOT, X, 0, 0);
   569         -  sqliteExprSpan(A,&B,&X->span);
          574  +  sqliteExprSpan(A,&B,&X->token);
   570    575   }
   571    576   expr(A) ::= BITNOT(B) expr(X). {
   572    577     A = sqliteExpr(TK_BITNOT, X, 0, 0);
   573         -  sqliteExprSpan(A,&B,&X->span);
          578  +  sqliteExprSpan(A,&B,&X->token);
   574    579   }
   575    580   expr(A) ::= MINUS(B) expr(X). [UMINUS] {
   576    581     A = sqliteExpr(TK_UMINUS, X, 0, 0);
   577         -  sqliteExprSpan(A,&B,&X->span);
          582  +  sqliteExprSpan(A,&B,&X->token);
   578    583   }
   579         -expr(A) ::= PLUS(B) expr(X). [UMINUS] {
   580         -  A = X;
   581         -  sqliteExprSpan(A,&B,&X->span);
          584  +expr(A) ::= PLUS(B) expr(X). [UPLUS] {
          585  +  A = sqliteExpr(TK_UPLUS, X, 0, 0);
          586  +  sqliteExprSpan(A,&B,&X->token);
   582    587   }
   583    588   expr(A) ::= LP(B) select(X) RP(E). {
   584    589     A = sqliteExpr(TK_SELECT, 0, 0, 0);
   585    590     if( A ) A->pSelect = X;
   586    591     sqliteExprSpan(A,&B,&E);
   587    592   }
   588    593   expr(A) ::= expr(W) BETWEEN expr(X) AND expr(Y). {
   589    594     ExprList *pList = sqliteExprListAppend(0, X, 0);
   590    595     pList = sqliteExprListAppend(pList, Y, 0);
   591    596     A = sqliteExpr(TK_BETWEEN, W, 0, 0);
   592    597     if( A ) A->pList = pList;
   593         -  sqliteExprSpan(A,&W->span,&Y->span);
          598  +  sqliteExprSpan(A,&W->token,&Y->token);
   594    599   }
   595    600   expr(A) ::= expr(W) NOT BETWEEN expr(X) AND expr(Y). {
   596    601     ExprList *pList = sqliteExprListAppend(0, X, 0);
   597    602     pList = sqliteExprListAppend(pList, Y, 0);
   598    603     A = sqliteExpr(TK_BETWEEN, W, 0, 0);
   599    604     if( A ) A->pList = pList;
   600    605     A = sqliteExpr(TK_NOT, A, 0, 0);
   601         -  sqliteExprSpan(A,&W->span,&Y->span);
          606  +  sqliteExprSpan(A,&W->token,&Y->token);
   602    607   }
   603    608   expr(A) ::= expr(X) IN LP exprlist(Y) RP(E).  {
   604    609     A = sqliteExpr(TK_IN, X, 0, 0);
   605    610     if( A ) A->pList = Y;
   606         -  sqliteExprSpan(A,&X->span,&E);
          611  +  sqliteExprSpan(A,&X->token,&E);
   607    612   }
   608    613   expr(A) ::= expr(X) IN LP select(Y) RP(E).  {
   609    614     A = sqliteExpr(TK_IN, X, 0, 0);
   610    615     if( A ) A->pSelect = Y;
   611         -  sqliteExprSpan(A,&X->span,&E);
          616  +  sqliteExprSpan(A,&X->token,&E);
   612    617   }
   613    618   expr(A) ::= expr(X) NOT IN LP exprlist(Y) RP(E).  {
   614    619     A = sqliteExpr(TK_IN, X, 0, 0);
   615    620     if( A ) A->pList = Y;
   616    621     A = sqliteExpr(TK_NOT, A, 0, 0);
   617         -  sqliteExprSpan(A,&X->span,&E);
          622  +  sqliteExprSpan(A,&X->token,&E);
   618    623   }
   619    624   expr(A) ::= expr(X) NOT IN LP select(Y) RP(E).  {
   620    625     A = sqliteExpr(TK_IN, X, 0, 0);
   621    626     if( A ) A->pSelect = Y;
   622    627     A = sqliteExpr(TK_NOT, A, 0, 0);
   623         -  sqliteExprSpan(A,&X->span,&E);
          628  +  sqliteExprSpan(A,&X->token,&E);
   624    629   }
   625    630   
   626    631   /* CASE expressions */
   627    632   expr(A) ::= CASE(C) case_operand(X) case_exprlist(Y) case_else(Z) END(E). {
   628    633     A = sqliteExpr(TK_CASE, X, Z, 0);
   629    634     if( A ) A->pList = Y;
   630    635     sqliteExprSpan(A, &C, &E);
................................................................................
   713    718   plus_opt ::= PLUS.
   714    719   plus_opt ::= .
   715    720   
   716    721   //////////////////////////// The CREATE TRIGGER command /////////////////////
   717    722   cmd ::= CREATE(A) TRIGGER nm(B) trigger_time(C) trigger_event(D) ON nm(E) 
   718    723                     foreach_clause(F) when_clause(G)
   719    724                     BEGIN trigger_cmd_list(S) END(Z). {
   720         -  sqliteCreateTrigger(pParse, &B, C, D.a, D.b, &E, F, G, S, 
   721         -      A.z, (int)(Z.z - A.z) + Z.n );
          725  +  Token all;
          726  +  all.z = A.z;
          727  +  all.n = (Z.z - A.z) + Z.n;
          728  +  sqliteCreateTrigger(pParse, &B, C, D.a, D.b, &E, F, G, S, &all);
   722    729   }
   723    730   
   724    731   %type trigger_time  {int}
   725    732   trigger_time(A) ::= BEFORE.      { A = TK_BEFORE; }
   726    733   trigger_time(A) ::= AFTER.       { A = TK_AFTER;  }
   727    734   trigger_time(A) ::= INSTEAD OF.  { A = TK_INSTEAD;}
   728    735   trigger_time(A) ::= .            { A = TK_BEFORE; }
................................................................................
   765    772   trigger_cmd(A) ::= DELETE FROM nm(X) where_opt(Y).
   766    773                  {A = sqliteTriggerDeleteStep(&X, Y);}
   767    774   
   768    775   // SELECT
   769    776   trigger_cmd(A) ::= select(X).  {A = sqliteTriggerSelectStep(X); }
   770    777   
   771    778   // The special RAISE expression that may occur in trigger programs
   772         -expr(A) ::= RAISE(X) LP IGNORE RP(Y).  { A = sqliteExpr(TK_RAISE, 0, 0, 0); 
   773         -    A->iColumn = OE_Ignore; sqliteExprSpan(A, &X, &Y);}
   774         -expr(A) ::= RAISE(X) LP ROLLBACK COMMA nm(Z) RP(Y).  
   775         -{ A = sqliteExpr(TK_RAISE, 0, 0, &Z); 
   776         -    A->iColumn = OE_Rollback; sqliteExprSpan(A, &X, &Y);}
   777         -expr(A) ::= RAISE(X) LP ABORT COMMA nm(Z) RP(Y).  
   778         -{ A = sqliteExpr(TK_RAISE, 0, 0, &Z); 
   779         -    A->iColumn = OE_Abort; sqliteExprSpan(A, &X, &Y);}
   780         -expr(A) ::= RAISE(X) LP FAIL COMMA nm(Z) RP(Y).  
   781         -{ A = sqliteExpr(TK_RAISE, 0, 0, &Z); 
   782         -    A->iColumn = OE_Fail; sqliteExprSpan(A, &X, &Y);}
          779  +expr(A) ::= RAISE(X) LP IGNORE RP(Y).  {
          780  +  A = sqliteExpr(TK_RAISE, 0, 0, 0); 
          781  +  A->iColumn = OE_Ignore;
          782  +  /* sqliteExprSpan(A, &X, &Y); */
          783  +}
          784  +expr(A) ::= RAISE(X) LP ROLLBACK COMMA nm(Z) RP(Y).  {
          785  +  A = sqliteExpr(TK_RAISE, 0, 0, &Z); 
          786  +  A->iColumn = OE_Rollback;
          787  +  /* sqliteExprSpan(A, &X, &Y); */
          788  +}
          789  +expr(A) ::= RAISE(X) LP ABORT COMMA nm(Z) RP(Y).  {
          790  +  A = sqliteExpr(TK_RAISE, 0, 0, &Z); 
          791  +  A->iColumn = OE_Abort;
          792  +  /* sqliteExprSpan(A, &X, &Y); */
          793  +}
          794  +expr(A) ::= RAISE(X) LP FAIL COMMA nm(Z) RP(Y).  {
          795  +  A = sqliteExpr(TK_RAISE, 0, 0, &Z); 
          796  +  A->iColumn = OE_Fail;
          797  +  /* sqliteExprSpan(A, &X, &Y); */
          798  +}
   783    799   
   784    800   ////////////////////////  DROP TRIGGER statement //////////////////////////////
   785    801   cmd ::= DROP TRIGGER nm(X). {
   786    802       sqliteDropTrigger(pParse,&X,0);
   787    803   }

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.107 2002/08/04 00:52:38 drh Exp $
           15  +** $Id: select.c,v 1.108 2002/08/24 18:24:54 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Allocate a new Select structure and return a pointer to that
    21     21   ** structure.
    22     22   */
................................................................................
   152    152     Token dummy;
   153    153     Expr *pE1a, *pE1b, *pE1c;
   154    154     Expr *pE2a, *pE2b, *pE2c;
   155    155     Expr *pE;
   156    156   
   157    157     dummy.z = zCol;
   158    158     dummy.n = strlen(zCol);
          159  +  dummy.base = 1;
          160  +  dummy.dyn = 0;
   159    161     pE1a = sqliteExpr(TK_ID, 0, 0, &dummy);
   160         -  pE1a->staticToken = 1;
   161    162     pE2a = sqliteExpr(TK_ID, 0, 0, &dummy);
   162         -  pE2a->staticToken = 1;
   163    163     dummy.z = pTab1->zName;
   164    164     dummy.n = strlen(dummy.z);
   165    165     pE1b = sqliteExpr(TK_ID, 0, 0, &dummy);
   166         -  pE1b->staticToken = 1;
   167    166     dummy.z = pTab2->zName;
   168    167     dummy.n = strlen(dummy.z);
   169    168     pE2b = sqliteExpr(TK_ID, 0, 0, &dummy);
   170         -  pE2b->staticToken = 1;
   171    169     pE1c = sqliteExpr(TK_DOT, pE1b, pE1a, 0);
   172    170     pE2c = sqliteExpr(TK_DOT, pE2b, pE2a, 0);
   173    171     pE = sqliteExpr(TK_EQ, pE1c, pE2c, 0);
   174    172     pE->isJoinExpr = 1;
   175    173     if( *ppExpr ){
   176    174       *ppExpr = sqliteExpr(TK_AND, *ppExpr, pE, 0);
   177    175     }else{
................................................................................
   639    637         if( iCol<0 ){
   640    638           zCol = "_ROWID_";
   641    639           zType = "INTEGER";
   642    640         }else{
   643    641           zCol = pTab->aCol[iCol].zName;
   644    642           zType = pTab->aCol[iCol].zType;
   645    643         }
   646         -      if( p->span.z && p->span.z[0] && !showFullNames ){
          644  +      if( p->token.z && p->token.z[0] && !showFullNames ){
   647    645           int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
   648         -        sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
          646  +        sqliteVdbeChangeP3(v, -1, p->token.z, p->token.n);
   649    647           sqliteVdbeCompressSpace(v, addr);
   650    648         }else if( pTabList->nSrc>1 || showFullNames ){
   651    649           char *zName = 0;
   652    650           char *zTab;
   653    651    
   654    652           zTab = pTabList->a[p->iTable - base].zAlias;
   655    653           if( showFullNames || zTab==0 ) zTab = pTab->zName;
................................................................................
   657    655           sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
   658    656           sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
   659    657           sqliteFree(zName);
   660    658         }else{
   661    659           sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
   662    660           sqliteVdbeChangeP3(v, -1, zCol, 0);
   663    661         }
   664         -    }else if( p->span.z && p->span.z[0] && !showFullNames ){
          662  +    }else if( p->token.z && p->token.z[0] && !showFullNames ){
   665    663         int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
   666         -      sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
          664  +      sqliteVdbeChangeP3(v, -1, p->token.z, p->token.n);
   667    665         sqliteVdbeCompressSpace(v, addr);
   668         -    }else if( p->span.z && p->span.z[0] ){
          666  +    }else if( p->token.z && p->token.z[0] ){
   669    667         int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
   670         -      sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
          668  +      sqliteVdbeChangeP3(v, -1, p->token.z, p->token.n);
   671    669         sqliteVdbeCompressSpace(v, addr);
   672    670       }else{
   673    671         char zName[30];
   674    672         assert( p->op!=TK_COLUMN || pTabList==0 );
   675    673         sprintf(zName, "column%d", i+1);
   676    674         sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
   677    675         sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
................................................................................
   726    724     pTab->nCol = pEList->nExpr;
   727    725     assert( pTab->nCol>0 );
   728    726     pTab->aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
   729    727     for(i=0; i<pTab->nCol; i++){
   730    728       Expr *p;
   731    729       if( pEList->a[i].zName ){
   732    730         pTab->aCol[i].zName = sqliteStrDup(pEList->a[i].zName);
   733         -    }else if( (p=pEList->a[i].pExpr)->span.z && p->span.z[0] ){
   734         -      sqliteSetNString(&pTab->aCol[i].zName, p->span.z, p->span.n, 0);
          731  +    }else if( (p=pEList->a[i].pExpr)->token.z && p->token.z[0] ){
          732  +      sqliteSetNString(&pTab->aCol[i].zName, p->token.z, p->token.n, 0);
   735    733       }else if( p->op==TK_DOT && p->pRight && p->pRight->token.z &&
   736    734              p->pRight->token.z[0] ){
   737    735         sqliteSetNString(&pTab->aCol[i].zName, 
   738    736              p->pRight->token.z, p->pRight->token.n, 0);
   739    737       }else{
   740    738         char zBuf[30];
   741    739         sprintf(zBuf, "column%d", i+1);
................................................................................
   891    889                 ** using clause from the table on the right. */
   892    890                 continue;
   893    891               }
   894    892               pRight = sqliteExpr(TK_ID, 0, 0, 0);
   895    893               if( pRight==0 ) break;
   896    894               pRight->token.z = zName;
   897    895               pRight->token.n = strlen(zName);
   898         -            if( zTabName ){
          896  +            pRight->token.dyn = 0;
          897  +            pRight->token.base = 1;
          898  +            if( zTabName && pTabList->nSrc>1 ){
   899    899                 pLeft = sqliteExpr(TK_ID, 0, 0, 0);
   900         -              if( pLeft==0 ) break;
   901         -              pLeft->token.z = zTabName;
   902         -              pLeft->token.n = strlen(zTabName);
   903    900                 pExpr = sqliteExpr(TK_DOT, pLeft, pRight, 0);
   904    901                 if( pExpr==0 ) break;
          902  +              pLeft->token.z = zTabName;
          903  +              pLeft->token.n = strlen(zTabName);
          904  +              pLeft->token.dyn = 0;
          905  +              pLeft->token.base = 1;
          906  +              sqliteSetString((char**)&pExpr->token.z, zTabName, ".", zName, 0);
          907  +              pExpr->token.n = strlen(pExpr->token.z);
          908  +              pExpr->token.base = 0;
          909  +              pExpr->token.dyn = 1;
   905    910               }else{
   906    911                 pExpr = pRight;
   907         -              pExpr->span = pExpr->token;
   908    912               }
   909    913               pNew = sqliteExprListAppend(pNew, pExpr, 0);
   910    914             }
   911    915           }
   912    916           if( !tableSeen ){
   913    917             if( pName ){
   914    918               sqliteSetNString(&pParse->zErrMsg, "no such table: ", -1, 
................................................................................
   941    945     SrcList *pSrc = p->pSrc;
   942    946     Table *pTab;
   943    947     if( p==0 ) return;
   944    948     for(i=0; i<pSrc->nSrc; i++){
   945    949       if( (pTab = pSrc->a[i].pTab)!=0 ){
   946    950         if( pTab->isTransient ){
   947    951           sqliteDeleteTable(0, pTab);
          952  +#if 0
   948    953           sqliteSelectDelete(pSrc->a[i].pSelect);
   949    954           pSrc->a[i].pSelect = 0;
          955  +#endif
   950    956         }
   951    957         pSrc->a[i].pTab = 0;
   952    958         if( pSrc->a[i].pSelect ){
   953    959           sqliteSelectUnbind(pSrc->a[i].pSelect);
   954    960         }
   955    961       }
   956    962     }
................................................................................
  1305   1311       pExpr->op = pNew->op;
  1306   1312       pExpr->pLeft = sqliteExprDup(pNew->pLeft);
  1307   1313       pExpr->pRight = sqliteExprDup(pNew->pRight);
  1308   1314       pExpr->pList = sqliteExprListDup(pNew->pList);
  1309   1315       pExpr->iTable = pNew->iTable;
  1310   1316       pExpr->iColumn = pNew->iColumn;
  1311   1317       pExpr->iAgg = pNew->iAgg;
  1312         -    pExpr->token = pNew->token;
         1318  +    pExpr->nFuncName = pNew->nFuncName;
         1319  +    sqliteTokenCopy(&pExpr->token, &pNew->token);
  1313   1320       if( iSub!=iTable ){
  1314   1321         changeTables(pExpr, iSub, iTable);
  1315   1322       }
  1316   1323     }else{
  1317   1324       static void substExprList(ExprList*,int,ExprList*,int);
  1318   1325       substExpr(pExpr->pLeft, iTable, pEList, iSub);
  1319   1326       substExpr(pExpr->pRight, iTable, pEList, iSub);
................................................................................
  1424   1431     iParent = p->base + iFrom;
  1425   1432     iSub = pSub->base;
  1426   1433     substExprList(p->pEList, iParent, pSub->pEList, iSub);
  1427   1434     pList = p->pEList;
  1428   1435     for(i=0; i<pList->nExpr; i++){
  1429   1436       if( pList->a[i].zName==0 ){
  1430   1437         Expr *pExpr = pList->a[i].pExpr;
  1431         -      pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
         1438  +      assert( pExpr->token.z!=0 );
         1439  +      pList->a[i].zName = sqliteStrNDup(pExpr->token.z, pExpr->token.n);
  1432   1440       }
  1433   1441     }
  1434   1442     if( isAgg ){
  1435   1443       substExprList(p->pGroupBy, iParent, pSub->pEList, iSub);
  1436   1444       substExpr(p->pHaving, iParent, pSub->pEList, iSub);
  1437   1445     }
  1438   1446     substExprList(p->pOrderBy, iParent, pSub->pEList, iSub);
................................................................................
  1531   1539     */
  1532   1540     if( p->pGroupBy || p->pHaving || p->pWhere ) return 0;
  1533   1541     if( p->pSrc->nSrc!=1 ) return 0;
  1534   1542     if( p->pEList->nExpr!=1 ) return 0;
  1535   1543     pExpr = p->pEList->a[0].pExpr;
  1536   1544     if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
  1537   1545     if( pExpr->pList==0 || pExpr->pList->nExpr!=1 ) return 0;
  1538         -  if( pExpr->token.n!=3 ) return 0;
         1546  +  if( pExpr->nFuncName!=3 ) return 0;
  1539   1547     if( sqliteStrNICmp(pExpr->token.z,"min",3)==0 ){
  1540   1548       seekOp = OP_Rewind;
  1541   1549     }else if( sqliteStrNICmp(pExpr->token.z,"max",3)==0 ){
  1542   1550       seekOp = OP_Last;
  1543   1551     }else{
  1544   1552       return 0;
  1545   1553     }

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.142 2002/08/02 10:36:10 drh Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.143 2002/08/24 18:24:55 drh Exp $
    15     15   */
    16     16   #include "sqlite.h"
    17     17   #include "hash.h"
    18     18   #include "vdbe.h"
    19     19   #include "parse.h"
    20     20   #include "btree.h"
    21     21   #include <stdio.h>
................................................................................
   387    387     u8 onError;      /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
   388    388     u8 autoIndex;    /* True if is automatically created (ex: by UNIQUE) */
   389    389     Index *pNext;    /* The next index associated with the same table */
   390    390   };
   391    391   
   392    392   /*
   393    393   ** Each token coming out of the lexer is an instance of
   394         -** this structure.
          394  +** this structure.  Tokens are also used as part of an expression.
          395  +**
          396  +** A "base" token is a real single token such as would come out of the
          397  +** lexer.  There are also compound tokens which are aggregates of one
          398  +** or more base tokens.  Compound tokens are used to name columns in the
          399  +** result set of a SELECT statement.  In the expression "a+b+c", "b"
          400  +** is a base token but "a+b" is a compound token.
   395    401   */
   396    402   struct Token {
   397    403     const char *z;      /* Text of the token.  Not NULL-terminated! */
   398         -  int n;              /* Number of characters in this token */
          404  +  unsigned dyn  : 1;  /* True for malloced memory, false for static */
          405  +  unsigned base : 1;  /* True for a base token, false for compounds */
          406  +  unsigned n    : 30; /* Number of characters in this token */
   399    407   };
   400    408   
   401    409   /*
   402    410   ** Each node of an expression in the parse tree is an instance
   403    411   ** of this structure.
   404    412   **
   405    413   ** Expr.op is the opcode.  The integer parser token codes are reused
................................................................................
   407    415   ** code representing the ">=" operator.  This same integer code is reused
   408    416   ** to represent the greater-than-or-equal-to operator in the expression
   409    417   ** tree.
   410    418   **
   411    419   ** Expr.pRight and Expr.pLeft are subexpressions.  Expr.pList is a list
   412    420   ** of argument if the expression is a function.
   413    421   **
   414         -** Expr.token is the operator token for this node.  Expr.span is the complete
   415         -** subexpression represented by this node and all its decendents.  These
   416         -** fields are used for error reporting and for reconstructing the text of
   417         -** an expression to use as the column name in a SELECT statement.
          422  +** Expr.token is the operator token for this node.  For some expressions
          423  +** that have subexpressions, Expr.token can be the complete text that gave
          424  +** rise to the Expr.  In the latter case, the token is marked as being
          425  +** a compound token.
   418    426   **
   419    427   ** An expression of the form ID or ID.ID refers to a column in a table.
   420    428   ** For such expressions, Expr.op is set to TK_COLUMN and Expr.iTable is
   421    429   ** the integer cursor number of a VDBE cursor pointing to that table and
   422    430   ** Expr.iColumn is the column number for the specific column.  If the
   423    431   ** expression is used as a result in an aggregate SELECT, then the
   424    432   ** value is also stored in the Expr.iAgg column in the aggregate so that
................................................................................
   431    439   ** be the right operand of an IN operator.  Or, if a scalar SELECT appears
   432    440   ** in an expression the opcode is TK_SELECT and Expr.pSelect is the only
   433    441   ** operand.
   434    442   */
   435    443   struct Expr {
   436    444     u8 op;                 /* Operation performed by this node */
   437    445     u8 dataType;           /* Either SQLITE_SO_TEXT or SQLITE_SO_NUM */
   438         -  u8 isJoinExpr;         /* Origina is the ON or USING phrase of a join */
   439         -  u8 staticToken;        /* Expr.token.z points to static memory */
          446  +  u8 isJoinExpr;         /* Origin is the ON or USING phrase of a join */
          447  +  u8 nFuncName;          /* Number of characters in a function name */
   440    448     Expr *pLeft, *pRight;  /* Left and right subnodes */
   441    449     ExprList *pList;       /* A list of expressions used as function arguments
   442    450                            ** or in "<expr> IN (<expr-list)" */
   443    451     Token token;           /* An operand token */
   444         -  Token span;            /* Complete text of the expression */
   445    452     int iTable, iColumn;   /* When op==TK_COLUMN, then this expr node means the
   446    453                            ** iColumn-th field of the iTable-th table. */
   447    454     int iAgg;              /* When op==TK_COLUMN and pParse->useAgg==TRUE, pull
   448    455                            ** result from the iAgg-th element of the aggregator */
   449    456     Select *pSelect;       /* When the expression is a sub-select.  Also the
   450    457                            ** right side of "<expr> IN (<select>)" */
   451    458   };
................................................................................
   673    680    * 1. In the "trigHash" hash table (part of the sqlite* that represents the 
   674    681    *    database). This allows Trigger structures to be retrieved by name.
   675    682    * 2. All triggers associated with a single table form a linked list, using the
   676    683    *    pNext member of struct Trigger. A pointer to the first element of the
   677    684    *    linked list is stored as the "pTrigger" member of the associated
   678    685    *    struct Table.
   679    686    *
   680         - * The "strings" member of struct Trigger contains a pointer to the memory 
   681         - * referenced by the various Token structures referenced indirectly by the
   682         - * "pWhen", "pColumns" and "step_list" members. (ie. the memory allocated for
   683         - * use in conjunction with the sqliteExprMoveStrings() etc. interface).
   684         - *
   685    687    * The "step_list" member points to the first element of a linked list
   686    688    * containing the SQL statements specified as the trigger program.
   687    689    *
   688    690    * When a trigger is initially created, the "isCommit" member is set to FALSE.
   689    691    * When a transaction is rolled back, any Trigger structures with "isCommit" set
   690    692    * to FALSE are deleted by the logic in sqliteRollbackInternalChanges(). When
   691    693    * a transaction is commited, the "isCommit" member is set to TRUE for any
................................................................................
   704    706     int tr_tm;              /* One of TK_BEFORE, TK_AFTER */
   705    707     Expr *pWhen;            /* The WHEN clause of the expresion (may be NULL) */
   706    708     IdList *pColumns;       /* If this is an UPDATE OF <column-list> trigger,
   707    709                                the <column-list> is stored here */
   708    710     int foreach;            /* One of TK_ROW or TK_STATEMENT */
   709    711   
   710    712     TriggerStep *step_list; /* Link list of trigger program steps             */
   711         -  char *strings;          /* pointer to allocation of Token strings */
   712    713     Trigger *pNext;         /* Next trigger associated with the table */
   713    714   };
   714    715   
   715    716   /*
   716    717    * An instance of struct TriggerStep is used to store a single SQL statement
   717    718    * that is a part of a trigger-program. 
   718    719    *
................................................................................
   916    917   int sqliteIsRowid(const char*);
   917    918   void sqliteGenerateRowDelete(sqlite*, Vdbe*, Table*, int, int);
   918    919   void sqliteGenerateRowIndexDelete(sqlite*, Vdbe*, Table*, int, char*);
   919    920   void sqliteGenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int);
   920    921   void sqliteCompleteInsertion(Parse*, Table*, int, char*, int, int);
   921    922   void sqliteBeginWriteOperation(Parse*, int);
   922    923   void sqliteEndWriteOperation(Parse*);
   923         -void sqliteExprMoveStrings(Expr*, int);
   924         -void sqliteExprListMoveStrings(ExprList*, int);
   925         -void sqliteSelectMoveStrings(Select*, int);
   926    924   Expr *sqliteExprDup(Expr*);
          925  +void sqliteTokenCopy(Token*, Token*);
   927    926   ExprList *sqliteExprListDup(ExprList*);
   928    927   SrcList *sqliteSrcListDup(SrcList*);
   929    928   IdList *sqliteIdListDup(IdList*);
   930    929   Select *sqliteSelectDup(Select*);
   931    930   FuncDef *sqliteFindFunction(sqlite*,const char*,int,int,int);
   932    931   void sqliteRegisterBuiltinFunctions(sqlite*);
   933    932   int sqliteSafetyOn(sqlite*);
   934    933   int sqliteSafetyOff(sqlite*);
   935    934   int sqliteSafetyCheck(sqlite*);
   936    935   void sqliteChangeCookie(sqlite*, Vdbe*);
   937    936   void sqliteCreateTrigger(Parse*, Token*, int, int, IdList*, Token*, 
   938         -                         int, Expr*, TriggerStep*, char const*,int);
          937  +                         int, Expr*, TriggerStep*, Token*);
   939    938   void sqliteDropTrigger(Parse*, Token*, int);
   940    939   int sqliteTriggersExist(Parse* , Trigger* , int , int , int, ExprList*);
   941    940   int sqliteCodeRowTrigger(Parse*, int, ExprList*, int, Table *, int, int, 
   942    941                            int, int);
   943    942   void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*);
   944    943   TriggerStep *sqliteTriggerSelectStep(Select*);
   945    944   TriggerStep *sqliteTriggerInsertStep(Token*, IdList*, ExprList*, Select*, int);
   946    945   TriggerStep *sqliteTriggerUpdateStep(Token*, ExprList*, Expr*, int);
   947    946   TriggerStep *sqliteTriggerDeleteStep(Token*, Expr*);
   948    947   void sqliteDeleteTrigger(Trigger*);
   949    948   int sqliteJoinType(Parse*, Token*, Token*, Token*);

Changes to src/tokenize.c.

    11     11   *************************************************************************
    12     12   ** An tokenizer for SQL
    13     13   **
    14     14   ** This file contains C code that splits an SQL input string up into
    15     15   ** individual tokens and sends those tokens one-by-one over to the
    16     16   ** parser for analysis.
    17     17   **
    18         -** $Id: tokenize.c,v 1.47 2002/07/01 12:27:09 drh Exp $
           18  +** $Id: tokenize.c,v 1.48 2002/08/24 18:24:56 drh Exp $
    19     19   */
    20     20   #include "sqliteInt.h"
    21     21   #include "os.h"
    22     22   #include <ctype.h>
    23     23   #include <stdlib.h>
    24     24   
    25     25   /*
................................................................................
   414    414       
   415    415       if( (db->flags & SQLITE_Interrupt)!=0 ){
   416    416         pParse->rc = SQLITE_INTERRUPT;
   417    417         sqliteSetString(pzErrMsg, "interrupt", 0);
   418    418         break;
   419    419       }
   420    420       pParse->sLastToken.z = &zSql[i];
          421  +    pParse->sLastToken.base = 1;
          422  +    pParse->sLastToken.dyn = 0;
   421    423       pParse->sLastToken.n = sqliteGetToken((unsigned char*)&zSql[i], &tokenType);
   422    424       i += pParse->sLastToken.n;
   423    425       if( once ){
   424    426         pParse->sFirstToken = pParse->sLastToken;
   425    427         once = 0;
   426    428       }
   427    429       switch( tokenType ){

Changes to src/trigger.c.

     7      7   **    May you find forgiveness for yourself and forgive others.
     8      8   **    May you share freely, never taking more than you give.
     9      9   **
    10     10   *************************************************************************
    11     11   *
    12     12   */
    13     13   #include "sqliteInt.h"
           14  +
           15  +/*
           16  +** Delete a linked list of TriggerStep structures.
           17  +*/
           18  +static void sqliteDeleteTriggerStep(TriggerStep *pTriggerStep){
           19  +  while( pTriggerStep ){
           20  +    TriggerStep * pTmp = pTriggerStep;
           21  +    pTriggerStep = pTriggerStep->pNext;
           22  +
           23  +    if( pTmp->target.dyn ) sqliteFree(pTmp->target.z);
           24  +    sqliteExprDelete(pTmp->pWhere);
           25  +    sqliteExprListDelete(pTmp->pExprList);
           26  +    sqliteSelectDelete(pTmp->pSelect);
           27  +    sqliteIdListDelete(pTmp->pIdList);
           28  +
           29  +    sqliteFree(pTmp);
           30  +  }
           31  +}
    14     32   
    15     33   /*
    16     34   ** This is called by the parser when it sees a CREATE TRIGGER statement. See
    17     35   ** comments surrounding struct Trigger in sqliteInt.h for a description of 
    18     36   ** how triggers are stored.
    19     37   */
    20     38   void sqliteCreateTrigger(
................................................................................
    23     41     int tr_tm,          /* One of TK_BEFORE, TK_AFTER , TK_INSTEAD */
    24     42     int op,             /* One of TK_INSERT, TK_UPDATE, TK_DELETE */
    25     43     IdList *pColumns,   /* column list if this is an UPDATE OF trigger */
    26     44     Token *pTableName,  /* The name of the table/view the trigger applies to */
    27     45     int foreach,        /* One of TK_ROW or TK_STATEMENT */
    28     46     Expr *pWhen,        /* WHEN clause */
    29     47     TriggerStep *pStepList, /* The triggered program */
    30         -  char const *zData,  /* The string data to make persistent */
    31         -  int zDataLen
           48  +  Token *pAll             /* Token that describes the complete CREATE TRIGGER */
    32     49   ){
    33     50     Trigger *nt;
    34     51     Table   *tab;
    35         -  int offset;
    36         -  TriggerStep *ss;
    37     52   
    38     53     /* Check that: 
    39     54     ** 1. the trigger name does not already exist.
    40     55     ** 2. the table (or view) does exist.
    41     56     ** 3. that we are not trying to create a trigger on the sqlite_master table
    42     57     ** 4. That we are not trying to create an INSTEAD OF trigger on a table.
    43     58     ** 5. That we are not trying to create a BEFORE or AFTER trigger on a view.
................................................................................
    94    109     }
    95    110   
    96    111     /* Build the Trigger object */
    97    112     nt = (Trigger*)sqliteMalloc(sizeof(Trigger));
    98    113     if( nt==0 ) goto trigger_cleanup;
    99    114     nt->name = sqliteStrNDup(pName->z, pName->n);
   100    115     nt->table = sqliteStrNDup(pTableName->z, pTableName->n);
   101         -  nt->strings = sqliteStrNDup(zData, zDataLen);
   102    116     if( sqlite_malloc_failed ) goto trigger_cleanup;
   103    117     nt->op = op;
   104    118     nt->tr_tm = tr_tm;
   105         -  nt->pWhen = pWhen;
   106         -  nt->pColumns = pColumns;
          119  +  nt->pWhen = sqliteExprDup(pWhen);
          120  +  sqliteExprDelete(pWhen);
          121  +  nt->pColumns = sqliteIdListDup(pColumns);
          122  +  sqliteIdListDelete(pColumns);
   107    123     nt->foreach = foreach;
   108    124     nt->step_list = pStepList;
   109         -  offset = (int)(nt->strings - zData);
   110         -  sqliteExprMoveStrings(nt->pWhen, offset);
   111         -
   112         -  ss = nt->step_list;
   113         -  while( ss ){
   114         -    sqliteSelectMoveStrings(ss->pSelect, offset);
   115         -    if( ss->target.z ){
   116         -      ss->target.z += offset;
   117         -    }
   118         -    sqliteExprMoveStrings(ss->pWhere, offset);
   119         -    sqliteExprListMoveStrings(ss->pExprList, offset);
   120         -
   121         -    ss = ss->pNext;
   122         -  }
   123    125   
   124    126     /* if we are not initializing, and this trigger is not on a TEMP table, 
   125    127     ** build the sqlite_master entry
   126    128     */
   127    129     if( !pParse->initFlag ){
   128    130       static VdbeOp insertTrig[] = {
   129    131         { OP_NewRecno,   0, 0,  0          },
................................................................................
   144    146       sqliteBeginWriteOperation(pParse, 0);
   145    147       sqliteOpenMasterTable(v, tab->isTemp);
   146    148       addr = sqliteVdbeAddOpList(v, ArraySize(insertTrig), insertTrig);
   147    149       sqliteVdbeChangeP3(v, addr, tab->isTemp ? TEMP_MASTER_NAME : MASTER_NAME,
   148    150                          P3_STATIC);
   149    151       sqliteVdbeChangeP3(v, addr+2, nt->name, 0); 
   150    152       sqliteVdbeChangeP3(v, addr+3, nt->table, 0); 
   151         -    sqliteVdbeChangeP3(v, addr+5, nt->strings, 0);
          153  +    sqliteVdbeChangeP3(v, addr+5, pAll->z, pAll->n);
   152    154       if( !tab->isTemp ){
   153    155         sqliteChangeCookie(pParse->db, v);
   154    156       }
   155    157       sqliteVdbeAddOp(v, OP_Close, 0, 0);
   156    158       sqliteEndWriteOperation(pParse);
   157    159     }
   158    160   
................................................................................
   161    163       sqliteHashInsert(&(pParse->db->trigHash), nt->name, pName->n + 1, nt);
   162    164   
   163    165       /* Attach it to the table object */
   164    166       nt->pNext = tab->pTrigger;
   165    167       tab->pTrigger = nt;
   166    168       return;
   167    169     }else{
   168         -    sqliteFree(nt->strings);
   169    170       sqliteFree(nt->name);
   170    171       sqliteFree(nt->table);
   171    172       sqliteFree(nt);
   172    173     }
   173    174   
   174    175   trigger_cleanup:
   175    176   
   176    177     sqliteIdListDelete(pColumns);
   177    178     sqliteExprDelete(pWhen);
   178         -  {
   179         -    TriggerStep * pp;
   180         -    TriggerStep * nn;
   181         -
   182         -    pp = pStepList;
   183         -    while( pp ){
   184         -      nn = pp->pNext;
   185         -      sqliteExprDelete(pp->pWhere);
   186         -      sqliteExprListDelete(pp->pExprList);
   187         -      sqliteSelectDelete(pp->pSelect);
   188         -      sqliteIdListDelete(pp->pIdList);
   189         -      sqliteFree(pp);
   190         -      pp = nn;
   191         -    }
          179  +  sqliteDeleteTriggerStep(pStepList);
          180  +}
          181  +
          182  +/*
          183  +** Make a copy of all components of the given trigger step.  This has
          184  +** the effect of copying all Expr.token.z values into memory obtained
          185  +** from sqliteMalloc().  As initially created, the Expr.token.z values
          186  +** all point to the input string that was fed to the parser.  But that
          187  +** string is ephemeral - it will go away as soon as the sqlite_exec()
          188  +** call that started the parser exits.  This routine makes a persistent
          189  +** copy of all the Expr.token.z strings so that the TriggerStep structure
          190  +** will be valid even after the sqlite_exec() call returns.
          191  +*/
          192  +static void sqlitePersistTriggerStep(TriggerStep *p){
          193  +  if( p->target.z ){
          194  +    p->target.z = sqliteStrNDup(p->target.z, p->target.n);
          195  +    p->target.dyn = 1;
          196  +  }
          197  +  if( p->pSelect ){
          198  +    Select *pNew = sqliteSelectDup(p->pSelect);
          199  +    sqliteSelectDelete(p->pSelect);
          200  +    p->pSelect = pNew;
          201  +  }
          202  +  if( p->pWhere ){
          203  +    Expr *pNew = sqliteExprDup(p->pWhere);
          204  +    sqliteExprDelete(p->pWhere);
          205  +    p->pWhere = pNew;
          206  +  }
          207  +  if( p->pExprList ){
          208  +    ExprList *pNew = sqliteExprListDup(p->pExprList);
          209  +    sqliteExprListDelete(p->pExprList);
          210  +    p->pExprList = pNew;
          211  +  }
          212  +  if( p->pIdList ){
          213  +    IdList *pNew = sqliteIdListDup(p->pIdList);
          214  +    sqliteIdListDelete(p->pIdList);
          215  +    p->pIdList = pNew;
   192    216     }
   193    217   }
   194    218   
   195    219   /*
   196    220   ** Turn a SELECT statement (that the pSelect parameter points to) into
   197    221   ** a trigger step.  Return a pointer to a TriggerStep structure.
   198    222   **
................................................................................
   202    226   TriggerStep *sqliteTriggerSelectStep(Select *pSelect){
   203    227     TriggerStep *pTriggerStep = sqliteMalloc(sizeof(TriggerStep));
   204    228     if( pTriggerStep==0 ) return 0;
   205    229   
   206    230     pTriggerStep->op = TK_SELECT;
   207    231     pTriggerStep->pSelect = pSelect;
   208    232     pTriggerStep->orconf = OE_Default;
          233  +  sqlitePersistTriggerStep(pTriggerStep);
   209    234   
   210    235     return pTriggerStep;
   211    236   }
   212    237   
   213    238   /*
   214    239   ** Build a trigger step out of an INSERT statement.  Return a pointer
   215    240   ** to the new trigger step.
................................................................................
   232    257   
   233    258     pTriggerStep->op = TK_INSERT;
   234    259     pTriggerStep->pSelect = pSelect;
   235    260     pTriggerStep->target  = *pTableName;
   236    261     pTriggerStep->pIdList = pColumn;
   237    262     pTriggerStep->pExprList = pEList;
   238    263     pTriggerStep->orconf = orconf;
          264  +  sqlitePersistTriggerStep(pTriggerStep);
   239    265   
   240    266     return pTriggerStep;
   241    267   }
   242    268   
   243    269   /*
   244    270   ** Construct a trigger step that implements an UPDATE statement and return
   245    271   ** a pointer to that trigger step.  The parser calls this routine when it
................................................................................
   255    281     if( pTriggerStep==0 ) return 0;
   256    282   
   257    283     pTriggerStep->op = TK_UPDATE;
   258    284     pTriggerStep->target  = *pTableName;
   259    285     pTriggerStep->pExprList = pEList;
   260    286     pTriggerStep->pWhere = pWhere;
   261    287     pTriggerStep->orconf = orconf;
          288  +  sqlitePersistTriggerStep(pTriggerStep);
   262    289   
   263    290     return pTriggerStep;
   264    291   }
   265    292   
   266    293   /*
   267    294   ** Construct a trigger step that implements a DELETE statement and return
   268    295   ** a pointer to that trigger step.  The parser calls this routine when it
................................................................................
   272    299     TriggerStep *pTriggerStep = sqliteMalloc(sizeof(TriggerStep));
   273    300     if( pTriggerStep==0 ) return 0;
   274    301   
   275    302     pTriggerStep->op = TK_DELETE;
   276    303     pTriggerStep->target  = *pTableName;
   277    304     pTriggerStep->pWhere = pWhere;
   278    305     pTriggerStep->orconf = OE_Default;
          306  +  sqlitePersistTriggerStep(pTriggerStep);
   279    307   
   280    308     return pTriggerStep;
   281    309   }
   282    310   
   283    311   /* 
   284    312   ** Recursively delete a Trigger structure
   285    313   */
   286    314   void sqliteDeleteTrigger(Trigger *pTrigger){
   287    315     TriggerStep *pTriggerStep;
   288    316   
   289         -  pTriggerStep = pTrigger->step_list;
   290         -  while( pTriggerStep ){
   291         -    TriggerStep * pTmp = pTriggerStep;
   292         -    pTriggerStep = pTriggerStep->pNext;
   293         -
   294         -    sqliteExprDelete(pTmp->pWhere);
   295         -    sqliteExprListDelete(pTmp->pExprList);
   296         -    sqliteSelectDelete(pTmp->pSelect);
   297         -    sqliteIdListDelete(pTmp->pIdList);
   298         -
   299         -    sqliteFree(pTmp);
   300         -  }
   301         -
          317  +  sqliteDeleteTriggerStep(pTrigger->step_list);
   302    318     sqliteFree(pTrigger->name);
   303    319     sqliteFree(pTrigger->table);
   304    320     sqliteExprDelete(pTrigger->pWhen);
   305    321     sqliteIdListDelete(pTrigger->pColumns);
   306         -  sqliteFree(pTrigger->strings);
   307    322     sqliteFree(pTrigger);
   308    323   }
   309    324   
   310    325   /*
   311    326    * This function is called to drop a trigger from the database schema. 
   312    327    *
   313    328    * This may be called directly from the parser, or from within 

Changes to test/all.test.

     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file runs all tests.
    12     12   #
    13         -# $Id: all.test,v 1.16 2002/08/11 20:10:49 drh Exp $
           13  +# $Id: all.test,v 1.17 2002/08/24 18:24:57 drh Exp $
    14     14   
    15     15   set testdir [file dirname $argv0]
    16     16   source $testdir/tester.tcl
    17     17   rename finish_test really_finish_test
    18     18   proc finish_test {} {memleak_check}
    19     19   
    20     20   if {[file exists ./sqlite_test_count]} {
................................................................................
    30     30   set LeakList {}
    31     31   
    32     32   set EXCLUDE {
    33     33     all.test
    34     34     quick.test
    35     35     malloc.test
    36     36     misuse.test
           37  +  memleak.test
    37     38   }
    38     39   #  btree2.test
    39     40   
    40     41   for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} {
    41     42     set btree_native_byte_order [expr {($Counter>>1)&0x1}]
    42     43     if {$Counter%2} {
    43     44       set ::SETUP_SQL {PRAGMA default_synchronous=off;}

Changes to test/quick.test.

     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file runs all tests.
    12     12   #
    13         -# $Id: quick.test,v 1.3 2002/07/07 16:52:47 drh Exp $
           13  +# $Id: quick.test,v 1.4 2002/08/24 18:24:57 drh Exp $
    14     14   
    15     15   set testdir [file dirname $argv0]
    16     16   source $testdir/tester.tcl
    17     17   rename finish_test really_finish_test
    18     18   proc finish_test {} {}
    19     19   set ISQUICK 1
    20     20   
    21     21   set EXCLUDE {
    22     22     all.test
    23     23     quick.test
    24     24     btree2.test
    25     25     malloc.test
           26  +  memleak.test
    26     27   }
    27     28   
    28     29   foreach testfile [lsort -dictionary [glob $testdir/*.test]] {
    29     30     set tail [file tail $testfile]
    30     31     if {[lsearch -exact $EXCLUDE $tail]>=0} continue
    31     32     source $testfile
    32     33   }
    33     34   
    34     35   really_finish_test

Changes to test/view.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing VIEW statements.
    13     13   #
    14         -# $Id: view.test,v 1.8 2002/07/16 02:05:45 drh Exp $
           14  +# $Id: view.test,v 1.9 2002/08/24 18:24:57 drh Exp $
    15     15   set testdir [file dirname $argv0]
    16     16   source $testdir/tester.tcl
    17     17   
    18     18   do_test view-1.0 {
    19     19     execsql {
    20     20       CREATE TABLE t1(a,b,c);
    21     21       INSERT INTO t1 VALUES(1,2,3);
................................................................................
   260    260   do_test view-7.6 {
   261    261     db close
   262    262     sqlite db test.db
   263    263     execsql {
   264    264       SELECT * FROM test;
   265    265     }
   266    266   } {1 2 3}
          267  +
          268  +do_test view-8.1 {
          269  +  execsql {
          270  +    CREATE VIEW v6 AS SELECT pqr, xyz FROM v1;
          271  +    SELECT * FROM v6 ORDER BY xyz;
          272  +  }
          273  +} {7 2 13 5 19 8 27 12}
          274  +if 0 {
          275  +do_test view-8.2 {
          276  +  db close
          277  +  sqlite db test.db
          278  +  execsql {
          279  +    SELECT * FROM v6 ORDER BY xyz;
          280  +  }
          281  +} {7 2 13 5 19 8 27 12}
          282  +do_test view-8.3 {
          283  +  execsql {
          284  +    CREATE VIEW v7 AS SELECT pqr+xyz AS a FROM v6;
          285  +    SELECT * FROM v7 ORDER BY a;
          286  +  }
          287  +} {9 18 27 39}
          288  +do_test view-8.4 {
          289  +  execsql { PRAGMA vdbe_trace=on;
          290  +    CREATE VIEW v8 AS SELECT max(cnt) FROM
          291  +      (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo);
          292  +    SELECT * FROM v8;
          293  +  }
          294  +} 3
          295  +}
   267    296   
   268    297   finish_test

Changes to www/c_interface.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: c_interface.tcl,v 1.34 2002/08/15 11:48:14 drh Exp $}
            4  +set rcsid {$Id: c_interface.tcl,v 1.35 2002/08/24 18:24:57 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>The C language interface to the SQLite library</title>
     9      9   </head>
    10     10   <body bgcolor=white>
    11     11   <h1 align=center>
................................................................................
   712    712   from malloc() and returns a pointer to the malloced buffer.  
   713    713   <b>sqlite_mprintf()</b> also understands the %q and %Q extensions described
   714    714   above.  The <b>sqlite_vmprintf()</b> is a varargs version of the same
   715    715   routine.  The string pointer that these routines return should be freed
   716    716   by passing it to <b>sqlite_freemem()</b>.
   717    717   </p>
   718    718   
          719  +<a name="cfunc">
   719    720   <h2>Adding New SQL Functions</h2>
   720    721   
   721    722   <p>Beginning with version 2.4.0, SQLite allows the SQL language to be
   722    723   extended with new functions implemented as C code.  The following interface
   723    724   is used:
   724    725   </p>
   725    726   
................................................................................
   756    757   The <b>sqlite_create_function()</b> interface is used to create 
   757    758   regular functions and <b>sqlite_create_aggregate()</b> is used to
   758    759   create new aggregate functions.  In both cases, the <b>db</b>
   759    760   parameter is an open SQLite database on which the functions should
   760    761   be registered, <b>zName</b> is the name of the new function,
   761    762   <b>nArg</b> is the number of arguments, and <b>pUserData</b> is
   762    763   a pointer which is passed through unchanged to the C implementation
   763         -of the function.
          764  +of the function.  Both routines return 0 on success and non-zero
          765  +if there are any errors.
          766  +</p>
          767  +
          768  +<p>
          769  +The length of a function name may not exceed 255 characters.
          770  +Any attempt to create a function whose name exceeds 255 characters
          771  +in length will result in an error.
   764    772   </p>
   765    773   
   766    774   <p>
   767    775   For regular functions, the <b>xFunc</b> callback is invoked once
   768    776   for each function call.  The implementation of xFunc should call
   769    777   one of the <b>sqlite_set_result_...</b> interfaces to return its
   770    778   result.  The <b>sqlite_user_data()</b> routine can be used to

Changes to www/faq.tcl.

     1      1   #
     2      2   # Run this script to generated a faq.html output file
     3      3   #
     4         -set rcsid {$Id: faq.tcl,v 1.18 2002/08/18 19:09:24 drh Exp $}
            4  +set rcsid {$Id: faq.tcl,v 1.19 2002/08/24 18:24:57 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>SQLite Frequently Asked Questions</title>
     9      9   </head>
    10     10   <body bgcolor="white">
    11     11   <h1 align="center">Frequently Asked Questions</h1>
................................................................................
   326    326     tables and indices, but again it is not really possible to reach this
   327    327     limit due to the file size constraint.</p>
   328    328   
   329    329     <p>The name and "CREATE TABLE" statement for a table must fit entirely
   330    330     within a 1-megabyte row of the SQLITE_MASTER table.  Other than this,
   331    331     there are no constraints on the length of the name of a table, or on the
   332    332     number of columns, etc.  Indices are similarly unconstrained.</p>
          333  +
          334  +  <p>The names of tables, indices, view, triggers, and columns can be
          335  +  as long as desired.  However, the names of SQL functions (as created
          336  +  by the <a href="c_interface.html#cfunc">sqlite_create_function()</a> API)
          337  +  may not exceed 255 characters in length.</p>
   333    338   }
   334    339   
   335    340   faq {
   336    341     What is the maximum size of a VARCHAR in SQLite?
   337    342   } {
   338    343     <p>Remember, SQLite is typeless.  A VARCHAR column can hold as much
   339    344     data as any other column.  The total amount of data in a single row

Changes to www/omitted.tcl.

     1      1   #
     2      2   # Run this script to generated a omitted.html output file
     3      3   #
     4         -set rcsid {$Id: omitted.tcl,v 1.2 2002/08/15 13:45:17 drh Exp $}
            4  +set rcsid {$Id: omitted.tcl,v 1.3 2002/08/24 18:24:58 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>SQL Features That SQLite Does Not Implement</title>
     9      9   </head>
    10     10   <body bgcolor="white">
    11     11   <h1 align="center">
................................................................................
    41     41   feature {CHECK constraints} {
    42     42     CHECK constraints are parsed but they are not enforced.
    43     43     NOT NULL and UNIQUE constraints are enforced, however.
    44     44   }
    45     45   
    46     46   feature {Variable subqueries} {
    47     47     Subqueries must be static.  They are evaluated only once.  They may not,
    48         -  therefore, refer to variables in the containing query.
           48  +  therefore, refer to variables in the main query.
    49     49   }
    50     50   
    51     51   feature {FOREIGN KEY constraints} {
    52     52     FOREIGN KEY constraints are parsed but are not enforced.
    53     53   }
           54  +
           55  +feature {Complete trigger support} {
           56  +  There is some support for triggers but it is not complete.  Missing
           57  +  subfeatures include FOR EACH STATEMENT triggers (currently all triggers
           58  +  must be FOR EACH ROW), INSTEAD OF triggers on tables (currently 
           59  +  INSTEAD OF triggers are only allowed on views), and recursive
           60  +  triggers - triggers that trigger themselves.
           61  +}
    54     62   
    55     63   feature {ALTER TABLE} {
    56     64     To change a table you have to delete it (saving its contents to a temporary
    57     65     table) and recreate it from scratch.
    58     66   }
           67  +
           68  +feature {Nested transactions} {
           69  +  The current implementation only allows a single active transaction.
           70  +}
    59     71   
    60     72   feature {The COUNT(DISTINCT X) function} {
    61     73     You can accomplish the same thing using a subquery, like this:<br />
    62     74     &nbsp;&nbsp;SELECT count(x) FROM (SELECT DISTINCT x FROM tbl);
    63     75   }
    64     76   
    65     77   feature {RIGHT and FULL OUTER JOIN} {

Changes to www/speed.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the speed.html file.
     3      3   #
     4         -set rcsid {$Id: speed.tcl,v 1.7 2002/08/06 12:05:01 drh Exp $ }
            4  +set rcsid {$Id: speed.tcl,v 1.8 2002/08/24 18:24:58 drh Exp $ }
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>Database Speed Comparison: SQLite versus PostgreSQL</title>
     9      9   </head>
    10     10   <body bgcolor=white>
    11     11   <h1 align=center>
................................................................................
    15     15   (This page was last modified on [lrange $rcsid 3 4] UTC)
    16     16   </p>"
    17     17   
    18     18   puts {
    19     19   <h2>Executive Summary</h2>
    20     20   
    21     21   <p>A series of tests were run to measure the relative performance of
    22         -SQLite 2.4.0, PostgreSQL, and MySQL
           22  +SQLite 2.7.0, PostgreSQL 7.1.3, and MySQL 3.23.41.
    23     23   The following are general
    24     24   conclusions drawn from these experiments:
    25     25   </p>
    26     26   
    27     27   <ul>
    28     28   <li><p>
    29         -  SQLite 2.4.0 is significantly faster than PostgreSQL
           29  +  SQLite 2.7.0 is significantly faster than PostgreSQL 7.1.3
    30     30     for most common operations.
    31     31   </p></li>
    32     32   <li><p>
    33         -  The speed of SQLite 2.4.0 is similar to MySQL.
           33  +  The speed of SQLite 2.7.0 is similar to MySQL 3.23.41.
    34     34     This is true in spite of the
    35     35     fact that SQLite contains full transaction support whereas the
    36     36     version of MySQL tested did not.
    37     37   </p></li>
           38  +<li><p>
           39  +  These tests did not attempt to measure multi-user performance or
           40  +  optimization of complex queries involving multiple joins and subqueries.
    38     41   </ul>
    39     42   
    40     43   <h2>Test Environment</h2>
    41     44   
    42     45   <p>
    43     46   The platform used for these tests is a 1.6GHz Athlon with 1GB or memory
    44     47   and an IDE disk drive.  The operating system is RedHat Linux 7.2 with
    45     48   a stock kernel.
    46     49   </p>
    47     50   
    48     51   <p>
    49     52   The PostgreSQL and MySQL servers used were as delivered by default on
    50         -RedHat 7.2.  No effort was made to tune these engines.  Note in particular
           53  +RedHat 7.2.  (PostgreSQL version 7.1.3 and MySQL version 3.23.41.)
           54  +No effort was made to tune these engines.  Note in particular
    51     55   the the default MySQL configuration on RedHat 7.2 does not support
    52     56   transactions.  Not having to support transactions gives MySQL a
    53         -big advantage, but SQLite is still able to hold its own on most
    54         -tests.
           57  +big speed advantage, but SQLite is still able to hold its own on most
           58  +tests.  On the other hand, I am told that the default PostgreSQL
           59  +configuration is unnecessarily conservative (it is designed to
           60  +work on a machine with 8MB of RAM) and that PostgreSQL could
           61  +be made to run a lot faster with some knowledgable configuration
           62  +tuning.  I have not, however, been able to personally confirm
           63  +these reports.
    55     64   </p>
    56     65   
    57     66   <p>
    58         -SQLite was compiled with -O6 optimization and with
           67  +SQLite was tested in the same configuration that it appears
           68  +on the website.  It was compiled with -O6 optimization and with
    59     69   the -DNDEBUG=1 switch which disables the many "assert()" statements
    60     70   in the SQLite code.  The -DNDEBUG=1 compiler option roughly doubles
    61     71   the speed of SQLite.
    62     72   </p>
    63     73   
    64     74   <p>
    65     75   All tests are conducted on an otherwise quiescent machine.
................................................................................
    96    106   INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');<br>
    97    107   <i>... 995 lines omitted</i><br>
    98    108   INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');<br>
    99    109   INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');<br>
   100    110   INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');<br>
   101    111   
   102    112   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   103         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.027</td></tr>
   104         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.113</td></tr>
   105         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;8.409</td></tr>
   106         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.188</td></tr>
          113  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.613</td></tr>
          114  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.086</td></tr>
          115  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;8.672</td></tr>
          116  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.286</td></tr>
   107    117   </table>
   108    118   
   109    119   <p>SQLite must close and reopen the database file, and thus invalidate
   110    120   its cache, for each SQL statement.  In spite of this, the asynchronous
   111    121   version of SQLite is still nearly as fast as MySQL.  Notice how much slower
   112    122   the synchronous version is, however.  This is due to the necessity of
   113    123   calling <b>fsync()</b> after each SQL statement.</p>
................................................................................
   119    129   INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one');<br>
   120    130   <i>... 24997 lines omitted</i><br>
   121    131   INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty seven');<br>
   122    132   INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred thirty');<br>
   123    133   COMMIT;<br>
   124    134   
   125    135   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   126         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.175</td></tr>
   127         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.444</td></tr>
   128         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.858</td></tr>
   129         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.739</td></tr>
          136  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.430</td></tr>
          137  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.025</td></tr>
          138  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;0.885</td></tr>
          139  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.753</td></tr>
   130    140   </table>
   131    141   
   132    142   <p>
   133    143   When all the INSERTs are put in a transaction, SQLite no longer has to
   134    144   close and reopen the database between each statement.  It also does not
   135    145   have to do any fsync()s until the very end.  When unshackled in
   136    146   this way, SQLite is much faster than either PostgreSQL and MySQL.
................................................................................
   143    153   SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<1200;<br>
   144    154   <i>... 94 lines omitted</i><br>
   145    155   SELECT count(*), avg(b) FROM t2 WHERE b>=9700 AND b<10700;<br>
   146    156   SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;<br>
   147    157   SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;<br>
   148    158   
   149    159   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   150         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.773</td></tr>
   151         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.023</td></tr>
   152         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;6.281</td></tr>
   153         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;6.247</td></tr>
          160  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.274</td></tr>
          161  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.624</td></tr>
          162  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;5.585</td></tr>
          163  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;5.443</td></tr>
   154    164   </table>
   155    165   
   156    166   <p>
   157    167   This test does 100 queries on a 25000 entry table without an index,
   158    168   thus requiring a full table scan.  SQLite is about half the speed of
   159    169   PostgreSQL and MySQL.  This is because SQLite stores all data as strings
   160    170   and must therefore call <b>strtod()</b> 5 million times in the
   161    171   course of evaluating the WHERE clauses.  Both PostgreSQL and MySQL
   162    172   store data as binary values where appropriate and can forego
   163    173   this conversion effort.
   164    174   </p>
   165         -
   166    175   
   167    176   <h2>Test 4: 100 SELECTs on a string comparison</h2>
   168    177   <blockquote>
   169    178   SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';<br>
   170    179   SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';<br>
   171    180   SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%three%';<br>
   172    181   <i>... 94 lines omitted</i><br>
   173    182   SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety eight%';<br>
   174    183   SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';<br>
   175    184   SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';<br>
   176    185   
   177    186   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   178         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;16.726</td></tr>
   179         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.237</td></tr>
   180         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;6.137</td></tr>
   181         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;6.112</td></tr>
          187  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;14.511</td></tr>
          188  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.616</td></tr>
          189  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;5.966</td></tr>
          190  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;5.918</td></tr>
   182    191   </table>
   183    192   
   184    193   <p>
   185    194   This set of 100 queries uses string comparisons instead of
   186    195   numerical comparisions.  As a result, the speed of SQLite is
   187    196   compariable to or better then PostgreSQL and MySQL.
   188    197   </p>
   189    198   
   190    199   <h2>Test 5: Creating an index</h2>
   191    200   <blockquote>
   192    201   CREATE INDEX i2a ON t2(a);<br>CREATE INDEX i2b ON t2(b);
   193    202   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   194         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.510</td></tr>
   195         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.352</td></tr>
   196         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.809</td></tr>
   197         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.720</td></tr>
          203  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.483</td></tr>
          204  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.304</td></tr>
          205  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;0.779</td></tr>
          206  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.637</td></tr>
   198    207   </table>
   199    208   
   200    209   <p>
   201    210   SQLite is slower at creating new indices.  But since creating
   202    211   new indices is an uncommon operation, this is not seen as a
   203    212   problem.
   204    213   </p>
................................................................................
   210    219   SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;<br>
   211    220   <i>... 4994 lines omitted</i><br>
   212    221   SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;<br>
   213    222   SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;<br>
   214    223   SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;<br>
   215    224   
   216    225   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   217         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.318</td></tr>
   218         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.555</td></tr>
   219         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;1.289</td></tr>
   220         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.273</td></tr>
          226  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.939</td></tr>
          227  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.335</td></tr>
          228  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;1.165</td></tr>
          229  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.144</td></tr>
   221    230   </table>
   222    231   
   223    232   <p>
   224    233   This test runs a set of 5000 queries that are similar in form to
   225    234   those in test 3.  But now instead of being half as fast, SQLite
   226    235   is faster than both PostgreSQL and MySQL.
   227    236   </p>
................................................................................
   233    242   UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;<br>
   234    243   <i>... 996 lines omitted</i><br>
   235    244   UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;<br>
   236    245   UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;<br>
   237    246   COMMIT;<br>
   238    247   
   239    248   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   240         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.828</td></tr>
   241         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;9.272</td></tr>
   242         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.915</td></tr>
   243         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.889</td></tr>
          249  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.536</td></tr>
          250  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;7.281</td></tr>
          251  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;0.817</td></tr>
          252  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.726</td></tr>
   244    253   </table>
   245    254   
   246    255   <p>
   247    256   Here is a case where MySQL is over 10 times slower than SQLite.
   248    257   The reason for this is unclear.
   249    258   </p>
   250    259   
................................................................................
   255    264   UPDATE t2 SET b=28304 WHERE a=2;<br>
   256    265   <i>... 24996 lines omitted</i><br>
   257    266   UPDATE t2 SET b=442549 WHERE a=24999;<br>
   258    267   UPDATE t2 SET b=423958 WHERE a=25000;<br>
   259    268   COMMIT;<br>
   260    269   
   261    270   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   262         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;28.021</td></tr>
   263         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;8.565</td></tr>
   264         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;10.939</td></tr>
   265         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;11.199</td></tr>
          271  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;29.318</td></tr>
          272  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;7.514</td></tr>
          273  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;7.681</td></tr>
          274  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;7.852</td></tr>
   266    275   </table>
   267    276   
   268    277   <p>
   269    278   In this case MySQL is slightly faster than SQLite, though not by much.
   270    279   The difference is believed to have to do with the fact SQLite 
   271    280   handles the integers as strings instead of binary numbers.
   272    281   </p>
................................................................................
   278    287   UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty eight' WHERE a=2;<br>
   279    288   <i>... 24996 lines omitted</i><br>
   280    289   UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;<br>
   281    290   UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' WHERE a=25000;<br>
   282    291   COMMIT;<br>
   283    292   
   284    293   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   285         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;48.739</td></tr>
   286         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;7.059</td></tr>
   287         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;7.868</td></tr>
   288         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;6.720</td></tr>
          294  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;50.020</td></tr>
          295  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.841</td></tr>
          296  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;5.346</td></tr>
          297  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;5.393</td></tr>
   289    298   </table>
   290    299   
   291    300   <p>
   292    301   When updating a text field instead of an integer field,
   293    302   SQLite is slightly faster than MySQL.
   294    303   </p>
   295    304   
   296    305   <h2>Test 10: INSERTs from a SELECT</h2>
   297    306   <blockquote>
   298    307   BEGIN;<br>INSERT INTO t1 SELECT * FROM t2;<br>INSERT INTO t2 SELECT * FROM t1;<br>COMMIT;
   299    308   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   300         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;54.822</td></tr>
   301         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.512</td></tr>
   302         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;4.423</td></tr>
   303         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;2.386</td></tr>
          309  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;57.834</td></tr>
          310  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.335</td></tr>
          311  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;5.073</td></tr>
          312  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;2.085</td></tr>
   304    313   </table>
   305    314   
   306    315   <p>
   307    316   The poor performance of PostgreSQL in this case appears to be due to its
   308         -synchronous behavior.  The CPU was mostly idle during the 55 second run.
          317  +synchronous behavior.  The CPU was mostly idle the test run.  Presumably,
          318  +PostgreSQL was spending most of its time waiting on disk I/O to complete.
          319  +</p>
          320  +
          321  +<p>
          322  +SQLite is slower than MySQL because it creates a temporary table to store
          323  +the result of the query, then does an insert from the temporary table.
          324  +A future enhancement that moves data directly from teh query into the
          325  +insert table should double the speed of SQLite.
   309    326   </p>
   310    327   
   311    328   <h2>Test 11: DELETE without an index</h2>
   312    329   <blockquote>
   313    330   DELETE FROM t2 WHERE c LIKE '%fifty%';
   314    331   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   315         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.734</td></tr>
   316         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.888</td></tr>
   317         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;5.405</td></tr>
   318         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.731</td></tr>
          332  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.733</td></tr>
          333  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.768</td></tr>
          334  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;5.418</td></tr>
          335  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.668</td></tr>
   319    336   </table>
   320         -
   321    337   
   322    338   <h2>Test 12: DELETE with an index</h2>
   323    339   <blockquote>
   324    340   DELETE FROM t2 WHERE a>10 AND a<20000;
   325    341   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   326         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.318</td></tr>
   327         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.600</td></tr>
   328         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;1.436</td></tr>
   329         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.775</td></tr>
          342  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.867</td></tr>
          343  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.068</td></tr>
          344  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;1.453</td></tr>
          345  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.745</td></tr>
   330    346   </table>
   331         -
   332    347   
   333    348   <h2>Test 13: A big INSERT after a big DELETE</h2>
   334    349   <blockquote>
   335    350   INSERT INTO t2 SELECT * FROM t1;
   336    351   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   337         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;63.867</td></tr>
   338         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.839</td></tr>
   339         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;3.971</td></tr>
   340         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.993</td></tr>
          352  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;66.099</td></tr>
          353  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.663</td></tr>
          354  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;4.029</td></tr>
          355  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.729</td></tr>
   341    356   </table>
   342    357   
   343    358   <p>
   344    359   Earlier versions of SQLite would show decreasing performance after a
   345    360   sequence DELETEs followed by new INSERTs.  As this test shows, the
   346    361   problem has now been resolved.
   347    362   </p>
................................................................................
   353    368   INSERT INTO t1 VALUES(1,29676,'twenty nine thousand six hundred seventy six');<br>
   354    369   <i>... 2997 lines omitted</i><br>
   355    370   INSERT INTO t1 VALUES(2999,37835,'thirty seven thousand eight hundred thirty five');<br>
   356    371   INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred seventeen');<br>
   357    372   COMMIT;<br>
   358    373   
   359    374   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   360         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.209</td></tr>
   361         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.031</td></tr>
   362         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.298</td></tr>
   363         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.282</td></tr>
          375  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.168</td></tr>
          376  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.866</td></tr>
          377  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;0.288</td></tr>
          378  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.155</td></tr>
   364    379   </table>
   365    380   
   366    381   <h2>Test 15: DROP TABLE</h2>
   367    382   <blockquote>
   368    383   DROP TABLE t1;<br>DROP TABLE t2;
   369    384   </blockquote><table border=0 cellpadding=0 cellspacing=0>
   370         -<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.105</td></tr>
   371         -<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.015</td></tr>
   372         -<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.472</td></tr>
   373         -<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.232</td></tr>
          385  +<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.100</td></tr>
          386  +<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.012</td></tr>
          387  +<tr><td>SQLite 2.7.0:</td><td align="right">&nbsp;&nbsp;&nbsp;0.572</td></tr>
          388  +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.168</td></tr>
   374    389   </table>
   375    390   
   376    391   <p>
   377    392   SQLite is slower than the other databases when it comes to dropping tables.
   378    393   This is not seen as a big problem, however, since DROP TABLE is seldom
   379    394   used in speed-critical situations.
   380    395   </p>