diff --git a/htdocs/sql/tbl_relation.php b/htdocs/sql/tbl_relation.php new file mode 100755 --- /dev/null +++ b/htdocs/sql/tbl_relation.php @@ -0,0 +1,571 @@ + 'CASCADE', 'SET_NULL' => 'SET NULL', 'NO_ACTION' => 'NO ACTION', 'RESTRICT' => 'RESTRICT'); + +/** + * Generate dropdown choices + * + * @param string Message to display + * @param string Name of the ' . "\n"; + echo '' . "\n"; + + foreach ($choices AS $one_value => $one_label) { + echo '' . "\n"; + } + echo '' . "\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 s +if (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'] : 'nix')) + || ($_REQUEST['on_update'][$master_field] != (!empty($existrel_innodb[$master_field]['on_update']) ? $existrel_innodb[$master_field]['on_update'] : 'nix')) + ) { + // 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 (! empty($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 '

' . $strForeignKeyError . ' : ' . $master_field + .'

' . PMA_showMySQLDocu('manual_Table_types', 'InnoDB_foreign_key_constraints') . "\n"; + } + unset($tmp_error); + $sql_query = ''; + } + } // 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 d + +if ($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 data +if (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 + */ + +// common form +echo '
' . "\n"; +echo PMA_generate_common_hidden_inputs($db, $table); + + +// relations + +if ($cfgRelation['relwork'] || $tbl_type=='INNODB') { + // To choose relations we first need all tables names in current db + // and if PMA version permits and the main table is innodb, + // we use SHOW TABLE STATUS because we need to find other InnoDB tables + + if ($tbl_type=='INNODB') { + $tab_query = 'SHOW TABLE STATUS FROM ' . PMA_backquote($db); + // [0] of the row is the name + // [1] is the type + } else { + $tab_query = 'SHOW TABLES FROM ' . PMA_backquote($db); + } + // [0] of the row is the name + + $tab_rs = PMA_DBI_query($tab_query, null, PMA_DBI_QUERY_STORE); + $selectboxall['nix'] = '--'; + $selectboxall_innodb['nix'] = '--'; + + while ($curr_table = @PMA_DBI_fetch_row($tab_rs)) { + if (($curr_table[0] != $table) && ($curr_table[0] != $cfg['Server']['relation'])) { + PMA_DBI_select_db($db); + + // need to use PMA_DBI_QUERY_STORE with PMA_DBI_num_rows() in mysqli + $fi_rs = PMA_DBI_query('SHOW KEYS FROM ' . PMA_backquote($curr_table[0]) . ';', null, PMA_DBI_QUERY_STORE); + if ($fi_rs && PMA_DBI_num_rows($fi_rs) > 0) { + $seen_a_primary = FALSE; + while ($curr_field = PMA_DBI_fetch_assoc($fi_rs)) { + if (isset($curr_field['Key_name']) && $curr_field['Key_name'] == 'PRIMARY') { + $seen_a_primary = TRUE; + $field_full = $db . '.' .$curr_field['Table'] . '.' . $curr_field['Column_name']; + $field_v = $curr_field['Table'] . '->' . $curr_field['Column_name']; + $selectboxall[$field_full] = $field_v; + // there could be more than one segment of the primary + // so do not break + + // Please watch here, tbl_type is INNODB but the + // resulting value of SHOW KEYS is InnoDB + + if ($tbl_type=='INNODB' && isset($curr_table[1]) && $curr_table[1]=='InnoDB') { + $selectboxall_innodb[$field_full] = $field_v; + } + + } elseif (isset($curr_field['Non_unique']) && $curr_field['Non_unique'] == 0 && $seen_a_primary==FALSE) { + // if we can't find a primary key we take any unique one + // (in fact, we show all segments of unique keys + // and all unique keys) + $field_full = $db . '.' . $curr_field['Table'] . '.' . $curr_field['Column_name']; + $field_v = $curr_field['Table'] . '->' . $curr_field['Column_name']; + $selectboxall[$field_full] = $field_v; + if ($tbl_type=='INNODB' && isset($curr_table[1]) && $curr_table[1]=='InnoDB') { + $selectboxall_innodb[$field_full] = $field_v; + } + + // for InnoDB, any index is allowed + } elseif ($tbl_type=='INNODB' && isset($curr_table[1]) && $curr_table[1]=='InnoDB') { + $field_full = $db . '.' . $curr_field['Table'] . '.' . $curr_field['Column_name']; + $field_v = $curr_field['Table'] . '->' . $curr_field['Column_name']; + $selectboxall_innodb[$field_full] = $field_v; + + } // end if + } // end while over keys + } // end if (PMA_DBI_num_rows) + PMA_DBI_free_result($fi_rs); + unset($fi_rs); + // Mike Beck - 24.07.02: i've been asked to add all keys of the + // current table (see bug report #574851) + } elseif ($curr_table[0] == $table) { + PMA_DBI_select_db($db); + + // need to use PMA_DBI_QUERY_STORE with PMA_DBI_num_rows() in mysqli + $fi_rs = PMA_DBI_query('SHOW KEYS FROM ' . PMA_backquote($curr_table[0]) . ';', null, PMA_DBI_QUERY_STORE); + if ($fi_rs && PMA_DBI_num_rows($fi_rs) > 0) { + while ($curr_field = PMA_DBI_fetch_assoc($fi_rs)) { + $field_full = $db . '.' . $curr_field['Table'] . '.' . $curr_field['Column_name']; + $field_v = $curr_field['Table'] . '->' . $curr_field['Column_name']; + $selectboxall[$field_full] = $field_v; + if ($tbl_type=='INNODB' && isset($curr_table[1]) && $curr_table[1]=='InnoDB') { + $selectboxall_innodb[$field_full] = $field_v; + } + } // end while + } // end if (PMA_DBI_num_rows) + PMA_DBI_free_result($fi_rs); + unset($fi_rs); + } + } // end while over tables + +} // end if + + +// Now find out the columns of our $table +// need to use PMA_DBI_QUERY_STORE with PMA_DBI_num_rows() in mysqli +$col_rs = PMA_DBI_try_query('SHOW COLUMNS FROM ' . PMA_backquote($table) . ';', null, PMA_DBI_QUERY_STORE); + +if ($col_rs && PMA_DBI_num_rows($col_rs) > 0) { + while ($row = PMA_DBI_fetch_assoc($col_rs)) { + $save_row[] = $row; + } + $saved_row_cnt = count($save_row); + ?> +
+ + + + + ' . $strInternalRelations; + if ($tbl_type=='INNODB') { + echo PMA_showHint($strInternalNotNecessary); + } + echo ''; + } + if ($tbl_type=='INNODB') { + echo ''; + } + ?> + + + + + + + '; + if (!empty($save_row[$i]['Key'])) { + ?> + + + + + ' . "\n" + .'' . "\n"; + + PMA_generate_dropdown('ON UPDATE', + 'on_update[' . htmlspecialchars($save_row[$i]['Field']) . ']', + $options_array, + isset($existrel_innodb[$myfield]['on_update']) ? $existrel_innodb[$myfield]['on_update']: ''); + echo '' . "\n"; + } else { + echo $strNoIndex; + } // end if (a key exists) + echo ' '; + } // end if (InnoDB) + ?> + + ' . "\n"; + echo '' . "\n"; + + if ($cfgRelation['displaywork']) { + // Get "display_field" infos + $disp = PMA_getDisplayField($db, $table); + ?> +
+ + +
+ +
+ +
+ + ' + .'** ' . sprintf($strUpgrade, 'MySQL', '4.0.13') + .''; +} + +/** + * Displays the footer + */ +require_once './libraries/footer.inc.php'; +?>
InnoDB'; + if (PMA_MYSQL_INT_VERSION < 40013) { + echo '(**)'; + } + echo '
+ +