[RFC]: DDL column classes for ENUM, SET and the remaining MySQL column types

[simon-mundy]

Proposed Version

0.6.0

Basic Information

Currently Sql\Ddl\Column has 22 classes (the Laminas set plus Double, Json and SmallInteger from #138) and no way to express anything else. Column::$type is protected with no setter and no constructor argument (Column.php#L31, #L33-L38), so the only way to get an ENUM, SET, TINYINT, MEDIUMINT, BIT, YEAR, sized TEXT/BLOB or spatial column is to subclass. This proposal adds those classes, with ENUM/SET values emitted as Argument\Value so nothing caller-supplied reaches SQL as a literal, and fixes Text/Blob silently dropping their length.

Background

The read side is already ahead of the write side. PhpDb\Mysql\Metadata\Source parses enum(...)/set(...) into erratas['permitted_values'] (Source.php#L92-L103) and the enum path is integration-tested against the test_enum fixture (there is no SET fixture in mysql.sql). Nothing consumes permitted_values, and nothing can turn it back into DDL.

Text and Blob override $specification to %s %s (Text.php#L9-L11, Blob.php#L9-L12), so new Text('t', 100) renders TEXT and MySQL's TEXT(n) size selection is unreachable.

The phpdb-mysql-ddl-overrides branch on simon-mundy/phpdb-mysql already has Enum, Set, TinyInteger, MediumInteger, Bit, Year, the sized Text/Blob variants and all eight spatial types — in the adapter namespace PhpDb\Mysql\Sql\Ddl. It is 69 commits behind 0.5.x, pinned to a core branch that no longer exists, and predates the literal-slot rule (Enum quotes values with "'" . $v . "'" and no escaping). Useful for the design and the tests; not something to rebase.

Considerations

• Core or adapter — that is the real question. Core already hosts Json and the MySQL-flavoured ON UPDATE CURRENT_TIMESTAMP in AbstractTimestampColumn, so I have assumed core here with the adapter rendering only SRID. The fork branch went the other way.
• The sized Text/Blob variants must suppress the length entirely: MySQL rejects TINYTEXT(100), MEDIUMBLOB(100) and LONGTEXT(10) with 1064.
• Column::setOption() is typed bool|string, so until it is widened (Untyped array boundaries (AbstractConnection::$connectionParameters, ColumnInterface::getOptions()) cause mixed-* fallout downstream #169, also raised in phpdb-mysql#81) an int SRID goes through the constructor $options array or as a string.
• Clean rendering depends on the AbstractLengthColumn ValueError fix and the Integer length-position fix (AbstractLengthColumn subclasses throw ValueError when rendered without a length #176 and Integer length option renders the display width after NOT NULL #178).
• Every rendering below (YEAR NOT NULL, BIT(1), ENUM('a', 'b'), POINT SRID 4326) has been executed on MySQL 8.4.10 (reproduced on 8.0.46).
• MariaDB and decorator work beyond SRID are out of scope.

Proposal(s)

In this order:

1. Enum and Set. Constructor (string $name, array $values, bool $nullable = false, string|int|float|bool|Literal|Value|null $default = null, array $options = []). Spec %s %s(%s, %s, ...) with one Argument\Value per member, so every value is quoted by the platform. Empty $values throws InvalidArgumentException.
2. TinyInteger (TINYINT) and MediumInteger (MEDIUMINT) extending Integer, same as SmallInteger.
3. Bit (BIT(M)) extending AbstractLengthColumn; Year (YEAR, no width — YEAR(4) is deprecated from 8.0.19).
4. TinyText, MediumText, LongText extending Text; TinyBlob, MediumBlob, LongBlob extending Blob. Text/Blob themselves either honour the length (TEXT(n), BLOB(n)) or reject it — the silent drop goes. The sized variants never emit a length.
5. Spatial: Geometry, Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, GeometryCollection, with an srid option. SRID n is MySQL syntax (8.0.3+), so rendering it belongs in the MySQL decorator; core only carries the option.

Test plan:

• new Enum('status', ['active', 'inactive']) renders `status` ENUM('active', 'inactive') NOT NULL on the MySQL platform with each value going through quoteValue(); Set renders SET(...) the same way.
• Every new class gets a unit test in test/unit/Sql/Ddl/Column/ asserting the rendered SQL string, not just getExpressionData() containment.
• Text/Blob length handling is explicit and tested; the sized variants never emit a length.
• Round trip: the test_enum fixture read through the metadata Source (core AbstractSource::getColumn(), values via ColumnObject::getErrata('permitted_values')) can be re-expressed as an Enum with the same values — integration test in phpdb-mysql. Add a test_set fixture and do the same for Set.
• No new class emits a caller-supplied string as an Argument\Literal.

Appendix/Additional Info

• [RFC]: Sql\Ddl\Metadata Additions #92 (parent), [RFC]: SQL Metadata Backed Enums #91 (backed enums), Ddl improvements #138 (previous DDL additions).
• From an internal DDL audit (not published — the findings are reproduced above).