engine

Overview ¶

Integer-preserving arithmetic shared by every evaluation path.

tinySQL follows SQLite's rules: +, -, * and % on two integers yield an integer, and +, - and * fall back to REAL (float64) only when the exact result does not fit into 64 bits. A REAL operand makes the result REAL. Division deliberately keeps tinySQL's REAL quotient (7 / 2 = 3.5) instead of SQLite's truncating integer division; see docs/sql-feature-gaps.md.

The abstract syntax tree: one type per statement and per expression form. The parser builds these, the executor switches on them, and nothing else needs to know how SQL text is spelled.

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.

Date and time functions, and the parsers behind them. Formats are accepted explicitly rather than guessed, so an unparseable value is an error instead of a silently wrong date.

Hash, encoding and identifier functions: MD5, SHA-1/256/512, Base64, hex, UUID and Soundex.

Numeric functions. The exact-decimal cases stay on big.Rat; the rest work in float64.

The scalar function registry: the name-to-handler table the evaluator looks up, and the thin adapters that give each function a uniform signature. The implementations live in the builtin_*.go files.

String, JSON and conditional scalar functions: case conversion, trimming, padding, substring and search, formatting, CAST, COALESCE/NULLIF/IF, and the JSON accessors.

Type inference and coercion. Every value entering a column passes through here, which is where SQLite's type affinities are applied.

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.

Parse-time constant folding for pure vector functions.

RAG queries almost always embed the query vector as a JSON literal:

SELECT ... FROM docs
ORDER BY VEC_COSINE_SIMILARITY(embedding, VEC_FROM_JSON('[0.1, ...]')) DESC

Without folding, VEC_FROM_JSON re-parses that literal for every row the expression is evaluated against — profiling showed ~85% of such a query's CPU time inside encoding/json. Folding the call to a Literal([]float64) once at parse time removes that per-row cost entirely, for every evaluation path (regular, raw fast path, WHERE, ORDER BY, table-function arguments).

Coordinate reference system identifiers and axis order helpers.

The functions in this file intentionally understand identifiers, not projections. A CRS can be named by an EPSG label, an OGC URN, or an OGC definition URI without tinySQL acquiring a dependency on a particular map service. Reprojection remains the separate responsibility of ST_TRANSFORM (or an external PROJ pipeline).

Aggregate evaluation over a group's rows: COUNT, SUM, AVG, MIN, MAX, MEDIAN, MIN_BY/MAX_BY and vector averaging, plus recognising which expressions are aggregates at all.

The general expression evaluator: recursive evaluation over literals, column references, unary and binary operators, IS NULL, BETWEEN, IN, LIKE, GLOB, SIMILAR TO, CASE, EXISTS and subqueries, against a row map.

Caching for WHERE/SELECT-list subqueries: EXISTS (...), scalar (SELECT ...) expressions, and the IN (SELECT ...) form (which reaches evalSubqueryExpr through evalExpr's generic *SubqueryExpr dispatch inside evalIn's value loop -- see eval_expr.go). Without this cache, evalExistsExpr and evalSubqueryExpr call executeSelect on the subquery's SELECT every single time they are evaluated, which for a subquery inside a WHERE clause means once per outer row (applyWhereClause in exec_join.go). Most such subqueries do not depend on the outer row at all -- e.g. "WHERE x IN (SELECT y FROM small_table)" -- so re-running them per row is pure waste.

The unsafe case is a genuinely correlated subquery, e.g. "WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.parent_id = outer.id)": caching its first result and reusing it for every subsequent outer row would silently produce wrong results. isSelectCorrelated below is the conservative, lexical safety check that keeps that from happening; see its doc comment for exactly what it checks and why unqualified references are not part of that check.

Window functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE, moving aggregates, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST and NTILE, plus the partitioning and frame arithmetic they share.

Choropleth classification window functions: EQUAL_INTERVAL and NATURAL_BREAKS (Jenks). Quantile classification needs no new code here -- NTILE(n) OVER (ORDER BY kpi) (eval_window.go) already buckets a column into n roughly-equal-sized groups, exactly mapshaper's -classify "quantile" method.

