libraries/tbl_indexes.lib.php

   1 <?php
   2 /* $Id$ */
   3 // vim: expandtab sw=4 ts=4 sts=4:
   4 /**
   5  * function library for handling table indexes
   6  */
   7
   8 /**
   9  * Return a list of all index types
  10  *
  11  * @access  public
  12  * @return  array       Index types
  13  * @author  Garvin Hicking (pma@supergarv.de)
  14  */
  15 function PMA_get_indextypes() {
  16     return array(
  17         'PRIMARY',
  18         'INDEX',
  19         'UNIQUE',
  20         'FULLTEXT'
  21     );
  22 }
  23
  24 /**
  25  * Function to get all index information from a certain table
  26  *
  27  * @param   string      Table name
  28  * @param   string      Error URL
  29  *
  30  * @access  public
  31  * @return  array       Index keys
  32  */
  33 function PMA_get_indexes($tbl_name, $err_url_0 = '') {
  34     $tbl_local_query = 'SHOW KEYS FROM ' . PMA_backquote($tbl_name);
  35     $tbl_result      = PMA_DBI_query($tbl_local_query) or PMA_mysqlDie('', $tbl_local_query, '', $err_url_0);
  36     $tbl_ret_keys    = array();
  37     while ($tbl_row = PMA_DBI_fetch_assoc($tbl_result)) {
  38         $tbl_ret_keys[]  = $tbl_row;
  39     }
  40     PMA_DBI_free_result($tbl_result);
  41
  42     return $tbl_ret_keys;
  43 }
  44
  45 /**
  46  * Function to check over array of indexes and look for common problems
  47  *
  48  * @param   array       Array of indexes
  49  * @param   boolean     Whether to output HTML in table layout
  50  *
  51  * @access  public
  52  * @return  string      Output HTML
  53  * @author  Garvin Hicking (pma@supergarv.de)
  54  */
  55 function PMA_check_indexes($idx_collection, $table = true) {
  56     $index_types = PMA_get_indextypes();
  57     $output  = '';
  58
  59     if ( ! is_array($idx_collection) || empty($idx_collection['ALL'])) {
  60         return $output;
  61     }
  62
  63     foreach ($idx_collection['ALL'] AS $w_keyname => $w_count) {
  64         if (isset($idx_collection['PRIMARY'][$w_keyname]) && (isset($idx_collection['INDEX'][$w_keyname]) || isset($idx_collection['UNIQUE'][$w_keyname]))) {
  65             $output .= PMA_index_warning(sprintf($GLOBALS['strIndexWarningPrimary'], htmlspecialchars($w_keyname)), $table);
  66         } elseif (isset($idx_collection['UNIQUE'][$w_keyname]) && isset($idx_collection['INDEX'][$w_keyname])) {
  67             $output .= PMA_index_warning(sprintf($GLOBALS['strIndexWarningUnique'], htmlspecialchars($w_keyname)), $table);
  68         }
  69
  70         foreach ($index_types AS $index_type) {
  71             if (isset($idx_collection[$index_type][$w_keyname]) && $idx_collection[$index_type][$w_keyname] > 1) {
  72                 $output .= PMA_index_warning(sprintf($GLOBALS['strIndexWarningMultiple'], $index_type, htmlspecialchars($w_keyname)), $table);
  73             }
  74         }
  75     }
  76
  77     return $output;
  78 }
  79
  80 /**
  81  * Loop array of returned index keys and extract key information to
  82  * seperate arrays. Those arrays are passed by reference.
  83  *
  84  * @param   array       Referenced Array of indexes
  85  * @param   array       Referenced return array
  86  * @param   array       Referenced return array
  87  * @param   array       Referenced return array
  88  *
  89  * @access  public
  90  * @return  boolean     void
  91  * @author  Garvin Hicking (pma@supergarv.de)
  92  */
  93 function PMA_extract_indexes(&$ret_keys, &$indexes, &$indexes_info, &$indexes_data) {
  94     if (!is_array($ret_keys)) {
  95         return false;
  96     }
  97
  98     $prev_index   = '';
  99     foreach ($ret_keys as $row) {
 100         if ($row['Key_name'] != $prev_index ){
 101             $indexes[]  = $row['Key_name'];
 102             $prev_index = $row['Key_name'];
 103         }
 104
 105         $indexes_info[$row['Key_name']]['Sequences'][]     = $row['Seq_in_index'];
 106         $indexes_info[$row['Key_name']]['Non_unique']      = $row['Non_unique'];
 107
 108         if (isset($row['Cardinality'])) {
 109             $indexes_info[$row['Key_name']]['Cardinality'] = $row['Cardinality'];
 110         }
 111
 112         //    I don't know what does following column mean....
 113         //    $indexes_info[$row['Key_name']]['Packed']          = $row['Packed'];
 114         $indexes_info[$row['Key_name']]['Comment']         = (isset($row['Comment']))
 115                                                            ? $row['Comment']
 116                                                            : '';
 117         $indexes_info[$row['Key_name']]['Index_type']      = (isset($row['Index_type']))
 118                                                            ? $row['Index_type']
 119                                                            : '';
 120
 121         $indexes_data[$row['Key_name']][$row['Seq_in_index']]['Column_name']  = $row['Column_name'];
 122         if (isset($row['Sub_part'])) {
 123             $indexes_data[$row['Key_name']][$row['Seq_in_index']]['Sub_part'] = $row['Sub_part'];
 124         }
 125     } // end while
 126
 127     return true;
 128 }
 129
 130 /**
 131  * Show index data and prepare returned collection array for index
 132  * key checks.
 133  *
 134  * @param   string      $table          The tablename
 135  * @param   array       $indexes        Referenced Array of indexes
 136  * @param   array       $indexes_info   Referenced info array
 137  * @param   array       $indexes_data   Referenced data array
 138  * @param   boolean     $display_html   Output HTML code, or just return collection array?
 139  * @param   boolean     $print_mode
 140  * @access  public
 141  * @return  array       Index collection array
 142  * @author  Garvin Hicking (pma@supergarv.de)
 143  */
 144 function PMA_show_indexes($table, &$indexes, &$indexes_info, &$indexes_data, $display_html = true, $print_mode = false) {
 145     $idx_collection = array();
 146     $odd_row = true;
 147     foreach ($indexes as $index_name) {
 148         if ($display_html) {
 149             $row_span = ' rowspan="' . count($indexes_info[$index_name]['Sequences']) . '" ';
 150
 151             echo '        <tr class="' . ( $odd_row ? 'odd' : 'even' ) . '">' . "\n";
 152             echo '            <th ' . $row_span . '>' . "\n"
 153                . '                ' . htmlspecialchars($index_name) . "\n"
 154                . '            </th>' . "\n";
 155         }
 156
 157         if ((PMA_MYSQL_INT_VERSION < 40002 && $indexes_info[$index_name]['Comment'] == 'FULLTEXT')
 158             || (PMA_MYSQL_INT_VERSION >= 40002 && $indexes_info[$index_name]['Index_type'] == 'FULLTEXT')) {
 159             $index_type = 'FULLTEXT';
 160         } elseif ($index_name == 'PRIMARY') {
 161             $index_type = 'PRIMARY';
 162         } elseif ($indexes_info[$index_name]['Non_unique'] == '0') {
 163             $index_type = 'UNIQUE';
 164         } else {
 165             $index_type = 'INDEX';
 166         }
 167
 168         if ($display_html) {
 169             echo '            <td ' . $row_span . '>' . "\n"
 170                . '                ' . $index_type . '</td>' . "\n";
 171
 172             echo '            <td ' . $row_span . ' align="right">' . "\n"
 173                . '                ' . (isset($indexes_info[$index_name]['Cardinality']) ? $indexes_info[$index_name]['Cardinality'] : $GLOBALS['strNone']) . '&nbsp;' . "\n"
 174                . '            </td>' . "\n";
 175
 176             if (!$print_mode) {
 177                 echo '            <td ' . $row_span . '>' . "\n"
 178                    . '                <a href="tbl_indexes.php?' . $GLOBALS['url_query'] . '&amp;index=' . urlencode($index_name) . '">' . $GLOBALS['edit_link_text'] . '</a>' . "\n"
 179                    . '            </td>' . "\n";
 180
 181                 if ($index_name == 'PRIMARY') {
 182                     $local_query = urlencode('ALTER TABLE ' . PMA_backquote($table) . ' DROP PRIMARY KEY');
 183                     $js_msg      = 'ALTER TABLE ' . PMA_jsFormat($table) . ' DROP PRIMARY KEY';
 184                     $zero_rows   = urlencode($GLOBALS['strPrimaryKeyHasBeenDropped']);
 185                 } else {
 186                     $local_query = urlencode('ALTER TABLE ' . PMA_backquote($table) . ' DROP INDEX ' . PMA_backquote($index_name));
 187                     $js_msg      = 'ALTER TABLE ' . PMA_jsFormat($table) . ' DROP INDEX ' . PMA_jsFormat($index_name);
 188                     $zero_rows   = urlencode(sprintf($GLOBALS['strIndexHasBeenDropped'], htmlspecialchars($index_name)));
 189                 }
 190
 191                 echo '            <td ' . $row_span . '>' . "\n"
 192                    . '                <a href="sql.php?' . $GLOBALS['url_query'] . '&amp;sql_query=' . $local_query . '&amp;zero_rows=' . $zero_rows . '" onclick="return confirmLink(this, \'' . $js_msg . '\')">' . $GLOBALS['drop_link_text']  . '</a>' . "\n"
 193                    . '            </td>' . "\n";
 194             }
 195         }
 196
 197         foreach ($indexes_info[$index_name]['Sequences'] AS $row_no => $seq_index) {
 198             $col_name = $indexes_data[$index_name][$seq_index]['Column_name'];
 199             if ($row_no == 0) {
 200                 if (isset($idx_collection[$index_type][$col_name])) {
 201                     $idx_collection[$index_type][$col_name]++;
 202                 } else {
 203                     $idx_collection[$index_type][$col_name] = 1;
 204                 }
 205
 206                 if (isset($idx_collection['ALL'][$col_name])) {
 207                     $idx_collection['ALL'][$col_name]++;
 208                 } else {
 209                     $idx_collection['ALL'][$col_name] = 1;
 210                 }
 211             }
 212
 213             if ($display_html) {
 214                 if ($row_no > 0) {
 215                     echo '        <tr class="' . ( $odd_row ? 'odd' : 'even' ) . '">' . "\n";
 216                 }
 217
 218                 if ( isset($indexes_data[$index_name][$seq_index]['Sub_part'])
 219                   && strlen($indexes_data[$index_name][$seq_index]['Sub_part']) ) {
 220                     echo '            <td>' . $col_name . '</td>' . "\n";
 221                     echo '            <td align="right">' . "\n"
 222                        . '                ' . $indexes_data[$index_name][$seq_index]['Sub_part'] . "\n"
 223                        . '            </td>' . "\n";
 224                     echo '        </tr>' . "\n";
 225                 } else {
 226                     echo '            <td colspan="2">' . "\n"
 227                        . '                ' . htmlspecialchars($col_name) . "\n"
 228                        . '            </td>' . "\n";
 229                     echo '        </tr>' . "\n";
 230                 }
 231             }
 232         } // end foreach $indexes_info[$index_name]['Sequences']
 233
 234         $odd_row = ! $odd_row;
 235     } // end while
 236
 237     return $idx_collection;
 238 }
 239
 240 /**
 241  * Function to emit a index warning
 242  *
 243  * @author  Garvin Hicking (pma@supergarv.de)
 244  * @access  public
 245  * @param   string      $string     Message string
 246  * @param   boolean     $table      Whether to output HTML in table layout
 247  * @return  string      Output HTML
 248  */
 249 function PMA_index_warning($string, $table = true) {
 250     $output = '<div class="warning">' . $string . '</div>';
 251
 252     if ( $table ) {
 253         $output = '<tr><td colspan=7">' . $output . '</td></tr>';
 254     }
 255
 256     return $output . "\n";
 257 }
 258 ?>