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