SQLite

Check-in [794bf67b6b]
Login

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

Overview
Comment:Added FOR EACH ROW triggers functionality (CVS 562)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 794bf67b6b36fce8854d5daff12f21dbb943240c
User & Date: danielk1977 2002-05-15 08:30:13.000
Context
2002-05-15
08:43
Add the Makefile.in that was forgotten with checkin #562 (CVS 563) (check-in: 29b8330ca6 user: danielk1977 tags: trunk)
08:30
Added FOR EACH ROW triggers functionality (CVS 562) (check-in: 794bf67b6b user: danielk1977 tags: trunk)
2002-05-10
14:41
Version 2.4.12 (CVS 561) (check-in: 06cdaf1c80 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.87 2002/05/08 21:30:15 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 







|







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.88 2002/05/15 08:30:13 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
246
247
248
249
250
251
252
















253
254
255
256
257
258
259
    pIndex->isCommit = 1;
  }
  while( (pElem=sqliteHashFirst(&db->idxDrop))!=0 ){
    Index *pIndex = sqliteHashData(pElem);
    sqliteUnlinkAndDeleteIndex(db, pIndex);
  }
  sqliteHashClear(&db->idxDrop);
















  db->flags &= ~SQLITE_InternChanges;
}

/*
** This routine runs when one or more CREATE TABLE, CREATE INDEX,
** DROP TABLE, or DROP INDEX statements gets rolled back.  The
** additions or deletions of Table and Index structures in the







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







246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
    pIndex->isCommit = 1;
  }
  while( (pElem=sqliteHashFirst(&db->idxDrop))!=0 ){
    Index *pIndex = sqliteHashData(pElem);
    sqliteUnlinkAndDeleteIndex(db, pIndex);
  }
  sqliteHashClear(&db->idxDrop);

  /* Set the commit flag on all triggers added this transaction */
  for(pElem=sqliteHashFirst(&db->trigHash); pElem; pElem=sqliteHashNext(pElem)){
    Trigger *pTrigger = sqliteHashData(pElem);
    pTrigger->isCommit = 1;
  }

  /* Delete the structures for triggers removed this transaction */
  pElem = sqliteHashFirst(&db->trigDrop);
  while (pElem) {
    Trigger *pTrigger = sqliteHashData(pElem);
    sqliteDeleteTrigger(pTrigger);
    pElem = sqliteHashNext(pElem);
  }
  sqliteHashClear(&db->trigDrop);

  db->flags &= ~SQLITE_InternChanges;
}

/*
** This routine runs when one or more CREATE TABLE, CREATE INDEX,
** DROP TABLE, or DROP INDEX statements gets rolled back.  The
** additions or deletions of Table and Index structures in the
300
301
302
303
304
305
306










































307
308
309
310
311
312
313
    Index *pOld, *p = sqliteHashData(pElem);
    assert( p->isCommit );
    p->isDropped = 0;
    pOld = sqliteHashInsert(&db->idxHash, p->zName, strlen(p->zName)+1, p);
    assert( pOld==0 || pOld==p );
  }
  sqliteHashClear(&db->idxDrop);










































  db->flags &= ~SQLITE_InternChanges;
}

/*
** Construct the name of a user table or index from a token.
**
** Space to hold the name is obtained from sqliteMalloc() and must







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







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
363
364
365
366
367
368
369
370
371
    Index *pOld, *p = sqliteHashData(pElem);
    assert( p->isCommit );
    p->isDropped = 0;
    pOld = sqliteHashInsert(&db->idxHash, p->zName, strlen(p->zName)+1, p);
    assert( pOld==0 || pOld==p );
  }
  sqliteHashClear(&db->idxDrop);

  /* Remove any triggers that haven't been commited yet */
  for(pElem = sqliteHashFirst(&db->trigHash); pElem; 
      pElem = (pElem?sqliteHashNext(pElem):0)) {
    Trigger * pTrigger = sqliteHashData(pElem);
    if (!pTrigger->isCommit) {
      Table * tbl = sqliteFindTable(db, pTrigger->table);
      if (tbl) {
	if (tbl->pTrigger == pTrigger) 
	  tbl->pTrigger = pTrigger->pNext;
	else {
	  Trigger * cc = tbl->pTrigger;
	  while (cc) {
	    if (cc->pNext == pTrigger) {
	      cc->pNext = cc->pNext->pNext;
	      break;
	    }
	    cc = cc->pNext;
	  }
	  assert(cc);
	}
      }
      sqliteHashInsert(&db->trigHash, pTrigger->name,
	      1 + strlen(pTrigger->name), 0);
      sqliteDeleteTrigger(pTrigger);
      pElem = sqliteHashFirst(&db->trigHash);
    }
  }

  /* Any triggers that were dropped - put 'em back in place */
  for(pElem = sqliteHashFirst(&db->trigDrop); pElem; 
      pElem = sqliteHashNext(pElem)) {
    Trigger * pTrigger = sqliteHashData(pElem);
    Table * tab = sqliteFindTable(db, pTrigger->table);
    sqliteHashInsert(&db->trigHash, pTrigger->name, 
	strlen(pTrigger->name) + 1, pTrigger);

    pTrigger->pNext = tab->pTrigger;
    tab->pTrigger = pTrigger;
  }

  sqliteHashClear(&db->trigDrop);
  db->flags &= ~SQLITE_InternChanges;
}

/*
** Construct the name of a user table or index from a token.
**
** Space to hold the name is obtained from sqliteMalloc() and must
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
**
** This plan is not completely bullet-proof.  It is possible for
** the schema to change multiple times and for the cookie to be
** set back to prior value.  But schema changes are infrequent
** and the probability of hitting the same cookie value is only
** 1 chance in 2^32.  So we're safe enough.
*/
static void changeCookie(sqlite *db){
  if( db->next_cookie==db->schema_cookie ){
    db->next_cookie = db->schema_cookie + sqliteRandomByte() + 1;
    db->flags |= SQLITE_InternChanges;
  }
}

/*







|







649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
**
** This plan is not completely bullet-proof.  It is possible for
** the schema to change multiple times and for the cookie to be
** set back to prior value.  But schema changes are infrequent
** and the probability of hitting the same cookie value is only
** 1 chance in 2^32.  So we're safe enough.
*/
void changeCookie(sqlite *db){
  if( db->next_cookie==db->schema_cookie ){
    db->next_cookie = db->schema_cookie + sqliteRandomByte() + 1;
    db->flags |= SQLITE_InternChanges;
  }
}

