scripts, sql: Reorganize

1 files changed, 91 insertions, 0 deletions

diff --git a/sql/schema.sql b/sql/schema.sql
new file mode 100644
index 0000000..e1d18f8
--- /dev/null
+++ b/sql/schema.sql
@@ -0,0 +1,91 @@
+-- SPDX-License-Identifier: AGPL-3.0-only
+-- SPDX-FileContributor: 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', 'ssh_pubkey', 'public')),
+	name TEXT NOT NULL,
+	UNIQUE(group_id, name),
+	description TEXT,
+	filesystem_path TEXT
+);
+
+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,
+	UNIQUE(group_id, name),
+	description TEXT
+);
+
+CREATE TABLE tickets (
+	id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+	tracker_id INTEGER NOT NULL REFERENCES ticket_trackers(id) ON DELETE CASCADE,
+	title TEXT NOT NULL,
+	description 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', '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)
+);
+
+-- TODO:
+CREATE TABLE merge_requests (
+	id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+	title TEXT,
+	repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
+	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, source_ref, destination_branch),
+	UNIQUE (repo_id, 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)
+);