Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

The Embedded Library

The crate turborel is the database itself, as a library: a Rust program that embeds it holds a database in its own process, and runs SQL through it with no server between. What a server's client does over a connection, the program does by calls: a Database, and a Session of it for each authorization identifier that runs statements. Each example below is a whole program, and runs as printed.

The database

Database::new makes a database held in memory, which ends with the program. Database::open opens the one kept in a directory, making it if there is none, and recovering it if it was not closed (Databases); open_files does the same through a FileSystem and OpenOptions of the program's own. A database kept in a directory is closed by close, with no transaction open: it writes a checkpoint and gives the directory up. export_manifest lists the durable files of a database kept in a directory as its last checkpoint left them, each with its length and owner, but its log's and its catalog's: what a copy of the database needs besides its log and its catalog. A file in its directory of no kind the database knows is refused (SQLCODE -910), as a copy would leave it behind.

A Database is a handle: cloned, it is the same database, and it may be shared by any number of threads. Its settings are set by set_setting, as SET DATABASE sets them, a setting read as the database opens set before open_files (Settings).

use std::path::PathBuf;

use turborel::db::Database;
use turborel::value::Value;

fn main() -> Result<(), turborel::error::SqlError> {
    let dir: PathBuf = std::env::temp_dir().join(format!("turborel-manual-{}", std::process::id()));
    let db = Database::open(&dir)?;
    db.schema("CREATE SCHEMA AUTHORIZATION ANN CREATE TABLE T (K INTEGER)")?;
    db.session("ANN").run("INSERT INTO T VALUES (1)")?;
    db.close()?;
    drop(db);
    let again = Database::open(&dir)?;
    let page = again.setting("STORAGE.PAGE_BYTES")?;
    assert_eq!(page.to_string(), "8192");
    assert!(matches!(page, Value::Exact(_)));
    again.close()?;
    std::fs::remove_dir_all(&dir).ok();
    Ok(())
}

The session that inserted the row was dropped at the end of its statement's line, its transaction committed as SESSION.COMMIT_AT_END says, so the row was kept.

Sessions

Database::schema runs a <schema definition> as the database's operator, for any owner. Database::session gives a session acting as an authorization identifier: what it may do is what that identifier is granted, as for a server's client connected as that user. run runs a statement, any but a query, which is read by a cursor, and gives its Completion: Success, NoData, or a Warning of its SQLSTATE. The first statement that reads or changes data begins a transaction; commit and rollback end it, as COMMIT and ROLLBACK do. A commit that fails with SQLCODE -922 is in doubt: its commit record could not be synced, so whether it committed is known only once the database is opened again; it is not to be run again before then, as it may have committed. A session dropped ends its transaction as the setting SESSION.COMMIT_AT_END says: committed, by default.

A session is used by one thread at a time; it may move to another between calls. Each thread of a program has its own sessions.

Database::set_owner makes a user the database's owner, unchecked, as the program holding the database may. Session::set_owner does it, as ALTER DATABASE does, for a session of the owner alone, outside a transaction (SQLCODE -104 for another's, -917 in a transaction, -103 for a name not a user's): the owner hands the database on, and its sessions go on as the owner no more (Users).

reseal_table("AUTH.TABLE"), as ALTER TABLE ... RESEAL does, seals a table's rows again through the encodings its option ENCODINGS names now, in the session's transaction, as its schema's owner alone may: committed and checkpointed, nothing of the table is read through a key it named before (Encryption).

A statement that fails gives an SqlError: its code, sqlcode() and sqlstate(), its message, the rule of the standard it cites, and where in the statement the fault is. Shown, it is as the client shows it (Appendix A).

use turborel::db::{Completion, Database};

fn main() -> Result<(), turborel::error::SqlError> {
    let db = Database::new();
    db.schema("CREATE SCHEMA AUTHORIZATION ANN CREATE TABLE STAFF (ID INTEGER PRIMARY KEY, NAME VARCHAR(30))")?;
    let ann = db.session("ANN");
    assert_eq!(ann.run("INSERT INTO STAFF VALUES (1, 'Ada'), (2, 'Grace')")?, Completion::Success);
    assert_eq!(ann.run("DELETE FROM STAFF WHERE ID = 9")?, Completion::NoData);
    ann.commit()?;
    let refused = ann.run("DELETE FROM NOPE").unwrap_err();
    assert_eq!((refused.sqlcode(), refused.sqlstate().to_string()), (-103, "2A000".to_owned()));
    assert_eq!(
        refused.to_string(),
        "SQLCODE -103 (SQLSTATE 2A000): table ANN.NOPE is not defined [X3.135-1986 §5.4 SR5] (at character 12)"
    );
    Ok(())
}

Reading rows

open_cursor runs a query and gives a Cursor of its rows: its columns, each of a name, if it has one, and a type; and fetch, the next row, a Vec of Values, or none after the last. Rows are read as they are fetched, in the session's transaction, not gathered first. direct runs any statement as a server runs its client's: a query gives a cursor, Direct::Rows; any other statement its completion, Direct::Done.

use turborel::db::{Database, Direct};
use turborel::value::Value;

fn main() -> Result<(), turborel::error::SqlError> {
    let db = Database::new();
    db.schema("CREATE SCHEMA AUTHORIZATION ANN CREATE TABLE STAFF (ID INTEGER, NAME VARCHAR(30))")?;
    let ann = db.session("ANN");
    ann.run("INSERT INTO STAFF VALUES (1, 'Ada'), (2, 'Grace'), (3, NULL)")?;
    let mut rows = ann.open_cursor("SELECT ID, NAME FROM STAFF ORDER BY ID")?;
    assert_eq!(rows.columns[1].name.as_deref(), Some("NAME"));
    let mut names = Vec::new();
    while let Some(row) = rows.fetch()? {
        names.push(if matches!(row[1], Value::Null) { "null".to_owned() } else { row[1].to_string() });
    }
    assert_eq!(names, ["Ada", "Grace", "null"]);
    assert!(matches!(ann.direct("UPDATE STAFF SET NAME = 'Edsger' WHERE ID = 3")?, Direct::Done(_)));
    assert!(matches!(ann.direct("VALUES 1")?, Direct::Rows(_)));
    Ok(())
}

explain gives the text of a query's plan that EXPLAIN shows as rows, without running it, its parameters of the values a Params gives. physical_plan gives the physical plan alone.

Parameters

A program's own values reach a statement as parameters, as SQL-86's module language passes them: Params declares each, by name, of a type and a value, and may give it an indicator, which a null value read into it sets to -1. execute runs a statement with them: a name in it that is a parameter's is the parameter, so parameters are best named apart from columns. SELECT ... INTO and FETCH ... INTO set them; value reads one back.

use turborel::db::Database;
use turborel::params::Params;
use turborel::value::{DataType, Decimal, Spelled, Value};

