[RFC]: DDL standalone index, view, rename and truncate statements, and typed table options

[simon-mundy]

Proposed Version

0.6.0

Basic Information

Currently Sql\Ddl has exactly three statements — CreateTable, AlterTable, DropTable. Everything else a schema tool needs is either unreachable or only expressible through an untyped KEY = value option map. This proposal adds CreateIndex/DropIndex, RenameTable, AlterTable::renameColumn()/renameIndex(), Truncate and CreateView/DropView, and replaces the option map with validated keys, keyword enums and partitionBy(). Six independent PRs against this issue.

Background

Statement	Today
CREATE INDEX / DROP INDEX (standalone)	only inside ALTER TABLE via addConstraint(new Index(...)) / dropIndex()
RENAME TABLE	nothing; AlterTable has no rename method
ALTER TABLE ... RENAME COLUMN old TO new	only changeColumn(), which renders CHANGE COLUMN old new <full definition> (AlterTable.php#L59-L63) and makes the caller restate the column
ALTER TABLE ... RENAME INDEX	nothing
TRUNCATE TABLE	nothing
CREATE VIEW / DROP VIEW	nothing, although Metadata reads views

CreateTable::setOption() / AlterTable::setOption() (added in #139, only on unreleased 0.6.x) render each entry as strtoupper($key) . ' = ' . $value (CreateTable.php#L207-L229, AlterTable.php#L281-L303). Verified on MySQL 8.4.10 (reproduced on 8.0.46):

-- works: strings quoted (ENGINE, DEFAULT CHARSET, COLLATE, COMMENT), int raw (AUTO_INCREMENT), Literal raw (ROW_FORMAT)
) ENGINE = 'InnoDB' DEFAULT CHARSET = 'utf8mb4' COLLATE = 'utf8mb4_unicode_ci' COMMENT = 'it\'s' AUTO_INCREMENT = 1000 ROW_FORMAT = DYNAMIC

-- setOption('auto_increment', '1000'): ERROR 1064 (only the int path works for AUTO_INCREMENT)
) AUTO_INCREMENT = '1000'

-- setOption('row_format', 'DYNAMIC'): ERROR 1064 (keyword required, string gets quoted); same for 'algorithm' / 'lock' on AlterTable
) ROW_FORMAT = 'DYNAMIC'

-- setOption('partition by', new Literal('HASH(id) PARTITIONS 4')): ERROR 1064 (' = ' always inserted)
) PARTITION BY = HASH(id) PARTITIONS 4

-- setOption("engine = InnoDB; DROP TABLE y; -- ", 'x'): key is emitted raw
) ENGINE = INNODB; DROP TABLE Y; --  = 'x'