NATURAL_BREAKS is the one window function in this file that cannot be O(1) per row the way every other window function here is: computing Jenks' variance-minimizing class boundaries needs a single O(len*n^2) dynamic-programming pass over the whole partition, and evalWindowFunction is invoked once per output row. Recomputing that DP on every row would turn an O(len*n^2) cost into O(len^2*n^2) for the query as a whole, which is not acceptable even at moderate partition sizes -- so its result is memoized per (window-function AST node, partition, n) in natBreaksCache, bounded and evicted the same way every other cache in this codebase is.

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.

Common table expressions, including recursive ones. Recursion is bounded by a row cap so a CTE without a terminating condition fails instead of running until memory is gone.

ALTER TABLE.

Views, materialized views and the jobs that refresh them: CREATE, DROP and ALTER for each, the cache table a materialized view is backed by, and the scheduled-refresh registration that keeps it fresh.

DELETE, plus the helpers shared by all three DML statements: building a row map for expression evaluation, marking dependent materialized views stale, and projecting a RETURNING clause.

INSERT, the column defaults it applies, and the per-row constraint checks every mutating statement shares. The constraint-index cache lives here too: it is the lookup structure that makes PRIMARY KEY and UNIQUE enforcement something other than a full scan per row.

UPDATE, including the fast path for a triggerless update whose predicate and assignments can be evaluated straight off the stored row.

Fast path for grouped aggregation: accumulate COUNT/SUM/AVG/MIN/MAX directly off stored rows in a single scan, instead of buffering each group's rows and re-scanning them once per aggregate expression.

This also covers the ungrouped case (no GROUP BY at all, e.g. "SELECT COUNT(*) FROM t" or "SELECT SUM(x), AVG(y) FROM t WHERE ..."), which used to be excluded here and fall all the way back to the general path: resolveFromClause materializes a dual-key Row map for every row of the table via rowsFromTable before aggregation even begins, for a query shape that is arguably the most common one in SQL. The accumulator machinery below already treats "zero group-by columns" as one implicit group (see executeSimpleMultiGroupAggregate), so the only genuinely new piece is synthesizing that one group's output row when no input row matched — a whole-table aggregate must still return exactly one row over zero matching rows (COUNT(*) = 0, SUM/AVG/MIN/MAX = NULL), whereas a real "GROUP BY x" correctly returns zero rows in that case. See the identical contract and its rationale in processAggregateQuery (exec_group.go).

Fast path for a two-table equi-join: compile the join keys, filters and projection once, then evaluate them against stored rows without building a row map per input row.

Fast path for the single most common join+aggregate shape: a two-table equi-join (one "ON left.col = right.col" condition, no other join predicates) combined with a single-column GROUP BY whose key is a column from either input table. Without this, that shape falls through the plain join fast path (exec_fastpath_join.go excludes any GroupBy) and the plain aggregate fast path (exec_fastpath_aggregate.go excludes any Joins), so it always takes the generic Row-map join (exec_join.go) followed by processAggregateQuery (exec_group.go), which buffers every joined row per group and re-scans each group's buffer once per aggregate expression.

This file adds a new, separate, narrowly-scoped fast path rather than loosening either existing eligibility check: it reuses the join mechanics (simpleJoinPlan, evalJoinRawExpr et al.) from exec_fastpath_join.go and the per-group accumulator (simpleAggregateState, accumulate/finalize helpers) from exec_fastpath_aggregate.go, mirroring executeSimpleAggregateFastPath's one-column strategy of using the grouped value itself as a map key. Any query shape outside this narrow case -- multi-column GROUP BY, non-equi joins, multiple join conditions, HAVING, more than two tables, ORDER BY, LIMIT/OFFSET, DISTINCT -- is rejected by simpleJoinAggregateEligibleSelect and falls through unchanged to the two existing fast paths (which already reject it) and ultimately the generic path.

Fast path for a single-table SELECT: an unfiltered scan, a filtered scan, and an ordered scan that degenerates to a bounded top-N heap when LIMIT is present. The float-keyed variant keeps the sort key unboxed.

Resolving a FROM clause to rows: physical tables, subqueries, CTE results, table-valued functions, views, materialized views and the sys.* catalog views.

