Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Update the foreign_key_check pragma so that when a parent table is undefined it is treated as an empty table. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | 208b259ad73b51e7df163fee3d7ed2bd |
| User & Date: | drh 2013-10-14 14:30:02 |
Context
|
2013-10-14
| ||
| 15:41 | Fix for building with SQLITE_OMIT_FOREIGN_KEY. check-in: b8b5f6c8 user: dan tags: trunk | |
| 14:30 | Update the foreign_key_check pragma so that when a parent table is undefined it is treated as an empty table. check-in: 208b259a user: drh tags: trunk | |
| 14:21 | Corrects to comments on the STAT4 implementation. check-in: e06f74d3 user: drh tags: trunk | |
|
2013-10-12
| ||
| 19:06 | In "PRAGMA foreign_key_check", treat missing parent tables as empty (instead of as errors). Closed-Leaf check-in: 8c13a7fd user: dan tags: fkc-missing-parent-tables | |
Changes
Changes to src/pragma.c.
1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 .... 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 .... 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 |
if( pTab==0 || pTab->pFKey==0 ) continue;
sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
if( pTab->nCol+regRow>pParse->nMem ) pParse->nMem = pTab->nCol + regRow;
sqlite3OpenTable(pParse, 0, iDb, pTab, OP_OpenRead);
sqlite3VdbeAddOp4(v, OP_String8, 0, regResult, 0, pTab->zName,
P4_TRANSIENT);
for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
if( pParent==0 ) break;
pIdx = 0;
sqlite3TableLock(pParse, iDb, pParent->tnum, 0, pParent->zName);
x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, 0);
if( x==0 ){
if( pIdx==0 ){
sqlite3OpenTable(pParse, i, iDb, pParent, OP_OpenRead);
}else{
................................................................................
sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF);
}
}else{
k = 0;
break;
}
}
if( pFK ) break;
if( pParse->nTab<i ) pParse->nTab = i;
addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, 0);
for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
assert( pParent!=0 );
pIdx = 0;
aiCols = 0;
x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, &aiCols);
assert( x==0 );
addrOk = sqlite3VdbeMakeLabel(v);
if( pIdx==0 ){
int iKey = pFK->aCol[0].iFrom;
assert( iKey>=0 && iKey<pTab->nCol );
if( iKey!=pTab->iPKey ){
sqlite3VdbeAddOp3(v, OP_Column, 0, iKey, regRow);
sqlite3ColumnDefault(v, pTab, iKey, regRow);
sqlite3VdbeAddOp2(v, OP_IsNull, regRow, addrOk);
sqlite3VdbeAddOp2(v, OP_MustBeInt, regRow,
................................................................................
}
sqlite3VdbeAddOp3(v, OP_NotExists, i, 0, regRow);
sqlite3VdbeAddOp2(v, OP_Goto, 0, addrOk);
sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
}else{
for(j=0; j<pFK->nCol; j++){
sqlite3ExprCodeGetColumnOfTable(v, pTab, 0,
aiCols ? aiCols[j] : pFK->aCol[0].iFrom, regRow+j);
sqlite3VdbeAddOp2(v, OP_IsNull, regRow+j, addrOk);
}
sqlite3VdbeAddOp3(v, OP_MakeRecord, regRow, pFK->nCol, regKey);
sqlite3VdbeChangeP4(v, -1,
sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT);
sqlite3VdbeAddOp4Int(v, OP_Found, i, addrOk, regKey, 0);
}
sqlite3VdbeAddOp2(v, OP_Rowid, 0, regResult+1);
sqlite3VdbeAddOp4(v, OP_String8, 0, regResult+2, 0,
pFK->zTo, P4_TRANSIENT);
sqlite3VdbeAddOp2(v, OP_Integer, i-1, regResult+3);
sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 4);
sqlite3VdbeResolveLabel(v, addrOk);
|
| | > | < > | | > | | > | | | | > |
1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 .... 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 .... 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 |
if( pTab==0 || pTab->pFKey==0 ) continue;
sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
if( pTab->nCol+regRow>pParse->nMem ) pParse->nMem = pTab->nCol + regRow;
sqlite3OpenTable(pParse, 0, iDb, pTab, OP_OpenRead);
sqlite3VdbeAddOp4(v, OP_String8, 0, regResult, 0, pTab->zName,
P4_TRANSIENT);
for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
pParent = sqlite3FindTable(db, pFK->zTo, zDb);
if( pParent==0 ) continue;
pIdx = 0;
sqlite3TableLock(pParse, iDb, pParent->tnum, 0, pParent->zName);
x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, 0);
if( x==0 ){
if( pIdx==0 ){
sqlite3OpenTable(pParse, i, iDb, pParent, OP_OpenRead);
}else{
................................................................................
sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF);
}
}else{
k = 0;
break;
}
}
assert( pParse->nErr>0 || pFK==0 );
if( pFK ) break;
if( pParse->nTab<i ) pParse->nTab = i;
addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, 0);
for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
pParent = sqlite3FindTable(db, pFK->zTo, zDb);
pIdx = 0;
aiCols = 0;
if( pParent ){
x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, &aiCols);
assert( x==0 );
}
addrOk = sqlite3VdbeMakeLabel(v);
if( pParent && pIdx==0 ){
int iKey = pFK->aCol[0].iFrom;
assert( iKey>=0 && iKey<pTab->nCol );
if( iKey!=pTab->iPKey ){
sqlite3VdbeAddOp3(v, OP_Column, 0, iKey, regRow);
sqlite3ColumnDefault(v, pTab, iKey, regRow);
sqlite3VdbeAddOp2(v, OP_IsNull, regRow, addrOk);
sqlite3VdbeAddOp2(v, OP_MustBeInt, regRow,
................................................................................
}
sqlite3VdbeAddOp3(v, OP_NotExists, i, 0, regRow);
sqlite3VdbeAddOp2(v, OP_Goto, 0, addrOk);
sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
}else{
for(j=0; j<pFK->nCol; j++){
sqlite3ExprCodeGetColumnOfTable(v, pTab, 0,
aiCols ? aiCols[j] : pFK->aCol[j].iFrom, regRow+j);
sqlite3VdbeAddOp2(v, OP_IsNull, regRow+j, addrOk);
}
if( pParent ){
sqlite3VdbeAddOp3(v, OP_MakeRecord, regRow, pFK->nCol, regKey);
sqlite3VdbeChangeP4(v, -1,
sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT);
sqlite3VdbeAddOp4Int(v, OP_Found, i, addrOk, regKey, 0);
}
}
sqlite3VdbeAddOp2(v, OP_Rowid, 0, regResult+1);
sqlite3VdbeAddOp4(v, OP_String8, 0, regResult+2, 0,
pFK->zTo, P4_TRANSIENT);
sqlite3VdbeAddOp2(v, OP_Integer, i-1, regResult+3);
sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 4);
sqlite3VdbeResolveLabel(v, addrOk);
|
Changes to test/fkey5.test.
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
320
321
322
323
324
325
326
327
328
329
|
# This file tests the PRAGMA foreign_key_check command.
#
# EVIDENCE-OF: R-05426-18119 PRAGMA foreign_key_check; PRAGMA
# foreign_key_check(table-name);
set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable {!foreignkey} {
finish_test
return
}
do_test fkey5-1.1 {
................................................................................
db eval {
DELETE FROM c22;
INSERT INTO c22 VALUES('abc ','ALPHA');
PRAGMA foreign_key_check(c22);
}
} {}
finish_test
|
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
...
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
|
# This file tests the PRAGMA foreign_key_check command. # # EVIDENCE-OF: R-05426-18119 PRAGMA foreign_key_check; PRAGMA # foreign_key_check(table-name); set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix fkey5 ifcapable {!foreignkey} { finish_test return } do_test fkey5-1.1 { ................................................................................ db eval { DELETE FROM c22; INSERT INTO c22 VALUES('abc ','ALPHA'); PRAGMA foreign_key_check(c22); } } {} #------------------------------------------------------------------------- # Tests 9.* verify that missing parent tables are handled correctly. # do_execsql_test 9.1.1 { CREATE TABLE k1(x REFERENCES s1); PRAGMA foreign_key_check(k1); } {} do_execsql_test 9.1.2 { INSERT INTO k1 VALUES(NULL); PRAGMA foreign_key_check(k1); } {} do_execsql_test 9.1.3 { INSERT INTO k1 VALUES(1); PRAGMA foreign_key_check(k1); } {k1 2 s1 0} do_execsql_test 9.2.1 { CREATE TABLE k2(x, y, FOREIGN KEY(x, y) REFERENCES s1(a, b)); PRAGMA foreign_key_check(k2); } {} do_execsql_test 9.2 { INSERT INTO k2 VALUES(NULL, 'five'); PRAGMA foreign_key_check(k2); } {} do_execsql_test 9.3 { INSERT INTO k2 VALUES('one', NULL); PRAGMA foreign_key_check(k2); } {} do_execsql_test 9.4 { INSERT INTO k2 VALUES('six', 'seven'); PRAGMA foreign_key_check(k2); } {k2 3 s1 0} finish_test |