Module compatibility 

SQL Compatibility Matrix

This module defines SochDB’s SQL dialect support and compatibility layer.

Design Goals

1. Portable Core: SQL-92 compatible baseline that works across ecosystems
2. Dialect Sugar: Support common dialect variants (MySQL, PostgreSQL, SQLite)
3. Single AST: All dialects normalize to one canonical AST representation
4. Extensible: Add new dialects without forking parsers/executors

SQL Feature Matrix

Guaranteed (Core SQL)

Category	Statement	Status	Notes
DML	SELECT	✅	With WHERE, ORDER BY, LIMIT, OFFSET
DML	INSERT	✅	Single and multi-row
DML	UPDATE	✅	With WHERE clause
DML	DELETE	✅	With WHERE clause
DDL	CREATE TABLE	✅	With column types and constraints
DDL	DROP TABLE	✅	Basic form
DDL	ALTER TABLE	🔄	ADD/DROP COLUMN
DDL	CREATE INDEX	✅	Single and multi-column
DDL	DROP INDEX	✅	Basic form
Tx	BEGIN	✅	Start transaction
Tx	COMMIT	✅	Commit transaction
Tx	ROLLBACK	✅	Rollback transaction

Idempotent DDL

Statement	Status	Notes
CREATE TABLE IF NOT EXISTS	✅	No-op if exists
DROP TABLE IF EXISTS	✅	No-op if not exists
CREATE INDEX IF NOT EXISTS	✅	No-op if exists
DROP INDEX IF EXISTS	✅	No-op if not exists

Conflict/Upsert Family

All of these normalize to InsertStmt { on_conflict: Some(OnConflict { .. }) }

Dialect	Syntax	Canonical AST
PostgreSQL	ON CONFLICT DO NOTHING	OnConflict { action: DoNothing }
PostgreSQL	ON CONFLICT DO UPDATE SET ...	OnConflict { action: DoUpdate(...) }
MySQL	INSERT IGNORE	OnConflict { action: DoNothing }
MySQL	ON DUPLICATE KEY UPDATE	OnConflict { action: DoUpdate(...) }
SQLite	INSERT OR IGNORE	OnConflict { action: DoNothing }
SQLite	INSERT OR REPLACE	OnConflict { action: DoReplace }

Out of Scope (Explicit Limitations)

Feature	Status	Reason
Multi-table JOINs	❌	Complexity; single-table focus for v1
Subqueries in WHERE	❌	Planning complexity
Window functions	❌	Future enhancement
CTEs (WITH clause)	❌	Future enhancement
Stored procedures	❌	Out of scope

Dialect Detection

SochDB auto-detects dialect from syntax:

• INSERT IGNORE → MySQL mode
• INSERT OR IGNORE → SQLite mode
• ON CONFLICT → PostgreSQL mode

All normalize to the same internal representation.

Structs

CompatibilityMatrix

Compatibility matrix for different SQL dialects

Enums

FeatureSupport

SQL Feature support level

SqlDialect

SQL Dialect for parsing/normalization

SqlFeature

SQL Feature categories

Functions

get_feature_support

Get feature support level