GROUP BY, HAVING and PIVOT, and the projection of both aggregate and non-aggregate queries, followed by OFFSET/LIMIT.

Joins over row maps: inner, left, right and full outer, plus the WHERE filter applied to their output. This is the general path; exec_fastpath_join.go handles the shape it can compile.

Compiling a SELECT into a plan, and caching that plan across executions of the same statement text. Also index selection: which secondary or constraint index a predicate can seek, and whether seeking it beats scanning.

Evaluating expressions directly against a stored row, for the fast paths. The generic evaluator in eval_expr.go handles the same algebra over row maps.

Compiling a WHERE clause into a closure over stored rows.

Each builder returns a filter specialised for the shapes it recognises — column against literal, integer comparison, compiled LIKE, IN over a value set — and falls back to the generic expression evaluator for anything it does not. A nil filter means "not compilable", which disqualifies the fast path rather than producing a wrong answer.

Projections and column addressing for the raw-row fast paths, and the test for whether an expression can be evaluated against a stored row at all (which is what makes a query eligible for those paths).

SELECT: the statement entry point, and the plan types the fast paths below compile a query into.

UNION, UNION ALL, EXCEPT and INTERSECT.

ORDER BY. Sorting either sorts the whole result or, when a LIMIT bounds it, keeps only the rows that can still make the cut in a bounded heap. The heap implementations are written against their concrete element type rather than container/heap, whose any-typed interface costs one allocation per row.

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.
• FTS_SEARCH builds a tokenized-document cache and an inverted index (term postings) lazily per searched column set, invalidated by table.Version — append and update deltas are applied lazily; structural changes rebuild.
• 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
• CONTAINS_ALL(text, term1, term2, ...) – true iff every term is a substring of text
• CONTAINS_ANY(text, term1, term2, ...) – true iff any term is a substring of text
• CONTAINS_SCORE(text, term1, term2, ...) – count of the given terms found in text

Query syntax supported by FTS_MATCH / FTS_RANK:

word         – single term
"phrase"     – exact phrase (consecutive tokens)
word*        – wildcard term (* / % = any sequence, ? / _ = one character)
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)

FTS_MATCH / FTS_RANK vs. CONTAINS_ALL / CONTAINS_ANY / CONTAINS_SCORE: FTS_MATCH and FTS_RANK tokenize, stem, and strip stop words from both the text and the query, and support a rich boolean query language (phrases, prefix wildcards, NOT, nested AND/OR) — they're the right choice for natural-language search boxes. CONTAINS_ALL, CONTAINS_ANY, and CONTAINS_SCORE instead do a raw case-insensitive substring match against a fixed list of literal terms, with no tokenizing, stemming, or query syntax — they're the right choice for exact codes/IDs/numbers (which stemming would mangle), or for a simple "must contain all/any of these words" filter without learning FTS_MATCH's query grammar.

FTS_WARM — eager warm-up for full-text retrieval structures.

Usage:

SELECT * FROM FTS_WARM('table_name' [, 'column_name'...])

FTS_SEARCH builds a tokenized document directory, term dictionary and inverted postings lazily on its first query for a table/column set. That is ideal for one-off searches, but a serving process should not make its first user wait for an O(corpus) build. FTS_WARM moves that work to an explicit, observable lifecycle step. It uses the exact same column-set semantics and cache key as FTS_SEARCH, so the first real query can reuse the warmed entry without changing retrieval behavior.

Geometry-collecting aggregate functions: GEO_DISSOLVE/GEO_UNION_AGG/ ST_UNION (dispatch by geometry type; polygons go through geo_dissolve.go's shared-edge-cancellation dissolve, points/lines are trivially concatenated into a Multi* geometry), GEO_BBOX_AGG (fold every row's own bounding box), and GEO_CENTROID_AGG (optionally weighted centroid, folding the existing per-row GEO_CENTROID rather than re-deriving its area/length-weighting rules).

These are registered as aggregates the same way every other one is in this codebase: added to isAggregate()'s name switch (so they trigger aggregation even without an unrelated GROUP BY forcing it) and to evalAggregateFuncCall()'s dispatch switch (both in eval_aggregate.go).

