SQLite

static Trigger *fkActionTrigger(
  Parse *pParse,
  Table *pTab,                    /* Table being updated or deleted from */
  FKey *pFKey,                    /* Foreign key to get action for */
  ExprList *pChanges              /* Change-list for UPDATE, NULL for DELETE */
){
  sqlite3 *db = pParse->db;       /* Database handle */
  int action;
  Trigger *pTrigger;
  int action;                     /* One of OE_None, OE_Cascade etc. */
  Trigger *pTrigger;              /* Trigger definition to return */

  if( pChanges ){
    action = pFKey->updateConf;
    pTrigger = pFKey->pOnUpdate;
  }else{
    action = pFKey->deleteConf;
    pTrigger = pFKey->pOnDelete;
  }

  assert( OE_SetNull>OE_Restrict && OE_SetDflt>OE_Restrict );
  assert( OE_Cascade>OE_Restrict && OE_None<OE_Restrict );

  if( action>OE_Restrict && !pTrigger ){
    u8 enableLookaside;           /* Copy of db->lookaside.bEnabled */
    char const *zFrom;            /* Name of referencing table */
    int nFrom;                    /* Length in bytes of zFrom */
    Index *pIdx = 0;
    int *aiCol = 0;
    TriggerStep *pStep;
    Index *pIdx = 0;              /* Parent key index for this FK */
    int *aiCol = 0;               /* child table cols -> parent key cols */
    TriggerStep *pStep;           /* First (only) step of trigger program */
    sqlite3 *dbMem = pTab->dbMem;
    Expr *pWhere = 0;
    ExprList *pList = 0;
    int i;
    Expr *pWhere = 0;             /* WHERE clause of trigger step */
    ExprList *pList = 0;          /* Changes list if ON UPDATE CASCADE */
    int i;                        /* Iterator variable */

    if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0;
    assert( aiCol || pFKey->nCol==1 );

    assert( dbMem==0 || dbMem==pParse->db );
    zFrom = pFKey->pFrom->zName;
    nFrom = sqlite3Strlen30(zFrom);
    pTrigger = (Trigger *)sqlite3DbMallocZero(dbMem, 
        sizeof(Trigger) +         /* struct Trigger */
        sizeof(TriggerStep) +     /* Single step in trigger program */
        nFrom + 1                 /* Space for pStep->target.z */
    );
    if( !pTrigger ){
      pParse->db->mallocFailed = 1;
      return 0;
    }
    pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1];
    pStep->target.z = (char *)&pStep[1];
    pStep->target.n = nFrom;
    memcpy((char *)pStep->target.z, zFrom, nFrom);

    for(i=0; i<pFKey->nCol; i++){
      Expr *pEq;
      int iFromCol;               /* Idx of column in referencing table */
      Token tFromCol;             /* Name of column in referencing table */
      Token tToCol;               /* Name of column in referenced table */
      Token tOld = { "old", 3 };  /* Literal "old" token */
      Token tNew = { "new", 3 };  /* Literal "new" token */
      Token tFromCol;             /* Name of column in referencing table */
      Token tToCol;               /* Name of column in referenced table */
      int iFromCol;               /* Idx of column in referencing table */
      Expr *pEq;                  /* tFromCol = OLD.tToCol */

      iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
      tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid";
      tFromCol.z = iFromCol<0 ? "oid" : pFKey->pFrom->aCol[iFromCol].zName;

      tToCol.n = sqlite3Strlen30(tToCol.z);
      tFromCol.n = sqlite3Strlen30(tFromCol.z);

      /* Create the expression "zFromCol = OLD.zToCol" */
      pEq = sqlite3PExpr(pParse, TK_EQ,
          sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol),
          sqlite3PExpr(pParse, TK_DOT, 
            sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
            sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
          , 0)
      , 0);
      pWhere = sqlite3ExprAnd(pParse->db, pWhere, pEq);
      pWhere = sqlite3ExprAnd(db, pWhere, pEq);

      if( action!=OE_Cascade || pChanges ){
        Expr *pNew;
        if( action==OE_Cascade ){
          pNew = sqlite3PExpr(pParse, TK_DOT, 
            sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew),
            sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)

        }else{
          pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
        }
        pList = sqlite3ExprListAppend(pParse, pList, pNew);
        sqlite3ExprListSetName(pParse, pList, &tFromCol, 0);
      }
    }
    sqlite3DbFree(pParse->db, aiCol);
    sqlite3DbFree(db, aiCol);

    /* If pTab->dbMem==0, then the table may be part of a shared-schema.
    ** Disable the lookaside buffer before allocating space for the
    ** trigger definition in this case.  */
    enableLookaside = db->lookaside.bEnabled;
    if( pTab->dbMem==0 ){
      db->lookaside.bEnabled = 0;
    }

    zFrom = pFKey->pFrom->zName;
    nFrom = sqlite3Strlen30(zFrom);
    pTrigger = (Trigger *)sqlite3DbMallocZero(db, 
        sizeof(Trigger) +         /* struct Trigger */
        sizeof(TriggerStep) +     /* Single step in trigger program */
        nFrom + 1                 /* Space for pStep->target.z */
    );
    if( pTrigger ){
      pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1];
      pStep->target.z = (char *)&pStep[1];
      pStep->target.n = nFrom;
      memcpy((char *)pStep->target.z, zFrom, nFrom);
  
    pStep->pWhere = sqlite3ExprDup(dbMem, pWhere, EXPRDUP_REDUCE);
    pStep->pExprList = sqlite3ExprListDup(dbMem, pList, EXPRDUP_REDUCE);
      pStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);
      pStep->pExprList = sqlite3ExprListDup(db, pList, EXPRDUP_REDUCE);
    }

    /* Re-enable the lookaside buffer, if it was disabled earlier. */
    db->lookaside.bEnabled = enableLookaside;

    sqlite3ExprDelete(pParse->db, pWhere);
    sqlite3ExprListDelete(pParse->db, pList);
    if( db->mallocFailed==1 ){
      fkTriggerDelete(db, pTrigger);
      return 0;
    }

    pStep->op = (action!=OE_Cascade || pChanges) ? TK_UPDATE : TK_DELETE;
    pStep->pTrig = pTrigger;
    pTrigger->pSchema = pTab->pSchema;
    pTrigger->pTabSchema = pTab->pSchema;

    if( pChanges ){

# fkey2-5.*: Check that if foreign-keys are enabled, it is not possible
#            to write to an FK column using the incremental blob API.
#
# fkey2-6.*: Test that FK processing is automatically disabled when 
#            running VACUUM.
#
# fkey2-7.*: Test using an IPK as the key in the child (referencing) table.
#
# fkey2-8.*: Test that enabling/disabling foreign key support while a 
#            transaction is active is not possible.
#
# 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}} {

    }
  } {}
}

