Model your data

This guide shows the one way to model data in Gizlang. One record type gives the table, the typed queries, the forms, the grids and the charts. The Data Access Layer (DAL) keeps the rules: typed values, expected revisions, transactions with one owner, online migrations and the access Policy.

Coming In development. This is the design it ships to. Roadmap

Before you start

You need:

• The Gizlang toolchain. Refer to the installation guide.
• Nothing else. The Data Access Layer is part of the gizdb module, and the model annotations are part of gizdb:model.

Declare the model

A model is a record type. Annotations from gizdb:model give its table, key, indexes, relations and input rules. Each field type gives the column type and the input decoder.

import {Option} from "gizlang:core";
import * as Model from "gizdb:model";
import {Dec} from "gizdb";

/** An order of a customer. */
@Model.Table("orders") @Model.Submit("Save")
export type Order = {
    @Model.Key
    id: i64;
    @Model.References("customers", "id", "restrict") @Model.Index("customer")
    customer: i64;
    @Model.Values("open,paid,void") @Model.Label("State")
    state: string;
    @Model.Min(0)
    amount: Dec;
    note: Option<string>;
};

Do these steps:

1. Write the model in a file, for example model.giz.
2. Read the Shape where you need it: Model.shape<Order>(). The program reads the declaration, its docs and its annotations at run time (Memory.describe), so no file is generated and a change of the type is a change of the Shape.
3. Keep the Shape in a variable when you use it more than once: reading a declaration takes work. Model.rowOf<Order>(shape, order) makes the row of a record, Model.valueOf<Order>(shape, values) the record of a row.

The Shape is the one type source:

Try the model in one file

This program uses a record type with no web app. It reads the Shape, makes the table for SQLite, checks a form with three problems, turns a record into a row and the row into JSON, and flattens a record to words and back. It is examples/cli/orders.giz, and a test checks its output.

// A model without a web app: one record type is the table, the form check, the stored row and the JSON.
// The program reads the type while it runs (`Model.shape`, `Memory.describe`), so no file is generated.
// Run: giz examples/cli/orders.giz
import * as Model from "gizdb:model";
import * as Memory from "gizlang:memory";
import {dialectOf} from "gizdb:sql/types/dialect";

/** An order of a customer. */
@Model.Table("orders") @Model.Submit("Save")
type Order = {
    @Model.Key
    id: i64;
    @Model.Values("open,paid,void") @Model.Label("State")
    state: string;
    @Model.Min(0)
    cents: i64;
    note: Option<string>;
};

function main(): void {
    // The Shape: read once from the declaration, its annotations included.
    const shape = Model.shape<Order>();
    print(`${shape.name} is table ${shape.table!} with ${shape.fields.length} fields`);

    // The table, for each SQL engine.
    for (const statement of Model.ddl(shape, dialectOf("sqlite"))) { print(statement); }

    // A form: every problem comes back in one rejection.
    const bad = Model.noFields().with("id", "1").with("state", "lost").with("cents", "-5");
    match (Model.read(shape, bad)) {
        Result.ok(values) => { print("accepted"); }
        Result.err(rejection) => { for (const problem of rejection.problems) { print(problem.message); } }
    }

    // A record becomes a stored row and comes back; the JSON is made from the row.
    const order: Order = { id: 7, state: "paid", cents: 1250, note: "gift" };
    const row = Model.rowOf<Order>(shape, order);
    print(string(Model.json(shape, row)));
    const back = Model.valueOf<Order>(shape, Model.valuesOf<Order>(shape, order));
    print(`${back.state} ${back.cents}`);

    // Any record flattens to words and back, with no generated code.
    const again = Memory.unflatten<Order>(Memory.flatten(order));
    print(string(again.id));
}

Order is table orders with 4 fields
CREATE TABLE IF NOT EXISTS orders (id INTEGER NOT NULL, state TEXT NOT NULL, cents INTEGER NOT NULL, note TEXT, PRIMARY KEY (id)) WITHOUT ROWID
State: Choose an allowed value.
cents: 0 is the minimum.
{"id":7,"state":"paid","cents":1250,"note":"gift"}
paid 1250
7

The program reads the type while it runs. Memory.describe<T>() (gizlang:memory) gives the declaration of a type with its docs and annotations, Model.shape<T>() builds the Shape from it, and Memory.flatten and Memory.unflatten<T> turn any record into words and back. Nothing is generated, so a change of the type is a change of the Shape on the next build.

Name the actions of an app

An action is a function of the app's context and an input record. It gives a record or a rejection. The registry is a function in your code, and the web layer, the agent tools and the tests read the same list:

import * as Model from "gizdb:model";
import {Result} from "gizlang:core";

export type Session = { actor: string; };
type Rename = { id: i64; name: string; };
type Renamed = { id: i64; name: string; };

