SQLite4  Check-in [77b33bff0a]

  Token *pEnd,            /* The final ')' token in the CREATE TABLE */
  Select *pSelect         /* Select from a "CREATE ... AS SELECT" */
){
  Table *p;
  sqlite4 *db = pParse->db;
  int iDb;
  int iPkRoot = 0;                /* Root page of primary key index */


  if( (pEnd==0 && pSelect==0) || db->mallocFailed ){
    return;
  }
  p = pParse->pNewTable;
  if( p==0 ) return;

  assert( !db->init.busy || !pSelect );
  iDb = sqlite4SchemaToIndex(db, p->pSchema);

  if( !IsView(p) ){
    Index *pPk;                   /* PRIMARY KEY index of table p */
    if( 0==(p->tabFlags & TF_HasPrimaryKey) ){
      /* If no explicit PRIMARY KEY has been created, add an implicit 
      ** primary key here.  An implicit primary key works the way "rowid" 
      ** did in SQLite 3.  */
      addImplicitPrimaryKey(pParse, p, iDb);
    }
    pPk = sqlite4FindPrimaryKey(p, 0);
    assert( pPk || pParse->nErr || db->mallocFailed );
    if( pPk ) iPkRoot = pPk->tnum;
  }

#ifndef SQLITE_OMIT_CHECK
  /* Resolve names in all CHECK constraint expressions.
  */
  if( p->pCheck ){
    SrcList sSrc;                   /* Fake SrcList for pParse->pNewTable */
    NameContext sNC;                /* Name context for pParse->pNewTable */

**                  pTabList              pWhere
*/
void sqlite4DeleteFrom(
  Parse *pParse,         /* The parser context */
  SrcList *pTabList,     /* The table from which we should delete things */
  Expr *pWhere           /* The WHERE clause.  May be null */
){
  sqlite4 *db = pParse->db;       /* Main database structure */
  Vdbe *v;                        /* The virtual database engine */
  Table *pTab;                    /* Table to delete from */
  const char *zDb;                /* Name of database holding pTab */






  AuthContext sContext;           /* Authorization context */
  NameContext sNC;                /* Name context to resolve WHERE expression */
  int iDb;                        /* Database number */
  int rcauth;                     /* Value returned by authorization callback */
  int iCur;                       /* Cursor number used by where.c */


  Trigger *pTrigger;              /* List of triggers, or NULL */


  memset(&sContext, 0, sizeof(sContext));
  memset(&sNC, 0, sizeof(sNC));

  db = pParse->db;
  if( pParse->nErr || db->mallocFailed ){
    goto delete_from_cleanup;
  }
  assert( pTabList->nSrc==1 );

  /* Locate the table which we want to delete. If it is a view, make sure
  ** that the column names are initialized. */


  pTab = sqlite4SrcListLookup(pParse, pTabList);
  if( pTab==0 )  goto delete_from_cleanup;
  iDb = sqlite4SchemaToIndex(db, pTab->pSchema);
  zDb = db->aDb[iDb].zName;
  assert( iDb<db->nDb );

  /* Figure out if there are any triggers */


  pTrigger = sqlite4TriggersExist(pParse, pTab, TK_DELETE, 0, 0);










  /* If pTab is really a view, make sure it has been initialized. */
  if( sqlite4ViewGetColumnNames(pParse, pTab) ) goto delete_from_cleanup;


  /* Check the table is not read-only. A table is read-only if it is one
  ** of the built-in system tables (e.g. sqlite_master, sqlite_stat) or
  ** if it is a view and there are no INSTEAD OF triggers to handle the 
  ** delete.  */
  if( sqlite4IsReadOnly(pParse, pTab, pTrigger!=0) ) goto delete_from_cleanup;


  assert( !IsView(pTab) || pTrigger );
  assert( !IsView(pTab) || pTab->pIndex==0 );

  /* Invoke the authorization callback */



  rcauth = sqlite4AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb);
  assert( rcauth==SQLITE_OK || rcauth==SQLITE_DENY || rcauth==SQLITE_IGNORE );
  if( rcauth==SQLITE_DENY ){
    goto delete_from_cleanup;
  }

  /* Assign a cursor number to the table or view this statement is 
  ** deleting from. If pTab is actually a view, this will be used as the




  ** ephemeral table cursor. 

  **
  ** Or, if this is a real table, it is the number of a read-only cursor 
  ** used by where.c to iterate through those records that match the WHERE 
  ** clause supplied by the user. This is a separate cursor from the array
  ** of read-write cursors used to delete entries from each of the tables
  ** indexes.  */
  pTabList->a[0].iCursor = iCur = pParse->nTab++;


  /* Begin generating code */

  v = sqlite4GetVdbe(pParse);

  if( v==0 ) goto delete_from_cleanup;

  if( pParse->nested==0 ) sqlite4VdbeCountChanges(v);
  sqlite4BeginWriteOperation(pParse, 1, iDb);

  /* If we are trying to delete from a view, realize that view into
  ** a ephemeral table.  */


  if( IsView(pTab) ){
    sqlite4AuthContextPush(pParse, &sContext, pTab->zName);
    sqlite4MaterializeView(pParse, pTab, pWhere, iCur);
  }


  /* Resolve the column names in the WHERE clause. This has to come after


  ** the call to sqlite4MaterializeView() above.  */
  sNC.pParse = pParse;
  sNC.pSrcList = pTabList;
  if( sqlite4ResolveExprNames(&sNC, pWhere) ){
    goto delete_from_cleanup;
  }

#ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table. Prior to version 3.6.5,
  ** this optimization caused the row change count (the value returned by 
  ** API function sqlite4_count_changes) to be set incorrectly.  */
  if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) 
   && 0==sqlite4FkRequired(pParse, pTab, 0)
  ){
    Index *pIdx;                  /* For looping over indices of the table */
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite4VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
    }
  }else
#endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
  /* The usual case: There is a WHERE clause so we have to scan through
  ** the table and pick which records to delete.
  */
  {
    WhereInfo *pWInfo;            /* Information about the WHERE clause */
    int baseCur = 0;
    int regSet = ++pParse->nMem;  /* Register for rowset of rows to delete */
    int regKey = ++pParse->nMem;  /* Used for storing row keys */
    int addrTop;                  /* Instruction (KeySetRead) at top of loop */

    /* Query the table for all rows that match the WHERE clause. Store the
    ** PRIMARY KEY for each matching row in the KeySet object in register
    ** regSet. After the scan is complete, the VM will loop through the set 

    if( pWInfo==0 ) goto delete_from_cleanup;
    sqlite4VdbeAddOp2(v, OP_RowKey, iCur, regKey);
    sqlite4VdbeAddOp2(v, OP_KeySetAdd, regSet, regKey);
    sqlite4WhereEnd(pWInfo);

    /* Unless this is a view, open cursors for all indexes on the table
    ** from which we are deleting.  */
    if( !IsView(pTab) ){
      baseCur = pParse->nTab;
      sqlite4OpenAllIndexes(pParse, pTab, baseCur, OP_OpenWrite);
    }

    addrTop = sqlite4VdbeAddOp3(v, OP_KeySetRead, regSet, 0, regKey);

    /* Delete the row */
#ifndef SQLITE_OMIT_VIRTUALTABLE
    if( IsVirtual(pTab) ){
      const char *pVTab = (const char *)sqlite4GetVTable(db, pTab);
      sqlite4VtabMakeWritable(pParse, pTab);
      sqlite4VdbeAddOp4(v, OP_VUpdate, 0, 1, iRowid, pVTab, P4_VTAB);
      sqlite4VdbeChangeP5(v, OE_Abort);
      sqlite4MayAbort(pParse);
    }else
#endif
    {

      sqlite4GenerateRowDelete(
          pParse, pTab, baseCur, regKey, pParse->nested==0, pTrigger, OE_Default
      );
    }

    /* End of the delete loop */
    sqlite4VdbeAddOp2(v, OP_Goto, 0, addrTop);
    sqlite4VdbeJumpHere(v, addrTop);

    /* Close all open cursors */
    sqlite4CloseAllIndexes(pParse, pTab, baseCur);
  }

  /* Update the sqlite_sequence table by storing the content of the
  ** maximum rowid counter values recorded while inserting into
  ** autoincrement tables.
  */
  if( pParse->nested==0 && pParse->pTriggerTab==0 ){
    sqlite4AutoincrementEnd(pParse);
  }