/*
1032
1033
1034
1035
1036
1037
1038







1039
1040
1041
1042
1043
1044
1045
      { OP_Next,       0, ADDR(4),  0}, /* 8 */
      { OP_Integer,    0, 0,        0}, /* 9 */
      { OP_SetCookie,  0, 0,        0},
      { OP_Close,      0, 0,        0},
    };
    Index *pIdx;
    sqliteBeginWriteOperation(pParse);







    if( !pTable->isTemp ){
      base = sqliteVdbeAddOpList(v, ArraySize(dropTable), dropTable);
      sqliteVdbeChangeP3(v, base+2, pTable->zName, 0);
      changeCookie(db);
      sqliteVdbeChangeP1(v, base+9, db->next_cookie);
    }
    if( !isView ){







>
>
>
>
>
>
>







1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
      { OP_Next,       0, ADDR(4),  0}, /* 8 */
      { OP_Integer,    0, 0,        0}, /* 9 */
      { OP_SetCookie,  0, 0,        0},
      { OP_Close,      0, 0,        0},
    };
    Index *pIdx;
    sqliteBeginWriteOperation(pParse);
    /* Drop all triggers associated with the table being dropped */
    while (pTable->pTrigger) {
      Token tt;
      tt.z = pTable->pTrigger->name;
      tt.n = strlen(pTable->pTrigger->name);
      sqliteDropTrigger(pParse, &tt, 1);
    }
    if( !pTable->isTemp ){
      base = sqliteVdbeAddOpList(v, ArraySize(dropTable), dropTable);
      sqliteVdbeChangeP3(v, base+2, pTable->zName, 0);
      changeCookie(db);
      sqliteVdbeChangeP1(v, base+9, db->next_cookie);
    }
    if( !isView ){
1649
1650
1651
1652
1653
1654
1655

1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674

1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691

1692
1693
1694
1695
1696
1697
1698
** all.  So there is not need to set a checkpoint is a transaction
** is already in effect.
*/
void sqliteBeginWriteOperation(Parse *pParse){
  Vdbe *v;
  v = sqliteGetVdbe(pParse);
  if( v==0 ) return;

  if( (pParse->db->flags & SQLITE_InTrans)==0  ){
    sqliteVdbeAddOp(v, OP_Transaction, 0, 0);
    sqliteVdbeAddOp(v, OP_VerifyCookie, pParse->db->schema_cookie, 0);
    pParse->schemaVerified = 1;
  }
}

/*
** Generate VDBE code that prepares for doing an operation that
** might change the database.  The operation might not be atomic in
** the sense that an error may be discovered and the operation might
** abort after some changes have been made.  If we are in the middle 
** of a transaction, then this sets a checkpoint.  If we are not in
** a transaction, then start a transaction.
*/
void sqliteBeginMultiWriteOperation(Parse *pParse){
  Vdbe *v;
  v = sqliteGetVdbe(pParse);
  if( v==0 ) return;

  if( (pParse->db->flags & SQLITE_InTrans)==0 ){
    sqliteVdbeAddOp(v, OP_Transaction, 0, 0);
    sqliteVdbeAddOp(v, OP_VerifyCookie, pParse->db->schema_cookie, 0);
    pParse->schemaVerified = 1;
  }else{
    sqliteVdbeAddOp(v, OP_Checkpoint, 0, 0);
  }
}

/*
** Generate code that concludes an operation that may have changed
** the database.  This is a companion function to BeginWriteOperation().
** If a transaction was started, then commit it.  If a checkpoint was
** started then commit that.
*/
void sqliteEndWriteOperation(Parse *pParse){
  Vdbe *v;

  v = sqliteGetVdbe(pParse);
  if( v==0 ) return;
  if( pParse->db->flags & SQLITE_InTrans ){
    /* Do Nothing */
  }else{
    sqliteVdbeAddOp(v, OP_Commit, 0, 0);
  }







>



















>

















>







1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
** all.  So there is not need to set a checkpoint is a transaction
** is already in effect.
*/
void sqliteBeginWriteOperation(Parse *pParse){
  Vdbe *v;
  v = sqliteGetVdbe(pParse);
  if( v==0 ) return;
  if (pParse->trigStack) return; /* if this is in a trigger */
  if( (pParse->db->flags & SQLITE_InTrans)==0  ){
    sqliteVdbeAddOp(v, OP_Transaction, 0, 0);
    sqliteVdbeAddOp(v, OP_VerifyCookie, pParse->db->schema_cookie, 0);
    pParse->schemaVerified = 1;
  }
}

/*
** Generate VDBE code that prepares for doing an operation that
** might change the database.  The operation might not be atomic in
** the sense that an error may be discovered and the operation might
** abort after some changes have been made.  If we are in the middle 
** of a transaction, then this sets a checkpoint.  If we are not in
** a transaction, then start a transaction.
*/
void sqliteBeginMultiWriteOperation(Parse *pParse){
  Vdbe *v;
  v = sqliteGetVdbe(pParse);
  if( v==0 ) return;
  if (pParse->trigStack) return; /* if this is in a trigger */
  if( (pParse->db->flags & SQLITE_InTrans)==0 ){
    sqliteVdbeAddOp(v, OP_Transaction, 0, 0);
    sqliteVdbeAddOp(v, OP_VerifyCookie, pParse->db->schema_cookie, 0);
    pParse->schemaVerified = 1;
  }else{
    sqliteVdbeAddOp(v, OP_Checkpoint, 0, 0);
  }
}

/*
** Generate code that concludes an operation that may have changed
** the database.  This is a companion function to BeginWriteOperation().
** If a transaction was started, then commit it.  If a checkpoint was
** started then commit that.
*/
void sqliteEndWriteOperation(Parse *pParse){
  Vdbe *v;
  if (pParse->trigStack) return; /* if this is in a trigger */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) return;
  if( pParse->db->flags & SQLITE_InTrans ){
    /* Do Nothing */
  }else{
    sqliteVdbeAddOp(v, OP_Commit, 0, 0);
  }
1910
1911
1912
1913
1914
1915
1916








1917
1918
1919
1920
1921
1922
1923
      int size = db->cache_size;
      if( size<0 ) size = -size;
      if( !getBoolean(zRight) ) size = -size;
      db->cache_size = size;
      sqliteBtreeSetCacheSize(db->pBe, db->cache_size);
    }
  }else









  if( sqliteStrICmp(zLeft, "vdbe_trace")==0 ){
    if( getBoolean(zRight) ){
      db->flags |= SQLITE_VdbeTrace;
    }else{
      db->flags &= ~SQLITE_VdbeTrace;
    }







>
>
>
>
>
>
>
>







1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
      int size = db->cache_size;
      if( size<0 ) size = -size;
      if( !getBoolean(zRight) ) size = -size;
      db->cache_size = size;
      sqliteBtreeSetCacheSize(db->pBe, db->cache_size);
    }
  }else

  if( sqliteStrICmp(zLeft, "trigger_overhead_test")==0 ){
    if( getBoolean(zRight) ){
      always_code_trigger_setup = 1;
    }else{
      always_code_trigger_setup = 0;
    }
  }else

  if( sqliteStrICmp(zLeft, "vdbe_trace")==0 ){
    if( getBoolean(zRight) ){
      db->flags |= SQLITE_VdbeTrace;
    }else{
      db->flags &= ~SQLITE_VdbeTrace;
    }
Changes to src/delete.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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 DELETE FROM statements.
**
** $Id: delete.c,v 1.30 2002/04/12 10:08:59 drh Exp $
*/
#include "sqliteInt.h"


/*
** Given a table name, find the corresponding table and make sure the
** table is writeable.  Generate an error and return NULL if not.  If







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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 DELETE FROM statements.
**
** $Id: delete.c,v 1.31 2002/05/15 08:30:13 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Given a table name, find the corresponding table and make sure the
** table is writeable.  Generate an error and return NULL if not.  If
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
  int i;                 /* Loop counter */
  WhereInfo *pWInfo;     /* Information about the WHERE clause */
  Index *pIdx;           /* For looping over indices of the table */
  int base;              /* Index of the first available table cursor */
  sqlite *db;            /* Main database structure */
  int openOp;            /* Opcode used to open a cursor to the table */




  if( pParse->nErr || sqlite_malloc_failed ){
    pTabList = 0;
    goto delete_from_cleanup;
  }
  db = pParse->db;


























  /* Locate the table which we want to delete.  This table has to be
  ** put in an IdList structure because some of the subroutines we
  ** will be calling are designed to work with multiple tables and expect
  ** an IdList* parameter instead of just a Table* parameger.
  */
  pTabList = sqliteTableTokenToIdList(pParse, pTableName);
  if( pTabList==0 ) goto delete_from_cleanup;
  assert( pTabList->nId==1 );
  pTab = pTabList->a[0].pTab;
  assert( pTab->pSelect==0 );  /* This table is not a view */




  /* Resolve the column names in all the expressions.
  */
  base = pParse->nTab++;
  if( pWhere ){
    if( sqliteExprResolveIds(pParse, base, pTabList, 0, pWhere) ){
      goto delete_from_cleanup;
    }
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      goto delete_from_cleanup;
    }
  }

  /* Begin generating code.
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto delete_from_cleanup;



  sqliteBeginWriteOperation(pParse);

  /* Initialize the counter of the number of rows deleted, if
  ** we are counting rows.
  */
  if( db->flags & SQLITE_CountRows ){
    sqliteVdbeAddOp(v, OP_Integer, 0, 0);
  }

  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table.
  */
  if( pWhere==0 ){
    if( db->flags & SQLITE_CountRows ){
      /* If counting rows deleted, just count the total number of
      ** entries in the table. */
      int endOfLoop = sqliteVdbeMakeLabel(v);
      int addr;
      openOp = pTab->isTemp ? OP_OpenAux : OP_Open;
      assert( base==0 );







>
>






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












>
>
>
















>
>
>
|











|







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
  int i;                 /* Loop counter */
  WhereInfo *pWInfo;     /* Information about the WHERE clause */
  Index *pIdx;           /* For looping over indices of the table */
  int base;              /* Index of the first available table cursor */
  sqlite *db;            /* Main database structure */
  int openOp;            /* Opcode used to open a cursor to the table */

  int row_triggers_exist = 0;
  int oldIdx = -1;

  if( pParse->nErr || sqlite_malloc_failed ){
    pTabList = 0;
    goto delete_from_cleanup;
  }
  db = pParse->db;

  /* Check for the special case of a VIEW with one or more ON DELETE triggers 
   * defined 
   */
  {
    Table * pTab;
    char * zTab = sqliteTableNameFromToken(pTableName);

    if(zTab != 0) {
      pTab = sqliteFindTable(pParse->db, zTab);
      if (pTab) {
	row_triggers_exist = 
	  sqliteTriggersExist(pParse, pTab->pTrigger, 
	      TK_DELETE, TK_BEFORE, TK_ROW, 0) ||
	  sqliteTriggersExist(pParse, pTab->pTrigger, 
	      TK_DELETE, TK_AFTER, TK_ROW, 0);
      }
      sqliteFree(zTab);
      if (row_triggers_exist &&  pTab->pSelect ) {
	/* Just fire VIEW triggers */
	sqliteViewTriggers(pParse, pTab, pWhere, OE_Replace, 0);
	return;
      }
    }
  }

  /* Locate the table which we want to delete.  This table has to be
  ** put in an IdList structure because some of the subroutines we
  ** will be calling are designed to work with multiple tables and expect
  ** an IdList* parameter instead of just a Table* parameger.
  */
  pTabList = sqliteTableTokenToIdList(pParse, pTableName);
  if( pTabList==0 ) goto delete_from_cleanup;
  assert( pTabList->nId==1 );
  pTab = pTabList->a[0].pTab;
  assert( pTab->pSelect==0 );  /* This table is not a view */

  if (row_triggers_exist) 
    oldIdx = pParse->nTab++;

  /* Resolve the column names in all the expressions.
  */
  base = pParse->nTab++;
  if( pWhere ){
    if( sqliteExprResolveIds(pParse, base, pTabList, 0, pWhere) ){
      goto delete_from_cleanup;
    }
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      goto delete_from_cleanup;
    }
  }

  /* Begin generating code.
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto delete_from_cleanup;
  if (row_triggers_exist) 
    sqliteBeginMultiWriteOperation(pParse);
  else 
    sqliteBeginWriteOperation(pParse);

  /* Initialize the counter of the number of rows deleted, if
  ** we are counting rows.
  */
  if( db->flags & SQLITE_CountRows ){
    sqliteVdbeAddOp(v, OP_Integer, 0, 0);
  }

  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table.
  */
  if( pWhere==0 && !row_triggers_exist){
    if( db->flags & SQLITE_CountRows ){
      /* If counting rows deleted, just count the total number of
      ** entries in the table. */
      int endOfLoop = sqliteVdbeMakeLabel(v);
      int addr;
      openOp = pTab->isTemp ? OP_OpenAux : OP_Open;
      assert( base==0 );
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
    sqliteWhereEnd(pWInfo);

    /* Delete every item whose key was written to the list during the
    ** database scan.  We have to delete items after the scan is complete
    ** because deleting an item can change the scan order.
    */
    sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);





























    openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
    sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
    for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
      sqliteVdbeAddOp(v, openOp, base+i, pIdx->tnum);
    }
    end = sqliteVdbeMakeLabel(v);

    addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);

    sqliteGenerateRowDelete(v, pTab, base, 1);










    sqliteVdbeAddOp(v, OP_Goto, 0, addr);
    sqliteVdbeResolveLabel(v, end);
    sqliteVdbeAddOp(v, OP_ListReset, 0, 0);








  }
  sqliteEndWriteOperation(pParse);

  /*
  ** Return the number of rows that were deleted.
  */
  if( db->flags & SQLITE_CountRows ){







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



|

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



>
>
>
>
>
>
>
>







205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
    sqliteWhereEnd(pWInfo);

    /* Delete every item whose key was written to the list during the
    ** database scan.  We have to delete items after the scan is complete
    ** because deleting an item can change the scan order.
    */
    sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
    end = sqliteVdbeMakeLabel(v);

    if (row_triggers_exist) {
      int ii;
      addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
      sqliteVdbeAddOp(v, OP_Dup, 0, 0);

      openOp = pTab->isTemp ? OP_OpenAux : OP_Open;
      sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
      sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
      sqliteVdbeAddOp(v, OP_OpenTemp, oldIdx, 0);

      sqliteVdbeAddOp(v, OP_Integer, 13, 0);
      for (ii = 0; ii < pTab->nCol; ii++) {
	if (ii == pTab->iPKey) 
	  sqliteVdbeAddOp(v, OP_Recno, base, 0);
	else
	  sqliteVdbeAddOp(v, OP_Column, base, ii);
      }
      sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
      sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);
      sqliteVdbeAddOp(v, OP_Close, base, 0);
      sqliteVdbeAddOp(v, OP_Rewind, oldIdx, 0);

      sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1, 
	  oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default);
    }

    pParse->nTab = base + 1;
    openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
    sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
    for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
      sqliteVdbeAddOp(v, openOp, pParse->nTab++, pIdx->tnum);
    }

    if (!row_triggers_exist) 
      addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);

    sqliteGenerateRowDelete(v, pTab, base, pParse->trigStack?0:1);

    if (row_triggers_exist) {
      for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
	sqliteVdbeAddOp(v, OP_Close, base + i, pIdx->tnum);
      }
      sqliteVdbeAddOp(v, OP_Close, base, 0);
      sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1, 
	  oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default);
    }

    sqliteVdbeAddOp(v, OP_Goto, 0, addr);
    sqliteVdbeResolveLabel(v, end);
    sqliteVdbeAddOp(v, OP_ListReset, 0, 0);

    if (!row_triggers_exist) {
      for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
	sqliteVdbeAddOp(v, OP_Close, base + i, pIdx->tnum);
      }
      sqliteVdbeAddOp(v, OP_Close, base, 0);
      pParse->nTab = base;
    }
  }
  sqliteEndWriteOperation(pParse);

  /*
  ** Return the number of rows that were deleted.
  */
  if( db->flags & SQLITE_CountRows ){
Changes to src/expr.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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.58 2002/04/20 14:24:42 drh Exp $
*/
#include "sqliteInt.h"


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







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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.59 2002/05/15 08:30:13 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
477
478
479
480
481
482
483



























484
485
486
487
488
489
490
              pExpr->iColumn = -1;
            }else{
              pExpr->iColumn = j;
            }
          }
        }
      }



























      if( cnt==0 && cntTab==1 && sqliteIsRowid(zRight) ){
        cnt = 1;
        pExpr->iColumn = -1;
      }
      sqliteFree(zLeft);
      sqliteFree(zRight);
      if( cnt==0 ){







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







477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
              pExpr->iColumn = -1;
            }else{
              pExpr->iColumn = j;
            }
          }
        }
      }

      /* If we have not already resolved this *.* expression, then maybe 
       * it is a new.* or old.* trigger argument reference */
      if (cnt == 0 && pParse->trigStack != 0) {
        TriggerStack * tt = pParse->trigStack;
        int j;
        int t = 0;
        if (tt->newIdx != -1 && sqliteStrICmp("new", zLeft) == 0) {
          pExpr->iTable = tt->newIdx;
          cntTab++;
          t = 1;
        }
        if (tt->oldIdx != -1 && sqliteStrICmp("old", zLeft) == 0) {
          pExpr->iTable = tt->oldIdx;
          cntTab++;
          t = 1;
        }

        if (t) 
          for(j=0; j<tt->pTab->nCol; j++) {
            if( sqliteStrICmp(tt->pTab->aCol[j].zName, zRight)==0 ){
              cnt++;
              pExpr->iColumn = j;
            }
          }
      }

      if( cnt==0 && cntTab==1 && sqliteIsRowid(zRight) ){
        cnt = 1;
        pExpr->iColumn = -1;
      }
      sqliteFree(zLeft);
      sqliteFree(zRight);
      if( cnt==0 ){
Changes to src/insert.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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 INSERT statements in SQLite.
**
** $Id: insert.c,v 1.52 2002/04/12 10:08:59 drh Exp $
*/
#include "sqliteInt.h"

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







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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 INSERT statements in SQLite.
**
** $Id: insert.c,v 1.53 2002/05/15 08:30:13 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
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
76




77
78
79
80
81
82
83
  Token *pTableName,    /* Name of table into which we are inserting */
  ExprList *pList,      /* List of values to be inserted */
  Select *pSelect,      /* A SELECT statement to use as the data source */
  IdList *pColumn,      /* Column names corresponding to IDLIST. */
  int onError           /* How to handle constraint errors */
){
  Table *pTab;          /* The table to insert into */
  char *zTab;           /* Name of the table into which we are inserting */
  int i, j, idx;        /* Loop counters */
  Vdbe *v;              /* Generate code into this virtual machine */
  Index *pIdx;          /* For looping over indices of the table */
  int srcTab;           /* Date comes from this temporary cursor if >=0 */
  int nColumn;          /* Number of columns in the data */
  int base;             /* First available cursor */
  int iCont, iBreak;    /* Beginning and end of the loop over srcTab */
  sqlite *db;           /* The main database structure */
  int openOp;           /* Opcode used to open cursors */
  int keyColumn = -1;   /* Column that is the INTEGER PRIMARY KEY */
  int endOfLoop;        /* Label for the end of the insertion loop */




  if( pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
  db = pParse->db;

  /* Locate the table into which we will be inserting new information.
  */
  zTab = sqliteTableNameFromToken(pTableName);
  if( zTab==0 ) goto insert_cleanup;






  pTab = sqliteTableNameToTable(pParse, zTab);
















  sqliteFree(zTab);


  if( pTab==0 ) goto insert_cleanup;
  assert( pTab->pSelect==0 );  /* This table is not a VIEW */

  /* Allocate a VDBE
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto insert_cleanup;
  if( pSelect ){
    sqliteBeginMultiWriteOperation(pParse);
  }else{
    sqliteBeginWriteOperation(pParse);
  }





  /* Figure out how many columns of data are supplied.  If the data
  ** is coming from a SELECT statement, then this step has to generate
  ** all the code to implement the SELECT statement and leave the data
  ** in a temporary table.  If data is coming from an expression list,
  ** then we just have to count the number of expressions.
  */







|











>
>
>








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

>
>

<





|




>
>
>
>







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
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
  Token *pTableName,    /* Name of table into which we are inserting */
  ExprList *pList,      /* List of values to be inserted */
  Select *pSelect,      /* A SELECT statement to use as the data source */
  IdList *pColumn,      /* Column names corresponding to IDLIST. */
  int onError           /* How to handle constraint errors */
){
  Table *pTab;          /* The table to insert into */
  char *zTab = 0;       /* Name of the table into which we are inserting */
  int i, j, idx;        /* Loop counters */
  Vdbe *v;              /* Generate code into this virtual machine */
  Index *pIdx;          /* For looping over indices of the table */
  int srcTab;           /* Date comes from this temporary cursor if >=0 */
  int nColumn;          /* Number of columns in the data */
  int base;             /* First available cursor */
  int iCont, iBreak;    /* Beginning and end of the loop over srcTab */
  sqlite *db;           /* The main database structure */
  int openOp;           /* Opcode used to open cursors */
  int keyColumn = -1;   /* Column that is the INTEGER PRIMARY KEY */
  int endOfLoop;        /* Label for the end of the insertion loop */

  int row_triggers_exist = 0; /* True if there are FOR EACH ROW triggers */
  int newIdx = -1;

  if( pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
  db = pParse->db;

  /* Locate the table into which we will be inserting new information.
  */
  zTab = sqliteTableNameFromToken(pTableName);
  if( zTab==0 ) goto insert_cleanup;
  pTab = sqliteFindTable(pParse->db, zTab);
  if( pTab==0 ){
    sqliteSetString(&pParse->zErrMsg, "no such table: ", zTab, 0);
    pParse->nErr++;
    goto insert_cleanup;
  }

  /* Ensure that:
  *  (a) the table is not read-only, 
  *  (b) that if it is a view then ON INSERT triggers exist
  */
  row_triggers_exist = 
    sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT, 
	TK_BEFORE, TK_ROW, 0) ||
    sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT, TK_AFTER, TK_ROW, 0);
  if( pTab->readOnly || (pTab->pSelect && !row_triggers_exist) ){
    sqliteSetString(&pParse->zErrMsg, 
      pTab->pSelect ? "view " : "table ",
      zTab,
      " may not be modified", 0);
    pParse->nErr++;
    goto insert_cleanup;
  }
  sqliteFree(zTab);
  zTab = 0;

  if( pTab==0 ) goto insert_cleanup;


  /* Allocate a VDBE
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto insert_cleanup;
  if( pSelect || row_triggers_exist ){
    sqliteBeginMultiWriteOperation(pParse);
  }else{
    sqliteBeginWriteOperation(pParse);
  }

  /* if there are row triggers, allocate a temp table for new.* references. */
  if (row_triggers_exist)
    newIdx = pParse->nTab++;

  /* Figure out how many columns of data are supplied.  If the data
  ** is coming from a SELECT statement, then this step has to generate
  ** all the code to implement the SELECT statement and leave the data
  ** in a temporary table.  If data is coming from an expression list,
  ** then we just have to count the number of expressions.
  */
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
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
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
298
299

300
301
302
303
304
305
306
  ** key, the set the keyColumn variable to the primary key column index
  ** in the original table definition.
  */
  if( pColumn==0 ){
    keyColumn = pTab->iPKey;
  }

  /* Open cursors into the table that is received the new data and


  ** all indices of that table.

  */






  base = pParse->nTab;
  openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
  sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
  sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
  for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
    sqliteVdbeAddOp(v, openOp, idx+base, pIdx->tnum);
    sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
  }
  pParse->nTab += idx;

  /* Initialize the count of rows to be inserted
  */
  if( db->flags & SQLITE_CountRows ){
    sqliteVdbeAddOp(v, OP_Integer, 0, 0);  /* Initialize the row count */
  }

  /* If the data source is a SELECT statement, then we have to create
  ** a loop because there might be multiple rows of data.  If the data
  ** source is an expression list, then exactly one row will be inserted
  ** and the loop is not used.
  */
  if( srcTab>=0 ){
    iBreak = sqliteVdbeMakeLabel(v);
    sqliteVdbeAddOp(v, OP_Rewind, srcTab, iBreak);
    iCont = sqliteVdbeCurrentAddr(v);
  }














































  /* Push the record number for the new entry onto the stack.  The
  ** record number is a randomly generate integer created by NewRecno
  ** except when the table has an INTEGER PRIMARY KEY column, in which
  ** case the record number is the same as that column. 
  */

  if( keyColumn>=0 ){
    if( srcTab>=0 ){
      sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
    }else{
      int addr;
      sqliteExprCode(pParse, pList->a[keyColumn].pExpr);

      /* If the PRIMARY KEY expression is NULL, then use OP_NewRecno
      ** to generate a unique primary key value.
      */
      addr = sqliteVdbeAddOp(v, OP_Dup, 0, 1);
      sqliteVdbeAddOp(v, OP_NotNull, 0, addr+4);
      sqliteVdbeAddOp(v, OP_Pop, 1, 0);
      sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
    }
    sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
  }else{
    sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
  }

  /* Push onto the stack, data for all columns of the new entry, beginning
  ** with the first column.
  */
  for(i=0; i<pTab->nCol; i++){
    if( i==pTab->iPKey ){
      /* The value of the INTEGER PRIMARY KEY column is always a NULL.
      ** Whenever this column is read, the record number will be substituted
      ** in its place.  So will fill this column with a NULL to avoid
      ** taking up data space with information that will never be used. */
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      continue;
    }
    if( pColumn==0 ){
      j = i;
    }else{
      for(j=0; j<pColumn->nId; j++){
        if( pColumn->a[j].idx==i ) break;
      }
    }
    if( pColumn && j>=pColumn->nId ){
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
    }else if( srcTab>=0 ){
      sqliteVdbeAddOp(v, OP_Column, srcTab, j); 
    }else{
      sqliteExprCode(pParse, pList->a[j].pExpr);
    }
  }

  /* Generate code to check constraints and generate index keys and
  ** do the insertion.
  */
  endOfLoop = sqliteVdbeMakeLabel(v);
  sqliteGenerateConstraintChecks(pParse, pTab, base, 0,0,0, onError, endOfLoop);
  sqliteCompleteInsertion(pParse, pTab, base, 0,0,0);

  /* Update the count of rows that are inserted
  */
  if( (db->flags & SQLITE_CountRows)!=0 ){
    sqliteVdbeAddOp(v, OP_AddImm, 1, 0);

















  }

  /* The bottom of the loop, if the data source is a SELECT statement
  */
  sqliteVdbeResolveLabel(v, endOfLoop);
  if( srcTab>=0 ){
    sqliteVdbeAddOp(v, OP_Next, srcTab, iCont);
    sqliteVdbeResolveLabel(v, iBreak);
    sqliteVdbeAddOp(v, OP_Close, srcTab, 0);
  }



  sqliteVdbeAddOp(v, OP_Close, base, 0);
  for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
    sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
  }


  sqliteEndWriteOperation(pParse);

  /*
  ** Return the number of rows inserted.
  */
  if( db->flags & SQLITE_CountRows ){
    sqliteVdbeAddOp(v, OP_ColumnCount, 1, 0);
    sqliteVdbeAddOp(v, OP_ColumnName, 0, 0);
    sqliteVdbeChangeP3(v, -1, "rows inserted", P3_STATIC);
    sqliteVdbeAddOp(v, OP_Callback, 1, 0);
  }

insert_cleanup:
  if( pList ) sqliteExprListDelete(pList);
  if( pSelect ) sqliteSelectDelete(pSelect);

  sqliteIdListDelete(pColumn);
}

