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