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

Changes to src/delete.c.

   338    338   
   339    339   #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
   340    340     /* Special case: A DELETE without a WHERE clause deletes everything.
   341    341     ** It is easier just to erase the whole table. Prior to version 3.6.5,
   342    342     ** this optimization caused the row change count (the value returned by 
   343    343     ** API function sqlite3_count_changes) to be set incorrectly.  */
   344    344     if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) 
   345         -   && 0==sqlite3FkRequired(pParse, pTab, 0)
          345  +   && 0==sqlite3FkRequired(pParse, pTab, 0, 0)
   346    346     ){
   347    347       assert( !isView );
   348    348       sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt,
   349    349                         pTab->zName, P4_STATIC);
   350    350       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   351    351         assert( pIdx->pSchema==pTab->pSchema );
   352    352         sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
................................................................................
   488    488     ** (this can happen if a trigger program has already deleted it), do
   489    489     ** not attempt to delete it or fire any DELETE triggers.  */
   490    490     iLabel = sqlite3VdbeMakeLabel(v);
   491    491     sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid);
   492    492    
   493    493     /* If there are any triggers to fire, allocate a range of registers to
   494    494     ** use for the old.* references in the triggers.  */
   495         -  if( sqlite3FkRequired(pParse, pTab, 0) || pTrigger ){
          495  +  if( sqlite3FkRequired(pParse, pTab, 0, 0) || pTrigger ){
   496    496       u32 mask;                     /* Mask of OLD.* columns in use */
   497    497       int iCol;                     /* Iterator used while populating OLD.* */
   498    498   
   499    499       /* TODO: Could use temporary registers here. Also could attempt to
   500    500       ** avoid copying the contents of the rowid register.  */
   501    501       mask = sqlite3TriggerOldmask(pParse, pTrigger, 0, pTab, onconf);
   502         -    mask |= sqlite3FkOldmask(pParse, pTab, 0);
          502  +    mask |= sqlite3FkOldmask(pParse, pTab);
   503    503       iOld = pParse->nMem+1;
   504    504       pParse->nMem += (1 + pTab->nCol);
   505    505   
   506    506       /* Populate the OLD.* pseudo-table register array. These values will be 
   507    507       ** used by any BEFORE and AFTER triggers that exist.  */
   508    508       sqlite3VdbeAddOp2(v, OP_Copy, iRowid, iOld);
   509    509       for(iCol=0; iCol<pTab->nCol; iCol++){
................................................................................
   524    524       ** being deleted. Do not attempt to delete the row a second time, and 
   525    525       ** do not fire AFTER triggers.  */
   526    526       sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid);
   527    527   
   528    528       /* Do FK processing. This call checks that any FK constraints that
   529    529       ** refer to this table (i.e. constraints attached to other tables) 
   530    530       ** are not violated by deleting this row.  */
   531         -    sqlite3FkCheck(pParse, pTab, 0, iOld, 0);
          531  +    sqlite3FkCheck(pParse, pTab, iOld, 0);
   532    532     }
   533    533   
   534    534     /* Delete the index and table entries. Skip this step if pTab is really
   535    535     ** a view (in which case the only effect of the DELETE statement is to
   536    536     ** fire the INSTEAD OF triggers).  */ 
   537    537     if( pTab->pSelect==0 ){
   538    538       sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, 0);

Changes to src/fkey.c.

   668    668   ** For an INSERT operation, regOld is passed zero and regNew is passed the
   669    669   ** first register of an array of (pTab->nCol+1) registers containing the new
   670    670   ** row data.
   671    671   **
   672    672   ** For an UPDATE operation, this function is called twice. Once before
   673    673   ** the original record is deleted from the table using the calling convention
   674    674   ** described for DELETE. Then again after the original record is deleted
   675         -** but before the new record is inserted using the INSERT convention. In
   676         -** both cases parameter pChanges is passed the list of columns being 
   677         -** updated by the statement.
          675  +** but before the new record is inserted using the INSERT convention. 
   678    676   */
   679    677   void sqlite3FkCheck(
   680    678     Parse *pParse,                  /* Parse context */
   681    679     Table *pTab,                    /* Row is being deleted from this table */ 
   682         -  ExprList *pChanges,             /* Changed columns if this is an UPDATE */
   683    680     int regOld,                     /* Previous row data is stored here */
   684    681     int regNew                      /* New row data is stored here */
   685    682   ){
   686    683     sqlite3 *db = pParse->db;       /* Database handle */
   687    684     Vdbe *v;                        /* VM to write code to */
   688    685     FKey *pFKey;                    /* Used to iterate through FKs */
   689    686     int iDb;                        /* Index of database containing pTab */
................................................................................
   721    718       }
   722    719       if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){
   723    720         if( !isIgnoreErrors || db->mallocFailed ) return;
   724    721         continue;
   725    722       }
   726    723       assert( pFKey->nCol==1 || (aiFree && pIdx) );
   727    724   
   728         -    /* If the key does not overlap with the pChanges list, skip this FK. */
   729         -    if( pChanges ){
   730         -      /* TODO */
   731         -    }
   732         -
   733    725       if( aiFree ){
   734    726         aiCol = aiFree;
   735    727       }else{
   736    728         iCol = pFKey->aCol[0].iFrom;
   737    729         aiCol = &iCol;
   738    730       }
   739    731       for(i=0; i<pFKey->nCol; i++){
................................................................................
   778    770   
   779    771       if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){
   780    772         if( !isIgnoreErrors || db->mallocFailed ) return;
   781    773         continue;
   782    774       }
   783    775       assert( aiCol || pFKey->nCol==1 );
   784    776   
   785         -    /* Check if this update statement has modified any of the child key 
   786         -    ** columns for this foreign key constraint. If it has not, there is 
   787         -    ** no need to search the child table for rows in violation. This is
   788         -    ** just an optimization. Things would work fine without this check.  */
   789         -    if( pChanges ){
   790         -      /* TODO */
   791         -    }
   792         -
   793    777       /* Create a SrcList structure containing a single table (the table 
   794    778       ** the foreign key that refers to this table is attached to). This
   795    779       ** is required for the sqlite3WhereXXX() interface.  */
   796    780       pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
   797    781       if( pSrc ){
   798    782         pSrc->a->pTab = pFKey->pFrom;
   799    783         pSrc->a->pTab->nRef++;
................................................................................
   818    802     }
   819    803   }
   820    804   
   821    805   #define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x)))
   822    806   
   823    807   /*
   824    808   ** This function is called before generating code to update or delete a 
   825         -** row contained in table pTab. If the operation is an update, then 
   826         -** pChanges is a pointer to the list of columns to modify. If this is a 
   827         -** delete, then pChanges is NULL.
          809  +** row contained in table pTab.
   828    810   */
   829    811   u32 sqlite3FkOldmask(
   830    812     Parse *pParse,                  /* Parse context */
   831         -  Table *pTab,                    /* Table being modified */
   832         -  ExprList *pChanges              /* Non-NULL for UPDATE operations */
          813  +  Table *pTab                     /* Table being modified */
   833    814   ){
   834    815     u32 mask = 0;
   835    816     if( pParse->db->flags&SQLITE_ForeignKeys ){
   836    817       FKey *p;
   837    818       int i;
   838    819       for(p=pTab->pFKey; p; p=p->pNextFrom){
   839    820         for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom);
................................................................................
   847    828       }
   848    829     }
   849    830     return mask;
   850    831   }
   851    832   
   852    833   /*
   853    834   ** This function is called before generating code to update or delete a 
   854         -** row contained in table pTab. If the operation is an update, then 
   855         -** pChanges is a pointer to the list of columns to modify. If this is a 
   856         -** delete, then pChanges is NULL.
          835  +** row contained in table pTab. If the operation is a DELETE, then
          836  +** parameter aChange is passed a NULL value. For an UPDATE, aChange points
          837  +** to an array of size N, where N is the number of columns in table pTab.
          838  +** If the i'th column is not modified by the UPDATE, then the corresponding 
          839  +** entry in the aChange[] array is set to -1. If the column is modified,
          840  +** the value is 0 or greater. Parameter chngRowid is set to true if the
          841  +** UPDATE statement modifies the rowid fields of the table.
   857    842   **
   858    843   ** If any foreign key processing will be required, this function returns
   859    844   ** true. If there is no foreign key related processing, this function 
   860    845   ** returns false.
   861    846   */
   862    847   int sqlite3FkRequired(
   863    848     Parse *pParse,                  /* Parse context */
   864    849     Table *pTab,                    /* Table being modified */
   865         -  ExprList *pChanges              /* Non-NULL for UPDATE operations */
          850  +  int *aChange,                   /* Non-NULL for UPDATE operations */
          851  +  int chngRowid                   /* True for UPDATE that affects rowid */
   866    852   ){
   867    853     if( pParse->db->flags&SQLITE_ForeignKeys ){
   868         -    if( sqlite3FkReferences(pTab) || pTab->pFKey ) return 1;
          854  +    if( !aChange ){
          855  +      /* A DELETE operation. Foreign key processing is required if the 
          856  +      ** table in question is either the child or parent table for any 
          857  +      ** foreign key constraint.  */
          858  +      return (sqlite3FkReferences(pTab) || pTab->pFKey);
          859  +    }else{
          860  +      /* This is an UPDATE. Foreign key processing is only required if the
          861  +      ** operation modifies one or more child or parent key columns. */
          862  +      int i;
          863  +      FKey *p;
          864  +
          865  +      /* Check if any child key columns are being modified. */
          866  +      for(p=pTab->pFKey; p; p=p->pNextFrom){
          867  +        for(i=0; i<p->nCol; i++){
          868  +          int iChildKey = p->aCol[i].iFrom;
          869  +          if( aChange[iChildKey]>=0 ) return 1;
          870  +          if( iChildKey==pTab->iPKey && chngRowid ) return 1;
          871  +        }
          872  +      }
          873  +
          874  +      /* Check if any parent key columns are being modified. */
          875  +      for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
          876  +        for(i=0; i<p->nCol; i++){
          877  +          char *zKey = p->aCol[i].zCol;
          878  +          int iKey;
          879  +          for(iKey=0; iKey<pTab->nCol; iKey++){
          880  +            Column *pCol = &pTab->aCol[iKey];
          881  +            if( (zKey ? !sqlite3StrICmp(pCol->zName, zKey) : pCol->isPrimKey) ){
          882  +              if( aChange[iKey]>=0 ) return 1;
          883  +              if( iKey==pTab->iPKey && chngRowid ) return 1;
          884  +            }
          885  +          }
          886  +        }
          887  +      }
          888  +    }
   869    889     }
   870    890     return 0;
   871    891   }
   872    892   
   873    893   /*
   874    894   ** This function is called when an UPDATE or DELETE operation is being 
   875    895   ** compiled on table pTab, which is the parent table of foreign-key pFKey.

Changes to src/insert.c.

   975    975       }else
   976    976   #endif
   977    977       {
   978    978         int isReplace;    /* Set to true if constraints may cause a replace */
   979    979         sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx,
   980    980             keyColumn>=0, 0, onError, endOfLoop, &isReplace
   981    981         );
   982         -      sqlite3FkCheck(pParse, pTab, 0, 0, regIns);
          982  +      sqlite3FkCheck(pParse, pTab, 0, regIns);
   983    983         sqlite3CompleteInsertion(
   984    984             pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0
   985    985         );
   986    986       }
   987    987     }
   988    988   
   989    989     /* Update the count of rows that are inserted
................................................................................
  1267   1267           ** the index b-tree entries only. The table b-tree entry will be 
  1268   1268           ** replaced by the new entry when it is inserted.  */
  1269   1269           Trigger *pTrigger = 0;
  1270   1270           if( pParse->db->flags&SQLITE_RecTriggers ){
  1271   1271             pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
  1272   1272           }
  1273   1273           sqlite3MultiWrite(pParse);
  1274         -        if( pTrigger || sqlite3FkRequired(pParse, pTab, 0) ){
         1274  +        if( pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0) ){
  1275   1275             sqlite3GenerateRowDelete(
  1276   1276                 pParse, pTab, baseCur, regRowid, 0, pTrigger, OE_Replace
  1277   1277             );
  1278   1278           }else{
  1279   1279             sqlite3GenerateRowIndexDelete(pParse, pTab, baseCur, 0);
  1280   1280           }
  1281   1281           seenReplace = 1;

