Advanced MySQL compatibility

[JanJakes]

This is a tracking issue for introducing a new MySQL query parser and an AST-based SQLite driver to significantly improve MySQL compatibility and expand WordPress plugin support.

I think the project can be represented in roughly the following phases:

1. Initial version of the MySQL parser — reasonably fast, small, and correct ("make it work").
2. Initial version of the SQLite driver — reach parity with the current test suite ("make it work").
3. Advanced MySQL support in the SQLite driver — see also test suites below ("make it right").
4. Advanced MySQL parser — add version support, verify against MySQL server grammar, fix edge cases ("make it right").
5. Advanced tooling — implement a custom parser for ANTLR or ENBF, add custom grammar tooling ("make it right").
6. Performance optimizations — explore possibilities to further optimize the new parser ("make it fast").

Here's a list of tasks and issues:

Core

• Exhaustive MySQL Parser
• AST-based SQLite driver #1 — match current MySQL compatibility
• SQLite driver — improve compatibility to satisfy multiple extensive test suites (see below)

Test suites

• SQL errors when activating most popular WP plugins: Playground Tester → SQL errors
• WP unit tests: Pass WordPress unit tests WordPress/wordpress-playground#111 (comment)
• Statically extracted queries from WP plugins: CI: Monitor support for all the SQL queries used by all the WordPress plugins WordPress/sqlite-database-integration#159
• Supporting all WP CLI commands: Some of the WP-CLI commands don't work WordPress/wordpress-playground#1618 (comment)

Advanced parser and tooling

• MySQL server version support in the new parser
• A generic grammar parser (ANTLR or [Data Liberation] EBNF processor WordPress/wordpress-playground#1981)

Supported MySQL constructs

CREATE TABLE

• CREATE TABLE t (id INT)
• CREATE TABLE t (id INT NOT NULL)
• CREATE TABLE t (id INT) DEFAULT 0
• CREATE TABLE t (...) ENGINE=InnoDB
• CREATE TABLE t (id INT) COLLATE utf8mb4_czech_ci
• CREATE TABLE t (id INT PRIMARY KEY)
• CREATE TABLE t (id INT PRIMARY KEY AUTOINCREMENT)
• CREATE TABLE t (id INT AUTOINCREMENT, PRIMARY KEY(id))
• CREATE TABLE t (a INT AUTOINCREMENT, b INT, c INT, PRIMARY KEY(a, b, c))
• CREATE TABLE t (id INT, UNIQUE(id))
• CREATE TABLE t LIKE tt
• CREATE TABLE t [AS] SELECT * FROM tt
• CREATE TABLE t (...) [AS] SELECT * FROM tt
• CREATE TABLE t (...) ENGINE=InnoDB [AS] SELECT * FROM tt
• CREATE TEMPORARY TABLE t (...)
• CREATE TEMPORARY TABLE t LIKE tt
• CREATE TABLE IF NOT EXISTS t LIKE tt
• CREATE TABLE IF NOT EXISTS t (...)
• CREATE TEMPORARY TABLE IF NOT EXISTS t (...)
• CREATE TEMPORARY TABLE IF NOT EXISTS t LIKE tt

ALTER TABLE

• ALTER TABLE t ADD [COLUMN] c INT
• ALTER TABLE t ADD [COLUMN] c1 INT, c2 TEXT, ...
• ALTER TABLE t ADD [COLUMN] c INT NOT NULL
• ALTER TABLE t ADD [COLUMN] c INT DEFAULT 0
• ALTER TABLE t ADD [COLUMN] c INT NOT NULL DEFAULT 0
• ALTER TABLE t ADD [COLUMN] c INT [PRIMARY] KEY
• ALTER TABLE t ADD [COLUMN] c INT UNIQUE [KEY]
• ALTER TABLE t ADD [COLUMN] c INT AUTO_INCREMENT
• ALTER TABLE t ADD [COLUMN] c INT COMMENT 'abc'
• ALTER TABLE t ADD [COLUMN] c TEXT COLLATE 'utf8mb4_0900_ai_ci'
• ALTER TABLE t ADD [COLUMN] c INT [GENERATED ALWAYS] AS (...)
• ALTER TABLE t ADD [COLUMN] c INT VIRTUAL
• ALTER TABLE t ADD [COLUMN] c INT STORED
• ALTER TABLE t ADD [COLUMN] c INT FIRST
• ALTER TABLE t ADD [COLUMN] c INT AFTER cc
• ALTER TABLE t ADD [COLUMN] c INT VISIBLE
• ALTER TABLE t ADD [COLUMN] c INT INVISIBLE
• ALTER TABLE t ADD [COLUMN] c INT COLUMN_FORMAT DEFAULT
• ALTER TABLE t ADD [COLUMN] c INT COLUMN_FORMAT FIXED
• ALTER TABLE t ADD [COLUMN] c INT COLUMN_FORMAT DYNAMIC
• ALTER TABLE t ADD [COLUMN] c INT ENGINE_ATTRIBUTE = '...'
• ALTER TABLE t ADD [COLUMN] c INT SECONDARY_ENGINE_ATTRIBUTE = '...'
• ALTER TABLE t ADD [COLUMN] c INT STORAGE DISK
• ALTER TABLE t ADD [COLUMN] c INT STORAGE MEMORY
• ALTER TABLE t ADD [COLUMN] c REFERENCES tt (id)
• ALTER TABLE t ADD [COLUMN] c REFERENCES tt (id) MATCH FULL
• ALTER TABLE t ADD [COLUMN] c REFERENCES tt (id) MATCH PARTIAL
• ALTER TABLE t ADD [COLUMN] c REFERENCES tt (id) MATCH SIMPLE
• ALTER TABLE t ADD [COLUMN] c REFERENCES tt (id) ON UPDATE ...
• ALTER TABLE t ADD [COLUMN] c REFERENCES tt (id) ON DELETE ...
• ALTER TABLE t ADD [COLUMN] c [CONSTRAINT chck] CHECK (...)
• ALTER TABLE t ADD [COLUMN] c [CONSTRAINT chck] CHECK (...) ENFORCED
• ALTER TABLE t ADD [COLUMN] c [CONSTRAINT chck] CHECK (...) NOT ENFORCED
• ALTER TABLE t DROP [COLUMN] c
• ALTER TABLE t DROP [COLUMN] c1, c2, ...
• ALTER TABLE t DROP INDEX idx
• ALTER TABLE t DROP KEY idx
• ALTER TABLE t DROP PRIMARY KEY
• ALTER TABLE t DROP FOREIGN KEY fk
• ALTER TABLE t DROP CHECK chck
• ALTER TABLE t DROP CONSTRAINT chck
• ALTER TABLE t ADD [COLUMN] c1 INT, DROP [COLUMN] c2

Supported MySQL INFORMATION_SCHEMA tables

INFORMATION_SCHEMA.TABLES

• TABLE_CATALOG
• TABLE_SCHEMA
• TABLE_NAME
• TABLE_TYPE
• ENGINE
• VERSION
• ROW_FORMAT
• TABLE_ROWS
• ĄVG_ROW_LENGTH
• DATA_LENGTH
• MAX_DATA_LENGTH
• INDEX_LENGTH
• DATA_FREE
• AUTO_INCREMENT
• CREATE_TIME
• UPDATE_TIME — should be easy to add
• CHECK_TIME
• TABLE_COLLATION
• CHECKSUM
• CREATE_OPTIONS
• TABLE_COMMENT

[JanJakes]

See: WordPress/sqlite-database-integration#162