fn main() -> Result<(), turborel::error::SqlError> {
    let db = Database::new();
    db.schema("CREATE SCHEMA AUTHORIZATION ANN CREATE TABLE STAFF (ID INTEGER, NAME VARCHAR(30))")?;
    let ann = db.session("ANN");
    let integer = DataType::Exact { precision: 9, scale: 0, spelled: Spelled::Integer };
    let mut p = Params::new()
        .declare("NEW_ID", integer, Value::Exact(Decimal::new(4, 0)))
        .declare("NEW_NAME", DataType::varying(30), Value::Char("Barbara".into()))
        .declare("OUT_NAME", DataType::varying(30), Value::Null)
        .indicator("OUT_IND", 0);
    ann.execute("INSERT INTO STAFF VALUES (NEW_ID, NEW_NAME)", &mut p)?;
    ann.execute("SELECT NAME INTO OUT_NAME INDICATOR OUT_IND FROM STAFF WHERE ID = NEW_ID", &mut p)?;
    assert_eq!(p.value("OUT_NAME").to_string(), "Barbara");
    assert_eq!(p.indicator_value("OUT_IND"), Some(0));
    Ok(())
}

Cursors

A cursor of the program's is declared by declare_cursor, not run as a statement; OPEN, FETCH, CLOSE and UPDATE or DELETE ... WHERE CURRENT OF then run by execute, with the program's parameters (DECLARE CURSOR). A FETCH past the last row completes with no data.

use turborel::db::{Completion, Database};
use turborel::params::Params;
use turborel::value::{DataType, Spelled, Value};

fn main() -> Result<(), turborel::error::SqlError> {
    let db = Database::new();
    db.schema("CREATE SCHEMA AUTHORIZATION ANN CREATE TABLE STAFF (ID INTEGER, SALARY DECIMAL(9,2))")?;
    let ann = db.session("ANN");
    ann.run("INSERT INTO STAFF VALUES (1, 7200), (2, 8100)")?;
    let mut p = Params::new().declare("MY_ID", DataType::Exact { precision: 9, scale: 0, spelled: Spelled::Integer }, Value::Null);
    ann.declare_cursor("DECLARE C CURSOR FOR SELECT ID FROM STAFF WHERE ID > 1 FOR UPDATE OF SALARY")?;
    ann.execute("OPEN C", &mut p)?;
    assert_eq!(ann.execute("FETCH C INTO MY_ID", &mut p)?, Completion::Success);
    assert_eq!(p.value("MY_ID").to_string(), "2");
    ann.execute("UPDATE STAFF SET SALARY = SALARY + 100 WHERE CURRENT OF C", &mut p)?;
    assert_eq!(ann.execute("FETCH C INTO MY_ID", &mut p)?, Completion::NoData);
    ann.execute("CLOSE C", &mut p)?;
    let mut rows = ann.open_cursor("SELECT SALARY FROM STAFF WHERE ID = 2")?;
    assert_eq!(rows.fetch()?.map(|r| r[0].to_string()), Some("8200.00".to_owned()));
    Ok(())
}

Prepared statements

prepare prepares a statement once, its dynamic parameters written ?: parameters gives the type each takes, columns a query's columns. execute_prepared runs it with values for them, in order; open_prepared runs a prepared query, and gives its cursor. A prepared statement stays valid across transactions. A query, an INSERT, and a searched or positioned UPDATE or DELETE keep their plan between executions, a query also when SQL text opens it as a dynamic cursor: each execution puts its values in the kept plan and chooses indexes by them. A query, a searched UPDATE or a searched DELETE that finds one row by a unique key, WHERE K = ? of a PRIMARY KEY or UNIQUE column K, keeps its access path too, so its executions choose nothing. Another query keeps its access paths once five executions in a row choose the same ones, as WHERE K BETWEEN ? AND ? does whose ranges are short, and is then planned no more, until a table it reads gains or loses a tenth of its rows and 50 more, when it looks again; one whose values keep choosing other paths is planned at each execution after twenty. The plan is made again when the definitions change (a table altered, an index made or dropped, a local temporary table declared, a privilege granted or revoked, the transaction's own uncommitted changes among them), when a setting of the database or of the session is set, or when the session's schema, authorization identifier or time zone changes; a plan that holds the statement's instant, of CURRENT_TIMESTAMP in the statement or a view it reads, or of a DEFAULT it writes, is made at each execution. plans_made counts the plans a statement's executions made.

use turborel::db::Database;
use turborel::value::{Decimal, Value};

fn main() -> Result<(), turborel::error::SqlError> {
    let db = Database::new();
    db.schema("CREATE SCHEMA AUTHORIZATION ANN CREATE TABLE STAFF (ID INTEGER, NAME VARCHAR(30))")?;
    let ann = db.session("ANN");
    let insert = ann.prepare("INSERT INTO STAFF VALUES (?, ?)")?;
    assert_eq!(insert.parameters().len(), 2);
    for (id, name) in [(1, "Ada"), (2, "Grace")] {
        ann.execute_prepared(&insert, &[Value::Exact(Decimal::new(id, 0)), Value::Char(name.into())])?;
    }
    let by_id = ann.prepare("SELECT NAME FROM STAFF WHERE ID = ?")?;
    let mut rows = ann.open_prepared(&by_id, &[Value::Exact(Decimal::new(2, 0))])?;
    assert_eq!(rows.fetch()?.map(|r| r[0].to_string()), Some("Grace".to_owned()));
    assert_eq!(insert.plans_made(), 1);
    Ok(())
}

A statement may be prepared in SQL text too, by name, its text and its values the program's parameters, as execute runs any statement: PREPARE, EXECUTE and the other statements of dynamic SQL, SQL descriptor areas among them (DESCRIBE).

use turborel::db::Database;
use turborel::params::Params;
use turborel::value::{DataType, Decimal, Spelled, Value};

fn main() -> Result<(), turborel::error::SqlError> {
    let db = Database::new();
    db.schema("CREATE SCHEMA AUTHORIZATION ANN CREATE TABLE STAFF (ID INTEGER, NAME VARCHAR(30))")?;
    let ann = db.session("ANN");
    let integer = DataType::Exact { precision: 9, scale: 0, spelled: Spelled::Numeric };
    let mut p = Params::new()
        .declare("TEXT", DataType::varying(200), Value::Char("INSERT INTO STAFF VALUES (?, ?)".into()))
        .declare("MY_ID", integer, Value::Exact(Decimal::new(1, 0)))
        .declare("MY_NAME", DataType::varying(30), Value::Char("Ada".into()));
    ann.execute("PREPARE ADD_ONE FROM TEXT", &mut p)?;
    ann.execute("EXECUTE ADD_ONE USING MY_ID, MY_NAME", &mut p)?;
    p.set("TEXT", Value::Char("SELECT NAME FROM STAFF WHERE ID = ?".into()));
    ann.execute("PREPARE NAME_OF FROM TEXT", &mut p)?;
    p.set("MY_NAME", Value::Null);
    ann.execute("EXECUTE NAME_OF INTO MY_NAME USING MY_ID", &mut p)?;
    assert_eq!(p.value("MY_NAME").to_string(), "Ada");
    Ok(())
}

Large objects

A CLOB or BLOB value is a Value::Lob, a turborel::value::lob::Lob. Lob::of_text and Lob::of_octets make one of what is in memory, to pass as a parameter. A value read from a table holds no octets, only where its chunks are. Its reader reads a range of it by read, or gives its pieces in order by each, a chunk at a time. Such a value is valid while the statement's snapshot is: the transaction at REPEATABLE READ and SERIALIZABLE, the statement at READ COMMITTED.

