/** * Function to handle foreign key updates * * @param array $destination_foreign_db destination foreign database * @param array $multi_edit_columns_name multi edit column names * @param array $destination_foreign_table destination foreign table * @param array $destination_foreign_column destination foreign column * @param array $options_array options array * @param string $table current table * @param array $existrel_foreign db, table, column * * @return array */ public function updateForeignKeys($destination_foreign_db, $multi_edit_columns_name, $destination_foreign_table, $destination_foreign_column, $options_array, $table, $existrel_foreign) { $html_output = ''; $preview_sql_data = ''; $display_query = ''; $seen_error = false; foreach ($destination_foreign_db as $master_field_md5 => $foreign_db) { $create = false; $drop = false; // Map the fieldname's md5 back to its real name $master_field = $multi_edit_columns_name[$master_field_md5]; $foreign_table = $destination_foreign_table[$master_field_md5]; $foreign_field = $destination_foreign_column[$master_field_md5]; if (isset($existrel_foreign[$master_field_md5]['ref_db_name'])) { $ref_db_name = $existrel_foreign[$master_field_md5]['ref_db_name']; } else { $ref_db_name = $GLOBALS['db']; } $empty_fields = false; foreach ($master_field as $key => $one_field) { if (!empty($one_field) && empty($foreign_field[$key]) || empty($one_field) && !empty($foreign_field[$key])) { $empty_fields = true; } if (empty($one_field) && empty($foreign_field[$key])) { unset($master_field[$key]); unset($foreign_field[$key]); } } if (!empty($foreign_db) && !empty($foreign_table) && !$empty_fields) { if (isset($existrel_foreign[$master_field_md5])) { $constraint_name = $existrel_foreign[$master_field_md5]['constraint']; $on_delete = !empty($existrel_foreign[$master_field_md5]['on_delete']) ? $existrel_foreign[$master_field_md5]['on_delete'] : 'RESTRICT'; $on_update = !empty($existrel_foreign[$master_field_md5]['on_update']) ? $existrel_foreign[$master_field_md5]['on_update'] : 'RESTRICT'; if ($ref_db_name != $foreign_db || $existrel_foreign[$master_field_md5]['ref_table_name'] != $foreign_table || $existrel_foreign[$master_field_md5]['ref_index_list'] != $foreign_field || $existrel_foreign[$master_field_md5]['index_list'] != $master_field || $_REQUEST['constraint_name'][$master_field_md5] != $constraint_name || $_REQUEST['on_delete'][$master_field_md5] != $on_delete || $_REQUEST['on_update'][$master_field_md5] != $on_update) { // another foreign key is already defined for this field // or an option has been changed for ON DELETE or ON UPDATE $drop = true; $create = true; } // end if... else.... } else { // no key defined for this field(s) $create = true; } } elseif (isset($existrel_foreign[$master_field_md5])) { $drop = true; } // end if... else.... $tmp_error_drop = false; if ($drop) { $drop_query = 'ALTER TABLE ' . PMA_Util::backquote($table) . ' DROP FOREIGN KEY ' . PMA_Util::backquote($existrel_foreign[$master_field_md5]['constraint']) . ';'; if (!isset($_REQUEST['preview_sql'])) { $display_query .= $drop_query . "\n"; $this->_dbi->tryQuery($drop_query); $tmp_error_drop = $this->_dbi->getError(); if (!empty($tmp_error_drop)) { $seen_error = true; $html_output .= PMA_Util::mysqlDie($tmp_error_drop, $drop_query, false, '', false); continue; } } else { $preview_sql_data .= $drop_query . "\n"; } } $tmp_error_create = false; if (!$create) { continue; } $create_query = $this->_getSQLToCreateForeignKey($table, $master_field, $foreign_db, $foreign_table, $foreign_field, $_REQUEST['constraint_name'][$master_field_md5], $options_array[$_REQUEST['on_delete'][$master_field_md5]], $options_array[$_REQUEST['on_update'][$master_field_md5]]); if (!isset($_REQUEST['preview_sql'])) { $display_query .= $create_query . "\n"; $this->_dbi->tryQuery($create_query); $tmp_error_create = $this->_dbi->getError(); if (!empty($tmp_error_create)) { $seen_error = true; if (substr($tmp_error_create, 1, 4) == '1005') { $message = PMA_Message::error(__('Error creating foreign key on %1$s (check data ' . 'types)')); $message->addParam(implode(', ', $master_field)); $html_output .= $message->getDisplay(); } else { $html_output .= PMA_Util::mysqlDie($tmp_error_create, $create_query, false, '', false); } $html_output .= PMA_Util::showMySQLDocu('InnoDB_foreign_key_constraints') . "\n"; } } else { $preview_sql_data .= $create_query . "\n"; } // this is an alteration and the old constraint has been dropped // without creation of a new one if ($drop && $create && empty($tmp_error_drop) && !empty($tmp_error_create)) { // a rollback may be better here $sql_query_recreate = '# Restoring the dropped constraint...' . "\n"; $sql_query_recreate .= $this->_getSQLToCreateForeignKey($table, $master_field, $existrel_foreign[$master_field_md5]['ref_db_name'], $existrel_foreign[$master_field_md5]['ref_table_name'], $existrel_foreign[$master_field_md5]['ref_index_list'], $existrel_foreign[$master_field_md5]['constraint'], $options_array[$existrel_foreign[$master_field_md5]['on_delete']], $options_array[$existrel_foreign[$master_field_md5]['on_update']]); if (!isset($_REQUEST['preview_sql'])) { $display_query .= $sql_query_recreate . "\n"; $this->_dbi->tryQuery($sql_query_recreate); } else { $preview_sql_data .= $sql_query_recreate; } } } // end foreach return array($html_output, $preview_sql_data, $display_query, $seen_error); }