/ 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 Unified Diffs Ignore Whitespace Patch

Changes to main.mk.

122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
# Generate the file "last_change" which contains the date of change
# of the most recently modified source code file
#
last_change:	$(SRC)
	cat $(SRC) | grep '$$Id: ' | sort +4 | tail -1 \
          | awk '{print $$5,$$6}' >last_change

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

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

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







|
|







122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
# Generate the file "last_change" which contains the date of change
# of the most recently modified source code file
#
last_change:	$(SRC)
	cat $(SRC) | grep '$$Id: ' | sort +4 | tail -1 \
          | awk '{print $$5,$$6}' >last_change

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

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

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

Changes to src/build.c.

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
840
841
842
843
844
845
846
847
848

849
850
851
852
853
854
855
856
857
858
859
860
861
862





863

864
865
866
867
868



869
870
871
872
873
874
875
876
877
878
879

880
881
882
883
884
885
886
887
888
889
890
891
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.109 2002/08/18 20:28:07 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
void sqliteCreateView(
  Parse *pParse,     /* The parsing context */
  Token *pBegin,     /* The CREATE token that begins the statement */
  Token *pName,      /* The token that holds the name of the view */
  Select *pSelect,   /* A SELECT statement that will become the new view */
  int isTemp         /* TRUE for a TEMPORARY view */
){
  Token sEnd;
  Table *p;

  const char *z;
  int n, offset;

  sqliteStartTable(pParse, pBegin, pName, isTemp);
  p = pParse->pNewTable;
  if( p==0 ){
    sqliteSelectDelete(pSelect);
    return;
  }
  /* Ignore ORDER BY clauses on a SELECT */
  if( pSelect->pOrderBy ){
    sqliteExprListDelete(pSelect->pOrderBy);
    pSelect->pOrderBy = 0;
  }





  p->pSelect = pSelect;

  if( !pParse->initFlag ){
    if( sqliteViewGetColumnNames(pParse, p) ){
      return;
    }
  }



  sEnd = pParse->sLastToken;
  if( sEnd.z[0]!=0 && sEnd.z[0]!=';' ){
    sEnd.z += sEnd.n;
  }
  sEnd.n = 0;
  n = ((int)sEnd.z) - (int)pBegin->z;
  z = pBegin->z;
  while( n>0 && (z[n-1]==';' || isspace(z[n-1])) ){ n--; }
  sEnd.z = &z[n-1];
  sEnd.n = 1;
  z = p->pSelect->zSelect = sqliteStrNDup(z, n);

  if( z ){
    offset = ((int)z) - (int)pBegin->z;
    sqliteSelectMoveStrings(p->pSelect, offset);
    sqliteEndTable(pParse, &sEnd, 0);
  }
  return;
}

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







|







 







<

>

|












>
>
>
>
>
|
>

|
<
|
|
>
>
>










<
>
|
<
<
|
<







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
840
841
842
843
844
845
846

847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871

872
873
874
875
876
877
878
879
880
881
882
883
884
885
886

887
888


889

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

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
void sqliteCreateView(
  Parse *pParse,     /* The parsing context */
  Token *pBegin,     /* The CREATE token that begins the statement */
  Token *pName,      /* The token that holds the name of the view */
  Select *pSelect,   /* A SELECT statement that will become the new view */
  int isTemp         /* TRUE for a TEMPORARY view */
){

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

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

  }

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


  /* Use sqliteEndTable() to add the view to the SQLITE_MASTER table */


  sqliteEndTable(pParse, &sEnd, 0);

  return;
}

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

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
30
31
32
33
34
35
36

37



38


39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55


56
57







58
59
60
61
62
63
64
..
67
68
69
70
71
72
73








74

75

76
77
78
79
80
81
82
83
84
85
86
87

88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180













181
182
183
184
185













186
187
188
189
190
191
192
193
194
195
196
197







198
199
200
201
202
203
204
...
359
360
361
362
363
364
365



366
367
368
369
370
371
372
...
707
708
709
710
711
712
713

































714
715
716
717
718
719
720
...
721
722
723
724
725
726
727


728
729
730
731
732
733
734


735
736

737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
...
845
846
847
848
849
850
851

852
853
854
855
856
857
858
859
860
861


862
863
864
865
866
867
868
....
1026
1027
1028
1029
1030
1031
1032













1033
1034
1035
1036
1037
1038
1039
....
1064
1065
1066
1067
1068
1069
1070


1071
1072
1073
1074
1075



1076
1077
1078
1079
1080
1081
1082
1083
1084
....
1398
1399
1400
1401
1402
1403
1404

1405





1406

1407
1408
1409
1410
1411
1412
1413
1414
....
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.79 2002/07/18 00:34:12 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
    sqliteExprDelete(pRight);
    return 0;
  }
  pNew->op = op;
  pNew->pLeft = pLeft;
  pNew->pRight = pRight;
  if( pToken ){

    pNew->token = *pToken;



  }else{


    pNew->token.z = 0;
    pNew->token.n = 0;
  }
  if( pLeft && pRight ){
    sqliteExprSpan(pNew, &pLeft->span, &pRight->span);
  }else{
    pNew->span = pNew->token;
  }
  return pNew;
}

/*
** Set the Expr.token field of the given expression to span all
** text between the two given tokens.
*/
void sqliteExprSpan(Expr *pExpr, Token *pLeft, Token *pRight){
  if( pExpr ){


    pExpr->span.z = pLeft->z;
    pExpr->span.n = pRight->n + Addr(pRight->z) - Addr(pLeft->z);







  }
}

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








  if( pToken ){

    pNew->token = *pToken;

  }else{
    pNew->token.z = 0;
    pNew->token.n = 0;
  }
  return pNew;
}

/*
** Recursively delete an expression tree.
*/
void sqliteExprDelete(Expr *p){
  if( p==0 ) return;

  if( p->pLeft ) sqliteExprDelete(p->pLeft);
  if( p->pRight ) sqliteExprDelete(p->pRight);
  if( p->pList ) sqliteExprListDelete(p->pList);
  if( p->pSelect ) sqliteSelectDelete(p->pSelect);
  sqliteFree(p);
}

/*
** The following group of functions are used to translate the string
** pointers of tokens in expression from one buffer to another.
**
** Normally, the Expr.token.z and Expr.span.z fields point into the
** original input buffer of an SQL statement.  This is usually OK
** since the SQL statement is executed and the expression is deleted
** before the input buffer is freed.  Making the tokens point to the
** original input buffer saves many calls to malloc() and thus helps
** the library to run faster. 
**
** But sometimes we need an expression to persist past the time when
** the input buffer is freed.  (Example: The SELECT clause of a
** CREATE VIEW statement contains expressions that must persist for
** the life of the view.)  When that happens we have to make a
** persistent copy of the input buffer and translate the Expr.token.z
** and Expr.span.z fields to point to the copy rather than the 
** original input buffer.  The following group of routines handle that
** translation.
**
** The "offset" parameter is the distance from the original input buffer
** to the persistent copy.  These routines recursively walk the entire
** expression tree and shift all tokens by "offset" amount.
**
** The work of figuring out the appropriate "offset" and making the
** presistent copy of the input buffer is done by the calling routine.
*/
void sqliteExprMoveStrings(Expr *p, int offset){
  if( p==0 ) return;
  if( !p->staticToken ){
    if( p->token.z ) p->token.z += offset;
    if( p->span.z ) p->span.z += offset;
  }
  if( p->pLeft ) sqliteExprMoveStrings(p->pLeft, offset);
  if( p->pRight ) sqliteExprMoveStrings(p->pRight, offset);
  if( p->pList ) sqliteExprListMoveStrings(p->pList, offset);
  if( p->pSelect ) sqliteSelectMoveStrings(p->pSelect, offset);
}
void sqliteExprListMoveStrings(ExprList *pList, int offset){
  int i;
  if( pList==0 ) return;
  for(i=0; i<pList->nExpr; i++){
    sqliteExprMoveStrings(pList->a[i].pExpr, offset);
  }
}
static void sqliteSrcListMoveStrings(SrcList *pSrc, int offset){
  int i;
  if( pSrc==0 ) return;
  for(i=0; i<pSrc->nSrc; i++){
    sqliteSelectMoveStrings(pSrc->a[i].pSelect, offset);
    sqliteExprMoveStrings(pSrc->a[i].pOn, offset);
  }
}
void sqliteSelectMoveStrings(Select *pSelect, int offset){
  if( pSelect==0 ) return;
  sqliteExprListMoveStrings(pSelect->pEList, offset);
  sqliteSrcListMoveStrings(pSelect->pSrc, offset);
  sqliteExprMoveStrings(pSelect->pWhere, offset);
  sqliteExprListMoveStrings(pSelect->pGroupBy, offset);
  sqliteExprMoveStrings(pSelect->pHaving, offset);
  sqliteExprListMoveStrings(pSelect->pOrderBy, offset);
  sqliteSelectMoveStrings(pSelect->pPrior, offset);
}

/*
** The following group of routines make deep copies of expressions,
** expression lists, ID lists, and select statements.  The copies can
** be deleted (by being passed to their respective ...Delete() routines)
** without effecting the originals.
**
** Note, however, that the Expr.token.z and Expr.span.z fields point to
** string space that is allocated separately from the expression tree
** itself.  These routines do NOT duplicate that string space.
**
** The expression list, ID, and source lists return by sqliteExprListDup(),
** sqliteIdListDup(), and sqliteSrcListDup() can not be further expanded 
** by subsequent calls to sqlite*ListAppend() routines.
**
** Any tables that the SrcList might point to are not duplicated.
*/
Expr *sqliteExprDup(Expr *p){
  Expr *pNew;
  if( p==0 ) return 0;
  pNew = sqliteMalloc( sizeof(*p) );
  if( pNew==0 ) return 0;
  memcpy(pNew, p, sizeof(*pNew));













  pNew->pLeft = sqliteExprDup(p->pLeft);
  pNew->pRight = sqliteExprDup(p->pRight);
  pNew->pList = sqliteExprListDup(p->pList);
  pNew->pSelect = sqliteSelectDup(p->pSelect);
  return pNew;













}
ExprList *sqliteExprListDup(ExprList *p){
  ExprList *pNew;
  int i;
  if( p==0 ) return 0;
  pNew = sqliteMalloc( sizeof(*pNew) );
  if( pNew==0 ) return 0;
  pNew->nExpr = p->nExpr;
  pNew->a = sqliteMalloc( p->nExpr*sizeof(p->a[0]) );
  if( pNew->a==0 ) return 0;
  for(i=0; i<p->nExpr; i++){
    pNew->a[i].pExpr = sqliteExprDup(p->a[i].pExpr);







    pNew->a[i].zName = sqliteStrDup(p->a[i].zName);
    pNew->a[i].sortOrder = p->a[i].sortOrder;
    pNew->a[i].isAgg = p->a[i].isAgg;
    pNew->a[i].done = 0;
  }
  return pNew;
}
................................................................................
      while( n>0 && *z && isdigit(*z) ){ z++; n--; }
      if( n==0 ){
        *pValue = atoi(p->token.z);
        return 1;
      }
      break;
    }



    case TK_UMINUS: {
      int v;
      if( sqliteExprIsInteger(p->pLeft, &v) ){
        *pValue = -v;
        return 1;
      }
      break;
................................................................................
          }
        }
      }
    }
  }
  return 0;
}


































