/**
* Function to send html for column dropdown list
*
* @return void
*/
function PMA_sendHtmlForColumnDropdownList()
{
$response = PMA_Response::getInstance();
$foreignTable = $_REQUEST['foreignTable'];
$table_obj = new PMA_Table($foreignTable, $_REQUEST['foreignDb']);
$columns = array();
foreach ($table_obj->getUniqueColumns(false, false) as $column) {
$columns[] = htmlspecialchars($column);
}
$response->addJSON('columns', $columns);
}
/**
* Test for getUniqueColumns
*
* @return void
*/
public function testGetUniqueColumns()
{
$table = 'PMA_BookMark';
$db = 'PMA';
$table = new PMA_Table($table, $db);
$return = $table->getUniqueColumns();
$expect = array('`PMA`.`PMA_BookMark`.`index1`', '`PMA`.`PMA_BookMark`.`index3`', '`PMA`.`PMA_BookMark`.`index5`');
$this->assertEquals($expect, $return);
}
/**
* Function to get html for an entire row in foreign key form
*
* @param array $one_key Single foreign key constraint
* @param bool $odd_row whether odd or even row
* @param array $columns Array of table columns
* @param int $i Row number
* @param array $options_array Options array
* @param string $tbl_storage_engine table storage engine
* @param string $db Database
*
* @return string html
*/
function PMA_getHtmlForForeignKeyRow($one_key, $odd_row, $columns, $i, $options_array, $tbl_storage_engine, $db)
{
$html_output = '<tr class="' . ($odd_row ? 'odd' : 'even') . '">';
// Drop key anchor.
$html_output .= '<td>';
if (isset($one_key['constraint'])) {
$drop_fk_query = 'ALTER TABLE ' . PMA_Util::backquote($GLOBALS['table']) . ' DROP FOREIGN KEY ' . PMA_Util::backquote($one_key['constraint']) . ';';
$this_params = $GLOBALS['url_params'];
$this_params['goto'] = 'tbl_relation.php';
$this_params['back'] = 'tbl_relation.php';
$this_params['sql_query'] = $drop_fk_query;
$this_params['message_to_show'] = sprintf(__('Foreign key constraint %s has been dropped'), $one_key['constraint']);
$js_msg = PMA_jsFormat('ALTER TABLE ' . $GLOBALS['table'] . ' DROP FOREIGN KEY ' . $one_key['constraint'] . ';');
$html_output .= '<input type="hidden" class="drop_foreign_key_msg"' . ' value="' . $js_msg . '" />';
$html_output .= ' <a class="drop_foreign_key_anchor';
$html_output .= ' ajax';
$html_output .= '" href="sql.php' . PMA_URL_getCommon($this_params) . '" >' . PMA_Util::getIcon('b_drop.png', __('Drop')) . '</a>';
}
$html_output .= '</td>';
$html_output .= '<td>';
$html_output .= '<span class="formelement clearfloat">';
$constraint_name = isset($one_key['constraint']) ? $one_key['constraint'] : '';
$html_output .= '<input type="text" name="constraint_name[' . $i . ']"' . ' value="' . htmlspecialchars($constraint_name) . '"' . ' placeholder="' . __('Constraint name') . '" />';
$html_output .= '</span>' . "\n";
$html_output .= '<div class="floatleft">';
$html_output .= '<span class="formelement">';
// For ON DELETE and ON UPDATE, the default action
// is RESTRICT as per MySQL doc; however, a SHOW CREATE TABLE
// won't display the clause if it's set as RESTRICT.
$on_delete = isset($one_key['on_delete']) ? $one_key['on_delete'] : 'RESTRICT';
$html_output .= PMA_generateDropdown('ON DELETE', 'on_delete[' . $i . ']', $options_array, $on_delete);
$html_output .= '</span>' . "\n";
$html_output .= '<span class="formelement">' . "\n";
$on_update = isset($one_key['on_update']) ? $one_key['on_update'] : 'RESTRICT';
$html_output .= PMA_generateDropdown('ON UPDATE', 'on_update[' . $i . ']', $options_array, $on_update);
$html_output .= '</span>';
$html_output .= '</div>';
$column_array = array();
$column_array[''] = '';
foreach ($columns as $column) {
if (!empty($column['Key'])) {
$column_array[$column['Field']] = $column['Field'];
}
}
$html_output .= '</span>' . "\n";
$html_output .= '</td>';
$html_output .= '<td>';
if (isset($one_key['index_list'])) {
foreach ($one_key['index_list'] as $key => $column) {
$html_output .= '<span class="formelement clearfloat">';
$html_output .= PMA_generateDropdown('', 'foreign_key_fields_name[' . $i . '][]', $column_array, $column);
$html_output .= '</span>';
}
} else {
$html_output .= '<span class="formelement clearfloat">';
$html_output .= PMA_generateDropdown('', 'foreign_key_fields_name[' . $i . '][]', $column_array, '');
$html_output .= '</span>';
}
$html_output .= '<a class="formelement clearfloat add_foreign_key_field"' . ' href="" data-index="' . $i . '">' . __('+ Add column') . '</a>';
$html_output .= '</td>';
$html_output .= '<td>';
$foreign_table = false;
// foreign database dropdown
$foreign_db = isset($one_key['ref_db_name']) ? $one_key['ref_db_name'] : $db;
$html_output .= '<span class="formelement clearfloat">';
$html_output .= PMA_generateRelationalDropdown('destination_foreign_db[' . $i . ']', $GLOBALS['pma']->databases, $foreign_db, __('Database'));
// end of foreign database dropdown
$html_output .= '</td>';
$html_output .= '<td>';
// foreign table dropdown
$tables = array();
if ($foreign_db) {
$foreign_table = isset($one_key['ref_table_name']) ? $one_key['ref_table_name'] : '';
// In Drizzle, 'SHOW TABLE STATUS' will show status only for the tables
// which are currently in the table cache. Hence we have to use
// 'SHOW TABLES' and manualy retrieve table engine values.
if (PMA_DRIZZLE) {
$tables_rs = $GLOBALS['dbi']->query('SHOW TABLES FROM ' . PMA_Util::backquote($foreign_db), null, PMA_DatabaseInterface::QUERY_STORE);
while ($row = $GLOBALS['dbi']->fetchArray($tables_rs)) {
$engine = PMA_Table::sGetStatusInfo($foreign_db, $row[0], 'Engine');
if (isset($engine) && mb_strtoupper($engine) == $tbl_storage_engine) {
$tables[] = $row[0];
}
}
} else {
$tables_rs = $GLOBALS['dbi']->query('SHOW TABLE STATUS FROM ' . PMA_Util::backquote($foreign_db), null, PMA_DatabaseInterface::QUERY_STORE);
while ($row = $GLOBALS['dbi']->fetchRow($tables_rs)) {
if (isset($row[1]) && mb_strtoupper($row[1]) == $tbl_storage_engine) {
$tables[] = $row[0];
}
}
}
}
$html_output .= '<span class="formelement clearfloat">';
$html_output .= PMA_generateRelationalDropdown('destination_foreign_table[' . $i . ']', $tables, $foreign_table, __('Table'));
$html_output .= '</span>';
// end of foreign table dropdown
$html_output .= '</td>';
$html_output .= '<td>';
// foreign column dropdown
if ($foreign_db && $foreign_table) {
foreach ($one_key['ref_index_list'] as $foreign_column) {
$table_obj = new PMA_Table($foreign_table, $foreign_db);
$columns = $table_obj->getUniqueColumns(false, false);
$html_output .= '<span class="formelement clearfloat">';
$html_output .= PMA_generateRelationalDropdown('destination_foreign_column[' . $i . '][]', $columns, $foreign_column, __('Column'));
$html_output .= '</span>';
}
} else {
$html_output .= '<span class="formelement clearfloat">';
$html_output .= PMA_generateRelationalDropdown('destination_foreign_column[' . $i . '][]', array(), '', __('Column'));
$html_output .= '</span>';
}
// end of foreign column dropdown
$html_output .= '</td>';
$html_output .= '</tr>';
return $html_output;
}
// same engine.
if (PMA_foreignkey_supported($tbl_type)) {
$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[] = '';
$selectboxall_foreign[] = '';
while ($curr_table = PMA_DBI_fetch_row($tab_rs)) {
$current_table = new PMA_Table($curr_table[0], $db);
// explicitely ask for non-quoted list of indexed columns
$selectboxall = array_merge($selectboxall, $current_table->getUniqueColumns($backquoted = false));
// if foreign keys are supported, collect all keys from other
// tables of the same engine
if (PMA_foreignkey_supported($tbl_type) && isset($curr_table[1]) && strtoupper($curr_table[1]) == $tbl_type) {
// explicitely ask for non-quoted list of indexed columns
// need to obtain backquoted values to support dots inside values
$selectboxall_foreign = array_merge($selectboxall_foreign, $current_table->getIndexedColumns($backquoted = true));
}
}
// 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) {
} else {
$tab_query = 'SHOW TABLES FROM ' . PMA_Util::backquote($db);
// [0] of the row is the name
}
$tab_rs = PMA_DBI_query($tab_query, null, PMA_DBI_QUERY_STORE);
$selectboxall[] = '';
$selectboxall_foreign[] = '';
while ($curr_table = PMA_DBI_fetch_row($tab_rs)) {
$current_table = new PMA_Table($curr_table[0], $db);
// explicitely ask for non-quoted list of indexed columns
$selectboxall = array_merge(
$selectboxall,
$current_table->getUniqueColumns($backquoted = false)
);
// if foreign keys are supported, collect all keys from other
// tables of the same engine
if (PMA_Util::isForeignKeySupported($tbl_storage_engine)
&& isset($curr_table[1])
&& strtoupper($curr_table[1]) == $tbl_storage_engine
) {
// explicitely ask for non-quoted list of indexed columns
// need to obtain backquoted values to support dots inside values
$selectboxall_foreign = array_merge(
$selectboxall_foreign,
$current_table->getIndexedColumns($backquoted = true)
);
}
