Merge branch 'QA_3_3'
[phpmyadmin/dkf.git] / libraries / Index.class.php
blob6e0d1c64dbd0e40437a628cd1fed36ceb5b0e5fd
1 <?php
2 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 /**
4 * holds the database index class
6 * @version $Id$
7 * @package phpMyAdmin
8 */
10 /**
11 * @since phpMyAdmin 3.0.0
13 * @package phpMyAdmin
15 class PMA_Index
17 /**
18 * Class-wide storage container for indexes (caching, singleton)
20 * @var array
22 protected static $_registry = array();
24 /**
25 * @var string The name of the schema
27 protected $_schema = '';
29 /**
30 * @var string The name of the table
32 protected $_table = '';
34 /**
35 * @var string The name of the index
37 protected $_name = '';
39 /**
40 * Columns in index
42 * @var array
44 protected $_columns = array();
46 /**
47 * The index method used (BTREE, FULLTEXT, HASH, RTREE).
49 * @var string
51 protected $_type = '';
53 /**
54 * The index choice (PRIMARY, UNIQUE, INDEX, FULLTEXT)
56 * @var string
58 protected $_choice = '';
60 /**
61 * Various remarks.
63 * @var string
65 protected $_remarks = '';
67 /**
68 * Any comment provided for the index with a COMMENT attribute when the
69 * index was created.
71 * @var string
73 protected $_comment = '';
75 /**
76 * @var integer 0 if the index cannot contain duplicates, 1 if it can.
78 protected $_non_unique = 0;
80 /**
81 * Indicates how the key is packed. NULL if it is not.
83 * @var string
85 protected $_packed = null;
87 /**
88 * Constructor
90 * @uses $this->set()
91 * @param array $params
93 public function __construct($params = array())
95 $this->set($params);
98 static public function singleton($schema, $table, $index_name = '')
100 PMA_Index::_loadIndexes($table, $schema);
101 if (! isset(PMA_Index::$_registry[$schema][$table][$index_name])) {
102 $index = new PMA_Index;
103 if (strlen($index_name)) {
104 $index->setName($index_name);
105 PMA_Index::$_registry[$schema][$table][$index->getName()] = $index;
107 return $index;
108 } else {
109 return PMA_Index::$_registry[$schema][$table][$index_name];
114 * returns an array with all indexes from the given table
116 * @uses PMA_Index::_loadIndexes()
117 * @uses PMA_Index::$_registry
118 * @param string $table
119 * @param string $schema
120 * @return array
122 static public function getFromTable($table, $schema)
124 PMA_Index::_loadIndexes($table, $schema);
126 if (isset(PMA_Index::$_registry[$schema][$table])) {
127 return PMA_Index::$_registry[$schema][$table];
128 } else {
129 return array();
134 * return primary if set, false otherwise
136 * @uses PMA_Index::_loadIndexes()
137 * @uses PMA_Index::$_registry
138 * @param string $table
139 * @param string $schema
140 * @return mixed primary index or false if no one exists
142 static public function getPrimary($table, $schema)
144 PMA_Index::_loadIndexes($table, $schema);
146 if (isset(PMA_Index::$_registry[$schema][$table]['PRIMARY'])) {
147 return PMA_Index::$_registry[$schema][$table]['PRIMARY'];
148 } else {
149 return false;
154 * Load index data for table
156 * @uses PMA_Index::$_registry
157 * @uses PMA_DBI_fetch_result()
158 * @uses PMA_backquote()
159 * @uses PMA_Index
160 * @uses PMA_Index->addColumn()
161 * @param string $table
162 * @param string $schema
163 * @return boolean
165 static protected function _loadIndexes($table, $schema)
167 if (isset(PMA_Index::$_registry[$schema][$table])) {
168 return true;
171 $_raw_indexes = PMA_DBI_fetch_result('SHOW INDEX FROM ' . PMA_backquote($schema) . '.' . PMA_backquote($table));
172 foreach ($_raw_indexes as $_each_index) {
173 $_each_index['Schema'] = $schema;
174 if (! isset(PMA_Index::$_registry[$schema][$table][$_each_index['Key_name']])) {
175 $key = new PMA_Index($_each_index);
176 PMA_Index::$_registry[$schema][$table][$_each_index['Key_name']] = $key;
177 } else {
178 $key = PMA_Index::$_registry[$schema][$table][$_each_index['Key_name']];
181 $key->addColumn($_each_index);
184 return true;
188 * Add column to index
190 * @uses $this->_columns
191 * @uses PMA_Index_Column
192 * @param array $params column params
194 public function addColumn($params)
196 if (strlen($params['Column_name'])) {
197 $this->_columns[$params['Column_name']] = new PMA_Index_Column($params);
201 public function addColumns($columns)
203 $_columns = array();
205 if (isset($columns['names'])) {
206 // coming from form
207 // $columns[names][]
208 // $columns[sub_parts][]
209 foreach ($columns['names'] as $key => $name) {
210 $_columns[] = array(
211 'Column_name' => $name,
212 'Sub_part' => $columns['sub_parts'][$key],
215 } else {
216 // coming from SHOW INDEXES
217 // $columns[][name]
218 // $columns[][sub_part]
219 // ...
220 $_columns = $columns;
223 foreach ($_columns as $column) {
224 $this->addColumn($column);
229 * Returns true if $column indexed in this index
231 * @uses $this->_columns
232 * @param string $column
233 * @return boolean
235 public function hasColumn($column)
237 return isset($this->_columns[$column]);
240 public function set($params)
242 if (isset($params['columns'])) {
243 $this->addColumns($params['columns']);
245 if (isset($params['Schema'])) {
246 $this->_schema = $params['Schema'];
248 if (isset($params['Table'])) {
249 $this->_table = $params['Table'];
251 if (isset($params['Key_name'])) {
252 $this->_name = $params['Key_name'];
254 if (isset($params['Index_type'])) {
255 $this->_type = $params['Index_type'];
257 if (isset($params['Comment'])) {
258 $this->_remarks = $params['Comment'];
260 if (isset($params['Index_comment'])) {
261 $this->_comment = $params['Index_comment'];
263 if (isset($params['Non_unique'])) {
264 $this->_non_unique = $params['Non_unique'];
266 if (isset($params['Packed'])) {
267 $this->_packed = $params['Packed'];
269 if ('PRIMARY' == $this->_name) {
270 $this->_choice = 'PRIMARY';
271 } elseif ('FULLTEXT' == $this->_type) {
272 $this->_choice = 'FULLTEXT';
273 } elseif ('0' == $this->_non_unique) {
274 $this->_choice = 'UNIQUE';
275 } else {
276 $this->_choice = 'INDEX';
280 public function getColumnCount()
282 return count($this->_columns);
285 public function getComment()
287 return $this->_comment;
290 public function getRemarks()
292 return $this->_remarks;
295 public function getComments()
297 $comments = $this->getRemarks();
298 if (strlen($comments)) {
299 $comments .= "\n";
301 $comments .= $this->getComment();
303 return $comments;
306 public function getType()
308 return $this->_type;
311 public function getChoice()
313 return $this->_choice;
317 * Return a list of all index choices
319 * @return array index choices
321 static public function getIndexChoices()
323 return array(
324 'PRIMARY',
325 'INDEX',
326 'UNIQUE',
327 'FULLTEXT',
331 public function generateIndexSelector()
333 $html_options = '';
335 foreach (PMA_Index::getIndexChoices() as $each_index_choice) {
336 if ($each_index_choice === 'PRIMARY'
337 && $this->_choice !== 'PRIMARY'
338 && PMA_Index::getPrimary($this->_table, $this->_schema)) {
339 // skip PRIMARY if there is already one in the table
340 continue;
342 $html_options .= '<option value="' . $each_index_choice . '"'
343 . (($this->_choice == $each_index_choice) ? ' selected="selected"' : '')
344 . '>'. $each_index_choice . '</option>' . "\n";
347 return $html_options;
350 public function getPacked()
352 return $this->_packed;
355 public function isPacked($as_text = false)
357 if ($as_text) {
358 $r = array(
359 '0' => $GLOBALS['strNo'],
360 '1' => $GLOBALS['strYes'],
362 } else {
363 $r = array(
364 '0' => false,
365 '1' => true,
369 if (null === $this->_packed) {
370 return $r[0];
373 return $this->_packed;
376 public function getNonUnique()
378 return $this->_non_unique;
381 public function isUnique($as_text = false)
383 if ($as_text) {
384 $r = array(
385 '0' => $GLOBALS['strYes'],
386 '1' => $GLOBALS['strNo'],
388 } else {
389 $r = array(
390 '0' => true,
391 '1' => false,
395 return $r[$this->_non_unique];
398 public function getName()
400 return $this->_name;
403 public function setName($name)
405 $this->_name = (string) $name;
408 public function getColumns()
410 return $this->_columns;
414 * Show index data
416 * @param string $table The tablename
417 * @param array $indexes_info Referenced info array
418 * @param array $indexes_data Referenced data array
419 * @param boolean $print_mode
420 * @access public
421 * @return array Index collection array
423 static public function getView($table, $schema, $print_mode = false)
425 $indexes = PMA_Index::getFromTable($table, $schema);
427 if (count($indexes) < 1) {
428 return PMA_Message::warning('strNoIndex')->getDisplay();
431 $r = '';
433 $r .= '<h2>' . $GLOBALS['strIndexes'] . ': ';
434 $r .= PMA_showMySQLDocu('optimization', 'optimizing-database-structure');
435 $r .= '</h2>';
436 $r .= '<table>';
437 $r .= '<thead>';
438 $r .= '<tr>';
439 if (! $print_mode) {
440 $r .= '<th colspan="2">' . $GLOBALS['strAction'] . '</th>';
442 $r .= '<th>' . $GLOBALS['strKeyname'] . '</th>';
443 $r .= '<th>' . $GLOBALS['strType'] . '</th>';
444 $r .= '<th>' . $GLOBALS['strUnique'] . '</th>';
445 $r .= '<th>' . $GLOBALS['strPacked'] . '</th>';
446 $r .= '<th>' . $GLOBALS['strField'] . '</th>';
447 $r .= '<th>' . $GLOBALS['strCardinality'] . '</th>';
448 $r .= '<th>' . $GLOBALS['strCollation'] . '</th>';
449 $r .= '<th>' . $GLOBALS['strNull'] . '</th>';
450 $r .= '<th>' . $GLOBALS['strComment'] . '</th>';
451 $r .= '</tr>';
452 $r .= '</thead>';
453 $r .= '<tbody>';
455 $odd_row = true;
456 foreach ($indexes as $index) {
457 $row_span = ' rowspan="' . $index->getColumnCount() . '" ';
459 $r .= '<tr class="' . ($odd_row ? 'odd' : 'even') . '">';
461 if (! $print_mode) {
462 $this_params = $GLOBALS['url_params'];
463 $this_params['index'] = $index->getName();
464 $r .= '<td ' . $row_span . '>'
465 . ' <a href="tbl_indexes.php' . PMA_generate_common_url($this_params)
466 . '">' . PMA_getIcon('b_edit.png', $GLOBALS['strEdit']) . '</a>'
467 . '</td>' . "\n";
469 $this_params = $GLOBALS['url_params'];
470 if ($index->getName() == 'PRIMARY') {
471 $this_params['sql_query'] = 'ALTER TABLE ' . PMA_backquote($table) . ' DROP PRIMARY KEY';
472 $this_params['zero_rows'] = $GLOBALS['strPrimaryKeyHasBeenDropped'];
473 $js_msg = PMA_jsFormat('ALTER TABLE ' . $table . ' DROP PRIMARY KEY');
474 } else {
475 $this_params['sql_query'] = 'ALTER TABLE ' . PMA_backquote($table) . ' DROP INDEX ' . PMA_backquote($index->getName());
476 $this_params['zero_rows'] = sprintf($GLOBALS['strIndexHasBeenDropped'], $index->getName());
477 $js_msg = PMA_jsFormat('ALTER TABLE ' . $table . ' DROP INDEX ' . $index->getName());
480 $r .= '<td ' . $row_span . '>'
481 . ' <a href="sql.php' . PMA_generate_common_url($this_params)
482 . '" onclick="return confirmLink(this, \'' . $js_msg . '\')">'
483 . PMA_getIcon('b_drop.png', $GLOBALS['strDrop']) . '</a>'
484 . '</td>' . "\n";
487 $r .= '<th ' . $row_span . '>' . htmlspecialchars($index->getName()) . '</th>';
488 $r .= '<td ' . $row_span . '>' . htmlspecialchars($index->getType()) . '</td>';
489 $r .= '<td ' . $row_span . '>' . $index->isUnique(true) . '</td>';
490 $r .= '<td ' . $row_span . '>' . $index->isPacked(true) . '</td>';
492 foreach ($index->getColumns() as $column) {
493 if ($column->getSeqInIndex() > 1) {
494 $r .= '<tr class="' . ($odd_row ? 'odd' : 'even') . '">';
496 $r .= '<td>' . htmlspecialchars($column->getName());
497 if ($column->getSubPart()) {
498 $r .= ' (' . $column->getSubPart() . ')';
500 $r .= '</td>';
501 $r .= '<td>' . htmlspecialchars($column->getCardinality()) . '</td>';
502 $r .= '<td>' . htmlspecialchars($column->getCollation()) . '</td>';
503 $r .= '<td>' . htmlspecialchars($column->getNull()) . '</td>';
505 if ($column->getSeqInIndex() == 1) {
506 $r .= '<td ' . $row_span . '>'
507 . htmlspecialchars($index->getComments()) . '</td>';
509 $r .= '</tr>';
510 } // end foreach $index['Sequences']
512 $odd_row = ! $odd_row;
513 } // end while
514 $r .= '</tbody>';
515 $r .= '</table>';
517 if (! $print_mode) {
518 $r .= PMA_Index::findDuplicates($table, $schema);
521 return $r;
524 public function getCompareData()
526 $data = array(
527 // 'Non_unique' => $this->_non_unique,
528 'Packed' => $this->_packed,
529 'Index_type' => $this->_type,
532 foreach ($this->_columns as $column) {
533 $data['columns'][] = $column->getCompareData();
536 return $data;
540 * Function to check over array of indexes and look for common problems
542 * @uses $GLOBALS['strIndexesSeemEqual']
543 * @uses is_string()
544 * @uses is_array()
545 * @uses count()
546 * @uses array_pop()
547 * @uses reset()
548 * @uses current()
549 * @access public
550 * @param string name of table
551 * @return string Output HTML
553 static public function findDuplicates($table, $schema)
555 $indexes = PMA_Index::getFromTable($table, $schema);
557 $output = '';
559 // count($indexes) < 2:
560 // there is no need to check if there less than two indexes
561 if (count($indexes) < 2) {
562 return $output;
565 // remove last index from stack and ...
566 while ($while_index = array_pop($indexes)) {
567 // ... compare with every remaining index in stack
568 foreach ($indexes as $each_index) {
569 if ($each_index->getCompareData() !== $while_index->getCompareData()) {
570 continue;
573 // did not find any difference
574 // so it makes no sense to have this two equal indexes
576 $message = PMA_Message::warning('strIndexesSeemEqual');
577 $message->addParam($each_index->getName());
578 $message->addParam($while_index->getName());
579 $output .= $message->getDisplay();
581 // there is no need to check any further indexes if we have already
582 // found that this one has a duplicate
583 continue 2;
586 return $output;
591 * @package phpMyAdmin
593 class PMA_Index_Column
596 * @var string The column name
598 protected $_name = '';
601 * @var integer The column sequence number in the index, starting with 1.
603 protected $_seq_in_index = 1;
606 * @var string How the column is sorted in the index. “A” (Ascending) or NULL (Not sorted)
608 protected $_collation = null;
611 * The number of indexed characters if the column is only partly indexed,
612 * NULL if the entire column is indexed.
614 * @var integer
616 protected $_sub_part = null;
619 * Contains YES if the column may contain NULL.
620 * If not, the column contains NO.
622 * @var string
624 protected $_null = '';
627 * An estimate of the number of unique values in the index. This is updated
628 * by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on
629 * statistics stored as integers, so the value is not necessarily exact even
630 * for small tables. The higher the cardinality, the greater the chance that
631 * MySQL uses the index when doing joins.
633 * @var integer
635 protected $_cardinality = 0;
637 public function __construct($params = array())
639 $this->set($params);
642 public function set($params)
644 if (isset($params['Column_name'])) {
645 $this->_name = $params['Column_name'];
647 if (isset($params['Seq_in_index'])) {
648 $this->_seq_in_index = $params['Seq_in_index'];
650 if (isset($params['Collation'])) {
651 $this->_collation = $params['Collation'];
653 if (isset($params['Cardinality'])) {
654 $this->_cardinality = $params['Cardinality'];
656 if (isset($params['Sub_part'])) {
657 $this->_sub_part = $params['Sub_part'];
659 if (isset($params['Null'])) {
660 $this->_null = $params['Null'];
664 public function getName()
666 return $this->_name;
669 public function getCollation()
671 return $this->_collation;
674 public function getCardinality()
676 return $this->_cardinality;
679 public function getNull()
681 return $this->_null;
684 public function getSeqInIndex()
686 return $this->_seq_in_index;
689 public function getSubPart()
691 return $this->_sub_part;
694 public function getCompareData()
696 return array(
697 'Column_name' => $this->_name,
698 'Seq_in_index' => $this->_seq_in_index,
699 'Collation' => $this->_collation,
700 'Sub_part' => $this->_sub_part,
701 'Null' => $this->_null,