EvaluatedApplications.HoloDb 21.0.2

dotnet add package EvaluatedApplications.HoloDb --version 21.0.2
                    
NuGet\Install-Package EvaluatedApplications.HoloDb -Version 21.0.2
                    
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="EvaluatedApplications.HoloDb" Version="21.0.2" />
                    
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="EvaluatedApplications.HoloDb" Version="21.0.2" />
                    
Directory.Packages.props
<PackageReference Include="EvaluatedApplications.HoloDb" />
                    
Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.
paket add EvaluatedApplications.HoloDb --version 21.0.2
                    
#r "nuget: EvaluatedApplications.HoloDb, 21.0.2"
                    
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
#:package EvaluatedApplications.HoloDb@21.0.2
                    
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package.
#addin nuget:?package=EvaluatedApplications.HoloDb&version=21.0.2
                    
Install as a Cake Addin
#tool nuget:?package=EvaluatedApplications.HoloDb&version=21.0.2
                    
Install as a Cake Tool

HoloDb

An embedded SQL database for .NET. Tables are typed columns, held in memory or on disk, and the engine builds the structures that make repeated queries fast from the queries it sees: summaries, sorted and keyed indexes, a tree of exact sums, per-block records of what earlier reads found, and links between blocks. You write SQL. The engine chooses and maintains the access paths, so there is no index design to do, and every answer is exact.

Tables live in typed columns. Everything the engine builds beside them is derived from those columns, so losing a derived structure costs time, never data. Integer sums are held in 128 bits and a statement that would overflow 64 bits throws instead of wrapping. DECIMAL(p, s) columns hold exact values of up to 18 digits. The package adds ACID transactions with a write-ahead log, a NEAREST operator that ranks rows by similarity of their holographic encoding, analytic SQL, and an optional exact aggregate plane built on Phasor's Pleroma stores.

The SQL is standard by default. Vendor spellings are opt-in with SqlDialect.Extended; the SQL dialect section lists both sets.

This file documents the public surface of the package, with a working example for each group. An example that uses an Extended statement says so on its first line.

Contents

Quick start

dotnet add package EvaluatedApplications.HoloDb
using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE users (id INT PRIMARY KEY, age INT, name TEXT)");
db.Execute("INSERT INTO users VALUES (1, 30, 'Ada')");

var result = await db.ExecuteAsync("SELECT name FROM users WHERE age >= 18");
foreach (var row in result.Rows) Console.WriteLine(row["name"]);
// Ada

With no WalPath the database is in memory. Set WalPath to make it durable (see Opening a database).

What it does

  • SQL over typed columns. CREATE TABLE, ALTER TABLE ... ADD COLUMN, INSERT, UPDATE, DELETE, and SELECT with joins, predicates, grouping, ordering, FETCH FIRST, transactions, and EXPLAIN (Extended).
  • Exact aggregates. COUNT, SUM, AVG, MIN, MAX and COUNT(DISTINCT ...). Whole-table totals and single-column groups read maintained totals instead of scanning.
  • Exact decimals. DECIMAL(p, s) for up to 18 digits: exact +, -, * and /, rounding half away from zero, overflow and division by zero refused with their SQLSTATE codes. See DECIMAL.
  • Self-tuning access paths. Summaries, sorted and keyed indexes, a holographic tree of exact sums, per-block records and learned block-to-block routes are built when the workload has paid for them, and dropped when it stops using them or when writes make them stale. No index has to be declared.
  • Placement that follows use. Rows that the same filters read are written together, by a regroup that runs as upkeep, and an UPDATE or INSERT moves a row to a block the repeated filters already read.
  • Deletes that cost what they delete. A DELETE marks rows and moves nothing. Row ids stay stable, and consolidation later fills the holes and gives the freed pages back, so the data file shrinks.
  • Durable transactions. A write-ahead log of signed deltas in a fixed ring, group commit, checksums on every log block and every data page, and crash recovery that replays the log.
  • Larger than memory. Paged tables read their pages through a buffer pool. A statement larger than the pool spills to a scratch file, and a rollback or a power cut leaves the table as it was.
  • History. Every committed change of a paged table is kept as a signed record. SELECT ... AS OF n reads the table as it was after commit n, and Seal(table, asOf: n) returns its seal then. What cannot be answered exactly is refused.
  • Analytic SQL. Narrow column types, dictionary-coded text, CASE, CAST, EXTRACT, table and column aliases, positional GROUP BY and ORDER BY, and COUNT(DISTINCT) on large cardinalities.
  • Similarity search. NEAREST (col = value, ...) ranks rows by graded closeness, with a score column.
  • Change feeds. ApplyChanges applies upserts and deletes atomically on in-memory tables, one write lock and one log commit per batch, and KeyDigest compares two key sets in modulus numbers.
  • Summary tables. A table that keeps no rows, only an exact aggregate plane, for dashboards over feeds that are too large to keep.

Storage format and upgrading

Paged tables. The write-ahead log of a paged table, wal.log, is a fixed-size ring of checksummed blocks holding signed deltas. It does not grow with history. Its tail is reclaimed as its deltas are written into data.pages, and a writer that finds it full writes some of them first. The log is format HWAL 5.

The table record is table.meta12. Its first line is HoloDb table format 15, followed by the table name, the catalog page, the buffer frames, the table's seal key and the store id of its page store.

Format 15. The catalog of format 15 records the precision and scale of each DECIMAL column. A store in format 14 opens, and its catalog is written again as format 15 on its next catalog write. A table record of format 13 or older, and a directory that holds an older table.meta* file, is refused by name. The message says to export the rows with the build that wrote them and load them into a new database. There is no in-place upgrade.

Across builds. A 20.0.0 build reads only format 14. It refuses a store written by this release (format 15) with a named message. Stores written by 20.0.0 open in this release.

Commits. Commits that wait for the same fsync are written as one batch. A statement that has returned is on disk.

Derived files. Sorted indexes, keyed summaries, summary files and learned records are trusted at open only when they match the table. A stale or damaged derived file is discarded and rebuilt from the columns, or, for a declared structure, rebuilt from its saved definition. SHOW SUMMARIES gives the reason.

Checksums. Every data page has a CRC-32C in data.crc. A non-empty paged store whose data.crc is missing or damaged is refused by name (data-checksum-missing): its pages cannot be checked, so they are not trusted and the file is not rebuilt.

Behaviour you will notice:

  • Row order after a DELETE, and after any write that moves a row, is unspecified. Add ORDER BY where order matters.
  • Deletes take time in proportion to the rows they delete, not to the table.
  • Upkeep runs by default: a paged table consolidates its deleted rows after a statement once the scans that read them have paid for it, and the planner builds and drops structures on its own. Database.AutoConsolidate = false and SET summaries = declared (or off) turn these off.
  • HoloDbOptions.CheckpointThresholdBytes governs only the text log of a database that is not in paged mode.

COUNT(*) and AVG count the rows that exist. Deleted places are not rows.

Core concepts

  • Columns are the source of truth. A table is typed columns. The planner's structures, the aggregate plane and the similarity encodings are derived from them, and each is trusted only when it matches the table's change counter and the exact totals.
  • Exact answers. INT sums are exact 128-bit running totals, and a statement throws if the true total leaves 64 bits. Counts, sums and extremes served from a structure equal the scan's answer.
  • Two storage shapes, one SQL surface. In-memory tables (CREATE TABLE on a database without a paged default) and paged tables (CreatePaged/OpenPaged, or whole-database paged mode) take the same Execute calls. Where a capability differs, the relevant section says so.
  • Locking. One reader-writer lock per database. Mutations, DDL statements and transactions take it exclusively. Simple SELECT statements read without the lock and retry if a write overlaps them. Whole-table counts and sums and single-column group-bys are also served from a snapshot republished after each write. A structure built in the background is checked under the shared lock; the exclusive lock covers only the O(1) swap that publishes it.
  • Transactions. START TRANSACTION copies nothing (BEGIN is the same statement under the Extended dialect). Each change is journaled as it happens and ROLLBACK replays the journal. A transaction is a pending delta: a reader sees the base plus its own changes, and commit adds the changes to the log. A multi-statement transaction is a series of Execute calls on the same database. On a paged table, a statement that fails inside a transaction undoes only itself: the statements before it stay, and COMMIT keeps them. The first DDL statement in a transaction turns it into a snapshot. Checkpoint() inside a transaction throws. Outside a transaction a failed statement changes nothing.
  • Constraints on write. Types, NOT NULL (the default for every column not declared NULL) and primary-key uniqueness are checked before a row is written.

Opening a database

public sealed class HoloDbOptions
{
    public string? WalPath { get; set; }                  // null = in memory
    public long CheckpointThresholdBytes { get; set; }    // text log (not paged mode): fold into a snapshot past this size; 0 = never
    public bool Paged { get; set; }                       // whole-database paged mode for a new database (see below)
    public SqlDialect Dialect { get; set; } = SqlDialect.Standard;   // the SQL the database accepts; see SQL dialect
}
  • HoloDbService is the entry point: Execute(sql[, args]), ExecuteAsync(sql[, args], ct), BulkLoad, BulkLoadColumns, ApplyChanges and ApplyChangesAsync (single table and several tables), ApplyPlaneChanges and its async form, KeyDigest, PlanePosition, PlanePositionBytes, PlaneTotals, ExportPlane, ImportPlane, CreatePlaneOnly, CreatePaged, OpenPaged, PagedBulkLoad, ClosePaged, Consolidate(table), Regroup(table), Checkpoint(), Close(), and .Database, the engine itself.
  • Database is the engine. Use it directly for the members not wrapped by the service: Database.Open(walPath = null, checkpointThresholdBytes, paged = false) (a static factory), Execute, Bind, BulkLoad, Checkpoint, Close, WalPath, IsPagedDefault, TableColumns(table), Consolidate(table), AutoConsolidate, PlaceOnWrite, Regroup(table), Dialect, Summaries and Indexes (the planner modes), StructureBudgetBytes, SetStructureBudget(bytes), RouteTrace, the aggregate plane members (EnablePleroma, DisablePleroma, PleromaMode, PleromaStats, PleromaVerify, PleromaSumExact), and the static per-thread usage counters LastRowsScanned, LastTableRows, LastRowBytes and LastLogicalBytes.
using HoloDb;

var path = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName() + ".wal");
var db = new HoloDbService(new HoloDbOptions { WalPath = path });
db.Execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)");
db.Execute("INSERT INTO t VALUES (1, 100)");
db.Checkpoint();
db.Close();

