SQLite

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.161 2008/02/12 16:52:14 drh Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.

  assert( opcode==OP_OpenWrite || opcode==OP_OpenRead );
  sqlite3TableLock(p, iDb, pTab->tnum, (opcode==OP_OpenWrite), pTab->zName);
  sqlite3VdbeAddOp3(v, opcode, iCur, pTab->tnum, iDb);
  VdbeComment((v, "%s", pTab->zName));
  sqlite3VdbeAddOp2(v, OP_SetNumColumns, iCur, pTab->nCol);
}


#if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
/*
** Evaluate a view and store its result in an ephemeral table.  The
** pWhere argument is an optional WHERE clause that restricts the
** set of rows in the view that are to be added to the ephemeral table.
*/
void sqlite3MaterializeView(
  Parse *pParse,       /* Parsing context */
  Select *pView,       /* View definition */
  Expr *pWhere,        /* Optional WHERE clause to be added */
  u32 col_mask,        /* Render only the columns in this mask. */
  int iCur             /* Cursor number for ephemerial table */
){
  SelectDest dest;
  Select *pDup;
  sqlite3 *db = pParse->db;

  pDup = sqlite3SelectDup(db, pView);
  if( pWhere ){
    SrcList *pFrom;
    
    pWhere = sqlite3ExprDup(db, pWhere);
    pFrom = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, 0, pDup, 0, 0);
    pDup = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 0, 0, 0);
  }
  sqlite3SelectMask(pParse, pDup, col_mask);
  sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur);
  sqlite3Select(pParse, pDup, &dest, 0, 0, 0, 0);
  sqlite3SelectDelete(pDup);
}
#endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */


/*
** Generate code for a DELETE FROM statement.
**
**     DELETE FROM table_wxyz WHERE a<5 AND b NOT NULL;
**                 \________/       \________________/
**                  pTabList              pWhere

  /* Allocate a cursor used to store the old.* data for a trigger.
  */
  if( triggers_exist ){ 
    oldIdx = pParse->nTab++;
  }

  /* Assign  cursor number to the table and all its indices.
  */
  assert( pTabList->nSrc==1 );
  iCur = pTabList->a[0].iCursor = pParse->nTab++;
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    pParse->nTab++;
  }







  /* Start the view context
  */
  if( isView ){
    sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
  }

    sqlite3VdbeJumpHere(v, iGoto);
  }

  /* If we are trying to delete from a view, realize that view into
  ** a ephemeral table.
  */
  if( isView ){
    sqlite3MaterializeView(pParse, pTab->pSelect, pWhere, old_col_mask, iCur);

  }

  /* Resolve the column names in the WHERE clause.
  */
  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  sNC.pSrcList = pTabList;
  if( sqlite3ExprResolveNames(&sNC, pWhere) ){

    goto delete_from_cleanup;
  }

  /* Initialize the counter of the number of rows deleted, if
  ** we are counting rows.
  */
  if( db->flags & SQLITE_CountRows ){
    memCnt = ++pParse->nMem;

/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.660 2008/02/12 16:52:14 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds

#else
# define sqlite3SafetyOn(A) 0
# define sqlite3SafetyOff(A) 0
#endif
int sqlite3SafetyCheckOk(sqlite3*);
int sqlite3SafetyCheckSickOrOk(sqlite3*);
void sqlite3ChangeCookie(Parse*, int);
void sqlite3MaterializeView(Parse*, Select*, Expr*, u32, int);

#ifndef SQLITE_OMIT_TRIGGER
  void sqlite3BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*,
                           Expr*,int, int);
  void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*);
  void sqlite3DropTrigger(Parse*, SrcList*, int);
  void sqlite3DropTriggerPtr(Parse*, Trigger*);

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.171 2008/02/12 16:52:14 drh Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */

                       pWhere);
    pWhere = 0;
    pTabList = 0;
    goto update_cleanup;
  }
