/**
* Get the delta queries between existing foreign key values and new foreign key values in the given table
*
* @param array foreign key lines from the install script Create Table commands
* @param string the processed table name
* @param boolean set to false to not process the required queries without asking the user consent
* @param string leave this to NUL for delta, set to 'add' to add a new foreign key and set to 'drop' to drop a single foreign key.
* @return array the delta queries if there are any or empty array if the required db queries have been processed or if there are no foreign key differences
*/
function db_delta_foreign_keys($foreign_key_fields, $table, $silent = true, $action = NULL)
{
global $DB;
$result = array();
// get create table sql from db
$ct_sql = $DB->get_var('SHOW CREATE TABLE ' . $table, 1, 0);
// Check related tables engine because the foreign key table and the reference table both must have InnoDB engine
if (!empty($foreign_key_fields) && $action != 'drop') {
// we have foreign key contraints, and we don't want to drop that ( In case of drop FK the table engine doesn't matter )
// which tables engine must be changed
$modify_table_eninges = array($table => 'InnoDB');
foreach ($foreign_key_fields as $foreign_key) {
// check reference tables engine
$modify_table_eninges[$foreign_key['reference_table']] = 'InnoDB';
}
$engine_queries = db_delta_table_engines($modify_table_eninges, $silent);
if (!$silent && !empty($engine_queries)) {
$result = array_merge($engine_queries, $result);
}
}
// get foreign key constraints from db
$existing_foreign_key_fields = array();
$db_fieldlines = get_fieldlines_from_query($ct_sql);
foreach ($db_fieldlines as $fieldname => $fieldline) {
// loop through all field lines and get those lines where are foreign key definitions
$fieldline = str_replace(array('`', '"'), '', $fieldline);
if (preg_match('~^(?:(CONSTRAINT)* \\s* ([^ ]*)) \\s* (FOREIGN\\s+KEY) \\s* \\((.*)\\) \\s* (REFERENCES) \\s* ([^( ]*) \\s* \\((.*)\\) \\s* (.*)$~ixs', $fieldline, $match)) {
// add existing foreign key fields, but set drop param to true only if we don't want to add/drop a single foreign key!
$existing_foreign_key_fields[] = array('fk_symbol' => $match[2], 'fk_fields' => $match[4], 'reference_table' => $match[6], 'reference_columns' => $match[7], 'fk_definition' => $match[8], 'drop' => $action == NULL);
}
}
foreach ($existing_foreign_key_fields as &$existing_foreign_key) {
// loop through existing foreign key definitions
foreach ($foreign_key_fields as &$foreign_key) {
// loop through the install script foreign key definitions
if ($action == NULL && !$existing_foreign_key['drop'] && !$foreign_key['create']) {
// if we already know that an old key should not be removed, and the recent key already exists skip this check
continue;
}
// check if the two foreign key constraint is the same or not
$match_found = $existing_foreign_key['fk_fields'] == $foreign_key['fk_fields'] && $existing_foreign_key['reference_table'] == $foreign_key['reference_table'] && $existing_foreign_key['reference_columns'] == $foreign_key['reference_columns'];
$exact_match_found = $match_found && $existing_foreign_key['fk_definition'] == $foreign_key['fk_definition'];
if (!empty($action) && $match_found) {
// action is not empty it means that we would like to add or drop a single foreign key
if ($action == 'drop') {
// set existing foreign key to be droped
$existing_foreign_key['drop'] = true;
break;
} elseif ($action = 'add') {
// add a new foreign key
if ($exact_match_found) {
// Exact match found so the FK already exists with the same definition
return;
}
// foreign key exists but not with the given definition so we have to drop the old FK
$existing_foreign_key['drop'] = true;
break;
}
}
// if exact match found betwen existing and recent foreign keys then we should not drop the old one
$existing_foreign_key['drop'] = $existing_foreign_key['drop'] && !$exact_match_found;
// if recent foreign keys already exists then we doesn't have to create a new one
$foreign_key['create'] = $foreign_key['create'] && !$exact_match_found;
}
if ($action !== NULL && $existing_foreign_key['drop']) {
// in case of add/drop a single foreign key, if we have found a match, then we don't have to look forward
break;
}
}
unset($existing_foreign_key);
unset($foreign_key);
foreach ($existing_foreign_key_fields as $existing_foreign_key) {
// loop through existing foreign keys
if ($existing_foreign_key['drop']) {
// this foreign key constraint should be removed, create the query
$query = 'ALTER TABLE ' . $table . ' DROP FOREIGN KEY ' . $existing_foreign_key['fk_symbol'];
if ($silent) {
// execute query in silent mode
$DB->query($query);
} else {
// set query definition
$result[] = array('queries' => array($query), 'note' => 'Drop <strong>' . $existing_foreign_key['fk_symbol'] . '</strong> foreign key constraint from <strong>' . $table . '</strong> table.', 'type' => 'drop_foreign_key');
}
}
}
foreach ($foreign_key_fields as $foreign_key) {
// loop through in up to date foreign keys
if ($foreign_key['create']) {
// // this foreign key constraint is new, it must be created
// Create delete query for orphan entries
$delete_query = 'DELETE FROM ' . $table . ' WHERE ' . $foreign_key['fk_fields'] . ' NOT IN (
SELECT DISTINCT(' . $foreign_key['reference_columns'] . ') FROM ' . $foreign_key['reference_table'] . ' )';
$query = 'ALTER TABLE ' . $table . ' ADD FOREIGN KEY (' . $foreign_key['fk_fields'] . ') REFERENCES ' . $foreign_key['reference_table'] . ' (' . $foreign_key['reference_columns'] . ') ' . $foreign_key['fk_definition'];
if ($silent) {
// execute queries in silent mode
if ($DB->query($delete_query) !== false) {
// orphan child entries have been deleted, create foreign key
$DB->query($query);
}
} else {
// set query definition
$result[] = array('queries' => array($delete_query), 'note' => 'Delete orphan <strong>' . $table . '</strong> entries.', 'type' => 'delete_orphan_entries');
$result[] = array('queries' => array($query), 'note' => 'Add foreign key constraint on <strong>' . $table . '(' . $foreign_key['fk_fields'] . ')' . '</strong> in reference to <strong>' . $foreign_key['reference_table'] . '(' . $foreign_key['reference_columns'] . ')' . '</strong>', 'type' => 'add_foreign_key');
}
}
}
return $result;
}
/**
* Get those colum definitions from a CREATE TABLE command where the 'CHARACTER SET' or the 'COLLATE' is defined
*
* @param string create table query
* @return array column_name => column_definition
*/
function get_columns_with_charset_definition($query)
{
$fields = get_fieldlines_from_query($query);
$fields_with_charset = array();
foreach ($fields as $field_name => $field_definition) {
if ($field_name == 'PRIMARY' || $field_name == '0') {
continue;
}
if (strpos($field_definition, 'CHARACTER SET') !== false) {
// Column contains 'CHARACTER SET' definition
$fields_with_charset[$field_name] = $field_definition;
} elseif (strpos($field_definition, 'COLLATE') !== false) {
// Column contains 'COLLATE' definition
$fields_with_charset[$field_name] = $field_definition;
}
}
return $fields_with_charset;
}