A session also gives locators, as a server gives its clients. locator_of gives one of a value, which keeps the transaction's snapshot until it ends, so the value stays readable under READ COMMITTED too. read_lob reads by one, free_locator ends one and hold_locator keeps one past the commit, as FREE LOCATOR and HOLD LOCATOR do. create_lob gives a locator of an empty value that append_lob adds to a piece at a time, written to the database's temporary store past a chunk; Lob::of_locator passes it as a parameter.

use turborel::db::Database;
use turborel::value::Value;
use turborel::value::lob::{Lob, LobKind};

fn main() -> Result<(), turborel::error::SqlError> {
    let db = Database::new();
    db.schema("CREATE SCHEMA AUTHORIZATION ANN CREATE TABLE NOTES (ID INTEGER, BODY CLOB)")?;
    let ann = db.session("ANN");
    let l = ann.create_lob(LobKind::Character);
    for piece in ["Ada wrote ", "the first program."] {
        ann.append_lob(l, piece.as_bytes())?;
    }
    let insert = ann.prepare("INSERT INTO NOTES VALUES (2, ?)")?;
    ann.execute_prepared(&insert, &[Value::Lob(Lob::of_locator(LobKind::Character, l, (0, 0)))])?;
    ann.commit()?;
    let mut rows = ann.open_cursor("SELECT BODY FROM NOTES")?;
    let Some(row) = rows.fetch()? else { panic!("no row") };
    let Value::Lob(body) = &row[0] else { panic!("not a CLOB") };
    let locator = ann.locator_of(body.clone());
    assert_eq!(ann.read_lob(locator, (0, 3), true)?, b"Ada");
    ann.free_locator(locator)?;
    Ok(())
}

Functions

A program may add scalar functions, and set functions, of its own: register_scalar_function takes an implementation of the trait ScalarFunction, of a name, the type of its result for the types of its arguments, and the value of a call; register_aggregate_function a set function's, and replaces a standard one of its name. Several scalar functions may share a name as overloads, if each declares the types of its parameters (ScalarFunction::parameter_types) and no call could invoke two of them alike. A call then invokes the one its arguments' types choose, as an SQL-invoked function's does, and each argument is stored in its parameter's type first. Any other second function of a name already registered, a standard one's such as UPPER too, is refused (SQLCODE -910), and the first stays. Overloads share the settings under their name, each as the first to declare it declared it. A function of the session's default schema that takes a call's arguments is invoked before a registered one of its name, and one of as many parameters as a call has arguments takes the call from a registered aggregate of its name, whatever their types. An index method that knows a predicate's function by its name should look at its parameter types too. A call of a registered function, NAME(arguments), names no function of a schema, so it is no routine invocation of the standard (extension): a session takes it while its setting SQL.FUNCTION_CALLS is ON, as it is by default, and refuses it while it is OFF, as SQL does (SQLCODE -102). The setting is checked as a definition is made. A view, constraint, trigger, domain, generated column or routine that calls a registered function goes on calling it while a session using it has the setting OFF, after the database is opened again or recovered, and on a replica. CREATE and ALTER check the setting of the session writing the new definition. A function whose deterministic returns false is refused in a CHECK, an assertion, a domain's constraint, a generated column and an index's key part (SQLCODE -103), as the standard refuses a routine that is possibly non-deterministic there.

use turborel::db::Database;
use turborel::error::{Code, Result, fail};
use turborel::functions::ScalarFunction;
use turborel::value::{DataType, Value};

/// TWICE(n): n added to itself.
struct Twice;

impl ScalarFunction for Twice {
    fn name(&self) -> &str {
        "TWICE"
    }
    fn return_type(&self, args: &[DataType]) -> Result<DataType> {
        match args {
            [exact @ DataType::Exact { .. }] => Ok(exact.clone()),
            _ => fail(Code::SyntaxRule, "TWICE takes one exact number"),
        }
    }
    fn call(&self, args: &[Value]) -> Result<Value> {
        match args {
            [Value::Exact(n)] => Ok(Value::Exact(n.checked_add(*n)?)),
            _ => Ok(Value::Null),
        }
    }
}

fn main() -> Result<()> {
    let db = Database::new();
    db.register_scalar_function(Box::new(Twice))?;
    let ann = db.session("ANN");
    let mut rows = ann.open_cursor("VALUES TWICE(21)")?;
    assert_eq!(rows.fetch()?.map(|r| r[0].to_string()), Some("42".to_owned()));
    ann.set_setting("SQL.FUNCTION_CALLS", &Value::Char("OFF".to_owned()))?;
    assert_eq!(ann.run("VALUES TWICE(21)").unwrap_err().sqlcode(), -102);
    Ok(())
}

A function may also declare the type of each argument, by argument_type: an argument of another type is then stored in it as a value is in a column of that type, a number given the declared scale, a character string made a value of a type beyond the standard that takes one; one that cannot be is refused before the statement runs (SQLCODE -103); return_type is told the declared types, and a ? there takes the declared type. A function declaring none is given its arguments as they are. One function is registered under a name, whatever its arguments.

use turborel::db::Database;
use turborel::error::{Code, Result, fail};
use turborel::functions::ScalarFunction;
use turborel::value::{DataType, Decimal, Spelled, Value};

/// WIDTH(s): the characters of s, stored in a CHARACTER(5).
struct Width;

const FIVE: DataType = DataType::character(5);

impl ScalarFunction for Width {
    fn name(&self) -> &str {
        "WIDTH"
    }
    fn argument_type(&self, _position: usize, _args: &[Option<DataType>]) -> Option<DataType> {
        Some(FIVE)
    }
    fn return_type(&self, args: &[DataType]) -> Result<DataType> {
        match args {
            [FIVE] => Ok(DataType::Exact { precision: 5, scale: 0, spelled: Spelled::Numeric }),
            _ => fail(Code::SyntaxRule, "WIDTH takes one string"),
        }
    }
    fn call(&self, args: &[Value]) -> Result<Value> {
        match args {
            [Value::Char(s)] => Ok(Value::Exact(Decimal::new(s.chars().count().try_into().unwrap_or(0), 0))),
            _ => Ok(Value::Null),
        }
    }
}

fn main() -> Result<()> {
    let db = Database::new();
    db.register_scalar_function(Box::new(Width))?;
    let ann = db.session("ANN");
    let mut rows = ann.open_cursor("VALUES WIDTH('abc')")?;
    assert_eq!(rows.fetch()?.map(|r| r[0].to_string()), Some("5".to_owned()));
    assert_eq!(ann.open_cursor("VALUES WIDTH(1)").err().map(|e| e.sqlcode()), Some(-103));
    Ok(())
}

A function is called from any thread a statement runs on, so it is Send and Sync, and guards any state of its own.

Table functions and procedures (extension)

register_table_function takes a TableFunction, which a FROM clause invokes as TABLE(NAME(arguments)) AS T. It names the types of its parameters and its columns, and gives its rows. The tables it reads are named by queries, made of the arguments known as the statement is bound (TableFunction::reads), so a table's name is a literal there, not a ?. Each query is checked with the privileges of the statement's user, and runs in its transaction. Its rows are given to the function. The table function may read the table references before it, as a lateral derived table does.