/*
** Generate code to do a constraint check prior to an INSERT or an UPDATE.
**
** When this routine is called, the stack contains (from bottom to top)







|
>
>
|
>

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











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






>
|
|
|
|
|
|

|
|
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|
|

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



|






>
>
>
|
|
|
|
>
>



|

|









>







199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227





228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
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
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
363
364
365
366
367
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
396
397
398
399
400
401
402
403
404
405
406
407
408
409
  ** key, the set the keyColumn variable to the primary key column index
  ** in the original table definition.
  */
  if( pColumn==0 ){
    keyColumn = pTab->iPKey;
  }

  /* Open the temp table for FOR EACH ROW triggers */
  if (row_triggers_exist)
    sqliteVdbeAddOp(v, OP_OpenTemp, newIdx, 0);
    
  /* Initialize the count of rows to be inserted
  */
  if( db->flags & SQLITE_CountRows && !pParse->trigStack){
    sqliteVdbeAddOp(v, OP_Integer, 0, 0);  /* Initialize the row count */
  }

  /* Open tables and indices if there are no row triggers */
  if (!row_triggers_exist) {
    base = pParse->nTab;
    openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
    sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
    sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
    for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
      sqliteVdbeAddOp(v, openOp, idx+base, pIdx->tnum);
      sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
    }
    pParse->nTab += idx;
  }






  /* If the data source is a SELECT statement, then we have to create
  ** a loop because there might be multiple rows of data.  If the data
  ** source is an expression list, then exactly one row will be inserted
  ** and the loop is not used.
  */
  if( srcTab>=0 ){
    iBreak = sqliteVdbeMakeLabel(v);
    sqliteVdbeAddOp(v, OP_Rewind, srcTab, iBreak);
    iCont = sqliteVdbeCurrentAddr(v);
  }

  if (row_triggers_exist) {

    /* build the new.* reference row */
    sqliteVdbeAddOp(v, OP_Integer, 13, 0);
    for(i=0; i<pTab->nCol; i++){
      if( pColumn==0 ){
	j = i;
      }else{
	for(j=0; j<pColumn->nId; j++){
	  if( pColumn->a[j].idx==i ) break;
	}
      }
      if( pColumn && j>=pColumn->nId ){
	sqliteVdbeAddOp(v, OP_String, 0, 0);
	sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
      }else if( srcTab>=0 ){
	sqliteVdbeAddOp(v, OP_Column, srcTab, j); 
      }else{
	sqliteExprCode(pParse, pList->a[j].pExpr);
      }
    }
    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
    sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
    sqliteVdbeAddOp(v, OP_Rewind, newIdx, 0);

    /* Fire BEFORE triggers */
    if (
    sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_BEFORE, pTab, newIdx, -1, 
	onError)
       ) goto insert_cleanup;

    /* Open the tables and indices for the INSERT */
    if (!pTab->pSelect) {
      base = pParse->nTab;
      openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
      sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
      sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
      for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
	sqliteVdbeAddOp(v, openOp, idx+base, pIdx->tnum);
	sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
      }
      pParse->nTab += idx;
    }
  }

  /* Push the record number for the new entry onto the stack.  The
  ** record number is a randomly generate integer created by NewRecno
  ** except when the table has an INTEGER PRIMARY KEY column, in which
  ** case the record number is the same as that column. 
  */
  if (!pTab->pSelect) {
    if( keyColumn>=0 ){
      if( srcTab>=0 ){
	sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
      }else{
	int addr;
	sqliteExprCode(pParse, pList->a[keyColumn].pExpr);

	/* If the PRIMARY KEY expression is NULL, then use OP_NewRecno
	 ** to generate a unique primary key value.
	 */
	addr = sqliteVdbeAddOp(v, OP_Dup, 0, 1);
	sqliteVdbeAddOp(v, OP_NotNull, 0, addr+4);
	sqliteVdbeAddOp(v, OP_Pop, 1, 0);
	sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
      }
      sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
    }else{
      sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
    }

    /* Push onto the stack, data for all columns of the new entry, beginning
     ** with the first column.
     */
    for(i=0; i<pTab->nCol; i++){
      if( i==pTab->iPKey ){
	/* The value of the INTEGER PRIMARY KEY column is always a NULL.
	 ** Whenever this column is read, the record number will be substituted
	 ** in its place.  So will fill this column with a NULL to avoid
	 ** taking up data space with information that will never be used. */
	sqliteVdbeAddOp(v, OP_String, 0, 0);
	continue;
      }
      if( pColumn==0 ){
	j = i;
      }else{
	for(j=0; j<pColumn->nId; j++){
	  if( pColumn->a[j].idx==i ) break;
	}
      }
      if( pColumn && j>=pColumn->nId ){
	sqliteVdbeAddOp(v, OP_String, 0, 0);
	sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
      }else if( srcTab>=0 ){
	sqliteVdbeAddOp(v, OP_Column, srcTab, j); 
      }else{
	sqliteExprCode(pParse, pList->a[j].pExpr);
      }
    }

    /* Generate code to check constraints and generate index keys and
     ** do the insertion.
     */
    endOfLoop = sqliteVdbeMakeLabel(v);
    sqliteGenerateConstraintChecks(pParse, pTab, base, 0,0,0,onError,endOfLoop);
    sqliteCompleteInsertion(pParse, pTab, base, 0,0,0);

    /* Update the count of rows that are inserted
     */
    if( (db->flags & SQLITE_CountRows)!=0 && !pParse->trigStack){
      sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
    }
  }

  if (row_triggers_exist) {
    /* Close all tables opened */
    if (!pTab->pSelect) {
      sqliteVdbeAddOp(v, OP_Close, base, 0);
      for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
	sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
      }
    }

    /* Code AFTER triggers */
    if (
	sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_AFTER, pTab, newIdx, -1, 
	  onError)
       ) goto insert_cleanup;
  }

  /* The bottom of the loop, if the data source is a SELECT statement
   */
  sqliteVdbeResolveLabel(v, endOfLoop);
  if( srcTab>=0 ){
    sqliteVdbeAddOp(v, OP_Next, srcTab, iCont);
    sqliteVdbeResolveLabel(v, iBreak);
    sqliteVdbeAddOp(v, OP_Close, srcTab, 0);
  }

  if (!row_triggers_exist) {
    /* Close all tables opened */
    sqliteVdbeAddOp(v, OP_Close, base, 0);
    for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
      sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
    }
  }

  sqliteEndWriteOperation(pParse);

  /*
   ** Return the number of rows inserted.
  */
  if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
    sqliteVdbeAddOp(v, OP_ColumnCount, 1, 0);
    sqliteVdbeAddOp(v, OP_ColumnName, 0, 0);
    sqliteVdbeChangeP3(v, -1, "rows inserted", P3_STATIC);
    sqliteVdbeAddOp(v, OP_Callback, 1, 0);
  }

insert_cleanup:
  if( pList ) sqliteExprListDelete(pList);
  if( pSelect ) sqliteSelectDelete(pSelect);
  if ( zTab ) sqliteFree(zTab);
  sqliteIdListDelete(pColumn);
}

/*
** Generate code to do a constraint check prior to an INSERT or an UPDATE.
**
** When this routine is called, the stack contains (from bottom to top)
569
570
571
572
573
574
575
576
577
578
579
580
  assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
  for(i=nIdx-1; i>=0; i--){
    if( aIdxUsed && aIdxUsed[i]==0 ) continue;
    sqliteVdbeAddOp(v, OP_IdxPut, base+i+1, 0);
  }
  sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
  sqliteVdbeAddOp(v, OP_PutIntKey, base, 1);
  if( isUpdate && recnoChng ){
    sqliteVdbeAddOp(v, OP_Pop, 1, 0);
  }
}







|




672
673
674
675
676
677
678
679
680
681
682
683
  assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
  for(i=nIdx-1; i>=0; i--){
    if( aIdxUsed && aIdxUsed[i]==0 ) continue;
    sqliteVdbeAddOp(v, OP_IdxPut, base+i+1, 0);
  }
  sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
  sqliteVdbeAddOp(v, OP_PutIntKey, base, pParse->trigStack?0:1);
  if( isUpdate && recnoChng ){
    sqliteVdbeAddOp(v, OP_Pop, 1, 0);
  }
}
Changes to src/main.c.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** 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.71 2002/05/10 13:14:07 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

/*
** This is the callback routine for the code that initializes the
** database.  See sqliteInit() below for additional information.







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** 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.72 2002/05/15 08:30:13 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"

/*
** This is the callback routine for the code that initializes the
** database.  See sqliteInit() below for additional information.
322
323
324
325
326
327
328


329
330
331
332
333
334
335

  /* Allocate the sqlite data structure */
  db = sqliteMalloc( sizeof(sqlite) );
  if( pzErrMsg ) *pzErrMsg = 0;
  if( db==0 ) goto no_mem_on_open;
  sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0);
  sqliteHashInit(&db->idxHash, SQLITE_HASH_STRING, 0);


  sqliteHashInit(&db->tblDrop, SQLITE_HASH_POINTER, 0);
  sqliteHashInit(&db->idxDrop, SQLITE_HASH_POINTER, 0);
  sqliteHashInit(&db->aFunc, SQLITE_HASH_STRING, 1);
  sqliteRegisterBuildinFunctions(db);
  db->onError = OE_Default;
  db->priorNewRowid = 0;
  db->magic = SQLITE_MAGIC_BUSY;







>
>







322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337

  /* Allocate the sqlite data structure */
  db = sqliteMalloc( sizeof(sqlite) );
  if( pzErrMsg ) *pzErrMsg = 0;
  if( db==0 ) goto no_mem_on_open;
  sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0);
  sqliteHashInit(&db->idxHash, SQLITE_HASH_STRING, 0);
  sqliteHashInit(&db->trigHash, SQLITE_HASH_STRING, 0);
  sqliteHashInit(&db->trigDrop, SQLITE_HASH_STRING, 0);
  sqliteHashInit(&db->tblDrop, SQLITE_HASH_POINTER, 0);
  sqliteHashInit(&db->idxDrop, SQLITE_HASH_POINTER, 0);
  sqliteHashInit(&db->aFunc, SQLITE_HASH_STRING, 1);
  sqliteRegisterBuildinFunctions(db);
  db->onError = OE_Default;
  db->priorNewRowid = 0;
  db->magic = SQLITE_MAGIC_BUSY;
379
380
381
382
383
384
385

386
387
388

389
390
















391
392
393
394
395
396
397
** This routine erases the stored schema.  This erasure occurs because
** either the database is being closed or because some other process
** changed the schema and this process needs to reread it.
*/
static void clearHashTable(sqlite *db, int preserveTemps){
  HashElem *pElem;
  Hash temp1;

  assert( sqliteHashFirst(&db->tblDrop)==0 ); /* There can not be uncommitted */
  assert( sqliteHashFirst(&db->idxDrop)==0 ); /*   DROP TABLEs or DROP INDEXs */
  temp1 = db->tblHash;

  sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0);
  sqliteHashClear(&db->idxHash);
















  for(pElem=sqliteHashFirst(&temp1); pElem; pElem=sqliteHashNext(pElem)){
    Table *pTab = sqliteHashData(pElem);
    if( preserveTemps && pTab->isTemp ){
      Index *pIdx;
      int nName = strlen(pTab->zName);
      Table *pOld = sqliteHashInsert(&db->tblHash, pTab->zName, nName+1, pTab);
      if( pOld!=0 ){







>



>
|

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







381
382
383
384
385
386
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
414
415
416
417
** This routine erases the stored schema.  This erasure occurs because
** either the database is being closed or because some other process
** changed the schema and this process needs to reread it.
*/
static void clearHashTable(sqlite *db, int preserveTemps){
  HashElem *pElem;
  Hash temp1;
  Hash temp2;
  assert( sqliteHashFirst(&db->tblDrop)==0 ); /* There can not be uncommitted */
  assert( sqliteHashFirst(&db->idxDrop)==0 ); /*   DROP TABLEs or DROP INDEXs */
  temp1 = db->tblHash;
  temp2 = db->trigHash;
  sqliteHashInit(&db->trigHash, SQLITE_HASH_STRING, 0);
  sqliteHashClear(&db->idxHash);

  for (pElem=sqliteHashFirst(&temp2); pElem; pElem=sqliteHashNext(pElem)){
    Trigger * pTrigger = sqliteHashData(pElem);
    Table *pTab = sqliteFindTable(db, pTrigger->table);
    assert(pTab);
    if (pTab->isTemp) { 
      sqliteHashInsert(&db->trigHash, pTrigger->name, strlen(pTrigger->name), 
	  pTrigger);
    } else {
      sqliteDeleteTrigger(pTrigger);
    }
  }
  sqliteHashClear(&temp2);

  sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0);

  for(pElem=sqliteHashFirst(&temp1); pElem; pElem=sqliteHashNext(pElem)){
    Table *pTab = sqliteHashData(pElem);
    if( preserveTemps && pTab->isTemp ){
      Index *pIdx;
      int nName = strlen(pTab->zName);
      Table *pOld = sqliteHashInsert(&db->tblHash, pTab->zName, nName+1, pTab);
      if( pOld!=0 ){
409
410
411
412
413
414
415

416
417
418
419
420
421
422
        }
      }
    }else{
      sqliteDeleteTable(db, pTab);
    }
  }
  sqliteHashClear(&temp1);

  db->flags &= ~SQLITE_Initialized;
}

/*
** Return the ROWID of the most recent insert
*/
int sqlite_last_insert_rowid(sqlite *db){







>







429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
        }
      }
    }else{
      sqliteDeleteTable(db, pTab);
    }
  }
  sqliteHashClear(&temp1);

  db->flags &= ~SQLITE_Initialized;
}

/*
** Return the ROWID of the most recent insert
*/
int sqlite_last_insert_rowid(sqlite *db){
454
455
456
457
458
459
460

461
462
463
464
465
466
467
}

/*
** Return TRUE if the given SQL string ends in a semicolon.
*/
int sqlite_complete(const char *zSql){
  int isComplete = 0;

  while( *zSql ){
    switch( *zSql ){
      case ';': {
        isComplete = 1;
        break;
      }
      case ' ':







>







475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
}

/*
** Return TRUE if the given SQL string ends in a semicolon.
*/
int sqlite_complete(const char *zSql){
  int isComplete = 0;
  int seenCreate = 0;
  while( *zSql ){
    switch( *zSql ){
      case ';': {
        isComplete = 1;
        break;
      }
      case ' ':
497
498
499
500
501
502
503










504
505
506
507
508
509
510
          break;
        }
        while( *zSql && *zSql!='\n' ){ zSql++; }
        if( *zSql==0 ) return isComplete;
        break;
      } 
      default: {










        isComplete = 0;
        break;
      }
    }
    zSql++;
  }
  return isComplete;







>
>
>
>
>
>
>
>
>
>







519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
          break;
        }
        while( *zSql && *zSql!='\n' ){ zSql++; }
        if( *zSql==0 ) return isComplete;
        break;
      } 
      default: {
        if (seenCreate && !sqliteStrNICmp(zSql, "trigger", 7)) 
	  while (sqliteStrNICmp(zSql, "end", 3))
	    if (!*++zSql) return 0;

        if (!sqliteStrNICmp(zSql, "create", 6)) {
	  zSql = zSql + 5;
	  seenCreate = 1;
	} else 
	  seenCreate = 0;

        isComplete = 0;
        break;
      }
    }
    zSql++;
  }
  return isComplete;
Changes to src/parse.y.
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
39
40
41
42
**
*************************************************************************
** 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.63 2002/05/08 21:46:15 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
  pParse->sErrToken = TOKEN;
}
%name sqliteParser
%include {
#include "sqliteInt.h"
#include "parse.h"

/*
** A structure for holding two integers
*/
struct twoint { int a,b; };





}

// These are extra tokens used by the lexer but never seen by the
// parser.  We put them in a rule so that the parser generator will
// add them to the parse.h output file.
//
%nonassoc END_OF_FILE ILLEGAL SPACE UNCLOSED_STRING COMMENT FUNCTION







|


















>
>
>
>
>







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
39
40
41
42
43
44
45
46
47
**
*************************************************************************
** 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.64 2002/05/15 08:30:14 danielk1977 Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
  pParse->sErrToken = TOKEN;
}
%name sqliteParser
%include {
#include "sqliteInt.h"
#include "parse.h"

/*
** A structure for holding two integers
*/
struct twoint { int a,b; };

/*
** A structure for holding an integer and an IdList
*/
struct int_idlist { int a; IdList * b; };
}

// These are extra tokens used by the lexer but never seen by the
// parser.  We put them in a rule so that the parser generator will
// add them to the parse.h output file.
//
%nonassoc END_OF_FILE ILLEGAL SPACE UNCLOSED_STRING COMMENT FUNCTION
624
625
626
627
628
629
630




























































