SQLite

Check-in [3f40c142c8]
Login

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

Overview
Comment:Ensure foreign key related processing takes place when rows are deleted from the database by REPLACE conflict handling.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3f40c142c8526c1572020bd4d945c03a72019135
User & Date: dan 2009-09-24 11:31:22.000
Context
2009-09-24
13:48
Comment enhancements. Add an ALWAYS macro in fkey.c to indicate a branch that is always taken. (check-in: d5b714cfff user: drh tags: trunk)
11:31
Ensure foreign key related processing takes place when rows are deleted from the database by REPLACE conflict handling. (check-in: 3f40c142c8 user: dan tags: trunk)
10:42
Use sqlite3FkOldmask() in delete.c instead of assuming that foreign key constraints always require all columns of the deleted row to be stored in registers. (check-in: ce554a3934 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/insert.c.
1267
1268
1269
1270
1271
1272
1273

1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
          ** flag is not set, call GenerateRowIndexDelete(). This removes
          ** the index b-tree entries only. The table b-tree entry will be 
          ** replaced by the new entry when it is inserted.  */
          Trigger *pTrigger = 0;
          if( pParse->db->flags&SQLITE_RecTriggers ){
            pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
          }

          if( pTrigger ){
            sqlite3GenerateRowDelete(
                pParse, pTab, baseCur, regRowid, 0, pTrigger, OE_Replace
            );
          }else{
            sqlite3GenerateRowIndexDelete(pParse, pTab, baseCur, 0);
          }
          sqlite3MultiWrite(pParse);
          seenReplace = 1;
          break;
        }
        case OE_Ignore: {
          assert( seenReplace==0 );
          sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
          break;







>
|






<







1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281

1282
1283
1284
1285
1286
1287
1288
          ** flag is not set, call GenerateRowIndexDelete(). This removes
          ** the index b-tree entries only. The table b-tree entry will be 
          ** replaced by the new entry when it is inserted.  */
          Trigger *pTrigger = 0;
          if( pParse->db->flags&SQLITE_RecTriggers ){
            pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
          }
          sqlite3MultiWrite(pParse);
          if( pTrigger || sqlite3FkRequired(pParse, pTab, 0) ){
            sqlite3GenerateRowDelete(
                pParse, pTab, baseCur, regRowid, 0, pTrigger, OE_Replace
            );
          }else{
            sqlite3GenerateRowIndexDelete(pParse, pTab, baseCur, 0);
          }

          seenReplace = 1;
          break;
        }
        case OE_Ignore: {
          assert( seenReplace==0 );
          sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
          break;
1376
1377
1378
1379
1380
1381
1382

1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
        assert( seenReplace==0 );
        sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
        break;
      }
      default: {
        Trigger *pTrigger = 0;
        assert( onError==OE_Replace );

        if( pParse->db->flags&SQLITE_RecTriggers ){
          pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
        }
        sqlite3GenerateRowDelete(
            pParse, pTab, baseCur, regR, 0, pTrigger, OE_Replace
        );
        sqlite3MultiWrite(pParse);
        seenReplace = 1;
        break;
      }
    }
    sqlite3VdbeJumpHere(v, j3);
    sqlite3ReleaseTempReg(pParse, regR);
  }







>






<







1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389

1390
1391
1392
1393
1394
1395
1396
        assert( seenReplace==0 );
        sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
        break;
      }
      default: {
        Trigger *pTrigger = 0;
        assert( onError==OE_Replace );
        sqlite3MultiWrite(pParse);
        if( pParse->db->flags&SQLITE_RecTriggers ){
          pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
        }
        sqlite3GenerateRowDelete(
            pParse, pTab, baseCur, regR, 0, pTrigger, OE_Replace
        );

        seenReplace = 1;
        break;
      }
    }
    sqlite3VdbeJumpHere(v, j3);
    sqlite3ReleaseTempReg(pParse, regR);
  }
Changes to test/fkey2.test.
50
51
52
53
54
55
56



57
58
59
60
61
62
63
# fkey2-9.*: Test SET DEFAULT actions.
#
# fkey2-10.*: Test errors.
#
# fkey2-11.*: Test CASCADE actions.
#
# fkey2-12.*: Test RESTRICT actions.



#
# fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
#            command. Recycled to test the built-in implementation.
#


proc drop_all_tables {{db db}} {







>
>
>







50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
# fkey2-9.*: Test SET DEFAULT actions.
#
# fkey2-10.*: Test errors.
#
# fkey2-11.*: Test CASCADE actions.
#
# fkey2-12.*: Test RESTRICT actions.
#
# fkey2-13.*: Test that FK processing is performed when a row is REPLACED by
#             an UPDATE or INSERT statement.
#
# fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
#            command. Recycled to test the built-in implementation.
#


proc drop_all_tables {{db db}} {
632
633
634
635
636
637
638



639
















































640
641
642
643
644
645
646
do_test fkey2-12.1.7 { 
  execsql {
    INSERT INTO t1 VALUES(2, 'two');
    COMMIT;
  }
} {}






















































#-------------------------------------------------------------------------
# The following block of tests, those prefixed with "fkey2-genfkey.", are 
# the same tests that were used to test the ".genfkey" command provided 
# by the shell tool. So these tests show that the built-in foreign key 
# implementation is more or less compatible with the triggers generated 
# by genfkey.







>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
do_test fkey2-12.1.7 { 
  execsql {
    INSERT INTO t1 VALUES(2, 'two');
    COMMIT;
  }
} {}

#-------------------------------------------------------------------------
# The following tests, fkey2-13.*, test that FK processing is performed
# when rows are REPLACEd.
#
drop_all_tables
do_test fkey2-13.1.1 {
  execsql {
    CREATE TABLE pp(a UNIQUE, b, c, PRIMARY KEY(b, c));
    CREATE TABLE cc(d, e, f UNIQUE, FOREIGN KEY(d, e) REFERENCES pp);
    INSERT INTO pp VALUES(1, 2, 3);
    INSERT INTO cc VALUES(2, 3, 1);
  }
} {}
foreach {tn stmt} {
  1   "REPLACE INTO pp VALUES(1, 4, 5)"
  2   "REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 3, 4)"
} {
  do_test fkey2-13.1.$tn.1 {
    catchsql $stmt
  } {1 {foreign key constraint failed}}
  do_test fkey2-13.1.$tn.2 {
    execsql {
      SELECT * FROM pp;
      SELECT * FROM cc;
    }
  } {1 2 3 2 3 1}
  do_test fkey2-13.1.$tn.3 {
    execsql BEGIN;
    catchsql $stmt
  } {1 {foreign key constraint failed}}
  do_test fkey2-13.1.$tn.4 {
    execsql {
      COMMIT;
      SELECT * FROM pp;
      SELECT * FROM cc;
    }
  } {1 2 3 2 3 1}
}
do_test fkey2-13.1.3 {
  execsql { 
    REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 2, 3);
    SELECT rowid, * FROM pp;
    SELECT * FROM cc;
  }
} {1 2 2 3 2 3 1}
do_test fkey2-13.1.4 {
  execsql { 
    REPLACE INTO pp(rowid, a, b, c) VALUES(2, 2, 2, 3);
    SELECT rowid, * FROM pp;
    SELECT * FROM cc;
  }
} {2 2 2 3 2 3 1}

#-------------------------------------------------------------------------
# The following block of tests, those prefixed with "fkey2-genfkey.", are 
# the same tests that were used to test the ".genfkey" command provided 
# by the shell tool. So these tests show that the built-in foreign key 
# implementation is more or less compatible with the triggers generated 
# by genfkey.