#endif








  /* Start the view context
  */
  if( isView ){
    sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
  }

  /* Generate the code for triggers.

    sqlite3VdbeJumpHere(v, iGoto);
  }

  /* If we are trying to update a view, realize that view into
  ** a ephemeral table.
  */
  if( isView ){
    sqlite3MaterializeView(pParse, pTab->pSelect, pWhere,
                           old_col_mask|new_col_mask, iCur);
  }

  /* Resolve the column names in all the expressions in the
  ** WHERE clause.

  */
  if( sqlite3ExprResolveNames(&sNC, pWhere) ){
    goto update_cleanup;
  }

  /* Begin the database scan
  */
  pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0);
  if( pWInfo==0 ) goto update_cleanup;

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the sqlite3_set_authorizer() API
# and related functionality.
#
# $Id: auth.test,v 1.41 2008/02/12 16:52:14 drh Exp $
#

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

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.

  set authargs {}
  execsql {
    UPDATE v1 SET x=1 WHERE x=117
  }
  set authargs
} [list \
  SQLITE_UPDATE v1     x  main {} \

  SQLITE_INSERT v1chng {} main r2 \
  SQLITE_READ   v1     x  main r2 \
  SQLITE_READ   v1     x  main r2 \
  SQLITE_READ   t2     a  main v1 \
  SQLITE_READ   t2     b  main v1 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_READ   v1     x  main v1 \
]
do_test auth-4.4 {
  execsql {
    CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN
      INSERT INTO v1chng VALUES(OLD.x,NULL);
    END;
    SELECT * FROM v1;
  }
} {115 117}
do_test auth-4.5 {
  set authargs {}
  execsql {
    DELETE FROM v1 WHERE x=117
  }
  set authargs
} [list \
  SQLITE_DELETE v1     {} main {} \

  SQLITE_INSERT v1chng {} main r3 \
  SQLITE_READ   v1     x  main r3 \
  SQLITE_READ   t2     a  main v1 \
  SQLITE_READ   t2     b  main v1 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_READ   v1     x  main v1 \
]

} ;# ifcapable view && trigger

# Ticket #1338:  Make sure authentication works in the presence of an AS
# clause.
#
do_test auth-5.1 {

# 2008 February 12
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. Specifically,
# it tests issues relating to firing an INSTEAD OF trigger on a VIEW
# when one tries to UPDATE or DELETE from the view.  Does the WHERE
# clause of the UPDATE or DELETE statement get passed down correctly 
# into the query that manifests the view?
#
# Ticket #2938
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable {!trigger} {
  finish_test
  return
}

# Create two table containing some sample data
#
do_test triggerA-1.1 {
  db eval {
    CREATE TABLE t1(x INTEGER PRIMARY KEY, y TEXT UNIQUE);
    CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c TEXT);
  }
  set i 1
  foreach word {one two three four five six seven eight nine ten} {
    set j [expr {$i*100 + [string length $word]}]
    db eval {
       INSERT INTO t1 VALUES($i,$word);
       INSERT INTO t2 VALUES(20-$i,$j,$word);
    }
    incr i
  }
  db eval {
    SELECT count(*) FROM t1 UNION ALL SELECT count(*) FROM t2;
  }
} {10 10}

# Create views of various forms against one or both of the two tables.
#
do_test triggerA-1.2 {
  db eval {
     CREATE VIEW v1 AS SELECT y, x FROM t1;
     SELECT * FROM v1 ORDER BY 1;
  }
} {eight 8 five 5 four 4 nine 9 one 1 seven 7 six 6 ten 10 three 3 two 2}
do_test triggerA-1.3 {
  db eval {
     CREATE VIEW v2 AS SELECT x, y FROM t1 WHERE y GLOB '*e*';
     SELECT * FROM v2 ORDER BY 1;
  }
} {1 one 3 three 5 five 7 seven 8 eight 9 nine 10 ten}
do_test triggerA-1.4 {
  db eval {
     CREATE VIEW v3 AS
       SELECT CAST(x AS TEXT) AS c1 FROM t1 UNION SELECT y FROM t1;
     SELECT * FROM v3 ORDER BY c1;
  }
} {1 10 2 3 4 5 6 7 8 9 eight five four nine one seven six ten three two}
do_test triggerA-1.5 {
  db eval {
     CREATE VIEW v4 AS
        SELECT CAST(x AS TEXT) AS c1 FROM t1
        UNION SELECT y FROM t1 WHERE x BETWEEN 3 and 5;
     SELECT * FROM v4 ORDER BY 1;
  }
} {1 10 2 3 4 5 6 7 8 9 five four three}
do_test triggerA-1.6 {
  db eval {
     CREATE VIEW v5 AS SELECT x, b FROM t1, t2 WHERE y=c;
     SELECT * FROM v5;
  }
} {1 103 2 203 3 305 4 404 5 504 6 603 7 705 8 805 9 904 10 1003}

# Create INSTEAD OF triggers on the views.  Run UPDATE and DELETE statements
# using those triggers.  Verify correct operation.
#
do_test triggerA-2.1 {
  db eval {
     CREATE TABLE result2(a,b);
     CREATE TRIGGER r1d INSTEAD OF DELETE ON v1 BEGIN
       INSERT INTO result2(a,b) VALUES(old.y, old.x);
     END;
     DELETE FROM v1 WHERE x=5;
     SELECT * FROM result2;
  }
} {five 5}
do_test triggerA-2.2 {
  db eval {
     CREATE TABLE result4(a,b,c,d);
     CREATE TRIGGER r1u INSTEAD OF UPDATE ON v1 BEGIN
       INSERT INTO result4(a,b,c,d) VALUES(old.y, old.x, new.y, new.x);
     END;
     UPDATE v1 SET y=y||'-extra' WHERE x BETWEEN 3 AND 5;
     SELECT * FROM result4 ORDER BY a;
  }
} {five 5 five-extra 5 four 4 four-extra 4 three 3 three-extra 3}


