/** * Tests for PMA_getSQLToCreateForeignKey() method. * * @return void * @test */ public function testPMAGetSQLToCreateForeignKey() { $table = "PMA_table"; $field = array("PMA_field1", "PMA_field2"); $foreignDb = "foreignDb"; $foreignTable = "foreignTable"; $foreignField = array("foreignField1", "foreignField2"); $sql = PMA_getSQLToCreateForeignKey($table, $field, $foreignDb, $foreignTable, $foreignField); $sql_excepted = 'ALTER TABLE `PMA_table` ADD ' . 'FOREIGN KEY (`PMA_field1`, `PMA_field2`) REFERENCES ' . '`foreignDb`.`foreignTable`(`foreignField1`, `foreignField2`);'; $this->assertEquals($sql_excepted, $sql); }
/** * 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; }
$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); $message->display(); } else { $html_output .= PMA_Util::mysqlDie($tmp_error_create, $create_query, false, '', false); } $html_output .= PMA_Util::showMySQLDocu('manual_Table_types', '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, PMA_Util::backquote($existrel_foreign[$master_field]['foreign_db']), PMA_Util::backquote($existrel_foreign[$master_field]['foreign_table']), PMA_Util::backquote($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"; PMA_DBI_try_query($sql_query_recreate); } } } // end foreach if (!empty($display_query) && !$seen_error) { $html_output .= PMA_Util::getMessage(__('Your SQL query has been executed successfully'), null, 'success'); } } // end if isset($destination_foreign) // U p d a t e s f o r d i s p l a y f i e l d if ($cfgRelation['displaywork'] && isset($display_field)) { $upd_query = false; if ($disp) {