phpmyadmin/tbl_indexes.php

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