Changes to src/sqliteInt.h.

  2945   2945   ** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
  2946   2946   ** key functionality is available. If OMIT_TRIGGER is defined but
  2947   2947   ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In
  2948   2948   ** this case foreign keys are parsed, but no other functionality is 
  2949   2949   ** provided (enforcement of FK constraints requires the triggers sub-system).
  2950   2950   */
  2951   2951   #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER)
  2952         -  void sqlite3FkCheck(Parse*, Table*, ExprList*, int, int);
         2952  +  void sqlite3FkCheck(Parse*, Table*, int, int);
  2953   2953     void sqlite3FkDropTable(Parse*, SrcList *, Table*);
  2954   2954     void sqlite3FkActions(Parse*, Table*, ExprList*, int);
  2955         -  int sqlite3FkRequired(Parse*, Table*, ExprList*);
  2956         -  u32 sqlite3FkOldmask(Parse*, Table*, ExprList*);
         2955  +  int sqlite3FkRequired(Parse*, Table*, int*, int);
         2956  +  u32 sqlite3FkOldmask(Parse*, Table*);
  2957   2957     FKey *sqlite3FkReferences(Table *);
  2958   2958   #else
  2959   2959     #define sqlite3FkActions(a,b,c,d)
  2960         -  #define sqlite3FkCheck(a,b,c,d,e)
         2960  +  #define sqlite3FkCheck(a,b,c,d)
  2961   2961     #define sqlite3FkDropTable(a,b,c)
  2962         -  #define sqlite3FkOldmask(a,b,c)  0
  2963         -  #define sqlite3FkRequired(a,b,c) 0
         2962  +  #define sqlite3FkOldmask(a,b)      0
         2963  +  #define sqlite3FkRequired(a,b,c,d) 0
  2964   2964   #endif
  2965   2965   #ifndef SQLITE_OMIT_FOREIGN_KEY
  2966   2966     void sqlite3FkDelete(Table*);
  2967   2967   #else
  2968   2968     #define sqlite3FkDelete(a)
  2969   2969   #endif
  2970   2970   