var reopened = new HoloDbService(new HoloDbOptions { WalPath = path });
var r = reopened.Execute("SELECT v FROM t WHERE id = 1");
Console.WriteLine(r.GetLong(0, 0));
// 100
reopened.Close();

Close() releases the file handles of every open paged table. Call it before you open the same WalPath again in the same process, and on shutdown. Close() also cancels the background builds and waits for them to stop. A build that was cancelled publishes nothing.

Diagnostics. Database.RouteTrace is a thread-static list. Set it to a new List<string> and the engine records the access path and the route each statement on that thread takes. EXPLAIN reports the same decision without running the statement. EXPLAIN is an Extended statement, so this example sets the dialect.

// Extended dialect: EXPLAIN is an Extended statement.
using HoloDb;

var db = new HoloDbService(new HoloDbOptions { Dialect = SqlDialect.Extended });
db.Execute("CREATE TABLE s (id INT PRIMARY KEY, n INT)");
db.Execute("INSERT INTO s VALUES (1, 1), (2, 2)");

Database.RouteTrace = new List<string>();
db.Execute("SELECT count(*) FROM s WHERE n > 1");
foreach (var step in Database.RouteTrace) Console.WriteLine(step);
Database.RouteTrace = null;

foreach (var r in db.Execute("EXPLAIN SELECT count(*) FROM s WHERE n > 1").Rows)
    Console.WriteLine($"{r["kind"]}: {r["route"]}");

SQL dialect

HoloDb accepts the SQL standard and its own statements by default. This is SqlDialect.Standard, and every example on this page that does not say otherwise runs on it as written.

Vendor spellings are the Extended dialect, opt-in:

  • new HoloDbOptions { Dialect = SqlDialect.Extended } for a HoloDbService, or Database.Dialect on the engine.
  • HoloDb.Server --dialect extended. The server default is standard. A client has no dialect setting of its own: the server decides which SQL it accepts.
using HoloDb;

var db = new HoloDbService(new HoloDbOptions { Dialect = SqlDialect.Extended });

Extended adds only LIMIT, ILIKE, CREATE INDEX and DROP INDEX (with USING KEYED), SHOW (other than SHOW SUMMARIES and SHOW SEQUENCE), DESCRIBE, EXPLAIN, IF [NOT] EXISTS, INSERT ... ON CONFLICT (pk) DO UPDATE, BEGIN, the % operator, and the functions CONCAT, REPLACE, LENGTH, SUBSTR, LEFT, RIGHT, ROUND, TRUNC, SIGN, NOW, DATE_TRUNC, REGEXP_REPLACE, STRING_AGG, MEDIAN, BOOL_AND, BOOL_OR, STDDEV and VARIANCE.

Both dialects accept HoloDb's own statements: NEAREST (with its own LIMIT k), AS OF, SET summaries, SET indexes, SHOW SUMMARIES, CREATE SUMMARY TABLE, and APPLY SUMMARY.

Refused. A statement that uses an extension under Standard is refused by name. The error has SQLSTATE 0A000, code extension-disabled (HoloErrorCodes.ExtensionDisabled), and a message that says how to enable Extended. A name that is in neither dialect, for example STRPOS, IIF, TRY_CAST, ARG_MAX, DATE_DIFF, STRFTIME or REGEXP_MATCHES, is an unknown function.

The check runs on the text of each statement sent to the public Execute calls. Statements the engine writes for itself and the replay of the log are not checked, so a log written by an Extended database opens under Standard.

Standard spellings for the Extended names:

Extended Standard
LIMIT n FETCH FIRST n ROWS ONLY
BEGIN START TRANSACTION
LENGTH(x) CHAR_LENGTH(x) or OCTET_LENGTH(x)
SUBSTR(x, n, m) SUBSTRING(x FROM n FOR m)
CONCAT(a, b), a \|\| b a \|\| b
LEFT(x, n), RIGHT(x, n) SUBSTRING(x FROM 1 FOR n), SUBSTRING(x FROM n)
TRUNC(x) FLOOR(x) or CEIL(x)
SIGN(x) a CASE expression
NOW() CURRENT_TIMESTAMP() (the parentheses are required)
a % b MOD(a, b)
ILIKE LOWER(x) LIKE LOWER(pattern)
STRING_AGG(x, s) LISTAGG(x, s) (the separator is required)
MEDIAN(x) PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x)
BOOL_AND(x) EVERY(x)
STDDEV(x) STDDEV_SAMP(x)
VARIANCE(x) VAR_SAMP(x)
ROUND, REPLACE, DATE_TRUNC, REGEXP_REPLACE no standard spelling
CREATE INDEX, DROP INDEX, EXPLAIN, DESCRIBE, SHOW none. The engine builds its own structures. Schema: information_schema.tables and information_schema.columns
INSERT ... ON CONFLICT (pk) DO UPDATE none in Standard. ApplyChanges is the host upsert
IF [NOT] EXISTS none

SQL reference

Types and tables

Statement Purpose
CREATE TABLE t (col TYPE [NULL \| NOT NULL] [PRIMARY KEY], ...) Defines a table. An ordinary table has one PRIMARY KEY, of type INT or TEXT. A GUID is a TEXT value.
CREATE TABLE t (col TYPE, ...) without a primary key, or with analytic types Defines an analytic table (see Analytic tables). Paged databases only.
CREATE TABLE IF NOT EXISTS t (...) Extended.
ALTER TABLE t ADD [COLUMN] col TYPE [NULL] [DEFAULT <literal> \| NULL] Adds one column. The DEFAULT fills the rows that existed when the column was added. A DECIMAL column can be added only with DEFAULT NULL. There is no DROP, rename or type change.
CREATE SUMMARY TABLE [IF NOT EXISTS] t (col TYPE DIMENSION \| MEASURE, ...) [WITH (max_dim_bits = n, max_joint_bits = n)] Defines a table that keeps no rows (see Summary tables). IF NOT EXISTS is Extended.
CREATE TABLE t AS SELECT ... Creates a keyless analytic table from a query result. Paged databases only. Every result column needs a name of its own (AS total) and no value may be NULL.
DROP TABLE [IF EXISTS] t Removes the table, its files and every structure built on it. IF EXISTS is Extended. A paged table cannot be dropped inside a transaction; an in-memory one rolls back with the transaction.
TRUNCATE [TABLE] t Removes every row (the same as DELETE FROM t).
DESCRIBE t Extended. Lists the columns. Under Standard, SELECT ... FROM information_schema.columns lists them as rows.

Type names: INT (also INTEGER), TEXT (also VARCHAR, STRING), REAL (also FLOAT, DOUBLE, DOUBLE PRECISION) and DECIMAL(p, s) or NUMERIC(p, s), which are exact (see DECIMAL). A bare DECIMAL is refused. There is no BOOLEAN (use INT 0 or 1). Column DEFAULT in CREATE TABLE, UNIQUE, CHECK, REFERENCES and generated keys are refused by name. A table-level PRIMARY KEY (col) is the same as the column form; a composite key is refused. Analytic tables also take SMALLINT, INTEGER, BIGINT, DATE, TIMESTAMP, CHAR(n) and VARCHAR(n), each stored at its declared width.

Values are stored exactly or refused by name. An INT column takes a whole number in the 64-bit range (3.0 is stored as 3; 2.5, a number past 64 bits or a text that is not a number throws). A REAL column takes any finite number; NaN and Infinity throw, from SQL, parameters and bulk loads alike. Numeric literals take a point or an exponent (1.5, 1e20, 2.5E-7). Integer arithmetic in a SELECT that would pass the 64-bit range throws for the rows the query keeps.

DECIMAL

DECIMAL(p, s) and NUMERIC(p, s) hold exact values of at most 18 digits in total, with 1 <= p <= 18 and 0 <= s <= p. The value is stored as a scaled 64-bit integer.

  • Arithmetic. +, -, * and / on decimal values are exact. A result is rounded half away from zero to the scale it needs. A result that does not fit 18 digits throws SQLSTATE 22003. Division by zero throws 22012.
  • Writes. A value written to a decimal column is brought to the column's scale. A value that does not fit the column's digits throws 22003. Text input to a decimal column is refused.
  • Results. A decimal result comes back as a System.Decimal with its scale, and QueryResult.ColumnScale gives the column's scale. A decimal with an integer is a decimal. A decimal with a double is a double.
  • Literals. A decimal literal is exact up to 15 significant digits. A longer literal is refused by name.
  • Works on in-memory and paged tables: decimal literals and columns in expressions and WHERE; INSERT; UPDATE and DELETE whose WHERE names no decimal column; SELECT with aggregates, GROUP BY and HAVING (SUM keeps the scale, AVG returns a decimal); DROP TABLE, SHOW and DESCRIBE.
  • Refused by name for a table that holds a decimal column (SQLSTATE 0A000): a join over it; NEAREST over it; an UPDATE or DELETE whose WHERE names a decimal column; Seal, KeyDigest, EnablePleroma, BulkLoad, BulkLoadColumns, PagedBulkLoad, ApplyChanges, and a read AS OF a past sequence.
  • Refused at definition: a bare DECIMAL (0A000); p above 18 (0A000); p below 1 or a scale outside 0..p (42601); a DECIMAL primary key; a DECIMAL column in a summary table; an ADD COLUMN of a decimal with a DEFAULT other than NULL.
  • The typed analytic route does not run on a table with a decimal column. Those statements run on the row engine.

Format 15 records the precision and scale of each column (see Storage format).

Indexes, summaries and planner settings

The engine builds its own structures under the Standard dialect. Declaring an index takes the Extended dialect.

