engine

Overview ¶

Audit logging hook for Execute (see internal/storage/audit.go for the hash-chained log itself).

Execute receives a parsed Statement, not the original SQL text — there's no reliable way to reconstruct exact SQL from an AST (the trigger-body bug fixed earlier this session, where a from-scratch AST-to-SQL printer silently produced non-executable text, is exactly why this doesn't attempt that). Callers that already have the original text (cmd/server, internal/driver — both parse from a string themselves) can attach it via WithAuditText so the audit trail records the real query; callers that only ever construct/receive an AST get a best-effort "<*engine.Insert>" style fallback identifying the statement kind, which is still enough to see *that* a mutation happened even without the exact text.

Package engine provides SQL parsing, planning, and execution for tinySQL.

This file focuses on the query compilation cache:

• What: A lightweight in-memory LRU cache that stores parsed/compiled representations of SQL statements (CompiledQuery).
• How: Queries are keyed by their exact SQL string. The cache holds a Statement AST plus metadata (ParsedAt) and returns it to callers to avoid re-parsing. LRU eviction using container/list keeps the cache within a fixed size with O(1) eviction.
• Why: Parsing is comparatively expensive and often repeated in loops or hot paths. Caching reduces parse overhead, improves latency, and keeps the execution path predictable while remaining simple and thread-safe.

Package engine implements the tinySQL execution engine.

What: This module evaluates parsed SQL statements (AST) against the storage layer and produces ResultSets. It covers DDL/DML/SELECT, joins, grouping, aggregates, expression evaluation, simple functions (JSON_*, DATEDIFF, etc.), and a minimal tri-state boolean logic (true/false/unknown).

How: The executor converts tables to row maps with both qualified and unqualified column keys, applies WHERE/GROUP/HAVING/ORDER/LIMIT/OFFSET, and optionally combines results with UNION/EXCEPT/INTERSECT. Expression evaluation is recursive over a small algebra of literals, variables, unary/ binary ops, IS NULL, and function calls. Aggregate evaluation runs per- group with reusable helpers shared with the scalar evaluator.

Why: Keeping execution self-contained and data-structure driven (Row maps and simple slices) makes the engine easy to reason about and to extend with new functions, operators, and clauses without introducing heavy planners.

Package engine provides helper functions for the YAML, URL, HASH, BITMAP, and BLOB extra column types added to tinySQL.

Note: MD5 and SHA1 are provided for compatibility and checksum use cases only. They are cryptographically broken and must NOT be used for security-sensitive operations (passwords, signatures, etc.). Prefer SHA256 or SHA512 instead.

Package engine: foreign key referential action enforcement.

