/**
  * 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;
}
Example #3
0
                $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) {