2 /* vim: set expandtab sw=4 ts=4 sts=4: */
4 * holds the datasbe index class
10 * @since phpMyAdmin 3.0.0
16 * Class-wide storage container for indexes (caching, singleton)
20 protected static $_registry = array();
23 * @var string The name of the schema
25 protected $_schema = '';
28 * @var string The name of the table
30 protected $_table = '';
33 * @var string The name of the index
35 protected $_name = '';
42 protected $_columns = array();
45 * The index method used (BTREE, FULLTEXT, HASH, RTREE).
49 protected $_type = '';
52 * The index choice (PRIMARY, UNIQUE, INDEX, FULLTEXT)
56 protected $_choice = '';
63 protected $_remarks = '';
66 * Any comment provided for the index with a COMMENT attribute when the
71 protected $_comment = '';
74 * @var integer 0 if the index cannot contain duplicates, 1 if it can.
76 protected $_non_unique = 0;
79 * Indicates how the key is packed. NULL if it is not.
83 protected $_packed = null;
89 * @param array $params
91 public function __construct($params = array())
96 static public function singleton($schema, $table, $index_name = '')
98 PMA_Index
::_loadIndexes($table, $schema);
99 if (! isset(PMA_Index
::$_registry[$schema][$table][$index_name])) {
100 $index = new PMA_Index
;
101 if (strlen($index_name)) {
102 $index->setName($index_name);
103 PMA_Index
::$_registry[$schema][$table][$index->getName()] = $index;
107 return PMA_Index
::$_registry[$schema][$table][$index_name];
112 * returns an array with all indexes from the given table
114 * @uses PMA_Index::_loadIndexes()
115 * @uses PMA_Index::$_registry
116 * @param string $table
117 * @param string $schema
120 static public function getFromTable($table, $schema)
122 PMA_Index
::_loadIndexes($table, $schema);
124 if (isset(PMA_Index
::$_registry[$schema][$table])) {
125 return PMA_Index
::$_registry[$schema][$table];
132 * return primary if set, false otherwise
134 * @uses PMA_Index::_loadIndexes()
135 * @uses PMA_Index::$_registry
136 * @param string $table
137 * @param string $schema
138 * @return mixed primary index or false if no one exists
140 static public function getPrimary($table, $schema)
142 PMA_Index
::_loadIndexes($table, $schema);
144 if (isset(PMA_Index
::$_registry[$schema][$table]['PRIMARY'])) {
145 return PMA_Index
::$_registry[$schema][$table]['PRIMARY'];
152 * Load index data for table
154 * @uses PMA_Index::$_registry
155 * @uses PMA_DBI_fetch_result()
156 * @uses PMA_backquote()
158 * @uses PMA_Index->addColumn()
159 * @param string $table
160 * @param string $schema
163 static protected function _loadIndexes($table, $schema)
165 if (isset(PMA_Index
::$_registry[$schema][$table])) {
169 $_raw_indexes = PMA_DBI_fetch_result('SHOW INDEX FROM ' . PMA_backquote($schema) . '.' . PMA_backquote($table));
170 foreach ($_raw_indexes as $_each_index) {
171 $_each_index['Schema'] = $schema;
172 if (! isset(PMA_Index
::$_registry[$schema][$table][$_each_index['Key_name']])) {
173 $key = new PMA_Index($_each_index);
174 PMA_Index
::$_registry[$schema][$table][$_each_index['Key_name']] = $key;
176 $key = PMA_Index
::$_registry[$schema][$table][$_each_index['Key_name']];
179 $key->addColumn($_each_index);
186 * Add column to index
188 * @uses $this->_columns
189 * @uses PMA_Index_Column
190 * @param array $params column params
192 public function addColumn($params)
194 if (strlen($params['Column_name'])) {
195 $this->_columns
[$params['Column_name']] = new PMA_Index_Column($params);
199 public function addColumns($columns)
203 if (isset($columns['names'])) {
206 // $columns[sub_parts][]
207 foreach ($columns['names'] as $key => $name) {
209 'Column_name' => $name,
210 'Sub_part' => $columns['sub_parts'][$key],
214 // coming from SHOW INDEXES
216 // $columns[][sub_part]
218 $_columns = $columns;
221 foreach ($_columns as $column) {
222 $this->addColumn($column);
227 * Returns true if $column indexed in this index
229 * @uses $this->_columns
230 * @param string $column
233 public function hasColumn($column)
235 return isset($this->_columns
[$column]);
238 public function set($params)
240 if (isset($params['columns'])) {
241 $this->addColumns($params['columns']);
243 if (isset($params['Schema'])) {
244 $this->_schema
= $params['Schema'];
246 if (isset($params['Table'])) {
247 $this->_table
= $params['Table'];
249 if (isset($params['Key_name'])) {
250 $this->_name
= $params['Key_name'];
252 if (isset($params['Index_type'])) {
253 $this->_type
= $params['Index_type'];
255 if (isset($params['Comment'])) {
256 $this->_remarks
= $params['Comment'];
258 if (isset($params['Index_comment'])) {
259 $this->_comment
= $params['Index_comment'];
261 if (isset($params['Non_unique'])) {
262 $this->_non_unique
= $params['Non_unique'];
264 if (isset($params['Packed'])) {
265 $this->_packed
= $params['Packed'];
267 if ('PRIMARY' == $this->_name
) {
268 $this->_choice
= 'PRIMARY';
269 } elseif ('FULLTEXT' == $this->_type
) {
270 $this->_choice
= 'FULLTEXT';
271 } elseif ('0' == $this->_non_unique
) {
272 $this->_choice
= 'UNIQUE';
274 $this->_choice
= 'INDEX';
278 public function getColumnCount()
280 return count($this->_columns
);
283 public function getComment()
285 return $this->_comment
;
288 public function getRemarks()
290 return $this->_remarks
;
293 public function getComments()
295 $comments = $this->getRemarks();
296 if (strlen($comments)) {
299 $comments .= $this->getComment();
304 public function getType()
310 * Return a list of all index choices
312 * @return array index choices
314 static public function getIndexChoices()
324 public function generateIndexSelector()
328 foreach (PMA_Index
::getIndexChoices() as $each_index_choice) {
329 if ($each_index_choice === 'PRIMARY'
330 && $this->_choice
!== 'PRIMARY'
331 && PMA_Index
::getPrimary($this->_table
, $this->_schema
)) {
332 // skip PRIMARY if there is already one in the table
335 $html_options .= '<option value="' . $each_index_choice . '"'
336 . (($this->_choice
== $each_index_choice) ?
' selected="selected"' : '')
337 . '>'. $each_index_choice . '</option>' . "\n";
340 return $html_options;
343 public function getPacked()
345 return $this->_packed
;
348 public function isPacked($as_text = false)
352 '0' => $GLOBALS['strNo'],
353 '1' => $GLOBALS['strYes'],
362 if (null === $this->_packed
) {
366 return $this->_packed
;
369 public function getNonUnique()
371 return $this->_non_unique
;
374 public function isUnique($as_text = false)
378 '0' => $GLOBALS['strYes'],
379 '1' => $GLOBALS['strNo'],
388 return $r[$this->_non_unique
];
391 public function getName()
396 public function setName($name)
398 $this->_name
= (string) $name;
401 public function getColumns()
403 return $this->_columns
;
409 * @param string $table The tablename
410 * @param array $indexes_info Referenced info array
411 * @param array $indexes_data Referenced data array
412 * @param boolean $print_mode
414 * @return array Index collection array
415 * @author Garvin Hicking (pma@supergarv.de)
417 static public function getView($table, $schema, $print_mode = false)
419 $indexes = PMA_Index
::getFromTable($table, $schema);
421 if (count($indexes) < 1) {
422 return PMA_Message
::warning('strNoIndex')->getDisplay();
427 $r .= '<h2>' . $GLOBALS['strIndexes'] . ': ';
428 $r .= PMA_showMySQLDocu('optimization', 'optimizing-database-structure');
434 $r .= '<th colspan="2">' . $GLOBALS['strAction'] . '</th>';
436 $r .= '<th>' . $GLOBALS['strKeyname'] . '</th>';
437 $r .= '<th>' . $GLOBALS['strType'] . '</th>';
438 $r .= '<th>' . $GLOBALS['strUnique'] . '</th>';
439 $r .= '<th>' . $GLOBALS['strPacked'] . '</th>';
440 $r .= '<th>' . $GLOBALS['strField'] . '</th>';
441 $r .= '<th>' . $GLOBALS['strCardinality'] . '</th>';
442 $r .= '<th>' . $GLOBALS['strCollation'] . '</th>';
443 $r .= '<th>' . $GLOBALS['strNull'] . '</th>';
444 $r .= '<th>' . $GLOBALS['strComment'] . '</th>';
450 foreach ($indexes as $index) {
451 $row_span = ' rowspan="' . $index->getColumnCount() . '" ';
453 $r .= '<tr class="' . ($odd_row ?
'odd' : 'even') . '">';
456 $this_params = $GLOBALS['url_params'];
457 $this_params['index'] = $index->getName();
458 $r .= '<td ' . $row_span . '>'
459 . ' <a href="tbl_indexes.php' . PMA_generate_common_url($this_params)
460 . '">' . PMA_getIcon('b_edit.png', $GLOBALS['strEdit']) . '</a>'
463 $this_params = $GLOBALS['url_params'];
464 if ($index->getName() == 'PRIMARY') {
465 $this_params['sql_query'] = 'ALTER TABLE ' . PMA_backquote($table) . ' DROP PRIMARY KEY';
466 $this_params['zero_rows'] = $GLOBALS['strPrimaryKeyHasBeenDropped'];
467 $js_msg = PMA_jsFormat('ALTER TABLE ' . $table . ' DROP PRIMARY KEY');
469 $this_params['sql_query'] = 'ALTER TABLE ' . PMA_backquote($table) . ' DROP INDEX ' . PMA_backquote($index->getName());
470 $this_params['zero_rows'] = sprintf($GLOBALS['strIndexHasBeenDropped'], $index->getName());
471 $js_msg = PMA_jsFormat('ALTER TABLE ' . $table . ' DROP INDEX ' . $index->getName());
474 $r .= '<td ' . $row_span . '>'
475 . ' <a href="sql.php' . PMA_generate_common_url($this_params)
476 . '" onclick="return confirmLink(this, \'' . $js_msg . '\')">'
477 . PMA_getIcon('b_drop.png', $GLOBALS['strDrop']) . '</a>'
481 $r .= '<th ' . $row_span . '>' . htmlspecialchars($index->getName()) . '</th>';
482 $r .= '<td ' . $row_span . '>' . htmlspecialchars($index->getType()) . '</td>';
483 $r .= '<td ' . $row_span . '>' . $index->isUnique(true) . '</td>';
484 $r .= '<td ' . $row_span . '>' . $index->isPacked(true) . '</td>';
486 foreach ($index->getColumns() as $column) {
487 if ($column->getSeqInIndex() > 1) {
488 $r .= '<tr class="' . ($odd_row ?
'odd' : 'even') . '">';
490 $r .= '<td>' . htmlspecialchars($column->getName());
491 if ($column->getSubPart()) {
492 $r .= ' (' . $column->getSubPart() . ')';
495 $r .= '<td>' . htmlspecialchars($column->getCardinality()) . '</td>';
496 $r .= '<td>' . htmlspecialchars($column->getCollation()) . '</td>';
497 $r .= '<td>' . htmlspecialchars($column->getNull()) . '</td>';
499 if ($column->getSeqInIndex() == 1) {
500 $r .= '<td ' . $row_span . '>'
501 . htmlspecialchars($index->getComments()) . '</td>';
504 } // end foreach $index['Sequences']
506 $odd_row = ! $odd_row;
512 $r .= PMA_Index
::findDuplicates($table, $schema);
518 public function getCompareData()
521 // 'Non_unique' => $this->_non_unique,
522 'Packed' => $this->_packed
,
523 'Index_type' => $this->_type
,
526 foreach ($this->_columns
as $column) {
527 $data['columns'][] = $column->getCompareData();
534 * Function to check over array of indexes and look for common problems
536 * @uses $GLOBALS['strIndexesSeemEqual']
544 * @param string name of table
545 * @return string Output HTML
547 static public function findDuplicates($table, $schema)
549 $indexes = PMA_Index
::getFromTable($table, $schema);
553 // count($indexes) < 2:
554 // there is no need to check if there less than two indexes
555 if (count($indexes) < 2) {
559 // remove last index from stack and ...
560 while ($while_index = array_pop($indexes)) {
561 // ... compare with every remaining index in stack
562 foreach ($indexes as $each_index) {
563 if ($each_index->getCompareData() !== $while_index->getCompareData()) {
567 // did not find any difference
568 // so it makes no sense to have this two equal indexes
570 $message = PMA_Message
::warning('strIndexesSeemEqual');
571 $message->addParam($each_index->getName());
572 $message->addParam($while_index->getName());
573 $output .= $message->getDisplay();
575 // there is no need to check any further indexes if we have already
576 // found that this one has a duplicate
584 class PMA_Index_Column
587 * @var string The column name
589 protected $_name = '';
592 * @var integer The column sequence number in the index, starting with 1.
594 protected $_seq_in_index = 1;
597 * @var string How the column is sorted in the index. “A” (Ascending) or NULL (Not sorted)
599 protected $_collation = null;
602 * The number of indexed characters if the column is only partly indexed,
603 * NULL if the entire column is indexed.
607 protected $_sub_part = null;
610 * Contains YES if the column may contain NULL.
611 * If not, the column contains NO.
615 protected $_null = '';
618 * An estimate of the number of unique values in the index. This is updated
619 * by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on
620 * statistics stored as integers, so the value is not necessarily exact even
621 * for small tables. The higher the cardinality, the greater the chance that
622 * MySQL uses the index when doing joins.
626 protected $_cardinality = 0;
628 public function __construct($params = array())
633 public function set($params)
635 if (isset($params['Column_name'])) {
636 $this->_name
= $params['Column_name'];
638 if (isset($params['Seq_in_index'])) {
639 $this->_seq_in_index
= $params['Seq_in_index'];
641 if (isset($params['Collation'])) {
642 $this->_collation
= $params['Collation'];
644 if (isset($params['Cardinality'])) {
645 $this->_cardinality
= $params['Cardinality'];
647 if (isset($params['Sub_part'])) {
648 $this->_sub_part
= $params['Sub_part'];
650 if (isset($params['Null'])) {
651 $this->_null
= $params['Null'];
655 public function getName()
660 public function getCollation()
662 return $this->_collation
;
665 public function getCardinality()
667 return $this->_cardinality
;
670 public function getNull()
675 public function getSeqInIndex()
677 return $this->_seq_in_index
;
680 public function getSubPart()
682 return $this->_sub_part
;
685 public function getCompareData()
688 'Column_name' => $this->_name
,
689 'Seq_in_index' => $this->_seq_in_index
,
690 'Collation' => $this->_collation
,
691 'Sub_part' => $this->_sub_part
,
692 'Null' => $this->_null
,