libraries/create_addfield.lib.php

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