The current query translation approach is rather minimalist, handling some differences between MySQL and SQLite, while leaving other/unknown syntax to fall to default translation handling and relying on the SQL dialect similarities.
For better confidence and data safety, it would be better to implement a more "allowlist"-oriented driver that would be explicit about what syntaxes are supported, leaving the rest as "unsupported". This is needed especially for data-writing queries, less so for read-only queries, in the following order of importance:
- DDL queries — here it's very important to allow only what we support to keep the information schema consistent.
- Data-writing queries — it can be important in some cases where an incorrectly understood query would result in a wrong operation.
- Read-only queries — here, allowlist can be a convenience (bail out early) rather than a necessity.
The full MySQL grammar contains many nodes, variations, and nesting, and it's hard to handle every detail using an allowlist — for instance, a specific construct may be generally valid in an expression, but not in an expression in a subquery in a join, etc. That said, I think we can be very explicit at the higher level — explicitly stating which top-level statements we do support and which ones we don't, and which main subconstructs of these statements are supported or not (e.g., LIMIT clause in a DELETE query).
See: Automattic/sqlite-database-integration#1 (comment)
The current query translation approach is rather minimalist, handling some differences between MySQL and SQLite, while leaving other/unknown syntax to fall to default translation handling and relying on the SQL dialect similarities.
For better confidence and data safety, it would be better to implement a more "allowlist"-oriented driver that would be explicit about what syntaxes are supported, leaving the rest as "unsupported". This is needed especially for data-writing queries, less so for read-only queries, in the following order of importance:
The full MySQL grammar contains many nodes, variations, and nesting, and it's hard to handle every detail using an allowlist — for instance, a specific construct may be generally valid in an expression, but not in an expression in a subquery in a join, etc. That said, I think we can be very explicit at the higher level — explicitly stating which top-level statements we do support and which ones we don't, and which main subconstructs of these statements are supported or not (e.g., LIMIT clause in a DELETE query).
See: Automattic/sqlite-database-integration#1 (comment)