delete_from_cleanup:
  sqlite4AuthContextPop(&sContext);
  sqlite4SrcListDelete(db, pTabList);
  sqlite4ExprDelete(db, pWhere);
  return;
}










/*
** This routine generates VDBE code that causes a single row of a
** single table to be deleted.
**
** The VDBE must be in a particular state when this routine is called.
** These are the requirements:

    ** Or, if the table contains more than 32 columns and at least one of
    ** the columns following the 32nd is required, set mask to 0xffffffff.  */
    mask = sqlite4TriggerColmask(
        pParse, pTrigger, 0, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onconf
    );
    mask |= sqlite4FkOldmask(pParse, pTab);

    /* Allocate an array of (nCol+1) registers, where nCol is the number

    ** of columns in the table. 
    **

    ** If the table has an implicit PK, the first register in the array 
    ** contains the rowid. Otherwise, its contents are undefined. The 


    ** remaining registers contain the OLD.* column values, in order. */
    regOld = pParse->nMem+1;
    pParse->nMem += (pTab->nCol+1);
    for(iCol=0; iCol<pTab->nCol; iCol++){
      if( mask==0xffffffff || mask&(1<<iCol) ){
        sqlite4ExprCodeGetColumnOfTable(v, pTab, iPkCsr, iCol, regOld+iCol+1);
      }
    }
    assert( (pPk==0)==IsView(pTab) );
    if( pPk && pPk->aiColumn[0]<0 ){
      sqlite4VdbeAddOp2(v, OP_Rowid, iPkCsr, regOld);
    }

    /* Invoke BEFORE DELETE trigger programs. */
    sqlite4CodeRowTrigger(pParse, pTrigger, 
        TK_DELETE, 0, TRIGGER_BEFORE, pTab, regOld, onconf, iLabel
    );

    ** are not violated by deleting this row.  */
    sqlite4FkCheck(pParse, pTab, regOld+1, 0);
  }

  /* Delete the index and table entries. Skip this step if pTab is really
  ** a view (in which case the only effect of the DELETE statement is to
  ** fire the INSTEAD OF triggers).  */ 
  if( !IsView(pTab) ){
    sqlite4GenerateRowIndexDelete(pParse, pTab, baseCur, 0);





  }

  /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
  ** handle rows (possibly in other tables) that refer via a foreign key
  ** to the row just deleted. This is a no-op if there are no configured
  ** foreign keys that use this table as a parent table.  */ 
  sqlite4FkActions(pParse, pTab, 0, regOld+1);

  }

  /* Set bImplicitPK to true for an implicit PRIMARY KEY, or false otherwise.
  ** Also set pPk to point to the primary key, and iPk to the cursor offset
  ** of the primary key cursor (i.e. so that the cursor opened on the primary
  ** key index is VDBE cursor (baseCur+iPk).  */
  pPk = sqlite4FindPrimaryKey(pTab, &iPk);
  assert( (pPk==0)==IsView(pTab) );
  bImplicitPK = (pPk && pPk->aiColumn[0]==-1);

  /* Figure out if we have any triggers and if the table being
  ** inserted into is a view. */
#ifndef SQLITE_OMIT_TRIGGER
  pTrigger = sqlite4TriggersExist(pParse, pTab, TK_INSERT, 0, &tmask);
  isView = pTab->pSelect!=0;
