SQLite

Check-in [d5d3998118]
Login

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

Overview
Comment:Check in implementation of foreign key constraints.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d5d399811876391642937edeb9e8434dd9e356f5
User & Date: dan 2009-09-19 17:00:31.000
Context
2009-09-19
17:59
Add fkey.c to the autoconf and amalgamation build systems. (check-in: aab7a4b3c9 user: dan tags: trunk)
17:00
Check in implementation of foreign key constraints. (check-in: d5d3998118 user: dan tags: trunk)
2009-09-17
00:41
When coding a trigger, assume that the "oldmask" requires all columns until we know otherwise. That pessimistic assumption assures that all necessary parameters are available on a cascading delete trigger. Ticket [e25d9ea771f] (check-in: 03e464be51 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to main.mk.
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
TCCX += -I$(TOP)/ext/rtree -I$(TOP)/ext/icu -I$(TOP)/ext/fts3
TCCX += -I$(TOP)/ext/async

# Object files for the SQLite library.
#
LIBOBJ+= alter.o analyze.o attach.o auth.o \
         backup.o bitvec.o btmutex.o btree.o build.o \
         callback.o complete.o date.o delete.o expr.o fault.o \
         fts3.o fts3_expr.o fts3_hash.o fts3_icu.o fts3_porter.o \
         fts3_tokenizer.o fts3_tokenizer1.o \
         func.o global.o hash.o \
         icu.o insert.o journal.o legacy.o loadext.o \
         main.o malloc.o mem0.o mem1.o mem2.o mem3.o mem5.o \
         memjournal.o \
         mutex.o mutex_noop.o mutex_os2.o mutex_unix.o mutex_w32.o \







|







48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
TCCX += -I$(TOP)/ext/rtree -I$(TOP)/ext/icu -I$(TOP)/ext/fts3
TCCX += -I$(TOP)/ext/async

# Object files for the SQLite library.
#
LIBOBJ+= alter.o analyze.o attach.o auth.o \
         backup.o bitvec.o btmutex.o btree.o build.o \
         callback.o complete.o date.o delete.o expr.o fault.o fkey.o \
         fts3.o fts3_expr.o fts3_hash.o fts3_icu.o fts3_porter.o \
         fts3_tokenizer.o fts3_tokenizer1.o \
         func.o global.o hash.o \
         icu.o insert.o journal.o legacy.o loadext.o \
         main.o malloc.o mem0.o mem1.o mem2.o mem3.o mem5.o \
         memjournal.o \
         mutex.o mutex_noop.o mutex_os2.o mutex_unix.o mutex_w32.o \
Changes to src/build.c.
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
** This routine just deletes the data structure.  It does not unlink
** the table data structure from the hash table.  But it does destroy
** memory structures of the indices and foreign keys associated with 
** the table.
*/
void sqlite3DeleteTable(Table *pTable){
  Index *pIndex, *pNext;
  FKey *pFKey, *pNextFKey;
  sqlite3 *db;

  if( pTable==0 ) return;
  db = pTable->dbMem;
  testcase( db==0 );

  /* Do not delete the table until the reference count reaches zero. */







<







500
501
502
503
504
505
506

507
508
509
510
511
512
513
** This routine just deletes the data structure.  It does not unlink
** the table data structure from the hash table.  But it does destroy
** memory structures of the indices and foreign keys associated with 
** the table.
*/
void sqlite3DeleteTable(Table *pTable){
  Index *pIndex, *pNext;

  sqlite3 *db;

  if( pTable==0 ) return;
  db = pTable->dbMem;
  testcase( db==0 );

  /* Do not delete the table until the reference count reaches zero. */
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
  */
  for(pIndex = pTable->pIndex; pIndex; pIndex=pNext){
    pNext = pIndex->pNext;
    assert( pIndex->pSchema==pTable->pSchema );
    sqlite3DeleteIndex(pIndex);
  }

#ifndef SQLITE_OMIT_FOREIGN_KEY
  /* Delete all foreign keys associated with this table. */
  for(pFKey=pTable->pFKey; pFKey; pFKey=pNextFKey){
    pNextFKey = pFKey->pNextFrom;
    sqlite3DbFree(db, pFKey);
  }
#endif

  /* Delete the Table structure itself.
  */
  sqliteResetColumnNames(pTable);
  sqlite3DbFree(db, pTable->zName);
  sqlite3DbFree(db, pTable->zColAff);
  sqlite3SelectDelete(db, pTable->pSelect);







<
|
<
<
|
<
<







521
522
523
524
525
526
527

528


529


530
531
532
533
534
535
536
  */
  for(pIndex = pTable->pIndex; pIndex; pIndex=pNext){
    pNext = pIndex->pNext;
    assert( pIndex->pSchema==pTable->pSchema );
    sqlite3DeleteIndex(pIndex);
  }


  /* Delete any foreign keys attached to this table. */


  sqlite3FkDelete(pTable);



  /* Delete the Table structure itself.
  */
  sqliteResetColumnNames(pTable);
  sqlite3DbFree(db, pTable->zName);
  sqlite3DbFree(db, pTable->zColAff);
  sqlite3SelectDelete(db, pTable->pSelect);
1170
1171
1172
1173
1174
1175
1176

1177



1178
1179
1180
1181
1182
1183
1184
    pTab->tabFlags |= autoInc*TF_Autoincrement;
  }else if( autoInc ){
#ifndef SQLITE_OMIT_AUTOINCREMENT
    sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an "
       "INTEGER PRIMARY KEY");
#endif
  }else{

    sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0, sortOrder, 0);



    pList = 0;
  }

primary_key_exit:
  sqlite3ExprListDelete(pParse->db, pList);
  return;
}







>
|
>
>
>







1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
    pTab->tabFlags |= autoInc*TF_Autoincrement;
  }else if( autoInc ){
#ifndef SQLITE_OMIT_AUTOINCREMENT
    sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an "
       "INTEGER PRIMARY KEY");
#endif
  }else{
    Index *p;
    p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0, sortOrder, 0);
    if( p ){
      p->autoIndex = 2;
    }
    pList = 0;
  }

primary_key_exit:
  sqlite3ExprListDelete(pParse->db, pList);
  return;
}
2149
2150
2151
2152
2153
2154
2155

2156
2157
2158
2159
2160
2161
2162
  Token *pTo,          /* Name of the other table */
  ExprList *pToCol,    /* Columns in the other table */
  int flags            /* Conflict resolution algorithms. */
){
  sqlite3 *db = pParse->db;
#ifndef SQLITE_OMIT_FOREIGN_KEY
  FKey *pFKey = 0;

  Table *p = pParse->pNewTable;
  int nByte;
  int i;
  int nCol;
  char *z;

  assert( pTo!=0 );







>







2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
  Token *pTo,          /* Name of the other table */
  ExprList *pToCol,    /* Columns in the other table */
  int flags            /* Conflict resolution algorithms. */
){
  sqlite3 *db = pParse->db;
#ifndef SQLITE_OMIT_FOREIGN_KEY
  FKey *pFKey = 0;
  FKey *pNextTo;
  Table *p = pParse->pNewTable;
  int nByte;
  int i;
  int nCol;
  char *z;

  assert( pTo!=0 );
2226
2227
2228
2229
2230
2231
2232










2233
2234
2235
2236
2237
2238
2239
      z += n+1;
    }
  }
  pFKey->isDeferred = 0;
  pFKey->deleteConf = (u8)(flags & 0xff);
  pFKey->updateConf = (u8)((flags >> 8 ) & 0xff);
  pFKey->insertConf = (u8)((flags >> 16 ) & 0xff);











  /* Link the foreign key to the table as the last step.
  */
  p->pFKey = pFKey;
  pFKey = 0;

fk_end:







>
>
>
>
>
>
>
>
>
>







2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
      z += n+1;
    }
  }
  pFKey->isDeferred = 0;
  pFKey->deleteConf = (u8)(flags & 0xff);
  pFKey->updateConf = (u8)((flags >> 8 ) & 0xff);
  pFKey->insertConf = (u8)((flags >> 16 ) & 0xff);

  pNextTo = (FKey *)sqlite3HashInsert(&p->pSchema->fkeyHash, 
      pFKey->zTo, sqlite3Strlen30(pFKey->zTo), (void *)pFKey
  );
  if( pNextTo==pFKey ) goto fk_end;
  if( pNextTo ){
    assert( pNextTo->pPrevTo==0 );
    pFKey->pNextTo = pNextTo;
    pNextTo->pPrevTo = pFKey;
  }

  /* Link the foreign key to the table as the last step.
  */
  p->pFKey = pFKey;
  pFKey = 0;

fk_end:
2346
2347
2348
2349
2350
2351
2352




2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365

2366
2367
2368
2369
2370
2371
2372
** UNIQUE constraint.  If pTable and pIndex are NULL, use pParse->pNewTable
** as the table to be indexed.  pParse->pNewTable is a table that is
** currently being constructed by a CREATE TABLE statement.
**
** pList is a list of columns to be indexed.  pList will be NULL if this
** is a primary key or unique-constraint on the most recent column added
** to the table currently under construction.  




*/
void sqlite3CreateIndex(
  Parse *pParse,     /* All information about this parse */
  Token *pName1,     /* First part of index name. May be NULL */
  Token *pName2,     /* Second part of index name. May be NULL */
  SrcList *pTblName, /* Table to index. Use pParse->pNewTable if 0 */
  ExprList *pList,   /* A list of columns to be indexed */
  int onError,       /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  Token *pStart,     /* The CREATE token that begins this statement */
  Token *pEnd,       /* The ")" that closes the CREATE INDEX statement */
  int sortOrder,     /* Sort order of primary key when pList==NULL */
  int ifNotExist     /* Omit error if index already exists */
){

  Table *pTab = 0;     /* Table to be indexed */
  Index *pIndex = 0;   /* The index to be created */
  char *zName = 0;     /* Name of the index */
  int nName;           /* Number of characters in zName */
  int i, j;
  Token nullId;        /* Fake token for an empty ID list */
  DbFixer sFix;        /* For assigning database names to pTable */







>
>
>
>

|











>







2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
** UNIQUE constraint.  If pTable and pIndex are NULL, use pParse->pNewTable
** as the table to be indexed.  pParse->pNewTable is a table that is
** currently being constructed by a CREATE TABLE statement.
**
** pList is a list of columns to be indexed.  pList will be NULL if this
** is a primary key or unique-constraint on the most recent column added
** to the table currently under construction.  
**
** If the index is created successfully, return a pointer to the new Index
** structure. This is used by sqlite3AddPrimaryKey() to mark the index
** as the tables primary key (Index.autoIndex==2).
*/
Index *sqlite3CreateIndex(
  Parse *pParse,     /* All information about this parse */
  Token *pName1,     /* First part of index name. May be NULL */
  Token *pName2,     /* Second part of index name. May be NULL */
  SrcList *pTblName, /* Table to index. Use pParse->pNewTable if 0 */
  ExprList *pList,   /* A list of columns to be indexed */
  int onError,       /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  Token *pStart,     /* The CREATE token that begins this statement */
  Token *pEnd,       /* The ")" that closes the CREATE INDEX statement */
  int sortOrder,     /* Sort order of primary key when pList==NULL */
  int ifNotExist     /* Omit error if index already exists */
){
  Index *pRet = 0;     /* Pointer to return */
  Table *pTab = 0;     /* Table to be indexed */
  Index *pIndex = 0;   /* The index to be created */
  char *zName = 0;     /* Name of the index */
  int nName;           /* Number of characters in zName */
  int i, j;
  Token nullId;        /* Fake token for an empty ID list */
  DbFixer sFix;        /* For assigning database names to pTable */
2794
2795
2796
2797
2798
2799
2800

2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
      Index *pOther = pTab->pIndex;
      while( pOther->pNext && pOther->pNext->onError!=OE_Replace ){
        pOther = pOther->pNext;
      }
      pIndex->pNext = pOther->pNext;
      pOther->pNext = pIndex;
    }

    pIndex = 0;
  }

  /* Clean up before exiting */
exit_create_index:
  if( pIndex ){
    sqlite3_free(pIndex->zColAff);
    sqlite3DbFree(db, pIndex);
  }
  sqlite3ExprListDelete(db, pList);
  sqlite3SrcListDelete(db, pTblName);
  sqlite3DbFree(db, zName);
  return;
}

/*
** Fill the Index.aiRowEst[] array with default information - information
** to be used when we have not run the ANALYZE command.
**
** aiRowEst[0] is suppose to contain the number of elements in the index.







>












|







2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
      Index *pOther = pTab->pIndex;
      while( pOther->pNext && pOther->pNext->onError!=OE_Replace ){
        pOther = pOther->pNext;
      }
      pIndex->pNext = pOther->pNext;
      pOther->pNext = pIndex;
    }
    pRet = pIndex;
    pIndex = 0;
  }

  /* Clean up before exiting */
exit_create_index:
  if( pIndex ){
    sqlite3_free(pIndex->zColAff);
    sqlite3DbFree(db, pIndex);
  }
  sqlite3ExprListDelete(db, pList);
  sqlite3SrcListDelete(db, pTblName);
  sqlite3DbFree(db, zName);
  return pRet;
}

/*
** Fill the Index.aiRowEst[] array with default information - information
** to be used when we have not run the ANALYZE command.
**
** aiRowEst[0] is suppose to contain the number of elements in the index.
Changes to src/callback.c.
419
420
421
422
423
424
425

426
427
428
429
430
431
432
  sqlite3HashInit(&pSchema->tblHash);
  for(pElem=sqliteHashFirst(&temp1); pElem; pElem=sqliteHashNext(pElem)){
    Table *pTab = sqliteHashData(pElem);
    assert( pTab->dbMem==0 );
    sqlite3DeleteTable(pTab);
  }
  sqlite3HashClear(&temp1);

  pSchema->pSeqTab = 0;
  pSchema->flags &= ~DB_SchemaLoaded;
}

/*
** Find and return the schema associated with a BTree.  Create
** a new one if necessary.







>







419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
  sqlite3HashInit(&pSchema->tblHash);
  for(pElem=sqliteHashFirst(&temp1); pElem; pElem=sqliteHashNext(pElem)){
    Table *pTab = sqliteHashData(pElem);
    assert( pTab->dbMem==0 );
    sqlite3DeleteTable(pTab);
  }
  sqlite3HashClear(&temp1);
  sqlite3HashClear(&pSchema->fkeyHash);
  pSchema->pSeqTab = 0;
  pSchema->flags &= ~DB_SchemaLoaded;
}

/*
** Find and return the schema associated with a BTree.  Create
** a new one if necessary.
440
441
442
443
444
445
446

447
448
449
450
  }
  if( !p ){
    db->mallocFailed = 1;
  }else if ( 0==p->file_format ){
    sqlite3HashInit(&p->tblHash);
    sqlite3HashInit(&p->idxHash);
    sqlite3HashInit(&p->trigHash);

    p->enc = SQLITE_UTF8;
  }
  return p;
}







>




441
442
443
444
445
446
447
448
449
450
451
452
  }
  if( !p ){
    db->mallocFailed = 1;
  }else if ( 0==p->file_format ){
    sqlite3HashInit(&p->tblHash);
    sqlite3HashInit(&p->idxHash);
    sqlite3HashInit(&p->trigHash);
    sqlite3HashInit(&p->fkeyHash);
    p->enc = SQLITE_UTF8;
  }
  return p;
}
Changes to src/delete.c.
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
  /* Begin generating code.
  */
  v = sqlite3GetVdbe(pParse);
  if( v==0 ){
    goto delete_from_cleanup;
  }
  if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
  sqlite3BeginWriteOperation(pParse, (pTrigger?1:0), iDb);

  /* If we are trying to delete from a view, realize that view into
  ** a ephemeral table.
  */
#if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
  if( isView ){
    sqlite3MaterializeView(pParse, pTab, pWhere, iCur);







|







304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
  /* Begin generating code.
  */
  v = sqlite3GetVdbe(pParse);
  if( v==0 ){
    goto delete_from_cleanup;
  }
  if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
  sqlite3BeginWriteOperation(pParse, 1, iDb);

  /* If we are trying to delete from a view, realize that view into
  ** a ephemeral table.
  */
#if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
  if( isView ){
    sqlite3MaterializeView(pParse, pTab, pWhere, iCur);
337
338
339
340
341
342
343
344


345
346
347
348
349
350
351
  }

#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 sqlite3_count_changes) to be set incorrectly.  */
  if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) ){


    assert( !isView );
    sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt,
                      pTab->zName, P4_STATIC);
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
    }







|
>
>







337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
  }

#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 sqlite3_count_changes) to be set incorrectly.  */
  if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) 
   && 0==sqlite3FkRequired(pParse, pTab, 0)
  ){
    assert( !isView );
    sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt,
                      pTab->zName, P4_STATIC);
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
    }
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
** 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:
**
**   1.  A read/write cursor pointing to pTab, the table containing the row
**       to be deleted, must be opened as cursor number "base".
**
**   2.  Read/write cursors for all indices of pTab must be open as
**       cursor number base+i for the i-th index.
**
**   3.  The record number of the row to be deleted must be stored in
**       memory cell iRowid.
**







|







444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
** 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:
**
**   1.  A read/write cursor pointing to pTab, the table containing the row
**       to be deleted, must be opened as cursor number $iCur.
**
**   2.  Read/write cursors for all indices of pTab must be open as
**       cursor number base+i for the i-th index.
**
**   3.  The record number of the row to be deleted must be stored in
**       memory cell iRowid.
**
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514





515
516
517
518
519
520
521
522
523
524
525
526
527





528
529
530
531
532
533
534
535
536
537
538
539
540
  ** (this can happen if a trigger program has already deleted it), do
  ** not attempt to delete it or fire any DELETE triggers.  */
  iLabel = sqlite3VdbeMakeLabel(v);
  sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid);
 
  /* If there are any triggers to fire, allocate a range of registers to
  ** use for the old.* references in the triggers.  */
  if( pTrigger ){
    u32 mask;                     /* Mask of OLD.* columns in use */
    int iCol;                     /* Iterator used while populating OLD.* */

    /* TODO: Could use temporary registers here. Also could attempt to
    ** avoid copying the contents of the rowid register.  */
    mask = sqlite3TriggerOldmask(pParse, pTrigger, TK_DELETE, 0, pTab, onconf);
    iOld = pParse->nMem+1;
    pParse->nMem += (1 + pTab->nCol);

    /* Populate the OLD.* pseudo-table register array. These values will be 
    ** used by any BEFORE and AFTER triggers that exist.  */
    sqlite3VdbeAddOp2(v, OP_Copy, iRowid, iOld);
    for(iCol=0; iCol<pTab->nCol; iCol++){
      if( mask==0xffffffff || mask&(1<<iCol) ){
        int iTarget = iOld + iCol + 1;
        sqlite3VdbeAddOp3(v, OP_Column, iCur, iCol, iTarget);
        sqlite3ColumnDefault(v, pTab, iCol, iTarget);
      }
    }

    /* Invoke any BEFORE trigger programs */
    sqlite3CodeRowTrigger(pParse, pTrigger, 
        TK_DELETE, 0, TRIGGER_BEFORE, pTab, -1, iOld, onconf, iLabel
    );

    /* Seek the cursor to the row to be deleted again. It may be that
    ** the BEFORE triggers coded above have already removed the row
    ** being deleted. Do not attempt to delete the row a second time, and 
    ** do not fire AFTER triggers.  */
    sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid);





  }

  /* 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( pTab->pSelect==0 ){
    sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, 0);
    sqlite3VdbeAddOp2(v, OP_Delete, iCur, (count?OPFLAG_NCHANGE:0));
    if( count ){
      sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_STATIC);
    }
  }






  /* Invoke AFTER triggers. */
  if( pTrigger ){
    sqlite3CodeRowTrigger(pParse, pTrigger, 
        TK_DELETE, 0, TRIGGER_AFTER, pTab, -1, iOld, onconf, iLabel
    );
  }

  /* Jump here if the row had already been deleted before any BEFORE
  ** trigger programs were invoked. Or if a trigger program throws a 
  ** RAISE(IGNORE) exception.  */
  sqlite3VdbeResolveLabel(v, iLabel);
}








