From db3253c44336bfafbf9fef7ba408ec99b0f131c2 Mon Sep 17 00:00:00 2001 From: Runxi Yu Date: Thu, 6 Mar 2025 22:37:06 +0800 Subject: scripts, sql: Reorganize --- lint | 3 -- purge.sql | 2 -- resources.go | 3 +- schema.sql | 91 ----------------------------------------------------- scripts/lint | 3 ++ scripts/update_deps | 7 +++++ sql/purge.sql | 2 ++ sql/schema.sql | 91 +++++++++++++++++++++++++++++++++++++++++++++++++++++ sql/test.sql | 37 ++++++++++++++++++++++ test.sql | 37 ---------------------- update_deps | 7 ----- 11 files changed, 141 insertions(+), 142 deletions(-) delete mode 100755 lint delete mode 100644 purge.sql delete mode 100644 schema.sql create mode 100755 scripts/lint create mode 100755 scripts/update_deps create mode 100644 sql/purge.sql create mode 100644 sql/schema.sql create mode 100644 sql/test.sql delete mode 100644 test.sql delete mode 100755 update_deps diff --git a/lint b/lint deleted file mode 100755 index 7850f0e..0000000 --- a/lint +++ /dev/null @@ -1,3 +0,0 @@ -#!/bin/sh - -golangci-lint run . --enable-all --disable wsl,wrapcheck,nlreturn,nonamedreturns,mnd,lll,intrange,godox,gochecknoglobals,gochecknoinits,forcetypeassert,gofmt,gofumpt,revive,stylecheck,exhaustruct,godot,unparam,err113,depguard diff --git a/purge.sql b/purge.sql deleted file mode 100644 index 03eecc3..0000000 --- a/purge.sql +++ /dev/null @@ -1,2 +0,0 @@ -DROP SCHEMA public CASCADE; -CREATE SCHEMA public; diff --git a/resources.go b/resources.go index 6c3cece..20d4484 100644 --- a/resources.go +++ b/resources.go @@ -17,8 +17,7 @@ import ( //go:embed *.go go.mod go.sum //go:embed *.scfg //go:embed Makefile -//go:embed schema.sql -//go:embed static/* templates/* +//go:embed static/* templates/* scripts/* sql/* //go:embed git_hooks_client/*.c //go:embed vendor/* var source_fs embed.FS 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 - -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/scripts/lint b/scripts/lint new file mode 100755 index 0000000..7850f0e --- /dev/null +++ b/scripts/lint @@ -0,0 +1,3 @@ +#!/bin/sh + +golangci-lint run . --enable-all --disable wsl,wrapcheck,nlreturn,nonamedreturns,mnd,lll,intrange,godox,gochecknoglobals,gochecknoinits,forcetypeassert,gofmt,gofumpt,revive,stylecheck,exhaustruct,godot,unparam,err113,depguard diff --git a/scripts/update_deps b/scripts/update_deps new file mode 100755 index 0000000..723e858 --- /dev/null +++ b/scripts/update_deps @@ -0,0 +1,7 @@ +#!/bin/sh + +set -eux + +go get -t -u +go mod tidy +go get github.com/go-git/go-git/v5@main 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 + +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; + diff --git a/test.sql b/test.sql deleted file mode 100644 index b270b76..0000000 --- a/test.sql +++ /dev/null @@ -1,37 +0,0 @@ -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; - diff --git a/update_deps b/update_deps deleted file mode 100755 index 723e858..0000000 --- a/update_deps +++ /dev/null @@ -1,7 +0,0 @@ -#!/bin/sh - -set -eux - -go get -t -u -go mod tidy -go get github.com/go-git/go-git/v5@main -- cgit v1.2.3