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