Default expression is not loaded into ColumnSchema

[SOHELAHMED7]

When column schema is loaded from DB, for columns having default value as expression but not constant, I get expression as string as default value instead of object of \yii\db\Expression.

Example:

What steps will reproduce the problem?

• create a new table with at least one column that have default expression. Example:

$mysqlColumns = [
            'ts' => 'datetime DEFAULT \'2011-11-11 00:00:00\'',
            'ts2' => 'datetime DEFAULT CURRENT_TIMESTAMP',
            'ts3' => 'datetime DEFAULT CURRENT_TIMESTAMP',
            'ts4' => 'timestamp DEFAULT CURRENT_TIMESTAMP',
            'ts5' => 'timestamp DEFAULT \'2011-11-11 00:00:00\'',
            'ts6' => 'timestamp DEFAULT CURRENT_TIMESTAMP',
            'd' => 'date DEFAULT \'2011-11-11\'',
            'd2' => 'text', // DEFAULT "2011-11-11"
            'd3' => 'text', // DEFAULT CURRENT_DATE + INTERVAL 1 YEAR
            'ts7' => 'date DEFAULT (CURRENT_DATE + INTERVAL 2 YEAR)',
        ];
        if (DB::isPostgres()) {
            $pgsqlColumns = $mysqlColumns;
            $pgsqlColumns['ts7'] = 'date DEFAULT (CURRENT_DATE + INTERVAL \'2 YEAR\')';
            Yii::$app->db->createCommand()->createTable('{{%fruits}}', $pgsqlColumns)->execute();
            return;
        }

        Yii::$app->db->createCommand()->createTable('{{%fruits}}', $mysqlColumns)->execute();

• now get columns schema from yii\db\TableSchema::$columns
• say for e.g. lets take ts7 column from example $ts7ColumnSchema = (object of yii\db\TableSchema)::$columns['ts7']

What is the expected result?

Now I expect $ts7ColumnSchema->defaultValue should be equal to object of \yii\db\Expression('(CURRENT_DATE + INTERVAL 2 YEAR)'') for Mysql

What do you get instead?

I get $ts7ColumnSchema->defaultValue as '(CURRENT_DATE + INTERVAL 2 YEAR)' as string

Hint for solution

For MySQL we get DEFAULT_GENERATED in Extra column, we can use that when we loadColumnSchema()

For PgSQL for default constant, its value is suffixed by ::data_type e.g. ::text

Additional info

Q	A
Yii version	2.0.*
PHP version
Operating system

[samdark]

Doesn't look good. Do you have a time for a fix?

[SOHELAHMED7]

What doesn't look good?

Do you have a time for a fix?

cannot say concretely. But I will try. If I take this issue in hand, can I introduce breaking change if it is needed? At this moment I cannot say that my PR will have breaking change (also I will try to avoid as much as possible) but if I need to choose a path for solution that might break something (e.g. method signature of public method), can I proceed on that path?

[samdark]

@SOHELAHMED7 if breaking change is unavoidable, feel free to do it. But it's better to avoid it.

[SOHELAHMED7]

I came to know that for MariaDB I don't get DEFAULT_GENERATED in Extra column (see this image for MySQL for corresponding data)

I just get empty data in Extra

So for Mariadb it is very hard to detect that default value is constant or expression:

At this moment I am skipping implementation for this issue for Mariadb

Any hint to fix this issue for Mariadb is more than welcome

[SOHELAHMED7]

For Mariadb

While getting info from INFORMATION_SCHEMA

I get default value

• number without quotes
• string with quotes
• expression as string without quotes

It will allow me to check if it is non-empty string without quotes it is expression.

Though I don't think it is 💯 perfect solution, but I will proceed with this in case of Mariadb

[SOHELAHMED7]

Note:

For table

CREATE TABLE  IF NOT EXISTS `datetime_test`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

for column 'ts', in Extra column

for MySQL we get 'DEFAULT_GENERATED on update CURRENT_TIMESTAMP'

for Maria we get 'on update current_timestamp()'

[SOHELAHMED7]

Tests for Mariadb

I am facing difficulties mocking methods/properties (below) that make db = mariadb in tests

public function isMysql()
    {
        return ($this->db->schema instanceof static && !$this->isMariaDb());
    }
    
    public function isMariaDb()
    {
        return strpos($this->db->schema->getServerVersion(), 'MariaDB') !== false;
    }

Also if I use real MariaDB server, I have to setup Mariadb in Github action.

So I have decided to proceed with tests for Mariadb in below way:

I will write tests for Mariadb that will connect with my real local Mariadb server. I will add condition, if db !== mariadb; skip this tests. So theses tests will always be skipped in Github actions but will run if connected to local Mariadb server.

[SOHELAHMED7]

After several attempts and a question at Stack Overflow, detecting default value is constant or expression in PgSQL is complex/not possible/requires lots of unreliable if condition. So I will skip implementation of this issue for PgSQL.