|





|







|






|









>
>
>
>
>













>
>
>
>
>
|
<
|
|
|
<







479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540

541
542
543

544
545
546
547
548
549
550
  ** (this can happen if a trigger program has already deleted it), do
  ** not attempt to delete it or fire any DELETE triggers.  */
  iLabel = sqlite3VdbeMakeLabel(v);
  sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid);
 
  /* If there are any triggers to fire, allocate a range of registers to
  ** use for the old.* references in the triggers.  */
  if( sqlite3FkRequired(pParse, pTab, 0) || pTrigger ){
    u32 mask;                     /* Mask of OLD.* columns in use */
    int iCol;                     /* Iterator used while populating OLD.* */

    /* TODO: Could use temporary registers here. Also could attempt to
    ** avoid copying the contents of the rowid register.  */
    mask = sqlite3TriggerOldmask(pParse, pTrigger, 0, pTab, onconf);
    iOld = pParse->nMem+1;
    pParse->nMem += (1 + pTab->nCol);

    /* Populate the OLD.* pseudo-table register array. These values will be 
    ** used by any BEFORE and AFTER triggers that exist.  */
    sqlite3VdbeAddOp2(v, OP_Copy, iRowid, iOld);
    for(iCol=0; iCol<pTab->nCol; iCol++){
      if( 1 || mask==0xffffffff || mask&(1<<iCol) ){
        int iTarget = iOld + iCol + 1;
        sqlite3VdbeAddOp3(v, OP_Column, iCur, iCol, iTarget);
        sqlite3ColumnDefault(v, pTab, iCol, iTarget);
      }
    }

    /* Invoke BEFORE DELETE trigger programs. */
    sqlite3CodeRowTrigger(pParse, pTrigger, 
        TK_DELETE, 0, TRIGGER_BEFORE, pTab, -1, iOld, onconf, iLabel
    );

    /* Seek the cursor to the row to be deleted again. It may be that
    ** the BEFORE triggers coded above have already removed the row
    ** being deleted. Do not attempt to delete the row a second time, and 
    ** do not fire AFTER triggers.  */
    sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid);

    /* Do FK processing. This call checks that any FK constraints that
    ** refer to this table (i.e. constraints attached to other tables) 
    ** are not violated by deleting this row.  */
    sqlite3FkCheck(pParse, pTab, 0, iOld, 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( pTab->pSelect==0 ){
    sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, 0);
    sqlite3VdbeAddOp2(v, OP_Delete, iCur, (count?OPFLAG_NCHANGE:0));
    if( count ){
      sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_STATIC);
    }
  }

  /* 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. */ 
  sqlite3FkActions(pParse, pTab, 0, iOld);

  /* Invoke AFTER DELETE trigger programs. */

  sqlite3CodeRowTrigger(pParse, pTrigger, 
      TK_DELETE, 0, TRIGGER_AFTER, pTab, -1, iOld, onconf, iLabel
  );


  /* Jump here if the row had already been deleted before any BEFORE
  ** trigger programs were invoked. Or if a trigger program throws a 
  ** RAISE(IGNORE) exception.  */
  sqlite3VdbeResolveLabel(v, iLabel);
}

Added src/fkey.c.
































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
/*
**
** 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 contains code used by the compiler to add foreign key
** support to compiled SQL statements.
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_FOREIGN_KEY

/*
** Deferred and Immediate FKs
** --------------------------
**
** Foreign keys in SQLite come in two flavours: deferred and immediate.
** If an immediate foreign key constraint is violated, an OP_Halt is 
** executed and the current statement transaction rolled back. If a 
** deferred foreign key constraint is violated, no action is taken 
** immediately. However if the application attempts to commit the 
** transaction before fixing the constraint violation, the attempt fails.
**
** Deferred constraints are implemented using a simple counter associated
** with the database handle. The counter is set to zero each time a 
** database transaction is opened. Each time a statement is executed 
** that causes a foreign key violation, the counter is incremented. Each
** time a statement is executed that removes an existing violation from
** the database, the counter is decremented. When the transaction is
** committed, the commit fails if the current value of the counter is
** greater than zero. This scheme has two big drawbacks:
**
**   * When a commit fails due to a deferred foreign key constraint, 
**     there is no way to tell which foreign constraint is not satisfied,
**     or which row it is not satisfied for.
**
**   * If the database contains foreign key violations when the 
**     transaction is opened, this may cause the mechanism to malfunction.
**
** Despite these problems, this approach is adopted as it seems simpler
** than the alternatives.
**
** INSERT operations:
**
**   I.1) For each FK for which the table is the referencing table, search
**        the referenced table for a match. If none is found, throw an 
**        exception for an immediate FK, or increment the counter for a
**        deferred FK.
**
**   I.2) For each deferred FK for which the table is the referenced table, 
**        search the referencing table for rows that correspond to the new
**        row in the referenced table. Decrement the counter for each row
**        found (as the constraint is now satisfied).
**
** DELETE operations:
**
**   D.1) For each deferred FK for which the table is the referencing table, 
**        search the referenced table for a row that corresponds to the 
**        deleted row in the referencing table. If such a row is not found, 
**        decrement the counter.
**
**   D.2) For each FK for which the table is the referenced table, search 
**        the referencing table for rows that correspond to the deleted row 
**        in the referenced table. For each found, throw an exception for an
**        immediate FK, or increment the counter for a deferred FK.
**
** UPDATE operations:
**
**   An UPDATE command requires that all 4 steps above are taken, but only
**   for FK constraints for which the affected columns are actually 
**   modified (values must be compared at runtime).
**
** Note that I.1 and D.1 are very similar operations, as are I.2 and D.2.
** This simplifies the implementation a bit.
**
** For the purposes of immediate FK constraints, the OR REPLACE conflict
** resolution is considered to delete rows before the new row is inserted.
** If a delete caused by OR REPLACE violates an FK constraint, an exception
** is thrown, even if the FK constraint would be satisfied after the new 
** row is inserted.
**
** TODO: How should dropping a table be handled? How should renaming a 
** table be handled?
*/

/*
** Query API Notes
** ---------------
**
** Before coding an UPDATE or DELETE row operation, the code-generator
** for those two operations needs to know whether or not the operation
** requires any FK processing and, if so, which columns of the original
** row are required by the FK processing VDBE code (i.e. if FKs were
** implemented using triggers, which of the old.* columns would be 
** accessed). No information is required by the code-generator before
** coding an INSERT operation.
**
*/

/*
** VDBE Calling Convention
** -----------------------
**
** Example:
**
**   For the following INSERT statement:
**
**     CREATE TABLE t1(a, b INTEGER PRIMARY KEY, c);
**     INSERT INTO t1 VALUES(1, 2, 3.1);
**
**   Register (x):        2    (type integer)
**   Register (x+1):      1    (type integer)
**   Register (x+2):      NULL (type NULL)
**   Register (x+3):      3.1  (type real)
*/

/*
** ON UPDATE and ON DELETE clauses
** -------------------------------
*/

/*
** Externally accessible module functions
** --------------------------------------
**
**   sqlite3FkRequired()
**   sqlite3FkOldmask()
**
**   sqlite3FkCheck()
**   sqlite3FkActions()
**
**   sqlite3FkDelete()
** 
*/

/*
** A foreign key constraint requires that the key columns in the referenced
** table are collectively subject to a UNIQUE or PRIMARY KEY constraint.
** Given that pTo is the referenced table for foreign key constraint
** pFKey, check that the columns in pTo are indeed subject to a such a
** constraint. If they are not, return non-zero and leave an error in pParse.
**
** If an error does not occur, return zero.
*/
static int locateFkeyIndex(
  Parse *pParse,                  /* Parse context to store any error in */
  Table *pTo,                     /* Referenced table */
  FKey *pFKey,                    /* Foreign key to find index for */
  Index **ppIdx,                  /* OUT: Unique index on referenced table */
  int **paiCol                    /* OUT: Map of index columns in pFKey */
){
  Index *pIdx = 0;
  int *aiCol = 0;
  int nCol = pFKey->nCol;
  char *zFirst = pFKey->aCol[0].zCol;

  /* The caller is responsible for zeroing output parameters. */
  assert( ppIdx && *ppIdx==0 );
  assert( !paiCol || *paiCol==0 );

  /* If this is a non-composite (single column) foreign key, check if it 
  ** maps to the INTEGER PRIMARY KEY of table pTo. If so, leave *ppIdx 
  ** and *paiCol set to zero and return early. 
  **
  ** Otherwise, for a composite foreign key (more than one column), allocate
  ** space for the aiCol array (returned via output parameter *paiCol).
  ** Non-composite foreign keys do not require the aiCol array.
  */
  if( nCol==1 ){
    /* The FK maps to the IPK if any of the following are true:
    **
    **   1) The FK is explicitly mapped to "rowid", "oid" or "_rowid_", or
    **   2) There is an explicit INTEGER PRIMARY KEY column and the FK is
    **      implicitly mapped to the primary key of table pTo, or
    **   3) The FK is explicitly mapped to a column declared as INTEGER
    **      PRIMARY KEY.
    */
    if( zFirst && sqlite3IsRowid(zFirst) ) return 0;
    if( pTo->iPKey>=0 ){
      if( !zFirst ) return 0;
      if( !sqlite3StrICmp(pTo->aCol[pTo->iPKey].zName, zFirst) ) return 0;
    }
  }else if( paiCol ){
    assert( nCol>1 );
    aiCol = (int *)sqlite3DbMallocRaw(pParse->db, nCol*sizeof(int));
    if( !aiCol ) return 1;
    *paiCol = aiCol;
  }

  for(pIdx=pTo->pIndex; pIdx; pIdx=pIdx->pNext){
    if( pIdx->nColumn==nCol && pIdx->onError!=OE_None ){ 
      /* pIdx is a UNIQUE index (or a PRIMARY KEY) and has the right number
      ** of columns. If each indexed column corresponds to a foreign key
      ** column of pFKey, then this index is a winner.  */

      if( zFirst==0 ){
        /* If zFirst is NULL, then this foreign key is implicitly mapped to 
        ** the PRIMARY KEY of table pTo. The PRIMARY KEY index may be 
        ** identified by the test (Index.autoIndex==2).  */
        if( pIdx->autoIndex==2 ){
          if( aiCol ) memcpy(aiCol, pIdx->aiColumn, sizeof(int)*nCol);
          break;
        }
      }else{
        /* If zFirst is non-NULL, then this foreign key was declared to
        ** map to an explicit list of columns in table pTo. Check if this
        ** index matches those columns.  */
        int i, j;
        for(i=0; i<nCol; i++){
          char *zIdxCol = pTo->aCol[pIdx->aiColumn[i]].zName;
          for(j=0; j<nCol; j++){
            if( sqlite3StrICmp(pFKey->aCol[j].zCol, zIdxCol)==0 ){
              if( aiCol ) aiCol[i] = pFKey->aCol[j].iFrom;
              break;
            }
          }
          if( j==nCol ) break;
        }
        if( i==nCol ) break;      /* pIdx is usable */
      }
    }
  }

  if( pParse && !pIdx ){
    sqlite3ErrorMsg(pParse, "foreign key mismatch");
    sqlite3DbFree(pParse->db, aiCol);
    return 1;
  }

  *ppIdx = pIdx;
  return 0;
}

static void fkCheckReference(
  Parse *pParse,        /* Parse context */
  int iDb,              /* Index of database housing pTab */
  Table *pTab,          /* Table referenced by FK pFKey */
  Index *pIdx,          /* Index ensuring uniqueness of FK in pTab */
  FKey *pFKey,          /* Foreign key to check */
  int *aiCol,           /* Map from FK column to referencing table column */
  int regData,          /* Address of array containing referencing row */
  int nIncr             /* If deferred FK, increment counter by this */
){
  int i;
  Vdbe *v = sqlite3GetVdbe(pParse);
  int iCur = pParse->nTab - 1;
  int iOk = sqlite3VdbeMakeLabel(v);

  assert( pFKey->isDeferred || nIncr==1 );

  /* Check if any of the key columns in the referencing table are 
  ** NULL. If any are, then the constraint is satisfied. No need
  ** to search for a matching row in the referenced table.  */
  for(i=0; i<pFKey->nCol; i++){
    int iReg = pFKey->aCol[i].iFrom + regData + 1;
    sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk);
  }

  if( pIdx==0 ){
    /* If pIdx is NULL, then the foreign key constraint references the
    ** INTEGER PRIMARY KEY column in the referenced table (table pTab).  */
    int iReg = pFKey->aCol[0].iFrom + regData + 1;
    sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);
    sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, iReg);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
    sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
  }else{
    int regRec = sqlite3GetTempReg(pParse);
    KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);

    sqlite3VdbeAddOp3(v, OP_OpenRead, iCur, pIdx->tnum, iDb);
    sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF);

    if( aiCol ){
      int nCol = pFKey->nCol;
      int regTemp = sqlite3GetTempRange(pParse, nCol);
      for(i=0; i<nCol; i++){ 
        sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[i]+1+regData, regTemp+i);
      }
      sqlite3VdbeAddOp3(v, OP_MakeRecord, regTemp, nCol, regRec);
      sqlite3ReleaseTempRange(pParse, regTemp, nCol);
    }else{
      int iReg = pFKey->aCol[0].iFrom + regData + 1;
      sqlite3VdbeAddOp3(v, OP_MakeRecord, iReg, 1, regRec);
      sqlite3IndexAffinityStr(v, pIdx);
    }

    sqlite3VdbeAddOp3(v, OP_Found, iCur, iOk, regRec);
    sqlite3ReleaseTempReg(pParse, regRec);
  }

  if( pFKey->isDeferred ){
    assert( nIncr==1 || nIncr==-1 );
    sqlite3VdbeAddOp1(v, OP_DeferredCons, nIncr);
  }else{
    sqlite3HaltConstraint(
        pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
    );
  }

  sqlite3VdbeResolveLabel(v, iOk);
}

static void fkScanReferences(
  Parse *pParse,                  /* Parse context */
  SrcList *pSrc,                  /* SrcList containing the table to scan */
  Index *pIdx,                    /* Foreign key index */
  FKey *pFKey,                    /* Foreign key relationship */
  int *aiCol,                     /* Map from FK to referenced table columns */
  int regData,                    /* Referenced table data starts here */
  int nIncr                       /* Amount to increment deferred counter by */
){
  sqlite3 *db = pParse->db;       /* Database handle */
  int i;                          /* Iterator variable */
  Expr *pWhere = 0;               /* WHERE clause to scan with */
  NameContext sNameContext;       /* Context used to resolve WHERE clause */
  WhereInfo *pWInfo;              /* Context used by sqlite3WhereXXX() */

  for(i=0; i<pFKey->nCol; i++){
    Expr *pLeft;                  /* Value from deleted row */
    Expr *pRight;                 /* Column ref to referencing table */
    Expr *pEq;                    /* Expression (pLeft = pRight) */
    int iCol;                     /* Index of column in referencing table */ 
    const char *zCol;             /* Name of column in referencing table */

    pLeft = sqlite3Expr(db, TK_REGISTER, 0);
    if( pLeft ){
      pLeft->iTable = (pIdx ? (regData+pIdx->aiColumn[i]+1) : regData);
    }
    iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
    if( iCol<0 ){
      zCol = "rowid";
    }else{
      zCol = pFKey->pFrom->aCol[iCol].zName;
    }
    pRight = sqlite3Expr(db, TK_ID, zCol);
    pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight, 0);
    pWhere = sqlite3ExprAnd(db, pWhere, pEq);
  }

  /* Resolve the references in the WHERE clause. */
  memset(&sNameContext, 0, sizeof(NameContext));
  sNameContext.pSrcList = pSrc;
  sNameContext.pParse = pParse;
  sqlite3ResolveExprNames(&sNameContext, pWhere);

  /* Create VDBE to loop through the entries in pSrc that match the WHERE
  ** clause. If the constraint is not deferred, throw an exception for
  ** each row found. Otherwise, for deferred constraints, increment the
  ** deferred constraint counter by nIncr for each row selected.  */
  pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0);
  if( pFKey->isDeferred && nIncr ){
    assert( nIncr==1 || nIncr==-1 );
    sqlite3VdbeAddOp1(pParse->pVdbe, OP_DeferredCons, nIncr);
  }else{
    assert( nIncr==1 || nIncr==0 );
    sqlite3HaltConstraint(
      pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
    );
  }
  sqlite3WhereEnd(pWInfo);

  /* Clean up the WHERE clause constructed above. */
  sqlite3ExprDelete(db, pWhere);
}

/*
** This function returns a pointer to the head of a linked list of FK
** constraints that refer to the table passed as an argument. For example,
** given the following schema:
**
**   CREATE TABLE t1(a PRIMARY KEY);
**   CREATE TABLE t2(b REFERENCES t1(a);
**
** Calling this function with table "t1" as an argument returns a pointer
** to the FKey structure representing the foreign key constraint on table
** "t2". Calling this function with "t2" as the argument would return a
** NULL pointer (as there are no FK constraints that refer to t2).
*/
static FKey *fkRefering(Table *pTab){
  int nName = sqlite3Strlen30(pTab->zName);
  return (FKey *)sqlite3HashFind(&pTab->pSchema->fkeyHash, pTab->zName, nName);
}

