/**
* Create a new foreign key from $masterTableName to $detailTableName based
* on the $detailFieldName field
* @param type $detailTableName The detail table name
* @param type $detailFieldName The detail table's field name, which
* connects with the master table
* @param type $masterTableName The master table name
* @param type $defaultEntryResolver A numeric value or a function which
* returns a numeric value, in order to get the master id field value. This
* will be used as default for those entries of the detail table, who are
* orphaned (have a wrong reference). If this value is null, or if the
* function returns null, and the field does not accept null values, then
* the orphaned entries will be removed from the detail table and recycled
* to the recyclebin table.
*/
public static function create($detailTableName, $detailFieldName, $masterTableName, $defaultEntryResolver) {
$masterIDFieldName = DBHelper::primaryKeyOf($masterTableName);
if (DBHelper::foreignKeyExists($detailTableName, $detailFieldName, $masterTableName, $masterIDFieldName))
return;
$detailIDFieldName = DBHelper::primaryKeyOf($detailTableName);
$defaultEntryID = is_null($defaultEntryResolver) ? null :
(is_numeric($defaultEntryResolver) ? $defaultEntryResolver :
is_callable($defaultEntryResolver) ? $defaultEntryResolver() :
null);
$nullable = DBHelper::isColumnNullable($detailTableName, $detailFieldName);
$wrongIDs = Database::get()->queryArray("select `$detailTableName`.`$detailIDFieldName` as detailid from `$detailTableName`
left join `$masterTableName` on `$detailTableName`.`$detailFieldName` = `$masterTableName`.`$masterIDFieldName`
where `$detailTableName`.`$detailFieldName` is not null and `$masterTableName`.`$masterIDFieldName` is null");
if ($wrongIDs) {
foreach ($wrongIDs as $entry) {
if (is_null($defaultEntryID)) {
if ($nullable)
Database::get()->query("update `" . $detailTableName . "` set `" . $detailFieldName . "` = NULL where $detailIDFieldName = ?d"
, $entry->detailid);
else
Recycle::deleteObject($detailTableName, $entry->detailid, $detailIDFieldName);
} else {
Database::get()->query("update `" . $detailTableName . "` set `" . $detailFieldName . "` = ?d where $detailIDFieldName = ?d"
, $defaultEntryID, $entry->detailid);
}
}
}
DBHelper::createForeignKey($detailTableName, $detailFieldName, $masterTableName, $masterIDFieldName);
}
/**
* Create a foreign key which connects the detail table's field $detailFieldName with master table's $masterIDFieldName
* @param type $detailTableName The detail table name
* @param type $detailFieldName The detail table's field name, which connects with master table
* @param type $masterTableName The master table name
* @param type $masterIDFieldName The master table's primary key field name
*/
public static function createForeignKey($detailTableName, $detailFieldName, $masterTableName, $masterIDFieldName = null) {
if (is_null($masterIDFieldName))
$masterIDFieldName = DBHelper::primaryKeyOf($masterTableName);
if (DBHelper::foreignKeyExists($detailTableName, $detailFieldName, $masterTableName, $masterIDFieldName))
return;
return DBHelper::impl()->createForeignKeyImpl($detailTableName, $detailFieldName, $masterTableName, $masterIDFieldName);
}