#-------------------------------------------------------------------------
# Test that it is possible to use an INTEGER PRIMARY KEY as the child key
# of a foreign constraint.
# 
drop_all_tables
do_test fkey2-7.1 {
  execsql {
    CREATE TABLE t1(a PRIMARY KEY, b);
    CREATE TABLE t2(c INTEGER PRIMARY KEY REFERENCES t1, b);
  }
} {}

} {1 {foreign key constraint failed}}
do_test fkey2-7.7 {
  execsql { DELETE FROM t1 WHERE a = 1 }
} {}
do_test fkey2-7.8 {
  catchsql { UPDATE t1 SET a = 3 }
} {1 {foreign key constraint failed}}

#-------------------------------------------------------------------------
# Test that it is not possible to enable/disable FK support while a
# transaction is open.
# 
drop_all_tables
proc fkey2-8-test {tn zSql value} {
  do_test fkey-2.8.$tn.1 [list execsql $zSql] {}
  do_test fkey-2.8.$tn.2 { execsql "PRAGMA foreign_keys" } $value
}
fkey2-8-test  1 { PRAGMA foreign_keys = 0     } 0
fkey2-8-test  2 { PRAGMA foreign_keys = 1     } 1
fkey2-8-test  3 { BEGIN                       } 1
fkey2-8-test  4 { PRAGMA foreign_keys = 0     } 1
fkey2-8-test  5 { COMMIT                      } 1
fkey2-8-test  6 { PRAGMA foreign_keys = 0     } 0
fkey2-8-test  7 { BEGIN                       } 0
fkey2-8-test  8 { PRAGMA foreign_keys = 1     } 0
fkey2-8-test  9 { COMMIT                      } 0
fkey2-8-test 10 { PRAGMA foreign_keys = 1     } 1
fkey2-8-test 11 { PRAGMA foreign_keys = off   } 0
fkey2-8-test 12 { PRAGMA foreign_keys = on    } 1
fkey2-8-test 13 { PRAGMA foreign_keys = no    } 0
fkey2-8-test 14 { PRAGMA foreign_keys = yes   } 1
fkey2-8-test 15 { PRAGMA foreign_keys = false } 0
fkey2-8-test 16 { PRAGMA foreign_keys = true  } 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.

# 2009 September 22
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !foreignkey||!trigger {
  finish_test
  return
}
source $testdir/malloc_common.tcl

do_malloc_test fkey_malloc-1 -sqlprep {
  PRAGMA foreign_keys = 1;
  CREATE TABLE t1(a PRIMARY KEY, b);
  CREATE TABLE t2(x REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE);
} -sqlbody {
  INSERT INTO t1 VALUES('aaa', 1);
  INSERT INTO t2 VALUES('aaa');
  UPDATE t1 SET a = 'bbb';
  DELETE FROM t1;
}

finish_test