init.sql 2.24 KB
Newer Older
1
2
3
4
5
6
CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE gms_group (
  id varchar NOT NULL,
  name text NOT NULL,
  path ltree NOT NULL,
7
  is_leaf boolean,
8
  locked boolean,
9
10
11
12
  primary key(id)
);

CREATE INDEX group_path_gist_idx ON gms_group USING GIST(path);
13
CREATE UNIQUE INDEX group_path_idx ON gms_group USING btree(path);
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE INDEX group_name_idx ON gms_group USING btree(name);

CREATE TABLE gms_membership (
  group_id varchar NOT NULL,
  user_id varchar NOT NULL,
  primary key (group_id, user_id),
  foreign key (group_id) references gms_group(id)
);

CREATE TYPE permission_type AS ENUM ('VIEW_MEMBERS', 'MANAGE_MEMBERS', 'ADMIN');

CREATE TABLE gms_permission (
  group_id varchar NOT NULL,
  user_id varchar NOT NULL,
  permission permission_type NOT NULL,
29
  group_path ltree NOT NULL, -- group_path is copied here for performance reasons
30
  primary key (group_id, user_id), -- we can have 1 permission for each group
31
32
  foreign key (group_id) references gms_group(id),
  foreign key (group_path) references gms_group(path)
33
);
34

35
36
37
38
39
40
41
42
43
44
45
46
CREATE TABLE error_log (
  "date" timestamp DEFAULT NOW(),
  "exception_class" varchar,
  "stack_trace" TEXT
);

CREATE TABLE audit_log (
  "date" timestamp DEFAULT NOW(),
  "user" varchar,
  "ip_address" varchar,
  "action" TEXT
);
47
48
49
50
51
52

CREATE TABLE invited_registration_request (
  id varchar NOT NULL,
  token_hash varchar NOT NULL,
  email varchar NOT NULL,
  creation_time timestamp DEFAULT NOW(),
53
  "user" varchar NULL, -- for audit purposes
54
55
56
57
58
59
60
  done boolean,
  PRIMARY KEY(id)
);

CREATE TABLE invited_registration_request_group (
  request_id varchar NOT NULL,
  group_id varchar NOT NULL,
61
62
63
64
  permission permission_type NOT NULL,
  PRIMARY KEY (request_id, group_id, permission),
  FOREIGN KEY (request_id) REFERENCES invited_registration_request(id),
  FOREIGN KEY (group_id) REFERENCES gms_group(id)
65
);
66
67
68
69
70
71
72
73
74
75
76

CREATE VIEW group_complete_name AS
SELECT id, string_agg(name, '.') AS complete_name
FROM (
    SELECT replace(name, '.', '\.') AS name, p.id
    FROM gms_group g
    JOIN (
        SELECT UNNEST(string_to_array(path::varchar, '.')) AS rel_id, id
        FROM gms_group
    ) AS p ON g.id = p.rel_id
    ORDER BY p.id, nlevel(g.path)
77
78
79
80
) AS j GROUP BY id
UNION
SELECT id, name AS complete_name FROM gms_group WHERE id = 'ROOT'
ORDER BY complete_name;