So keyword-valued options need a Literal the caller has to know about, PARTITION BY has no supported form (a Literal smuggled into another option's value carries it — a hack, not an API), ALGORITHM/LOCK on AlterTable work only via Literal, and the option key is a raw literal slot.

The phpdb-mysql-ddl-overrides branch on simon-mundy/phpdb-mysql has CreateIndex, DropIndex, TruncateTable, MysqlAlterTable (modifyColumn, renameColumn, renameTable, partitions), MysqlCreateTable, MysqlTableOptionsTrait and Partition, all in the adapter namespace. No views, no RenameTable, no renameIndex. It is pinned to a core branch that no longer exists and predates the literal-slot invariant, so it is a source of design and tests rather than something to merge.

Considerations

• Core or adapter? This proposes the statements in core, with the MySQL adapter registering a decorator only where MySQL syntax differs. The fork branch put them in the adapter. Agree on placement first.
• Identifier quoting. Core's Sql92 output double-quotes identifiers, which MySQL only accepts under ANSI_QUOTES; backtick quoting comes from the adapter's AdapterPlatform, as for DropTable today. "No decorator needed" means the grammar is the same, not that core output runs on MySQL unchanged.
• Views. AbstractSql::processSubSelect() only clones a decorator when the enclosing statement is itself a decorator, so the adapter needs a pass-through CreateView decorator for the Select to be decorated on MySQL. MySQL forbids parameter markers inside a view definition (ERROR 1351), so CreateView inlines values and is getSqlString()/query()-only.
• Index reuse. Ddl\Index\Index hard-codes INDEX %s(...) in its spec, so sharing its rendering means extracting it or overriding the spec. No USING for FULLTEXT or SPATIAL (1064).
• Table options. setOption() only exists on unreleased 0.6.x, so there is no released BC surface. ALGORITHM/LOCK are ALTER TABLE-only (1064 in CREATE TABLE). PARTITION BY has to follow the table options.
• Integration tests. The adapter has no DDL integration tests today (tables and views come from the raw mysql.sql fixture), so whichever PR lands first adds the class.
• IF NOT EXISTS on CreateTable and IF EXISTS on DropTable already exist (Ddl improvements #138) and are valid MySQL; AlterTable correctly emits neither. MariaDB-only forms (CREATE OR REPLACE TABLE, IF [NOT] EXISTS inside ALTER TABLE) are out of scope, as are column-level attributes (phpdb-mysql#81).

Proposal(s)

• PR 1: CreateIndex / DropIndex. CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX name ON table (cols) [USING type] (no USING for FULLTEXT/SPATIAL) and DROP INDEX name ON table. Share column/length/type rendering with Ddl\Index\Index. No IF [NOT] EXISTS; MySQL has none for these.
• PR 2: RenameTable. RENAME TABLE a TO b [, c TO d]; string|TableIdentifier pairs.
• PR 3: AlterTable::renameColumn(string $old, string $new) and renameIndex(string $old, string $new). RENAME COLUMN old TO new and RENAME INDEX old TO new; MySQL 8.0 syntax and standard-shaped, no decorator needed. Both go in getRawState().
• PR 4: Truncate. TRUNCATE TABLE name.
• PR 5: CreateView / DropView. CREATE [OR REPLACE] VIEW name [(cols)] AS <Select> taking a Sql\Select, optional WITH [CASCADED|LOCAL] CHECK OPTION; DROP VIEW [IF EXISTS] name. Pass-through decorator in phpdb-mysql; values inlined.
• PR 6: typed table options. Validate keys against [A-Za-z_ ]+ (or a backed enum of known options) and never emit an unvalidated key; add RowFormat, Algorithm, Lock backed enums, widen the Literal|bool|int|string value union to take them, render them as keywords, and have CreateTable reject Algorithm/Lock; add CreateTable::partitionBy(Literal|string $clause) rendering PARTITION BY <clause> after the other options with no =. ENGINE, DEFAULT CHARSET, COLLATE, COMMENT keep working with quoted strings and AUTO_INCREMENT with an int.

Test plan (per PR):

• Each new Sql\Ddl\<Statement> extends AbstractSql, implements getRawState() as CreateTable/AlterTable do (DropTable has none), and has a unit test pinning the rendered SQL under Sql92 quoting, plus one under the MySQL platform in phpdb-mysql if a decorator is added.
• docs/book/sql-ddl/ gains a section per statement.
• PR 6: setOption('row_format', RowFormat::Dynamic) renders ROW_FORMAT = DYNAMIC; partitionBy(new Literal('HASH(id) PARTITIONS 4')) renders PARTITION BY HASH(id) PARTITIONS 4; an invalid key throws InvalidArgumentException; CreateTable rejects Algorithm/Lock; the existing option tests in CreateTableTest and AlterTableTest keep passing.
• Integration tests in phpdb-mysql execute each new statement against the CI MySQL image.

Appendix/Additional Info

• Sql\Platform: decorator contract is incomplete and Platform's per-platform registry is unused #175 (decorator contract), Untyped array boundaries (AbstractConnection::$connectionParameters, ColumnInterface::getOptions()) cause mixed-* fallout downstream #169 (untyped option arrays), Ddl improvements table #139 (where table options were added), Ddl improvements #138 (IF [NOT] EXISTS on Create/DropTable).
• From an internal DDL audit (not published — the findings are reproduced above).