/**
* Validate whether the table exists.
*
* @return void
*/
protected function validateTableAndLoadFields()
{
$sql = 'DESCRIBE ' . PMA\libraries\Util::backquote($this->tableName);
$result = $GLOBALS['dbi']->tryQuery($sql, null, PMA\libraries\DatabaseInterface::QUERY_STORE);
if (!$result || !$GLOBALS['dbi']->numRows($result)) {
$this->showMissingTableError();
}
if ($this->showKeys) {
$indexes = PMA\libraries\Index::getFromTable($this->tableName, $this->db);
$all_columns = array();
foreach ($indexes as $index) {
$all_columns = array_merge($all_columns, array_flip(array_keys($index->getColumns())));
}
$this->fields = array_keys($all_columns);
} else {
while ($row = $GLOBALS['dbi']->fetchRow($result)) {
$this->fields[] = $row[0];
}
}
}
/**
* Prepare unsorted sql query and sort by key drop down
*
* @param array $analyzed_sql_results analyzed sql results
* @param string $sort_expression sort expression
*
* @return array two element array - $unsorted_sql_query, $drop_down_html
*
* @access private
*
* @see _getTableHeaders()
*/
private function _getUnsortedSqlAndSortByKeyDropDown($analyzed_sql_results, $sort_expression)
{
$drop_down_html = '';
$unsorted_sql_query = Query::replaceClause($analyzed_sql_results['statement'], $analyzed_sql_results['parser']->list, 'ORDER BY', '');
// Data is sorted by indexes only if it there is only one table.
if ($this->_isSelect($analyzed_sql_results)) {
// grab indexes data:
$indexes = Index::getFromTable($this->__get('table'), $this->__get('db'));
// do we have any index?
if (!empty($indexes)) {
$drop_down_html = $this->_getSortByKeyDropDown($indexes, $sort_expression, $unsorted_sql_query);
}
}
return array($unsorted_sql_query, $drop_down_html);
}
/**
* Returns descriptions of columns in given table (all or given by $column)
*
* @param string $database name of database
* @param string $table name of table to retrieve columns from
* @param string $column name of column, null to show all columns
* @param boolean $full whether to return full info or only column names
* @param mixed $link mysql link resource
*
* @return array array indexed by column names or,
* if $column is given, flat array description
*/
public function getColumns($database, $table, $column = null, $full = false, $link = null)
{
$sql = $this->getColumnsSql($database, $table, $column, $full);
$fields = $this->fetchResult($sql, 'Field', null, $link);
if (!is_array($fields) || count($fields) == 0) {
return array();
}
// Check if column is a part of multiple-column index and set its 'Key'.
$indexes = Index::getFromTable($table, $database);
foreach ($fields as $field => $field_data) {
if (!empty($field_data['Key'])) {
continue;
}
foreach ($indexes as $index) {
/** @var Index $index */
if (!$index->hasColumn($field)) {
continue;
}
$index_columns = $index->getColumns();
if ($index_columns[$field]->getSeqInIndex() > 1) {
if ($index->isUnique()) {
$fields[$field]['Key'] = 'UNI';
} else {
$fields[$field]['Key'] = 'MUL';
}
}
}
}
return $column != null ? array_shift($fields) : $fields;
}
/**
* Get columns with indexes
*
* @param int $types types bitmask
*
* @return array an array of columns
*/
function getColumnsWithIndex($types)
{
$columns_with_index = array();
foreach (Index::getFromTableByChoice($this->_name, $this->_db_name, $types) as $index) {
$columns = $index->getColumns();
foreach ($columns as $column_name => $dummy) {
$columns_with_index[] = $column_name;
}
}
return $columns_with_index;
}
/**
* Index action
*
* @return void
*/
public function indexAction()
{
PageSettings::showGroup('TableStructure');
/**
* Function implementations for this script
*/
include_once 'libraries/check_user_privileges.lib.php';
include_once 'libraries/index.lib.php';
include_once 'libraries/sql.lib.php';
include_once 'libraries/bookmark.lib.php';
$this->response->getHeader()->getScripts()->addFiles(array('tbl_structure.js', 'indexes.js'));
/**
* Handle column moving
*/
if (isset($_REQUEST['move_columns']) && is_array($_REQUEST['move_columns']) && $this->response->isAjax()) {
$this->moveColumns();
return;
}
/**
* handle MySQL reserved words columns check
*/
if (isset($_REQUEST['reserved_word_check'])) {
if ($GLOBALS['cfg']['ReservedWordDisableWarning'] === false) {
$columns_names = $_REQUEST['field_name'];
$reserved_keywords_names = array();
foreach ($columns_names as $column) {
if (SqlParser\Context::isKeyword(trim($column), true)) {
$reserved_keywords_names[] = trim($column);
}
}
if (SqlParser\Context::isKeyword(trim($this->table), true)) {
$reserved_keywords_names[] = trim($this->table);
}
if (count($reserved_keywords_names) == 0) {
$this->response->setRequestStatus(false);
}
$this->response->addJSON('message', sprintf(_ngettext('The name \'%s\' is a MySQL reserved keyword.', 'The names \'%s\' are MySQL reserved keywords.', count($reserved_keywords_names)), implode(',', $reserved_keywords_names)));
} else {
$this->response->setRequestStatus(false);
}
return;
}
/**
* A click on Change has been made for one column
*/
if (isset($_REQUEST['change_column'])) {
$this->displayHtmlForColumnChange(null, 'tbl_structure.php');
return;
}
/**
* Adding or editing partitioning of the table
*/
if (isset($_REQUEST['edit_partitioning']) && !isset($_REQUEST['save_partitioning'])) {
$this->displayHtmlForPartitionChange();
return;
}
/**
* handle multiple field commands if required
*
* submit_mult_*_x comes from IE if <input type="img" ...> is used
*/
$submit_mult = $this->getMultipleFieldCommandType();
if (!empty($submit_mult)) {
if (isset($_REQUEST['selected_fld'])) {
if ($submit_mult == 'browse') {
// browsing the table displaying only selected columns
$this->displayTableBrowseForSelectedColumns($GLOBALS['goto'], $GLOBALS['pmaThemeImage']);
} else {
// handle multiple field commands
// handle confirmation of deleting multiple columns
$action = 'tbl_structure.php';
$GLOBALS['selected'] = $_REQUEST['selected_fld'];
list($what_ret, $query_type_ret, $is_unset_submit_mult, $mult_btn_ret, $centralColsError) = $this->getDataForSubmitMult($submit_mult, $_REQUEST['selected_fld'], $action);
//update the existing variables
// todo: refactor mult_submits.inc.php such as
// below globals are not needed anymore
if (isset($what_ret)) {
$GLOBALS['what'] = $what_ret;
global $what;
}
if (isset($query_type_ret)) {
$GLOBALS['query_type'] = $query_type_ret;
global $query_type;
}
if ($is_unset_submit_mult) {
unset($submit_mult);
}
if (isset($mult_btn_ret)) {
$GLOBALS['mult_btn'] = $mult_btn_ret;
global $mult_btn;
}
include 'libraries/mult_submits.inc.php';
/**
* if $submit_mult == 'change', execution will have stopped
* at this point
*/
if (empty($message)) {
$message = Message::success();
}
$this->response->addHTML(Util::getMessage($message, $sql_query));
}
} else {
$this->response->setRequestStatus(false);
$this->response->addJSON('message', __('No column selected.'));
}
}
// display secondary level tabs if necessary
$engine = $this->table_obj->getStatusInfo('ENGINE');
$this->response->addHTML(Template::get('table/secondary_tabs')->render(array('url_params' => array('db' => $this->db, 'table' => $this->table), 'engine' => $engine)));
$this->response->addHTML('<div id="structure_content">');
/**
* Modifications have been submitted -> updates the table
*/
if (isset($_REQUEST['do_save_data'])) {
$regenerate = $this->updateColumns();
if ($regenerate) {
// This happens when updating failed
// @todo: do something appropriate
} else {
// continue to show the table's structure
unset($_REQUEST['selected']);
}
}
/**
* Modifications to the partitioning have been submitted -> updates the table
*/
if (isset($_REQUEST['save_partitioning'])) {
$this->updatePartitioning();
}
/**
* Adding indexes
*/
if (isset($_REQUEST['add_key']) || isset($_REQUEST['partition_maintenance'])) {
//todo: set some variables for sql.php include, to be eliminated
//after refactoring sql.php
$db = $this->db;
$table = $this->table;
$cfg = $GLOBALS['cfg'];
$is_superuser = $GLOBALS['dbi']->isSuperuser();
$pmaThemeImage = $GLOBALS['pmaThemeImage'];
include 'sql.php';
$GLOBALS['reload'] = true;
}
/**
* Gets the relation settings
*/
$cfgRelation = PMA_getRelationsParam();
/**
* Runs common work
*/
// set db, table references, for require_once that follows
// got to be eliminated in long run
$db =& $this->db;
$table =& $this->table;
include_once 'libraries/tbl_common.inc.php';
$this->_db_is_system_schema = $db_is_system_schema;
$this->_url_query = $url_query . '&goto=tbl_structure.php&back=tbl_structure.php';
$url_params['goto'] = 'tbl_structure.php';
$url_params['back'] = 'tbl_structure.php';
/**
* Gets tables information
*/
include_once 'libraries/tbl_info.inc.php';
// 2. Gets table keys and retains them
// @todo should be: $server->db($db)->table($table)->primary()
$primary = Index::getPrimary($this->table, $this->db);
$columns_with_index = $this->dbi->getTable($this->db, $this->table)->getColumnsWithIndex(Index::UNIQUE | Index::INDEX | Index::SPATIAL | Index::FULLTEXT);
$columns_with_unique_index = $this->dbi->getTable($this->db, $this->table)->getColumnsWithIndex(Index::UNIQUE);
// 3. Get fields
$fields = (array) $this->dbi->getColumns($this->db, $this->table, null, true);
// Get more complete field information
// For now, this is done just for MySQL 4.1.2+ new TIMESTAMP options
// but later, if the analyser returns more information, it
// could be executed for any MySQL version and replace
// the info given by SHOW FULL COLUMNS FROM.
//
// We also need this to correctly learn if a TIMESTAMP is NOT NULL, since
// SHOW FULL COLUMNS or INFORMATION_SCHEMA incorrectly says NULL
// and SHOW CREATE TABLE says NOT NULL (tested
// in MySQL 4.0.25 and 5.0.21, http://bugs.mysql.com/20910).
$show_create_table = $this->table_obj->showCreate();
$parser = new SqlParser\Parser($show_create_table);
/**
* @var CreateStatement $stmt
*/
$stmt = $parser->statements[0];
$create_table_fields = SqlTable::getFields($stmt);
//display table structure
$this->response->addHTML($this->displayStructure($cfgRelation, $columns_with_unique_index, $url_params, $primary, $fields, $columns_with_index, $create_table_fields));
$this->response->addHTML('</div>');
}
/**
* Send table columns for foreign table dropdown
*
* @return void
*
*/
public function getDropdownValueForTableAction()
{
$foreignTable = $_REQUEST['foreignTable'];
$table_obj = $this->dbi->getTable($_REQUEST['foreignDb'], $foreignTable);
// Since views do not have keys defined on them provide the full list of
// columns
if ($table_obj->isView()) {
$columnList = $table_obj->getColumns(false, false);
} else {
$columnList = $table_obj->getIndexedColumns(false, false);
}
$columns = array();
foreach ($columnList as $column) {
$columns[] = htmlspecialchars($column);
}
$this->response->addJSON('columns', $columns);
// @todo should be: $server->db($db)->table($table)->primary()
$primary = Index::getPrimary($foreignTable, $_REQUEST['foreignDb']);
if (false === $primary) {
return;
}
$this->response->addJSON('primary', array_keys($primary->getColumns()));
}
/**
* Function to check over array of indexes and look for common problems
*
* @param string $table table name
* @param string $schema schema name
*
* @return string Output HTML
* @access public
*/
public static function findDuplicates($table, $schema)
{
$indexes = Index::getFromTable($table, $schema);
$output = '';
// count($indexes) < 2:
// there is no need to check if there less than two indexes
if (count($indexes) < 2) {
return $output;
}
// remove last index from stack and ...
while ($while_index = array_pop($indexes)) {
// ... compare with every remaining index in stack
foreach ($indexes as $each_index) {
if ($each_index->getCompareData() !== $while_index->getCompareData()) {
continue;
}
// did not find any difference
// so it makes no sense to have this two equal indexes
$message = Message::notice(__('The indexes %1$s and %2$s seem to be equal and one of them ' . 'could possibly be removed.'));
$message->addParam($each_index->getName());
$message->addParam($while_index->getName());
$output .= $message->getDisplay();
// there is no need to check any further indexes if we have already
// found that this one has a duplicate
continue 2;
}
}
return $output;
}
/**
* Process the data from the edit/create index form,
* run the query to build the new index
* and moves back to "tbl_sql.php"
*
* @return void
*/
public function doSaveDataAction()
{
$error = false;
$sql_query = $this->dbi->getTable($this->db, $this->table)->getSqlQueryForIndexCreateOrEdit($this->index, $error);
// If there is a request for SQL previewing.
if (isset($_REQUEST['preview_sql'])) {
$this->response->addJSON('sql_data', Template::get('preview_sql')->render(array('query_data' => $sql_query)));
} elseif (!$error) {
$this->dbi->query($sql_query);
if ($GLOBALS['is_ajax_request'] == true) {
$message = Message::success(__('Table %1$s has been altered successfully.'));
$message->addParam($this->table);
$this->response->addJSON('message', Util::getMessage($message, $sql_query, 'success'));
$this->response->addJSON('index_table', Index::getHtmlForIndexes($this->table, $this->db));
} else {
include 'tbl_structure.php';
}
} else {
$this->response->setRequestStatus(false);
$this->response->addJSON('message', $error);
}
}