do_test triggerA-2.3 {
  db eval {
     DELETE FROM result2;
     CREATE TRIGGER r2d INSTEAD OF DELETE ON v2 BEGIN
       INSERT INTO result2(a,b) VALUES(old.y, old.x);
     END;
     DELETE FROM v2 WHERE x=5;
     SELECT * FROM result2;
  }
} {five 5}
do_test triggerA-2.4 {
  db eval {
     DELETE FROM result4;
     CREATE TRIGGER r2u INSTEAD OF UPDATE ON v2 BEGIN
       INSERT INTO result4(a,b,c,d) VALUES(old.y, old.x, new.y, new.x);
     END;
     UPDATE v2 SET y=y||'-extra' WHERE x BETWEEN 3 AND 5;
     SELECT * FROM result4 ORDER BY a;
  }
} {five 5 five-extra 5 three 3 three-extra 3}


do_test triggerA-2.5 {
  db eval {
     CREATE TABLE result1(a);
     CREATE TRIGGER r3d INSTEAD OF DELETE ON v3 BEGIN
       INSERT INTO result1(a) VALUES(old.c1);
     END;
     DELETE FROM v3 WHERE c1 BETWEEN '8' AND 'eight';
     SELECT * FROM result1 ORDER BY a;
  }
} {8 9 eight}
do_test triggerA-2.6 {
  db eval {
     DELETE FROM result2;
     CREATE TRIGGER r3u INSTEAD OF UPDATE ON v3 BEGIN
       INSERT INTO result2(a,b) VALUES(old.c1, new.c1);
     END;
     UPDATE v3 SET c1 = c1 || '-extra' WHERE c1 BETWEEN '8' and 'eight';
     SELECT * FROM result2 ORDER BY a;
  }
} {8 8-extra 9 9-extra eight eight-extra}


do_test triggerA-2.7 {
  db eval {
     DELETE FROM result1;
     CREATE TRIGGER r4d INSTEAD OF DELETE ON v4 BEGIN
       INSERT INTO result1(a) VALUES(old.c1);
     END;
     DELETE FROM v4 WHERE c1 BETWEEN '8' AND 'eight';
     SELECT * FROM result1 ORDER BY a;
  }
} {8 9}
do_test triggerA-2.8 {
  db eval {
     DELETE FROM result2;
     CREATE TRIGGER r4u INSTEAD OF UPDATE ON v4 BEGIN
       INSERT INTO result2(a,b) VALUES(old.c1, new.c1);
     END;
     UPDATE v4 SET c1 = c1 || '-extra' WHERE c1 BETWEEN '8' and 'eight';
     SELECT * FROM result2 ORDER BY a;
  }
} {8 8-extra 9 9-extra}


do_test triggerA-2.9 {
  db eval {
     DELETE FROM result2;
     CREATE TRIGGER r5d INSTEAD OF DELETE ON v5 BEGIN
       INSERT INTO result2(a,b) VALUES(old.x, old.b);
     END;
     DELETE FROM v5 WHERE x=5;
     SELECT * FROM result2;
  }
} {5 504}
do_test triggerA-2.10 {
  db eval {
     DELETE FROM result4;
     CREATE TRIGGER r5u INSTEAD OF UPDATE ON v5 BEGIN
       INSERT INTO result4(a,b,c,d) VALUES(old.x, old.b, new.x, new.b);
     END;
     UPDATE v5 SET b = b+9900000 WHERE x BETWEEN 3 AND 5;
     SELECT * FROM result4 ORDER BY a;
  }
} {3 305 3 9900305 4 404 4 9900404 5 504 5 9900504}

# Only run the reamining tests if memory debugging is turned on.
#
ifcapable !memdebug {
   puts "Skipping triggerA malloc tests: not compiled with -DSQLITE_MEMDEBUG..."
   finish_test
   return
}
source $testdir/malloc_common.tcl

# Save a copy of the current database configuration.
#
db close
file delete -force test.db-triggerA
file copy test.db test.db-triggerA
sqlite3 db test.db

# Run malloc tests on the INSTEAD OF trigger firing.
#
do_malloc_test triggerA-3 -tclprep {
  db close
  file delete -force test.db test.db-journal
  file copy -force test.db-triggerA test.db
  sqlite3 db test.db
  sqlite3_extended_result_codes db 1  
  db eval {SELECT * FROM v5; -- warm up the cache}
} -sqlbody {
   DELETE FROM v5 WHERE x=5;
   UPDATE v5 SET b=b+9900000 WHERE x BETWEEN 3 AND 5;
}

# Clean up the saved database copy.
#
file delete -force test.db-triggerA

finish_test