/*
** Error check the functions in an expression.  Make sure all
** function names are recognized and all functions have the correct
** number of arguments.  Leave an error message in pParse->zErrMsg
** if anything is amiss.  Return the number of errors.
**
................................................................................
** if pIsAgg is not null and this expression is an aggregate function
** (like count(*) or max(value)) then write a 1 into *pIsAgg.
*/
int sqliteExprCheck(Parse *pParse, Expr *pExpr, int allowAgg, int *pIsAgg){
  int nErr = 0;
  if( pExpr==0 ) return 0;
  switch( pExpr->op ){


    case TK_FUNCTION: {
      int n = pExpr->pList ? pExpr->pList->nExpr : 0;  /* Number of arguments */
      int no_such_func = 0;       /* True if no such function exists */
      int is_type_of = 0;         /* True if is the special TypeOf() function */
      int wrong_num_args = 0;     /* True if wrong number of arguments */
      int is_agg = 0;             /* True if is an aggregate function */
      int i;


      FuncDef *pDef;


      pDef = sqliteFindFunction(pParse->db,
         pExpr->token.z, pExpr->token.n, n, 0);
      if( pDef==0 ){
        pDef = sqliteFindFunction(pParse->db,
           pExpr->token.z, pExpr->token.n, -1, 0);
        if( pDef==0 ){
          if( n==1 && pExpr->token.n==6
               && sqliteStrNICmp(pExpr->token.z, "typeof", 6)==0 ){
            is_type_of = 1;
          }else {
            no_such_func = 1;
          }
        }else{
          wrong_num_args = 1;
        }
      }else{
        is_agg = pDef->xFunc==0;
      }
      if( is_agg && !allowAgg ){
        sqliteSetNString(&pParse->zErrMsg, "misuse of aggregate function ", -1,
           pExpr->token.z, pExpr->token.n, "()", 2, 0);
        pParse->nErr++;
        nErr++;
        is_agg = 0;
      }else if( no_such_func ){
        sqliteSetNString(&pParse->zErrMsg, "no such function: ", -1,
           pExpr->token.z, pExpr->token.n, 0);
        pParse->nErr++;
        nErr++;
      }else if( wrong_num_args ){
        sqliteSetNString(&pParse->zErrMsg, 
           "wrong number of arguments to function ",-1,
           pExpr->token.z, pExpr->token.n, "()", 2, 0);
        pParse->nErr++;
        nErr++;
      }
      if( is_agg ) pExpr->op = TK_AGG_FUNCTION;
      if( is_agg && pIsAgg ) *pIsAgg = 1;
      for(i=0; nErr==0 && i<n; i++){
        nErr = sqliteExprCheck(pParse, pExpr->pList->a[i].pExpr,
................................................................................
    case TK_SLASH:
    case TK_AND:
    case TK_OR:
    case TK_ISNULL:
    case TK_NOTNULL:
    case TK_NOT:
    case TK_UMINUS:

    case TK_BITAND:
    case TK_BITOR:
    case TK_BITNOT:
    case TK_LSHIFT:
    case TK_RSHIFT:
    case TK_REM:
    case TK_INTEGER:
    case TK_FLOAT:
    case TK_IN:
    case TK_BETWEEN:


      return SQLITE_SO_NUM;

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

................................................................................
      break;
    }
    case TK_CONCAT: {
      sqliteExprCode(pParse, pExpr->pLeft);
      sqliteExprCode(pParse, pExpr->pRight);
      sqliteVdbeAddOp(v, OP_Concat, 2, 0);
      break;













    }
    case TK_UMINUS: {
      assert( pExpr->pLeft );
      if( pExpr->pLeft->op==TK_FLOAT || pExpr->pLeft->op==TK_INTEGER ){
        Token *p = &pExpr->pLeft->token;
        char *z = sqliteMalloc( p->n + 2 );
        sprintf(z, "-%.*s", p->n, p->z);
................................................................................
      sqliteVdbeAddOp(v, OP_AddImm, -1, 0);
      break;
    }
    case TK_AGG_FUNCTION: {
      sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg);
      break;
    }


    case TK_FUNCTION: {
      int i;
      ExprList *pList = pExpr->pList;
      int nExpr = pList ? pList->nExpr : 0;
      FuncDef *pDef;



      pDef = sqliteFindFunction(pParse->db,
                      pExpr->token.z, pExpr->token.n, nExpr, 0);
      assert( pDef!=0 );
      for(i=0; i<nExpr; i++){
        sqliteExprCode(pParse, pList->a[i].pExpr);
      }
      sqliteVdbeAddOp(v, OP_Function, nExpr, 0);
      sqliteVdbeChangeP3(v, -1, (char*)pDef, P3_POINTER);
      break;
................................................................................
    }
  }else if( pB->pList ){
    return 0;
  }
  if( pA->pSelect || pB->pSelect ) return 0;
  if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 0;
  if( pA->token.z ){

    if( pB->token.z==0 ) return 0;





    if( pB->token.n!=pA->token.n ) return 0;

    if( sqliteStrNICmp(pA->token.z, pB->token.z, pA->token.n)!=0 ) return 0;
  }
  return 1;
}

/*
** Add a new element to the pParse->aAgg[] array and return its index.
*/
................................................................................
      }
      if( i>=pParse->nAgg ){
        i = appendAggInfo(pParse);
        if( i<0 ) return 1;
        pParse->aAgg[i].isAgg = 1;
        pParse->aAgg[i].pExpr = pExpr;
        pParse->aAgg[i].pFunc = sqliteFindFunction(pParse->db,
             pExpr->token.z, pExpr->token.n,
             pExpr->pList ? pExpr->pList->nExpr : 0, 0);
      }
      pExpr->iAgg = i;
      break;
    }
    default: {
      if( pExpr->pLeft ){







|







 







>

>
>
>

>
>



<
<
<
<
<









>
>
|
|
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>

>

>












>







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<






<
<
<
<












>
>
>
>
>
>
>
>
>
>
>
>
>





>
>
>
>
>
>
>
>
>
>
>
>
>











|
>
>
>
>
>
>
>







 







>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>







>
>


>
|
<

|
<

<
|












|




|
<




|
<







 







>










>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>





>
>
>
|
<







 







>

>
>
>
>
>
|
>
|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47





48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
..
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
































116































117
118
119
120
121
122




123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
...
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
...
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
...
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765

766
767

768

769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787

788
789
790
791
792

793
794
795
796
797
798
799
...
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
....
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
....
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120

1121
1122
1123
1124
1125
1126
1127
....
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
....
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.80 2002/08/24 18:24:54 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
    sqliteExprDelete(pRight);
    return 0;
  }
  pNew->op = op;
  pNew->pLeft = pLeft;
  pNew->pRight = pRight;
  if( pToken ){
    assert( pToken->dyn==0 );
    pNew->token = *pToken;
    pNew->token.base = 1;
  }else if( pLeft && pRight ){
    sqliteExprSpan(pNew, &pLeft->token, &pRight->token);
  }else{
    pNew->token.dyn = 0;
    pNew->token.base = 1;
    pNew->token.z = 0;
    pNew->token.n = 0;
  }





  return pNew;
}

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

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

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

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

































































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




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

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

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

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

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

        if( pDef==0 ){

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

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

        pParse->nErr++;
        nErr++;
      }
      if( is_agg ) pExpr->op = TK_AGG_FUNCTION;
      if( is_agg && pIsAgg ) *pIsAgg = 1;
      for(i=0; nErr==0 && i<n; i++){
        nErr = sqliteExprCheck(pParse, pExpr->pList->a[i].pExpr,
................................................................................
    case TK_SLASH:
    case TK_AND:
    case TK_OR:
    case TK_ISNULL:
    case TK_NOTNULL:
    case TK_NOT:
    case TK_UMINUS:
    case TK_UPLUS:
    case TK_BITAND:
    case TK_BITOR:
    case TK_BITNOT:
    case TK_LSHIFT:
    case TK_RSHIFT:
    case TK_REM:
    case TK_INTEGER:
    case TK_FLOAT:
    case TK_IN:
    case TK_BETWEEN:
    case TK_GLOB:
    case TK_LIKE:
      return SQLITE_SO_NUM;

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

................................................................................
      break;
    }
    case TK_CONCAT: {
      sqliteExprCode(pParse, pExpr->pLeft);
      sqliteExprCode(pParse, pExpr->pRight);
      sqliteVdbeAddOp(v, OP_Concat, 2, 0);
      break;
    }
    case TK_UPLUS: {
      Expr *pLeft = pExpr->pLeft;
      if( pLeft && pLeft->op==TK_INTEGER ){
        sqliteVdbeAddOp(v, OP_Integer, atoi(pLeft->token.z), 0);
        sqliteVdbeChangeP3(v, -1, pLeft->token.z, pLeft->token.n);
      }else if( pLeft && pLeft->op==TK_FLOAT ){
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        sqliteVdbeChangeP3(v, -1, pLeft->token.z, pLeft->token.n);
      }else{
        sqliteExprCode(pParse, pExpr->pLeft);
      }
      break;
    }
    case TK_UMINUS: {
      assert( pExpr->pLeft );
      if( pExpr->pLeft->op==TK_FLOAT || pExpr->pLeft->op==TK_INTEGER ){
        Token *p = &pExpr->pLeft->token;
        char *z = sqliteMalloc( p->n + 2 );
        sprintf(z, "-%.*s", p->n, p->z);
................................................................................
      sqliteVdbeAddOp(v, OP_AddImm, -1, 0);
      break;
    }
    case TK_AGG_FUNCTION: {
      sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg);
      break;
    }
    case TK_GLOB:
    case TK_LIKE:
    case TK_FUNCTION: {
      int i;
      ExprList *pList = pExpr->pList;
      int nExpr = pList ? pList->nExpr : 0;
      FuncDef *pDef;
      int nId;
      const char *zId;
      getFunctionName(pExpr, &zId, &nId);
      pDef = sqliteFindFunction(pParse->db, zId, nId, nExpr, 0);

      assert( pDef!=0 );
      for(i=0; i<nExpr; i++){
        sqliteExprCode(pParse, pList->a[i].pExpr);
      }
      sqliteVdbeAddOp(v, OP_Function, nExpr, 0);
      sqliteVdbeChangeP3(v, -1, (char*)pDef, P3_POINTER);
      break;
................................................................................
    }
  }else if( pB->pList ){
    return 0;
  }
  if( pA->pSelect || pB->pSelect ) return 0;
  if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 0;
  if( pA->token.z ){
    int n;
    if( pB->token.z==0 ) return 0;
    if( pA->op==TK_FUNCTION || pA->op==TK_AGG_FUNCTION ){
      n = pA->nFuncName;
      if( pB->nFuncName!=n ) return 0;
    }else{
      n = pA->token.n;
      if( pB->token.n!=n ) return 0;
    }
    if( sqliteStrNICmp(pA->token.z, pB->token.z, n)!=0 ) return 0;
  }
  return 1;
}