void sqlite3FkCheck(
  Parse *pParse,                  /* Parse context */
  Table *pTab,                    /* Row is being deleted from this table */ 
  ExprList *pChanges,             /* Changed columns if this is an UPDATE */
  int regOld,                     /* Previous row data is stored here */
  int regNew                      /* New row data is stored here */
){
  sqlite3 *db = pParse->db;       /* Database handle */
  Vdbe *v;                        /* VM to write code to */
  FKey *pFKey;                    /* Used to iterate through FKs */
  int iDb;                        /* Index of database containing pTab */
  const char *zDb;                /* Name of database containing pTab */

  assert( ( pChanges &&  regOld &&  regNew)           /* UPDATE operation */
       || (!pChanges && !regOld &&  regNew)           /* INSERT operation */
       || (!pChanges &&  regOld && !regNew)           /* DELETE operation */
  );

  /* If foreign-keys are disabled, this function is a no-op. */
  if( (db->flags&SQLITE_ForeignKeys)==0 ) return;

  v = sqlite3GetVdbe(pParse);
  iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  zDb = db->aDb[iDb].zName;

  /* Loop through all the foreign key constraints attached to the table. */
  for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){
    Table *pTo;                   /* Table referenced by this FK */
    Index *pIdx = 0;              /* Index on key columns in pTo */
    int *aiCol = 0;

    if( pFKey->isDeferred==0 && regNew==0 ) continue;

    /* Find the table this foreign key references. Also find a unique 
    ** index on the referenced table that corresponds to the key columns. 
    ** If either of these things cannot be located, set an error in pParse
    ** and return early.  */
    pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb);
    if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiCol) ) return;
    assert( pFKey->nCol==1 || (aiCol && pIdx) );

    /* If the key does not overlap with the pChanges list, skip this FK. */
    if( pChanges ){
      /* TODO */
    }

    /* Take a shared-cache advisory read-lock on the referenced table.
    ** Allocate a cursor to use to search the unique index on the FK 
    ** columns in the referenced table.  */
    sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName);
    pParse->nTab++;

    if( regOld!=0 && pFKey->isDeferred ){
      fkCheckReference(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1);
    }
    if( regNew!=0 ){
      fkCheckReference(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1);
    }

    sqlite3DbFree(db, aiCol);
  }

  /* Loop through all the foreign key constraints that refer to this table */
  for(pFKey = fkRefering(pTab); pFKey; pFKey=pFKey->pNextTo){
    int iGoto;                    /* Address of OP_Goto instruction */
    Index *pIdx = 0;              /* Foreign key index for pFKey */
    SrcList *pSrc;
    int *aiCol = 0;

    /* For immediate constraints, skip this scan if:
    **
    **   1) this is an INSERT operation, or
    **   2) an UPDATE operation and the FK action is a trigger-action, or
    **   3) a DELETE operation and the FK action is a trigger-action.
    **
    ** A "trigger-action" is one of CASCADE, SET DEFAULT or SET NULL.
    */
    if( pFKey->isDeferred==0 ){
      if( regOld==0 ) continue;                                     /* 1 */
      if( regNew!=0 && pFKey->updateConf>OE_Restrict ) continue;    /* 2 */
      if( regNew==0 && pFKey->deleteConf>OE_Restrict ) continue;    /* 3 */
    }

    if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return;
    assert( aiCol || pFKey->nCol==1 );

    /* Check if this update statement has modified any of the key columns
    ** for this foreign key constraint. If it has not, there is no need
    ** to search the referencing table for rows in violation. This is
    ** just an optimization. Things would work fine without this check.  */
    if( pChanges ){
      /* TODO */
    }

    /* Create a SrcList structure containing a single table (the table 
    ** the foreign key that refers to this table is attached to). This
    ** is required for the sqlite3WhereXXX() interface.  */
    pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
    if( !pSrc ) return;
    pSrc->a->pTab = pFKey->pFrom;
    pSrc->a->pTab->nRef++;
    pSrc->a->iCursor = pParse->nTab++;

    /* If this is an UPDATE, and none of the columns associated with this
    ** FK have been modified, do not scan the referencing table. Unlike
    ** the compile-time test implemented above, this is not just an 
    ** optimization. It is required so that immediate foreign keys do not 
    ** throw exceptions when the user executes a statement like:
    **
    **     UPDATE refd_table SET refd_column = refd_column
    */
    if( pChanges ){
      int i;
      int iJump = sqlite3VdbeCurrentAddr(v) + pFKey->nCol + 1;
      for(i=0; i<pFKey->nCol; i++){
        int iOff = (pIdx ? pIdx->aiColumn[i] : -1) + 1;
        sqlite3VdbeAddOp3(v, OP_Ne, regOld+iOff, iJump, regNew+iOff);
      }
      iGoto = sqlite3VdbeAddOp0(v, OP_Goto);
    }

    if( regNew!=0 && pFKey->isDeferred ){
      fkScanReferences(pParse, pSrc, pIdx, pFKey, aiCol, regNew, -1);
    }
    if( regOld!=0 ){
      /* If there is a RESTRICT action configured for the current operation
      ** on the referenced table of this FK, then throw an exception 
      ** immediately if the FK constraint is violated, even if this is a
      ** deferred trigger. That's what RESTRICT means. To defer checking
      ** the constraint, the FK should specify NO ACTION (represented
      ** using OE_None). NO ACTION is the default.  */
      fkScanReferences(pParse, pSrc, pIdx, pFKey, aiCol, regOld, 
          (pChanges!=0 && pFKey->updateConf!=OE_Restrict)
       || (pChanges==0 && pFKey->deleteConf!=OE_Restrict)
      );
    }

    if( pChanges ){
      sqlite3VdbeJumpHere(v, iGoto);
    }
    sqlite3SrcListDelete(db, pSrc);
    sqlite3DbFree(db, aiCol);
  }
}

#define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x)))

/*
** This function is called before generating code to update or delete a 
** row contained in table pTab. If the operation is an update, then 
** pChanges is a pointer to the list of columns to modify. If this is a 
** delete, then pChanges is NULL.
*/
u32 sqlite3FkOldmask(
  Parse *pParse,                  /* Parse context */
  Table *pTab,                    /* Table being modified */
  ExprList *pChanges              /* Non-NULL for UPDATE operations */
){
  u32 mask = 0;
  if( pParse->db->flags&SQLITE_ForeignKeys ){
    FKey *p;
    int i;
    for(p=pTab->pFKey; p; p=p->pNextFrom){
      if( pChanges || p->isDeferred ){
        for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom);
      }
    }
    for(p=fkRefering(pTab); p; p=p->pNextTo){
      Index *pIdx = 0;
      locateFkeyIndex(0, pTab, p, &pIdx, 0);
      if( pIdx ){
        for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
      }
    }
  }
  return mask;
}

/*
** This function is called before generating code to update or delete a 
** row contained in table pTab. If the operation is an update, then 
** pChanges is a pointer to the list of columns to modify. If this is a 
** delete, then pChanges is NULL.
**
** If any foreign key processing will be required, this function returns
** true. If there is no foreign key related processing, this function 
** returns false.
*/
int sqlite3FkRequired(
  Parse *pParse,                  /* Parse context */
  Table *pTab,                    /* Table being modified */
  ExprList *pChanges              /* Non-NULL for UPDATE operations */
){
  if( pParse->db->flags&SQLITE_ForeignKeys ){
    FKey *p;
    for(p=pTab->pFKey; p; p=p->pNextFrom){
      if( pChanges || p->isDeferred ) return 1;
    }
    if( fkRefering(pTab) ) return 1;
  }
  return 0;
}

static Trigger *fkActionTrigger(
  Parse *pParse,
  Table *pTab,                    /* Table being updated or deleted from */
  FKey *pFKey,                    /* Foreign key to get action for */
  ExprList *pChanges              /* Change-list for UPDATE, NULL for DELETE */
){
  sqlite3 *db = pParse->db;       /* Database handle */
  int action;
  Trigger *pTrigger;

  if( pChanges ){
    action = pFKey->updateConf;
    pTrigger = pFKey->pOnUpdate;
  }else{
    action = pFKey->deleteConf;
    pTrigger = pFKey->pOnDelete;
  }

  assert( OE_SetNull>OE_Restrict && OE_SetDflt>OE_Restrict );
  assert( OE_Cascade>OE_Restrict && OE_None<OE_Restrict );

  if( action>OE_Restrict && !pTrigger ){
    char const *zFrom;            /* Name of referencing table */
    int nFrom;                    /* Length in bytes of zFrom */
    Index *pIdx = 0;
    int *aiCol = 0;
    TriggerStep *pStep;
    sqlite3 *dbMem = pTab->dbMem;
    Expr *pWhere = 0;
    ExprList *pList = 0;
    int i;

    if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0;
    assert( aiCol || pFKey->nCol==1 );

    assert( dbMem==0 || dbMem==pParse->db );
    zFrom = pFKey->pFrom->zName;
    nFrom = sqlite3Strlen30(zFrom);
    pTrigger = (Trigger *)sqlite3DbMallocZero(dbMem, 
        sizeof(Trigger) +         /* struct Trigger */
        sizeof(TriggerStep) +     /* Single step in trigger program */
        nFrom + 1                 /* Space for pStep->target.z */
    );
    if( !pTrigger ){
      pParse->db->mallocFailed = 1;
      return 0;
    }
    pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1];
    pStep->target.z = (char *)&pStep[1];
    pStep->target.n = nFrom;
    memcpy((char *)pStep->target.z, zFrom, nFrom);

    for(i=0; i<pFKey->nCol; i++){
      Expr *pEq;
      int iFromCol;               /* Idx of column in referencing table */
      Token tFromCol;             /* Name of column in referencing table */
      Token tToCol;               /* Name of column in referenced table */
      Token tOld = { "old", 3 };  /* Literal "old" token */
      Token tNew = { "new", 3 };  /* Literal "new" token */

      iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
      tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid";
      tFromCol.z = iFromCol<0 ? "oid" : pFKey->pFrom->aCol[iFromCol].zName;

      tToCol.n = sqlite3Strlen30(tToCol.z);
      tFromCol.n = sqlite3Strlen30(tFromCol.z);

      /* Create the expression "zFromCol = OLD.zToCol" */
      pEq = sqlite3PExpr(pParse, TK_EQ,
          sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol),
          sqlite3PExpr(pParse, TK_DOT, 
            sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
            sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
          , 0)
      , 0);
      pWhere = sqlite3ExprAnd(pParse->db, pWhere, pEq);

      if( action!=OE_Cascade || pChanges ){
        Expr *pNew;
        if( action==OE_Cascade ){
          pNew = sqlite3PExpr(pParse, TK_DOT, 
            sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew),
            sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
          , 0);
        }else if( action==OE_SetDflt ){
          Expr *pDflt = pIdx ? 0 : pTab->aCol[pIdx->aiColumn[i]].pDflt;
          if( pDflt ){
            pNew = sqlite3ExprDup(db, pDflt, 0);
          }else{
            pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
          }
        }else{
          pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
        }
        pList = sqlite3ExprListAppend(pParse, pList, pNew);
        sqlite3ExprListSetName(pParse, pList, &tFromCol, 0);
      }
    }
    sqlite3DbFree(pParse->db, aiCol);

    pStep->pWhere = sqlite3ExprDup(dbMem, pWhere, EXPRDUP_REDUCE);
    pStep->pExprList = sqlite3ExprListDup(dbMem, pList, EXPRDUP_REDUCE);
    sqlite3ExprDelete(pParse->db, pWhere);
    sqlite3ExprListDelete(pParse->db, pList);

    pStep->op = (action!=OE_Cascade || pChanges) ? TK_UPDATE : TK_DELETE;
    pStep->pTrig = pTrigger;
    pTrigger->pSchema = pTab->pSchema;
    pTrigger->pTabSchema = pTab->pSchema;

    if( pChanges ){
      pFKey->pOnUpdate = pTrigger;
      pTrigger->op = TK_UPDATE;
      pStep->op = TK_UPDATE;
    }else{
      pFKey->pOnDelete = pTrigger;
      pTrigger->op = TK_DELETE;
      pStep->op = (action==OE_Cascade)?TK_DELETE:TK_UPDATE;
    }
  }

  return pTrigger;
}

static void fkTriggerDelete(sqlite3 *dbMem, Trigger *p){
  if( p ){
    TriggerStep *pStep = p->step_list;
    sqlite3ExprDelete(dbMem, pStep->pWhere);
    sqlite3ExprListDelete(dbMem, pStep->pExprList);
    sqlite3DbFree(dbMem, p);
  }
}

/*
** This function is called when deleting or updating a row to implement
** any required CASCADE, SET NULL or SET DEFAULT actions.
*/
void sqlite3FkActions(
  Parse *pParse,                  /* Parse context */
  Table *pTab,                    /* Table being updated or deleted from */
  ExprList *pChanges,             /* Change-list for UPDATE, NULL for DELETE */
  int regOld                      /* Address of array containing old row */
){
  /* If foreign-key support is enabled, iterate through all FKs that 
  ** refer to table pTab. If there is an action associated with the FK 
  ** for this operation (either update or delete), invoke the associated 
  ** trigger sub-program.  */
  if( pParse->db->flags&SQLITE_ForeignKeys ){
    FKey *pFKey;                  /* Iterator variable */
    for(pFKey = fkRefering(pTab); pFKey; pFKey=pFKey->pNextTo){
      Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges);
      if( pAction ){
        sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0);
      }
    }
  }
}

/*
** Free all memory associated with foreign key definitions attached to
** table pTab. Remove the deleted foreign keys from the Schema.fkeyHash
** hash table.
*/
void sqlite3FkDelete(Table *pTab){
  FKey *pFKey;                    /* Iterator variable */
  FKey *pNext;                    /* Copy of pFKey->pNextFrom */

  for(pFKey=pTab->pFKey; pFKey; pFKey=pNext){

    /* Remove the FK from the fkeyHash hash table. */
    if( pFKey->pPrevTo ){
      pFKey->pPrevTo->pNextTo = pFKey->pNextTo;
    }else{
      void *data = (void *)pFKey->pNextTo;
      const char *z = (data ? pFKey->pNextTo->zTo : pFKey->zTo);
      sqlite3HashInsert(&pTab->pSchema->fkeyHash, z, sqlite3Strlen30(z), data);
    }
    if( pFKey->pNextTo ){
      pFKey->pNextTo->pPrevTo = pFKey->pPrevTo;
    }

    /* Delete any triggers created to implement actions for this FK. */
    fkTriggerDelete(pTab->dbMem, pFKey->pOnDelete);
    fkTriggerDelete(pTab->dbMem, pFKey->pOnUpdate);

    /* Delete the memory allocated for the FK structure. */
    pNext = pFKey->pNextFrom;
    sqlite3DbFree(pTab->dbMem, pFKey);
  }
}

#endif
Changes to src/insert.c.
975
976
977
978
979
980
981

982
983
984
985
986
987
988
    }else
#endif
    {
      int isReplace;    /* Set to true if constraints may cause a replace */
      sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx,
          keyColumn>=0, 0, onError, endOfLoop, &isReplace
      );

      sqlite3CompleteInsertion(
          pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0
      );
    }
  }

  /* Update the count of rows that are inserted







>







975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
    }else
#endif
    {
      int isReplace;    /* Set to true if constraints may cause a replace */
      sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx,
          keyColumn>=0, 0, onError, endOfLoop, &isReplace
      );
      sqlite3FkCheck(pParse, pTab, 0, 0, regIns);
      sqlite3CompleteInsertion(
          pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0
      );
    }
  }

  /* Update the count of rows that are inserted
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
        seenReplace = 1;
        break;
      }
    }
    sqlite3VdbeJumpHere(v, j3);
    sqlite3ReleaseTempReg(pParse, regR);
  }

  if( pbMayReplace ){
    *pbMayReplace = seenReplace;
  }
}

/*
** This routine generates code to finish the INSERT or UPDATE operation







|







1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
        seenReplace = 1;
        break;
      }
    }
    sqlite3VdbeJumpHere(v, j3);
    sqlite3ReleaseTempReg(pParse, regR);
  }
  
  if( pbMayReplace ){
    *pbMayReplace = seenReplace;
  }
}

/*
** This routine generates code to finish the INSERT or UPDATE operation
Changes to src/main.c.
726
727
728
729
730
731
732



733
734
735
736
737
738
739
  sqlite3VtabRollback(db);
  sqlite3EndBenignMalloc();

  if( db->flags&SQLITE_InternChanges ){
    sqlite3ExpirePreparedStatements(db);
    sqlite3ResetInternalSchema(db, 0);
  }




  /* If one has been configured, invoke the rollback-hook callback */
  if( db->xRollbackCallback && (inTrans || !db->autoCommit) ){
    db->xRollbackCallback(db->pRollbackArg);
  }
}








>
>
>







726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
  sqlite3VtabRollback(db);
  sqlite3EndBenignMalloc();

  if( db->flags&SQLITE_InternChanges ){
    sqlite3ExpirePreparedStatements(db);
    sqlite3ResetInternalSchema(db, 0);
  }

  /* Any deferred constraint violations have now been resolved. */
  db->nDeferredCons = 0;

  /* If one has been configured, invoke the rollback-hook callback */
  if( db->xRollbackCallback && (inTrans || !db->autoCommit) ){
    db->xRollbackCallback(db->pRollbackArg);
  }
}

Changes to src/parse.y.
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
id(A) ::= INDEXED(X).    {A = X;}

// The following directive causes tokens ABORT, AFTER, ASC, etc. to
// fallback to ID if they will not parse as their original value.
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW CONFLICT
  DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH PLAN
  QUERY KEY OF OFFSET PRAGMA RAISE RELEASE REPLACE RESTRICT ROW ROLLBACK
  SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL
%ifdef SQLITE_OMIT_COMPOUND_SELECT
  EXCEPT INTERSECT UNION
%endif SQLITE_OMIT_COMPOUND_SELECT
  REINDEX RENAME CTIME_KW IF
  .







|
|
|







192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
id(A) ::= INDEXED(X).    {A = X;}

// The following directive causes tokens ABORT, AFTER, ASC, etc. to
// fallback to ID if they will not parse as their original value.
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
  CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
  QUERY KEY OF OFFSET PRAGMA RAISE RELEASE REPLACE RESTRICT ROW ROLLBACK
  SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL
%ifdef SQLITE_OMIT_COMPOUND_SELECT
  EXCEPT INTERSECT UNION
%endif SQLITE_OMIT_COMPOUND_SELECT
  REINDEX RENAME CTIME_KW IF
  .
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328

329
330
331
332
333
334
335
336
337

// The next group of rules parses the arguments to a REFERENCES clause
// that determine if the referential integrity checking is deferred or
// or immediate and which determine what action to take if a ref-integ
// check fails.
//
%type refargs {int}
refargs(A) ::= .                     { A = OE_Restrict * 0x010101; }
refargs(A) ::= refargs(X) refarg(Y). { A = (X & ~Y.mask) | Y.value; }
%type refarg {struct {int value; int mask;}}
refarg(A) ::= MATCH nm.              { A.value = 0;     A.mask = 0x000000; }
refarg(A) ::= ON DELETE refact(X).   { A.value = X;     A.mask = 0x0000ff; }
refarg(A) ::= ON UPDATE refact(X).   { A.value = X<<8;  A.mask = 0x00ff00; }
refarg(A) ::= ON INSERT refact(X).   { A.value = X<<16; A.mask = 0xff0000; }
%type refact {int}
refact(A) ::= SET NULL.              { A = OE_SetNull; }
refact(A) ::= SET DEFAULT.           { A = OE_SetDflt; }
refact(A) ::= CASCADE.               { A = OE_Cascade; }
refact(A) ::= RESTRICT.              { A = OE_Restrict; }

%type defer_subclause {int}
defer_subclause(A) ::= NOT DEFERRABLE init_deferred_pred_opt(X).  {A = X;}
defer_subclause(A) ::= DEFERRABLE init_deferred_pred_opt(X).      {A = X;}
%type init_deferred_pred_opt {int}
init_deferred_pred_opt(A) ::= .                       {A = 0;}
init_deferred_pred_opt(A) ::= INITIALLY DEFERRED.     {A = 1;}
init_deferred_pred_opt(A) ::= INITIALLY IMMEDIATE.    {A = 0;}

// For the time being, the only constraint we care about is the primary







|





<





>

|







310
311
312
313
314
315
316
317
318
319
320
321
322

323
324
325
326
327
328
329
330
331
332
333
334
335
336
337

// The next group of rules parses the arguments to a REFERENCES clause
// that determine if the referential integrity checking is deferred or
// or immediate and which determine what action to take if a ref-integ
// check fails.
//
%type refargs {int}
refargs(A) ::= .                     { A = OE_None * 0x010101; }
refargs(A) ::= refargs(X) refarg(Y). { A = (X & ~Y.mask) | Y.value; }
%type refarg {struct {int value; int mask;}}
refarg(A) ::= MATCH nm.              { A.value = 0;     A.mask = 0x000000; }
refarg(A) ::= ON DELETE refact(X).   { A.value = X;     A.mask = 0x0000ff; }
refarg(A) ::= ON UPDATE refact(X).   { A.value = X<<8;  A.mask = 0x00ff00; }

%type refact {int}
refact(A) ::= SET NULL.              { A = OE_SetNull; }
refact(A) ::= SET DEFAULT.           { A = OE_SetDflt; }
refact(A) ::= CASCADE.               { A = OE_Cascade; }
refact(A) ::= RESTRICT.              { A = OE_Restrict; }
refact(A) ::= NO ACTION.             { A = OE_None; }
%type defer_subclause {int}
defer_subclause(A) ::= NOT DEFERRABLE init_deferred_pred_opt.     {A = 0;}
defer_subclause(A) ::= DEFERRABLE init_deferred_pred_opt(X).      {A = X;}
%type init_deferred_pred_opt {int}
init_deferred_pred_opt(A) ::= .                       {A = 0;}
init_deferred_pred_opt(A) ::= INITIALLY DEFERRED.     {A = 1;}
init_deferred_pred_opt(A) ::= INITIALLY IMMEDIATE.    {A = 0;}

// For the time being, the only constraint we care about is the primary
Changes to src/pragma.c.
187
188
189
190
191
192
193



