generateAlter() public static method

Generates column specification for ALTER syntax
See also: Table::generateFieldSpec()
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
return string field specification
Example #1
0
 /**
  * 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;
 }