libraries/tbl_indexes.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * function library for handling table indexes
   5  *
   6  * @version $Id$
   7  */
   8
   9 /**
  10  * Return a list of all index types
  11  *
  12  * @access  public
  13  * @return  array       Index types
  14  * @author  Garvin Hicking (pma@supergarv.de)
  15  */
  16 function PMA_get_indextypes()
  17 {
  18     return array(
  19         'PRIMARY',
  20         'INDEX',
  21         'UNIQUE',
  22         'FULLTEXT',
  23     );
  24 }
  25
  26 /**
  27  * Function to get all index information from a certain table
  28  *
  29  * @uses    PMA_DBI_fetch_result()
  30  * @uses    PMA_backquote()
  31  * @param   string  $tbl_name   Table name to ftech indexes from
  32  * @param   string  $err_url_0  Error URL
  33  *
  34  * @access  public
  35  * @return  array       Index keys
  36  */
  37 function PMA_get_indexes($tbl_name, $err_url_0 = '')
  38 {
  39     return PMA_DBI_fetch_result('SHOW KEYS FROM ' . PMA_backquote($tbl_name));
  40 }
  41
  42 /**
  43  * Function to check over array of indexes and look for common problems
  44  *
  45  * @uses    $GLOBALS['strIndexesSeemEqual']
  46  * @uses    PMA_get_indexes()
  47  * @uses    is_string()
  48  * @uses    is_array()
  49  * @uses    count()
  50  * @uses    array_pop()
  51  * @uses    reset()
  52  * @uses    current()
  53  * @access  public
  54  * @param   mixed       array of indexes from PMA_get_indexes()
  55  *                      or name of table
  56  * @return  string      Output HTML
  57  */
  58 function PMA_check_indexes($idx_collection)
  59 {
  60     if (is_string($idx_collection)) {
  61         $idx_collection = PMA_get_indexes($idx_collection);
  62     }
  63
  64     // count($idx_collection) < 2:
  65     //   there is no need to check if there less than two indexes
  66     if (! is_array($idx_collection) || count($idx_collection) < 2) {
  67         return false;
  68     }
  69
  70     $indexes = array();
  71     foreach ($idx_collection as $index_field) {
  72         $indexes[$index_field['Key_name']][$index_field['Column_name']]
  73             = $index_field;
  74     }
  75
  76     $output  = '';
  77
  78     // remove last index from stack and ...
  79     while ($while_index = array_pop($indexes)) {
  80         // ... compare with every remaining index in stack
  81         foreach ($indexes as $each_index_name => $each_index) {
  82             if (count($while_index) !== count($each_index)) {
  83                 // number of fields are not equal
  84                 continue;
  85             }
  86
  87             // compare some key elements of every column in this two indexes
  88             foreach ($each_index as $col_name => $each_index_column) {
  89                 if (! isset($while_index[$col_name])
  90                  // the position
  91                  || $while_index[$col_name]['Seq_in_index'] !== $each_index_column['Seq_in_index']
  92                  // the order, ASC or DESC
  93                  || $while_index[$col_name]['Collation']    !== $each_index_column['Collation']
  94                  // the length
  95                  || $while_index[$col_name]['Sub_part']     !== $each_index_column['Sub_part']
  96                  // BTREE or HASH
  97                  || $while_index[$col_name]['Index_type']   !== $each_index_column['Index_type']) {
  98                     continue 2;
  99                 }
 100             }
 101
 102             // did not find any difference
 103             // so it makes no sense to have this two equal indexes
 104
 105             // use first column from index to fetch index name
 106             reset($while_index);
 107             $first_column = current($while_index);
 108
 109             $output .= '<div class="warning">';
 110             $output .= $GLOBALS['strIndexesSeemEqual'] . ' ';
 111             $output .= $each_index_name . ', ' . $first_column['Key_name'];
 112             $output .= '</div>';
 113
 114             // there is no need to check any further indexes if we have already
 115             // found that this one has a duplicate
 116             continue 2;
 117         }
 118     }
 119
 120     if ($output) {
 121         $output = '<tr><td colspan=7">' . $output . '</td></tr>';
 122     }
 123
 124     return $output;
 125 }
 126
 127 /**
 128  * Loop array of returned index keys and extract key information to
 129  * seperate arrays. Those arrays are passed by reference.
 130  *
 131  * @param   array       Referenced Array of indexes
 132  * @param   array       Referenced return array
 133  * @param   array       Referenced return array
 134  * @param   array       Referenced return array
 135  *
 136  * @access  public
 137  * @return  boolean     void
 138  * @author  Garvin Hicking (pma@supergarv.de)
 139  */
 140 function PMA_extract_indexes(&$ret_keys, &$indexes, &$indexes_info, &$indexes_data)
 141 {
 142     if (! is_array($ret_keys)) {
 143         return false;
 144     }
 145
 146     $prev_index   = '';
 147     foreach ($ret_keys as $row) {
 148         if ($row['Key_name'] != $prev_index){
 149             $indexes[]  = $row['Key_name'];
 150             $prev_index = $row['Key_name'];
 151         }
 152
 153         $indexes_info[$row['Key_name']]['Sequences'][]     = $row['Seq_in_index'];
 154         $indexes_info[$row['Key_name']]['Non_unique']      = $row['Non_unique'];
 155
 156         if (isset($row['Cardinality'])) {
 157             $indexes_info[$row['Key_name']]['Cardinality'] = $row['Cardinality'];
 158         }
 159
 160         //    I don't know what does following column mean....
 161         //    $indexes_info[$row['Key_name']]['Packed']          = $row['Packed'];
 162         $indexes_info[$row['Key_name']]['Comment']         = (isset($row['Comment']))
 163                                                            ? $row['Comment']
 164                                                            : '';
 165         $indexes_info[$row['Key_name']]['Index_type']      = (isset($row['Index_type']))
 166                                                            ? $row['Index_type']
 167                                                            : '';
 168
 169         $indexes_data[$row['Key_name']][$row['Seq_in_index']]['Column_name']  = $row['Column_name'];
 170         if (isset($row['Sub_part'])) {
 171             $indexes_data[$row['Key_name']][$row['Seq_in_index']]['Sub_part'] = $row['Sub_part'];
 172         }
 173     } // end while
 174
 175     return true;
 176 }
 177
 178 /**
 179  * Show index data and prepare returned collection array for index
 180  * key checks.
 181  *
 182  * @param   string      $table          The tablename
 183  * @param   array       $indexes        Referenced Array of indexes
 184  * @param   array       $indexes_info   Referenced info array
 185  * @param   array       $indexes_data   Referenced data array
 186  * @param   boolean     $display_html   Output HTML code, or just return collection array?
 187  * @param   boolean     $print_mode
 188  * @access  public
 189  * @return  array       Index collection array
 190  * @author  Garvin Hicking (pma@supergarv.de)
 191  */
 192 function PMA_show_indexes($table, &$indexes, &$indexes_info, &$indexes_data,
 193     $display_html = true, $print_mode = false)
 194 {
 195     $idx_collection = array();
 196     $odd_row = true;
 197     foreach ($indexes as $index_name) {
 198         if ($display_html) {
 199             $row_span = ' rowspan="' . count($indexes_info[$index_name]['Sequences']) . '" ';
 200
 201             echo '        <tr class="' . ($odd_row ? 'odd' : 'even') . '">' . "\n";
 202             echo '            <th ' . $row_span . '>' . "\n"
 203                . '                ' . htmlspecialchars($index_name) . "\n"
 204                . '            </th>' . "\n";
 205         }
 206
 207         if ((PMA_MYSQL_INT_VERSION < 40002 && $indexes_info[$index_name]['Comment'] == 'FULLTEXT')
 208             || (PMA_MYSQL_INT_VERSION >= 40002 && $indexes_info[$index_name]['Index_type'] == 'FULLTEXT')) {
 209             $index_type = 'FULLTEXT';
 210         } elseif ($index_name == 'PRIMARY') {
 211             $index_type = 'PRIMARY';
 212         } elseif ($indexes_info[$index_name]['Non_unique'] == '0') {
 213             $index_type = 'UNIQUE';
 214         } else {
 215             $index_type = 'INDEX';
 216         }
 217
 218         if ($display_html) {
 219             echo '            <td ' . $row_span . '>' . "\n"
 220                . '                ' . $index_type . '</td>' . "\n";
 221
 222             echo '            <td ' . $row_span . ' align="right">' . "\n"
 223                . '                ' . (isset($indexes_info[$index_name]['Cardinality']) ? $indexes_info[$index_name]['Cardinality'] : $GLOBALS['strNone']) . '&nbsp;' . "\n"
 224                . '            </td>' . "\n";
 225
 226             if (!$print_mode) {
 227                 echo '            <td ' . $row_span . '>' . "\n"
 228                    . '                <a href="tbl_indexes.php?'
 229                    . $GLOBALS['url_query'] . '&amp;index=' . urlencode($index_name)
 230                    . '">' . $GLOBALS['edit_link_text'] . '</a>' . "\n"
 231                    . '            </td>' . "\n";
 232
 233                 if ($index_name == 'PRIMARY') {
 234                     $local_query = urlencode('ALTER TABLE ' . PMA_backquote($table) . ' DROP PRIMARY KEY');
 235                     $js_msg      = 'ALTER TABLE ' . PMA_jsFormat($table) . ' DROP PRIMARY KEY';
 236                     $zero_rows   = urlencode($GLOBALS['strPrimaryKeyHasBeenDropped']);
 237                 } else {
 238                     $local_query = urlencode('ALTER TABLE ' . PMA_backquote($table) . ' DROP INDEX ' . PMA_backquote($index_name));
 239                     $js_msg      = 'ALTER TABLE ' . PMA_jsFormat($table) . ' DROP INDEX ' . PMA_jsFormat($index_name);
 240                     $zero_rows   = urlencode(sprintf($GLOBALS['strIndexHasBeenDropped'], htmlspecialchars($index_name)));
 241                 }
 242
 243                 echo '            <td ' . $row_span . '>' . "\n"
 244                    . '                <a href="sql.php?' . $GLOBALS['url_query']
 245                    . '&amp;sql_query=' . $local_query . '&amp;zero_rows='
 246                    . $zero_rows . '" onclick="return confirmLink(this, \''
 247                    . $js_msg . '\')">' . $GLOBALS['drop_link_text']  . '</a>' . "\n"
 248                    . '            </td>' . "\n";
 249             }
 250         }
 251
 252         foreach ($indexes_info[$index_name]['Sequences'] AS $row_no => $seq_index) {
 253             $col_name = $indexes_data[$index_name][$seq_index]['Column_name'];
 254             if ($row_no == 0) {
 255                 if (isset($idx_collection[$index_type][$col_name])) {
 256                     $idx_collection[$index_type][$col_name]++;
 257                 } else {
 258                     $idx_collection[$index_type][$col_name] = 1;
 259                 }
 260
 261                 if (isset($idx_collection['ALL'][$col_name])) {
 262                     $idx_collection['ALL'][$col_name]++;
 263                 } else {
 264                     $idx_collection['ALL'][$col_name] = 1;
 265                 }
 266             }
 267
 268             if ($display_html) {
 269                 if ($row_no > 0) {
 270                     echo '        <tr class="' . ($odd_row ? 'odd' : 'even') . '">' . "\n";
 271                 }
 272
 273                 if (isset($indexes_data[$index_name][$seq_index]['Sub_part'])
 274                  && strlen($indexes_data[$index_name][$seq_index]['Sub_part'])) {
 275                     echo '            <td>' . $col_name . '</td>' . "\n";
 276                     echo '            <td align="right">' . "\n"
 277                        . '                ' . $indexes_data[$index_name][$seq_index]['Sub_part'] . "\n"
 278                        . '            </td>' . "\n";
 279                     echo '        </tr>' . "\n";
 280                 } else {
 281                     echo '            <td colspan="2">' . "\n"
 282                        . '                ' . htmlspecialchars($col_name) . "\n"
 283                        . '            </td>' . "\n";
 284                     echo '        </tr>' . "\n";
 285                 }
 286             }
 287         } // end foreach $indexes_info[$index_name]['Sequences']
 288
 289         $odd_row = ! $odd_row;
 290     } // end while
 291
 292     return $idx_collection;
 293 }
 294
 295 ?>