I have been looking into using SQLite as the ‘backend’ for Andl. It has:
- Most of ‘standard SQL’
- CREATE, ALTER, DROP TABLE
- TRANSACTIONS
- SELECT, INSERT, UPDATE, DELETE
- INDEXES, VIEWS, TRIGGERS
- Minimal types: null, integer, real, text, blob
- Parameters for prepared statements
- Minimal functions, including aggregates
- Minimal (open) expressions
- User-defined functions in C
It does not have:
- A ‘real’ type system, including relational types
- Stored code: statements, functions, etc
- Session variables
The results of a reasonably detailed examination of this 155,000 lines of dense C code:
- Hand-coded lexer
- LALR parser
- No real symbol table, just the schema, some fixed tables and the parse tree.
- Builds an AST-like tree structure called Parse.
- Progressively converts that into a VM opcode tree called Vdbe.
- The VM tree is manipulated directly by the query planner based on stats (if available) and execution time estimates.
- Relational operators (JOIN, etc) are implemented as nested loops in VM opcodes (there is no pipe).
- A ‘prepare’ operation returns a prepared statement which is simply the VM tree.
- An ‘execute’ operation binds variable values.
- A ‘step’ operation runs the VM program until a row is available.
[If you want to see the VM opcodes and the join algorithms, just use “EXPLAIN query”.]
My problem is that the Parse structure is tightly bound to the SQL language, and the Vdbe level sits after the query planner. There is no obvious layer at which it’s possible to create VM programs independent of SQL but still taking advantage of JOIN and other algorithms.
This does not look like a good prospect. I shall keep looking.