Changes to src/update.c.

   155    155   # define isView 0
   156    156   #endif
   157    157   #ifdef SQLITE_OMIT_VIEW
   158    158   # undef isView
   159    159   # define isView 0
   160    160   #endif
   161    161   
   162         -  hasFK = sqlite3FkRequired(pParse, pTab, pChanges);
   163         -
   164    162     if( sqlite3ViewGetColumnNames(pParse, pTab) ){
   165    163       goto update_cleanup;
   166    164     }
   167    165     if( sqlite3IsReadOnly(pParse, pTab, (pTrigger?1:0)) ){
   168    166       goto update_cleanup;
   169    167     }
   170    168     aXRef = sqlite3DbMallocRaw(db, sizeof(int) * pTab->nCol );
................................................................................
   225    223           goto update_cleanup;
   226    224         }else if( rc==SQLITE_IGNORE ){
   227    225           aXRef[j] = -1;
   228    226         }
   229    227       }
   230    228   #endif
   231    229     }
          230  +
          231  +  hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngRowid);
   232    232   
   233    233     /* Allocate memory for the array aRegIdx[].  There is one entry in the
   234    234     ** array for each index associated with table being updated.  Fill in
   235    235     ** the value with a register number for indices that are to be used
   236    236     ** and with zero for unused indices.
   237    237     */
   238    238     for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