Statement Purpose
CREATE INDEX [name] ON t (col) (Extended) A sorted index on one INT or TEXT column.
CREATE INDEX [name] ON t (dims ...) [INCLUDE (measures ...)] USING SUMMARY [WITH (max_dim_bits = n, max_joint_bits = n)] (Extended) Builds the aggregate plane from SQL. Dimensions are INT or TEXT columns, measures are INT columns. A dimension past its cap does not fail the statement: the row in SHOW INDEXES lists it as over the cap, and the queries that need it go to the engine.
CREATE INDEX [name] ON t (intKey [, splitCol]) INCLUDE (measures) USING KEYED (Extended) A keyed summary on a paged table: the exact count and sums for each value of an INT key, optionally split by a second column with few distinct values.
CREATE INDEX ... USING NEAREST Refused in both dialects. Similarity search needs no index: see NEAREST.
DROP INDEX name [ON t] (Extended) Removes an index or summary.
SHOW INDEXES [ON t] (Extended) One row per index: table, name, kind, columns, include, rows, bytes, served, declined, over_cap.
SHOW SUMMARIES [ON t] One row per summary and index the planner knows about, with its origin (declared or automatic), state, size, counts served and declined, and the reason for its state.
SET summaries = auto \| declared \| off auto (the default) lets the engine build and drop aggregate summaries. declared keeps only the ones you declared. off uses none.
SET indexes = auto \| declared \| off The same for sorted and text indexes, and for the paged sorted and keyed structures.
EXPLAIN [PLAN [FOR]] SELECT ... (Extended) How the statement would be answered, without running it. One row per step: step, kind, route, detail, est_rows.

EXPLAIN and SHOW SUMMARIES say what the planner is waiting for: how much scan time the shape has paid and what the build would cost, and when a structure was built and why.

Writes

Statement Purpose
INSERT INTO t [(col, ...)] VALUES (v, ...)[, (v, ...)] Inserts one or more rows. An omitted column is NULL when declared NULL, otherwise its type's zero (0, 0.0 or '').
INSERT INTO t [(col, ...)] SELECT ... Inserts a query result.
INSERT INTO t [(col, ...)] VALUES (...) ON CONFLICT (pk) DO UPDATE SET col = EXCLUDED.col[, ...] (Extended) Upsert. An existing key changes only the SET columns. EXCLUDED.col must name the same column. In-memory tables only.
UPDATE t SET col = expression[, ...] [WHERE ...] Updates matching rows. The expression may read the row (SET n = n + 1). A literal NULL is allowed for a column declared NULL. The primary key can not be updated.
DELETE FROM t [WHERE ...] Deletes matching rows.

A plain INSERT of an existing primary key throws HoloException. UPDATE validates every SET value before it changes a row, so a bad value leaves the table as it was.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE p (id INT PRIMARY KEY, name TEXT)");
db.Execute("INSERT INTO p VALUES (1, 'Ada')");
try { db.Execute("INSERT INTO p VALUES (1, 'Dup')"); }
catch (HoloException ex) { Console.WriteLine(ex.Message); }
// duplicate primary key 1
try { db.Execute("UPDATE p SET id = 2 WHERE id = 1"); }
catch (HoloException ex) { Console.WriteLine(ex.Message); }
// cannot UPDATE the primary key

Parameterized queries

Every Execute has an overload that takes ? placeholders followed by arguments. The arguments are bound as correctly quoted SQL literals before the statement is parsed. A string argument has its quotes doubled. A ? inside a string literal is left alone. Supported argument types are string, bool, any integer type, and any floating-point type; a floating-point argument binds with all its digits and reads back exactly. A null or DBNull argument is the SQL literal NULL. An argument-count mismatch, an unsupported type, a ulong past the 64-bit range, NaN or Infinity throws. Database.Bind(sql, args) returns the bound text without running it.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE p (id INT PRIMARY KEY, name TEXT)");
db.Execute("INSERT INTO p VALUES (?, ?)", 1L, "O'Brien");
var r = db.Execute("SELECT name FROM p WHERE id = ?", 1L);
Console.WriteLine(r.Rows[0]["name"]);
// O'Brien

string text = Database.Bind("SELECT * FROM t WHERE id = ? AND name = ?", 1L, "Widget");
Console.WriteLine(text);
// SELECT * FROM t WHERE id = 1 AND name = 'Widget'

NULL

A column holds SQL NULL only when it is declared NULL: CREATE TABLE orders (id INT PRIMARY KEY, region TEXT NULL, amount INT NULL, qty INT NOT NULL). A primary key is never NULL.

Paged tables store the NULL cells of their nullable columns, and a statement that names such a column reads it with the same three-valued rules as in memory. Analytic tables have no NULL columns.

  • Three-valued logic. WHERE and HAVING keep a row only when the predicate is true. A comparison, LIKE, IN or BETWEEN on a NULL is unknown. IS NULL and IS NOT NULL are always true or false.
  • Aggregates skip NULL. COUNT(*) counts rows. COUNT(col), COUNT(DISTINCT col), SUM, AVG, MIN and MAX use the non-NULL values, and are NULL when there are none.
  • Grouping and ordering. GROUP BY and DISTINCT make one group of all the NULLs. ORDER BY puts NULL first when ascending and last when descending. An INNER JOIN never matches a NULL key.
  • Results. Read a NULL cell with Rows or GetValue, where it is DBNull.Value, or test QueryResult.IsNull. GetLong, GetDouble and GetText throw a HoloException on a NULL.
  • Literal NULL in a comparison. On a SELECT, a literal NULL beside =, <>, <, >, <=, >=, IN, BETWEEN or LIKE is unknown, as the three-valued rules say. In a DELETE or UPDATE WHERE, the same literal is refused by name, with a message that says to write IS NULL.
using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE orders (id INT PRIMARY KEY, region TEXT NULL, amount INT NULL, qty INT NOT NULL)");
db.Execute("INSERT INTO orders VALUES (1, 'north', 10, 1), (2, NULL, 20, 1), (3, 'north', NULL, 2)");
var r = db.Execute("SELECT COUNT(*), COUNT(amount), SUM(amount), AVG(amount) FROM orders").Rows[0];
Console.WriteLine($"{r["count(*)"]} rows, {r["count(amount)"]} amounts, sum {r["sum(amount)"]}, avg {r["avg(amount)"]}");
// 3 rows, 2 amounts, sum 30, avg 15
Console.WriteLine(db.Execute("SELECT COUNT(*) FROM orders WHERE region IS NULL").Rows[0]["count(*)"]);
// 1
Console.WriteLine(db.Execute("SELECT COUNT(*) FROM orders WHERE region != 'north'").Rows[0]["count(*)"]);
// 0
foreach (var g in db.Execute("SELECT region, COUNT(*) FROM orders GROUP BY region ORDER BY region").Rows)
    Console.WriteLine($"{(g["region"] is DBNull ? "(null)" : g["region"])}: {g["count(*)"]}");
// (null): 1
// north: 2

SELECT

SELECT [DISTINCT] item [, item ...] FROM t
    [INNER JOIN t2 ON a.col = b.col [...]]
    [NEAREST (col = value [, ...])]
    [WHERE ...] [GROUP BY col [, ...]] [HAVING ...]
    [ORDER BY col | position | alias | expression [ASC|DESC] [, ...]]
    [OFFSET n] [FETCH FIRST n ROWS ONLY]

Under the Extended dialect, LIMIT n is also accepted in the position of FETCH FIRST. The LIMIT k of NEAREST is a separate clause and is accepted in both dialects.

An item is a column, *, an aggregate (COUNT(*), COUNT(col), COUNT(DISTINCT col), SUM, AVG, MIN, MAX), or on an analytic-capable SELECT an expression with an AS alias. Aggregate result columns are named agg(col) in lower case, for example count(*) and sum(amount), and are read by that name or by position. AVG over an INT column returns a double.

  • Row order. Without ORDER BY the order of the rows, and so which rows FETCH FIRST keeps, is unspecified. The count is min(n, matching rows), every row satisfies the WHERE, and no row repeats.
  • Empty selections. SUM, AVG, MIN and MAX of no rows are NULL. COUNT is 0. An aggregate without GROUP BY always returns one row.
  • Refused, not reinterpreted. A statement that would otherwise drop one of its clauses throws HoloException: DISTINCT together with an aggregate (use GROUP BY), and NEAREST combined with a join, GROUP BY, HAVING, ORDER BY, DISTINCT or an aggregate.
using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE sales (id INT PRIMARY KEY, region TEXT, amount INT)");
db.Execute("INSERT INTO sales VALUES (1,'EU',100),(2,'EU',50),(3,'US',10)");

var agg = db.Execute("SELECT region, sum(amount) FROM sales GROUP BY region HAVING sum(amount) > 20 ORDER BY region FETCH FIRST 10 ROWS ONLY");
foreach (var row in agg.Rows) Console.WriteLine($"{row["region"]}={row["sum(amount)"]}");
// EU=150

var distinctRegions = db.Execute("SELECT DISTINCT region FROM sales ORDER BY region");
foreach (var row in distinctRegions.Rows) Console.WriteLine(row["region"]);
// EU
// US

Joins

INNER JOIN t2 ON a = b (JOIN is the same) runs on the engine's own join routes: an in-memory database accepts several in one statement, a paged table one. Qualify a column with table.column when both sides share its name. Everything else about a join, a LEFT, RIGHT, FULL or CROSS join, USING, a comma join, a table alias, a join of more than one paged table, a self join, a non-equality ON, runs on the row engine. NATURAL joins are refused with a HoloException that names what is not supported. A clause the dialect does not have is never silently dropped.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE users (id INT PRIMARY KEY, name TEXT)");
db.Execute("INSERT INTO users VALUES (1, 'Ada')");
db.Execute("CREATE TABLE orders (id INT PRIMARY KEY, userId INT)");
db.Execute("INSERT INTO orders VALUES (1, 1)");
var j = db.Execute("SELECT users.name FROM orders INNER JOIN users ON orders.userId = users.id");
foreach (var row in j.Rows) Console.WriteLine(row["users.name"]);
// Ada

The row engine

A statement the typed routes decline runs on the row engine: table aliases, every join kind (LEFT, RIGHT, FULL, CROSS, USING, comma, self joins, any ON), derived tables (FROM (SELECT ...) t), WITH (several, chained, with column lists; not RECURSIVE), UNION, UNION ALL, INTERSECT and EXCEPT (with ALL, a trailing ORDER BY and FETCH FIRST), scalar, IN and EXISTS subqueries (correlated ones too), = ANY, SOME and ALL over a subquery, VALUES as a table, SELECT without FROM, window functions (ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, and every aggregate with OVER (PARTITION BY .. ORDER BY .. ROWS | RANGE ...)), CASE (without ELSE it gives NULL), and NULL throughout with three-valued logic. It reads each table with an ordinary SELECT (every table kind, every route), holds the rows it needs in memory, and refuses by name a statement that would hold more than 20 million cells (rows times columns across its tables, joins and results). Filter or aggregate first, or read fewer columns. A table read twice by a statement is read once. A write between two table reads reruns the statement, up to three times, and then refuses. EXPLAIN of such a query names the route and lists its sources.

