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                 break;
 324         }
 325     }
 326
 327     if (isset($columnMeta['Type'])) {
 328         $extracted_columnspec = Util::extractColumnSpec(
 329             $columnMeta['Type']
 330         );
 331         if ($extracted_columnspec['type'] == 'bit') {
 332             $columnMeta['Default']
 333                 = Util::convertBitDefaultValue($columnMeta['Default']);
 334         }
 335         $type = $extracted_columnspec['type'];
 336         if ($length == '') {
 337             $length = $extracted_columnspec['spec_in_brackets'];
 338         }
 339     } else {
 340         // creating a column
 341         $columnMeta['Type'] = '';
 342     }
 343
 344     // Variable tell if current column is bound in a foreign key constraint or not.
 345     // MySQL version from 5.6.6 allow renaming columns with foreign keys
 346     if (isset($columnMeta['Field'])
 347         && isset($form_params['table'])
 348         && $GLOBALS['dbi']->getVersion() < 50606
 349     ) {
 350         $columnMeta['column_status'] = $relation->checkChildForeignReferences(
 351             $form_params['db'],
 352             $form_params['table'],
 353             $columnMeta['Field'],
 354             $foreigners,
 355             $child_references
 356         );
 357     }
 358
 359     // some types, for example longtext, are reported as
 360     // "longtext character set latin7" when their charset and / or collation
 361     // differs from the ones of the corresponding database.
 362     // rtrim the type, for cases like "float unsigned"
 363     $type = rtrim(
 364         preg_replace('/[\s]character set[\s][\S]+/', '', $type)
 365     );
 366
 367     /**
 368      * old column attributes
 369      */
 370     if ($is_backup) {
 371         // old column name
 372         if (isset($columnMeta['Field'])) {
 373             $form_params['field_orig[' . $columnNumber . ']']
 374                 = $columnMeta['Field'];
 375             if (isset($columnMeta['column_status'])
 376                 && ! $columnMeta['column_status']['isEditable']
 377             ) {
 378                 $form_params['field_name[' . $columnNumber . ']']
 379                     = $columnMeta['Field'];
 380             }
 381         } else {
 382             $form_params['field_orig[' . $columnNumber . ']'] = '';
 383         }
 384
 385         // old column type
 386         if (isset($columnMeta['Type'])) {
 387             // keep in uppercase because the new type will be in uppercase
 388             $form_params['field_type_orig[' . $columnNumber . ']'] = mb_strtoupper($type);
 389             if (isset($columnMeta['column_status'])
 390                 && ! $columnMeta['column_status']['isEditable']
 391             ) {
 392                 $form_params['field_type[' . $columnNumber . ']'] = mb_strtoupper($type);
 393             }
 394         } else {
 395             $form_params['field_type_orig[' . $columnNumber . ']'] = '';
 396         }
 397
 398         // old column length
 399         $form_params['field_length_orig[' . $columnNumber . ']'] = $length;
 400
 401         // old column default
 402         $form_params = array_merge(
 403             $form_params,
 404             [
 405                 "field_default_value_orig[${columnNumber}]" => Util::getValueByKey(
 406                     $columnMeta,
 407                     'Default',
 408                     ''
 409                 ),
 410                 "field_default_type_orig[${columnNumber}]"  => Util::getValueByKey(
 411                     $columnMeta,
 412                     'DefaultType',
 413                     ''
 414                 ),
 415                 "field_collation_orig[${columnNumber}]"     => Util::getValueByKey(
 416                     $columnMeta,
 417                     'Collation',
 418                     ''
 419                 ),
 420                 "field_attribute_orig[${columnNumber}]"     => trim(
 421                     Util::getValueByKey($extracted_columnspec, 'attribute', '')
 422                 ),
 423                 "field_null_orig[${columnNumber}]"          => Util::getValueByKey(
 424                     $columnMeta,
 425                     'Null',
 426                     ''
 427                 ),
 428                 "field_extra_orig[${columnNumber}]"         => Util::getValueByKey(
 429                     $columnMeta,
 430                     'Extra',
 431                     ''
 432                 ),
 433                 "field_comments_orig[${columnNumber}]"      => Util::getValueByKey(
 434                     $columnMeta,
 435                     'Comment',
 436                     ''
 437                 ),
 438                 "field_virtuality_orig[${columnNumber}]"    => Util::getValueByKey(
 439                     $columnMeta,
 440                     'Virtuality',
 441                     ''
 442                 ),
 443                 "field_expression_orig[${columnNumber}]"    => Util::getValueByKey(
 444                     $columnMeta,
 445                     'Expression',
 446                     ''
 447                 ),
 448             ]
 449         );
 450     }
 451
 452     $default_value = '';
 453     $type_upper = mb_strtoupper($type);
 454
 455     // For a TIMESTAMP, do not show the string "CURRENT_TIMESTAMP" as a default value
 456     if (isset($columnMeta['DefaultValue'])) {
 457         $default_value = $columnMeta['DefaultValue'];
 458     }
 459     if ($type_upper == 'BIN)') {
 460         $default_value = Util::convertBitDefaultValue($columnMeta['DefaultValue']);
 461     } elseif ($type_upper == 'BINARY' || $type_upper == 'VARBINARY') {
 462         $default_value = bin2hex($columnMeta['DefaultValue']);
 463     }
 464
 465     $content_cells[$columnNumber] = [
 466         'column_number' => $columnNumber,
 467         'column_meta' => $columnMeta,
 468         'type_upper' => $type_upper,
 469         'default_value' => $default_value,
 470         'length_values_input_size' => $length_values_input_size,
 471         'length' => $length,
 472         'extracted_columnspec' => $extracted_columnspec,
 473         'submit_attribute' => $submit_attribute,
 474         'comments_map' => $comments_map,
 475         'fields_meta' => isset($fields_meta) ? $fields_meta : null,
 476         'is_backup' => $is_backup,
 477         'move_columns' => $move_columns,
 478         'cfg_relation' => $cfgRelation,
 479         'available_mime' => $available_mime,
 480         'mime_map' => isset($mime_map) ? $mime_map : [],
 481     ];
 482 } // end for
 483
 484 $partitionDetails = TablePartitionDefinition::getDetails();
 485
 486 $charsets = Charsets::getCharsets($GLOBALS['dbi'], $GLOBALS['cfg']['Server']['DisableIS']);
 487 $collations = Charsets::getCollations($GLOBALS['dbi'], $GLOBALS['cfg']['Server']['DisableIS']);
 488 $charsetsList = [];
 489 /** @var Charset $charset */
 490 foreach ($charsets as $charset) {
 491     $collationsList = [];
 492     /** @var Collation $collation */
 493     foreach ($collations[$charset->getName()] as $collation) {
 494         $collationsList[] = [
 495             'name' => $collation->getName(),
 496             'description' => $collation->getDescription(),
 497         ];
 498     }
 499     $charsetsList[] = [
 500         'name' => $charset->getName(),
 501         'description' => $charset->getDescription(),
 502         'collations' => $collationsList,
 503     ];
 504 }
 505
 506 $html = $template->render('columns_definitions/column_definitions_form', [
 507     'is_backup' => $is_backup,
 508     'fields_meta' => isset($fields_meta) ? $fields_meta : null,
 509     'mimework' => $cfgRelation['mimework'],
 510     'action' => $action,
 511     'form_params' => $form_params,
 512     'content_cells' => $content_cells,
 513     'partition_details' => $partitionDetails,
 514     'primary_indexes' => isset($_POST['primary_indexes']) ? $_POST['primary_indexes'] : null,
 515     'unique_indexes' => isset($_POST['unique_indexes']) ? $_POST['unique_indexes'] : null,
 516     'indexes' => isset($_POST['indexes']) ? $_POST['indexes'] : null,
 517     'fulltext_indexes' => isset($_POST['fulltext_indexes']) ? $_POST['fulltext_indexes'] : null,
 518     'spatial_indexes' => isset($_POST['spatial_indexes']) ? $_POST['spatial_indexes'] : null,
 519     'table' => isset($_POST['table']) ? $_POST['table'] : null,
 520     'comment' => isset($_POST['comment']) ? $_POST['comment'] : null,
 521     'tbl_collation' => isset($_POST['tbl_collation']) ? $_POST['tbl_collation'] : null,
 522     'charsets' => $charsetsList,
 523     'tbl_storage_engine' => isset($_POST['tbl_storage_engine']) ? $_POST['tbl_storage_engine'] : null,
 524     'connection' => isset($_POST['connection']) ? $_POST['connection'] : null,
 525     'change_column' => isset($_POST['change_column']) ? $_POST['change_column'] : null,
 526     'is_virtual_columns_supported' => Util::isVirtualColumnsSupported(),
 527     'browse_mime' => isset($GLOBALS['cfg']['BrowseMIME']) ? $GLOBALS['cfg']['BrowseMIME'] : null,
 528     'server_type' => Util::getServerType(),
 529     'max_rows' => intval($GLOBALS['cfg']['MaxRows']),
 530     'char_editing' => isset($GLOBALS['cfg']['CharEditing']) ? $GLOBALS['cfg']['CharEditing'] : null,
 531     'attribute_types' => $GLOBALS['dbi']->types->getAttributes(),
 532     'privs_available' => isset($GLOBALS['col_priv']) ? $GLOBALS['col_priv'] : false
 533         && isset($GLOBALS['is_reload_priv']) ? $GLOBALS['is_reload_priv'] : false,
 534     'max_length' => $GLOBALS['dbi']->getVersion() >= 50503 ? 1024 : 255,
 535     'have_partitioning' => Partition::havePartitioning(),
 536     'dbi' => $GLOBALS['dbi'],
 537     'disable_is' => $GLOBALS['cfg']['Server']['DisableIS'],
 538 ]);
 539
 540 unset($form_params);
 541
 542 $response = Response::getInstance();
 543 $response->getHeader()->getScripts()->addFiles(
 544     [
 545         'vendor/jquery/jquery.uitablefilter.js',
 546         'indexes.js',
 547     ]
 548 );
 549 $response->addHTML($html);