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)orEscape::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 leadingxmlare 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.
weightandboundare 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.
boundaccepts only the forms it reads, never DEFAULT, and gives a bound the other methods take.- Of siblings whose bounds do not
overlaps,containsis true of at most one for a key, androutegives that one. admitsis 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.