Functions, Standard. CHAR_LENGTH, CHARACTER_LENGTH, OCTET_LENGTH, BIT_LENGTH, SUBSTRING (with FROM and FOR), ||, UPPER, LOWER, TRIM (with BOTH, LEADING, TRAILING), LTRIM, RTRIM, LPAD, RPAD, POSITION, LIKE and NOT LIKE; ABS, MOD, FLOOR, CEIL, CEILING, POWER, SQRT, EXP, LN, LOG, LOG10, GREATEST, LEAST, and the trigonometric functions through ATAN2; EXTRACT, CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), LOCALTIMESTAMP() (bare keywords without parentheses are not parsed), and date arithmetic (date + n, date - date, date + INTERVAL n unit, where a month end clamps); CAST, COALESCE, NULLIF, CASE. Aggregates: COUNT (also DISTINCT and FILTER (WHERE ..)), SUM, AVG, MIN, MAX, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, PERCENTILE_CONT and PERCENTILE_DISC with WITHIN GROUP, LISTAGG(value, separator), ANY_VALUE, EVERY.

Functions, Extended. LENGTH, SUBSTR, CONCAT, REPLACE, LEFT, RIGHT, ROUND, TRUNC, SIGN, NOW, DATE_TRUNC, REGEXP_REPLACE, ILIKE, the % operator, STRING_AGG, MEDIAN, BOOL_AND, BOOL_OR, STDDEV and VARIANCE.

Semantics. / is real division (7 / 2 is 3.5). A DATE is text YYYY-MM-DD, a TIMESTAMP text YYYY-MM-DD HH:MM:SS. NULL sorts as the smallest value (first ascending, last descending; NULLS FIRST and NULLS LAST override it). Integer overflow and division by zero are errors, not wrapped values. LIKE is case-sensitive on every route: _ matches one character, and ESCAPE 'c' has no default. For case-insensitive matching write ILIKE (Extended) or LOWER(x) LIKE LOWER(pattern). A literal NULL in a comparison is handled as described in NULL.

Refused by name in both dialects: WITH RECURSIVE, NATURAL JOIN, GROUP BY ROLLUP | CUBE | GROUPING SETS, GROUP BY ALL, QUALIFY, the WINDOW clause and named windows, DISTINCT ON, LATERAL, SELECT * EXCLUDE, RANGE frames with an offset, GROUPS frames, SIMILAR TO, table functions (read_csv, read_parquet), and COPY. A statement a client sends never reads a file of the server: load files with ImportCsv.

Statements that read first

INSERT INTO t [(cols)] SELECT ... inserts a query result, and UPDATE t SET col = expression [WHERE ...] takes any expression (SET n = n + 1, SET name = name || '!'). Both run as ordinary statements in one transaction (the caller's own, inside START TRANSACTION): a failure changes nothing. An UPDATE with an expression reads the matching rows, deletes them and inserts them again, so their row order changes and a concurrent writer on another thread is not excluded between the read and the write; an UPDATE with literal values is unchanged. Every value of a DATE or TIMESTAMP column is written as 'YYYY-MM-DD' or 'YYYY-MM-DD HH:MM:SS'.

Predicates

AND, OR, NOT and parentheses. Comparisons =, != (or <>), <, >, <=, >=. LIKE (% for any run, _ for one character; case-sensitive). IN (v, ...) and IN (SELECT ...) (a single-column subquery). BETWEEN lo AND hi. IS [NOT] NULL on a declared NULL column. Each of LIKE, IN and BETWEEN takes an optional leading NOT.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE p (id INT PRIMARY KEY, name TEXT, price INT)");
db.Execute("INSERT INTO p VALUES (1,'Widget',25),(2,'Gadget',75),(3,'Widgetpro',150)");

Console.WriteLine(db.Execute("SELECT name FROM p WHERE name LIKE 'Widget%'").Rows.Count);            // 2
Console.WriteLine(db.Execute("SELECT name FROM p WHERE id IN (1,2)").Rows.Count);                    // 2
Console.WriteLine(db.Execute("SELECT name FROM p WHERE price BETWEEN 50 AND 100").Rows[0]["name"]);  // Gadget
Console.WriteLine(db.Execute("SELECT name FROM p WHERE id IN (SELECT id FROM p WHERE price > 50)").Rows.Count); // 2

Similarity search: NEAREST

SELECT ... FROM t NEAREST (col = value [, col = value ...]) [WHERE ...] [LIMIT k]

NEAREST ranks rows by the cosine similarity of their holographic encoding to a partial key: any subset of columns, of any type. The encoding is derived from the columns, so a row is ranked by the values it holds now: an UPDATE changes its rank. A WHERE filters the rows before they are ranked. The LIMIT k is NEAREST's own clause and is accepted in both dialects. NEAREST does not combine with a join or with GROUP BY, and it is refused over a table with a decimal column.

Similarity is graded. A number or date close to the key ranks ahead of a far one, and a text value that is nearly the same string ranks ahead of an unrelated one, by character trigrams. Add score to the projection to read the ranking value (1.0 for an exact match). score is a pseudo-column: SELECT * does not return it, and it can not appear in a WHERE. Above a size threshold, the ranking uses an approximate candidate search (locality-sensitive hashing) instead of comparing every row, so its order can differ from an exact scan.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE products (id INT PRIMARY KEY, category TEXT, price INT)");
db.Execute("INSERT INTO products VALUES (1,'electronics',100),(2,'electronics',120),(3,'furniture',300)");

var near = db.Execute("SELECT id, category, price, score FROM products NEAREST (category = 'electronics', price = 110) LIMIT 2");
foreach (var row in near.Rows) Console.WriteLine($"id={row["id"]} category={row["category"]} price={row["price"]} score={row["score"]:F3}");
// id=2 category=electronics price=120 score=0.989
// id=1 category=electronics price=100 score=0.989

Transactions

START TRANSACTION, COMMIT and ROLLBACK. BEGIN is accepted under the Extended dialect. A rolled-back transaction leaves the same rows, though their order may differ. DDL inside a transaction is allowed. BulkLoad is not, and Checkpoint() throws inside one.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE p (id INT PRIMARY KEY, name TEXT)");

db.Execute("START TRANSACTION");
db.Execute("INSERT INTO p VALUES (1, 'Temp')");
db.Execute("ROLLBACK");
Console.WriteLine(db.Execute("SELECT count(*) FROM p").Rows[0]["count(*)"]);
// 0

db.Execute("START TRANSACTION");
db.Execute("INSERT INTO p VALUES (1, 'Kept')");
db.Execute("COMMIT");
Console.WriteLine(db.Execute("SELECT count(*) FROM p").Rows[0]["count(*)"]);
// 1

Analytic tables and the typed SELECT

An analytic table is for wide, append-mostly data. Declare SMALLINT, INTEGER, BIGINT, DATE, TIMESTAMP, CHAR(n) or VARCHAR(n) columns, or leave out the primary key, and the table is analytic. An analytic table exists only in a paged database, and its columns can not be NULL. Each column is stored at its declared width. Text is dictionary-coded. Load it column by column with BulkLoadColumns (one transaction per call).

A single-table SELECT that the older parser refuses runs on the typed executor. The typed executor also runs on in-memory tables that have no NULL columns. It accepts AS aliases (and a table alias, FROM hits h), expressions, CASE, CAST, EXTRACT, DATE_TRUNC (Extended), REGEXP_REPLACE (Extended), the string and math functions, OFFSET, HAVING without GROUP BY, and GROUP BY or ORDER BY by alias, position or expression. DATE and TIMESTAMP results come back as strings. Its aggregates run on morsels of rows across all cores, with dense per-worker arrays for small key ranges, partitioned hash tables for large ones, and exact COUNT(DISTINCT).

using HoloDb;

var dir = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
Directory.CreateDirectory(dir);
var db = new HoloDbService(new HoloDbOptions { WalPath = Path.Combine(dir, "db.wal"), Paged = true });
db.Execute("CREATE TABLE hits (watch SMALLINT NOT NULL, day DATE NOT NULL, url VARCHAR(200) NOT NULL, n BIGINT NOT NULL)");
db.BulkLoadColumns("hits", new[] { "watch", "day", "url", "n" },
    new object[] { new short[] { 1, 2, 3 }, new int[] { 19000, 19000, 19001 }, new[] { "a", "b", "a" }, new long[] { 10, 20, 30 } }, 3);
var r = db.Execute("SELECT day, COUNT(*) AS visits, SUM(n) AS total FROM hits GROUP BY day ORDER BY day");
for (int i = 0; i < r.RowCount; i++) Console.WriteLine($"{r.GetValue(i, 0)} {r.GetValue(i, 1)} {r.GetValue(i, 2)}");
// 2022-01-08 2 30
// 2022-01-09 1 30
db.Close();

History: AS OF and sealed sums

Paged tables keep each committed change as a signed record, so a past state is the present state with the later changes taken back out. No copy of the table is kept.

using HoloDb;

var dir = Path.Combine(Path.GetTempPath(), "holodb-asof-demo");
var db = Database.Open(null);
db.CreatePaged("CREATE TABLE orders (id INT PRIMARY KEY, amount INT)", dir);
db.Execute("INSERT INTO orders (id, amount) VALUES (1, 10), (2, 20)");
long before = db.CommitSequence;                                  // the sequence after the insert
db.Execute("UPDATE orders SET amount = 99 WHERE id = 2");

var now = db.Execute("SELECT COUNT(*), SUM(amount) FROM orders");
var past = db.Execute($"SELECT COUNT(*), SUM(amount) FROM orders AS OF {before}");
Console.WriteLine($"now: {now.GetValue(0, 1)}, as of {before}: {past.GetValue(0, 1)}");

var sealPast = db.Seal("orders", asOf: before);                   // the table's seal as it was after commit `before`
Console.WriteLine($"seal as of {before}: {sealPast.Rows} rows");
db.Close();
  • SELECT ... FROM t AS OF n reads the table as it was after commit n. AS OF LATEST is the present state. SHOW SEQUENCE returns the current sequence and the floor; Database.CommitSequence and Database.HistoryFloor(table) return the same numbers.
  • Exact at every n at or above the floor: COUNT, SUM, MIN, MAX, GROUP BY, DISTINCT and row reads. COUNT and SUM without a WHERE, GROUP BY on one INT key and MIN/MAX with a sorted index on the column start from the present answer and apply the overlay's signed correction. Other shapes scan the table and apply the same correction to the rows that changed. EXPLAIN names the route and says why a shape scans.
  • Seal(table, asOf: n) returns the table's seal as it was after commit n.
  • Refused by name, never answered approximately: a join, NEAREST, an in-memory table, a plane-only table, n above the current sequence, n below the floor, n strictly inside a retained segment, and a read inside a transaction that has written the table.
  • Retention is bounded by the structure budget: older history collapses into segments. An ALTER TABLE ... ADD COLUMN is a barrier: it moves the floor, and an AS OF read below the floor is refused by name.
  • Seal(table) is maintained as rows change, for paged and in-memory tables, so reading it is O(1). Seal(table, verify: true) re-derives it from the rows and repairs any drift. A key range scans the rows.

Loading data and change batches

ImportCsv

long ImportCsv(string table, string path, bool header = true, char delimiter = ',')

Loads a CSV file into an existing table (RFC 4180: quoted fields, doubled quotes, delimiters and newlines inside quotes) and returns the rows loaded. With header the first record names the columns, in any order; without it the fields are the table's columns in order. An unquoted empty field is NULL for a NULL column, an empty text for TEXT and a refusal for a number. One atomic load: a bad field refuses the whole file by name with its line number and nothing is loaded. It is a method, not a statement, so a SQL client cannot make the server read a file.

BulkLoad and BulkLoadColumns

int BulkLoad(string table, IReadOnlyList<string> cols, IReadOnlyList<Array> data, int count[, IReadOnlyList<bool[]?>? nulls])
int BulkLoadColumns(string table, IReadOnlyList<string> cols, IReadOnlyList<object> data, int count)   // analytic and paged
long PagedBulkLoad(string table, IEnumerable<object?[]> rows, int commitEvery)                          // paged

BulkLoad appends typed arrays (long[] for INT, double[] for REAL, string[] for TEXT) without the SQL parser. It validates every key first, so a duplicate primary key leaves the table unchanged. A column left out of cols takes its type's zero, or NULL if declared NULL. The nulls argument gives one mask per column, true for NULL. BulkLoadColumns takes long[], int[], short[], double[], string[] or Utf8Column for analytic tables. The bulk load forms refuse a table that holds a decimal column.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE users (id INT PRIMARY KEY, age INT, name TEXT)");
var cols = new[] { "id", "age", "name" };
var data = new Array[] { new long[] { 10, 11 }, new long[] { 40, 22 }, new[] { "Bo", "Cy" } };
int n = db.BulkLoad("users", cols, data, 2);
Console.WriteLine(n);
// 2

Change batches: ApplyChanges

A replica or a feed sends an ordered stream of upserts and deletes. ApplyChanges(table, changes) applies a batch atomically on an in-memory table: one write lock, one log commit, and a reader sees the table before the batch or after it, never part of it. A paged table refuses ApplyChanges. RowChange.Upsert(row) carries a full row, with every column, and a null for a NULL column. RowChange.Delete(key) deletes by primary key, and a missing key is a counted no-op. Changes count in order, the last change of a key wins, and only the net effect is applied. ChangeResult has Inserted, Updated, Deleted, MissingDeletes and Applied. A failure leaves the table as it was.

The SQL form INSERT ... ON CONFLICT (pk) DO UPDATE SET ... (Extended dialect) changes only the listed columns of an existing row, which is the way to keep the other columns of a row. It is in-memory only.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE orders (id INT PRIMARY KEY, region TEXT, total INT)");

var r = db.ApplyChanges("orders", new[]
{
    RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 1L, ["region"] = "north", ["total"] = 40L }),
    RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 2L, ["region"] = "south", ["total"] = 15L }),
    RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 1L, ["region"] = "north", ["total"] = 45L }),
    RowChange.Delete(2L),
    RowChange.Delete(99L),
});
Console.WriteLine($"{r.Inserted} inserted, {r.Updated} updated, {r.Deleted} deleted, {r.MissingDeletes} missing");
// 2 inserted, 1 updated, 1 deleted, 1 missing