194
195
196
197
198
199
200
    { "writable_schema",          SQLITE_WriteSchema|SQLITE_RecoveryMode },
    { "omit_readlock",            SQLITE_NoReadlock    },

    /* TODO: Maybe it shouldn't be possible to change the ReadUncommitted
    ** flag if there are any active statements. */
    { "read_uncommitted",         SQLITE_ReadUncommitted },
    { "recursive_triggers",       SQLITE_RecTriggers },



  };
  int i;
  const struct sPragmaType *p;
  for(i=0, p=aPragma; i<ArraySize(aPragma); i++, p++){
    if( sqlite3StrICmp(zLeft, p->zName)==0 ){
      sqlite3 *db = pParse->db;
      Vdbe *v;







>
>
>







187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
    { "writable_schema",          SQLITE_WriteSchema|SQLITE_RecoveryMode },
    { "omit_readlock",            SQLITE_NoReadlock    },

    /* TODO: Maybe it shouldn't be possible to change the ReadUncommitted
    ** flag if there are any active statements. */
    { "read_uncommitted",         SQLITE_ReadUncommitted },
    { "recursive_triggers",       SQLITE_RecTriggers },

    /* TODO: Prevent this flag from being set if not in auto-commit mode? */
    { "foreign_keys",             SQLITE_ForeignKeys },
  };
  int i;
  const struct sPragmaType *p;
  for(i=0, p=aPragma; i<ArraySize(aPragma); i++, p++){
    if( sqlite3StrICmp(zLeft, p->zName)==0 ){
      sqlite3 *db = pParse->db;
      Vdbe *v;
227
228
229
230
231
232
233
234
235
236

237
238
239
240
241
242
243
244
245

/*
** Return a human-readable name for a constraint resolution action.
*/
static const char *actionName(u8 action){
  const char *zName;
  switch( action ){
    case OE_SetNull:  zName = "SET NULL";            break;
    case OE_SetDflt:  zName = "SET DEFAULT";         break;
    case OE_Cascade:  zName = "CASCADE";             break;

    default:          zName = "RESTRICT";  
                      assert( action==OE_Restrict ); break;
  }
  return zName;
}

/*
** Process a pragma statement.  
**







|
|
|
>
|
|







230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249

/*
** Return a human-readable name for a constraint resolution action.
*/
static const char *actionName(u8 action){
  const char *zName;
  switch( action ){
    case OE_SetNull:  zName = "SET NULL";        break;
    case OE_SetDflt:  zName = "SET DEFAULT";     break;
    case OE_Cascade:  zName = "CASCADE";         break;
    case OE_Restrict: zName = "RESTRICT";        break;
    default:          zName = "NO ACTION";  
                      assert( action==OE_None ); break;
  }
  return zName;
}

/*
** Process a pragma statement.  
**
Changes to src/shell.c.
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
      */
      "CREATE TRIGGER /name/_delete_referenced BEFORE DELETE ON /ref/ WHEN\n"
      "    EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n"
      "BEGIN\n"
      "  /delete_action/\n"
      "END;\n"

      /* The "BEFORE DELETE ON <referenced>" trigger. This trigger's job 
      ** is to detect when the key columns of a row in the referenced table 
      ** to which one or more rows in the referencing table correspond are
      ** updated. The action taken depends on the value of the 'ON UPDATE' 
      ** clause.
      */
      "CREATE TRIGGER /name/_update_referenced AFTER\n"
      "    UPDATE OF /fkey_list/ ON /ref/ WHEN \n"







|







847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
      */
      "CREATE TRIGGER /name/_delete_referenced BEFORE DELETE ON /ref/ WHEN\n"
      "    EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n"
      "BEGIN\n"
      "  /delete_action/\n"
      "END;\n"

      /* The "AFTER UPDATE ON <referenced>" trigger. This trigger's job 
      ** is to detect when the key columns of a row in the referenced table 
      ** to which one or more rows in the referencing table correspond are
      ** updated. The action taken depends on the value of the 'ON UPDATE' 
      ** clause.
      */
      "CREATE TRIGGER /name/_update_referenced AFTER\n"
      "    UPDATE OF /fkey_list/ ON /ref/ WHEN \n"
Changes to src/sqliteInt.h.
666
667
668
669
670
671
672

673
674
675
676
677
678
679
** statements.
*/
struct Schema {
  int schema_cookie;   /* Database schema version number for this file */
  Hash tblHash;        /* All tables indexed by name */
  Hash idxHash;        /* All (named) indices indexed by name */
  Hash trigHash;       /* All triggers indexed by name */

  Table *pSeqTab;      /* The sqlite_sequence table used by AUTOINCREMENT */
  u8 file_format;      /* Schema format version for this file */
  u8 enc;              /* Text encoding used by this database */
  u16 flags;           /* Flags associated with this schema */
  int cache_size;      /* Number of pages to use in the cache */
#ifndef SQLITE_OMIT_VIRTUALTABLE
  sqlite3 *db;         /* "Owner" connection. See comment above */







>







666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
** statements.
*/
struct Schema {
  int schema_cookie;   /* Database schema version number for this file */
  Hash tblHash;        /* All tables indexed by name */
  Hash idxHash;        /* All (named) indices indexed by name */
  Hash trigHash;       /* All triggers indexed by name */
  Hash fkeyHash;       /* All foreign keys by referenced table name */
  Table *pSeqTab;      /* The sqlite_sequence table used by AUTOINCREMENT */
  u8 file_format;      /* Schema format version for this file */
  u8 enc;              /* Text encoding used by this database */
  u16 flags;           /* Flags associated with this schema */
  int cache_size;      /* Number of pages to use in the cache */
#ifndef SQLITE_OMIT_VIRTUALTABLE
  sqlite3 *db;         /* "Owner" connection. See comment above */
856
857
858
859
860
861
862



863
864
865
866
867
868
869
  BusyHandler busyHandler;      /* Busy callback */
  int busyTimeout;              /* Busy handler timeout, in msec */
  Db aDbStatic[2];              /* Static space for the 2 default backends */
  Savepoint *pSavepoint;        /* List of active savepoints */
  int nSavepoint;               /* Number of non-transaction savepoints */
  int nStatement;               /* Number of nested statement-transactions  */
  u8 isTransactionSavepoint;    /* True if the outermost savepoint is a TS */




#ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
  /* The following variables are all protected by the STATIC_MASTER 
  ** mutex, not by sqlite3.mutex. They are used by code in notify.c. 
  **
  ** When X.pUnlockConnection==Y, that means that X is waiting for Y to
  ** unlock so that it can proceed.







>
>
>







857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
  BusyHandler busyHandler;      /* Busy callback */
  int busyTimeout;              /* Busy handler timeout, in msec */
  Db aDbStatic[2];              /* Static space for the 2 default backends */
  Savepoint *pSavepoint;        /* List of active savepoints */
  int nSavepoint;               /* Number of non-transaction savepoints */
  int nStatement;               /* Number of nested statement-transactions  */
  u8 isTransactionSavepoint;    /* True if the outermost savepoint is a TS */
#ifndef SQLITE_OMIT_FOREIGN_KEY
  i64 nDeferredCons;            /* Net deferred constraints this transaction. */
#endif

#ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
  /* The following variables are all protected by the STATIC_MASTER 
  ** mutex, not by sqlite3.mutex. They are used by code in notify.c. 
  **
  ** When X.pUnlockConnection==Y, that means that X is waiting for Y to
  ** unlock so that it can proceed.
912
913
914
915
916
917
918

919
920
921
922
923
924
925
#define SQLITE_LegacyFileFmt  0x00008000  /* Create new databases in format 1 */
#define SQLITE_FullFSync      0x00010000  /* Use full fsync on the backend */
#define SQLITE_LoadExtension  0x00020000  /* Enable load_extension */

#define SQLITE_RecoveryMode   0x00040000  /* Ignore schema errors */
#define SQLITE_ReverseOrder   0x00100000  /* Reverse unordered SELECTs */
#define SQLITE_RecTriggers    0x00200000  /* Enable recursive triggers */


/*
** Possible values for the sqlite.magic field.
** The numbers are obtained at random and have no special meaning, other
** than being distinct from one another.
*/
#define SQLITE_MAGIC_OPEN     0xa029a697  /* Database is open */







>







916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
#define SQLITE_LegacyFileFmt  0x00008000  /* Create new databases in format 1 */
#define SQLITE_FullFSync      0x00010000  /* Use full fsync on the backend */
#define SQLITE_LoadExtension  0x00020000  /* Enable load_extension */

#define SQLITE_RecoveryMode   0x00040000  /* Ignore schema errors */
#define SQLITE_ReverseOrder   0x00100000  /* Reverse unordered SELECTs */
#define SQLITE_RecTriggers    0x00200000  /* Enable recursive triggers */
#define SQLITE_ForeignKeys    0x00400000  /* Enforce foreign key constraints  */

/*
** Possible values for the sqlite.magic field.
** The numbers are obtained at random and have no special meaning, other
** than being distinct from one another.
*/
#define SQLITE_MAGIC_OPEN     0xa029a697  /* Database is open */
998
999
1000
1001
1002
1003
1004

1005
1006
1007
1008
1009
1010
1011
** All current savepoints are stored in a linked list starting at
** sqlite3.pSavepoint. The first element in the list is the most recently
** opened savepoint. Savepoints are added to the list by the vdbe
** OP_Savepoint instruction.
*/
struct Savepoint {
  char *zName;                        /* Savepoint name (nul-terminated) */

  Savepoint *pNext;                   /* Parent savepoint (if any) */
};

/*
** The following are used as the second parameter to sqlite3Savepoint(),
** and as the P1 argument to the OP_Savepoint instruction.
*/







>







1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
** All current savepoints are stored in a linked list starting at
** sqlite3.pSavepoint. The first element in the list is the most recently
** opened savepoint. Savepoints are added to the list by the vdbe
** OP_Savepoint instruction.
*/
struct Savepoint {
  char *zName;                        /* Savepoint name (nul-terminated) */
  int nDeferredCons;                  /* Number of deferred fk violations */
  Savepoint *pNext;                   /* Parent savepoint (if any) */
};

/*
** The following are used as the second parameter to sqlite3Savepoint(),
** and as the P1 argument to the OP_Savepoint instruction.
*/
1277
1278
1279
1280
1281
1282
1283


1284
1285
1286
1287
1288


1289
1290
1291
1292
1293
1294
1295
** which is attached to the from-table.  The to-table need not exist when
** the from-table is created.  The existence of the to-table is not checked.
*/
struct FKey {
  Table *pFrom;     /* The table that contains the REFERENCES clause */
  FKey *pNextFrom;  /* Next foreign key in pFrom */
  char *zTo;        /* Name of table that the key points to */


  int nCol;         /* Number of columns in this key */
  u8 isDeferred;    /* True if constraint checking is deferred till COMMIT */
  u8 updateConf;    /* How to resolve conflicts that occur on UPDATE */
  u8 deleteConf;    /* How to resolve conflicts that occur on DELETE */
  u8 insertConf;    /* How to resolve conflicts that occur on INSERT */


  struct sColMap {  /* Mapping of columns in pFrom to columns in zTo */
    int iFrom;         /* Index of column in pFrom */
    char *zCol;        /* Name of column in zTo.  If 0 use PRIMARY KEY */
  } aCol[1];        /* One entry for each of nCol column s */
};

/*







>
>





>
>







1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
** which is attached to the from-table.  The to-table need not exist when
** the from-table is created.  The existence of the to-table is not checked.
*/
struct FKey {
  Table *pFrom;     /* The table that contains the REFERENCES clause */
  FKey *pNextFrom;  /* Next foreign key in pFrom */
  char *zTo;        /* Name of table that the key points to */
  FKey *pNextTo;    /* Next foreign key on table named zTo */
  FKey *pPrevTo;    /* Previous foreign key on table named zTo */
  int nCol;         /* Number of columns in this key */
  u8 isDeferred;    /* True if constraint checking is deferred till COMMIT */
  u8 updateConf;    /* How to resolve conflicts that occur on UPDATE */
  u8 deleteConf;    /* How to resolve conflicts that occur on DELETE */
  u8 insertConf;    /* How to resolve conflicts that occur on INSERT */
  Trigger *pOnUpdate;  /* Trigger for AFTER UPDATE ON zTo */
  Trigger *pOnDelete;  /* Trigger for AFTER DELETE ON zTo */
  struct sColMap {  /* Mapping of columns in pFrom to columns in zTo */
    int iFrom;         /* Index of column in pFrom */
    char *zCol;        /* Name of column in zTo.  If 0 use PRIMARY KEY */
  } aCol[1];        /* One entry for each of nCol column s */
};

/*
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
                                      Token*, Select*, Expr*, IdList*);
void sqlite3SrcListIndexedBy(Parse *, SrcList *, Token *);
int sqlite3IndexedByLookup(Parse *, struct SrcList_item *);
void sqlite3SrcListShiftJoinType(SrcList*);
void sqlite3SrcListAssignCursors(Parse*, SrcList*);
void sqlite3IdListDelete(sqlite3*, IdList*);
void sqlite3SrcListDelete(sqlite3*, SrcList*);
void sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                        Token*, int, int);
void sqlite3DropIndex(Parse*, SrcList*, int);
int sqlite3Select(Parse*, Select*, SelectDest*);
Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
                         Expr*,ExprList*,int,Expr*,Expr*);
void sqlite3SelectDelete(sqlite3*, Select*);
Table *sqlite3SrcListLookup(Parse*, SrcList*);







|







2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
                                      Token*, Select*, Expr*, IdList*);
void sqlite3SrcListIndexedBy(Parse *, SrcList *, Token *);
int sqlite3IndexedByLookup(Parse *, struct SrcList_item *);
void sqlite3SrcListShiftJoinType(SrcList*);
void sqlite3SrcListAssignCursors(Parse*, SrcList*);
void sqlite3IdListDelete(sqlite3*, IdList*);
void sqlite3SrcListDelete(sqlite3*, SrcList*);
Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                        Token*, int, int);
void sqlite3DropIndex(Parse*, SrcList*, int);
int sqlite3Select(Parse*, Select*, SelectDest*);
Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
                         Expr*,ExprList*,int,Expr*,Expr*);
void sqlite3SelectDelete(sqlite3*, Select*);
Table *sqlite3SrcListLookup(Parse*, SrcList*);
2690
2691
2692
2693
2694
2695
2696

2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
  void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*);
  void sqlite3DropTrigger(Parse*, SrcList*, int);
  void sqlite3DropTriggerPtr(Parse*, Trigger*);
  Trigger *sqlite3TriggersExist(Parse *, Table*, int, ExprList*, int *pMask);
  Trigger *sqlite3TriggerList(Parse *, Table *);
  void sqlite3CodeRowTrigger(Parse*, Trigger *, int, ExprList*, int, Table *,
                            int, int, int, int);

  void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*);
  void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*);
  TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*);
  TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*,
                                        ExprList*,Select*,u8);
  TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, u8);
  TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*);
  void sqlite3DeleteTrigger(sqlite3*, Trigger*);
  void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*);
  u32 sqlite3TriggerOldmask(Parse*,Trigger*,int,ExprList*,Table*,int);
# define sqlite3ParseToplevel(p) ((p)->pToplevel ? (p)->pToplevel : (p))
#else
# define sqlite3TriggersExist(B,C,D,E,F) 0
# define sqlite3DeleteTrigger(A,B)
# define sqlite3DropTriggerPtr(A,B)
# define sqlite3UnlinkAndDeleteTrigger(A,B,C)
# define sqlite3CodeRowTrigger(A,B,C,D,E,F,G,H,I,J)
# define sqlite3TriggerList(X, Y) 0
# define sqlite3ParseToplevel(p) p
# define sqlite3TriggerOldmask(A,B,C,D,E,F) 0
#endif

int sqlite3JoinType(Parse*, Token*, Token*, Token*);
void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int);
void sqlite3DeferForeignKey(Parse*, int);
#ifndef SQLITE_OMIT_AUTHORIZATION
  void sqlite3AuthRead(Parse*,Expr*,Schema*,SrcList*);







>









|









|







2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
  void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*);
  void sqlite3DropTrigger(Parse*, SrcList*, int);
  void sqlite3DropTriggerPtr(Parse*, Trigger*);
  Trigger *sqlite3TriggersExist(Parse *, Table*, int, ExprList*, int *pMask);
  Trigger *sqlite3TriggerList(Parse *, Table *);
  void sqlite3CodeRowTrigger(Parse*, Trigger *, int, ExprList*, int, Table *,
                            int, int, int, int);
  void sqlite3CodeRowTriggerDirect(Parse *, Trigger *, Table *, int, int, int);
  void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*);
  void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*);
  TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*);
  TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*,
                                        ExprList*,Select*,u8);
  TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, u8);
  TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*);
  void sqlite3DeleteTrigger(sqlite3*, Trigger*);
  void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*);
  u32 sqlite3TriggerOldmask(Parse*,Trigger*,ExprList*,Table*,int);
# define sqlite3ParseToplevel(p) ((p)->pToplevel ? (p)->pToplevel : (p))
#else
# define sqlite3TriggersExist(B,C,D,E,F) 0
# define sqlite3DeleteTrigger(A,B)
# define sqlite3DropTriggerPtr(A,B)
# define sqlite3UnlinkAndDeleteTrigger(A,B,C)
# define sqlite3CodeRowTrigger(A,B,C,D,E,F,G,H,I,J)
# define sqlite3TriggerList(X, Y) 0
# define sqlite3ParseToplevel(p) p
# define sqlite3TriggerOldmask(A,B,C,D,E) 0
#endif

int sqlite3JoinType(Parse*, Token*, Token*, Token*);
void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int);
void sqlite3DeferForeignKey(Parse*, int);
#ifndef SQLITE_OMIT_AUTHORIZATION
  void sqlite3AuthRead(Parse*,Expr*,Schema*,SrcList*);
2927
2928
2929
2930
2931
2932
2933
2934












2935
2936
2937
2938
2939
2940
2941
int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *);
int sqlite3Reprepare(Vdbe*);
void sqlite3ExprListCheckLength(Parse*, ExprList*, const char*);
CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *);
int sqlite3TempInMemory(const sqlite3*);
VTable *sqlite3GetVTable(sqlite3*, Table*);















/*
** Available fault injectors.  Should be numbered beginning with 0.
*/
#define SQLITE_FAULTINJECTOR_MALLOC     0
#define SQLITE_FAULTINJECTOR_COUNT      1








|
>
>
>
>
>
>
>
>
>
>
>
>







2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
2964
int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *);
int sqlite3Reprepare(Vdbe*);
void sqlite3ExprListCheckLength(Parse*, ExprList*, const char*);
CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *);
int sqlite3TempInMemory(const sqlite3*);
VTable *sqlite3GetVTable(sqlite3*, Table*);

#ifndef SQLITE_OMIT_FOREIGN_KEY
  void sqlite3FkCheck(Parse*, Table*, ExprList*, int, int);
  void sqlite3FkActions(Parse*, Table*, ExprList*, int);
  void sqlite3FkDelete(Table*);
  int sqlite3FkRequired(Parse*, Table*, ExprList*);
  u32 sqlite3FkOldmask(Parse*, Table*, ExprList*);
#else
  #define sqlite3FkCheck(a,b,c,d,e)
  #define sqlite3FkActions(a,b,c,d)
  #define sqlite3FkDelete(a)
  #define sqlite3FkRequired(a,b,c) 0
  #define sqlite3FkOldmask(a,b,c)  0
#endif

/*
** Available fault injectors.  Should be numbered beginning with 0.
*/
#define SQLITE_FAULTINJECTOR_MALLOC     0
#define SQLITE_FAULTINJECTOR_COUNT      1

Changes to src/trigger.c.
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
  Expr *pWhen = 0;            /* Duplicate of trigger WHEN expression */
  Vdbe *v;                    /* Temporary VM */
  NameContext sNC;            /* Name context for sub-vdbe */
  SubProgram *pProgram = 0;   /* Sub-vdbe for trigger program */
  Parse *pSubParse;           /* Parse context for sub-vdbe */
  int iEndTrigger = 0;        /* Label to jump to if WHEN is false */

  assert( pTab==tableOfTrigger(pTrigger) );

  /* Allocate the TriggerPrg and SubProgram objects. To ensure that they
  ** are freed if an error occurs, link them into the Parse.pTriggerPrg 
  ** list of the top-level Parse object sooner rather than later.  */
  pPrg = sqlite3DbMallocZero(db, sizeof(TriggerPrg));
  if( !pPrg ) return 0;
  pPrg->pNext = pTop->pTriggerPrg;







|







791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
  Expr *pWhen = 0;            /* Duplicate of trigger WHEN expression */
  Vdbe *v;                    /* Temporary VM */
  NameContext sNC;            /* Name context for sub-vdbe */
  SubProgram *pProgram = 0;   /* Sub-vdbe for trigger program */
  Parse *pSubParse;           /* Parse context for sub-vdbe */
  int iEndTrigger = 0;        /* Label to jump to if WHEN is false */

  assert( pTrigger->zName==0 || pTab==tableOfTrigger(pTrigger) );

  /* Allocate the TriggerPrg and SubProgram objects. To ensure that they
  ** are freed if an error occurs, link them into the Parse.pTriggerPrg 
  ** list of the top-level Parse object sooner rather than later.  */
  pPrg = sqlite3DbMallocZero(db, sizeof(TriggerPrg));
  if( !pPrg ) return 0;
  pPrg->pNext = pTop->pTriggerPrg;
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
































917
918
919
920
921
922
923
  Trigger *pTrigger,   /* Trigger to code */
  Table *pTab,         /* The table trigger pTrigger is attached to */
  int orconf           /* ON CONFLICT algorithm. */
){
  Parse *pRoot = sqlite3ParseToplevel(pParse);
  TriggerPrg *pPrg;

  assert( pTab==tableOfTrigger(pTrigger) );

  /* It may be that this trigger has already been coded (or is in the
  ** process of being coded). If this is the case, then an entry with
  ** a matching TriggerPrg.pTrigger field will be present somewhere
  ** in the Parse.pTriggerPrg list. Search for such an entry.  */
  for(pPrg=pRoot->pTriggerPrg; 
      pPrg && (pPrg->pTrigger!=pTrigger || pPrg->orconf!=orconf); 
      pPrg=pPrg->pNext
  );

  /* If an existing TriggerPrg could not be located, create a new one. */
  if( !pPrg ){
    pPrg = codeRowTrigger(pParse, pTrigger, pTab, orconf);
  }

  return pPrg;
}

































/*
** This is called to code FOR EACH ROW triggers.
**
** When the code that this function generates is executed, the following 
** must be true:
**







|

















>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
  Trigger *pTrigger,   /* Trigger to code */
  Table *pTab,         /* The table trigger pTrigger is attached to */
  int orconf           /* ON CONFLICT algorithm. */
){
  Parse *pRoot = sqlite3ParseToplevel(pParse);
  TriggerPrg *pPrg;

  assert( pTrigger->zName==0 || pTab==tableOfTrigger(pTrigger) );

  /* It may be that this trigger has already been coded (or is in the
  ** process of being coded). If this is the case, then an entry with
  ** a matching TriggerPrg.pTrigger field will be present somewhere
  ** in the Parse.pTriggerPrg list. Search for such an entry.  */
  for(pPrg=pRoot->pTriggerPrg; 
      pPrg && (pPrg->pTrigger!=pTrigger || pPrg->orconf!=orconf); 
      pPrg=pPrg->pNext
  );

  /* If an existing TriggerPrg could not be located, create a new one. */
  if( !pPrg ){
    pPrg = codeRowTrigger(pParse, pTrigger, pTab, orconf);
  }

  return pPrg;
}

void sqlite3CodeRowTriggerDirect(
  Parse *pParse,       /* Parse context */
  Trigger *p,          /* Trigger to code */
  Table *pTab,         /* The table to code triggers from */
  int oldIdx,          /* The indice of the "old" row to access */
  int orconf,          /* ON CONFLICT policy */
  int ignoreJump       /* Instruction to jump to for RAISE(IGNORE) */
){
  Vdbe *v = sqlite3GetVdbe(pParse); /* Main VM */
  TriggerPrg *pPrg;
  pPrg = getRowTrigger(pParse, p, pTab, orconf);
  assert( pPrg || pParse->nErr || pParse->db->mallocFailed );

  /* Code the OP_Program opcode in the parent VDBE. P4 of the OP_Program 
  ** is a pointer to the sub-vdbe containing the trigger program.  */
  if( pPrg ){
    sqlite3VdbeAddOp3(v, OP_Program, oldIdx, ignoreJump, ++pParse->nMem);
    pPrg->pProgram->nRef++;
    sqlite3VdbeChangeP4(v, -1, (const char *)pPrg->pProgram, P4_SUBPROGRAM);
    VdbeComment(
        (v, "Call: %s.%s", (p->zName?p->zName:"fkey"), onErrorText(orconf)));

    /* Set the P5 operand of the OP_Program instruction to non-zero if
    ** recursive invocation of this trigger program is disallowed. Recursive
    ** invocation is disallowed if (a) the sub-program is really a trigger,
    ** not a foreign key action, and (b) the flag to enable recursive triggers
    ** is clear.  */
    sqlite3VdbeChangeP5(v, p->zName && !(pParse->db->flags&SQLITE_RecTriggers));

  }
}

/*
** This is called to code FOR EACH ROW triggers.
**
** When the code that this function generates is executed, the following 
** must be true:
**
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
         || p->pSchema==pParse->db->aDb[1].pSchema );

    /* Determine whether we should code this trigger */
    if( p->op==op 
     && p->tr_tm==tr_tm 
     && checkColumnOverlap(p->pColumns,pChanges)
    ){
      Vdbe *v = sqlite3GetVdbe(pParse); /* Main VM */
      TriggerPrg *pPrg;
      pPrg = getRowTrigger(pParse, p, pTab, orconf);
      assert( pPrg || pParse->nErr || pParse->db->mallocFailed );

      /* Code the OP_Program opcode in the parent VDBE. P4 of the OP_Program 
      ** is a pointer to the sub-vdbe containing the trigger program.  */
      if( pPrg ){
        sqlite3VdbeAddOp3(v, OP_Program, oldIdx, ignoreJump, ++pParse->nMem);
        pPrg->pProgram->nRef++;
        sqlite3VdbeChangeP4(v, -1, (const char *)pPrg->pProgram, P4_SUBPROGRAM);
        VdbeComment((v, "Call: %s.%s", p->zName, onErrorText(orconf)));
      }
    }
  }
}