/*
** Add a new element to the pParse->aAgg[] array and return its index.
*/
................................................................................
      }
      if( i>=pParse->nAgg ){
        i = appendAggInfo(pParse);
        if( i<0 ) return 1;
        pParse->aAgg[i].isAgg = 1;
        pParse->aAgg[i].pExpr = pExpr;
        pParse->aAgg[i].pFunc = sqliteFindFunction(pParse->db,
             pExpr->token.z, pExpr->nFuncName,
             pExpr->pList ? pExpr->pList->nExpr : 0, 0);
      }
      pExpr->iAgg = i;
      break;
    }
    default: {
      if( pExpr->pLeft ){

Changes to src/main.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
791
792
793
794
795
796
797

798


799
800
801
802
803
804
805
806
...
809
810
811
812
813
814
815

816


817
818
819
820
821
822
823
824
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.97 2002/08/13 23:02:57 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** A pointer to this structure is used to communicate information
................................................................................
  sqlite *db,          /* Add the function to this database connection */
  const char *zName,   /* Name of the function to add */
  int nArg,            /* Number of arguments */
  void (*xFunc)(sqlite_func*,int,const char**),  /* The implementation */
  void *pUserData      /* User data */
){
  FuncDef *p;

  if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1;


  p = sqliteFindFunction(db, zName, strlen(zName), nArg, 1);
  if( p==0 ) return 1;
  p->xFunc = xFunc;
  p->xStep = 0;
  p->xFinalize = 0;
  p->pUserData = pUserData;
  return 0;
}
................................................................................
  const char *zName,   /* Name of the function to add */
  int nArg,            /* Number of arguments */
  void (*xStep)(sqlite_func*,int,const char**), /* The step function */
  void (*xFinalize)(sqlite_func*),              /* The finalizer */
  void *pUserData      /* User data */
){
  FuncDef *p;

  if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1;


  p = sqliteFindFunction(db, zName, strlen(zName), nArg, 1);
  if( p==0 ) return 1;
  p->xFunc = 0;
  p->xStep = xStep;
  p->xFinalize = xFinalize;
  p->pUserData = pUserData;
  return 0;
}







|







 







>

>
>
|







 







>

>
>
|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
...
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.98 2002/08/24 18:24:54 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** A pointer to this structure is used to communicate information
................................................................................
  sqlite *db,          /* Add the function to this database connection */
  const char *zName,   /* Name of the function to add */
  int nArg,            /* Number of arguments */
  void (*xFunc)(sqlite_func*,int,const char**),  /* The implementation */
  void *pUserData      /* User data */
){
  FuncDef *p;
  int nName;
  if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1;
  nName = strlen(zName);
  if( nName>255 ) return 1;
  p = sqliteFindFunction(db, zName, nName, nArg, 1);
  if( p==0 ) return 1;
  p->xFunc = xFunc;
  p->xStep = 0;
  p->xFinalize = 0;
  p->pUserData = pUserData;
  return 0;
}
................................................................................
  const char *zName,   /* Name of the function to add */
  int nArg,            /* Number of arguments */
  void (*xStep)(sqlite_func*,int,const char**), /* The step function */
  void (*xFinalize)(sqlite_func*),              /* The finalizer */
  void *pUserData      /* User data */
){
  FuncDef *p;
  int nName;
  if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1;
  nName = strlen(zName);
  if( nName>255 ) return 1;
  p = sqliteFindFunction(db, zName, nName, nArg, 1);
  if( p==0 ) return 1;
  p->xFunc = 0;
  p->xStep = xStep;
  p->xFinalize = xFinalize;
  p->pUserData = pUserData;
  return 0;
}

Changes to src/parse.y.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
...
502
503
504
505
506
507
508

509
510
511
512

513
514
515
516
517
518
519
...
522
523
524
525
526
527
528
529

530
531
532
533
534
535

536
537
538

539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
...
713
714
715
716
717
718
719



720
721
722
723
724
725
726
727
728
...
765
766
767
768
769
770
771
772


773

774
775
776


777
778

779

780
781

782

783
784
785
786
787
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.81 2002/08/18 22:41:22 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
%right NOT.
%left EQ NE ISNULL NOTNULL IS LIKE GLOB BETWEEN IN.
%left GT GE LT LE.
%left BITAND BITOR LSHIFT RSHIFT.
%left PLUS MINUS.
%left STAR SLASH REM.
%left CONCAT.
%right UMINUS BITNOT.

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

expr(A) ::= LP(B) expr(X) RP(E). {A = X; sqliteExprSpan(A,&B,&E);}
expr(A) ::= NULL(X).             {A = sqliteExpr(TK_NULL, 0, 0, &X);}
expr(A) ::= ID(X).               {A = sqliteExpr(TK_ID, 0, 0, &X);}
................................................................................
}
expr(A) ::= INTEGER(X).      {A = sqliteExpr(TK_INTEGER, 0, 0, &X);}
expr(A) ::= FLOAT(X).        {A = sqliteExpr(TK_FLOAT, 0, 0, &X);}
expr(A) ::= STRING(X).       {A = sqliteExpr(TK_STRING, 0, 0, &X);}
expr(A) ::= ID(X) LP exprlist(Y) RP(E). {
  A = sqliteExprFunction(Y, &X);
  sqliteExprSpan(A,&X,&E);

}
expr(A) ::= ID(X) LP STAR RP(E). {
  A = sqliteExprFunction(0, &X);
  sqliteExprSpan(A,&X,&E);

}
expr(A) ::= expr(X) AND expr(Y).   {A = sqliteExpr(TK_AND, X, Y, 0);}
expr(A) ::= expr(X) OR expr(Y).    {A = sqliteExpr(TK_OR, X, Y, 0);}
expr(A) ::= expr(X) LT expr(Y).    {A = sqliteExpr(TK_LT, X, Y, 0);}
expr(A) ::= expr(X) GT expr(Y).    {A = sqliteExpr(TK_GT, X, Y, 0);}
expr(A) ::= expr(X) LE expr(Y).    {A = sqliteExpr(TK_LE, X, Y, 0);}
expr(A) ::= expr(X) GE expr(Y).    {A = sqliteExpr(TK_GE, X, Y, 0);}
................................................................................
expr(A) ::= expr(X) BITAND expr(Y). {A = sqliteExpr(TK_BITAND, X, Y, 0);}
expr(A) ::= expr(X) BITOR expr(Y).  {A = sqliteExpr(TK_BITOR, X, Y, 0);}
expr(A) ::= expr(X) LSHIFT expr(Y). {A = sqliteExpr(TK_LSHIFT, X, Y, 0);}
expr(A) ::= expr(X) RSHIFT expr(Y). {A = sqliteExpr(TK_RSHIFT, X, Y, 0);}
expr(A) ::= expr(X) likeop(OP) expr(Y).  [LIKE]  {
  ExprList *pList = sqliteExprListAppend(0, Y, 0);
  pList = sqliteExprListAppend(pList, X, 0);
  A = sqliteExprFunction(pList, &OP);

  sqliteExprSpan(A, &X->span, &Y->span);
}
expr(A) ::= expr(X) NOT likeop(OP) expr(Y). [LIKE] {
  ExprList *pList = sqliteExprListAppend(0, Y, 0);
  pList = sqliteExprListAppend(pList, X, 0);
  A = sqliteExprFunction(pList, &OP);

  A = sqliteExpr(TK_NOT, A, 0, 0);
  sqliteExprSpan(A,&X->span,&Y->span);
}

likeop(A) ::= LIKE(X). {A = X;}
likeop(A) ::= GLOB(X). {A = X;}
expr(A) ::= expr(X) PLUS expr(Y).  {A = sqliteExpr(TK_PLUS, X, Y, 0);}
expr(A) ::= expr(X) MINUS expr(Y). {A = sqliteExpr(TK_MINUS, X, Y, 0);}
expr(A) ::= expr(X) STAR expr(Y).  {A = sqliteExpr(TK_STAR, X, Y, 0);}
expr(A) ::= expr(X) SLASH expr(Y). {A = sqliteExpr(TK_SLASH, X, Y, 0);}
expr(A) ::= expr(X) REM expr(Y).   {A = sqliteExpr(TK_REM, X, Y, 0);}
expr(A) ::= expr(X) CONCAT expr(Y). {A = sqliteExpr(TK_CONCAT, X, Y, 0);}
expr(A) ::= expr(X) ISNULL(E). {
  A = sqliteExpr(TK_ISNULL, X, 0, 0);
  sqliteExprSpan(A,&X->span,&E);
}
expr(A) ::= expr(X) IS NULL(E). {
  A = sqliteExpr(TK_ISNULL, X, 0, 0);
  sqliteExprSpan(A,&X->span,&E);
}
expr(A) ::= expr(X) NOTNULL(E). {
  A = sqliteExpr(TK_NOTNULL, X, 0, 0);
  sqliteExprSpan(A,&X->span,&E);
}
expr(A) ::= expr(X) NOT NULL(E). {
  A = sqliteExpr(TK_NOTNULL, X, 0, 0);
  sqliteExprSpan(A,&X->span,&E);
}
expr(A) ::= expr(X) IS NOT NULL(E). {
  A = sqliteExpr(TK_NOTNULL, X, 0, 0);
  sqliteExprSpan(A,&X->span,&E);
}
expr(A) ::= NOT(B) expr(X). {
  A = sqliteExpr(TK_NOT, X, 0, 0);
  sqliteExprSpan(A,&B,&X->span);
}
expr(A) ::= BITNOT(B) expr(X). {
  A = sqliteExpr(TK_BITNOT, X, 0, 0);
  sqliteExprSpan(A,&B,&X->span);
}
expr(A) ::= MINUS(B) expr(X). [UMINUS] {
  A = sqliteExpr(TK_UMINUS, X, 0, 0);
  sqliteExprSpan(A,&B,&X->span);
}
expr(A) ::= PLUS(B) expr(X). [UMINUS] {
  A = X;
  sqliteExprSpan(A,&B,&X->span);
}
expr(A) ::= LP(B) select(X) RP(E). {
  A = sqliteExpr(TK_SELECT, 0, 0, 0);
  if( A ) A->pSelect = X;
  sqliteExprSpan(A,&B,&E);
}
expr(A) ::= expr(W) BETWEEN expr(X) AND expr(Y). {
  ExprList *pList = sqliteExprListAppend(0, X, 0);
  pList = sqliteExprListAppend(pList, Y, 0);
  A = sqliteExpr(TK_BETWEEN, W, 0, 0);
  if( A ) A->pList = pList;
  sqliteExprSpan(A,&W->span,&Y->span);
}
expr(A) ::= expr(W) NOT BETWEEN expr(X) AND expr(Y). {
  ExprList *pList = sqliteExprListAppend(0, X, 0);
  pList = sqliteExprListAppend(pList, Y, 0);
  A = sqliteExpr(TK_BETWEEN, W, 0, 0);
  if( A ) A->pList = pList;
  A = sqliteExpr(TK_NOT, A, 0, 0);
  sqliteExprSpan(A,&W->span,&Y->span);
}
expr(A) ::= expr(X) IN LP exprlist(Y) RP(E).  {
  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pList = Y;
  sqliteExprSpan(A,&X->span,&E);
}
expr(A) ::= expr(X) IN LP select(Y) RP(E).  {
  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pSelect = Y;
  sqliteExprSpan(A,&X->span,&E);
}
expr(A) ::= expr(X) NOT IN LP exprlist(Y) RP(E).  {
  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pList = Y;
  A = sqliteExpr(TK_NOT, A, 0, 0);
  sqliteExprSpan(A,&X->span,&E);
}
expr(A) ::= expr(X) NOT IN LP select(Y) RP(E).  {
  A = sqliteExpr(TK_IN, X, 0, 0);
  if( A ) A->pSelect = Y;
  A = sqliteExpr(TK_NOT, A, 0, 0);
  sqliteExprSpan(A,&X->span,&E);
}

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

