Epic: ship a pure-PHP MySQL-compatible canonical database engine

[chubes4]

Goal

Run WordPress and arbitrary plugins through a pure-PHP database engine that preserves MySQL-visible SQL and wpdb behavior while using MDI canonical files as authoritative storage. The implementation is database-agnostic and has no SQLite, MySQL, MariaDB, sidecar, wire-protocol, or compiled-runtime dependency.

Architecture constraints

• Implement the SQL lexer, parser, typed AST, planner, optimizer, and executor in PHP.
• Execute through WP_Markdown_Native_WPDB; the native engine does not require or emulate a real mysqli server handle.
• Keep SQL grammar, planning, and execution table-neutral. WordPress and plugin semantics enter through typed schemas and canonical storage providers.
• Use canonical options, Markdown content, JSON snapshots, and row partitions as native storage primitives.
• Implement MySQL-compatible coercion, collations, metadata, errors, insert IDs, affected rows, session state, and transaction behavior without linking behavior to a MySQL implementation.
• Expand compatibility from deterministic corpus and shadow evidence, not table-specific parser branches.

Workstreams

1. Replace the bounded regex recognizer with a pure-PHP tokenizer and typed MySQL AST.
2. Add generic expressions, aliases, joins, boolean predicates, ordering, grouping, aggregation, and limits.
3. Add a typed schema catalog and MySQL-compatible type/collation system.
4. Add canonical indexes and pushdown over options, Markdown posts, snapshots, and row partitions.
5. Add generic DML, auto-increment, constraints, and exact wpdb mutation state.
6. Add atomic multi-file transactions, savepoints, locking, journaling, and crash recovery.
7. Add dynamic DDL and plugin table persistence for dbDelta() and schema introspection.
8. Add a native db.php bootstrap path before every SQLite/PDO check.
9. Differentially verify WordPress install, front end, admin, REST, cron, WP-CLI, multisite, and representative plugins.

Acceptance criteria

• WordPress installs and boots with PHP alone and no database extension or external database process.
• Arbitrary plugin SQL issued through wpdb is parsed and executed by the generic PHP engine.
• Core and plugin DDL/DML/transactions preserve MySQL-visible rows, metadata, errors, insert IDs, affected rows, and session behavior.
• Canonical files remain authoritative and recover after process termination at every transaction boundary.
• Unsupported SQL fails through deterministic MySQL-compatible wpdb errors without fallback to SQLite or MySQL.
• The compatibility matrix has no unexplained differential failures for the accepted WordPress/plugin corpus.

References

• Refactor native query contracts #227
• Add bounded native provider access #229
• Add exact native partition reads #231

AI assistance

GPT-5.6 Sol via OpenCode helped inspect the existing PHP query and storage contracts and structure the corrected pure-PHP workstreams. Chris Huber explicitly required a pure-PHP, database-agnostic implementation and rejected the prior sidecar direction.