sqlite-database-integration Advanced MySQL compatibility

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:

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

Here's a list of tasks and issues:

Core

[x] Exhaustive MySQL Parser
[x] https://github.com/Automattic/sqlite-database-integration/pull/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: https://github.com/WordPress/wordpress-playground/issues/111#issuecomment-2434885741
[ ] Statically extracted queries from WP plugins: https://github.com/WordPress/sqlite-database-integration/issues/159
[ ] Supporting all WP CLI commands: https://github.com/WordPress/wordpress-playground/issues/1618#issuecomment-2380150978

Advanced parser and tooling

[ ] MySQL server version support in the new parser
[ ] A generic grammar parser (ANTLR or https://github.com/WordPress/wordpress-playground/issues/1981)

Supported MySQL constructs

CREATE TABLE

[x] CREATE TABLE t (id INT)
[x] CREATE TABLE t (id INT NOT NULL)
[x] CREATE TABLE t (id INT) DEFAULT 0
[x] CREATE TABLE t (...) ENGINE=InnoDB
[x] CREATE TABLE t (id INT) COLLATE utf8mb4_czech_ci
[x] CREATE TABLE t (id INT PRIMARY KEY)
[x] CREATE TABLE t (id INT PRIMARY KEY AUTOINCREMENT)
[x] CREATE TABLE t (id INT AUTOINCREMENT, PRIMARY KEY(id))
[ ] CREATE TABLE t (a INT AUTOINCREMENT, b INT, c INT, PRIMARY KEY(a, b, c))
[x] 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
[x] 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

[x] ALTER TABLE t ADD [COLUMN] c INT
[x] ALTER TABLE t ADD [COLUMN] c1 INT, c2 TEXT, ...
[x] ALTER TABLE t ADD [COLUMN] c INT NOT NULL
[x] ALTER TABLE t ADD [COLUMN] c INT DEFAULT 0
[x] 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
[x] ALTER TABLE t DROP [COLUMN] c
[x] ALTER TABLE t DROP [COLUMN] c1, c2, ...
[ ] ALTER TABLE t DROP INDEX idx
[ ] ALTER TABLE t DROP KEY idx
[x] ALTER TABLE t DROP PRIMARY KEY
[x] ALTER TABLE t DROP FOREIGN KEY fk
[ ] ALTER TABLE t DROP CHECK chck
[x] ALTER TABLE t DROP CONSTRAINT chck
[x] ALTER TABLE t ADD [COLUMN] c1 INT, DROP [COLUMN] c2

Supported MySQL `INFORMATION_SCHEMA` tables

INFORMATION_SCHEMA.TABLES

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

Related issues

Here's a list of issues that are likely related. This may need further review and triage:

[ ] https://github.com/WordPress/sqlite-database-integration/issues/146
[ ] https://github.com/WordPress/sqlite-database-integration/issues/144
[ ] https://github.com/WordPress/sqlite-database-integration/issues/140
[ ] https://github.com/WordPress/sqlite-database-integration/issues/125
[ ] https://github.com/WordPress/sqlite-database-integration/issues/119
[ ] https://github.com/WordPress/sqlite-database-integration/issues/116
[ ] https://github.com/WordPress/sqlite-database-integration/issues/115
[ ] https://github.com/WordPress/sqlite-database-integration/issues/114
[ ] https://github.com/WordPress/sqlite-database-integration/issues/107
[ ] https://github.com/WordPress/sqlite-database-integration/issues/106
[ ] https://github.com/WordPress/sqlite-database-integration/issues/100
[ ] https://github.com/WordPress/sqlite-database-integration/issues/97
[ ] https://github.com/WordPress/sqlite-database-integration/issues/90
[ ] https://github.com/WordPress/sqlite-database-integration/issues/80
[ ] https://github.com/WordPress/sqlite-database-integration/issues/79
[ ] https://github.com/WordPress/sqlite-database-integration/issues/78
[ ] https://github.com/WordPress/sqlite-database-integration/issues/74
[ ] https://github.com/WordPress/sqlite-database-integration/issues/70
[ ] https://github.com/WordPress/sqlite-database-integration/issues/59
[ ] https://github.com/WordPress/sqlite-database-integration/issues/58
[ ] https://github.com/WordPress/sqlite-database-integration/issues/53 ?
[ ] https://github.com/WordPress/sqlite-database-integration/issues/52
[ ] https://github.com/WordPress/sqlite-database-integration/issues/47
[ ] https://github.com/WordPress/sqlite-database-integration/issues/39
[ ] https://github.com/WordPress/sqlite-database-integration/issues/35
[ ] https://github.com/WordPress/sqlite-database-integration/issues/34
[ ] https://github.com/WordPress/sqlite-database-integration/issues/31
[ ] https://github.com/WordPress/sqlite-database-integration/issues/26
[ ] https://github.com/WordPress/sqlite-database-integration/issues/19

Nov 04 '24 10:11 JanJakes

I don't see this in your list, but while running the plugin I'm developing in Playground, I'm getting a lot of syntax errors due to keywords in the queries, which SQLite doesn't tolerate.

For example

SELECT v.name, g.title AS grouptitle, g.id, g.mode FROM wp_participants_database_fields v INNER JOIN wp_participants_database_groups g ON v.group = g.name ORDER BY v.id

The keyword "group" needs to be escaped for SQLite, while MariaDb is fine with it.

SELECT v.name, g.title AS grouptitle, g.id, g.mode FROM wp_participants_database_fields v INNER JOIN wp_participants_database_groups g ON v.`group` = g.name ORDER BY v.id

Nov 09 '24 02:11 xnau

@xnau Thanks for the report! Indeed, some keywords that are non-reserved in MySQL can be reserved in SQLite. We can address this on a per-keyword basis, or by quoting all identifiers.

Nov 13 '24 16:11 JanJakes

Advanced MySQL compatibility

Core

Test suites

Advanced parser and tooling

Supported MySQL constructs

Supported MySQL INFORMATION_SCHEMA tables

Related issues

Supported MySQL `INFORMATION_SCHEMA` tables