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, FULLTEXT, HASH, RTREE).
  47      *
  48      * @var string
  49      */
  50     protected $_type = '';
  51
  52     /**
  53      * The index choice (PRIMARY, UNIQUE, INDEX, 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      * @uses    $this->set()
  90      * @param   array $params
  91      */
  92     public function __construct($params = array())
  93     {
  94         $this->set($params);
  95     }
  96
  97     static public function singleton($schema, $table, $index_name = '')
  98     {
  99         PMA_Index::_loadIndexes($table, $schema);
 100         if (! isset(PMA_Index::$_registry[$schema][$table][$index_name])) {
 101             $index = new PMA_Index;
 102             if (strlen($index_name)) {
 103                 $index->setName($index_name);
 104                 PMA_Index::$_registry[$schema][$table][$index->getName()] = $index;
 105             }
 106             return $index;
 107         } else {
 108             return PMA_Index::$_registry[$schema][$table][$index_name];
 109         }
 110     }
 111
 112     /**
 113      * returns an array with all indexes from the given table
 114      *
 115      * @uses    PMA_Index::_loadIndexes()
 116      * @uses    PMA_Index::$_registry
 117      * @param   string $table
 118      * @param   string $schema
 119      * @return  array
 120      */
 121     static public function getFromTable($table, $schema)
 122     {
 123         PMA_Index::_loadIndexes($table, $schema);
 124
 125         if (isset(PMA_Index::$_registry[$schema][$table])) {
 126             return PMA_Index::$_registry[$schema][$table];
 127         } else {
 128             return array();
 129         }
 130     }
 131
 132     /**
 133      * return primary if set, false otherwise
 134      *
 135      * @uses    PMA_Index::_loadIndexes()
 136      * @uses    PMA_Index::$_registry
 137      * @param   string $table
 138      * @param   string $schema
 139      * @return  mixed primary index or false if no one exists
 140      */
 141     static public function getPrimary($table, $schema)
 142     {
 143         PMA_Index::_loadIndexes($table, $schema);
 144
 145         if (isset(PMA_Index::$_registry[$schema][$table]['PRIMARY'])) {
 146             return PMA_Index::$_registry[$schema][$table]['PRIMARY'];
 147         } else {
 148             return false;
 149         }
 150     }
 151
 152     /**
 153      * Load index data for table
 154      *
 155      * @uses    PMA_Index::$_registry
 156      * @uses    PMA_DBI_fetch_result()
 157      * @uses    PMA_backquote()
 158      * @uses    PMA_Index
 159      * @uses    PMA_Index->addColumn()
 160      * @param   string $table
 161      * @param   string $schema
 162      * @return  boolean
 163      */
 164     static protected function _loadIndexes($table, $schema)
 165     {
 166         if (isset(PMA_Index::$_registry[$schema][$table])) {
 167             return true;
 168         }
 169
 170         $_raw_indexes = PMA_DBI_fetch_result('SHOW INDEX FROM ' . PMA_backquote($schema) . '.' . PMA_backquote($table));
 171         foreach ($_raw_indexes as $_each_index) {
 172             $_each_index['Schema'] = $schema;
 173             if (! isset(PMA_Index::$_registry[$schema][$table][$_each_index['Key_name']])) {
 174                 $key = new PMA_Index($_each_index);
 175                 PMA_Index::$_registry[$schema][$table][$_each_index['Key_name']] = $key;
 176             } else {
 177                 $key = PMA_Index::$_registry[$schema][$table][$_each_index['Key_name']];
 178             }
 179
 180             $key->addColumn($_each_index);
 181         }
 182
 183         return true;
 184     }
 185
 186     /**
 187      * Add column to index
 188      *
 189      * @uses    $this->_columns
 190      * @uses    PMA_Index_Column
 191      * @param   array $params column params
 192      */
 193     public function addColumn($params)
 194     {
 195         if (strlen($params['Column_name'])) {
 196             $this->_columns[$params['Column_name']] = new PMA_Index_Column($params);
 197         }
 198     }
 199
 200     public function addColumns($columns)
 201     {
 202         $_columns = array();
 203
 204         if (isset($columns['names'])) {
 205             // coming from form
 206             // $columns[names][]
 207             // $columns[sub_parts][]
 208             foreach ($columns['names'] as $key => $name) {
 209                 $_columns[] = array(
 210                     'Column_name'   => $name,
 211                     'Sub_part'      => $columns['sub_parts'][$key],
 212                 );
 213             }
 214         } else {
 215             // coming from SHOW INDEXES
 216             // $columns[][name]
 217             // $columns[][sub_part]
 218             // ...
 219             $_columns = $columns;
 220         }
 221
 222         foreach ($_columns as $column) {
 223             $this->addColumn($column);
 224         }
 225     }
 226
 227     /**
 228      * Returns true if $column indexed in this index
 229      *
 230      * @uses    $this->_columns
 231      * @param   string $column
 232      * @return  boolean
 233      */
 234     public function hasColumn($column)
 235     {
 236         return isset($this->_columns[$column]);
 237     }
 238
 239     public function set($params)
 240     {
 241         if (isset($params['columns'])) {
 242             $this->addColumns($params['columns']);
 243         }
 244         if (isset($params['Schema'])) {
 245             $this->_schema = $params['Schema'];
 246         }
 247         if (isset($params['Table'])) {
 248             $this->_table = $params['Table'];
 249         }
 250         if (isset($params['Key_name'])) {
 251             $this->_name = $params['Key_name'];
 252         }
 253         if (isset($params['Index_type'])) {
 254             $this->_type = $params['Index_type'];
 255         }
 256         if (isset($params['Comment'])) {
 257             $this->_remarks = $params['Comment'];
 258         }
 259         if (isset($params['Index_comment'])) {
 260             $this->_comment = $params['Index_comment'];
 261         }
 262         if (isset($params['Non_unique'])) {
 263             $this->_non_unique = $params['Non_unique'];
 264         }
 265         if (isset($params['Packed'])) {
 266             $this->_packed = $params['Packed'];
 267         }
 268         if ('PRIMARY' == $this->_name) {
 269             $this->_choice = 'PRIMARY';
 270         } elseif ('FULLTEXT' == $this->_type) {
 271             $this->_choice = 'FULLTEXT';
 272         } elseif ('0' == $this->_non_unique) {
 273             $this->_choice = 'UNIQUE';
 274         } else {
 275             $this->_choice = 'INDEX';
 276         }
 277     }
 278
 279     public function getColumnCount()
 280     {
 281         return count($this->_columns);
 282     }
 283
 284     public function getComment()
 285     {
 286         return $this->_comment;
 287     }
 288
 289     public function getRemarks()
 290     {
 291         return $this->_remarks;
 292     }
 293
 294     public function getComments()
 295     {
 296         $comments = $this->getRemarks();
 297         if (strlen($comments)) {
 298             $comments .= "\n";
 299         }
 300         $comments .= $this->getComment();
 301
 302         return $comments;
 303     }
 304
 305     public function getType()
 306     {
 307         return $this->_type;
 308     }
 309
 310     public function getChoice()
 311     {
 312         return $this->_choice;
 313     }
 314
 315     /**
 316      * Return a list of all index choices
 317      *
 318      * @return  array index choices
 319      */
 320     static public function getIndexChoices()
 321     {
 322         return array(
 323             'PRIMARY',
 324             'INDEX',
 325             'UNIQUE',
 326             'FULLTEXT',
 327         );
 328     }
 329
 330     public function generateIndexSelector()
 331     {
 332         $html_options = '';
 333
 334         foreach (PMA_Index::getIndexChoices() as $each_index_choice) {
 335             if ($each_index_choice === 'PRIMARY'
 336              && $this->_choice !== 'PRIMARY'
 337              && PMA_Index::getPrimary($this->_table, $this->_schema)) {
 338                 // skip PRIMARY if there is already one in the table
 339                 continue;
 340             }
 341             $html_options .= '<option value="' . $each_index_choice . '"'
 342                  . (($this->_choice == $each_index_choice) ? ' selected="selected"' : '')
 343                  . '>'. $each_index_choice . '</option>' . "\n";
 344         }
 345
 346         return $html_options;
 347     }
 348
 349     public function getPacked()
 350     {
 351         return $this->_packed;
 352     }
 353
 354     public function isPacked($as_text = false)
 355     {
 356         if ($as_text) {
 357             $r = array(
 358                 '0' => __('No'),
 359                 '1' => __('Yes'),
 360             );
 361         } else {
 362             $r = array(
 363                 '0' => false,
 364                 '1' => true,
 365             );
 366         }
 367
 368         if (null === $this->_packed) {
 369             return $r[0];
 370         }
 371
 372         return $this->_packed;
 373     }
 374
 375     public function getNonUnique()
 376     {
 377         return $this->_non_unique;
 378     }
 379
 380     public function isUnique($as_text = false)
 381     {
 382         if ($as_text) {
 383             $r = array(
 384                 '0' => __('Yes'),
 385                 '1' => __('No'),
 386             );
 387         } else {
 388             $r = array(
 389                 '0' => true,
 390                 '1' => false,
 391             );
 392         }
 393
 394         return $r[$this->_non_unique];
 395     }
 396
 397     public function getName()
 398     {
 399         return $this->_name;
 400     }
 401
 402     public function setName($name)
 403     {
 404         $this->_name = (string) $name;
 405     }
 406
 407     public function getColumns()
 408     {
 409         return $this->_columns;
 410     }
 411
 412     /**
 413      * Show index data
 414      *
 415      * @param   string      $table          The tablename
 416      * @param   array       $indexes_info   Referenced info array
 417      * @param   array       $indexes_data   Referenced data array
 418      * @param   boolean     $print_mode
 419      * @access  public
 420      * @return  array       Index collection array
 421      */
 422     static public function getView($table, $schema, $print_mode = false)
 423     {
 424         $indexes = PMA_Index::getFromTable($table, $schema);
 425
 426         if (count($indexes) < 1) {
 427             return PMA_Message::warning(__('No index defined!'))->getDisplay();
 428         }
 429
 430         $r = '';
 431
 432         $r .= '<h2>' . __('Indexes') . ': ';
 433         $r .= PMA_showMySQLDocu('optimization', 'optimizing-database-structure');
 434         $r .= '</h2>';
 435         $r .= '<table>';
 436         $r .= '<thead>';
 437         $r .= '<tr>';
 438         if (! $print_mode) {
 439             $r .= '<th colspan="2">' . __('Action') . '</th>';
 440         }
 441         $r .= '<th>' . __('Keyname') . '</th>';
 442         $r .= '<th>' . __('Type') . '</th>';
 443         $r .= '<th>' . __('Unique') . '</th>';
 444         $r .= '<th>' . __('Packed') . '</th>';
 445         $r .= '<th>' . __('Column') . '</th>';
 446         $r .= '<th>' . __('Cardinality') . '</th>';
 447         $r .= '<th>' . __('Collation') . '</th>';
 448         $r .= '<th>' . __('Null') . '</th>';
 449         $r .= '<th>' . __('Comment') . '</th>';
 450         $r .= '</tr>';
 451         $r .= '</thead>';
 452         $r .= '<tbody>';
 453
 454         $odd_row = true;
 455         foreach ($indexes as $index) {
 456             $row_span = ' rowspan="' . $index->getColumnCount() . '" ';
 457
 458             $r .= '<tr class="' . ($odd_row ? 'odd' : 'even') . '">';
 459
 460             if (! $print_mode) {
 461                 $this_params = $GLOBALS['url_params'];
 462                 $this_params['index'] = $index->getName();
 463                 $r .= '<td ' . $row_span . '>'
 464                    . '    <a href="tbl_indexes.php' . PMA_generate_common_url($this_params)
 465                    . '">' . PMA_getIcon('b_edit.png', __('Edit')) . '</a>'
 466                    . '</td>' . "\n";
 467
 468                 $this_params = $GLOBALS['url_params'];
 469                 if ($index->getName() == 'PRIMARY') {
 470                     $this_params['sql_query'] = 'ALTER TABLE ' . PMA_backquote($table) . ' DROP PRIMARY KEY';
 471                     $this_params['message_to_show'] = __('The primary key has been dropped');
 472                     $js_msg      = PMA_jsFormat('ALTER TABLE ' . $table . ' DROP PRIMARY KEY');
 473                 } else {
 474                     $this_params['sql_query'] = 'ALTER TABLE ' . PMA_backquote($table) . ' DROP INDEX ' . PMA_backquote($index->getName());
 475                     $this_params['message_to_show'] = sprintf(__('Index %s has been dropped'), $index->getName());
 476                     $js_msg      = PMA_jsFormat('ALTER TABLE ' . $table . ' DROP INDEX ' . $index->getName());
 477                 }
 478
 479                 $r .= '<td ' . $row_span . '>'
 480                     . '    <a ';
 481                 if ($GLOBALS['cfg']['AjaxEnable']) {
 482                     $r .= 'class="drop_primary_key_index_anchor" ';
 483                 }
 484                 $r .= ' href="sql.php' . PMA_generate_common_url($this_params)
 485                    . '" >'
 486                    . PMA_getIcon('b_drop.png', __('Drop'))  . '</a>'
 487                    . '</td>' . "\n";
 488
 489                 $r .= '<input type="hidden" class="drop_primary_key_index_msg" value="' . $js_msg . '" />';
 490             }
 491
 492             $r .= '<th ' . $row_span . '>' . htmlspecialchars($index->getName()) . '</th>';
 493             $r .= '<td ' . $row_span . '>' . htmlspecialchars($index->getType()) . '</td>';
 494             $r .= '<td ' . $row_span . '>' . $index->isUnique(true) . '</td>';
 495             $r .= '<td ' . $row_span . '>' . $index->isPacked(true) . '</td>';
 496
 497             foreach ($index->getColumns() as $column) {
 498                 if ($column->getSeqInIndex() > 1) {
 499                     $r .= '<tr class="' . ($odd_row ? 'odd' : 'even') . '">';
 500                 }
 501                 $r .= '<td>' . htmlspecialchars($column->getName());
 502                 if ($column->getSubPart()) {
 503                     $r .= ' (' . $column->getSubPart() . ')';
 504                 }
 505                 $r .= '</td>';
 506                 $r .= '<td>' . htmlspecialchars($column->getCardinality()) . '</td>';
 507                 $r .= '<td>' . htmlspecialchars($column->getCollation()) . '</td>';
 508                 $r .= '<td>' . htmlspecialchars($column->getNull()) . '</td>';
 509
 510                 if ($column->getSeqInIndex() == 1) {
 511                     $r .= '<td ' . $row_span . '>'
 512                         . htmlspecialchars($index->getComments()) . '</td>';
 513                 }
 514                 $r .= '</tr>';
 515             } // end foreach $index['Sequences']
 516
 517             $odd_row = ! $odd_row;
 518         } // end while
 519         $r .= '</tbody>';
 520         $r .= '</table>';
 521
 522         if (! $print_mode) {
 523             $r .= PMA_Index::findDuplicates($table, $schema);
 524         }
 525
 526         return $r;
 527     }
 528
 529     public function getCompareData()
 530     {
 531         $data = array(
 532             // 'Non_unique'    => $this->_non_unique,
 533             'Packed'        => $this->_packed,
 534             'Index_type'    => $this->_type,
 535         );
 536
 537         foreach ($this->_columns as $column) {
 538             $data['columns'][] = $column->getCompareData();
 539         }
 540
 541         return $data;
 542     }
 543
 544     /**
 545      * Function to check over array of indexes and look for common problems
 546      *
 547      * @uses    is_string()
 548      * @uses    is_array()
 549      * @uses    count()
 550      * @uses    array_pop()
 551      * @uses    reset()
 552      * @uses    current()
 553      * @access  public
 554      * @param   string      name of table
 555      * @return  string      Output HTML
 556      */
 557     static public function findDuplicates($table, $schema)
 558     {
 559         $indexes = PMA_Index::getFromTable($table, $schema);
 560
 561         $output  = '';
 562
 563         // count($indexes) < 2:
 564         //   there is no need to check if there less than two indexes
 565         if (count($indexes) < 2) {
 566             return $output;
 567         }
 568
 569         // remove last index from stack and ...
 570         while ($while_index = array_pop($indexes)) {
 571             // ... compare with every remaining index in stack
 572             foreach ($indexes as $each_index) {
 573                 if ($each_index->getCompareData() !== $while_index->getCompareData()) {
 574                     continue;
 575                 }
 576
 577                 // did not find any difference
 578                 // so it makes no sense to have this two equal indexes
 579
 580                 $message = PMA_Message::warning(__('The indexes %1$s and %2$s seem to be equal and one of them could possibly be removed.'));
 581                 $message->addParam($each_index->getName());
 582                 $message->addParam($while_index->getName());
 583                 $output .= $message->getDisplay();
 584
 585                 // there is no need to check any further indexes if we have already
 586                 // found that this one has a duplicate
 587                 continue 2;
 588             }
 589         }
 590         return $output;
 591     }
 592 }
 593
 594 /**
 595  * @package phpMyAdmin
 596  */
 597 class PMA_Index_Column
 598 {
 599     /**
 600      * @var string The column name
 601      */
 602     protected $_name = '';
 603
 604     /**
 605      * @var integer The column sequence number in the index, starting with 1.
 606      */
 607     protected $_seq_in_index = 1;
 608
 609     /**
 610      * @var string How the column is sorted in the index. “A” (Ascending) or NULL (Not sorted)
 611      */
 612     protected $_collation = null;
 613
 614     /**
 615      * The number of indexed characters if the column is only partly indexed,
 616      * NULL if the entire column is indexed.
 617      *
 618      * @var integer
 619      */
 620     protected $_sub_part = null;
 621
 622     /**
 623      * Contains YES if the column may contain NULL.
 624      * If not, the column contains NO.
 625      *
 626      * @var string
 627      */
 628     protected $_null = '';
 629
 630     /**
 631      * An estimate of the number of unique values in the index. This is updated
 632      * by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on
 633      * statistics stored as integers, so the value is not necessarily exact even
 634      * for small tables. The higher the cardinality, the greater the chance that
 635      * MySQL uses the index when doing joins.
 636      *
 637      * @var integer
 638      */
 639     protected $_cardinality = 0;
 640
 641     public function __construct($params = array())
 642     {
 643         $this->set($params);
 644     }
 645
 646     public function set($params)
 647     {
 648         if (isset($params['Column_name'])) {
 649             $this->_name = $params['Column_name'];
 650         }
 651         if (isset($params['Seq_in_index'])) {
 652             $this->_seq_in_index = $params['Seq_in_index'];
 653         }
 654         if (isset($params['Collation'])) {
 655             $this->_collation = $params['Collation'];
 656         }
 657         if (isset($params['Cardinality'])) {
 658             $this->_cardinality = $params['Cardinality'];
 659         }
 660         if (isset($params['Sub_part'])) {
 661             $this->_sub_part = $params['Sub_part'];
 662         }
 663         if (isset($params['Null'])) {
 664             $this->_null = $params['Null'];
 665         }
 666     }
 667
 668     public function getName()
 669     {
 670         return $this->_name;
 671     }
 672
 673     public function getCollation()
 674     {
 675         return $this->_collation;
 676     }
 677
 678     public function getCardinality()
 679     {
 680         return $this->_cardinality;
 681     }
 682
 683     public function getNull()
 684     {
 685         return $this->_null;
 686     }
 687
 688     public function getSeqInIndex()
 689     {
 690         return $this->_seq_in_index;
 691     }
 692
 693     public function getSubPart()
 694     {
 695         return $this->_sub_part;
 696     }
 697
 698     public function getCompareData()
 699     {
 700         return array(
 701             'Column_name'   => $this->_name,
 702             'Seq_in_index'  => $this->_seq_in_index,
 703             'Collation'     => $this->_collation,
 704             'Sub_part'      => $this->_sub_part,
 705             'Null'          => $this->_null,
 706         );
 707     }
 708 }
 709 ?>