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] = 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 Object $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 (! $error) {
  54         $GLOBALS['dbi']->query($sql_query);
  55         $message = PMA_Message::success(
  56             __('Table %1$s has been altered successfully')
  57         );
  58         $message->addParam($table);
  59
  60         if ($GLOBALS['is_ajax_request'] == true) {
  61             $response = PMA_Response::getInstance();
  62             $response->addJSON('message', $message);
  63             $response->addJSON('index_table', PMA_Index::getView($table, $db));
  64             $response->addJSON(
  65                 'sql_query',
  66                 PMA_Util::getMessage(null, $sql_query)
  67             );
  68         } else {
  69             $active_page = 'tbl_structure.php';
  70             include 'tbl_structure.php';
  71         }
  72         exit;
  73     } else {
  74         $response = PMA_Response::getInstance();
  75         $response->isSuccess(false);
  76         $response->addJSON('message', $error);
  77         exit;
  78     }
  79 }
  80
  81 /**
  82  * Function to get the sql query for index creation or edit
  83  *
  84  * @param string $db     current db
  85  * @param string $table  current table
  86  * @param Object $index  current index
  87  * @param bool   &$error whether error occoured or not
  88  *
  89  * @return string
  90  */
  91 function PMA_getSqlQueryForIndexCreateOrEdit($db, $table, $index, &$error)
  92 {
  93     // $sql_query is the one displayed in the query box
  94     $sql_query = 'ALTER TABLE ' . PMA_Util::backquote($db)
  95         . '.' . PMA_Util::backquote($table);
  96
  97     // Drops the old index
  98     if (! empty($_REQUEST['old_index'])) {
  99         if ($_REQUEST['old_index'] == 'PRIMARY') {
 100             $sql_query .= ' DROP PRIMARY KEY,';
 101         } else {
 102             $sql_query .= ' DROP INDEX '
 103                 . PMA_Util::backquote($_REQUEST['old_index']) . ',';
 104         }
 105     } // end if
 106
 107     // Builds the new one
 108     switch ($index->getType()) {
 109     case 'PRIMARY':
 110         if ($index->getName() == '') {
 111             $index->setName('PRIMARY');
 112         } elseif ($index->getName() != 'PRIMARY') {
 113             $error = PMA_Message::error(
 114                 __('The name of the primary key must be "PRIMARY"!')
 115             );
 116         }
 117         $sql_query .= ' ADD PRIMARY KEY';
 118         break;
 119     case 'FULLTEXT':
 120     case 'UNIQUE':
 121     case 'INDEX':
 122     case 'SPATIAL':
 123         if ($index->getName() == 'PRIMARY') {
 124             $error = PMA_Message::error(__('Can\'t rename index to PRIMARY!'));
 125         }
 126         $sql_query .= ' ADD ' . $index->getType() . ' '
 127             . ($index->getName() ? PMA_Util::backquote($index->getName()) : '');
 128         break;
 129     } // end switch
 130
 131     $index_fields = array();
 132     foreach ($index->getColumns() as $key => $column) {
 133         $index_fields[$key] = PMA_Util::backquote($column->getName());
 134         if ($column->getSubPart()) {
 135             $index_fields[$key] .= '(' . $column->getSubPart() . ')';
 136         }
 137     } // end while
 138
 139     if (empty($index_fields)) {
 140         $error = PMA_Message::error(__('No index parts defined!'));
 141     } else {
 142         $sql_query .= ' (' . implode(', ', $index_fields) . ')';
 143     }
 144
 145     if (PMA_MYSQL_INT_VERSION > 50500) {
 146         $sql_query .= "COMMENT '"
 147             . PMA_Util::sqlAddSlashes($index->getComment())
 148             . "'";
 149     }
 150     $sql_query .= ';';
 151
 152     return $sql_query;
 153 }
 154
 155 /**
 156  * Function to prepare the form values for index
 157  *
 158  * @param string $db    curent database
 159  * @param string $table current table
 160  *
 161  * @return PMA_Index
 162  */
 163 function PMA_prepareFormValues($db, $table)
 164 {
 165     if (isset($_REQUEST['index'])) {
 166         if (is_array($_REQUEST['index'])) {
 167             // coming already from form
 168             $index = new PMA_Index($_REQUEST['index']);
 169         } else {
 170             $index = PMA_Index::singleton($db, $table, $_REQUEST['index']);
 171         }
 172     } else {
 173         $index = new PMA_Index;
 174     }
 175
 176     return $index;
 177 }
 178
 179 /**
 180  * Function to get the number of fields for the form
 181  *
 182  * @param Object $index index
 183  *
 184  * @return int
 185  */
 186 function PMA_getNumberOfFieldsForForm($index)
 187 {
 188     if (isset($_REQUEST['index']) && is_array($_REQUEST['index'])) {
 189         // coming already from form
 190         $add_fields
 191             = count($_REQUEST['index']['columns']['names'])
 192             - $index->getColumnCount();
 193         if (isset($_REQUEST['add_fields'])) {
 194             $add_fields += $_REQUEST['added_fields'];
 195         }
 196     } elseif (isset($_REQUEST['create_index'])) {
 197         $add_fields = $_REQUEST['added_fields'];
 198     } else {
 199         $add_fields = 1;
 200     }// end preparing form values
 201
 202     return $add_fields;
 203 }
 204
 205 /**
 206  * Function to get form parameters
 207  *
 208  * @param string $db    current db
 209  * @param string $table current table
 210  *
 211  * @return array
 212  */
 213 function PMA_getFormParameters($db, $table)
 214 {
 215     $form_params = array(
 216         'db'    => $db,
 217         'table' => $table,
 218     );
 219
 220     if (isset($_REQUEST['create_index'])) {
 221         $form_params['create_index'] = 1;
 222     } elseif (isset($_REQUEST['old_index'])) {
 223         $form_params['old_index'] = $_REQUEST['old_index'];
 224     } elseif (isset($_REQUEST['index'])) {
 225         $form_params['old_index'] = $_REQUEST['index'];
 226     }
 227
 228     return $form_params;
 229 }
 230
 231 /**
 232  * Function to get html for displaying the index form
 233  *
 234  * @param array  $fields      fields
 235  * @param Object $index       index
 236  * @param array  $form_params form parameters
 237  * @param int    $add_fields  number of fields in the form
 238  *
 239  * @return string
 240  */
 241 function PMA_getHtmlForIndexForm($fields, $index, $form_params, $add_fields)
 242 {
 243     $html = "";
 244     $html .= '<form action="tbl_indexes.php" method="post" name="index_frm" id="'
 245         . 'index_frm" class="ajax"'
 246         . 'onsubmit="if (typeof(this.elements[\'index[Key_name]\'].disabled) !='
 247         . ' \'undefined\') {'
 248         . 'this.elements[\'index[Key_name]\'].disabled = false}">';
 249
 250     $html .= PMA_URL_getHiddenInputs($form_params);
 251
 252     $html .= '<fieldset id="index_edit_fields">';
 253
 254     $html .= '<div class="index_info">';
 255
 256     $html .= '<div>'
 257         . '<div class="label">'
 258         . '<strong>'
 259         . '<label for="input_index_name">'
 260         . __('Index name:')
 261         . PMA_Util::showHint(
 262             PMA_Message::notice(
 263                 __(
 264                     '"PRIMARY" <b>must</b> be the name of'
 265                     . ' and <b>only of</b> a primary key!'
 266                 )
 267             )
 268         )
 269         . '</label>'
 270         . '</strong>'
 271         . '</div>'
 272         . '<input type="text" name="index[Key_name]" id="input_index_name"'
 273         . ' size="25"'
 274         . 'value="' . htmlspecialchars($index->getName()) . '"'
 275         . 'onfocus="this.select()" />'
 276         . '</div>';
 277
 278     if (PMA_MYSQL_INT_VERSION > 50500) {
 279         $html .= '<div>'
 280             . '<div class="label">'
 281             . '<strong>'
 282             . '<label for="input_index_comment">'
 283             . __('Comment:')
 284             . '</label>'
 285             . '</strong>'
 286             . '</div>'
 287             . '<input type="text" name="index[Index_comment]" '
 288             . 'id="input_index_comment" size="30"'
 289             . 'value="' . htmlspecialchars($index->getComment()) . '"'
 290             . 'onfocus="this.select()" />'
 291             . '</div>';
 292     }
 293
 294     $html .= '<div>'
 295         . '<div class="label">'
 296         . '<strong>'
 297         . '<label for="select_index_type">'
 298         . __('Index type:')
 299         . PMA_Util::showMySQLDocu('ALTER_TABLE')
 300         . '</label>'
 301         . '</strong>'
 302         . '</div>'
 303         . '<select name="index[Index_type]" id="select_index_type" >'
 304         . $index->generateIndexSelector()
 305         . '</select>'
 306         . '</div>';
 307
 308     $html .= '<div class="clearfloat"></div>';
 309
 310     $html .= '</div>';
 311
 312     $html .= '<table id="index_columns">';
 313
 314     $html .= '<thead>'
 315         . '<tr>'
 316         . '<th>' . __('Column') . '</th>'
 317         . '<th>' . __('Size') . '</th>'
 318         . '</tr>'
 319         . '</thead>';
 320
 321     $odd_row = true;
 322     $spatial_types = array(
 323         'geometry', 'point', 'linestring', 'polygon', 'multipoint',
 324         'multilinestring', 'multipolygon', 'geomtrycollection'
 325     );
 326     $html .= '<tbody>';
 327     foreach ($index->getColumns() as $column) {
 328         $html .= '<tr class="';
 329         $html .= $odd_row ? 'odd' : 'even';
 330         $html .= 'noclick">';
 331         $html .= '<td>';
 332         $html .= '<select name="index[columns][names][]">';
 333         $html .= '<option value="">-- ' . __('Ignore') . ' --</option>';
 334         foreach ($fields as $field_name => $field_type) {
 335             if (($index->getType() != 'FULLTEXT'
 336                 || preg_match('/(char|text)/i', $field_type))
 337                 && ($index->getType() != 'SPATIAL'
 338                 || in_array($field_type, $spatial_types))
 339             ) {
 340                 $html .= '<option value="' . htmlspecialchars($field_name) . '"'
 341                     . (($field_name == $column->getName())
 342                     ? ' selected="selected"'
 343                     : '') . '>'
 344                     . htmlspecialchars($field_name) . ' ['
 345                     . htmlspecialchars($field_type) . ']'
 346                     . '</option>' . "\n";
 347             }
 348         } // end foreach $fields
 349         $html .= '</select>';
 350         $html .= '</td>';
 351         $html .= '<td>';
 352         $html .= '<input type="text" size="5" onfocus="this.select()"'
 353             . 'name="index[columns][sub_parts][]" value="';
 354         if ($index->getType() != 'SPATIAL') {
 355             $html .= $column->getSubPart();
 356         }
 357         $html .= '"/>';
 358         $html .= '</td>';
 359         $html .= '</tr>';
 360         $odd_row = !$odd_row;
 361     } // end foreach $edited_index_info['Sequences']
 362
 363     for ($i = 0; $i < $add_fields; $i++) {
 364         $html .= '<tr class="';
 365         $html .= $odd_row ? 'odd' : 'even';
 366         $html .= 'noclick">';
 367         $html .= '<td>';
 368         $html .= '<select name="index[columns][names][]">';
 369         $html .= '<option value="">-- ' . __('Ignore') . ' --</option>';
 370         foreach ($fields as $field_name => $field_type) {
 371             $html .= '<option value="' . htmlspecialchars($field_name) . '">'
 372                 . htmlspecialchars($field_name) . ' ['
 373                 . htmlspecialchars($field_type) . ']'
 374                 . '</option>' . "\n";
 375         } // end foreach $fields
 376         $html .= '</select>';
 377         $html .= '</td>';
 378         $html .= '<td>'
 379             . '<input type="text" size="5" onfocus="this.select()"'
 380             . 'name="index[columns][sub_parts][]" value="" />'
 381             . '</td>';
 382         $html .= '</tr>';
 383         $odd_row = !$odd_row;
 384     } // end foreach $edited_index_info['Sequences']
 385
 386     $html .= '</tbody>';
 387
 388     $html .= '</table>';
 389
 390     $html .= '</fieldset>';
 391
 392     $html .= '<fieldset class="tblFooters">';
 393
 394     $btn_value = sprintf(__('Add %s column(s) to index'), 1);
 395     $html .= '<div class="slider"></div>';
 396     $html .= '<div class="add_fields">';
 397     $html .= '<input type="submit" value="' . $btn_value . '" />';
 398     $html .= '</div>';
 399
 400     $html .= '</fieldset>';
 401
 402     $html .= '</form>';
 403
 404     return $html;
 405 }
 406 ?>