register_procedure takes a Procedure, invoked by CALL NAME(arguments), unqualified. It runs in the caller's session and transaction through a ProcedureContext, whose run runs a statement and rows a query, each with ? taking the values given. A CALL that fails is undone whole. The procedures of SQL/MM Spatial's catalog are built this way (Spatial Data).

use turborel::db::Database;
use turborel::error::Result;
use turborel::functions::builtin::{Procedure, ProcedureContext, TableFunction};
use turborel::value::{DataType, Decimal, Spelled, Value};

const INTEGER: DataType = DataType::Exact { precision: 10, scale: 0, spelled: Spelled::Integer };

/// CALL KEEP(n): n kept in table A.KEPT.
#[derive(Debug)]
struct Keep;

impl Procedure for Keep {
    fn name(&self) -> &str {
        "KEEP"
    }
    fn parameters(&self, count: usize) -> Option<Vec<DataType>> {
        (count == 1).then(|| vec![INTEGER])
    }
    fn call(&self, args: &[Value], cx: &mut dyn ProcedureContext) -> Result<()> {
        cx.run("INSERT INTO A.KEPT VALUES (?)", args)
    }
}

/// TABLE(UPTO(n)): the integers 1 to n.
#[derive(Debug)]
struct UpTo;

impl TableFunction for UpTo {
    fn name(&self) -> &str {
        "UPTO"
    }
    fn parameters(&self, count: usize) -> Option<Vec<DataType>> {
        (count == 1).then(|| vec![INTEGER])
    }
    fn columns(&self) -> Vec<(String, DataType)> {
        vec![("N".to_owned(), INTEGER)]
    }
    fn rows(&self, args: &[Value], _read: &[Vec<Vec<Value>>]) -> Result<Vec<Vec<Value>>> {
        let n = match args {
            [Value::Exact(n)] => n.units,
            _ => 0,
        };
        Ok((1..=n).map(|k| vec![Value::Exact(Decimal::new(k, 0))]).collect())
    }
}

fn main() -> Result<()> {
    let db = Database::new();
    db.register_procedure(Box::new(Keep))?;
    db.register_table_function(Box::new(UpTo))?;
    db.schema("CREATE SCHEMA AUTHORIZATION A CREATE TABLE KEPT (N INTEGER)")?;
    let a = db.session("A");
    a.run("CALL KEEP(2)")?;
    let mut rows = a.open_cursor("SELECT COUNT(*) FROM KEPT, TABLE(UPTO(3)) AS T WHERE T.N <= KEPT.N")?;
    assert_eq!(rows.fetch()?.map(|r| r[0].to_string()), Some("2".to_owned()));
    Ok(())
}

XML names

turborel::xml maps SQL identifiers to XML Names and back, as SQL/XML defines it and as XMLELEMENT, XMLFOREST and XMLMAP map names. A program invokes the mappings directly, as an SQL statement does with XMLNAME_TO_IDENTIFIER and IDENTIFIER_TO_XMLNAME (SQL/XML):

  • name_of(identifier, Escape::Partial) or Escape::Full: the XML Name of an identifier's characters. A character no XML 1.1 name holds is written _xHHHH_, or _xHHHHHH_ beyond the Basic Multilingual Plane. Fully escaped, a colon and a leading xml are escaped too.
  • name_of_parts(&[...]): the XML Name of a name of several parts, each fully escaped and its periods escaped, joined by periods.
  • identifier_of(name): the characters of the identifier an XML Name stands for, each escape of an assigned character read as it.
  • delimited(identifier): the delimited identifier of those characters, in double quotes, each double quote doubled.
use turborel::xml::{Escape, delimited, identifier_of, name_of, name_of_parts};

fn main() {
    assert_eq!(name_of("my column", Escape::Partial), "my_x0020_column");
    assert_eq!(name_of("a:b", Escape::Full), "a_x003A_b");
    assert_eq!(name_of_parts(&["S", "T.1"]), "S.T_x002E_1");
    assert_eq!(identifier_of("my_x0020_column"), "my column");
    assert_eq!(delimited(&identifier_of("say_x0020__x0022_hi_x0022_")), "\"say \"\"hi\"\"\"");
}

Engines and other plug-ins

Each extension point is a trait, whose implementation the program registers on the database, by name, before the database opens its files, so the definitions that name one find it:

Registered by What
register_engine a storage engine, StorageEngine: what keeps a table's rows, named by CREATE TABLE ... USING, and by the setting TABLES.ENGINE
register_index_method an index method, IndexMethod, named by CREATE INDEX ... USING
register_type a data type beyond the standard's, SqlType, named in a column definition and CAST: a value of it is CAST from a character string, and a character string is stored in it as in a character column only if the type says it takes one (assigns_text); no privilege is needed to use it
register_scalar_function, register_aggregate_function functions, as above
register_table_function, register_procedure table functions and procedures written in Rust, as above
register_encoding an encoding of files or tables' rows, Encoding, named by STORAGE.ENCODINGS and a table's ENCODINGS
register_hash_function a hash function for checksums, HashFunction, named by STORAGE.CHECKSUM
register_system_table a table of the Information Schema's kind, SystemTable
register_ranking a ranking of full-text search, Ranking, named by a full-text index's FULLTEXT.RANKING (Full-text rankings)
register_metric a distance metric of vectors, turborel::vector::metric::VectorMetric, named by VECTOR_DISTANCE and a vector index (extension) (Vectors); its name a regular identifier in upper case, or a schema's name and its own joined by a period, none built in or registered, or it fails with SQLCODE -910, as a call does of a result that breaks its declared properties
register_quantizer a quantizer of vectors, turborel::vector::quantizer::Quantizer, named by a vector index (extension); named as a metric is, without the period
register_embedder an embedding model's plug-in, turborel::vector::embed::Embedder, shared as an Arc, named by a model's option TYPE and by EMBED and CHUNK_TEXT (extension) (CREATE EMBEDDING MODEL); its name in upper case and not registered, or it fails with SQLCODE -301. Vectors waiting for it are then given
set_diagnostics where the database's events go, Diagnostics (Diagnostics); tell gives it one of the program's own, as a server's, those below DIAGNOSTICS.LEVEL dropped

A plug-in of any kind above, and a catalog's or a log's storage, the credentials, a server's connections, a spill, a log (set_log) and a clock (set_clock), may declare settings of the database's, under its own name (a log, a sink or a clock giving none is named log_plugin, diagnostics_plugin or clock_plugin), as the column store declares COLUMNSTORE.HEADER_CACHE_BYTES: it gives itself as its Configurable (configurable), whose settings it declares. It is told their values as it is registered, as each changes and as the database opens (Configurable::configure), and never calls back into the database to read them; a setting outside its name, or a built-in one's, is refused, and the plug-in not registered. with_optimizer adds rules to the optimizer (Rule), each with its setting, OPTIMIZER and its name, which turns it on or off, as the built-in rules' do (Settings).

The built-in engines are rowstore, the default, and columnstore (Table Options); the built-in index methods are btree, hash, rtree, fulltext, and xml_path, xml_value, xml_property and xml_primary, whose lookups Index::xml_rows gives, and the primary's answers Index::xml_answers. Each trait's contract, what the database calls and what an implementation must keep, is in the crate's own documentation, of the module the trait is in.

