Migration does not work for Postgresql

[mtangoo]

the COMMENT and KEY does not work with Postgresql

here is the migration class that works with Postgresql. Note only getTables() method is modified

<?php

namespace rhertogh\Yii2Oauth2Server\migrations;

use rhertogh\Yii2Oauth2Server\interfaces\models\Oauth2AccessTokenInterface;
use rhertogh\Yii2Oauth2Server\interfaces\models\Oauth2AccessTokenScopeInterface;
use rhertogh\Yii2Oauth2Server\interfaces\models\Oauth2AuthCodeInterface;
use rhertogh\Yii2Oauth2Server\interfaces\models\Oauth2AuthCodeScopeInterface;
use rhertogh\Yii2Oauth2Server\interfaces\models\Oauth2ClientInterface;
use rhertogh\Yii2Oauth2Server\interfaces\models\Oauth2ClientScopeInterface;
use rhertogh\Yii2Oauth2Server\interfaces\models\Oauth2RefreshTokenInterface;
use rhertogh\Yii2Oauth2Server\interfaces\models\Oauth2ScopeInterface;
use rhertogh\Yii2Oauth2Server\interfaces\models\Oauth2UserClientInterface;
use rhertogh\Yii2Oauth2Server\interfaces\models\Oauth2UserClientScopeInterface;
use rhertogh\Yii2Oauth2Server\migrations\base\Oauth2BaseMigration;
use rhertogh\Yii2Oauth2Server\models\Oauth2AccessToken;
use rhertogh\Yii2Oauth2Server\models\Oauth2Client;
use rhertogh\Yii2Oauth2Server\models\Oauth2Scope;
use rhertogh\Yii2Oauth2Server\Oauth2Module;
use yii\base\InvalidConfigException;
use yii\db\Schema;

/**
 * phpcs:disable Squiz.Classes.ValidClassName.NotCamelCaps
 * phpcs:disable Generic.Files.LineLength.TooLong
 */
abstract class Oauth2_00001_CreateOauth2TablesMigration extends Oauth2BaseMigration
{
    /**
     * @var int Number of tables expected to be returned by getTables(),
     * when dependency injection is misconfigured this can be off.
     */
    protected $numTables = 10;

    /**
     * @inheritDoc
     */
    public static function generationIsActive($module)
    {
        return true;
    }

    /**
     * @inheritDoc
     */
    public function safeUp()
    {
        foreach ($this->getTables() as $table => $definition) {
            $this->createTable($table, $definition);
        }
    }

    /**
     * @inheritDoc
     */
    public function safeDown()
    {
        foreach (array_reverse($this->getTables()) as $table => $definition) {
            $this->dropTable($table);
        }
    }

