[RFC]: MySQL DDL column attributes: fractional seconds, ON UPDATE for DATETIME, generated columns, INVISIBLE, strict option handling

[simon-mundy]

Proposed Version

0.5.0 — builds on #79, which needs to merge first

Basic Information

Currently the MySQL decorators handle unsigned, zerofill, charset, collate, auto_increment (and aliases), comment, column_format, storage (CreateTableDecorator.php#L118-L170, AlterTableDecorator.php#L130-L185) and after (ADD COLUMN only, in AlterTableDecorator::processAddColumns — ignored for CHANGE COLUMN and in CreateTableDecorator). Anything else is dropped without a word: none of the three switch statements (create, add, change) has a default case, so setOption('invisible', true) or setOption('srid', '4326') emit nothing.

This proposal adds the attributes the decorators can't express (GENERATED ALWAYS AS, INVISIBLE), fixes the silent drop, and files a small core sub-task for fractional-second precision, ON UPDATE on Datetime, and a wider Column::setOption() type. Every new option value goes out as an identifier, a quoted value, a whitelisted keyword or an explicit Literal — the invariant #79 introduces.

Background

What is missing on the write side, all within the >= 8.0.19 baseline unless noted:

Attribute	Status	Where the gap is
Fractional seconds DATETIME(6), TIMESTAMP(3), TIME(6)	missing	core: Datetime, Time, Timestamp have no length slot (spec %s %s)
ON UPDATE CURRENT_TIMESTAMP on DATETIME	silently ignored	core: only Timestamp extends AbstractTimestampColumn (AbstractTimestampColumn.php#L22-L25); Datetime extends Column
GENERATED ALWAYS AS (expr) VIRTUAL | STORED	missing	adapter
INVISIBLE	missing	adapter; MySQL 8.0.23+, above the baseline, needs documenting as such
Unknown option	silently ignored	adapter (see the #79 note under Considerations)
Options after an inline CHECK	cosmetic	getSqlInsertOffsets() doesn't know CHECK, so the last-slot options land after it: INTEGER NOT NULL CHECK (age >= 0) COMMENT 'c'. MySQL 8.4.10 (reproduced on 8.0.46) accepts that order, so low priority

The phpdb-mysql-ddl-overrides branch has GeneratedColumn, Datetime/Time/Timestamp with an $fsp argument, a MySQL Check with ENFORCED, an IndexOptionsTrait and Fulltext/Spatial index classes. It predates the invariant #79 introduces — GeneratedColumn concatenates the expression and DATETIME(' . $fsp . ') is built as a Literal — so the design and tests carry over, the rendering does not.

Considerations

• Validate DDL column options and PDO DSN parameters #79. At 6ee0d25d the ignore-unknown-option behaviour is neither documented nor tested. Validate DDL column options and PDO DSN parameters #79 adds a 'nonsense' => 'ignored' case to ColumnOptionMatrix for addColumn and changeColumn, so once it merges the ignore behaviour is pinned and switching to throwing changes a Validate DDL column options and PDO DSN parameters #79 expectation. Best decided with Validate DDL column options and PDO DSN parameters #79, not after it.
• Core type. Column::setOption() is typed bool|string, so setOption('generated', new Literal(...)) and setOption('srid', 4326) are TypeErrors today; only the constructor $options array and setOptions() accept them. Widening it is part of the core sub-task (overlaps Untyped array boundaries (AbstractConnection::$connectionParameters, ColumnInterface::getOptions()) cause mixed-* fallout downstream phpdb#169).
• Generated-column attribute order is strict. ... NOT NULL STORED and AS (expr) COLLATE ... STORED are both 1064; the decorator has to emit data_type [COLLATE] GENERATED ALWAYS AS (expr) [VIRTUAL|STORED] [NOT NULL|NULL] [VISIBLE|INVISIBLE] [UNIQUE] [PRIMARY KEY] [COMMENT] ....
• CI. One floating mysql:8.0 image through the shared phpdb-qa-tools workflow, which takes a single db-image string and has no matrix. That image is 8.0.4x, so INVISIBLE (8.0.23+) already runs there. Pinning the 8.0.19 floor and adding LTS legs is in the support-floor RFC ([RFC]: Document the MySQL >= 8.0.19 / PHP >= 8.3 support floor and cover ALTER TABLE drop paths with MySQL tests #80) — each image is a separate uses: job.
• Docs. The adapter has no option table (docs/book/adapter.md is empty); the only one is core's docs/book/sql-ddl/columns.md#L451-L457, which could gain a MySQL-availability column.
• Insert offsets. range(0, 3) in getSqlInsertOffsets() already allocates a slot 3 that is never populated; a new CONSTRAINT/ CHECK needle has to cascade into any lower slot that is unset, as the NOT NULL/DEFAULT needles do, or the options still end up after the check.
• Out of scope: MariaDB (PERSISTENT, different INVISIBLE availability); table-level options and new statements ([RFC]: DDL standalone index, view, rename and truncate statements, and typed table options phpdb#179); spatial SRID rendering (goes with the spatial column classes in [RFC]: DDL column classes for ENUM, SET and the remaining MySQL column types phpdb#180).

Proposal(s)

Core (separate issue in php-db/phpdb, linked here):

1. Optional precision on Datetime, Time and Timestamp (constructor argument or precision option) rendering DATETIME(6) etc. via an Argument\Literal built from an int in 0..6 (cast to string — Argument\Literal takes a string).
2. on_update for Datetime — extend AbstractTimestampColumn, or move the option handling into a trait — emitting CURRENT_TIMESTAMP(n) when a precision is set, since MySQL requires the ON UPDATE precision to match the column's.
3. Widen Column::setOption() from bool|string to bool|int|string|Literal.

Adapter:

1. generated (an Argument\Literal or PhpDb\Sql\Literal expression) plus stored (bool, default false) render GENERATED ALWAYS AS (<expr>) STORED|VIRTUAL in the order above. Reject a column that also has DEFAULT or AUTO_INCREMENT — MySQL disallows both on generated columns.
2. invisible (bool) renders INVISIBLE between the default and AUTO_INCREMENT, per ... [DEFAULT ...] [VISIBLE | INVISIBLE] [AUTO_INCREMENT] .... Document the 8.0.23 requirement.
3. Unknown option names throw InvalidArgumentException naming the option and the accepted list — or, if agreed with Validate DDL column options and PDO DSN parameters #79, stay ignored but documented.
4. getSqlInsertOffsets() treats CONSTRAINT and CHECK as boundaries for the last slot so COMMENT/COLUMN_FORMAT/STORAGE land before an inline check, with the cascade noted above.
5. length on Integer is ignored by the MySQL decorator (deprecated since 8.0.17) and documented as such. The core rendering fix is Integer length option renders the display width after NOT NULL phpdb#178, so platforms that accept a display width keep INTEGER(11) NOT NULL (PostgreSQL doesn't accept one either).

Test plan:

• new Datetime('d', options: ['precision' => 6]) (or a constructor argument) renders `d` DATETIME(6) NOT NULL; with on_update, ... ON UPDATE CURRENT_TIMESTAMP(6); same for Timestamp and Time.
• new Decimal('total', 10, 2, options: ['generated' => new Literal('price * qty'), 'stored' => true]) (or setOption() once widened) renders `total` DECIMAL(10,2) GENERATED ALWAYS AS (price * qty) STORED NOT NULL; without stored, VIRTUAL.
• ['invisible' => true] renders INVISIBLE in the right position; docs say 8.0.23+.
• An unknown option throws (or is documented and tested as ignored, agreed with Validate DDL column options and PDO DSN parameters #79).
• test/unit/Sql/Ddl/TestAsset/ColumnOptionMatrix.php (from Validate DDL column options and PDO DSN parameters #79) gains cases for every new option and pins exact SQL for CreateTable, AlterTable::addColumn and AlterTable::changeColumn.
• An integration test executes each new attribute against the CI MySQL image.

Appendix/Additional Info

• Validate DDL column options and PDO DSN parameters #79 (option validation), DDL does not render Charset/Collation options during Alter or Create Table #47 (charset/collate, closed), Fix record: mixed-* findings in Connection/Pdo/Connection/Ddl decorators stem from untyped upstream array boundaries #65 and [Analysis] AlterTableDecorator.php: $insertStart[$j] loop-bound findings stem from mago flow-analysis limitation #69 (open mago findings in the decorators — analysis noise, not bug reports), Untyped array boundaries (AbstractConnection::$connectionParameters, ColumnInterface::getOptions()) cause mixed-* fallout downstream phpdb#169 (untyped getOptions()).
• From an internal DDL audit (not published — the option table above is the relevant part).