libraries/Index.class.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * holds the database index class
   5  *
   6  * @package PhpMyAdmin
   7  */
   8
   9 /**
  10  * @since phpMyAdmin 3.0.0
  11  *
  12  * @package PhpMyAdmin
  13  */
  14 class PMA_Index
  15 {
  16     /**
  17      * Class-wide storage container for indexes (caching, singleton)
  18      *
  19      * @var array
  20      */
  21     protected static $_registry = array();
  22
  23     /**
  24      * @var string The name of the schema
  25      */
  26     protected $_schema = '';
  27
  28     /**
  29      * @var string The name of the table
  30      */
  31     protected $_table = '';
  32
  33     /**
  34      * @var string The name of the index
  35      */
  36     protected $_name = '';
  37
  38     /**
  39      * Columns in index
  40      *
  41      * @var array
  42      */
  43     protected $_columns = array();
  44
  45     /**
  46      * The index method used (BTREE, SPATIAL, FULLTEXT, HASH, RTREE).
  47      *
  48      * @var string
  49      */
  50     protected $_type = '';
  51
  52     /**
  53      * The index choice (PRIMARY, UNIQUE, INDEX, SPATIAL, FULLTEXT)
  54      *
  55      * @var string
  56      */
  57     protected $_choice = '';
  58
  59     /**
  60      * Various remarks.
  61      *
  62      * @var string
  63      */
  64     protected $_remarks = '';
  65
  66     /**
  67      * Any comment provided for the index with a COMMENT attribute when the
  68      * index was created.
  69      *
  70      * @var string
  71      */
  72     protected $_comment = '';
  73
  74     /**
  75      * @var integer 0 if the index cannot contain duplicates, 1 if it can.
  76      */
  77     protected $_non_unique = 0;
  78
  79     /**
  80      * Indicates how the key is packed. NULL if it is not.
  81      *
  82      * @var string
  83      */
  84     protected $_packed = null;
  85
  86     /**
  87      * Constructor
  88      *
  89      * @param array $params
  90      */
  91     public function __construct($params = array())
  92     {
  93         $this->set($params);
  94     }
  95
  96     static public function singleton($schema, $table, $index_name = '')
  97     {
  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;
 104             }
 105             return $index;
 106         } else {
 107             return PMA_Index::$_registry[$schema][$table][$index_name];
 108         }
 109     }
 110
 111     /**
 112      * returns an array with all indexes from the given table
 113      *
 114      * @param string $table
 115      * @param string $schema
 116      * @return  array
 117      */
 118     static public function getFromTable($table, $schema)
 119     {
 120         PMA_Index::_loadIndexes($table, $schema);
 121
 122         if (isset(PMA_Index::$_registry[$schema][$table])) {
 123             return PMA_Index::$_registry[$schema][$table];
 124         } else {
 125             return array();
 126         }
 127     }
 128
 129     /**
 130      * return primary if set, false otherwise
 131      *
 132      * @param string $table
 133      * @param string $schema
 134      * @return  mixed primary index or false if no one exists
 135      */
 136     static public function getPrimary($table, $schema)
 137     {
 138         PMA_Index::_loadIndexes($table, $schema);
 139
 140         if (isset(PMA_Index::$_registry[$schema][$table]['PRIMARY'])) {
 141             return PMA_Index::$_registry[$schema][$table]['PRIMARY'];
 142         } else {
 143             return false;
 144         }
 145     }
 146
 147     /**
 148      * Load index data for table
 149      *
 150      * @param string $table
 151      * @param string $schema
 152      * @return  boolean
 153      */
 154     static protected function _loadIndexes($table, $schema)
 155     {
 156         if (isset(PMA_Index::$_registry[$schema][$table])) {
 157             return true;
 158         }
 159
 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']];
 168             }
 169
 170             $key->addColumn($_each_index);
 171         }
 172
 173         return true;
 174     }
 175
 176     /**
 177      * Add column to index
 178      *
 179      * @param array $params column params
 180      */
 181     public function addColumn($params)
 182     {
 183         if (strlen($params['Column_name'])) {
 184             $this->_columns[$params['Column_name']] = new PMA_Index_Column($params);
 185         }
 186     }
 187
 188     public function addColumns($columns)
 189     {
 190         $_columns = array();
 191
 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,
 201                 );
 202             }
 203         } else {
 204             // coming from SHOW INDEXES
 205             // $columns[][name]
 206             // $columns[][sub_part]
 207             // ...
 208             $_columns = $columns;
 209         }
 210
 211         foreach ($_columns as $column) {
 212             $this->addColumn($column);
 213         }
 214     }
 215
 216     /**
 217      * Returns true if $column indexed in this index
 218      *
 219      * @param string $column
 220      * @return  boolean
 221      */
 222     public function hasColumn($column)
 223     {
 224         return isset($this->_columns[$column]);
 225     }
 226
 227     public function set($params)
 228     {
 229         if (isset($params['columns'])) {
 230             $this->addColumns($params['columns']);
 231         }
 232         if (isset($params['Schema'])) {
 233             $this->_schema = $params['Schema'];
 234         }
 235         if (isset($params['Table'])) {
 236             $this->_table = $params['Table'];
 237         }
 238         if (isset($params['Key_name'])) {
 239             $this->_name = $params['Key_name'];
 240         }
 241         if (isset($params['Index_type'])) {
 242             $this->_type = $params['Index_type'];
 243         }
 244         if (isset($params['Comment'])) {
 245             $this->_remarks = $params['Comment'];
 246         }
 247         if (isset($params['Index_comment'])) {
 248             $this->_comment = $params['Index_comment'];
 249         }
 250         if (isset($params['Non_unique'])) {
 251             $this->_non_unique = $params['Non_unique'];
 252         }
 253         if (isset($params['Packed'])) {
 254             $this->_packed = $params['Packed'];
 255         }
 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';
 266         }
 267     }
 268
 269     public function getColumnCount()
 270     {
 271         return count($this->_columns);
 272     }
 273
 274     public function getComment()
 275     {
 276         return $this->_comment;
 277     }
 278
 279     public function getRemarks()
 280     {
 281         return $this->_remarks;
 282     }
 283
 284     public function getComments()
 285     {
 286         $comments = $this->getRemarks();
 287         if (strlen($comments)) {
 288             $comments .= "\n";
 289         }
 290         $comments .= $this->getComment();
 291
 292         return $comments;
 293     }
 294
 295     public function getType()
 296     {
 297         return $this->_type;
 298     }
 299
 300     public function getChoice()
 301     {
 302         return $this->_choice;
 303     }
 304
 305     /**
 306      * Return a list of all index choices
 307      *
 308      * @return  array index choices
 309      */
 310     static public function getIndexChoices()
 311     {
 312         return array(
 313             'PRIMARY',
 314             'INDEX',
 315             'UNIQUE',
 316             'SPATIAL',
 317             'FULLTEXT',
 318         );
 319     }
 320
 321     public function generateIndexSelector()
 322     {
 323         $html_options = '';
 324
 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;
 331             }
 332             $html_options .= '<option value="' . $each_index_choice . '"'
 333                  . (($this->_choice == $each_index_choice) ? ' selected="selected"' : '')
 334                  . '>'. $each_index_choice . '</option>' . "\n";
 335         }
 336
 337         return $html_options;
 338     }
 339
 340     public function getPacked()
 341     {
 342         return $this->_packed;
 343     }
 344
 345     public function isPacked($as_text = false)
 346     {
 347         if ($as_text) {
 348             $r = array(
 349                 '0' => __('No'),
 350                 '1' => __('Yes'),
 351             );
 352         } else {
 353             $r = array(
 354                 '0' => false,
 355                 '1' => true,
 356             );
 357         }
 358
 359         if (null === $this->_packed) {
 360             return $r[0];
 361         }
 362
 363         return $this->_packed;
 364     }
 365
 366     public function getNonUnique()
 367     {
 368         return $this->_non_unique;
 369     }
 370
 371     public function isUnique($as_text = false)
 372     {
 373         if ($as_text) {
 374             $r = array(
 375                 '0' => __('Yes'),
 376                 '1' => __('No'),
 377             );
 378         } else {
 379             $r = array(
 380                 '0' => true,
 381                 '1' => false,
 382             );
 383         }
 384
 385         return $r[$this->_non_unique];
 386     }
 387
 388     public function getName()
 389     {
 390         return $this->_name;
 391     }
 392
 393     public function setName($name)
 394     {
 395         $this->_name = (string) $name;
 396     }
 397
 398     public function getColumns()
 399     {
 400         return $this->_columns;
 401     }
 402
 403     /**
 404      * Show index data
 405      *
 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
 412      */
 413     static public function getView($table, $schema, $print_mode = false)
 414     {
 415         $indexes = PMA_Index::getFromTable($table, $schema);
 416
 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>';
 421
 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;
 430         }
 431         if (! $print_mode) {
 432             $r .= PMA_Index::findDuplicates($table, $schema);
 433         }
 434         $r .= '<table id="table_index">';
 435         $r .= '<thead>';
 436         $r .= '<tr>';
 437         if (! $print_mode) {
 438             $r .= '<th colspan="2">' . __('Action') . '</th>';
 439         }
 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>';
 452
 453         $odd_row = true;
 454         foreach ($indexes as $index) {
 455             $row_span = ' rowspan="' . $index->getColumnCount() . '" ';
 456
 457             $r .= '<tr class="noclick ' . ($odd_row ? 'odd' : 'even') . '">';
 458
 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" ';
 465                 }
 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";
 470
 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());
 480                 }
 481
 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" ';
 487                 }
 488                 $r .= ' href="sql.php' . PMA_generate_common_url($this_params)
 489                    . '" >'
 490                    . PMA_getIcon('b_drop.png', __('Drop'))  . '</a>'
 491                    . '</td>' . "\n";
 492             }
 493
 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>';
 498
 499             foreach ($index->getColumns() as $column) {
 500                 if ($column->getSeqInIndex() > 1) {
 501                     $r .= '<tr class="noclick ' . ($odd_row ? 'odd' : 'even') . '">';
 502                 }
 503                 $r .= '<td>' . htmlspecialchars($column->getName());
 504                 if ($column->getSubPart()) {
 505                     $r .= ' (' . $column->getSubPart() . ')';
 506                 }
 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>';
 511
 512                 if ($column->getSeqInIndex() == 1) {
 513                     $r .= '<td ' . $row_span . '>'
 514                         . htmlspecialchars($index->getComments()) . '</td>';
 515                 }
 516                 $r .= '</tr>';
 517             } // end foreach $index['Sequences']
 518
 519             $odd_row = ! $odd_row;
 520         } // end while
 521         $r .= '</tbody>';
 522         $r .= '</table>';
 523         $r .= '</fieldset>';
 524
 525         return $r;
 526     }
 527
 528     public function getCompareData()
 529     {
 530         $data = array(
 531             // 'Non_unique'    => $this->_non_unique,
 532             'Packed'        => $this->_packed,
 533             'Index_type'    => $this->_type,
 534         );
 535
 536         foreach ($this->_columns as $column) {
 537             $data['columns'][] = $column->getCompareData();
 538         }
 539
 540         return $data;
 541     }
 542
 543     /**
 544      * Function to check over array of indexes and look for common problems
 545      *
 546      * @access  public
 547      * @param string      name of table
 548      * @return  string      Output HTML
 549      */
 550     static public function findDuplicates($table, $schema)
 551     {
 552         $indexes = PMA_Index::getFromTable($table, $schema);
 553
 554         $output  = '';
 555
 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;
 560         }
 561
 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;
 568                 }
 569
 570                 // did not find any difference
 571                 // so it makes no sense to have this two equal indexes
 572
 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();
 577
 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;
 581             }
 582         }
 583         return $output;
 584     }
 585 }
 586
 587 /**
 588  * @package PhpMyAdmin
 589  */
 590 class PMA_Index_Column
 591 {
 592     /**
 593      * @var string The column name
 594      */
 595     protected $_name = '';
 596
 597     /**
 598      * @var integer The column sequence number in the index, starting with 1.
 599      */
 600     protected $_seq_in_index = 1;
 601
 602     /**
 603      * @var string How the column is sorted in the index. “A” (Ascending) or NULL (Not sorted)
 604      */
 605     protected $_collation = null;
 606
 607     /**
 608      * The number of indexed characters if the column is only partly indexed,
 609      * NULL if the entire column is indexed.
 610      *
 611      * @var integer
 612      */
 613     protected $_sub_part = null;
 614
 615     /**
 616      * Contains YES if the column may contain NULL.
 617      * If not, the column contains NO.
 618      *
 619      * @var string
 620      */
 621     protected $_null = '';
 622
 623     /**
 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.
 629      *
 630      * @var integer
 631      */
 632     protected $_cardinality = null;
 633
 634     public function __construct($params = array())
 635     {
 636         $this->set($params);
 637     }
 638
 639     public function set($params)
 640     {
 641         if (isset($params['Column_name'])) {
 642             $this->_name = $params['Column_name'];
 643         }
 644         if (isset($params['Seq_in_index'])) {
 645             $this->_seq_in_index = $params['Seq_in_index'];
 646         }
 647         if (isset($params['Collation'])) {
 648             $this->_collation = $params['Collation'];
 649         }
 650         if (isset($params['Cardinality'])) {
 651             $this->_cardinality = $params['Cardinality'];
 652         }
 653         if (isset($params['Sub_part'])) {
 654             $this->_sub_part = $params['Sub_part'];
 655         }
 656         if (isset($params['Null'])) {
 657             $this->_null = $params['Null'];
 658         }
 659     }
 660
 661     public function getName()
 662     {
 663         return $this->_name;
 664     }
 665
 666     public function getCollation()
 667     {
 668         return $this->_collation;
 669     }
 670
 671     public function getCardinality()
 672     {
 673         return $this->_cardinality;
 674     }
 675
 676     public function getNull($as_text = false)
 677     {
 678         return $as_text
 679             ? (!$this->_null || $this->_null == 'NO' ? __('No') : __('Yes'))
 680             : $this->_null;
 681     }
 682
 683     public function getSeqInIndex()
 684     {
 685         return $this->_seq_in_index;
 686     }
 687
 688     public function getSubPart()
 689     {
 690         return $this->_sub_part;
 691     }
 692
 693     public function getCompareData()
 694     {
 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,
 701         );
 702     }
 703 }
 704 ?>