WordPress / WordPress/sqlite-database-integration

Advanced MySQL compatibility

Open
#162 2 comments 1 reaction 1 assignee View on GitHub

@JanJakes is already working on this.

Since Nov 4, 2024.

Dominant language
PHP
Stars
363
Forks
67
Avg merge
2d 15h
Merged PRs (30d)
7

Description

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
Test suites
Advanced parser and tooling
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
Related issues

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

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.