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 |
Timelines: | family | ancestors | descendants | both | omit-rowid |
Files: | files | file ages | folders |
SHA1: |
af128862ab6008df9dda1ee90f93f9ef |
User & Date: | drh 2013-11-04 13:56:00.182 |
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: d072bcd0a8 user: drh tags: omit-rowid) | |
13:56 | Correctly handle self-referential foreign keys on WITHOUT ROWID tables. (check-in: af128862ab user: drh tags: omit-rowid) | |
2013-11-03
| ||
02:27 | Improved comments on foreign key logic. (check-in: 1315d9109c user: drh tags: omit-rowid) | |
Changes
Changes to src/fkey.c.
︙ | ︙ | |||
441 442 443 444 445 446 447 448 449 450 451 452 453 454 | sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr); } sqlite3VdbeResolveLabel(v, iOk); sqlite3VdbeAddOp1(v, OP_Close, iCur); } /* ** This function is called to generate code executed when a row is deleted ** from the parent table of foreign key constraint pFKey and, if pFKey is ** deferred, when a row is inserted into the same table. When generating ** code for an SQL UPDATE operation, this function may be called twice - ** once to "delete" the old row and once to "insert" the new row. ** | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr); } sqlite3VdbeResolveLabel(v, iOk); sqlite3VdbeAddOp1(v, OP_Close, iCur); } /* ** Return an Expr object that refers to a memory register corresponding ** to column iCol of table pTab. ** ** regBase is the first of an array of register that contains the data ** for pTab. regBase itself holds the rowid. regBase+1 holds the first ** column. regBase+2 holds the second column, and so forth. */ static Expr *exprTableRegister( Parse *pParse, /* Parsing and code generating context */ Table *pTab, /* The table whose content is at r[regBase]... */ int regBase, /* Contents of table pTab */ i16 iCol /* Which column of pTab is desired */ ){ Expr *pExpr; Column *pCol; const char *zColl; sqlite3 *db = pParse->db; pExpr = sqlite3Expr(db, TK_REGISTER, 0); if( pExpr ){ if( iCol>=0 && iCol!=pTab->iPKey ){ pCol = &pTab->aCol[iCol]; pExpr->iTable = regBase + iCol + 1; pExpr->affinity = pCol->affinity; zColl = pCol->zColl; if( zColl==0 ) zColl = db->pDfltColl->zName; pExpr = sqlite3ExprAddCollateString(pParse, pExpr, zColl); }else{ pExpr->iTable = regBase; pExpr->affinity = SQLITE_AFF_INTEGER; } } return pExpr; } /* ** Return an Expr object that refers to column iCol of table pTab which ** has cursor iCur. */ static Expr *exprTableColumn( sqlite3 *db, /* The database connection */ Table *pTab, /* The table whose column is desired */ int iCursor, /* The open cursor on the table */ i16 iCol /* The column that is wanted */ ){ Expr *pExpr = sqlite3Expr(db, TK_COLUMN, 0); if( pExpr ){ pExpr->pTab = pTab; pExpr->iTable = iCursor; pExpr->iColumn = iCol; } return pExpr; } /* ** This function is called to generate code executed when a row is deleted ** from the parent table of foreign key constraint pFKey and, if pFKey is ** deferred, when a row is inserted into the same table. When generating ** code for an SQL UPDATE operation, this function may be called twice - ** once to "delete" the old row and once to "insert" the new row. ** |
︙ | ︙ | |||
510 511 512 513 514 515 516 | for(i=0; i<pFKey->nCol; i++){ Expr *pLeft; /* Value from parent table row */ Expr *pRight; /* Column ref to child table */ Expr *pEq; /* Expression (pLeft = pRight) */ i16 iCol; /* Index of column in child table */ const char *zCol; /* Name of column in child table */ | < < < < < < < | < < < < < < | < < < < < | < | | > > > > > > > > | > | > | > | | > > > | | | > | | > | 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 | for(i=0; i<pFKey->nCol; i++){ Expr *pLeft; /* Value from parent table row */ Expr *pRight; /* Column ref to child table */ Expr *pEq; /* Expression (pLeft = pRight) */ i16 iCol; /* Index of column in child table */ const char *zCol; /* Name of column in child table */ iCol = pIdx ? pIdx->aiColumn[i] : -1; pLeft = exprTableRegister(pParse, pTab, regData, iCol); iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom; assert( iCol>=0 ); 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); } /* If the child table is the same as the parent table, then add terms ** to the WHERE clause that prevent this entry from being scanned. ** The added WHERE clause terms are like this: ** ** $current_rowid!=rowid ** NOT( $current_a==a AND $current_b==b AND ... ) ** ** The first form is used for rowid tables. The second form is used ** for WITHOUT ROWID tables. In the second form, the primary key is ** (a,b,...) */ if( pTab==pFKey->pFrom && nIncr>0 ){ Expr *pNe; /* Expression (pLeft != pRight) */ Expr *pLeft; /* Value from parent table row */ Expr *pRight; /* Column ref to child table */ if( HasRowid(pTab) ){ pLeft = exprTableRegister(pParse, pTab, regData, -1); pRight = exprTableColumn(db, pTab, pSrc->a[0].iCursor, -1); pNe = sqlite3PExpr(pParse, TK_NE, pLeft, pRight, 0); }else{ int i; Expr *pEq, *pAll = 0; Index *pPk = sqlite3PrimaryKeyIndex(pTab); for(i=0; i<pPk->nKeyCol; i++){ i16 iCol = pIdx->aiColumn[i]; pLeft = exprTableRegister(pParse, pTab, regData, iCol); pRight = exprTableColumn(db, pTab, pSrc->a[0].iCursor, iCol); pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight, 0); pAll = sqlite3ExprAnd(db, pAll, pEq); } pNe = sqlite3PExpr(pParse, TK_NOT, pAll, 0, 0); } pWhere = sqlite3ExprAnd(db, pWhere, pNe); } /* Resolve the references in the WHERE clause. */ memset(&sNameContext, 0, sizeof(NameContext)); sNameContext.pSrcList = pSrc; sNameContext.pParse = pParse; |
︙ | ︙ |