Generates column specification for ALTER syntax
public static generateAlter ( string $oldcol, string $newcol, string $type, string $length, string $attribute, string $collation, boolean | string $null, string $default_type, string $default_value, string $extra, string $comment, string $virtuality, string $expression, string $move_to ) : string | ||
$oldcol | string | old column name |
$newcol | string | new column name |
$type | string | type ('INT', 'VARCHAR', 'BIT', ...) |
$length | string | length ('2', '5,2', '', ...) |
$attribute | string | attribute |
$collation | string | collation |
$null | boolean | string | with 'NULL' or 'NOT NULL' |
$default_type | string | whether default is CURRENT_TIMESTAMP, NULL, NONE, USER_DEFINED |
$default_value | string | default value for USER_DEFINED default type |
$extra | string | 'AUTO_INCREMENT' |
$comment | string | field comment |
$virtuality | string | virtuality of the column |
$expression | string | expression for the virtual column |
$move_to | string | new position for column |
Résultat | string | field specification |
/** * Test for generateAlter * * @return void */ public function testGenerateAlter() { //parameter $oldcol = 'name'; $newcol = 'new_name'; $type = 'VARCHAR'; $length = '2'; $attribute = 'new_name'; $collation = 'charset1'; $null = 'NULL'; $default_type = 'USER_DEFINED'; $default_value = 'VARCHAR'; $extra = 'AUTO_INCREMENT'; $comment = 'PMA comment'; $virtuality = ''; $expression = ''; $move_to = 'new_name'; $result = Table::generateAlter($oldcol, $newcol, $type, $length, $attribute, $collation, $null, $default_type, $default_value, $extra, $comment, $virtuality, $expression, $move_to); $expect = "`name` `new_name` VARCHAR(2) new_name CHARACTER SET " . "charset1 NULL DEFAULT 'VARCHAR' " . "AUTO_INCREMENT COMMENT 'PMA comment' AFTER `new_name`"; $this->assertEquals($expect, $result); }
/** * Update the table's structure based on $_REQUEST * * @return boolean $regenerate true if error occurred * */ protected function updateColumns() { $err_url = 'tbl_structure.php' . PMA_URL_getCommon(array('db' => $this->db, 'table' => $this->table)); $regenerate = false; $field_cnt = count($_REQUEST['field_name']); $changes = array(); $adjust_privileges = array(); for ($i = 0; $i < $field_cnt; $i++) { if (!$this->columnNeedsAlterTable($i)) { continue; } $changes[] = 'CHANGE ' . Table::generateAlter(Util_lib\get($_REQUEST, "field_orig.{$i}", ''), $_REQUEST['field_name'][$i], $_REQUEST['field_type'][$i], $_REQUEST['field_length'][$i], $_REQUEST['field_attribute'][$i], Util_lib\get($_REQUEST, "field_collation.{$i}", ''), Util_lib\get($_REQUEST, "field_null.{$i}", 'NOT NULL'), $_REQUEST['field_default_type'][$i], $_REQUEST['field_default_value'][$i], Util_lib\get($_REQUEST, "field_extra.{$i}", false), Util_lib\get($_REQUEST, "field_comments.{$i}", ''), Util_lib\get($_REQUEST, "field_virtuality.{$i}", ''), Util_lib\get($_REQUEST, "field_expression.{$i}", ''), Util_lib\get($_REQUEST, "field_move_to.{$i}", '')); // find the remembered sort expression $sorted_col = $this->table_obj->getUiProp(Table::PROP_SORTED_COLUMN); // if the old column name is part of the remembered sort expression if (mb_strpos($sorted_col, Util::backquote($_REQUEST['field_orig'][$i])) !== false) { // delete the whole remembered sort expression $this->table_obj->removeUiProp(Table::PROP_SORTED_COLUMN); } if (isset($_REQUEST['field_adjust_privileges'][$i]) && !empty($_REQUEST['field_adjust_privileges'][$i]) && $_REQUEST['field_orig'][$i] != $_REQUEST['field_name'][$i]) { $adjust_privileges[$_REQUEST['field_orig'][$i]] = $_REQUEST['field_name'][$i]; } } // end for if (count($changes) > 0 || isset($_REQUEST['preview_sql'])) { // Builds the primary keys statements and updates the table $key_query = ''; /** * this is a little bit more complex * * @todo if someone selects A_I when altering a column we need to check: * - no other column with A_I * - the column has an index, if not create one * */ // To allow replication, we first select the db to use // and then run queries on this db. if (!$this->dbi->selectDb($this->db)) { Util::mysqlDie($this->dbi->getError(), 'USE ' . Util::backquote($this->db) . ';', false, $err_url); } $sql_query = 'ALTER TABLE ' . Util::backquote($this->table) . ' '; $sql_query .= implode(', ', $changes) . $key_query; $sql_query .= ';'; // If there is a request for SQL previewing. if (isset($_REQUEST['preview_sql'])) { PMA_previewSQL(count($changes) > 0 ? $sql_query : ''); } $columns_with_index = $this->dbi->getTable($this->db, $this->table)->getColumnsWithIndex(PMA_Index::PRIMARY | PMA_Index::UNIQUE | PMA_Index::INDEX | PMA_Index::SPATIAL | PMA_Index::FULLTEXT); $changedToBlob = array(); // While changing the Column Collation // First change to BLOB for ($i = 0; $i < $field_cnt; $i++) { if (isset($_REQUEST['field_collation'][$i]) && isset($_REQUEST['field_collation_orig'][$i]) && $_REQUEST['field_collation'][$i] !== $_REQUEST['field_collation_orig'][$i] && !in_array($_REQUEST['field_orig'][$i], $columns_with_index)) { $secondary_query = 'ALTER TABLE ' . Util::backquote($this->table) . ' CHANGE ' . Util::backquote($_REQUEST['field_orig'][$i]) . ' ' . Util::backquote($_REQUEST['field_orig'][$i]) . ' BLOB;'; $this->dbi->query($secondary_query); $changedToBlob[$i] = true; } else { $changedToBlob[$i] = false; } } // Then make the requested changes $result = $this->dbi->tryQuery($sql_query); if ($result !== false) { $changed_privileges = $this->adjustColumnPrivileges($adjust_privileges); if ($changed_privileges) { $message = Message::success(__('Table %1$s has been altered successfully. Privileges ' . 'have been adjusted.')); } else { $message = Message::success(__('Table %1$s has been altered successfully.')); } $message->addParam($this->table); $this->response->addHTML(Util::getMessage($message, $sql_query, 'success')); } else { // An error happened while inserting/updating a table definition // Save the Original Error $orig_error = $this->dbi->getError(); $changes_revert = array(); // Change back to Original Collation and data type for ($i = 0; $i < $field_cnt; $i++) { if ($changedToBlob[$i]) { $changes_revert[] = 'CHANGE ' . Table::generateAlter(Util_lib\get($_REQUEST, "field_orig.{$i}", ''), $_REQUEST['field_name'][$i], $_REQUEST['field_type_orig'][$i], $_REQUEST['field_length_orig'][$i], $_REQUEST['field_attribute_orig'][$i], Util_lib\get($_REQUEST, "field_collation_orig.{$i}", ''), Util_lib\get($_REQUEST, "field_null_orig.{$i}", 'NOT NULL'), $_REQUEST['field_default_type_orig'][$i], $_REQUEST['field_default_value_orig'][$i], Util_lib\get($_REQUEST, "field_extra_orig.{$i}", false), Util_lib\get($_REQUEST, "field_comments_orig.{$i}", ''), Util_lib\get($_REQUEST, "field_virtuality_orig.{$i}", ''), Util_lib\get($_REQUEST, "field_expression_orig.{$i}", ''), Util_lib\get($_REQUEST, "field_move_to_orig.{$i}", '')); } } $revert_query = 'ALTER TABLE ' . Util::backquote($this->table) . ' '; $revert_query .= implode(', ', $changes_revert) . ''; $revert_query .= ';'; // Column reverted back to original $this->dbi->query($revert_query); $this->response->setRequestStatus(false); $this->response->addJSON('message', Message::rawError(__('Query error') . ':<br />' . $orig_error)); $regenerate = true; } } // update field names in relation if (isset($_REQUEST['field_orig']) && is_array($_REQUEST['field_orig'])) { foreach ($_REQUEST['field_orig'] as $fieldindex => $fieldcontent) { if ($_REQUEST['field_name'][$fieldindex] != $fieldcontent) { PMA_REL_renameField($this->db, $this->table, $fieldcontent, $_REQUEST['field_name'][$fieldindex]); } } } // update mime types if (isset($_REQUEST['field_mimetype']) && is_array($_REQUEST['field_mimetype']) && $GLOBALS['cfg']['BrowseMIME']) { foreach ($_REQUEST['field_mimetype'] as $fieldindex => $mimetype) { if (isset($_REQUEST['field_name'][$fieldindex]) && mb_strlen($_REQUEST['field_name'][$fieldindex])) { PMA_setMIME($this->db, $this->table, $_REQUEST['field_name'][$fieldindex], $mimetype, $_REQUEST['field_transformation'][$fieldindex], $_REQUEST['field_transformation_options'][$fieldindex], $_REQUEST['field_input_transformation'][$fieldindex], $_REQUEST['field_input_transformation_options'][$fieldindex]); } } } return $regenerate; }