//////////////////////////// The CREATE TRIGGER command /////////////////////
cmd ::= CREATE(A) TRIGGER nm(B) trigger_time(C) trigger_event(D) ON nm(E) 
                  foreach_clause(F) when_clause(G)
                  BEGIN trigger_cmd_list(S) END(Z). {



  sqliteCreateTrigger(pParse, &B, C, D.a, D.b, &E, F, G, S, 
      A.z, (int)(Z.z - A.z) + Z.n );
}

%type trigger_time  {int}
trigger_time(A) ::= BEFORE.      { A = TK_BEFORE; }
trigger_time(A) ::= AFTER.       { A = TK_AFTER;  }
trigger_time(A) ::= INSTEAD OF.  { A = TK_INSTEAD;}
trigger_time(A) ::= .            { A = TK_BEFORE; }
................................................................................
trigger_cmd(A) ::= DELETE FROM nm(X) where_opt(Y).
               {A = sqliteTriggerDeleteStep(&X, Y);}

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

// The special RAISE expression that may occur in trigger programs
expr(A) ::= RAISE(X) LP IGNORE RP(Y).  { A = sqliteExpr(TK_RAISE, 0, 0, 0); 


    A->iColumn = OE_Ignore; sqliteExprSpan(A, &X, &Y);}

expr(A) ::= RAISE(X) LP ROLLBACK COMMA nm(Z) RP(Y).  
{ A = sqliteExpr(TK_RAISE, 0, 0, &Z); 
    A->iColumn = OE_Rollback; sqliteExprSpan(A, &X, &Y);}


expr(A) ::= RAISE(X) LP ABORT COMMA nm(Z) RP(Y).  
{ A = sqliteExpr(TK_RAISE, 0, 0, &Z); 

    A->iColumn = OE_Abort; sqliteExprSpan(A, &X, &Y);}

expr(A) ::= RAISE(X) LP FAIL COMMA nm(Z) RP(Y).  
{ A = sqliteExpr(TK_RAISE, 0, 0, &Z); 

    A->iColumn = OE_Fail; sqliteExprSpan(A, &X, &Y);}


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







|







 







|







 







>




>







 







|
>
|




|
>

|

>
|
|








|



|



|



|



|



|



|



|

|
|
|











|







|




|




|





|





|







 







>
>
>
|
<







 







|
>
>
|
>
|
|
|
>
>
|
|
>
|
>
|
|
>
|
>





10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
...
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
...
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
...
718
719
720
721
722
723
724
725
726
727
728

729
730
731
732
733
734
735
...
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.82 2002/08/24 18:24:54 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
%right NOT.
%left EQ NE ISNULL NOTNULL IS LIKE GLOB BETWEEN IN.
%left GT GE LT LE.
%left BITAND BITOR LSHIFT RSHIFT.
%left PLUS MINUS.
%left STAR SLASH REM.
%left CONCAT.
%right UMINUS UPLUS BITNOT.

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

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

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

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

}

%type trigger_time  {int}
trigger_time(A) ::= BEFORE.      { A = TK_BEFORE; }
trigger_time(A) ::= AFTER.       { A = TK_AFTER;  }
trigger_time(A) ::= INSTEAD OF.  { A = TK_INSTEAD;}
trigger_time(A) ::= .            { A = TK_BEFORE; }
................................................................................
trigger_cmd(A) ::= DELETE FROM nm(X) where_opt(Y).
               {A = sqliteTriggerDeleteStep(&X, Y);}

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

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

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

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
152
153
154
155
156
157
158


159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
...
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
...
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
...
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
...
891
892
893
894
895
896
897


898
899

900
901
902
903
904






905
906
907
908
909
910
911
912
913
914
...
941
942
943
944
945
946
947

948
949

950
951
952
953
954
955
956
....
1305
1306
1307
1308
1309
1310
1311
1312

1313
1314
1315
1316
1317
1318
1319
....
1424
1425
1426
1427
1428
1429
1430

