diff options
| -rw-r--r-- | js/functions.js | 22 | ||||
| -rw-r--r-- | libraries/Table.class.php | 150 | ||||
| -rw-r--r-- | libraries/Util.class.php | 9 | ||||
| -rw-r--r-- | libraries/create_addfield.lib.php | 6 | ||||
| -rw-r--r-- | libraries/structure.lib.php | 20 | ||||
| -rw-r--r-- | libraries/tbl_columns_definition_form.inc.php | 12 | ||||
| -rw-r--r-- | templates/columns_definitions/column_attributes.phtml | 12 | ||||
| -rw-r--r-- | templates/columns_definitions/column_virtuality.phtml | 48 | ||||
| -rw-r--r-- | templates/columns_definitions/table_fields_definitions.phtml | 6 | ||||
| -rw-r--r-- | test/classes/PMA_Table_test.php | 19 |
10 files changed, 240 insertions, 64 deletions
diff --git a/js/functions.js b/js/functions.js index 092490daea..531994079a 100644 --- a/js/functions.js +++ b/js/functions.js @@ -230,6 +230,21 @@ function PMA_hideShowDefaultValue($default_type) } /** + * Hides/shows the input field for column expression based on whether + * VIRTUAL/PERSISTENT is selected + * + * @param $virtuality virtuality dropdown + */ +function PMA_hideShowExpression($virtuality) +{ + if ($virtuality.val() == '') { + $virtuality.siblings('.expression').hide(); + } else { + $virtuality.siblings('.expression').show(); + } +} + +/** * Show notices for ENUM columns; add/hide the default value * */ @@ -241,6 +256,9 @@ function PMA_verifyColumnsProperties() $("select.default_type").each(function () { PMA_hideShowDefaultValue($(this)); }); + $('select.virtuality').each(function () { + PMA_hideShowExpression($(this)); + }); } /** @@ -3035,6 +3053,7 @@ AJAX.registerOnload('functions.js', function () { AJAX.registerTeardown('functions.js', function () { $(document).off('change', "select.column_type"); $(document).off('change', "select.default_type"); + $(document).off('change', "select.virtuality"); $(document).off('change', 'input.allow_null'); $(document).off('change', '.create_table_form select[name=tbl_storage_engine]'); }); @@ -3054,6 +3073,9 @@ AJAX.registerOnload('functions.js', function () { $(document).on('change', "select.default_type", function () { PMA_hideShowDefaultValue($(this)); }); + $(document).on('change', "select.virtuality", function () { + PMA_hideShowExpression($(this)); + }); $(document).on('change', 'input.allow_null', function () { PMA_validateDefaultValue($(this)); }); diff --git a/libraries/Table.class.php b/libraries/Table.class.php index 2ac3c56e35..9d3cbecaa1 100644 --- a/libraries/Table.class.php +++ b/libraries/Table.class.php @@ -380,6 +380,8 @@ class PMA_Table * default type * @param string $extra 'AUTO_INCREMENT' * @param string $comment field comment + * @param string $virtuality virtuality of the column + * @param string $expression expression for the virtual column * @param string $move_to new position for column * * @todo move into class PMA_Column @@ -391,7 +393,7 @@ class PMA_Table static function generateFieldSpec($name, $type, $length = '', $attribute = '', $collation = '', $null = false, $default_type = 'USER_DEFINED', $default_value = '', $extra = '', - $comment = '', $move_to = '' + $comment = '', $virtuality = '', $expression = '', $move_to = '' ) { $is_timestamp = /*overload*/mb_strpos( /*overload*/mb_strtoupper($type), @@ -412,70 +414,74 @@ class PMA_Table $query .= '(' . $length . ')'; } - if ($attribute != '') { - $query .= ' ' . $attribute; - } + if ($virtuality) { + $query .= ' AS (' . $expression . ') ' . $virtuality; + } else { + if ($attribute != '') { + $query .= ' ' . $attribute; + } - $matches = preg_match( - '@^(TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT|VARCHAR|CHAR|ENUM|SET)$@i', - $type - ); - if (! empty($collation) && $collation != 'NULL' && $matches) { - $query .= PMA_generateCharsetQueryPart($collation); - } + $matches = preg_match( + '@^(TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT|VARCHAR|CHAR|ENUM|SET)$@i', + $type + ); + if (! empty($collation) && $collation != 'NULL' && $matches) { + $query .= PMA_generateCharsetQueryPart($collation); + } - if ($null !== false) { - if ($null == 'NULL') { - $query .= ' NULL'; - } else { - $query .= ' NOT NULL'; + if ($null !== false) { + if ($null == 'NULL') { + $query .= ' NULL'; + } else { + $query .= ' NOT NULL'; + } } - } - switch ($default_type) { - case 'USER_DEFINED' : - if ($is_timestamp && $default_value === '0') { - // a TIMESTAMP does not accept DEFAULT '0' - // but DEFAULT 0 works - $query .= ' DEFAULT 0'; - } elseif ($type == 'BIT') { - $query .= ' DEFAULT b\'' - . preg_replace('/[^01]/', '0', $default_value) - . '\''; - } elseif ($type == 'BOOLEAN') { - if (preg_match('/^1|T|TRUE|YES$/i', $default_value)) { - $query .= ' DEFAULT TRUE'; - } elseif (preg_match('/^0|F|FALSE|NO$/i', $default_value)) { - $query .= ' DEFAULT FALSE'; + switch ($default_type) { + case 'USER_DEFINED' : + if ($is_timestamp && $default_value === '0') { + // a TIMESTAMP does not accept DEFAULT '0' + // but DEFAULT 0 works + $query .= ' DEFAULT 0'; + } elseif ($type == 'BIT') { + $query .= ' DEFAULT b\'' + . preg_replace('/[^01]/', '0', $default_value) + . '\''; + } elseif ($type == 'BOOLEAN') { + if (preg_match('/^1|T|TRUE|YES$/i', $default_value)) { + $query .= ' DEFAULT TRUE'; + } elseif (preg_match('/^0|F|FALSE|NO$/i', $default_value)) { + $query .= ' DEFAULT FALSE'; + } else { + // Invalid BOOLEAN value + $query .= ' DEFAULT \'' + . PMA_Util::sqlAddSlashes($default_value) . '\''; + } + } elseif ($type == 'BINARY' || $type == 'VARBINARY') { + $query .= ' DEFAULT 0x' . $default_value; } else { - // Invalid BOOLEAN value $query .= ' DEFAULT \'' . PMA_Util::sqlAddSlashes($default_value) . '\''; } - } elseif ($type == 'BINARY' || $type == 'VARBINARY') { - $query .= ' DEFAULT 0x' . $default_value; - } else { - $query .= ' DEFAULT \'' - . PMA_Util::sqlAddSlashes($default_value) . '\''; - } - break; - case 'NULL' : - // If user uncheck null checkbox and not change default value null, - // default value will be ignored. - if ($null !== false && $null !== 'NULL') { + break; + case 'NULL' : + // If user uncheck null checkbox and not change default value null, + // default value will be ignored. + if ($null !== false && $null !== 'NULL') { + break; + } + // else fall-through intended, no break here + case 'CURRENT_TIMESTAMP' : + $query .= ' DEFAULT ' . $default_type; + break; + case 'NONE' : + default : break; } - // else fall-through intended, no break here - case 'CURRENT_TIMESTAMP' : - $query .= ' DEFAULT ' . $default_type; - break; - case 'NONE' : - default : - break; - } - if (!empty($extra)) { - $query .= ' ' . $extra; + if (!empty($extra)) { + $query .= ' ' . $extra; + } } if (!empty($comment)) { $query .= " COMMENT '" . PMA_Util::sqlAddSlashes($comment) . "'"; @@ -592,6 +598,8 @@ class PMA_Table * type * @param string $extra 'AUTO_INCREMENT' * @param string $comment field comment + * @param string $virtuality virtuality of the column + * @param string $expression expression for the virtual column * @param string $move_to new position for column * * @see PMA_Table::generateFieldSpec() @@ -600,13 +608,13 @@ class PMA_Table */ static public function generateAlter($oldcol, $newcol, $type, $length, $attribute, $collation, $null, $default_type, $default_value, - $extra, $comment, $move_to + $extra, $comment, $virtuality, $expression, $move_to ) { return PMA_Util::backquote($oldcol) . ' ' . PMA_Table::generateFieldSpec( $newcol, $type, $length, $attribute, $collation, $null, $default_type, $default_value, $extra, - $comment, $move_to + $comment, $virtuality, $expression, $move_to ); } // end function @@ -2272,5 +2280,35 @@ class PMA_Table return $sql_query; } + + /** + * Returns the generation expression for virtual columns + * + * @param string $column name of the column + * + * @return array associative array of column name and their expressions + */ + public function getColumnGenerationExpression($column = null) + { + $serverType = PMA_Util::getServerType(); + if ($serverType == 'MySQL' + && PMA_MYSQL_INT_VERSION > 50705 + && ! $GLOBALS['cfg']['Server']['DisableIS'] + ) { + $sql = "SELECT + `COLUMN_NAME` AS `Field`, + `GENERATION_EXPRESSION` AS `Expression` + FROM + `information_schema`.`COLUMNS` + WHERE + `TABLE_SCHEMA` = '" . PMA_Util::sqlAddSlashes($this->_db_name) . "' + AND `TABLE_NAME` = '" . PMA_Util::sqlAddSlashes($this->_name) . "'"; + if ($column != null) { + $sql .= " AND `COLUMN_NAME` = '" . PMA_Util::sqlAddSlashes($column) . "'"; + } + $columns = $this->_dbi->fetchResult($sql, 'Field', 'Expression'); + return $columns; + } + } } ?> diff --git a/libraries/Util.class.php b/libraries/Util.class.php index 8e80703d90..75ff0f4487 100644 --- a/libraries/Util.class.php +++ b/libraries/Util.class.php @@ -4832,6 +4832,15 @@ class PMA_Util ) ); } + + /** + * Returns whether the database server supports virtual columns + */ + public static function isVirtualColumnsSupported() + { + $serverType = self::getServerType(); + return $serverType == 'MySQL' && PMA_MYSQL_INT_VERSION > 50705; + } } ?> diff --git a/libraries/create_addfield.lib.php b/libraries/create_addfield.lib.php index 2aafdd681a..c6f9e12a6b 100644 --- a/libraries/create_addfield.lib.php +++ b/libraries/create_addfield.lib.php @@ -72,6 +72,12 @@ function PMA_buildColumnCreationStatement( : false, isset($_REQUEST['field_comments'][$i]) ? $_REQUEST['field_comments'][$i] + : '', + isset($_REQUEST['field_virtuality'][$i]) + ? $_REQUEST['field_virtuality'][$i] + : '', + isset($_REQUEST['field_expression'][$i]) + ? $_REQUEST['field_expression'][$i] : '' ); diff --git a/libraries/structure.lib.php b/libraries/structure.lib.php index 982b0b79a9..7be0447863 100644 --- a/libraries/structure.lib.php +++ b/libraries/structure.lib.php @@ -2579,6 +2579,12 @@ function PMA_updateColumns($db, $table) isset($_REQUEST['field_comments'][$i]) ? $_REQUEST['field_comments'][$i] : '', + isset($_REQUEST['field_virtuality'][$i]) + ? $_REQUEST['field_virtuality'][$i] + : '', + isset($_REQUEST['field_expression'][$i]) + ? $_REQUEST['field_expression'][$i] + : '', isset($_REQUEST['field_move_to'][$i]) ? $_REQUEST['field_move_to'][$i] : '' @@ -2868,6 +2874,18 @@ function PMA_moveColumns($db, $table) ? 'NONE' : 'USER_DEFINED')); + $virtual = array( + 'VIRTUAL', 'PERSISTENT', 'VIRTUAL GENERATED', 'STORED GENERATED' + ); + $data['Virtuality'] = ''; + $data['Expression'] = ''; + if (isset($data['Extra']) && in_array($data['Extra'], $virtual)) { + $data['Virtuality'] = str_replace(' GENERATED', '', $data['Extra']); + $table = new PMA_Table($GLOBALS['table'], $GLOBALS['db']); + $expressions = $table->getColumnGenerationExpression($column); + $data['Expression'] = $expressions[$column]; + } + $changes[] = 'CHANGE ' . PMA_Table::generateAlter( $column, $column, @@ -2881,6 +2899,8 @@ function PMA_moveColumns($db, $table) isset($data['Extra']) && $data['Extra'] !== '' ? $data['Extra'] : false, isset($data['COLUMN_COMMENT']) && $data['COLUMN_COMMENT'] !== '' ? $data['COLUMN_COMMENT'] : false, + $data['Virtuality'], + $data['Expression'], $i === 0 ? '-first' : $column_names[$i - 1] ); // update current column_names array, first delete old position diff --git a/libraries/tbl_columns_definition_form.inc.php b/libraries/tbl_columns_definition_form.inc.php index cc367e0358..5029cb25ee 100644 --- a/libraries/tbl_columns_definition_form.inc.php +++ b/libraries/tbl_columns_definition_form.inc.php @@ -146,6 +146,8 @@ for ($columnNumber = 0; $columnNumber < $num_fields; $columnNumber++) { 'DefaultType' => Util\get($_REQUEST, "field_default_type.${columnNumber}", 'NONE'), 'DefaultValue' => Util\get($_REQUEST, "field_default_value.${columnNumber}", ''), 'Extra' => Util\get($_REQUEST, "field_extra.${columnNumber}", false), + 'Virtuality' => Util\get($_REQUEST, "field_virtuality.${columnNumber}", ''), + 'Expression' => Util\get($_REQUEST, "field_expression.${columnNumber}", ''), )); $columnMeta['Key'] = ''; @@ -193,6 +195,14 @@ for ($columnNumber = 0; $columnNumber < $num_fields; $columnNumber++) { } elseif (isset($fields_meta[$columnNumber])) { $columnMeta = $fields_meta[$columnNumber]; + $virtual = array( + 'VIRTUAL', 'PERSISTENT', 'VIRTUAL GENERATED', 'STORED GENERATED' + ); + if (in_array($columnMeta['Extra'], $virtual)) { + $table = new PMA_Table($GLOBALS['table'], $GLOBALS['db']); + $expressions = $table->getColumnGenerationExpression($columnMeta['Field']); + $columnMeta['Expression'] = $expressions[$columnMeta['Field']]; + } switch ($columnMeta['Default']) { case null: if (is_null($columnMeta['Default'])) { // null @@ -305,6 +315,8 @@ for ($columnNumber = 0; $columnNumber < $num_fields; $columnNumber++) { "field_null_orig[${columnNumber}]" => Util\get($columnMeta, 'Null', ''), "field_extra_orig[${columnNumber}]" => Util\get($columnMeta, 'Extra', ''), "field_comments_orig[${columnNumber}]" => Util\get($columnMeta, 'Comment', ''), + "field_virtuality_orig[${columnNumber}]" => Util\get($columnMeta, 'Virtuality', ''), + "field_expression_orig[${columnNumber}]" => Util\get($columnMeta, 'Expression', ''), )); } diff --git a/templates/columns_definitions/column_attributes.phtml b/templates/columns_definitions/column_attributes.phtml index eb4049c463..9fbc4ab5f7 100644 --- a/templates/columns_definitions/column_attributes.phtml +++ b/templates/columns_definitions/column_attributes.phtml @@ -131,6 +131,18 @@ $ci_offset = -1; 'comments_map' => $comments_map )); ?> </td> + <!-- column virtuality --> +<?php if (PMA_Util::isVirtualColumnsSupported()): ?> + <td class="center"> + <?php echo PMA\Template::get('columns_definitions/column_virtuality') + ->render(array( + 'columnNumber' => $columnNumber, + 'ci' => $ci++, + 'ci_offset' => $ci_offset, + 'columnMeta' => isset($columnMeta) ? $columnMeta : null + )); ?> + </td> +<?php endif; ?> <!-- move column --> <?php if (isset($fields_meta)): ?> <?php $current_index = 0; diff --git a/templates/columns_definitions/column_virtuality.phtml b/templates/columns_definitions/column_virtuality.phtml new file mode 100644 index 0000000000..ac4f2dfdf0 --- /dev/null +++ b/templates/columns_definitions/column_virtuality.phtml @@ -0,0 +1,48 @@ +<?php +$options = array( + '' => '', + 'VIRTUAL' => 'VIRTUAL', +); +$serverType = PMA_Util::getServerType(); +if ($serverType == 'MariaDB') { + $options['PERSISTENT'] = 'PERSISTENT'; +} else { + $options['STORED'] = 'STORED'; +} + +$expression = ''; +if (isset($columnMeta['Expression'])) { + $expression = $columnMeta['Expression']; +} +?> + +<select name="field_virtuality[<?php echo $columnNumber; ?>]" + id="field_<?php echo $columnNumber; ?>_<?php echo($ci - $ci_offset); ?>" + class="virtuality"> + <?php foreach ($options as $key => $value): ?> + <option value="<?php echo $key; ?>" + <?php if (isset($columnMeta['Extra']) + && $key != '' + && strpos($columnMeta['Extra'], $key) === 0): ?> + selected="selected" + <?php endif; ?>> + <?php echo $value; ?> + </option> + <?php endforeach; ?> +</select> +<br /> +<?php if ($GLOBALS['cfg']['CharEditing'] == 'textarea'): ?> + <textarea name="field_expression[<?php echo $columnNumber; ?>]" + cols="15" + class="textfield + expression"> + <?php echo htmlspecialchars($expression); ?> + </textarea> +<?php else: ?> + <input type="text" + name="field_expression[<?php echo $columnNumber; ?>]" + size="12" + value="<?php echo htmlspecialchars($expression); ?>" + placeholder="<?php echo __('Expression'); ?>" + class="textfield expression" /> +<?php endif; ?>
\ No newline at end of file diff --git a/templates/columns_definitions/table_fields_definitions.phtml b/templates/columns_definitions/table_fields_definitions.phtml index dfcdf9851d..037232a62c 100644 --- a/templates/columns_definitions/table_fields_definitions.phtml +++ b/templates/columns_definitions/table_fields_definitions.phtml @@ -65,6 +65,12 @@ <?php echo __('Comments'); ?> </th> + <?php if (PMA_Util::isVirtualColumnsSupported()): ?> + <th> + <?php echo __('Virtuality'); ?> + </th> + <?php endif; ?> + <?php if (isset($fields_meta)): ?> <th> <?php echo __('Move column'); ?> diff --git a/test/classes/PMA_Table_test.php b/test/classes/PMA_Table_test.php index f6eaed3758..dd0d60a207 100644 --- a/test/classes/PMA_Table_test.php +++ b/test/classes/PMA_Table_test.php @@ -475,14 +475,15 @@ class PMA_Table_Test extends PHPUnit_Framework_TestCase $default_type = 'USER_DEFINED'; $default_value = 12; $extra = 'AUTO_INCREMENT'; - $comment = 'PMA_comment'; + $virtuality = ''; + $expression = ''; $move_to = '-first'; $query = PMA_Table::generateFieldSpec( $name, $type, $length, $attribute, $collation, $null, $default_type, $default_value, $extra, $comment, - $move_to + $virtuality, $expression, $move_to ); $this->assertEquals( "`PMA_name` BIT(12) PMA_attribute NULL DEFAULT b'10' " @@ -495,7 +496,7 @@ class PMA_Table_Test extends PHPUnit_Framework_TestCase $query = PMA_Table::generateFieldSpec( $name, $type, $length, $attribute, $collation, $null, $default_type, $default_value, $extra, $comment, - $move_to + $virtuality, $expression, $move_to ); $this->assertEquals( "`PMA_name` DOUBLE(12) PMA_attribute NULL DEFAULT '12' " @@ -508,7 +509,7 @@ class PMA_Table_Test extends PHPUnit_Framework_TestCase $query = PMA_Table::generateFieldSpec( $name, $type, $length, $attribute, $collation, $null, $default_type, $default_value, $extra, $comment, - $move_to + $virtuality, $expression, $move_to ); $this->assertEquals( "`PMA_name` BOOLEAN PMA_attribute NULL DEFAULT TRUE " @@ -521,7 +522,7 @@ class PMA_Table_Test extends PHPUnit_Framework_TestCase $query = PMA_Table::generateFieldSpec( $name, $type, $length, $attribute, $collation, $null, $default_type, $default_value, $extra, $comment, - $move_to + $virtuality, $expression, $move_to ); $this->assertEquals( "`PMA_name` BOOLEAN PMA_attribute NULL DEFAULT NULL " @@ -534,7 +535,7 @@ class PMA_Table_Test extends PHPUnit_Framework_TestCase $query = PMA_Table::generateFieldSpec( $name, $type, $length, $attribute, $collation, $null, $default_type, $default_value, $extra, $comment, - $move_to + $virtuality, $expression, $move_to ); $this->assertEquals( "`PMA_name` BOOLEAN PMA_attribute NULL DEFAULT CURRENT_TIMESTAMP " @@ -549,7 +550,7 @@ class PMA_Table_Test extends PHPUnit_Framework_TestCase $query = PMA_Table::generateFieldSpec( $name, $type, $length, $attribute, $collation, $null, $default_type, $default_value, $extra, $comment, - $move_to + $virtuality, $expression, $move_to ); $this->assertEquals( "`PMA_name` BOOLEAN PMA_attribute NULL INCREMENT " @@ -678,12 +679,14 @@ class PMA_Table_Test extends PHPUnit_Framework_TestCase $default_value = 'VARCHAR'; $extra = 'AUTO_INCREMENT'; $comment = 'PMA comment'; + $virtuality = ''; + $expression = ''; $move_to = 'new_name'; $result = PMA_Table::generateAlter( $oldcol, $newcol, $type, $length, $attribute, $collation, $null, $default_type, $default_value, - $extra, $comment, $move_to + $extra, $comment, $virtuality, $expression, $move_to ); $expect = ""; |