tbl_indexes.php

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