public function getAlterTableSQL(TableDiff $diff)
{
$queryParts = array();
$sql = array();
$columnSql = array();
$commentsSql = array();
/** @var \Doctrine\DBAL\Schema\Column $column */
foreach ($diff->addedColumns as $column) {
if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) {
continue;
}
$columnDef = $column->toArray();
$queryParts[] = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef);
if (isset($columnDef['default'])) {
$queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column);
}
$comment = $this->getColumnComment($column);
if (!empty($comment) || is_numeric($comment)) {
$commentsSql[] = $this->getCreateColumnCommentSQL($diff->name, $column->getQuotedName($this), $comment);
}
}
foreach ($diff->removedColumns as $column) {
if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) {
continue;
}
$queryParts[] = 'DROP COLUMN ' . $column->getQuotedName($this);
}
/* @var $columnDiff \Doctrine\DBAL\Schema\ColumnDiff */
foreach ($diff->changedColumns as $columnDiff) {
if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) {
continue;
}
$column = $columnDiff->column;
$comment = $this->getColumnComment($column);
$hasComment = !empty($comment) || is_numeric($comment);
if ($columnDiff->fromColumn instanceof Column) {
$fromComment = $this->getColumnComment($columnDiff->fromColumn);
$hasFromComment = !empty($fromComment) || is_numeric($fromComment);
if ($hasFromComment && $hasComment && $fromComment != $comment) {
$commentsSql[] = $this->getAlterColumnCommentSQL($diff->name, $column->getQuotedName($this), $comment);
} elseif ($hasFromComment && !$hasComment) {
$commentsSql[] = $this->getDropColumnCommentSQL($diff->name, $column->getQuotedName($this));
} elseif ($hasComment) {
$commentsSql[] = $this->getCreateColumnCommentSQL($diff->name, $column->getQuotedName($this), $comment);
}
} else {
// todo: Original comment cannot be determined. What to do? Add, update, drop or skip?
}
// Do not add query part if only comment has changed.
if ($columnDiff->hasChanged('comment') && count($columnDiff->changedProperties) === 1) {
continue;
}
$requireDropDefaultConstraint = $this->alterColumnRequiresDropDefaultConstraint($columnDiff);
if ($requireDropDefaultConstraint) {
$queryParts[] = $this->getAlterTableDropDefaultConstraintClause($diff->name, $columnDiff->oldColumnName);
}
$columnDef = $column->toArray();
$queryParts[] = 'ALTER COLUMN ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef);
if (isset($columnDef['default']) && ($requireDropDefaultConstraint || $columnDiff->hasChanged('default'))) {
$queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column);
}
}
foreach ($diff->renamedColumns as $oldColumnName => $column) {
if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) {
continue;
}
$oldColumnName = new Identifier($oldColumnName);
$sql[] = "sp_RENAME '" . $diff->getName($this)->getQuotedName($this) . "." . $oldColumnName->getQuotedName($this) . "', '" . $column->getQuotedName($this) . "', 'COLUMN'";
// Recreate default constraint with new column name if necessary (for future reference).
if ($column->getDefault() !== null) {
$queryParts[] = $this->getAlterTableDropDefaultConstraintClause($diff->name, $oldColumnName->getQuotedName($this));
$queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column);
}
}
$tableSql = array();
if ($this->onSchemaAlterTable($diff, $tableSql)) {
return array_merge($tableSql, $columnSql);
}
foreach ($queryParts as $query) {
$sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query;
}
$sql = array_merge($sql, $commentsSql);
if ($diff->newName !== false) {
$sql[] = "sp_RENAME '" . $diff->getName($this)->getQuotedName($this) . "', '" . $diff->getNewName()->getName() . "'";
/**
* Rename table's default constraints names
* to match the new table name.
* This is necessary to ensure that the default
* constraints can be referenced in future table
* alterations as the table name is encoded in
* default constraints' names.
*/
$sql[] = "DECLARE @sql NVARCHAR(MAX) = N''; " . "SELECT @sql += N'EXEC sp_rename N''' + dc.name + ''', N''' " . "+ REPLACE(dc.name, '" . $this->generateIdentifierName($diff->name) . "', " . "'" . $this->generateIdentifierName($diff->newName) . "') + ''', ''OBJECT'';' " . "FROM sys.default_constraints dc " . "JOIN sys.tables tbl ON dc.parent_object_id = tbl.object_id " . "WHERE tbl.name = '" . $diff->getNewName()->getName() . "';" . "EXEC sp_executesql @sql";
}
$sql = array_merge($this->getPreAlterTableIndexForeignKeySQL($diff), $sql, $this->getPostAlterTableIndexForeignKeySQL($diff));
return array_merge($sql, $tableSql, $columnSql);
}