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 class="drop_primary_key_index_anchor" href="sql.php' . PMA_generate_common_url($this_params)
 481                    . '" >'
 482                    . PMA_getIcon('b_drop.png', __('Drop'))  . '</a>'
 483                    . '</td>' . "\n";
 484
 485                 $r .= '<input type="hidden" class="drop_primary_key_index_msg" value="' . $js_msg . '" />';
 486             }
 487
 488             $r .= '<th ' . $row_span . '>' . htmlspecialchars($index->getName()) . '</th>';
 489             $r .= '<td ' . $row_span . '>' . htmlspecialchars($index->getType()) . '</td>';
 490             $r .= '<td ' . $row_span . '>' . $index->isUnique(true) . '</td>';
 491             $r .= '<td ' . $row_span . '>' . $index->isPacked(true) . '</td>';
 492
 493             foreach ($index->getColumns() as $column) {
 494                 if ($column->getSeqInIndex() > 1) {
 495                     $r .= '<tr class="' . ($odd_row ? 'odd' : 'even') . '">';
 496                 }
 497                 $r .= '<td>' . htmlspecialchars($column->getName());
 498                 if ($column->getSubPart()) {
 499                     $r .= ' (' . $column->getSubPart() . ')';
 500                 }
 501                 $r .= '</td>';
 502                 $r .= '<td>' . htmlspecialchars($column->getCardinality()) . '</td>';
 503                 $r .= '<td>' . htmlspecialchars($column->getCollation()) . '</td>';
 504                 $r .= '<td>' . htmlspecialchars($column->getNull()) . '</td>';
 505
 506                 if ($column->getSeqInIndex() == 1) {
 507                     $r .= '<td ' . $row_span . '>'
 508                         . htmlspecialchars($index->getComments()) . '</td>';
 509                 }
 510                 $r .= '</tr>';
 511             } // end foreach $index['Sequences']
 512
 513             $odd_row = ! $odd_row;
 514         } // end while
 515         $r .= '</tbody>';
 516         $r .= '</table>';
 517
 518         if (! $print_mode) {
 519             $r .= PMA_Index::findDuplicates($table, $schema);
 520         }
 521
 522         return $r;
 523     }
 524
 525     public function getCompareData()
 526     {
 527         $data = array(
 528             // 'Non_unique'    => $this->_non_unique,
 529             'Packed'        => $this->_packed,
 530             'Index_type'    => $this->_type,
 531         );
 532
 533         foreach ($this->_columns as $column) {
 534             $data['columns'][] = $column->getCompareData();
 535         }
 536
 537         return $data;
 538     }
 539
 540     /**
 541      * Function to check over array of indexes and look for common problems
 542      *
 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
 552      */
 553     static public function findDuplicates($table, $schema)
 554     {
 555         $indexes = PMA_Index::getFromTable($table, $schema);
 556
 557         $output  = '';
 558
 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;
 563         }
 564
 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;
 571                 }
 572
 573                 // did not find any difference
 574                 // so it makes no sense to have this two equal indexes
 575
 576                 $message = PMA_Message::warning(__('The indexes %1$s and %2$s seem to be equal and one of them could possibly be removed.'));
 577                 $message->addParam($each_index->getName());
 578                 $message->addParam($while_index->getName());
 579                 $output .= $message->getDisplay();
 580
 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;
 584             }
 585         }
 586         return $output;
 587     }
 588 }
 589
 590 /**
 591  * @package phpMyAdmin
 592  */
 593 class PMA_Index_Column
 594 {
 595     /**
 596      * @var string The column name
 597      */
 598     protected $_name = '';
 599
 600     /**
 601      * @var integer The column sequence number in the index, starting with 1.
 602      */
 603     protected $_seq_in_index = 1;
 604
 605     /**
 606      * @var string How the column is sorted in the index. “A” (Ascending) or NULL (Not sorted)
 607      */
 608     protected $_collation = null;
 609
 610     /**
 611      * The number of indexed characters if the column is only partly indexed,
 612      * NULL if the entire column is indexed.
 613      *
 614      * @var integer
 615      */
 616     protected $_sub_part = null;
 617
 618     /**
 619      * Contains YES if the column may contain NULL.
 620      * If not, the column contains NO.
 621      *
 622      * @var string
 623      */
 624     protected $_null = '';
 625
 626     /**
 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.
 632      *
 633      * @var integer
 634      */
 635     protected $_cardinality = 0;
 636
 637     public function __construct($params = array())
 638     {
 639         $this->set($params);
 640     }
 641
 642     public function set($params)
 643     {
 644         if (isset($params['Column_name'])) {
 645             $this->_name = $params['Column_name'];
 646         }
 647         if (isset($params['Seq_in_index'])) {
 648             $this->_seq_in_index = $params['Seq_in_index'];
 649         }
 650         if (isset($params['Collation'])) {
 651             $this->_collation = $params['Collation'];
 652         }
 653         if (isset($params['Cardinality'])) {
 654             $this->_cardinality = $params['Cardinality'];
 655         }
 656         if (isset($params['Sub_part'])) {
 657             $this->_sub_part = $params['Sub_part'];
 658         }
 659         if (isset($params['Null'])) {
 660             $this->_null = $params['Null'];
 661         }
 662     }
 663
 664     public function getName()
 665     {
 666         return $this->_name;
 667     }
 668
 669     public function getCollation()
 670     {
 671         return $this->_collation;
 672     }
 673
 674     public function getCardinality()
 675     {
 676         return $this->_cardinality;
 677     }
 678
 679     public function getNull()
 680     {
 681         return $this->_null;
 682     }
 683
 684     public function getSeqInIndex()
 685     {
 686         return $this->_seq_in_index;
 687     }
 688
 689     public function getSubPart()
 690     {
 691         return $this->_sub_part;
 692     }
 693
 694     public function getCompareData()
 695     {
 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,
 702         );
 703     }
 704 }
 705 ?>