Newer
Older
CREATE EXTENSION IF NOT EXISTS ltree;
CREATE TABLE gms_group (
id varchar NOT NULL,
name text NOT NULL,
path ltree NOT NULL,
locked boolean,
primary key(id)
);
CREATE INDEX group_path_gist_idx ON gms_group USING GIST(path);
CREATE UNIQUE INDEX group_path_idx ON gms_group USING btree(path);
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,
group_path ltree NOT NULL, -- group_path is copied here for performance reasons
primary key (group_id, user_id), -- we can have 1 permission for each group
foreign key (group_id) references gms_group(id),
foreign key (group_path) references gms_group(path)
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
);
CREATE TABLE invited_registration_request (
id varchar NOT NULL,
token_hash varchar NOT NULL,
email varchar NOT NULL,
creation_time timestamp DEFAULT NOW(),
"user" varchar NULL, -- for audit purposes
done boolean,
PRIMARY KEY(id)
);
CREATE TABLE invited_registration_request_group (
request_id varchar NOT NULL,
group_id varchar NOT NULL,
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)
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)
) AS j GROUP BY id
UNION
SELECT id, name AS complete_name FROM gms_group WHERE id = 'ROOT'
ORDER BY complete_name;