/*
** Triggers fired by UPDATE or DELETE statements may access values stored
** in the old.* pseudo-table. This function returns a 32-bit bitmask







<
<
<
<
|
<
<
<
<
<
<
<
<







1004
1005
1006
1007
1008
1009
1010




1011








1012
1013
1014
1015
1016
1017
1018
         || p->pSchema==pParse->db->aDb[1].pSchema );

    /* Determine whether we should code this trigger */
    if( p->op==op 
     && p->tr_tm==tr_tm 
     && checkColumnOverlap(p->pColumns,pChanges)
    ){




      sqlite3CodeRowTriggerDirect(pParse, p, pTab, oldIdx, orconf, ignoreJump);








    }
  }
}

/*
** Triggers fired by UPDATE or DELETE statements may access values stored
** in the old.* pseudo-table. This function returns a 32-bit bitmask
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022

1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
** by triggers. The caller must always assume that it is.
**
** There is no equivalent function for new.* references.
*/
u32 sqlite3TriggerOldmask(
  Parse *pParse,       /* Parse context */
  Trigger *pTrigger,   /* List of triggers on table pTab */
  int op,              /* Either TK_UPDATE or TK_DELETE */
  ExprList *pChanges,  /* Changes list for any UPDATE OF triggers */
  Table *pTab,         /* The table to code triggers from */
  int orconf           /* Default ON CONFLICT policy for trigger steps */
){

  u32 mask = 0;
  Trigger *p;

  assert(op==TK_UPDATE || op==TK_DELETE);
  for(p=pTrigger; p; p=p->pNext){
    if( p->op==op && checkColumnOverlap(p->pColumns,pChanges) ){
      TriggerPrg *pPrg;
      pPrg = getRowTrigger(pParse, p, pTab, orconf);
      if( pPrg ){
        mask |= pPrg->oldmask;
      }







<




>



<







1031
1032
1033
1034
1035
1036
1037

1038
1039
1040
1041
1042
1043
1044
1045

1046
1047
1048
1049
1050
1051
1052
** by triggers. The caller must always assume that it is.
**
** There is no equivalent function for new.* references.
*/
u32 sqlite3TriggerOldmask(
  Parse *pParse,       /* Parse context */
  Trigger *pTrigger,   /* List of triggers on table pTab */

  ExprList *pChanges,  /* Changes list for any UPDATE OF triggers */
  Table *pTab,         /* The table to code triggers from */
  int orconf           /* Default ON CONFLICT policy for trigger steps */
){
  const int op = pChanges ? TK_UPDATE : TK_DELETE;
  u32 mask = 0;
  Trigger *p;


  for(p=pTrigger; p; p=p->pNext){
    if( p->op==op && checkColumnOverlap(p->pColumns,pChanges) ){
      TriggerPrg *pPrg;
      pPrg = getRowTrigger(pParse, p, pTab, orconf);
      if( pPrg ){
        mask |= pPrg->oldmask;
      }
Changes to src/update.c.
111
112
113
114
115
116
117

118
119
120
121
122
123
124
125
126
127
128
129
130
  Expr *pRowidExpr = 0;  /* Expression defining the new record number */
  int openAll = 0;       /* True if all indices need to be opened */
  AuthContext sContext;  /* The authorization context */
  NameContext sNC;       /* The name-context to resolve expressions in */
  int iDb;               /* Database containing the table being updated */
  int j1;                /* Addresses of jump instructions */
  int okOnePass;         /* True for one-pass algorithm without the FIFO */


#ifndef SQLITE_OMIT_TRIGGER
  int isView;                  /* Trying to update a view */
  Trigger *pTrigger;           /* List of triggers on pTab, if required */
#endif
  u32 oldmask = 0;        /* Mask of OLD.* columns in use */

  /* Register Allocations */
  int regRowCount = 0;   /* A count of rows changed */
  int regOldRowid;       /* The old rowid */
  int regNewRowid;       /* The new rowid */
  int regNew;
  int regOld = 0;







>





<







111
112
113
114
115
116
117
118
119
120
121
122
123

124
125
126
127
128
129
130
  Expr *pRowidExpr = 0;  /* Expression defining the new record number */
  int openAll = 0;       /* True if all indices need to be opened */
  AuthContext sContext;  /* The authorization context */
  NameContext sNC;       /* The name-context to resolve expressions in */
  int iDb;               /* Database containing the table being updated */
  int j1;                /* Addresses of jump instructions */
  int okOnePass;         /* True for one-pass algorithm without the FIFO */
  int hasFK;             /* True if foreign key processing is required */

#ifndef SQLITE_OMIT_TRIGGER
  int isView;                  /* Trying to update a view */
  Trigger *pTrigger;           /* List of triggers on pTab, if required */
#endif


  /* Register Allocations */
  int regRowCount = 0;   /* A count of rows changed */
  int regOldRowid;       /* The old rowid */
  int regNewRowid;       /* The new rowid */
  int regNew;
  int regOld = 0;
154
155
156
157
158
159
160


161
162
163
164
165
166
167
# define pTrigger 0
# define isView 0
#endif
#ifdef SQLITE_OMIT_VIEW
# undef isView
# define isView 0
#endif



  if( sqlite3ViewGetColumnNames(pParse, pTab) ){
    goto update_cleanup;
  }
  if( sqlite3IsReadOnly(pParse, pTab, (pTrigger?1:0)) ){
    goto update_cleanup;
  }







>
>







154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
# define pTrigger 0
# define isView 0
#endif
#ifdef SQLITE_OMIT_VIEW
# undef isView
# define isView 0
#endif

  hasFK = sqlite3FkRequired(pParse, pTab, pChanges);

  if( sqlite3ViewGetColumnNames(pParse, pTab) ){
    goto update_cleanup;
  }
  if( sqlite3IsReadOnly(pParse, pTab, (pTrigger?1:0)) ){
    goto update_cleanup;
  }
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
    pTabList = 0;
    goto update_cleanup;
  }
#endif

  /* Allocate required registers. */
  regOldRowid = regNewRowid = ++pParse->nMem;
  if( pTrigger ){
    regOld = pParse->nMem + 1;
    pParse->nMem += pTab->nCol;
  }
  if( chngRowid || pTrigger ){
    regNewRowid = ++pParse->nMem;
  }
  regNew = pParse->nMem + 1;
  pParse->nMem += pTab->nCol;
  regRec = ++pParse->nMem;

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

  /* If there are any triggers, set oldmask and new_col_mask. */
  oldmask = sqlite3TriggerOldmask(
      pParse, pTrigger, TK_UPDATE, pChanges, pTab, onError);

  /* If we are trying to update a view, realize that view into
  ** a ephemeral table.
  */
#if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
  if( isView ){
    sqlite3MaterializeView(pParse, pTab, pWhere, iCur);
  }







|



|











<
<
<
<







271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293




294
295
296
297
298
299
300
    pTabList = 0;
    goto update_cleanup;
  }
#endif

  /* Allocate required registers. */
  regOldRowid = regNewRowid = ++pParse->nMem;
  if( pTrigger || hasFK ){
    regOld = pParse->nMem + 1;
    pParse->nMem += pTab->nCol;
  }
  if( chngRowid || pTrigger || hasFK ){
    regNewRowid = ++pParse->nMem;
  }
  regNew = pParse->nMem + 1;
  pParse->nMem += pTab->nCol;
  regRec = ++pParse->nMem;

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





  /* If we are trying to update a view, realize that view into
  ** a ephemeral table.
  */
#if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
  if( isView ){
    sqlite3MaterializeView(pParse, pTab, pWhere, iCur);
  }
373
374
375
376
377
378
379










380
381
382
383


384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403

404
405
406
407
408
409
410
    addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, 0, regOldRowid);
  }

  /* Make cursor iCur point to the record that is being updated. If
  ** this record does not exist for some reason (deleted by a trigger,
  ** for example, then jump to the next iteration of the RowSet loop.  */
  sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid);











  /* If there are triggers on this table, populate an array of registers 
  ** with the required old.* column data.  */
  if( pTrigger ){


    for(i=0; i<pTab->nCol; i++){
      if( aXRef[i]<0 || oldmask==0xffffffff || (oldmask & (1<<i)) ){
        sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regOld+i);
        sqlite3ColumnDefault(v, pTab, i, regOld+i);
      }else{
        sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i);
      }
    }
  }

  /* If the record number will change, set register regNewRowid to
  ** contain the new value. If the record number is not being modified,
  ** then regNewRowid is the same register as regOldRowid, which is
  ** already populated.  */
  assert( chngRowid || pTrigger || regOldRowid==regNewRowid );
  if( chngRowid ){
    sqlite3ExprCode(pParse, pRowidExpr, regNewRowid);
    sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid);
  }else if( pTrigger ){
    sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid);

  }

  /* Populate the array of registers beginning at regNew with the new
  ** row data. This array is used to check constaints, create the new
  ** table and index records, and as the values for any new.* references
  ** made by triggers.  */
  for(i=0; i<pTab->nCol; i++){







>
>
>
>
>
>
>
>
>
>



|
>
>








<
<
<
<
<
<
<
|
<
<
<
|
>







371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401







402



403
404
405
406
407
408
409
410
411
    addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, 0, regOldRowid);
  }

  /* Make cursor iCur point to the record that is being updated. If
  ** this record does not exist for some reason (deleted by a trigger,
  ** for example, then jump to the next iteration of the RowSet loop.  */
  sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid);

  /* If the record number will change, set register regNewRowid to
  ** contain the new value. If the record number is not being modified,
  ** then regNewRowid is the same register as regOldRowid, which is
  ** already populated.  */
  assert( chngRowid || pTrigger || hasFK || regOldRowid==regNewRowid );
  if( chngRowid ){
    sqlite3ExprCode(pParse, pRowidExpr, regNewRowid);
    sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid);
  }

  /* If there are triggers on this table, populate an array of registers 
  ** with the required old.* column data.  */
  if( hasFK || pTrigger ){
    u32 oldmask = sqlite3FkOldmask(pParse, pTab, pChanges);
    oldmask |= sqlite3TriggerOldmask(pParse, pTrigger, pChanges, pTab, onError);
    for(i=0; i<pTab->nCol; i++){
      if( aXRef[i]<0 || oldmask==0xffffffff || (oldmask & (1<<i)) ){
        sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regOld+i);
        sqlite3ColumnDefault(v, pTab, i, regOld+i);
      }else{
        sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i);
      }
    }







    if( chngRowid==0 ){



      sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid);
    }
  }

  /* Populate the array of registers beginning at regNew with the new
  ** row data. This array is used to check constaints, create the new
  ** table and index records, and as the values for any new.* references
  ** made by triggers.  */
  for(i=0; i<pTab->nCol; i++){
439
440
441
442
443
444
445



446
447
448
449
450
451
452
453
454
455
456
457





458
459
460
461
462
463
464

  if( !isView ){

    /* Do constraint checks. */
    sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid,
        aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0);




    /* Delete the index entries associated with the current record.  */
    j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid);
    sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx);
  
    /* If changing the record number, delete the old record.  */
    if( chngRowid ){
      sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0);
    }
    sqlite3VdbeJumpHere(v, j1);
  
    /* Insert the new index entries and the new record. */
    sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0);





  }

  /* Increment the row counter 
  */
  if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){
    sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
  }







>
>
>












>
>
>
>
>







440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473

  if( !isView ){

    /* Do constraint checks. */
    sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid,
        aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0);

    /* Do FK constraint checks. */
    sqlite3FkCheck(pParse, pTab, pChanges, regOldRowid, regNewRowid);

    /* Delete the index entries associated with the current record.  */
    j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid);
    sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx);
  
    /* If changing the record number, delete the old record.  */
    if( chngRowid ){
      sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0);
    }
    sqlite3VdbeJumpHere(v, j1);
  
    /* Insert the new index entries and the new record. */
    sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 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 updated. */ 
    sqlite3FkActions(pParse, pTab, pChanges, regOldRowid);
  }

  /* Increment the row counter 
  */
  if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){
    sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
  }
Changes to src/vdbe.c.
868
869
870
871
872
873
874
875
876
877
878


879
880
881
882
883
884
885
  p->rc = pOp->p1;
  p->errorAction = (u8)pOp->p2;
  p->pc = pc;
  if( pOp->p4.z ){
    sqlite3SetString(&p->zErrMsg, db, "%s", pOp->p4.z);
  }
  rc = sqlite3VdbeHalt(p);
  assert( rc==SQLITE_BUSY || rc==SQLITE_OK );
  if( rc==SQLITE_BUSY ){
    p->rc = rc = SQLITE_BUSY;
  }else{


    rc = p->rc ? SQLITE_ERROR : SQLITE_DONE;
  }
  goto vdbe_return;
}