Text keeps every character it was given, including line breaks and backslashes. A batch is one log commit group, so one call with several changes is one fsync.

Several tables in one step

ApplyChanges(IReadOnlyList<TableChanges>) applies the changes of several in-memory tables as one atomic step, with one write lock and one log commit. A reader sees every table before the batch or every table after it. Every table and value is checked before the first change, and a failure leaves all of them unchanged. You get one ChangeResult per table, in list order. It works inside START TRANSACTION and COMMIT.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE accounts (id INT PRIMARY KEY, balance INT)");
db.Execute("CREATE TABLE ledger (id INT PRIMARY KEY, account INT, amount INT)");
db.Execute("INSERT INTO accounts VALUES (1, 100), (2, 100)");

var results = db.ApplyChanges(new[]
{
    new TableChanges("accounts", new[]
    {
        RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 1L, ["balance"] = 70L }),
        RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 2L, ["balance"] = 130L }),
    }),
    new TableChanges("ledger", new[]
    {
        RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 1L, ["account"] = 1L, ["amount"] = -30L }),
        RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 2L, ["account"] = 2L, ["amount"] = 30L }),
    }),
});
Console.WriteLine($"{results[0].Updated} updated, {results[1].Inserted} inserted");
// 2 updated, 2 inserted

var both = db.Execute("SELECT SUM(accounts.balance), SUM(ledger.amount) FROM accounts JOIN ledger ON accounts.id = ledger.account");
Console.WriteLine($"{both.GetValue(0, 0)} {both.GetValue(0, 1)}");
// 200 0

Across two statements a batch can still be seen half applied. Only one statement, such as a join, reads one consistent state of several tables.

Comparing key sets: KeyDigest

KeyDigest(table, modulus) returns one KeyBucket (Count and an exact Int128 Sum) per bucket of an INT primary key, in one pass. Bucket b holds the keys whose value modulo modulus is b. Two systems that compute the same buckets can compare modulus numbers instead of every key, and list the keys of the buckets that differ. KeyDigest refuses a table that holds a decimal column.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE accounts (id INT PRIMARY KEY, balance INT)");
db.Execute("INSERT INTO accounts VALUES (1, 100), (2, 100), (6, 5)");

KeyBucket[] d = db.KeyDigest("accounts", 4);
Console.WriteLine($"bucket 1: {d[1].Count} key, sum {d[1].Sum}; bucket 2: {d[2].Count} keys, sum {d[2].Sum}");
// bucket 1: 1 key, sum 1; bucket 2: 2 keys, sum 8

Summary tables

A summary table keeps no rows. It stores only the aggregate plane, the value dictionaries of its dimensions and the position each feed has reached, so its size depends on the distinct values of its dimensions, not on how many rows went through it.

CREATE SUMMARY TABLE sales (region TEXT DIMENSION, channel TEXT DIMENSION, units INT MEASURE, revenue INT MEASURE)

A dimension is an INT or TEXT column you filter, group or count distinct on. A measure is an INT column you SUM or AVG. The columns are fixed when the table is created. Database.CreatePlaneOnly(...) is the same thing from code.

  • Writing. ApplyPlaneChanges(table, source, position, changes) takes PlaneChange.Add(row), PlaneChange.Remove(row) and PlaneChange.Replace(oldRow, newRow), as one atomic batch. A row is an array in the declared column order, or a dictionary with every column. A Remove carries the row's values, because the table has no rows to look them up in.
  • Exactly once. Each batch carries the position its source has reached. A batch at or below the position already applied for that source is skipped, and Skipped is true. The position is a number, or opaque bytes compared as unsigned numbers compared lexicographically (for example a 10-byte, big-endian log sequence number). It is saved in the same log commit as the counts. PlanePosition and PlanePositionBytes read it back.
  • No negative counts. A Remove of a row that was never added refuses the whole batch, and nothing changes.
  • Reading. COUNT, SUM and AVG of the measures. MIN, MAX and COUNT(DISTINCT) of a dimension. WHERE over dimensions. GROUP BY and HAVING over COUNT and SUM. ORDER BY and FETCH FIRST. Anything that needs a row (SELECT *, a filter on a measure, a join, an UPDATE) is refused with the reason in the message.
  • Totals and export. PlaneTotals(table) returns the row count and the exact sum of each measure. ExportPlane(table) returns the whole state as one blob with a checksum. ImportPlane(table, blob, replace) checks the blob before it changes anything.

The dimension caps (a maximum number of distinct values per dimension, and a maximum number of flag bits across all dimensions) are named in the refusal when a batch would pass them.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE SUMMARY TABLE sales (region TEXT DIMENSION, channel TEXT DIMENSION, units INT MEASURE, revenue INT MEASURE)");

var batch = new[]
{
    PlaneChange.Add(new object?[] { "north", "web", 3L, 30L }),
    PlaneChange.Add(new object?[] { "south", "shop", 5L, 50L }),
    PlaneChange.Add(new object?[] { "south", "web", 1L, 10L }),
};
Console.WriteLine(db.ApplyPlaneChanges("sales", "orders-feed", 100, batch).Skipped);   // False
Console.WriteLine(db.ApplyPlaneChanges("sales", "orders-feed", 100, batch).Skipped);   // True
db.ApplyPlaneChanges("sales", "orders-feed", 101, new[]
{
    PlaneChange.Replace(new object?[] { "south", "shop", 5L, 50L }, new object?[] { "south", "shop", 6L, 60L }),
});
foreach (var row in db.Execute("SELECT region, SUM(units), SUM(revenue) FROM sales GROUP BY region ORDER BY region").Rows)
    Console.WriteLine($"{row["region"]} {row["sum(units)"]} {row["sum(revenue)"]}");
// north 3 30
// south 7 70

var t = db.PlaneTotals("sales");
Console.WriteLine($"{t.Rows} rows, {t.Sums["units"]} units, position {db.PlanePosition("sales", "orders-feed")}");
// 3 rows, 10 units, position 101

try { db.Execute("SELECT * FROM sales"); }
catch (HoloException e) { Console.WriteLine(e.Message); }

Durable, larger-than-memory tables

A paged table keeps its pages in a buffer pool, and its changes in a write-ahead log. It is durable, and it can be larger than memory: pages that are not in the pool are read from data.pages, through a read-only memory map. Paged tables take the SQL reference with these exceptions: one INNER JOIN per statement on the join route (the row engine runs the rest), ApplyChanges (for one table or several) refuses a paged table, and INSERT ... ON CONFLICT is in-memory only. An analytic table has no NULL columns; a paged table with a nullable column stores its NULL cells (see NULL).

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
var dir = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
db.CreatePaged("CREATE TABLE big (id INT PRIMARY KEY, v INT)", dir);
db.PagedBulkLoad("big", new object?[][] { new object?[] { 1L, 100L }, new object?[] { 2L, 200L } });
Console.WriteLine(db.Execute("SELECT sum(v) FROM big").Rows[0]["sum(v)"]);
// 300
db.ClosePaged("big");

