returns an array with all indexes from the given table
|
public static getFromTable ( string $table, string $schema ) : |
||
| $table | string | table |
| $schema | string | schema |
| return | array of indexes | |
/**
* 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;
}
/**
* 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;
}