................................................................................
   385    385       sqlite3ExprCode(pParse, pRowidExpr, regNewRowid);
   386    386       sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid);
   387    387     }
   388    388   
   389    389     /* If there are triggers on this table, populate an array of registers 
   390    390     ** with the required old.* column data.  */
   391    391     if( hasFK || pTrigger ){
   392         -    u32 oldmask = sqlite3FkOldmask(pParse, pTab, pChanges);
          392  +    u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0);
   393    393       oldmask |= sqlite3TriggerOldmask(pParse, pTrigger, pChanges, pTab, onError);
   394    394       for(i=0; i<pTab->nCol; i++){
   395    395         if( aXRef[i]<0 || oldmask==0xffffffff || (oldmask & (1<<i)) ){
   396    396           sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regOld+i);
   397    397           sqlite3ColumnDefault(v, pTab, i, regOld+i);
   398    398         }else{
   399    399           sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i);
................................................................................
   441    441     if( !isView ){
   442    442   
   443    443       /* Do constraint checks. */
   444    444       sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid,
   445    445           aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0);
   446    446   
   447    447       /* Do FK constraint checks. */
   448         -    sqlite3FkCheck(pParse, pTab, pChanges, regOldRowid, 0);
          448  +    if( hasFK ){
          449  +      sqlite3FkCheck(pParse, pTab, regOldRowid, 0);
          450  +    }
   449    451   
   450    452       /* Delete the index entries associated with the current record.  */
   451    453       j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid);
   452    454       sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx);
   453    455     
   454    456       /* If changing the record number, delete the old record.  */
   455    457       if( hasFK || chngRowid ){
   456    458         sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0);
   457    459       }
   458    460       sqlite3VdbeJumpHere(v, j1);
   459    461   
   460         -    sqlite3FkCheck(pParse, pTab, pChanges, 0, regNewRowid);
          462  +    if( hasFK ){
          463  +      sqlite3FkCheck(pParse, pTab, 0, regNewRowid);
          464  +    }
   461    465     
   462    466       /* Insert the new index entries and the new record. */
   463    467       sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0);
   464    468   
   465    469       /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
   466    470       ** handle rows (possibly in other tables) that refer via a foreign key
   467    471       ** to the row just updated. */ 
   468         -    sqlite3FkActions(pParse, pTab, pChanges, regOldRowid);
          472  +    if( hasFK ){
          473  +      sqlite3FkActions(pParse, pTab, pChanges, regOldRowid);
          474  +    }
   469    475     }
   470    476   
   471    477     /* Increment the row counter 
   472    478     */
   473    479     if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){
   474    480       sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
   475    481     }

