yii2:支持mysql和postgresql

b0zn9rqh  于 2021-06-15  发布在  Mysql
关注(0)|答案(2)|浏览(313)

我的yii2应用程序应该允许客户使用mysql或postgresql作为数据库后端。所以我需要为这两个数据库编写代码。
我开始支持mysql,需要删除mysql特定的代码,例如在迁移中:

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'
    );
}

如何重写这段代码?
有没有解决这个问题的回购?mysql和postgresql兼容性的最佳实践是什么?

7vux5j2d

7vux5j2d1#

我对postgre不是很熟悉,但是从app-advanced中的基本迁移来看,你可以查看 driverName 并在此基础上创建不同的逻辑。
迁移文件

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

rseugnpd2#

如果我正确理解您所说的编写迁移,它可以根据当前选定的或目标数据库处理特定于db的关键字或函数,那么您可能需要为迁移创建自己的逻辑来支持它。
我使用下面的类作为基本迁移类,并扩展从这个类到 tableOptions 删除列约束。您可以根据需要对其进行升级并添加必要的操作/功能。但它可以创建逻辑来实现你的要求。

<?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']));
        }
    }

}

相关问题