How to change (modify) a column definition for a database table

dmitrii_fediuk · January 26, 2016, 9:16pm

If you want just to rename a column: How to rename a database column?

Use the changeColumn() or the modifyColumn() method of the \Magento\Framework\DB\Adapter\AdapterInterface:

magento/magento2/blob/720667e/lib/internal/Magento/Framework/DB/Adapter/AdapterInterface.php#L263-L283


/**
 * Change the column name and definition
 *
 * For change definition of column - use modifyColumn
 *
 * @param string $tableName
 * @param string $oldColumnName
 * @param string $newColumnName
 * @param array|string $definition
 * @param boolean $flushData        flush table statistic
 * @param string $schemaName
 * @return \Magento\Framework\DB\Adapter\AdapterInterface
 */
public function changeColumn(
    $tableName,
    $oldColumnName,
    $newColumnName,
    $definition,
    $flushData = false,
    $schemaName = null
);

github.com

magento/magento2/blob/720667e/lib/internal/Magento/Framework/DB/Adapter/AdapterInterface.php#L285-L295


/**
 * Modify the column definition
 *
 * @param string $tableName
 * @param string $columnName
 * @param array|string $definition
 * @param boolean $flushData
 * @param string $schemaName
 * @return \Magento\Framework\DB\Adapter\AdapterInterface
 */
public function modifyColumn($tableName, $columnName, $definition, $flushData = false, $schemaName = null);

The difference between the CHANGE COLUMN and MODIFY COLUMN MySQL expressions is explained here: http://stackoverflow.com/a/22858945

If you need to rename a column then use the changeColumn() method.
If you need not rename the column then use the modifyColumn() method.

Usage examples from the core:

github.com

magento/magento2/blob/720667e/app/code/Magento/Widget/Setup/InstallSchema.php#L93-L102


$installer->getConnection()->changeColumn(
    $installer->getTable('widget'),
    'code',
    'widget_code',
    [
        'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
        'length' => 255,
        'comment' => 'Widget code for template directive'
    ]
);

github.com

magento/magento2/blob/720667e/app/code/Magento/Widget/Setup/InstallSchema.php#L104-L109


$installer->getConnection()->changeColumn(
    $installer->getTable('widget'),
    'type',
    'widget_type',
    ['type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT, 'length' => 255, 'comment' => 'Widget Type']
);

github.com

magento/magento2/blob/720667e/app/code/Magento/Quote/Setup/UpgradeSchema.php#L33-L42


$setup->getConnection()->changeColumn(
    $setup->getTable('quote_address'),
    'street',
    'street',
    [
        'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
        'length' => 255,
        'comment' => 'Street'
    ]
);

See also: The \Magento\Quote\Setup\UpgradeSchema::upgrade() method can be simplified by using the modifyColumn() instead of changeColumn() method of the \Magento\Framework\DB\Adapter\AdapterInterface because the column need not be renamed.

By default the \Magento\Framework\DB\Adapter\AdapterInterface interface is implemented by the \Magento\Framework\DB\Adapter\Pdo\Mysql class:

github.com

magento/magento2/blob/720667e/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php#L952-L1004


/**
 * Change the column name and definition
 *
 * For change definition of column - use modifyColumn
 *
 * @param string $tableName
 * @param string $oldColumnName
 * @param string $newColumnName
 * @param array $definition
 * @param boolean $flushData        flush table statistic
 * @param string $schemaName
 * @return \Zend_Db_Statement_Pdo
 * @throws \Zend_Db_Exception
 */
public function changeColumn(
    $tableName,
    $oldColumnName,
    $newColumnName,
    $definition,
    $flushData = false,
    $schemaName = null
) {
    if (!$this->tableColumnExists($tableName, $oldColumnName, $schemaName)) {
        throw new \Zend_Db_Exception(
            sprintf(
                'Column "%s" does not exist in table "%s".',
                $oldColumnName,
                $tableName
            )
        );
    }


    if (is_array($definition)) {
        $definition = $this->_getColumnDefinition($definition);
    }


    $sql = sprintf(
        'ALTER TABLE %s CHANGE COLUMN %s %s %s',
        $this->quoteIdentifier($tableName),
        $this->quoteIdentifier($oldColumnName),
        $this->quoteIdentifier($newColumnName),
        $definition
    );


    $result = $this->rawQuery($sql);


    if ($flushData) {
        $this->showTableStatus($tableName, $schemaName);
    }
    $this->resetDdlCache($tableName, $schemaName);


    return $result;
}

github.com

magento/magento2/blob/720667e/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php#L1006-L1040


/**
 * Modify the column definition
 *
 * @param string $tableName
 * @param string $columnName
 * @param array|string $definition
 * @param boolean $flushData
 * @param string $schemaName
 * @return $this
 * @throws \Zend_Db_Exception
 */
public function modifyColumn($tableName, $columnName, $definition, $flushData = false, $schemaName = null)
{
    if (!$this->tableColumnExists($tableName, $columnName, $schemaName)) {
        throw new \Zend_Db_Exception(sprintf('Column "%s" does not exist in table "%s".', $columnName, $tableName));
    }
    if (is_array($definition)) {
        $definition = $this->_getColumnDefinition($definition);
    }


    $sql = sprintf(
        'ALTER TABLE %s MODIFY COLUMN %s %s',
        $this->quoteIdentifier($tableName),
        $this->quoteIdentifier($columnName),
        $definition
    );


    $this->rawQuery($sql);
    if ($flushData) {
        $this->showTableStatus($tableName, $schemaName);
    }
    $this->resetDdlCache($tableName, $schemaName);


    return $this;
}

dmitrii_fediuk · January 26, 2016, 9:19pm