cmd ::= PRAGMA ids(X).                   {sqlitePragma(pParse,&X,&X,0);}
plus_num(A) ::= plus_opt number(X).   {A = X;}
minus_num(A) ::= MINUS number(X).     {A = X;}
number(A) ::= INTEGER(X).  {A = X;}
number(A) ::= FLOAT(X).    {A = X;}
plus_opt ::= PLUS.
plus_opt ::= .



































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
cmd ::= PRAGMA ids(X).                   {sqlitePragma(pParse,&X,&X,0);}
plus_num(A) ::= plus_opt number(X).   {A = X;}
minus_num(A) ::= MINUS number(X).     {A = X;}
number(A) ::= INTEGER(X).  {A = X;}
number(A) ::= FLOAT(X).    {A = X;}
plus_opt ::= PLUS.
plus_opt ::= .

//////////////////////////// The CREATE TRIGGER command /////////////////////
cmd ::= CREATE(A) TRIGGER ids(B) trigger_time(C) trigger_event(D) ON ids(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; }

%type trigger_event {struct int_idlist}
trigger_event(A) ::= DELETE. { A.a = TK_DELETE; A.b = 0; }
trigger_event(A) ::= INSERT. { A.a = TK_INSERT; A.b = 0; }
trigger_event(A) ::= UPDATE. { A.a = TK_UPDATE; A.b = 0;}
trigger_event(A) ::= UPDATE OF inscollist(X). {A.a = TK_UPDATE; A.b = X; }

%type foreach_clause {int}
foreach_clause(A) ::= .                   { A = TK_ROW; }
foreach_clause(A) ::= FOR EACH ROW.       { A = TK_ROW; }
foreach_clause(A) ::= FOR EACH STATEMENT. { A = TK_STATEMENT; }

%type when_clause {Expr *}
when_clause(A) ::= .             { A = 0; }
when_clause(A) ::= WHEN expr(X). { A = X; }

%type trigger_cmd_list {TriggerStep *}
trigger_cmd_list(A) ::= trigger_cmd(X) SEMI trigger_cmd_list(Y). {
  X->pNext = Y ; A = X; }
trigger_cmd_list(A) ::= . { A = 0; }

%type trigger_cmd {TriggerStep *}
// UPDATE 
trigger_cmd(A) ::= UPDATE orconf(R) ids(X) SET setlist(Y) where_opt(Z).  
               { A = sqliteTriggerUpdateStep(&X, Y, Z, R); }

// INSERT
trigger_cmd(A) ::= INSERT orconf(R) INTO ids(X) inscollist_opt(F) 
  VALUES LP itemlist(Y) RP.  
{A = sqliteTriggerInsertStep(&X, F, Y, 0, R);}

trigger_cmd(A) ::= INSERT orconf(R) INTO ids(X) inscollist_opt(F) select(S).
               {A = sqliteTriggerInsertStep(&X, F, 0, S, R);}

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

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

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

Changes to src/sqliteInt.h.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.107 2002/05/10 13:14:07 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.108 2002/05/15 08:30:14 danielk1977 Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
140
141
142
143
144
145
146



147
148
149
150
151
152
153
typedef struct Token Token;
typedef struct IdList IdList;
typedef struct WhereInfo WhereInfo;
typedef struct WhereLevel WhereLevel;
typedef struct Select Select;
typedef struct AggExpr AggExpr;
typedef struct FuncDef FuncDef;




/*
** Each database is an instance of the following structure
*/
struct sqlite {
  Btree *pBe;                   /* The B*Tree backend */
  Btree *pBeTemp;               /* Backend for session temporary tables */







>
>
>







140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
typedef struct Token Token;
typedef struct IdList IdList;
typedef struct WhereInfo WhereInfo;
typedef struct WhereLevel WhereLevel;
typedef struct Select Select;
typedef struct AggExpr AggExpr;
typedef struct FuncDef FuncDef;
typedef struct Trigger Trigger;
typedef struct TriggerStep TriggerStep;
typedef struct TriggerStack TriggerStack;

/*
** Each database is an instance of the following structure
*/
struct sqlite {
  Btree *pBe;                   /* The B*Tree backend */
  Btree *pBeTemp;               /* Backend for session temporary tables */
166
167
168
169
170
171
172



173
174
175
176
177
178
179
  Hash aFunc;                   /* All functions that can be in SQL exprs */
  int lastRowid;                /* ROWID of most recent insert */
  int priorNewRowid;            /* Last randomly generated ROWID */
  int onError;                  /* Default conflict algorithm */
  int magic;                    /* Magic number for detect library misuse */
  int nChange;                  /* Number of rows changed */
  int recursionDepth;           /* Number of nested calls to sqlite_exec() */



};

/*
** Possible values for the sqlite.flags.
*/
#define SQLITE_VdbeTrace      0x00000001  /* True to trace VDBE execution */
#define SQLITE_Initialized    0x00000002  /* True after initialization */







>
>
>







169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
  Hash aFunc;                   /* All functions that can be in SQL exprs */
  int lastRowid;                /* ROWID of most recent insert */
  int priorNewRowid;            /* Last randomly generated ROWID */
  int onError;                  /* Default conflict algorithm */
  int magic;                    /* Magic number for detect library misuse */
  int nChange;                  /* Number of rows changed */
  int recursionDepth;           /* Number of nested calls to sqlite_exec() */

  Hash trigHash;                /* All triggers indexed by name */
  Hash trigDrop;                /* Uncommited dropped triggers */
};

/*
** Possible values for the sqlite.flags.
*/
#define SQLITE_VdbeTrace      0x00000001  /* True to trace VDBE execution */
#define SQLITE_Initialized    0x00000002  /* True after initialization */
266
267
268
269
270
271
272


273
274
275
276
277
278
279
  Select *pSelect; /* NULL for tables.  Points to definition if a view. */
  u8 readOnly;     /* True if this table should not be written by the user */
  u8 isCommit;     /* True if creation of this table has been committed */
  u8 isTemp;       /* True if stored in db->pBeTemp instead of db->pBe */
  u8 isTransient;  /* True if automatically deleted when VDBE finishes */
  u8 hasPrimKey;   /* True if there exists a primary key */
  u8 keyConf;      /* What to do in case of uniqueness conflict on iPKey */


};

/*
** SQLite supports 5 different ways to resolve a contraint
** error.  ROLLBACK processing means that a constraint violation
** causes the operation in proces to fail and for the current transaction
** to be rolled back.  ABORT processing means the operation in process







>
>







272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
  Select *pSelect; /* NULL for tables.  Points to definition if a view. */
  u8 readOnly;     /* True if this table should not be written by the user */
  u8 isCommit;     /* True if creation of this table has been committed */
  u8 isTemp;       /* True if stored in db->pBeTemp instead of db->pBe */
  u8 isTransient;  /* True if automatically deleted when VDBE finishes */
  u8 hasPrimKey;   /* True if there exists a primary key */
  u8 keyConf;      /* What to do in case of uniqueness conflict on iPKey */

  Trigger *pTrigger; /* List of SQL triggers on this table */
};

/*
** SQLite supports 5 different ways to resolve a contraint
** error.  ROLLBACK processing means that a constraint violation
** causes the operation in proces to fail and for the current transaction
** to be rolled back.  ABORT processing means the operation in process
546
547
548
549
550
551
552


553
554






















































555
556
557
558
559
560
561
  int nSet;            /* Number of sets used so far */
  int nAgg;            /* Number of aggregate expressions */
  AggExpr *aAgg;       /* An array of aggregate expressions */
  int useAgg;          /* If true, extract field values from the aggregator
                       ** while generating expressions.  Normally false */
  int schemaVerified;  /* True if an OP_VerifySchema has been coded someplace
                       ** other than after an OP_Transaction */


};























































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







>
>


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







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
  int nSet;            /* Number of sets used so far */
  int nAgg;            /* Number of aggregate expressions */
  AggExpr *aAgg;       /* An array of aggregate expressions */
  int useAgg;          /* If true, extract field values from the aggregator
                       ** while generating expressions.  Normally false */
  int schemaVerified;  /* True if an OP_VerifySchema has been coded someplace
                       ** other than after an OP_Transaction */

  TriggerStack * trigStack;
};

struct TriggerStack {
  Trigger * pTrigger;
  Table *   pTab;         /* Table that triggers are currently being coded as */
  int       newIdx;       /* Index of "new" temp table */
  int       oldIdx;       /* Index of "old" temp table */
  int       orconf;       /* Current orconf policy */
  struct TriggerStack * pNext;
};
struct TriggerStep {
  int op;               /* One of TK_DELETE, TK_UPDATE, TK_INSERT, TK_SELECT */
  int orconf;

  Select * pSelect;     /* Valid for SELECT and sometimes 
			   INSERT steps (when pExprList == 0) */
  Token target;         /* Valid for DELETE, UPDATE, INSERT steps */
  Expr * pWhere;        /* Valid for DELETE, UPDATE steps */
  ExprList * pExprList; /* Valid for UPDATE statements and sometimes 
			   INSERT steps (when pSelect == 0)         */
  IdList *pIdList;      /* Valid for INSERT statements only */

  TriggerStep * pNext;  /* Next in the link-list */
};
struct Trigger {
  char * name;             /* The name of the trigger                        */
  char * table;            /* The table or view to which the trigger applies */
  int    op;               /* One of TK_DELETE, TK_UPDATE, TK_INSERT         */
  int    tr_tm;            /* One of TK_BEFORE, TK_AFTER, TK_INSTEAD         */
  Expr * pWhen;            /* The WHEN clause of the expresion (may be NULL) */
  IdList * pColumns;       /* If this is an UPDATE OF <column-list> trigger,
			      the column names are stored in this list       */
  int foreach;             /* One of TK_ROW or TK_STATEMENT */

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

  char * strings;  /* pointer to the allocation of Token strings */
  Trigger * pNext; /* Next trigger associated with the table */
  int isCommit;
};

TriggerStep * sqliteTriggerSelectStep(Select *);
TriggerStep * sqliteTriggerInsertStep(Token *, IdList *, ExprList *, 
    Select *, int);
TriggerStep * sqliteTriggerUpdateStep(Token *, ExprList *, Expr *, int);
TriggerStep * sqliteTriggerDeleteStep(Token *, Expr *);

extern int always_code_trigger_setup;

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 * pParse, int op, ExprList *, int tr_tm,   Table * tbl, int newTable, int oldTable, int onError);

void sqliteViewTriggers(Parse *, Table *, Expr *, int, ExprList *);

/*
** Internal function prototypes
*/
int sqliteStrICmp(const char *, const char *);
int sqliteStrNICmp(const char *, const char *, int);
int sqliteHashNoCase(const char *, int);
int sqliteCompare(const char *, const char *);
658
659
660
661
662
663
664


IdList *sqliteIdListDup(IdList*);
Select *sqliteSelectDup(Select*);
FuncDef *sqliteFindFunction(sqlite*,const char*,int,int,int);
void sqliteRegisterBuildinFunctions(sqlite*);
int sqliteSafetyOn(sqlite*);
int sqliteSafetyOff(sqlite*);
int sqliteSafetyCheck(sqlite*);









>
>
722
723
724
725
726
727
728
729
730
IdList *sqliteIdListDup(IdList*);
Select *sqliteSelectDup(Select*);
FuncDef *sqliteFindFunction(sqlite*,const char*,int,int,int);
void sqliteRegisterBuildinFunctions(sqlite*);
int sqliteSafetyOn(sqlite*);
int sqliteSafetyOff(sqlite*);
int sqliteSafetyCheck(sqlite*);

void changeCookie(sqlite *);
Changes to src/tokenize.c.
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
*************************************************************************
** 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.40 2002/03/24 13:13:29 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include <stdlib.h>

/*







|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
*************************************************************************
** 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.41 2002/05/15 08:30:14 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include <stdlib.h>

/*
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

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
};

/*
** These are the keywords
*/
static Keyword aKeywordTable[] = {
  { "ABORT",             0, TK_ABORT,            0 },

  { "ALL",               0, TK_ALL,              0 },
  { "AND",               0, TK_AND,              0 },
  { "AS",                0, TK_AS,               0 },
  { "ASC",               0, TK_ASC,              0 },

  { "BEGIN",             0, TK_BEGIN,            0 },
  { "BETWEEN",           0, TK_BETWEEN,          0 },
  { "BY",                0, TK_BY,               0 },
  { "CASE",              0, TK_CASE,             0 },
  { "CHECK",             0, TK_CHECK,            0 },
  { "CLUSTER",           0, TK_CLUSTER,          0 },
  { "COMMIT",            0, TK_COMMIT,           0 },
  { "CONFLICT",          0, TK_CONFLICT,         0 },
  { "CONSTRAINT",        0, TK_CONSTRAINT,       0 },
  { "COPY",              0, TK_COPY,             0 },
  { "CREATE",            0, TK_CREATE,           0 },
  { "DEFAULT",           0, TK_DEFAULT,          0 },
  { "DELETE",            0, TK_DELETE,           0 },
  { "DELIMITERS",        0, TK_DELIMITERS,       0 },
  { "DESC",              0, TK_DESC,             0 },
  { "DISTINCT",          0, TK_DISTINCT,         0 },
  { "DROP",              0, TK_DROP,             0 },
  { "END",               0, TK_END,              0 },

  { "ELSE",              0, TK_ELSE,             0 },
  { "EXCEPT",            0, TK_EXCEPT,           0 },
  { "EXPLAIN",           0, TK_EXPLAIN,          0 },
  { "FAIL",              0, TK_FAIL,             0 },

  { "FROM",              0, TK_FROM,             0 },
  { "GLOB",              0, TK_GLOB,             0 },
  { "GROUP",             0, TK_GROUP,            0 },
  { "HAVING",            0, TK_HAVING,           0 },
  { "IGNORE",            0, TK_IGNORE,           0 },
  { "IN",                0, TK_IN,               0 },
  { "INDEX",             0, TK_INDEX,            0 },
  { "INSERT",            0, TK_INSERT,           0 },

  { "INTERSECT",         0, TK_INTERSECT,        0 },
  { "INTO",              0, TK_INTO,             0 },
  { "IS",                0, TK_IS,               0 },
  { "ISNULL",            0, TK_ISNULL,           0 },
  { "KEY",               0, TK_KEY,              0 },
  { "LIKE",              0, TK_LIKE,             0 },
  { "LIMIT",             0, TK_LIMIT,            0 },
  { "NOT",               0, TK_NOT,              0 },
  { "NOTNULL",           0, TK_NOTNULL,          0 },
  { "NULL",              0, TK_NULL,             0 },

  { "OFFSET",            0, TK_OFFSET,           0 },
  { "ON",                0, TK_ON,               0 },
  { "OR",                0, TK_OR,               0 },
  { "ORDER",             0, TK_ORDER,            0 },
  { "PRAGMA",            0, TK_PRAGMA,           0 },
  { "PRIMARY",           0, TK_PRIMARY,          0 },
  { "REPLACE",           0, TK_REPLACE,          0 },
  { "ROLLBACK",          0, TK_ROLLBACK,         0 },

  { "SELECT",            0, TK_SELECT,           0 },
  { "SET",               0, TK_SET,              0 },
  { "TABLE",             0, TK_TABLE,            0 },
  { "TEMP",              0, TK_TEMP,             0 },
  { "TEMPORARY",         0, TK_TEMP,             0 },
  { "THEN",              0, TK_THEN,             0 },
  { "TRANSACTION",       0, TK_TRANSACTION,      0 },

  { "UNION",             0, TK_UNION,            0 },
  { "UNIQUE",            0, TK_UNIQUE,           0 },
  { "UPDATE",            0, TK_UPDATE,           0 },
  { "USING",             0, TK_USING,            0 },
  { "VACUUM",            0, TK_VACUUM,           0 },
  { "VALUES",            0, TK_VALUES,           0 },
  { "VIEW",              0, TK_VIEW,             0 },







>




>


















>




>








>










>








>







>







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
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
};

