libraries/Index.class.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * holds the datasbe 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     /**
 312      * Return a list of all index choices
 313      *
 314      * @return  array index choices
 315      */
 316     static public function getIndexChoices()
 317     {
 318         return array(
 319             'PRIMARY',
 320             'INDEX',
 321             'UNIQUE',
 322             'FULLTEXT',
 323         );
 324     }
 325
 326     public function generateIndexSelector()
 327     {
 328         $html_options = '';
 329
 330         foreach (PMA_Index::getIndexChoices() as $each_index_choice) {
 331             if ($each_index_choice === 'PRIMARY'
 332              && $this->_choice !== 'PRIMARY'
 333              && PMA_Index::getPrimary($this->_table, $this->_schema)) {
 334                 // skip PRIMARY if there is already one in the table
 335                 continue;
 336             }
 337             $html_options .= '<option value="' . $each_index_choice . '"'
 338                  . (($this->_choice == $each_index_choice) ? ' selected="selected"' : '')
 339                  . '>'. $each_index_choice . '</option>' . "\n";
 340         }
 341
 342         return $html_options;
 343     }
 344
 345     public function getPacked()
 346     {
 347         return $this->_packed;
 348     }
 349
 350     public function isPacked($as_text = false)
 351     {
 352         if ($as_text) {
 353             $r = array(
 354                 '0' => $GLOBALS['strNo'],
 355                 '1' => $GLOBALS['strYes'],
 356             );
 357         } else {
 358             $r = array(
 359                 '0' => false,
 360                 '1' => true,
 361             );
 362         }
 363
 364         if (null === $this->_packed) {
 365             return $r[0];
 366         }
 367
 368         return $this->_packed;
 369     }
 370
 371     public function getNonUnique()
 372     {
 373         return $this->_non_unique;
 374     }
 375
 376     public function isUnique($as_text = false)
 377     {
 378         if ($as_text) {
 379             $r = array(
 380                 '0' => $GLOBALS['strYes'],
 381                 '1' => $GLOBALS['strNo'],
 382             );
 383         } else {
 384             $r = array(
 385                 '0' => true,
 386                 '1' => false,
 387             );
 388         }
 389
 390         return $r[$this->_non_unique];
 391     }
 392
 393     public function getName()
 394     {
 395         return $this->_name;
 396     }
 397
 398     public function setName($name)
 399     {
 400         $this->_name = (string) $name;
 401     }
 402
 403     public function getColumns()
 404     {
 405         return $this->_columns;
 406     }
 407
 408     /**
 409      * Show index data
 410      *
 411      * @param   string      $table          The tablename
 412      * @param   array       $indexes_info   Referenced info array
 413      * @param   array       $indexes_data   Referenced data array
 414      * @param   boolean     $print_mode
 415      * @access  public
 416      * @return  array       Index collection array
 417      * @author  Garvin Hicking (pma@supergarv.de)
 418      */
 419     static public function getView($table, $schema, $print_mode = false)
 420     {
 421         $indexes = PMA_Index::getFromTable($table, $schema);
 422
 423         if (count($indexes) < 1) {
 424             return PMA_Message::warning('strNoIndex')->getDisplay();
 425         }
 426
 427         $r = '';
 428
 429         $r .= '<h2>' . $GLOBALS['strIndexes'] . ': ';
 430         $r .= PMA_showMySQLDocu('optimization', 'optimizing-database-structure');
 431         $r .= '</h2>';
 432         $r .= '<table>';
 433         $r .= '<thead>';
 434         $r .= '<tr>';
 435         if (! $print_mode) {
 436             $r .= '<th colspan="2">' . $GLOBALS['strAction'] . '</th>';
 437         }
 438         $r .= '<th>' . $GLOBALS['strKeyname'] . '</th>';
 439         $r .= '<th>' . $GLOBALS['strType'] . '</th>';
 440         $r .= '<th>' . $GLOBALS['strUnique'] . '</th>';
 441         $r .= '<th>' . $GLOBALS['strPacked'] . '</th>';
 442         $r .= '<th>' . $GLOBALS['strField'] . '</th>';
 443         $r .= '<th>' . $GLOBALS['strCardinality'] . '</th>';
 444         $r .= '<th>' . $GLOBALS['strCollation'] . '</th>';
 445         $r .= '<th>' . $GLOBALS['strNull'] . '</th>';
 446         $r .= '<th>' . $GLOBALS['strComment'] . '</th>';
 447         $r .= '</tr>';
 448         $r .= '</thead>';
 449         $r .= '<tbody>';
 450
 451         $odd_row = true;
 452         foreach ($indexes as $index) {
 453             $row_span = ' rowspan="' . $index->getColumnCount() . '" ';
 454
 455             $r .= '<tr class="' . ($odd_row ? 'odd' : 'even') . '">';
 456
 457             if (! $print_mode) {
 458                 $this_params = $GLOBALS['url_params'];
 459                 $this_params['index'] = $index->getName();
 460                 $r .= '<td ' . $row_span . '>'
 461                    . '    <a href="tbl_indexes.php' . PMA_generate_common_url($this_params)
 462                    . '">' . PMA_getIcon('b_edit.png', $GLOBALS['strEdit']) . '</a>'
 463                    . '</td>' . "\n";
 464
 465                 $this_params = $GLOBALS['url_params'];
 466                 if ($index->getName() == 'PRIMARY') {
 467                     $this_params['sql_query'] = 'ALTER TABLE ' . PMA_backquote($table) . ' DROP PRIMARY KEY';
 468                     $this_params['zero_rows'] = $GLOBALS['strPrimaryKeyHasBeenDropped'];
 469                     $js_msg      = PMA_jsFormat('ALTER TABLE ' . $table . ' DROP PRIMARY KEY');
 470                 } else {
 471                     $this_params['sql_query'] = 'ALTER TABLE ' . PMA_backquote($table) . ' DROP INDEX ' . PMA_backquote($index->getName());
 472                     $this_params['zero_rows'] = sprintf($GLOBALS['strIndexHasBeenDropped'], $index->getName());
 473                     $js_msg      = PMA_jsFormat('ALTER TABLE ' . $table . ' DROP INDEX ' . $index->getName());
 474                 }
 475
 476                 $r .= '<td ' . $row_span . '>'
 477                    . '    <a href="sql.php' . PMA_generate_common_url($this_params)
 478                    . '" onclick="return confirmLink(this, \'' . $js_msg . '\')">'
 479                    . PMA_getIcon('b_drop.png', $GLOBALS['strDrop'])  . '</a>'
 480                    . '</td>' . "\n";
 481             }
 482
 483             $r .= '<th ' . $row_span . '>' . htmlspecialchars($index->getName()) . '</th>';
 484             $r .= '<td ' . $row_span . '>' . htmlspecialchars($index->getType()) . '</td>';
 485             $r .= '<td ' . $row_span . '>' . $index->isUnique(true) . '</td>';
 486             $r .= '<td ' . $row_span . '>' . $index->isPacked(true) . '</td>';
 487
 488             foreach ($index->getColumns() as $column) {
 489                 if ($column->getSeqInIndex() > 1) {
 490                     $r .= '<tr class="' . ($odd_row ? 'odd' : 'even') . '">';
 491                 }
 492                 $r .= '<td>' . htmlspecialchars($column->getName());
 493                 if ($column->getSubPart()) {
 494                     $r .= ' (' . $column->getSubPart() . ')';
 495                 }
 496                 $r .= '</td>';
 497                 $r .= '<td>' . htmlspecialchars($column->getCardinality()) . '</td>';
 498                 $r .= '<td>' . htmlspecialchars($column->getCollation()) . '</td>';
 499                 $r .= '<td>' . htmlspecialchars($column->getNull()) . '</td>';
 500
 501                 if ($column->getSeqInIndex() == 1) {
 502                     $r .= '<td ' . $row_span . '>'
 503                         . htmlspecialchars($index->getComments()) . '</td>';
 504                 }
 505                 $r .= '</tr>';
 506             } // end foreach $index['Sequences']
 507
 508             $odd_row = ! $odd_row;
 509         } // end while
 510         $r .= '</tbody>';
 511         $r .= '</table>';
 512
 513         if (! $print_mode) {
 514             $r .= PMA_Index::findDuplicates($table, $schema);
 515         }
 516
 517         return $r;
 518     }
 519
 520     public function getCompareData()
 521     {
 522         $data = array(
 523             // 'Non_unique'    => $this->_non_unique,
 524             'Packed'        => $this->_packed,
 525             'Index_type'    => $this->_type,
 526         );
 527
 528         foreach ($this->_columns as $column) {
 529             $data['columns'][] = $column->getCompareData();
 530         }
 531
 532         return $data;
 533     }
 534
 535     /**
 536      * Function to check over array of indexes and look for common problems
 537      *
 538      * @uses    $GLOBALS['strIndexesSeemEqual']
 539      * @uses    is_string()
 540      * @uses    is_array()
 541      * @uses    count()
 542      * @uses    array_pop()
 543      * @uses    reset()
 544      * @uses    current()
 545      * @access  public
 546      * @param   string      name of table
 547      * @return  string      Output HTML
 548      */
 549     static public function findDuplicates($table, $schema)
 550     {
 551         $indexes = PMA_Index::getFromTable($table, $schema);
 552
 553         $output  = '';
 554
 555         // count($indexes) < 2:
 556         //   there is no need to check if there less than two indexes
 557         if (count($indexes) < 2) {
 558             return $output;
 559         }
 560
 561         // remove last index from stack and ...
 562         while ($while_index = array_pop($indexes)) {
 563             // ... compare with every remaining index in stack
 564             foreach ($indexes as $each_index) {
 565                 if ($each_index->getCompareData() !== $while_index->getCompareData()) {
 566                     continue;
 567                 }
 568
 569                 // did not find any difference
 570                 // so it makes no sense to have this two equal indexes
 571
 572                 $message = PMA_Message::warning('strIndexesSeemEqual');
 573                 $message->addParam($each_index->getName());
 574                 $message->addParam($while_index->getName());
 575                 $output .= $message->getDisplay();
 576
 577                 // there is no need to check any further indexes if we have already
 578                 // found that this one has a duplicate
 579                 continue 2;
 580             }
 581         }
 582         return $output;
 583     }
 584 }
 585
 586 /**
 587  * @package phpMyAdmin
 588  */
 589 class PMA_Index_Column
 590 {
 591     /**
 592      * @var string The column name
 593      */
 594     protected $_name = '';
 595
 596     /**
 597      * @var integer The column sequence number in the index, starting with 1.
 598      */
 599     protected $_seq_in_index = 1;
 600
 601     /**
 602      * @var string How the column is sorted in the index. “A” (Ascending) or NULL (Not sorted)
 603      */
 604     protected $_collation = null;
 605
 606     /**
 607      * The number of indexed characters if the column is only partly indexed,
 608      * NULL if the entire column is indexed.
 609      *
 610      * @var integer
 611      */
 612     protected $_sub_part = null;
 613
 614     /**
 615      * Contains YES if the column may contain NULL.
 616      * If not, the column contains NO.
 617      *
 618      * @var string
 619      */
 620     protected $_null = '';
 621
 622     /**
 623      * An estimate of the number of unique values in the index. This is updated
 624      * by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on
 625      * statistics stored as integers, so the value is not necessarily exact even
 626      * for small tables. The higher the cardinality, the greater the chance that
 627      * MySQL uses the index when doing joins.
 628      *
 629      * @var integer
 630      */
 631     protected $_cardinality = 0;
 632
 633     public function __construct($params = array())
 634     {
 635         $this->set($params);
 636     }
 637
 638     public function set($params)
 639     {
 640         if (isset($params['Column_name'])) {
 641             $this->_name = $params['Column_name'];
 642         }
 643         if (isset($params['Seq_in_index'])) {
 644             $this->_seq_in_index = $params['Seq_in_index'];
 645         }
 646         if (isset($params['Collation'])) {
 647             $this->_collation = $params['Collation'];
 648         }
 649         if (isset($params['Cardinality'])) {
 650             $this->_cardinality = $params['Cardinality'];
 651         }
 652         if (isset($params['Sub_part'])) {
 653             $this->_sub_part = $params['Sub_part'];
 654         }
 655         if (isset($params['Null'])) {
 656             $this->_null = $params['Null'];
 657         }
 658     }
 659
 660     public function getName()
 661     {
 662         return $this->_name;
 663     }
 664
 665     public function getCollation()
 666     {
 667         return $this->_collation;
 668     }
 669
 670     public function getCardinality()
 671     {
 672         return $this->_cardinality;
 673     }
 674
 675     public function getNull()
 676     {
 677         return $this->_null;
 678     }
 679
 680     public function getSeqInIndex()
 681     {
 682         return $this->_seq_in_index;
 683     }
 684
 685     public function getSubPart()
 686     {
 687         return $this->_sub_part;
 688     }
 689
 690     public function getCompareData()
 691     {
 692         return array(
 693             'Column_name'   => $this->_name,
 694             'Seq_in_index'  => $this->_seq_in_index,
 695             'Collation'     => $this->_collation,
 696             'Sub_part'      => $this->_sub_part,
 697             'Null'          => $this->_null,
 698         );
 699     }
 700 }
 701 ?>