Recipes

A table that extends another one

A product’s nutrition facts, keyed by the product, should go wherever the product goes:

/// A product's nutrition facts: wherever the product goes.
#[derive(sqlx::FromRow, Clone, magic_sync::Row)]
#[row(
    table = "nutrition",
    schema = "shop",
    mirror = "catalog",
    key = "product_id",
    sync = "follows(product_id)"
)]
pub struct Nutrition {
    pub product_id: Uuid,
    pub calories: i32,
}

Orders for one register

An order is taken by one register and goes to that register only:

/// An order: to the register it is assigned to.
#[derive(sqlx::FromRow, Clone, magic_sync::Row)]
#[row(
    table = "orders",
    schema = "shop",
    mirror = "catalog",
    sync = "assigned(register_id)"
)]
pub struct Order {
    pub id: Uuid,
    pub register_id: Option<Uuid>,
    pub product_id: Uuid,
    pub quantity: i32,
}

The order’s product goes with it — no declaration: the foreign key says so.

Give a register a whole list at once

Customers belong to lists, and a register is given lists. A list of 10,000 customers given to a register is one row in register_lists:

CREATE TABLE shop.lists (id UUID PRIMARY KEY);
CREATE TABLE shop.list_members (
    list_id UUID REFERENCES shop.lists (id), customer_id UUID REFERENCES shop.customers (id),
    PRIMARY KEY (list_id, customer_id));
CREATE TABLE shop.register_lists (
    list_id UUID REFERENCES shop.lists (id), register_id UUID REFERENCES shop.registers (id),
    PRIMARY KEY (list_id, register_id));

/// A customer: to every register given a list it is on.
#[derive(sqlx::FromRow, Clone, magic_sync::Row)]
#[row(
    table = "customers",
    schema = "shop",
    mirror = "catalog",
    sync = "grouped(members = list_members, assigned = register_lists)"
)]
pub struct Customer {
    pub id: Uuid,
    pub name: String,
}

Taking a list away from a register is deleting one row; the engine works out which customers to take back, and keeps those another list still gives it. The members table points at the grouped table by its key — one column or several — and both group tables point at the table of groups. Each group table gets a trigger of its own (sync_note_group), which Engine::triggers prints with the others.

A table both sides write

A register reports its own status on a row the head office owns. Mark the columns the register owns: they go up, the rest come down, and a change to only the register’s columns is not sent back down:

/// A register's state: the head office writes its label, the register its
/// battery.
#[derive(sqlx::FromRow, Clone, magic_sync::Row)]
#[row(
    table = "register_state",
    schema = "shop",
    mirror = "both",
    sync = "assigned(id)"
)]
pub struct RegisterState {
    pub id: Uuid,
    pub label: String,
    #[row(replica_owned)]
    pub battery: Option<i32>,
    pub updated_at: DateTime<Utc>,
}

The row begins on the server: a register edits only its own columns of a row it holds.

A setting both sides want to change

The head office wants a register’s screen brightness at 70; the register’s user turned it to 40. Two writers, one setting. The register is right about what the setting is, and the head office about what it wants: declare the register’s row a setting, and let the head office wish it other values — Settings and wishes is the whole of it.

Archived rows replicas should not hold

Keep a view of the rows that should travel, and name it:

CREATE VIEW shop.live_products AS SELECT * FROM shop.products WHERE archived_at IS NULL;

/// A product, as replicas hold it: never an archived one.
#[derive(sqlx::FromRow, Clone, magic_sync::Row)]
#[row(
    table = "products",
    schema = "shop",
    mirror = "catalog",
    sync = "broadcast",
    active = "shop.live_products",
    unmirrored = "archived_at"
)]
pub struct LiveProduct {
    pub id: Uuid,
    pub name: String,
    pub updated_at: DateTime<Utc>,
}

Archiving a product takes it back from every register — retired where a receipt still points at it. The view decides what is sent for its own sake, not what may travel: an archived product a row the register holds points at still goes there, as that row’s parent, and leaves once nothing points at it. Leave active out and archived products travel like any other, with their archived_at as data, for the register to show as archived.

Several companies on one server

Give rows and replicas a partition column, and name it:

use magic_sync::plan::Replicas;

pub const REGISTERS: Replicas = Replicas::new("shop.registers", "registers")
    .live("active")
    .partitioned_by("company_id");

Every table that goes down carries company_id; a broadcast product reaches only its company’s registers. The triggers Engine::triggers prints note it.

PostgreSQL can keep a row from pointing into another company: give each parent a unique key on (company_id, id), and point at it by both columns.

CREATE TABLE shop.regions (
    id UUID PRIMARY KEY, company_id UUID NOT NULL, name TEXT NOT NULL,
    UNIQUE (company_id, id));
CREATE TABLE shop.stores (
    id UUID PRIMARY KEY, company_id UUID NOT NULL, region_id UUID NOT NULL,
    FOREIGN KEY (company_id, region_id) REFERENCES shop.regions (company_id, id));

What happens: the engine reads the partition column on both sides of a foreign key as “the same company”, which no row crosses anyway: a store points at its region by region_id, so sync = "follows(region_id)" works, and a region goes wherever a store that points at it goes. A key made only of company_id — into a table of companies, say — points at no parent. A parent whose primary key holds company_id — a table partitioned by it — is pointed at by every column of the key, company_id too.

Keyboard shortcuts

magic_sync