tbl_indexes.php

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