/** * 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; }