pub const CATALOG_SQL: &str = r#"
CREATE SCHEMA IF NOT EXISTS improv;
CREATE TABLE IF NOT EXISTS improv.model (
id bigserial PRIMARY KEY,
name text NOT NULL UNIQUE,
sync_mode text NOT NULL DEFAULT 'logical'
CHECK (sync_mode IN ('logical', 'trigger', 'commit', 'manual')),
slot_name text,
created timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS improv.category (
model_id bigint NOT NULL REFERENCES improv.model(id) ON DELETE CASCADE,
id bigint NOT NULL,
name text NOT NULL,
PRIMARY KEY (model_id, id)
);
CREATE TABLE IF NOT EXISTS improv.item (
model_id bigint NOT NULL REFERENCES improv.model(id) ON DELETE CASCADE,
id bigint NOT NULL,
category_id bigint NOT NULL,
name text NOT NULL,
PRIMARY KEY (model_id, id)
);
CREATE TABLE IF NOT EXISTS improv.measure (
model_id bigint NOT NULL REFERENCES improv.model(id) ON DELETE CASCADE,
id bigint NOT NULL,
name text NOT NULL,
value_type text NOT NULL,
kind text NOT NULL CHECK (kind IN ('input', 'derived')),
formula text,
description text,
categories bigint[] NOT NULL DEFAULT '{}',
PRIMARY KEY (model_id, id)
);
CREATE TABLE IF NOT EXISTS improv.binding (
model_id bigint NOT NULL REFERENCES improv.model(id) ON DELETE CASCADE,
measure_id bigint NOT NULL,
rel regclass NOT NULL,
value_col text NOT NULL,
dim_cols jsonb NOT NULL,
PRIMARY KEY (model_id, measure_id)
);
CREATE TABLE IF NOT EXISTS improv.cell (
model_id bigint NOT NULL REFERENCES improv.model(id) ON DELETE CASCADE,
measure_id bigint NOT NULL,
coord jsonb NOT NULL,
value jsonb NOT NULL,
PRIMARY KEY (model_id, measure_id, coord)
);
CREATE TABLE IF NOT EXISTS improv.view (
model_id bigint NOT NULL REFERENCES improv.model(id) ON DELETE CASCADE,
id bigint NOT NULL,
name text NOT NULL,
layout jsonb NOT NULL,
PRIMARY KEY (model_id, id)
);
CREATE TABLE IF NOT EXISTS improv.meta (
model_id bigint PRIMARY KEY REFERENCES improv.model(id) ON DELETE CASCADE,
blob jsonb NOT NULL
);
CREATE TABLE IF NOT EXISTS improv.model_version (
model_id bigint PRIMARY KEY REFERENCES improv.model(id) ON DELETE CASCADE,
tx bigint NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS improv.lambda (
model_id bigint NOT NULL REFERENCES improv.model(id) ON DELETE CASCADE,
id bigint NOT NULL,
name text NOT NULL,
definition text NOT NULL,
PRIMARY KEY (model_id, id)
);
-- Shared with the pg_improv extension (design §9.2): the same DDL in
-- idempotent form (this runs against an existing database); the extension
-- uses plain CREATE. Both pin mint_items' search_path.
ALTER TABLE improv.binding ADD COLUMN IF NOT EXISTS agg text
CHECK (agg IN ('sum', 'avg', 'min', 'max', 'count', 'count_distinct'));
CREATE UNIQUE INDEX IF NOT EXISTS item_name_uniq ON improv.item (model_id, category_id, name);
CREATE OR REPLACE FUNCTION improv.mint_items(p_model bigint, p_cats bigint[], p_names text[])
RETURNS TABLE (category_id bigint, name text, id bigint) LANGUAGE plpgsql
SET search_path = pg_catalog, pg_temp AS $$
BEGIN
PERFORM 1 FROM improv.model m WHERE m.id = p_model FOR UPDATE;
INSERT INTO improv.item (model_id, id, category_id, name)
SELECT p_model,
(SELECT coalesce(max(i.id), 0) FROM improv.item i WHERE i.model_id = p_model)
+ row_number() OVER (ORDER BY s.c, s.n),
s.c, s.n
FROM (SELECT DISTINCT u.c, u.n FROM unnest(p_cats, p_names) AS u(c, n)
WHERE NOT EXISTS (SELECT 1 FROM improv.item i
WHERE i.model_id = p_model AND i.category_id = u.c AND i.name = u.n)) s
ON CONFLICT DO NOTHING;
RETURN QUERY
SELECT i.category_id, i.name, i.id FROM improv.item i
JOIN (SELECT DISTINCT u.c, u.n FROM unnest(p_cats, p_names) AS u(c, n)) w
ON i.model_id = p_model AND i.category_id = w.c AND i.name = w.n;
END $$;
"#;Expand description
The catalog this client depends on (design §2), created when the
pg_improv extension is not installed so a plain Postgres works too.
Kept in this ONE place so it can be reconciled with the extension’s own
catalog SQL; nothing else in this crate issues DDL against improv.*.