/* Opcode: Integer P1 P2 * * *
**







|



>
>







868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
  p->rc = pOp->p1;
  p->errorAction = (u8)pOp->p2;
  p->pc = pc;
  if( pOp->p4.z ){
    sqlite3SetString(&p->zErrMsg, db, "%s", pOp->p4.z);
  }
  rc = sqlite3VdbeHalt(p);
  assert( rc==SQLITE_BUSY || rc==SQLITE_OK || rc==SQLITE_ERROR );
  if( rc==SQLITE_BUSY ){
    p->rc = rc = SQLITE_BUSY;
  }else{
    assert( rc==SQLITE_OK || p->rc==SQLITE_CONSTRAINT );
    assert( rc==SQLITE_OK || db->nDeferredCons>0 );
    rc = p->rc ? SQLITE_ERROR : SQLITE_DONE;
  }
  goto vdbe_return;
}

/* Opcode: Integer P1 P2 * * *
**
2503
2504
2505
2506
2507
2508
2509

2510
2511
2512
2513
2514
2515
2516
          db->isTransactionSavepoint = 1;
        }else{
          db->nSavepoint++;
        }
    
        /* Link the new savepoint into the database handle's list. */
        pNew->pNext = db->pSavepoint;

        db->pSavepoint = pNew;
      }
    }
  }else{
    iSavepoint = 0;

    /* Find the named savepoint. If there is no such savepoint, then an







>







2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
          db->isTransactionSavepoint = 1;
        }else{
          db->nSavepoint++;
        }
    
        /* Link the new savepoint into the database handle's list. */
        pNew->pNext = db->pSavepoint;
        pNew->nDeferredCons = db->nDeferredCons;
        db->pSavepoint = pNew;
      }
    }
  }else{
    iSavepoint = 0;

    /* Find the named savepoint. If there is no such savepoint, then an
2541
2542
2543
2544
2545
2546
2547



2548
2549
2550
2551
2552
2553
2554

      /* Determine whether or not this is a transaction savepoint. If so,
      ** and this is a RELEASE command, then the current transaction 
      ** is committed. 
      */
      int isTransaction = pSavepoint->pNext==0 && db->isTransactionSavepoint;
      if( isTransaction && p1==SAVEPOINT_RELEASE ){



        db->autoCommit = 1;
        if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
          p->pc = pc;
          db->autoCommit = 0;
          p->rc = rc = SQLITE_BUSY;
          goto vdbe_return;
        }







>
>
>







2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560

      /* Determine whether or not this is a transaction savepoint. If so,
      ** and this is a RELEASE command, then the current transaction 
      ** is committed. 
      */
      int isTransaction = pSavepoint->pNext==0 && db->isTransactionSavepoint;
      if( isTransaction && p1==SAVEPOINT_RELEASE ){
        if( (rc = sqlite3VdbeCheckDeferred(p))!=SQLITE_OK ){
          goto vdbe_return;
        }
        db->autoCommit = 1;
        if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
          p->pc = pc;
          db->autoCommit = 0;
          p->rc = rc = SQLITE_BUSY;
          goto vdbe_return;
        }
2573
2574
2575
2576
2577
2578
2579
2580



2581
2582
2583
2584
2585
2586
2587


2588
2589
2590
2591
2592
2593
2594
      while( db->pSavepoint!=pSavepoint ){
        pTmp = db->pSavepoint;
        db->pSavepoint = pTmp->pNext;
        sqlite3DbFree(db, pTmp);
        db->nSavepoint--;
      }

      /* If it is a RELEASE, then destroy the savepoint being operated on too */



      if( p1==SAVEPOINT_RELEASE ){
        assert( pSavepoint==db->pSavepoint );
        db->pSavepoint = pSavepoint->pNext;
        sqlite3DbFree(db, pSavepoint);
        if( !isTransaction ){
          db->nSavepoint--;
        }


      }
    }
  }

  break;
}








|
>
>
>







>
>







2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
      while( db->pSavepoint!=pSavepoint ){
        pTmp = db->pSavepoint;
        db->pSavepoint = pTmp->pNext;
        sqlite3DbFree(db, pTmp);
        db->nSavepoint--;
      }

      /* If it is a RELEASE, then destroy the savepoint being operated on 
      ** too. If it is a ROLLBACK TO, then set the number of deferred 
      ** constraint violations present in the database to the value stored
      ** when the savepoint was created.  */
      if( p1==SAVEPOINT_RELEASE ){
        assert( pSavepoint==db->pSavepoint );
        db->pSavepoint = pSavepoint->pNext;
        sqlite3DbFree(db, pSavepoint);
        if( !isTransaction ){
          db->nSavepoint--;
        }
      }else{
        db->nDeferredCons = pSavepoint->nDeferredCons;
      }
    }
  }

  break;
}

2629
2630
2631
2632
2633
2634
2635


2636
2637
2638
2639
2640
2641
2642
        "SQL statements in progress");
    rc = SQLITE_BUSY;
  }else if( desiredAutoCommit!=db->autoCommit ){
    if( iRollback ){
      assert( desiredAutoCommit==1 );
      sqlite3RollbackAll(db);
      db->autoCommit = 1;


    }else{
      db->autoCommit = (u8)desiredAutoCommit;
      if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
        p->pc = pc;
        db->autoCommit = (u8)(1-desiredAutoCommit);
        p->rc = rc = SQLITE_BUSY;
        goto vdbe_return;







>
>







2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
        "SQL statements in progress");
    rc = SQLITE_BUSY;
  }else if( desiredAutoCommit!=db->autoCommit ){
    if( iRollback ){
      assert( desiredAutoCommit==1 );
      sqlite3RollbackAll(db);
      db->autoCommit = 1;
    }else if( (rc = sqlite3VdbeCheckDeferred(p))!=SQLITE_OK ){
      goto vdbe_return;
    }else{
      db->autoCommit = (u8)desiredAutoCommit;
      if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
        p->pc = pc;
        db->autoCommit = (u8)(1-desiredAutoCommit);
        p->rc = rc = SQLITE_BUSY;
        goto vdbe_return;
2716
2717
2718
2719
2720
2721
2722





2723
2724
2725
2726
2727
2728
2729
      assert( sqlite3BtreeIsInTrans(pBt) );
      if( p->iStatement==0 ){
        assert( db->nStatement>=0 && db->nSavepoint>=0 );
        db->nStatement++; 
        p->iStatement = db->nSavepoint + db->nStatement;
      }
      rc = sqlite3BtreeBeginStmt(pBt, p->iStatement);





    }
  }
  break;
}

/* Opcode: ReadCookie P1 P2 P3 * *
**







>
>
>
>
>







2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
      assert( sqlite3BtreeIsInTrans(pBt) );
      if( p->iStatement==0 ){
        assert( db->nStatement>=0 && db->nSavepoint>=0 );
        db->nStatement++; 
        p->iStatement = db->nSavepoint + db->nStatement;
      }
      rc = sqlite3BtreeBeginStmt(pBt, p->iStatement);

      /* Store the current value of the database handles deferred constraint
      ** counter. If the statement transaction needs to be rolled back,
      ** the value of this counter needs to be restored too.  */
      p->nStmtDefCons = db->nDeferredCons;
    }
  }
  break;
}

/* Opcode: ReadCookie P1 P2 P3 * *
**
4729
4730
4731
4732
4733
4734
4735
4736
4737

4738
4739
4740
4741
4742
4743
4744
4745
4746
4747
4748
4749
4750
4751
4752
4753
4754
  SubProgram *pProgram;   /* Sub-program to execute */
  void *t;                /* Token identifying trigger */

  pProgram = pOp->p4.pProgram;
  pRt = &p->aMem[pOp->p3];
  assert( pProgram->nOp>0 );
  
  /* If the SQLITE_RecTriggers flag is clear, then recursive invocation of
  ** triggers is disabled for backwards compatibility (flag set/cleared by

  ** the "PRAGMA recursive_triggers" command). 
  ** 
  ** It is recursive invocation of triggers, at the SQL level, that is 
  ** disabled. In some cases a single trigger may generate more than one 
  ** SubProgram (if the trigger may be executed with more than one different 
  ** ON CONFLICT algorithm). SubProgram structures associated with a
  ** single trigger all have the same value for the SubProgram.token 
  ** variable.
  */
  if( 0==(db->flags&SQLITE_RecTriggers) ){
    t = pProgram->token;
    for(pFrame=p->pFrame; pFrame && pFrame->token!=t; pFrame=pFrame->pParent);
    if( pFrame ) break;
  }

  if( p->nFrame>db->aLimit[SQLITE_LIMIT_TRIGGER_DEPTH] ){
    rc = SQLITE_ERROR;







|
|
>
|






|
<
|







4747
4748
4749
4750
4751
4752
4753
4754
4755
4756
4757
4758
4759
4760
4761
4762
4763
4764

4765
4766
4767
4768
4769
4770
4771
4772
  SubProgram *pProgram;   /* Sub-program to execute */
  void *t;                /* Token identifying trigger */

  pProgram = pOp->p4.pProgram;
  pRt = &p->aMem[pOp->p3];
  assert( pProgram->nOp>0 );
  
  /* If the p5 flag is clear, then recursive invocation of triggers is 
  ** disabled for backwards compatibility (p5 is set if this sub-program
  ** is really a trigger, not a foreign key action, and the flag set
  ** and cleared by the "PRAGMA recursive_triggers" command is clear).
  ** 
  ** It is recursive invocation of triggers, at the SQL level, that is 
  ** disabled. In some cases a single trigger may generate more than one 
  ** SubProgram (if the trigger may be executed with more than one different 
  ** ON CONFLICT algorithm). SubProgram structures associated with a
  ** single trigger all have the same value for the SubProgram.token 
  ** variable.  */

  if( pOp->p5 ){
    t = pProgram->token;
    for(pFrame=p->pFrame; pFrame && pFrame->token!=t; pFrame=pFrame->pParent);
    if( pFrame ) break;
  }

  if( p->nFrame>db->aLimit[SQLITE_LIMIT_TRIGGER_DEPTH] ){
    rc = SQLITE_ERROR;
4838
4839
4840
4841
4842
4843
4844












4845
4846
4847
4848
4849
4850
4851
  pIn = &pFrame->aMem[pOp->p1 + pFrame->aOp[pFrame->pc].p1];   
  sqlite3VdbeMemShallowCopy(pOut, pIn, MEM_Ephem);
  break;
}

#endif /* #ifndef SQLITE_OMIT_TRIGGER */













#ifndef SQLITE_OMIT_AUTOINCREMENT
/* Opcode: MemMax P1 P2 * * *
**
** P1 is a register in the root frame of this VM (the root frame is
** different from the current frame if this instruction is being executed
** within a sub-program). Set the value of register P1 to the maximum of 
** its current value and the value in register P2.







>
>
>
>
>
>
>
>
>
>
>
>







4856
4857
4858
4859
4860
4861
4862
4863
4864
4865
4866
4867
4868
4869
4870
4871
4872
4873
4874
4875
4876
4877
4878
4879
4880
4881
  pIn = &pFrame->aMem[pOp->p1 + pFrame->aOp[pFrame->pc].p1];   
  sqlite3VdbeMemShallowCopy(pOut, pIn, MEM_Ephem);
  break;
}

#endif /* #ifndef SQLITE_OMIT_TRIGGER */

#ifndef SQLITE_OMIT_FOREIGN_KEY
/* Opcode: DeferredCons P1 * * * *
**
** Increment the database handles "deferred constraint violation" counter
** by P1 (P1 may be negative or positive).
*/
case OP_DeferredCons: {
  db->nDeferredCons += pOp->p1;
  break;
}
#endif /* #ifndef SQLITE_OMIT_FOREIGN_KEY */

#ifndef SQLITE_OMIT_AUTOINCREMENT
/* Opcode: MemMax P1 P2 * * *
**
** P1 is a register in the root frame of this VM (the root frame is
** different from the current frame if this instruction is being executed
** within a sub-program). Set the value of register P1 to the maximum of 
** its current value and the value in register P2.
Changes to src/vdbeInt.h.
311
312
313
314
315
316
317

318
319
320
321
322
323
324
  int nChange;            /* Number of db changes made since last reset */
  int btreeMask;          /* Bitmask of db->aDb[] entries referenced */
  i64 startTime;          /* Time when query started - used for profiling */
  BtreeMutexArray aMutex; /* An array of Btree used here and needing locks */
  int aCounter[2];        /* Counters used by sqlite3_stmt_status() */
  char *zSql;             /* Text of the SQL statement that generated this */
  void *pFree;            /* Free this when deleting the vdbe */

  int iStatement;         /* Statement number (or 0 if has not opened stmt) */
#ifdef SQLITE_DEBUG
  FILE *trace;            /* Write an execution trace here, if not NULL */
#endif
  VdbeFrame *pFrame;      /* Parent frame */
  int nFrame;             /* Number of frames in pFrame list */
};







>







311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
  int nChange;            /* Number of db changes made since last reset */
  int btreeMask;          /* Bitmask of db->aDb[] entries referenced */
  i64 startTime;          /* Time when query started - used for profiling */
  BtreeMutexArray aMutex; /* An array of Btree used here and needing locks */
  int aCounter[2];        /* Counters used by sqlite3_stmt_status() */
  char *zSql;             /* Text of the SQL statement that generated this */
  void *pFree;            /* Free this when deleting the vdbe */
  i64 nStmtDefCons;       /* Number of def. constraints when stmt started */
  int iStatement;         /* Statement number (or 0 if has not opened stmt) */
#ifdef SQLITE_DEBUG
  FILE *trace;            /* Write an execution trace here, if not NULL */
#endif
  VdbeFrame *pFrame;      /* Parent frame */
  int nFrame;             /* Number of frames in pFrame list */
};
382
383
384
385
386
387
388






389
390
391
392
393
394
395
int sqlite3VdbeMemGrow(Mem *pMem, int n, int preserve);
int sqlite3VdbeCloseStatement(Vdbe *, int);
void sqlite3VdbeFrameDelete(VdbeFrame*);
int sqlite3VdbeFrameRestore(VdbeFrame *);
#ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
int sqlite3VdbeReleaseBuffers(Vdbe *p);
#endif







#ifndef SQLITE_OMIT_SHARED_CACHE
void sqlite3VdbeMutexArrayEnter(Vdbe *p);
#else
# define sqlite3VdbeMutexArrayEnter(p)
#endif








>
>
>
>
>
>







383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
int sqlite3VdbeMemGrow(Mem *pMem, int n, int preserve);
int sqlite3VdbeCloseStatement(Vdbe *, int);
void sqlite3VdbeFrameDelete(VdbeFrame*);
int sqlite3VdbeFrameRestore(VdbeFrame *);
#ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
int sqlite3VdbeReleaseBuffers(Vdbe *p);
#endif

#ifndef SQLITE_OMIT_FOREIGN_KEY
int sqlite3VdbeCheckDeferred(Vdbe *);
#else
# define sqlite3VdbeCheckDeferred(p) 0
#endif

#ifndef SQLITE_OMIT_SHARED_CACHE
void sqlite3VdbeMutexArrayEnter(Vdbe *p);
#else
# define sqlite3VdbeMutexArrayEnter(p)
#endif

Changes to src/vdbeapi.c.
318
319
320
321
322
323
324


325
326
327
328
329
330
331
    /* If there are no other statements currently running, then
    ** reset the interrupt flag.  This prevents a call to sqlite3_interrupt
    ** from interrupting a statement that has not yet started.
    */
    if( db->activeVdbeCnt==0 ){
      db->u1.isInterrupted = 0;
    }



#ifndef SQLITE_OMIT_TRACE
    if( db->xProfile && !db->init.busy ){
      double rNow;
      sqlite3OsCurrentTime(db->pVfs, &rNow);
      p->startTime = (u64)((rNow - (int)rNow)*3600.0*24.0*1000000000.0);
    }







>
>







318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
    /* If there are no other statements currently running, then
    ** reset the interrupt flag.  This prevents a call to sqlite3_interrupt
    ** from interrupting a statement that has not yet started.
    */
    if( db->activeVdbeCnt==0 ){
      db->u1.isInterrupted = 0;
    }

    assert( db->writeVdbeCnt>0 || db->autoCommit==0 || db->nDeferredCons==0 );

#ifndef SQLITE_OMIT_TRACE
    if( db->xProfile && !db->init.busy ){
      double rNow;
      sqlite3OsCurrentTime(db->pVfs, &rNow);
      p->startTime = (u64)((rNow - (int)rNow)*3600.0*24.0*1000000000.0);
    }
Changes to src/vdbeaux.c.
1891
1892
1893
1894
1895
1896
1897







1898
1899
1900
1901
1902
1903
1904
        if( rc==SQLITE_OK ){
          rc = rc2;
        }
      }
    }
    db->nStatement--;
    p->iStatement = 0;







  }
  return rc;
}

/*
** If SQLite is compiled to support shared-cache mode and to be threadsafe,
** this routine obtains the mutex associated with each BtShared structure







>
>
>
>
>
>
>







1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
        if( rc==SQLITE_OK ){
          rc = rc2;
        }
      }
    }
    db->nStatement--;
    p->iStatement = 0;

    /* If the statement transaction is being rolled back, also restore the 
    ** database handles deferred constraint counter to the value it had when 
    ** the statement transaction was opened.  */
    if( eOp==SAVEPOINT_ROLLBACK ){
      db->nDeferredCons = p->nStmtDefCons;
    }
  }
  return rc;
}

/*
** If SQLite is compiled to support shared-cache mode and to be threadsafe,
** this routine obtains the mutex associated with each BtShared structure
1922
1923
1924
1925
1926
1927
1928






















1929
1930
1931
1932
1933
1934
1935
  sqlite3BtreeMutexArrayEnter(&p->aMutex);
#else
  sqlite3BtreeEnterAll(p->db);
#endif
}
#endif























/*
** This routine is called the when a VDBE tries to halt.  If the VDBE
** has made changes and is in autocommit mode, then commit those
** changes.  If a rollback is needed, then do the rollback.
**
** This routine is the only way to move the state of a VM from
** SQLITE_MAGIC_RUN to SQLITE_MAGIC_HALT.  It is harmless to







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
  sqlite3BtreeMutexArrayEnter(&p->aMutex);
#else
  sqlite3BtreeEnterAll(p->db);
#endif
}
#endif

/*
** This function is called when a transaction opened by the database 
** handle associated with the VM passed as an argument is about to be 
** committed. If there are outstanding deferred foreign key constraint
** violations, return SQLITE_ERROR. Otherwise, SQLITE_OK.
**
** If there are outstanding FK violations and this function returns 
** SQLITE_ERROR, set the result of the VM to SQLITE_CONSTRAINT and write
** an error message to it. Then return SQLITE_ERROR.
*/
#ifndef SQLITE_OMIT_FOREIGN_KEY
int sqlite3VdbeCheckDeferred(Vdbe *p){
  sqlite3 *db = p->db;
  if( db->nDeferredCons ){
    p->rc = SQLITE_CONSTRAINT;
    sqlite3SetString(&p->zErrMsg, db, "foreign key constraint failed");
    return SQLITE_ERROR;
  }
  return SQLITE_OK;
}
#endif