1431
1432
1433
1434
1435
1436
1437
1438
....
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.107 2002/08/04 00:52:38 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
  Token dummy;
  Expr *pE1a, *pE1b, *pE1c;
  Expr *pE2a, *pE2b, *pE2c;
  Expr *pE;

  dummy.z = zCol;
  dummy.n = strlen(zCol);


  pE1a = sqliteExpr(TK_ID, 0, 0, &dummy);
  pE1a->staticToken = 1;
  pE2a = sqliteExpr(TK_ID, 0, 0, &dummy);
  pE2a->staticToken = 1;
  dummy.z = pTab1->zName;
  dummy.n = strlen(dummy.z);
  pE1b = sqliteExpr(TK_ID, 0, 0, &dummy);
  pE1b->staticToken = 1;
  dummy.z = pTab2->zName;
  dummy.n = strlen(dummy.z);
  pE2b = sqliteExpr(TK_ID, 0, 0, &dummy);
  pE2b->staticToken = 1;
  pE1c = sqliteExpr(TK_DOT, pE1b, pE1a, 0);
  pE2c = sqliteExpr(TK_DOT, pE2b, pE2a, 0);
  pE = sqliteExpr(TK_EQ, pE1c, pE2c, 0);
  pE->isJoinExpr = 1;
  if( *ppExpr ){
    *ppExpr = sqliteExpr(TK_AND, *ppExpr, pE, 0);
  }else{
................................................................................
      if( iCol<0 ){
        zCol = "_ROWID_";
        zType = "INTEGER";
      }else{
        zCol = pTab->aCol[iCol].zName;
        zType = pTab->aCol[iCol].zType;
      }
      if( p->span.z && p->span.z[0] && !showFullNames ){
        int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
        sqliteVdbeCompressSpace(v, addr);
      }else if( pTabList->nSrc>1 || showFullNames ){
        char *zName = 0;
        char *zTab;
 
        zTab = pTabList->a[p->iTable - base].zAlias;
        if( showFullNames || zTab==0 ) zTab = pTab->zName;
................................................................................
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
        sqliteFree(zName);
      }else{
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, zCol, 0);
      }
    }else if( p->span.z && p->span.z[0] && !showFullNames ){
      int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
      sqliteVdbeCompressSpace(v, addr);
    }else if( p->span.z && p->span.z[0] ){
      int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
      sqliteVdbeCompressSpace(v, addr);
    }else{
      char zName[30];
      assert( p->op!=TK_COLUMN || pTabList==0 );
      sprintf(zName, "column%d", i+1);
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
................................................................................
  pTab->nCol = pEList->nExpr;
  assert( pTab->nCol>0 );
  pTab->aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
  for(i=0; i<pTab->nCol; i++){
    Expr *p;
    if( pEList->a[i].zName ){
      pTab->aCol[i].zName = sqliteStrDup(pEList->a[i].zName);
    }else if( (p=pEList->a[i].pExpr)->span.z && p->span.z[0] ){
      sqliteSetNString(&pTab->aCol[i].zName, p->span.z, p->span.n, 0);
    }else if( p->op==TK_DOT && p->pRight && p->pRight->token.z &&
           p->pRight->token.z[0] ){
      sqliteSetNString(&pTab->aCol[i].zName, 
           p->pRight->token.z, p->pRight->token.n, 0);
    }else{
      char zBuf[30];
      sprintf(zBuf, "column%d", i+1);
................................................................................
              ** using clause from the table on the right. */
              continue;
            }
            pRight = sqliteExpr(TK_ID, 0, 0, 0);
            if( pRight==0 ) break;
            pRight->token.z = zName;
            pRight->token.n = strlen(zName);


            if( zTabName ){
              pLeft = sqliteExpr(TK_ID, 0, 0, 0);

              if( pLeft==0 ) break;
              pLeft->token.z = zTabName;
              pLeft->token.n = strlen(zTabName);
              pExpr = sqliteExpr(TK_DOT, pLeft, pRight, 0);
              if( pExpr==0 ) break;






            }else{
              pExpr = pRight;
              pExpr->span = pExpr->token;
            }
            pNew = sqliteExprListAppend(pNew, pExpr, 0);
          }
        }
        if( !tableSeen ){
          if( pName ){
            sqliteSetNString(&pParse->zErrMsg, "no such table: ", -1, 
................................................................................
  SrcList *pSrc = p->pSrc;
  Table *pTab;
  if( p==0 ) return;
  for(i=0; i<pSrc->nSrc; i++){
    if( (pTab = pSrc->a[i].pTab)!=0 ){
      if( pTab->isTransient ){
        sqliteDeleteTable(0, pTab);

        sqliteSelectDelete(pSrc->a[i].pSelect);
        pSrc->a[i].pSelect = 0;

      }
      pSrc->a[i].pTab = 0;
      if( pSrc->a[i].pSelect ){
        sqliteSelectUnbind(pSrc->a[i].pSelect);
      }
    }
  }
................................................................................
    pExpr->op = pNew->op;
    pExpr->pLeft = sqliteExprDup(pNew->pLeft);
    pExpr->pRight = sqliteExprDup(pNew->pRight);
    pExpr->pList = sqliteExprListDup(pNew->pList);
    pExpr->iTable = pNew->iTable;
    pExpr->iColumn = pNew->iColumn;
    pExpr->iAgg = pNew->iAgg;
    pExpr->token = pNew->token;

    if( iSub!=iTable ){
      changeTables(pExpr, iSub, iTable);
    }
  }else{
    static void substExprList(ExprList*,int,ExprList*,int);
    substExpr(pExpr->pLeft, iTable, pEList, iSub);
    substExpr(pExpr->pRight, iTable, pEList, iSub);
................................................................................
  iParent = p->base + iFrom;
  iSub = pSub->base;
  substExprList(p->pEList, iParent, pSub->pEList, iSub);
  pList = p->pEList;
  for(i=0; i<pList->nExpr; i++){
    if( pList->a[i].zName==0 ){
      Expr *pExpr = pList->a[i].pExpr;

      pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
    }
  }
  if( isAgg ){
    substExprList(p->pGroupBy, iParent, pSub->pEList, iSub);
    substExpr(p->pHaving, iParent, pSub->pEList, iSub);
  }
  substExprList(p->pOrderBy, iParent, pSub->pEList, iSub);
................................................................................
  */
  if( p->pGroupBy || p->pHaving || p->pWhere ) return 0;
  if( p->pSrc->nSrc!=1 ) return 0;
  if( p->pEList->nExpr!=1 ) return 0;
  pExpr = p->pEList->a[0].pExpr;
  if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
  if( pExpr->pList==0 || pExpr->pList->nExpr!=1 ) return 0;
  if( pExpr->token.n!=3 ) return 0;
  if( sqliteStrNICmp(pExpr->token.z,"min",3)==0 ){
    seekOp = OP_Rewind;
  }else if( sqliteStrNICmp(pExpr->token.z,"max",3)==0 ){
    seekOp = OP_Last;
  }else{
    return 0;
  }







|







 







>
>

<

<



<



<







 







|

|







 







|

|

|

|







 







|
|







 







>
>
|

>
|


<
<
>
>
>
>
>
>


<







 







>


>







 







|
>







 







>
|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
152
153
154
155
156
157
158
159
160
161

162

163
164
165

166
167
168

169
170
171
172
173
174
175
...
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
...
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
...
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
...
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903


904
905
906
907
908
909
910
911

912
913
914
915
916
917
918
...
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
....
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
....
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
....
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.108 2002/08/24 18:24:54 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
  Token dummy;
  Expr *pE1a, *pE1b, *pE1c;
  Expr *pE2a, *pE2b, *pE2c;
  Expr *pE;

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

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

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

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

  pE1c = sqliteExpr(TK_DOT, pE1b, pE1a, 0);
  pE2c = sqliteExpr(TK_DOT, pE2b, pE2a, 0);
  pE = sqliteExpr(TK_EQ, pE1c, pE2c, 0);
  pE->isJoinExpr = 1;
  if( *ppExpr ){
    *ppExpr = sqliteExpr(TK_AND, *ppExpr, pE, 0);
  }else{
................................................................................
      if( iCol<0 ){
        zCol = "_ROWID_";
        zType = "INTEGER";
      }else{
        zCol = pTab->aCol[iCol].zName;
        zType = pTab->aCol[iCol].zType;
      }
      if( p->token.z && p->token.z[0] && !showFullNames ){
        int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, p->token.z, p->token.n);
        sqliteVdbeCompressSpace(v, addr);
      }else if( pTabList->nSrc>1 || showFullNames ){
        char *zName = 0;
        char *zTab;
 
        zTab = pTabList->a[p->iTable - base].zAlias;
        if( showFullNames || zTab==0 ) zTab = pTab->zName;
................................................................................
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
        sqliteFree(zName);
      }else{
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, zCol, 0);
      }
    }else if( p->token.z && p->token.z[0] && !showFullNames ){
      int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, p->token.z, p->token.n);
      sqliteVdbeCompressSpace(v, addr);
    }else if( p->token.z && p->token.z[0] ){
      int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, p->token.z, p->token.n);
      sqliteVdbeCompressSpace(v, addr);
    }else{
      char zName[30];
      assert( p->op!=TK_COLUMN || pTabList==0 );
      sprintf(zName, "column%d", i+1);
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
................................................................................
  pTab->nCol = pEList->nExpr;
  assert( pTab->nCol>0 );
  pTab->aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
  for(i=0; i<pTab->nCol; i++){
    Expr *p;
    if( pEList->a[i].zName ){
      pTab->aCol[i].zName = sqliteStrDup(pEList->a[i].zName);
    }else if( (p=pEList->a[i].pExpr)->token.z && p->token.z[0] ){
      sqliteSetNString(&pTab->aCol[i].zName, p->token.z, p->token.n, 0);
    }else if( p->op==TK_DOT && p->pRight && p->pRight->token.z &&
           p->pRight->token.z[0] ){
      sqliteSetNString(&pTab->aCol[i].zName, 
           p->pRight->token.z, p->pRight->token.n, 0);
    }else{
      char zBuf[30];
      sprintf(zBuf, "column%d", i+1);
................................................................................
              ** using clause from the table on the right. */
              continue;
            }
            pRight = sqliteExpr(TK_ID, 0, 0, 0);
            if( pRight==0 ) break;
            pRight->token.z = zName;
            pRight->token.n = strlen(zName);
            pRight->token.dyn = 0;
            pRight->token.base = 1;
            if( zTabName && pTabList->nSrc>1 ){
              pLeft = sqliteExpr(TK_ID, 0, 0, 0);
              pExpr = sqliteExpr(TK_DOT, pLeft, pRight, 0);
              if( pExpr==0 ) break;
              pLeft->token.z = zTabName;
              pLeft->token.n = strlen(zTabName);


              pLeft->token.dyn = 0;
              pLeft->token.base = 1;
              sqliteSetString((char**)&pExpr->token.z, zTabName, ".", zName, 0);
              pExpr->token.n = strlen(pExpr->token.z);
              pExpr->token.base = 0;
              pExpr->token.dyn = 1;
            }else{
              pExpr = pRight;

            }
            pNew = sqliteExprListAppend(pNew, pExpr, 0);
          }
        }
        if( !tableSeen ){
          if( pName ){
            sqliteSetNString(&pParse->zErrMsg, "no such table: ", -1, 
................................................................................
  SrcList *pSrc = p->pSrc;
  Table *pTab;
  if( p==0 ) return;
  for(i=0; i<pSrc->nSrc; i++){
    if( (pTab = pSrc->a[i].pTab)!=0 ){
      if( pTab->isTransient ){
        sqliteDeleteTable(0, pTab);
#if 0
        sqliteSelectDelete(pSrc->a[i].pSelect);
        pSrc->a[i].pSelect = 0;
#endif
      }
      pSrc->a[i].pTab = 0;
      if( pSrc->a[i].pSelect ){
        sqliteSelectUnbind(pSrc->a[i].pSelect);
      }
    }
  }
................................................................................
    pExpr->op = pNew->op;
    pExpr->pLeft = sqliteExprDup(pNew->pLeft);
    pExpr->pRight = sqliteExprDup(pNew->pRight);
    pExpr->pList = sqliteExprListDup(pNew->pList);
    pExpr->iTable = pNew->iTable;
    pExpr->iColumn = pNew->iColumn;
    pExpr->iAgg = pNew->iAgg;
    pExpr->nFuncName = pNew->nFuncName;
    sqliteTokenCopy(&pExpr->token, &pNew->token);
    if( iSub!=iTable ){
      changeTables(pExpr, iSub, iTable);
    }
  }else{
    static void substExprList(ExprList*,int,ExprList*,int);
    substExpr(pExpr->pLeft, iTable, pEList, iSub);
    substExpr(pExpr->pRight, iTable, pEList, iSub);
................................................................................
  iParent = p->base + iFrom;
  iSub = pSub->base;
  substExprList(p->pEList, iParent, pSub->pEList, iSub);
  pList = p->pEList;
  for(i=0; i<pList->nExpr; i++){
    if( pList->a[i].zName==0 ){
      Expr *pExpr = pList->a[i].pExpr;
      assert( pExpr->token.z!=0 );
      pList->a[i].zName = sqliteStrNDup(pExpr->token.z, pExpr->token.n);
    }
  }
  if( isAgg ){
    substExprList(p->pGroupBy, iParent, pSub->pEList, iSub);
    substExpr(p->pHaving, iParent, pSub->pEList, iSub);
  }
  substExprList(p->pOrderBy, iParent, pSub->pEList, iSub);
................................................................................
  */
  if( p->pGroupBy || p->pHaving || p->pWhere ) return 0;
  if( p->pSrc->nSrc!=1 ) return 0;
  if( p->pEList->nExpr!=1 ) return 0;
  pExpr = p->pEList->a[0].pExpr;
  if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
  if( pExpr->pList==0 || pExpr->pList->nExpr!=1 ) return 0;
  if( pExpr->nFuncName!=3 ) return 0;
  if( sqliteStrNICmp(pExpr->token.z,"min",3)==0 ){
    seekOp = OP_Rewind;
  }else if( sqliteStrNICmp(pExpr->token.z,"max",3)==0 ){
    seekOp = OP_Last;
  }else{
    return 0;
  }

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
387
388
389
390
391
392
393
394






395
396
397


398
399
400
401
402
403
404
405
...
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
...
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
...
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
...
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
...
916
917
918
919
920
921
922
923
924
925
926

927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.142 2002/08/02 10:36:10 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
  u8 onError;      /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  u8 autoIndex;    /* True if is automatically created (ex: by UNIQUE) */
  Index *pNext;    /* The next index associated with the same table */
};

/*
** Each token coming out of the lexer is an instance of
** this structure.






*/
struct Token {
  const char *z;      /* Text of the token.  Not NULL-terminated! */


  int n;              /* Number of characters in this token */
};

/*
** Each node of an expression in the parse tree is an instance
** of this structure.
**
** Expr.op is the opcode.  The integer parser token codes are reused
................................................................................
** code representing the ">=" operator.  This same integer code is reused
** to represent the greater-than-or-equal-to operator in the expression
** tree.
**
** Expr.pRight and Expr.pLeft are subexpressions.  Expr.pList is a list
** of argument if the expression is a function.
**
** Expr.token is the operator token for this node.  Expr.span is the complete
** subexpression represented by this node and all its decendents.  These
** fields are used for error reporting and for reconstructing the text of
** an expression to use as the column name in a SELECT statement.
**
** An expression of the form ID or ID.ID refers to a column in a table.
** For such expressions, Expr.op is set to TK_COLUMN and Expr.iTable is
** the integer cursor number of a VDBE cursor pointing to that table and
** Expr.iColumn is the column number for the specific column.  If the
** expression is used as a result in an aggregate SELECT, then the
** value is also stored in the Expr.iAgg column in the aggregate so that
................................................................................
** be the right operand of an IN operator.  Or, if a scalar SELECT appears
** in an expression the opcode is TK_SELECT and Expr.pSelect is the only
** operand.
*/
struct Expr {
  u8 op;                 /* Operation performed by this node */
  u8 dataType;           /* Either SQLITE_SO_TEXT or SQLITE_SO_NUM */
  u8 isJoinExpr;         /* Origina is the ON or USING phrase of a join */
  u8 staticToken;        /* Expr.token.z points to static memory */
  Expr *pLeft, *pRight;  /* Left and right subnodes */
  ExprList *pList;       /* A list of expressions used as function arguments
                         ** or in "<expr> IN (<expr-list)" */
  Token token;           /* An operand token */
  Token span;            /* Complete text of the expression */
  int iTable, iColumn;   /* When op==TK_COLUMN, then this expr node means the
                         ** iColumn-th field of the iTable-th table. */
  int iAgg;              /* When op==TK_COLUMN and pParse->useAgg==TRUE, pull
                         ** result from the iAgg-th element of the aggregator */
  Select *pSelect;       /* When the expression is a sub-select.  Also the
                         ** right side of "<expr> IN (<select>)" */
};
................................................................................
 * 1. In the "trigHash" hash table (part of the sqlite* that represents the 
 *    database). This allows Trigger structures to be retrieved by name.
 * 2. All triggers associated with a single table form a linked list, using the
 *    pNext member of struct Trigger. A pointer to the first element of the
 *    linked list is stored as the "pTrigger" member of the associated
 *    struct Table.
 *
 * The "strings" member of struct Trigger contains a pointer to the memory 
 * referenced by the various Token structures referenced indirectly by the
 * "pWhen", "pColumns" and "step_list" members. (ie. the memory allocated for
 * use in conjunction with the sqliteExprMoveStrings() etc. interface).
 *
 * The "step_list" member points to the first element of a linked list
 * containing the SQL statements specified as the trigger program.
 *
 * When a trigger is initially created, the "isCommit" member is set to FALSE.
 * When a transaction is rolled back, any Trigger structures with "isCommit" set
 * to FALSE are deleted by the logic in sqliteRollbackInternalChanges(). When
 * a transaction is commited, the "isCommit" member is set to TRUE for any
................................................................................
  int tr_tm;              /* One of TK_BEFORE, TK_AFTER */
  Expr *pWhen;            /* The WHEN clause of the expresion (may be NULL) */
  IdList *pColumns;       /* If this is an UPDATE OF <column-list> trigger,
                             the <column-list> is stored here */
  int foreach;            /* One of TK_ROW or TK_STATEMENT */

  TriggerStep *step_list; /* Link list of trigger program steps             */
  char *strings;          /* pointer to allocation of Token strings */
  Trigger *pNext;         /* Next trigger associated with the table */
};

/*
 * An instance of struct TriggerStep is used to store a single SQL statement
 * that is a part of a trigger-program. 
 *
................................................................................
int sqliteIsRowid(const char*);
void sqliteGenerateRowDelete(sqlite*, Vdbe*, Table*, int, int);
void sqliteGenerateRowIndexDelete(sqlite*, Vdbe*, Table*, int, char*);
void sqliteGenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int);
void sqliteCompleteInsertion(Parse*, Table*, int, char*, int, int);
void sqliteBeginWriteOperation(Parse*, int);
void sqliteEndWriteOperation(Parse*);
void sqliteExprMoveStrings(Expr*, int);
void sqliteExprListMoveStrings(ExprList*, int);
void sqliteSelectMoveStrings(Select*, int);
Expr *sqliteExprDup(Expr*);

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







|







 







|
>
>
>
>
>
>



>
>
|







 







|
|
|
|







 







|
|




<







 







<
<
<
<
<







 







<







 







<
<
<

>











|











7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
...
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
...
439
440
441
442
443
444
445
446
447
448
449
450
451

452
453
454
455
456
457
458
...
680
681
682
683
684
685
686





687
688
689
690
691
692
693
...
706
707
708
709
710
711
712

713
714
715
716
717
718
719
...
917
918
919
920
921
922
923



924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.143 2002/08/24 18:24:55 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
  u8 onError;      /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  u8 autoIndex;    /* True if is automatically created (ex: by UNIQUE) */
  Index *pNext;    /* The next index associated with the same table */
};

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

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

  int iTable, iColumn;   /* When op==TK_COLUMN, then this expr node means the
                         ** iColumn-th field of the iTable-th table. */
  int iAgg;              /* When op==TK_COLUMN and pParse->useAgg==TRUE, pull
                         ** result from the iAgg-th element of the aggregator */
  Select *pSelect;       /* When the expression is a sub-select.  Also the
                         ** right side of "<expr> IN (<select>)" */
};
................................................................................
 * 1. In the "trigHash" hash table (part of the sqlite* that represents the 
 *    database). This allows Trigger structures to be retrieved by name.
 * 2. All triggers associated with a single table form a linked list, using the
 *    pNext member of struct Trigger. A pointer to the first element of the
 *    linked list is stored as the "pTrigger" member of the associated
 *    struct Table.
 *





 * The "step_list" member points to the first element of a linked list
 * containing the SQL statements specified as the trigger program.
 *
 * When a trigger is initially created, the "isCommit" member is set to FALSE.
 * When a transaction is rolled back, any Trigger structures with "isCommit" set
 * to FALSE are deleted by the logic in sqliteRollbackInternalChanges(). When
 * a transaction is commited, the "isCommit" member is set to TRUE for any
