2 /* vim: set expandtab sw=4 ts=4 sts=4: */
4 * holds the database index class
8 if (! defined('PHPMYADMIN')) {
13 * Index manipulation class
16 * @since phpMyAdmin 3.0.0
21 * Class-wide storage container for indexes (caching, singleton)
25 private static $_registry = array();
28 * @var string The name of the schema
30 private $_schema = '';
33 * @var string The name of the table
38 * @var string The name of the index
47 private $_columns = array();
50 * The index method used (BTREE, SPATIAL, FULLTEXT, HASH, RTREE).
57 * The index choice (PRIMARY, UNIQUE, INDEX, SPATIAL, FULLTEXT)
61 private $_choice = '';
68 private $_remarks = '';
71 * Any comment provided for the index with a COMMENT attribute when the
76 private $_comment = '';
79 * @var integer 0 if the index cannot contain duplicates, 1 if it can.
81 private $_non_unique = 0;
84 * Indicates how the key is packed. NULL if it is not.
88 private $_packed = null;
93 * @param array $params parameters
95 public function __construct($params = array())
101 * Creates(if not already created) and returns the corresponding Index object
103 * @param string $schema database name
104 * @param string $table table name
105 * @param string $index_name index name
107 * @return PMA_Index corresponding Index object
109 static public function singleton($schema, $table, $index_name = '')
111 PMA_Index
::_loadIndexes($table, $schema);
112 if (! isset(PMA_Index
::$_registry[$schema][$table][$index_name])) {
113 $index = new PMA_Index
;
114 if (/*overload*/mb_strlen($index_name)) {
115 $index->setName($index_name);
116 PMA_Index
::$_registry[$schema][$table][$index->getName()] = $index;
120 return PMA_Index
::$_registry[$schema][$table][$index_name];
125 * returns an array with all indexes from the given table
127 * @param string $table table
128 * @param string $schema schema
130 * @return array array of indexes
132 static public function getFromTable($table, $schema)
134 PMA_Index
::_loadIndexes($table, $schema);
136 if (isset(PMA_Index
::$_registry[$schema][$table])) {
137 return PMA_Index
::$_registry[$schema][$table];
144 * return primary if set, false otherwise
146 * @param string $table table
147 * @param string $schema schema
149 * @return mixed primary index or false if no one exists
151 static public function getPrimary($table, $schema)
153 PMA_Index
::_loadIndexes($table, $schema);
155 if (isset(PMA_Index
::$_registry[$schema][$table]['PRIMARY'])) {
156 return PMA_Index
::$_registry[$schema][$table]['PRIMARY'];
163 * Load index data for table
165 * @param string $table table
166 * @param string $schema schema
168 * @return boolean whether loading was successful
170 static private function _loadIndexes($table, $schema)
172 if (isset(PMA_Index
::$_registry[$schema][$table])) {
176 $_raw_indexes = $GLOBALS['dbi']->getTableIndexes($schema, $table);
177 foreach ($_raw_indexes as $_each_index) {
178 $_each_index['Schema'] = $schema;
179 $keyName = $_each_index['Key_name'];
180 if (! isset(PMA_Index
::$_registry[$schema][$table][$keyName])) {
181 $key = new PMA_Index($_each_index);
182 PMA_Index
::$_registry[$schema][$table][$keyName] = $key;
184 $key = PMA_Index
::$_registry[$schema][$table][$keyName];
187 $key->addColumn($_each_index);
194 * Add column to index
196 * @param array $params column params
200 public function addColumn($params)
202 if (isset($params['Column_name'])
203 && /*overload*/mb_strlen($params['Column_name'])
205 $this->_columns
[$params['Column_name']] = new PMA_Index_Column($params);
210 * Adds a list of columns to the index
212 * @param array $columns array containing details about the columns
216 public function addColumns($columns)
220 if (isset($columns['names'])) {
223 // $columns[sub_parts][]
224 foreach ($columns['names'] as $key => $name) {
225 $sub_part = isset($columns['sub_parts'][$key])
226 ?
$columns['sub_parts'][$key] : '';
228 'Column_name' => $name,
229 'Sub_part' => $sub_part,
233 // coming from SHOW INDEXES
235 // $columns[][sub_part]
237 $_columns = $columns;
240 foreach ($_columns as $column) {
241 $this->addColumn($column);
246 * Returns true if $column indexed in this index
248 * @param string $column the column
250 * @return boolean true if $column indexed in this index
252 public function hasColumn($column)
254 return isset($this->_columns
[$column]);
260 * @param array $params index details
264 public function set($params)
266 if (isset($params['columns'])) {
267 $this->addColumns($params['columns']);
269 if (isset($params['Schema'])) {
270 $this->_schema
= $params['Schema'];
272 if (isset($params['Table'])) {
273 $this->_table
= $params['Table'];
275 if (isset($params['Key_name'])) {
276 $this->_name
= $params['Key_name'];
278 if (isset($params['Index_type'])) {
279 $this->_type
= $params['Index_type'];
281 if (isset($params['Comment'])) {
282 $this->_remarks
= $params['Comment'];
284 if (isset($params['Index_comment'])) {
285 $this->_comment
= $params['Index_comment'];
287 if (isset($params['Non_unique'])) {
288 $this->_non_unique
= $params['Non_unique'];
290 if (isset($params['Packed'])) {
291 $this->_packed
= $params['Packed'];
293 if ('PRIMARY' == $this->_name
) {
294 $this->_choice
= 'PRIMARY';
295 } elseif ('FULLTEXT' == $this->_type
) {
296 $this->_choice
= 'FULLTEXT';
297 } elseif ('SPATIAL' == $this->_type
) {
298 $this->_choice
= 'SPATIAL';
299 } elseif ('0' == $this->_non_unique
) {
300 $this->_choice
= 'UNIQUE';
302 $this->_choice
= 'INDEX';
307 * Returns the number of columns of the index
309 * @return integer the number of the columns
311 public function getColumnCount()
313 return count($this->_columns
);
317 * Returns the index comment
319 * @return string index comment
321 public function getComment()
323 return $this->_comment
;
327 * Returns index remarks
329 * @return string index remarks
331 public function getRemarks()
333 return $this->_remarks
;
337 * Returns concatenated remarks and comment
339 * @return string concatenated remarks and comment
341 public function getComments()
343 $comments = $this->getRemarks();
344 if (/*overload*/mb_strlen($comments)) {
347 $comments .= $this->getComment();
353 * Returns index type ((BTREE, SPATIAL, FULLTEXT, HASH, RTREE)
355 * @return string index type
357 public function getType()
363 * Returns index choice (PRIMARY, UNIQUE, INDEX, SPATIAL, FULLTEXT)
365 * @return string index choice
367 public function getChoice()
369 return $this->_choice
;
373 * Return a list of all index choices
375 * @return string[] index choices
377 static public function getIndexChoices()
389 * Returns HTML for the index choice selector
391 * @return string HTML for the index choice selector
393 public function generateIndexSelector()
397 foreach (PMA_Index
::getIndexChoices() as $each_index_choice) {
398 if ($each_index_choice === 'PRIMARY'
399 && $this->_choice
!== 'PRIMARY'
400 && PMA_Index
::getPrimary($this->_table
, $this->_schema
)
402 // skip PRIMARY if there is already one in the table
405 $html_options .= '<option value="' . $each_index_choice . '"'
406 . (($this->_choice
== $each_index_choice)
407 ?
' selected="selected"'
409 . '>' . $each_index_choice . '</option>' . "\n";
412 return $html_options;
416 * Returns how the index is packed
418 * @return string how the index is packed
420 public function getPacked()
422 return $this->_packed
;
426 * Returns 'No'/false if the index is not packed,
427 * how the index is packed if packed
429 * @param boolean $as_text whether to output should be in text
431 * @return mixed how index is packed
433 public function isPacked($as_text = false)
447 if (null === $this->_packed
) {
451 return $this->_packed
;
455 * Returns integer 0 if the index cannot contain duplicates, 1 if it can
457 * @return integer 0 if the index cannot contain duplicates, 1 if it can
459 public function getNonUnique()
461 return $this->_non_unique
;
465 * Returns whether the index is a 'Unique' index
467 * @param boolean $as_text whether to output should be in text
469 * @return mixed whether the index is a 'Unique' index
471 public function isUnique($as_text = false)
485 return $r[$this->_non_unique
];
489 * Returns the name of the index
491 * @return string the name of the index
493 public function getName()
499 * Sets the name of the index
501 * @param string $name index name
505 public function setName($name)
507 $this->_name
= (string) $name;
511 * Returns the columns of the index
513 * @return PMA_Index_Column[] the columns of the index
515 public function getColumns()
517 return $this->_columns
;
523 * @param string $table The table name
524 * @param string $schema The schema name
525 * @param boolean $print_mode Whether the output is for the print mode
527 * @return string HTML for showing index
531 static public function getView($table, $schema, $print_mode = false)
533 $indexes = PMA_Index
::getFromTable($table, $schema);
535 $no_indexes_class = count($indexes) > 0 ?
' hide' : '';
536 $no_indexes = "<div class='no_indexes_defined$no_indexes_class'>";
537 $no_indexes .= PMA_Message
::notice(__('No index defined!'))->getDisplay();
538 $no_indexes .= '</div>';
541 $r = '<fieldset class="index_info">';
542 $r .= '<legend id="index_header">' . __('Indexes');
543 $r .= PMA_Util
::showMySQLDocu('optimizing-database-structure');
547 if (count($indexes) < 1) {
551 $r .= PMA_Index
::findDuplicates($table, $schema);
553 $r = '<h3>' . __('Indexes') . '</h3>';
555 if (count($indexes) < 1) {
559 $r .= '<table id="table_index">';
563 $r .= '<th colspan="2">' . __('Action') . '</th>';
565 $r .= '<th>' . __('Keyname') . '</th>';
566 $r .= '<th>' . __('Type') . '</th>';
567 $r .= '<th>' . __('Unique') . '</th>';
568 $r .= '<th>' . __('Packed') . '</th>';
569 $r .= '<th>' . __('Column') . '</th>';
570 $r .= '<th>' . __('Cardinality') . '</th>';
571 $r .= '<th>' . __('Collation') . '</th>';
572 $r .= '<th>' . __('Null') . '</th>';
573 $r .= '<th>' . __('Comment') . '</th>';
579 foreach ($indexes as $index) {
580 $row_span = ' rowspan="' . $index->getColumnCount() . '" ';
582 $r .= '<tr class="noclick ' . ($odd_row ?
'odd' : 'even') . '">';
585 $this_params = $GLOBALS['url_params'];
586 $this_params['index'] = $index->getName();
587 $r .= '<td class="edit_index';
589 $r .= '" ' . $row_span . '>'
592 $r .= '" href="tbl_indexes.php' . PMA_URL_getCommon($this_params)
593 . '">' . PMA_Util
::getIcon('b_edit.png', __('Edit')) . '</a>'
595 $this_params = $GLOBALS['url_params'];
596 if ($index->getName() == 'PRIMARY') {
597 $this_params['sql_query'] = 'ALTER TABLE '
598 . PMA_Util
::backquote($table)
599 . ' DROP PRIMARY KEY;';
600 $this_params['message_to_show']
601 = __('The primary key has been dropped.');
602 $js_msg = PMA_jsFormat(
603 'ALTER TABLE ' . $table . ' DROP PRIMARY KEY'
606 $this_params['sql_query'] = 'ALTER TABLE '
607 . PMA_Util
::backquote($table) . ' DROP INDEX '
608 . PMA_Util
::backquote($index->getName()) . ';';
609 $this_params['message_to_show'] = sprintf(
610 __('Index %s has been dropped.'), $index->getName()
613 $js_msg = PMA_jsFormat(
614 'ALTER TABLE ' . $table . ' DROP INDEX '
615 . $index->getName() . ';'
620 $r .= '<td ' . $row_span . '>';
621 $r .= '<input type="hidden" class="drop_primary_key_index_msg"'
622 . ' value="' . $js_msg . '" />';
623 $r .= ' <a class="drop_primary_key_index_anchor';
625 $r .= '" href="sql.php' . PMA_URL_getCommon($this_params)
627 . PMA_Util
::getIcon('b_drop.png', __('Drop')) . '</a>'
632 $r .= '<th ' . $row_span . '>'
633 . htmlspecialchars($index->getName())
636 $r .= '<td ' . $row_span . '>'
637 . htmlspecialchars($index->getName())
640 $r .= '<td ' . $row_span . '>'
641 . htmlspecialchars($index->getType())
643 $r .= '<td ' . $row_span . '>' . $index->isUnique(true) . '</td>';
644 $r .= '<td ' . $row_span . '>' . $index->isPacked(true) . '</td>';
646 foreach ($index->getColumns() as $column) {
647 if ($column->getSeqInIndex() > 1) {
648 $r .= '<tr class="noclick ' . ($odd_row ?
'odd' : 'even') . '">';
650 $r .= '<td>' . htmlspecialchars($column->getName());
651 if ($column->getSubPart()) {
652 $r .= ' (' . $column->getSubPart() . ')';
656 . htmlspecialchars($column->getCardinality())
659 . htmlspecialchars($column->getCollation())
662 . htmlspecialchars($column->getNull(true))
665 if ($column->getSeqInIndex() == 1
667 $r .= '<td ' . $row_span . '>'
668 . htmlspecialchars($index->getComments()) . '</td>';
671 } // end foreach $index['Sequences']
673 $odd_row = ! $odd_row;
685 * Gets the properties in an array for comparison purposes
687 * @return array an array containing the properties of the index
689 public function getCompareData()
692 // 'Non_unique' => $this->_non_unique,
693 'Packed' => $this->_packed
,
694 'Index_type' => $this->_type
,
697 foreach ($this->_columns
as $column) {
698 $data['columns'][] = $column->getCompareData();
705 * Function to check over array of indexes and look for common problems
707 * @param string $table table name
708 * @param string $schema schema name
710 * @return string Output HTML
713 static public function findDuplicates($table, $schema)
715 $indexes = PMA_Index
::getFromTable($table, $schema);
719 // count($indexes) < 2:
720 // there is no need to check if there less than two indexes
721 if (count($indexes) < 2) {
725 // remove last index from stack and ...
726 while ($while_index = array_pop($indexes)) {
727 // ... compare with every remaining index in stack
728 foreach ($indexes as $each_index) {
729 if ($each_index->getCompareData() !== $while_index->getCompareData()
734 // did not find any difference
735 // so it makes no sense to have this two equal indexes
737 $message = PMA_Message
::notice(
738 __('The indexes %1$s and %2$s seem to be equal and one of them could possibly be removed.')
740 $message->addParam($each_index->getName());
741 $message->addParam($while_index->getName());
742 $output .= $message->getDisplay();
744 // there is no need to check any further indexes if we have already
745 // found that this one has a duplicate
754 * Index column wrapper
756 * @package PhpMyAdmin
758 class PMA_Index_Column
761 * @var string The column name
766 * @var integer The column sequence number in the index, starting with 1.
768 private $_seq_in_index = 1;
771 * @var string How the column is sorted in the index. “A” (Ascending) or
774 private $_collation = null;
777 * The number of indexed characters if the column is only partly indexed,
778 * NULL if the entire column is indexed.
782 private $_sub_part = null;
785 * Contains YES if the column may contain NULL.
786 * If not, the column contains NO.
793 * An estimate of the number of unique values in the index. This is updated
794 * by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on
795 * statistics stored as integers, so the value is not necessarily exact even
796 * for small tables. The higher the cardinality, the greater the chance that
797 * MySQL uses the index when doing joins.
801 private $_cardinality = null;
806 * @param array $params an array containing the parameters of the index column
808 public function __construct($params = array())
814 * Sets parameters of the index column
816 * @param array $params an array containing the parameters of the index column
820 public function set($params)
822 if (isset($params['Column_name'])) {
823 $this->_name
= $params['Column_name'];
825 if (isset($params['Seq_in_index'])) {
826 $this->_seq_in_index
= $params['Seq_in_index'];
828 if (isset($params['Collation'])) {
829 $this->_collation
= $params['Collation'];
831 if (isset($params['Cardinality'])) {
832 $this->_cardinality
= $params['Cardinality'];
834 if (isset($params['Sub_part'])) {
835 $this->_sub_part
= $params['Sub_part'];
837 if (isset($params['Null'])) {
838 $this->_null
= $params['Null'];
843 * Returns the column name
845 * @return string column name
847 public function getName()
853 * Return the column collation
855 * @return string column collation
857 public function getCollation()
859 return $this->_collation
;
863 * Returns the cardinality of the column
865 * @return int cardinality of the column
867 public function getCardinality()
869 return $this->_cardinality
;
873 * Returns whether the column is nullable
875 * @param boolean $as_text whether to returned the string representation
877 * @return mixed nullability of the column. True/false or Yes/No depending
878 * on the value of the $as_text parameter
880 public function getNull($as_text = false)
883 ?
(!$this->_null ||
$this->_null
== 'NO' ?
__('No') : __('Yes'))
888 * Returns the sequence number of the column in the index
890 * @return int sequence number of the column in the index
892 public function getSeqInIndex()
894 return $this->_seq_in_index
;
898 * Returns the number of indexed characters if the column is only
901 * @return int the number of indexed characters
903 public function getSubPart()
905 return $this->_sub_part
;
909 * Gets the properties in an array for comparison purposes
911 * @return array an array containing the properties of the index column
913 public function getCompareData()
916 'Column_name' => $this->_name
,
917 'Seq_in_index' => $this->_seq_in_index
,
918 'Collation' => $this->_collation
,
919 'Sub_part' => $this->_sub_part
,
920 'Null' => $this->_null
,