Translation update done using Pootle.
[phpmyadmin.git] / libraries / Index.class.php
blobe198bf6c553d105c468545600a9e7a8743cc0884
1 <?php
2 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 /**
4 * holds the database index class
6 * @package PhpMyAdmin
7 */
9 /**
10 * @since phpMyAdmin 3.0.0
12 * @package PhpMyAdmin
14 class PMA_Index
16 /**
17 * Class-wide storage container for indexes (caching, singleton)
19 * @var array
21 protected static $_registry = array();
23 /**
24 * @var string The name of the schema
26 protected $_schema = '';
28 /**
29 * @var string The name of the table
31 protected $_table = '';
33 /**
34 * @var string The name of the index
36 protected $_name = '';
38 /**
39 * Columns in index
41 * @var array
43 protected $_columns = array();
45 /**
46 * The index method used (BTREE, SPATIAL, FULLTEXT, HASH, RTREE).
48 * @var string
50 protected $_type = '';
52 /**
53 * The index choice (PRIMARY, UNIQUE, INDEX, SPATIAL, FULLTEXT)
55 * @var string
57 protected $_choice = '';
59 /**
60 * Various remarks.
62 * @var string
64 protected $_remarks = '';
66 /**
67 * Any comment provided for the index with a COMMENT attribute when the
68 * index was created.
70 * @var string
72 protected $_comment = '';
74 /**
75 * @var integer 0 if the index cannot contain duplicates, 1 if it can.
77 protected $_non_unique = 0;
79 /**
80 * Indicates how the key is packed. NULL if it is not.
82 * @var string
84 protected $_packed = null;
86 /**
87 * Constructor
89 * @param array $params
91 public function __construct($params = array())
93 $this->set($params);
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;
105 return $index;
106 } else {
107 return PMA_Index::$_registry[$schema][$table][$index_name];
112 * returns an array with all indexes from the given table
114 * @param string $table
115 * @param string $schema
116 * @return array
118 static public function getFromTable($table, $schema)
120 PMA_Index::_loadIndexes($table, $schema);
122 if (isset(PMA_Index::$_registry[$schema][$table])) {
123 return PMA_Index::$_registry[$schema][$table];
124 } else {
125 return array();
130 * return primary if set, false otherwise
132 * @param string $table
133 * @param string $schema
134 * @return mixed primary index or false if no one exists
136 static public function getPrimary($table, $schema)
138 PMA_Index::_loadIndexes($table, $schema);
140 if (isset(PMA_Index::$_registry[$schema][$table]['PRIMARY'])) {
141 return PMA_Index::$_registry[$schema][$table]['PRIMARY'];
142 } else {
143 return false;
148 * Load index data for table
150 * @param string $table
151 * @param string $schema
152 * @return boolean
154 static protected function _loadIndexes($table, $schema)
156 if (isset(PMA_Index::$_registry[$schema][$table])) {
157 return true;
160 $_raw_indexes = PMA_DBI_get_table_indexes($schema, $table);
161 foreach ($_raw_indexes as $_each_index) {
162 $_each_index['Schema'] = $schema;
163 if (! isset(PMA_Index::$_registry[$schema][$table][$_each_index['Key_name']])) {
164 $key = new PMA_Index($_each_index);
165 PMA_Index::$_registry[$schema][$table][$_each_index['Key_name']] = $key;
166 } else {
167 $key = PMA_Index::$_registry[$schema][$table][$_each_index['Key_name']];
170 $key->addColumn($_each_index);
173 return true;
177 * Add column to index
179 * @param array $params column params
181 public function addColumn($params)
183 if (strlen($params['Column_name'])) {
184 $this->_columns[$params['Column_name']] = new PMA_Index_Column($params);
188 public function addColumns($columns)
190 $_columns = array();
192 if (isset($columns['names'])) {
193 // coming from form
194 // $columns[names][]
195 // $columns[sub_parts][]
196 foreach ($columns['names'] as $key => $name) {
197 $sub_part = isset($columns['sub_parts'][$key]) ? $columns['sub_parts'][$key] : '';
198 $_columns[] = array(
199 'Column_name' => $name,
200 'Sub_part' => $sub_part,
203 } else {
204 // coming from SHOW INDEXES
205 // $columns[][name]
206 // $columns[][sub_part]
207 // ...
208 $_columns = $columns;
211 foreach ($_columns as $column) {
212 $this->addColumn($column);
217 * Returns true if $column indexed in this index
219 * @param string $column
220 * @return boolean
222 public function hasColumn($column)
224 return isset($this->_columns[$column]);
227 public function set($params)
229 if (isset($params['columns'])) {
230 $this->addColumns($params['columns']);
232 if (isset($params['Schema'])) {
233 $this->_schema = $params['Schema'];
235 if (isset($params['Table'])) {
236 $this->_table = $params['Table'];
238 if (isset($params['Key_name'])) {
239 $this->_name = $params['Key_name'];
241 if (isset($params['Index_type'])) {
242 $this->_type = $params['Index_type'];
244 if (isset($params['Comment'])) {
245 $this->_remarks = $params['Comment'];
247 if (isset($params['Index_comment'])) {
248 $this->_comment = $params['Index_comment'];
250 if (isset($params['Non_unique'])) {
251 $this->_non_unique = $params['Non_unique'];
253 if (isset($params['Packed'])) {
254 $this->_packed = $params['Packed'];
256 if ('PRIMARY' == $this->_name) {
257 $this->_choice = 'PRIMARY';
258 } elseif ('FULLTEXT' == $this->_type) {
259 $this->_choice = 'FULLTEXT';
260 } elseif ('SPATIAL' == $this->_type) {
261 $this->_choice = 'SPATIAL';
262 } elseif ('0' == $this->_non_unique) {
263 $this->_choice = 'UNIQUE';
264 } else {
265 $this->_choice = 'INDEX';
269 public function getColumnCount()
271 return count($this->_columns);
274 public function getComment()
276 return $this->_comment;
279 public function getRemarks()
281 return $this->_remarks;
284 public function getComments()
286 $comments = $this->getRemarks();
287 if (strlen($comments)) {
288 $comments .= "\n";
290 $comments .= $this->getComment();
292 return $comments;
295 public function getType()
297 return $this->_type;
300 public function getChoice()
302 return $this->_choice;
306 * Return a list of all index choices
308 * @return array index choices
310 static public function getIndexChoices()
312 return array(
313 'PRIMARY',
314 'INDEX',
315 'UNIQUE',
316 'SPATIAL',
317 'FULLTEXT',
321 public function generateIndexSelector()
323 $html_options = '';
325 foreach (PMA_Index::getIndexChoices() as $each_index_choice) {
326 if ($each_index_choice === 'PRIMARY'
327 && $this->_choice !== 'PRIMARY'
328 && PMA_Index::getPrimary($this->_table, $this->_schema)) {
329 // skip PRIMARY if there is already one in the table
330 continue;
332 $html_options .= '<option value="' . $each_index_choice . '"'
333 . (($this->_choice == $each_index_choice) ? ' selected="selected"' : '')
334 . '>'. $each_index_choice . '</option>' . "\n";
337 return $html_options;
340 public function getPacked()
342 return $this->_packed;
345 public function isPacked($as_text = false)
347 if ($as_text) {
348 $r = array(
349 '0' => __('No'),
350 '1' => __('Yes'),
352 } else {
353 $r = array(
354 '0' => false,
355 '1' => true,
359 if (null === $this->_packed) {
360 return $r[0];
363 return $this->_packed;
366 public function getNonUnique()
368 return $this->_non_unique;
371 public function isUnique($as_text = false)
373 if ($as_text) {
374 $r = array(
375 '0' => __('Yes'),
376 '1' => __('No'),
378 } else {
379 $r = array(
380 '0' => true,
381 '1' => false,
385 return $r[$this->_non_unique];
388 public function getName()
390 return $this->_name;
393 public function setName($name)
395 $this->_name = (string) $name;
398 public function getColumns()
400 return $this->_columns;
404 * Show index data
406 * @param string $table The tablename
407 * @param array $indexes_info Referenced info array
408 * @param array $indexes_data Referenced data array
409 * @param boolean $print_mode
410 * @access public
411 * @return array Index collection array
413 static public function getView($table, $schema, $print_mode = false)
415 $indexes = PMA_Index::getFromTable($table, $schema);
417 $no_indexes_class = count($indexes) > 0 ? ' hide' : '';
418 $no_indexes = "<div class='no_indexes_defined$no_indexes_class'>";
419 $no_indexes .= PMA_Message::notice(__('No index defined!'))->getDisplay();
420 $no_indexes .= '</div>';
422 $r = '<fieldset>';
423 $r .= '<legend id="index_header">' . __('Indexes');
424 $r .= PMA_showMySQLDocu('optimization', 'optimizing-database-structure');
425 $r .= '</legend>';
426 $r .= $no_indexes;
427 if (count($indexes) < 1) {
428 $r .= '</fieldset>';
429 return $r;
431 if (! $print_mode) {
432 $r .= PMA_Index::findDuplicates($table, $schema);
434 $r .= '<table id="table_index">';
435 $r .= '<thead>';
436 $r .= '<tr>';
437 if (! $print_mode) {
438 $r .= '<th colspan="2">' . __('Action') . '</th>';
440 $r .= '<th>' . __('Keyname') . '</th>';
441 $r .= '<th>' . __('Type') . '</th>';
442 $r .= '<th>' . __('Unique') . '</th>';
443 $r .= '<th>' . __('Packed') . '</th>';
444 $r .= '<th>' . __('Column') . '</th>';
445 $r .= '<th>' . __('Cardinality') . '</th>';
446 $r .= '<th>' . __('Collation') . '</th>';
447 $r .= '<th>' . __('Null') . '</th>';
448 $r .= '<th>' . __('Comment') . '</th>';
449 $r .= '</tr>';
450 $r .= '</thead>';
451 $r .= '<tbody>';
453 $odd_row = true;
454 foreach ($indexes as $index) {
455 $row_span = ' rowspan="' . $index->getColumnCount() . '" ';
457 $r .= '<tr class="noclick ' . ($odd_row ? 'odd' : 'even') . '">';
459 if (! $print_mode) {
460 $this_params = $GLOBALS['url_params'];
461 $this_params['index'] = $index->getName();
462 $r .= '<td class="edit_index ';
463 if ($GLOBALS['cfg']['AjaxEnable']) {
464 $r .= 'ajax" ';
466 $r .= '" ' . $row_span . '>'
467 . ' <a href="tbl_indexes.php' . PMA_generate_common_url($this_params)
468 . '">' . PMA_getIcon('b_edit.png', __('Edit')) . '</a>'
469 . '</td>' . "\n";
471 $this_params = $GLOBALS['url_params'];
472 if ($index->getName() == 'PRIMARY') {
473 $this_params['sql_query'] = 'ALTER TABLE ' . PMA_backquote($table) . ' DROP PRIMARY KEY';
474 $this_params['message_to_show'] = __('The primary key has been dropped');
475 $js_msg = PMA_jsFormat('ALTER TABLE ' . $table . ' DROP PRIMARY KEY');
476 } else {
477 $this_params['sql_query'] = 'ALTER TABLE ' . PMA_backquote($table) . ' DROP INDEX ' . PMA_backquote($index->getName());
478 $this_params['message_to_show'] = sprintf(__('Index %s has been dropped'), $index->getName());
479 $js_msg = PMA_jsFormat('ALTER TABLE ' . $table . ' DROP INDEX ' . $index->getName());
482 $r .= '<td ' . $row_span . '>';
483 $r .= '<input type="hidden" class="drop_primary_key_index_msg" value="' . $js_msg . '" />';
484 $r .= ' <a ';
485 if ($GLOBALS['cfg']['AjaxEnable']) {
486 $r .= 'class="drop_primary_key_index_anchor" ';
488 $r .= ' href="sql.php' . PMA_generate_common_url($this_params)
489 . '" >'
490 . PMA_getIcon('b_drop.png', __('Drop')) . '</a>'
491 . '</td>' . "\n";
494 $r .= '<th ' . $row_span . '>' . htmlspecialchars($index->getName()) . '</th>';
495 $r .= '<td ' . $row_span . '>' . htmlspecialchars($index->getType()) . '</td>';
496 $r .= '<td ' . $row_span . '>' . $index->isUnique(true) . '</td>';
497 $r .= '<td ' . $row_span . '>' . $index->isPacked(true) . '</td>';
499 foreach ($index->getColumns() as $column) {
500 if ($column->getSeqInIndex() > 1) {
501 $r .= '<tr class="noclick ' . ($odd_row ? 'odd' : 'even') . '">';
503 $r .= '<td>' . htmlspecialchars($column->getName());
504 if ($column->getSubPart()) {
505 $r .= ' (' . $column->getSubPart() . ')';
507 $r .= '</td>';
508 $r .= '<td>' . htmlspecialchars($column->getCardinality()) . '</td>';
509 $r .= '<td>' . htmlspecialchars($column->getCollation()) . '</td>';
510 $r .= '<td>' . htmlspecialchars($column->getNull(true)) . '</td>';
512 if ($column->getSeqInIndex() == 1) {
513 $r .= '<td ' . $row_span . '>'
514 . htmlspecialchars($index->getComments()) . '</td>';
516 $r .= '</tr>';
517 } // end foreach $index['Sequences']
519 $odd_row = ! $odd_row;
520 } // end while
521 $r .= '</tbody>';
522 $r .= '</table>';
523 $r .= '</fieldset>';
525 return $r;
528 public function getCompareData()
530 $data = array(
531 // 'Non_unique' => $this->_non_unique,
532 'Packed' => $this->_packed,
533 'Index_type' => $this->_type,
536 foreach ($this->_columns as $column) {
537 $data['columns'][] = $column->getCompareData();
540 return $data;
544 * Function to check over array of indexes and look for common problems
546 * @access public
547 * @param string name of table
548 * @return string Output HTML
550 static public function findDuplicates($table, $schema)
552 $indexes = PMA_Index::getFromTable($table, $schema);
554 $output = '';
556 // count($indexes) < 2:
557 // there is no need to check if there less than two indexes
558 if (count($indexes) < 2) {
559 return $output;
562 // remove last index from stack and ...
563 while ($while_index = array_pop($indexes)) {
564 // ... compare with every remaining index in stack
565 foreach ($indexes as $each_index) {
566 if ($each_index->getCompareData() !== $while_index->getCompareData()) {
567 continue;
570 // did not find any difference
571 // so it makes no sense to have this two equal indexes
573 $message = PMA_Message::notice(__('The indexes %1$s and %2$s seem to be equal and one of them could possibly be removed.'));
574 $message->addParam($each_index->getName());
575 $message->addParam($while_index->getName());
576 $output .= $message->getDisplay();
578 // there is no need to check any further indexes if we have already
579 // found that this one has a duplicate
580 continue 2;
583 return $output;
588 * @package PhpMyAdmin
590 class PMA_Index_Column
593 * @var string The column name
595 protected $_name = '';
598 * @var integer The column sequence number in the index, starting with 1.
600 protected $_seq_in_index = 1;
603 * @var string How the column is sorted in the index. “A” (Ascending) or NULL (Not sorted)
605 protected $_collation = null;
608 * The number of indexed characters if the column is only partly indexed,
609 * NULL if the entire column is indexed.
611 * @var integer
613 protected $_sub_part = null;
616 * Contains YES if the column may contain NULL.
617 * If not, the column contains NO.
619 * @var string
621 protected $_null = '';
624 * An estimate of the number of unique values in the index. This is updated
625 * by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on
626 * statistics stored as integers, so the value is not necessarily exact even
627 * for small tables. The higher the cardinality, the greater the chance that
628 * MySQL uses the index when doing joins.
630 * @var integer
632 protected $_cardinality = null;
634 public function __construct($params = array())
636 $this->set($params);
639 public function set($params)
641 if (isset($params['Column_name'])) {
642 $this->_name = $params['Column_name'];
644 if (isset($params['Seq_in_index'])) {
645 $this->_seq_in_index = $params['Seq_in_index'];
647 if (isset($params['Collation'])) {
648 $this->_collation = $params['Collation'];
650 if (isset($params['Cardinality'])) {
651 $this->_cardinality = $params['Cardinality'];
653 if (isset($params['Sub_part'])) {
654 $this->_sub_part = $params['Sub_part'];
656 if (isset($params['Null'])) {
657 $this->_null = $params['Null'];
661 public function getName()
663 return $this->_name;
666 public function getCollation()
668 return $this->_collation;
671 public function getCardinality()
673 return $this->_cardinality;
676 public function getNull($as_text = false)
678 return $as_text
679 ? (!$this->_null || $this->_null == 'NO' ? __('No') : __('Yes'))
680 : $this->_null;
683 public function getSeqInIndex()
685 return $this->_seq_in_index;
688 public function getSubPart()
690 return $this->_sub_part;
693 public function getCompareData()
695 return array(
696 'Column_name' => $this->_name,
697 'Seq_in_index' => $this->_seq_in_index,
698 'Collation' => $this->_collation,
699 'Sub_part' => $this->_sub_part,
700 'Null' => $this->_null,