<?php/* $Id: tbl_relation.php 9809 2006-12-29 16:27:25Z lem9 $ */// vim: expandtab sw=4 ts=4 sts=4:/** * Gets some core libraries */require_once('./libraries/common.lib.php');require_once('./libraries/tbl_common.php');$url_query .= '&amp;goto=tbl_sql.php';/** * Gets tables informations */require_once('./libraries/tbl_info.inc.php');// Note: in libraries/tbl_links.inc.php we get and display the table comment.// For InnoDB, this comment contains the REFER information but any update// has not been done yet (will be done in tbl_relation.php later).$avoid_show_comment = TRUE;/** * Displays top menu links */require_once('./libraries/tbl_links.inc.php');require_once('./libraries/relation.lib.php');$options_array = array('CASCADE' => 'CASCADE', 'SET_NULL' => 'SET NULL', 'NO_ACTION' => 'NO ACTION', 'RESTRICT' => 'RESTRICT');/** * Generate dropdown choices * * @param   string   Message to display * @param   string   Name of the <select> field * @param   array    Choices for dropdown * @return  string   The existing value (for selected) * * @access  public */function PMA_generate_dropdown($dropdown_question, $radio_name, $choices, $selected_value){    echo $dropdown_question . '&nbsp;&nbsp;';    echo '<select name="' . $radio_name . '">' . "\n";    echo '<option value="nix">--</option>' . "\n";    foreach ($choices AS $one_value => $one_label) {        echo '<option value="' . $one_value . '"';        if ($selected_value == $one_value) {            echo ' selected="selected" ';        }        echo '>' . $one_label . '</option>' . "\n";    }    echo '</select>' . "\n";    echo "\n";}/** * Gets the relation settings */$cfgRelation = PMA_getRelationsParam();/** * Updates */// ensure we are positionned to our current db (since the previous reading// of relations makes pmadb the current one, maybe depending on the MySQL version)PMA_DBI_select_db($db);if ($cfgRelation['relwork']) {    $existrel = PMA_getForeigners($db, $table, '', 'internal');}if ($tbl_type=='INNODB') {    $existrel_innodb = PMA_getForeigners($db, $table, '', 'innodb');}if ($cfgRelation['displaywork']) {    $disp     = PMA_getDisplayField($db, $table);}// u p d a t e s   f o r   I n t e r n a l    r e l a t i o n sif (isset($destination) && $cfgRelation['relwork']) {    foreach ($destination AS $master_field => $foreign_string) {        if ($foreign_string != 'nix') {            list($foreign_db, $foreign_table, $foreign_field) = explode('.', $foreign_string);            if (!isset($existrel[$master_field])) {                $upd_query  = 'INSERT INTO ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($cfgRelation['relation'])                            . '(master_db, master_table, master_field, foreign_db, foreign_table, foreign_field)'                            . ' values('                            . '\'' . PMA_sqlAddslashes($db) . '\', '                            . '\'' . PMA_sqlAddslashes($table) . '\', '                            . '\'' . PMA_sqlAddslashes($master_field) . '\', '                            . '\'' . PMA_sqlAddslashes($foreign_db) . '\', '                            . '\'' . PMA_sqlAddslashes($foreign_table) . '\','                            . '\'' . PMA_sqlAddslashes($foreign_field) . '\')';            } elseif ($existrel[$master_field]['foreign_db'] . '.' .$existrel[$master_field]['foreign_table'] . '.' . $existrel[$master_field]['foreign_field'] != $foreign_string) {                $upd_query  = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($cfgRelation['relation']) . ' SET'                            . ' foreign_db       = \'' . PMA_sqlAddslashes($foreign_db) . '\', '                            . ' foreign_table    = \'' . PMA_sqlAddslashes($foreign_table) . '\', '                            . ' foreign_field    = \'' . PMA_sqlAddslashes($foreign_field) . '\' '                            . ' WHERE master_db  = \'' . PMA_sqlAddslashes($db) . '\''                            . ' AND master_table = \'' . PMA_sqlAddslashes($table) . '\''                            . ' AND master_field = \'' . PMA_sqlAddslashes($master_field) . '\'';            } // end if... else....        } elseif (isset($existrel[$master_field])) {            $upd_query      = 'DELETE FROM ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($cfgRelation['relation'])                            . ' WHERE master_db  = \'' . PMA_sqlAddslashes($db) . '\''                            . ' AND master_table = \'' . PMA_sqlAddslashes($table) . '\''                            . ' AND master_field = \'' . PMA_sqlAddslashes($master_field) . '\'';        } // end if... else....        if (isset($upd_query)) {            $upd_rs         = PMA_query_as_cu($upd_query);            unset($upd_query);        }    } // end while} // end if (updates for internal relations)// u p d a t e s   f o r   I n n o D B// ( for now, one index name only; we keep the definitions if the// foreign db is not the same)// I use $sql_query to be able to display directly the query via// PMA_showMessage()if (isset($_REQUEST['destination_innodb'])) {    $display_query = '';    $seen_error = false;    foreach ($_REQUEST['destination_innodb'] as $master_field => $foreign_string) {        if ($foreign_string != 'nix') {            list($foreign_db, $foreign_table, $foreign_field) = explode('.', $foreign_string);            if (!isset($existrel_innodb[$master_field])) {                // no key defined for this field                // The next few lines are repeated below, so they                // could be put in an include file                // Note: I tried to enclose the db and table name with                // backquotes but MySQL 4.0.16 did not like the syntax                // (for example: `base2`.`table1` )                $sql_query  = 'ALTER TABLE ' . PMA_backquote($table)                            . ' ADD FOREIGN KEY ('                            . PMA_backquote($master_field) . ')'                            . ' REFERENCES '                            . PMA_backquote($foreign_db) . '.'                            . PMA_backquote($foreign_table) . '('                            . PMA_backquote($foreign_field) . ')';                if ($_REQUEST['on_delete'][$master_field] != 'nix') {                    $sql_query   .= ' ON DELETE ' . $options_array[$_REQUEST['on_delete'][$master_field]];                }                if ($_REQUEST['on_update'][$master_field] != 'nix') {                    $sql_query   .= ' ON UPDATE ' . $options_array[$_REQUEST['on_update'][$master_field]];                }                $sql_query .= ';';                $display_query .= $sql_query . "\n";                // end repeated code            } elseif (($existrel_innodb[$master_field]['foreign_db'] . '.' .$existrel_innodb[$master_field]['foreign_table'] . '.' . $existrel_innodb[$master_field]['foreign_field'] != $foreign_string)              || ( $_REQUEST['on_delete'][$master_field] != (!empty($existrel_innodb[$master_field]['on_delete']) ? $existrel_innodb[$master_field]['on_delete'] : ''))              || ( $_REQUEST['on_update'][$master_field] != (!empty($existrel_innodb[$master_field]['on_update']) ? $existrel_innodb[$master_field]['on_update'] : ''))                   ) {                // another foreign key is already defined for this field                // or                // an option has been changed for ON DELETE or ON UPDATE                // remove existing key                if (PMA_MYSQL_INT_VERSION >= 40013) {                    $sql_query  = 'ALTER TABLE ' . PMA_backquote($table)                                . ' DROP FOREIGN KEY '                                . PMA_backquote($existrel_innodb[$master_field]['constraint']) . ';';                    // I tried to send both in one query but it failed                    $upd_rs     = PMA_DBI_query($sql_query);                    $display_query .= $sql_query . "\n";                }                // add another                $sql_query  = 'ALTER TABLE ' . PMA_backquote($table)                            . ' ADD FOREIGN KEY ('                            . PMA_backquote($master_field) . ')'                            . ' REFERENCES '                            . PMA_backquote($foreign_db) . '.'                            . PMA_backquote($foreign_table) . '('                            . PMA_backquote($foreign_field) . ')';                if ($_REQUEST['on_delete'][$master_field] != 'nix') {                    $sql_query   .= ' ON DELETE '                        . $options_array[$_REQUEST['on_delete'][$master_field]];                }                if ($_REQUEST['on_update'][$master_field] != 'nix') {                    $sql_query   .= ' ON UPDATE '                        . $options_array[$_REQUEST['on_update'][$master_field]];                }                $sql_query .= ';';                $display_query .= $sql_query . "\n";            } // end if... else....        } elseif (isset($existrel_innodb[$master_field])) {            if (PMA_MYSQL_INT_VERSION >= 40013) {                $sql_query  = 'ALTER TABLE ' . PMA_backquote($table)                        . ' DROP FOREIGN KEY '                        . PMA_backquote($existrel_innodb[$master_field]['constraint']);                $sql_query .= ';';                $display_query .= $sql_query . "\n";            }        } // end if... else....        if (isset($sql_query)) {            $upd_rs    = PMA_DBI_try_query($sql_query);            $tmp_error = PMA_DBI_getError();            if (! empty($tmp_error)) {                $seen_error = true;            }            if (substr($tmp_error, 1, 4) == '1216'            ||  substr($tmp_error, 1, 4) == '1452') {                PMA_mysqlDie($tmp_error, $sql_query, FALSE, '', FALSE);                echo PMA_showMySQLDocu('manual_Table_types', 'InnoDB_foreign_key_constraints') . "\n";            }            if (substr($tmp_error, 1, 4) == '1005') {                echo '<p class="warning">' . $strForeignKeyError . ' : ' . $master_field                    .'</p>'  . PMA_showMySQLDocu('manual_Table_types', 'InnoDB_foreign_key_constraints') . "\n";            }            unset($sql_query, $tmp_error);        }    } // end foreach     if (!empty($display_query)) {        if ($seen_error) {            PMA_showMessage($strError);         } else {            PMA_showMessage($strSuccess);         }    }} // end if isset($destination_innodb)// U p d a t e s   f o r   d i s p l a y   f i e l dif ($cfgRelation['displaywork']    && isset($display_field)) {    if ($disp) {        if ($display_field != '') {            $upd_query = 'UPDATE ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($cfgRelation['table_info'])                       . ' SET display_field = \'' . PMA_sqlAddslashes($display_field) . '\''                       . ' WHERE db_name  = \'' . PMA_sqlAddslashes($db) . '\''                       . ' AND table_name = \'' . PMA_sqlAddslashes($table) . '\'';        } else {            $upd_query = 'DELETE FROM ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($cfgRelation['table_info'])                       . ' WHERE db_name  = \'' . PMA_sqlAddslashes($db) . '\''                       . ' AND table_name = \'' . PMA_sqlAddslashes($table) . '\'';        }    } elseif ($display_field != '') {        $upd_query = 'INSERT INTO ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($cfgRelation['table_info'])                   . '(db_name, table_name, display_field) '                   . ' VALUES('                   . '\'' . PMA_sqlAddslashes($db) . '\','                   . '\'' . PMA_sqlAddslashes($table) . '\','                   . '\'' . PMA_sqlAddslashes($display_field) . '\')';    }    if (isset($upd_query)) {        $upd_rs    = PMA_query_as_cu($upd_query);    }} // end if// If we did an update, refresh our dataif (isset($destination) && $cfgRelation['relwork']) {    $existrel = PMA_getForeigners($db, $table, '', 'internal');}if (isset($destination_innodb) && $tbl_type=='INNODB') {    $existrel_innodb = PMA_getForeigners($db, $table, '', 'innodb');}if ($cfgRelation['displaywork']) {    $disp     = PMA_getDisplayField($db, $table);}/** * Dialog