/*
** These are the keywords
*/
static Keyword aKeywordTable[] = {
  { "ABORT",             0, TK_ABORT,            0 },
  { "AFTER",             0, TK_AFTER,            0 },
  { "ALL",               0, TK_ALL,              0 },
  { "AND",               0, TK_AND,              0 },
  { "AS",                0, TK_AS,               0 },
  { "ASC",               0, TK_ASC,              0 },
  { "BEFORE",            0, TK_BEFORE,           0 },
  { "BEGIN",             0, TK_BEGIN,            0 },
  { "BETWEEN",           0, TK_BETWEEN,          0 },
  { "BY",                0, TK_BY,               0 },
  { "CASE",              0, TK_CASE,             0 },
  { "CHECK",             0, TK_CHECK,            0 },
  { "CLUSTER",           0, TK_CLUSTER,          0 },
  { "COMMIT",            0, TK_COMMIT,           0 },
  { "CONFLICT",          0, TK_CONFLICT,         0 },
  { "CONSTRAINT",        0, TK_CONSTRAINT,       0 },
  { "COPY",              0, TK_COPY,             0 },
  { "CREATE",            0, TK_CREATE,           0 },
  { "DEFAULT",           0, TK_DEFAULT,          0 },
  { "DELETE",            0, TK_DELETE,           0 },
  { "DELIMITERS",        0, TK_DELIMITERS,       0 },
  { "DESC",              0, TK_DESC,             0 },
  { "DISTINCT",          0, TK_DISTINCT,         0 },
  { "DROP",              0, TK_DROP,             0 },
  { "END",               0, TK_END,              0 },
  { "EACH",              0, TK_EACH,             0 },
  { "ELSE",              0, TK_ELSE,             0 },
  { "EXCEPT",            0, TK_EXCEPT,           0 },
  { "EXPLAIN",           0, TK_EXPLAIN,          0 },
  { "FAIL",              0, TK_FAIL,             0 },
  { "FOR",               0, TK_FOR,              0 },
  { "FROM",              0, TK_FROM,             0 },
  { "GLOB",              0, TK_GLOB,             0 },
  { "GROUP",             0, TK_GROUP,            0 },
  { "HAVING",            0, TK_HAVING,           0 },
  { "IGNORE",            0, TK_IGNORE,           0 },
  { "IN",                0, TK_IN,               0 },
  { "INDEX",             0, TK_INDEX,            0 },
  { "INSERT",            0, TK_INSERT,           0 },
  { "INSTEAD",           0, TK_INSTEAD,          0 },
  { "INTERSECT",         0, TK_INTERSECT,        0 },
  { "INTO",              0, TK_INTO,             0 },
  { "IS",                0, TK_IS,               0 },
  { "ISNULL",            0, TK_ISNULL,           0 },
  { "KEY",               0, TK_KEY,              0 },
  { "LIKE",              0, TK_LIKE,             0 },
  { "LIMIT",             0, TK_LIMIT,            0 },
  { "NOT",               0, TK_NOT,              0 },
  { "NOTNULL",           0, TK_NOTNULL,          0 },
  { "NULL",              0, TK_NULL,             0 },
  { "OF",                0, TK_OF,               0 },
  { "OFFSET",            0, TK_OFFSET,           0 },
  { "ON",                0, TK_ON,               0 },
  { "OR",                0, TK_OR,               0 },
  { "ORDER",             0, TK_ORDER,            0 },
  { "PRAGMA",            0, TK_PRAGMA,           0 },
  { "PRIMARY",           0, TK_PRIMARY,          0 },
  { "REPLACE",           0, TK_REPLACE,          0 },
  { "ROLLBACK",          0, TK_ROLLBACK,         0 },
  { "ROW",               0, TK_ROW,              0 },
  { "SELECT",            0, TK_SELECT,           0 },
  { "SET",               0, TK_SET,              0 },
  { "TABLE",             0, TK_TABLE,            0 },
  { "TEMP",              0, TK_TEMP,             0 },
  { "TEMPORARY",         0, TK_TEMP,             0 },
  { "THEN",              0, TK_THEN,             0 },
  { "TRANSACTION",       0, TK_TRANSACTION,      0 },
  { "TRIGGER",           0, TK_TRIGGER,          0 },
  { "UNION",             0, TK_UNION,            0 },
  { "UNIQUE",            0, TK_UNIQUE,           0 },
  { "UPDATE",            0, TK_UPDATE,           0 },
  { "USING",             0, TK_USING,            0 },
  { "VACUUM",            0, TK_VACUUM,           0 },
  { "VALUES",            0, TK_VALUES,           0 },
  { "VIEW",              0, TK_VIEW,             0 },
Added src/trigger.c.






































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
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
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
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
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
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
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
363
364
365
366
367
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
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
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
631
632
633
634
635
636
637
638
639
640
641
642
643
/*
 * All copyright on this work is disclaimed by the author.
 *
 */

#include "sqliteInt.h"
/*
 * This is called by the parser when it sees a CREATE TRIGGER statement
 */
void 
sqliteCreateTrigger(
    Parse * pParse, /* The parse context of the CREATE TRIGGER statement */
    Token * nm,     /* The name of the trigger */
    int tr_tm,      /* One of TK_BEFORE, TK_AFTER */
    int op,         /* One of TK_INSERT, TK_UPDATE, TK_DELETE */
    IdList * cols,  /* column list if this is an UPDATE OF trigger */
    Token * tbl,    /* The name of the table/view the trigger applies to */
    int foreach,    /* One of TK_ROW or TK_STATEMENT */
    Expr * pWhen,   /* WHEN clause */
    TriggerStep * steps,      /* The triggered program */
    char const * cc, int len) /* The string data to make persistent */
{
  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.
   */
  {
    char * tmp_str = sqliteStrNDup(nm->z, nm->n);
    if (sqliteHashFind(&(pParse->db->trigHash), tmp_str, nm->n + 1)) {
      sqliteSetNString(&pParse->zErrMsg, "trigger ", -1,
	  nm->z, nm->n, " already exists", -1, 0);
      sqliteFree(tmp_str);
      pParse->nErr++;
      goto trigger_cleanup;
    }
    sqliteFree(tmp_str);
  }
  {
    char * tmp_str = sqliteStrNDup(tbl->z, tbl->n);
    tab = sqliteFindTable(pParse->db, tmp_str);
    sqliteFree(tmp_str);
    if (!tab) {
      sqliteSetNString(&pParse->zErrMsg, "no such table: ", -1,
	  tbl->z, tbl->n, 0);
      pParse->nErr++;
      goto trigger_cleanup;
    }
  }

  /* Build the Trigger object */
  nt = (Trigger *)sqliteMalloc(sizeof(Trigger));

  nt->name = sqliteStrNDup(nm->z, nm->n);
  nt->table = sqliteStrNDup(tbl->z, tbl->n);
  nt->op = op;
  nt->tr_tm = tr_tm;
  nt->pWhen = pWhen;
  nt->pColumns = cols;
  nt->foreach = foreach;
  nt->step_list = steps;
  nt->isCommit = 0;

  nt->strings = sqliteStrNDup(cc, len);
  offset = (int)(nt->strings - cc);

  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 && !tab->isTemp) {

    /* Make an entry in the sqlite_master table */
    sqliteBeginWriteOperation(pParse);

    sqliteVdbeAddOp(pParse->pVdbe,        OP_OpenWrite, 0, 2);
    sqliteVdbeChangeP3(pParse->pVdbe, -1, MASTER_NAME,           P3_STATIC);
    sqliteVdbeAddOp(pParse->pVdbe,        OP_NewRecno,  0, 0);
    sqliteVdbeAddOp(pParse->pVdbe,        OP_String,    0, 0);
    sqliteVdbeChangeP3(pParse->pVdbe, -1, "trigger",             P3_STATIC);
    sqliteVdbeAddOp(pParse->pVdbe,        OP_String,    0, 0);
    sqliteVdbeChangeP3(pParse->pVdbe, -1, nt->name,        0); 
    sqliteVdbeAddOp(pParse->pVdbe,        OP_String,    0, 0);
    sqliteVdbeChangeP3(pParse->pVdbe, -1, nt->table,        0); 
    sqliteVdbeAddOp(pParse->pVdbe,        OP_Integer,    0, 0);
    sqliteVdbeAddOp(pParse->pVdbe,        OP_String,    0, 0);
    sqliteVdbeChangeP3(pParse->pVdbe, -1, nt->strings,     0);
    sqliteVdbeAddOp(pParse->pVdbe,        OP_MakeRecord, 5, 0);
    sqliteVdbeAddOp(pParse->pVdbe,        OP_PutIntKey, 0, 1);

    /* Change the cookie, since the schema is changed */
    changeCookie(pParse->db);
    sqliteVdbeAddOp(pParse->pVdbe, OP_Integer, pParse->db->next_cookie, 0);
    sqliteVdbeAddOp(pParse->pVdbe, OP_SetCookie, 0, 0);

    sqliteVdbeAddOp(pParse->pVdbe,        OP_Close,     0, 0);

    sqliteEndWriteOperation(pParse);
  }

  if (!pParse->explain) {
    /* Stick it in the hash-table */
    sqliteHashInsert(&(pParse->db->trigHash), nt->name, nm->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(cols);
  sqliteExprDelete(pWhen);
  {
    TriggerStep * pp;
    TriggerStep * nn;

    pp = steps;
    while (pp) {
      nn = pp->pNext;
      sqliteExprDelete(pp->pWhere);
      sqliteExprListDelete(pp->pExprList);
      sqliteSelectDelete(pp->pSelect);
      sqliteIdListDelete(pp->pIdList);
      sqliteFree(pp);
      pp = nn;
    }
  }
}

  TriggerStep * 
sqliteTriggerSelectStep(Select * s)
{
  TriggerStep * tt = sqliteMalloc(sizeof(TriggerStep));

  tt->op = TK_SELECT;
  tt->pSelect = s;
  tt->orconf = OE_Default;

  return tt;
}

TriggerStep * 
sqliteTriggerInsertStep(Token * tbl, IdList * col, ExprList * val, Select * s, int orconf)
{
  TriggerStep * tt = sqliteMalloc(sizeof(TriggerStep));

  assert(val == 0 || s == 0);
  assert(val != 0 || s != 0);

  tt->op = TK_INSERT;
  tt->pSelect = s;
  tt->target  = *tbl;
  tt->pIdList = col;
  tt->pExprList = val;
  tt->orconf = orconf;

  return tt;
}

TriggerStep * 
sqliteTriggerUpdateStep(Token * tbl, ExprList * val, Expr * w, int orconf)
{
  TriggerStep * tt = sqliteMalloc(sizeof(TriggerStep));

  tt->op = TK_UPDATE;
  tt->target  = *tbl;
  tt->pExprList = val;
  tt->pWhere = w;
  tt->orconf = orconf;

  return tt;
}

TriggerStep * 
sqliteTriggerDeleteStep(Token * tbl, Expr * w)
{
  TriggerStep * tt = sqliteMalloc(sizeof(TriggerStep));

  tt->op = TK_DELETE;
  tt->target  = *tbl;
  tt->pWhere = w;
  tt->orconf = OE_Default;

  return tt;
}


/* This does a recursive delete of the trigger structure */
void sqliteDeleteTrigger(Trigger * tt)
{
  TriggerStep * ts, * tc;
  ts = tt->step_list;

  while (ts) {
    tc = ts;
    ts = ts->pNext;

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

    sqliteFree(tc);
  }

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

/*
 * "nested" is true if this is begin called as the result of a DROP TABLE
 */
void sqliteDropTrigger(Parse *pParse, Token * trigname, int nested)
{
  char * tmp_name;
  Trigger * trig;
  Table   * tbl;

  tmp_name = sqliteStrNDup(trigname->z, trigname->n);

  /* ensure that the trigger being dropped exists */
  trig = sqliteHashFind(&(pParse->db->trigHash), tmp_name, trigname->n + 1); 
  if (!trig) {
    sqliteSetNString(&pParse->zErrMsg, "no such trigger: ", -1,
	tmp_name, -1, 0);
    sqliteFree(tmp_name);
    return;
  }

  /*
   * If this is not an "explain", do the following:
   * 1. Remove the trigger from its associated table structure
   * 2. Move the trigger from the trigHash hash to trigDrop
   */
  if (!pParse->explain) {
    /* 1 */
    tbl = sqliteFindTable(pParse->db, trig->table);
    assert(tbl);
    if (tbl->pTrigger == trig) 
      tbl->pTrigger = trig->pNext;
    else {
      Trigger * cc = tbl->pTrigger;
      while (cc) {
	if (cc->pNext == trig) {
	  cc->pNext = cc->pNext->pNext;
	  break;
	}
	cc = cc->pNext;
      }
      assert(cc);
    }

    /* 2 */
    sqliteHashInsert(&(pParse->db->trigHash), tmp_name, 
	trigname->n + 1, NULL);
    sqliteHashInsert(&(pParse->db->trigDrop), trig->name, 
	trigname->n + 1, trig);
  }

  /* Unless this is a trigger on a TEMP TABLE, generate code to destroy the
   * database record of the trigger */
  if (!tbl->isTemp) {
    int base;
    static VdbeOp dropTrigger[] = {
      { OP_OpenWrite,  0, 2,        MASTER_NAME},
      { OP_Rewind,     0, ADDR(9),  0},
      { OP_String,     0, 0,        0}, /* 2 */
      { OP_MemStore,   1, 1,        0},
      { OP_MemLoad,    1, 0,        0}, /* 4 */
      { OP_Column,     0, 1,        0},
      { OP_Ne,         0, ADDR(8),  0},
      { OP_Delete,     0, 0,        0},
      { OP_Next,       0, ADDR(4),  0}, /* 8 */
      { OP_Integer,    0, 0,        0}, /* 9 */
      { OP_SetCookie,  0, 0,        0},
      { OP_Close,      0, 0,        0},
    };

    if (!nested) 
      sqliteBeginWriteOperation(pParse);

    base = sqliteVdbeAddOpList(pParse->pVdbe, 
	ArraySize(dropTrigger), dropTrigger);
    sqliteVdbeChangeP3(pParse->pVdbe, base+2, tmp_name, 0);

    if (!nested)
      changeCookie(pParse->db);

    sqliteVdbeChangeP1(pParse->pVdbe, base+9, pParse->db->next_cookie);

    if (!nested)
      sqliteEndWriteOperation(pParse);
  }

  sqliteFree(tmp_name);
}

static int checkColumnOverLap(IdList * ii, ExprList * ee)
{
  int i, e;
  if (!ii) return 1;
  if (!ee) return 1;

  for (i = 0; i < ii->nId; i++) 
    for (e = 0; e < ee->nExpr; e++) 
      if (!sqliteStrICmp(ii->a[i].zName, ee->a[e].zName))
	return 1;

  return 0; 
}

/* A global variable that is TRUE if we should always set up temp tables for
 * for triggers, even if there are no triggers to code. This is used to test 
 * how much overhead the triggers algorithm is causing.
 *
 * This flag can be set or cleared using the "trigger_overhead_test" pragma.
 * The pragma is not documented since it is not really part of the interface
 * to SQLite, just the test procedure.
*/
int always_code_trigger_setup = 0;

/*
 * Returns true if a trigger matching op, tr_tm and foreach that is NOT already
 * on the Parse objects trigger-stack (to prevent recursive trigger firing) is
 * found in the list specified as pTrigger.
 */
int sqliteTriggersExist(
    Parse * pParse, 
    Trigger * pTrigger,
    int op,                 /* one of TK_DELETE, TK_INSERT, TK_UPDATE */
    int tr_tm,              /* one of TK_BEFORE, TK_AFTER */
    int foreach,            /* one of TK_ROW or TK_STATEMENT */
    ExprList * pChanges)
{
  Trigger * tt;

  if (always_code_trigger_setup) return 1;

  tt = pTrigger;
  while (tt) {
    if (tt->op == op && tt->tr_tm == tr_tm && tt->foreach == foreach &&
	checkColumnOverLap(tt->pColumns, pChanges)) {
      TriggerStack * ss;
      ss = pParse->trigStack;
      while (ss && ss->pTrigger != pTrigger) ss = ss->pNext;
      if (!ss) return 1;
    }
    tt = tt->pNext;
  }

  return 0;
}

static int codeTriggerProgram(
	Parse *pParse,
	TriggerStep * program,
	int onError)
{
    TriggerStep * step = program;
    int orconf;

    while (step) {
	int saveNTab = pParse->nTab;
	orconf = (onError == OE_Default)?step->orconf:onError;
	pParse->trigStack->orconf = orconf;
	switch(step->op) {
	    case TK_SELECT: {
                int tmp_tbl = pParse->nTab++;
		sqliteVdbeAddOp(pParse->pVdbe, OP_OpenTemp, tmp_tbl, 0);
		sqliteVdbeAddOp(pParse->pVdbe, OP_KeyAsData, tmp_tbl, 1);
		sqliteSelect(pParse, step->pSelect, 
			SRT_Union, tmp_tbl, 0, 0, 0);
		sqliteVdbeAddOp(pParse->pVdbe, OP_Close, tmp_tbl, 0);
		pParse->nTab--;
		break;
			    }
	    case TK_UPDATE: {
                sqliteVdbeAddOp(pParse->pVdbe, OP_PushList, 0, 0);
		sqliteUpdate(pParse, &step->target, 
			sqliteExprListDup(step->pExprList), 
			sqliteExprDup(step->pWhere), orconf);
                sqliteVdbeAddOp(pParse->pVdbe, OP_PopList, 0, 0);
		break;
			    }
	    case TK_INSERT: {
                sqliteInsert(pParse, &step->target, 
			sqliteExprListDup(step->pExprList), 
			sqliteSelectDup(step->pSelect), 
			sqliteIdListDup(step->pIdList), orconf);
		break;
			    }
	    case TK_DELETE: {
		sqliteVdbeAddOp(pParse->pVdbe, OP_PushList, 0, 0);
                sqliteDeleteFrom(pParse, &step->target, 
			sqliteExprDup(step->pWhere)
			);
		sqliteVdbeAddOp(pParse->pVdbe, OP_PopList, 0, 0);
		break;
			    }
	    default:
			    assert(0);
	} 
	pParse->nTab = saveNTab;
	step = step->pNext;
    }

    return 0;
}

int sqliteCodeRowTrigger(
	Parse * pParse,  /* Parse context */
	int op,          /* One of TK_UPDATE, TK_INSERT, TK_DELETE */
	ExprList * changes, /* Changes list for any UPDATE OF triggers */
	int tr_tm,       /* One of TK_BEFORE, TK_AFTER */
	Table * tbl,     /* The table to code triggers from */
	int newTable,    /* The indice of the "new" row to access */
	int oldTable,    /* The indice of the "old" row to access */
	int onError)     /* ON CONFLICT policy */
{
  Trigger * pTrigger;
  TriggerStack * pTriggerStack;


  assert(op == TK_UPDATE || op == TK_INSERT || op == TK_DELETE);
  assert(tr_tm == TK_BEFORE || tr_tm == TK_AFTER);

  assert(newTable != -1 || oldTable != -1);

  pTrigger = tbl->pTrigger;
  while (pTrigger) {
    int fire_this = 0;

    /* determine whether we should code this trigger */
    if (pTrigger->op == op && pTrigger->tr_tm == tr_tm && 
	pTrigger->foreach == TK_ROW) {
      fire_this = 1;
      pTriggerStack = pParse->trigStack;
      while (pTriggerStack) {
	if (pTriggerStack->pTrigger == pTrigger) fire_this = 0;
	pTriggerStack = pTriggerStack->pNext;
      }
      if (op == TK_UPDATE && pTrigger->pColumns &&
	  !checkColumnOverLap(pTrigger->pColumns, changes))
	fire_this = 0;
    }

    if (fire_this) {
      int endTrigger;
      IdList dummyTablist;
      Expr * whenExpr;

      dummyTablist.nId = 0;
      dummyTablist.a = 0;

      /* Push an entry on to the trigger stack */
      pTriggerStack = sqliteMalloc(sizeof(TriggerStack));
      pTriggerStack->pTrigger = pTrigger;
      pTriggerStack->newIdx = newTable;
      pTriggerStack->oldIdx = oldTable;
      pTriggerStack->pTab = tbl;
      pTriggerStack->pNext = pParse->trigStack;
      pParse->trigStack = pTriggerStack;

      /* code the WHEN clause */
      endTrigger = sqliteVdbeMakeLabel(pParse->pVdbe);
      whenExpr = sqliteExprDup(pTrigger->pWhen);
      if (sqliteExprResolveIds(pParse, 0, &dummyTablist, 0, whenExpr)) {
	pParse->trigStack = pParse->trigStack->pNext;
	sqliteFree(pTriggerStack);
	sqliteExprDelete(whenExpr);
	return 1;
      }
      sqliteExprIfFalse(pParse, whenExpr, endTrigger);
      sqliteExprDelete(whenExpr);

      codeTriggerProgram(pParse, pTrigger->step_list, onError); 

      /* Pop the entry off the trigger stack */
      pParse->trigStack = pParse->trigStack->pNext;
      sqliteFree(pTriggerStack);

      sqliteVdbeResolveLabel(pParse->pVdbe, endTrigger);
    }
    pTrigger = pTrigger->pNext;
  }

  return 0;
}

/*
 * Handle UPDATE and DELETE triggers on views
 */
void sqliteViewTriggers(Parse *pParse, Table *pTab, 
    Expr * pWhere, int onError, ExprList * pChanges)
{
  int oldIdx = -1;
  int newIdx = -1;
  int *aXRef = 0;   
  Vdbe *v;
  int endOfLoop;
  int startOfLoop;
  Select theSelect;
  Token tblNameToken;

  assert(pTab->pSelect);

  tblNameToken.z = pTab->zName;
  tblNameToken.n = strlen(pTab->zName);

  theSelect.isDistinct = 0;
  theSelect.pEList = sqliteExprListAppend(0, sqliteExpr(TK_ALL, 0, 0, 0), 0);
  theSelect.pSrc   = sqliteIdListAppend(0, &tblNameToken);
  theSelect.pWhere = pWhere;    pWhere = 0;
  theSelect.pGroupBy = 0;
  theSelect.pHaving = 0;
  theSelect.pOrderBy = 0;
  theSelect.op = TK_SELECT; /* ?? */
  theSelect.pPrior = 0;
  theSelect.nLimit = -1;
  theSelect.nOffset = -1;
  theSelect.zSelect = 0;
  theSelect.base = 0;

  v = sqliteGetVdbe(pParse);
  assert(v);
  sqliteBeginMultiWriteOperation(pParse);

  /* Allocate temp tables */
  oldIdx = pParse->nTab++;
  sqliteVdbeAddOp(v, OP_OpenTemp, oldIdx, 0);
  if (pChanges) {
    newIdx = pParse->nTab++;
    sqliteVdbeAddOp(v, OP_OpenTemp, newIdx, 0);
  }

  /* Snapshot the view */
  if (sqliteSelect(pParse, &theSelect, SRT_Table, oldIdx, 0, 0, 0)) {
    goto trigger_cleanup;
  }

  /* loop thru the view snapshot, executing triggers for each row */
  endOfLoop = sqliteVdbeMakeLabel(v);
  sqliteVdbeAddOp(v, OP_Rewind, oldIdx, endOfLoop);

  /* Loop thru the view snapshot, executing triggers for each row */
  startOfLoop = sqliteVdbeCurrentAddr(v);

  /* Build the updated row if required */
  if (pChanges) {
    int ii, jj;

    aXRef = sqliteMalloc( sizeof(int) * pTab->nCol );
    if( aXRef==0 ) goto trigger_cleanup;
    for (ii = 0; ii < pTab->nCol; ii++)
      aXRef[ii] = -1;

    for(ii=0; ii<pChanges->nExpr; ii++){
      int jj;
      if( sqliteExprResolveIds(pParse, oldIdx, theSelect.pSrc , 0, 
	    pChanges->a[ii].pExpr) )
	goto trigger_cleanup;

      if( sqliteExprCheck(pParse, pChanges->a[ii].pExpr, 0, 0) )
	goto trigger_cleanup;

      for(jj=0; jj<pTab->nCol; jj++){
	if( sqliteStrICmp(pTab->aCol[jj].zName, pChanges->a[ii].zName)==0 ){
	  aXRef[jj] = ii;
	  break;
	}
      }
      if( jj>=pTab->nCol ){
	sqliteSetString(&pParse->zErrMsg, "no such column: ", 
	    pChanges->a[ii].zName, 0);
	pParse->nErr++;
	goto trigger_cleanup;
      }
    }

    sqliteVdbeAddOp(v, OP_Integer, 13, 0);

    for (ii = 0; ii < pTab->nCol; ii++)
      if( aXRef[ii] < 0 ) 
	sqliteVdbeAddOp(v, OP_Column, oldIdx, ii);
      else
	sqliteExprCode(pParse, pChanges->a[aXRef[ii]].pExpr);

    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
    sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
    sqliteVdbeAddOp(v, OP_Rewind, newIdx, 0);

    sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_BEFORE, 
	pTab, newIdx, oldIdx, onError);
    sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_AFTER, 
	pTab, newIdx, oldIdx, onError);
  } else {
    sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1, oldIdx, 
	onError);
    sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1, oldIdx, 
	onError);
  }

  sqliteVdbeAddOp(v, OP_Next, oldIdx, startOfLoop);

  sqliteVdbeResolveLabel(v, endOfLoop);
  sqliteEndWriteOperation(pParse);