var reopened = db.OpenPaged(dir);
Console.WriteLine(db.Execute($"SELECT count(*) FROM {reopened}").Rows[0]["count(*)"]);
// 2
db.ClosePaged(reopened);
  • CreatePaged(createTableSql, dir, bufferFrames) creates a paged table in dir. bufferFrames is the pool size in pages.
  • OpenPaged(dir) reopens it and recovers committed state from its log. It returns the table name.
  • PagedBulkLoad(table, rows, commitEvery) streams rows and commits every commitEvery rows.
  • ClosePaged(table) checkpoints and closes one table.

Integrity

Every page of data.pages has a CRC-32C in data.crc. A page that fails its check throws a HoloException that names the page and the file, with the code data-page-checksum, so a flipped bit or a bad sector is an error and never a different answer. A non-empty store whose data.crc is missing or damaged is refused by name before any page is read (data-checksum-missing); its pages cannot be checked, so they are not trusted.

A statement may change more pages than the pool holds. The pool then writes the open transaction's least recently used pages to a scratch file, txn.spill, and deletes it when the transaction ends. A large UPDATE or DELETE completes, and a ROLLBACK or a power cut leaves the table as it was.

UPDATE and DELETE find their rows through the primary key, a sorted index or a group index when one proves the answer, and otherwise through the block scan. They adjust the stored totals by the difference.

Deletes and consolidation

A DELETE marks its rows and moves nothing. The stored totals, group indexes and learned block records are adjusted by the deleted values, so the cost follows the rows deleted, not the table. Every scan, index and join skips a deleted row. COUNT(*) and AVG count the rows that exist. A deleted key can be inserted again.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
var dir = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
db.CreatePaged("CREATE TABLE events (id INT PRIMARY KEY, g INT, v INT)", dir);
var rows = Enumerable.Range(1, 20000).Select(i => new object?[] { (long)i, (long)(i % 10), (long)i }).ToList();
db.PagedBulkLoad("events", rows);
db.Execute("DELETE FROM events WHERE g = 7");
Console.WriteLine(db.Execute("SELECT count(*) FROM events").Rows[0]["count(*)"]);
// 18000
long slots = db.Consolidate("events");
Console.WriteLine(slots > 0);
// True
Console.WriteLine(db.Execute("SELECT count(*) FROM events WHERE g = 7").Rows[0]["count(*)"]);
// 0
db.ClosePaged("events");

Deleted rows keep their place until the table is consolidated. The engine consolidates by itself, after a statement, once the scans that read the dead places have paid for the rewrite (Database.AutoConsolidate, on by default), or you call Consolidate(table), which returns the number of places removed. A consolidation moves rows into the holes and cuts the end off. A moved row keeps its id, and the learned records follow it exactly. Rows come back in a different order afterwards unless you ORDER BY.

Pages that a consolidation, a regroup or an emptied table frees go to a free map, and new rows take the lowest free page first. Free pages at the end of data.pages are cut off at the same checkpoint, and their checksums with them. The free pool is derived from the table when it is first written, so a crash leaves nothing to repair.

Whole-database paged mode

HoloDbOptions.Paged = true (or Database.Open(walPath, checkpointBytes, paged: true)) puts a new database on the paged engine. A plain CREATE TABLE then creates a paged table, and no CreatePaged call is needed. The option is for a database with no existing files at WalPath. Opening over an existing text log with paged: true throws. Database.IsPagedDefault reports the mode.

using HoloDb;

var dir = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
Directory.CreateDirectory(dir);
var walPath = Path.Combine(dir, "db.wal");
var pd = new HoloDbService(new HoloDbOptions { WalPath = walPath, Paged = true });
pd.Execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)");
pd.Execute("INSERT INTO t VALUES (1, 42)");
Console.WriteLine(pd.Database.IsPagedDefault);
// True
Console.WriteLine(pd.Execute("SELECT v FROM t WHERE id = 1").Rows[0]["v"]);
// 42
pd.Close();

What the engine builds for itself

Nothing here is declared. The engine builds a structure when the scans it would answer have paid for its build and for the writes that would keep it current, and it drops a structure when its maintenance costs more than the scans it saves, or when a write makes it stale. Every structure takes its bytes from one budget that scales with the data's own size. The cap is hard. A candidate that does not fit evicts the structures that save the fewest reads per byte. A declared structure that still does not fit is refused, with the amount it needs. Declared structures are never evicted. Answers are the same with any mix of structures, or none.

  • Aggregate summaries. A table's small-domain columns, summarised so that filters, grouping, COUNT(DISTINCT) and equi-joins are answered from the summary. The planner picks the dimensions and measures from the statements it sees. A summary on a paged table is built in the background and saved beside the table.
  • Sorted index on a paged table. An ordered list of the live rows' values, read in checksummed blocks. A range COUNT, MIN and MAX, and ORDER BY col DESC FETCH FIRST k ROWS ONLY, are answered from it without reading the table's pages. SUM over a range is not served from it.
  • Keyed summary. The exact count and sums for each value of a high-cardinality INT key, optionally split by a second column. It answers GROUP BY key with SUM and COUNT, ORDER BY ... FETCH FIRST, and an equality filter on the split column, and a single join with a dimension table on its primary key. For each join shape the planner has paid for, it keeps pre-grouped totals, which appended rows and new dimension rows update.
  • Holographic tree. A tree of exact counts and sums over key ranges, for a SUM over a key range or a GROUP BY on a key whose values are too spread for a keyed summary. It is built when a range sum or a group-by on that key has paid for it.
  • Row indexes in memory. A sorted index on an INT column, or an equality index on a TEXT column, for a selective point or range predicate on a large in-memory table.
  • Learned block records. For each WHERE clause the engine has seen, the number of matching live rows in each block. A block with no match is not read. Records are exact: an UPDATE or DELETE changes them by the rows it touches, and a move of rows takes the counts of the blocks it changed out of them. Each block also keeps the bounds of its columns, so a block outside a range is skipped.
  • Learned routes. A link is the set of blocks that held a match for a conjunction of filters on an earlier read. A later query follows the links whose filters it repeats, and reads only the blocks they name. Every link is checked when it is followed: a block whose first row id or checksum does not match is a stale hop, and the statement reruns without routing. A query that has no link is an ordinary scan.
  • Gravity. Rows that satisfy the same repeatedly-scanned predicates are written into the same blocks, by a regroup. Rows keep their ids. A regroup runs as upkeep, when the reads it would save are worth more than the rewrite.
  • Placement on write. An UPDATE or INSERT that gives a row new values moves it into a free slot of a block that the repeated filters already read, when that saves more reads than the move costs. The row keeps its id, and the learned records follow it exactly.

Control and inspection. SET summaries and SET indexes take auto, declared or off. declared keeps the structures you created and drops the ones the planner built. off keeps every structure but uses none. EXPLAIN names the structure that answered a statement, or the reason it did not, and SHOW SUMMARIES reports the state of each structure and what it is waiting for. Database.Regroup(table) runs a regroup now. Database.PlaceOnWrite turns placement on writes off.

Zone maps are store data: they are kept in the table's page store and are written with the data they describe. Learned routing links, predicate counts and key-presence records are saved in learned.idx beside the table. A saved file is trusted at open only when it matches the table's change stamp. A stale or damaged file is discarded and its contents are learned again by use, and SHOW SUMMARIES gives the reason for a structure that was discarded.

// Extended dialect: CREATE INDEX, EXPLAIN and DROP INDEX are Extended statements.
using HoloDb;

var db = new HoloDbService(new HoloDbOptions { Dialect = SqlDialect.Extended });
db.Execute("CREATE TABLE sales (id INT PRIMARY KEY, region TEXT, tier INT, qty INT)");
db.Execute("INSERT INTO sales VALUES (1,'north',1,10),(2,'north',2,-4),(3,'south',1,7),(4,'east',3,5),(5,'south',2,9)");
db.Execute("SET summaries = declared");
db.Execute("CREATE INDEX by_region ON sales (region, tier) INCLUDE (qty) USING SUMMARY");
foreach (var r in db.Execute("EXPLAIN SELECT SUM(qty) FROM sales WHERE region = 'north' OR tier = 3").Rows)
    Console.WriteLine($"{r["kind"]}: {r["route"]}");
foreach (var r in db.Execute("SHOW SUMMARIES ON sales").Rows)
    Console.WriteLine($"{r["name"]} {r["origin"]} {r["state"]}");
db.Execute("DROP INDEX by_region");

The aggregate plane (Pleroma)

An optional aggregate plane, held in Phasor Pleroma stores, summarises the small-domain columns of one table. It is derived from the columns, updated by every write, and answers WHERE aggregates over any combination of AND, OR, NOT, LIKE, IN and BETWEEN on its dimensions, multi-column GROUP BY with HAVING, COUNT(DISTINCT dimension), MIN and MAX of a dimension, and one equi-join COUNT or SUM between two tables that both have a plane. Sums are exact past 64 bits: the plane raises an error rather than wrap, and PleromaSumExact returns the exact 128-bit total.

A plane that is attached with EnablePleroma, or built by the planner, answers only what its dimensions and measures cover. Anything else goes to the engine, so a statement returns the same rows with the plane on or off.

using HoloDb;

var db = Database.Open();
db.Execute("CREATE TABLE sales (id INT PRIMARY KEY, region TEXT, tier INT, qty INT)");
db.Execute("INSERT INTO sales (id, region, tier, qty) VALUES (1,'north',1,10),(2,'north',2,-4),(3,'south',1,7),(4,'east',3,5),(5,'south',2,9)");

db.EnablePleroma("sales", new PleromaPlaneOptions(new[] { "region", "tier" }, new[] { "qty" }));
db.PleromaMode = PleromaMode.Always;

