SQLite: Check-in [3f40c142]

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 \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA1:	3f40c142c8526c1572020bd4d945c03a72019135
User & Date:	dan 2009-09-24 11:31:22

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: d5b714cf 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: 3f40c142 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: ce554a39 user: dan tags: trunk)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

          ** 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;
................................................................................
        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);
  }

          ** 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;
................................................................................
        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.

# 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}} {
................................................................................
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.








>
>
>







 







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

# 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}} {
................................................................................
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.