ScalarFunction::call_borrowed receives references to existing argument values and the same temporary large-object store as call_with. The executor can use it for direct unary and binary row expressions and scalar comparisons over batches. The default copies the arguments and calls call_with once, so existing implementations need no changes. An override can avoid those copies but must preserve results, errors, side effects and large-object handling. References are valid only for the call; retaining a value requires owning it.

ScalarFunction::specialize may prepare an equivalent function using constant arguments discovered by constant folding. It must preserve the function's type contracts, results and runtime errors, including when a later execution supplies different arguments. Preparation has no visible side effects; an invalid constant must be left for runtime because a CASE branch or an empty input may never evaluate it. An already prepared function returns no further specialization. Geometry relationships use this hook to retain a decoded constant operand within the expression. Polygon constants may also retain immutable interval indexes for point location. They use a separate memory reservation; if it is refused, the decoded operand still serves ordinary relationship evaluation. The hook receives the database's MemoryBudget; retained state must own a reservation and decline preparation if it cannot obtain one. A reused physical plan prepares calls after filling that execution's parameters. Indexed function joins may also call the hook for each outer row, with its column values fixed for that probe. The full condition and its argument expressions are still evaluated for each candidate pair. Disabling or removing the constant-folding rule disables these steps too. For spatial probes, the declared operation lets the planner recognize an equivalent prepared function when learning a reusable plan. Ordinary function probes still require the same function object, not just its name.

use turborel::db::Database;

fn main() -> Result<(), turborel::error::SqlError> {
    let db = Database::new();
    db.schema("CREATE SCHEMA AUTHORIZATION ANN CREATE TABLE EVENTS (ID INTEGER, KIND CHAR(8)) USING columnstore")?;
    let ann = db.session("ANN");
    ann.run("INSERT INTO EVENTS VALUES (1, 'start')")?;
    let engine = "SELECT DISTINCT ENGINE_NAME FROM INFORMATION_SCHEMA.TABLE_OPTIONS WHERE TABLE_NAME = 'EVENTS'";
    let mut rows = ann.open_cursor(engine)?;
    assert_eq!(rows.fetch()?.map(|r| r[0].to_string().trim_end().to_owned()), Some("columnstore".to_owned()));
    Ok(())
}

Indexes for function calls (extension)

A plug-in index can find rows for a WHERE filter written as a function call. The example below registers NEXT_TO(a, b), true when a is one greater than b, and an index method that supports this query:

SELECT N FROM ITEMS WHERE NEXT_TO(N, ?);

With the parameter set to 40, the example index returns rows containing 40, 41 and 42. These are candidates: the database fetches them and checks the full WHERE condition. Only 41 passes. This permits an index to return extra rows while requiring it to find every true match. For example, a spatial index could find shapes by their bounding boxes and leave the exact function to reject the extra matches (RULES.md D34).

The example defines its own integer function and index. An application must register both before using them; NEXT_TO is not a built-in function. A plug-in supplies the search for an arbitrary Boolean function. The built-in geometry lookup has the separate contract described under Spatial data.

The function and index opt in separately:

API What the implementation promises
ScalarFunction::index_predicate True means every call accepted by the function's type checks returns BOOLEAN or null without an error or side effect. Equal inputs give equal answers, and either argument being null gives null. deterministic must also return true.
IndexMethod::supports_predicate True means every index it creates on the given key types returns every match for the registered function, indexed column and argument position. Support covers every accepted value of the other argument, including null.
Index::search with Probe::Predicate Return matching inserted (Key, RowId) pairs, each once. Extra candidates are allowed. Keys retain inserted values unless Index::key_projection declares a projection. The executor rechecks the visible row version.

Both opt-ins default to false and their promises hold for the lifetime of the registered implementations. The planner recognizes calls with two arguments: one indexed column and one constant or parameter, in either order. Predicate::column is the column's position in the index key; Predicate::argument is its position in the call, 0 or 1. Probe::Predicate::value supplies the other argument. The example method below supports the column in the first argument only.

An index stream must retain its storage until it closes. This applies to search, key_rows and nearest: an existing cursor may continue reading after the index is dropped, without another catalog snapshot keeping the index alive. Reclaim its pages only when the last reader releases it.

The planner compares the lookup cost with a scan. Index::estimate counts every candidate, including those the WHERE check rejects. If it returns None, the planner prices fetching every table row. If probe_pages returns None, the planner uses its usual estimate of a tree descent. Each prepared execution supplies its current parameter to both the lookup and the WHERE check, including when it reuses a learned plan (D45). EXPLAIN shows the function lookup and the filter above it.

The lookup must preserve results and errors:

  • If the function, an argument expression or another part of the WHERE filter can raise, the table is read whole. Skipping a row must not hide an error (D15).
  • Every candidate is fetched and checked against the whole WHERE condition. FALSE and unknown discard the row. This lookup never uses an index-only scan.
  • An index of collation sort keys cannot supply the function's original column values, so it is not eligible for a function lookup.
  • If another key column may be null, the index must hold null keys to avoid missing matches. The planner keeps the requested sort when those nulls could survive the filter (D34, D66).
  • The executor records a read of the whole table, including nulls, so conflict checks catch rows inserted or changed by another transaction. Storage checks never call the function while holding their locks (D29, R-ERR-4).

The traits are Send + Sync. Implementations protect their own state when sessions call them concurrently and never call back into a database or session (D27, D42, R-ERR-4).

This complete program wraps a B-link tree to return the three integer candidates described above. Its estimate of three candidates makes the planner choose the lookup over scanning the 201 rows. It also executes the prepared statement with a null argument and checks that the plan contains both IndexScan and the WHERE filter.

use std::sync::Arc;
use turborel::db::Database;
use turborel::error::{Code, Result, SqlError};
use turborel::functions::ScalarFunction;
use turborel::index::{BTreeMethod, Entries, Index, IndexMethod, Key, KeyOrder, Predicate, Probe};
use turborel::params::{ParameterType, Params};
use turborel::storage::RowId;
use turborel::value::{CompOp, DataType, Decimal, Spelled, Value};

const FUNCTION: &str = "NEXT_TO";
const METHOD: &str = "next_candidates";
const INTEGER: DataType = DataType::Exact {
    precision: 10,
    scale: 0,
    spelled: Spelled::Integer,
};
const CANDIDATES: usize = 3;
const EXTRA: i128 = 2;
const ROWS: i128 = 200;

fn integer(t: &DataType) -> bool {
    matches!(t, DataType::Exact { scale: 0, .. })
}

