MSSQL Migration enahnchements

[tsdogs]

When using migrations with MSSQL the dropColumn, dropTable are often blocked by constraints created automatically by SQL Server (i.e. a DEFAULT VALUE), and doing up and down is a PITA.

I have created the attached Migration class which I use to mitigate this.

N.B. This though does not solve the problem on when doing an alterTable and changing a field which has a constraint defined.
If I add a column link this:

$this->addColumn('{{%table}}','field',$this->integer()->null());
SQL Server would add a contraint for the NULL default value.

Then doing:
$this->alterColumn('{{%table}}','field',$this->string(40)->notNull());
would FAIL as there is a constraint.
this->dropColumnConstraints('{{%table}}','field');
must be called before.

Maybe can be of help for integration in default yii2 Migration ...

[tsdogs]

Migration.php

namespace app\migrations;

use Yii;

class Migration extends \yii\db\Migration
{
    /**
     * @var string
     */
    protected $tableOptions;
    protected $restrict = 'RESTRICT';
    protected $cascade = 'CASCADE';
    protected $dbType;
    

    /**
     * @inheritdoc
     */
    public function init()
    {
        parent::init();

        switch ($this->db->driverName) {
            case 'mysql':
                $this->tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
                $this->dbType = 'mysql';
                break;
            case 'pgsql':
                $this->tableOptions = null;
                $this->dbType = 'pgsql';
                break;
            case 'dblib':
            case 'mssql':
            case 'sqlsrv':
                $this->restrict = 'NO ACTION';
                $this->tableOptions = null;
                $this->dbType = 'sqlsrv';
                break;
            default:
                throw new \RuntimeException('Your database is not supported!');
        }
    }
    
    public function dropTable($table)
    {
        if ($this->dbType = 'sqlsrv') {
            $this->dropTableConstraints($table);
        }
        return parent::dropTable($table);
    }
    
    public function dropColumn($table, $column)
    {
        if ($this->dbType = 'sqlsrv') {
            $this->dropColumnConstraints($table, $column);
        }
        return parent::dropColumn($table, $column);
    }
    
    /*
     *  Drops contratints and Indexes referencind a Table Column
     */
    public function dropColumnConstraints($table, $column)
    {
        $table = Yii::$app->db->schema->getRawTableName($table);
        $cmd = Yii::$app->db->createCommand('SELECT name FROM sys.default_constraints
                                WHERE parent_object_id = object_id(:table)
                                AND type = \'D\' AND parent_column_id = (
                                    SELECT column_id 
                                    FROM sys.columns 
                                    WHERE object_id = object_id(:table)
                                          AND name = :column
                                )', [ ':table' => $table, ':column' => $column ]);
                                
        $constraints = $cmd->queryAll();
        foreach ($constraints as $c) {
            $this->execute('ALTER TABLE '.Yii::$app->db->quoteTableName($table).' DROP CONSTRAINT '.Yii::$app->db->quoteColumnName($c['name']));
        }
        
        // checking for indexes
        $cmd = Yii::$app->db->createCommand('SELECT ind.name FROM sys.indexes ind
                                                INNER JOIN sys.index_columns ic 
                                                    ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
                                                INNER JOIN sys.columns col 
                                                    ON ic.object_id = col.object_id and ic.column_id = col.column_id 
                                                WHERE ind.object_id = object_id(:table)
                                                AND col.name = :column',
                                [ ':table' => $table, ':column' => $column ]);
                                
        $indexes = $cmd->queryAll();
        foreach ($indexes as $i) {
            $this->dropIndex($i['name'],$table);
        }
    }
    
    /*
     *  Drops contratints referencing the Table
     */
    public function dropTableConstraints($table)
    {
        $table = Yii::$app->db->schema->getRawTableName($table);
        $cmd = Yii::$app->db->createCommand('SELECT name, OBJECT_NAME(parent_object_id) as tbl FROM sys.foreign_keys
                                WHERE referenced_object_id = object_id(:table)',
                                [ ':table' => $table ]);
        $constraints = $cmd->queryAll();
        foreach ($constraints as $c) {
            echo 'Dropping constrain: '.$c['name']."\n";
            $this->execute('ALTER TABLE '.Yii::$app->db->quoteTableName($c['tbl']).' DROP CONSTRAINT '.Yii::$app->db->quoteColumnName($c['name']));
        }
    }
    
}

[tsdogs]

Just an update 'cause windows srvsql driver does not like the original query to check constraints (it worked with dblib though).
This is an updated version of the query.

$cmd = Yii::$app->db->createCommand('SELECT name FROM sys.default_constraints
                                WHERE parent_object_id = object_id(:table_1)
                                AND type = \'D\' AND parent_column_id IN (
                                    SELECT column_id 
                                    FROM sys.columns 
                                    WHERE object_id = object_id(:table_2)
                                          AND name = :column
                                )', [ ':table_1' => $table,  ':table_2'=>$table, ':column' => $column ]);

[samdark]

See #14105

[cebe]

as #14105 is merged, is this solved or is there more work to do?

[sergeymakinen]

@cebe a low level logic is here, a high level one is coming soon. This issue describes both as I understand.

[Tigrov]

Fixed by yiisoft/db-mssql#282