/** * Tests for PMA_getSQLToDropForeignKey() method. * * @return void * @test */ public function testPMAGetSQLToDropForeignKey() { $table = "pma_table"; $fk = "pma_fk"; $this->assertEquals("ALTER TABLE `pma_table` DROP FOREIGN KEY `pma_fk`;", PMA_getSQLToDropForeignKey($table, $fk)); }
/** * Function to handle update for a foreign key * * @param array $multi_edit_columns_name multu edit columns name * @param string $master_field_md5 master field md5 * @param string $destination_foreign_table destination foreign table * @param string $destination_foreign_column destination foreign column * @param array $options_array options array * @param array $existrel_foreign db, table, column * @param string $table current table * @param bool &$seen_error whether seen error * @param string &$display_query display query * @param string $foreign_db foreign database * * @return string */ function PMA_handleUpdateForForeignKey($multi_edit_columns_name, $master_field_md5, $destination_foreign_table, $destination_foreign_column, $options_array, $existrel_foreign, $table, &$seen_error, &$display_query, $foreign_db) { $html_output = ''; $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 (!empty($foreign_db) && !empty($foreign_table) && !empty($foreign_field)) { if (isset($existrel_foreign[$master_field])) { $constraint_name = $existrel_foreign[$master_field]['constraint']; $on_delete = !empty($existrel_foreign[$master_field]['on_delete']) ? $existrel_foreign[$master_field]['on_delete'] : 'RESTRICT'; $on_update = !empty($existrel_foreign[$master_field]['on_update']) ? $existrel_foreign[$master_field]['on_update'] : 'RESTRICT'; } if (!isset($existrel_foreign[$master_field])) { // no key defined for this field $create = true; } elseif ($existrel_foreign[$master_field]['foreign_db'] != $foreign_db || $existrel_foreign[$master_field]['foreign_table'] != $foreign_table || $existrel_foreign[$master_field]['foreign_field'] != $foreign_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.... } elseif (isset($existrel_foreign[$master_field])) { $drop = true; } // end if... else.... $tmp_error_drop = false; if ($drop) { $drop_query = PMA_getSQLToDropForeignKey($table, $existrel_foreign[$master_field]['constraint']); $display_query .= $drop_query . "\n"; $GLOBALS['dbi']->tryQuery($drop_query); $tmp_error_drop = $GLOBALS['dbi']->getError(); if (!empty($tmp_error_drop)) { $seen_error = true; $html_output .= PMA_Util::mysqlDie($tmp_error_drop, $drop_query, false, '', false); return $html_output; } } $tmp_error_create = false; if ($create) { $create_query = PMA_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]]); $display_query .= $create_query . "\n"; $GLOBALS['dbi']->tryQuery($create_query); $tmp_error_create = $GLOBALS['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($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"; } // 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 .= PMA_getSQLToCreateForeignKey($table, $master_field, $existrel_foreign[$master_field]['foreign_db'], $existrel_foreign[$master_field]['foreign_table'], $existrel_foreign[$master_field]['foreign_field'], $existrel_foreign[$master_field]['constraint'], $options_array[$existrel_foreign[$master_field]['on_delete']], $options_array[$existrel_foreign[$master_field]['on_update']]); $display_query .= $sql_query_recreate . "\n"; $GLOBALS['dbi']->tryQuery($sql_query_recreate); } } return $html_output; }
// no key defined for this field $create = true; } elseif (PMA_Util::backquote($existrel_foreign[$master_field]['foreign_db']) != $foreign_db || PMA_Util::backquote($existrel_foreign[$master_field]['foreign_table']) != $foreign_table || PMA_Util::backquote($existrel_foreign[$master_field]['foreign_field']) != $foreign_field || $_REQUEST['constraint_name'][$master_field_md5] != $existrel_foreign[$master_field]['constraint'] || $_REQUEST['on_delete'][$master_field_md5] != (!empty($existrel_foreign[$master_field]['on_delete']) ? $existrel_foreign[$master_field]['on_delete'] : 'RESTRICT') || $_REQUEST['on_update'][$master_field_md5] != (!empty($existrel_foreign[$master_field]['on_update']) ? $existrel_foreign[$master_field]['on_update'] : 'RESTRICT')) { // 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.... } elseif (isset($existrel_foreign[$master_field])) { $drop = true; } // end if... else.... $tmp_error_drop = false; if ($drop) { $drop_query = PMA_getSQLToDropForeignKey($table, $existrel_foreign[$master_field]['constraint']); $display_query .= $drop_query . "\n"; PMA_DBI_try_query($drop_query); $tmp_error_drop = PMA_DBI_getError(); if (!empty($tmp_error_drop)) { $seen_error = true; $html_output .= PMA_Util::mysqlDie($tmp_error_drop, $drop_query, false, '', false); continue; } } $tmp_error_create = false; if ($create) { $create_query = PMA_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]]); $display_query .= $create_query . "\n"; PMA_DBI_try_query($create_query); $tmp_error_create = PMA_DBI_getError();