Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | Allow the WHERE clause in an UPDATE or DELETE against a view with an INSTEAD OF trigger to refer to the view by name. Ticket #3298. (CVS 5589) |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
7360e5d1f37ed6672f336b625b3c90d0 |
| User & Date: | drh 2008-08-22 12:30:52 |
|
2008-08-22
| ||
| 12:46 | Add test case to verify that Ticket #3301 was fixed by (5569) (CVS 5590) (check-in: 73b26068 user: drh tags: trunk) | |
| 12:30 | Allow the WHERE clause in an UPDATE or DELETE against a view with an INSTEAD OF trigger to refer to the view by name. Ticket #3298. (CVS 5589) (check-in: 7360e5d1 user: drh tags: trunk) | |
| 00:47 | Fix os_unix.c so that it will compile on Linux again. (CVS 5588) (check-in: 24167082 user: drh tags: trunk) | |
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 ** in order to generate code for DELETE FROM statements. 14 14 ** 15 -** $Id: delete.c,v 1.172 2008/08/20 16:35:10 drh Exp $ 15 +** $Id: delete.c,v 1.173 2008/08/22 12:30:52 drh Exp $ 16 16 */ 17 17 #include "sqliteInt.h" 18 18 19 19 /* 20 20 ** Look up every table that is named in pSrc. If any table is not found, 21 21 ** add an error message to pParse->zErrMsg and return NULL. If all tables 22 22 ** are found, return a pointer to the last table. ................................................................................ 86 86 /* 87 87 ** Evaluate a view and store its result in an ephemeral table. The 88 88 ** pWhere argument is an optional WHERE clause that restricts the 89 89 ** set of rows in the view that are to be added to the ephemeral table. 90 90 */ 91 91 void sqlite3MaterializeView( 92 92 Parse *pParse, /* Parsing context */ 93 - Select *pView, /* View definition */ 93 + Table *pView, /* View definition */ 94 94 Expr *pWhere, /* Optional WHERE clause to be added */ 95 95 int iCur /* Cursor number for ephemerial table */ 96 96 ){ 97 97 SelectDest dest; 98 98 Select *pDup; 99 99 sqlite3 *db = pParse->db; 100 100 101 - pDup = sqlite3SelectDup(db, pView); 101 + pDup = sqlite3SelectDup(db, pView->pSelect); 102 102 if( pWhere ){ 103 103 SrcList *pFrom; 104 + Token viewName; 104 105 105 106 pWhere = sqlite3ExprDup(db, pWhere); 106 - pFrom = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, 0, pDup, 0, 0); 107 + viewName.z = pView->zName; 108 + viewName.n = strlen(viewName.z); 109 + pFrom = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, &viewName, pDup, 0,0); 107 110 pDup = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 0, 0, 0); 108 111 } 109 112 sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur); 110 113 sqlite3Select(pParse, pDup, &dest); 111 114 sqlite3SelectDelete(db, pDup); 112 115 } 113 116 #endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */ ................................................................................ 243 246 sqlite3VdbeJumpHere(v, iGoto); 244 247 } 245 248 246 249 /* If we are trying to delete from a view, realize that view into 247 250 ** a ephemeral table. 248 251 */ 249 252 if( isView ){ 250 - sqlite3MaterializeView(pParse, pTab->pSelect, pWhere, iCur); 253 + sqlite3MaterializeView(pParse, pTab, pWhere, iCur); 251 254 } 252 255 253 256 /* Resolve the column names in the WHERE clause. 254 257 */ 255 258 memset(&sNC, 0, sizeof(sNC)); 256 259 sNC.pParse = pParse; 257 260 sNC.pSrcList = pTabList;
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.759 2008/08/21 20:21:35 drh Exp $ 14 +** @(#) $Id: sqliteInt.h,v 1.760 2008/08/22 12:30:52 drh Exp $ 15 15 */ 16 16 #ifndef _SQLITEINT_H_ 17 17 #define _SQLITEINT_H_ 18 18 19 19 /* 20 20 ** Include the configuration header output by 'configure' if we're using the 21 21 ** autoconf-based build ................................................................................ 2184 2184 #else 2185 2185 # define sqlite3SafetyOn(A) 0 2186 2186 # define sqlite3SafetyOff(A) 0 2187 2187 #endif 2188 2188 int sqlite3SafetyCheckOk(sqlite3*); 2189 2189 int sqlite3SafetyCheckSickOrOk(sqlite3*); 2190 2190 void sqlite3ChangeCookie(Parse*, int); 2191 -void sqlite3MaterializeView(Parse*, Select*, Expr*, int); 2191 +void sqlite3MaterializeView(Parse*, Table*, Expr*, int); 2192 2192 2193 2193 #ifndef SQLITE_OMIT_TRIGGER 2194 2194 void sqlite3BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*, 2195 2195 Expr*,int, int); 2196 2196 void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*); 2197 2197 void sqlite3DropTrigger(Parse*, SrcList*, int); 2198 2198 void sqlite3DropTriggerPtr(Parse*, Trigger*);
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.182 2008/08/20 16:35:10 drh Exp $ 15 +** $Id: update.c,v 1.183 2008/08/22 12:30:52 drh Exp $ 16 16 */ 17 17 #include "sqliteInt.h" 18 18 19 19 #ifndef SQLITE_OMIT_VIRTUALTABLE 20 20 /* Forward declaration */ 21 21 static void updateVirtualTable( 22 22 Parse *pParse, /* The parsing context */ ................................................................................ 325 325 sqlite3VdbeJumpHere(v, iGoto); 326 326 } 327 327 328 328 /* If we are trying to update a view, realize that view into 329 329 ** a ephemeral table. 330 330 */ 331 331 if( isView ){ 332 - sqlite3MaterializeView(pParse, pTab->pSelect, pWhere, iCur); 332 + sqlite3MaterializeView(pParse, pTab, pWhere, iCur); 333 333 } 334 334 335 335 /* Resolve the column names in all the expressions in the 336 336 ** WHERE clause. 337 337 */ 338 338 if( sqlite3ResolveExprNames(&sNC, pWhere) ){ 339 339 goto update_cleanup;
Added test/tkt3298.test.
1 +# 2008 August 12 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# 12 +# This file tests changes to the name resolution logic that occurred 13 +# in august of 2008 and where associated with tickets #3298 14 +# 15 +# $Id: tkt3298.test,v 1.1 2008/08/22 12:30:52 drh Exp $ 16 + 17 +set testdir [file dirname $argv0] 18 +source $testdir/tester.tcl 19 + 20 +do_test tkt3298-1.1 { 21 + execsql { 22 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT); 23 + INSERT INTO t1 VALUES(0, 1); 24 + INSERT INTO t1 VALUES(1, 1); 25 + INSERT INTO t1 VALUES(2, 1); 26 + CREATE VIEW v1 AS SELECT a AS x, b+1 AS y FROM t1; 27 + CREATE TRIGGER r1 INSTEAD OF UPDATE ON v1 28 + BEGIN 29 + UPDATE t1 SET b=new.y-1 WHERE a=new.x; 30 + END; 31 + CREATE TRIGGER r2 INSTEAD OF DELETE ON v1 32 + BEGIN 33 + DELETE FROM t1 WHERE a=old.x; 34 + END; 35 + SELECT * FROM v1 ORDER BY x; 36 + } 37 +} {0 2 1 2 2 2} 38 +do_test tkt3298-1.2 { 39 + execsql { 40 + UPDATE v1 SET y=3 WHERE x=0; 41 + SELECT * FROM v1 ORDER by x; 42 + } 43 +} {0 3 1 2 2 2} 44 +do_test tkt3298-1.3 { 45 + execsql { 46 + UPDATE v1 SET y=4 WHERE v1.x=2; 47 + SELECT * FROM v1 ORDER by x; 48 + } 49 +} {0 3 1 2 2 4} 50 +do_test tkt3298-1.4 { 51 + execsql { 52 + DELETE FROM v1 WHERE x=1; 53 + SELECT * FROM v1 ORDER BY x; 54 + } 55 +} {0 3 2 4} 56 +do_test tkt3298-1.5 { 57 + execsql { 58 + DELETE FROM v1 WHERE v1.x=2; 59 + SELECT * FROM v1 ORDER BY x; 60 + } 61 +} {0 3} 62 + 63 + 64 + 65 +finish_test