................................................................................
  int tr_tm;              /* One of TK_BEFORE, TK_AFTER */
  Expr *pWhen;            /* The WHEN clause of the expresion (may be NULL) */
  IdList *pColumns;       /* If this is an UPDATE OF <column-list> trigger,
                             the <column-list> is stored here */
  int foreach;            /* One of TK_ROW or TK_STATEMENT */

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

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

/*
 * An instance of struct TriggerStep is used to store a single SQL statement
 * that is a part of a trigger-program. 
 *
................................................................................
int sqliteIsRowid(const char*);
void sqliteGenerateRowDelete(sqlite*, Vdbe*, Table*, int, int);
void sqliteGenerateRowIndexDelete(sqlite*, Vdbe*, Table*, int, char*);
void sqliteGenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int);
void sqliteCompleteInsertion(Parse*, Table*, int, char*, int, int);
void sqliteBeginWriteOperation(Parse*, int);
void sqliteEndWriteOperation(Parse*);



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

Changes to src/tokenize.c.

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
414
415
416
417
418
419
420


421
422
423
424
425
426
427
*************************************************************************
** An tokenizer for SQL
**
** This file contains C code that splits an SQL input string up into
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
** $Id: tokenize.c,v 1.47 2002/07/01 12:27:09 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include <stdlib.h>

/*
................................................................................
    
    if( (db->flags & SQLITE_Interrupt)!=0 ){
      pParse->rc = SQLITE_INTERRUPT;
      sqliteSetString(pzErrMsg, "interrupt", 0);
      break;
    }
    pParse->sLastToken.z = &zSql[i];


    pParse->sLastToken.n = sqliteGetToken((unsigned char*)&zSql[i], &tokenType);
    i += pParse->sLastToken.n;
    if( once ){
      pParse->sFirstToken = pParse->sLastToken;
      once = 0;
    }
    switch( tokenType ){







|







 







>
>







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
*************************************************************************
** An tokenizer for SQL
**
** This file contains C code that splits an SQL input string up into
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
** $Id: tokenize.c,v 1.48 2002/08/24 18:24:56 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include <stdlib.h>

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

Changes to src/trigger.c.

7
8
9
10
11
12
13


















14
15
16
17
18
19
20
..
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
..
94
95
96
97
98
99
100
101
102
103
104
105

106

107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
...
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
...
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177


178










179
180



181
182
183
184







185


186
187

188
189
190

191




192
193
194
195
196
197
198
...
202
203
204
205
206
207
208

209
210
211
212
213
214
215
...
232
233
234
235
236
237
238

239
240
241
242
243
244
245
...
255
256
257
258
259
260
261

262
263
264
265
266
267
268
...
272
273
274
275
276
277
278

279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
*
*/
#include "sqliteInt.h"



