GEO_CLIP/ST_CLIP(geometry, boundary_polygon [, allow_nonconvex]): Sutherland-Hodgman polygon clipping.

Sutherland-Hodgman is only guaranteed correct when the *clip* boundary is convex -- clipping against a non-convex boundary with the plain algorithm can produce a wrong shape. This is validated by default (protect first, explicit override for advanced use, matching GEO_SMOOTH's iteration cap and GEO_AFFINE's anchor requirement elsewhere in this file family) and left as an opt-in best-effort via a third `allow_nonconvex` argument rather than refused outright, since clipping to a bbox or a convex-hull region -- both very common BI operations ("clip this layer to my dashboard's current map viewport") -- is exactly what it's for.

GEO_DISSOLVE's polygon-merging core: a shared-edge-cancellation dissolve.

Two adjacent, topologically-clean polygons that share a boundary segment traverse that segment in *opposite* directions -- each ring's own winding keeps its own polygon's interior on its own side of the edge. Cancelling exact-reverse directed-edge pairs therefore removes every interior boundary between adjacent input polygons and leaves only the true outer (and inner, i.e. hole) boundary of their union.

This is correct for topologically-clean, vertex-aligned adjacent polygons -- real GIS boundary data, or this project's own dissolve output fed back in -- and deliberately NOT a general polygon-boolean-union: overlapping but not edge-aligned polygons would need a true Weiler-Atherton/ Greiner-Hormann boolean union, which is out of scope here. That is a documented v1 limitation, the same kind of honest scope cut GEO_IS_VALID (no self-intersection check) and plan_range_index.go (no R-tree) already make elsewhere in this codebase.

OGC GeoPackageBinary scalar helpers. These functions operate on a BLOB and therefore work independently of where it came from: a GeoPackage file, an HTTP API, a message queue, or an ordinary tinySQL column.

Geohash: encode/decode a lon/lat point to/from the base-32 string Gustavo Niemeyer's geohash.org popularized, plus the neighbor and bounding-box helpers that make it useful as a coarse spatial-locality key (e.g. a GROUP BY or index prefix column that clusters nearby points together without needing GEO_SEARCH's grid index).

The algorithm is the standard interleaved-bit one: alternately halve a longitude range and a latitude range, appending a 1 bit each time the point falls in the upper half, and packing every 5 bits into one base-32 character using the same non-alphabetic-lookalike alphabet (0-9b-hjkmnp-z, omitting a, i, l, o) the reference implementation uses.

A direct encoder for the geometry objects GIS functions return.

json.Marshal of a map[string]any holding nested []any and boxed float64 values cost as much as the geometry work that produced it. marshalGeoJSON writes the same bytes without reflection for the small set of value types geometry results are built from, and hands anything else -- an unexpected type, a NaN or infinity, a string that would need escaping -- to json.Marshal unchanged, so output and errors are identical either way. geo_json_encode_test.go compares the two encoders byte for byte.

A small, allocation-lean decoder for the GeoJSON every GIS function reads.

GEO_* and ST_* functions take geometries as GeoJSON text and decode them on every call. encoding/json (on current Go a layer over json/v2, reflecting into map[string]any) made that decode more than half of the CPU time of a typical GIS query; the geometry arithmetic itself is tiny by comparison.

The decoders here accept only the plain, compact subset that tinySQL itself writes (see canonicalGeoJSON): ASCII strings without escapes, JSON-grammar numbers. Typed geometry headers reject duplicate members; the generic object decoder keeps the last value like encoding/json. These decoders report ok=false for anything else, and the callers then run the original encoding/json path -- so malformed input, exotic input and every error message stay exactly what they were. When a decoder does accept a value, it is the value encoding/json would have produced; geo_json_fast_test.go checks that differentially.

MBTILES_TILE, MBTILES_TILES and MBTILES_METADATA let a SQL query read directly from an .mbtiles file's own SQLite storage, without the GEO_FROM_WKT-style step of first running a separate Go-API import/open call (ImportMBTiles, OpenMBTiles, ImportMBTilesArtifact -- all in internal/importer, and all still the right tool when the goal is *persisting* a tileset into tinySQL rather than reading it in place from one query).