struct NextTo;
impl ScalarFunction for NextTo {
    fn name(&self) -> &str {
        FUNCTION
    }
    fn return_type(&self, args: &[DataType]) -> Result<DataType> {
        if args.len() != 2 || !args.iter().all(integer) {
            return Err(SqlError::new(
                Code::SyntaxRule,
                "[EXTENSION] NEXT_TO takes two integers",
            ));
        }
        Ok(DataType::Boolean)
    }
    fn parameter_type(
        &self,
        _position: usize,
        _args: &[Option<DataType>],
    ) -> Option<ParameterType> {
        Some(ParameterType::Of(INTEGER))
    }
    fn index_predicate(&self) -> bool {
        true
    }
    fn call(&self, args: &[Value]) -> Result<Value> {
        if args.iter().any(Value::is_null) {
            return Ok(Value::Null);
        }
        let [Value::Exact(a), Value::Exact(b)] = args else {
            return Err(SqlError::new(
                Code::Api,
                "[DESIGN] NEXT_TO requires two integer values",
            ));
        };
        Ok(Value::Bool(a.units.checked_sub(b.units) == Some(1)))
    }
}

struct CandidateMethod;
impl IndexMethod for CandidateMethod {
    fn name(&self) -> &'static str {
        METHOD
    }
    fn supports(&self, _op: CompOp) -> bool {
        false
    }
    fn supports_predicate(&self, p: &Predicate, columns: &[DataType]) -> bool {
        let one_integer = columns.len() == 1 && columns.first().is_some_and(integer);
        p.function.0.name() == FUNCTION && p.column == 0 && p.argument == Some(0) && one_integer
    }
    fn create(&self, columns: &[DataType]) -> Result<Box<dyn Index>> {
        Ok(Box::new(CandidateIndex(Arc::from(
            BTreeMethod::in_memory().create(columns)?,
        ))))
    }
}

struct CandidateIndex(Arc<dyn Index>);
impl Index for CandidateIndex {
    fn insert(&self, key: Key, row: RowId) -> Result<()> {
        self.0.insert(key, row)
    }
    fn remove(&self, key: &Key, row: RowId) -> Result<()> {
        self.0.remove(key, row)
    }
    fn rows(&self, key: &Key) -> Result<Vec<RowId>> {
        self.0.rows(key)
    }
    fn estimate(&self, _probe: &Probe) -> Option<usize> {
        Some(CANDIDATES)
    }
    fn probe_pages(&self, _probe: &Probe) -> Option<f64> {
        Some(1.0)
    }
    fn search(self: Arc<Self>, probe: &Probe, order: KeyOrder) -> Entries {
        let Probe::Predicate { value, .. } = probe else {
            return Arc::clone(&self.0).search(probe, order);
        };
        let Value::Exact(n) = value else {
            return Box::new(std::iter::empty());
        };
        // D34: include n and n + 2 so the WHERE check must reject them.
        let high = Value::Exact(Decimal::new(n.units.saturating_add(EXTRA), 0));
        let candidates = Probe::Range {
            equal: Vec::new(),
            low: Box::new((CompOp::Ge, value.clone())),
            high: Box::new((CompOp::Le, high)),
        };
        Arc::clone(&self.0).search(&candidates, order)
    }
}

fn main() -> Result<()> {
    let db = Database::new();
    db.register_scalar_function(Box::new(NextTo))?;
    db.register_index_method(Box::new(CandidateMethod))?;
    db.schema("CREATE SCHEMA AUTHORIZATION ANN CREATE TABLE ITEMS (N INTEGER)")?;
    let ann = db.session("ANN");
    for n in 0..ROWS {
        ann.run(&format!("INSERT INTO ITEMS VALUES ({n})"))?;
    }
    ann.run("INSERT INTO ITEMS VALUES (NULL)")?;
    ann.commit()?;
    ann.run("CREATE INDEX NEAR_ITEMS ON ITEMS (N) USING next_candidates")?;
    let query = ann.prepare("SELECT N FROM ITEMS WHERE NEXT_TO(N, ?)")?;
    let mut rows = ann.open_prepared(&query, &[Value::Exact(Decimal::new(40, 0))])?;
    while let Some(row) = rows.fetch()? {
        if let Some(value) = row.first() {
            println!("{value}");
        }
    }
    let mut unknown = ann.open_prepared(&query, &[Value::Null])?;
    println!(
        "null argument: {} rows",
        usize::from(unknown.fetch()?.is_some())
    );
    let shown = ann.physical_plan("SELECT N FROM ITEMS WHERE NEXT_TO(N, 40)", &Params::new())?;
    println!("predicate index: {}", shown.contains("IndexScan"));
    println!("recheck: {}", shown.contains("Filter NEXT_TO"));
    Ok(())
}

Output:

41
null argument: 0 rows
predicate index: true
recheck: true

Full-text rankings (extension)

Rank scores a document by BM25, or by the ranking its column's full-text index names in FULLTEXT.RANKING (Full-Text Search). A program adds a ranking with register_ranking, an implementation of turborel::fulltext::rank::Ranking. Its name is a regular identifier in upper case, not one built in or registered already, or registering it fails with SQLCODE -910. An index may name it from then on, and Rank of such an index fails with -910 in a database opened without it.

A pattern's rank is the sum, over the terms its words match in the document, of the ranking's weight of each. The database gives it the collection's statistics as the statement first read them, as a Corpus, a TermStats of the term and an InDocument of the term in the document. The ranking keeps this contract.

  • weight and bound are deterministic, bit for bit, finite, never negative, and never panic.
  • weight(c, t, d) <= bound(c, t) for every document. The index reads the best rows of a top-k by these bounds, and fails a statement with SQLCODE -910 where a weight passes its bound, so a top-k is never wrong.
  • Neither calls back into the database or its sessions, nor holds a lock past its return.

This ranking counts each occurrence of a matched term once:

use turborel::db::Database;
use turborel::fulltext::rank::{Corpus, InDocument, Ranking, TermStats};

/// Counts each occurrence of a matched term once, whatever the collection.
struct Occurrences;

impl Ranking for Occurrences {
    fn name(&self) -> &str {
        "OCCURRENCES"
    }

    fn weight(&self, _corpus: &Corpus, _term: &TermStats, in_doc: &InDocument) -> f64 {
        f64::from(in_doc.frequency)
    }

    fn bound(&self, _corpus: &Corpus, term: &TermStats) -> f64 {
        f64::from(term.most)
    }
}

fn main() -> Result<(), turborel::error::SqlError> {
    let db = Database::new();
    db.register_ranking(Box::new(Occurrences))?;
    db.schema("CREATE SCHEMA AUTHORIZATION ANN CREATE TABLE DOCS (ID INTEGER PRIMARY KEY, BODY VARCHAR(200))")?;
    let ann = db.session("ANN");
    ann.run("INSERT INTO DOCS VALUES (1, 'fox fox fox'), (2, 'a fox and a dog'), (3, 'a cat')")?;
    ann.run("COMMIT")?;
    ann.run("CREATE INDEX DOCS_BODY ON DOCS (BODY) USING FULLTEXT WITH (FULLTEXT.RANKING = OCCURRENCES)")?;
    let mut rows = ann.open_cursor("SELECT ID, RANK(BODY, '\"fox\"') FROM DOCS ORDER BY ID")?;
    while let Some(row) = rows.fetch()? {
        println!("{} {}", row[0], row[1]);
    }
    Ok(())
}

Output:

1 3E0
2 1E0
3 0E0

Partition strategies (extension)

A partitioned table divides its rows among its partitions by the strategy PARTITION BY names (Partitioned Tables). RANGE, LIST and HASH are built in. A program adds a strategy with register_partition_strategy, an implementation of turborel::partition::PartitionStrategy. Its name is a regular identifier in upper case, not one built in or registered already, or registering it fails with SQLCODE -910. A table may name it from then on. In a database opened without it, such a table keeps its rows and is read whole, and a row written to it fails with SQLCODE -103.

