How to alter a database table?

dmitry_fedyuk · January 26, 2016, 3:38pm

There are ready methods for some popular alterations:

If you need a custom alteration then use the query() and resetDdlCache() methods of the \Magento\Framework\DB\Adapter\AdapterInterface:

github.com

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


/**
 * Prepares and executes an SQL statement with bound data.
 *
 * @param  mixed  $sql  The SQL statement with placeholders.
 *                      May be a string or \Magento\Framework\DB\Select.
 * @param  mixed  $bind An array of data or data itself to bind to the placeholders.
 * @return \Zend_Db_Statement_Interface
 */
public function query($sql, $bind = []);

github.com

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


/**
 * Reset cached DDL data from cache
 * if table name is null - reset all cached DDL data
 *
 * @param string $tableName
 * @param string $schemaName OPTIONAL
 * @return \Magento\Framework\DB\Adapter\AdapterInterface
 */
public function resetDdlCache($tableName = null, $schemaName = null);

An example from the core:

github.com

magento/magento2/blob/d50ee5/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php#L871-L881


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


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


$this->resetDdlCache($tableName, $schemaName);

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#L491-L502


/**
 * Special handling for PDO query().
 * All bind parameter names must begin with ':'.
 *
 * @param string|\Magento\Framework\DB\Select $sql The SQL statement with placeholders.
 * @param mixed $bind An array of data or data itself to bind to the placeholders.
 * @return \Zend_Db_Statement_Pdo|void
 * @throws \Zend_Db_Adapter_Exception To re-throw \PDOException.
 * @throws LocalizedException In case multiple queries are attempted at once, to protect from SQL injection
 * @SuppressWarnings(PHPMD.CyclomaticComplexity)
 */
public function query($sql, $bind = [])

When you have a \Magento\Framework\DB\Adapter\Pdo\Mysql instance you can also use rawQuery() method instead of query() (rawQuery() method is absent in the \Magento\Framework\DB\Adapter\AdapterInterface):

github.com

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


public function rawQuery($sql)
{
    try {
        $result = $this->query($sql);
    } catch (\Zend_Db_Statement_Exception $e) {
        // Convert to \PDOException to maintain backwards compatibility with usage of MySQL adapter
        $e = $e->getPrevious();
        if (!($e instanceof \PDOException)) {
            $e = new \PDOException($e->getMessage(), $e->getCode());
        }
        throw $e;
    }


    return $result;
}

github.com

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


/**
 * Reset cached DDL data from cache
 * if table name is null - reset all cached DDL data
 *
 * @param string $tableName
 * @param string $schemaName OPTIONAL
 * @return $this
 */
public function resetDdlCache($tableName = null, $schemaName = null)

dmitry_fedyuk · January 26, 2016, 4:30pm