From db3253c44336bfafbf9fef7ba408ec99b0f131c2 Mon Sep 17 00:00:00 2001
From: Runxi Yu <me@runxiyu.org>
Date: Thu, 6 Mar 2025 22:37:06 +0800
Subject: scripts, sql: Reorganize

---
 schema.sql | 91 --------------------------------------------------------------
 1 file changed, 91 deletions(-)
 delete mode 100644 schema.sql

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
deleted file mode 100644
index e1d18f8..0000000
--- a/schema.sql
+++ /dev/null
@@ -1,91 +0,0 @@
--- 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)
-);
-- 
cgit v1.2.3