interface/main/myadmin/tbl_indexes.php

   1 <?php
   2 /* $Id$ */
   3 // vim: expandtab sw=4 ts=4 sts=4:
   4
   5
   6 /**
   7  * Gets some core libraries
   8  */
   9 require_once('./libraries/grab_globals.lib.php');
  10 require_once('./libraries/common.lib.php');
  11
  12
  13 /**
  14  * Defines the index types ("FULLTEXT" is available since MySQL 3.23.23)
  15  */
  16 $index_types_cnt   = 4;
  17 $index_types       = array(
  18     'PRIMARY',
  19     'INDEX',
  20     'UNIQUE',
  21     'FULLTEXT'
  22 );
  23
  24 /**
  25  * Ensures the db & table are valid, then loads headers and gets indexes
  26  * informations.
  27  * Skipped if this script is called by "tbl_properties.php"
  28  */
  29 if (!defined('PMA_IDX_INCLUDED')) {
  30     // Not a valid db name -> back to the welcome page
  31     if (!empty($db)) {
  32         $is_db = @PMA_mysql_select_db($db);
  33     }
  34     if (empty($db) || !$is_db) {
  35         header('Location: ' . $cfg['PmaAbsoluteUri'] . 'main.php?' . PMA_generate_common_url('', '', '&') . (isset($message) ? '&message=' . urlencode($message) : '') . '&reload=1');
  36         exit;
  37     }
  38     // Not a valid table name -> back to the default db_details sub-page
  39     if (!empty($table)) {
  40         $is_table = @PMA_mysql_query('SHOW TABLES LIKE \'' . PMA_sqlAddslashes($table, TRUE) . '\'');
  41     }
  42     if (empty($table)
  43         || !($is_table && @mysql_numrows($is_table))) {
  44         header('Location: ' . $cfg['PmaAbsoluteUri'] . $cfg['DefaultTabDatabase'] . '?' . PMA_generate_common_url($db, '', '&') . (isset($message) ? '&message=' . urlencode($message) : '') . '&reload=1');
  45         exit;
  46     } else if (isset($is_table)) {
  47         mysql_free_result($is_table);
  48     }
  49
  50     // Displays headers (if needed)
  51     $js_to_run = ((isset($index) && isset($do_save_data)) ? 'functions.js' : 'indexes.js');
  52     require_once('./header.inc.php');
  53 } // end if
  54
  55
  56 /**
  57  * Gets fields and indexes informations
  58  */
  59 if (defined('PMA_IDX_INCLUDED')) {
  60     $err_url_0 = 'db_details.php?' . PMA_generate_common_url($db);
  61 }
  62
  63 //  Gets table keys and store them in arrays
  64 $indexes      = array();
  65 $prev_index   = '';
  66 $indexes_info = array();
  67 $indexes_data = array();
  68 // keys had already been grabbed in "tbl_properties.php"
  69 if (defined('PMA_IDX_INCLUDED')) {
  70     $idx_cnt     = count($ret_keys);
  71 } else {
  72     $local_query = 'SHOW KEYS FROM ' . PMA_backquote($table);
  73     $result      = PMA_mysql_query($local_query) or PMA_mysqlDie('', $local_query, '', $err_url_0);
  74     $idx_cnt     = mysql_num_rows($result);
  75 }
  76
  77 for ($i = 0; $i < $idx_cnt; $i++) {
  78     $row = (defined('PMA_IDX_INCLUDED') ? $ret_keys[$i] : PMA_mysql_fetch_array($result));
  79
  80     if ($row['Key_name'] != $prev_index ){
  81         $indexes[]  = $row['Key_name'];
  82         $prev_index = $row['Key_name'];
  83     }
  84     $indexes_info[$row['Key_name']]['Sequences'][]     = $row['Seq_in_index'];
  85     $indexes_info[$row['Key_name']]['Non_unique']      = $row['Non_unique'];
  86     if (isset($row['Cardinality'])) {
  87         $indexes_info[$row['Key_name']]['Cardinality'] = $row['Cardinality'];
  88     }
  89 //    I don't know what does following column mean....
  90 //    $indexes_info[$row['Key_name']]['Packed']          = $row['Packed'];
  91     $indexes_info[$row['Key_name']]['Comment']         = (isset($row['Comment']))
  92                                                        ? $row['Comment']
  93                                                        : '';
  94     $indexes_info[$row['Key_name']]['Index_type']      = (isset($row['Index_type']))
  95                                                        ? $row['Index_type']
  96                                                        : '';
  97
  98     $indexes_data[$row['Key_name']][$row['Seq_in_index']]['Column_name']  = $row['Column_name'];
  99     if (isset($row['Sub_part'])) {
 100         $indexes_data[$row['Key_name']][$row['Seq_in_index']]['Sub_part'] = $row['Sub_part'];
 101     }
 102 } // end while
 103
 104 if (defined('PMA_IDX_INCLUDED')) {
 105     unset($ret_keys);
 106 } else if ($result) {
 107     mysql_free_result($result);
 108 }
 109
 110 // Get fields and stores their name/type
 111 // fields had already been grabbed in "tbl_properties.php"
 112 if (defined('PMA_IDX_INCLUDED')) {
 113     mysql_data_seek($fields_rs, 0);
 114 } else {
 115     $local_query = 'SHOW FIELDS FROM ' . PMA_backquote($table);
 116     $fields_rs   = PMA_mysql_query($local_query) or PMA_mysqlDie('', $local_query, '', $err_url_0);
 117     $fields_cnt  = mysql_num_rows($fields_rs);
 118 }
 119
 120 $fields_names           = array();
 121 $fields_types           = array();
 122 while ($row = PMA_mysql_fetch_array($fields_rs)) {
 123     $fields_names[]     = $row['Field'];
 124     // loic1: set or enum types: slashes single quotes inside options
 125     if (preg_match('@^(set|enum)\((.+)\)$@i', $row['Type'], $tmp)) {
 126         $tmp[2]         = substr(preg_replace('@([^,])\'\'@', '\\1\\\'', ',' . $tmp[2]), 1);
 127         $fields_types[] = $tmp[1] . '(' . str_replace(',', ', ', $tmp[2]) . ')';
 128     } else {
 129         $fields_types[] = $row['Type'];
 130     }
 131 } // end while
 132
 133 if ($fields_rs) {
 134     mysql_free_result($fields_rs);
 135 }
 136
 137
 138 /**
 139  * Do run the query to build the new index and moves back to
 140  * "tbl_properties.php"
 141  */
 142 if (!defined('PMA_IDX_INCLUDED')
 143     && (isset($index) && isset($do_save_data))) {
 144
 145     $err_url     = 'tbl_indexes.php?' . PMA_generate_common_url($db, $table);
 146     if (empty($old_index)) {
 147         $err_url .= '&amp;create_index=1&amp;idx_num_fields=' . $idx_num_fields;
 148     } else {
 149         $err_url .= '&amp;index=' . urlencode($old_index);
 150     }
 151
 152     if ($index_type == 'PRIMARY') {
 153         if ($index == '') {
 154             $index = 'PRIMARY';
 155         } else if ($index != 'PRIMARY') {
 156             PMA_mysqlDie($strPrimaryKeyName, '', FALSE, $err_url);
 157         }
 158     } else if ($index == 'PRIMARY') {
 159          PMA_mysqlDie($strCantRenameIdxToPrimary, '', FALSE, $err_url);
 160     }
 161
 162
 163     // $sql_query is the one displayed in the query box
 164     $sql_query         = 'ALTER TABLE ' . PMA_backquote($table);
 165
 166     // Drops the old index
 167     if (!empty($old_index)) {
 168         if ($old_index == 'PRIMARY') {
 169             $sql_query .= ' DROP PRIMARY KEY,';
 170         } else {
 171             $sql_query .= ' DROP INDEX ' . PMA_backquote($old_index) .',';
 172         }
 173     } // end if
 174
 175     // Builds the new one
 176     switch ($index_type) {
 177         case 'PRIMARY':
 178             $sql_query .= ' ADD PRIMARY KEY (';
 179             break;
 180         case 'FULLTEXT':
 181             $sql_query .= ' ADD FULLTEXT ' . (empty($index) ? '' : PMA_backquote($index)) . ' (';
 182             break;
 183         case 'UNIQUE':
 184             $sql_query .= ' ADD UNIQUE ' . (empty($index) ? '' : PMA_backquote($index)) . ' (';
 185             break;
 186         case 'INDEX':
 187             $sql_query .= ' ADD INDEX ' . (empty($index) ? '' : PMA_backquote($index)) . ' (';
 188             break;
 189     } // end switch
 190     $index_fields         = '';
 191     foreach($column AS $i => $name) {
 192         if ($name != '--ignore--') {
 193             $index_fields .= (empty($index_fields) ? '' : ',')
 194                           . PMA_backquote($name)
 195                           . (empty($sub_part[$i]) ? '' : '(' . $sub_part[$i] . ')');
 196         }
 197     } // end while
 198     if (empty($index_fields)){
 199         PMA_mysqlDie($strNoIndexPartsDefined, '', FALSE, $err_url);
 200     } else {
 201         $sql_query .= $index_fields . ')';
 202     }
 203
 204     $result    = PMA_mysql_query($sql_query) or PMA_mysqlDie('', $sql_query, FALSE, $err_url);
 205     $message   = $strTable . ' ' . htmlspecialchars($table) . ' ' . $strHasBeenAltered;
 206
 207     $active_page = 'tbl_properties_structure.php';
 208     require('./tbl_properties_structure.php');
 209 } // end builds the new index
 210
 211
 212 /**
 213  * Edits an index or defines a new one
 214  */
 215 else if (!defined('PMA_IDX_INCLUDED')
 216          && (isset($index) || isset($create_index))) {
 217
 218     // Prepares the form values
 219     if (!isset($index)) {
 220         $index                                = '';
 221     }
 222     if (!isset($old_index)){
 223         $old_index                            = $index;
 224     }
 225     if (!isset($index_type)) {
 226         $index_type                           = '';
 227     }
 228     if ($old_index == '' || !isset($indexes_info[$old_index])) {
 229         $edited_index_info['Sequences']       = array();
 230         $edited_index_data                    = array();
 231         for ($i = 1; $i <= $idx_num_fields; $i++) {
 232             $edited_index_info['Sequences'][] = $i;
 233             $edited_index_data[$i]            = array('Column_name' => '', 'Sub_part' => '');
 234         } // end for
 235         if ($old_index == ''
 236             && !isset($indexes_info['PRIMARY'])
 237             && ($index_type == '' || $index_type == 'PRIMARY')) {
 238             $old_index                        = 'PRIMARY';
 239         }
 240     } else {
 241         $edited_index_info                    = $indexes_info[$old_index];
 242         $edited_index_data                    = $indexes_data[$old_index];
 243
 244
 245         if ((PMA_MYSQL_INT_VERSION < 40002 && $edited_index_info['Comment'] == 'FULLTEXT')
 246                 || (PMA_MYSQL_INT_VERSION >= 40002 && $edited_index_info['Index_type'] == 'FULLTEXT')) {
 247             $index_type                       = 'FULLTEXT';
 248         } else if ($index == 'PRIMARY') {
 249             $index_type                       = 'PRIMARY';
 250         } else if ($edited_index_info['Non_unique'] == '0') {
 251             $index_type                       = 'UNIQUE';
 252         } else {
 253             $index_type                       = 'INDEX';
 254         }
 255     } // end if... else...
 256
 257     if (isset($add_fields)) {
 258         if (isset($prev_add_fields)) {
 259             $added_fields += $prev_add_fields;
 260         }
 261         $field_cnt = count($edited_index_info['Sequences']) + 1;
 262         for ($i = $field_cnt; $i < ($added_fields + $field_cnt); $i++) {
 263             $edited_index_info['Sequences'][] = $i;
 264             $edited_index_data[$i]            = array('Column_name' => '', 'Sub_part' => '');
 265         } // end for
 266
 267         // Restore entered values
 268         foreach($column AS $i => $name) {
 269             if ($name != '--ignore--'){
 270                 $edited_index_data[$i+1]['Column_name'] = $name;
 271                 $edited_index_data[$i+1]['Sub_part']    = $sub_part[$i];
 272             }
 273         } // end while
 274     } // end if
 275     // end preparing form values
 276     ?>
 277
 278 <!-- Build index form -->
 279 <form action="tbl_indexes.php" method="post" name="index_frm"
 280     onsubmit="if (typeof(this.elements['index'].disabled) != 'undefined') {this.elements['index'].disabled = false}">
 281     <?php echo PMA_generate_common_hidden_inputs($db, $table); ?>
 282     <?php
 283     if (isset($create_index)) {
 284         echo '<input type="hidden" name="create_index" value="1" />';
 285     }
 286     echo "\n";
 287     ?>
 288     <input type="hidden" name="old_index" value="<?php echo (isset($create_index) ? '' : htmlspecialchars($old_index)); ?>" />
 289     <b><?php echo '------ ' . (isset($create_index) ? $strCreateIndexTopic : $strModifyIndexTopic) . ' ------'; ?></b>
 290     <br /><br />
 291
 292     <table border="0">
 293     <tr>
 294         <td><?php echo $strIndexName; ?>&nbsp;</td>
 295         <td>
 296             <input type="text" name="index" value="<?php echo htmlspecialchars($index); ?>" class="textfield" onfocus="this.select()" />
 297             &nbsp;<?php echo $strPrimaryKeyWarning . "\n"; ?>
 298         </td>
 299     </tr>
 300     <tr>
 301         <td><?php echo $strIndexType; ?>&nbsp;</td>
 302         <td>
 303             <select name="index_type" onchange="return checkIndexName()">
 304     <?php
 305     echo "\n";
 306     for ($i = 0; $i < $index_types_cnt; $i++) {
 307         if ($index_types[$i] == 'PRIMARY') {
 308             if ($index == 'PRIMARY' || !isset($indexes_info['PRIMARY'])) {
 309                 echo '                '
 310                      . '<option value="PRIMARY"' . (($index_type == 'PRIMARY') ? ' selected="selected"' : '') . '>PRIMARY</option>'
 311                      . "\n";
 312             }
 313         } else {
 314             echo '                '
 315                  . '<option value="' . $index_types[$i] . '"' . (($index_type == $index_types[$i]) ? ' selected="selected"' : '') . '>'. $index_types[$i] . '</option>'
 316                  . "\n";
 317
 318         } // end if... else...
 319     } // end for
 320     ?>
 321             </select>&nbsp;
 322             <?php echo PMA_showMySQLDocu('Reference', 'ALTER_TABLE') . "\n"; ?>
 323         </td>
 324     </tr>
 325     </table><br />
 326
 327     <table border="<?php echo $cfg['Border']; ?>" cellpadding="5">
 328     <tr>
 329         <th><?php echo $strField; ?></th>
 330         <th><?php echo $strSize; ?></th>
 331     </tr>
 332     <?php
 333     foreach($edited_index_info['Sequences'] AS $row_no => $seq_index) {
 334         $add_type     = (is_array($fields_types) && count($fields_types) == count($fields_names));
 335         $selected     = $edited_index_data[$seq_index]['Column_name'];
 336         if (!empty($edited_index_data[$seq_index]['Sub_part'])) {
 337             $sub_part = ' value="' . $edited_index_data[$seq_index]['Sub_part'] . '"';
 338         } else {
 339             $sub_part = '';
 340         }
 341         $bgcolor      = (($row_no % 2) ? $cfg['BgcolorOne'] : $cfg['BgcolorTwo']);
 342         echo "\n";
 343         ?>
 344     <tr>
 345         <td bgcolor="<?php echo $bgcolor; ?>">
 346             <select name="column[]">
 347                 <option value="--ignore--"<?php if ('--ignore--' == $selected) echo ' selected="selected"'; ?>>
 348                     -- <?php echo $strIgnore; ?> --</option>
 349         <?php
 350         foreach($fields_names AS $key => $val) {
 351             if ($index_type != 'FULLTEXT'
 352                 || preg_match('@^(varchar|text|tinytext|mediumtext|longtext)@i', $fields_types[$key])) {
 353                 echo "\n" . '                '
 354                      . '<option value="' . htmlspecialchars($val) . '"' . (($val == $selected) ? ' selected="selected"' : '') . '>'
 355                      . htmlspecialchars($val) . (($add_type) ? ' [' . $fields_types[$key] . ']' : '' ) . '</option>' . "\n";
 356             }
 357         } // end while
 358         echo "\n";
 359         ?>
 360             </select>
 361         </td>
 362         <td bgcolor="<?php echo $bgcolor; ?>">
 363             <input type="text" size="5" name="sub_part[]"<?php echo $sub_part; ?> onfocus="this.select()" />
 364         </td>
 365     </tr>
 366         <?php
 367     } // end while
 368
 369     echo "\n";
 370     ?>
 371     </table><br />
 372
 373     <input type="submit" name="do_save_data" value="<?php echo $strSave; ?>" /><br />
 374
 375     <?php
 376     echo "\n";
 377     if (isset($added_fields)) {
 378         echo '    <input type="hidden" name="prev_add_fields" value="' . $added_fields . '" />';
 379     }
 380     if (isset($idx_num_fields)) {
 381         echo '    <input type="hidden" name="idx_num_fields" value="' . $idx_num_fields . '" />' . "\n";
 382     }
 383     echo '    <hr /><br />' . "\n";
 384     echo '    ' . sprintf($strAddToIndex,  '<input type="text" name="added_fields" size="4" value="1" class="textfield" onfocus="this.select()" />') . "\n";
 385     echo '    &nbsp;<input type="submit" name="add_fields" value="' . $strGo . '" onclick="return checkFormElementInRange(this.form, \'added_fields\', 1)" />' . "\n";
 386
 387 } else {
 388     /**
 389      * Display indexes
 390      */
 391     ?>
 392     <!-- Indexes form -->
 393     <form action="tbl_indexes.php" method="post">
 394         <?php echo PMA_generate_common_hidden_inputs($db, $table); ?>
 395     <?php
 396     echo "\n";
 397     echo '        ' . $strIndexes . '&nbsp;:' . "\n";
 398     echo '        ' . PMA_showMySQLDocu('MySQL_Optimisation', 'Optimising_Database_Structure') . '<br />' ."\n";
 399
 400     if ($idx_cnt > 0) {
 401         ?>
 402         <table border="<?php echo $cfg['Border']; ?>">
 403         <tr>
 404             <th><?php echo $strKeyname; ?></th>
 405             <th><?php echo $strType; ?></th>
 406             <th><?php echo $strCardinality; ?></th>
 407             <th colspan="2"><?php echo $strAction; ?></th>
 408             <th colspan="2"><?php echo $strField; ?></th>
 409         </tr>
 410         <?php
 411         echo "\n";
 412         foreach($indexes AS $index_no => $index_name) {
 413             $cell_bgd = (($index_no % 2) ? $cfg['BgcolorOne'] : $cfg['BgcolorTwo']);
 414             $index_td = '            <td bgcolor="' . $cell_bgd . '" rowspan="' . count($indexes_info[$index_name]['Sequences']) . '">' . "\n";
 415             echo '        <tr>' . "\n";
 416             echo $index_td
 417                  . '                ' . htmlspecialchars($index_name) . "\n"
 418                  . '            </td>' . "\n";
 419
 420             if ((PMA_MYSQL_INT_VERSION < 40002 && $indexes_info[$index_name]['Comment'] == 'FULLTEXT')
 421                 || (PMA_MYSQL_INT_VERSION >= 40002 && $indexes_info[$index_name]['Index_type'] == 'FULLTEXT')) {
 422                 $index_type = 'FULLTEXT';
 423             } else if ($index_name == 'PRIMARY') {
 424                 $index_type = 'PRIMARY';
 425             } else if ($indexes_info[$index_name]['Non_unique'] == '0') {
 426                 $index_type = 'UNIQUE';
 427             } else {
 428                 $index_type = 'INDEX';
 429             }
 430             echo $index_td
 431                  . '                ' . $index_type . "\n"
 432                  . '            </td>' . "\n";
 433
 434             echo str_replace('">' . "\n", '" align="right">' . "\n", $index_td)
 435                  . '                ' . (isset($indexes_info[$index_name]['Cardinality']) ? $indexes_info[$index_name]['Cardinality'] : $strNone) . '&nbsp;' . "\n"
 436                  . '            </td>' . "\n";
 437
 438             echo $index_td
 439                  . '                <a href="tbl_indexes.php?' . $url_query . '&amp;index=' . urlencode($index_name) . '">' . ($cfg['PropertiesIconic'] ? '<img src="./images/button_edit.png" width="12" height="13" hspace="7" border="0" title="' . $strEdit . '" alt="' . $strEdit . '">' : $strEdit) . '</a>' . "\n"
 440                  . '            </td>' . "\n";
 441
 442             if ($index_name == 'PRIMARY') {
 443                 $local_query = urlencode('ALTER TABLE ' . PMA_backquote($table) . ' DROP PRIMARY KEY');
 444                 $js_msg    = 'ALTER TABLE ' . PMA_jsFormat($table) . ' DROP PRIMARY KEY';
 445                 $zero_rows = urlencode($strPrimaryKeyHasBeenDropped);
 446             } else {
 447                 $local_query = urlencode('ALTER TABLE ' . PMA_backquote($table) . ' DROP INDEX ' . PMA_backquote($index_name));
 448                 $js_msg    = 'ALTER TABLE ' . PMA_jsFormat($table) . ' DROP INDEX ' . PMA_jsFormat($index_name);
 449                 $zero_rows = urlencode(sprintf($strIndexHasBeenDropped, htmlspecialchars($index_name)));
 450             }
 451             echo $index_td
 452                  . '                <a href="sql.php?' . $url_query . '&amp;sql_query=' . $local_query . '&amp;zero_rows=' . $zero_rows . '" onclick="return confirmLink(this, \'' . $js_msg . '\')">' . ($cfg['PropertiesIconic'] ? '<img src="./images/button_drop.png" width="11" height="12" hspace="7" border="0" title="' . $strDrop . '" alt="' . $strDrop . '">' : $strDrop) . '</a>' . "\n"
 453                  . '            </td>' . "\n";
 454
 455             foreach($indexes_info[$index_name]['Sequences'] AS $row_no => $seq_index) {
 456                 if ($row_no > 0) {
 457                     echo '        <tr>' . "\n";
 458                 }
 459                 if (!empty($indexes_data[$index_name][$seq_index]['Sub_part'])) {
 460                     echo '            <td bgcolor="' . $cell_bgd . '">' . "\n"
 461                          . '                ' . $indexes_data[$index_name][$seq_index]['Column_name'] . "\n"
 462                          . '            </td>' . "\n";
 463                     echo '            <td align="right" bgcolor="' . $cell_bgd . '">' . "\n"
 464                          . '                ' . $indexes_data[$index_name][$seq_index]['Sub_part'] . "\n"
 465                          . '            </td>' . "\n";
 466                     echo '        </tr>' . "\n";
 467                 } else {
 468                     echo '            <td bgcolor="' . $cell_bgd . '" colspan="2">' . "\n"
 469                          . '                ' . htmlspecialchars($indexes_data[$index_name][$seq_index]['Column_name']) . "\n"
 470                          . '            </td>' . "\n";
 471                     echo '        </tr>' . "\n";
 472                 }
 473             } // end while
 474         } // end while
 475         ?>
 476         </table><br />
 477         <?php
 478         echo "\n\n";
 479     } // end display indexes
 480     else {
 481         // none indexes
 482         echo "\n" . '        <br />' . "\n";
 483         echo '        <i>' . $strNoIndex . '</i><br /><br />' . "\n\n";
 484     }
 485
 486     echo '        ' . sprintf($strCreateIndex, '<input type="text" size="4" name="idx_num_fields" value="1" class="textfield" />') . "\n";
 487     echo '        &nbsp;<input type="submit" name="create_index" value="' . $strGo . '" onclick="return checkFormElementInRange(this.form, \'idx_num_fields\', 1)" />' . "\n";
 488     echo '    ';
 489 } // end display indexes
 490
 491 ?>
 492 </form>
 493
 494
 495 <?php
 496 /**
 497  * Displays the footer
 498  */
 499 echo "\n";
 500
 501 if (!defined('PMA_IDX_INCLUDED')){
 502     require_once('./footer.inc.php');
 503 }
 504 ?>