    /**
     * Get all table definitions.
     * @return array[]
     * @throws InvalidConfigException
     * @since 1.0.0
     */
    protected function getTables()
    {
        $module = Oauth2Module::getInstance();
        if (empty($module)) {
            throw new InvalidConfigException('Oauth2Module is not instantiated. Is it added to the config in the "module" and "bootstrap" section?');
        }

        $accessTokenTable = $this->getTableName(Oauth2AccessTokenInterface::class);
        $accessTokenScopeTable = $this->getTableName(Oauth2AccessTokenScopeInterface::class);
        $authCodeTable = $this->getTableName(Oauth2AuthCodeInterface::class);
        $authCodeScopeTable = $this->getTableName(Oauth2AuthCodeScopeInterface::class);
        $clientTable = $this->getTableName(Oauth2ClientInterface::class);
        $clientScopeTable = $this->getTableName(Oauth2ClientScopeInterface::class);
        $refreshTokenTable = $this->getTableName(Oauth2RefreshTokenInterface::class);
        $scopeTable = $this->getTableName(Oauth2ScopeInterface::class);
        $userClientTable = $this->getTableName(Oauth2UserClientInterface::class);
        $userClientScopeTable = $this->getTableName(Oauth2UserClientScopeInterface::class);

        $userTableSchema = $this->getTableSchema($module->identityClass);
        if ($userTableSchema) {
            if (count($userTableSchema->primaryKey) != 1) {
                throw new InvalidConfigException('The primary key of `userClass` must consist of a single column');
            }
            $userTable = $userTableSchema->name;
            $userPkColumn = $userTableSchema->primaryKey[0];
            $userPkSchema = $userTableSchema->columns[$userPkColumn];
        } else {
            $userTable = false;
            $userPkColumn = null;
            $userPkSchema = null;
        }

        // See https://datatracker.ietf.org/doc/html/rfc7591#section-2
        // (although not yet fully implemented, some fields follow this standard).
        $tables = [
            $clientTable => [
                'id' => $this->primaryKey(),
                'identifier' => $this->string()->notNull()->unique(),
                'name' => $this->string()->notNull(),
                'type' => $this->boolean()->notNull()->defaultValue(boolval(Oauth2ClientInterface::TYPE_CONFIDENTIAL)),
                'secret' => $this->text(),
                'old_secret' => $this->text(),
                'old_secret_valid_until' => $this->dateTime(),
                'logo_uri' => $this->string(),
                'tos_uri' => $this->string(),
                'contacts' => $this->text(),
                'redirect_uris' => $this->json(),
                'token_types' => $this->integer()->notNull()->defaultValue(Oauth2AccessToken::TYPE_BEARER),
                'grant_types' => $this->integer()->notNull()->defaultValue(Oauth2Module::GRANT_TYPE_AUTH_CODE | Oauth2Module::GRANT_TYPE_REFRESH_TOKEN),
                'scope_access' => $this->integer()->notNull()->defaultValue(Oauth2Client::SCOPE_ACCESS_STRICT),
                'user_account_selection' => $this->integer(),
                'allow_auth_code_without_pkce' => $this->boolean()->notNull()->defaultValue(false),
                'skip_authorization_if_scope_is_allowed' => $this->boolean()->notNull()->defaultValue(false),
                'client_credentials_grant_user_id' => ($userTable ? $userPkSchema->dbType : Schema::TYPE_STRING),
                'oidc_allow_offline_access_without_consent' => $this->boolean()->notNull()->defaultValue(false),
                'oidc_userinfo_encrypted_response_alg' => $this->string(),
                'enabled' => $this->boolean()->notNull()->defaultValue(true),
                'created_at' => $this->integer()->notNull(),
                'updated_at' => $this->integer()->notNull(),

                ...($userTable
                    ? ['FOREIGN KEY (client_credentials_grant_user_id) REFERENCES ' . $userTable . ' (' . $userPkColumn . ') ON DELETE RESTRICT ON UPDATE CASCADE']
                    : ['KEY (client_credentials_grant_user_id)']
                ),
            ],

            $scopeTable => [
                'id' => $this->primaryKey(),
                'identifier' => $this->string()->notNull()->unique(),
                'description' => $this->text(),
                'authorization_message' => $this->text(),
                'applied_by_default' => $this->string()->notNull()->defaultValue(Oauth2Scope::APPLIED_BY_DEFAULT_NO),
                'required_on_authorization' => $this->boolean()->notNull()->defaultValue(true),
                'enabled' => $this->boolean()->notNull()->defaultValue(true),
                'created_at' => $this->integer()->notNull(),
                'updated_at' => $this->integer()->notNull(),
            ],

            $clientScopeTable => [
                //'id' => $this->primaryKey(),
                'client_id' => $this->integer()->notNull(),
                'scope_id' => $this->integer()->notNull(),
                'applied_by_default' => $this->integer(),
                'required_on_authorization' => $this->boolean(),
                'enabled' => $this->boolean()->notNull()->defaultValue(true),
                'created_at' => $this->integer()->notNull(),
                'updated_at' => $this->integer()->notNull(),
                'PRIMARY KEY (client_id, scope_id)',
                'FOREIGN KEY (client_id) REFERENCES ' . $clientTable . ' (id) ON DELETE CASCADE ON UPDATE CASCADE',
                'FOREIGN KEY (scope_id) REFERENCES ' . $scopeTable . ' (id) ON DELETE CASCADE ON UPDATE CASCADE',
            ],

            $authCodeTable => [
                'id' => $this->bigPrimaryKey()->unsigned(),
                'identifier' => $this->string()->notNull()->unique(),
                'redirect_uri' => $this->string(),
                'expiry_date_time' => $this->dateTime()->notNull(),
                'client_id' => $this->integer()->notNull(),
                'user_id' => ($userTable ? $userPkSchema->dbType : Schema::TYPE_STRING) . ' NOT NULL',
                'enabled' => $this->boolean()->notNull()->defaultValue(true), // ToDo: do we need this ???
                'created_at' => $this->integer()->notNull(),
                'updated_at' => $this->integer()->notNull(),

                'FOREIGN KEY (client_id) REFERENCES ' . $clientTable . ' (id) ON DELETE CASCADE ON UPDATE CASCADE',
                ...($userTable
                    ? ['FOREIGN KEY (user_id) REFERENCES ' . $userTable . ' (' . $userPkColumn . ') ON DELETE CASCADE ON UPDATE CASCADE']
                    : ['KEY (user_id)']
                ),
            ],

            $authCodeScopeTable => [
                'auth_code_id' => $this->bigInteger()->unsigned()->notNull(),
                'scope_id' => $this->integer()->notNull(),
                'created_at' => $this->integer()->notNull(),

                'PRIMARY KEY (auth_code_id, scope_id)',
                'FOREIGN KEY (auth_code_id) REFERENCES ' . $authCodeTable . ' (id) ON DELETE CASCADE ON UPDATE CASCADE',
                'FOREIGN KEY (scope_id) REFERENCES ' . $scopeTable . ' (id) ON DELETE CASCADE ON UPDATE CASCADE',
            ],

            $accessTokenTable => [
                'id' => $this->bigPrimaryKey()->unsigned(),
                'identifier' => $this->string()->notNull()->unique(),
                'client_id' => $this->integer()->notNull(),
                'user_id' => ($userTable ? $userPkSchema->dbType : Schema::TYPE_STRING) . ' DEFAULT NULL',
                'type' => $this->integer()->notNull(),
                'mac_key' => $this->string(500),
                'mac_algorithm' => Schema::TYPE_SMALLINT,
                'allowance' => Schema::TYPE_SMALLINT,
                'allowance_updated_at' => $this->integer(),
                'expiry_date_time' => $this->dateTime()->notNull(),
                'enabled' => $this->boolean()->notNull()->defaultValue(true),
                'created_at' => $this->integer()->notNull(),
                'updated_at' => $this->integer()->notNull(),

                'FOREIGN KEY (client_id) REFERENCES ' . $clientTable . ' (id) ON DELETE CASCADE ON UPDATE CASCADE',
                ...($userTable
                    ? ['FOREIGN KEY (user_id) REFERENCES ' . $userTable . ' (' . $userPkColumn . ') ON DELETE CASCADE ON UPDATE CASCADE']
                    : ['KEY (user_id)']
                ),
            ],

            $accessTokenScopeTable => [
                'access_token_id' => $this->bigInteger()->unsigned()->notNull(),
                'scope_id' => $this->integer()->notNull(),
                'created_at' => $this->integer()->notNull(),

                'PRIMARY KEY (access_token_id, scope_id)',
                'FOREIGN KEY (access_token_id) REFERENCES ' . $accessTokenTable . ' (id) ON DELETE CASCADE ON UPDATE CASCADE',
                'FOREIGN KEY (scope_id) REFERENCES ' . $scopeTable . ' (id) ON DELETE CASCADE ON UPDATE CASCADE',
            ],

            $refreshTokenTable => [
                'id' => $this->bigPrimaryKey()->unsigned(),
                'access_token_id' => $this->bigInteger()->unsigned(),
                'identifier' => $this->string()->notNull()->unique(),
                'expiry_date_time' => $this->dateTime()->notNull(),
                'enabled' => $this->boolean()->notNull()->defaultValue(true),
                'created_at' => $this->integer()->notNull(),
                'updated_at' => $this->integer()->notNull(),

                'FOREIGN KEY (access_token_id) REFERENCES ' . $accessTokenTable . ' (id) ON DELETE CASCADE ON UPDATE CASCADE',
            ],

            $userClientTable => [
                'user_id' => ($userTable ? $userPkSchema->dbType : Schema::TYPE_STRING) . ' NOT NULL',
                'client_id' => $this->integer()->notNull(),
                'enabled' => $this->boolean()->notNull()->defaultValue(true),
                'created_at' => $this->integer()->notNull(),
                'updated_at' => $this->integer()->notNull(),

                'PRIMARY KEY (user_id, client_id)',
                ...($userTable
                    ? ['FOREIGN KEY (user_id) REFERENCES ' . $userTable . ' (' . $userPkColumn . ') ON DELETE CASCADE ON UPDATE CASCADE']
                    : ['KEY (user_id)']
                ),
                'FOREIGN KEY (client_id) REFERENCES ' . $clientTable . ' (id) ON DELETE CASCADE ON UPDATE CASCADE',
            ],

            $userClientScopeTable => [
                'user_id' => ($userTable ? $userPkSchema->dbType : Schema::TYPE_STRING) . ' NOT NULL',
                'client_id' => $this->integer()->notNull(),
                'scope_id' => $this->integer()->notNull(),
                'enabled' => $this->boolean()->notNull()->defaultValue(true),
                'created_at' => $this->integer()->notNull(),
                'updated_at' => $this->integer()->notNull(),

                'PRIMARY KEY (user_id, client_id, scope_id)',
                'FOREIGN KEY (user_id, client_id) REFERENCES ' . $userClientTable . ' (user_id, client_id) ON DELETE CASCADE ON UPDATE CASCADE',
                'FOREIGN KEY (scope_id) REFERENCES ' . $scopeTable . ' (id) ON DELETE CASCADE ON UPDATE CASCADE', # Note: Not connected to client_scope table since scopes can also be applied by default to all clients
            ],
        ];

        if (count(array_unique(array_keys($tables))) != $this->numTables) {
            throw new InvalidConfigException('Incorrect number of tables in definition. Are the Active Record classes correctly configured?');
        }

        return $tables;
    }

    //    /**
    //     * @param string $tableClass
    //     * @return ActiveRecord
    //     * @throws InvalidConfigException
    //     */
    //    protected function getArInstance($tableClass)
    //    {
    //        $activeRecord = Yii::createObject($tableClass);
    //
    //        if (!($activeRecord instanceof ActiveRecord)) {
    //            throw new InvalidConfigException($tableClass . ' must be an instance of ActiveRecord');
    //        }
    //
    //        return $activeRecord;
    //    }
}

[rhertogh]

@mtangoo Thanks for your post. I'll try to look into it this weekend.

[mtangoo]

Related: yiisoft/yii2#17449 (comment)

[rhertogh]

@mtangoo I've release version 1.0.0-alpha5, it should be compatible with PostgreSQL (tested with v14). Could you please verify if it's working for you?

[mtangoo]

Sure!
I will test it later today. Thanks

[mtangoo]

Sure!
I will test it later today. Thanks

[rhertogh]

@mtangoo Since I haven't heard anything I'm closing this one for now. In case you encounter any issues, please open a new one.

[mtangoo]

I have not got time to test this. When I go back and apply the fix, I will definitely report any issue
Thanks for your patience!