src/CreateAddField.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 namespace PhpMyAdmin;
   6
   7 use PhpMyAdmin\Html\Generator;
   8 use PhpMyAdmin\Identifiers\DatabaseName;
   9 use PhpMyAdmin\Table\Table;
  10
  11 use function count;
  12 use function implode;
  13 use function in_array;
  14 use function json_decode;
  15 use function preg_replace;
  16 use function trim;
  17
  18 /**
  19  * Set of functions for /table/create and /table/add-field
  20  */
  21 class CreateAddField
  22 {
  23     public function __construct(private DatabaseInterface $dbi)
  24     {
  25     }
  26
  27     /**
  28      * Transforms the radio button field_key into 4 arrays
  29      *
  30      * @return mixed[] An array of arrays which represents column keys for each index type
  31      * @psalm-return array{int, array, array, array, array, array}
  32      */
  33     private function getIndexedColumns(): array
  34     {
  35         $fieldCount = count($_POST['field_name']);
  36         $fieldPrimary = json_decode($_POST['primary_indexes'], true);
  37         $fieldIndex = json_decode($_POST['indexes'], true);
  38         $fieldUnique = json_decode($_POST['unique_indexes'], true);
  39         $fieldFullText = json_decode($_POST['fulltext_indexes'], true);
  40         $fieldSpatial = json_decode($_POST['spatial_indexes'], true);
  41
  42         return [$fieldCount, $fieldPrimary, $fieldIndex, $fieldUnique, $fieldFullText, $fieldSpatial];
  43     }
  44
  45     /**
  46      * Initiate the column creation statement according to the table creation or
  47      * add columns to a existing table
  48      *
  49      * @param int  $fieldCount    number of columns
  50      * @param bool $isCreateTable true if requirement is to get the statement
  51      *                            for table creation
  52      *
  53      * @return mixed[] An array of initial sql statements
  54      *                             according to the request
  55      */
  56     private function buildColumnCreationStatement(
  57         int $fieldCount,
  58         bool $isCreateTable = true,
  59     ): array {
  60         $definitions = [];
  61         $previousField = -1;
  62         for ($i = 0; $i < $fieldCount; ++$i) {
  63             if ($_POST['field_name'][$i] === '') {
  64                 continue;
  65             }
  66
  67             $definition = $this->getStatementPrefix($isCreateTable) . Table::generateFieldSpec(
  68                 trim($_POST['field_name'][$i]),
  69                 $_POST['field_type'][$i],
  70                 $_POST['field_length'][$i],
  71                 $_POST['field_attribute'][$i],
  72                 $_POST['field_collation'][$i] ?? '',
  73                 $_POST['field_null'][$i] ?? 'NO',
  74                 $_POST['field_default_type'][$i],
  75                 $_POST['field_default_value'][$i],
  76                 $_POST['field_extra'][$i] ?? '',
  77                 $_POST['field_comments'][$i] ?? '',
  78                 $_POST['field_virtuality'][$i] ?? '',
  79                 $_POST['field_expression'][$i] ?? '',
  80             );
  81
  82             $definition .= $this->setColumnCreationStatementSuffix($previousField, $isCreateTable);
  83             $previousField = $i;
  84             $definitions[] = $definition;
  85         }
  86
  87         return $definitions;
  88     }
  89
  90     /**
  91      * Set column creation suffix according to requested position of the new column
  92      *
  93      * @param int  $previousField previous field for ALTER statement
  94      * @param bool $isCreateTable true if requirement is to get the statement
  95      *                            for table creation
  96      *
  97      * @return string suffix
  98      */
  99     private function setColumnCreationStatementSuffix(
 100         int $previousField,
 101         bool $isCreateTable = true,
 102     ): string {
 103         // no suffix is needed if request is a table creation
 104         if ($isCreateTable) {
 105             return ' ';
 106         }
 107
 108         if ((string) $_POST['field_where'] === 'last') {
 109             return ' ';
 110         }
 111
 112         // Only the first field can be added somewhere other than at the end
 113         if ($previousField === -1) {
 114             if ((string) $_POST['field_where'] === 'first') {
 115                 return ' FIRST';
 116             }
 117
 118             if (! empty($_POST['after_field'])) {
 119                 return ' AFTER '
 120                         . Util::backquote($_POST['after_field']);
 121             }
 122
 123             return ' ';
 124         }
 125
 126         return ' AFTER ' . Util::backquote($_POST['field_name'][$previousField]);
 127     }
 128
 129     /**
 130      * Create relevant index statements
 131      *
 132      * @param mixed[] $index         an array of index columns
 133      * @param string  $indexChoice   index choice that which represents
 134      *                               the index type of $indexed_fields
 135      * @param bool    $isCreateTable true if requirement is to get the statement
 136      *                               for table creation
 137      *
 138      * @return string sql statement for indexes
 139      */
 140     private function buildIndexStatement(
 141         array $index,
 142         string $indexChoice,
 143         bool $isCreateTable = true,
 144     ): string {
 145         if ($index === []) {
 146             return '';
 147         }
 148
 149         $sqlQuery = $this->getStatementPrefix($isCreateTable) . $indexChoice;
 150
 151         if (! empty($index['Key_name']) && $index['Key_name'] !== 'PRIMARY') {
 152             $sqlQuery .= ' ' . Util::backquote($index['Key_name']);
 153         }
 154
 155         $indexFields = [];
 156         foreach ($index['columns'] as $key => $column) {
 157             $indexFields[$key] = Util::backquote($_POST['field_name'][$column['col_index']]);
 158             if (! $column['size']) {
 159                 continue;
 160             }
 161
 162             $indexFields[$key] .= '(' . $column['size'] . ')';
 163         }
 164
 165         $sqlQuery .= ' (' . implode(', ', $indexFields) . ')';
 166
 167         if ($index['Key_block_size']) {
 168             $sqlQuery .= ' KEY_BLOCK_SIZE = '
 169                  . $this->dbi->escapeString($index['Key_block_size']);
 170         }
 171
 172         // specifying index type is allowed only for primary, unique and index only
 173         if (
 174             $index['Index_choice'] !== 'SPATIAL'
 175             && $index['Index_choice'] !== 'FULLTEXT'
 176             && in_array($index['Index_type'], Index::getIndexTypes())
 177         ) {
 178             $sqlQuery .= ' USING ' . $index['Index_type'];
 179         }
 180
 181         if ($index['Index_choice'] === 'FULLTEXT' && $index['Parser']) {
 182             $sqlQuery .= ' WITH PARSER ' . $this->dbi->escapeString($index['Parser']);
 183         }
 184
 185         if ($index['Index_comment']) {
 186             $sqlQuery .= ' COMMENT ' . $this->dbi->quoteString($index['Index_comment']);
 187         }
 188
 189         return $sqlQuery;
 190     }
 191
 192     /**
 193      * Statement prefix for the buildColumnCreationStatement()
 194      *
 195      * @param bool $isCreateTable true if requirement is to get the statement
 196      *                            for table creation
 197      *
 198      * @return string prefix
 199      */
 200     private function getStatementPrefix(bool $isCreateTable = true): string
 201     {
 202         return $isCreateTable ? '' : 'ADD ';
 203     }
 204
 205     /**
 206      * Returns sql statement according to the column and index specifications as
 207      * requested
 208      *
 209      * @param bool $isCreateTable true if requirement is to get the statement
 210      *                            for table creation
 211      *
 212      * @return string sql statement
 213      */
 214     private function getColumnCreationStatements(bool $isCreateTable = true): string
 215     {
 216         $sqlStatement = '';
 217         [
 218             $fieldCount,
 219             $fieldPrimary,
 220             $fieldIndex,
 221             $fieldUnique,
 222             $fieldFullText,
 223             $fieldSpatial,
 224         ] = $this->getIndexedColumns();
 225         $definitions = $this->buildColumnCreationStatement($fieldCount, $isCreateTable);
 226
 227         // Builds the PRIMARY KEY statements
 228         if (isset($fieldPrimary[0])) {
 229             $definitions[] = $this->buildIndexStatement($fieldPrimary[0], 'PRIMARY KEY', $isCreateTable);
 230         }
 231
 232         // Builds the INDEX statements
 233         foreach ($fieldIndex as $index) {
 234             $definitions[] = $this->buildIndexStatement($index, 'INDEX', $isCreateTable);
 235         }
 236
 237         // Builds the UNIQUE statements
 238         foreach ($fieldUnique as $index) {
 239             $definitions[] = $this->buildIndexStatement($index, 'UNIQUE', $isCreateTable);
 240         }
 241
 242         // Builds the FULLTEXT statements
 243         foreach ($fieldFullText as $index) {
 244             $definitions[] = $this->buildIndexStatement($index, 'FULLTEXT', $isCreateTable);
 245         }
 246
 247         // Builds the SPATIAL statements
 248         foreach ($fieldSpatial as $index) {
 249             $definitions[] = $this->buildIndexStatement($index, 'SPATIAL', $isCreateTable);
 250         }
 251
 252         if ($definitions !== []) {
 253             $sqlStatement = implode(', ', $definitions);
 254         }
 255
 256         return preg_replace('@, $@', '', $sqlStatement) ?? '';
 257     }
 258
 259     /**
 260      * Returns the partitioning clause
 261      *
 262      * @return string partitioning clause
 263      */
 264     public function getPartitionsDefinition(): string
 265     {
 266         $sqlQuery = '';
 267         if (
 268             ! empty($_POST['partition_by'])
 269             && ! empty($_POST['partition_expr'])
 270             && ! empty($_POST['partition_count'])
 271             && $_POST['partition_count'] > 1
 272         ) {
 273             $sqlQuery .= ' PARTITION BY ' . $_POST['partition_by']
 274                 . ' (' . $_POST['partition_expr'] . ')'
 275                 . ' PARTITIONS ' . $_POST['partition_count'];
 276         }
 277
 278         if (
 279             ! empty($_POST['subpartition_by'])
 280             && ! empty($_POST['subpartition_expr'])
 281             && ! empty($_POST['subpartition_count'])
 282             && $_POST['subpartition_count'] > 1
 283         ) {
 284             $sqlQuery .= ' SUBPARTITION BY ' . $_POST['subpartition_by']
 285                . ' (' . $_POST['subpartition_expr'] . ')'
 286                . ' SUBPARTITIONS ' . $_POST['subpartition_count'];
 287         }
 288
 289         if (! empty($_POST['partitions'])) {
 290             $partitions = [];
 291             foreach ($_POST['partitions'] as $partition) {
 292                 $partitions[] = $this->getPartitionDefinition($partition);
 293             }
 294
 295             $sqlQuery .= ' (' . implode(', ', $partitions) . ')';
 296         }
 297
 298         return $sqlQuery;
 299     }
 300
 301     /**
 302      * Returns the definition of a partition/subpartition
 303      *
 304      * @param mixed[] $partition      array of partition/subpartition details
 305      * @param bool    $isSubPartition whether a subpartition
 306      *
 307      * @return string partition/subpartition definition
 308      */
 309     private function getPartitionDefinition(
 310         array $partition,
 311         bool $isSubPartition = false,
 312     ): string {
 313         $sqlQuery = ' ' . ($isSubPartition ? 'SUB' : '') . 'PARTITION ';
 314         $sqlQuery .= $partition['name'];
 315
 316         if (! empty($partition['value_type'])) {
 317             $sqlQuery .= ' VALUES ' . $partition['value_type'];
 318
 319             if ($partition['value_type'] !== 'LESS THAN MAXVALUE') {
 320                 $sqlQuery .= ' (' . $partition['value'] . ')';
 321             }
 322         }
 323
 324         if (! empty($partition['engine'])) {
 325             $sqlQuery .= ' ENGINE = ' . $partition['engine'];
 326         }
 327
 328         if (! empty($partition['comment'])) {
 329             $sqlQuery .= " COMMENT = '" . $partition['comment'] . "'";
 330         }
 331
 332         if (! empty($partition['data_directory'])) {
 333             $sqlQuery .= " DATA DIRECTORY = '" . $partition['data_directory'] . "'";
 334         }
 335
 336         if (! empty($partition['index_directory'])) {
 337             $sqlQuery .= " INDEX_DIRECTORY = '" . $partition['index_directory'] . "'";
 338         }
 339
 340         if (! empty($partition['max_rows'])) {
 341             $sqlQuery .= ' MAX_ROWS = ' . $partition['max_rows'];
 342         }
 343
 344         if (! empty($partition['min_rows'])) {
 345             $sqlQuery .= ' MIN_ROWS = ' . $partition['min_rows'];
 346         }
 347
 348         if (! empty($partition['tablespace'])) {
 349             $sqlQuery .= ' TABLESPACE = ' . $partition['tablespace'];
 350         }
 351
 352         if (! empty($partition['node_group'])) {
 353             $sqlQuery .= ' NODEGROUP = ' . $partition['node_group'];
 354         }
 355
 356         if (! empty($partition['subpartitions'])) {
 357             $subpartitions = [];
 358             foreach ($partition['subpartitions'] as $subpartition) {
 359                 $subpartitions[] = $this->getPartitionDefinition($subpartition, true);
 360             }
 361
 362             $sqlQuery .= ' (' . implode(', ', $subpartitions) . ')';
 363         }
 364
 365         return $sqlQuery;
 366     }
 367
 368     /**
 369      * Function to get table creation sql query
 370      *
 371      * @param string $db    database name
 372      * @param string $table table name
 373      */
 374     public function getTableCreationQuery(string $db, string $table): string
 375     {
 376         // get column addition statements
 377         $sqlStatement = $this->getColumnCreationStatements();
 378
 379         // Builds the 'create table' statement
 380         $sqlQuery = 'CREATE TABLE ' . Util::backquote($db) . '.'
 381             . Util::backquote(trim($table)) . ' (' . $sqlStatement . ')';
 382
 383         // Adds table type, character set, comments and partition definition
 384         if (
 385             ! empty($_POST['tbl_storage_engine'])
 386             && ($_POST['tbl_storage_engine'] !== 'Default')
 387             && StorageEngine::isValid($_POST['tbl_storage_engine'])
 388         ) {
 389             $sqlQuery .= ' ENGINE = ' . $_POST['tbl_storage_engine'];
 390         }
 391
 392         if (! empty($_POST['tbl_collation'])) {
 393             $sqlQuery .= Util::getCharsetQueryPart($_POST['tbl_collation']);
 394         }
 395
 396         if (
 397             ! empty($_POST['connection'])
 398             && ! empty($_POST['tbl_storage_engine'])
 399             && $_POST['tbl_storage_engine'] === 'FEDERATED'
 400         ) {
 401             $sqlQuery .= ' CONNECTION = ' . $this->dbi->quoteString($_POST['connection']);
 402         }
 403
 404         if (! empty($_POST['comment'])) {
 405             $sqlQuery .= ' COMMENT = ' . $this->dbi->quoteString($_POST['comment']);
 406         }
 407
 408         $sqlQuery .= $this->getPartitionsDefinition();
 409         $sqlQuery .= ';';
 410
 411         return $sqlQuery;
 412     }
 413
 414     /**
 415      * Function to get the column creation statement
 416      *
 417      * @param string $table current table
 418      */
 419     public function getColumnCreationQuery(
 420         string $table,
 421     ): string {
 422         // get column addition statements
 423         $sqlStatement = $this->getColumnCreationStatements(false);
 424
 425         $sqlQuery = 'ALTER TABLE ' . Util::backquote($table) . ' ' . $sqlStatement;
 426         if (isset($_POST['online_transaction'])) {
 427             $sqlQuery .= ', ALGORITHM=INPLACE, LOCK=NONE';
 428         }
 429
 430         return $sqlQuery . ';';
 431     }
 432
 433     /**
 434      * Function to execute the column creation statement
 435      *
 436      * @param DatabaseName $db       current database
 437      * @param string       $sqlQuery the query to run
 438      * @param string       $errorUrl error page url
 439      */
 440     public function tryColumnCreationQuery(
 441         DatabaseName $db,
 442         string $sqlQuery,
 443         string $errorUrl,
 444     ): bool {
 445         // To allow replication, we first select the db to use and then run queries
 446         // on this db.
 447         if (! $this->dbi->selectDb($db)) {
 448             Generator::mysqlDie(
 449                 $this->dbi->getError(),
 450                 'USE ' . Util::backquote($db->getName()),
 451                 false,
 452                 $errorUrl,
 453             );
 454         }
 455
 456         return (bool) $this->dbi->tryQuery($sqlQuery);
 457     }
 458 }