/*
** This routine is called the when a VDBE tries to halt.  If the VDBE
** has made changes and is in autocommit mode, then commit those
** changes.  If a rollback is needed, then do the rollback.
**
** This routine is the only way to move the state of a VM from
** SQLITE_MAGIC_RUN to SQLITE_MAGIC_HALT.  It is harmless to
2008
2009
2010
2011
2012
2013
2014




2015
2016

2017
2018
2019
2020
2021
2022
2023
2024
2025
2026

2027
2028
2029
2030
2031
2032
2033
    ** above has occurred. 
    */
    if( !sqlite3VtabInSync(db) 
     && db->autoCommit 
     && db->writeVdbeCnt==(p->readOnly==0) 
    ){
      if( p->rc==SQLITE_OK || (p->errorAction==OE_Fail && !isSpecialError) ){




        /* The auto-commit flag is true, and the vdbe program was 
        ** successful or hit an 'OR FAIL' constraint. This means a commit 

        ** is required.
        */
        rc = vdbeCommit(db, p);
        if( rc==SQLITE_BUSY ){
          sqlite3BtreeMutexArrayLeave(&p->aMutex);
          return SQLITE_BUSY;
        }else if( rc!=SQLITE_OK ){
          p->rc = rc;
          sqlite3RollbackAll(db);
        }else{

          sqlite3CommitInternalChanges(db);
        }
      }else{
        sqlite3RollbackAll(db);
      }
      db->nStatement = 0;
    }else if( eStatementOp==0 ){







>
>
>
>
|
|
>
|
<








>







2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051

2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
    ** above has occurred. 
    */
    if( !sqlite3VtabInSync(db) 
     && db->autoCommit 
     && db->writeVdbeCnt==(p->readOnly==0) 
    ){
      if( p->rc==SQLITE_OK || (p->errorAction==OE_Fail && !isSpecialError) ){
        if( sqlite3VdbeCheckDeferred(p) ){
          sqlite3BtreeMutexArrayLeave(&p->aMutex);
          return SQLITE_ERROR;
        }
        /* The auto-commit flag is true, the vdbe program was successful 
        ** or hit an 'OR FAIL' constraint and there are no deferred foreign
        ** key constraints to hold up the transaction. This means a commit 
        ** is required.  */

        rc = vdbeCommit(db, p);
        if( rc==SQLITE_BUSY ){
          sqlite3BtreeMutexArrayLeave(&p->aMutex);
          return SQLITE_BUSY;
        }else if( rc!=SQLITE_OK ){
          p->rc = rc;
          sqlite3RollbackAll(db);
        }else{
          db->nDeferredCons = 0;
          sqlite3CommitInternalChanges(db);
        }
      }else{
        sqlite3RollbackAll(db);
      }
      db->nStatement = 0;
    }else if( eStatementOp==0 ){
Changes to src/vdbeblob.c.
140
141
142
143
144
145
146

147
148
149
150

151













152
153
154
155
156
157
158
159
160
161
162
163
164
165








166
167
168
169
170
171
172
      rc = SQLITE_ERROR;
      (void)sqlite3SafetyOff(db);
      sqlite3BtreeLeaveAll(db);
      goto blob_open_out;
    }

    /* If the value is being opened for writing, check that the

    ** column is not indexed. It is against the rules to open an
    ** indexed column for writing.
    */
    if( flags ){

      Index *pIdx;













      for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
        int j;
        for(j=0; j<pIdx->nColumn; j++){
          if( pIdx->aiColumn[j]==iCol ){
            sqlite3DbFree(db, zErr);
            zErr = sqlite3MPrintf(db,
                             "cannot open indexed column for writing");
            rc = SQLITE_ERROR;
            (void)sqlite3SafetyOff(db);
            sqlite3BtreeLeaveAll(db);
            goto blob_open_out;
          }
        }
      }








    }

    v = sqlite3VdbeCreate(db);
    if( v ){
      int iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
      sqlite3VdbeAddOpList(v, sizeof(openBlob)/sizeof(VdbeOpList), openBlob);
      flags = !!flags;                 /* flags = (flags ? 1 : 0); */







>
|
|
<

>

>
>
>
>
>
>
>
>
>
>
>
>
>




<
<
|
<
<
<
<



>
>
>
>
>
>
>
>







140
141
142
143
144
145
146
147
148
149

150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169


170




171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
      rc = SQLITE_ERROR;
      (void)sqlite3SafetyOff(db);
      sqlite3BtreeLeaveAll(db);
      goto blob_open_out;
    }

    /* If the value is being opened for writing, check that the
    ** column is not indexed, and that it is not part of a foreign key. 
    ** It is against the rules to open a column to which either of these
    ** descriptions applies for writing.  */

    if( flags ){
      const char *zFault = 0;
      Index *pIdx;
#ifndef SQLITE_OMIT_FOREIGN_KEY
      if( db->flags&SQLITE_ForeignKeys ){
        FKey *pFKey;
        for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){
          int j;
          for(j=0; j<pFKey->nCol; j++){
            if( pFKey->aCol[j].iFrom==iCol ){
              zFault = "foreign key";
            }
          }
        }
      }
#endif
      for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
        int j;
        for(j=0; j<pIdx->nColumn; j++){
          if( pIdx->aiColumn[j]==iCol ){


            zFault = "indexed";




          }
        }
      }
      if( zFault ){
        sqlite3DbFree(db, zErr);
        zErr = sqlite3MPrintf(db, "cannot open %s column for writing", zFault);
        rc = SQLITE_ERROR;
        (void)sqlite3SafetyOff(db);
        sqlite3BtreeLeaveAll(db);
        goto blob_open_out;
      }
    }

    v = sqlite3VdbeCreate(db);
    if( v ){
      int iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
      sqlite3VdbeAddOpList(v, sizeof(openBlob)/sizeof(VdbeOpList), openBlob);
      flags = !!flags;                 /* flags = (flags ? 1 : 0); */
Changes to test/auth.test.
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258



2259

2260
2261
2262
2263
2264
2265
2266
  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_SELECT {}     {} {}   v1 \
  SQLITE_READ   t2     a  main v1 \
  SQLITE_READ   t2     b  main v1 \
  SQLITE_SELECT {}     {} {}   {} \
  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;
  }







<
<
<





>
>
>

>







2244
2245
2246
2247
2248
2249
2250



2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
  set authargs {}
  execsql {
    UPDATE v1 SET x=1 WHERE x=117
  }
  set authargs
} [list \
  SQLITE_UPDATE v1     x  main {} \



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

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;
  }
Changes to test/fkey1.test.
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
      y TEXT
    );
  }
} {}
do_test fkey1-1.2 {
  execsql {
    CREATE TABLE t3(
      a INTEGER REFERENCES t2 ON INSERT RESTRICT,
      b INTEGER REFERENCES t1,
      FOREIGN KEY (a,b) REFERENCES t2(x,y)
    );
  }
} {}

do_test fkey1-2.1 {







|







42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
      y TEXT
    );
  }
} {}
do_test fkey1-1.2 {
  execsql {
    CREATE TABLE t3(
      a INTEGER REFERENCES t2,
      b INTEGER REFERENCES t1,
      FOREIGN KEY (a,b) REFERENCES t2(x,y)
    );
  }
} {}

do_test fkey1-2.1 {
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
    CREATE TABLE t5(a PRIMARY KEY, b, c);
    CREATE TABLE t6(
      d REFERENCES t5,
      e REFERENCES t5(c)
    );
    PRAGMA foreign_key_list(t6);
  }
} [concat            \
  {0 0 t5 e c RESTRICT RESTRICT NONE}       \
  {1 0 t5 d {} RESTRICT RESTRICT NONE}      \
]
do_test fkey1-3.2 {
  execsql {
    CREATE TABLE t7(d, e, f,
      FOREIGN KEY (d, e) REFERENCES t5(a, b)
    );
    PRAGMA foreign_key_list(t7);
  }
} [concat                        \
  {0 0 t5 d a RESTRICT RESTRICT NONE} \
  {0 1 t5 e b RESTRICT RESTRICT NONE} \
]
do_test fkey1-3.3 {
  execsql {
    CREATE TABLE t8(d, e, f,
      FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL
    );
    PRAGMA foreign_key_list(t8);







|
|
|








|
|
|







76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
    CREATE TABLE t5(a PRIMARY KEY, b, c);
    CREATE TABLE t6(
      d REFERENCES t5,
      e REFERENCES t5(c)
    );
    PRAGMA foreign_key_list(t6);
  }
} [concat                                         \
  {0 0 t5 e c {NO ACTION} {NO ACTION} NONE}       \
  {1 0 t5 d {} {NO ACTION} {NO ACTION} NONE}      \
]
do_test fkey1-3.2 {
  execsql {
    CREATE TABLE t7(d, e, f,
      FOREIGN KEY (d, e) REFERENCES t5(a, b)
    );
    PRAGMA foreign_key_list(t7);
  }
} [concat                                   \
  {0 0 t5 d a {NO ACTION} {NO ACTION} NONE} \
  {0 1 t5 e b {NO ACTION} {NO ACTION} NONE} \
]
do_test fkey1-3.3 {
  execsql {
    CREATE TABLE t8(d, e, f,
      FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL
    );
    PRAGMA foreign_key_list(t8);
Added test/fkey2.test.








































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
# 2009 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for foreign keys.
#

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

#-------------------------------------------------------------------------
# Test structure:
#
# fkey2-1.*: Simple tests to check that immediate and deferred foreign key 
#            constraints work when not inside a transaction.
#            
# fkey2-2.*: Tests to verify that deferred foreign keys work inside
#            explicit transactions (i.e that processing really is deferred).
#
# fkey2-3.*: Tests that a statement transaction is rolled back if an
#            immediate foreign key constraint is violated.
#
# fkey2-4.*: Test that FK actions may recurse even when recursive triggers
#            are disabled.
#
# fkey2-5.*: Check that if foreign-keys are enabled, it is not possible
#            to write to an FK column using the incremental blob API.
#
# fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
#            command. Recycled to test the built-in implementation.
#


proc drop_all_tables {{db db}} {
  set tbls [execsql {SELECT name FROM sqlite_master WHERE type = 'table'}]
  foreach t [execsql {
    SELECT name FROM sqlite_master 
    WHERE type = 'table' AND name NOT like 'sqlite_%'
  }] {
    execsql "DROP TABLE $t"
  }
}


execsql { PRAGMA foreign_keys = on }

set FkeySimpleSchema {
  PRAGMA foreign_keys = on;
  CREATE TABLE t1(a PRIMARY KEY, b);
  CREATE TABLE t2(c REFERENCES t1(a) /D/ , d);

  CREATE TABLE t3(a PRIMARY KEY, b);
  CREATE TABLE t4(c REFERENCES t3 /D/, d);

  CREATE TABLE t5(a INTEGER PRIMARY KEY, b);
  CREATE TABLE t6(c REFERENCES t5(oid) /D/, d);

  CREATE TABLE t7(a, b INTEGER PRIMARY KEY);
  CREATE TABLE t8(c REFERENCES t7 /D/, d);

  CREATE TABLE t9(a REFERENCES nosuchtable, b);
  CREATE TABLE t10(a REFERENCES t9(c) /D/, b);
}
set FkeySimpleTests {
  1.1  "INSERT INTO t2 VALUES(1, 3)"      {1 {foreign key constraint failed}}
  1.2  "INSERT INTO t1 VALUES(1, 2)"      {0 {}}
  1.3  "INSERT INTO t2 VALUES(1, 3)"      {0 {}}
  1.4  "INSERT INTO t2 VALUES(2, 4)"      {1 {foreign key constraint failed}}
  1.5  "INSERT INTO t2 VALUES(NULL, 4)"   {0 {}}
  1.6  "UPDATE t2 SET c=2 WHERE d=4"      {1 {foreign key constraint failed}}
  1.7  "UPDATE t2 SET c=1 WHERE d=4"      {0 {}}
  1.9  "UPDATE t2 SET c=1 WHERE d=4"      {0 {}}
  1.10 "UPDATE t2 SET c=NULL WHERE d=4"   {0 {}}
  1.11 "DELETE FROM t1 WHERE a=1"         {1 {foreign key constraint failed}}
  1.12 "UPDATE t1 SET a = 2"              {1 {foreign key constraint failed}}
  1.13 "UPDATE t1 SET a = 1"              {0 {}}

  2.1  "INSERT INTO t4 VALUES(1, 3)"      {1 {foreign key constraint failed}}
  2.2  "INSERT INTO t3 VALUES(1, 2)"      {0 {}}
  2.3  "INSERT INTO t4 VALUES(1, 3)"      {0 {}}

  3.1  "INSERT INTO t6 VALUES(1, 3)"      {1 {foreign key constraint failed}}
  3.2  "INSERT INTO t5 VALUES(1, 2)"      {0 {}}
  3.3  "INSERT INTO t6 VALUES(1, 3)"      {0 {}}

  4.1  "INSERT INTO t8 VALUES(1, 3)"      {1 {foreign key constraint failed}}
  4.2  "INSERT INTO t7 VALUES(2, 1)"      {0 {}}
  4.3  "INSERT INTO t8 VALUES(1, 3)"      {0 {}}
  4.4  "INSERT INTO t8 VALUES(2, 4)"      {1 {foreign key constraint failed}}
  4.5  "INSERT INTO t8 VALUES(NULL, 4)"   {0 {}}
  4.6  "UPDATE t8 SET c=2 WHERE d=4"      {1 {foreign key constraint failed}}
  4.7  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
  4.9  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
  4.10 "UPDATE t8 SET c=NULL WHERE d=4"   {0 {}}
  4.11 "DELETE FROM t7 WHERE b=1"         {1 {foreign key constraint failed}}
  4.12 "UPDATE t7 SET b = 2"              {1 {foreign key constraint failed}}
  4.13 "UPDATE t7 SET b = 1"              {0 {}}

  5.1  "INSERT INTO t9 VALUES(1, 3)"      {1 {no such table: main.nosuchtable}}
  5.2  "INSERT INTO t10 VALUES(1, 3)"     {1 {foreign key mismatch}}
}

do_test fkey2-1.1.0 {
  execsql [string map {/D/ {}} $FkeySimpleSchema]
} {}
foreach {tn zSql res} $FkeySimpleTests {
  do_test fkey2-1.1.$tn { catchsql $zSql } $res
}
drop_all_tables

do_test fkey2-1.2.0 {
  execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema
  ]
} {}
foreach {tn zSql res} $FkeySimpleTests {
  do_test fkey2-1.2.$tn { catchsql $zSql } $res
}
drop_all_tables

#-------------------------------------------------------------------------
# This section (test cases fkey2-2.*) contains tests to check that the
# deferred foreign key constraint logic works.
#
proc fkey2-2-test {tn nocommit sql {res {}}} {
  if {$res eq "FKV"} {
    set expected {1 {foreign key constraint failed}}
  } else {
    set expected [list 0 $res]
  }
  do_test fkey2-2.$tn [list catchsql $sql] $expected
  if {$nocommit} {
    do_test fkey2-2.${tn}c {
      catchsql COMMIT
    } {1 {foreign key constraint failed}}
  }
}

fkey2-2-test 1 0 {
  CREATE TABLE node(
    nodeid PRIMARY KEY,
    parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
  );
  CREATE TABLE leaf(
    cellid PRIMARY KEY,
    parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
  );
}

fkey2-2-test 1  0 "INSERT INTO node VALUES(1, 0)"       FKV
fkey2-2-test 2  0 "BEGIN"
fkey2-2-test 3  1   "INSERT INTO node VALUES(1, 0)"
fkey2-2-test 4  0   "UPDATE node SET parent = NULL"
fkey2-2-test 5  0 "COMMIT"
fkey2-2-test 6  0 "SELECT * FROM node" {1 {}}

fkey2-2-test 7  0 "BEGIN"
fkey2-2-test 8  1   "INSERT INTO leaf VALUES('a', 2)"
fkey2-2-test 9  1   "INSERT INTO node VALUES(2, 0)"
fkey2-2-test 10 0   "UPDATE node SET parent = 1 WHERE nodeid = 2"
fkey2-2-test 11 0 "COMMIT"
fkey2-2-test 12 0 "SELECT * FROM node" {1 {} 2 1}
fkey2-2-test 13 0 "SELECT * FROM leaf" {a 2}

fkey2-2-test 14 0 "BEGIN"
fkey2-2-test 15 1   "DELETE FROM node WHERE nodeid = 2"
fkey2-2-test 16 0   "INSERT INTO node VALUES(2, NULL)"
fkey2-2-test 17 0 "COMMIT"
fkey2-2-test 18 0 "SELECT * FROM node" {1 {} 2 {}}
fkey2-2-test 19 0 "SELECT * FROM leaf" {a 2}

fkey2-2-test 20 0 "BEGIN"
fkey2-2-test 21 0   "INSERT INTO leaf VALUES('b', 1)"
fkey2-2-test 22 0   "SAVEPOINT save"
fkey2-2-test 23 0     "DELETE FROM node WHERE nodeid = 1"
fkey2-2-test 24 0   "ROLLBACK TO save"
fkey2-2-test 25 0 "COMMIT"
fkey2-2-test 26 0 "SELECT * FROM node" {1 {} 2 {}}
fkey2-2-test 27 0 "SELECT * FROM leaf" {a 2 b 1}

fkey2-2-test 28 0 "BEGIN"
fkey2-2-test 29 0   "INSERT INTO leaf VALUES('c', 1)"
fkey2-2-test 30 0   "SAVEPOINT save"
fkey2-2-test 31 0     "DELETE FROM node WHERE nodeid = 1"
fkey2-2-test 32 1   "RELEASE save"
fkey2-2-test 33 1   "DELETE FROM leaf WHERE cellid = 'b'"
fkey2-2-test 34 0   "DELETE FROM leaf WHERE cellid = 'c'"
fkey2-2-test 35 0 "COMMIT"
fkey2-2-test 36 0 "SELECT * FROM node" {2 {}} 
fkey2-2-test 37 0 "SELECT * FROM leaf" {a 2}

fkey2-2-test 38 0 "SAVEPOINT outer"
fkey2-2-test 39 1   "INSERT INTO leaf VALUES('d', 3)"
fkey2-2-test 40 1 "RELEASE outer"    FKV
fkey2-2-test 41 1   "INSERT INTO leaf VALUES('e', 3)"
fkey2-2-test 42 0   "INSERT INTO node VALUES(3, 2)"
fkey2-2-test 43 0 "RELEASE outer"

fkey2-2-test 44 0 "SAVEPOINT outer"
fkey2-2-test 45 1   "DELETE FROM node WHERE nodeid=3"
fkey2-2-test 47 0   "INSERT INTO node VALUES(3, 2)"
fkey2-2-test 48 0 "ROLLBACK TO outer"
fkey2-2-test 49 0 "RELEASE outer"

fkey2-2-test 50 0 "SAVEPOINT outer"
fkey2-2-test 51 1   "INSERT INTO leaf VALUES('f', 4)"
fkey2-2-test 52 1   "SAVEPOINT inner"
fkey2-2-test 53 1     "INSERT INTO leaf VALUES('g', 4)"
fkey2-2-test 54 1  "RELEASE outer"   FKV
fkey2-2-test 55 1   "ROLLBACK TO inner"
fkey2-2-test 56 0  "COMMIT"          FKV
fkey2-2-test 57 0   "INSERT INTO node VALUES(4, NULL)"
fkey2-2-test 58 0 "RELEASE outer"
fkey2-2-test 59 0 "SELECT * FROM node" {2 {} 3 2 4 {}}
fkey2-2-test 60 0 "SELECT * FROM leaf" {a 2 d 3 e 3 f 4}

# The following set of tests check that if a statement that affects 
# multiple rows violates some foreign key constraints, then strikes a 
# constraint that causes the statement-transaction to be rolled back, 
# the deferred constraint counter is correctly reset to the value it 
# had before the statement-transaction was opened.
#
fkey2-2-test 61 0 "BEGIN"
fkey2-2-test 62 0   "DELETE FROM leaf"
fkey2-2-test 63 0   "DELETE FROM node"
fkey2-2-test 64 1   "INSERT INTO leaf VALUES('a', 1)"
fkey2-2-test 65 1   "INSERT INTO leaf VALUES('b', 2)"
fkey2-2-test 66 1   "INSERT INTO leaf VALUES('c', 1)"
do_test fkey2-2-test-67 {
  catchsql          "INSERT INTO node SELECT parent, 3 FROM leaf"
} {1 {column nodeid is not unique}}
fkey2-2-test 68 0 "COMMIT"           FKV
fkey2-2-test 69 1   "INSERT INTO node VALUES(1, NULL)"
fkey2-2-test 70 0   "INSERT INTO node VALUES(2, NULL)"
fkey2-2-test 71 0 "COMMIT"

fkey2-2-test 72 0 "BEGIN"
fkey2-2-test 73 1   "DELETE FROM node"
fkey2-2-test 74 0   "INSERT INTO node(nodeid) SELECT DISTINCT parent FROM leaf"
fkey2-2-test 75 0 "COMMIT"

#-------------------------------------------------------------------------
# Test cases fkey2-3.* test that a program that executes foreign key
# actions (CASCADE, SET DEFAULT, SET NULL etc.) or tests FK constraints
# opens a statement transaction if required.
#
# fkey2-3.1.*: Test UPDATE statements.
# fkey2-3.2.*: Test DELETE statements.
#
drop_all_tables
do_test fkey2-3.1.1 {
  execsql {
    CREATE TABLE ab(a PRIMARY KEY, b);
    CREATE TABLE cd(
      c PRIMARY KEY REFERENCES ab ON UPDATE CASCADE ON DELETE CASCADE, 
      d
    );
    CREATE TABLE ef(
      e REFERENCES cd ON UPDATE CASCADE, 
      f, CHECK (e!=5)
    );
  }
} {}
do_test fkey2-3.1.2 {
  execsql {
    INSERT INTO ab VALUES(1, 'b');
    INSERT INTO cd VALUES(1, 'd');
    INSERT INTO ef VALUES(1, 'e');
  }
} {}
do_test fkey2-3.1.3 {
  catchsql { UPDATE ab SET a = 5 }
} {1 {constraint failed}}
do_test fkey2-3.1.4 {
  execsql { SELECT * FROM ab }
} {1 b}
do_test fkey2-3.1.4 {
  execsql BEGIN;
  catchsql { UPDATE ab SET a = 5 }
} {1 {constraint failed}}
do_test fkey2-3.1.5 {
  execsql COMMIT;
  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
} {1 b 1 d 1 e}
do_test fkey2-3.2.1 {
  execsql BEGIN;
  catchsql { DELETE FROM ab }
} {1 {foreign key constraint failed}}
do_test fkey2-3.2.2 {
  execsql COMMIT
  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
} {1 b 1 d 1 e}

#-------------------------------------------------------------------------
# Test cases fkey2-4.* test that recursive foreign key actions 
# (i.e. CASCADE) are allowed even if recursive triggers are disabled.
#
drop_all_tables
do_test fkey2-4.1 {
  execsql {
    CREATE TABLE t1(
      node PRIMARY KEY, 
      parent REFERENCES t1 ON DELETE CASCADE
    );
    CREATE TABLE t2(node PRIMARY KEY, parent);
    CREATE TRIGGER t2t AFTER DELETE ON t2 BEGIN
      DELETE FROM t2 WHERE parent = old.node;
    END;
    INSERT INTO t1 VALUES(1, NULL);
    INSERT INTO t1 VALUES(2, 1);
    INSERT INTO t1 VALUES(3, 1);
    INSERT INTO t1 VALUES(4, 2);
    INSERT INTO t1 VALUES(5, 2);
    INSERT INTO t1 VALUES(6, 3);
    INSERT INTO t1 VALUES(7, 3);
    INSERT INTO t2 SELECT * FROM t1;
  }
} {}
do_test fkey2-4.2 {
  execsql { PRAGMA recursive_triggers = off }
  execsql { 
    BEGIN;
      DELETE FROM t1 WHERE node = 1;
      SELECT node FROM t1;
  }
} {}
do_test fkey2-4.3 {
  execsql { 
      DELETE FROM t2 WHERE node = 1;
      SELECT node FROM t2;
    ROLLBACK;
  }
} {4 5 6 7}
do_test fkey2-4.4 {
  execsql { PRAGMA recursive_triggers = on }
  execsql { 
    BEGIN;
      DELETE FROM t1 WHERE node = 1;
      SELECT node FROM t1;
  }
} {}
do_test fkey2-4.3 {
  execsql { 
      DELETE FROM t2 WHERE node = 1;
      SELECT node FROM t2;
    ROLLBACK;
  }
} {}

#-------------------------------------------------------------------------
# Test cases fkey2-5.* verify that the incremental blob API may not
# write to a foreign key column while foreign-keys are enabled.
#
drop_all_tables
do_test fkey2-5.1 {
  execsql {
    CREATE TABLE t1(a PRIMARY KEY, b);
    CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1(a));
    INSERT INTO t1 VALUES('hello', 'world');
    INSERT INTO t2 VALUES('key', 'hello');
  }
} {}
do_test fkey2-5.2 {
  set rc [catch { set fd [db incrblob t2 b 1] } msg]
  list $rc $msg
} {1 {cannot open foreign key column for writing}}
do_test fkey2-5.3 {
  set rc [catch { set fd [db incrblob -readonly t2 b 1] } msg]
  close $fd
  set rc
} {0}
do_test fkey2-5.4 {
  execsql { PRAGMA foreign_keys = off }
  set rc [catch { set fd [db incrblob t2 b 1] } msg]
  close $fd
  set rc
} {0}
do_test fkey2-5.5 {
  execsql { PRAGMA foreign_keys = on }
} {}

