DB Driver migration - SQL Server 2012

[Justin-Barker]

I have been able to consistently reproduce this error within SQL Server 2012.

            "name": "yiisoft/yii2-queue",
            "version": "2.0.2",
            "source": {
                "type": "git",
                "url": "https://github.com/yiisoft/yii2-queue.git",
                "reference": "8c2b337f7d9ea934c2affdfc21c9fb387d0a0773"
            },
            "dist": {
                "type": "zip",
                "url": "https://api.github.com/repos/yiisoft/yii2-queue/zipball/8c2b337f7d9ea934c2affdfc21c9fb387d0a0773",
                "reference": "8c2b337f7d9ea934c2affdfc21c9fb387d0a0773",
                "shasum": ""
            },

Steps:

1. Run migration 'yii\queue\db\migrations'
2. Run migrate down

[Justin-Barker]

I reviewed the DB driver migrations and merged into one for a work around.

m180224_033528_queue.php.zip

[zhuravljov]

Migration can not be combined. It breaks extension updating. What is DF_qu_prior_ object that is dependent on priority column?

[Justin-Barker]

It is from ../src/drivers/db/migrations/M...Priority.php:

public function up()
    {
        $this->addColumn($this->tableName, 'priority', $this->integer()->unsigned()->notNull()->defaultValue(1024)->after('delay'));
        $this->createIndex('priority', $this->tableName, 'priority');
    }

[zhuravljov]

The migration includes priority column and priority index creating. What is DF_qu_prior_ object? Is it special mssql object?

[zhuravljov]

I don't have experience with mssql. I need more info about.

[Justin-Barker]

I am not well versed in MSSQL inner component handling, but from what I am able to discern is that tables will create a constraint object that defines anything from column definitions such as NOT NULL to indices. Therefore, it appears the index must be removed prior to column rollback. This will appear under the table's Constraints folder within MS SQL Studio. See below...

[Edited: add more detailed screenshots]

[Justin-Barker]

SQL Server Developer Edition can be downloaded for free here.

[achretien]

In MSSQL when we use ->defaultValue('xxx') it add a autogenerated CONSTRAINT named DB__xxxx. To drop those columns in migrations, we should drop the constraint before the column.

Because the name is autogenerated, you should find the name it db sys table.

To do that, I added a method in our Migration base:

/**
     * Builds and executes a SQL statement for dropping a column default constraint.
     *
     * @param string $table  the table whose column is to be altered. The name will be properly quoted by the method.
     * @param string $column the name of the column to be altered. The name will be properly quoted by the method.
     */
    public function dropDefaultConstraint($table, $column)
    {
        echo "    > drop default value constraint for column $column from table $table ...";
        $time       = microtime(true);
        $constraint = (new Query())
            ->select(['default_constraints.name'])
            ->from('sys.default_constraints')
            ->innerJoin('sys.all_columns', 'default_constraints.object_id = all_columns.default_object_id')
            ->innerJoin('sys.tables', 'tables.object_id = all_columns.object_id')
            ->innerJoin('sys.schemas', 'schemas.schema_id = tables.schema_id')
            ->where(
                [
                    'AND',
                    ['schemas.name' => 'dbo'],
                    ['tables.name' => $this->db->schema->getRawTableName($table)],
                    ['all_columns.name' => $column],
                ]
            )
            ->scalar($this->db);
        if (!empty($constraint)) {
            $this->db->createCommand()->dropForeignKey($constraint, $table)->execute();
        }
        echo ' done (time: '.sprintf('%.3f', microtime(true) - $time)."s)\n";
    }