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