libraries/tbl_indexes.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Set of functions related to table indexes
   5  *
   6  * @package PhpMyAdmin
   7  */
   8 if (! defined('PHPMYADMIN')) {
   9     exit;
  10 }
  11
  12 /**
  13  * Function to get the name and type of the columns of a table
  14  *
  15  * @param string $db    current database
  16  * @param string $table current table
  17  *
  18  * @return array
  19  */
  20 function PMA_getNameAndTypeOfTheColumns($db, $table)
  21 {
  22     $columns = array();
  23     foreach ($GLOBALS['dbi']->getColumnsFull($db, $table) as $row) {
  24         if (preg_match('@^(set|enum)\((.+)\)$@i', $row['Type'], $tmp)) {
  25             $tmp[2] = /*overload*/mb_substr(
  26                 preg_replace('@([^,])\'\'@', '\\1\\\'', ',' . $tmp[2]), 1
  27             );
  28             $columns[$row['Field']] = $tmp[1] . '('
  29                 . str_replace(',', ', ', $tmp[2]) . ')';
  30         } else {
  31             $columns[$row['Field']] = $row['Type'];
  32         }
  33     } // end while
  34
  35     return $columns;
  36 }
  37
  38 /**
  39  * Function to handle the creation or edit of an index
  40  *
  41  * @param string    $db    current db
  42  * @param string    $table current table
  43  * @param PMA_Index $index current index
  44  *
  45  * @return void
  46  */
  47 function PMA_handleCreateOrEditIndex($db, $table, $index)
  48 {
  49     $error = false;
  50
  51     $sql_query = PMA_getSqlQueryForIndexCreateOrEdit($db, $table, $index, $error);
  52
  53     // If there is a request for SQL previewing.
  54     if (isset($_REQUEST['preview_sql'])) {
  55         PMA_previewSQL($sql_query);
  56     }
  57
  58     if (! $error) {
  59         $GLOBALS['dbi']->query($sql_query);
  60         $message = PMA_Message::success(
  61             __('Table %1$s has been altered successfully.')
  62         );
  63         $message->addParam($table);
  64
  65         if ($GLOBALS['is_ajax_request'] == true) {
  66             $response = PMA_Response::getInstance();
  67             $response->addJSON(
  68                 'message', PMA_Util::getMessage($message, $sql_query, 'success')
  69             );
  70             $response->addJSON('index_table', PMA_Index::getView($table, $db));
  71         } else {
  72             include 'tbl_structure.php';
  73         }
  74         exit;
  75     } else {
  76         $response = PMA_Response::getInstance();
  77         $response->isSuccess(false);
  78         $response->addJSON('message', $error);
  79         exit;
  80     }
  81 }
  82
  83 /**
  84  * Function to get the sql query for index creation or edit
  85  *
  86  * @param string    $db     current db
  87  * @param string    $table  current table
  88  * @param PMA_Index $index  current index
  89  * @param bool      &$error whether error occurred or not
  90  *
  91  * @return string
  92  */
  93 function PMA_getSqlQueryForIndexCreateOrEdit($db, $table, $index, &$error)
  94 {
  95     // $sql_query is the one displayed in the query box
  96     $sql_query = 'ALTER TABLE ' . PMA_Util::backquote($db)
  97         . '.' . PMA_Util::backquote($table);
  98
  99     // Drops the old index
 100     if (! empty($_REQUEST['old_index'])) {
 101         if ($_REQUEST['old_index'] == 'PRIMARY') {
 102             $sql_query .= ' DROP PRIMARY KEY,';
 103         } else {
 104             $sql_query .= ' DROP INDEX '
 105                 . PMA_Util::backquote($_REQUEST['old_index']) . ',';
 106         }
 107     } // end if
 108
 109     // Builds the new one
 110     switch ($index->getChoice()) {
 111     case 'PRIMARY':
 112         if ($index->getName() == '') {
 113             $index->setName('PRIMARY');
 114         } elseif ($index->getName() != 'PRIMARY') {
 115             $error = PMA_Message::error(
 116                 __('The name of the primary key must be "PRIMARY"!')
 117             );
 118         }
 119         $sql_query .= ' ADD PRIMARY KEY';
 120         break;
 121     case 'FULLTEXT':
 122     case 'UNIQUE':
 123     case 'INDEX':
 124     case 'SPATIAL':
 125         if ($index->getName() == 'PRIMARY') {
 126             $error = PMA_Message::error(__('Can\'t rename index to PRIMARY!'));
 127         }
 128         $sql_query .= ' ADD ' . $index->getChoice() . ' '
 129             . ($index->getName() ? PMA_Util::backquote($index->getName()) : '');
 130         break;
 131     } // end switch
 132
 133     $index_fields = array();
 134     foreach ($index->getColumns() as $key => $column) {
 135         $index_fields[$key] = PMA_Util::backquote($column->getName());
 136         if ($column->getSubPart()) {
 137             $index_fields[$key] .= '(' . $column->getSubPart() . ')';
 138         }
 139     } // end while
 140
 141     if (empty($index_fields)) {
 142         $error = PMA_Message::error(__('No index parts defined!'));
 143     } else {
 144         $sql_query .= ' (' . implode(', ', $index_fields) . ')';
 145     }
 146
 147     $keyBlockSizes = $index->getKeyBlockSize();
 148     if (! empty($keyBlockSizes)) {
 149         $sql_query .= " KEY_BLOCK_SIZE = "
 150              . PMA_Util::sqlAddSlashes($keyBlockSizes);
 151     }
 152
 153     // specifying index type is allowed only for primary, unique and index only
 154     $type = $index->getType();
 155     if ($index->getChoice() != 'SPATIAL'
 156         && $index->getChoice() != 'FULLTEXT'
 157         && in_array($type, PMA_Index::getIndexTypes())
 158     ) {
 159         $sql_query .= ' USING ' . $type;
 160     }
 161
 162     $parser = $index->getParser();
 163     if ($index->getChoice() == 'FULLTEXT' && ! empty($parser)) {
 164         $sql_query .= " WITH PARSER " . PMA_Util::sqlAddSlashes($parser);
 165     }
 166
 167     $comment = $index->getComment();
 168     if (! empty($comment)) {
 169         $sql_query .= " COMMENT '" . PMA_Util::sqlAddSlashes($comment) . "'";
 170     }
 171
 172     $sql_query .= ';';
 173
 174     return $sql_query;
 175 }
 176
 177 /**
 178  * Function to prepare the form values for index
 179  *
 180  * @param string $db    current database
 181  * @param string $table current table
 182  *
 183  * @return PMA_Index
 184  */
 185 function PMA_prepareFormValues($db, $table)
 186 {
 187     if (isset($_REQUEST['index'])) {
 188         if (is_array($_REQUEST['index'])) {
 189             // coming already from form
 190             $index = new PMA_Index($_REQUEST['index']);
 191         } else {
 192             $index = PMA_Index::singleton($db, $table, $_REQUEST['index']);
 193         }
 194     } else {
 195         $index = new PMA_Index;
 196     }
 197
 198     return $index;
 199 }
 200
 201 /**
 202  * Function to get the number of fields for the form
 203  *
 204  * @param PMA_Index $index index
 205  *
 206  * @return int
 207  */
 208 function PMA_getNumberOfFieldsForForm($index)
 209 {
 210     if (isset($_REQUEST['index']) && is_array($_REQUEST['index'])) {
 211         // coming already from form
 212         $add_fields
 213             = isset($_REQUEST['index']['columns']['names'])?
 214             count($_REQUEST['index']['columns']['names'])
 215             - $index->getColumnCount():0;
 216         if (isset($_REQUEST['add_fields'])) {
 217             $add_fields += $_REQUEST['added_fields'];
 218         }
 219     } elseif (isset($_REQUEST['create_index'])) {
 220         $add_fields = $_REQUEST['added_fields'];
 221     } else {
 222         $add_fields = 0;
 223     }// end preparing form values
 224
 225     return $add_fields;
 226 }
 227
 228 /**
 229  * Function to get form parameters
 230  *
 231  * @param string $db    current db
 232  * @param string $table current table
 233  *
 234  * @return array
 235  */
 236 function PMA_getFormParameters($db, $table)
 237 {
 238     $form_params = array(
 239         'db'    => $db,
 240         'table' => $table,
 241     );
 242
 243     if (isset($_REQUEST['create_index'])) {
 244         $form_params['create_index'] = 1;
 245     } elseif (isset($_REQUEST['old_index'])) {
 246         $form_params['old_index'] = $_REQUEST['old_index'];
 247     } elseif (isset($_REQUEST['index'])) {
 248         $form_params['old_index'] = $_REQUEST['index'];
 249     }
 250
 251     return $form_params;
 252 }
 253
 254 /**
 255  * Function to get html for displaying the index form
 256  *
 257  * @param array     $fields      fields
 258  * @param PMA_Index $index       index
 259  * @param array     $form_params form parameters
 260  * @param int       $add_fields  number of fields in the form
 261  *
 262  * @return string
 263  */
 264 function PMA_getHtmlForIndexForm($fields, $index, $form_params, $add_fields)
 265 {
 266     $html = "";
 267     $html .= '<form action="tbl_indexes.php" method="post" name="index_frm" id="'
 268         . 'index_frm" class="ajax"'
 269         . 'onsubmit="if (typeof(this.elements[\'index[Key_name]\'].disabled) !='
 270         . ' \'undefined\') {'
 271         . 'this.elements[\'index[Key_name]\'].disabled = false}">';
 272
 273     $html .= PMA_URL_getHiddenInputs($form_params);
 274
 275     $html .= '<fieldset id="index_edit_fields">';
 276
 277     $html .= '<div class="index_info">';
 278
 279     $html .= '<div>'
 280         . '<div class="label">'
 281         . '<strong>'
 282         . '<label for="input_index_name">'
 283         . __('Index name:')
 284         . PMA_Util::showHint(
 285             PMA_Message::notice(
 286                 __(
 287                     '"PRIMARY" <b>must</b> be the name of'
 288                     . ' and <b>only of</b> a primary key!'
 289                 )
 290             )
 291         )
 292         . '</label>'
 293         . '</strong>'
 294         . '</div>'
 295         . '<input type="text" name="index[Key_name]" id="input_index_name"'
 296         . ' size="25"'
 297         . 'value="' . htmlspecialchars($index->getName()) . '"'
 298         . 'onfocus="this.select()" />'
 299         . '</div>';
 300
 301     $html .= '<div>'
 302         . '<div class="label">'
 303         . '<strong>'
 304         . '<label for="select_index_choice">'
 305         . __('Index choice:')
 306         . PMA_Util::showMySQLDocu('ALTER_TABLE')
 307         . '</label>'
 308         . '</strong>'
 309         . '</div>'
 310         . $index->generateIndexChoiceSelector(isset($_REQUEST['create_edit_table']))
 311         . '</div>';
 312
 313     $html .= PMA_Util::getDivForSliderEffect(
 314         'indexoptions', __('Options')
 315     );
 316
 317     $html .= '<div>'
 318         . '<div class="label">'
 319         . '<strong>'
 320         . '<label for="input_key_block_size">'
 321         . __('Key block size:')
 322         . '</label>'
 323         . '</strong>'
 324         . '</div>'
 325         . '<input type="text" name="index[Key_block_size]" '
 326         . 'id="input_key_block_size" size="30" value="'
 327         . htmlspecialchars($index->getKeyBlockSize()) . '" />'
 328         . '</div>';
 329
 330     $html .= '<div>'
 331         . '<div class="label">'
 332         . '<strong>'
 333         . '<label for="select_index_type">'
 334         . __('Index type:')
 335         . PMA_Util::showMySQLDocu('ALTER_TABLE')
 336         . '</label>'
 337         . '</strong>'
 338         . '</div>'
 339         . $index->generateIndexTypeSelector()
 340         . '</div>';
 341
 342     $html .= '<div>'
 343         . '<div class="label">'
 344         . '<strong>'
 345         . '<label for="input_parser">'
 346         . __('Parser:')
 347         . '</label>'
 348         . '</strong>'
 349         . '</div>'
 350         . '<input type="text" name="index[Parser]" '
 351         . 'id="input_parse" size="30" value="' . htmlspecialchars($index->getParser()) . '" />'
 352         . '</div>';
 353
 354     $html .= '<div>'
 355         . '<div class="label">'
 356         . '<strong>'
 357         . '<label for="input_index_comment">'
 358         . __('Comment:')
 359         . '</label>'
 360         . '</strong>'
 361         . '</div>'
 362         . '<input type="text" name="index[Index_comment]" '
 363         . 'id="input_index_comment" size="30"'
 364         . 'value="' . htmlspecialchars($index->getComment()) . '" />'
 365         . '</div>';
 366
 367     $html .= '</div>'; // end of indexoptions div
 368
 369     $html .= '<div class="clearfloat"></div>';
 370
 371     $html .= '</div>';
 372
 373     $html .= '<table id="index_columns">';
 374
 375     $html .= '<thead>'
 376         . '<tr>'
 377         . '<th>' . __('Column') . '</th>'
 378         . '<th>' . __('Size') . '</th>'
 379         . '</tr>'
 380         . '</thead>';
 381
 382     $odd_row = true;
 383     $spatial_types = array(
 384         'geometry', 'point', 'linestring', 'polygon', 'multipoint',
 385         'multilinestring', 'multipolygon', 'geomtrycollection'
 386     );
 387     $html .= '<tbody>';
 388     /* @var $column PMA_Index_Column */
 389     foreach ($index->getColumns() as $column) {
 390         $html .= '<tr class="';
 391         $html .= $odd_row ? 'odd' : 'even';
 392         $html .= 'noclick">';
 393         $html .= '<td><span class="drag_icon" title="' . __('Drag to reorder') . '"'
 394             . '></span>';
 395         $html .= '<select name="index[columns][names][]">';
 396         $html .= '<option value="">-- ' . __('Ignore') . ' --</option>';
 397         foreach ($fields as $field_name => $field_type) {
 398             if (($index->getChoice() != 'FULLTEXT'
 399                 || preg_match('/(char|text)/i', $field_type))
 400                 && ($index->getChoice() != 'SPATIAL'
 401                 || in_array($field_type, $spatial_types))
 402             ) {
 403                 $html .= '<option value="' . htmlspecialchars($field_name) . '"'
 404                     . (($field_name == $column->getName())
 405                     ? ' selected="selected"'
 406                     : '') . '>'
 407                     . htmlspecialchars($field_name) . ' ['
 408                     . htmlspecialchars($field_type) . ']'
 409                     . '</option>' . "\n";
 410             }
 411         } // end foreach $fields
 412         $html .= '</select>';
 413         $html .= '</td>';
 414         $html .= '<td>';
 415         $html .= '<input type="text" size="5" onfocus="this.select()"'
 416             . 'name="index[columns][sub_parts][]" value="';
 417         if ($index->getChoice() != 'SPATIAL') {
 418             $html .= $column->getSubPart();
 419         }
 420         $html .= '"/>';
 421         $html .= '</td>';
 422         $html .= '</tr>';
 423         $odd_row = !$odd_row;
 424     } // end foreach $edited_index_info['Sequences']
 425
 426     for ($i = 0; $i < $add_fields; $i++) {
 427         $html .= '<tr class="';
 428         $html .= $odd_row ? 'odd' : 'even';
 429         $html .= 'noclick">';
 430         $html .= '<td><span class="drag_icon" title="' . __('Drag to reorder') . '"'
 431             . '></span>';
 432         $html .= '<select name="index[columns][names][]">';
 433         $html .= '<option value="">-- ' . __('Ignore') . ' --</option>';
 434         $j = 0;
 435         foreach ($fields as $field_name => $field_type) {
 436             if (isset($_REQUEST['create_edit_table'])) {
 437                 $col_index = $field_type[1];
 438                 $field_type = $field_type[0];
 439             }
 440             $html .= '<option value="'
 441                 . htmlspecialchars((isset($col_index)) ? $col_index : $field_name)
 442                 . '" ' . ($j++ == $i ? 'selected="selected"' : '') . '>'
 443                 . htmlspecialchars($field_name) . ' ['
 444                 . htmlspecialchars($field_type) . ']'
 445                 . '</option>' . "\n";
 446         } // end foreach $fields
 447         $html .= '</select>';
 448         $html .= '</td>';
 449         $html .= '<td>'
 450             . '<input type="text" size="5" onfocus="this.select()"'
 451             . 'name="index[columns][sub_parts][]" value="" />'
 452             . '</td>';
 453         $html .= '</tr>';
 454         $odd_row = !$odd_row;
 455     } // end foreach $edited_index_info['Sequences']
 456
 457     $html .= '</tbody>';
 458
 459     $html .= '</table>';
 460
 461     $html .= '<div class="add_more">';
 462     $btn_value = sprintf(__('Add %s column(s) to index'), 1);
 463     $html .= '<div class="slider"></div>';
 464     $html .= '<div class="add_fields hide">';
 465     $html .= '<input type="submit" id="add_fields" value="' . $btn_value . '" />';
 466     $html .= '</div>';
 467     $html .= '</div>';
 468
 469     $html .= '</fieldset>';
 470
 471     $html .= '<fieldset class="tblFooters">';
 472     $html .= '<button type="submit" id="preview_index_frm">' . __('Preview SQL') . '</button>';
 473     $html .= '<input type="submit" id="save_index_frm" value="' . __('Go') . '" />';
 474     $html .= '</fieldset>';
 475
 476     $html .= '</form>';
 477
 478     return $html;
 479 }
 480 ?>