SQL Subsystem
SQL Support & Dialect Specification
MagnumDB implements a relational SQL query execution engine supporting DDL, DML, filtering, joins, aggregates, subqueries, CTEs, and window functions.
SQL Engine Reference
SQL Support Matrix
| SQL Command | Support Status | Execution Notes |
|---|---|---|
| CREATE TABLE | Supported | Supports PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT |
| INSERT INTO | Supported | Appends tuples into B+ Tree slotted leaf pages |
| SELECT | Supported | Filtering via WHERE clauses and binary expressions |
| UPDATE | Supported | Creates new tuple version with current xmin under MVCC |
| DELETE | Supported | Marks tuples as deleted by setting xmax transaction ID |
| DROP TABLE | Supported | Deallocates relation B+ Tree metadata and pages |
| ALTER TABLE | ADD COLUMN Supported | Appends new attribute to schema definition |
| JOIN | INNER / LEFT Supported | Evaluated by Volcano physical join operators |
| GROUP BY & HAVING | Supported | Aggregates tuple groups and filters calculated values |
| ORDER BY & LIMIT | Supported | Sorts result sets and applies offset boundaries |
| CTEs (WITH clause) | Supported | Evaluates named temporary CTE result streams |
| Window Functions | ROW_NUMBER Supported | Evaluates partition window frame functions |
| UNION / UNION ALL | Supported | Combines query result streams |
| FOREIGN KEY | Parsed, NOT Enforced | Syntax parses cleanly, but referential integrity is not enforced |
Foreign Key Constraint Disclaimer:
FOREIGN KEY syntax is parsed by the lexer for DDL script compatibility, but referential integrity constraints are NOT currently enforced by the query execution engine.