create-db-script.sql 2.65 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DROP TABLE IF EXISTS public.member_to_group;
DROP TABLE IF EXISTS public."member";
DROP TABLE IF EXISTS public.embedded_group;
DROP TABLE IF EXISTS public.app_id;
DROP TABLE IF EXISTS public."group";

CREATE TABLE public."group"
(
id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
name character varying COLLATE pg_catalog."default",
description text COLLATE pg_catalog."default",
email character varying COLLATE pg_catalog."default",
partition_id character varying COLLATE pg_catalog."default",
CONSTRAINT group_pkey PRIMARY KEY (id),
CONSTRAINT group_email_key UNIQUE (email),
16
CONSTRAINT group_name_partition_id_key UNIQUE (name, partition_id)
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
)

TABLESPACE pg_default;

ALTER TABLE public."group"
OWNER to postgres;

CREATE TABLE public.app_id
(
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    group_id bigint,
    app_id character varying,
    PRIMARY KEY (id),
CONSTRAINT app_id_group_fk FOREIGN KEY (group_id)
REFERENCES public."group" (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID
);

ALTER TABLE public.app_id
OWNER to postgres;

CREATE TABLE public.member
(
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
email character varying COLLATE pg_catalog."default",
partition_id character varying COLLATE pg_catalog."default",
CONSTRAINT member_pkey PRIMARY KEY (id),
CONSTRAINT member_email_key UNIQUE (email)
)

TABLESPACE pg_default;

ALTER TABLE public.member
OWNER to postgres;

CREATE TABLE public.member_to_group
(
    group_id bigint NOT NULL,
    member_id bigint NOT NULL,
    role character varying COLLATE pg_catalog."default",
    CONSTRAINT member_to_group_pkey PRIMARY KEY (group_id, member_id),
CONSTRAINT group_as_member_holder_fk FOREIGN KEY (group_id)
REFERENCES public."group" (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID,
CONSTRAINT user_as_member_fk FOREIGN KEY (member_id)
REFERENCES public.member (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID
)

TABLESPACE pg_default;

ALTER TABLE public.member_to_group
OWNER to postgres;

CREATE TABLE public.embedded_group
(
    parent_id bigint NOT NULL,
    child_id bigint NOT NULL,
    CONSTRAINT embedded_group_pk PRIMARY KEY (parent_id, child_id),
CONSTRAINT group_as_child_fk FOREIGN KEY (child_id)
REFERENCES public."group" (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID,
CONSTRAINT group_as_parent_fk FOREIGN KEY (parent_id)
REFERENCES public."group" (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID
)

TABLESPACE pg_default;

ALTER TABLE public.embedded_group
OWNER to postgres;