#-------------------------------------------------------------------------
# The following block of tests, those prefixed with "fkey2-genfkey.", are 
# the same tests that were used to test the ".genfkey" command provided 
# by the shell tool. So these tests show that the built-in foreign key 
# implementation is more or less compatible with the triggers generated 
# by genfkey.
#
drop_all_tables
do_test fkey2-genfkey.1.1 {
  execsql {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
    CREATE TABLE t2(e REFERENCES t1, f);
    CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));
  }
} {}
do_test fkey2-genfkey.1.2 {
  catchsql { INSERT INTO t2 VALUES(1, 2) }
} {1 {foreign key constraint failed}}
do_test fkey2-genfkey.1.3 {
  execsql {
    INSERT INTO t1 VALUES(1, 2, 3);
    INSERT INTO t2 VALUES(1, 2);
  }
} {}
do_test fkey2-genfkey.1.4 {
  execsql { INSERT INTO t2 VALUES(NULL, 3) }
} {}
do_test fkey2-genfkey.1.5 {
  catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL }
} {1 {foreign key constraint failed}}
do_test fkey2-genfkey.1.6 {
  execsql { UPDATE t2 SET e = 1 WHERE e IS NULL }
} {}
do_test fkey2-genfkey.1.7 {
  execsql { UPDATE t2 SET e = NULL WHERE f = 3 }
} {}
do_test fkey2-genfkey.1.8 {
  catchsql { UPDATE t1 SET a = 10 }
} {1 {foreign key constraint failed}}
do_test fkey2-genfkey.1.9 {
  catchsql { UPDATE t1 SET a = NULL }
} {1 {datatype mismatch}}
do_test fkey2-genfkey.1.10 {
  catchsql { DELETE FROM t1 }
} {1 {foreign key constraint failed}}
do_test fkey2-genfkey.1.11 {
  execsql { UPDATE t2 SET e = NULL }
} {}
do_test fkey2-genfkey.1.12 {
  execsql { 
    UPDATE t1 SET a = 10;
    DELETE FROM t1;
    DELETE FROM t2;
  }
} {}
do_test fkey2-genfkey.1.13 {
  execsql {
    INSERT INTO t3 VALUES(1, NULL, NULL);
    INSERT INTO t3 VALUES(1, 2, NULL);
    INSERT INTO t3 VALUES(1, NULL, 3);
  }
} {}
do_test fkey2-genfkey.1.14 {
  catchsql { INSERT INTO t3 VALUES(3, 1, 4) }
} {1 {foreign key constraint failed}}
do_test fkey2-genfkey.1.15 {
  execsql { 
    INSERT INTO t1 VALUES(1, 1, 4);
    INSERT INTO t3 VALUES(3, 1, 4);
  }
} {}
do_test fkey2-genfkey.1.16 {
  catchsql { DELETE FROM t1 }
} {1 {foreign key constraint failed}}
do_test fkey2-genfkey.1.17 {
  catchsql { UPDATE t1 SET b = 10}
} {1 {foreign key constraint failed}}
do_test fkey2-genfkey.1.18 {
  execsql { UPDATE t1 SET a = 10}
} {}
do_test fkey2-genfkey.1.19 {
  catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3}
} {1 {foreign key constraint failed}}

drop_all_tables
do_test fkey2-genfkey.2.1 {
  execsql {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
    CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f);
    CREATE TABLE t3(g, h, i, 
        FOREIGN KEY (h, i) 
        REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE
    );
  }
} {}
do_test fkey2-genfkey.2.2 {
  execsql {
    INSERT INTO t1 VALUES(1, 2, 3);
    INSERT INTO t1 VALUES(4, 5, 6);
    INSERT INTO t2 VALUES(1, 'one');
    INSERT INTO t2 VALUES(4, 'four');
  }
} {}
do_test fkey2-genfkey.2.3 {
  execsql {
    UPDATE t1 SET a = 2 WHERE a = 1;
    SELECT * FROM t2;
  }
} {2 one 4 four}
do_test fkey2-genfkey.2.4 {
  execsql {
    DELETE FROM t1 WHERE a = 4;
    SELECT * FROM t2;
  }
} {2 one}
do_test fkey2-genfkey.2.5 {
  execsql {
    INSERT INTO t3 VALUES('hello', 2, 3);
    UPDATE t1 SET c = 2;
    SELECT * FROM t3;
  }
} {hello 2 2}
do_test fkey2-genfkey.2.6 {
  execsql {
    DELETE FROM t1;
    SELECT * FROM t3;
  }
} {}

drop_all_tables
do_test fkey2-genfkey.3.1 {
  execsql {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b));
    CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f);
    CREATE TABLE t3(g, h, i, 
        FOREIGN KEY (h, i) 
        REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL
    );
  }
} {}
do_test fkey2-genfkey.3.2 {
  execsql {
    INSERT INTO t1 VALUES(1, 2, 3);
    INSERT INTO t1 VALUES(4, 5, 6);
    INSERT INTO t2 VALUES(1, 'one');
    INSERT INTO t2 VALUES(4, 'four');
  }
} {}
do_test fkey2-genfkey.3.3 {
  execsql {
    UPDATE t1 SET a = 2 WHERE a = 1;
    SELECT * FROM t2;
  }
} {{} one 4 four}
do_test fkey2-genfkey.3.4 {
  execsql {
    DELETE FROM t1 WHERE a = 4;
    SELECT * FROM t2;
  }
} {{} one {} four}
do_test fkey2-genfkey.3.5 {
  execsql {
    INSERT INTO t3 VALUES('hello', 2, 3);
    UPDATE t1 SET c = 2;
    SELECT * FROM t3;
  }
} {hello {} {}}
do_test fkey2-genfkey.3.6 {
  execsql {
    UPDATE t3 SET h = 2, i = 2;
    DELETE FROM t1;
    SELECT * FROM t3;
  }
} {hello {} {}}

finish_test
Changes to test/pragma.test.
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
db nullvalue {}
ifcapable {foreignkey} {
  do_test pragma-6.3.1 {
    execsql {
      CREATE TABLE t3(a int references t2(b), b UNIQUE);
      pragma foreign_key_list(t3);
    }
  } {0 0 t2 a b RESTRICT RESTRICT NONE}
  do_test pragma-6.3.2 {
    execsql {
      pragma foreign_key_list;
    }
  } {}
  do_test pragma-6.3.3 {
    execsql {







|







529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
db nullvalue {}
ifcapable {foreignkey} {
  do_test pragma-6.3.1 {
    execsql {
      CREATE TABLE t3(a int references t2(b), b UNIQUE);
      pragma foreign_key_list(t3);
    }
  } {0 0 t2 a b {NO ACTION} {NO ACTION} NONE}
  do_test pragma-6.3.2 {
    execsql {
      pragma foreign_key_list;
    }
  } {}
  do_test pragma-6.3.3 {
    execsql {
test/progress.test became executable.
Changes to test/trigger3.test.
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
catchsql { pragma recursive_triggers = off } 

# Test that we can cause ROLLBACK, FAIL and ABORT correctly
#
catchsql { CREATE TABLE tbl(a, b ,c) }
execsql {
    CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE 
	WHEN (new.a = 4) THEN RAISE(IGNORE) END;
    END;

    CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE 
	WHEN (new.a = 1) THEN RAISE(ABORT,    'Trigger abort') 
	WHEN (new.a = 2) THEN RAISE(FAIL,     'Trigger fail') 
	WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END;
    END;
}
# ABORT
do_test trigger3-1.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (1, 5, 6);
    }
} {1 {Trigger abort}}
do_test trigger3-1.2 {
    execsql {
	SELECT * FROM tbl;
	ROLLBACK;
    }
} {5 5 6}
do_test trigger3-1.3 {
    execsql {SELECT * FROM tbl}
} {}

# FAIL
do_test trigger3-2.1 {
    catchsql {
        BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (2, 5, 6);
    }
} {1 {Trigger fail}}
do_test trigger3-2.2 {
    execsql {
	SELECT * FROM tbl;
	ROLLBACK;
    }
} {5 5 6 2 5 6}
# ROLLBACK
do_test trigger3-3.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (3, 5, 6);
    }
} {1 {Trigger rollback}}
do_test trigger3-3.2 {
    execsql {
	SELECT * FROM tbl;
    }
} {}

# Verify that a ROLLBACK trigger works like a FAIL trigger if
# we are not within a transaction.  Ticket #3035.
#
do_test trigger3-3.3 {
    catchsql {COMMIT}
    catchsql {
        INSERT INTO tbl VALUES (3, 9, 10);
    }
} {1 {Trigger rollback}}
do_test trigger3-3.4 {
    execsql {SELECT * FROM tbl}
} {}

# IGNORE
do_test trigger3-4.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (4, 5, 6);
    }
} {0 {}}
do_test trigger3-4.2 {
    execsql {
	SELECT * FROM tbl;
	ROLLBACK;
    }
} {5 5 6}

# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE
execsql {DROP TABLE tbl;}
execsql {CREATE TABLE tbl (a, b, c);}
execsql {INSERT INTO tbl VALUES(1, 2, 3);}
execsql {INSERT INTO tbl VALUES(4, 5, 6);}
execsql {
    CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN
	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
    END;

    CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN
	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
    END;
}
do_test trigger3-5.1 {
    execsql {
	UPDATE tbl SET c = 10;
	SELECT * FROM tbl;
    }
} {1 2 3 4 5 10}
do_test trigger3-5.2 {
    execsql {
	DELETE FROM tbl;
	SELECT * FROM tbl;
    }
} {1 2 3}

# Check that RAISE(IGNORE) works correctly for nested triggers:
execsql {CREATE TABLE tbl2(a, b, c)}
execsql {
    CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN
	UPDATE tbl SET c = 10;
        INSERT INTO tbl2 VALUES (new.a, new.b, new.c);
    END;
}
do_test trigger3-6 {
    execsql {
	INSERT INTO tbl2 VALUES (1, 2, 3);
	SELECT * FROM tbl2;
	SELECT * FROM tbl;
    }
} {1 2 3 1 2 3 1 2 3}

# Check that things also work for view-triggers

ifcapable view {

execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl}
execsql {
    CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN
	SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')
	            WHEN (new.a = 2) THEN RAISE(IGNORE) 
	            WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;
    END;
}

do_test trigger3-7.1 {
    catchsql {
	INSERT INTO tbl_view VALUES(1, 2, 3);
    }
} {1 {View rollback}}
do_test trigger3-7.2 {
    catchsql {
	INSERT INTO tbl_view VALUES(2, 2, 3);
    }
} {0 {}}
do_test trigger3-7.3 {
    catchsql {
	INSERT INTO tbl_view VALUES(3, 2, 3);
    }
} {1 {View abort}}

} ;# ifcapable view

integrity_check trigger3-8.1

catchsql { DROP TABLE tbl; } 
catchsql { DROP TABLE tbl2; } 
catchsql { DROP VIEW tbl_view; }

finish_test







|



|
|
|





|






|
|
















|
|





|






|



















|






|
|










|



|




|
|




|
|







|





|
|
|










|
|
|





|




|




|












24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
catchsql { pragma recursive_triggers = off } 

# Test that we can cause ROLLBACK, FAIL and ABORT correctly
#
catchsql { CREATE TABLE tbl(a, b ,c) }
execsql {
    CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE 
        WHEN (new.a = 4) THEN RAISE(IGNORE) END;
    END;

    CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE 
        WHEN (new.a = 1) THEN RAISE(ABORT,    'Trigger abort') 
        WHEN (new.a = 2) THEN RAISE(FAIL,     'Trigger fail') 
        WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END;
    END;
}
# ABORT
do_test trigger3-1.1 {
    catchsql {
        BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (1, 5, 6);
    }
} {1 {Trigger abort}}
do_test trigger3-1.2 {
    execsql {
        SELECT * FROM tbl;
        ROLLBACK;
    }
} {5 5 6}
do_test trigger3-1.3 {
    execsql {SELECT * FROM tbl}
} {}

# FAIL
do_test trigger3-2.1 {
    catchsql {
        BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (2, 5, 6);
    }
} {1 {Trigger fail}}
do_test trigger3-2.2 {
    execsql {
        SELECT * FROM tbl;
        ROLLBACK;
    }
} {5 5 6 2 5 6}
# ROLLBACK
do_test trigger3-3.1 {
    catchsql {
        BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (3, 5, 6);
    }
} {1 {Trigger rollback}}
do_test trigger3-3.2 {
    execsql {
        SELECT * FROM tbl;
    }
} {}

# Verify that a ROLLBACK trigger works like a FAIL trigger if
# we are not within a transaction.  Ticket #3035.
#
do_test trigger3-3.3 {
    catchsql {COMMIT}
    catchsql {
        INSERT INTO tbl VALUES (3, 9, 10);
    }
} {1 {Trigger rollback}}
do_test trigger3-3.4 {
    execsql {SELECT * FROM tbl}
} {}

# IGNORE
do_test trigger3-4.1 {
    catchsql {
        BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (4, 5, 6);
    }
} {0 {}}
do_test trigger3-4.2 {
    execsql {
        SELECT * FROM tbl;
        ROLLBACK;
    }
} {5 5 6}

# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE
execsql {DROP TABLE tbl;}
execsql {CREATE TABLE tbl (a, b, c);}
execsql {INSERT INTO tbl VALUES(1, 2, 3);}
execsql {INSERT INTO tbl VALUES(4, 5, 6);}
execsql {
    CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN
        SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
    END;

    CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN
        SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
    END;
}
do_test trigger3-5.1 {
    execsql {
        UPDATE tbl SET c = 10;
        SELECT * FROM tbl;
    }
} {1 2 3 4 5 10}
do_test trigger3-5.2 {
    execsql {
        DELETE FROM tbl;
        SELECT * FROM tbl;
    }
} {1 2 3}

# Check that RAISE(IGNORE) works correctly for nested triggers:
execsql {CREATE TABLE tbl2(a, b, c)}
execsql {
    CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN
        UPDATE tbl SET c = 10;
        INSERT INTO tbl2 VALUES (new.a, new.b, new.c);
    END;
}
do_test trigger3-6 {
    execsql {
        INSERT INTO tbl2 VALUES (1, 2, 3);
        SELECT * FROM tbl2;
        SELECT * FROM tbl;
    }
} {1 2 3 1 2 3 1 2 3}

# Check that things also work for view-triggers

ifcapable view {

execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl}
execsql {
    CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN
        SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')
                    WHEN (new.a = 2) THEN RAISE(IGNORE) 
                    WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;
    END;
}

do_test trigger3-7.1 {
    catchsql {
        INSERT INTO tbl_view VALUES(1, 2, 3);
    }
} {1 {View rollback}}
do_test trigger3-7.2 {
    catchsql {
        INSERT INTO tbl_view VALUES(2, 2, 3);
    }
} {0 {}}
do_test trigger3-7.3 {
    catchsql {
        INSERT INTO tbl_view VALUES(3, 2, 3);
    }
} {1 {View abort}}

} ;# ifcapable view

integrity_check trigger3-8.1

catchsql { DROP TABLE tbl; } 
catchsql { DROP TABLE tbl2; } 
catchsql { DROP VIEW tbl_view; }

finish_test
tool/mkopts.tcl became executable.