The database owns the DEFAULT partition, which no strategy sees, and gives the strategy each partition's bound as its bound checked it. The strategy keeps this contract, which test/integration/partitioning/contract.rs checks of each built-in one.

  • bound accepts only the forms it reads, never DEFAULT, and gives a bound the other methods take.
  • Of siblings whose bounds do not overlaps, contains is true of at most one for a key, and route gives that one.
  • admits is true of every bound that contains a key meeting every restriction a read gives. It may be true of others, never false of one of these, so a read never misses a row.
  • Every method is deterministic, the same on any machine, as the rows a partition holds depend on it, and none calls back into the database.

This strategy sends a row to the partition of its key's parity:

use turborel::catalog::PartitionBound;
use turborel::db::Database;
use turborel::error::{Code, Result, SqlError};
use turborel::partition::{PartitionStrategy, Restriction};
use turborel::value::{DataType, Value};

/// WITH (EVEN 1) holds the even keys, WITH (EVEN 0) the odd ones.
#[derive(Debug)]
struct Parity;

/// Whether `bound` holds the even keys, if it is a bound of PARITY.
fn even(bound: &PartitionBound) -> Option<bool> {
    let PartitionBound::With(options) = bound else { return None };
    options.first().map(|(_, v)| v.to_string() == "1")
}

impl PartitionStrategy for Parity {
    fn name(&self) -> &str {
        "PARITY"
    }

    fn check_key(&self, types: &[DataType]) -> Result<()> {
        match types {
            [DataType::Exact { scale: 0, .. }] => Ok(()),
            _ => Err(SqlError::new(Code::SyntaxRule, "PARITY divides one whole number")),
        }
    }

    fn bound(&self, _types: &[DataType], spec: PartitionBound) -> Result<PartitionBound> {
        match even(&spec) {
            Some(_) => Ok(spec),
            None => Err(SqlError::new(Code::SyntaxRule, "PARITY takes WITH (EVEN 0) or WITH (EVEN 1)")),
        }
    }

    fn overlaps(&self, a: &PartitionBound, b: &PartitionBound) -> Result<bool> {
        Ok(even(a) == even(b))
    }

    fn contains(&self, bound: &PartitionBound, key: &[Value]) -> Result<bool> {
        let key_even = key.first().and_then(|v| v.to_string().parse::<i64>().ok()).map(|k| k % 2 == 0);
        Ok(key_even.is_some() && even(bound) == key_even)
    }

    fn admits(&self, bound: &PartitionBound, restrictions: &[Option<Restriction>]) -> Result<bool> {
        let Some(Some(Restriction::Equal(values))) = restrictions.first() else { return Ok(true) };
        Ok(values.iter().any(|v| self.contains(bound, std::slice::from_ref(v)).unwrap_or(true)))
    }
}

fn main() -> Result<()> {
    let db = Database::new();
    db.register_partition_strategy(Box::new(Parity))?;
    db.schema("CREATE SCHEMA AUTHORIZATION ANN")?;
    let ann = db.session("ANN");
    ann.run("CREATE TABLE T (K INTEGER) PARTITION BY PARITY (K)")?;
    ann.run("CREATE TABLE T_EVEN PARTITION OF T FOR VALUES WITH (EVEN 1)")?;
    ann.run("CREATE TABLE T_ODD PARTITION OF T FOR VALUES WITH (EVEN 0)")?;
    ann.run("INSERT INTO T VALUES (1), (2), (3), (4)")?;
    let mut rows = ann.open_cursor("SELECT K FROM T_EVEN ORDER BY K")?;
    while let Some(row) = rows.fetch()? {
        println!("{}", row[0]);
    }
    Ok(())
}

Output:

2
4

Spatial data (extension)

The geometry types and their functions are built in, so every Database has them and nothing is registered. This example shows a host application checking a courier route against a roadworks area, using the same local metre grid as the Spatial Guide:

use turborel::geometry::Geometry;

fn main() -> Result<(), turborel::error::SqlError> {
    let route = Geometry::from_wkt("LINESTRING (0 200, 1000 200)", 0)?;
    let roadworks = Geometry::rectangle([300.0, 100.0], [500.0, 300.0], 0)?;
    let stored_value = route.to_value();
    let restored_route = Geometry::from_value(&stored_value)?;
    println!(
        "Route meets roadworks: {}",
        restored_route.intersects(&roadworks)?
    );
    println!("Route: {}", restored_route.to_wkt());
    Ok(())
}
Route meets roadworks: true
Route: LINESTRING (0 200, 1e3 200)

stored_value is the value to bind through the ordinary prepared-statement API. The spatial test checks the whole line. Both route endpoints are outside the roadworks, but the path between them crosses it. It does not find a replacement route or calculate travel time.

Registering a type or function of one of their names is refused, as the names are taken.

Geometry::from_wkt(text, srid) and Geometry::from_wkb(bytes, srid) construct structurally checked representations. to_wkt and to_wkb export them without their SRID; to_value and from_value bridge to SQL's extension values. bounds() returns a closed Cartesian envelope, or None for an empty point set. Construction computes the envelope; reading a SQL value decodes its payload and computes it again. Geometry::point(x, y, srid) and Geometry::rectangle(lower, upper, srid) construct directly from coordinates. The rectangle is a closed polygon; both axes require finite, strictly positive extents. Unlike storage Bounds, it refuses zero-width rectangles rather than producing an invalid polygon. Geometry::intersects makes exact XY topological decisions; Geometry::distance returns an approximate planar distance, or None for an empty operand. Both require matching SRIDs and valid polygon inputs. geometry::data_type_with_srid constructs a column descriptor that enforces one nonnegative SRID at assignment and storage boundaries; geometry::data_type permits any signed reference label.

The codecs accept at most 16 MiB of input, 100,000 components, rings and coordinates in total, and 16 geometry levels including the root. Counts and input lengths are checked before allocating from them. SQL functions retain the existing SQL string limits. Formats, errors and semantics are specified in the SQL reference; the Spatial Guide contains SQL workflows.

The version-1 payload contains the SRID and canonical little-endian WKB. Unknown versions and noncanonical stored encodings are refused. Existing file, log, catalog and wire formats are unchanged. A client without the type uses the existing opaque extension-value handling. The type must be registered again before reopening database files that contain it.

An RTreeMethod index on one geometry::data_type_with_srid(srid) column stores envelope projections. ScalarFunction::spatial_operation identifies the intersection, distance and emptiness contracts, including result types, NULL behavior and SRID errors; it does not promise D34's error-free Boolean behavior. The planner uses it with a constrained column and a valid constant or parameter. A later query parameter with a different SRID causes a full index read, preserving the runtime mismatch error. Other raising conjuncts prevent pruning. An inner join can supply the probe from the other column when both column types enforce the same SRID. Its JoinProbe::Predicate binds each outer value and retains the full ON condition for exact checking.

