CREATE EXTENSION IF NOT EXISTS ltree; CREATE TABLE gms_group ( id varchar NOT NULL, name text NOT NULL, path ltree NOT NULL, is_leaf boolean, 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;