libraries/tbl_columns_definition_form.inc.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Display form for changing/adding table fields/columns.
   5  * Included by tbl_addfield.php and tbl_create.php
   6  *
   7  * @package PhpMyAdmin
   8  */
   9 declare(strict_types=1);
  10
  11 use PhpMyAdmin\Charsets;
  12 use PhpMyAdmin\Charsets\Charset;
  13 use PhpMyAdmin\Charsets\Collation;
  14 use PhpMyAdmin\DatabaseInterface;
  15 use PhpMyAdmin\Partition;
  16 use PhpMyAdmin\Relation;
  17 use PhpMyAdmin\Response;
  18 use PhpMyAdmin\Table;
  19 use PhpMyAdmin\TablePartitionDefinition;
  20 use PhpMyAdmin\Template;
  21 use PhpMyAdmin\Transformations;
  22 use PhpMyAdmin\Util;
  23
  24 if (! defined('PHPMYADMIN')) {
  25     exit;
  26 }
  27
  28 /**
  29  * Check parameters
  30  */
  31 Util::checkParameters(
  32     [
  33         'server',
  34         'db',
  35         'table',
  36         'action',
  37         'num_fields',
  38     ]
  39 );
  40
  41 global $db, $table;
  42
  43 /** @var Relation $relation */
  44 $relation = $containerBuilder->get('relation');
  45 $transformations = new Transformations();
  46 $template = new Template();
  47
  48 /**
  49  * Initialize to avoid code execution path warnings
  50  */
  51
  52 if (! isset($num_fields)) {
  53     $num_fields = 0;
  54 }
  55 if (! isset($mime_map)) {
  56     $mime_map = null;
  57 }
  58 if (! isset($columnMeta)) {
  59     $columnMeta = [];
  60 }
  61
  62 $length_values_input_size = 8;
  63
  64 $content_cells = [];
  65
  66 /** @var string $db */
  67 $form_params = [
  68     'db' => $db,
  69 ];
  70
  71 if ($action == 'tbl_create.php') {
  72     $form_params['reload'] = 1;
  73 } else {
  74     if ($action == 'tbl_addfield.php') {
  75         $form_params = array_merge(
  76             $form_params,
  77             [
  78                 'field_where' => Util::getValueByKey($_POST, 'field_where'),
  79             ]
  80         );
  81         if (isset($_POST['field_where'])) {
  82             $form_params['after_field'] = $_POST['after_field'];
  83         }
  84     }
  85     $form_params['table'] = $table;
  86 }
  87
  88 $form_params['orig_num_fields'] = $num_fields;
  89
  90 $form_params = array_merge(
  91     $form_params,
  92     [
  93         'orig_field_where' => Util::getValueByKey($_POST, 'field_where'),
  94         'orig_after_field' => Util::getValueByKey($_POST, 'after_field'),
  95     ]
  96 );
  97
  98 if (isset($selected) && is_array($selected)) {
  99     foreach ($selected as $o_fld_nr => $o_fld_val) {
 100         $form_params['selected[' . $o_fld_nr . ']'] = $o_fld_val;
 101     }
 102 }
 103
 104 $is_backup = ($action != 'tbl_create.php' && $action != 'tbl_addfield.php');
 105
 106 $cfgRelation = $relation->getRelationsParam();
 107
 108 $comments_map = $relation->getComments($db, $table);
 109
 110 $move_columns = [];
 111 if (isset($fields_meta)) {
 112     /** @var DatabaseInterface $dbi */
 113     $dbi = $containerBuilder->get('dbi');
 114     $move_columns = $dbi->getTable($db, $table)->getColumnsMeta();
 115 }
 116
 117 $available_mime = [];
 118 if ($cfgRelation['mimework'] && $GLOBALS['cfg']['BrowseMIME']) {
 119     $mime_map = $transformations->getMime($db, $table);
 120     $available_mime = $transformations->getAvailableMimeTypes();
 121 }
 122
 123 // this will be used on templates/columns_definitions/transformation.twig
 124 $mime_types = [
 125     'input_transformation',
 126     'transformation',
 127 ];
 128 foreach ($mime_types as $mime_type) {
 129     if (isset($available_mime[$mime_type]) and is_iterable($available_mime[$mime_type])) {
 130         foreach ($available_mime[$mime_type] as $mimekey => $transform) {
 131             $available_mime[$mime_type . '_file_quoted'][$mimekey] = preg_quote(
 132                 $available_mime[$mime_type . '_file'][$mimekey],
 133                 '@'
 134             );
 135         }
 136     }
 137 }
 138
 139 //  workaround for field_fulltext, because its submitted indices contain
 140 //  the index as a value, not a key. Inserted here for easier maintenance
 141 //  and less code to change in existing files.
 142 if (isset($field_fulltext) && is_array($field_fulltext)) {
 143     foreach ($field_fulltext as $fulltext_nr => $fulltext_indexkey) {
 144         $submit_fulltext[$fulltext_indexkey] = $fulltext_indexkey;
 145     }
 146 }
 147 if (isset($_POST['submit_num_fields'])
 148     || isset($_POST['submit_partition_change'])
 149 ) {
 150     //if adding new fields, set regenerate to keep the original values
 151     $regenerate = 1;
 152 }
 153
 154 $foreigners = $relation->getForeigners($db, $table, '', 'foreign');
 155 $child_references = null;
 156 // From MySQL 5.6.6 onwards columns with foreign keys can be renamed.
 157 // Hence, no need to get child references
 158 if ($GLOBALS['dbi']->getVersion() < 50606) {
 159     $child_references = $relation->getChildReferences($db, $table);
 160 }
 161
 162 for ($columnNumber = 0; $columnNumber < $num_fields; $columnNumber++) {
 163     $type = '';
 164     $length = '';
 165     $columnMeta = [];
 166     $submit_attribute = null;
 167     $extracted_columnspec = [];
 168
 169     if (! empty($regenerate)) {
 170         $columnMeta = array_merge(
 171             $columnMeta,
 172             [
 173                 'Field'        => Util::getValueByKey(
 174                     $_POST,
 175                     "field_name.${columnNumber}",
 176                     null
 177                 ),
 178                 'Type'         => Util::getValueByKey(
 179                     $_POST,
 180                     "field_type.${columnNumber}",
 181                     null
 182                 ),
 183                 'Collation'    => Util::getValueByKey(
 184                     $_POST,
 185                     "field_collation.${columnNumber}",
 186                     ''
 187                 ),
 188                 'Null'         => Util::getValueByKey(
 189                     $_POST,
 190                     "field_null.${columnNumber}",
 191                     ''
 192                 ),
 193                 'DefaultType'  => Util::getValueByKey(
 194                     $_POST,
 195                     "field_default_type.${columnNumber}",
 196                     'NONE'
 197                 ),
 198                 'DefaultValue' => Util::getValueByKey(
 199                     $_POST,
 200                     "field_default_value.${columnNumber}",
 201                     ''
 202                 ),
 203                 'Extra'        => Util::getValueByKey(
 204                     $_POST,
 205                     "field_extra.${columnNumber}",
 206                     null
 207                 ),
 208                 'Virtuality'   => Util::getValueByKey(
 209                     $_POST,
 210                     "field_virtuality.${columnNumber}",
 211                     ''
 212                 ),
 213                 'Expression'   => Util::getValueByKey(
 214                     $_POST,
 215                     "field_expression.${columnNumber}",
 216                     ''
 217                 ),
 218             ]
 219         );
 220
 221         $columnMeta['Key'] = '';
 222         $parts = explode(
 223             '_',
 224             Util::getValueByKey($_POST, "field_key.${columnNumber}", ''),
 225             2
 226         );
 227         if (count($parts) === 2 && $parts[1] == $columnNumber) {
 228             $columnMeta['Key'] = Util::getValueByKey(
 229                 [
 230                     'primary' => 'PRI',
 231                     'index' => 'MUL',
 232                     'unique' => 'UNI',
 233                     'fulltext' => 'FULLTEXT',
 234                     'spatial' => 'SPATIAL',
 235                 ],
 236                 $parts[0],
 237                 ''
 238             );
 239         }
 240
 241         $columnMeta['Comment']
 242             = isset($submit_fulltext[$columnNumber])
 243             && ($submit_fulltext[$columnNumber] == $columnNumber)
 244                 ? 'FULLTEXT' : false;
 245
 246         switch ($columnMeta['DefaultType']) {
 247             case 'NONE':
 248                 $columnMeta['Default'] = null;
 249                 break;
 250             case 'USER_DEFINED':
 251                 $columnMeta['Default'] = $columnMeta['DefaultValue'];
 252                 break;
 253             case 'NULL':
 254             case 'CURRENT_TIMESTAMP':
 255             case 'current_timestamp()':
 256                 $columnMeta['Default'] = $columnMeta['DefaultType'];
 257                 break;
 258         }
 259
 260         $length = Util::getValueByKey($_POST, "field_length.${columnNumber}", $length);
 261         $submit_attribute = Util::getValueByKey(
 262             $_POST,
 263             "field_attribute.${columnNumber}",
 264             false
 265         );
 266         $comments_map[$columnMeta['Field']] = Util::getValueByKey(
 267             $_POST,
 268             "field_comments.${columnNumber}"
 269         );
 270
 271         $mime_map[$columnMeta['Field']] = array_merge(
 272             $mime_map[$columnMeta['Field']],
 273             [
 274                 'mimetype' => Util::getValueByKey($_POST, "field_mimetype.${$columnNumber}"),
 275                 'transformation' => Util::getValueByKey(
 276                     $_POST,
 277                     "field_transformation.${$columnNumber}"
 278                 ),
 279                 'transformation_options' => Util::getValueByKey(
 280                     $_POST,
 281                     "field_transformation_options.${$columnNumber}"
 282                 ),
 283             ]
 284         );
 285     } elseif (isset($fields_meta[$columnNumber])) {
 286         $columnMeta = $fields_meta[$columnNumber];
 287         $virtual = [
 288             'VIRTUAL',
 289             'PERSISTENT',
 290             'VIRTUAL GENERATED',
 291             'STORED GENERATED',
 292         ];
 293         if (in_array($columnMeta['Extra'], $virtual)) {
 294             $tableObj = new Table($GLOBALS['table'], $GLOBALS['db']);
 295             $expressions = $tableObj->getColumnGenerationExpression(
 296                 $columnMeta['Field']
 297             );
 298             $columnMeta['Expression'] = $expressions[$columnMeta['Field']];
 299         }
 300         switch ($columnMeta['Default']) {
 301             case null:
 302                 if ($columnMeta['Default'] === null) {
 303                     if ($columnMeta['Null'] == 'YES') {
 304                         $columnMeta['DefaultType'] = 'NULL';
 305                         $columnMeta['DefaultValue'] = '';
 306                     } else {
 307                         $columnMeta['DefaultType'] = 'NONE';
 308                         $columnMeta['DefaultValue'] = '';
 309                     }
 310                 } else { // empty
 311                     $columnMeta['DefaultType'] = 'USER_DEFINED';
 312                     $columnMeta['DefaultValue'] = $columnMeta['Default'];
 313                 }
 314                 break;
 315             case 'CURRENT_TIMESTAMP':
 316             case 'current_timestamp()':
 317                 $columnMeta['DefaultType'] = 'CURRENT_TIMESTAMP';
 318                 $columnMeta['DefaultValue'] = '';
 319                 break;
 320             default:
 321                 $columnMeta['DefaultType'] = 'USER_DEFINED';
 322                 $columnMeta['DefaultValue'] = $columnMeta['Default'];
 323
 324                 if ('text' === substr($columnMeta['Type'], -4)) {
 325                     $textDefault = substr($columnMeta['Default'], 1, -1);
 326                     $columnMeta['Default'] = stripcslashes($textDefault !== false ? $textDefault : $columnMeta['Default']);
 327                 }
 328
 329                 break;
 330         }
 331     }
 332
 333     if (isset($columnMeta['Type'])) {
 334         $extracted_columnspec = Util::extractColumnSpec(
 335             $columnMeta['Type']
 336         );
 337         if ($extracted_columnspec['type'] == 'bit') {
 338             $columnMeta['Default']
 339                 = Util::convertBitDefaultValue($columnMeta['Default']);
 340         }
 341         $type = $extracted_columnspec['type'];
 342         if ($length == '') {
 343             $length = $extracted_columnspec['spec_in_brackets'];
 344         }
 345     } else {
 346         // creating a column
 347         $columnMeta['Type'] = '';
 348     }
 349
 350     // Variable tell if current column is bound in a foreign key constraint or not.
 351     // MySQL version from 5.6.6 allow renaming columns with foreign keys
 352     if (isset($columnMeta['Field'])
 353         && isset($form_params['table'])
 354         && $GLOBALS['dbi']->getVersion() < 50606
 355     ) {
 356         $columnMeta['column_status'] = $relation->checkChildForeignReferences(
 357             $form_params['db'],
 358             $form_params['table'],
 359             $columnMeta['Field'],
 360             $foreigners,
 361             $child_references
 362         );
 363     }
 364
 365     // some types, for example longtext, are reported as
 366     // "longtext character set latin7" when their charset and / or collation
 367     // differs from the ones of the corresponding database.
 368     // rtrim the type, for cases like "float unsigned"
 369     $type = rtrim(
 370         preg_replace('/[\s]character set[\s][\S]+/', '', $type)
 371     );
 372
 373     /**
 374      * old column attributes
 375      */
 376     if ($is_backup) {
 377         // old column name
 378         if (isset($columnMeta['Field'])) {
 379             $form_params['field_orig[' . $columnNumber . ']']
 380                 = $columnMeta['Field'];
 381             if (isset($columnMeta['column_status'])
 382                 && ! $columnMeta['column_status']['isEditable']
 383             ) {
 384                 $form_params['field_name[' . $columnNumber . ']']
 385                     = $columnMeta['Field'];
 386             }
 387         } else {
 388             $form_params['field_orig[' . $columnNumber . ']'] = '';
 389         }
 390
 391         // old column type
 392         if (isset($columnMeta['Type'])) {
 393             // keep in uppercase because the new type will be in uppercase
 394             $form_params['field_type_orig[' . $columnNumber . ']'] = mb_strtoupper($type);
 395             if (isset($columnMeta['column_status'])
 396                 && ! $columnMeta['column_status']['isEditable']
 397             ) {
 398                 $form_params['field_type[' . $columnNumber . ']'] = mb_strtoupper($type);
 399             }
 400         } else {
 401             $form_params['field_type_orig[' . $columnNumber . ']'] = '';
 402         }
 403
 404         // old column length
 405         $form_params['field_length_orig[' . $columnNumber . ']'] = $length;
 406
 407         // old column default
 408         $form_params = array_merge(
 409             $form_params,
 410             [
 411                 "field_default_value_orig[${columnNumber}]" => Util::getValueByKey(
 412                     $columnMeta,
 413                     'Default',
 414                     ''
 415                 ),
 416                 "field_default_type_orig[${columnNumber}]"  => Util::getValueByKey(
 417                     $columnMeta,
 418                     'DefaultType',
 419                     ''
 420                 ),
 421                 "field_collation_orig[${columnNumber}]"     => Util::getValueByKey(
 422                     $columnMeta,
 423                     'Collation',
 424                     ''
 425                 ),
 426                 "field_attribute_orig[${columnNumber}]"     => trim(
 427                     Util::getValueByKey($extracted_columnspec, 'attribute', '')
 428                 ),
 429                 "field_null_orig[${columnNumber}]"          => Util::getValueByKey(
 430                     $columnMeta,
 431                     'Null',
 432                     ''
 433                 ),
 434                 "field_extra_orig[${columnNumber}]"         => Util::getValueByKey(
 435                     $columnMeta,
 436                     'Extra',
 437                     ''
 438                 ),
 439                 "field_comments_orig[${columnNumber}]"      => Util::getValueByKey(
 440                     $columnMeta,
 441                     'Comment',
 442                     ''
 443                 ),
 444                 "field_virtuality_orig[${columnNumber}]"    => Util::getValueByKey(
 445                     $columnMeta,
 446                     'Virtuality',
 447                     ''
 448                 ),
 449                 "field_expression_orig[${columnNumber}]"    => Util::getValueByKey(
 450                     $columnMeta,
 451                     'Expression',
 452                     ''
 453                 ),
 454             ]
 455         );
 456     }
 457
 458     $default_value = '';
 459     $type_upper = mb_strtoupper($type);
 460
 461     // For a TIMESTAMP, do not show the string "CURRENT_TIMESTAMP" as a default value
 462     if (isset($columnMeta['DefaultValue'])) {
 463         $default_value = $columnMeta['DefaultValue'];
 464     }
 465     if ($type_upper == 'BIN)') {
 466         $default_value = Util::convertBitDefaultValue($columnMeta['DefaultValue']);
 467     } elseif ($type_upper == 'BINARY' || $type_upper == 'VARBINARY') {
 468         $default_value = bin2hex($columnMeta['DefaultValue']);
 469     }
 470
 471     $content_cells[$columnNumber] = [
 472         'column_number' => $columnNumber,
 473         'column_meta' => $columnMeta,
 474         'type_upper' => $type_upper,
 475         'default_value' => $default_value,
 476         'length_values_input_size' => $length_values_input_size,
 477         'length' => $length,
 478         'extracted_columnspec' => $extracted_columnspec,
 479         'submit_attribute' => $submit_attribute,
 480         'comments_map' => $comments_map,
 481         'fields_meta' => isset($fields_meta) ? $fields_meta : null,
 482         'is_backup' => $is_backup,
 483         'move_columns' => $move_columns,
 484         'cfg_relation' => $cfgRelation,
 485         'available_mime' => $available_mime,
 486         'mime_map' => isset($mime_map) ? $mime_map : [],
 487     ];
 488 } // end for
 489
 490 $partitionDetails = TablePartitionDefinition::getDetails();
 491
 492 $charsets = Charsets::getCharsets($GLOBALS['dbi'], $GLOBALS['cfg']['Server']['DisableIS']);
 493 $collations = Charsets::getCollations($GLOBALS['dbi'], $GLOBALS['cfg']['Server']['DisableIS']);
 494 $charsetsList = [];
 495 /** @var Charset $charset */
 496 foreach ($charsets as $charset) {
 497     $collationsList = [];
 498     /** @var Collation $collation */
 499     foreach ($collations[$charset->getName()] as $collation) {
 500         $collationsList[] = [
 501             'name' => $collation->getName(),
 502             'description' => $collation->getDescription(),
 503         ];
 504     }
 505     $charsetsList[] = [
 506         'name' => $charset->getName(),
 507         'description' => $charset->getDescription(),
 508         'collations' => $collationsList,
 509     ];
 510 }
 511
 512 $html = $template->render('columns_definitions/column_definitions_form', [
 513     'is_backup' => $is_backup,
 514     'fields_meta' => isset($fields_meta) ? $fields_meta : null,
 515     'mimework' => $cfgRelation['mimework'],
 516     'action' => $action,
 517     'form_params' => $form_params,
 518     'content_cells' => $content_cells,
 519     'partition_details' => $partitionDetails,
 520     'primary_indexes' => isset($_POST['primary_indexes']) ? $_POST['primary_indexes'] : null,
 521     'unique_indexes' => isset($_POST['unique_indexes']) ? $_POST['unique_indexes'] : null,
 522     'indexes' => isset($_POST['indexes']) ? $_POST['indexes'] : null,
 523     'fulltext_indexes' => isset($_POST['fulltext_indexes']) ? $_POST['fulltext_indexes'] : null,
 524     'spatial_indexes' => isset($_POST['spatial_indexes']) ? $_POST['spatial_indexes'] : null,
 525     'table' => isset($_POST['table']) ? $_POST['table'] : null,
 526     'comment' => isset($_POST['comment']) ? $_POST['comment'] : null,
 527     'tbl_collation' => isset($_POST['tbl_collation']) ? $_POST['tbl_collation'] : null,
 528     'charsets' => $charsetsList,
 529     'tbl_storage_engine' => isset($_POST['tbl_storage_engine']) ? $_POST['tbl_storage_engine'] : null,
 530     'connection' => isset($_POST['connection']) ? $_POST['connection'] : null,
 531     'change_column' => isset($_POST['change_column']) ? $_POST['change_column'] : null,
 532     'is_virtual_columns_supported' => Util::isVirtualColumnsSupported(),
 533     'browse_mime' => isset($GLOBALS['cfg']['BrowseMIME']) ? $GLOBALS['cfg']['BrowseMIME'] : null,
 534     'server_type' => Util::getServerType(),
 535     'max_rows' => intval($GLOBALS['cfg']['MaxRows']),
 536     'char_editing' => isset($GLOBALS['cfg']['CharEditing']) ? $GLOBALS['cfg']['CharEditing'] : null,
 537     'attribute_types' => $GLOBALS['dbi']->types->getAttributes(),
 538     'privs_available' => ((isset($GLOBALS['col_priv']) ? $GLOBALS['col_priv'] : false)
 539         && (isset($GLOBALS['is_reload_priv']) ? $GLOBALS['is_reload_priv'] : false)
 540     ),
 541     'max_length' => $GLOBALS['dbi']->getVersion() >= 50503 ? 1024 : 255,
 542     'have_partitioning' => Partition::havePartitioning(),
 543     'dbi' => $GLOBALS['dbi'],
 544     'disable_is' => $GLOBALS['cfg']['Server']['DisableIS'],
 545 ]);
 546
 547 unset($form_params);
 548
 549 $response = Response::getInstance();
 550 $response->getHeader()->getScripts()->addFiles(
 551     [
 552         'vendor/jquery/jquery.uitablefilter.js',
 553         'indexes.js',
 554     ]
 555 );
 556 $response->addHTML($html);