/*
** This is called by the parser when it sees a CREATE TRIGGER statement. See
** comments surrounding struct Trigger in sqliteInt.h for a description of 
** how triggers are stored.
*/
void sqliteCreateTrigger(
................................................................................
  int tr_tm,          /* One of TK_BEFORE, TK_AFTER , TK_INSTEAD */
  int op,             /* One of TK_INSERT, TK_UPDATE, TK_DELETE */
  IdList *pColumns,   /* column list if this is an UPDATE OF trigger */
  Token *pTableName,  /* The name of the table/view the trigger applies to */
  int foreach,        /* One of TK_ROW or TK_STATEMENT */
  Expr *pWhen,        /* WHEN clause */
  TriggerStep *pStepList, /* The triggered program */
  char const *zData,  /* The string data to make persistent */
  int zDataLen
){
  Trigger *nt;
  Table   *tab;
  int offset;
  TriggerStep *ss;

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

  /* Build the Trigger object */
  nt = (Trigger*)sqliteMalloc(sizeof(Trigger));
  if( nt==0 ) goto trigger_cleanup;
  nt->name = sqliteStrNDup(pName->z, pName->n);
  nt->table = sqliteStrNDup(pTableName->z, pTableName->n);
  nt->strings = sqliteStrNDup(zData, zDataLen);
  if( sqlite_malloc_failed ) goto trigger_cleanup;
  nt->op = op;
  nt->tr_tm = tr_tm;
  nt->pWhen = pWhen;

  nt->pColumns = pColumns;

  nt->foreach = foreach;
  nt->step_list = pStepList;
  offset = (int)(nt->strings - zData);
  sqliteExprMoveStrings(nt->pWhen, offset);

  ss = nt->step_list;
  while( ss ){
    sqliteSelectMoveStrings(ss->pSelect, offset);
    if( ss->target.z ){
      ss->target.z += offset;
    }
    sqliteExprMoveStrings(ss->pWhere, offset);
    sqliteExprListMoveStrings(ss->pExprList, offset);

    ss = ss->pNext;
  }

  /* if we are not initializing, and this trigger is not on a TEMP table, 
  ** build the sqlite_master entry
  */
  if( !pParse->initFlag ){
    static VdbeOp insertTrig[] = {
      { OP_NewRecno,   0, 0,  0          },
................................................................................
    sqliteBeginWriteOperation(pParse, 0);
    sqliteOpenMasterTable(v, tab->isTemp);
    addr = sqliteVdbeAddOpList(v, ArraySize(insertTrig), insertTrig);
    sqliteVdbeChangeP3(v, addr, tab->isTemp ? TEMP_MASTER_NAME : MASTER_NAME,
                       P3_STATIC);
    sqliteVdbeChangeP3(v, addr+2, nt->name, 0); 
    sqliteVdbeChangeP3(v, addr+3, nt->table, 0); 
    sqliteVdbeChangeP3(v, addr+5, nt->strings, 0);
    if( !tab->isTemp ){
      sqliteChangeCookie(pParse->db, v);
    }
    sqliteVdbeAddOp(v, OP_Close, 0, 0);
    sqliteEndWriteOperation(pParse);
  }

................................................................................
    sqliteHashInsert(&(pParse->db->trigHash), nt->name, pName->n + 1, nt);

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

trigger_cleanup:

  sqliteIdListDelete(pColumns);
  sqliteExprDelete(pWhen);


  {










    TriggerStep * pp;
    TriggerStep * nn;




    pp = pStepList;
    while( pp ){
      nn = pp->pNext;







      sqliteExprDelete(pp->pWhere);


      sqliteExprListDelete(pp->pExprList);
      sqliteSelectDelete(pp->pSelect);

      sqliteIdListDelete(pp->pIdList);
      sqliteFree(pp);
      pp = nn;

    }




  }
}

/*
** Turn a SELECT statement (that the pSelect parameter points to) into
** a trigger step.  Return a pointer to a TriggerStep structure.
**
................................................................................
TriggerStep *sqliteTriggerSelectStep(Select *pSelect){
  TriggerStep *pTriggerStep = sqliteMalloc(sizeof(TriggerStep));
  if( pTriggerStep==0 ) return 0;

  pTriggerStep->op = TK_SELECT;
  pTriggerStep->pSelect = pSelect;
  pTriggerStep->orconf = OE_Default;


  return pTriggerStep;
}

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

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


  return pTriggerStep;
}

/*
** Construct a trigger step that implements an UPDATE statement and return
** a pointer to that trigger step.  The parser calls this routine when it
................................................................................
  if( pTriggerStep==0 ) return 0;

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


  return pTriggerStep;
}

/*
** Construct a trigger step that implements a DELETE statement and return
** a pointer to that trigger step.  The parser calls this routine when it
................................................................................
  TriggerStep *pTriggerStep = sqliteMalloc(sizeof(TriggerStep));
  if( pTriggerStep==0 ) return 0;

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


  return pTriggerStep;
}

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

  pTriggerStep = pTrigger->step_list;
  while( pTriggerStep ){
    TriggerStep * pTmp = pTriggerStep;
    pTriggerStep = pTriggerStep->pNext;

    sqliteExprDelete(pTmp->pWhere);
    sqliteExprListDelete(pTmp->pExprList);
    sqliteSelectDelete(pTmp->pSelect);
    sqliteIdListDelete(pTmp->pIdList);

    sqliteFree(pTmp);
  }

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

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







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|
<



<
<







 







<



|
>
|
>


<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







|







 







<









>
>
|
>
>
>
>
>
>
>
>
>
>
|
<
>
>
>
|
<
<
<
>
>
>
>
>
>
>
|
>
>
|
<
>
|
<
<
>
|
>
>
>
>







 







>







 







>







 







>







 







>










|
<
<
<
<
<
<
<
<
<
<
<
<




<







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
..
41
42
43
44
45
46
47
48

49
50
51


52
53
54
55
56
57
58
...
109
110
111
112
113
114
115

116
117
118
119
120
121
122
123
124














125
126
127
128
129
130
131
...
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
...
163
164
165
166
167
168
169

170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192

193
194
195
196



197
198
199
200
201
202
203
204
205
206
207

208
209


210
211
212
213
214
215
216
217
218
219
220
221
222
...
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
...
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
...
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
...
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317












318
319
320
321

322
323
324
325
326
327
328
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
*
*/
#include "sqliteInt.h"

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

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

    sqliteFree(pTmp);
  }
}

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

){
  Trigger *nt;
  Table   *tab;



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

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

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















  /* if we are not initializing, and this trigger is not on a TEMP table, 
  ** build the sqlite_master entry
  */
  if( !pParse->initFlag ){
    static VdbeOp insertTrig[] = {
      { OP_NewRecno,   0, 0,  0          },
................................................................................
    sqliteBeginWriteOperation(pParse, 0);
    sqliteOpenMasterTable(v, tab->isTemp);
    addr = sqliteVdbeAddOpList(v, ArraySize(insertTrig), insertTrig);
    sqliteVdbeChangeP3(v, addr, tab->isTemp ? TEMP_MASTER_NAME : MASTER_NAME,
                       P3_STATIC);
    sqliteVdbeChangeP3(v, addr+2, nt->name, 0); 
    sqliteVdbeChangeP3(v, addr+3, nt->table, 0); 
    sqliteVdbeChangeP3(v, addr+5, pAll->z, pAll->n);
    if( !tab->isTemp ){
      sqliteChangeCookie(pParse->db, v);
    }
    sqliteVdbeAddOp(v, OP_Close, 0, 0);
    sqliteEndWriteOperation(pParse);
  }

................................................................................
    sqliteHashInsert(&(pParse->db->trigHash), nt->name, pName->n + 1, nt);

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

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

trigger_cleanup:

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

/*
** Make a copy of all components of the given trigger step.  This has
** the effect of copying all Expr.token.z values into memory obtained
** from sqliteMalloc().  As initially created, the Expr.token.z values
** all point to the input string that was fed to the parser.  But that
** string is ephemeral - it will go away as soon as the sqlite_exec()
** call that started the parser exits.  This routine makes a persistent
** copy of all the Expr.token.z strings so that the TriggerStep structure
** will be valid even after the sqlite_exec() call returns.
*/
static void sqlitePersistTriggerStep(TriggerStep *p){

  if( p->target.z ){
    p->target.z = sqliteStrNDup(p->target.z, p->target.n);
    p->target.dyn = 1;
  }



  if( p->pSelect ){
    Select *pNew = sqliteSelectDup(p->pSelect);
    sqliteSelectDelete(p->pSelect);
    p->pSelect = pNew;
  }
  if( p->pWhere ){
    Expr *pNew = sqliteExprDup(p->pWhere);
    sqliteExprDelete(p->pWhere);
    p->pWhere = pNew;
  }
  if( p->pExprList ){

    ExprList *pNew = sqliteExprListDup(p->pExprList);
    sqliteExprListDelete(p->pExprList);


    p->pExprList = pNew;
  }
  if( p->pIdList ){
    IdList *pNew = sqliteIdListDup(p->pIdList);
    sqliteIdListDelete(p->pIdList);
    p->pIdList = pNew;
  }
}

/*
** Turn a SELECT statement (that the pSelect parameter points to) into
** a trigger step.  Return a pointer to a TriggerStep structure.
**
................................................................................
TriggerStep *sqliteTriggerSelectStep(Select *pSelect){
  TriggerStep *pTriggerStep = sqliteMalloc(sizeof(TriggerStep));
  if( pTriggerStep==0 ) return 0;

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

  return pTriggerStep;
}

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

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

  return pTriggerStep;
}

/*
** Construct a trigger step that implements an UPDATE statement and return
** a pointer to that trigger step.  The parser calls this routine when it
................................................................................
  if( pTriggerStep==0 ) return 0;

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

  return pTriggerStep;
}

/*
** Construct a trigger step that implements a DELETE statement and return
** a pointer to that trigger step.  The parser calls this routine when it
................................................................................
  TriggerStep *pTriggerStep = sqliteMalloc(sizeof(TriggerStep));
  if( pTriggerStep==0 ) return 0;

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

  return pTriggerStep;
}

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

  sqliteDeleteTriggerStep(pTrigger->step_list);












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

  sqliteFree(pTrigger);
}

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

Changes to test/all.test.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
30
31
32
33
34
35
36

37
38
39
40
41
42
43
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file runs all tests.
#
# $Id: all.test,v 1.16 2002/08/11 20:10:49 drh Exp $

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

if {[file exists ./sqlite_test_count]} {
................................................................................
set LeakList {}

set EXCLUDE {
  all.test
  quick.test
  malloc.test
  misuse.test

}
#  btree2.test

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







|







 







>







6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file runs all tests.
#
# $Id: all.test,v 1.17 2002/08/24 18:24:57 drh Exp $

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

if {[file exists ./sqlite_test_count]} {
................................................................................
set LeakList {}

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

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

Changes to test/quick.test.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

26
27
28
29
30
31
32
33
34
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file runs all tests.
#
# $Id: quick.test,v 1.3 2002/07/07 16:52:47 drh Exp $

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

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

}

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

really_finish_test







|












>









6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file runs all tests.
#
# $Id: quick.test,v 1.4 2002/08/24 18:24:57 drh Exp $

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

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

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

really_finish_test

Changes to test/view.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
260
261
262
263
264
265
266
267





























268
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing VIEW statements.
#
# $Id: view.test,v 1.8 2002/07/16 02:05:45 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test view-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);
................................................................................
do_test view-7.6 {
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM test;
  }
} {1 2 3}






























finish_test







|







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing VIEW statements.
#
# $Id: view.test,v 1.9 2002/08/24 18:24:57 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test view-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);
................................................................................
do_test view-7.6 {
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM test;
  }
} {1 2 3}

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

finish_test

Changes to www/c_interface.tcl.

1
2
3
4
5
6
7
8
9
10
11
...
712
713
714
715
716
717
718

719
720
721
722
723
724
725
...
756
757
758
759
760
761
762
763







764
765
766
767
768
769
770
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.34 2002/08/15 11:48:14 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
from malloc() and returns a pointer to the malloced buffer.  
<b>sqlite_mprintf()</b> also understands the %q and %Q extensions described
above.  The <b>sqlite_vmprintf()</b> is a varargs version of the same
routine.  The string pointer that these routines return should be freed
by passing it to <b>sqlite_freemem()</b>.
</p>


<h2>Adding New SQL Functions</h2>

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

................................................................................
The <b>sqlite_create_function()</b> interface is used to create 
regular functions and <b>sqlite_create_aggregate()</b> is used to
create new aggregate functions.  In both cases, the <b>db</b>
parameter is an open SQLite database on which the functions should
be registered, <b>zName</b> is the name of the new function,
<b>nArg</b> is the number of arguments, and <b>pUserData</b> is
a pointer which is passed through unchanged to the C implementation
of the function.







</p>

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



|







 







>







 







|
>
>
>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
...
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
...
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.35 2002/08/24 18:24:57 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
from malloc() and returns a pointer to the malloced buffer.  
<b>sqlite_mprintf()</b> also understands the %q and %Q extensions described
above.  The <b>sqlite_vmprintf()</b> is a varargs version of the same
routine.  The string pointer that these routines return should be freed
by passing it to <b>sqlite_freemem()</b>.
</p>

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

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

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

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

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

Changes to www/faq.tcl.

1
2
3
4
5
6
7
8
9
10
11
...
326
327
328
329
330
331
332





333
334
335
336
337
338
339
#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.18 2002/08/18 19:09:24 drh Exp $}

puts {<html>
<head>
  <title>SQLite Frequently Asked Questions</title>
</head>
<body bgcolor="white">
<h1 align="center">Frequently Asked Questions</h1>
................................................................................
  tables and indices, but again it is not really possible to reach this
  limit due to the file size constraint.</p>

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





}

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



|







 







>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
...
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.19 2002/08/24 18:24:57 drh Exp $}

puts {<html>
<head>
  <title>SQLite Frequently Asked Questions</title>
</head>
<body bgcolor="white">
<h1 align="center">Frequently Asked Questions</h1>
................................................................................
  tables and indices, but again it is not really possible to reach this
  limit due to the file size constraint.</p>

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

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

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

Changes to www/omitted.tcl.

1
2
3
4
5
6
7
8
9
10
11
..
41
42
43
44
45
46
47
48
49
50
51
52
53








54
55
56
57
58




59
60
61
62
63
64
65
#
# Run this script to generated a omitted.html output file
#
set rcsid {$Id: omitted.tcl,v 1.2 2002/08/15 13:45:17 drh Exp $}

puts {<html>
<head>
  <title>SQL Features That SQLite Does Not Implement</title>
</head>
<body bgcolor="white">
<h1 align="center">
................................................................................
feature {CHECK constraints} {
  CHECK constraints are parsed but they are not enforced.
  NOT NULL and UNIQUE constraints are enforced, however.
}

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

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









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





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

feature {RIGHT and FULL OUTER JOIN} {



|







 







|





>
>
>
>
>
>
>
>





>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
..
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
#
# Run this script to generated a omitted.html output file
#
set rcsid {$Id: omitted.tcl,v 1.3 2002/08/24 18:24:58 drh Exp $}

puts {<html>
<head>
  <title>SQL Features That SQLite Does Not Implement</title>
</head>
<body bgcolor="white">
<h1 align="center">
................................................................................
feature {CHECK constraints} {
  CHECK constraints are parsed but they are not enforced.
  NOT NULL and UNIQUE constraints are enforced, however.
}

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

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

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

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

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

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

feature {RIGHT and FULL OUTER JOIN} {

Changes to www/speed.tcl.

1
2
3
4
5
6
7
8
9
10
11
..
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37



38
39
40
41
42
43
44
45
46
47
48
49

50
51
52
53
54






55
56
57

58
59
60
61
62
63
64
65
..
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
...
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
...
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
...
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
...
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
...
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
...
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308








309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
...
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
#
# Run this Tcl script to generate the speed.html file.
#
set rcsid {$Id: speed.tcl,v 1.7 2002/08/06 12:05:01 drh Exp $ }

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

puts {
<h2>Executive Summary</h2>

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

<ul>
<li><p>
  SQLite 2.4.0 is significantly faster than PostgreSQL
  for most common operations.
</p></li>
<li><p>
  The speed of SQLite 2.4.0 is similar to MySQL.
  This is true in spite of the
  fact that SQLite contains full transaction support whereas the
  version of MySQL tested did not.
</p></li>



</ul>

<h2>Test Environment</h2>

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

<p>
The PostgreSQL and MySQL servers used were as delivered by default on

RedHat 7.2.  No effort was made to tune these engines.  Note in particular
the the default MySQL configuration on RedHat 7.2 does not support
transactions.  Not having to support transactions gives MySQL a
big advantage, but SQLite is still able to hold its own on most
tests.






</p>

<p>

SQLite was compiled with -O6 optimization and with
the -DNDEBUG=1 switch which disables the many "assert()" statements
in the SQLite code.  The -DNDEBUG=1 compiler option roughly doubles
the speed of SQLite.
</p>

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

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.027</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.113</td></tr>
<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;8.409</td></tr>
<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.188</td></tr>
</table>

<p>SQLite must close and reopen the database file, and thus invalidate
its cache, for each SQL statement.  In spite of this, the asynchronous
version of SQLite is still nearly as fast as MySQL.  Notice how much slower
the synchronous version is, however.  This is due to the necessity of
calling <b>fsync()</b> after each SQL statement.</p>
................................................................................
INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one');<br>
<i>... 24997 lines omitted</i><br>
INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty seven');<br>
INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred thirty');<br>
COMMIT;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.175</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.444</td></tr>
<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.858</td></tr>
<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.739</td></tr>
</table>

<p>
When all the INSERTs are put in a transaction, SQLite no longer has to
close and reopen the database between each statement.  It also does not
have to do any fsync()s until the very end.  When unshackled in
this way, SQLite is much faster than either PostgreSQL and MySQL.
................................................................................
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<1200;<br>
<i>... 94 lines omitted</i><br>
SELECT count(*), avg(b) FROM t2 WHERE b>=9700 AND b<10700;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.773</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.023</td></tr>
<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;6.281</td></tr>
<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;6.247</td></tr>
</table>

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


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

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;16.726</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.237</td></tr>
<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;6.137</td></tr>
<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;6.112</td></tr>
</table>

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

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

<p>
SQLite is slower at creating new indices.  But since creating
new indices is an uncommon operation, this is not seen as a
problem.
</p>
................................................................................
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;<br>
<i>... 4994 lines omitted</i><br>
SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;5.318</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.555</td></tr>
<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;1.289</td></tr>
<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.273</td></tr>
</table>

<p>
This test runs a set of 5000 queries that are similar in form to
those in test 3.  But now instead of being half as fast, SQLite
is faster than both PostgreSQL and MySQL.
</p>
................................................................................
UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;<br>
<i>... 996 lines omitted</i><br>
UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;<br>
UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;<br>
COMMIT;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.828</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;9.272</td></tr>
<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.915</td></tr>
<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.889</td></tr>
</table>

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

................................................................................
UPDATE t2 SET b=28304 WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET b=442549 WHERE a=24999;<br>
UPDATE t2 SET b=423958 WHERE a=25000;<br>
COMMIT;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;28.021</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;8.565</td></tr>
<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;10.939</td></tr>
<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;11.199</td></tr>
</table>

<p>
In this case MySQL is slightly faster than SQLite, though not by much.
The difference is believed to have to do with the fact SQLite 
handles the integers as strings instead of binary numbers.
</p>
................................................................................
UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty eight' WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;<br>
UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' WHERE a=25000;<br>
COMMIT;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;48.739</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;7.059</td></tr>
<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;7.868</td></tr>
<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;6.720</td></tr>
</table>

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

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

<p>
The poor performance of PostgreSQL in this case appears to be due to its
synchronous behavior.  The CPU was mostly idle during the 55 second run.








</p>

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


<h2>Test 12: DELETE with an index</h2>
<blockquote>
DELETE FROM t2 WHERE a>10 AND a<20000;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.318</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.600</td></tr>
<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;1.436</td></tr>
<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.775</td></tr>
</table>


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

<p>
Earlier versions of SQLite would show decreasing performance after a
sequence DELETEs followed by new INSERTs.  As this test shows, the
problem has now been resolved.
</p>
................................................................................
INSERT INTO t1 VALUES(1,29676,'twenty nine thousand six hundred seventy six');<br>
<i>... 2997 lines omitted</i><br>
INSERT INTO t1 VALUES(2999,37835,'thirty seven thousand eight hundred thirty five');<br>
INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred seventeen');<br>
COMMIT;<br>

</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.209</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.031</td></tr>
<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.298</td></tr>
<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.282</td></tr>
</table>

<h2>Test 15: DROP TABLE</h2>
<blockquote>
DROP TABLE t1;<br>DROP TABLE t2;
</blockquote><table border=0 cellpadding=0 cellspacing=0>
<tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.105</td></tr>
<tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.015</td></tr>
<tr><td>SQLite 2.4:</td><td align="right">&nbsp;&nbsp;&nbsp;0.472</td></tr>
<tr><td>SQLite 2.4 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.232</td></tr>
</table>

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



|







 







|






|



|




>
>
>












>
|


|
<
>
>
>
>
>
>



>
|







 







|
|
|
|







 







|
|
|
|







 







|
|
|
|











<












|
|
|
|












|
|
|
|







 







|
|
|
|







 







|
|
|
|







 







|
|
|
|







 







|
|
|
|











|
|
|
|




|
>
>
>
>
>
>
>
>






|
|
|
|

<





|
|
|
|

<





|
|
|
|







 







|
|
|
|






|
|
|
|







1
2
3
4
5
6
7
8
9
10
11
..
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
...
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
...
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
...
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174

175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
...
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
...
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
...
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
...
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336

337
338
339
340
341
342
343
344
345
346

347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
...
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
#
# Run this Tcl script to generate the speed.html file.
#
set rcsid {$Id: speed.tcl,v 1.8 2002/08/24 18:24:58 drh Exp $ }

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

puts {
<h2>Executive Summary</h2>

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

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

<h2>Test Environment</h2>

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

<p>
The PostgreSQL and MySQL servers used were as delivered by default on
RedHat 7.2.  (PostgreSQL version 7.1.3 and MySQL version 3.23.41.)
No effort was made to tune these engines.  Note in particular
the the default MySQL configuration on RedHat 7.2 does not support
transactions.  Not having to support transactions gives MySQL a
big speed advantage, but SQLite is still able to hold its own on most

tests.  On the other hand, I am told that the default PostgreSQL
configuration is unnecessarily conservative (it is designed to
work on a machine with 8MB of RAM) and that PostgreSQL could
be made to run a lot faster with some knowledgable configuration
tuning.  I have not, however, been able to personally confirm
these reports.
</p>

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

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

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

<p>SQLite must close and reopen the database file, and thus invalidate
its cache, for each SQL statement.  In spite of this, the asynchronous
version of SQLite is still nearly as fast as MySQL.  Notice how much slower
the synchronous version is, however.  This is due to the necessity of
calling <b>fsync()</b> after each SQL statement.</p>
................................................................................
INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one');<br>
<i>... 24997 lines omitted</i><br>
INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty seven');<br>
INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred thirty');<br>
COMMIT;<br>

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

<p>
When all the INSERTs are put in a transaction, SQLite no longer has to
close and reopen the database between each statement.  It also does not
have to do any fsync()s until the very end.  When unshackled in
this way, SQLite is much faster than either PostgreSQL and MySQL.
................................................................................
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<1200;<br>
<i>... 94 lines omitted</i><br>
SELECT count(*), avg(b) FROM t2 WHERE b>=9700 AND b<10700;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;<br>

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

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


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

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

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

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

<p>
SQLite is slower at creating new indices.  But since creating
new indices is an uncommon operation, this is not seen as a
problem.
</p>
................................................................................
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;<br>
<i>... 4994 lines omitted</i><br>
SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;<br>
SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;<br>

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

<p>
This test runs a set of 5000 queries that are similar in form to
those in test 3.  But now instead of being half as fast, SQLite
is faster than both PostgreSQL and MySQL.
</p>
................................................................................
UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;<br>
<i>... 996 lines omitted</i><br>
UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;<br>
UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;<br>
COMMIT;<br>

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

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

................................................................................
UPDATE t2 SET b=28304 WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET b=442549 WHERE a=24999;<br>
UPDATE t2 SET b=423958 WHERE a=25000;<br>
COMMIT;<br>

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

<p>
In this case MySQL is slightly faster than SQLite, though not by much.
The difference is believed to have to do with the fact SQLite 
handles the integers as strings instead of binary numbers.
</p>
................................................................................
UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty eight' WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;<br>
UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' WHERE a=25000;<br>
COMMIT;<br>

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

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

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

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

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

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


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


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

<p>
Earlier versions of SQLite would show decreasing performance after a
sequence DELETEs followed by new INSERTs.  As this test shows, the
problem has now been resolved.
</p>
................................................................................
INSERT INTO t1 VALUES(1,29676,'twenty nine thousand six hundred seventy six');<br>
<i>... 2997 lines omitted</i><br>
INSERT INTO t1 VALUES(2999,37835,'thirty seven thousand eight hundred thirty five');<br>
INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred seventeen');<br>
COMMIT;<br>

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

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

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