Changes to test/fkey2.test.

    66     66   #             to check if this has repaired an outstanding violation.
    67     67   #
    68     68   # fkey2-16.*: Test that rows that refer to themselves may be inserted, 
    69     69   #             updated and deleted.
    70     70   #
    71     71   # fkey2-17.*: Test that the "count_changes" pragma does not interfere with
    72     72   #             FK constraint processing.
           73  +# 
           74  +# fkey2-18.*: Test that the authorization callback is invoked when processing
           75  +#             FK constraints.
    73     76   #
    74     77   # fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
    75     78   #            command. Recycled to test the built-in implementation.
    76     79   #
    77     80   
    78     81   
    79     82   proc drop_all_tables {{db db}} {
................................................................................
   132    135     4.7  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
   133    136     4.9  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
   134    137     4.10 "UPDATE t8 SET c=NULL WHERE d=4"   {0 {}}
   135    138     4.11 "DELETE FROM t7 WHERE b=1"         {1 {foreign key constraint failed}}
   136    139     4.12 "UPDATE t7 SET b = 2"              {1 {foreign key constraint failed}}
   137    140     4.13 "UPDATE t7 SET b = 1"              {0 {}}
   138    141     4.14 "INSERT INTO t8 VALUES('a', 'b')"  {1 {foreign key constraint failed}}
          142  +  4.15 "UPDATE t7 SET b = 5"              {1 {foreign key constraint failed}}
          143  +  4.16 "UPDATE t7 SET rowid = 5"          {1 {foreign key constraint failed}}
   139    144   
   140    145     5.1  "INSERT INTO t9 VALUES(1, 3)"      {1 {no such table: main.nosuchtable}}
   141    146     5.2  "INSERT INTO t10 VALUES(1, 3)"     {1 {foreign key mismatch}}
   142    147   }
   143    148   
   144    149   do_test fkey2-1.1.0 {
   145    150     execsql [string map {/D/ {}} $FkeySimpleSchema]
................................................................................
  1338   1343   } {1}
  1339   1344   do_test fkey2-17.2.9 {
  1340   1345     expr [db total_changes] - $nTotal
  1341   1346   } {4}
  1342   1347   do_test fkey2-17.2.10 {
  1343   1348     execsql { SELECT * FROM high ; SELECT * FROM low }
  1344   1349   } {}
         1350  +
         1351  +#-------------------------------------------------------------------------
         1352  +# Test that the authorization callback works.
         1353  +#
  1345   1354   
  1346   1355   
  1347   1356   execsql { PRAGMA count_changes = 0 }
  1348   1357   #-------------------------------------------------------------------------
  1349   1358   # The following block of tests, those prefixed with "fkey2-genfkey.", are 
  1350   1359   # the same tests that were used to test the ".genfkey" command provided 
  1351   1360   # by the shell tool. So these tests show that the built-in foreign key 

Changes to test/fkey3.test.

     1         -# 2001 September 15
            1  +# 2009 September 15
     2      2   #
     3      3   # The author disclaims copyright to this source code.  In place of
     4      4   # a legal notice, here is a blessing:
     5      5   #
     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.