/ Check-in [7360e5d1]
Login

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

Overview
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: 7360e5d1f37ed6672f336b625b3c90d060e0a020
User & Date: drh 2008-08-22 12:30:52
Context
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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