trigger_cleanup:
  sqliteFree(aXRef);
  sqliteExprListDelete(pChanges);
  sqliteExprDelete(pWhere);
  sqliteExprListDelete(theSelect.pEList);
  sqliteIdListDelete(theSelect.pSrc);
  sqliteExprDelete(theSelect.pWhere);
  return;
}


Changes to src/update.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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 UPDATE statements.
**
** $Id: update.c,v 1.36 2002/03/03 18:59:41 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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 UPDATE statements.
**
** $Id: update.c,v 1.37 2002/05/15 08:30:14 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(
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
                         ** an expression for the i-th column of the table.
                         ** aXRef[i]==-1 if the i-th column is not changed. */
  int openOp;            /* Opcode used to open tables */
  int chngRecno;         /* True if the record number is being changed */
  Expr *pRecnoExpr;      /* Expression defining the new record number */
  int openAll;           /* True if all indices need to be opened */






  if( pParse->nErr || sqlite_malloc_failed ) goto update_cleanup;
  db = pParse->db;

























  /* Locate the table which we want to update.  This table has to be
  ** put in an IdList structure because some of the subroutines we
  ** will be calling are designed to work with multiple tables and expect
  ** an IdList* parameter instead of just a Table* parameter.
  */
  pTabList = sqliteTableTokenToIdList(pParse, pTableName);
  if( pTabList==0 ) goto update_cleanup;
  pTab = pTabList->a[0].pTab;
  assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  aXRef = sqliteMalloc( sizeof(int) * pTab->nCol );
  if( aXRef==0 ) goto update_cleanup;
  for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;






  /* Resolve the column names in all the expressions in both the
  ** WHERE clause and in the new values.  Also find the column index
  ** for each column to be updated in the pChanges array.
  */
  base = pParse->nTab++;
  if( pWhere ){







>
>
>
>
>


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













>
>
>
>
>







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
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
                         ** an expression for the i-th column of the table.
                         ** aXRef[i]==-1 if the i-th column is not changed. */
  int openOp;            /* Opcode used to open tables */
  int chngRecno;         /* True if the record number is being changed */
  Expr *pRecnoExpr;      /* Expression defining the new record number */
  int openAll;           /* True if all indices need to be opened */

  int row_triggers_exist = 0;

  int newIdx      = -1;  /* index of trigger "new" temp table       */
  int oldIdx      = -1;  /* index of trigger "old" temp table       */

  if( pParse->nErr || sqlite_malloc_failed ) goto update_cleanup;
  db = pParse->db;

  /* Check for the special case of a VIEW with one or more ON UPDATE triggers 
   * defined 
   */
  {
    char * zTab = sqliteTableNameFromToken(pTableName);

    if(zTab != 0) {
      pTab = sqliteFindTable(pParse->db, zTab);
      if (pTab) {
	row_triggers_exist = 
	  sqliteTriggersExist(pParse, pTab->pTrigger, 
	      TK_UPDATE, TK_BEFORE, TK_ROW, pChanges) ||
	  sqliteTriggersExist(pParse, pTab->pTrigger, 
	      TK_UPDATE, TK_AFTER, TK_ROW, pChanges);
      }
      sqliteFree(zTab);
      if (row_triggers_exist &&  pTab->pSelect ) {
	/* Just fire VIEW triggers */
	sqliteViewTriggers(pParse, pTab, pWhere, onError, pChanges);
	return;
      }
    }
  }

  /* Locate the table which we want to update.  This table has to be
  ** put in an IdList structure because some of the subroutines we
  ** will be calling are designed to work with multiple tables and expect
  ** an IdList* parameter instead of just a Table* parameter.
  */
  pTabList = sqliteTableTokenToIdList(pParse, pTableName);
  if( pTabList==0 ) goto update_cleanup;
  pTab = pTabList->a[0].pTab;
  assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  aXRef = sqliteMalloc( sizeof(int) * pTab->nCol );
  if( aXRef==0 ) goto update_cleanup;
  for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;

  if (row_triggers_exist) {
    newIdx = pParse->nTab++;
    oldIdx = pParse->nTab++;
  }

  /* Resolve the column names in all the expressions in both the
  ** WHERE clause and in the new values.  Also find the column index
  ** for each column to be updated in the pChanges array.
  */
  base = pParse->nTab++;
  if( pWhere ){
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

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

  /* Initialize the count of updated rows
  */
  if( db->flags & SQLITE_CountRows ){
    sqliteVdbeAddOp(v, OP_Integer, 0, 0);
  }















































  /* Rewind the list of records that need to be updated and
  ** open every index that needs updating.  Note that if any
  ** index could potentially invoke a REPLACE conflict resolution 
  ** action, then we need to open all indices because we might need
  ** to be deleting some records.
  */
  sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
  openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
  sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
  if( onError==OE_Replace ){
    openAll = 1;
  }else{
    openAll = 0;
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){







|


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







<







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
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251

252
253
254
255
256
257
258

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

  /* Initialize the count of updated rows
  */
  if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
    sqliteVdbeAddOp(v, OP_Integer, 0, 0);
  }

  if (row_triggers_exist) {
    int ii;

    sqliteVdbeAddOp(v, OP_OpenTemp, oldIdx, 0);
    sqliteVdbeAddOp(v, OP_OpenTemp, newIdx, 0);

    sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
    addr = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
    sqliteVdbeAddOp(v, OP_Dup, 0, 0);

    sqliteVdbeAddOp(v, OP_Dup, 0, 0);
    sqliteVdbeAddOp(v, OP_Open, base, pTab->tnum);
    sqliteVdbeAddOp(v, OP_MoveTo, base, 0);

    sqliteVdbeAddOp(v, OP_Integer, 13, 0);
    for (ii = 0; ii < pTab->nCol; ii++) {
	if (ii == pTab->iPKey) 
	    sqliteVdbeAddOp(v, OP_Recno, base, 0);
	else
	    sqliteVdbeAddOp(v, OP_Column, base, ii);
    }
    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
    sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);

    sqliteVdbeAddOp(v, OP_Integer, 13, 0);
    for (ii = 0; ii < pTab->nCol; ii++){
      if( aXRef[ii] < 0 ){
        if (ii == pTab->iPKey)
          sqliteVdbeAddOp(v, OP_Recno, base, 0);
        else
          sqliteVdbeAddOp(v, OP_Column, base, ii);
      }else{
        sqliteExprCode(pParse, pChanges->a[aXRef[ii]].pExpr);
      }
    }
    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
    sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
    sqliteVdbeAddOp(v, OP_Close, base, 0);

    sqliteVdbeAddOp(v, OP_Rewind, oldIdx, 0);
    sqliteVdbeAddOp(v, OP_Rewind, newIdx, 0);

    if (sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_BEFORE, pTab, 
	  newIdx, oldIdx, onError)) goto update_cleanup;
  }

  /* Rewind the list of records that need to be updated and
  ** open every index that needs updating.  Note that if any
  ** index could potentially invoke a REPLACE conflict resolution 
  ** action, then we need to open all indices because we might need
  ** to be deleting some records.
  */

  openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
  sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
  if( onError==OE_Replace ){
    openAll = 1;
  }else{
    openAll = 0;
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
193
194
195
196
197
198
199



200
201

202
203
204
205
206
207
208

  /* Loop over every record that needs updating.  We have to load
  ** the old data for each record to be updated because some columns
  ** might not change and we will need to copy the old value.
  ** Also, the old data is needed to delete the old index entires.
  ** So make the cursor point at the old record.
  */



  addr = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
  sqliteVdbeAddOp(v, OP_Dup, 0, 0);

  sqliteVdbeAddOp(v, OP_MoveTo, base, 0);

  /* If the record number will change, push the record number as it
  ** will be after the update. (The old record number is currently
  ** on top of the stack.)
  */
  if( chngRecno ){







>
>
>
|
|
>







272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291

  /* Loop over every record that needs updating.  We have to load
  ** the old data for each record to be updated because some columns
  ** might not change and we will need to copy the old value.
  ** Also, the old data is needed to delete the old index entires.
  ** So make the cursor point at the old record.
  */
  if (!row_triggers_exist) {
    int ii;
    sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
    addr = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
    sqliteVdbeAddOp(v, OP_Dup, 0, 0);
  }
  sqliteVdbeAddOp(v, OP_MoveTo, base, 0);

  /* If the record number will change, push the record number as it
  ** will be after the update. (The old record number is currently
  ** on top of the stack.)
  */
  if( chngRecno ){
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255












256
257
258
259
260
261
262















263
264
265
266
267
268
269
270
271
272
273
274
275
  /* Delete the old indices for the current record.
  */
  sqliteGenerateRowIndexDelete(v, pTab, base, aIdxUsed);

  /* If changing the record number, delete the old record.
  */
  if( chngRecno ){
    sqliteVdbeAddOp(v, OP_Delete, 0, 0);
  }

  /* Create the new index entries and the new record.
  */
  sqliteCompleteInsertion(pParse, pTab, base, aIdxUsed, chngRecno, 1);

  /* Increment the row counter 
  */
  if( db->flags & SQLITE_CountRows ){
    sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
  }













  /* Repeat the above with the next record to be updated, until
  ** all record selected by the WHERE clause have been updated.
  */
  sqliteVdbeAddOp(v, OP_Goto, 0, addr);
  sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v));
  sqliteVdbeAddOp(v, OP_ListReset, 0, 0);















  sqliteEndWriteOperation(pParse);

  /*
  ** Return the number of rows that were changed.
  */
  if( db->flags & SQLITE_CountRows ){
    sqliteVdbeAddOp(v, OP_ColumnCount, 1, 0);
    sqliteVdbeAddOp(v, OP_ColumnName, 0, 0);
    sqliteVdbeChangeP3(v, -1, "rows updated", P3_STATIC);
    sqliteVdbeAddOp(v, OP_Callback, 1, 0);
  }

update_cleanup:







|








|


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







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





|







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
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
  /* Delete the old indices for the current record.
  */
  sqliteGenerateRowIndexDelete(v, pTab, base, aIdxUsed);

  /* If changing the record number, delete the old record.
  */
  if( chngRecno ){
    sqliteVdbeAddOp(v, OP_Delete, base, 0);
  }

  /* Create the new index entries and the new record.
  */
  sqliteCompleteInsertion(pParse, pTab, base, aIdxUsed, chngRecno, 1);

  /* Increment the row counter 
  */
  if( db->flags & SQLITE_CountRows && !pParse->trigStack){
    sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
  }

  if (row_triggers_exist) {
    for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      if( openAll || aIdxUsed[i] )
	sqliteVdbeAddOp(v, OP_Close, base+i+1, 0);
    }
    sqliteVdbeAddOp(v, OP_Close, base, 0);
    pParse->nTab = base;

    if (sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_AFTER, pTab, 
	  newIdx, oldIdx, onError)) goto update_cleanup;
  }

  /* Repeat the above with the next record to be updated, until
  ** all record selected by the WHERE clause have been updated.
  */
  sqliteVdbeAddOp(v, OP_Goto, 0, addr);
  sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v));
  sqliteVdbeAddOp(v, OP_ListReset, 0, 0);

  /* Close all tables if there were no FOR EACH ROW triggers */
  if (!row_triggers_exist) {
    for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      if( openAll || aIdxUsed[i] ){
	sqliteVdbeAddOp(v, OP_Close, base+i+1, 0);
      }
    }
    sqliteVdbeAddOp(v, OP_Close, base, 0);
    pParse->nTab = base;
  } else {
    sqliteVdbeAddOp(v, OP_Close, newIdx, 0);
    sqliteVdbeAddOp(v, OP_Close, oldIdx, 0);
  }

  sqliteEndWriteOperation(pParse);

  /*
  ** Return the number of rows that were changed.
  */
  if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
    sqliteVdbeAddOp(v, OP_ColumnCount, 1, 0);
    sqliteVdbeAddOp(v, OP_ColumnName, 0, 0);
    sqliteVdbeChangeP3(v, -1, "rows updated", P3_STATIC);
    sqliteVdbeAddOp(v, OP_Callback, 1, 0);
  }

