/ Check-in [af128862]
Login

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

Overview
Comment:Correctly handle self-referential foreign keys on WITHOUT ROWID tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1: af128862ab6008df9dda1ee90f93f9efd629e259
User & Date: drh 2013-11-04 13:56:00
Context
2013-11-04
15:23
Correctly handle changing counting when inserting and deleting on WITHOUT ROWID tables. Add more FOREIGN KEY test cases. check-in: d072bcd0 user: drh tags: omit-rowid
13:56
Correctly handle self-referential foreign keys on WITHOUT ROWID tables. check-in: af128862 user: drh tags: omit-rowid
2013-11-03
02:27
Improved comments on foreign key logic. check-in: 1315d910 user: drh tags: omit-rowid
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/fkey.c.

   441    441       sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
   442    442     }
   443    443   
   444    444     sqlite3VdbeResolveLabel(v, iOk);
   445    445     sqlite3VdbeAddOp1(v, OP_Close, iCur);
   446    446   }
   447    447   
          448  +
          449  +/*
          450  +** Return an Expr object that refers to a memory register corresponding
          451  +** to column iCol of table pTab.
          452  +**
          453  +** regBase is the first of an array of register that contains the data
          454  +** for pTab.  regBase itself holds the rowid.  regBase+1 holds the first
          455  +** column.  regBase+2 holds the second column, and so forth.
          456  +*/
          457  +static Expr *exprTableRegister(
          458  +  Parse *pParse,     /* Parsing and code generating context */
          459  +  Table *pTab,       /* The table whose content is at r[regBase]... */
          460  +  int regBase,       /* Contents of table pTab */
          461  +  i16 iCol           /* Which column of pTab is desired */
          462  +){
          463  +  Expr *pExpr;
          464  +  Column *pCol;
          465  +  const char *zColl;
          466  +  sqlite3 *db = pParse->db;
          467  +
          468  +  pExpr = sqlite3Expr(db, TK_REGISTER, 0);
          469  +  if( pExpr ){
          470  +    if( iCol>=0 && iCol!=pTab->iPKey ){
          471  +      pCol = &pTab->aCol[iCol];
          472  +      pExpr->iTable = regBase + iCol + 1;
          473  +      pExpr->affinity = pCol->affinity;
          474  +      zColl = pCol->zColl;
          475  +      if( zColl==0 ) zColl = db->pDfltColl->zName;
          476  +      pExpr = sqlite3ExprAddCollateString(pParse, pExpr, zColl);
          477  +    }else{
          478  +      pExpr->iTable = regBase;
          479  +      pExpr->affinity = SQLITE_AFF_INTEGER;
          480  +    }
          481  +  }
          482  +  return pExpr;
          483  +}
          484  +
          485  +/*
          486  +** Return an Expr object that refers to column iCol of table pTab which
          487  +** has cursor iCur.
          488  +*/
          489  +static Expr *exprTableColumn(
          490  +  sqlite3 *db,      /* The database connection */
          491  +  Table *pTab,      /* The table whose column is desired */
          492  +  int iCursor,      /* The open cursor on the table */
          493  +  i16 iCol          /* The column that is wanted */
          494  +){
          495  +  Expr *pExpr = sqlite3Expr(db, TK_COLUMN, 0);
          496  +  if( pExpr ){
          497  +    pExpr->pTab = pTab;
          498  +    pExpr->iTable = iCursor;
          499  +    pExpr->iColumn = iCol;
          500  +  }
          501  +  return pExpr;
          502  +}
          503  +
   448    504   /*
   449    505   ** This function is called to generate code executed when a row is deleted
   450    506   ** from the parent table of foreign key constraint pFKey and, if pFKey is 
   451    507   ** deferred, when a row is inserted into the same table. When generating
   452    508   ** code for an SQL UPDATE operation, this function may be called twice -
   453    509   ** once to "delete" the old row and once to "insert" the new row.
   454    510   **
................................................................................
   510    566     for(i=0; i<pFKey->nCol; i++){
   511    567       Expr *pLeft;                  /* Value from parent table row */
   512    568       Expr *pRight;                 /* Column ref to child table */
   513    569       Expr *pEq;                    /* Expression (pLeft = pRight) */
   514    570       i16 iCol;                     /* Index of column in child table */ 
   515    571       const char *zCol;             /* Name of column in child table */
   516    572   
   517         -    pLeft = sqlite3Expr(db, TK_REGISTER, 0);
   518         -    if( pLeft ){
   519         -      /* Set the collation sequence and affinity of the LHS of each TK_EQ
   520         -      ** expression to the parent key column defaults.  */
   521         -      if( pIdx ){
   522         -        Column *pCol;
   523         -        const char *zColl;
   524         -        iCol = pIdx->aiColumn[i];
   525         -        pCol = &pTab->aCol[iCol];
   526         -        if( pTab->iPKey==iCol ) iCol = -1;
   527         -        pLeft->iTable = regData+iCol+1;
   528         -        pLeft->affinity = pCol->affinity;
   529         -        zColl = pCol->zColl;
   530         -        if( zColl==0 ) zColl = db->pDfltColl->zName;
   531         -        pLeft = sqlite3ExprAddCollateString(pParse, pLeft, zColl);
   532         -      }else{
   533         -        pLeft->iTable = regData;
   534         -        pLeft->affinity = SQLITE_AFF_INTEGER;
   535         -      }
   536         -    }
          573  +    iCol = pIdx ? pIdx->aiColumn[i] : -1;
          574  +    pLeft = exprTableRegister(pParse, pTab, regData, iCol);
   537    575       iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
   538    576       assert( iCol>=0 );
   539    577       zCol = pFKey->pFrom->aCol[iCol].zName;
   540    578       pRight = sqlite3Expr(db, TK_ID, zCol);
   541    579       pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight, 0);
   542    580       pWhere = sqlite3ExprAnd(db, pWhere, pEq);
   543    581     }
   544    582   
   545         -  /* If the child table is the same as the parent table, and this scan
   546         -  ** is taking place as part of a DELETE operation (operation D.2), omit the
   547         -  ** row being deleted from the scan by adding ($rowid != rowid) to the WHERE 
   548         -  ** clause, where $rowid is the rowid of the row being deleted.  */
   549         -  if( pTab==pFKey->pFrom && nIncr>0 && HasRowid(pTab) /*FIXME*/ ){
          583  +  /* If the child table is the same as the parent table, then add terms
          584  +  ** to the WHERE clause that prevent this entry from being scanned.
          585  +  ** The added WHERE clause terms are like this:
          586  +  **
          587  +  **     $current_rowid!=rowid
          588  +  **     NOT( $current_a==a AND $current_b==b AND ... )
          589  +  **
          590  +  ** The first form is used for rowid tables.  The second form is used
          591  +  ** for WITHOUT ROWID tables.  In the second form, the primary key is
          592  +  ** (a,b,...)
          593  +  */
          594  +  if( pTab==pFKey->pFrom && nIncr>0 ){
   550    595       Expr *pNe;                    /* Expression (pLeft != pRight) */
   551    596       Expr *pLeft;                  /* Value from parent table row */
   552    597       Expr *pRight;                 /* Column ref to child table */
   553         -    pLeft = sqlite3Expr(db, TK_REGISTER, 0);
   554         -    pRight = sqlite3Expr(db, TK_COLUMN, 0);
   555         -    if( pLeft && pRight ){
   556         -      pLeft->iTable = regData;
   557         -      pLeft->affinity = SQLITE_AFF_INTEGER;
   558         -      pRight->iTable = pSrc->a[0].iCursor;
   559         -      pRight->iColumn = -1;
          598  +    if( HasRowid(pTab) ){
          599  +      pLeft = exprTableRegister(pParse, pTab, regData, -1);
          600  +      pRight = exprTableColumn(db, pTab, pSrc->a[0].iCursor, -1);
          601  +      pNe = sqlite3PExpr(pParse, TK_NE, pLeft, pRight, 0);
          602  +    }else{
          603  +      int i;
          604  +      Expr *pEq, *pAll = 0;
          605  +      Index *pPk = sqlite3PrimaryKeyIndex(pTab);
          606  +      for(i=0; i<pPk->nKeyCol; i++){
          607  +        i16 iCol = pIdx->aiColumn[i];
          608  +        pLeft = exprTableRegister(pParse, pTab, regData, iCol);
          609  +        pRight = exprTableColumn(db, pTab, pSrc->a[0].iCursor, iCol);
          610  +        pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight, 0);
          611  +        pAll = sqlite3ExprAnd(db, pAll, pEq);
          612  +      }
          613  +      pNe = sqlite3PExpr(pParse, TK_NOT, pAll, 0, 0);
   560    614       }
   561         -    pNe = sqlite3PExpr(pParse, TK_NE, pLeft, pRight, 0);
   562    615       pWhere = sqlite3ExprAnd(db, pWhere, pNe);
   563    616     }
   564    617   
   565    618     /* Resolve the references in the WHERE clause. */
   566    619     memset(&sNameContext, 0, sizeof(NameContext));
   567    620     sNameContext.pSrcList = pSrc;
   568    621     sNameContext.pParse = pParse;