diff options
Diffstat (limited to '')
| -rw-r--r-- | sql/schema.sql | 195 |
1 files changed, 195 insertions, 0 deletions
diff --git a/sql/schema.sql b/sql/schema.sql new file mode 100644 index 0000000..a6efc39 --- /dev/null +++ b/sql/schema.sql @@ -0,0 +1,195 @@ +-- SPDX-License-Identifier: AGPL-3.0-only +-- SPDX-FileCopyrightText: Copyright (c) 2025 Runxi Yu <https://runxiyu.org> + +CREATE TABLE groups ( + id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + name TEXT NOT NULL, + parent_group INTEGER REFERENCES groups(id) ON DELETE CASCADE, + description TEXT, + UNIQUE NULLS NOT DISTINCT (parent_group, name) +); + +CREATE TABLE repos ( + id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE RESTRICT, -- I mean, should be CASCADE but deleting Git repos on disk also needs to be considered + contrib_requirements TEXT NOT NULL CHECK (contrib_requirements IN ('closed', 'registered_user', 'federated', 'ssh_pubkey', 'public')), + name TEXT NOT NULL, + UNIQUE(group_id, name), + description TEXT, + filesystem_path TEXT +); + +CREATE TABLE mailing_lists ( + id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE RESTRICT, + name TEXT NOT NULL, + UNIQUE(group_id, name), + description TEXT +); + +CREATE TABLE mailing_list_emails ( + id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + list_id INTEGER NOT NULL REFERENCES mailing_lists(id) ON DELETE CASCADE, + title TEXT NOT NULL, + sender TEXT NOT NULL, + date TIMESTAMP NOT NULL, + content BYTEA NOT NULL +); + +CREATE TABLE users ( + id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + username TEXT UNIQUE, + type TEXT NOT NULL CHECK (type IN ('pubkey_only', 'federated', 'registered')), + password TEXT +); + +CREATE TABLE ssh_public_keys ( + id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + key_string TEXT NOT NULL, + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + CONSTRAINT unique_key_string EXCLUDE USING HASH (key_string WITH =) +); + +CREATE TABLE sessions ( + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + session_id TEXT PRIMARY KEY NOT NULL, + UNIQUE(user_id, session_id) +); + +CREATE TABLE user_group_roles ( + group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE, + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + PRIMARY KEY(user_id, group_id) +); + +CREATE TABLE federated_identities ( + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + service TEXT NOT NULL, + remote_username TEXT NOT NULL, + PRIMARY KEY(user_id, service) +); + +-- Ticket tracking + +CREATE TABLE ticket_trackers ( + id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE RESTRICT, + name TEXT NOT NULL, + description TEXT, + UNIQUE(group_id, name) +); + +CREATE TABLE tickets ( + id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + tracker_id INTEGER NOT NULL REFERENCES ticket_trackers(id) ON DELETE CASCADE, + tracker_local_id INTEGER NOT NULL, + title TEXT NOT NULL, + description TEXT, + UNIQUE(tracker_id, tracker_local_id) +); + +CREATE OR REPLACE FUNCTION create_tracker_ticket_sequence() +RETURNS TRIGGER AS $$ +DECLARE + seq_name TEXT := 'tracker_ticket_seq_' || NEW.id; +BEGIN + EXECUTE format('CREATE SEQUENCE %I', seq_name); + RETURN NEW; +END; +$$ LANGUAGE plpgsql; +CREATE TRIGGER after_insert_ticket_tracker +AFTER INSERT ON ticket_trackers +FOR EACH ROW +EXECUTE FUNCTION create_tracker_ticket_sequence(); + +CREATE OR REPLACE FUNCTION drop_tracker_ticket_sequence() +RETURNS TRIGGER AS $$ +DECLARE + seq_name TEXT := 'tracker_ticket_seq_' || OLD.id; +BEGIN + EXECUTE format('DROP SEQUENCE IF EXISTS %I', seq_name); + RETURN OLD; +END; +$$ LANGUAGE plpgsql; +CREATE TRIGGER before_delete_ticket_tracker +BEFORE DELETE ON ticket_trackers +FOR EACH ROW +EXECUTE FUNCTION drop_tracker_ticket_sequence(); + +CREATE OR REPLACE FUNCTION assign_tracker_local_id() +RETURNS TRIGGER AS $$ +DECLARE + seq_name TEXT := 'tracker_ticket_seq_' || NEW.tracker_id; +BEGIN + IF NEW.tracker_local_id IS NULL THEN + EXECUTE format('SELECT nextval(%L)', seq_name) + INTO NEW.tracker_local_id; + END IF; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; +CREATE TRIGGER before_insert_ticket +BEFORE INSERT ON tickets +FOR EACH ROW +EXECUTE FUNCTION assign_tracker_local_id(); + +-- Merge requests + +CREATE TABLE merge_requests ( + id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE, + repo_local_id INTEGER NOT NULL, + title TEXT, + creator INTEGER REFERENCES users(id) ON DELETE SET NULL, + source_ref TEXT NOT NULL, + destination_branch TEXT, + status TEXT NOT NULL CHECK (status IN ('open', 'merged', 'closed')), + UNIQUE (repo_id, repo_local_id), + UNIQUE (repo_id, source_ref, destination_branch) +); + +CREATE OR REPLACE FUNCTION create_repo_mr_sequence() +RETURNS TRIGGER AS $$ +DECLARE + seq_name TEXT := 'repo_mr_seq_' || NEW.id; +BEGIN + EXECUTE format('CREATE SEQUENCE %I', seq_name); + RETURN NEW; +END; +$$ LANGUAGE plpgsql; +CREATE TRIGGER after_insert_repo +AFTER INSERT ON repos +FOR EACH ROW +EXECUTE FUNCTION create_repo_mr_sequence(); + +CREATE OR REPLACE FUNCTION drop_repo_mr_sequence() +RETURNS TRIGGER AS $$ +DECLARE + seq_name TEXT := 'repo_mr_seq_' || OLD.id; +BEGIN + EXECUTE format('DROP SEQUENCE IF EXISTS %I', seq_name); + RETURN OLD; +END; +$$ LANGUAGE plpgsql; +CREATE TRIGGER before_delete_repo +BEFORE DELETE ON repos +FOR EACH ROW +EXECUTE FUNCTION drop_repo_mr_sequence(); + + +CREATE OR REPLACE FUNCTION assign_repo_local_id() +RETURNS TRIGGER AS $$ +DECLARE + seq_name TEXT := 'repo_mr_seq_' || NEW.repo_id; +BEGIN + IF NEW.repo_local_id IS NULL THEN + EXECUTE format('SELECT nextval(%L)', seq_name) + INTO NEW.repo_local_id; + END IF; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; +CREATE TRIGGER before_insert_merge_request +BEFORE INSERT ON merge_requests +FOR EACH ROW +EXECUTE FUNCTION assign_repo_local_id(); |