This file adds no SQLite-reading code of its own: it is a thin bridge from the SQL layer to internal/importer's existing, already-hardened mbtiles readers (mbtiles_open.go's OpenMBTiles for the bulk/metadata cases, mbtiles_lookup.go's LookupMBTilesTile for the single-tile case). Those are gated behind the sqliteimport build tag with a same-signature "not supported in this build" stub for every other target (js/wasm/ baremetal, or sqliteimport simply not requested) -- calling into them unconditionally here, with no build tag on this file itself, means these three SQL functions degrade the exact same way on those targets: they exist and can be called, and return that same clear error, rather than being silently absent from the function registry.

A small cache of parsed polygons for constant arguments.

ST_WITHIN(pt, <region>) and ST_CONTAINS(<region>, pt) are almost always evaluated with one constant region over many rows. The region is an argument like any other, so it was decoded from GeoJSON for every row: for a 5,000-vertex border that is hundreds of microseconds and a megabyte of allocation per row, far more than the point-in-polygon test itself.

The cache holds the last few large polygon texts together with their parsed form and bounding box. A lookup is a handful of string comparisons -- Go compares equal strings with a pointer check first, so the same constant string is an O(1) hit -- and the read path takes no lock. Parsed polygons are shared between callers and must be treated as read-only.

ST_TOUCHES, ST_COVERS/ST_COVEREDBY and ST_PERIMETER round out geo_relate.go's ST_INTERSECTS/ST_DISJOINT/ST_EQUALS trio with the other DE-9IM predicates GIS users reach for most. Same engineering stance as that file: exact, non-epsilon comparisons, and a documented sampling-based approximation (vertices plus edge midpoints, rather than a full DE-9IM matrix) wherever exactness would require a general polygon-arrangement algorithm this codebase does not otherwise need. See the package doc comment on getGeoRelateFunctions for the same judgment call already made about self-intersecting input.

ROUTE_SHORTEST_PATH and ROUTE_DISTANCE run Dijkstra's algorithm over an ordinary edge-list table -- exactly the shape ImportRoutingGraph (internal/importer/routing_graph.go) already produces (source/target/ cost columns, one row per edge) but not tied to that importer: any table with a source column, a target column and a non-negative numeric weight column works.

This follows GEO_SEARCH's table-valued-function convention (table name and column names passed as string arguments, not identifiers the parser resolves) rather than adding graph traversal to the query planner, which only ever recognizes per-row predicates -- see spatial_index.go's doc comment for the same reasoning GEO_SEARCH already gives. Like GEO_SEARCH, the derived adjacency structure is cached by tenant, table version, column set and direction. Repeated routes therefore pay only the shortest-path search, while writes invalidate through Table.Version and DROP/rollback purge the derived graph eagerly.

