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  * @version $Id$
   7  * @package phpMyAdmin
   8  */
   9
  10 /**
  11  * @since phpMyAdmin 3.0.0
  12  *
  13  * @package phpMyAdmin
  14  */
  15 class PMA_Index
  16 {
  17     /**
  18      * Class-wide storage container for indexes (caching, singleton)
  19      *
  20      * @var array
  21      */
  22     protected static $_registry = array();
  23
  24     /**
  25      * @var string The name of the schema
  26      */
  27     protected $_schema = '';
  28
  29     /**
  30      * @var string The name of the table
  31      */
  32     protected $_table = '';
  33
  34     /**
  35      * @var string The name of the index
  36      */
  37     protected $_name = '';
  38
  39     /**
  40      * Columns in index
  41      *
  42      * @var array
  43      */
  44     protected $_columns = array();
  45
  46     /**
  47      * The index method used (BTREE, FULLTEXT, HASH, RTREE).
  48      *
  49      * @var string
  50      */
  51     protected $_type = '';
  52
  53     /**
  54      * The index choice (PRIMARY, UNIQUE, INDEX, FULLTEXT)
  55      *
  56      * @var string
  57      */
  58     protected $_choice = '';
  59
  60     /**
  61      * Various remarks.
  62      *
  63      * @var string
  64      */
  65     protected $_remarks = '';
  66
  67     /**
  68      * Any comment provided for the index with a COMMENT attribute when the
  69      * index was created.
  70      *
  71      * @var string
  72      */
  73     protected $_comment = '';
  74
  75     /**
  76      * @var integer 0 if the index cannot contain duplicates, 1 if it can.
  77      */
  78     protected $_non_unique = 0;
  79
  80     /**
  81      * Indicates how the key is packed. NULL if it is not.
  82      *
  83      * @var string
  84      */
  85     protected $_packed = null;
  86
  87     /**
  88      * Constructor
  89      *
  90      * @uses    $this->set()
  91      * @param   array $params
  92      */
  93     public function __construct($params = array())
  94     {
  95         $this->set($params);
  96     }
  97
  98     static public function singleton($schema, $table, $index_name = '')
  99     {
 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;
 106             }
 107             return $index;
 108         } else {
 109             return PMA_Index::$_registry[$schema][$table][$index_name];
 110         }
 111     }
 112
 113     /**
 114      * returns an array with all indexes from the given table
 115      *
 116      * @uses    PMA_Index::_loadIndexes()
 117      * @uses    PMA_Index::$_registry
 118      * @param   string $table
 119      * @param   string $schema
 120      * @return  array
 121      */
 122     static public function getFromTable($table, $schema)
 123     {
 124         PMA_Index::_loadIndexes($table, $schema);
 125
 126         if (isset(PMA_Index::$_registry[$schema][$table])) {
 127             return PMA_Index::$_registry[$schema][$table];
 128         } else {
 129             return array();
 130         }
 131     }
 132
 133     /**
 134      * return primary if set, false otherwise
 135      *
 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
 141      */
 142     static public function getPrimary($table, $schema)
 143     {
 144         PMA_Index::_loadIndexes($table, $schema);
 145
 146         if (isset(PMA_Index::$_registry[$schema][$table]['PRIMARY'])) {
 147             return PMA_Index::$_registry[$schema][$table]['PRIMARY'];
 148         } else {
 149             return false;
 150         }
 151     }
 152
 153     /**
 154      * Load index data for table
 155      *
 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
 164      */
 165     static protected function _loadIndexes($table, $schema)
 166     {
 167         if (isset(PMA_Index::$_registry[$schema][$table])) {
 168             return true;
 169         }
 170
 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']];
 179             }
 180
 181             $key->addColumn($_each_index);
 182         }
 183
 184         return true;
 185     }
 186
 187     /**
 188      * Add column to index
 189      *
 190      * @uses    $this->_columns
 191      * @uses    PMA_Index_Column
 192      * @param   array $params column params
 193      */
 194     public function addColumn($params)
 195     {
 196         if (strlen($params['Column_name'])) {
 197             $this->_columns[$params['Column_name']] = new PMA_Index_Column($params);
 198         }
 199     }
 200
 201     public function addColumns($columns)
 202     {
 203         $_columns = array();
 204
 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],
 213                 );
 214             }
 215         } else {
 216             // coming from SHOW INDEXES
 217             // $columns[][name]
 218             // $columns[][sub_part]
 219             // ...
 220             $_columns = $columns;
 221         }
 222
 223         foreach ($_columns as $column) {
 224             $this->addColumn($column);
 225         }
 226     }
 227
 228     /**
 229      * Returns true if $column indexed in this index
 230      *
 231      * @uses    $this->_columns
 232      * @param   string $column
 233      * @return  boolean
 234      */
 235     public function hasColumn($column)
 236     {
 237         return isset($this->_columns[$column]);
 238     }
 239
 240     public function set($params)
 241     {
 242         if (isset($params['columns'])) {
 243             $this->addColumns($params['columns']);
 244         }
 245         if (isset($params['Schema'])) {
 246             $this->_schema = $params['Schema'];
 247         }
 248         if (isset($params['Table'])) {
 249             $this->_table = $params['Table'];
 250         }
 251         if (isset($params['Key_name'])) {
 252             $this->_name = $params['Key_name'];
 253         }
 254         if (isset($params['Index_type'])) {
 255             $this->_type = $params['Index_type'];
 256         }
 257         if (isset($params['Comment'])) {
 258             $this->_remarks = $params['Comment'];
 259         }
 260         if (isset($params['Index_comment'])) {
 261             $this->_comment = $params['Index_comment'];
 262         }
 263         if (isset($params['Non_unique'])) {
 264             $this->_non_unique = $params['Non_unique'];
 265         }
 266         if (isset($params['Packed'])) {
 267             $this->_packed = $params['Packed'];
 268         }
 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';
 277         }
 278     }
 279
 280     public function getColumnCount()
 281     {
 282         return count($this->_columns);
 283     }
 284
 285     public function getComment()
 286     {
 287         return $this->_comment;
 288     }
 289
 290     public function getRemarks()
 291     {
 292         return $this->_remarks;
 293     }
 294
 295     public function getComments()
 296     {
 297         $comments = $this->getRemarks();
 298         if (strlen($comments)) {
 299             $comments .= "\n";
 300         }
 301         $comments .= $this->getComment();
 302
 303         return $comments;
 304     }
 305
 306     public function getType()
 307     {
 308         return $this->_type;
 309     }
 310
 311     public function getChoice()
 312     {
 313         return $this->_choice;
 314     }
 315
 316     /**
 317      * Return a list of all index choices
 318      *
 319      * @return  array index choices
 320      */
 321     static public function getIndexChoices()
 322     {
 323         return array(
 324             'PRIMARY',
 325             'INDEX',
 326             'UNIQUE',
 327             'FULLTEXT',
 328         );
 329     }
 330
 331     public function generateIndexSelector()
 332     {
 333         $html_options = '';
 334
 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;
 341             }
 342             $html_options .= '<option value="' . $each_index_choice . '"'
 343                  . (($this->_choice == $each_index_choice) ? ' selected="selected"' : '')
 344                  . '>'. $each_index_choice . '</option>' . "\n";
 345         }
 346
 347         return $html_options;
 348     }
 349
 350     public function getPacked()
 351     {
 352         return $this->_packed;
 353     }
 354
 355     public function isPacked($as_text = false)
 356     {
 357         if ($as_text) {
 358             $r = array(
 359                 '0' => __('No'),
 360                 '1' => __('Yes'),
 361             );
 362         } else {
 363             $r = array(
 364                 '0' => false,
 365                 '1' => true,
 366             );
 367         }
 368
 369         if (null === $this->_packed) {
 370             return $r[0];
 371         }
 372
 373         return $this->_packed;
 374     }
 375
 376     public function getNonUnique()
 377     {
 378         return $this->_non_unique;
 379     }
 380
 381     public function isUnique($as_text = false)
 382     {
 383         if ($as_text) {
 384             $r = array(
 385                 '0' => __('Yes'),
 386                 '1' => __('No'),
 387             );
 388         } else {
 389             $r = array(
 390                 '0' => true,
 391                 '1' => false,
 392             );
 393         }
 394
 395         return $r[$this->_non_unique];
 396     }
 397
 398     public function getName()
 399     {
 400         return $this->_name;
 401     }
 402
 403     public function setName($name)
 404     {
 405         $this->_name = (string) $name;
 406     }
 407
 408     public function getColumns()
 409     {
 410         return $this->_columns;
 411     }
 412
 413     /**
 414      * Show index data
 415      *
 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
 422      */
 423     static public function getView($table, $schema, $print_mode = false)
 424     {
 425         $indexes = PMA_Index::getFromTable($table, $schema);
 426
 427         if (count($indexes) < 1) {
 428             return PMA_Message::warning(__('No index defined!'))->getDisplay();
 429         }
 430
 431         $r = '';
 432
 433         $r .= '<h2>' . __('Indexes') . ': ';
 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">' . __('Action') . '</th>';
 441         }
 442         $r .= '<th>' . __('Keyname') . '</th>';
 443         $r .= '<th>' . __('Type') . '</th>';
 444         $r .= '<th>' . __('Unique') . '</th>';
 445         $r .= '<th>' . __('Packed') . '</th>';
 446         $r .= '<th>' . __('Column') . '</th>';
 447         $r .= '<th>' . __('Cardinality') . '</th>';
 448         $r .= '<th>' . __('Collation') . '</th>';
 449         $r .= '<th>' . __('Null') . '</th>';
 450         $r .= '<th>' . __('Comment') . '</th>';
 451         $r .= '</tr>';
 452         $r .= '</thead>';
 453         $r .= '<tbody>';
 454
 455         $odd_row = true;
 456         foreach ($indexes as $index) {
 457             $row_span = ' rowspan="' . $index->getColumnCount() . '" ';
 458
 459             $r .= '<tr class="' . ($odd_row ? 'odd' : 'even') . '">';
 460
 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', __('Edit')) . '</a>'
 467                    . '</td>' . "\n";
 468
 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'] = __('The primary key has been dropped');
 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(__('Index %s has been dropped'), $index->getName());
 477                     $js_msg      = PMA_jsFormat('ALTER TABLE ' . $table . ' DROP INDEX ' . $index->getName());
 478                 }
 479
 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', __('Drop'))  . '</a>'
 484                    . '</td>' . "\n";
 485             }
 486
 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>';
 491
 492             foreach ($index->getColumns() as $column) {
 493                 if ($column->getSeqInIndex() > 1) {
 494                     $r .= '<tr class="' . ($odd_row ? 'odd' : 'even') . '">';
 495                 }
 496                 $r .= '<td>' . htmlspecialchars($column->getName());
 497                 if ($column->getSubPart()) {
 498                     $r .= ' (' . $column->getSubPart() . ')';
 499                 }
 500                 $r .= '</td>';
 501                 $r .= '<td>' . htmlspecialchars($column->getCardinality()) . '</td>';
 502                 $r .= '<td>' . htmlspecialchars($column->getCollation()) . '</td>';
 503                 $r .= '<td>' . htmlspecialchars($column->getNull()) . '</td>';
 504
 505                 if ($column->getSeqInIndex() == 1) {
 506                     $r .= '<td ' . $row_span . '>'
 507                         . htmlspecialchars($index->getComments()) . '</td>';
 508                 }
 509                 $r .= '</tr>';
 510             } // end foreach $index['Sequences']
 511
 512             $odd_row = ! $odd_row;
 513         } // end while
 514         $r .= '</tbody>';
 515         $r .= '</table>';
 516
 517         if (! $print_mode) {
 518             $r .= PMA_Index::findDuplicates($table, $schema);
 519         }
 520
 521         return $r;
 522     }
 523
 524     public function getCompareData()
 525     {
 526         $data = array(
 527             // 'Non_unique'    => $this->_non_unique,
 528             'Packed'        => $this->_packed,
 529             'Index_type'    => $this->_type,
 530         );
 531
 532         foreach ($this->_columns as $column) {
 533             $data['columns'][] = $column->getCompareData();
 534         }
 535
 536         return $data;
 537     }
 538
 539     /**
 540      * Function to check over array of indexes and look for common problems
 541      *
 542      * @uses    __('The indexes %1 and %2 seem to be equal and one of them could possibly be removed.')
 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 ?>