#else

  int baseCur
){
  int i;
  Index *pIdx;
  Vdbe *v;

  assert( pTab->pIndex==0 || IsVirtual(pTab)==0 );

  assert( pTab->pIndex==0 || IsView(pTab)==0 );


  v = sqlite4GetVdbe(pParse);
  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
    sqlite4VdbeAddOp1(v, OP_Close, baseCur+i);
  }
}

        Table *pTab = (Table *)sqliteHashData(x);
        int addrRewind;
        int nIdx = 0;
        int iPkCsr;
        Index *pPk;
        int iCsr;

        /* Do nothing for views */
        if( IsView(pTab) ) continue;

        /* Open all indexes for table pTab. */
        for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          if( pIdx->eIndexType==SQLITE_INDEX_PRIMARYKEY ){
            pPk = pIdx;
            iPkCsr = nIdx+baseCsr;
          }
          nIdx++;

#  define IsVirtual(X)      (((X)->tabFlags & TF_Virtual)!=0)
#  define IsHiddenColumn(X) ((X)->isHidden)
#else
#  define IsVirtual(X)      0
#  define IsHiddenColumn(X) 0
#endif

/* Test to see if a table is actually a view. */
#ifndef SQLITE_OMIT_VIEW
#  define IsView(X)         ((X)->pSelect!=0)
#else
#  define IsView(X)         0
#endif

/*
** Each foreign key constraint is an instance of the following structure.
**
** A foreign key is associated with two tables.  The "from" table is
** the table that contains the REFERENCES clause that creates the foreign
** key.  The "to" table is the table that is named in the REFERENCES clause.
** Consider this example:

void sqlite4RegisterGlobalFunctions(void);
int sqlite4SafetyCheckOk(sqlite4*);
int sqlite4SafetyCheckSickOrOk(sqlite4*);
void sqlite4ChangeCookie(Parse*, int);

#if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
void sqlite4MaterializeView(Parse*, Table*, Expr*, int);
#else
# define sqlite4MaterializeView(w,x,y,z)
#endif

#ifndef SQLITE_OMIT_TRIGGER
  void sqlite4BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*,
                           Expr*,int, int);
  void sqlite4FinishTrigger(Parse*, TriggerStep*, Token*);
  void sqlite4DropTrigger(Parse*, SrcList*, int);

  int regOldKey;                  /* Register containing the original PK */

  int regNewRowid;       /* The new rowid */
  int regNew;            /* Content of the NEW.* table in triggers */

  int regOld = 0;                 /* Content of OLD.* table in triggers */
  int regKeySet = 0;              /* Register containing KeySet object */
  Index *pPk = 0;                 /* The primary key index of this table */
  int iPk = 0;                    /* Offset of primary key in aRegIdx[] */
  int bChngPk = 0;                /* True if any PK columns are updated */
  int bOpenAll = 0;               /* True if all indexes were opened */
  int bImplicitPk = 0;            /* True if pTab has an implicit PK */
  int regOldTr = 0;               /* Content of OLD.* table including IPK */
  int regNewTr = 0;               /* Content of NEW.* table including IPK */

  memset(&sContext, 0, sizeof(sContext));
  db = pParse->db;
  if( pParse->nErr || db->mallocFailed ){
    goto update_cleanup;
  }
  assert( pSrc->nSrc==1 );

  /* Locate and analyze the table to be updated. This block sets:
  **
  **   pTab
  **   iDb
  **   pPk
  **   bImplicitPk
  */
  pTab = sqlite4SrcListLookup(pParse, pSrc);
  if( pTab==0 ) goto update_cleanup;
  iDb = sqlite4SchemaToIndex(pParse->db, pTab->pSchema);
  if( IsView(pTab)==0 ){
    pPk = sqlite4FindPrimaryKey(pTab, &iPk);
    bImplicitPk = (pPk->aiColumn[0]<0);
  }

  /* Figure out if we have any triggers and if the table being
  ** updated is a view.
  */
