AkurAI-Framework / site/content/docs/sql-executor.md

Latest change d971623579ac6cb425eea3ca33f24af5983c18b2 - feat(sqlexec): SQL executor over the storage B+tree (CREATE/INSERT/SELECT) by Ólafur Búi Ólafsson

# SQL Executor

The `akurai-sqlexec` crate is the back half of the query engine. Where
`akurai-sql` turns SQL text into an AST, the executor *runs* that AST over the
`akurai-storage` copy-on-write B+tree. It is pure `std` with zero external
dependencies, like every shipped crate.

```rust
use akurai_sqlexec::{Executor, Outcome};

let mut db = Executor::open("app.db").unwrap();
db.execute("CREATE TABLE users (id INTEGER NOT NULL, name TEXT, age INTEGER)").unwrap();
db.execute("INSERT INTO users (id, name, age) VALUES (1, 'ada', 36)").unwrap();

if let Outcome::Rows(rs) = db.execute(
    "SELECT name, age FROM users WHERE age >= 18 ORDER BY age DESC LIMIT 10"
).unwrap() {
    println!("{:?}", rs.columns); // ["name", "age"]
    println!("{:?}", rs.rows);
}
```

## Supported statements

The executor runs one statement per `execute` call and returns an `Outcome`:

| Statement | What it does | Outcome |
|-----------|--------------|---------|
| `CREATE TABLE t (col TYPE [NOT NULL], …)` | Registers a table schema. Rejects a duplicate table. | `Created(table)` |
| `INSERT INTO t (cols) VALUES (vals)` | Validates against the schema, materializes one typed row, writes it. | `Affected(1)` |
| `SELECT <*|cols> FROM t [WHERE …] [ORDER BY col [ASC|DESC]] [LIMIT n]` | Scans, filters, sorts, limits, and projects. | `Rows(ResultSet)` |

A `ResultSet` carries the projected `columns` (names, left to right) and `rows`
(each a `Vec<Value>`, one cell per projected column).

### WHERE

`WHERE` supports the comparison operators `=`, `!=` / `<>`, `<`, `<=`, `>`, `>=`
between a column and a literal (either order), combined with `AND`, `OR`, `NOT`,
and parentheses — `AND` binds tighter than `OR`. Comparisons use numeric
semantics across `INTEGER`/`REAL`, and lexical order for `TEXT`. Any comparison
that involves a `NULL` or two incomparable types evaluates to false rather than
erroring, so a single odd row never aborts a scan.

### ORDER BY and LIMIT

`ORDER BY <column> [ASC|DESC]` sorts the surviving rows; `NULL` sorts first.
`LIMIT n` then truncates to the first `n` rows.

## Row and key model

Each table owns three kinds of keys in the shared B+tree:

- **Catalog** — `\x00cat:<table>` holds the encoded schema (column names, types,
  `NOT NULL` flags). The `\x00` prefix is reserved; table names are SQL
  identifiers and never begin with it, so catalog and data keys can never
  collide.
- **Rowid counter** — `\x00seq:<table>` holds a big-endian `u64` of the last
  rowid handed out. It is seeded at `CREATE TABLE` and bumped per insert, so
  rowids keep climbing across reopens.
- **Data** — `<table>:<rowid_be>` holds one encoded row. The 8-byte big-endian
  rowid makes data keys sort in insertion order, and a `SELECT` scans the whole
  table with a single half-open `range` over the `<table>:` prefix.

A row is encoded as a self-describing sequence of cells: a length prefix, then
for each column a one-byte type tag (`NULL`/`INT`/`FLOAT`/`STR`) followed by its
payload. Integers and floats are fixed 8-byte big-endian; text is a length plus
UTF-8 bytes.

## Persistence

Every `CREATE TABLE` and `INSERT` ends with a storage-layer `commit()`, which
fsyncs the new pages and atomically swaps the B+tree root. Reopening the
database file therefore sees every committed table and row, and the rowid
sequence resumes where it left off.

## Limitations (this slice)

This is a deliberately tight slice — CREATE / INSERT / SELECT end to end. Not
yet implemented:

- **`UPDATE` and `DELETE` execution.** The parser may accept them, but the
  executor does not run them yet.
- **Joins, subqueries, aggregates, and `GROUP BY`.**
- **Indexes** (including HNSW / vector search) — every `SELECT` is a full table
  scan.
- **Transactions beyond the storage layer's per-statement `commit`** — there is
  no multi-statement `BEGIN`/`COMMIT`/`ROLLBACK`.