scripts, sql: Reorganize

3 files changed, 130 insertions, 0 deletions

diff --git a/sql/purge.sql b/sql/purge.sql
new file mode 100644
index 0000000..03eecc3
--- /dev/null
+++ b/sql/purge.sql
@@ -0,0 +1,2 @@
+DROP SCHEMA public CASCADE;
+CREATE SCHEMA public;
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)
+);
diff --git a/sql/test.sql b/sql/test.sql
new file mode 100644
index 0000000..b270b76
--- /dev/null
+++ b/sql/test.sql
@@ -0,0 +1,37 @@
+WITH parent_group AS (
+	INSERT INTO groups (name, description)
+	VALUES ('lindenii', 'The Lindenii Project')
+	RETURNING id
+),
+child_group AS (
+	INSERT INTO groups (name, description, parent_group)
+	SELECT 'forge', 'Lindenii Forge', id
+	FROM parent_group
+	RETURNING id
+),
+create_repos AS (
+	INSERT INTO repos (name, group_id, contrib_requirements, filesystem_path)
+	SELECT 'server', id, 'public', '/home/runxiyu/Lindenii/forge/server/.git'
+	FROM child_group
+),
+new_user AS (
+	INSERT INTO users (username, type, password)
+	VALUES ('test', 'registered', '$argon2id$v=19$m=4096,t=3,p=1$YWFhYWFhYWFhYWFh$i40k7TPFHqXRH4eQOAYGH3LvzwQ38jqqlfap9Rtiy3c')
+	RETURNING id
+),
+new_ssh AS (
+	INSERT INTO ssh_public_keys (key_string, user_id)
+	SELECT 'ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIAuavKDhEM1L6CufIecy2P712gp151CqZuwSYahTWvmq', id
+	FROM new_user
+	RETURNING user_id
+)
+INSERT INTO user_group_roles (group_id, user_id)
+SELECT child_group.id, new_ssh.user_id
+FROM child_group, new_ssh;
+
+SELECT * FROM groups;
+SELECT * FROM repos;
+SELECT * FROM users;
+SELECT * FROM ssh_public_keys;
+SELECT * FROM user_group_roles;
+