src/Table/ColumnsDefinition.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 namespace PhpMyAdmin\Table;
   6
   7 use PhpMyAdmin\Charsets;
   8 use PhpMyAdmin\Config;
   9 use PhpMyAdmin\ConfigStorage\Relation;
  10 use PhpMyAdmin\Current;
  11 use PhpMyAdmin\DatabaseInterface;
  12 use PhpMyAdmin\Partitioning\Partition;
  13 use PhpMyAdmin\Partitioning\TablePartitionDefinition;
  14 use PhpMyAdmin\Query\Compatibility;
  15 use PhpMyAdmin\StorageEngine;
  16 use PhpMyAdmin\Transformations;
  17 use PhpMyAdmin\UserPrivileges;
  18 use PhpMyAdmin\Util;
  19
  20 use function array_keys;
  21 use function array_merge;
  22 use function bin2hex;
  23 use function count;
  24 use function explode;
  25 use function in_array;
  26 use function is_array;
  27 use function mb_strtoupper;
  28 use function preg_quote;
  29 use function preg_replace;
  30 use function rtrim;
  31 use function str_ends_with;
  32 use function stripcslashes;
  33 use function substr;
  34 use function trim;
  35
  36 /**
  37  * Displays the form used to define the structure of the table
  38  */
  39 final class ColumnsDefinition
  40 {
  41     public function __construct(
  42         private DatabaseInterface $dbi,
  43         private Relation $relation,
  44         private Transformations $transformations,
  45     ) {
  46     }
  47
  48     /**
  49      * @param mixed[]|null $selected  Selected
  50      * @param int          $numFields The number of fields
  51      * @psalm-param list<array{
  52      *  Field: string,
  53      *  Type: string,
  54      *  Collation: string|null,
  55      *  Null:'YES'|'NO',
  56      *  Key: string,
  57      *  Default: string|null,
  58      *  Extra: string,
  59      *  Privileges: string,
  60      *  Comment: string
  61      * }>|null $fieldsMeta Fields meta
  62      * @psalm-param '/table/create'|'/table/add-field'|'/table/structure/save' $action
  63      *
  64      * @return array<string, mixed>
  65      */
  66     public function displayForm(
  67         UserPrivileges $userPrivileges,
  68         string $action,
  69         int $numFields = 0,
  70         array|null $selected = null,
  71         array|null $fieldsMeta = null,
  72     ): array {
  73         $GLOBALS['mime_map'] ??= null;
  74
  75         $regenerate = false;
  76         $lengthValuesInputSize = 8;
  77         $contentCells = [];
  78         $formParams = ['db' => Current::$database];
  79
  80         if ($action === '/table/create') {
  81             $formParams['reload'] = 1;
  82         } else {
  83             if ($action === '/table/add-field') {
  84                 $formParams = array_merge($formParams, ['field_where' => $_POST['field_where'] ?? null]);
  85                 if (isset($_POST['field_where'])) {
  86                     $formParams['after_field'] = (string) $_POST['after_field'];
  87                 }
  88             }
  89
  90             $formParams['table'] = Current::$table;
  91         }
  92
  93         $formParams['orig_num_fields'] = $numFields;
  94
  95         $formParams = array_merge(
  96             $formParams,
  97             ['orig_field_where' => $_POST['field_where'] ?? null, 'orig_after_field' => $_POST['after_field'] ?? null],
  98         );
  99
 100         if (is_array($selected)) {
 101             foreach ($selected as $oFldNr => $oFldVal) {
 102                 $formParams['selected[' . $oFldNr . ']'] = $oFldVal;
 103             }
 104         }
 105
 106         $isBackup = $action !== '/table/create' && $action !== '/table/add-field';
 107
 108         $relationParameters = $this->relation->getRelationParameters();
 109
 110         $commentsMap = $this->relation->getComments(Current::$database, Current::$table);
 111
 112         $moveColumns = [];
 113         if ($fieldsMeta !== null) {
 114             $moveColumns = $this->dbi->getTable(Current::$database, Current::$table)->getColumnsMeta();
 115         }
 116
 117         $availableMime = [];
 118         $config = Config::getInstance();
 119         if ($relationParameters->browserTransformationFeature !== null && $config->settings['BrowseMIME']) {
 120             $GLOBALS['mime_map'] = $this->transformations->getMime(Current::$database, Current::$table);
 121             $availableMime = $this->transformations->getAvailableMimeTypes();
 122         }
 123
 124         $mimeTypes = ['input_transformation', 'transformation'];
 125         foreach ($mimeTypes as $mimeType) {
 126             if (! isset($availableMime[$mimeType])) {
 127                 continue;
 128             }
 129
 130             foreach (array_keys($availableMime[$mimeType]) as $mimekey) {
 131                 $availableMime[$mimeType . '_file_quoted'][$mimekey] = preg_quote(
 132                     $availableMime[$mimeType . '_file'][$mimekey],
 133                     '@',
 134                 );
 135             }
 136         }
 137
 138         if (isset($_POST['submit_num_fields']) || isset($_POST['submit_partition_change'])) {
 139             //if adding new fields, set regenerate to keep the original values
 140             $regenerate = true;
 141         }
 142
 143         $foreigners = $this->relation->getForeignKeysData(Current::$database, Current::$table);
 144         $childReferences = null;
 145         // From MySQL 5.6.6 onwards columns with foreign keys can be renamed.
 146         // Hence, no need to get child references
 147         if ($this->dbi->getVersion() < 50606) {
 148             $childReferences = $this->relation->getChildReferences(Current::$database, Current::$table);
 149         }
 150
 151         /** @infection-ignore-all */
 152         for ($columnNumber = 0; $columnNumber < $numFields; $columnNumber++) {
 153             $type = '';
 154             $length = '';
 155             $columnMeta = [];
 156             $submitAttribute = null;
 157             $extractedColumnSpec = [];
 158
 159             if ($regenerate) {
 160                 $columnMeta = $this->getColumnMetaForRegeneratedFields($columnNumber);
 161
 162                 $length = Util::getValueByKey($_POST, ['field_length', $columnNumber], $length);
 163                 $submitAttribute = Util::getValueByKey($_POST, ['field_attribute', $columnNumber], false);
 164                 $commentsMap[$columnMeta['Field']] = Util::getValueByKey($_POST, ['field_comments', $columnNumber]);
 165
 166                 $GLOBALS['mime_map'][$columnMeta['Field']] = array_merge(
 167                     $GLOBALS['mime_map'][$columnMeta['Field']] ?? [],
 168                     [
 169                         'mimetype' => Util::getValueByKey($_POST, ['field_mimetype', $columnNumber]),
 170                         'transformation' => Util::getValueByKey(
 171                             $_POST,
 172                             ['field_transformation' , $columnNumber],
 173                         ),
 174                         'transformation_options' => Util::getValueByKey(
 175                             $_POST,
 176                             ['field_transformation_options' , $columnNumber],
 177                         ),
 178                     ],
 179                 );
 180             } elseif (isset($fieldsMeta[$columnNumber])) {
 181                 $columnMeta = $fieldsMeta[$columnNumber];
 182                 $virtual = ['VIRTUAL', 'PERSISTENT', 'VIRTUAL GENERATED', 'STORED GENERATED'];
 183                 if (in_array($columnMeta['Extra'], $virtual, true)) {
 184                     $tableObj = new Table(Current::$table, Current::$database, $this->dbi);
 185                     $expressions = $tableObj->getColumnGenerationExpression($columnMeta['Field']);
 186                     $columnMeta['Expression'] = is_array($expressions) ? $expressions[$columnMeta['Field']] : null;
 187                 }
 188
 189                 $columnMetaDefault = self::decorateColumnMetaDefault(
 190                     $columnMeta['Type'],
 191                     $columnMeta['Default'],
 192                     $columnMeta['Null'] === 'YES',
 193                 );
 194                 $columnMeta = array_merge($columnMeta, $columnMetaDefault);
 195             }
 196
 197             if (isset($columnMeta['Type'])) {
 198                 $extractedColumnSpec = Util::extractColumnSpec($columnMeta['Type']);
 199                 if ($extractedColumnSpec['type'] === 'bit') {
 200                     $columnMeta['Default'] = Util::convertBitDefaultValue($columnMeta['Default']);
 201                 }
 202
 203                 $type = $extractedColumnSpec['type'];
 204                 if ($length == '') {
 205                     $length = $extractedColumnSpec['spec_in_brackets'];
 206                 }
 207             } else {
 208                 // creating a column
 209                 $columnMeta['Type'] = '';
 210             }
 211
 212             // Variable tell if current column is bound in a foreign key constraint or not.
 213             // MySQL version from 5.6.6 allow renaming columns with foreign keys
 214             if (isset($columnMeta['Field'], $formParams['table']) && $this->dbi->getVersion() < 50606) {
 215                 $columnMeta['column_status'] = $this->relation->checkChildForeignReferences(
 216                     $formParams['db'],
 217                     $formParams['table'],
 218                     $columnMeta['Field'],
 219                     $foreigners,
 220                     $childReferences,
 221                 );
 222             }
 223
 224             // some types, for example longtext, are reported as
 225             // "longtext character set latin7" when their charset and / or collation
 226             // differs from the ones of the corresponding database.
 227             // rtrim the type, for cases like "float unsigned"
 228             $type = rtrim(
 229                 preg_replace('/[\s]character set[\s][\S]+/', '', $type),
 230             );
 231
 232             /**
 233              * old column attributes
 234              */
 235             if ($isBackup) {
 236                 // old column name
 237                 if (isset($columnMeta['Field'])) {
 238                     $formParams['field_orig[' . $columnNumber . ']'] = $columnMeta['Field'];
 239                     if (isset($columnMeta['column_status']) && ! $columnMeta['column_status']['isEditable']) {
 240                         $formParams['field_name[' . $columnNumber . ']'] = $columnMeta['Field'];
 241                     }
 242                 } else {
 243                     $formParams['field_orig[' . $columnNumber . ']'] = '';
 244                 }
 245
 246                 // old column type
 247                 // keep in uppercase because the new type will be in uppercase
 248                 $formParams['field_type_orig[' . $columnNumber . ']'] = mb_strtoupper($type);
 249                 if (isset($columnMeta['column_status']) && ! $columnMeta['column_status']['isEditable']) {
 250                     $formParams['field_type[' . $columnNumber . ']'] = mb_strtoupper($type);
 251                 }
 252
 253                 // old column length
 254                 $formParams['field_length_orig[' . $columnNumber . ']'] = $length;
 255
 256                 // old column default
 257                 $formParams = array_merge(
 258                     $formParams,
 259                     [
 260                         'field_default_value_orig[' . $columnNumber . ']' => $columnMeta['Default'] ?? '',
 261                         'field_default_type_orig[' . $columnNumber . ']' => $columnMeta['DefaultType'] ?? '',
 262                         'field_collation_orig[' . $columnNumber . ']' => $columnMeta['Collation'] ?? '',
 263                         'field_attribute_orig[' . $columnNumber . ']' => trim($extractedColumnSpec['attribute'] ?? ''),
 264                         'field_null_orig[' . $columnNumber . ']' => $columnMeta['Null'] ?? '',
 265                         'field_extra_orig[' . $columnNumber . ']' => $columnMeta['Extra'] ?? '',
 266                         'field_comments_orig[' . $columnNumber . ']' => $columnMeta['Comment'] ?? '',
 267                         'field_virtuality_orig[' . $columnNumber . ']' => $columnMeta['Virtuality'] ?? '',
 268                         'field_expression_orig[' . $columnNumber . ']' => $columnMeta['Expression'] ?? '',
 269                     ],
 270                 );
 271             }
 272
 273             $defaultValue = '';
 274             $typeUpper = mb_strtoupper($type);
 275
 276             // For a TIMESTAMP, do not show the string "CURRENT_TIMESTAMP" as a default value
 277             if (isset($columnMeta['DefaultValue'])) {
 278                 $defaultValue = $columnMeta['DefaultValue'];
 279             }
 280
 281             if ($typeUpper === 'BIT') {
 282                 $defaultValue = ! empty($columnMeta['DefaultValue'])
 283                     ? Util::convertBitDefaultValue($columnMeta['DefaultValue'])
 284                     : '';
 285             } elseif ($typeUpper === 'BINARY' || $typeUpper === 'VARBINARY') {
 286                 $defaultValue = bin2hex((string) $columnMeta['DefaultValue']);
 287             }
 288
 289             $contentCells[$columnNumber] = [
 290                 'column_number' => $columnNumber,
 291                 'column_meta' => $columnMeta,
 292                 'type_upper' => $typeUpper,
 293                 'default_value' => $defaultValue,
 294                 'length_values_input_size' => $lengthValuesInputSize,
 295                 'length' => $length,
 296                 'extracted_columnspec' => $extractedColumnSpec,
 297                 'submit_attribute' => $submitAttribute,
 298                 'comments_map' => $commentsMap,
 299                 'fields_meta' => $fieldsMeta ?? null,
 300                 'is_backup' => $isBackup,
 301                 'move_columns' => $moveColumns,
 302                 'available_mime' => $availableMime,
 303                 'mime_map' => $GLOBALS['mime_map'] ?? [],
 304             ];
 305         }
 306
 307         $partitionDetails = TablePartitionDefinition::getDetails();
 308
 309         $charsets = Charsets::getCharsets($this->dbi, $config->selectedServer['DisableIS']);
 310         $collations = Charsets::getCollations($this->dbi, $config->selectedServer['DisableIS']);
 311         $charsetsList = [];
 312         foreach ($charsets as $charset) {
 313             $collationsList = [];
 314             foreach ($collations[$charset->getName()] as $collation) {
 315                 $collationsList[] = ['name' => $collation->getName(), 'description' => $collation->getDescription()];
 316             }
 317
 318             $charsetsList[] = [
 319                 'name' => $charset->getName(),
 320                 'description' => $charset->getDescription(),
 321                 'collations' => $collationsList,
 322             ];
 323         }
 324
 325         $storageEngines = StorageEngine::getArray();
 326         $isIntegersLengthRestricted = Compatibility::isIntegersLengthRestricted($this->dbi);
 327
 328         return [
 329             'is_backup' => $isBackup,
 330             'fields_meta' => $fieldsMeta ?? null,
 331             'relation_parameters' => $relationParameters,
 332             'action' => $action,
 333             'form_params' => $formParams,
 334             'content_cells' => $contentCells,
 335             'partition_details' => $partitionDetails,
 336             'primary_indexes' => $_POST['primary_indexes'] ?? null,
 337             'unique_indexes' => $_POST['unique_indexes'] ?? null,
 338             'indexes' => $_POST['indexes'] ?? null,
 339             'fulltext_indexes' => $_POST['fulltext_indexes'] ?? null,
 340             'spatial_indexes' => $_POST['spatial_indexes'] ?? null,
 341             'table' => $_POST['table'] ?? null,
 342             'comment' => $_POST['comment'] ?? null,
 343             'tbl_collation' => $_POST['tbl_collation'] ?? null,
 344             'charsets' => $charsetsList,
 345             'tbl_storage_engine' => $_POST['tbl_storage_engine'] ?? null,
 346             'storage_engines' => $storageEngines,
 347             'connection' => $_POST['connection'] ?? null,
 348             'change_column' => $_POST['change_column'] ?? $_GET['change_column'] ?? null,
 349             'is_virtual_columns_supported' => Compatibility::isVirtualColumnsSupported($this->dbi->getVersion()),
 350             'is_integers_length_restricted' => $isIntegersLengthRestricted,
 351             'browse_mime' => $config->settings['BrowseMIME'] ?? null,
 352             'supports_stored_keyword' => Compatibility::supportsStoredKeywordForVirtualColumns(
 353                 $this->dbi->getVersion(),
 354             ),
 355             'server_version' => $this->dbi->getVersion(),
 356             'max_rows' => (int) $config->settings['MaxRows'],
 357             'char_editing' => $config->settings['CharEditing'] ?? null,
 358             'attribute_types' => $this->dbi->types->getAttributes(),
 359             'privs_available' => $userPrivileges->column && $userPrivileges->isReload,
 360             'max_length' => $this->dbi->getVersion() >= 50503 ? 1024 : 255,
 361             'have_partitioning' => Partition::havePartitioning(),
 362             'disable_is' => $config->selectedServer['DisableIS'],
 363         ];
 364     }
 365
 366     /**
 367      * Set default type and default value according to the column metadata
 368      *
 369      * @return array{DefaultType:string, DefaultValue: string, Default?: string}
 370      */
 371     public static function decorateColumnMetaDefault(string $type, string|null $default, bool $isNull): array
 372     {
 373         $metaDefault = ['DefaultType' => 'USER_DEFINED', 'DefaultValue' => ''];
 374
 375         switch ($default) {
 376             case null:
 377                 $metaDefault['DefaultType'] = $isNull ? 'NULL' : 'NONE';
 378
 379                 break;
 380             case 'CURRENT_TIMESTAMP':
 381             case 'current_timestamp()':
 382                 $metaDefault['DefaultType'] = 'CURRENT_TIMESTAMP';
 383
 384                 break;
 385             case 'UUID':
 386             case 'uuid()':
 387                 $metaDefault['DefaultType'] = 'UUID';
 388
 389                 break;
 390             default:
 391                 $metaDefault['DefaultValue'] = $default;
 392
 393                 if (str_ends_with($type, 'text')) {
 394                     $textDefault = substr($default, 1, -1);
 395                     $metaDefault['Default'] = stripcslashes($textDefault);
 396                 }
 397
 398                 break;
 399         }
 400
 401         return $metaDefault;
 402     }
 403
 404     /** @return mixed[] */
 405     private function getColumnMetaForRegeneratedFields(int $columnNumber): array
 406     {
 407         $columnMeta = [
 408             'Field' => Util::getValueByKey($_POST, ['field_name', $columnNumber]),
 409             'Type' => Util::getValueByKey($_POST, ['field_type', $columnNumber]),
 410             'Collation' => Util::getValueByKey($_POST, ['field_collation', $columnNumber], ''),
 411             'Null' => Util::getValueByKey($_POST, ['field_null', $columnNumber], ''),
 412             'DefaultType' => Util::getValueByKey($_POST, ['field_default_type', $columnNumber], 'NONE'),
 413             'DefaultValue' => Util::getValueByKey($_POST, ['field_default_value', $columnNumber], ''),
 414             'Extra' => Util::getValueByKey($_POST, ['field_extra', $columnNumber]),
 415             'Virtuality' => Util::getValueByKey($_POST, ['field_virtuality', $columnNumber], ''),
 416             'Expression' => Util::getValueByKey($_POST, ['field_expression', $columnNumber], ''),
 417             'Key' => '',
 418             'Comment' => false,
 419         ];
 420
 421         $parts = explode(
 422             '_',
 423             Util::getValueByKey($_POST, ['field_key', $columnNumber], ''),
 424             2,
 425         );
 426         if (count($parts) === 2 && $parts[1] == $columnNumber) {
 427             $columnMeta['Key'] = match ($parts[0]) {
 428                 'primary' => 'PRI',
 429                 'index' => 'MUL',
 430                 'unique' => 'UNI',
 431                 'fulltext' => 'FULLTEXT',
 432                 'spatial' => 'SPATIAL',
 433                 default => '',
 434             };
 435         }
 436
 437         $columnMeta['Default'] = match ($columnMeta['DefaultType']) {
 438             'NONE' => null,
 439             'USER_DEFINED' => $columnMeta['DefaultValue'],
 440             default => $columnMeta['DefaultType'],
 441         };
 442
 443         return $columnMeta;
 444     }
 445 }