#ifndef SQLITE_OMIT_TRIGGER
  pTrigger = sqlite4TriggersExist(pParse, pTab, TK_UPDATE, pChanges, &tmask);
  isView = pTab->pSelect!=0;

  for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;

  /* Allocate a cursors for the main database table and for all indices.
  ** The index cursors might not be used, but if they are used they
  ** need to occur right after the database cursor.  So go ahead and
  ** allocate enough space, just in case.  */
  iCur = pParse->nTab;
  pSrc->a[0].iCursor = iCur+iPk;
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    pParse->nTab++;
  }
  if( IsView(pTab) ) pParse->nTab++;

  /* Initialize the name-context */
  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  sNC.pSrcList = pSrc;

  /* Resolve the column names in all the expressions of the of the UPDATE 

      sqlite4ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName);
      pParse->checkSchema = 1;
      goto update_cleanup;
    }
    aXRef[j] = i;

    /* Check if this column is part of the primary key. If so, set bChngPk. */
    if( !IsView(pTab) ){
      for(iPkCol=0; iPkCol<pPk->nColumn; iPkCol++){
        if( pPk->aiColumn[iPkCol]==j ) bChngPk = 1;
      }
    }

#ifndef SQLITE_OMIT_AUTHORIZATION
    {
      int rc;
      rc = sqlite4AuthCheck(pParse, SQLITE_UPDATE, pTab->zName,
                           pTab->aCol[j].zName, db->aDb[iDb].zName);

#-------------------------------------------------------------------------
reset_db
do_execsql_test 37.1 {
  CREATE TABLE t1(a PRIMARY KEY, b);
  INSERT INTO t1 VALUES('x', 'xxx');
  INSERT INTO t1 VALUES('y', 'yyy');
}

do_execsql_test 37.2 {
  BEGIN;
    DELETE FROM t1 WHERE a='y';
    INSERT INTO t1 VALUES('y', 'yyy');
    DELETE FROM t1 WHERE a='y';
    INSERT INTO t1 VALUES('y', 'yyy');
  ROLLBACK;
}
 
#-------------------------------------------------------------------------
reset_db
do_execsql_test 38.1 {
  CREATE TABLE t1(a, b);
  CREATE TABLE log(a, b);

  -- INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(3, 4);

  CREATE VIEW v1 AS SELECT a, b FROM t1;
  CREATE TRIGGER tr1 INSTEAD OF DELETE ON v1 BEGIN
    INSERT INTO log VALUES(old.b, old.a);
  END;
}

do_execsql_test 38.2 {
  DELETE FROM v1 WHERE a = 3;
  SELECT * FROM log;
} {4 3}


#proc populate_t1 {} {
#  db eval {
#    INSERT INTO t1(a, b) VALUES(4, 'four');
#    INSERT INTO t1(a, b) VALUES(9, 'nine');
#    INSERT INTO t1(a, b) VALUES(5, 'five');
#    INSERT INTO t1(a, b) VALUES(1, 'one');

  execsql {
  CREATE TABLE ab(a, b);
  CREATE TABLE cd(c, d);
  INSERT INTO ab VALUES (1, 2);
  INSERT INTO ab VALUES (0, 0);
  INSERT INTO cd VALUES (3, 4);


  CREATE TABLE tlog(olda, oldb, oldc, oldd, newa, newb, newc, newd);

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

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

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

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



do_execsql_test trigger2-7.2.1 { UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1 }
do_execsql_test trigger2.7.2.2 { SELECT * FROM tlog } {
  1 2 3 4 100 25 3 4 
  1 2 3 4 100 25 3 4 
}
do_execsql_test trigger2-7.2.3 { DELETE FROM abcd WHERE a = 1 }

do_execsql_test trigger2.7.2.4 { SELECT * FROM tlog } {
  1 2 3 4 100 25 3 4 
  1 2 3 4 100 25 3 4 
  1 2 3 4 0 0 0 0 
  1 2 3 4 0 0 0 0
}
do_execsql_test trigger2-7.2.5 { INSERT INTO abcd VALUES(10, 20, 30, 40) }
do_execsql_test trigger2.7.2.6 { SELECT * FROM tlog } {
  1 2 3 4 100 25 3 4 
  1 2 3 4 100 25 3 4 
  1 2 3 4 0 0 0 0 
  1 2 3 4 0 0 0 0
  0 0 0 0 10 20 30 40 
  0 0 0 0 10 20 30 40 
}

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

do_test trigger2-8.1 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);
    CREATE VIEW v1 AS