tbl_alter.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Alter one or more table columns
   5  *
   6  * linked from table_structure, uses libraries/tbl_properties.inc.php to display
   7  * form and handles this form data
   8  *
   9  * @package PhpMyAdmin
  10  */
  11
  12 /**
  13  * Gets some core libraries
  14  */
  15 require_once 'libraries/common.inc.php';
  16
  17 require_once 'libraries/header.inc.php';
  18
  19 if (isset($_REQUEST['field'])) {
  20     $GLOBALS['field'] = $_REQUEST['field'];
  21 }
  22
  23 // Check parameters
  24 PMA_checkParameters(array('db', 'table'));
  25
  26 /**
  27  * Gets tables informations
  28  */
  29 require_once 'libraries/tbl_common.php';
  30 require_once 'libraries/tbl_info.inc.php';
  31
  32 $active_page = 'tbl_structure.php';
  33
  34 /**
  35  * Defines the url to return to in case of error in a sql statement
  36  */
  37 $err_url = 'tbl_structure.php?' . PMA_generate_common_url($db, $table);
  38
  39 /**
  40  * Moving columns
  41  */
  42 if (isset($_REQUEST['move_columns'])
  43     && is_array($_REQUEST['move_columns'])
  44     && $GLOBALS['is_ajax_request']) {
  45     /*
  46      * first, load the definitions for all columns
  47      */
  48     $columns = PMA_DBI_get_columns_full($db, $table);
  49     $column_names = array_keys($columns);
  50     $changes = array();
  51     $we_dont_change_keys = array();
  52
  53     // move columns from first to last
  54     for ($i = 0, $l = count($_REQUEST['move_columns']); $i < $l; $i++) {
  55         $column = $_REQUEST['move_columns'][$i];
  56         // is this column already correctly placed?
  57         if ($column_names[$i] == $column) {
  58             continue;
  59         }
  60
  61         // it is not, let's move it to index $i
  62         $data = $columns[$column];
  63         $extracted_columnspec = PMA_extractColumnSpec($data['Type']);
  64         if (isset($data['Extra']) && $data['Extra'] == 'on update CURRENT_TIMESTAMP') {
  65             $extracted_columnspec['attribute'] = $data['Extra'];
  66             unset($data['Extra']);
  67         }
  68         $current_timestamp = false;
  69         if ($data['Type'] == 'timestamp' && $data['Default'] == 'CURRENT_TIMESTAMP') {
  70             $current_timestamp = true;
  71         }
  72         $default_type =
  73             $data['Null'] === 'YES' && $data['Default'] === null
  74                 ? 'NULL'
  75                 : ($current_timestamp
  76                     ? 'CURRENT_TIMESTAMP'
  77                     : ($data['Default'] == ''
  78                         ? 'NONE'
  79                         : 'USER_DEFINED'));
  80
  81         $changes[] = 'CHANGE ' . PMA_Table::generateAlter(
  82             $column,
  83             $column,
  84             strtoupper($extracted_columnspec['type']),
  85             $extracted_columnspec['spec_in_brackets'],
  86             $extracted_columnspec['attribute'],
  87             isset($data['Collation'])
  88                 ? $data['Collation']
  89                 : '',
  90             $data['Null'] === 'YES'
  91                 ? 'NULL'
  92                 : 'NOT NULL',
  93             $default_type,
  94             $current_timestamp
  95                 ? ''
  96                 : $data['Default'],
  97             isset($data['Extra']) && $data['Extra'] !== ''
  98                 ? $data['Extra']
  99                 : false,
 100             isset($data['Comments']) && $data['Comments'] !== ''
 101                 ? $data['Comments']
 102                 : false,
 103             $we_dont_change_keys,
 104             $i,
 105             $data['Default'],
 106             $i === 0
 107                 ? '-first'
 108                 : $column_names[$i - 1]
 109                 );
 110         // update current column_names array, first delete old position
 111         for($j = 0, $ll = count($column_names); $j < $ll; $j++) {
 112             if ($column_names[$j] == $column) {
 113                 unset($column_names[$j]);
 114             }
 115         }
 116         // insert moved column
 117         array_splice($column_names, $i, 0, $column);
 118     }
 119     if (empty($changes)) { // should never happen
 120         PMA_ajaxResponse('', true);
 121     }
 122     $move_query = 'ALTER TABLE ' . PMA_backquote($table) . ' ';
 123     $move_query .= implode(', ', $changes);
 124     // move columns
 125     $result = PMA_DBI_try_query($move_query);
 126     $tmp_error = PMA_DBI_getError();
 127     if ($tmp_error) {
 128         PMA_ajaxResponse(PMA_Message::error($tmp_error), false);
 129     }
 130     PMA_ajaxResponse(PMA_Message::success(__('The columns have been moved successfully.')), true);
 131 }
 132
 133 /**
 134  * Modifications have been submitted -> updates the table
 135  */
 136 $abort = false;
 137 if (isset($_REQUEST['do_save_data'])) {
 138     $field_cnt = count($_REQUEST['field_orig']);
 139     $key_fields = array();
 140     $changes = array();
 141
 142     for ($i = 0; $i < $field_cnt; $i++) {
 143         $changes[] = 'CHANGE ' . PMA_Table::generateAlter(
 144             $_REQUEST['field_orig'][$i],
 145             $_REQUEST['field_name'][$i],
 146             $_REQUEST['field_type'][$i],
 147             $_REQUEST['field_length'][$i],
 148             $_REQUEST['field_attribute'][$i],
 149             isset($_REQUEST['field_collation'][$i])
 150                 ? $_REQUEST['field_collation'][$i]
 151                 : '',
 152             isset($_REQUEST['field_null'][$i])
 153                 ? $_REQUEST['field_null'][$i]
 154                 : 'NOT NULL',
 155             $_REQUEST['field_default_type'][$i],
 156             $_REQUEST['field_default_value'][$i],
 157             isset($_REQUEST['field_extra'][$i])
 158                 ? $_REQUEST['field_extra'][$i]
 159                 : false,
 160             isset($_REQUEST['field_comments'][$i])
 161                 ? $_REQUEST['field_comments'][$i]
 162                 : '',
 163             $key_fields,
 164             $i,
 165             $_REQUEST['field_default_orig'][$i],
 166             isset($_REQUEST['field_move_to'][$i])
 167                 ? $_REQUEST['field_move_to'][$i]
 168                 : ''
 169         );
 170     } // end for
 171
 172     // Builds the primary keys statements and updates the table
 173     $key_query = '';
 174     /**
 175      * this is a little bit more complex
 176      *
 177      * @todo if someone selects A_I when altering a column we need to check:
 178      *  - no other column with A_I
 179      *  - the column has an index, if not create one
 180      *
 181     if (count($key_fields)) {
 182         $fields = array();
 183         foreach ($key_fields as $each_field) {
 184             if (isset($_REQUEST['field_name'][$each_field]) && strlen($_REQUEST['field_name'][$each_field])) {
 185                 $fields[] = PMA_backquote($_REQUEST['field_name'][$each_field]);
 186             }
 187         } // end for
 188         $key_query = ', ADD KEY (' . implode(', ', $fields) . ') ';
 189     }
 190      */
 191
 192     // To allow replication, we first select the db to use and then run queries
 193     // on this db.
 194     if (! PMA_DBI_select_db($db)) {
 195         PMA_mysqlDie(
 196             PMA_DBI_getError(),
 197             'USE ' . PMA_backquote($db) . ';',
 198             '',
 199             $err_url
 200         );
 201     }
 202     $sql_query = 'ALTER TABLE ' . PMA_backquote($table) . ' ';
 203     $sql_query .= implode(', ', $changes) . $key_query;
 204     $sql_query .= ';';
 205     $result    = PMA_DBI_try_query($sql_query);
 206
 207     if ($result !== false) {
 208         $message = PMA_Message::success(
 209             __('Table %1$s has been altered successfully')
 210         );
 211         $message->addParam($table);
 212         $btnDrop = 'Fake';
 213
 214         /**
 215          * If comments were sent, enable relation stuff
 216          */
 217         include_once 'libraries/transformations.lib.php';
 218
 219         // updaet field names in relation
 220         if (isset($_REQUEST['field_orig']) && is_array($_REQUEST['field_orig'])) {
 221             foreach ($_REQUEST['field_orig'] as $fieldindex => $fieldcontent) {
 222                 if ($_REQUEST['field_name'][$fieldindex] != $fieldcontent) {
 223                     PMA_REL_renameField(
 224                         $db, $table, $fieldcontent,
 225                         $_REQUEST['field_name'][$fieldindex]
 226                     );
 227                 }
 228             }
 229         }
 230
 231         // update mime types
 232         if (isset($_REQUEST['field_mimetype'])
 233             && is_array($_REQUEST['field_mimetype'])
 234             && $cfg['BrowseMIME']
 235         ) {
 236             foreach ($_REQUEST['field_mimetype'] as $fieldindex => $mimetype) {
 237                 if (isset($_REQUEST['field_name'][$fieldindex])
 238                     && strlen($_REQUEST['field_name'][$fieldindex])
 239                 ) {
 240                     PMA_setMIME(
 241                         $db, $table, $_REQUEST['field_name'][$fieldindex],
 242                         $mimetype,
 243                         $_REQUEST['field_transformation'][$fieldindex],
 244                         $_REQUEST['field_transformation_options'][$fieldindex]
 245                     );
 246                 }
 247             }
 248         }
 249
 250         if ( $_REQUEST['ajax_request'] == true) {
 251             $extra_data['sql_query'] = PMA_showMessage(null, $sql_query);
 252             PMA_ajaxResponse($message, $message->isSuccess(), $extra_data);
 253         }
 254
 255         $active_page = 'tbl_structure.php';
 256         include 'tbl_structure.php';
 257     } else {
 258         PMA_mysqlDie('', '', '', $err_url, false);
 259         // An error happened while inserting/updating a table definition.
 260         // to prevent total loss of that data, we embed the form once again.
 261         // The variable $regenerate will be used to restore data in libraries/tbl_properties.inc.php
 262         if (isset($_REQUEST['orig_field'])) {
 263             $_REQUEST['field'] = $_REQUEST['orig_field'];
 264         }
 265
 266         $regenerate = true;
 267     }
 268 }
 269
 270 /**
 271  * No modifications yet required -> displays the table fields
 272  *
 273  * $selected comes from multi_submits.inc.php
 274  */
 275 if ($abort == false) {
 276     if (!isset($_REQUEST['ajax_request']) || $_REQUEST['ajax_request'] != true) {
 277         include_once 'libraries/tbl_links.inc.php';
 278     }
 279
 280     if (! isset($selected)) {
 281         PMA_checkParameters(array('field'));
 282         $selected[]   = $_REQUEST['field'];
 283         $selected_cnt = 1;
 284     } else { // from a multiple submit
 285         $selected_cnt = count($selected);
 286     }
 287
 288     /**
 289      * @todo optimize in case of multiple fields to modify
 290      */
 291     for ($i = 0; $i < $selected_cnt; $i++) {
 292         $fields_meta[] = PMA_DBI_get_columns($db, $table, $selected[$i], true);
 293     }
 294     $num_fields  = count($fields_meta);
 295     $action      = 'tbl_alter.php';
 296
 297     // Get more complete field information.
 298     // For now, this is done to obtain MySQL 4.1.2+ new TIMESTAMP options
 299     // and to know when there is an empty DEFAULT value.
 300     // Later, if the analyser returns more information, it
 301     // could be executed to replace the info given by SHOW FULL COLUMNS FROM.
 302     /**
 303      * @todo put this code into a require()
 304      * or maybe make it part of PMA_DBI_get_columns();
 305      */
 306
 307     // We also need this to correctly learn if a TIMESTAMP is NOT NULL, since
 308     // SHOW FULL COLUMNS says NULL and SHOW CREATE TABLE says NOT NULL (tested
 309     // in MySQL 4.0.25).
 310
 311     $show_create_table = PMA_DBI_fetch_value(
 312         'SHOW CREATE TABLE ' . PMA_backquote($db) . '.' . PMA_backquote($table),
 313         0, 1
 314     );
 315     $analyzed_sql = PMA_SQP_analyze(PMA_SQP_parse($show_create_table));
 316     unset($show_create_table);
 317     /**
 318      * Form for changing properties.
 319      */
 320     include 'libraries/tbl_properties.inc.php';
 321 }
 322
 323
 324 /**
 325  * Displays the footer
 326  */
 327 require 'libraries/footer.inc.php';
 328 ?>