update_cleanup:
Changes to src/vdbe.c.
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.141 2002/05/10 13:14:07 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test







|







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.142 2002/05/15 08:30:14 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
245
246
247
248
249
250
251



252
253
254
255
256
257
258
  Mem *aMem;          /* The memory locations */
  Agg agg;            /* Aggregate information */
  int nSet;           /* Number of sets allocated */
  Set *aSet;          /* An array of sets */
  int nCallback;      /* Number of callbacks invoked so far */
  int iLimit;         /* Limit on the number of callbacks remaining */
  int iOffset;        /* Offset before beginning to do callbacks */



};

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







>
>
>







245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
  Mem *aMem;          /* The memory locations */
  Agg agg;            /* Aggregate information */
  int nSet;           /* Number of sets allocated */
  Set *aSet;          /* An array of sets */
  int nCallback;      /* Number of callbacks invoked so far */
  int iLimit;         /* Limit on the number of callbacks remaining */
  int iOffset;        /* Offset before beginning to do callbacks */

  int keylistStackDepth; 
  Keylist ** keylistStack;
};

/*
** Create a new virtual database engine.
*/
Vdbe *sqliteVdbeCreate(sqlite *db){
  Vdbe *p;
995
996
997
998
999
1000
1001









1002
1003
1004
1005
1006
1007
1008
  AggReset(&p->agg);
  for(i=0; i<p->nSet; i++){
    sqliteHashClear(&p->aSet[i].hash);
  }
  sqliteFree(p->aSet);
  p->aSet = 0;
  p->nSet = 0;









}

/*
** Delete an entire VDBE.
*/
void sqliteVdbeDelete(Vdbe *p){
  int i;







>
>
>
>
>
>
>
>
>







998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
  AggReset(&p->agg);
  for(i=0; i<p->nSet; i++){
    sqliteHashClear(&p->aSet[i].hash);
  }
  sqliteFree(p->aSet);
  p->aSet = 0;
  p->nSet = 0;
  if (p->keylistStackDepth > 0) {
    int ii;
    for (ii = 0; ii < p->keylistStackDepth; ii++) {
      KeylistFree(p->keylistStack[ii]);
    }
    sqliteFree(p->keylistStack);
    p->keylistStackDepth = 0;
    p->keylistStack = 0;
  }
}

/*
** Delete an entire VDBE.
*/
void sqliteVdbeDelete(Vdbe *p){
  int i;
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
  "MustBeInt",         "Add",               "AddImm",            "Subtract",
  "Multiply",          "Divide",            "Remainder",         "BitAnd",
  "BitOr",             "BitNot",            "ShiftLeft",         "ShiftRight",
  "AbsValue",          "Eq",                "Ne",                "Lt",
  "Le",                "Gt",                "Ge",                "IsNull",
  "NotNull",           "Negative",          "And",               "Or",
  "Not",               "Concat",            "Noop",              "Function",
  "Limit",           
};

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







|







1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
  "MustBeInt",         "Add",               "AddImm",            "Subtract",
  "Multiply",          "Divide",            "Remainder",         "BitAnd",
  "BitOr",             "BitNot",            "ShiftLeft",         "ShiftRight",
  "AbsValue",          "Eq",                "Ne",                "Lt",
  "Le",                "Gt",                "Ge",                "IsNull",
  "NotNull",           "Negative",          "And",               "Or",
  "Not",               "Concat",            "Noop",              "Function",
  "Limit",             "PushList",          "PopList",           
};

/*
** Given the name of an opcode, return its number.  Return 0 if
** there is no match.
**
** This routine is used for testing and debugging.
4534
4535
4536
4537
4538
4539
4540

































4541
4542
4543
4544
4545
4546
4547
  if( VERIFY( i>=0 && i<p->nSet &&) 
       sqliteHashFind(&p->aSet[i].hash, zStack[tos], aStack[tos].n)){
    pc = pOp->p2 - 1;
  }
  POPSTACK;
  break;
}


































/* Opcode: SetNotFound P1 P2 *
**
** Pop the stack once and compare the value popped off with the
** contents of set P1.  If the element popped does not exists in 
** set P1, then jump to P2.  Otherwise fall through.
*/







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







4546
4547
4548
4549
4550
4551
4552
4553
4554
4555
4556
4557
4558
4559
4560
4561
4562
4563
4564
4565
4566
4567
4568
4569
4570
4571
4572
4573
4574
4575
4576
4577
4578
4579
4580
4581
4582
4583
4584
4585
4586
4587
4588
4589
4590
4591
4592
  if( VERIFY( i>=0 && i<p->nSet &&) 
       sqliteHashFind(&p->aSet[i].hash, zStack[tos], aStack[tos].n)){
    pc = pOp->p2 - 1;
  }
  POPSTACK;
  break;
}

/* Opcode: PushList * * * 
**
** Save the current Vdbe list such that it can be restored by a PopList 
** opcode. The list is empty after this is executed.
*/
case OP_PushList: {
  p->keylistStackDepth++;
  assert(p->keylistStackDepth > 0);
  p->keylistStack = sqliteRealloc(p->keylistStack, 
	  sizeof(Keylist *) * p->keylistStackDepth);
  p->keylistStack[p->keylistStackDepth - 1] = p->pList;
  p->pList = 0;
  break;
}

/* Opcode: PopList * * * 
**
** Restore the Vdbe list to the state it was in when PushList was last
** executed.
*/
case OP_PopList: {
  assert(p->keylistStackDepth > 0);
  p->keylistStackDepth--;
  KeylistFree(p->pList);
  p->pList = p->keylistStack[p->keylistStackDepth];
  p->keylistStack[p->keylistStackDepth] = 0;
  if (p->keylistStackDepth == 0) {
    sqliteFree(p->keylistStack);
    p->keylistStack = 0;
  }
  break;
}

/* Opcode: SetNotFound P1 P2 *
**
** Pop the stack once and compare the value popped off with the
** contents of set P1.  If the element popped does not exists in 
** set P1, then jump to P2.  Otherwise fall through.
*/
Changes to src/vdbe.h.
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.50 2002/04/20 14:24:43 drh Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

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







|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.51 2002/05/15 08:30:14 danielk1977 Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines
194
195
196
197
198
199
200



201
202
203
204
205
206
207
208
#define OP_Not               109
#define OP_Concat            110
#define OP_Noop              111
#define OP_Function          112

#define OP_Limit             113




#define OP_MAX               113

/*
** Prototypes for the VDBE interface.  See comments on the implementation
** for a description of what each of these routines does.
*/
Vdbe *sqliteVdbeCreate(sqlite*);
void sqliteVdbeCreateCallback(Vdbe*, int*);







>
>
>
|







194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
#define OP_Not               109
#define OP_Concat            110
#define OP_Noop              111
#define OP_Function          112

#define OP_Limit             113

#define OP_PushList          114
#define OP_PopList           115

#define OP_MAX               115

/*
** Prototypes for the VDBE interface.  See comments on the implementation
** for a description of what each of these routines does.
*/
Vdbe *sqliteVdbeCreate(sqlite*);
void sqliteVdbeCreateCallback(Vdbe*, int*);
Changes to src/where.c.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.41 2002/04/30 19:20:29 drh Exp $
*/
#include "sqliteInt.h"

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







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.42 2002/05/15 08:30:14 danielk1977 Exp $
*/
#include "sqliteInt.h"

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
















219
220
221
222
223
224
225
  memset(aExpr, 0, sizeof(aExpr));
  nExpr = exprSplit(ARRAYSIZE(aExpr), aExpr, pWhere);

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
















  }

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







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







212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
  memset(aExpr, 0, sizeof(aExpr));
  nExpr = exprSplit(ARRAYSIZE(aExpr), aExpr, pWhere);

  /* Analyze all of the subexpressions.
  */
  for(i=0; i<nExpr; i++){
    exprAnalyze(base, &aExpr[i]);
    if (pParse->trigStack && pParse->trigStack->newIdx >= 0) {
	aExpr[i].prereqRight = 
	    aExpr[i].prereqRight & ~(1 << pParse->trigStack->newIdx - base);
	aExpr[i].prereqLeft = 
	    aExpr[i].prereqLeft & ~(1 << pParse->trigStack->newIdx - base);
	aExpr[i].prereqAll = 
	    aExpr[i].prereqAll & ~(1 << pParse->trigStack->newIdx - base);
    }
    if (pParse->trigStack && pParse->trigStack->oldIdx >= 0) {
	aExpr[i].prereqRight = 
	    aExpr[i].prereqRight & ~(1 << pParse->trigStack->oldIdx - base);
	aExpr[i].prereqLeft = 
	    aExpr[i].prereqLeft & ~(1 << pParse->trigStack->oldIdx - base);
	aExpr[i].prereqAll = 
	    aExpr[i].prereqAll & ~(1 << pParse->trigStack->oldIdx - base);
    }
  }

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


































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
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
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
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
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file tests creating and dropping triggers, and interaction thereof
# with the database COMMIT/ROLLBACK logic.
#
# 1. CREATE and DROP TRIGGER tests
# trig-1.1: Error if table does not exist
# trig-1.2: Error if trigger already exists
# trig-1.3: Created triggers are deleted if the transaction is rolled back
# trig-1.4: DROP TRIGGER removes trigger
# trig-1.5: Dropped triggers are restored if the transaction is rolled back
# trig-1.6: Error if dropped trigger doesn't exist
# trig-1.7: Dropping the table automatically drops all triggers
# trig-1.8: A trigger created on a TEMP table is not inserted into sqlite_master
#

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

do_test trig_cd-1.1 {
   catchsql {
     CREATE TRIGGER trig UPDATE ON no_such_table BEGIN
       SELECT * from sqlite_master;
     END;
   } 
} {1 {no such table: no_such_table}}

execsql {
    CREATE TABLE t1(a);
}
execsql {
	CREATE TRIGGER tr1 INSERT ON t1 BEGIN
	  INSERT INTO t1 values(1);
 	END;
}
do_test trig_cd-1.2 {
    catchsql {
	CREATE TRIGGER tr1 DELETE ON t1 BEGIN
	    SELECT * FROM sqlite_master;
 	END
     }
} {1 {trigger tr1 already exists}}

do_test trig_cd-1.3 {
    catchsql {
	BEGIN;
	CREATE TRIGGER tr2 INSERT ON t1 BEGIN
	    SELECT * from sqlite_master; END;
        ROLLBACK;
	CREATE TRIGGER tr2 INSERT ON t1 BEGIN
	    SELECT * from sqlite_master; END;
    }
} {0 {}}

do_test trig_cd-1.4 {
    catchsql {
	DROP TRIGGER tr1;
	CREATE TRIGGER tr1 DELETE ON t1 BEGIN
	    SELECT * FROM sqlite_master;
	END
    }
} {0 {}}

do_test trig_cd-1.5 {
    execsql {
	BEGIN;
	DROP TRIGGER tr2;
	ROLLBACK;
	DROP TRIGGER tr2;
    }
} {}

do_test trig_cd-1.6 {
    catchsql {
	DROP TRIGGER biggles;
    }
} {1 {no such trigger: biggles}}

do_test trig_cd-1.7 {
    catchsql {
	DROP TABLE t1;
	DROP TRIGGER tr1;
    }
} {1 {no such trigger: tr1}}

execsql {
  CREATE TEMP TABLE temp_table(a);
}
do_test trig_cd-1.8 {
  execsql {
	CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN
	    SELECT * from sqlite_master;
	END;
	SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig';
  } 
} {0}

catchsql {
  DROP TABLE temp_table;
}
catchsql {
  DROP TABLE t1;
}

finish_test

Added test/trigger2.test.










































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
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
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
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
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
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
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
363
364
365
366
367
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
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
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
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Regression testing of FOR EACH ROW table triggers
#
# 1. Trigger execution order tests. 
# These tests ensure that BEFORE and AFTER triggers are fired at the correct
# times relative to each other and the triggering statement. 
#
# trig-1.1.*: ON UPDATE trigger execution model.
# trig-1.2.*: DELETE trigger execution model.
# trig-1.3.*: INSERT trigger execution model.
#
# 2. Trigger program execution tests.
# These tests ensure that trigger programs execute correctly (ie. that a
# trigger program can correctly execute INSERT, UPDATE, DELETE * SELECT
# statements, and combinations thereof).
#
# 3. Selective trigger execution 
# This tests that conditional triggers (ie. UPDATE OF triggers and triggers
# with WHEN clauses) are fired only fired when they are supposed to be.
#
# trig-3.1: UPDATE OF triggers
# trig-3.2: WHEN clause
#
# 4. Cascaded trigger execution 
# Tests that trigger-programs may cause other triggers to fire. Also that a 
# trigger-program is never executed recursively.
# 
# trig-4.1: Trivial cascading trigger
# trig-4.2: Trivial recursive trigger handling 
#
# 5. Count changes behaviour.
# Verify that rows altered by triggers are not included in the return value
# of the "count changes" interface.
#
# 6. ON CONFLICT clause handling
# trig-6.1[a-f]: INSERT statements
# trig-6.2[a-f]: UPDATE statements
#
# 7. Triggers on views fire correctly.
#

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

# 1.
set ii 0
foreach tbl_defn [ list \
	{CREATE TABLE tbl (a, b);} \
	{CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);} \
        {CREATE TABLE tbl (a, b PRIMARY KEY);} \
	{CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} ] {
  incr ii
  catchsql { DROP INDEX tbl_idx; }
  catchsql {
    DROP TABLE rlog;
    DROP TABLE clog;
    DROP TABLE tbl;
    DROP TABLE other_tbl;
  }

  execsql $tbl_defn

  execsql {
    INSERT INTO tbl VALUES(1, 2);
    INSERT INTO tbl VALUES(3, 4);

    CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
    CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);

    CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW 
      BEGIN
      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  new.a, new.b);
    END;

    CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW 
      BEGIN
      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  new.a, new.b);
    END;

    CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW
      WHEN old.a = 1
      BEGIN
      INSERT INTO clog VALUES ( (SELECT max(idx) + 1 FROM clog), 
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  new.a, new.b);
    END;
  }

  do_test trig-1.1.$ii {
    execsql {
      UPDATE tbl SET a = a * 10, b = b * 10;
      SELECT * FROM rlog ORDER BY idx;
      SELECT * FROM clog ORDER BY idx;
    }
  } [list 1 1 2  4  6 10 20 \
          2 1 2 13 24 10 20 \
	  3 3 4 13 24 30 40 \
	  4 3 4 40 60 30 40 \
          1 1 2 13 24 10 20 ]
  
  execsql {
    DELETE FROM rlog;
    DELETE FROM tbl;
    INSERT INTO tbl VALUES (100, 100);
    INSERT INTO tbl VALUES (300, 200);
    CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
      BEGIN
      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  0, 0);
    END;

    CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW
      BEGIN
      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  0, 0);
    END;
  }
  do_test trig-1.2.$ii {
    execsql {
      DELETE FROM tbl;
      SELECT * FROM rlog;
    }
  } [list 1 100 100 400 300 0 0 \
          2 100 100 300 200 0 0 \
          3 300 200 300 200 0 0 \
          4 300 200 0 0 0 0 ]

  execsql {
    DELETE FROM rlog;
    CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW
      BEGIN
      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
	  0, 0,
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  new.a, new.b);
    END;

    CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW
      BEGIN
      INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 
	  0, 0,
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  new.a, new.b);
    END;
  }
  do_test trig-1.3.$ii {
    execsql {

      CREATE TABLE other_tbl(a, b);
      INSERT INTO other_tbl VALUES(1, 2);
      INSERT INTO other_tbl VALUES(3, 4);
      -- INSERT INTO tbl SELECT * FROM other_tbl;
      INSERT INTO tbl VALUES(5, 6);
      DROP TABLE other_tbl;

      SELECT * FROM rlog;
    }
  } [list 1 0 0 0 0 5 6 \
          2 0 0 5 6 5 6 ]
}
catchsql {
  DROP TABLE rlog;
  DROP TABLE clog;
  DROP TABLE tbl;
  DROP TABLE other_tbl;
}

