/**
* 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);
}
}
// Unique and foreign keys for course_department table
if (DBHelper::indexExists('course_department', 'cdep_index')) {
Database::get()->query('DROP INDEX `cdep_index` ON course_department');
}
if (!DBHelper::indexExists('course_department', 'cdep_unique')) {
Database::get()->queryFunc('SELECT course_department.id FROM course
RIGHT JOIN course_department ON course.id = course_department.course
WHERE course.id IS NULL', function ($item) {
Recycle::deleteObject('course_department', $item->id, 'id');
});
Database::get()->queryFunc('SELECT course_department.id FROM hierarchy
RIGHT JOIN course_department ON hierarchy.id = course_department.department
WHERE hierarchy.id IS NULL', function ($item) {
Recycle::deleteObject('course_department', $item->id, 'id');
});
Database::get()->query('ALTER IGNORE TABLE `course_department`
ADD UNIQUE KEY `cdep_unique` (`course`,`department`),
ADD FOREIGN KEY (course) REFERENCES course(id) ON DELETE CASCADE,
ADD FOREIGN KEY (department) REFERENCES hierarchy(id) ON DELETE CASCADE');
}
// External authentication via Hybridauth
Database::get()->query("INSERT IGNORE INTO `auth`
(auth_id, auth_name, auth_title, auth_settings, auth_instructions, auth_default)
VALUES
(8, 'facebook', '', '', '', 0),
(9, 'twitter', '', '', '', 0),
(10, 'google', '', '', '', 0),
(11, 'live', 'Microsoft Live Account', '', 'does not work locally', 0),
