Parse MySQL column default value

[billy1624]

PR Info

• Closes https://github.com/SeaQL/sea-schema/issues/23

Bug Fixes

• [x] Parse MySQL column default value

[billy1624]

1. parse the EXTRA column; if it contains DEFAULT_GENERATED, then the COLUMN_DEFAULT should be regarded as an expression

Hmmm... no. This logic doesn't work on every MySQL / Mariadb version

• https://github.com/SeaQL/sea-schema/actions/runs/5210808637/jobs/9402381504
• https://github.com/SeaQL/sea-schema/actions/runs/5210808637/jobs/9402380721

[tyt2y3]

May be we simply regard CURRENT_TIMESTAMP as special, but only if the column type is timestamp or datatime. Note that, CURRENT_DATE is not special.

Before MariaDB 10.2.1 you couldn't usually provide an expression or function to evaluate at insertion time. You had to provide a constant default value instead. The one exception is that you may use CURRENT_TIMESTAMP as the default value for a TIMESTAMP column to use the current timestamp at insertion time. CURRENT_TIMESTAMP may also be used as the default value for a DATETIME From MariaDB 10.2.1 you can use most functions in DEFAULT. Expressions should have parentheses around them

and this https://mariadb.com/kb/en/information-schema-columns-table/ https://jira.mariadb.org/browse/MDEV-13132 apparently MariaDB would sometimes quote the literal

Default value for the column. From MariaDB 10.2.7, literals are quoted to distinguish them from expressions. NULL means that the column has no default. In MariaDB 10.2.6 and earlier, no quotes were used for any type of default and NULL can either mean that there is no default, or that the default column value is NULL.

[tyt2y3]

Sadly to implement this correctly we have to perform some version check:

MySQL 5: Only literal is supported, so:

1. if column is TIMESTAMP or DATETIME and value is CURRENT_TIMESTAMP, regard it as DefaultExpr::CurrentTimestamp
2. if it is numeric-like then regard it as Number
3. otherwise regard it as String

MySQL 8:

1. parse the EXTRA column; if it contains DEFAULT_GENERATED, then the COLUMN_DEFAULT should be regarded as an expression
2. if the default is an expression, parse it as a Expr and recognize the keywords CURRENT_TIMESTAMP, otherwise Custom
3. if the default is not an expression, then it's a literal; then we should a. if it is numeric-like then regard it as Number b. otherwise regard it as String

MariaDB < 10.2.1: Same as MySQL 5

MariaDB >= 10.2.1:

1. if it starts with a ' it must be a String
2. if it is numeric-like then it is a Number
3. otherwise it is an expression, and can be one of the keywords (CURRENT_TIMESTAMP)

[billy1624]

Hey @tyt2y3, was a success. Please proofread the db specific parsing part for me :)

[billy1624]

Done

[tyt2y3]

I just checked on my machine, MySQL 8 default (NULL) is valid and is different from default null

[tyt2y3]

Wait... I am re-reading this

From MariaDB 10.2.7, literals are quoted to distinguish them from expressions. NULL means that the column has no default

It means for the versions >= 10.2.1 and < 10.2.7 is a void, and there is no way to properly parse the default.

[github-actions[bot]]

:tada: Released In 0.12.0 :tada:

Thank you everyone for the contribution! This feature is now available in the latest release. Now is a good time to upgrade! Your participation is what makes us unique; your adoption is what drives us forward. You can support SeaQL 🌊 by starring our repos, sharing our libraries and becoming a sponsor ⭐.