2 /* vim: set expandtab sw=4 ts=4 sts=4: */
4 * Holds the PhpMyAdmin\CreateAddField class
8 declare(strict_types
=1);
13 * Set of functions for tbl_create.php and tbl_addfield.php
20 * @var DatabaseInterface
27 * @param DatabaseInterface $dbi DatabaseInterface interface
29 public function __construct(DatabaseInterface
$dbi)
35 * Transforms the radio button field_key into 4 arrays
37 * @return array An array of arrays which represents column keys for each index type
39 private function getIndexedColumns(): array
41 $fieldCount = count($_POST['field_name']);
42 $fieldPrimary = json_decode($_POST['primary_indexes'], true);
43 $fieldIndex = json_decode($_POST['indexes'], true);
44 $fieldUnique = json_decode($_POST['unique_indexes'], true);
45 $fieldFullText = json_decode($_POST['fulltext_indexes'], true);
46 $fieldSpatial = json_decode($_POST['spatial_indexes'], true);
59 * Initiate the column creation statement according to the table creation or
60 * add columns to a existing table
62 * @param int $fieldCount number of columns
63 * @param boolean $isCreateTable true if requirement is to get the statement
66 * @return array An array of initial sql statements
67 * according to the request
69 private function buildColumnCreationStatement(
71 bool $isCreateTable = true
75 for ($i = 0; $i < $fieldCount; ++
$i) {
76 // '0' is also empty for php :-(
77 if (strlen($_POST['field_name'][$i]) === 0) {
81 $definition = $this->getStatementPrefix($isCreateTable) .
82 Table
::generateFieldSpec(
83 trim($_POST['field_name'][$i]),
84 $_POST['field_type'][$i],
85 $_POST['field_length'][$i],
86 $_POST['field_attribute'][$i],
87 isset($_POST['field_collation'][$i])
88 ?
$_POST['field_collation'][$i]
90 isset($_POST['field_null'][$i])
91 ?
$_POST['field_null'][$i]
93 $_POST['field_default_type'][$i],
94 $_POST['field_default_value'][$i],
95 isset($_POST['field_extra'][$i])
96 ?
$_POST['field_extra'][$i]
98 isset($_POST['field_comments'][$i])
99 ?
$_POST['field_comments'][$i]
101 isset($_POST['field_virtuality'][$i])
102 ?
$_POST['field_virtuality'][$i]
104 isset($_POST['field_expression'][$i])
105 ?
$_POST['field_expression'][$i]
109 $definition .= $this->setColumnCreationStatementSuffix(
114 $definitions[] = $definition;
121 * Set column creation suffix according to requested position of the new column
123 * @param int $previousField previous field for ALTER statement
124 * @param bool $isCreateTable true if requirement is to get the statement
127 * @return string suffix
129 private function setColumnCreationStatementSuffix(
131 bool $isCreateTable = true
133 // no suffix is needed if request is a table creation
135 if ($isCreateTable) {
139 if ((string) $_POST['field_where'] === 'last') {
143 // Only the first field can be added somewhere other than at the end
144 if ($previousField == -1) {
145 if ((string) $_POST['field_where'] === 'first') {
146 $sqlSuffix .= ' FIRST';
147 } elseif (! empty($_POST['after_field'])) {
148 $sqlSuffix .= ' AFTER '
149 . Util
::backquote($_POST['after_field']);
152 $sqlSuffix .= ' AFTER '
154 $_POST['field_name'][$previousField]
162 * Create relevant index statements
164 * @param array $index an array of index columns
165 * @param string $indexChoice index choice that which represents
166 * the index type of $indexed_fields
167 * @param boolean $isCreateTable true if requirement is to get the statement
170 * @return array an array of sql statements for indexes
172 private function buildIndexStatements(
175 bool $isCreateTable = true
178 if (! count($index)) {
182 $sqlQuery = $this->getStatementPrefix($isCreateTable)
183 . ' ' . $indexChoice;
185 if (! empty($index['Key_name']) && $index['Key_name'] != 'PRIMARY') {
186 $sqlQuery .= ' ' . Util
::backquote($index['Key_name']);
190 foreach ($index['columns'] as $key => $column) {
191 $indexFields[$key] = Util
::backquote(
192 $_POST['field_name'][$column['col_index']]
194 if ($column['size']) {
195 $indexFields[$key] .= '(' . $column['size'] . ')';
199 $sqlQuery .= ' (' . implode(', ', $indexFields) . ')';
201 $keyBlockSizes = $index['Key_block_size'];
202 if (! empty($keyBlockSizes)) {
203 $sqlQuery .= " KEY_BLOCK_SIZE = "
204 . $this->dbi
->escapeString($keyBlockSizes);
207 // specifying index type is allowed only for primary, unique and index only
208 $type = $index['Index_type'];
209 if ($index['Index_choice'] != 'SPATIAL'
210 && $index['Index_choice'] != 'FULLTEXT'
211 && in_array($type, Index
::getIndexTypes())
213 $sqlQuery .= ' USING ' . $type;
216 $parser = $index['Parser'];
217 if ($index['Index_choice'] == 'FULLTEXT' && ! empty($parser)) {
218 $sqlQuery .= " WITH PARSER " . $this->dbi
->escapeString($parser);
221 $comment = $index['Index_comment'];
222 if (! empty($comment)) {
223 $sqlQuery .= " COMMENT '" . $this->dbi
->escapeString($comment)
227 $statement[] = $sqlQuery;
233 * Statement prefix for the buildColumnCreationStatement()
235 * @param boolean $isCreateTable true if requirement is to get the statement
238 * @return string prefix
240 private function getStatementPrefix(bool $isCreateTable = true): string
243 if (! $isCreateTable) {
244 $sqlPrefix = ' ADD ';
250 * Merge index definitions for one type of index
252 * @param array $definitions the index definitions to merge to
253 * @param boolean $isCreateTable true if requirement is to get the statement
255 * @param array $indexedColumns the columns for one type of index
256 * @param string $indexKeyword the index keyword to use in the definition
260 private function mergeIndexStatements(
263 array $indexedColumns,
266 foreach ($indexedColumns as $index) {
267 $statements = $this->buildIndexStatements(
269 " " . $indexKeyword . " ",
272 $definitions = array_merge($definitions, $statements);
278 * Returns sql statement according to the column and index specifications as
281 * @param boolean $isCreateTable true if requirement is to get the statement
284 * @return string sql statement
286 private function getColumnCreationStatements(bool $isCreateTable = true): string
296 ) = $this->getIndexedColumns();
297 $definitions = $this->buildColumnCreationStatement(
302 // Builds the PRIMARY KEY statements
303 $primaryKeyStatements = $this->buildIndexStatements(
304 isset($fieldPrimary[0]) ?
$fieldPrimary[0] : [],
308 $definitions = array_merge($definitions, $primaryKeyStatements);
310 // Builds the INDEX statements
311 $definitions = $this->mergeIndexStatements(
318 // Builds the UNIQUE statements
319 $definitions = $this->mergeIndexStatements(
326 // Builds the FULLTEXT statements
327 $definitions = $this->mergeIndexStatements(
334 // Builds the SPATIAL statements
335 $definitions = $this->mergeIndexStatements(
342 if (count($definitions)) {
343 $sqlStatement = implode(', ', $definitions);
345 return preg_replace('@, $@', '', $sqlStatement);
349 * Returns the partitioning clause
351 * @return string partitioning clause
353 public function getPartitionsDefinition(): string
356 if (! empty($_POST['partition_by'])
357 && ! empty($_POST['partition_expr'])
358 && ! empty($_POST['partition_count'])
359 && $_POST['partition_count'] > 1
361 $sqlQuery .= " PARTITION BY " . $_POST['partition_by']
362 . " (" . $_POST['partition_expr'] . ")"
363 . " PARTITIONS " . $_POST['partition_count'];
366 if (! empty($_POST['subpartition_by'])
367 && ! empty($_POST['subpartition_expr'])
368 && ! empty($_POST['subpartition_count'])
369 && $_POST['subpartition_count'] > 1
371 $sqlQuery .= " SUBPARTITION BY " . $_POST['subpartition_by']
372 . " (" . $_POST['subpartition_expr'] . ")"
373 . " SUBPARTITIONS " . $_POST['subpartition_count'];
376 if (! empty($_POST['partitions'])) {
378 foreach ($_POST['partitions'] as $partition) {
379 $partitions[] = $this->getPartitionDefinition($partition);
381 $sqlQuery .= " (" . implode(", ", $partitions) . ")";
388 * Returns the definition of a partition/subpartition
390 * @param array $partition array of partition/subpartition detiails
391 * @param boolean $isSubPartition whether a subpartition
393 * @return string partition/subpartition definition
395 private function getPartitionDefinition(
397 bool $isSubPartition = false
399 $sqlQuery = " " . ($isSubPartition ?
"SUB" : "") . "PARTITION ";
400 $sqlQuery .= $partition['name'];
402 if (! empty($partition['value_type'])) {
403 $sqlQuery .= " VALUES " . $partition['value_type'];
405 if ($partition['value_type'] != 'LESS THAN MAXVALUE') {
406 $sqlQuery .= " (" . $partition['value'] . ")";
410 if (! empty($partition['engine'])) {
411 $sqlQuery .= " ENGINE = " . $partition['engine'];
413 if (! empty($partition['comment'])) {
414 $sqlQuery .= " COMMENT = '" . $partition['comment'] . "'";
416 if (! empty($partition['data_directory'])) {
417 $sqlQuery .= " DATA DIRECTORY = '" . $partition['data_directory'] . "'";
419 if (! empty($partition['index_directory'])) {
420 $sqlQuery .= " INDEX_DIRECTORY = '" . $partition['index_directory'] . "'";
422 if (! empty($partition['max_rows'])) {
423 $sqlQuery .= " MAX_ROWS = " . $partition['max_rows'];
425 if (! empty($partition['min_rows'])) {
426 $sqlQuery .= " MIN_ROWS = " . $partition['min_rows'];
428 if (! empty($partition['tablespace'])) {
429 $sqlQuery .= " TABLESPACE = " . $partition['tablespace'];
431 if (! empty($partition['node_group'])) {
432 $sqlQuery .= " NODEGROUP = " . $partition['node_group'];
435 if (! empty($partition['subpartitions'])) {
437 foreach ($partition['subpartitions'] as $subpartition) {
438 $subpartitions[] = $this->getPartitionDefinition(
443 $sqlQuery .= " (" . implode(", ", $subpartitions) . ")";
450 * Function to get table creation sql query
452 * @param string $db database name
453 * @param string $table table name
457 public function getTableCreationQuery(string $db, string $table): string
459 // get column addition statements
460 $sqlStatement = $this->getColumnCreationStatements(true);
462 // Builds the 'create table' statement
463 $sqlQuery = 'CREATE TABLE ' . Util
::backquote($db) . '.'
464 . Util
::backquote(trim($table)) . ' (' . $sqlStatement . ')';
466 // Adds table type, character set, comments and partition definition
467 if (! empty($_POST['tbl_storage_engine'])
468 && ($_POST['tbl_storage_engine'] != 'Default')
470 $sqlQuery .= ' ENGINE = ' . $this->dbi
->escapeString($_POST['tbl_storage_engine']);
472 if (! empty($_POST['tbl_collation'])) {
473 $sqlQuery .= Util
::getCharsetQueryPart($_POST['tbl_collation']);
475 if (! empty($_POST['connection'])
476 && ! empty($_POST['tbl_storage_engine'])
477 && $_POST['tbl_storage_engine'] == 'FEDERATED'
479 $sqlQuery .= " CONNECTION = '"
480 . $this->dbi
->escapeString($_POST['connection']) . "'";
482 if (! empty($_POST['comment'])) {
483 $sqlQuery .= ' COMMENT = \''
484 . $this->dbi
->escapeString($_POST['comment']) . '\'';
486 $sqlQuery .= $this->getPartitionsDefinition();
493 * Function to get the number of fields for the table creation form
497 public function getNumberOfFieldsFromRequest(): int
499 // Limit to 4096 fields (MySQL maximal value)
502 if (isset($_POST['submit_num_fields'])) { // adding new fields
503 $numberOfFields = intval($_POST['orig_num_fields']) +
intval($_POST['added_fields']);
504 } elseif (isset($_POST['orig_num_fields'])) { // retaining existing fields
505 $numberOfFields = intval($_POST['orig_num_fields']);
506 } elseif (isset($_POST['num_fields'])
507 && intval($_POST['num_fields']) > 0
508 ) { // new table with specified number of fields
509 $numberOfFields = intval($_POST['num_fields']);
510 } else { // new table with unspecified number of fields
514 return min($numberOfFields, $mysqlLimit);
518 * Function to execute the column creation statement
520 * @param string $db current database
521 * @param string $table current table
522 * @param string $errorUrl error page url
526 public function tryColumnCreationQuery(
531 // get column addition statements
532 $sqlStatement = $this->getColumnCreationStatements(false);
534 // To allow replication, we first select the db to use and then run queries
536 if (! $this->dbi
->selectDb($db)) {
538 $this->dbi
->getError(),
539 'USE ' . Util
::backquote($db),
544 $sqlQuery = 'ALTER TABLE ' .
545 Util
::backquote($table) . ' ' . $sqlStatement . ';';
546 // If there is a request for SQL previewing.
547 if (isset($_POST['preview_sql'])) {
548 Core
::previewSQL($sqlQuery);
551 $this->dbi
->tryQuery($sqlQuery),