Repository navigation
Expand file tree
/
Copy pathschema.sql
More file actions
37 lines (33 loc) · 1.46 KB
/
Copy pathschema.sql
File metadata and controls
37 lines (33 loc) · 1.46 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- The example schema the README and docs/*.md use. bb docs:example generates
-- docs/example/pgmalli.edn from it; the doc tests read that file.
CREATE TYPE user_status AS ENUM ('active', 'closed');
CREATE TABLE groups (
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name text NOT NULL UNIQUE CHECK (length(trim(name)) > 0)
);
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
group_id integer NOT NULL REFERENCES groups,
email varchar(120) NOT NULL UNIQUE,
nick varchar(40),
status user_status NOT NULL DEFAULT 'active',
closed_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT closed_at_matches_status CHECK (
status = 'active' AND closed_at IS NULL OR status = 'closed' AND closed_at IS NOT NULL)
);
CREATE TABLE posts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
author_id bigint NOT NULL REFERENCES users,
title varchar(200) NOT NULL,
title_upper text GENERATED ALWAYS AS (upper(title)) STORED,
likes integer NOT NULL DEFAULT 0 CHECK (likes >= 0),
views integer NOT NULL DEFAULT 0,
CONSTRAINT likes_within_views CHECK (likes <= views)
);
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users,
schema_version integer NOT NULL CHECK (schema_version IN (1, 2)),
payload jsonb NOT NULL CHECK (jsonb_typeof(payload) = 'object')
);