SQLite

Check-in [d072bcd0a8]
Login

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
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1: d072bcd0a8692d590c13c2bf458454c10c12a3e2
User & Date: drh 2013-11-04 15:23:25.268
Context
2013-11-04
17:00
Fix a problem with processing INTEGER PRIMARY KEY on a WITHOUT ROWID table. (check-in: 89098e6d18 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: 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)
Changes
Unified Diff Ignore Whitespace Patch
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
4590
4591
4592
4593
4594
4595
4596
4597
4598
4599
4600
4601
4602
4603
4604
4605
4606
4607
4608
4609
4610
4611
4612
4613
4614
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{
        nKey = pIn2->n;
        zKey = pIn2->z;
        rc = sqlite3BtreeInsert(pCrsr, zKey, nKey, "", 0, 0, pOp->p3, 
            ((pOp->p5 & OPFLAG_USESEEKRESULT) ? pC->seekResult : 0)
            );
        assert( pC->deferredMoveto==0 );
        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: {
  VdbeCursor *pC;
  BtCursor *pCrsr;
  int res;
  UnpackedRecord r;

  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
4591
4592
4593
4594
4595
4596
4597
4598
4599
4600
4601
4602
4603
4604
4605
4606
4607
4608
4609
4610
4611
4612
4613
4614
4615
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{
        nKey = pIn2->n;
        zKey = pIn2->z;
        rc = sqlite3BtreeInsert(pCrsr, zKey, nKey, "", 0, 0, pOp->p3, 
            ((pOp->p5 & OPFLAG_USESEEKRESULT) ? pC->seekResult : 0)
            );
        assert( pC->deferredMoveto==0 );
        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: {
  VdbeCursor *pC;
  BtCursor *pCrsr;
  int res;
  UnpackedRecord r;

  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.
︙︙