function rename(s: Session, input: Rename): Result<Renamed, Model.Rejection> {
    return Result.ok({ id: input.id, name: input.name });
}
export function actions(): Vec<Model.Action<Session>> {
    return [Model.action<Session, Rename, Renamed>("rename", "Rename a record.", rename)];
}
function main(): void {
    const s: Session = { actor: "kim" };
    const out = Model.dispatch(actions(), s, "rename", true, Model.noFields().with("id", "3").with("name", "Bo"));
    match (out) { Model.Outcome.done(json) => { print(string(json)); } Model.Outcome.rejected(r) => { print(r.message); } Model.Outcome.unknown() => { print("unknown"); } }
    print(string(Model.handlers(actions()).length));
}

{"id":3,"name":"Bo"}
1

• Model.handlers(actions()) gives the form and tool descriptions of the registry for Web.shell and Web.actions.
• Model.dispatch(actions(), session, id, bare, fields) runs one action with the fields of a form or a JSON body, and answers with an Outcome. A name that is not in the registry gives Outcome.unknown.
• Build the registry inside a task that uses it: an action closure cannot be captured by Task.thread.

The web guide builds a whole app on these two functions.

Choose how to reach the data

The web guide tutorial uses level 1. Each level below it gives more control and needs more code. Use the highest level that does the job, and go down for one query, not for the whole app.

At level 1, the records of a Shape are read with a filter whose values are bound.

import * as Model from "gizdb:model";
import * as Data from "gizdb:data";

/** A saved note. */
@Model.Table("notes")
export type Note = { @Model.Key id: string; title: string; body: string; };

/** The notes with the title TITLE. */
export function titled(db: Data.Db, title: string): Vec<Note> {
    const shape = Model.shape<Note>();
    return Data.find(db, shape, "title = $1", [title], Model.rowReader<Note>(shape))!;
}

At level 5 you map each column yourself. SQLite gives every column as optional text, and you bind the values ($1), never put them into the SQL text:

import {Option} from "gizlang:core";
import * as Sqlite from "gizlang:sqlite";

function main(): void {
    const db = Sqlite.open("notes.db")!;
    db.exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL)")!;
    const rows = db.query("SELECT id, title FROM notes WHERE title = $1", [Option.some("Milk")])!.rows;
    print(`${rows.length} notes`);
}

Open a database and query it with types

Access.local opens an embedded database in a folder: the data actor runs in your process. One engine opens a folder at a time: a second opener, in this or another process, fails until the first closes. The threads of one process (Task.thread) share the engine: each thread calls Access.connect(local.endpoint, local.identity, local.secret) for its own Connection. Other processes reach the data through a gizdb server, with Source.postgres.

This program makes a table, queries it with a typed query, updates a row with an expected revision and rolls a transaction back.

import {System} from "gizlang:system";
import {Result} from "gizlang:core";
import * as Access from "gizdb:access";
import * as Typed from "gizdb:typed";
import * as Query from "gizdb:query";
import {Value, Type, DbError, parse} from "gizdb";

type Task = { id: i64; title: string; revision: i64; };

function main(sys: System): void {
    const dir = sys.temporaryDir()!;
    const db = Access.local(sys, dir, "guide", 67108864)!;
    const c = db.connection;
    c.write(parse("CREATE TABLE tasks(id BIGINT PRIMARY KEY, title TEXT NOT NULL, done BOOLEAN NOT NULL, revision BIGINT NOT NULL)")!, [])!;
    const rows = [[Value.int(1), Value.text("Write the guide"), Value.boolean(false), Value.int(0)],
                  [Value.int(2), Value.text("Test the guide"), Value.boolean(false), Value.int(0)]];
    c.write([Query.insert("tasks", ["id", "title", "done", "revision"], rows)!], [])!;

    // A typed query: columns with codecs, one parameter, decoded records.
    const id = Query.field("tasks", "id", Typed.integerCodec());
    const title = Query.field("tasks", "title", Typed.textCodec());
    const revision = Query.field("tasks", "revision", Typed.integerCodec());
    const done = Query.field("tasks", "done", Typed.booleanCodec());
    let tree = Query.where(Query.table("tasks"), Query.equalTo(done, Query.parameter(0, Typed.booleanCodec())));
    tree = Query.projection(tree, "id", id); tree = Query.projection(tree, "title", title); tree = Query.projection(tree, "revision", revision);
    tree = Query.orderBy(tree, id, false, false);
    const outputs = [Typed.Slot("id", Type.int(), false), Typed.Slot("title", Type.text(), false), Typed.Slot("revision", Type.int(), false)];
    const open = Query.Prepared(tree, [Typed.Slot("done", Type.boolean(), false)], outputs,
        (input: bool): Result<Vec<Value>, DbError> => { return Result.ok([Value.boolean(input)]); },
        (row: Vec<Value>): Result<Task, DbError> => {
            return Result.ok(Task(try Typed.integerCodec().decode(row[0]), try Typed.textCodec().decode(row[1]), try Typed.integerCodec().decode(row[2])));
        }, "");
    const source = Access.native(c, "guide");
    for (const task of open.fetch(source, false)!) { print(string(task.id) + " " + task.title + " r" + string(task.revision)); }

    // An update that expects the revision it read: exactly one row, or a conflict.
    const finish = Query.require(parse("UPDATE tasks SET done = true, revision = revision + 1 WHERE id = $1 AND revision = $2")![0], 1)!;
    c.write([finish], [Value.int(1), Value.int(0)])!;
    match (c.write([finish], [Value.int(1), Value.int(0)])) {
        Result.ok(reply) => { print("stale write accepted"); }
        Result.err(error) => { print("stale write: " + error.message); }
    }

    // A transaction has one owner. Its writes are visible to others only after commit.
    const tx = c.begin()!;
    tx.write([finish], [Value.int(2), Value.int(0)])!;
    tx.rollback()!;
    print("open after rollback: " + string(open.fetch(source, false)!.length));

    db.close()!;
    dir.close();
}

