/**
* 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);
}
/**
* Check if a foreign key which connects the detail table's field $detailFieldName with master table's $masterIDFieldName already exists.
* @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 $masterIDFieldName The master table's primary key field name
*/
public static function foreignKeyExists($detailTableName, $detailFieldName, $masterTableName, $masterIDFieldName = null) {
if (is_null($masterIDFieldName))
$masterIDFieldName = DBHelper::primaryKeyOf($masterTableName);
return DBHelper::impl()->foreignKeyExistsImpl($detailTableName, $detailFieldName, $masterTableName, $masterIDFieldName);
}
/**
* Undelete a table entry from the recycle bin and store it back to the table.
* @param string $tablename The table to retrieve data from
* @param string $id The id of the table entry to undelete. If the id field was altered in the recycle bin table, then the new id will be used, not the old id when the object was stored.
* @param string $idfieldname The primary key id of the table; could be null and retrieved automatically
* @return boolean true, if successful
*/
public static function undeleteObject($tablename, $id, $idfieldname = null) {
if (is_null($idfieldname))
$idfieldname = DBHelper::primaryKeyOf($tablename);
if (is_null($idfieldname))
return null;
$result = Recycle::restoreObject($tablename, $id, $idfieldname);
if ($result)
return Recycle::restoreFromRecycle($tablename, $result, $id);
else
return false;
}