/** * 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),
/** * Persist an object to a table * @param type $tablename The table to store data to * @param type $entity The object to persist * @param type $idfieldname The primary key id of the table; could be null and retrieved automatically * @return boolean true, if successful */ public static function persistObject($tablename, $entity) { Recycle::restoreFromRecycle($tablename, $entity, null); }