<?php/* $Id: tbl_relation.php,v 2.31.2.1 2006/04/11 17:51:03 cybot_tm Exp $ */// vim: expandtab sw=4 ts=4 sts=4:/*** Gets some core libraries*/require_once('./libraries/common.lib.php');require_once('./libraries/tbl_properties_common.php');$url_query .= '&goto=tbl_properties.php';/*** Gets tables informations*/require_once('./libraries/tbl_properties_table_info.inc.php');// Note: in libraries/tbl_properties_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_properties_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 . ' ';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)if (isset($_REQUEST['destination_innodb'])) {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` )$upd_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') {$upd_query .= ' ON DELETE ' . $options_array[$_REQUEST['on_delete'][$master_field]];}if ($_REQUEST['on_update'][$master_field] != 'nix') {$upd_query .= ' ON UPDATE ' . $options_array[$_REQUEST['on_update'][$master_field]];}// 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 keyif (PMA_MYSQL_INT_VERSION >= 40013) {$upd_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($upd_query);}// add another$upd_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') {$upd_query .= ' ON DELETE '. $options_array[$_REQUEST['on_delete'][$master_field]];}if ($_REQUEST['on_update'][$master_field] != 'nix') {$upd_query .= ' ON UPDATE '. $options_array[$_REQUEST['on_update'][$master_field]];}} // end if... else....} elseif (isset($existrel_innodb[$master_field])) {if (PMA_MYSQL_INT_VERSION >= 40013) {$upd_query = 'ALTER TABLE ' . PMA_backquote($table). ' DROP FOREIGN KEY '. PMA_backquote($existrel_innodb[$master_field]['constraint']);}} // end if... else....if (isset($upd_query)) {$upd_rs = PMA_DBI_try_query($upd_query);$tmp_error = PMA_DBI_getError();if (substr($tmp_error, 1, 4) == '1216') {PMA_mysqlDie($tmp_error, $upd_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">' . $strNoIndex . ' (' . $master_field.')</p>' . PMA_showMySQLDocu('manual_Table_types', 'InnoDB_foreign_key_constraints') . "\n";}unset($upd_query, $tmp_error);}} // end while} // 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*/// common formecho '<form method="post" action="tbl_relation.php">' . "\n";echo PMA_generate_common_hidden_inputs($db, $table);// relationsif ($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 tablesif ($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 InnoDBif ($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);?><fieldset><legend><?php echo $strLinksTo; ?></legend><table><tr><th></th><?phpif ( $cfgRelation['relwork'] ) {echo '<th>' . $strInternalRelations;if ($tbl_type=='INNODB') {echo PMA_showHint( $strInternalNotNecessary );}echo '</th>';}if ( $tbl_type=='INNODB' ) {echo '<th colspan="2">InnoDB';if (PMA_MYSQL_INT_VERSION < 40013) {echo '(**)';}echo '</th>';}?></tr><?php$odd_row = true;for ($i = 0; $i < $saved_row_cnt; $i++) {$myfield = $save_row[$i]['Field'];?><tr class="<?php echo $odd_row ? 'odd' : 'even'; $odd_row = ! $odd_row; ?>"><td align="center"><b><?php echo $save_row[$i]['Field']; ?></b></td><?phpif ($cfgRelation['relwork']) {?><td><select name="destination[<?php echo htmlspecialchars($save_row[$i]['Field']); ?>]"><?php// PMA internal relationsif (isset($existrel[$myfield])) {$foreign_field = $existrel[$myfield]['foreign_db'] . '.'. $existrel[$myfield]['foreign_table'] . '.'. $existrel[$myfield]['foreign_field'];} else {$foreign_field = FALSE;}$seen_key = FALSE;foreach ($selectboxall AS $key => $value) {echo ' '. '<option value="' . htmlspecialchars($key) . '"';if ($foreign_field && $key == $foreign_field) {echo ' selected="selected"';$seen_key = TRUE;}echo '>' . $value . '</option>'. "\n";} // end while// if the link defined in relationtable points to a foreign field// that is not a key in the foreign table, we show the link// (will not be shown with an arrow)if ($foreign_field && !$seen_key) {echo ' '.'<option value="' . htmlspecialchars($foreign_field) . '"'.' selected="selected"'.'>' . $foreign_field . '</option>'. "\n";}?></select></td><?php} // end if (internal relations)if ($tbl_type=='INNODB') {echo '<td>';if (!empty($save_row[$i]['Key'])) {?><span class="formelement"><select name="destination_innodb[<?php echo htmlspecialchars($save_row[$i]['Field']); ?>]"><?phpif (isset($existrel_innodb[$myfield])) {$foreign_field = $existrel_innodb[$myfield]['foreign_db'] . '.'. $existrel_innodb[$myfield]['foreign_table'] . '.'. $existrel_innodb[$myfield]['foreign_field'];} else {$foreign_field = FALSE;}$found_foreign_field = FALSE;foreach ($selectboxall_innodb AS $key => $value) {echo ' '. '<option value="' . htmlspecialchars($key) . '"';if ($foreign_field && $key == $foreign_field) {echo ' selected="selected"';$found_foreign_field = TRUE;}echo '>' . $value . '</option>'. "\n";} // end while// we did not find the foreign field in the tables of current db,// must be defined in another db so show it to avoid erasing itif (!$found_foreign_field && $foreign_field) {echo ' '. '<option value="' . htmlspecialchars($foreign_field) . '"';echo ' selected="selected"';echo '>' . $foreign_field . '</option>' . "\n";}?></select></span><span class="formelement"><?phpPMA_generate_dropdown('ON DELETE','on_delete[' . htmlspecialchars($save_row[$i]['Field']) . ']',$options_array,isset($existrel_innodb[$myfield]['on_delete']) ? $existrel_innodb[$myfield]['on_delete']: '' );echo '</span>' . "\n".'<span class="formelement">' . "\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 '</span>' . "\n";} else {echo $strNoIndex;} // end if (a key exists)echo ' </td>';} // end if (InnoDB)?></tr><?php} // end forecho ' </table>' . "\n";echo '</fieldset>' . "\n";if ($cfgRelation['displaywork']) {// Get "display_field" infos$disp = PMA_getDisplayField($db, $table);?><fieldset><label><?php echo $strChangeDisplay . ': '; ?></label><select name="display_field" style="vertical-align: middle"><option value="">---</option><?phpforeach ($save_row AS $row) {echo ' <option value="' . htmlspecialchars($row['Field']) . '"';if (isset($disp) && $row['Field'] == $disp) {echo ' selected="selected"';}echo '>' . htmlspecialchars($row['Field']) . '</option>'. "\n";} // end while?></select></fieldset><?php} // end if (displayworks)?><fieldset class="tblFooters"><input type="submit" value="<?php echo $strSave; ?>" /></fieldset></form><?php} // end if (we have columns in this table)if ( $tbl_type === 'INNODB' && PMA_MYSQL_INT_VERSION < 40013 ) {echo '<div class="warning">'.'** ' . sprintf($strUpgrade, 'MySQL', '4.0.13').'</div>';}/*** Displays the footer*/require_once('./libraries/footer.inc.php');?>