# 2.
set ii 0
foreach tr_program [ list \
   {UPDATE tbl SET b = old.b;} \
  {INSERT INTO log VALUES(new.c, 2, 3);} \
  {DELETE FROM log WHERE a = 1;} \
  {INSERT INTO tbl VALUES(500, new.b * 10, 700); 
    UPDATE tbl SET c = old.c; 
    DELETE FROM log;} \
  {INSERT INTO log select * from tbl;} 
   ] \
{
  foreach test_varset [ list \
    {
      set statement {UPDATE tbl SET c = 10 WHERE a = 1;} 
      set prep      {INSERT INTO tbl VALUES(1, 2, 3);}
      set newC 10
      set newB 2
      set newA 1
      set oldA 1
      set oldB 2
      set oldC 3
    } \
    {
      set statement {DELETE FROM tbl WHERE a = 1;}
      set prep      {INSERT INTO tbl VALUES(1, 2, 3);}
      set oldA 1
      set oldB 2
      set oldC 3
    } \
    {
      set statement {INSERT INTO tbl VALUES(1, 2, 3);}
      set newA 1
      set newB 2
      set newC 3
    }
  ] \
  {
    set statement {}
    set prep {}
    set newA {''}
    set newB {''}
    set newC {''}
    set oldA {''}
    set oldB {''}
    set oldC {''}

    incr ii

    eval $test_varset

    set statement_type [string range $statement 0 5]
    set tr_program_fixed $tr_program
    if {$statement_type == "DELETE"} {
      regsub -all new\.a $tr_program_fixed {''} tr_program_fixed 
      regsub -all new\.b $tr_program_fixed {''} tr_program_fixed 
      regsub -all new\.c $tr_program_fixed {''} tr_program_fixed 
    }
    if {$statement_type == "INSERT"} {
      regsub -all old\.a $tr_program_fixed {''} tr_program_fixed 
      regsub -all old\.b $tr_program_fixed {''} tr_program_fixed 
      regsub -all old\.c $tr_program_fixed {''} tr_program_fixed 
    }


    set tr_program_cooked $tr_program
    regsub -all new\.a $tr_program_cooked $newA tr_program_cooked 
    regsub -all new\.b $tr_program_cooked $newB tr_program_cooked 
    regsub -all new\.c $tr_program_cooked $newC tr_program_cooked 
    regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked 
    regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked 
    regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked 

    catchsql {
      DROP TABLE tbl;
      DROP TABLE log;
    }
    execsql {
      CREATE TABLE tbl(a PRIMARY KEY, b, c);
      CREATE TABLE log(a, b, c);
    }

    set query {SELECT * FROM tbl; SELECT * FROM log;}
    set prep "$prep; INSERT INTO log VALUES(1, 2, 3); INSERT INTO log VALUES(10, 20, 30);"

# Check execution of BEFORE programs:

    set before_data [ execsql "$prep $tr_program_cooked $statement $query" ]

    execsql "DELETE FROM tbl; DELETE FROM log; $prep";
    execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;"

    do_test trig-2-$ii-before "execsql {$statement $query}" $before_data

    execsql "DROP TRIGGER the_trigger;"
    execsql "DELETE FROM tbl; DELETE FROM log;"

# Check execution of AFTER programs
    set after_data [ execsql "$prep $statement $tr_program_cooked $query" ]

    execsql "DELETE FROM tbl; DELETE FROM log; $prep";

    execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;"

    do_test trig-2-$ii-after "execsql {$statement $query}" $after_data
    execsql "DROP TRIGGER the_trigger;"
  }
}
catchsql {
  DROP TABLE tbl;
  DROP TABLE log;
}

# 3.

# trig-3.1: UPDATE OF triggers
execsql {
  CREATE TABLE tbl (a, b, c, d);
  CREATE TABLE log (a);
  INSERT INTO log VALUES (0);
  INSERT INTO tbl VALUES (0, 0, 0, 0);
  INSERT INTO tbl VALUES (1, 0, 0, 0);
  CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl
    BEGIN
      UPDATE log SET a = a + 1;
    END;
}
do_test trig-3.1 {
  execsql {
    UPDATE tbl SET b = 1, c = 10; -- 2
    UPDATE tbl SET b = 10; -- 0
    UPDATE tbl SET d = 4 WHERE a = 0; --1
    UPDATE tbl SET a = 4, b = 10; --0
    SELECT * FROM log;
  }
} {3}
execsql {
  DROP TABLE tbl;
  DROP TABLE log;
}

# trig-3.2: WHEN clause
set when_triggers [ list \
             {t1 BEFORE INSERT ON tbl WHEN new.a > 20} \
             {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0} ]

execsql {
  CREATE TABLE tbl (a, b, c, d);
  CREATE TABLE log (a);
  INSERT INTO log VALUES (0);
}

foreach trig $when_triggers {
  execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;"
}

do_test trig-3.2 {
  execsql { 

    INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 1
    SELECT * FROM log;
    UPDATE log SET a = 0;

    INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 0
    SELECT * FROM log;
    UPDATE log SET a = 0;

    INSERT INTO tbl VALUES(200, 0, 0, 0);     -- 1
    SELECT * FROM log;
    UPDATE log SET a = 0;
  }
} {1 0 1}
execsql {
  DROP TABLE tbl;
  DROP TABLE log;
}

# Simple cascaded trigger
execsql {
  CREATE TABLE tblA(a, b);
  CREATE TABLE tblB(a, b);
  CREATE TABLE tblC(a, b);

  CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN
    INSERT INTO tblB values(new.a, new.b);
  END;

  CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN
    INSERT INTO tblC values(new.a, new.b);
  END;
}
do_test trig-4.1 {
  execsql {
    INSERT INTO tblA values(1, 2);
    SELECT * FROM tblA;
    SELECT * FROM tblB;
    SELECT * FROM tblC;
  }
} {1 2 1 2 1 2}
execsql {
  DROP TABLE tblA;
  DROP TABLE tblB;
  DROP TABLE tblC;
}

# Simple recursive trigger
execsql {
  CREATE TABLE tbl(a, b, c);
  CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 
    BEGIN
      INSERT INTO tbl VALUES (new.a, new.b, new.c);
    END;
}
do_test trig-4.2 {
  execsql {
    INSERT INTO tbl VALUES (1, 2, 3);
    select * from tbl;
  }
} {1 2 3 1 2 3}
execsql {
  DROP TABLE tbl;
}

# 5.
execsql {
  CREATE TABLE tbl(a, b, c);
  CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 
    BEGIN
      INSERT INTO tbl VALUES (1, 2, 3);
      INSERT INTO tbl VALUES (2, 2, 3);
      UPDATE tbl set b = 10 WHERE a = 1;
      DELETE FROM tbl WHERE a = 1;
      DELETE FROM tbl;
    END;
}
do_test trig-5 {
  execsql {
    INSERT INTO tbl VALUES(100, 200, 300);
  }
  db changes
} {1}
execsql {
  DROP TABLE tbl;
}


# Handling of ON CONFLICT by INSERT statements inside triggers
execsql {
  CREATE TABLE tbl (a primary key, b, c);
  CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN
    INSERT OR IGNORE INTO tbl values (new.a, 0, 0);
  END;
}
do_test trig-6.1a {
  execsql {
    BEGIN;
    INSERT INTO tbl values (1, 2, 3);
    SELECT * from tbl;
  }
} {1 2 3}
do_test trig-6.1b {
  catchsql {
    INSERT OR ABORT INTO tbl values (2, 2, 3);
  }
} {1 {constraint failed}}
do_test trig-6.1c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3}
do_test trig-6.1d {
  catchsql {
    INSERT OR FAIL INTO tbl values (2, 2, 3);
  }
} {1 {constraint failed}}
do_test trig-6.1e {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3 2 2 3}
do_test trig-6.1f {
  execsql {
    INSERT OR REPLACE INTO tbl values (2, 2, 3);
    SELECT * from tbl;
  }
} {1 2 3 2 0 0}
do_test trig-6.1g {
  catchsql {
    INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
  }
} {1 {constraint failed}}
do_test trig-6.1h {
  execsql {
    SELECT * from tbl;
  }
} {}


# Handling of ON CONFLICT by UPDATE statements inside triggers
execsql {
  INSERT INTO tbl values (4, 2, 3);
  INSERT INTO tbl values (6, 3, 4);
  CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN
    UPDATE OR IGNORE tbl SET a = new.a, c = 10;
  END;
}
do_test trig-6.2a {
  execsql {
    BEGIN;
    UPDATE tbl SET a = 1 WHERE a = 4;
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trig-6.2b {
  catchsql {
    UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trig-6.2c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trig-6.2d {
  catchsql {
    UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trig-6.2e {
  execsql {
    SELECT * from tbl;
  }
} {4 2 10 6 3 4}
do_test trig-6.2f {
  execsql {
    UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4;
    SELECT * from tbl;
  }
} {1 3 10}
execsql {
  INSERT INTO tbl VALUES (2, 3, 4);
}
do_test trig-6.2g {
  catchsql {
    UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trig-6.2h {
  execsql {
    SELECT * from tbl;
  }
} {4 2 3 6 3 4}
execsql {
  DROP TABLE tbl;
}

# 7. Triggers on views
execsql {
  CREATE TABLE ab(a, b);
  CREATE TABLE cd(c, d);
  INSERT INTO ab VALUES (1, 2);
  INSERT INTO ab VALUES (0, 0);
  INSERT INTO cd VALUES (3, 4);

  CREATE TABLE tlog(ii INTEGER PRIMARY KEY, 
      olda, oldb, oldc, oldd, newa, newb, newc, newd);

  CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd;

  CREATE TRIGGER before_update BEFORE UPDATE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
  END;
  CREATE TRIGGER after_update AFTER UPDATE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
  END;

  CREATE TRIGGER before_delete BEFORE DELETE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, 0, 0, 0, 0);
  END;
  CREATE TRIGGER after_delete AFTER DELETE ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	old.a, old.b, old.c, old.d, 0, 0, 0, 0);
  END;

  CREATE TRIGGER before_insert BEFORE INSERT ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	0, 0, 0, 0, new.a, new.b, new.c, new.d);
  END;
   CREATE TRIGGER after_insert AFTER INSERT ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	0, 0, 0, 0, new.a, new.b, new.c, new.d);
   END;
}

do_test trig-7 {
  execsql {
    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
    DELETE FROM abcd WHERE a = 1;
    INSERT INTO abcd VALUES(10, 20, 30, 40);
    SELECT * FROM tlog;
  }
} [ list 1 1 2 3 4 100 25 3 4 \
         2 1 2 3 4 100 25 3 4 \
 3 1 2 3 4 0 0 0 0 4 1 2 3 4 0 0 0 0 \
 5 0 0 0 0 10 20 30 40 6 0 0 0 0 10 20 30 40 ]

finish_test

Changes to www/lang.tcl.
1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.33 2002/05/06 11:47:33 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>



|







1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.34 2002/05/15 08:30:15 danielk1977 Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
50
51
52
53
54
55
56


57
58
59
60
61
62
63
  {EXPLAIN explain}
  {expression expr}
  {{BEGIN TRANSACTION} transaction}
  {PRAGMA pragma}
  {{ON CONFLICT clause} conflict}
  {{CREATE VIEW} createview}
  {{DROP VIEW} dropview}


}] {
  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
}
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>







>
>







50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
  {EXPLAIN explain}
  {expression expr}
  {{BEGIN TRANSACTION} transaction}
  {PRAGMA pragma}
  {{ON CONFLICT clause} conflict}
  {{CREATE VIEW} createview}
  {{DROP VIEW} dropview}
  {{CREATE TRIGGER} createtrigger}
  {{DROP TRIGGER} droptrigger}
}] {
  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
}
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>
1084
1085
1086
1087
1088
1089
1090
1091

1092


























1093
































































































1094
1095
1096
1097
1098
1099
table or index then it is suppose to clean up the named table or index.
In version 1.0 of SQLite, the VACUUM command would invoke 
<b>gdbm_reorganize()</b> to clean up the backend database file.
Beginning with version 2.0 of SQLite, GDBM is no longer used for
the database backend and VACUUM has become a no-op.
</p>
}





























puts {
































































































<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}








>

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

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






1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
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
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
table or index then it is suppose to clean up the named table or index.
In version 1.0 of SQLite, the VACUUM command would invoke 
<b>gdbm_reorganize()</b> to clean up the backend database file.
Beginning with version 2.0 of SQLite, GDBM is no longer used for
the database backend and VACUUM has become a no-op.
</p>
}

Section {CREATE TRIGGER} createtrigger

Syntax {sql-statement} {
CREATE TRIGGER <trigger-name> [ BEFORE | AFTER ]
<database-event>
<trigger-action>
}

Syntax {database-event} {
DELETE | 
INSERT | 
UPDATE | 
UPDATE OF <column-list>
ON <table-name> 
}

Syntax {trigger-action} {
[ FOR EACH ROW ] [ WHEN <expression> ] 
BEGIN 
  <trigger-step> ; [ <trigger-step> ; ]*
END
}

Syntax {trigger-step} {
<update-statement> | <insert-statement> | 
<delete-statement> | <select-statement> 
}

puts {
<p>The CREATE TRIGGER statement is used to add triggers to the 
database schema. Triggers are database operations (the <i>trigger-action</i>) 
that are automatically performed when a specified database event (the
<i>database-event</i>) occurs.  </p>

<p>A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a
particular database table occurs, or whenever an UPDATE of one or more
specified columns of a table are updated.</p>

<p>At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH
STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional.  FOR
EACH ROW implies that the SQL statements specified as <i>trigger-steps</i> 
may be executed (depending on the WHEN clause) for each database row being
inserted, updated or deleted by the statement causing the trigger to fire.</p>

<p>Both the WHEN clause and the <i>trigger-steps</i> may access elements of 
the row being inserted, deleted or updated using references of the form 
"NEW.<i>column-name</i>" and "OLD.<i>column-name</i>", where
<i>column-name</i> is the name of a column from the table that the trigger
is associated with. OLD and NEW references may only be used in triggers on
<i>trigger-event</i>s for which they are relevant, as follows:</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120><i>INSERT</i></td>
<td valign="top">NEW references are valid</td>
</tr>
<tr>
<td valign="top" align="right" width=120><i>UPDATE</i></td>
<td valign="top">NEW and OLD references are valid</td>
</tr>
<tr>
<td valign="top" align="right" width=120><i>DELETE</i></td>
<td valign="top">OLD references are valid</td>
</tr>
</table>
</p>

<p>If a WHEN clause is supplied, the SQL statements specified as <i>trigger-steps</i> are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.</p>

<p>The specified <i>trigger-time</i> determines when the <i>trigger-steps</i>
will be executed relative to the insertion, modification or removal of the
associated row.</p>

<p>An ON CONFLICT clause may be specified as part of an UPDATE or INSERT
<i>trigger-step</i>. However if an ON CONFLICT clause is specified as part of 
the statement causing the trigger to fire, then this conflict handling
policy is used instead.</p>

<p>Triggers are automatically dropped when the table that they are 
associated with is dropped.</p>

<p>Triggers may be created on views, as well as ordinary tables. If one or
more INSERT, DELETE or UPDATE triggers are defined on a view, then it is not
an error to execute an INSERT, DELETE or UPDATE statement on the view, 
respectively. Thereafter, executing an INSERT, DELETE or UPDATE on the view
causes the associated triggers to fire. The real tables underlying the view
are not modified (except possibly explicitly, by a trigger program).</p>

<p><b>Example:</b></p>

<p>Assuming that customer records are stored in the "customers" table, and
that order records are stored in the "orders" table, the following trigger
ensures that all associated orders are redirected when a customer changes
his or her address:</p>
}
Example {
CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;
}
puts {
<p>With this trigger installed, executing the statement:</p>
}
Example {
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
}
puts {
<p>causes the following to be automatically executed:</p>
}
Example {
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
}

Section {DROP TRIGGER} droptrigger
Syntax {sql-statement} {
DROP TRIGGER <trigger-name>
}
puts { 
  <p>Used to drop a trigger from the database schema. Note that triggers
  are automatically dropped when the associated table is dropped.</p>
}


puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}