var r = db.Execute("SELECT COUNT(*), SUM(qty) FROM sales WHERE region = 'north' OR tier = 3");
Console.WriteLine($"{r.Rows[0]["count(*)"]} {r.Rows[0]["sum(qty)"]}");
// 3 11
r = db.Execute("SELECT COUNT(DISTINCT region) FROM sales");
Console.WriteLine(r.Rows[0]["count(distinct region)"]);
// 3
db.Execute("DELETE FROM sales WHERE id = 4");
Console.WriteLine(db.Execute("SELECT COUNT(DISTINCT region) FROM sales").Rows[0]["count(distinct region)"]);
// 2
Console.WriteLine(db.PleromaVerify("sales") ?? "plane equals a rebuild from the columns");
// plane equals a rebuild from the columns
Console.WriteLine(db.PleromaSumExact("SELECT SUM(qty) FROM sales"));
// 22

PleromaMode has three values. Auto (the default) leaves whole-table counts and sums, and single-column GROUP BY, to the engine's maintained totals. Always serves every shape the plane covers. Off bypasses the plane while it is still kept current. PleromaVerify rebuilds the plane from the columns and compares it, and returns null when they agree. PleromaStats reports sizes and the number of statements served and declined.

A plane created from SQL with CREATE INDEX ... USING SUMMARY (Extended), and one the planner builds, is logged and saved with the database, and survives a restart. A plane attached with EnablePleroma is not saved: call EnablePleroma again after Database.Open. EnablePleroma refuses a table that holds a decimal column.

The Pleroma page store

PleromaPageStore keeps many exact Pleroma pages of one shape as the data itself, durably and larger than memory. PleromaSparsePageStore does the same for sparse pages over a large vocabulary. Each page carries a Phasor check prime, so a damaged record is detected, and records are grouped with exact-sum parity records, so a lost page or one damaged record per group is rebuilt by Repair. Readers do not wait for a writer that is working on other pages.

using HoloDb;
using Phasor;

var shape = new HologramShape { KeyCapacity = 2, KeyCycle = 110, CompleteKeys = true };
var dir = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());

using (var store = PleromaPageStore.Open(dir, shape))
{
    store.AddBatch(new[] { new PageAdd(42, 5, 3), new PageAdd(42, 77, -2), new PageAdd(7, 5, 1) });
    store.AppendEvent("context 42 seen"u8);
    Console.WriteLine($"{store.Counts(42)[5]} {store.Counts(42)[77]}");
    // 3 -2
}

using (var store = PleromaPageStore.Open(dir, shape))
{
    Console.WriteLine($"{store.Counts(42)[5]} {store.Counts(7)[5]}");
    // 3 1
    store.ReplayEvents((seq, bytes) => Console.WriteLine($"{seq} {System.Text.Encoding.UTF8.GetString(bytes)}"));
    // 2 context 42 seen
    Console.WriteLine(store.Verify().Clean);
    // True
}
  • Writes are journaled before they are applied, and a page remembers the last batch applied to it, so a crash replays each logged add exactly once. A torn journal tail is discarded, so a batch survives whole or not at all.
  • Restore points. Checkpoint() returns a point and RestoreTo(id) returns the store to exactly that state, without a recount. A restore point is copy-on-write.
  • Events. AppendEvent stores an opaque record in the same sequence as the batches. ReplayEvents returns them in order, and TrimEvents drops an absorbed prefix.
  • Repair. Verify checks every record and parity group. Repair rebuilds a lost page or a damaged record from its group. A rebuilt page must pass its own check before it is written.

The store's shape must be CompleteKeys and admissible for a check prime. One process opens a store at a time, and a batch is applied page by page, so a reader can see a batch on some pages and not on others.

Statement limits: cancel, deadline and result budget

Execute(sql, StatementLimits) bounds one statement. Each of the three limits is off unless you set it.

  • Cancel: a CancellationToken. A cancelled statement stops at its next block or morsel boundary and throws HoloCancelledException with Reason set to Cancelled.
  • Timeout: a TimeSpan measured from the start of the statement. The same exception is thrown with Reason set to TimedOut.
  • ResultBytes: a byte budget. The engine estimates the size of the result and of the working rows it creates, and refuses the statement with HoloResultTooLargeException before it allocates the block that would pass the budget. Limit is the budget and Needed is the estimate that was reached.

A stopped read returns nothing. A stopped write has no effect: the statement is undone exactly, as a failed statement is. Inside a transaction, a stopped statement undoes only itself, and the earlier statements of the transaction stay.

using HoloDb;

var db = Database.Open();
db.Execute("CREATE TABLE r (id INT PRIMARY KEY, a INT)");
db.Execute("INSERT INTO r (id, a) VALUES (1, 10), (2, 20)");

using var cts = new CancellationTokenSource();
try
{
    var rows = db.Execute("SELECT id, a FROM r WHERE a > 5", new StatementLimits
    {
        Cancel = cts.Token,
        Timeout = TimeSpan.FromSeconds(5),
        ResultBytes = 64L << 20,
    });
    Console.WriteLine(rows.RowCount);
}
catch (HoloCancelledException e)
{
    Console.WriteLine($"stopped: {e.Reason}");
}
catch (HoloResultTooLargeException e)
{
    Console.WriteLine($"refused: budget {e.Limit} bytes, estimate {e.Needed} bytes");
}

Two database-wide settings apply the same limits to every Execute(sql) call that has no per-call limits: db.StatementTimeout (a TimeSpan?, null by default) and db.DefaultResultBytes (a long, 0 by default). Set them before the database is used.

The budget counts what the engine allocates for a statement, so it bounds memory as well as the result. A join is charged for every row of each table it decodes, before WHERE is applied, so a join over large tables can be refused even when its WHERE would keep few rows. A GROUP BY is charged for each group it creates. The estimate is 8 bytes for each INT or REAL cell and 48 bytes for each TEXT, BOOL, DATE or TIMESTAMP cell, and 64 bytes plus 48 per cell for each boxed row. The engine's final column copy is an extra transient of about the same size.

Cancellation and the budget are checked inside the scans, joins, sorts and group builders. A sort or a hash build of one very large set runs as one call to the sort routine; the check happens between its comparisons at a fixed interval, not inside a single comparison.

Costs and limits

This is the one place the package states its costs. Each is a current behaviour.

  • Deleting the last row that holds a column's minimum or maximum re-reads that column. A sorted key or group index that already knows the next value avoids the re-read.
  • A DELETE or UPDATE of a column that a sorted index or keyed summary reads is applied to it as a signed delta, so the structure stays current. A write the structure cannot follow drops it: a key outside a keyed summary's range, or more deleted rows than a sorted index holds as deltas. ALTER TABLE ... ADD COLUMN drops a sorted index. Until the planner has paid for a rebuild, the scans run without it.
  • Learned records and routes help when a repeated predicate matches rows that sit together. A predicate whose rows are spread evenly over every block gains nothing from them.
  • The first lookup of a literal that no earlier read has seen is a full scan, unless the dictionary or the column's range proves no row can match: a text value absent from the column, or a number outside its range, reads no block. A number inside the range is still a full scan on the first read.
  • Routes, predicate counts and key-presence records are saved in learned.idx beside the table. After a write that the saved file does not cover, they are learned again by use.
  • Consolidation and regroup run as upkeep, in slices. A statement that arrives during a slice waits for that slice, not for the whole job. Scattered holes cost the page each one sits in.
  • The holographic tree is built in slices under the shared lock. Its final merge is one pass that is not sliced, and only the publish takes the exclusive lock.
  • Paged tables take one INNER JOIN per statement on the join route (the row engine runs the rest). ApplyChanges and summary tables are in-memory only, and ApplyChanges refuses a paged table.
  • Without ORDER BY the row order is unspecified. After a DELETE, or any write that moves a row, it can differ from the order before it.
  • Sums past 64 bits throw.
  • NEAREST from a few thousand rows up is approximate, and its ranking is not guaranteed to order every pair of far values by distance.
  • The aggregate plane declines what it does not cover. A plane attached with EnablePleroma is not kept across a restart.
  • A table with a decimal column runs on the row engine for its reads and refuses the statements listed under DECIMAL.

Dependency injection

public static IServiceCollection AddHoloDb(this IServiceCollection services, Action<HoloDbOptions>? configure = null)

AddHoloDb registers a singleton HoloDbService. It needs only IServiceCollection, from Microsoft.Extensions.DependencyInjection.Abstractions. ServiceCollection and BuildServiceProvider come from the full Microsoft.Extensions.DependencyInjection package, which ASP.NET Core and the generic host already include. A console project adds it with dotnet add package Microsoft.Extensions.DependencyInjection.

using HoloDb;
using Microsoft.Extensions.DependencyInjection;

var services = new ServiceCollection();
services.AddHoloDb(o => o.WalPath = null);
var provider = services.BuildServiceProvider();
var db = provider.GetRequiredService<HoloDbService>();
db.Execute("CREATE TABLE ping (id INT PRIMARY KEY)");
db.Execute("INSERT INTO ping VALUES (1)");
Console.WriteLine(db.Execute("SELECT count(*) FROM ping").Rows[0]["count(*)"]);
// 1

EvalApp pipeline integration

public static IAppBuilder WithHoloDb(this IAppBuilder builder, HoloDbService service)

WithHoloDb sets a HoloDbContext, an EvalApp global context that wraps the service, as the app's context. A step that derives from ContextPureStep<HoloDbContext, ...> reaches the service through global.Service. An app has one global context. When the app needs the database and other things too, put a HoloDbContext inside your own context object and pass that to WithContext.

using EvalApp;
using HoloDb;

var service = new HoloDbService(new HoloDbOptions());
service.Execute("CREATE TABLE users (id INT PRIMARY KEY, name TEXT)");
service.Execute("INSERT INTO users VALUES (1, 'Ada')");

var services = new Services(new HoloDbContext(service));

Eval.App("Catalog")
    .WithContext(services)
    .DefineDomain("Catalog")
        .DefineTask<LookupQuery>("LookupUser")
            .AddStep<LookupUserStep>("Lookup")
        .Run(out ICompiledPipeline<LookupQuery> pipeline)
    .Build();

var outcome = await pipeline.RunAsync(new LookupQuery(1));
if (outcome is PipelineResult<LookupQuery>.Success ok) Console.WriteLine(ok.Data.Name);
// Ada

public sealed record LookupQuery(long Id, string Name = "");

public sealed class Services(HoloDbContext db) : GlobalContext
{
    public HoloDbContext Db { get; } = db;
}

