Yii2: Supporting MySQL and PostgreSQL

Question

My Yii2 application should allow customers to use MySQL or PostgreSQL as database backends. So I need to write code which runs for both databases.

I started supporting MySQL and need to eleminate MySQL specific code, e.g. in migrations:

public function up() {
    $this->execute('ALTER SCHEMA DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci');
    $this->execute('ALTER SCHEMA CHARACTER SET utf8 COLLATE utf8_general_ci');
    $this->createTable('user', [ 
            'id' => $this->primaryKey(),
            ... 
        ],
       'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB'
    );
}

How to rewrite this piece of code?

Are there repos which have tackled this? What are best practices for MySQL and PostgreSQL compatibility?

Jørgen · Accepted Answer · 2019-01-09 11:23:10Z

2

I'm not that familiar with Postgre, but by the looks of the basic migration in app-advanced you can check the driverName and create different logic based on that.

Migration File

$tableOptions = null;
if ($this->db->driverName === 'mysql') {
    $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
}

answered Jan 9, 2019 at 11:23

Jørgen

3,5876 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Muhammad Omer Aslam · Accepted Answer · 2019-01-09 12:12:21Z

If i understand correctly you are talking about writing migrations which can handle the db specific keywords or functions depending upon the currently selected or targetted databases, then you might have to create your own logic for that for the migrations to support it.

I use the following class as the base Migration class and extend the migrations from this class for the tableOptions and dropping column constraints. You can upgrade it and add the necessary operations/functions according to your needs. But it can create the logic to implement what you are asking.

<?php

namespace console\migrations;
use Yii;
class Migration extends \yii\db\Migration
{

    /**
     * @var string
     */
    protected $tableOptions;

    /**
     * @var string
     */
    protected $restrict = 'RESTRICT';

    /**
     * @var string
     */
    protected $cascade = 'CASCADE';

    /**
     * @var string
     */
    protected $noAction = 'NO ACTION';

    /**
     * @var mixed
     */
    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!');
        }
    }

    /**
     * Drops the constraints for the given column
     *
     * @param string $table  the table name
     * @param string $column the column name
     *
     * @return null
     */
    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']));
        }
    }

}

Thank you. Why do you use $this->dbType? This is only shorthand for $this->db->driverName. Or do I miss something?

Collectives™ on Stack Overflow

Yii2: Supporting MySQL and PostgreSQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related