Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Added FOR EACH ROW triggers functionality (CVS 562) |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | 794bf67b6b36fce8854d5daff12f21db |
| User & Date: | danielk1977 2002-05-15 08:30:13 |
Context
|
2002-05-15
| ||
| 08:43 | Add the Makefile.in that was forgotten with checkin #562 (CVS 563) check-in: 29b8330c user: danielk1977 tags: trunk | |
| 08:30 | Added FOR EACH ROW triggers functionality (CVS 562) check-in: 794bf67b user: danielk1977 tags: trunk | |
|
2002-05-10
| ||
| 14:41 | Version 2.4.12 (CVS 561) check-in: 06cdaf1c user: drh tags: trunk | |
Changes
Changes to src/build.c.
21 21 ** COPY 22 22 ** VACUUM 23 23 ** BEGIN TRANSACTION 24 24 ** COMMIT 25 25 ** ROLLBACK 26 26 ** PRAGMA 27 27 ** 28 -** $Id: build.c,v 1.87 2002/05/08 21:30:15 drh Exp $ 28 +** $Id: build.c,v 1.88 2002/05/15 08:30:13 danielk1977 Exp $ 29 29 */ 30 30 #include "sqliteInt.h" 31 31 #include <ctype.h> 32 32 33 33 /* 34 34 ** This routine is called after a single SQL statement has been 35 35 ** parsed and we want to execute the VDBE code to implement ................................................................................ 246 246 pIndex->isCommit = 1; 247 247 } 248 248 while( (pElem=sqliteHashFirst(&db->idxDrop))!=0 ){ 249 249 Index *pIndex = sqliteHashData(pElem); 250 250 sqliteUnlinkAndDeleteIndex(db, pIndex); 251 251 } 252 252 sqliteHashClear(&db->idxDrop); 253 + 254 + /* Set the commit flag on all triggers added this transaction */ 255 + for(pElem=sqliteHashFirst(&db->trigHash); pElem; pElem=sqliteHashNext(pElem)){ 256 + Trigger *pTrigger = sqliteHashData(pElem); 257 + pTrigger->isCommit = 1; 258 + } 259 + 260 + /* Delete the structures for triggers removed this transaction */ 261 + pElem = sqliteHashFirst(&db->trigDrop); 262 + while (pElem) { 263 + Trigger *pTrigger = sqliteHashData(pElem); 264 + sqliteDeleteTrigger(pTrigger); 265 + pElem = sqliteHashNext(pElem); 266 + } 267 + sqliteHashClear(&db->trigDrop); 268 + 253 269 db->flags &= ~SQLITE_InternChanges; 254 270 } 255 271 256 272 /* 257 273 ** This routine runs when one or more CREATE TABLE, CREATE INDEX, 258 274 ** DROP TABLE, or DROP INDEX statements gets rolled back. The 259 275 ** additions or deletions of Table and Index structures in the ................................................................................ 300 316 Index *pOld, *p = sqliteHashData(pElem); 301 317 assert( p->isCommit ); 302 318 p->isDropped = 0; 303 319 pOld = sqliteHashInsert(&db->idxHash, p->zName, strlen(p->zName)+1, p); 304 320 assert( pOld==0 || pOld==p ); 305 321 } 306 322 sqliteHashClear(&db->idxDrop); 323 + 324 + /* Remove any triggers that haven't been commited yet */ 325 + for(pElem = sqliteHashFirst(&db->trigHash); pElem; 326 + pElem = (pElem?sqliteHashNext(pElem):0)) { 327 + Trigger * pTrigger = sqliteHashData(pElem); 328 + if (!pTrigger->isCommit) { 329 + Table * tbl = sqliteFindTable(db, pTrigger->table); 330 + if (tbl) { 331 + if (tbl->pTrigger == pTrigger) 332 + tbl->pTrigger = pTrigger->pNext; 333 + else { 334 + Trigger * cc = tbl->pTrigger; 335 + while (cc) { 336 + if (cc->pNext == pTrigger) { 337 + cc->pNext = cc->pNext->pNext; 338 + break; 339 + } 340 + cc = cc->pNext; 341 + } 342 + assert(cc); 343 + } 344 + } 345 + sqliteHashInsert(&db->trigHash, pTrigger->name, 346 + 1 + strlen(pTrigger->name), 0); 347 + sqliteDeleteTrigger(pTrigger); 348 + pElem = sqliteHashFirst(&db->trigHash); 349 + } 350 + } 351 + 352 + /* Any triggers that were dropped - put 'em back in place */ 353 + for(pElem = sqliteHashFirst(&db->trigDrop); pElem; 354 + pElem = sqliteHashNext(pElem)) { 355 + Trigger * pTrigger = sqliteHashData(pElem); 356 + Table * tab = sqliteFindTable(db, pTrigger->table); 357 + sqliteHashInsert(&db->trigHash, pTrigger->name, 358 + strlen(pTrigger->name) + 1, pTrigger); 359 + 360 + pTrigger->pNext = tab->pTrigger; 361 + tab->pTrigger = pTrigger; 362 + } 363 + 364 + sqliteHashClear(&db->trigDrop); 307 365 db->flags &= ~SQLITE_InternChanges; 308 366 } 309 367 310 368 /* 311 369 ** Construct the name of a user table or index from a token. 312 370 ** 313 371 ** Space to hold the name is obtained from sqliteMalloc() and must ................................................................................ 591 649 ** 592 650 ** This plan is not completely bullet-proof. It is possible for 593 651 ** the schema to change multiple times and for the cookie to be 594 652 ** set back to prior value. But schema changes are infrequent 595 653 ** and the probability of hitting the same cookie value is only 596 654 ** 1 chance in 2^32. So we're safe enough. 597 655 */ 598 -static void changeCookie(sqlite *db){ 656 +void changeCookie(sqlite *db){ 599 657 if( db->next_cookie==db->schema_cookie ){ 600 658 db->next_cookie = db->schema_cookie + sqliteRandomByte() + 1; 601 659 db->flags |= SQLITE_InternChanges; 602 660 } 603 661 } 604 662 605 663 /* ................................................................................ 1032 1090 { OP_Next, 0, ADDR(4), 0}, /* 8 */ 1033 1091 { OP_Integer, 0, 0, 0}, /* 9 */ 1034 1092 { OP_SetCookie, 0, 0, 0}, 1035 1093 { OP_Close, 0, 0, 0}, 1036 1094 }; 1037 1095 Index *pIdx; 1038 1096 sqliteBeginWriteOperation(pParse); 1097 + /* Drop all triggers associated with the table being dropped */ 1098 + while (pTable->pTrigger) { 1099 + Token tt; 1100 + tt.z = pTable->pTrigger->name; 1101 + tt.n = strlen(pTable->pTrigger->name); 1102 + sqliteDropTrigger(pParse, &tt, 1); 1103 + } 1039 1104 if( !pTable->isTemp ){ 1040 1105 base = sqliteVdbeAddOpList(v, ArraySize(dropTable), dropTable); 1041 1106 sqliteVdbeChangeP3(v, base+2, pTable->zName, 0); 1042 1107 changeCookie(db); 1043 1108 sqliteVdbeChangeP1(v, base+9, db->next_cookie); 1044 1109 } 1045 1110 if( !isView ){ ................................................................................ 1649 1714 ** all. So there is not need to set a checkpoint is a transaction 1650 1715 ** is already in effect. 1651 1716 */ 1652 1717 void sqliteBeginWriteOperation(Parse *pParse){ 1653 1718 Vdbe *v; 1654 1719 v = sqliteGetVdbe(pParse); 1655 1720 if( v==0 ) return; 1721 + if (pParse->trigStack) return; /* if this is in a trigger */ 1656 1722 if( (pParse->db->flags & SQLITE_InTrans)==0 ){ 1657 1723 sqliteVdbeAddOp(v, OP_Transaction, 0, 0); 1658 1724 sqliteVdbeAddOp(v, OP_VerifyCookie, pParse->db->schema_cookie, 0); 1659 1725 pParse->schemaVerified = 1; 1660 1726 } 1661 1727 } 1662 1728 ................................................................................ 1668 1734 ** of a transaction, then this sets a checkpoint. If we are not in 1669 1735 ** a transaction, then start a transaction. 1670 1736 */ 1671 1737 void sqliteBeginMultiWriteOperation(Parse *pParse){ 1672 1738 Vdbe *v; 1673 1739 v = sqliteGetVdbe(pParse); 1674 1740 if( v==0 ) return; 1741 + if (pParse->trigStack) return; /* if this is in a trigger */ 1675 1742 if( (pParse->db->flags & SQLITE_InTrans)==0 ){ 1676 1743 sqliteVdbeAddOp(v, OP_Transaction, 0, 0); 1677 1744 sqliteVdbeAddOp(v, OP_VerifyCookie, pParse->db->schema_cookie, 0); 1678 1745 pParse->schemaVerified = 1; 1679 1746 }else{ 1680 1747 sqliteVdbeAddOp(v, OP_Checkpoint, 0, 0); 1681 1748 } ................................................................................ 1685 1752 ** Generate code that concludes an operation that may have changed 1686 1753 ** the database. This is a companion function to BeginWriteOperation(). 1687 1754 ** If a transaction was started, then commit it. If a checkpoint was 1688 1755 ** started then commit that. 1689 1756 */ 1690 1757 void sqliteEndWriteOperation(Parse *pParse){ 1691 1758 Vdbe *v; 1759 + if (pParse->trigStack) return; /* if this is in a trigger */ 1692 1760 v = sqliteGetVdbe(pParse); 1693 1761 if( v==0 ) return; 1694 1762 if( pParse->db->flags & SQLITE_InTrans ){ 1695 1763 /* Do Nothing */ 1696 1764 }else{ 1697 1765 sqliteVdbeAddOp(v, OP_Commit, 0, 0); 1698 1766 } ................................................................................ 1910 1978 int size = db->cache_size; 1911 1979 if( size<0 ) size = -size; 1912 1980 if( !getBoolean(zRight) ) size = -size; 1913 1981 db->cache_size = size; 1914 1982 sqliteBtreeSetCacheSize(db->pBe, db->cache_size); 1915 1983 } 1916 1984 }else 1985 + 1986 + if( sqliteStrICmp(zLeft, "trigger_overhead_test")==0 ){ 1987 + if( getBoolean(zRight) ){ 1988 + always_code_trigger_setup = 1; 1989 + }else{ 1990 + always_code_trigger_setup = 0; 1991 + } 1992 + }else 1917 1993 1918 1994 if( sqliteStrICmp(zLeft, "vdbe_trace")==0 ){ 1919 1995 if( getBoolean(zRight) ){ 1920 1996 db->flags |= SQLITE_VdbeTrace; 1921 1997 }else{ 1922 1998 db->flags &= ~SQLITE_VdbeTrace; 1923 1999 }
Changes to src/delete.c.
8 8 ** May you find forgiveness for yourself and forgive others. 9 9 ** May you share freely, never taking more than you give. 10 10 ** 11 11 ************************************************************************* 12 12 ** This file contains C code routines that are called by the parser 13 13 ** to handle DELETE FROM statements. 14 14 ** 15 -** $Id: delete.c,v 1.30 2002/04/12 10:08:59 drh Exp $ 15 +** $Id: delete.c,v 1.31 2002/05/15 08:30:13 danielk1977 Exp $ 16 16 */ 17 17 #include "sqliteInt.h" 18 18 19 19 20 20 /* 21 21 ** Given a table name, find the corresponding table and make sure the 22 22 ** table is writeable. Generate an error and return NULL if not. If ................................................................................ 80 80 int i; /* Loop counter */ 81 81 WhereInfo *pWInfo; /* Information about the WHERE clause */ 82 82 Index *pIdx; /* For looping over indices of the table */ 83 83 int base; /* Index of the first available table cursor */ 84 84 sqlite *db; /* Main database structure */ 85 85 int openOp; /* Opcode used to open a cursor to the table */ 86 86 87 + int row_triggers_exist = 0; 88 + int oldIdx = -1; 87 89 88 90 if( pParse->nErr || sqlite_malloc_failed ){ 89 91 pTabList = 0; 90 92 goto delete_from_cleanup; 91 93 } 92 94 db = pParse->db; 95 + 96 + /* Check for the special case of a VIEW with one or more ON DELETE triggers 97 + * defined 98 + */ 99 + { 100 + Table * pTab; 101 + char * zTab = sqliteTableNameFromToken(pTableName); 102 + 103 + if(zTab != 0) { 104 + pTab = sqliteFindTable(pParse->db, zTab); 105 + if (pTab) { 106 + row_triggers_exist = 107 + sqliteTriggersExist(pParse, pTab->pTrigger, 108 + TK_DELETE, TK_BEFORE, TK_ROW, 0) || 109 + sqliteTriggersExist(pParse, pTab->pTrigger, 110 + TK_DELETE, TK_AFTER, TK_ROW, 0); 111 + } 112 + sqliteFree(zTab); 113 + if (row_triggers_exist && pTab->pSelect ) { 114 + /* Just fire VIEW triggers */ 115 + sqliteViewTriggers(pParse, pTab, pWhere, OE_Replace, 0); 116 + return; 117 + } 118 + } 119 + } 93 120 94 121 /* Locate the table which we want to delete. This table has to be 95 122 ** put in an IdList structure because some of the subroutines we 96 123 ** will be calling are designed to work with multiple tables and expect 97 124 ** an IdList* parameter instead of just a Table* parameger. 98 125 */ 99 126 pTabList = sqliteTableTokenToIdList(pParse, pTableName); 100 127 if( pTabList==0 ) goto delete_from_cleanup; 101 128 assert( pTabList->nId==1 ); 102 129 pTab = pTabList->a[0].pTab; 103 130 assert( pTab->pSelect==0 ); /* This table is not a view */ 104 131 132 + if (row_triggers_exist) 133 + oldIdx = pParse->nTab++; 134 + 105 135 /* Resolve the column names in all the expressions. 106 136 */ 107 137 base = pParse->nTab++; 108 138 if( pWhere ){ 109 139 if( sqliteExprResolveIds(pParse, base, pTabList, 0, pWhere) ){ 110 140 goto delete_from_cleanup; 111 141 } ................................................................................ 114 144 } 115 145 } 116 146 117 147 /* Begin generating code. 118 148 */ 119 149 v = sqliteGetVdbe(pParse); 120 150 if( v==0 ) goto delete_from_cleanup; 121 - sqliteBeginWriteOperation(pParse); 151 + if (row_triggers_exist) 152 + sqliteBeginMultiWriteOperation(pParse); 153 + else 154 + sqliteBeginWriteOperation(pParse); 122 155 123 156 /* Initialize the counter of the number of rows deleted, if 124 157 ** we are counting rows. 125 158 */ 126 159 if( db->flags & SQLITE_CountRows ){ 127 160 sqliteVdbeAddOp(v, OP_Integer, 0, 0); 128 161 } 129 162 130 163 /* Special case: A DELETE without a WHERE clause deletes everything. 131 164 ** It is easier just to erase the whole table. 132 165 */ 133 - if( pWhere==0 ){ 166 + if( pWhere==0 && !row_triggers_exist){ 134 167 if( db->flags & SQLITE_CountRows ){ 135 168 /* If counting rows deleted, just count the total number of 136 169 ** entries in the table. */ 137 170 int endOfLoop = sqliteVdbeMakeLabel(v); 138 171 int addr; 139 172 openOp = pTab->isTemp ? OP_OpenAux : OP_Open; 140 173 assert( base==0 ); ................................................................................ 172 205 sqliteWhereEnd(pWInfo); 173 206 174 207 /* Delete every item whose key was written to the list during the 175 208 ** database scan. We have to delete items after the scan is complete 176 209 ** because deleting an item can change the scan order. 177 210 */ 178 211 sqliteVdbeAddOp(v, OP_ListRewind, 0, 0); 212 + end = sqliteVdbeMakeLabel(v); 213 + 214 + if (row_triggers_exist) { 215 + int ii; 216 + addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end); 217 + sqliteVdbeAddOp(v, OP_Dup, 0, 0); 218 + 219 + openOp = pTab->isTemp ? OP_OpenAux : OP_Open; 220 + sqliteVdbeAddOp(v, openOp, base, pTab->tnum); 221 + sqliteVdbeAddOp(v, OP_MoveTo, base, 0); 222 + sqliteVdbeAddOp(v, OP_OpenTemp, oldIdx, 0); 223 + 224 + sqliteVdbeAddOp(v, OP_Integer, 13, 0); 225 + for (ii = 0; ii < pTab->nCol; ii++) { 226 + if (ii == pTab->iPKey) 227 + sqliteVdbeAddOp(v, OP_Recno, base, 0); 228 + else 229 + sqliteVdbeAddOp(v, OP_Column, base, ii); 230 + } 231 + sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0); 232 + sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0); 233 + sqliteVdbeAddOp(v, OP_Close, base, 0); 234 + sqliteVdbeAddOp(v, OP_Rewind, oldIdx, 0); 235 + 236 + sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1, 237 + oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default); 238 + } 239 + 240 + pParse->nTab = base + 1; 179 241 openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite; 180 242 sqliteVdbeAddOp(v, openOp, base, pTab->tnum); 181 243 for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ 182 - sqliteVdbeAddOp(v, openOp, base+i, pIdx->tnum); 244 + sqliteVdbeAddOp(v, openOp, pParse->nTab++, pIdx->tnum); 245 + } 246 + 247 + if (!row_triggers_exist) 248 + addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end); 249 + 250 + sqliteGenerateRowDelete(v, pTab, base, pParse->trigStack?0:1); 251 + 252 + if (row_triggers_exist) { 253 + for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ 254 + sqliteVdbeAddOp(v, OP_Close, base + i, pIdx->tnum); 255 + } 256 + sqliteVdbeAddOp(v, OP_Close, base, 0); 257 + sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1, 258 + oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default); 183 259 } 184 - end = sqliteVdbeMakeLabel(v); 185 - addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end); 186 - sqliteGenerateRowDelete(v, pTab, base, 1); 260 + 187 261 sqliteVdbeAddOp(v, OP_Goto, 0, addr); 188 262 sqliteVdbeResolveLabel(v, end); 189 263 sqliteVdbeAddOp(v, OP_ListReset, 0, 0); 264 + 265 + if (!row_triggers_exist) { 266 + for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ 267 + sqliteVdbeAddOp(v, OP_Close, base + i, pIdx->tnum); 268 + } 269 + sqliteVdbeAddOp(v, OP_Close, base, 0); 270 + pParse->nTab = base; 271 + } 190 272 } 191 273 sqliteEndWriteOperation(pParse); 192 274 193 275 /* 194 276 ** Return the number of rows that were deleted. 195 277 */ 196 278 if( db->flags & SQLITE_CountRows ){
Changes to src/expr.c.
8 8 ** May you find forgiveness for yourself and forgive others. 9 9 ** May you share freely, never taking more than you give. 10 10 ** 11 11 ************************************************************************* 12 12 ** This file contains routines used for analyzing expressions and 13 13 ** for generating VDBE code that evaluates expressions in SQLite. 14 14 ** 15 -** $Id: expr.c,v 1.58 2002/04/20 14:24:42 drh Exp $ 15 +** $Id: expr.c,v 1.59 2002/05/15 08:30:13 danielk1977 Exp $ 16 16 */ 17 17 #include "sqliteInt.h" 18 18 19 19 20 20 /* 21 21 ** Construct a new expression node and return a pointer to it. Memory 22 22 ** for this node is obtained from sqliteMalloc(). The calling function ................................................................................ 477 477 pExpr->iColumn = -1; 478 478 }else{ 479 479 pExpr->iColumn = j; 480 480 } 481 481 } 482 482 } 483 483 } 484 + 485 + /* If we have not already resolved this *.* expression, then maybe 486 + * it is a new.* or old.* trigger argument reference */ 487 + if (cnt == 0 && pParse->trigStack != 0) { 488 + TriggerStack * tt = pParse->trigStack; 489 + int j; 490 + int t = 0; 491 + if (tt->newIdx != -1 && sqliteStrICmp("new", zLeft) == 0) { 492 + pExpr->iTable = tt->newIdx; 493 + cntTab++; 494 + t = 1; 495 + } 496 + if (tt->oldIdx != -1 && sqliteStrICmp("old", zLeft) == 0) { 497 + pExpr->iTable = tt->oldIdx; 498 + cntTab++; 499 + t = 1; 500 + } 501 + 502 + if (t) 503 + for(j=0; j<tt->pTab->nCol; j++) { 504 + if( sqliteStrICmp(tt->pTab->aCol[j].zName, zRight)==0 ){ 505 + cnt++; 506 + pExpr->iColumn = j; 507 + } 508 + } 509 + } 510 + 484 511 if( cnt==0 && cntTab==1 && sqliteIsRowid(zRight) ){ 485 512 cnt = 1; 486 513 pExpr->iColumn = -1; 487 514 } 488 515 sqliteFree(zLeft); 489 516 sqliteFree(zRight); 490 517 if( cnt==0 ){
Changes to src/insert.c.
8 8 ** May you find forgiveness for yourself and forgive others. 9 9 ** May you share freely, never taking more than you give. 10 10 ** 11 11 ************************************************************************* 12 12 ** This file contains C code routines that are called by the parser 13 13 ** to handle INSERT statements in SQLite. 14 14 ** 15 -** $Id: insert.c,v 1.52 2002/04/12 10:08:59 drh Exp $ 15 +** $Id: insert.c,v 1.53 2002/05/15 08:30:13 danielk1977 Exp $ 16 16 */ 17 17 #include "sqliteInt.h" 18 18 19 19 /* 20 20 ** This routine is call to handle SQL of the following forms: 21 21 ** 22 22 ** insert into TABLE (IDLIST) values(EXPRLIST) ................................................................................ 36 36 Token *pTableName, /* Name of table into which we are inserting */ 37 37 ExprList *pList, /* List of values to be inserted */ 38 38 Select *pSelect, /* A SELECT statement to use as the data source */ 39 39 IdList *pColumn, /* Column names corresponding to IDLIST. */ 40 40 int onError /* How to handle constraint errors */ 41 41 ){ 42 42 Table *pTab; /* The table to insert into */ 43 - char *zTab; /* Name of the table into which we are inserting */ 43 + char *zTab = 0; /* Name of the table into which we are inserting */ 44 44 int i, j, idx; /* Loop counters */ 45 45 Vdbe *v; /* Generate code into this virtual machine */ 46 46 Index *pIdx; /* For looping over indices of the table */ 47 47 int srcTab; /* Date comes from this temporary cursor if >=0 */ 48 48 int nColumn; /* Number of columns in the data */ 49 49 int base; /* First available cursor */ 50 50 int iCont, iBreak; /* Beginning and end of the loop over srcTab */ 51 51 sqlite *db; /* The main database structure */ 52 52 int openOp; /* Opcode used to open cursors */ 53 53 int keyColumn = -1; /* Column that is the INTEGER PRIMARY KEY */ 54 54 int endOfLoop; /* Label for the end of the insertion loop */ 55 + 56 + int row_triggers_exist = 0; /* True if there are FOR EACH ROW triggers */ 57 + int newIdx = -1; 55 58 56 59 if( pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup; 57 60 db = pParse->db; 58 61 59 62 /* Locate the table into which we will be inserting new information. 60 63 */ 61 64 zTab = sqliteTableNameFromToken(pTableName); 62 65 if( zTab==0 ) goto insert_cleanup; 63 - pTab = sqliteTableNameToTable(pParse, zTab); 66 + pTab = sqliteFindTable(pParse->db, zTab); 67 + if( pTab==0 ){ 68 + sqliteSetString(&pParse->zErrMsg, "no such table: ", zTab, 0); 69 + pParse->nErr++; 70 + goto insert_cleanup; 71 + } 72 + 73 + /* Ensure that: 74 + * (a) the table is not read-only, 75 + * (b) that if it is a view then ON INSERT triggers exist 76 + */ 77 + row_triggers_exist = 78 + sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT, 79 + TK_BEFORE, TK_ROW, 0) || 80 + sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT, TK_AFTER, TK_ROW, 0); 81 + if( pTab->readOnly || (pTab->pSelect && !row_triggers_exist) ){ 82 + sqliteSetString(&pParse->zErrMsg, 83 + pTab->pSelect ? "view " : "table ", 84 + zTab, 85 + " may not be modified", 0); 86 + pParse->nErr++; 87 + goto insert_cleanup; 88 + } 64 89 sqliteFree(zTab); 90 + zTab = 0; 91 + 65 92 if( pTab==0 ) goto insert_cleanup; 66 - assert( pTab->pSelect==0 ); /* This table is not a VIEW */ 67 93 68 94 /* Allocate a VDBE 69 95 */ 70 96 v = sqliteGetVdbe(pParse); 71 97 if( v==0 ) goto insert_cleanup; 72 - if( pSelect ){ 98 + if( pSelect || row_triggers_exist ){ 73 99 sqliteBeginMultiWriteOperation(pParse); 74 100 }else{ 75 101 sqliteBeginWriteOperation(pParse); 76 102 } 103 + 104 + /* if there are row triggers, allocate a temp table for new.* references. */ 105 + if (row_triggers_exist) 106 + newIdx = pParse->nTab++; 77 107 78 108 /* Figure out how many columns of data are supplied. If the data 79 109 ** is coming from a SELECT statement, then this step has to generate 80 110 ** all the code to implement the SELECT statement and leave the data 81 111 ** in a temporary table. If data is coming from an expression list, 82 112 ** then we just have to count the number of expressions. 83 113 */ ................................................................................ 169 199 ** key, the set the keyColumn variable to the primary key column index 170 200 ** in the original table definition. 171 201 */ 172 202 if( pColumn==0 ){ 173 203 keyColumn = pTab->iPKey; 174 204 } 175 205 176 - /* Open cursors into the table that is received the new data and 177 - ** all indices of that table. 178 - */ 179 - base = pParse->nTab; 180 - openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite; 181 - sqliteVdbeAddOp(v, openOp, base, pTab->tnum); 182 - sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC); 183 - for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ 184 - sqliteVdbeAddOp(v, openOp, idx+base, pIdx->tnum); 185 - sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC); 186 - } 187 - pParse->nTab += idx; 188 - 206 + /* Open the temp table for FOR EACH ROW triggers */ 207 + if (row_triggers_exist) 208 + sqliteVdbeAddOp(v, OP_OpenTemp, newIdx, 0); 209 + 189 210 /* Initialize the count of rows to be inserted 190 211 */ 191 - if( db->flags & SQLITE_CountRows ){ 212 + if( db->flags & SQLITE_CountRows && !pParse->trigStack){ 192 213 sqliteVdbeAddOp(v, OP_Integer, 0, 0); /* Initialize the row count */ 193 214 } 215 + 216 + /* Open tables and indices if there are no row triggers */ 217 + if (!row_triggers_exist) { 218 + base = pParse->nTab; 219 + openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite; 220 + sqliteVdbeAddOp(v, openOp, base, pTab->tnum); 221 + sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC); 222 + for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ 223 + sqliteVdbeAddOp(v, openOp, idx+base, pIdx->tnum); 224 + sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC); 225 + } 226 + pParse->nTab += idx; 227 + } 194 228 195 229 /* If the data source is a SELECT statement, then we have to create 196 230 ** a loop because there might be multiple rows of data. If the data 197 231 ** source is an expression list, then exactly one row will be inserted 198 232 ** and the loop is not used. 199 233 */ 200 234 if( srcTab>=0 ){ 201 235 iBreak = sqliteVdbeMakeLabel(v); 202 236 sqliteVdbeAddOp(v, OP_Rewind, srcTab, iBreak); 203 237 iCont = sqliteVdbeCurrentAddr(v); 204 238 } 239 + 240 + if (row_triggers_exist) { 241 + 242 + /* build the new.* reference row */ 243 + sqliteVdbeAddOp(v, OP_Integer, 13, 0); 244 + for(i=0; i<pTab->nCol; i++){ 245 + if( pColumn==0 ){ 246 + j = i; 247 + }else{ 248 + for(j=0; j<pColumn->nId; j++){ 249 + if( pColumn->a[j].idx==i ) break; 250 + } 251 + } 252 + if( pColumn && j>=pColumn->nId ){ 253 + sqliteVdbeAddOp(v, OP_String, 0, 0); 254 + sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC); 255 + }else if( srcTab>=0 ){ 256 + sqliteVdbeAddOp(v, OP_Column, srcTab, j); 257 + }else{ 258 + sqliteExprCode(pParse, pList->a[j].pExpr); 259 + } 260 + } 261 + sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0); 262 + sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0); 263 + sqliteVdbeAddOp(v, OP_Rewind, newIdx, 0); 264 + 265 + /* Fire BEFORE triggers */ 266 + if ( 267 + sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_BEFORE, pTab, newIdx, -1, 268 + onError) 269 + ) goto insert_cleanup; 270 + 271 + /* Open the tables and indices for the INSERT */ 272 + if (!pTab->pSelect) { 273 + base = pParse->nTab; 274 + openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite; 275 + sqliteVdbeAddOp(v, openOp, base, pTab->tnum); 276 + sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC); 277 + for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ 278 + sqliteVdbeAddOp(v, openOp, idx+base, pIdx->tnum); 279 + sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC); 280 + } 281 + pParse->nTab += idx; 282 + } 283 + } 205 284 206 285 /* Push the record number for the new entry onto the stack. The 207 286 ** record number is a randomly generate integer created by NewRecno 208 287 ** except when the table has an INTEGER PRIMARY KEY column, in which 209 288 ** case the record number is the same as that column. 210 289 */ 211 - if( keyColumn>=0 ){ 212 - if( srcTab>=0 ){ 213 - sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn); 290 + if (!pTab->pSelect) { 291 + if( keyColumn>=0 ){ 292 + if( srcTab>=0 ){ 293 + sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn); 294 + }else{ 295 + int addr; 296 + sqliteExprCode(pParse, pList->a[keyColumn].pExpr); 297 + 298 + /* If the PRIMARY KEY expression is NULL, then use OP_NewRecno 299 + ** to generate a unique primary key value. 300 + */ 301 + addr = sqliteVdbeAddOp(v, OP_Dup, 0, 1); 302 + sqliteVdbeAddOp(v, OP_NotNull, 0, addr+4); 303 + sqliteVdbeAddOp(v, OP_Pop, 1, 0); 304 + sqliteVdbeAddOp(v, OP_NewRecno, base, 0); 305 + } 306 + sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0); 214 307 }else{ 215 - int addr; 216 - sqliteExprCode(pParse, pList->a[keyColumn].pExpr); 217 - 218 - /* If the PRIMARY KEY expression is NULL, then use OP_NewRecno 219 - ** to generate a unique primary key value. 220 - */ 221 - addr = sqliteVdbeAddOp(v, OP_Dup, 0, 1); 222 - sqliteVdbeAddOp(v, OP_NotNull, 0, addr+4); 223 - sqliteVdbeAddOp(v, OP_Pop, 1, 0); 224 308 sqliteVdbeAddOp(v, OP_NewRecno, base, 0); 225 309 } 226 - sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0); 227 - }else{ 228 - sqliteVdbeAddOp(v, OP_NewRecno, base, 0); 229 - } 230 - 231 - /* Push onto the stack, data for all columns of the new entry, beginning 232 - ** with the first column. 233 - */ 234 - for(i=0; i<pTab->nCol; i++){ 235 - if( i==pTab->iPKey ){ 236 - /* The value of the INTEGER PRIMARY KEY column is always a NULL. 237 - ** Whenever this column is read, the record number will be substituted 238 - ** in its place. So will fill this column with a NULL to avoid 239 - ** taking up data space with information that will never be used. */ 240 - sqliteVdbeAddOp(v, OP_String, 0, 0); 241 - continue; 242 - } 243 - if( pColumn==0 ){ 244 - j = i; 245 - }else{ 246 - for(j=0; j<pColumn->nId; j++){ 247 - if( pColumn->a[j].idx==i ) break; 310 + 311 + /* Push onto the stack, data for all columns of the new entry, beginning 312 + ** with the first column. 313 + */ 314 + for(i=0; i<pTab->nCol; i++){ 315 + if( i==pTab->iPKey ){ 316 + /* The value of the INTEGER PRIMARY KEY column is always a NULL. 317 + ** Whenever this column is read, the record number will be substituted 318 + ** in its place. So will fill this column with a NULL to avoid 319 + ** taking up data space with information that will never be used. */ 320 + sqliteVdbeAddOp(v, OP_String, 0, 0); 321 + continue; 322 + } 323 + if( pColumn==0 ){ 324 + j = i; 325 + }else{ 326 + for(j=0; j<pColumn->nId; j++){ 327 + if( pColumn->a[j].idx==i ) break; 328 + } 329 + } 330 + if( pColumn && j>=pColumn->nId ){ 331 + sqliteVdbeAddOp(v, OP_String, 0, 0); 332 + sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC); 333 + }else if( srcTab>=0 ){ 334 + sqliteVdbeAddOp(v, OP_Column, srcTab, j); 335 + }else{ 336 + sqliteExprCode(pParse, pList->a[j].pExpr); 248 337 } 249 338 } 250 - if( pColumn && j>=pColumn->nId ){ 251 - sqliteVdbeAddOp(v, OP_String, 0, 0); 252 - sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC); 253 - }else if( srcTab>=0 ){ 254 - sqliteVdbeAddOp(v, OP_Column, srcTab, j); 255 - }else{ 256 - sqliteExprCode(pParse, pList->a[j].pExpr); 339 + 340 + /* Generate code to check constraints and generate index keys and 341 + ** do the insertion. 342 + */ 343 + endOfLoop = sqliteVdbeMakeLabel(v); 344 + sqliteGenerateConstraintChecks(pParse, pTab, base, 0,0,0,onError,endOfLoop); 345 + sqliteCompleteInsertion(pParse, pTab, base, 0,0,0); 346 + 347 + /* Update the count of rows that are inserted 348 + */ 349 + if( (db->flags & SQLITE_CountRows)!=0 && !pParse->trigStack){ 350 + sqliteVdbeAddOp(v, OP_AddImm, 1, 0); 257 351 } 258 352 } 259 353 260 - /* Generate code to check constraints and generate index keys and 261 - ** do the insertion. 262 - */ 263 - endOfLoop = sqliteVdbeMakeLabel(v); 264 - sqliteGenerateConstraintChecks(pParse, pTab, base, 0,0,0, onError, endOfLoop); 265 - sqliteCompleteInsertion(pParse, pTab, base, 0,0,0); 354 + if (row_triggers_exist) { 355 + /* Close all tables opened */ 356 + if (!pTab->pSelect) { 357 + sqliteVdbeAddOp(v, OP_Close, base, 0); 358 + for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ 359 + sqliteVdbeAddOp(v, OP_Close, idx+base, 0); 360 + } 361 + } 266 362 267 - /* Update the count of rows that are inserted 268 - */ 269 - if( (db->flags & SQLITE_CountRows)!=0 ){ 270 - sqliteVdbeAddOp(v, OP_AddImm, 1, 0); 363 + /* Code AFTER triggers */ 364 + if ( 365 + sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_AFTER, pTab, newIdx, -1, 366 + onError) 367 + ) goto insert_cleanup; 271 368 } 272 369 273 370 /* The bottom of the loop, if the data source is a SELECT statement 274 - */ 371 + */ 275 372 sqliteVdbeResolveLabel(v, endOfLoop); 276 373 if( srcTab>=0 ){ 277 374 sqliteVdbeAddOp(v, OP_Next, srcTab, iCont); 278 375 sqliteVdbeResolveLabel(v, iBreak); 279 376 sqliteVdbeAddOp(v, OP_Close, srcTab, 0); 280 377 } 281 - sqliteVdbeAddOp(v, OP_Close, base, 0); 282 - for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ 283 - sqliteVdbeAddOp(v, OP_Close, idx+base, 0); 378 + 379 + if (!row_triggers_exist) { 380 + /* Close all tables opened */ 381 + sqliteVdbeAddOp(v, OP_Close, base, 0); 382 + for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ 383 + sqliteVdbeAddOp(v, OP_Close, idx+base, 0); 384 + } 284 385 } 386 + 285 387 sqliteEndWriteOperation(pParse); 286 388 287 389 /* 288 - ** Return the number of rows inserted. 390 + ** Return the number of rows inserted. 289 391 */ 290 - if( db->flags & SQLITE_CountRows ){ 392 + if( db->flags & SQLITE_CountRows && !pParse->trigStack ){ 291 393 sqliteVdbeAddOp(v, OP_ColumnCount, 1, 0); 292 394 sqliteVdbeAddOp(v, OP_ColumnName, 0, 0); 293 395 sqliteVdbeChangeP3(v, -1, "rows inserted", P3_STATIC); 294 396 sqliteVdbeAddOp(v, OP_Callback, 1, 0); 295 397 } 296 398 297 399 insert_cleanup: 298 400 if( pList ) sqliteExprListDelete(pList); 299 401 if( pSelect ) sqliteSelectDelete(pSelect); 402 + if ( zTab ) sqliteFree(zTab); 300 403 sqliteIdListDelete(pColumn); 301 404 } 302 405 303 406 /* 304 407 ** Generate code to do a constraint check prior to an INSERT or an UPDATE. 305 408 ** 306 409 ** When this routine is called, the stack contains (from bottom to top) ................................................................................ 569 672 assert( pTab->pSelect==0 ); /* This table is not a VIEW */ 570 673 for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){} 571 674 for(i=nIdx-1; i>=0; i--){ 572 675 if( aIdxUsed && aIdxUsed[i]==0 ) continue; 573 676 sqliteVdbeAddOp(v, OP_IdxPut, base+i+1, 0); 574 677 } 575 678 sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0); 576 - sqliteVdbeAddOp(v, OP_PutIntKey, base, 1); 679 + sqliteVdbeAddOp(v, OP_PutIntKey, base, pParse->trigStack?0:1); 577 680 if( isUpdate && recnoChng ){ 578 681 sqliteVdbeAddOp(v, OP_Pop, 1, 0); 579 682 } 580 683 }
Changes to src/main.c.
10 10 ** 11 11 ************************************************************************* 12 12 ** Main file for the SQLite library. The routines in this file 13 13 ** implement the programmer interface to the library. Routines in 14 14 ** other files are for internal use by SQLite and should not be 15 15 ** accessed by users of the library. 16 16 ** 17 -** $Id: main.c,v 1.71 2002/05/10 13:14:07 drh Exp $ 17 +** $Id: main.c,v 1.72 2002/05/15 08:30:13 danielk1977 Exp $ 18 18 */ 19 19 #include "sqliteInt.h" 20 20 #include "os.h" 21 21 22 22 /* 23 23 ** This is the callback routine for the code that initializes the 24 24 ** database. See sqliteInit() below for additional information. ................................................................................ 322 322 323 323 /* Allocate the sqlite data structure */ 324 324 db = sqliteMalloc( sizeof(sqlite) ); 325 325 if( pzErrMsg ) *pzErrMsg = 0; 326 326 if( db==0 ) goto no_mem_on_open; 327 327 sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0); 328 328 sqliteHashInit(&db->idxHash, SQLITE_HASH_STRING, 0); 329 + sqliteHashInit(&db->trigHash, SQLITE_HASH_STRING, 0); 330 + sqliteHashInit(&db->trigDrop, SQLITE_HASH_STRING, 0); 329 331 sqliteHashInit(&db->tblDrop, SQLITE_HASH_POINTER, 0); 330 332 sqliteHashInit(&db->idxDrop, SQLITE_HASH_POINTER, 0); 331 333 sqliteHashInit(&db->aFunc, SQLITE_HASH_STRING, 1); 332 334 sqliteRegisterBuildinFunctions(db); 333 335 db->onError = OE_Default; 334 336 db->priorNewRowid = 0; 335 337 db->magic = SQLITE_MAGIC_BUSY; ................................................................................ 379 381 ** This routine erases the stored schema. This erasure occurs because 380 382 ** either the database is being closed or because some other process 381 383 ** changed the schema and this process needs to reread it. 382 384 */ 383 385 static void clearHashTable(sqlite *db, int preserveTemps){ 384 386 HashElem *pElem; 385 387 Hash temp1; 388 + Hash temp2; 386 389 assert( sqliteHashFirst(&db->tblDrop)==0 ); /* There can not be uncommitted */ 387 390 assert( sqliteHashFirst(&db->idxDrop)==0 ); /* DROP TABLEs or DROP INDEXs */ 388 391 temp1 = db->tblHash; 389 - sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0); 392 + temp2 = db->trigHash; 393 + sqliteHashInit(&db->trigHash, SQLITE_HASH_STRING, 0); 390 394 sqliteHashClear(&db->idxHash); 395 + 396 + for (pElem=sqliteHashFirst(&temp2); pElem; pElem=sqliteHashNext(pElem)){ 397 + Trigger * pTrigger = sqliteHashData(pElem); 398 + Table *pTab = sqliteFindTable(db, pTrigger->table); 399 + assert(pTab); 400 + if (pTab->isTemp) { 401 + sqliteHashInsert(&db->trigHash, pTrigger->name, strlen(pTrigger->name), 402 + pTrigger); 403 + } else { 404 + sqliteDeleteTrigger(pTrigger); 405 + } 406 + } 407 + sqliteHashClear(&temp2); 408 + 409 + sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0); 410 + 391 411 for(pElem=sqliteHashFirst(&temp1); pElem; pElem=sqliteHashNext(pElem)){ 392 412 Table *pTab = sqliteHashData(pElem); 393 413 if( preserveTemps && pTab->isTemp ){ 394 414 Index *pIdx; 395 415 int nName = strlen(pTab->zName); 396 416 Table *pOld = sqliteHashInsert(&db->tblHash, pTab->zName, nName+1, pTab); 397 417 if( pOld!=0 ){ ................................................................................ 409 429 } 410 430 } 411 431 }else{ 412 432 sqliteDeleteTable(db, pTab); 413 433 } 414 434 } 415 435 sqliteHashClear(&temp1); 436 + 416 437 db->flags &= ~SQLITE_Initialized; 417 438 } 418 439 419 440 /* 420 441 ** Return the ROWID of the most recent insert 421 442 */ 422 443 int sqlite_last_insert_rowid(sqlite *db){ ................................................................................ 454 475 } 455 476 456 477 /* 457 478 ** Return TRUE if the given SQL string ends in a semicolon. 458 479 */ 459 480 int sqlite_complete(const char *zSql){ 460 481 int isComplete = 0; 482 + int seenCreate = 0; 461 483 while( *zSql ){ 462 484 switch( *zSql ){ 463 485 case ';': { 464 486 isComplete = 1; 465 487 break; 466 488 } 467 489 case ' ': ................................................................................ 497 519 break; 498 520 } 499 521 while( *zSql && *zSql!='\n' ){ zSql++; } 500 522 if( *zSql==0 ) return isComplete; 501 523 break; 502 524 } 503 525 default: { 526 + if (seenCreate && !sqliteStrNICmp(zSql, "trigger", 7)) 527 + while (sqliteStrNICmp(zSql, "end", 3)) 528 + if (!*++zSql) return 0; 529 + 530 + if (!sqliteStrNICmp(zSql, "create", 6)) { 531 + zSql = zSql + 5; 532 + seenCreate = 1; 533 + } else 534 + seenCreate = 0; 535 + 504 536 isComplete = 0; 505 537 break; 506 538 } 507 539 } 508 540 zSql++; 509 541 } 510 542 return isComplete;
Changes to src/parse.y.
10 10 ** 11 11 ************************************************************************* 12 12 ** This file contains SQLite's grammar for SQL. Process this file 13 13 ** using the lemon parser generator to generate C code that runs 14 14 ** the parser. Lemon will also generate a header file containing 15 15 ** numeric codes for all of the tokens. 16 16 ** 17 -** @(#) $Id: parse.y,v 1.63 2002/05/08 21:46:15 drh Exp $ 17 +** @(#) $Id: parse.y,v 1.64 2002/05/15 08:30:14 danielk1977 Exp $ 18 18 */ 19 19 %token_prefix TK_ 20 20 %token_type {Token} 21 21 %default_type {Token} 22 22 %extra_argument {Parse *pParse} 23 23 %syntax_error { 24 24 sqliteSetString(&pParse->zErrMsg,"syntax error",0); ................................................................................ 29 29 #include "sqliteInt.h" 30 30 #include "parse.h" 31 31 32 32 /* 33 33 ** A structure for holding two integers 34 34 */ 35 35 struct twoint { int a,b; }; 36 + 37 +/* 38 +** A structure for holding an integer and an IdList 39 +*/ 40 +struct int_idlist { int a; IdList * b; }; 36 41 } 37 42 38 43 // These are extra tokens used by the lexer but never seen by the 39 44 // parser. We put them in a rule so that the parser generator will 40 45 // add them to the parse.h output file. 41 46 // 42 47 %nonassoc END_OF_FILE ILLEGAL SPACE UNCLOSED_STRING COMMENT FUNCTION ................................................................................ 624 629 cmd ::= PRAGMA ids(X). {sqlitePragma(pParse,&X,&X,0);} 625 630 plus_num(A) ::= plus_opt number(X). {A = X;} 626 631 minus_num(A) ::= MINUS number(X). {A = X;} 627 632 number(A) ::= INTEGER(X). {A = X;} 628 633 number(A) ::= FLOAT(X). {A = X;} 629 634 plus_opt ::= PLUS. 630 635 plus_opt ::= . 636 + 637 +//////////////////////////// The CREATE TRIGGER command ///////////////////// 638 +cmd ::= CREATE(A) TRIGGER ids(B) trigger_time(C) trigger_event(D) ON ids(E) 639 + foreach_clause(F) when_clause(G) 640 + BEGIN trigger_cmd_list(S) END(Z). { 641 + sqliteCreateTrigger(pParse, &B, C, D.a, D.b, &E, F, G, S, 642 + A.z, (int)(Z.z - A.z) + Z.n ); 643 +} 644 + 645 +%type trigger_time {int} 646 +trigger_time(A) ::= BEFORE. { A = TK_BEFORE; } 647 +trigger_time(A) ::= AFTER. { A = TK_AFTER; } 648 +trigger_time(A) ::= INSTEAD OF. { A = TK_INSTEAD;} 649 +trigger_time(A) ::= . { A = TK_BEFORE; } 650 + 651 +%type trigger_event {struct int_idlist} 652 +trigger_event(A) ::= DELETE. { A.a = TK_DELETE; A.b = 0; } 653 +trigger_event(A) ::= INSERT. { A.a = TK_INSERT; A.b = 0; } 654 +trigger_event(A) ::= UPDATE. { A.a = TK_UPDATE; A.b = 0;} 655 +trigger_event(A) ::= UPDATE OF inscollist(X). {A.a = TK_UPDATE; A.b = X; } 656 + 657 +%type foreach_clause {int} 658 +foreach_clause(A) ::= . { A = TK_ROW; } 659 +foreach_clause(A) ::= FOR EACH ROW. { A = TK_ROW; } 660 +foreach_clause(A) ::= FOR EACH STATEMENT. { A = TK_STATEMENT; } 661 + 662 +%type when_clause {Expr *} 663 +when_clause(A) ::= . { A = 0; } 664 +when_clause(A) ::= WHEN expr(X). { A = X; } 665 + 666 +%type trigger_cmd_list {TriggerStep *} 667 +trigger_cmd_list(A) ::= trigger_cmd(X) SEMI trigger_cmd_list(Y). { 668 + X->pNext = Y ; A = X; } 669 +trigger_cmd_list(A) ::= . { A = 0; } 670 + 671 +%type trigger_cmd {TriggerStep *} 672 +// UPDATE 673 +trigger_cmd(A) ::= UPDATE orconf(R) ids(X) SET setlist(Y) where_opt(Z). 674 + { A = sqliteTriggerUpdateStep(&X, Y, Z, R); } 675 + 676 +// INSERT 677 +trigger_cmd(A) ::= INSERT orconf(R) INTO ids(X) inscollist_opt(F) 678 + VALUES LP itemlist(Y) RP. 679 +{A = sqliteTriggerInsertStep(&X, F, Y, 0, R);} 680 + 681 +trigger_cmd(A) ::= INSERT orconf(R) INTO ids(X) inscollist_opt(F) select(S). 682 + {A = sqliteTriggerInsertStep(&X, F, 0, S, R);} 683 + 684 +// DELETE 685 +trigger_cmd(A) ::= DELETE FROM ids(X) where_opt(Y). 686 + {A = sqliteTriggerDeleteStep(&X, Y);} 687 + 688 +// SELECT 689 +trigger_cmd(A) ::= select(X). {A = sqliteTriggerSelectStep(X); } 690 + 691 +//////////////////////// DROP TRIGGER statement ////////////////////////////// 692 +cmd ::= DROP TRIGGER ids(X). { 693 + sqliteDropTrigger(pParse,&X,0); 694 +} 695 +
Changes to src/sqliteInt.h.
7 7 ** May you do good and not evil. 8 8 ** May you find forgiveness for yourself and forgive others. 9 9 ** May you share freely, never taking more than you give. 10 10 ** 11 11 ************************************************************************* 12 12 ** Internal interface definitions for SQLite. 13 13 ** 14 -** @(#) $Id: sqliteInt.h,v 1.107 2002/05/10 13:14:07 drh Exp $ 14 +** @(#) $Id: sqliteInt.h,v 1.108 2002/05/15 08:30:14 danielk1977 Exp $ 15 15 */ 16 16 #include "sqlite.h" 17 17 #include "hash.h" 18 18 #include "vdbe.h" 19 19 #include "parse.h" 20 20 #include "btree.h" 21 21 #include <stdio.h> ................................................................................ 140 140 typedef struct Token Token; 141 141 typedef struct IdList IdList; 142 142 typedef struct WhereInfo WhereInfo; 143 143 typedef struct WhereLevel WhereLevel; 144 144 typedef struct Select Select; 145 145 typedef struct AggExpr AggExpr; 146 146 typedef struct FuncDef FuncDef; 147 +typedef struct Trigger Trigger; 148 +typedef struct TriggerStep TriggerStep; 149 +typedef struct TriggerStack TriggerStack; 147 150 148 151 /* 149 152 ** Each database is an instance of the following structure 150 153 */ 151 154 struct sqlite { 152 155 Btree *pBe; /* The B*Tree backend */ 153 156 Btree *pBeTemp; /* Backend for session temporary tables */ ................................................................................ 166 169 Hash aFunc; /* All functions that can be in SQL exprs */ 167 170 int lastRowid; /* ROWID of most recent insert */ 168 171 int priorNewRowid; /* Last randomly generated ROWID */ 169 172 int onError; /* Default conflict algorithm */ 170 173 int magic; /* Magic number for detect library misuse */ 171 174 int nChange; /* Number of rows changed */ 172 175 int recursionDepth; /* Number of nested calls to sqlite_exec() */ 176 + 177 + Hash trigHash; /* All triggers indexed by name */ 178 + Hash trigDrop; /* Uncommited dropped triggers */ 173 179 }; 174 180 175 181 /* 176 182 ** Possible values for the sqlite.flags. 177 183 */ 178 184 #define SQLITE_VdbeTrace 0x00000001 /* True to trace VDBE execution */ 179 185 #define SQLITE_Initialized 0x00000002 /* True after initialization */ ................................................................................ 266 272 Select *pSelect; /* NULL for tables. Points to definition if a view. */ 267 273 u8 readOnly; /* True if this table should not be written by the user */ 268 274 u8 isCommit; /* True if creation of this table has been committed */ 269 275 u8 isTemp; /* True if stored in db->pBeTemp instead of db->pBe */ 270 276 u8 isTransient; /* True if automatically deleted when VDBE finishes */ 271 277 u8 hasPrimKey; /* True if there exists a primary key */ 272 278 u8 keyConf; /* What to do in case of uniqueness conflict on iPKey */ 279 + 280 + Trigger *pTrigger; /* List of SQL triggers on this table */ 273 281 }; 274 282 275 283 /* 276 284 ** SQLite supports 5 different ways to resolve a contraint 277 285 ** error. ROLLBACK processing means that a constraint violation 278 286 ** causes the operation in proces to fail and for the current transaction 279 287 ** to be rolled back. ABORT processing means the operation in process ................................................................................ 546 554 int nSet; /* Number of sets used so far */ 547 555 int nAgg; /* Number of aggregate expressions */ 548 556 AggExpr *aAgg; /* An array of aggregate expressions */ 549 557 int useAgg; /* If true, extract field values from the aggregator 550 558 ** while generating expressions. Normally false */ 551 559 int schemaVerified; /* True if an OP_VerifySchema has been coded someplace 552 560 ** other than after an OP_Transaction */ 561 + 562 + TriggerStack * trigStack; 563 +}; 564 + 565 +struct TriggerStack { 566 + Trigger * pTrigger; 567 + Table * pTab; /* Table that triggers are currently being coded as */ 568 + int newIdx; /* Index of "new" temp table */ 569 + int oldIdx; /* Index of "old" temp table */ 570 + int orconf; /* Current orconf policy */ 571 + struct TriggerStack * pNext; 572 +}; 573 +struct TriggerStep { 574 + int op; /* One of TK_DELETE, TK_UPDATE, TK_INSERT, TK_SELECT */ 575 + int orconf; 576 + 577 + Select * pSelect; /* Valid for SELECT and sometimes 578 + INSERT steps (when pExprList == 0) */ 579 + Token target; /* Valid for DELETE, UPDATE, INSERT steps */ 580 + Expr * pWhere; /* Valid for DELETE, UPDATE steps */ 581 + ExprList * pExprList; /* Valid for UPDATE statements and sometimes 582 + INSERT steps (when pSelect == 0) */ 583 + IdList *pIdList; /* Valid for INSERT statements only */ 584 + 585 + TriggerStep * pNext; /* Next in the link-list */ 586 +}; 587 +struct Trigger { 588 + char * name; /* The name of the trigger */ 589 + char * table; /* The table or view to which the trigger applies */ 590 + int op; /* One of TK_DELETE, TK_UPDATE, TK_INSERT */ 591 + int tr_tm; /* One of TK_BEFORE, TK_AFTER, TK_INSTEAD */ 592 + Expr * pWhen; /* The WHEN clause of the expresion (may be NULL) */ 593 + IdList * pColumns; /* If this is an UPDATE OF <column-list> trigger, 594 + the column names are stored in this list */ 595 + int foreach; /* One of TK_ROW or TK_STATEMENT */ 596 + 597 + TriggerStep * step_list; /* Link list of trigger program steps */ 598 + 599 + char * strings; /* pointer to the allocation of Token strings */ 600 + Trigger * pNext; /* Next trigger associated with the table */ 601 + int isCommit; 553 602 }; 554 603 604 +TriggerStep * sqliteTriggerSelectStep(Select *); 605 +TriggerStep * sqliteTriggerInsertStep(Token *, IdList *, ExprList *, 606 + Select *, int); 607 +TriggerStep * sqliteTriggerUpdateStep(Token *, ExprList *, Expr *, int); 608 +TriggerStep * sqliteTriggerDeleteStep(Token *, Expr *); 609 + 610 +extern int always_code_trigger_setup; 611 + 612 +void sqliteCreateTrigger(Parse * ,Token *, int, int, IdList *, Token *, int, Expr *, TriggerStep *, char const *,int); 613 +void sqliteDropTrigger(Parse *, Token *, int); 614 +int sqliteTriggersExist( Parse * , Trigger * , int , int , int, ExprList * ); 615 +int sqliteCodeRowTrigger( Parse * pParse, int op, ExprList *, int tr_tm, Table * tbl, int newTable, int oldTable, int onError); 616 + 617 +void sqliteViewTriggers(Parse *, Table *, Expr *, int, ExprList *); 618 + 555 619 /* 556 620 ** Internal function prototypes 557 621 */ 558 622 int sqliteStrICmp(const char *, const char *); 559 623 int sqliteStrNICmp(const char *, const char *, int); 560 624 int sqliteHashNoCase(const char *, int); 561 625 int sqliteCompare(const char *, const char *); ................................................................................ 658 722 IdList *sqliteIdListDup(IdList*); 659 723 Select *sqliteSelectDup(Select*); 660 724 FuncDef *sqliteFindFunction(sqlite*,const char*,int,int,int); 661 725 void sqliteRegisterBuildinFunctions(sqlite*); 662 726 int sqliteSafetyOn(sqlite*); 663 727 int sqliteSafetyOff(sqlite*); 664 728 int sqliteSafetyCheck(sqlite*); 729 + 730 +void changeCookie(sqlite *);
Changes to src/tokenize.c.
11 11 ************************************************************************* 12 12 ** An tokenizer for SQL 13 13 ** 14 14 ** This file contains C code that splits an SQL input string up into 15 15 ** individual tokens and sends those tokens one-by-one over to the 16 16 ** parser for analysis. 17 17 ** 18 -** $Id: tokenize.c,v 1.40 2002/03/24 13:13:29 drh Exp $ 18 +** $Id: tokenize.c,v 1.41 2002/05/15 08:30:14 danielk1977 Exp $ 19 19 */ 20 20 #include "sqliteInt.h" 21 21 #include "os.h" 22 22 #include <ctype.h> 23 23 #include <stdlib.h> 24 24 25 25 /* ................................................................................ 35 35 }; 36 36 37 37 /* 38 38 ** These are the keywords 39 39 */ 40 40 static Keyword aKeywordTable[] = { 41 41 { "ABORT", 0, TK_ABORT, 0 }, 42 + { "AFTER", 0, TK_AFTER, 0 }, 42 43 { "ALL", 0, TK_ALL, 0 }, 43 44 { "AND", 0, TK_AND, 0 }, 44 45 { "AS", 0, TK_AS, 0 }, 45 46 { "ASC", 0, TK_ASC, 0 }, 47 + { "BEFORE", 0, TK_BEFORE, 0 }, 46 48 { "BEGIN", 0, TK_BEGIN, 0 }, 47 49 { "BETWEEN", 0, TK_BETWEEN, 0 }, 48 50 { "BY", 0, TK_BY, 0 }, 49 51 { "CASE", 0, TK_CASE, 0 }, 50 52 { "CHECK", 0, TK_CHECK, 0 }, 51 53 { "CLUSTER", 0, TK_CLUSTER, 0 }, 52 54 { "COMMIT", 0, TK_COMMIT, 0 }, ................................................................................ 57 59 { "DEFAULT", 0, TK_DEFAULT, 0 }, 58 60 { "DELETE", 0, TK_DELETE, 0 }, 59 61 { "DELIMITERS", 0, TK_DELIMITERS, 0 }, 60 62 { "DESC", 0, TK_DESC, 0 }, 61 63 { "DISTINCT", 0, TK_DISTINCT, 0 }, 62 64 { "DROP", 0, TK_DROP, 0 }, 63 65 { "END", 0, TK_END, 0 }, 66 + { "EACH", 0, TK_EACH, 0 }, 64 67 { "ELSE", 0, TK_ELSE, 0 }, 65 68 { "EXCEPT", 0, TK_EXCEPT, 0 }, 66 69 { "EXPLAIN", 0, TK_EXPLAIN, 0 }, 67 70 { "FAIL", 0, TK_FAIL, 0 }, 71 + { "FOR", 0, TK_FOR, 0 }, 68 72 { "FROM", 0, TK_FROM, 0 }, 69 73 { "GLOB", 0, TK_GLOB, 0 }, 70 74 { "GROUP", 0, TK_GROUP, 0 }, 71 75 { "HAVING", 0, TK_HAVING, 0 }, 72 76 { "IGNORE", 0, TK_IGNORE, 0 }, 73 77 { "IN", 0, TK_IN, 0 }, 74 78 { "INDEX", 0, TK_INDEX, 0 }, 75 79 { "INSERT", 0, TK_INSERT, 0 }, 80 + { "INSTEAD", 0, TK_INSTEAD, 0 }, 76 81 { "INTERSECT", 0, TK_INTERSECT, 0 }, 77 82 { "INTO", 0, TK_INTO, 0 }, 78 83 { "IS", 0, TK_IS, 0 }, 79 84 { "ISNULL", 0, TK_ISNULL, 0 }, 80 85 { "KEY", 0, TK_KEY, 0 }, 81 86 { "LIKE", 0, TK_LIKE, 0 }, 82 87 { "LIMIT", 0, TK_LIMIT, 0 }, 83 88 { "NOT", 0, TK_NOT, 0 }, 84 89 { "NOTNULL", 0, TK_NOTNULL, 0 }, 85 90 { "NULL", 0, TK_NULL, 0 }, 91 + { "OF", 0, TK_OF, 0 }, 86 92 { "OFFSET", 0, TK_OFFSET, 0 }, 87 93 { "ON", 0, TK_ON, 0 }, 88 94 { "OR", 0, TK_OR, 0 }, 89 95 { "ORDER", 0, TK_ORDER, 0 }, 90 96 { "PRAGMA", 0, TK_PRAGMA, 0 }, 91 97 { "PRIMARY", 0, TK_PRIMARY, 0 }, 92 98 { "REPLACE", 0, TK_REPLACE, 0 }, 93 99 { "ROLLBACK", 0, TK_ROLLBACK, 0 }, 100 + { "ROW", 0, TK_ROW, 0 }, 94 101 { "SELECT", 0, TK_SELECT, 0 }, 95 102 { "SET", 0, TK_SET, 0 }, 96 103 { "TABLE", 0, TK_TABLE, 0 }, 97 104 { "TEMP", 0, TK_TEMP, 0 }, 98 105 { "TEMPORARY", 0, TK_TEMP, 0 }, 99 106 { "THEN", 0, TK_THEN, 0 }, 100 107 { "TRANSACTION", 0, TK_TRANSACTION, 0 }, 108 + { "TRIGGER", 0, TK_TRIGGER, 0 }, 101 109 { "UNION", 0, TK_UNION, 0 }, 102 110 { "UNIQUE", 0, TK_UNIQUE, 0 }, 103 111 { "UPDATE", 0, TK_UPDATE, 0 }, 104 112 { "USING", 0, TK_USING, 0 }, 105 113 { "VACUUM", 0, TK_VACUUM, 0 }, 106 114 { "VALUES", 0, TK_VALUES, 0 }, 107 115 { "VIEW", 0, TK_VIEW, 0 },
Added src/trigger.c.
1 +/* 2 + * All copyright on this work is disclaimed by the author. 3 + * 4 + */ 5 + 6 +#include "sqliteInt.h" 7 +/* 8 + * This is called by the parser when it sees a CREATE TRIGGER statement 9 + */ 10 +void 11 +sqliteCreateTrigger( 12 + Parse * pParse, /* The parse context of the CREATE TRIGGER statement */ 13 + Token * nm, /* The name of the trigger */ 14 + int tr_tm, /* One of TK_BEFORE, TK_AFTER */ 15 + int op, /* One of TK_INSERT, TK_UPDATE, TK_DELETE */ 16 + IdList * cols, /* column list if this is an UPDATE OF trigger */ 17 + Token * tbl, /* The name of the table/view the trigger applies to */ 18 + int foreach, /* One of TK_ROW or TK_STATEMENT */ 19 + Expr * pWhen, /* WHEN clause */ 20 + TriggerStep * steps, /* The triggered program */ 21 + char const * cc, int len) /* The string data to make persistent */ 22 +{ 23 + Trigger * nt; 24 + Table * tab; 25 + int offset; 26 + TriggerStep * ss; 27 + 28 + /* Check that: 29 + 1. the trigger name does not already exist. 30 + 2. the table (or view) does exist. 31 + */ 32 + { 33 + char * tmp_str = sqliteStrNDup(nm->z, nm->n); 34 + if (sqliteHashFind(&(pParse->db->trigHash), tmp_str, nm->n + 1)) { 35 + sqliteSetNString(&pParse->zErrMsg, "trigger ", -1, 36 + nm->z, nm->n, " already exists", -1, 0); 37 + sqliteFree(tmp_str); 38 + pParse->nErr++; 39 + goto trigger_cleanup; 40 + } 41 + sqliteFree(tmp_str); 42 + } 43 + { 44 + char * tmp_str = sqliteStrNDup(tbl->z, tbl->n); 45 + tab = sqliteFindTable(pParse->db, tmp_str); 46 + sqliteFree(tmp_str); 47 + if (!tab) { 48 + sqliteSetNString(&pParse->zErrMsg, "no such table: ", -1, 49 + tbl->z, tbl->n, 0); 50 + pParse->nErr++; 51 + goto trigger_cleanup; 52 + } 53 + } 54 + 55 + /* Build the Trigger object */ 56 + nt = (Trigger *)sqliteMalloc(sizeof(Trigger)); 57 + 58 + nt->name = sqliteStrNDup(nm->z, nm->n); 59 + nt->table = sqliteStrNDup(tbl->z, tbl->n); 60 + nt->op = op; 61 + nt->tr_tm = tr_tm; 62 + nt->pWhen = pWhen; 63 + nt->pColumns = cols; 64 + nt->foreach = foreach; 65 + nt->step_list = steps; 66 + nt->isCommit = 0; 67 + 68 + nt->strings = sqliteStrNDup(cc, len); 69 + offset = (int)(nt->strings - cc); 70 + 71 + sqliteExprMoveStrings(nt->pWhen, offset); 72 + 73 + ss = nt->step_list; 74 + while (ss) { 75 + sqliteSelectMoveStrings(ss->pSelect, offset); 76 + if (ss->target.z) ss->target.z += offset; 77 + sqliteExprMoveStrings(ss->pWhere, offset); 78 + sqliteExprListMoveStrings(ss->pExprList, offset); 79 + 80 + ss = ss->pNext; 81 + } 82 + 83 + /* if we are not initializing, and this trigger is not on a TEMP table, 84 + build the sqlite_master entry */ 85 + if (!pParse->initFlag && !tab->isTemp) { 86 + 87 + /* Make an entry in the sqlite_master table */ 88 + sqliteBeginWriteOperation(pParse); 89 + 90 + sqliteVdbeAddOp(pParse->pVdbe, OP_OpenWrite, 0, 2); 91 + sqliteVdbeChangeP3(pParse->pVdbe, -1, MASTER_NAME, P3_STATIC); 92 + sqliteVdbeAddOp(pParse->pVdbe, OP_NewRecno, 0, 0); 93 + sqliteVdbeAddOp(pParse->pVdbe, OP_String, 0, 0); 94 + sqliteVdbeChangeP3(pParse->pVdbe, -1, "trigger", P3_STATIC); 95 + sqliteVdbeAddOp(pParse->pVdbe, OP_String, 0, 0); 96 + sqliteVdbeChangeP3(pParse->pVdbe, -1, nt->name, 0); 97 + sqliteVdbeAddOp(pParse->pVdbe, OP_String, 0, 0); 98 + sqliteVdbeChangeP3(pParse->pVdbe, -1, nt->table, 0); 99 + sqliteVdbeAddOp(pParse->pVdbe, OP_Integer, 0, 0); 100 + sqliteVdbeAddOp(pParse->pVdbe, OP_String, 0, 0); 101 + sqliteVdbeChangeP3(pParse->pVdbe, -1, nt->strings, 0); 102 + sqliteVdbeAddOp(pParse->pVdbe, OP_MakeRecord, 5, 0); 103 + sqliteVdbeAddOp(pParse->pVdbe, OP_PutIntKey, 0, 1); 104 + 105 + /* Change the cookie, since the schema is changed */ 106 + changeCookie(pParse->db); 107 + sqliteVdbeAddOp(pParse->pVdbe, OP_Integer, pParse->db->next_cookie, 0); 108 + sqliteVdbeAddOp(pParse->pVdbe, OP_SetCookie, 0, 0); 109 + 110 + sqliteVdbeAddOp(pParse->pVdbe, OP_Close, 0, 0); 111 + 112 + sqliteEndWriteOperation(pParse); 113 + } 114 + 115 + if (!pParse->explain) { 116 + /* Stick it in the hash-table */ 117 + sqliteHashInsert(&(pParse->db->trigHash), nt->name, nm->n + 1, nt); 118 + 119 + /* Attach it to the table object */ 120 + nt->pNext = tab->pTrigger; 121 + tab->pTrigger = nt; 122 + return; 123 + } else { 124 + sqliteFree(nt->strings); 125 + sqliteFree(nt->name); 126 + sqliteFree(nt->table); 127 + sqliteFree(nt); 128 + } 129 + 130 +trigger_cleanup: 131 + 132 + sqliteIdListDelete(cols); 133 + sqliteExprDelete(pWhen); 134 + { 135 + TriggerStep * pp; 136 + TriggerStep * nn; 137 + 138 + pp = steps; 139 + while (pp) { 140 + nn = pp->pNext; 141 + sqliteExprDelete(pp->pWhere); 142 + sqliteExprListDelete(pp->pExprList); 143 + sqliteSelectDelete(pp->pSelect); 144 + sqliteIdListDelete(pp->pIdList); 145 + sqliteFree(pp); 146 + pp = nn; 147 + } 148 + } 149 +} 150 + 151 + TriggerStep * 152 +sqliteTriggerSelectStep(Select * s) 153 +{ 154 + TriggerStep * tt = sqliteMalloc(sizeof(TriggerStep)); 155 + 156 + tt->op = TK_SELECT; 157 + tt->pSelect = s; 158 + tt->orconf = OE_Default; 159 + 160 + return tt; 161 +} 162 + 163 +TriggerStep * 164 +sqliteTriggerInsertStep(Token * tbl, IdList * col, ExprList * val, Select * s, int orconf) 165 +{ 166 + TriggerStep * tt = sqliteMalloc(sizeof(TriggerStep)); 167 + 168 + assert(val == 0 || s == 0); 169 + assert(val != 0 || s != 0); 170 + 171 + tt->op = TK_INSERT; 172 + tt->pSelect = s; 173 + tt->target = *tbl; 174 + tt->pIdList = col; 175 + tt->pExprList = val; 176 + tt->orconf = orconf; 177 + 178 + return tt; 179 +} 180 + 181 +TriggerStep * 182 +sqliteTriggerUpdateStep(Token * tbl, ExprList * val, Expr * w, int orconf) 183 +{ 184 + TriggerStep * tt = sqliteMalloc(sizeof(TriggerStep)); 185 + 186 + tt->op = TK_UPDATE; 187 + tt->target = *tbl; 188 + tt->pExprList = val; 189 + tt->pWhere = w; 190 + tt->orconf = orconf; 191 + 192 + return tt; 193 +} 194 + 195 +TriggerStep * 196 +sqliteTriggerDeleteStep(Token * tbl, Expr * w) 197 +{ 198 + TriggerStep * tt = sqliteMalloc(sizeof(TriggerStep)); 199 + 200 + tt->op = TK_DELETE; 201 + tt->target = *tbl; 202 + tt->pWhere = w; 203 + tt->orconf = OE_Default; 204 + 205 + return tt; 206 +} 207 + 208 + 209 +/* This does a recursive delete of the trigger structure */ 210 +void sqliteDeleteTrigger(Trigger * tt) 211 +{ 212 + TriggerStep * ts, * tc; 213 + ts = tt->step_list; 214 + 215 + while (ts) { 216 + tc = ts; 217 + ts = ts->pNext; 218 + 219 + sqliteExprDelete(tc->pWhere); 220 + sqliteExprListDelete(tc->pExprList); 221 + sqliteSelectDelete(tc->pSelect); 222 + sqliteIdListDelete(tc->pIdList); 223 + 224 + sqliteFree(tc); 225 + } 226 + 227 + sqliteFree(tt->name); 228 + sqliteFree(tt->table); 229 + sqliteExprDelete(tt->pWhen); 230 + sqliteIdListDelete(tt->pColumns); 231 + sqliteFree(tt->strings); 232 + sqliteFree(tt); 233 +} 234 + 235 +/* 236 + * "nested" is true if this is begin called as the result of a DROP TABLE 237 + */ 238 +void sqliteDropTrigger(Parse *pParse, Token * trigname, int nested) 239 +{ 240 + char * tmp_name; 241 + Trigger * trig; 242 + Table * tbl; 243 + 244 + tmp_name = sqliteStrNDup(trigname->z, trigname->n); 245 + 246 + /* ensure that the trigger being dropped exists */ 247 + trig = sqliteHashFind(&(pParse->db->trigHash), tmp_name, trigname->n + 1); 248 + if (!trig) { 249 + sqliteSetNString(&pParse->zErrMsg, "no such trigger: ", -1, 250 + tmp_name, -1, 0); 251 + sqliteFree(tmp_name); 252 + return; 253 + } 254 + 255 + /* 256 + * If this is not an "explain", do the following: 257 + * 1. Remove the trigger from its associated table structure 258 + * 2. Move the trigger from the trigHash hash to trigDrop 259 + */ 260 + if (!pParse->explain) { 261 + /* 1 */ 262 + tbl = sqliteFindTable(pParse->db, trig->table); 263 + assert(tbl); 264 + if (tbl->pTrigger == trig) 265 + tbl->pTrigger = trig->pNext; 266 + else { 267 + Trigger * cc = tbl->pTrigger; 268 + while (cc) { 269 + if (cc->pNext == trig) { 270 + cc->pNext = cc->pNext->pNext; 271 + break; 272 + } 273 + cc = cc->pNext; 274 + } 275 + assert(cc); 276 + } 277 + 278 + /* 2 */ 279 + sqliteHashInsert(&(pParse->db->trigHash), tmp_name, 280 + trigname->n + 1, NULL); 281 + sqliteHashInsert(&(pParse->db->trigDrop), trig->name, 282 + trigname->n + 1, trig); 283 + } 284 + 285 + /* Unless this is a trigger on a TEMP TABLE, generate code to destroy the 286 + * database record of the trigger */ 287 + if (!tbl->isTemp) { 288 + int base; 289 + static VdbeOp dropTrigger[] = { 290 + { OP_OpenWrite, 0, 2, MASTER_NAME}, 291 + { OP_Rewind, 0, ADDR(9), 0}, 292 + { OP_String, 0, 0, 0}, /* 2 */ 293 + { OP_MemStore, 1, 1, 0}, 294 + { OP_MemLoad, 1, 0, 0}, /* 4 */ 295 + { OP_Column, 0, 1, 0}, 296 + { OP_Ne, 0, ADDR(8), 0}, 297 + { OP_Delete, 0, 0, 0}, 298 + { OP_Next, 0, ADDR(4), 0}, /* 8 */ 299 + { OP_Integer, 0, 0, 0}, /* 9 */ 300 + { OP_SetCookie, 0, 0, 0}, 301 + { OP_Close, 0, 0, 0}, 302 + }; 303 + 304 + if (!nested) 305 + sqliteBeginWriteOperation(pParse); 306 + 307 + base = sqliteVdbeAddOpList(pParse->pVdbe, 308 + ArraySize(dropTrigger), dropTrigger); 309 + sqliteVdbeChangeP3(pParse->pVdbe, base+2, tmp_name, 0); 310 + 311 + if (!nested) 312 + changeCookie(pParse->db); 313 + 314 + sqliteVdbeChangeP1(pParse->pVdbe, base+9, pParse->db->next_cookie); 315 + 316 + if (!nested) 317 + sqliteEndWriteOperation(pParse); 318 + } 319 + 320 + sqliteFree(tmp_name); 321 +} 322 + 323 +static int checkColumnOverLap(IdList * ii, ExprList * ee) 324 +{ 325 + int i, e; 326 + if (!ii) return 1; 327 + if (!ee) return 1; 328 + 329 + for (i = 0; i < ii->nId; i++) 330 + for (e = 0; e < ee->nExpr; e++) 331 + if (!sqliteStrICmp(ii->a[i].zName, ee->a[e].zName)) 332 + return 1; 333 + 334 + return 0; 335 +} 336 + 337 +/* A global variable that is TRUE if we should always set up temp tables for 338 + * for triggers, even if there are no triggers to code. This is used to test 339 + * how much overhead the triggers algorithm is causing. 340 + * 341 + * This flag can be set or cleared using the "trigger_overhead_test" pragma. 342 + * The pragma is not documented since it is not really part of the interface 343 + * to SQLite, just the test procedure. 344 +*/ 345 +int always_code_trigger_setup = 0; 346 + 347 +/* 348 + * Returns true if a trigger matching op, tr_tm and foreach that is NOT already 349 + * on the Parse objects trigger-stack (to prevent recursive trigger firing) is 350 + * found in the list specified as pTrigger. 351 + */ 352 +int sqliteTriggersExist( 353 + Parse * pParse, 354 + Trigger * pTrigger, 355 + int op, /* one of TK_DELETE, TK_INSERT, TK_UPDATE */ 356 + int tr_tm, /* one of TK_BEFORE, TK_AFTER */ 357 + int foreach, /* one of TK_ROW or TK_STATEMENT */ 358 + ExprList * pChanges) 359 +{ 360 + Trigger * tt; 361 + 362 + if (always_code_trigger_setup) return 1; 363 + 364 + tt = pTrigger; 365 + while (tt) { 366 + if (tt->op == op && tt->tr_tm == tr_tm && tt->foreach == foreach && 367 + checkColumnOverLap(tt->pColumns, pChanges)) { 368 + TriggerStack * ss; 369 + ss = pParse->trigStack; 370 + while (ss && ss->pTrigger != pTrigger) ss = ss->pNext; 371 + if (!ss) return 1; 372 + } 373 + tt = tt->pNext; 374 + } 375 + 376 + return 0; 377 +} 378 + 379 +static int codeTriggerProgram( 380 + Parse *pParse, 381 + TriggerStep * program, 382 + int onError) 383 +{ 384 + TriggerStep * step = program; 385 + int orconf; 386 + 387 + while (step) { 388 + int saveNTab = pParse->nTab; 389 + orconf = (onError == OE_Default)?step->orconf:onError; 390 + pParse->trigStack->orconf = orconf; 391 + switch(step->op) { 392 + case TK_SELECT: { 393 + int tmp_tbl = pParse->nTab++; 394 + sqliteVdbeAddOp(pParse->pVdbe, OP_OpenTemp, tmp_tbl, 0); 395 + sqliteVdbeAddOp(pParse->pVdbe, OP_KeyAsData, tmp_tbl, 1); 396 + sqliteSelect(pParse, step->pSelect, 397 + SRT_Union, tmp_tbl, 0, 0, 0); 398 + sqliteVdbeAddOp(pParse->pVdbe, OP_Close, tmp_tbl, 0); 399 + pParse->nTab--; 400 + break; 401 + } 402 + case TK_UPDATE: { 403 + sqliteVdbeAddOp(pParse->pVdbe, OP_PushList, 0, 0); 404 + sqliteUpdate(pParse, &step->target, 405 + sqliteExprListDup(step->pExprList), 406 + sqliteExprDup(step->pWhere), orconf); 407 + sqliteVdbeAddOp(pParse->pVdbe, OP_PopList, 0, 0); 408 + break; 409 + } 410 + case TK_INSERT: { 411 + sqliteInsert(pParse, &step->target, 412 + sqliteExprListDup(step->pExprList), 413 + sqliteSelectDup(step->pSelect), 414 + sqliteIdListDup(step->pIdList), orconf); 415 + break; 416 + } 417 + case TK_DELETE: { 418 + sqliteVdbeAddOp(pParse->pVdbe, OP_PushList, 0, 0); 419 + sqliteDeleteFrom(pParse, &step->target, 420 + sqliteExprDup(step->pWhere) 421 + ); 422 + sqliteVdbeAddOp(pParse->pVdbe, OP_PopList, 0, 0); 423 + break; 424 + } 425 + default: 426 + assert(0); 427 + } 428 + pParse->nTab = saveNTab; 429 + step = step->pNext; 430 + } 431 + 432 + return 0; 433 +} 434 + 435 +int sqliteCodeRowTrigger( 436 + Parse * pParse, /* Parse context */ 437 + int op, /* One of TK_UPDATE, TK_INSERT, TK_DELETE */ 438 + ExprList * changes, /* Changes list for any UPDATE OF triggers */ 439 + int tr_tm, /* One of TK_BEFORE, TK_AFTER */ 440 + Table * tbl, /* The table to code triggers from */ 441 + int newTable, /* The indice of the "new" row to access */ 442 + int oldTable, /* The indice of the "old" row to access */ 443 + int onError) /* ON CONFLICT policy */ 444 +{ 445 + Trigger * pTrigger; 446 + TriggerStack * pTriggerStack; 447 + 448 + 449 + assert(op == TK_UPDATE || op == TK_INSERT || op == TK_DELETE); 450 + assert(tr_tm == TK_BEFORE || tr_tm == TK_AFTER); 451 + 452 + assert(newTable != -1 || oldTable != -1); 453 + 454 + pTrigger = tbl->pTrigger; 455 + while (pTrigger) { 456 + int fire_this = 0; 457 + 458 + /* determine whether we should code this trigger */ 459 + if (pTrigger->op == op && pTrigger->tr_tm == tr_tm && 460 + pTrigger->foreach == TK_ROW) { 461 + fire_this = 1; 462 + pTriggerStack = pParse->trigStack; 463 + while (pTriggerStack) { 464 + if (pTriggerStack->pTrigger == pTrigger) fire_this = 0; 465 + pTriggerStack = pTriggerStack->pNext; 466 + } 467 + if (op == TK_UPDATE && pTrigger->pColumns && 468 + !checkColumnOverLap(pTrigger->pColumns, changes)) 469 + fire_this = 0; 470 + } 471 + 472 + if (fire_this) { 473 + int endTrigger; 474 + IdList dummyTablist; 475 + Expr * whenExpr; 476 + 477 + dummyTablist.nId = 0; 478 + dummyTablist.a = 0; 479 + 480 + /* Push an entry on to the trigger stack */ 481 + pTriggerStack = sqliteMalloc(sizeof(TriggerStack)); 482 + pTriggerStack->pTrigger = pTrigger; 483 + pTriggerStack->newIdx = newTable; 484 + pTriggerStack->oldIdx = oldTable; 485 + pTriggerStack->pTab = tbl; 486 + pTriggerStack->pNext = pParse->trigStack; 487 + pParse->trigStack = pTriggerStack; 488 + 489 + /* code the WHEN clause */ 490 + endTrigger = sqliteVdbeMakeLabel(pParse->pVdbe); 491 + whenExpr = sqliteExprDup(pTrigger->pWhen); 492 + if (sqliteExprResolveIds(pParse, 0, &dummyTablist, 0, whenExpr)) { 493 + pParse->trigStack = pParse->trigStack->pNext; 494 + sqliteFree(pTriggerStack); 495 + sqliteExprDelete(whenExpr); 496 + return 1; 497 + } 498 + sqliteExprIfFalse(pParse, whenExpr, endTrigger); 499 + sqliteExprDelete(whenExpr); 500 + 501 + codeTriggerProgram(pParse, pTrigger->step_list, onError); 502 + 503 + /* Pop the entry off the trigger stack */ 504 + pParse->trigStack = pParse->trigStack->pNext; 505 + sqliteFree(pTriggerStack); 506 + 507 + sqliteVdbeResolveLabel(pParse->pVdbe, endTrigger); 508 + } 509 + pTrigger = pTrigger->pNext; 510 + } 511 + 512 + return 0; 513 +} 514 + 515 +/* 516 + * Handle UPDATE and DELETE triggers on views 517 + */ 518 +void sqliteViewTriggers(Parse *pParse, Table *pTab, 519 + Expr * pWhere, int onError, ExprList * pChanges) 520 +{ 521 + int oldIdx = -1; 522 + int newIdx = -1; 523 + int *aXRef = 0; 524 + Vdbe *v; 525 + int endOfLoop; 526 + int startOfLoop; 527 + Select theSelect; 528 + Token tblNameToken; 529 + 530 + assert(pTab->pSelect); 531 + 532 + tblNameToken.z = pTab->zName; 533 + tblNameToken.n = strlen(pTab->zName); 534 + 535 + theSelect.isDistinct = 0; 536 + theSelect.pEList = sqliteExprListAppend(0, sqliteExpr(TK_ALL, 0, 0, 0), 0); 537 + theSelect.pSrc = sqliteIdListAppend(0, &tblNameToken); 538 + theSelect.pWhere = pWhere; pWhere = 0; 539 + theSelect.pGroupBy = 0; 540 + theSelect.pHaving = 0; 541 + theSelect.pOrderBy = 0; 542 + theSelect.op = TK_SELECT; /* ?? */ 543 + theSelect.pPrior = 0; 544 + theSelect.nLimit = -1; 545 + theSelect.nOffset = -1; 546 + theSelect.zSelect = 0; 547 + theSelect.base = 0; 548 + 549 + v = sqliteGetVdbe(pParse); 550 + assert(v); 551 + sqliteBeginMultiWriteOperation(pParse); 552 + 553 + /* Allocate temp tables */ 554 + oldIdx = pParse->nTab++; 555 + sqliteVdbeAddOp(v, OP_OpenTemp, oldIdx, 0); 556 + if (pChanges) { 557 + newIdx = pParse->nTab++; 558 + sqliteVdbeAddOp(v, OP_OpenTemp, newIdx, 0); 559 + } 560 + 561 + /* Snapshot the view */ 562 + if (sqliteSelect(pParse, &theSelect, SRT_Table, oldIdx, 0, 0, 0)) { 563 + goto trigger_cleanup; 564 + } 565 + 566 + /* loop thru the view snapshot, executing triggers for each row */ 567 + endOfLoop = sqliteVdbeMakeLabel(v); 568 + sqliteVdbeAddOp(v, OP_Rewind, oldIdx, endOfLoop); 569 + 570 + /* Loop thru the view snapshot, executing triggers for each row */ 571 + startOfLoop = sqliteVdbeCurrentAddr(v); 572 + 573 + /* Build the updated row if required */ 574 + if (pChanges) { 575 + int ii, jj; 576 + 577 + aXRef = sqliteMalloc( sizeof(int) * pTab->nCol ); 578 + if( aXRef==0 ) goto trigger_cleanup; 579 + for (ii = 0; ii < pTab->nCol; ii++) 580 + aXRef[ii] = -1; 581 + 582 + for(ii=0; ii<pChanges->nExpr; ii++){ 583 + int jj; 584 + if( sqliteExprResolveIds(pParse, oldIdx, theSelect.pSrc , 0, 585 + pChanges->a[ii].pExpr) ) 586 + goto trigger_cleanup; 587 + 588 + if( sqliteExprCheck(pParse, pChanges->a[ii].pExpr, 0, 0) ) 589 + goto trigger_cleanup; 590 + 591 + for(jj=0; jj<pTab->nCol; jj++){ 592 + if( sqliteStrICmp(pTab->aCol[jj].zName, pChanges->a[ii].zName)==0 ){ 593 + aXRef[jj] = ii; 594 + break; 595 + } 596 + } 597 + if( jj>=pTab->nCol ){ 598 + sqliteSetString(&pParse->zErrMsg, "no such column: ", 599 + pChanges->a[ii].zName, 0); 600 + pParse->nErr++; 601 + goto trigger_cleanup; 602 + } 603 + } 604 + 605 + sqliteVdbeAddOp(v, OP_Integer, 13, 0); 606 + 607 + for (ii = 0; ii < pTab->nCol; ii++) 608 + if( aXRef[ii] < 0 ) 609 + sqliteVdbeAddOp(v, OP_Column, oldIdx, ii); 610 + else 611 + sqliteExprCode(pParse, pChanges->a[aXRef[ii]].pExpr); 612 + 613 + sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0); 614 + sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0); 615 + sqliteVdbeAddOp(v, OP_Rewind, newIdx, 0); 616 + 617 + sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_BEFORE, 618 + pTab, newIdx, oldIdx, onError); 619 + sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_AFTER, 620 + pTab, newIdx, oldIdx, onError); 621 + } else { 622 + sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1, oldIdx, 623 + onError); 624 + sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1, oldIdx, 625 + onError); 626 + } 627 + 628 + sqliteVdbeAddOp(v, OP_Next, oldIdx, startOfLoop); 629 + 630 + sqliteVdbeResolveLabel(v, endOfLoop); 631 + sqliteEndWriteOperation(pParse); 632 + 633 +trigger_cleanup: 634 + sqliteFree(aXRef); 635 + sqliteExprListDelete(pChanges); 636 + sqliteExprDelete(pWhere); 637 + sqliteExprListDelete(theSelect.pEList); 638 + sqliteIdListDelete(theSelect.pSrc); 639 + sqliteExprDelete(theSelect.pWhere); 640 + return; 641 +} 642 + 643 +
Changes to src/update.c.
8 8 ** May you find forgiveness for yourself and forgive others. 9 9 ** May you share freely, never taking more than you give. 10 10 ** 11 11 ************************************************************************* 12 12 ** This file contains C code routines that are called by the parser 13 13 ** to handle UPDATE statements. 14 14 ** 15 -** $Id: update.c,v 1.36 2002/03/03 18:59:41 drh Exp $ 15 +** $Id: update.c,v 1.37 2002/05/15 08:30:14 danielk1977 Exp $ 16 16 */ 17 17 #include "sqliteInt.h" 18 18 19 19 /* 20 20 ** Process an UPDATE statement. 21 21 */ 22 22 void sqliteUpdate( ................................................................................ 43 43 ** an expression for the i-th column of the table. 44 44 ** aXRef[i]==-1 if the i-th column is not changed. */ 45 45 int openOp; /* Opcode used to open tables */ 46 46 int chngRecno; /* True if the record number is being changed */ 47 47 Expr *pRecnoExpr; /* Expression defining the new record number */ 48 48 int openAll; /* True if all indices need to be opened */ 49 49 50 + int row_triggers_exist = 0; 51 + 52 + int newIdx = -1; /* index of trigger "new" temp table */ 53 + int oldIdx = -1; /* index of trigger "old" temp table */ 54 + 50 55 if( pParse->nErr || sqlite_malloc_failed ) goto update_cleanup; 51 56 db = pParse->db; 57 + 58 + /* Check for the special case of a VIEW with one or more ON UPDATE triggers 59 + * defined 60 + */ 61 + { 62 + char * zTab = sqliteTableNameFromToken(pTableName); 63 + 64 + if(zTab != 0) { 65 + pTab = sqliteFindTable(pParse->db, zTab); 66 + if (pTab) { 67 + row_triggers_exist = 68 + sqliteTriggersExist(pParse, pTab->pTrigger, 69 + TK_UPDATE, TK_BEFORE, TK_ROW, pChanges) || 70 + sqliteTriggersExist(pParse, pTab->pTrigger, 71 + TK_UPDATE, TK_AFTER, TK_ROW, pChanges); 72 + } 73 + sqliteFree(zTab); 74 + if (row_triggers_exist && pTab->pSelect ) { 75 + /* Just fire VIEW triggers */ 76 + sqliteViewTriggers(pParse, pTab, pWhere, onError, pChanges); 77 + return; 78 + } 79 + } 80 + } 52 81 53 82 /* Locate the table which we want to update. This table has to be 54 83 ** put in an IdList structure because some of the subroutines we 55 84 ** will be calling are designed to work with multiple tables and expect 56 85 ** an IdList* parameter instead of just a Table* parameter. 57 86 */ 58 87 pTabList = sqliteTableTokenToIdList(pParse, pTableName); 59 88 if( pTabList==0 ) goto update_cleanup; 60 89 pTab = pTabList->a[0].pTab; 61 90 assert( pTab->pSelect==0 ); /* This table is not a VIEW */ 62 91 aXRef = sqliteMalloc( sizeof(int) * pTab->nCol ); 63 92 if( aXRef==0 ) goto update_cleanup; 64 93 for(i=0; i<pTab->nCol; i++) aXRef[i] = -1; 94 + 95 + if (row_triggers_exist) { 96 + newIdx = pParse->nTab++; 97 + oldIdx = pParse->nTab++; 98 + } 65 99 66 100 /* Resolve the column names in all the expressions in both the 67 101 ** WHERE clause and in the new values. Also find the column index 68 102 ** for each column to be updated in the pChanges array. 69 103 */ 70 104 base = pParse->nTab++; 71 105 if( pWhere ){ ................................................................................ 155 189 156 190 /* End the database scan loop. 157 191 */ 158 192 sqliteWhereEnd(pWInfo); 159 193 160 194 /* Initialize the count of updated rows 161 195 */ 162 - if( db->flags & SQLITE_CountRows ){ 196 + if( db->flags & SQLITE_CountRows && !pParse->trigStack ){ 163 197 sqliteVdbeAddOp(v, OP_Integer, 0, 0); 164 198 } 199 + 200 + if (row_triggers_exist) { 201 + int ii; 202 + 203 + sqliteVdbeAddOp(v, OP_OpenTemp, oldIdx, 0); 204 + sqliteVdbeAddOp(v, OP_OpenTemp, newIdx, 0); 205 + 206 + sqliteVdbeAddOp(v, OP_ListRewind, 0, 0); 207 + addr = sqliteVdbeAddOp(v, OP_ListRead, 0, 0); 208 + sqliteVdbeAddOp(v, OP_Dup, 0, 0); 209 + 210 + sqliteVdbeAddOp(v, OP_Dup, 0, 0); 211 + sqliteVdbeAddOp(v, OP_Open, base, pTab->tnum); 212 + sqliteVdbeAddOp(v, OP_MoveTo, base, 0); 213 + 214 + sqliteVdbeAddOp(v, OP_Integer, 13, 0); 215 + for (ii = 0; ii < pTab->nCol; ii++) { 216 + if (ii == pTab->iPKey) 217 + sqliteVdbeAddOp(v, OP_Recno, base, 0); 218 + else 219 + sqliteVdbeAddOp(v, OP_Column, base, ii); 220 + } 221 + sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0); 222 + sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0); 223 + 224 + sqliteVdbeAddOp(v, OP_Integer, 13, 0); 225 + for (ii = 0; ii < pTab->nCol; ii++){ 226 + if( aXRef[ii] < 0 ){ 227 + if (ii == pTab->iPKey) 228 + sqliteVdbeAddOp(v, OP_Recno, base, 0); 229 + else 230 + sqliteVdbeAddOp(v, OP_Column, base, ii); 231 + }else{ 232 + sqliteExprCode(pParse, pChanges->a[aXRef[ii]].pExpr); 233 + } 234 + } 235 + sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0); 236 + sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0); 237 + sqliteVdbeAddOp(v, OP_Close, base, 0); 238 + 239 + sqliteVdbeAddOp(v, OP_Rewind, oldIdx, 0); 240 + sqliteVdbeAddOp(v, OP_Rewind, newIdx, 0); 241 + 242 + if (sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_BEFORE, pTab, 243 + newIdx, oldIdx, onError)) goto update_cleanup; 244 + } 165 245 166 246 /* Rewind the list of records that need to be updated and 167 247 ** open every index that needs updating. Note that if any 168 248 ** index could potentially invoke a REPLACE conflict resolution 169 249 ** action, then we need to open all indices because we might need 170 250 ** to be deleting some records. 171 251 */ 172 - sqliteVdbeAddOp(v, OP_ListRewind, 0, 0); 173 252 openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite; 174 253 sqliteVdbeAddOp(v, openOp, base, pTab->tnum); 175 254 if( onError==OE_Replace ){ 176 255 openAll = 1; 177 256 }else{ 178 257 openAll = 0; 179 258 for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ ................................................................................ 193 272 194 273 /* Loop over every record that needs updating. We have to load 195 274 ** the old data for each record to be updated because some columns 196 275 ** might not change and we will need to copy the old value. 197 276 ** Also, the old data is needed to delete the old index entires. 198 277 ** So make the cursor point at the old record. 199 278 */ 200 - addr = sqliteVdbeAddOp(v, OP_ListRead, 0, 0); 201 - sqliteVdbeAddOp(v, OP_Dup, 0, 0); 279 + if (!row_triggers_exist) { 280 + int ii; 281 + sqliteVdbeAddOp(v, OP_ListRewind, 0, 0); 282 + addr = sqliteVdbeAddOp(v, OP_ListRead, 0, 0); 283 + sqliteVdbeAddOp(v, OP_Dup, 0, 0); 284 + } 202 285 sqliteVdbeAddOp(v, OP_MoveTo, base, 0); 203 286 204 287 /* If the record number will change, push the record number as it 205 288 ** will be after the update. (The old record number is currently 206 289 ** on top of the stack.) 207 290 */ 208 291 if( chngRecno ){ ................................................................................ 237 320 /* Delete the old indices for the current record. 238 321 */ 239 322 sqliteGenerateRowIndexDelete(v, pTab, base, aIdxUsed); 240 323 241 324 /* If changing the record number, delete the old record. 242 325 */ 243 326 if( chngRecno ){ 244 - sqliteVdbeAddOp(v, OP_Delete, 0, 0); 327 + sqliteVdbeAddOp(v, OP_Delete, base, 0); 245 328 } 246 329 247 330 /* Create the new index entries and the new record. 248 331 */ 249 332 sqliteCompleteInsertion(pParse, pTab, base, aIdxUsed, chngRecno, 1); 250 333 251 334 /* Increment the row counter 252 335 */ 253 - if( db->flags & SQLITE_CountRows ){ 336 + if( db->flags & SQLITE_CountRows && !pParse->trigStack){ 254 337 sqliteVdbeAddOp(v, OP_AddImm, 1, 0); 255 338 } 339 + 340 + if (row_triggers_exist) { 341 + for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ 342 + if( openAll || aIdxUsed[i] ) 343 + sqliteVdbeAddOp(v, OP_Close, base+i+1, 0); 344 + } 345 + sqliteVdbeAddOp(v, OP_Close, base, 0); 346 + pParse->nTab = base; 347 + 348 + if (sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_AFTER, pTab, 349 + newIdx, oldIdx, onError)) goto update_cleanup; 350 + } 256 351 257 352 /* Repeat the above with the next record to be updated, until 258 353 ** all record selected by the WHERE clause have been updated. 259 354 */ 260 355 sqliteVdbeAddOp(v, OP_Goto, 0, addr); 261 356 sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v)); 262 357 sqliteVdbeAddOp(v, OP_ListReset, 0, 0); 358 + 359 + /* Close all tables if there were no FOR EACH ROW triggers */ 360 + if (!row_triggers_exist) { 361 + for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ 362 + if( openAll || aIdxUsed[i] ){ 363 + sqliteVdbeAddOp(v, OP_Close, base+i+1, 0); 364 + } 365 + } 366 + sqliteVdbeAddOp(v, OP_Close, base, 0); 367 + pParse->nTab = base; 368 + } else { 369 + sqliteVdbeAddOp(v, OP_Close, newIdx, 0); 370 + sqliteVdbeAddOp(v, OP_Close, oldIdx, 0); 371 + } 372 + 263 373 sqliteEndWriteOperation(pParse); 264 374 265 375 /* 266 376 ** Return the number of rows that were changed. 267 377 */ 268 - if( db->flags & SQLITE_CountRows ){ 378 + if( db->flags & SQLITE_CountRows && !pParse->trigStack ){ 269 379 sqliteVdbeAddOp(v, OP_ColumnCount, 1, 0); 270 380 sqliteVdbeAddOp(v, OP_ColumnName, 0, 0); 271 381 sqliteVdbeChangeP3(v, -1, "rows updated", P3_STATIC); 272 382 sqliteVdbeAddOp(v, OP_Callback, 1, 0); 273 383 } 274 384 275 385 update_cleanup:
Changes to src/vdbe.c.
26 26 ** type to the other occurs as necessary. 27 27 ** 28 28 ** Most of the code in this file is taken up by the sqliteVdbeExec() 29 29 ** function which does the work of interpreting a VDBE program. 30 30 ** But other routines are also provided to help in building up 31 31 ** a program instruction by instruction. 32 32 ** 33 -** $Id: vdbe.c,v 1.141 2002/05/10 13:14:07 drh Exp $ 33 +** $Id: vdbe.c,v 1.142 2002/05/15 08:30:14 danielk1977 Exp $ 34 34 */ 35 35 #include "sqliteInt.h" 36 36 #include <ctype.h> 37 37 38 38 /* 39 39 ** The following global variable is incremented every time a cursor 40 40 ** moves, either by the OP_MoveTo or the OP_Next opcode. The test ................................................................................ 245 245 Mem *aMem; /* The memory locations */ 246 246 Agg agg; /* Aggregate information */ 247 247 int nSet; /* Number of sets allocated */ 248 248 Set *aSet; /* An array of sets */ 249 249 int nCallback; /* Number of callbacks invoked so far */ 250 250 int iLimit; /* Limit on the number of callbacks remaining */ 251 251 int iOffset; /* Offset before beginning to do callbacks */ 252 + 253 + int keylistStackDepth; 254 + Keylist ** keylistStack; 252 255 }; 253 256 254 257 /* 255 258 ** Create a new virtual database engine. 256 259 */ 257 260 Vdbe *sqliteVdbeCreate(sqlite *db){ 258 261 Vdbe *p; ................................................................................ 995 998 AggReset(&p->agg); 996 999 for(i=0; i<p->nSet; i++){ 997 1000 sqliteHashClear(&p->aSet[i].hash); 998 1001 } 999 1002 sqliteFree(p->aSet); 1000 1003 p->aSet = 0; 1001 1004 p->nSet = 0; 1005 + if (p->keylistStackDepth > 0) { 1006 + int ii; 1007 + for (ii = 0; ii < p->keylistStackDepth; ii++) { 1008 + KeylistFree(p->keylistStack[ii]); 1009 + } 1010 + sqliteFree(p->keylistStack); 1011 + p->keylistStackDepth = 0; 1012 + p->keylistStack = 0; 1013 + } 1002 1014 } 1003 1015 1004 1016 /* 1005 1017 ** Delete an entire VDBE. 1006 1018 */ 1007 1019 void sqliteVdbeDelete(Vdbe *p){ 1008 1020 int i; ................................................................................ 1058 1070 "MustBeInt", "Add", "AddImm", "Subtract", 1059 1071 "Multiply", "Divide", "Remainder", "BitAnd", 1060 1072 "BitOr", "BitNot", "ShiftLeft", "ShiftRight", 1061 1073 "AbsValue", "Eq", "Ne", "Lt", 1062 1074 "Le", "Gt", "Ge", "IsNull", 1063 1075 "NotNull", "Negative", "And", "Or", 1064 1076 "Not", "Concat", "Noop", "Function", 1065 - "Limit", 1077 + "Limit", "PushList", "PopList", 1066 1078 }; 1067 1079 1068 1080 /* 1069 1081 ** Given the name of an opcode, return its number. Return 0 if 1070 1082 ** there is no match. 1071 1083 ** 1072 1084 ** This routine is used for testing and debugging. ................................................................................ 4534 4546 if( VERIFY( i>=0 && i<p->nSet &&) 4535 4547 sqliteHashFind(&p->aSet[i].hash, zStack[tos], aStack[tos].n)){ 4536 4548 pc = pOp->p2 - 1; 4537 4549 } 4538 4550 POPSTACK; 4539 4551 break; 4540 4552 } 4553 + 4554 +/* Opcode: PushList * * * 4555 +** 4556 +** Save the current Vdbe list such that it can be restored by a PopList 4557 +** opcode. The list is empty after this is executed. 4558 +*/ 4559 +case OP_PushList: { 4560 + p->keylistStackDepth++; 4561 + assert(p->keylistStackDepth > 0); 4562 + p->keylistStack = sqliteRealloc(p->keylistStack, 4563 + sizeof(Keylist *) * p->keylistStackDepth); 4564 + p->keylistStack[p->keylistStackDepth - 1] = p->pList; 4565 + p->pList = 0; 4566 + break; 4567 +} 4568 + 4569 +/* Opcode: PopList * * * 4570 +** 4571 +** Restore the Vdbe list to the state it was in when PushList was last 4572 +** executed. 4573 +*/ 4574 +case OP_PopList: { 4575 + assert(p->keylistStackDepth > 0); 4576 + p->keylistStackDepth--; 4577 + KeylistFree(p->pList); 4578 + p->pList = p->keylistStack[p->keylistStackDepth]; 4579 + p->keylistStack[p->keylistStackDepth] = 0; 4580 + if (p->keylistStackDepth == 0) { 4581 + sqliteFree(p->keylistStack); 4582 + p->keylistStack = 0; 4583 + } 4584 + break; 4585 +} 4541 4586 4542 4587 /* Opcode: SetNotFound P1 P2 * 4543 4588 ** 4544 4589 ** Pop the stack once and compare the value popped off with the 4545 4590 ** contents of set P1. If the element popped does not exists in 4546 4591 ** set P1, then jump to P2. Otherwise fall through. 4547 4592 */
Changes to src/vdbe.h.
11 11 ************************************************************************* 12 12 ** Header file for the Virtual DataBase Engine (VDBE) 13 13 ** 14 14 ** This header defines the interface to the virtual database engine 15 15 ** or VDBE. The VDBE implements an abstract machine that runs a 16 16 ** simple program to access and modify the underlying database. 17 17 ** 18 -** $Id: vdbe.h,v 1.50 2002/04/20 14:24:43 drh Exp $ 18 +** $Id: vdbe.h,v 1.51 2002/05/15 08:30:14 danielk1977 Exp $ 19 19 */ 20 20 #ifndef _SQLITE_VDBE_H_ 21 21 #define _SQLITE_VDBE_H_ 22 22 #include <stdio.h> 23 23 24 24 /* 25 25 ** A single VDBE is an opaque structure named "Vdbe". Only routines ................................................................................ 194 194 #define OP_Not 109 195 195 #define OP_Concat 110 196 196 #define OP_Noop 111 197 197 #define OP_Function 112 198 198 199 199 #define OP_Limit 113 200 200 201 -#define OP_MAX 113 201 +#define OP_PushList 114 202 +#define OP_PopList 115 203 + 204 +#define OP_MAX 115 202 205 203 206 /* 204 207 ** Prototypes for the VDBE interface. See comments on the implementation 205 208 ** for a description of what each of these routines does. 206 209 */ 207 210 Vdbe *sqliteVdbeCreate(sqlite*); 208 211 void sqliteVdbeCreateCallback(Vdbe*, int*);
Changes to src/where.c.
9 9 ** May you share freely, never taking more than you give. 10 10 ** 11 11 ************************************************************************* 12 12 ** This module contains C code that generates VDBE code used to process 13 13 ** the WHERE clause of SQL statements. Also found here are subroutines 14 14 ** to generate VDBE code to evaluate expressions. 15 15 ** 16 -** $Id: where.c,v 1.41 2002/04/30 19:20:29 drh Exp $ 16 +** $Id: where.c,v 1.42 2002/05/15 08:30:14 danielk1977 Exp $ 17 17 */ 18 18 #include "sqliteInt.h" 19 19 20 20 /* 21 21 ** The query generator uses an array of instances of this structure to 22 22 ** help it analyze the subexpressions of the WHERE clause. Each WHERE 23 23 ** clause subexpression is separated from the others by an AND operator. ................................................................................ 212 212 memset(aExpr, 0, sizeof(aExpr)); 213 213 nExpr = exprSplit(ARRAYSIZE(aExpr), aExpr, pWhere); 214 214 215 215 /* Analyze all of the subexpressions. 216 216 */ 217 217 for(i=0; i<nExpr; i++){ 218 218 exprAnalyze(base, &aExpr[i]); 219 + if (pParse->trigStack && pParse->trigStack->newIdx >= 0) { 220 + aExpr[i].prereqRight = 221 + aExpr[i].prereqRight & ~(1 << pParse->trigStack->newIdx - base); 222 + aExpr[i].prereqLeft = 223 + aExpr[i].prereqLeft & ~(1 << pParse->trigStack->newIdx - base); 224 + aExpr[i].prereqAll = 225 + aExpr[i].prereqAll & ~(1 << pParse->trigStack->newIdx - base); 226 + } 227 + if (pParse->trigStack && pParse->trigStack->oldIdx >= 0) { 228 + aExpr[i].prereqRight = 229 + aExpr[i].prereqRight & ~(1 << pParse->trigStack->oldIdx - base); 230 + aExpr[i].prereqLeft = 231 + aExpr[i].prereqLeft & ~(1 << pParse->trigStack->oldIdx - base); 232 + aExpr[i].prereqAll = 233 + aExpr[i].prereqAll & ~(1 << pParse->trigStack->oldIdx - base); 234 + } 219 235 } 220 236 221 237 /* Figure out a good nesting order for the tables. aOrder[0] will 222 238 ** be the index in pTabList of the outermost table. aOrder[1] will 223 239 ** be the first nested loop and so on. aOrder[pTabList->nId-1] will 224 240 ** be the innermost loop. 225 241 **
Added test/trigger1.test.
1 +# The author disclaims copyright to this source code. In place of 2 +# a legal notice, here is a blessing: 3 +# 4 +# May you do good and not evil. 5 +# May you find forgiveness for yourself and forgive others. 6 +# May you share freely, never taking more than you give. 7 +# 8 +#*********************************************************************** 9 +# 10 +# This file tests creating and dropping triggers, and interaction thereof 11 +# with the database COMMIT/ROLLBACK logic. 12 +# 13 +# 1. CREATE and DROP TRIGGER tests 14 +# trig-1.1: Error if table does not exist 15 +# trig-1.2: Error if trigger already exists 16 +# trig-1.3: Created triggers are deleted if the transaction is rolled back 17 +# trig-1.4: DROP TRIGGER removes trigger 18 +# trig-1.5: Dropped triggers are restored if the transaction is rolled back 19 +# trig-1.6: Error if dropped trigger doesn't exist 20 +# trig-1.7: Dropping the table automatically drops all triggers 21 +# trig-1.8: A trigger created on a TEMP table is not inserted into sqlite_master 22 +# 23 + 24 +set testdir [file dirname $argv0] 25 +source $testdir/tester.tcl 26 + 27 +do_test trig_cd-1.1 { 28 + catchsql { 29 + CREATE TRIGGER trig UPDATE ON no_such_table BEGIN 30 + SELECT * from sqlite_master; 31 + END; 32 + } 33 +} {1 {no such table: no_such_table}} 34 + 35 +execsql { 36 + CREATE TABLE t1(a); 37 +} 38 +execsql { 39 + CREATE TRIGGER tr1 INSERT ON t1 BEGIN 40 + INSERT INTO t1 values(1); 41 + END; 42 +} 43 +do_test trig_cd-1.2 { 44 + catchsql { 45 + CREATE TRIGGER tr1 DELETE ON t1 BEGIN 46 + SELECT * FROM sqlite_master; 47 + END 48 + } 49 +} {1 {trigger tr1 already exists}} 50 + 51 +do_test trig_cd-1.3 { 52 + catchsql { 53 + BEGIN; 54 + CREATE TRIGGER tr2 INSERT ON t1 BEGIN 55 + SELECT * from sqlite_master; END; 56 + ROLLBACK; 57 + CREATE TRIGGER tr2 INSERT ON t1 BEGIN 58 + SELECT * from sqlite_master; END; 59 + } 60 +} {0 {}} 61 + 62 +do_test trig_cd-1.4 { 63 + catchsql { 64 + DROP TRIGGER tr1; 65 + CREATE TRIGGER tr1 DELETE ON t1 BEGIN 66 + SELECT * FROM sqlite_master; 67 + END 68 + } 69 +} {0 {}} 70 + 71 +do_test trig_cd-1.5 { 72 + execsql { 73 + BEGIN; 74 + DROP TRIGGER tr2; 75 + ROLLBACK; 76 + DROP TRIGGER tr2; 77 + } 78 +} {} 79 + 80 +do_test trig_cd-1.6 { 81 + catchsql { 82 + DROP TRIGGER biggles; 83 + } 84 +} {1 {no such trigger: biggles}} 85 + 86 +do_test trig_cd-1.7 { 87 + catchsql { 88 + DROP TABLE t1; 89 + DROP TRIGGER tr1; 90 + } 91 +} {1 {no such trigger: tr1}} 92 + 93 +execsql { 94 + CREATE TEMP TABLE temp_table(a); 95 +} 96 +do_test trig_cd-1.8 { 97 + execsql { 98 + CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN 99 + SELECT * from sqlite_master; 100 + END; 101 + SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig'; 102 + } 103 +} {0} 104 + 105 +catchsql { 106 + DROP TABLE temp_table; 107 +} 108 +catchsql { 109 + DROP TABLE t1; 110 +} 111 + 112 +finish_test 113 +
Added test/trigger2.test.
1 +# The author disclaims copyright to this source code. In place of 2 +# a legal notice, here is a blessing: 3 +# 4 +# May you do good and not evil. 5 +# May you find forgiveness for yourself and forgive others. 6 +# May you share freely, never taking more than you give. 7 +# 8 +#*********************************************************************** 9 +# 10 +# Regression testing of FOR EACH ROW table triggers 11 +# 12 +# 1. Trigger execution order tests. 13 +# These tests ensure that BEFORE and AFTER triggers are fired at the correct 14 +# times relative to each other and the triggering statement. 15 +# 16 +# trig-1.1.*: ON UPDATE trigger execution model. 17 +# trig-1.2.*: DELETE trigger execution model. 18 +# trig-1.3.*: INSERT trigger execution model. 19 +# 20 +# 2. Trigger program execution tests. 21 +# These tests ensure that trigger programs execute correctly (ie. that a 22 +# trigger program can correctly execute INSERT, UPDATE, DELETE * SELECT 23 +# statements, and combinations thereof). 24 +# 25 +# 3. Selective trigger execution 26 +# This tests that conditional triggers (ie. UPDATE OF triggers and triggers 27 +# with WHEN clauses) are fired only fired when they are supposed to be. 28 +# 29 +# trig-3.1: UPDATE OF triggers 30 +# trig-3.2: WHEN clause 31 +# 32 +# 4. Cascaded trigger execution 33 +# Tests that trigger-programs may cause other triggers to fire. Also that a 34 +# trigger-program is never executed recursively. 35 +# 36 +# trig-4.1: Trivial cascading trigger 37 +# trig-4.2: Trivial recursive trigger handling 38 +# 39 +# 5. Count changes behaviour. 40 +# Verify that rows altered by triggers are not included in the return value 41 +# of the "count changes" interface. 42 +# 43 +# 6. ON CONFLICT clause handling 44 +# trig-6.1[a-f]: INSERT statements 45 +# trig-6.2[a-f]: UPDATE statements 46 +# 47 +# 7. Triggers on views fire correctly. 48 +# 49 + 50 +set testdir [file dirname $argv0] 51 +source $testdir/tester.tcl 52 + 53 +# 1. 54 +set ii 0 55 +foreach tbl_defn [ list \ 56 + {CREATE TABLE tbl (a, b);} \ 57 + {CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);} \ 58 + {CREATE TABLE tbl (a, b PRIMARY KEY);} \ 59 + {CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} ] { 60 + incr ii 61 + catchsql { DROP INDEX tbl_idx; } 62 + catchsql { 63 + DROP TABLE rlog; 64 + DROP TABLE clog; 65 + DROP TABLE tbl; 66 + DROP TABLE other_tbl; 67 + } 68 + 69 + execsql $tbl_defn 70 + 71 + execsql { 72 + INSERT INTO tbl VALUES(1, 2); 73 + INSERT INTO tbl VALUES(3, 4); 74 + 75 + CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); 76 + CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); 77 + 78 + CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW 79 + BEGIN 80 + INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 81 + old.a, old.b, 82 + (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 83 + new.a, new.b); 84 + END; 85 + 86 + CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW 87 + BEGIN 88 + INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 89 + old.a, old.b, 90 + (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 91 + new.a, new.b); 92 + END; 93 + 94 + CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW 95 + WHEN old.a = 1 96 + BEGIN 97 + INSERT INTO clog VALUES ( (SELECT max(idx) + 1 FROM clog), 98 + old.a, old.b, 99 + (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 100 + new.a, new.b); 101 + END; 102 + } 103 + 104 + do_test trig-1.1.$ii { 105 + execsql { 106 + UPDATE tbl SET a = a * 10, b = b * 10; 107 + SELECT * FROM rlog ORDER BY idx; 108 + SELECT * FROM clog ORDER BY idx; 109 + } 110 + } [list 1 1 2 4 6 10 20 \ 111 + 2 1 2 13 24 10 20 \ 112 + 3 3 4 13 24 30 40 \ 113 + 4 3 4 40 60 30 40 \ 114 + 1 1 2 13 24 10 20 ] 115 + 116 + execsql { 117 + DELETE FROM rlog; 118 + DELETE FROM tbl; 119 + INSERT INTO tbl VALUES (100, 100); 120 + INSERT INTO tbl VALUES (300, 200); 121 + CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW 122 + BEGIN 123 + INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 124 + old.a, old.b, 125 + (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 126 + 0, 0); 127 + END; 128 + 129 + CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW 130 + BEGIN 131 + INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 132 + old.a, old.b, 133 + (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 134 + 0, 0); 135 + END; 136 + } 137 + do_test trig-1.2.$ii { 138 + execsql { 139 + DELETE FROM tbl; 140 + SELECT * FROM rlog; 141 + } 142 + } [list 1 100 100 400 300 0 0 \ 143 + 2 100 100 300 200 0 0 \ 144 + 3 300 200 300 200 0 0 \ 145 + 4 300 200 0 0 0 0 ] 146 + 147 + execsql { 148 + DELETE FROM rlog; 149 + CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW 150 + BEGIN 151 + INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 152 + 0, 0, 153 + (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 154 + new.a, new.b); 155 + END; 156 + 157 + CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW 158 + BEGIN 159 + INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog), 160 + 0, 0, 161 + (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 162 + new.a, new.b); 163 + END; 164 + } 165 + do_test trig-1.3.$ii { 166 + execsql { 167 + 168 + CREATE TABLE other_tbl(a, b); 169 + INSERT INTO other_tbl VALUES(1, 2); 170 + INSERT INTO other_tbl VALUES(3, 4); 171 + -- INSERT INTO tbl SELECT * FROM other_tbl; 172 + INSERT INTO tbl VALUES(5, 6); 173 + DROP TABLE other_tbl; 174 + 175 + SELECT * FROM rlog; 176 + } 177 + } [list 1 0 0 0 0 5 6 \ 178 + 2 0 0 5 6 5 6 ] 179 +} 180 +catchsql { 181 + DROP TABLE rlog; 182 + DROP TABLE clog; 183 + DROP TABLE tbl; 184 + DROP TABLE other_tbl; 185 +} 186 + 187 +# 2. 188 +set ii 0 189 +foreach tr_program [ list \ 190 + {UPDATE tbl SET b = old.b;} \ 191 + {INSERT INTO log VALUES(new.c, 2, 3);} \ 192 + {DELETE FROM log WHERE a = 1;} \ 193 + {INSERT INTO tbl VALUES(500, new.b * 10, 700); 194 + UPDATE tbl SET c = old.c; 195 + DELETE FROM log;} \ 196 + {INSERT INTO log select * from tbl;} 197 + ] \ 198 +{ 199 + foreach test_varset [ list \ 200 + { 201 + set statement {UPDATE tbl SET c = 10 WHERE a = 1;} 202 + set prep {INSERT INTO tbl VALUES(1, 2, 3);} 203 + set newC 10 204 + set newB 2 205 + set newA 1 206 + set oldA 1 207 + set oldB 2 208 + set oldC 3 209 + } \ 210 + { 211 + set statement {DELETE FROM tbl WHERE a = 1;} 212 + set prep {INSERT INTO tbl VALUES(1, 2, 3);} 213 + set oldA 1 214 + set oldB 2 215 + set oldC 3 216 + } \ 217 + { 218 + set statement {INSERT INTO tbl VALUES(1, 2, 3);} 219 + set newA 1 220 + set newB 2 221 + set newC 3 222 + } 223 + ] \ 224 + { 225 + set statement {} 226 + set prep {} 227 + set newA {''} 228 + set newB {''} 229 + set newC {''} 230 + set oldA {''} 231 + set oldB {''} 232 + set oldC {''} 233 + 234 + incr ii 235 + 236 + eval $test_varset 237 + 238 + set statement_type [string range $statement 0 5] 239 + set tr_program_fixed $tr_program 240 + if {$statement_type == "DELETE"} { 241 + regsub -all new\.a $tr_program_fixed {''} tr_program_fixed 242 + regsub -all new\.b $tr_program_fixed {''} tr_program_fixed 243 + regsub -all new\.c $tr_program_fixed {''} tr_program_fixed 244 + } 245 + if {$statement_type == "INSERT"} { 246 + regsub -all old\.a $tr_program_fixed {''} tr_program_fixed 247 + regsub -all old\.b $tr_program_fixed {''} tr_program_fixed 248 + regsub -all old\.c $tr_program_fixed {''} tr_program_fixed 249 + } 250 + 251 + 252 + set tr_program_cooked $tr_program 253 + regsub -all new\.a $tr_program_cooked $newA tr_program_cooked 254 + regsub -all new\.b $tr_program_cooked $newB tr_program_cooked 255 + regsub -all new\.c $tr_program_cooked $newC tr_program_cooked 256 + regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked 257 + regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked 258 + regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked 259 + 260 + catchsql { 261 + DROP TABLE tbl; 262 + DROP TABLE log; 263 + } 264 + execsql { 265 + CREATE TABLE tbl(a PRIMARY KEY, b, c); 266 + CREATE TABLE log(a, b, c); 267 + } 268 + 269 + set query {SELECT * FROM tbl; SELECT * FROM log;} 270 + set prep "$prep; INSERT INTO log VALUES(1, 2, 3); INSERT INTO log VALUES(10, 20, 30);" 271 + 272 +# Check execution of BEFORE programs: 273 + 274 + set before_data [ execsql "$prep $tr_program_cooked $statement $query" ] 275 + 276 + execsql "DELETE FROM tbl; DELETE FROM log; $prep"; 277 + execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;" 278 + 279 + do_test trig-2-$ii-before "execsql {$statement $query}" $before_data 280 + 281 + execsql "DROP TRIGGER the_trigger;" 282 + execsql "DELETE FROM tbl; DELETE FROM log;" 283 + 284 +# Check execution of AFTER programs 285 + set after_data [ execsql "$prep $statement $tr_program_cooked $query" ] 286 + 287 + execsql "DELETE FROM tbl; DELETE FROM log; $prep"; 288 + 289 + execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;" 290 + 291 + do_test trig-2-$ii-after "execsql {$statement $query}" $after_data 292 + execsql "DROP TRIGGER the_trigger;" 293 + } 294 +} 295 +catchsql { 296 + DROP TABLE tbl; 297 + DROP TABLE log; 298 +} 299 + 300 +# 3. 301 + 302 +# trig-3.1: UPDATE OF triggers 303 +execsql { 304 + CREATE TABLE tbl (a, b, c, d); 305 + CREATE TABLE log (a); 306 + INSERT INTO log VALUES (0); 307 + INSERT INTO tbl VALUES (0, 0, 0, 0); 308 + INSERT INTO tbl VALUES (1, 0, 0, 0); 309 + CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl 310 + BEGIN 311 + UPDATE log SET a = a + 1; 312 + END; 313 +} 314 +do_test trig-3.1 { 315 + execsql { 316 + UPDATE tbl SET b = 1, c = 10; -- 2 317 + UPDATE tbl SET b = 10; -- 0 318 + UPDATE tbl SET d = 4 WHERE a = 0; --1 319 + UPDATE tbl SET a = 4, b = 10; --0 320 + SELECT * FROM log; 321 + } 322 +} {3} 323 +execsql { 324 + DROP TABLE tbl; 325 + DROP TABLE log; 326 +} 327 + 328 +# trig-3.2: WHEN clause 329 +set when_triggers [ list \ 330 + {t1 BEFORE INSERT ON tbl WHEN new.a > 20} \ 331 + {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0} ] 332 + 333 +execsql { 334 + CREATE TABLE tbl (a, b, c, d); 335 + CREATE TABLE log (a); 336 + INSERT INTO log VALUES (0); 337 +} 338 + 339 +foreach trig $when_triggers { 340 + execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;" 341 +} 342 + 343 +do_test trig-3.2 { 344 + execsql { 345 + 346 + INSERT INTO tbl VALUES(0, 0, 0, 0); -- 1 347 + SELECT * FROM log; 348 + UPDATE log SET a = 0; 349 + 350 + INSERT INTO tbl VALUES(0, 0, 0, 0); -- 0 351 + SELECT * FROM log; 352 + UPDATE log SET a = 0; 353 + 354 + INSERT INTO tbl VALUES(200, 0, 0, 0); -- 1 355 + SELECT * FROM log; 356 + UPDATE log SET a = 0; 357 + } 358 +} {1 0 1} 359 +execsql { 360 + DROP TABLE tbl; 361 + DROP TABLE log; 362 +} 363 + 364 +# Simple cascaded trigger 365 +execsql { 366 + CREATE TABLE tblA(a, b); 367 + CREATE TABLE tblB(a, b); 368 + CREATE TABLE tblC(a, b); 369 + 370 + CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN 371 + INSERT INTO tblB values(new.a, new.b); 372 + END; 373 + 374 + CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN 375 + INSERT INTO tblC values(new.a, new.b); 376 + END; 377 +} 378 +do_test trig-4.1 { 379 + execsql { 380 + INSERT INTO tblA values(1, 2); 381 + SELECT * FROM tblA; 382 + SELECT * FROM tblB; 383 + SELECT * FROM tblC; 384 + } 385 +} {1 2 1 2 1 2} 386 +execsql { 387 + DROP TABLE tblA; 388 + DROP TABLE tblB; 389 + DROP TABLE tblC; 390 +} 391 + 392 +# Simple recursive trigger 393 +execsql { 394 + CREATE TABLE tbl(a, b, c); 395 + CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 396 + BEGIN 397 + INSERT INTO tbl VALUES (new.a, new.b, new.c); 398 + END; 399 +} 400 +do_test trig-4.2 { 401 + execsql { 402 + INSERT INTO tbl VALUES (1, 2, 3); 403 + select * from tbl; 404 + } 405 +} {1 2 3 1 2 3} 406 +execsql { 407 + DROP TABLE tbl; 408 +} 409 + 410 +# 5. 411 +execsql { 412 + CREATE TABLE tbl(a, b, c); 413 + CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 414 + BEGIN 415 + INSERT INTO tbl VALUES (1, 2, 3); 416 + INSERT INTO tbl VALUES (2, 2, 3); 417 + UPDATE tbl set b = 10 WHERE a = 1; 418 + DELETE FROM tbl WHERE a = 1; 419 + DELETE FROM tbl; 420 + END; 421 +} 422 +do_test trig-5 { 423 + execsql { 424 + INSERT INTO tbl VALUES(100, 200, 300); 425 + } 426 + db changes 427 +} {1} 428 +execsql { 429 + DROP TABLE tbl; 430 +} 431 + 432 + 433 +# Handling of ON CONFLICT by INSERT statements inside triggers 434 +execsql { 435 + CREATE TABLE tbl (a primary key, b, c); 436 + CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN 437 + INSERT OR IGNORE INTO tbl values (new.a, 0, 0); 438 + END; 439 +} 440 +do_test trig-6.1a { 441 + execsql { 442 + BEGIN; 443 + INSERT INTO tbl values (1, 2, 3); 444 + SELECT * from tbl; 445 + } 446 +} {1 2 3} 447 +do_test trig-6.1b { 448 + catchsql { 449 + INSERT OR ABORT INTO tbl values (2, 2, 3); 450 + } 451 +} {1 {constraint failed}} 452 +do_test trig-6.1c { 453 + execsql { 454 + SELECT * from tbl; 455 + } 456 +} {1 2 3} 457 +do_test trig-6.1d { 458 + catchsql { 459 + INSERT OR FAIL INTO tbl values (2, 2, 3); 460 + } 461 +} {1 {constraint failed}} 462 +do_test trig-6.1e { 463 + execsql { 464 + SELECT * from tbl; 465 + } 466 +} {1 2 3 2 2 3} 467 +do_test trig-6.1f { 468 + execsql { 469 + INSERT OR REPLACE INTO tbl values (2, 2, 3); 470 + SELECT * from tbl; 471 + } 472 +} {1 2 3 2 0 0} 473 +do_test trig-6.1g { 474 + catchsql { 475 + INSERT OR ROLLBACK INTO tbl values (3, 2, 3); 476 + } 477 +} {1 {constraint failed}} 478 +do_test trig-6.1h { 479 + execsql { 480 + SELECT * from tbl; 481 + } 482 +} {} 483 + 484 + 485 +# Handling of ON CONFLICT by UPDATE statements inside triggers 486 +execsql { 487 + INSERT INTO tbl values (4, 2, 3); 488 + INSERT INTO tbl values (6, 3, 4); 489 + CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN 490 + UPDATE OR IGNORE tbl SET a = new.a, c = 10; 491 + END; 492 +} 493 +do_test trig-6.2a { 494 + execsql { 495 + BEGIN; 496 + UPDATE tbl SET a = 1 WHERE a = 4; 497 + SELECT * from tbl; 498 + } 499 +} {1 2 10 6 3 4} 500 +do_test trig-6.2b { 501 + catchsql { 502 + UPDATE OR ABORT tbl SET a = 4 WHERE a = 1; 503 + } 504 +} {1 {constraint failed}} 505 +do_test trig-6.2c { 506 + execsql { 507 + SELECT * from tbl; 508 + } 509 +} {1 2 10 6 3 4} 510 +do_test trig-6.2d { 511 + catchsql { 512 + UPDATE OR FAIL tbl SET a = 4 WHERE a = 1; 513 + } 514 +} {1 {constraint failed}} 515 +do_test trig-6.2e { 516 + execsql { 517 + SELECT * from tbl; 518 + } 519 +} {4 2 10 6 3 4} 520 +do_test trig-6.2f { 521 + execsql { 522 + UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4; 523 + SELECT * from tbl; 524 + } 525 +} {1 3 10} 526 +execsql { 527 + INSERT INTO tbl VALUES (2, 3, 4); 528 +} 529 +do_test trig-6.2g { 530 + catchsql { 531 + UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1; 532 + } 533 +} {1 {constraint failed}} 534 +do_test trig-6.2h { 535 + execsql { 536 + SELECT * from tbl; 537 + } 538 +} {4 2 3 6 3 4} 539 +execsql { 540 + DROP TABLE tbl; 541 +} 542 + 543 +# 7. Triggers on views 544 +execsql { 545 + CREATE TABLE ab(a, b); 546 + CREATE TABLE cd(c, d); 547 + INSERT INTO ab VALUES (1, 2); 548 + INSERT INTO ab VALUES (0, 0); 549 + INSERT INTO cd VALUES (3, 4); 550 + 551 + CREATE TABLE tlog(ii INTEGER PRIMARY KEY, 552 + olda, oldb, oldc, oldd, newa, newb, newc, newd); 553 + 554 + CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd; 555 + 556 + CREATE TRIGGER before_update BEFORE UPDATE ON abcd BEGIN 557 + INSERT INTO tlog VALUES(NULL, 558 + old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); 559 + END; 560 + CREATE TRIGGER after_update AFTER UPDATE ON abcd BEGIN 561 + INSERT INTO tlog VALUES(NULL, 562 + old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); 563 + END; 564 + 565 + CREATE TRIGGER before_delete BEFORE DELETE ON abcd BEGIN 566 + INSERT INTO tlog VALUES(NULL, 567 + old.a, old.b, old.c, old.d, 0, 0, 0, 0); 568 + END; 569 + CREATE TRIGGER after_delete AFTER DELETE ON abcd BEGIN 570 + INSERT INTO tlog VALUES(NULL, 571 + old.a, old.b, old.c, old.d, 0, 0, 0, 0); 572 + END; 573 + 574 + CREATE TRIGGER before_insert BEFORE INSERT ON abcd BEGIN 575 + INSERT INTO tlog VALUES(NULL, 576 + 0, 0, 0, 0, new.a, new.b, new.c, new.d); 577 + END; 578 + CREATE TRIGGER after_insert AFTER INSERT ON abcd BEGIN 579 + INSERT INTO tlog VALUES(NULL, 580 + 0, 0, 0, 0, new.a, new.b, new.c, new.d); 581 + END; 582 +} 583 + 584 +do_test trig-7 { 585 + execsql { 586 + UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 587 + DELETE FROM abcd WHERE a = 1; 588 + INSERT INTO abcd VALUES(10, 20, 30, 40); 589 + SELECT * FROM tlog; 590 + } 591 +} [ list 1 1 2 3 4 100 25 3 4 \ 592 + 2 1 2 3 4 100 25 3 4 \ 593 + 3 1 2 3 4 0 0 0 0 4 1 2 3 4 0 0 0 0 \ 594 + 5 0 0 0 0 10 20 30 40 6 0 0 0 0 10 20 30 40 ] 595 + 596 +finish_test 597 +
Changes to www/lang.tcl.
1 1 # 2 2 # Run this Tcl script to generate the sqlite.html file. 3 3 # 4 -set rcsid {$Id: lang.tcl,v 1.33 2002/05/06 11:47:33 drh Exp $} 4 +set rcsid {$Id: lang.tcl,v 1.34 2002/05/15 08:30:15 danielk1977 Exp $} 5 5 6 6 puts {<html> 7 7 <head> 8 8 <title>Query Language Understood By SQLite</title> 9 9 </head> 10 10 <body bgcolor=white> 11 11 <h1 align=center> ................................................................................ 50 50 {EXPLAIN explain} 51 51 {expression expr} 52 52 {{BEGIN TRANSACTION} transaction} 53 53 {PRAGMA pragma} 54 54 {{ON CONFLICT clause} conflict} 55 55 {{CREATE VIEW} createview} 56 56 {{DROP VIEW} dropview} 57 + {{CREATE TRIGGER} createtrigger} 58 + {{DROP TRIGGER} droptrigger} 57 59 }] { 58 60 puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>" 59 61 } 60 62 puts {</ul></p> 61 63 62 64 <p>Details on the implementation of each command are provided in 63 65 the sequel.</p> ................................................................................ 1084 1086 table or index then it is suppose to clean up the named table or index. 1085 1087 In version 1.0 of SQLite, the VACUUM command would invoke 1086 1088 <b>gdbm_reorganize()</b> to clean up the backend database file. 1087 1089 Beginning with version 2.0 of SQLite, GDBM is no longer used for 1088 1090 the database backend and VACUUM has become a no-op. 1089 1091 </p> 1090 1092 } 1093 + 1094 +Section {CREATE TRIGGER} createtrigger 1095 + 1096 +Syntax {sql-statement} { 1097 +CREATE TRIGGER <trigger-name> [ BEFORE | AFTER ] 1098 +<database-event> 1099 +<trigger-action> 1100 +} 1101 + 1102 +Syntax {database-event} { 1103 +DELETE | 1104 +INSERT | 1105 +UPDATE | 1106 +UPDATE OF <column-list> 1107 +ON <table-name> 1108 +} 1109 + 1110 +Syntax {trigger-action} { 1111 +[ FOR EACH ROW ] [ WHEN <expression> ] 1112 +BEGIN 1113 + <trigger-step> ; [ <trigger-step> ; ]* 1114 +END 1115 +} 1116 + 1117 +Syntax {trigger-step} { 1118 +<update-statement> | <insert-statement> | 1119 +<delete-statement> | <select-statement> 1120 +} 1121 + 1122 +puts { 1123 +<p>The CREATE TRIGGER statement is used to add triggers to the 1124 +database schema. Triggers are database operations (the <i>trigger-action</i>) 1125 +that are automatically performed when a specified database event (the 1126 +<i>database-event</i>) occurs. </p> 1127 + 1128 +<p>A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a 1129 +particular database table occurs, or whenever an UPDATE of one or more 1130 +specified columns of a table are updated.</p> 1131 + 1132 +<p>At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH 1133 +STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional. FOR 1134 +EACH ROW implies that the SQL statements specified as <i>trigger-steps</i> 1135 +may be executed (depending on the WHEN clause) for each database row being 1136 +inserted, updated or deleted by the statement causing the trigger to fire.</p> 1137 + 1138 +<p>Both the WHEN clause and the <i>trigger-steps</i> may access elements of 1139 +the row being inserted, deleted or updated using references of the form 1140 +"NEW.<i>column-name</i>" and "OLD.<i>column-name</i>", where 1141 +<i>column-name</i> is the name of a column from the table that the trigger 1142 +is associated with. OLD and NEW references may only be used in triggers on 1143 +<i>trigger-event</i>s for which they are relevant, as follows:</p> 1144 + 1145 +<table border=0 cellpadding=10> 1146 +<tr> 1147 +<td valign="top" align="right" width=120><i>INSERT</i></td> 1148 +<td valign="top">NEW references are valid</td> 1149 +</tr> 1150 +<tr> 1151 +<td valign="top" align="right" width=120><i>UPDATE</i></td> 1152 +<td valign="top">NEW and OLD references are valid</td> 1153 +</tr> 1154 +<tr> 1155 +<td valign="top" align="right" width=120><i>DELETE</i></td> 1156 +<td valign="top">OLD references are valid</td> 1157 +</tr> 1158 +</table> 1159 +</p> 1160 + 1161 +<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> 1162 + 1163 +<p>The specified <i>trigger-time</i> determines when the <i>trigger-steps</i> 1164 +will be executed relative to the insertion, modification or removal of the 1165 +associated row.</p> 1166 + 1167 +<p>An ON CONFLICT clause may be specified as part of an UPDATE or INSERT 1168 +<i>trigger-step</i>. However if an ON CONFLICT clause is specified as part of 1169 +the statement causing the trigger to fire, then this conflict handling 1170 +policy is used instead.</p> 1171 + 1172 +<p>Triggers are automatically dropped when the table that they are 1173 +associated with is dropped.</p> 1174 + 1175 +<p>Triggers may be created on views, as well as ordinary tables. If one or 1176 +more INSERT, DELETE or UPDATE triggers are defined on a view, then it is not 1177 +an error to execute an INSERT, DELETE or UPDATE statement on the view, 1178 +respectively. Thereafter, executing an INSERT, DELETE or UPDATE on the view 1179 +causes the associated triggers to fire. The real tables underlying the view 1180 +are not modified (except possibly explicitly, by a trigger program).</p> 1181 + 1182 +<p><b>Example:</b></p> 1183 + 1184 +<p>Assuming that customer records are stored in the "customers" table, and 1185 +that order records are stored in the "orders" table, the following trigger 1186 +ensures that all associated orders are redirected when a customer changes 1187 +his or her address:</p> 1188 +} 1189 +Example { 1190 +CREATE TRIGGER update_customer_address UPDATE OF address ON customers 1191 + BEGIN 1192 + UPDATE orders SET address = new.address WHERE customer_name = old.name; 1193 + END; 1194 +} 1195 +puts { 1196 +<p>With this trigger installed, executing the statement:</p> 1197 +} 1198 +Example { 1199 +UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones'; 1200 +} 1201 +puts { 1202 +<p>causes the following to be automatically executed:</p> 1203 +} 1204 +Example { 1205 +UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones'; 1206 +} 1207 + 1208 +Section {DROP TRIGGER} droptrigger 1209 +Syntax {sql-statement} { 1210 +DROP TRIGGER <trigger-name> 1211 +} 1212 +puts { 1213 + <p>Used to drop a trigger from the database schema. Note that triggers 1214 + are automatically dropped when the associated table is dropped.</p> 1215 +} 1091 1216 1092 1217 1093 1218 puts { 1094 1219 <p><hr /></p> 1095 1220 <p><a href="index.html"><img src="/goback.jpg" border=0 /> 1096 1221 Back to the SQLite Home Page</a> 1097 1222 </p> 1098 1223 1099 1224 </body></html>}