Before this, FOREIGN KEY constraints only protected the child side: an INSERT/UPDATE into a table with a FOREIGN KEY column checked that the referenced row existed (validateRowConstraints in exec.go), but nothing checked the parent side — DELETE (or UPDATE of a referenced column) on the parent table silently orphaned any referencing child rows, regardless of an ON DELETE/ON UPDATE clause (which didn't even parse). This file adds that missing half: RESTRICT (the default when no clause is given, matching how tinySQL checks constraints immediately rather than deferring them), CASCADE, and SET NULL.

Package engine provides a lightweight Full-Text Search (FTS) engine inspired by SQLite FTS5.

Design:

• CREATE VIRTUAL TABLE t USING fts(col1, col2) creates a regular tinySQL table with FTS indexing enabled.
• INSERT/UPDATE/DELETE automatically maintain an in-memory inverted index.
• FTS_MATCH(text, query) – boolean match check with phrase/boolean query support
• FTS_RANK(text, query) – BM25-like relevance score
• FTS_SNIPPET(text, query [, before, after, ellipsis, max_tokens]) – highlighted snippet
• FTS_HIGHLIGHT(text, query [, before, after]) – full-text highlighting alias
• BM25(text, query) – alias for FTS_RANK
• FTS_SEARCH table-valued function for corpus-level k-nearest search

Query syntax supported by FTS_MATCH / FTS_RANK:

word         – single term
"phrase"     – exact phrase (consecutive tokens)
word*        – prefix wildcard
A AND B      – both terms must match
A OR B       – either term must match
NOT A        – term must not match
A B          – implicit AND (same as A AND B)

Package engine contains the SQL lexer used by the parser.

What: A minimal, whitespace- and comment-aware tokenizer that recognizes identifiers, keywords, numeric and string literals, and symbols. How: Single-pass rune-based scanner supporting -- and /* */ comments, uppercasing keywords, and preserving identifier case. Keywords are a fixed allow-list tailored to tinySQL features. Why: A compact lexer reduces parser complexity and keeps error messages local and actionable without external dependencies.

Package engine provides a hand-written SQL parser for tinySQL.

What: It parses a practical subset of SQL into an AST (statements and expressions) used by the execution engine. Supported features include DDL, DML, SELECT with JOIN/GROUP/HAVING/ORDER/LIMIT/OFFSET, and set ops. How: A straightforward recursive-descent parser over a small token stream from the lexer. It favors clarity and precise error messages. Ident-like parsing accepts keywords as identifiers to keep the grammar practical for common column names. Why: A small, readable parser is easy to extend and reason about, enabling rapid iteration on language features without a complex generator toolchain.

RBAC enforcement for Execute (see internal/storage/rbac.go for the underlying users/roles/grants model).

The acting user travels via context.Context (WithUser/UserFromContext), not as an Execute parameter — adding a parameter would break every existing caller's signature (internal/driver, cmd/server, every test in this module). A context key is the idiomatic Go way to thread an optional, cross-cutting value like this without an API break, and it composes with context.WithTimeout/WithCancel that callers already use.

Enforcement itself is opt-in at the database level: checkPermission is a no-op unless db.IsRBACEnabled() is true (which itself is false until the first CreateUser call), so every existing embedder and test that never creates a user sees zero behavior change. Call db.SetRBACEnabled(false) to force enforcement off even after users/roles have been created — for a setup that provisions accounts ahead of time but isn't ready to enforce yet, or a dev/test environment that wants audit-log attribution via WithUser without access checks getting in the way.

Parser and AST for RBAC DDL: CREATE/DROP/ALTER USER, CREATE/DROP ROLE, and GRANT/REVOKE. Enforcement lives in rbac.go; the underlying users/roles/grants model is internal/storage/rbac.go.

Grammar (see parser_test-style examples in rbac_parser_test.go):

CREATE USER name WITH PASSWORD 'secret' [ROLE role1 [, role2 ...]]
DROP USER name
ALTER USER name ENABLE | DISABLE
ALTER USER name WITH PASSWORD 'newsecret'
CREATE ROLE name
DROP ROLE name
GRANT perm [, perm ...] ON [schema.]table TO ROLE role
GRANT perm [, perm ...] ON * TO ROLE role
REVOKE perm [, perm ...] ON [schema.]table FROM ROLE role
GRANT ROLE role TO USER user
REVOKE ROLE role FROM USER user

Package engine provides plaintext/fulltext utility functions and table-valued functions for RAG (Retrieval-Augmented Generation) workloads.

TEXT_CHUNKS(text, chunk_size [, overlap [, unit]])

Splits text into overlapping chunks suitable for embedding and retrieval.

Parameters:
  text       – the source document text
  chunk_size – maximum tokens (words) per chunk (default 100)
  overlap    – number of tokens shared between adjacent chunks (default 20)
  unit       – 'words' (default) or 'chars'

Returns columns: chunk_index INT, chunk_text TEXT, start_pos INT, end_pos INT

TEXT_WORD_COUNT(text) – count of whitespace-separated words TEXT_CHAR_COUNT(text) – count of Unicode characters TEXT_TRUNCATE(text, max_len [, ellipsis]) – truncate to max_len chars

Package engine implements trigger execution for tinySQL. Triggers fire BEFORE or AFTER INSERT, UPDATE, and DELETE operations. NEW refers to the new row (INSERT/UPDATE), OLD refers to the old row (UPDATE/DELETE).

Package engine provides vector/embedding functions for tinySQL, enabling RAG (Retrieval-Augmented Generation) workloads and vector similarity search.

Supported functions:

VEC_FROM_JSON(json_string)           – parse "[1.0, 2.0, 3.0]" → []float64
VEC_TO_JSON(vector)                  – serialize vector → JSON string
RECENCY_SCORE(ts, half_life_days [, now]) – exponential decay score in [0, 1]
RAG_HYBRID_SCORE(similarity, ts, half_life_days [, sim_weight, now]) – blend of
                                                                 normalized similarity and recency
RAG_RANK_SCORE(similarity, ts, half_life_days, quality [, sim_w, recency_w, quality_w, now])
                                      – weighted RAG score over similarity, freshness and quality
VEC_DIM(vector)                      – number of dimensions
VEC_NORM(vector)                     – L2 (Euclidean) norm
VEC_NORMALIZE(vector)                – unit-length normalised copy
VEC_ADD(v1, v2)                      – element-wise addition
VEC_SUB(v1, v2)                      – element-wise subtraction
VEC_MUL(v1, v2)                      – element-wise multiplication (Hadamard)
VEC_SCALE(vector, scalar)            – scalar multiplication
VEC_DOT(v1, v2)                      – dot / inner product
VEC_COSINE_SIMILARITY(v1, v2)        – cosine similarity  ∈ [-1, 1]
VEC_COSINE_DISTANCE(v1, v2)          – 1 - cosine similarity ∈ [0, 2]
VEC_L2_DISTANCE(v1, v2)              – Euclidean distance
VEC_MANHATTAN_DISTANCE(v1, v2)       – L1 / Manhattan distance
VEC_DISTANCE(v1, v2 [, metric])      – generic distance, metric = 'cosine' | 'l2' | 'manhattan' | 'dot'
VEC_SLICE(vector, start, length)     – sub-vector extraction
VEC_CONCAT(v1, v2)                   – vector concatenation
VEC_QUANTIZE(vector, bits)           – quantize to reduced precision (8/16 bit simulation)
VEC_RANDOM(dimensions [, seed])      – random unit vector of given dimension

Package engine provides a VEC_SEARCH table-valued function for k-nearest neighbor (k-NN) vector search — the core building block for Retrieval-Augmented Generation (RAG) workloads in tinySQL.

Usage:

SELECT * FROM VEC_SEARCH('table_name', 'vector_column', query_vector, k [, 'metric' [, 'index']])

Parameters:

table_name     – name of the table containing vectors
vector_column  – column storing VECTOR ([]float64) values
query_vector   – the search vector ([]float64 or JSON string)
k              – number of nearest neighbors to return
metric         – optional distance metric: 'cosine' (default), 'l2', 'manhattan', 'dot'
index          – optional index mode: 'flat' (default exact), 'ivf', 'hnsw'

Returns all original columns plus:

_vec_distance  – computed distance from query_vector
_vec_rank      – 1-based rank (1 = closest)

The results are returned in ascending order of distance (closest first).

VEC_WARM — eager warm-up for vector search structures.

Usage:

SELECT * FROM VEC_WARM('table_name', 'vector_column' [, 'metric' [, 'index']])

Prebuilds the vector column cache (and, for cosine, the L2 norms) plus the requested ANN index (ivf or hnsw). This shifts the one-time O(n log n) index-build cost from the first query to an explicit warm-up step — e.g. right after a nightly bulk load — so serving queries never pay it.

Returns a single row describing what was warmed:

table_name, column_name, metric, index_mode, row_count, vector_count, dims

Automatic AdvancedWAL logging for INSERT/UPDATE/DELETE.

Before this, tinySQL had two WAL implementations — the basic WALManager (whole-table snapshot diffing, used by internal/driver's explicit BeginTx/Commit) and AdvancedWAL (row-level, LSN-based, with real REDO/recovery) — but nothing in the engine's own INSERT/UPDATE/DELETE path ever called AdvancedWAL's LogInsert/LogUpdate/LogDelete. A caller using ModeAdvancedWAL and writing through the ordinary tinysql.Execute API (not through internal/driver's transaction machinery) got no durability logging at all — a silent gap, since nothing errored or warned about it.

walAuto wraps each mutating statement in its own implicit, autocommitted WAL transaction (LogBegin before the row loop, LogCommit after) and logs every row change as it happens. It is a complete no-op — zero calls, zero overhead beyond one nil check — when no AdvancedWAL is attached, which remains the default for ModeMemory/ModeDisk/ModeJSON/ModeHybrid/ModeWAL.