Index::key_projection shares an immutable KeyProjection with readers. Counts belong to projected-key/row pairs, so colliding versions keep one entry until their last reference is removed. Equality lookups return candidates; constraints and foreign-key actions compare original values. The executor checks each visible row's projection and then the full SQL condition. Projected indexes cannot supply original values for covering reads or claim SQL key order. A spatial predicate read records the envelopes the index names (Index::read_region) as a serializable read region, given to TableStorage::read_by_region, whose default records the whole table (D134). Geometry index locators include the SRID, and numeric locators do not. Both are version 1.

An envelope is a candidate-search bound. Geometry::intersects checks actual shapes, including polygon holes and boundaries, using exact orientation signs. Both arguments must have the same SRID and polygon topology must be valid. SQL constant folding can keep a decoded constant operand per expression, avoiding repeated decoding without a cache lock.

Spatial storage primitives

The built-in RTreeMethod connects these pages to SQL. An index on two REAL, FLOAT or DOUBLE PRECISION columns treats them as (X, Y); four columns are (min X, min Y, max X, max Y). The planner can combine bounds on each coordinate. See CREATE INDEX.

spatial::Bounds represents a finite, closed Cartesian rectangle in X/Y order. It accepts points and zero-width rectangles, rejects reversed bounds and non-finite coordinates, and treats touching boundaries as intersections.

For a storage-level viewport filter, the bounds can test whether a courier's point is visible, including the viewport's edge:

use turborel::spatial::Bounds;

fn main() -> Result<(), turborel::error::SqlError> {
    let viewport = Bounds::new([0.0, 0.0], [1000.0, 400.0])?;
    let courier = Bounds::point([1000.0, 100.0])?;
    assert!(viewport.intersects(courier));
    assert!(viewport.contains(courier));
    Ok(())
}

Storage implementations can use engine::rtree::RTree with a registered buffer-pool file. It inserts and removes counted (Bounds, RowId) entries and streams intersecting entries once each. Clones share its node locks; readers hold a page latch only while copying that page. Its meta page is the locator used to reopen it after the owning pager is checkpointed.

RTree::nearest streams entries by Euclidean rectangle distance, breaking ties by row identifier. Take the first k entries to stop as soon as those neighbours are known. Its priority queue reserves memory from the supplied MemoryBudget and returns SQLCODE -918 if it cannot grow. Dropping the iterator releases that memory. Distances use the coordinates' units.

Index::nearest supplies NeighbourEntries for a supported NearestQuery. These are candidates ordered by conservative distance bounds. The caller checks the visible geometry and its scalar distance, keeps the best k results, and continues through every bound equal to the current kth distance. Taking the first k candidates is insufficient for geometry. The geometry index declines requests outside its certified finite-distance domain, returning None so the caller can scan and preserve runtime errors. The SQL executor uses this contract for NearestScan, with a separate budgeted heap of scalar distances and the existing SQL sorter. It checks each fetched version's projected key, records a serializable table read, and observes cancellation before returning rows. When too few qualifying non-NULL distances exist, a scan supplies the NULL-distance tail.

RTree::join streams pairs of intersecting entries from two trees. It prunes disjoint branches on both sides and charges its workspace to the supplied budget. Joining a tree to itself includes both orientations and each self-pair. Reference counts do not multiply the returned pairs.

These are rectangle storage operations. An exact geometry predicate needs to test the candidate geometry, and rectangle containment has different boundary semantics from a geometry's topological ST_Contains operation.

Backups (extension)

A database kept in a directory takes a backup while it serves with Database::backup, as BACKUP DATABASE does, and verify_backup checks one without restoring it. A database not yet opened restores one into a directory not there yet with restore, and is closed once it is done. A program then opens the directory as any other (Backups and Recovery).

A target is a turborel::backup::BackupTarget, a store of named objects, and ArchiveTarget is the same trait, which the archive of the log writes to. A TargetProvider opens the targets of one scheme from their locations, and register_target_provider adds one to a database before it takes a backup or restores one. The built-in providers are dir, also of a location with no scheme, and memory, each location a store of its own for the database's life.

The trait's methods take and give strings, integers and chunks of bytes alone, so a provider may hand the work to another process or a remote service, and it is safe to call as a dyn object. An upload is begun (begin), given its bytes in chunks (write), and then made an object durably (finish) or dropped (abandon). An object is read by ranges (read), listed by the prefix of its name (list) and removed (remove). An object exists once finish returns and never before, is never replaced, and its name is of parts each of letters, digits, ., _ and -, joined by /. Its methods may be called from several threads at once and never call back into the database. The contract in full is the trait's own documentation.

A provider whose targets are one store in memory, shared by two databases, so that the second restores the first's backup:

use std::sync::Arc;

use turborel::backup::{BackupTarget, MemoryTarget, TargetProvider};
use turborel::db::backup::{BackupOptions, RestoreOptions};
use turborel::db::{Database, OpenOptions};
use turborel::backup::seed::RecoveryTarget;
use turborel::disk::io::{FileSystem, OsFileSystem};

/// Every location of the scheme `vault` is the one store it holds.
struct Vault(Arc<MemoryTarget>);

impl TargetProvider for Vault {
    fn scheme(&self) -> &'static str {
        "vault"
    }
    fn open(&self, _location: &str) -> turborel::error::Result<Arc<dyn BackupTarget>> {
        Ok(Arc::clone(&self.0) as Arc<dyn BackupTarget>)
    }
}

fn main() -> Result<(), turborel::error::SqlError> {
    let scratch = std::env::temp_dir().join(format!("turborel-embedding-backup-{}", std::process::id()));
    let fs: Arc<dyn FileSystem> = Arc::new(OsFileSystem);
    let vault = Arc::new(Vault(Arc::new(MemoryTarget::new("vault:main"))));

    let db = Database::new();
    db.register_target_provider(Arc::clone(&vault) as Arc<dyn TargetProvider>)?;
    db.open_files(&fs, &scratch.join("db"), &OpenOptions::default())?;
    db.schema("CREATE SCHEMA AUTHORIZATION APP CREATE TABLE T (K INTEGER PRIMARY KEY)")?;
    let s = db.session("APP");
    s.run("INSERT INTO T VALUES (1), (2)")?;
    s.run("COMMIT")?;
    let taken = db.backup(&BackupOptions { location: "vault:main".into(), ..BackupOptions::default() })?;
    assert_eq!(db.verify_backup("vault:main", &taken.id)?.backups, 1);

    let restorer = Database::new();
    restorer.register_target_provider(vault)?;
    let options = RestoreOptions {
        dir: scratch.join("back"),
        location: "vault:main".into(),
        backup: String::new(),
        archive: None,
        target: RecoveryTarget::End,
        timeline: None,
    };
    assert_eq!(restorer.restore(&options)?.backup, taken.id);
    let back = Database::new();
    back.open_files(&fs, &scratch.join("back"), &OpenOptions::default())?;
    let app = back.session("APP");
    let mut rows = app.open_cursor("SELECT COUNT(*) FROM T")?;
    assert_eq!(rows.fetch()?.map(|r| r[0].to_string()).as_deref(), Some("2"));
    std::fs::remove_dir_all(&scratch).ok();
    Ok(())
}

See also

The Client Library, to connect to a server instead; Statements; the crate's documentation, cargo doc -p turborel.