Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Correctly handle changing counting when inserting and deleting on WITHOUT ROWID tables. Add more FOREIGN KEY test cases. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | omit-rowid |
| Files: | files | file ages | folders |
| SHA1: |
d072bcd0a8692d590c13c2bf458454c1 |
| User & Date: | drh 2013-11-04 15:23:25 |
Context
|
2013-11-04
| ||
| 17:00 | Fix a problem with processing INTEGER PRIMARY KEY on a WITHOUT ROWID table. (check-in: 89098e6d user: drh tags: omit-rowid) | |
| 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) | |
Changes
Changes to src/delete.c.
389 390 391 392 393 394 395 396 397 398 399 400 401 402 |
if( pWInfo==0 ) goto delete_from_cleanup;
for(i=0; i<nPk; i++){
sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, pPk->aiColumn[i],iPk+i);
}
sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, iKey,
sqlite3IndexAffinityStr(v, pPk), P4_TRANSIENT);
sqlite3VdbeAddOp2(v, OP_IdxInsert, iEph, iKey);
sqlite3WhereEnd(pWInfo);
/* Open cursors for all indices of the table.
*/
sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite,
iTabCur, &iDataCur, &iIdxCur);
|
> > > |
389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 |
if( pWInfo==0 ) goto delete_from_cleanup;
for(i=0; i<nPk; i++){
sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, pPk->aiColumn[i],iPk+i);
}
sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, iKey,
sqlite3IndexAffinityStr(v, pPk), P4_TRANSIENT);
sqlite3VdbeAddOp2(v, OP_IdxInsert, iEph, iKey);
if( db->flags & SQLITE_CountRows ){
sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
}
sqlite3WhereEnd(pWInfo);
/* Open cursors for all indices of the table.
*/
sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite,
iTabCur, &iDataCur, &iIdxCur);
|
Changes to src/insert.c.
1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 |
assert( pTab->pSelect==0 ); /* This table is not a VIEW */
for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
if( aRegIdx[i]==0 ) continue;
if( pIdx->pPartIdxWhere ){
sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
}
sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i]);
if( useSeekResult ){
sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
}
}
if( !HasRowid(pTab) ) return;
regData = regNewData + 1;
regRec = sqlite3GetTempReg(pParse);
sqlite3VdbeAddOp3(v, OP_MakeRecord, regData, pTab->nCol, regRec);
sqlite3TableAffinityStr(v, pTab);
sqlite3ExprCacheAffinityChange(pParse, regData, pTab->nCol);
|
> | < > > > |
1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 |
assert( pTab->pSelect==0 ); /* This table is not a VIEW */
for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
if( aRegIdx[i]==0 ) continue;
if( pIdx->pPartIdxWhere ){
sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
}
sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i]);
pik_flags = 0;
if( useSeekResult ) pik_flags = OPFLAG_USESEEKRESULT;
if( pIdx->autoIndex==2 && !HasRowid(pTab) && pParse->nested==0 ){
pik_flags |= OPFLAG_NCHANGE;
}
if( pik_flags ) sqlite3VdbeChangeP5(v, pik_flags);
}
if( !HasRowid(pTab) ) return;
regData = regNewData + 1;
regRec = sqlite3GetTempReg(pParse);
sqlite3VdbeAddOp3(v, OP_MakeRecord, regData, pTab->nCol, regRec);
sqlite3TableAffinityStr(v, pTab);
sqlite3ExprCacheAffinityChange(pParse, regData, pTab->nCol);
|
Changes to src/vdbe.c.
4576 4577 4578 4579 4580 4581 4582 4583 4584 4585 4586 4587 4588 4589 .... 4596 4597 4598 4599 4600 4601 4602 4603 4604 4605 4606 4607 4608 4609 4610 .... 4615 4616 4617 4618 4619 4620 4621 4622 4623 4624 4625 4626 4627 4628 |
assert( pOp->p1>=0 && pOp->p1<p->nCursor );
pC = p->apCsr[pOp->p1];
assert( pC!=0 );
assert( pC->isSorter==(pOp->opcode==OP_SorterInsert) );
pIn2 = &aMem[pOp->p2];
assert( pIn2->flags & MEM_Blob );
pCrsr = pC->pCursor;
if( ALWAYS(pCrsr!=0) ){
assert( pC->isTable==0 );
rc = ExpandBlob(pIn2);
if( rc==SQLITE_OK ){
if( isSorter(pC) ){
rc = sqlite3VdbeSorterWrite(db, pC, pIn2);
}else{
................................................................................
pC->cacheStatus = CACHE_STALE;
}
}
}
break;
}
/* Opcode: IdxDelete P1 P2 P3 * *
** Synopsis: key=r[P2@P3]
**
** The content of P3 registers starting at register P2 form
** an unpacked index key. This opcode removes that entry from the
** index opened by cursor P1.
*/
case OP_IdxDelete: {
................................................................................
assert( pOp->p3>0 );
assert( pOp->p2>0 && pOp->p2+pOp->p3<=(p->nMem-p->nCursor)+1 );
assert( pOp->p1>=0 && pOp->p1<p->nCursor );
pC = p->apCsr[pOp->p1];
assert( pC!=0 );
pCrsr = pC->pCursor;
if( ALWAYS(pCrsr!=0) ){
r.pKeyInfo = pC->pKeyInfo;
r.nField = (u16)pOp->p3;
r.flags = 0;
r.aMem = &aMem[pOp->p2];
#ifdef SQLITE_DEBUG
{ int i; for(i=0; i<r.nField; i++) assert( memIsValid(&r.aMem[i]) ); }
|
> | > |
4576 4577 4578 4579 4580 4581 4582 4583 4584 4585 4586 4587 4588 4589 4590 .... 4597 4598 4599 4600 4601 4602 4603 4604 4605 4606 4607 4608 4609 4610 4611 .... 4616 4617 4618 4619 4620 4621 4622 4623 4624 4625 4626 4627 4628 4629 4630 |
assert( pOp->p1>=0 && pOp->p1<p->nCursor ); pC = p->apCsr[pOp->p1]; assert( pC!=0 ); assert( pC->isSorter==(pOp->opcode==OP_SorterInsert) ); pIn2 = &aMem[pOp->p2]; assert( pIn2->flags & MEM_Blob ); pCrsr = pC->pCursor; if( pOp->p5 & OPFLAG_NCHANGE ) p->nChange++; if( ALWAYS(pCrsr!=0) ){ assert( pC->isTable==0 ); rc = ExpandBlob(pIn2); if( rc==SQLITE_OK ){ if( isSorter(pC) ){ rc = sqlite3VdbeSorterWrite(db, pC, pIn2); }else{ ................................................................................ pC->cacheStatus = CACHE_STALE; } } } break; } /* Opcode: IdxDelete P1 P2 P3 * P5 ** Synopsis: key=r[P2@P3] ** ** The content of P3 registers starting at register P2 form ** an unpacked index key. This opcode removes that entry from the ** index opened by cursor P1. */ case OP_IdxDelete: { ................................................................................ assert( pOp->p3>0 ); assert( pOp->p2>0 && pOp->p2+pOp->p3<=(p->nMem-p->nCursor)+1 ); assert( pOp->p1>=0 && pOp->p1<p->nCursor ); pC = p->apCsr[pOp->p1]; assert( pC!=0 ); pCrsr = pC->pCursor; if( pOp->p5 & OPFLAG_NCHANGE ) p->nChange++; if( ALWAYS(pCrsr!=0) ){ r.pKeyInfo = pC->pKeyInfo; r.nField = (u16)pOp->p3; r.flags = 0; r.aMem = &aMem[pOp->p2]; #ifdef SQLITE_DEBUG { int i; for(i=0; i<r.nField; i++) assert( memIsValid(&r.aMem[i]) ); } |
Changes to test/without_rowid3.test.
1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 |
do_test without_rowid3-16.1.$tn.7 {
execsql { DELETE FROM self }
} {}
do_test without_rowid3-16.1.$tn.8 {
catchsql { INSERT INTO self VALUES(20, 21) }
} {1 {foreign key constraint failed}}
}
#-------------------------------------------------------------------------
# This next block of tests, without_rowid3-17.*, tests that if "PRAGMA count_changes"
# is turned on statements that violate immediate FK constraints return
# SQLITE_CONSTRAINT immediately, not after returning a number of rows.
# Whereas statements that violate deferred FK constraints return the number
# of rows before failing.
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 |
do_test without_rowid3-16.1.$tn.7 {
execsql { DELETE FROM self }
} {}
do_test without_rowid3-16.1.$tn.8 {
catchsql { INSERT INTO self VALUES(20, 21) }
} {1 {foreign key constraint failed}}
}
# Additional tests cases using multi-column self-referential
# FOREIGN KEY constraints.
#
drop_all_tables
do_execsql_test without_rowid3-16.4.1.1 {
PRAGMA foreign_keys=ON;
CREATE TABLE t1(a,b,c,d,e,f,
UNIQUE (a,b),
PRIMARY KEY (e,c),
FOREIGN KEY (d,f) REFERENCES t1(e,c)
) WITHOUT rowid;
INSERT INTO t1 VALUES(1,2,3,5,5,3);
INSERT INTO t1 VALUES(2,3,4,6,6,4);
INSERT INTO t1 VALUES('x','y',1.5,'fizzle','fizzle',1.5);
SELECT *, '|' FROM t1 ORDER BY a, b;
} {1 2 3 5 5 3 | 2 3 4 6 6 4 | x y 1.5 fizzle fizzle 1.5 |}
do_execsql_test without_rowid3-16.4.1.2 {
UPDATE t1 SET c=99, f=99 WHERE a=1;
SELECT *, '|' FROM t1 ORDER BY a, b;
} {1 2 99 5 5 99 | 2 3 4 6 6 4 | x y 1.5 fizzle fizzle 1.5 |}
do_execsql_test without_rowid3-16.4.1.3 {
UPDATE t1 SET e=876, d=876 WHERE a=2;
SELECT *, '|' FROM t1 ORDER BY a, b;
} {1 2 99 5 5 99 | 2 3 4 876 876 4 | x y 1.5 fizzle fizzle 1.5 |}
do_test without_rowid3-16.4.1.4 {
catchsql {
UPDATE t1 SET c=11, e=22 WHERE a=1;
}
} {1 {foreign key constraint failed}}
do_test without_rowid3-16.4.1.5 {
catchsql {
UPDATE t1 SET d=11, f=22 WHERE a=1;
}
} {1 {foreign key constraint failed}}
do_execsql_test without_rowid3-16.4.1.6 {
DELETE FROM t1 WHERE a=1;
SELECT *, '|' FROM t1 ORDER BY a, b;
} {2 3 4 876 876 4 | x y 1.5 fizzle fizzle 1.5 |}
do_execsql_test without_rowid3-16.4.2.1 {
DROP TABLE t1;
CREATE TABLE t1(a,b,c,d,e,f,
PRIMARY KEY (a,b),
UNIQUE (e,c),
FOREIGN KEY (d,f) REFERENCES t1(e,c)
) WITHOUT rowid;
INSERT INTO t1 VALUES(1,2,3,5,5,3);
INSERT INTO t1 VALUES(2,3,4,6,6,4);
INSERT INTO t1 VALUES('x','y',1.5,'fizzle','fizzle',1.5);
SELECT *, '|' FROM t1 ORDER BY a, b;
} {1 2 3 5 5 3 | 2 3 4 6 6 4 | x y 1.5 fizzle fizzle 1.5 |}
do_execsql_test without_rowid3-16.4.2.2 {
UPDATE t1 SET c=99, f=99 WHERE a=1;
SELECT *, '|' FROM t1 ORDER BY a, b;
} {1 2 99 5 5 99 | 2 3 4 6 6 4 | x y 1.5 fizzle fizzle 1.5 |}
do_execsql_test without_rowid3-16.4.2.3 {
UPDATE t1 SET e=876, d=876 WHERE a=2;
SELECT *, '|' FROM t1 ORDER BY a, b;
} {1 2 99 5 5 99 | 2 3 4 876 876 4 | x y 1.5 fizzle fizzle 1.5 |}
do_test without_rowid3-16.4.2.4 {
catchsql {
UPDATE t1 SET c=11, e=22 WHERE a=1;
}
} {1 {foreign key constraint failed}}
do_test without_rowid3-16.4.2.5 {
catchsql {
UPDATE t1 SET d=11, f=22 WHERE a=1;
}
} {1 {foreign key constraint failed}}
do_execsql_test without_rowid3-16.4.2.6 {
DELETE FROM t1 WHERE a=1;
SELECT *, '|' FROM t1 ORDER BY a, b;
} {2 3 4 876 876 4 | x y 1.5 fizzle fizzle 1.5 |}
#-------------------------------------------------------------------------
# This next block of tests, without_rowid3-17.*, tests that if "PRAGMA count_changes"
# is turned on statements that violate immediate FK constraints return
# SQLITE_CONSTRAINT immediately, not after returning a number of rows.
# Whereas statements that violate deferred FK constraints return the number
# of rows before failing.
|