2 /* vim: set expandtab sw=4 ts=4 sts=4: */
4 * holds the database index class
8 namespace PMA\libraries
;
10 use PMA\libraries\URL
;
11 use PMA\libraries\Sanitize
;
14 * Index manipulation class
17 * @since phpMyAdmin 3.0.0
28 * Class-wide storage container for indexes (caching, singleton)
32 private static $_registry = array();
35 * @var string The name of the schema
37 private $_schema = '';
40 * @var string The name of the table
45 * @var string The name of the index
54 private $_columns = array();
57 * The index method used (BTREE, HASH, RTREE).
64 * The index choice (PRIMARY, UNIQUE, INDEX, SPATIAL, FULLTEXT)
68 private $_choice = '';
75 private $_remarks = '';
78 * Any comment provided for the index with a COMMENT attribute when the
83 private $_comment = '';
86 * @var integer 0 if the index cannot contain duplicates, 1 if it can.
88 private $_non_unique = 0;
91 * Indicates how the key is packed. NULL if it is not.
95 private $_packed = null;
98 * Block size for the index
102 private $_key_block_size = null;
105 * Parser option for the index
109 private $_parser = null;
114 * @param array $params parameters
116 public function __construct($params = array())
122 * Creates(if not already created) and returns the corresponding Index object
124 * @param string $schema database name
125 * @param string $table table name
126 * @param string $index_name index name
128 * @return Index corresponding Index object
130 static public function singleton($schema, $table, $index_name = '')
132 Index
::_loadIndexes($table, $schema);
133 if (! isset(Index
::$_registry[$schema][$table][$index_name])) {
135 if (strlen($index_name) > 0) {
136 $index->setName($index_name);
137 Index
::$_registry[$schema][$table][$index->getName()] = $index;
141 return Index
::$_registry[$schema][$table][$index_name];
146 * returns an array with all indexes from the given table
148 * @param string $table table
149 * @param string $schema schema
151 * @return Index[] array of indexes
153 static public function getFromTable($table, $schema)
155 Index
::_loadIndexes($table, $schema);
157 if (isset(Index
::$_registry[$schema][$table])) {
158 return Index
::$_registry[$schema][$table];
165 * Returns an array with all indexes from the given table of the requested types
167 * @param string $table table
168 * @param string $schema schema
169 * @param int $choices choices
171 * @return Index[] array of indexes
173 static public function getFromTableByChoice($table, $schema, $choices = 31)
176 foreach (self
::getFromTable($table, $schema) as $index) {
177 if (($choices & Index
::PRIMARY
)
178 && $index->getChoice() == 'PRIMARY'
182 if (($choices & Index
::UNIQUE
)
183 && $index->getChoice() == 'UNIQUE'
187 if (($choices & Index
::INDEX
)
188 && $index->getChoice() == 'INDEX'
192 if (($choices & Index
::SPATIAL
)
193 && $index->getChoice() == 'SPATIAL'
197 if (($choices & Index
::FULLTEXT
)
198 && $index->getChoice() == 'FULLTEXT'
207 * return primary if set, false otherwise
209 * @param string $table table
210 * @param string $schema schema
212 * @return mixed primary index or false if no one exists
214 static public function getPrimary($table, $schema)
216 Index
::_loadIndexes($table, $schema);
218 if (isset(Index
::$_registry[$schema][$table]['PRIMARY'])) {
219 return Index
::$_registry[$schema][$table]['PRIMARY'];
226 * Load index data for table
228 * @param string $table table
229 * @param string $schema schema
231 * @return boolean whether loading was successful
233 static private function _loadIndexes($table, $schema)
235 if (isset(Index
::$_registry[$schema][$table])) {
239 $_raw_indexes = $GLOBALS['dbi']->getTableIndexes($schema, $table);
240 foreach ($_raw_indexes as $_each_index) {
241 $_each_index['Schema'] = $schema;
242 $keyName = $_each_index['Key_name'];
243 if (! isset(Index
::$_registry[$schema][$table][$keyName])) {
244 $key = new Index($_each_index);
245 Index
::$_registry[$schema][$table][$keyName] = $key;
247 $key = Index
::$_registry[$schema][$table][$keyName];
250 $key->addColumn($_each_index);
257 * Add column to index
259 * @param array $params column params
263 public function addColumn($params)
265 if (isset($params['Column_name'])
266 && strlen($params['Column_name']) > 0
268 $this->_columns
[$params['Column_name']] = new IndexColumn($params);
273 * Adds a list of columns to the index
275 * @param array $columns array containing details about the columns
279 public function addColumns($columns)
283 if (isset($columns['names'])) {
286 // $columns[sub_parts][]
287 foreach ($columns['names'] as $key => $name) {
288 $sub_part = isset($columns['sub_parts'][$key])
289 ?
$columns['sub_parts'][$key] : '';
291 'Column_name' => $name,
292 'Sub_part' => $sub_part,
296 // coming from SHOW INDEXES
298 // $columns[][sub_part]
300 $_columns = $columns;
303 foreach ($_columns as $column) {
304 $this->addColumn($column);
309 * Returns true if $column indexed in this index
311 * @param string $column the column
313 * @return boolean true if $column indexed in this index
315 public function hasColumn($column)
317 return isset($this->_columns
[$column]);
323 * @param array $params index details
327 public function set($params)
329 if (isset($params['columns'])) {
330 $this->addColumns($params['columns']);
332 if (isset($params['Schema'])) {
333 $this->_schema
= $params['Schema'];
335 if (isset($params['Table'])) {
336 $this->_table
= $params['Table'];
338 if (isset($params['Key_name'])) {
339 $this->_name
= $params['Key_name'];
341 if (isset($params['Index_type'])) {
342 $this->_type
= $params['Index_type'];
344 if (isset($params['Comment'])) {
345 $this->_remarks
= $params['Comment'];
347 if (isset($params['Index_comment'])) {
348 $this->_comment
= $params['Index_comment'];
350 if (isset($params['Non_unique'])) {
351 $this->_non_unique
= $params['Non_unique'];
353 if (isset($params['Packed'])) {
354 $this->_packed
= $params['Packed'];
356 if (isset($params['Index_choice'])) {
357 $this->_choice
= $params['Index_choice'];
359 if ('PRIMARY' == $this->_name
) {
360 $this->_choice
= 'PRIMARY';
361 } elseif ('FULLTEXT' == $this->_type
) {
362 $this->_choice
= 'FULLTEXT';
364 } elseif ('SPATIAL' == $this->_type
) {
365 $this->_choice
= 'SPATIAL';
367 } elseif ('0' == $this->_non_unique
) {
368 $this->_choice
= 'UNIQUE';
370 $this->_choice
= 'INDEX';
373 if (isset($params['Key_block_size'])) {
374 $this->_key_block_size
= $params['Key_block_size'];
376 if (isset($params['Parser'])) {
377 $this->_parser
= $params['Parser'];
382 * Returns the number of columns of the index
384 * @return integer the number of the columns
386 public function getColumnCount()
388 return count($this->_columns
);
392 * Returns the index comment
394 * @return string index comment
396 public function getComment()
398 return $this->_comment
;
402 * Returns index remarks
404 * @return string index remarks
406 public function getRemarks()
408 return $this->_remarks
;
412 * Return the key block size
416 public function getKeyBlockSize()
418 return $this->_key_block_size
;
426 public function getParser()
428 return $this->_parser
;
432 * Returns concatenated remarks and comment
434 * @return string concatenated remarks and comment
436 public function getComments()
438 $comments = $this->getRemarks();
439 if (strlen($comments) > 0) {
442 $comments .= $this->getComment();
448 * Returns index type (BTREE, HASH, RTREE)
450 * @return string index type
452 public function getType()
458 * Returns index choice (PRIMARY, UNIQUE, INDEX, SPATIAL, FULLTEXT)
460 * @return string index choice
462 public function getChoice()
464 return $this->_choice
;
468 * Return a list of all index choices
470 * @return string[] index choices
472 static public function getIndexChoices()
484 * Returns a lit of all index types
486 * @return string[] index types
488 static public function getIndexTypes()
497 * Returns HTML for the index choice selector
499 * @param boolean $edit_table whether this is table editing
501 * @return string HTML for the index choice selector
503 public function generateIndexChoiceSelector($edit_table)
505 $html_options = '<select name="index[Index_choice]"'
506 . ' id="select_index_choice" '
507 . ($edit_table ?
'disabled="disabled"' : '') . '>';
509 foreach (Index
::getIndexChoices() as $each_index_choice) {
510 if ($each_index_choice === 'PRIMARY'
511 && $this->_choice
!== 'PRIMARY'
512 && Index
::getPrimary($this->_table
, $this->_schema
)
514 // skip PRIMARY if there is already one in the table
517 $html_options .= '<option value="' . $each_index_choice . '"'
518 . (($this->_choice
== $each_index_choice)
519 ?
' selected="selected"'
521 . '>' . $each_index_choice . '</option>' . "\n";
523 $html_options .= '</select>';
525 return $html_options;
529 * Returns HTML for the index type selector
531 * @return string HTML for the index type selector
533 public function generateIndexTypeSelector()
535 $types = array("" => "--");
536 foreach (Index
::getIndexTypes() as $type) {
537 $types[$type] = $type;
540 return Util
::getDropdown(
541 "index[Index_type]", $types,
542 $this->_type
, "select_index_type"
547 * Returns how the index is packed
549 * @return string how the index is packed
551 public function getPacked()
553 return $this->_packed
;
557 * Returns 'No' if the index is not packed,
558 * how the index is packed if packed
562 public function isPacked()
564 if (null === $this->_packed
) {
568 return htmlspecialchars($this->_packed
);
572 * Returns integer 0 if the index cannot contain duplicates, 1 if it can
574 * @return integer 0 if the index cannot contain duplicates, 1 if it can
576 public function getNonUnique()
578 return $this->_non_unique
;
582 * Returns whether the index is a 'Unique' index
584 * @param boolean $as_text whether to output should be in text
586 * @return mixed whether the index is a 'Unique' index
588 public function isUnique($as_text = false)
602 return $r[$this->_non_unique
];
606 * Returns the name of the index
608 * @return string the name of the index
610 public function getName()
616 * Sets the name of the index
618 * @param string $name index name
622 public function setName($name)
624 $this->_name
= (string) $name;
628 * Returns the columns of the index
630 * @return IndexColumn[] the columns of the index
632 public function getColumns()
634 return $this->_columns
;
640 * @param string $table The table name
641 * @param string $schema The schema name
642 * @param boolean $print_mode Whether the output is for the print mode
644 * @return string HTML for showing index
648 static public function getHtmlForIndexes($table, $schema, $print_mode = false)
650 $indexes = Index
::getFromTable($table, $schema);
652 $no_indexes_class = count($indexes) > 0 ?
' hide' : '';
653 $no_indexes = "<div class='no_indexes_defined$no_indexes_class'>";
654 $no_indexes .= Message
::notice(__('No index defined!'))->getDisplay();
655 $no_indexes .= '</div>';
658 $r = '<fieldset class="index_info">';
659 $r .= '<legend id="index_header">' . __('Indexes');
660 $r .= Util
::showMySQLDocu('optimizing-database-structure');
664 if (count($indexes) < 1) {
668 $r .= Index
::findDuplicates($table, $schema);
670 $r = '<h3>' . __('Indexes') . '</h3>';
672 if (count($indexes) < 1) {
676 $r .= '<table id="table_index">';
680 $r .= '<th colspan="2" class="print_ignore">' . __('Action') . '</th>';
682 $r .= '<th>' . __('Keyname') . '</th>';
683 $r .= '<th>' . __('Type') . '</th>';
684 $r .= '<th>' . __('Unique') . '</th>';
685 $r .= '<th>' . __('Packed') . '</th>';
686 $r .= '<th>' . __('Column') . '</th>';
687 $r .= '<th>' . __('Cardinality') . '</th>';
688 $r .= '<th>' . __('Collation') . '</th>';
689 $r .= '<th>' . __('Null') . '</th>';
690 $r .= '<th>' . __('Comment') . '</th>';
695 foreach ($indexes as $index) {
696 $row_span = ' rowspan="' . $index->getColumnCount() . '" ';
698 $r .= '<tr class="noclick" >';
701 $this_params = $GLOBALS['url_params'];
702 $this_params['index'] = $index->getName();
703 $r .= '<td class="edit_index print_ignore';
705 $r .= '" ' . $row_span . '>'
708 $r .= '" href="tbl_indexes.php' . URL
::getCommon($this_params)
709 . '">' . Util
::getIcon('b_edit.png', __('Edit')) . '</a>'
711 $this_params = $GLOBALS['url_params'];
712 if ($index->getName() == 'PRIMARY') {
713 $this_params['sql_query'] = 'ALTER TABLE '
714 . Util
::backquote($table)
715 . ' DROP PRIMARY KEY;';
716 $this_params['message_to_show']
717 = __('The primary key has been dropped.');
718 $js_msg = Sanitize
::jsFormat($this_params['sql_query']);
720 $this_params['sql_query'] = 'ALTER TABLE '
721 . Util
::backquote($table) . ' DROP INDEX '
722 . Util
::backquote($index->getName()) . ';';
723 $this_params['message_to_show'] = sprintf(
724 __('Index %s has been dropped.'), htmlspecialchars($index->getName())
726 $js_msg = Sanitize
::jsFormat($this_params['sql_query']);
729 $r .= '<td ' . $row_span . ' class="print_ignore">';
730 $r .= '<input type="hidden" class="drop_primary_key_index_msg"'
731 . ' value="' . $js_msg . '" />';
732 $r .= ' <a class="drop_primary_key_index_anchor';
734 $r .= '" href="sql.php' . URL
::getCommon($this_params)
736 . Util
::getIcon('b_drop.png', __('Drop')) . '</a>'
741 $r .= '<th ' . $row_span . '>'
742 . htmlspecialchars($index->getName())
745 $r .= '<td ' . $row_span . '>'
746 . htmlspecialchars($index->getName())
749 $r .= '<td ' . $row_span . '>';
750 $type = $index->getType();
751 if (! empty($type)) {
752 $r .= htmlspecialchars($type);
754 $r .= htmlspecialchars($index->getChoice());
757 $r .= '<td ' . $row_span . '>' . $index->isUnique(true) . '</td>';
758 $r .= '<td ' . $row_span . '>' . $index->isPacked() . '</td>';
760 foreach ($index->getColumns() as $column) {
761 if ($column->getSeqInIndex() > 1) {
762 $r .= '<tr class="noclick" >';
764 $r .= '<td>' . htmlspecialchars($column->getName());
765 if ($column->getSubPart()) {
766 $r .= ' (' . htmlspecialchars($column->getSubPart()) . ')';
770 . htmlspecialchars($column->getCardinality())
773 . htmlspecialchars($column->getCollation())
776 . htmlspecialchars($column->getNull(true))
779 if ($column->getSeqInIndex() == 1
781 $r .= '<td ' . $row_span . '>'
782 . htmlspecialchars($index->getComments()) . '</td>';
785 } // end foreach $index['Sequences']
798 * Gets the properties in an array for comparison purposes
800 * @return array an array containing the properties of the index
802 public function getCompareData()
805 // 'Non_unique' => $this->_non_unique,
806 'Packed' => $this->_packed
,
807 'Index_choice' => $this->_choice
,
810 foreach ($this->_columns
as $column) {
811 $data['columns'][] = $column->getCompareData();
818 * Function to check over array of indexes and look for common problems
820 * @param string $table table name
821 * @param string $schema schema name
823 * @return string Output HTML
826 static public function findDuplicates($table, $schema)
828 $indexes = Index
::getFromTable($table, $schema);
832 // count($indexes) < 2:
833 // there is no need to check if there less than two indexes
834 if (count($indexes) < 2) {
838 // remove last index from stack and ...
839 while ($while_index = array_pop($indexes)) {
840 // ... compare with every remaining index in stack
841 foreach ($indexes as $each_index) {
842 if ($each_index->getCompareData() !== $while_index->getCompareData()
847 // did not find any difference
848 // so it makes no sense to have this two equal indexes
850 $message = Message
::notice(
852 'The indexes %1$s and %2$s seem to be equal and one of them '
853 . 'could possibly be removed.'
856 $message->addParam($each_index->getName());
857 $message->addParam($while_index->getName());
858 $output .= $message->getDisplay();
860 // there is no need to check any further indexes if we have already
861 // found that this one has a duplicate