Fix alter column with default null (#282)

Tigrov · web-flow · commit ff5d43cd4fe7 · 2023-11-10T07:28:29.000+03:00
* Fix alter column with default null

* Add line to CHANGELOG.md
diff --git a/CHANGELOG.md b/CHANGELOG.md
@@ -5,6 +5,7 @@
 - Bug #275: Refactor `DMLQueryBuilder`, related with yiisoft/db#746 (@Tigrov)
 - Bug #278: Remove `RECURSIVE` expression from CTE queries (@Tigrov)
 - Bug #280: Fix type boolean (@terabytesoftw)
+- Bug #282: Fix `DDLQueryBuilder::alterColumn()` for columns with default null (@Tigrov)
 - Enh #283: Move methods from `Command` to `AbstractPdoCommand` class (@Tigrov)
 
 ## 1.0.1 July 24, 2023
diff --git a/src/DDLQueryBuilder.php b/src/DDLQueryBuilder.php
@@ -53,8 +53,7 @@ public function addDefaultValue(string $table, string $name, string $column, mix
      */
     public function alterColumn(string $table, string $column, ColumnInterface|string $type): string
     {
-        $sqlAfter = [$this->dropConstraintsForColumn($table, $column, 'D')];
-
+        $sqlAfter = [];
         $columnName = $this->quoter->quoteColumnName($column);
         $tableName = $this->quoter->quoteTableName($table);
         $constraintBase = preg_replace('/[^a-z0-9_]/i', '', $table . '_' . $column);
@@ -85,11 +84,11 @@ public function alterColumn(string $table, string $column, ColumnInterface|strin
             }
         }
 
-        return 'ALTER TABLE ' . $tableName
-            . ' ALTER COLUMN '
-            . $columnName . ' '
-            . $this->queryBuilder->getColumnType($type) . "\n"
-            . implode("\n", $sqlAfter);
+        return implode("\n", [
+            $this->dropConstraintsForColumn($table, $column, 'D'),
+            "ALTER TABLE $tableName ALTER COLUMN $columnName {$this->queryBuilder->getColumnType($type)}",
+            ...$sqlAfter,
+        ]);
     }
 
     /**
diff --git a/tests/CommandTest.php b/tests/CommandTest.php
@@ -12,6 +12,7 @@
 use Yiisoft\Db\Exception\InvalidConfigException;
 use Yiisoft\Db\Exception\NotSupportedException;
 use Yiisoft\Db\Expression\Expression;
+use Yiisoft\Db\Mssql\Column;
 use Yiisoft\Db\Mssql\Connection;
 use Yiisoft\Db\Mssql\Dsn;
 use Yiisoft\Db\Mssql\Driver;
@@ -353,4 +354,27 @@ public function testShowDatabases(): void
         $this->assertSame('sqlsrv:Server=localhost,1433;', $db->getDriver()->getDsn());
         $this->assertSame(['yiitest'], $command->showDatabases());
     }
+
+    /** @link https://github.com/yiisoft/db-migration/issues/11 */
+    public function testAlterColumnWithDefaultNull()
+    {
+        $db = $this->getConnection();
+        $command = $db->createCommand();
+
+        if ($db->getTableSchema('column_with_constraint', true) !== null) {
+            $command->dropTable('column_with_constraint')->execute();
+        }
+
+        $command->createTable('column_with_constraint', ['id' => 'pk'])->execute();
+        $command->addColumn('column_with_constraint', 'field', (new Column('integer'))->null()->asString())->execute();
+        $command->alterColumn('column_with_constraint', 'field', (new Column('string', 40))->notNull()->asString())->execute();
+
+        $fieldCol = $db->getTableSchema('column_with_constraint', true)->getColumn('field');
+
+        $this->assertFalse($fieldCol->isAllowNull());
+        $this->assertNull($fieldCol->getDefaultValue());
+        $this->assertSame('nvarchar(40)', $fieldCol->getDbType());
+
+        $command->dropTable('column_with_constraint');
+    }
 }
diff --git a/tests/QueryBuilderTest.php b/tests/QueryBuilderTest.php

Original file line number	Diff line number	Diff line change
`@@ -53,8 +53,7 @@ public function addDefaultValue(string $table, string $name, string $column, mix`
`53`	`53`	`*/`
`54`	`54`	`public function alterColumn(string $table, string $column, ColumnInterface\|string $type): string`
`55`	`55`	`{`
`56`		`- $sqlAfter = [$this->dropConstraintsForColumn($table, $column, 'D')];`
`57`		`-`
	`56`	`+ $sqlAfter = [];`
`58`	`57`	`$columnName = $this->quoter->quoteColumnName($column);`
`59`	`58`	`$tableName = $this->quoter->quoteTableName($table);`
`60`	`59`	`$constraintBase = preg_replace('/[^a-z0-9_]/i', '', $table . '_' . $column);`
`@@ -85,11 +84,11 @@ public function alterColumn(string $table, string $column, ColumnInterface\|strin`
`85`	`84`	`}`
`86`	`85`	`}`
`87`	`86`
`88`		`- return 'ALTER TABLE ' . $tableName`
`89`		`- . ' ALTER COLUMN '`
`90`		`- . $columnName . ' '`
`91`		`- . $this->queryBuilder->getColumnType($type) . "\n"`
`92`		`- . implode("\n", $sqlAfter);`
	`87`	`+ return implode("\n", [`
	`88`	`+ $this->dropConstraintsForColumn($table, $column, 'D'),`
	`89`	`+ "ALTER TABLE $tableName ALTER COLUMN $columnName {$this->queryBuilder->getColumnType($type)}",`
	`90`	`+ ...$sqlAfter,`
	`91`	`+ ]);`
`93`	`92`	`}`
`94`	`93`
`95`	`94`	`/**`