Only non-negative edge weights are accepted (Dijkstra's own precondition); a graph with negative weights needs Bellman-Ford, which is out of scope here the same way a general reprojection engine is out of scope for ST_TRANSFORM (geo_transform.go) -- both are real features with a real algorithmic cost this codebase has no present need to carry.

ST_TRANSFORM reprojects a geometry between WGS84 (SRID 4326, the lon/lat degrees every other geo function in this engine assumes) and Web Mercator (SRID 3857, the projection every XYZ/Slippy-map tile server -- and this engine's own TILE_* functions, see tile_functions.go -- uses). These two are supported because they are the pair that actually shows up in practice for a tile-serving engine: a table of WGS84 geometry needs to become Mercator meters to compute a tile bounding box or measure a screen-space quantity, and a value pulled from a Mercator-native source (e.g. an MVT tile, or a system that stores 3857 natively) needs to become WGS84 to interoperate with everything else here.

A general reprojection engine (arbitrary EPSG codes, datum shifts, PROJ pipelines) is out of scope: that needs a large parameter database this project has no appetite for vendoring (see the project's own minimal-dependencies stance), and every other geo function already assumes -- and only makes sense for -- WGS84 degrees. ST_TRANSFORM's job here is narrowly to get a geometry to or from the one other SRID this codebase's tile support cares about, not to become a general GIS reprojection layer.

WKB (Well-Known Binary) input and output, the binary sibling of geo_wkt.go's text format. Same role: a converter at the edges between tinySQL's GeoJSON-text storage and a foreign wire format, here one that typically arrives as a BLOB (e.g. a value read out of another database's geometry column, or produced by a client library's ST_AsBinary).

The standard uncompressed OGC WKB encoding is implemented: 1 byte byte- order marker, a 4-byte geometry type, then coordinates. Both EWKB Z/M/SRID flag bits and the ISO SQL/MM +1000/+2000/+3000 Z/M/ZM type codes are accepted. M is consumed and dropped because GeoJSON has no measure ordinate; Z is preserved.

WKT (Well-Known Text) input and output for the GeoJSON geometries every other geo_*.go file operates on. tinySQL stores geometry as GeoJSON text (see canonicalGeoJSON in geo_functions.go), so these functions are pure converters at the edges: ST_GEOMFROMTEXT parses foreign text *into* that representation and ST_ASTEXT renders it back out. Nothing in between ever sees WKT, which is why there is no WKT-native code path anywhere else -- adding one would mean maintaining every predicate twice.

Scope notes, all deliberate:

• EWKT's "SRID=4326;" prefix is accepted on input. Since tinySQL's geo layer is WGS84 lon/lat throughout, a non-WGS84 SRID is rejected with a pointer to ST_TRANSFORM rather than silently relabelled -- see geoRequireWGS84SRID.
• The M (measure) ordinate is parsed and discarded: a GeoJSON position is [lon, lat] or [lon, lat, z] and has nowhere to put it. Z is preserved end to end.
• "EMPTY" round-trips through GeoJSON's empty-coordinates form ({"type":"Point","coordinates":[]}). That form is deliberately *not* accepted by a GEOMETRY column (validateGeometryShape requires a real position), so an empty geometry can be read, inspected and re-rendered but not stored. Erroring out mid-import on a single EMPTY row -- which real-world WKT dumps do contain -- would be the worse trade.

JSON path evaluation for JSON_GET and JSON_SET.

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.

Parsing a column definition and its constraints: NOT NULL, DEFAULT, PRIMARY KEY, UNIQUE, REFERENCES and the referential actions on a foreign key.

Parsing a declared column type and mapping it to a storage type and a SQLite type affinity.

Parsing CREATE and DROP for tables, triggers, virtual tables, indexes and scheduled jobs.

Parsing ALTER, for tables, views and materialized views.

Parsing CREATE, DROP and REFRESH for views and materialized views, including a materialized view's refresh schedule and staleness options.

Parsing INSERT, UPDATE, DELETE and the RETURNING clause they share.

Parsing the operand levels of an expression: additive and multiplicative arithmetic, unary operators, and the primaries — literals, column references, parenthesised expressions, subqueries and CASE.

Parsing the predicate levels of an expression, loosest first: OR, AND, NOT, IS NULL, and the comparisons — including BETWEEN, IN, LIKE, REGEXP and SIMILAR TO.

Parsing a function call, and the OVER clause that turns one into a window function: PARTITION BY, ORDER BY and the frame.

Parsing SELECT: common table expressions, DISTINCT, the projection list, FROM, joins, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, PIVOT and the set operations that combine two selects.

The statement dispatcher, and the statements small enough to parse in one place: CALL, EXPLAIN, ANALYZE and PRAGMA.

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.

RAG_SEARCH composes VEC_SEARCH, FTS_SEARCH, and RAG_CONTEXT_FROM's neighbor-expansion logic into a single table-valued function, so a typical RAG retrieval workflow — vector search, optional hybrid text fusion, optional neighboring-chunk expansion — no longer requires callers to hand-write the multi-primitive pipeline documented in docs/rag-guide.md.

Usage:

SELECT * FROM RAG_SEARCH('chunks', 'embedding', query_vector, 5)

SELECT * FROM RAG_SEARCH('chunks', 'embedding', query_vector, 5, '{
  "text_columns": ["heading", "chunk_text"],
  "text_query": "what is the capital of France",
  "key_columns": ["doc_id", "chunk_index"],
  "expand_before": 1,
  "expand_after": 1,
  "doc_id_column": "doc_id",
  "chunk_index_column": "chunk_index"
}')

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

Row plumbing: turning a stored table into row maps, merging rows for joins, building group and DISTINCT keys, and comparing rows.

A lazy, version-invalidated spatial index for GEO_SEARCH, following the exact same shape as the Vector index (vector_index.go) and FTS's doc cache (fts.go): tinySQL has no R-tree and no CREATE INDEX ... USING clause, so a fundamentally different index structure is bolted on the side as a table-valued-function-scoped cache rather than wired into the query planner (which today only ever recognizes *Binary comparisons in a WHERE clause, never a *FuncCall predicate like GEO_DWITHIN).

The structure itself is a fixed-size equirectangular lon/lat grid: each row's geometry is bucketed into every cell its bounding box touches, by its own bounding box (not just its centroid), so a geometry spanning several cells is a candidate from all of them. Candidate selection is deliberately a superset filter -- GEO_SEARCH always re-verifies every candidate against the exact, unmodified GEO_WITHIN_BBOX/GEO_DWITHIN math (geo_search.go), the same "index narrows, per-row function call verifies" division of labor plan_range_index.go already documents for its own 1-D range seeks.

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

Web Mercator tile addressing, for working with MBTiles tilesets in SQL.

Two coordinate conventions meet in a tileset, and confusing them is the classic MBTiles bug:

• XYZ ("slippy map", what web clients and /{z}/{x}/{y}.png URLs use) counts rows from the *top*: row 0 is the northernmost.
• TMS (what the MBTiles specification stores in tiles.tile_row) counts rows from the *bottom*: row 0 is the southernmost.

They differ by tile_row = 2^zoom - 1 - y. A tileset served with the flip missing looks plausible — right tiles, wrong places, mirrored vertically — which is why every function here states which convention it speaks, and why TILE_FLIP_Y exists as the one explicit conversion.

Functions taking or returning a y value use XYZ, because that is what callers have when serving a map request. Only TILE_FLIP_Y and the MBTiles helpers mention TMS.

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).

SQL value semantics: NULL, three-valued boolean logic, and comparison and ordering across the storage types. Everything that decides what "equal", "true" and "less than" mean lives here, so those rules can be read in one place rather than inferred from their call sites.

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 (lower = closer)
_vec_similarity  – similarity derived from the distance (higher = closer);
                   feed this, not _vec_distance, into RAG_HYBRID_SCORE /
                   RAG_RANK_SCORE, which expect a similarity input
_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,
distinct_dims, excluded_rows

distinct_dims counts how many different vector lengths were seen among the valid rows (e.g. during an embedding-model migration where old and new dimensionalities briefly coexist in the same column). The ANN index is only ever built for the first-seen dims; excluded_rows reports how many otherwise-valid rows were left out of that index because their vector's length did not match dims, so callers can detect a mixed-dimensionality column instead of silently getting a partial index.

Automatic WAL logging for INSERT/UPDATE/DELETE, for both WAL implementations tinySQL supports.

tinySQL has two WAL implementations — the basic WALManager (whole-table snapshot diffing, used by ModeWAL) and AdvancedWAL (row-level, LSN-based, with real REDO/recovery, used by ModeAdvancedWAL) — but historically nothing in the engine's own INSERT/UPDATE/DELETE path called either one. Both were only driven by internal/driver's explicit BeginTx/Commit and autocommit paths. A caller using ModeWAL or ModeAdvancedWAL and writing through the ordinary tinysql.Execute API (not through internal/driver's connection machinery — e.g. cmd/server, or any direct embedder) got no durability logging at all: a silent gap, since nothing errored or warned about it, and DB.HealthCheck() still reported WALActive/AdvancedWALActive as true.

walAuto wraps each mutating statement in its own implicit, autocommitted AdvancedWAL transaction (LogBegin before the row loop, LogCommit after) and logs every row change as it happens. maybeLogToWALManager instead diffs the statement's pre-execution snapshot against the now-mutated DB and logs the resulting table-level changes to WALManager, mirroring what internal/driver's commitTx/execStatement already do for driver-mediated access. Both are complete no-ops — zero calls, zero overhead beyond a nil check — when the corresponding WAL isn't attached.