public sealed class LookupUserStep : ContextPureStep<Services, LookupUserStep.NoDomain, LookupQuery>
{
    public sealed class NoDomain : DomainContext { }
    protected override ValueTask<LookupQuery> TransformAsync(LookupQuery data, Services global, NoDomain domain, CancellationToken ct)
    {
        var r = global.Db.Service.Database.Execute($"SELECT name FROM users WHERE id = {data.Id}");
        return ValueTask.FromResult(data with { Name = (string)r.Rows[0]["name"] });
    }
}

HoloDbService.ExecuteAsync and ApplyChangesAsync run the synchronous engine on a thread-pool thread, and do not go through the pipeline. Inside a step, call the synchronous Database as above.

Usage metering

MeteredDatabase wraps a Database and reports, for each query, the work it did: the rows and bytes it read, and whether it was served from a maintained total without reading rows.

public readonly record struct QueryUsage(long RowsScanned, long TableRows, long RowBytes, long BilledBytes, double ElapsedMs)
{
    public long BytesActuallyRead { get; }   // RowsScanned * RowBytes
    public bool ConstantTime { get; }         // true iff RowsScanned == 0
}
public sealed class MeteredDatabase
{
    public MeteredDatabase(Database db, HoloPricing? pricing = null);
    public (QueryResult Result, QueryUsage Usage) Run(string sql);
    public double OurCostUsd(QueryUsage u);
    public double ScanCostUsd(QueryUsage u, ScanPricing engine);
}

ScanPricing and HoloPricing are records that hold a price per tebibyte and a minimum bill per query. The ScanPricing names are BigQuery, Athena and All. Database.LastRowsScanned, LastTableRows, LastRowBytes and LastLogicalBytes are the per-thread counters Run reads.

using HoloDb;
using HoloDb.Analytics;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)");
db.Execute("INSERT INTO t VALUES (1, 100), (2, 200)");
var metered = new MeteredDatabase(db.Database);
var (res, usage) = metered.Run("SELECT sum(v) FROM t");
Console.WriteLine(usage.ConstantTime);
// True
Console.WriteLine(metered.OurCostUsd(usage) <= metered.ScanCostUsd(usage, ScanPricing.BigQuery));
// True

Parallel fan-out (HoloPar)

HoloPar.Cpu is the parallel primitive the engine fans out through: Map(chunks, body) runs the body once per chunk. You do not need to call it, since every query path that benefits already does. Each call site learns its own cost, and runs inline when a fan-out would not pay. Nothing in this package is gated.

using HoloDb;

var sum = 0; var gate = new object();
HoloPar.Cpu.Map(4, i => { lock (gate) sum += i; });
Console.WriteLine(sum);
// 6

Engine building blocks

These types are public in the assembly, but none of them is needed to use the SQL surface. They are the building blocks the engine is made of.

Type What it is
Holo (static) The row encoding: EncodeRow(Schema, row) binds the columns into a holographic vector, and Similarity(a, b) is the cosine similarity. NEAREST ranks with these.
HoloLsh The locality-sensitive index that NEAREST builds over row encodings once a table is large enough: Add, Approx and Exact top-k.
GrowColumn<T> An append-only column array with a published count, which readers can see without a lock.
Table, Schema, Column, ColType, LiveMetrics, GroupRow The in-memory table primitives Database builds on. Build tables through SQL, not with new Table(schema).
using HoloDb;

var schema = new Schema(new[] { new Column("id", ColType.Int, true), new Column("tag", ColType.Text, false) });
var v1 = Holo.EncodeRow(schema, new Dictionary<string, object?> { ["tag"] = "red" });
var v2 = Holo.EncodeRow(schema, new Dictionary<string, object?> { ["tag"] = "red" });
Console.WriteLine(Holo.Similarity(v1, v2) > 0.99);
// True

Exceptions

Every error the engine raises on purpose is a HoloException. Catch that type.

public class HoloException : Exception
{
    public string? Code { get; }     // set for a plane-only cap, a refused plane blob, a refused statement or a data page that fails its checksum
    public string SqlState { get; }  // five characters, from Code
    public string? Dimension { get; }
    public int? Cap { get; }
    public int? Needed { get; }
}

Code takes the values in HoloErrorCodes. The plane codes are PlaneDimensionCap, PlaneJointCap, PlaneBlobCorrupt and PlaneBlobVersion. The SQL surface adds, among others:

  • extension-disabled (SQLSTATE 0A000, HoloErrorCodes.ExtensionDisabled): a vendor extension under the Standard dialect. See SQL dialect.
  • DivisionByZero (22012) and NumericOverflow (22003): decimal and integer arithmetic.
  • data-page-checksum: a damaged data page. data-checksum-missing: a store whose data.crc is missing or damaged.
  • table-format: a table record of a format this build does not read.

The statement limits raise two subclasses: HoloCancelledException (Reason, a StatementStopReason) and HoloResultTooLargeException (Limit and Needed, both bytes). See Statement limits.

File layout

  • In memory (WalPath = null): nothing is written to disk.
  • Text log (WalPath set, an in-memory database): WalPath is an append-only log of committed statements, and WalPath + ".snap" is the latest snapshot. Checkpoint() folds the log into a new snapshot and empties the log.
  • Paged table (CreatePaged(sql, dir), or whole-database paged mode): one directory per table.
    • Keep these together. They are the data and its record: data.pages (the pages), wal.log (the ring of deltas not yet written into data.pages), table.meta12 (the table record, format 15), and data.crc (one CRC-32C per data page). Without data.crc a non-empty store is refused.
    • txn.spill and stmt.spill: scratch space for a transaction or a statement larger than the pool. They exist only while the statement or transaction runs.
    • learned.idx: the learned routing links, predicate counts and key-presence records. It is derived: a file that does not match the table is discarded and the records are learned again.
    • Declaration files (*.decl) hold the definitions of the indexes you created with CREATE INDEX (Extended dialect). They are not derived. Back them up with the table, and do not delete them.
    • Other derived files, such as the sorted index files, keyed summary files, planner-built summaries and the holographic tree files, are rebuilt from the columns when they are missing or stale.

License

Proprietary: this package ships the compiled library only; see LICENSE for the full terms. Installing and using unmodified copies for internal evaluation and non-commercial development or testing is licensed; commercial, production, or revenue-generating use requires a separate commercial licence from the owner. No feature on this page is gated behind a tier or a licence key. Every capability described works identically with or without one. Copyright (c) 2026 Dongyang Stephen Chen, trading as Evaluated Applications. All rights reserved.

Product Compatible and additional computed target framework versions.
.NET net8.0 is compatible.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed.  net9.0 was computed.  net9.0-android was computed.  net9.0-browser was computed.  net9.0-ios was computed.  net9.0-maccatalyst was computed.  net9.0-macos was computed.  net9.0-tvos was computed.  net9.0-windows was computed.  net10.0 is compatible.  net10.0-android was computed.  net10.0-browser was computed.  net10.0-ios was computed.  net10.0-maccatalyst was computed.  net10.0-macos was computed.  net10.0-tvos was computed.  net10.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (4)

Showing the top 4 NuGet packages that depend on EvaluatedApplications.HoloDb:

Package Downloads
EvaluatedApplications.Prose

Grammar-driven synthetic corpus generator: mines nouns, verbs and adjectives and the syntax that joins them from real text with the rules of grammar, stores them in HoloDb, and recombines them into new grammatical sentences built only from words and pairings the text used, plus reading-comprehension Q&A pairs (single-sentence, multi-sentence passage, and two-turn). Rules-first (deterministic, zero-training) part-of-speech tagging, chunking and role assignment; mining fans out across cores through EvalApp (generation does too, opt-in); an optional mini-HoloFormer scorer re-ranks generated candidates by next-lemma likelihood once trained (POS tagging stays 100% rules-based).

EvaluatedApplications.HoloDb.Protocol

The wire contract for HoloDb clients and servers: length-prefixed TCP framing plus a columnar-preserving binary codec that encodes requests and decodes responses as typed column arrays (long[], double[], string[]) instead of JSON. Includes protocol version negotiation and three exception types for version skew, malformed frames, and server errors. Use this if you are implementing a HoloDb server or building a custom client; most applications should use EvaluatedApplications.HoloDb.Client instead.

EvaluatedApplications.HoloGenome

Encode DNA as phasor holograms whose geometry comes from measured DNA physics. Each base is a Phasor symbol face carried on a helical phase that advances by published, sequence-dependent dinucleotide twist (Olson et al. 1998, built in), optionally with a local DNA-shape layer (minor groove width, propeller twist, roll, helix twist) read from a pentamer table you supply. Windows of sequence become fixed-width vectors you can store in HoloDb, compare, or feed to any regression. Also grades sequences against your own measurements, with no files or console so it runs in a browser: a zero-parameter helical score, a cross-validated ridge readout on the holograms, and a fitted phasor model that reports the helical period (bases per turn), each with a correlation and a block-resampled 95% interval. Research and education only: it makes no health, disease-risk or drug-response claims. Experimental (0.x).

EvaluatedApplications.PleromaMind

A persistent conversational unit built on exact holograms, created empty from a design you choose and taught from your own text; the package ships no voice and no text. Its alphabet is Prism's 320 tokens (characters, common words and chat tags) plus subwords you can mint from your own text. It learns every exchange it hears and says, plus anything taught or ingested by reference, into exact per-conversation counts for the context lengths you pick. Memory grows with the text learned (ModelDesign.Estimate gives the cost by formula). It has a conversation memory for long matches (its trust can be calibrated on your own text and is journaled) and an optional learned sharing layer (token classes and solved trust) that learns only when you call LearnStep, with Compare scoring held-out text with and without it; it offers N sampled replies to pick from without learning anything; takes bounded and fading approve or disapprove feedback without touching memory; forgets a conversation exactly; and rebuilds its whole state bit for bit from its journal on a HoloDb page store, including after a crash. Every choice can be inspected per context length.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last Updated
21.0.2 0 11/21/2026
21.0.1 0 11/21/2026
21.0.0 0 11/21/2026
20.0.0 0 11/20/2026
19.1.0 0 11/20/2026
19.0.0 8 11/20/2026
18.2.0 11 11/20/2026
18.1.7 43 11/20/2026
18.1.6 47 11/20/2026
18.1.5 44 11/20/2026
18.1.4 55 11/19/2026
18.1.3 52 11/19/2026
18.1.2 42 11/19/2026
18.1.1 43 11/19/2026
18.1.0 48 11/19/2026
18.0.0 46 11/19/2026
17.2.2 39 11/19/2026
17.2.1 43 11/19/2026
17.2.0 48 11/19/2026
17.1.1 53 11/19/2026
Loading failed