1 Write the guide r0
2 Test the guide r0
stale write: required 1 affected rows, got 0
open after rollback: 1

Rules:

• Write with an expected revision. Read the revision, then write with Query.require(statement, 1). A concurrent write makes the count 0, and the whole write rolls back with a conflict. Read again and retry.
• Use Connection.begin for more than one write that must succeed together. commit, rollback, savepoint, rollbackTo and release act on that one transaction. An idle transaction expires after its lease.
• Page with keys, not offsets: Query.page(source, query, input, keyFields, after, size).
• Load relations in one query for each relation: Query.include(source, parents, relation).
• Raw SQL with typed results is the escape hatch: Query.sql(text, parameters, outputs, encode, decode). Query.statements(text) parses a script of several statements (DDL, admin writes).
• Write a Shape's records with the SQL text of Model.insertRowsSql(shape, n) (or Store.insert(db, shape, record) for one record; db/store.giz also has migrate, records, record and one) and read them with Model.select(shape, filter, parameters, encode, of) (key order). The same statement and query run on each Source: Access.native(connection, id) (GizDB), sqlite(database, id) (db/ingest/sqlitesource.giz, so a program without SQLite does not link it), Source.postgres(connection, id) and Source.odbc(connection, id, semantics) (SQLite and MySQL drivers are tested). Source.semantics(dialect) gives the collation, time zone and NULL typing of postgres, sqlite and mysql.
• A watch needs a change log. GizDB gives one (source.watch). A SQLite or PostgreSQL client source refuses the watch as a missing capability. A watched query has no ORDER BY.

Change the model

A model change is a migration. Do these steps:

1. Change the record type.
2. Let the schema planner (Schema.changes, which Store.migrate(db, shape) runs) compare the new Shape with the table. It gives the schema statements: add a column, widen a type, add an index. It refuses a change that loses data, unless you give an explicit contract step.
3. Put the steps in a Migrate.Migration (gizdb:migrate): schema steps, data steps (Backfill, which moves rows in bounded batches) and contract steps.
4. Run Migrate.apply(connection, migration, limits). The migration runs online. Each batch is one transaction with its progress. Migrate.status(connection, id) shows the progress. Run apply again with the same migration to continue after a stop or a conflict.
5. Until the contract step, write the old and the new fields in your application. A batch does not replay rows that change behind it.

To copy data from another database, use gizdb migrate copy, verify and compare. They read PostgreSQL, SQLite and ODBC sources.

Control access

The Policy (gizdb:policy) keeps grants on records and the ancestry of records in two tables. A record inherits the grants of its ancestors.

A record without ancestry has no access (default deny). An agent gets the lowest of its own grant, its person's grant and its ceiling.

Judge rows and fields with the Model

The Gate (db/gate.giz, used through Data.as) applies the Policy to the rows of a Shape, so a page, an API call, an agent's tool and a job that use one Data.Scoped are judged by one function. A table is the record SPACE/table/NAME; a row is SPACE/NAME/KEY below it, or below the row that its @Model.Within field names. A key of a judged Shape is a required text or integer of letters, digits and - . _ ~.

const mine = Data.as(db, "acme", Security.Principal.person("kim"), now)!;
Data.provision(mine, [Model.shape<Project>()]);     // tables, Policy tables, kim owns the table
const rows = Data.rows(mine, shape, "name LIKE $1", [Value.text("A%")], "ORDER BY name LIMIT 25")!;
Data.create(mine, shape, values); Data.update(mine, shape, values); Data.remove(mine, shape, key);

A read adds the Policy to the query, so a row the actor may not read is never selected, and a field above its role is NULL. A write is decided before it runs and is placed in the Policy in the transaction that inserts it. A Data.Db without a scope is trusted code; give pages and tools a Data.Scoped.

A field may be a record of its own or a list. A nested record is stored as columns prefixed by the field's name (ship: Address makes ship_street and ship_zip, and ("to") on ship changes the prefix to to_), at any depth; its fields keep their annotations. A Vec<string> or Vec<i64> is one JSON text column. A recursive record, an optional record and a list of records are refused with the field's name.

Show the data

The server renders the rows of a Shape into the page. Data.follow and Web.live keep them current: the page gets the patches of the rows that change while it is open. A filter is a GET form, so it works without JavaScript.

Refer to the web guide for pages and actions.

Reference