From 02dc20f93ed870bf370f7b7efdd3f452ee6fdfa8 Mon Sep 17 00:00:00 2001 From: Runxi Yu Date: Mon, 18 Aug 2025 02:11:26 +0800 Subject: Move sql to inside forged --- Makefile | 4 +- forged/sql/queries/groups.sql | 27 +++++ forged/sql/schema.sql | 225 ++++++++++++++++++++++++++++++++++++++++++ forged/sqlc.yaml | 15 +++ sql/queries/groups.sql | 27 ----- sql/schema.sql | 225 ------------------------------------------ sqlc.yaml | 15 --- 7 files changed, 269 insertions(+), 269 deletions(-) create mode 100644 forged/sql/queries/groups.sql create mode 100644 forged/sql/schema.sql create mode 100644 forged/sqlc.yaml delete mode 100644 sql/queries/groups.sql delete mode 100644 sql/schema.sql delete mode 100644 sqlc.yaml diff --git a/Makefile b/Makefile index 3034676..a894e2b 100644 --- a/Makefile +++ b/Makefile @@ -13,9 +13,9 @@ CFLAGS = -Wall -Wextra -pedantic -std=c99 -D_GNU_SOURCE all: dist/forged dist/git2d dist/hookc -dist/forged: $(shell git ls-files forged) $(shell git ls-files sql) +dist/forged: $(shell git ls-files forged) mkdir -p dist - sqlc generate + sqlc -f forged/sqlc.yaml generate CGO_ENABLED=0 go build -o dist/forged -ldflags '-extldflags "-f no-PIC -static"' -tags 'osusergo netgo static_build' ./forged dist/git2d: $(wildcard git2d/*.c) diff --git a/forged/sql/queries/groups.sql b/forged/sql/queries/groups.sql new file mode 100644 index 0000000..07fe5e7 --- /dev/null +++ b/forged/sql/queries/groups.sql @@ -0,0 +1,27 @@ +-- name: GetGroupIDDescByPath :one +WITH RECURSIVE group_path_cte AS ( + SELECT + id, + parent_group, + name, + 1 AS depth + FROM groups + WHERE name = ($1::text[])[1] + AND parent_group IS NULL + + UNION ALL + + SELECT + g.id, + g.parent_group, + g.name, + group_path_cte.depth + 1 + FROM groups g + JOIN group_path_cte ON g.parent_group = group_path_cte.id + WHERE g.name = ($1::text[])[group_path_cte.depth + 1] + AND group_path_cte.depth + 1 <= cardinality($1::text[]) +) +SELECT c.id, COALESCE(g.description, '') +FROM group_path_cte c +JOIN groups g ON g.id = c.id +WHERE c.depth = cardinality($1::text[]); diff --git a/forged/sql/schema.sql b/forged/sql/schema.sql new file mode 100644 index 0000000..f7216f3 --- /dev/null +++ b/forged/sql/schema.sql @@ -0,0 +1,225 @@ +-- SPDX-License-Identifier: AGPL-3.0-only +-- SPDX-FileCopyrightText: Copyright (c) 2025 Runxi Yu + +-- Currently, slugs accept arbitrary unicode text. We should +-- look into normalization options later. +-- May consider using citext and limiting it to safe characters. + +CREATE TABLE groups ( + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + name TEXT NOT NULL, + parent_group BIGINT REFERENCES groups(id) ON DELETE RESTRICT, + description TEXT, + UNIQUE NULLS NOT DISTINCT (parent_group, name) +); +CREATE INDEX IF NOT EXISTS groups_parent_idx ON groups(parent_group); + +DO $$ BEGIN + CREATE TYPE contrib_requirement AS ENUM ('closed','registered_user','federated','ssh_pubkey','open'); + -- closed means only those with direct access; each layer adds that level of user +EXCEPTION WHEN duplicate_object THEN END $$; +CREATE TABLE repos ( + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + group_id BIGINT NOT NULL REFERENCES groups(id) ON DELETE RESTRICT, -- I mean, should be CASCADE but deleting Git repos on disk also needs to be considered + name TEXT NOT NULL, + description TEXT, + contrib_requirements contrib_requirement NOT NULL, + filesystem_path TEXT NOT NULL, -- does not have to be unique, double-mounting is allowed + UNIQUE(group_id, name) +); +CREATE INDEX IF NOT EXISTS repos_group_idx ON repos(group_id); + +CREATE TABLE mailing_lists ( + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + group_id BIGINT NOT NULL REFERENCES groups(id) ON DELETE RESTRICT, + name TEXT NOT NULL, + description TEXT, + UNIQUE(group_id, name) +); +CREATE INDEX IF NOT EXISTS mailing_lists_group_idx ON mailing_lists(group_id); + +CREATE TABLE mailing_list_emails ( + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + list_id BIGINT NOT NULL REFERENCES mailing_lists(id) ON DELETE CASCADE, + title TEXT NOT NULL, + sender TEXT NOT NULL, + date TIMESTAMPTZ NOT NULL, -- everything must be in UTC + message_id TEXT, -- no uniqueness guarantee as it's arbitrarily set by senders + content BYTEA NOT NULL +); + +DO $$ BEGIN + CREATE TYPE user_type AS ENUM ('pubkey_only','federated','registered','admin'); +EXCEPTION WHEN duplicate_object THEN END $$; +CREATE TABLE users ( + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + username TEXT UNIQUE, -- NULL when, for example, pubkey_only + type user_type NOT NULL, + password_hash TEXT, + created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() +); + +CREATE TABLE ssh_public_keys ( + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, + key_string TEXT NOT NULL, + CONSTRAINT unique_key_string EXCLUDE USING HASH (key_string WITH =) -- because apparently some haxxor like using rsa16384 keys which are too long for a simple UNIQUE constraint :D +); +CREATE INDEX IF NOT EXISTS ssh_keys_user_idx ON ssh_public_keys(user_id); + +CREATE TABLE sessions ( + session_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, + token_hash BYTEA UNIQUE NOT NULL, + created_at TIMESTAMPTZ NOT NULL DEFAULT now(), + expires_at TIMESTAMPTZ NOT NULL +); +CREATE INDEX IF NOT EXISTS sessions_user_idx ON sessions(user_id); + +DO $$ BEGIN + CREATE TYPE group_role AS ENUM ('owner'); -- just owner for now, might need to rethink ACL altogether later; might consider using a join table if we need it to be dynamic, but enum suffices for now +EXCEPTION WHEN duplicate_object THEN END $$; +CREATE TABLE user_group_roles ( + group_id BIGINT NOT NULL REFERENCES groups(id) ON DELETE CASCADE, + user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, + role group_role NOT NULL, + PRIMARY KEY(user_id, group_id) +); +CREATE INDEX IF NOT EXISTS ugr_group_idx ON user_group_roles(group_id); + +CREATE TABLE federated_identities ( + user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT, + service TEXT NOT NULL, -- might need to constrain + remote_username TEXT NOT NULL, + PRIMARY KEY(user_id, service), + UNIQUE(service, remote_username) +); + +CREATE TABLE ticket_trackers ( + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + group_id BIGINT NOT NULL REFERENCES groups(id) ON DELETE RESTRICT, + name TEXT NOT NULL, + description TEXT, + UNIQUE(group_id, name) +); + +CREATE TABLE tickets ( + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + tracker_id BIGINT NOT NULL REFERENCES ticket_trackers(id) ON DELETE CASCADE, + tracker_local_id BIGINT 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 := format('tracker_ticket_seq_%s', NEW.id); +BEGIN + EXECUTE format('CREATE SEQUENCE IF NOT EXISTS %I', seq_name); + RETURN NEW; +END; +$$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION drop_tracker_ticket_sequence() +RETURNS TRIGGER AS $$ +DECLARE + seq_name TEXT := format('tracker_ticket_seq_%s', OLD.id); +BEGIN + EXECUTE format('DROP SEQUENCE IF EXISTS %I', seq_name); + RETURN OLD; +END; +$$ LANGUAGE plpgsql; +DROP TRIGGER IF EXISTS after_insert_ticket_tracker ON ticket_trackers; +CREATE TRIGGER after_insert_ticket_tracker +AFTER INSERT ON ticket_trackers +FOR EACH ROW +EXECUTE FUNCTION create_tracker_ticket_sequence(); +DROP TRIGGER IF EXISTS before_delete_ticket_tracker ON ticket_trackers; +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 := format('tracker_ticket_seq_%s', 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; +DROP TRIGGER IF EXISTS before_insert_ticket ON tickets; +CREATE TRIGGER before_insert_ticket +BEFORE INSERT ON tickets +FOR EACH ROW +EXECUTE FUNCTION assign_tracker_local_id(); +CREATE INDEX IF NOT EXISTS tickets_tracker_idx ON tickets(tracker_id); + +DO $$ BEGIN + CREATE TYPE mr_status AS ENUM ('open','merged','closed'); +EXCEPTION WHEN duplicate_object THEN END $$; + +CREATE TABLE merge_requests ( + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + repo_id BIGINT NOT NULL REFERENCES repos(id) ON DELETE CASCADE, + repo_local_id BIGINT NOT NULL, + title TEXT NOT NULL, + creator BIGINT REFERENCES users(id) ON DELETE SET NULL, + source_repo BIGINT NOT NULL REFERENCES repos(id) ON DELETE RESTRICT, + source_ref TEXT NOT NULL, + destination_branch TEXT, + status mr_status NOT NULL, + UNIQUE (repo_id, repo_local_id) +); +CREATE UNIQUE INDEX IF NOT EXISTS mr_open_src_dst_uniq + ON merge_requests (repo_id, source_repo, source_ref, coalesce(destination_branch, '')) + WHERE status = 'open'; +CREATE INDEX IF NOT EXISTS mr_repo_idx ON merge_requests(repo_id); +CREATE INDEX IF NOT EXISTS mr_creator_idx ON merge_requests(creator); +CREATE OR REPLACE FUNCTION create_repo_mr_sequence() +RETURNS TRIGGER AS $$ +DECLARE + seq_name TEXT := format('repo_mr_seq_%s', NEW.id); +BEGIN + EXECUTE format('CREATE SEQUENCE IF NOT EXISTS %I', seq_name); + RETURN NEW; +END; +$$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION drop_repo_mr_sequence() +RETURNS TRIGGER AS $$ +DECLARE + seq_name TEXT := format('repo_mr_seq_%s', OLD.id); +BEGIN + EXECUTE format('DROP SEQUENCE IF EXISTS %I', seq_name); + RETURN OLD; +END; +$$ LANGUAGE plpgsql; +DROP TRIGGER IF EXISTS after_insert_repo ON repos; +CREATE TRIGGER after_insert_repo +AFTER INSERT ON repos +FOR EACH ROW +EXECUTE FUNCTION create_repo_mr_sequence(); +DROP TRIGGER IF EXISTS before_delete_repo ON repos; +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 := format('repo_mr_seq_%s', 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; +DROP TRIGGER IF EXISTS before_insert_merge_request ON merge_requests; +CREATE TRIGGER before_insert_merge_request +BEFORE INSERT ON merge_requests +FOR EACH ROW +EXECUTE FUNCTION assign_repo_local_id(); diff --git a/forged/sqlc.yaml b/forged/sqlc.yaml new file mode 100644 index 0000000..2b6e035 --- /dev/null +++ b/forged/sqlc.yaml @@ -0,0 +1,15 @@ +version: "2" +sql: + - engine: "postgresql" + schema: "sql/schema.sql" + queries: "sql/queries" + gen: + go: + package: "queries" + out: "internal/database/queries" + sql_package: "pgx/v5" + emit_json_tags: true + emit_db_tags: true + emit_prepared_queries: true + emit_pointers_for_null_types: true + emit_enum_valid_method: true diff --git a/sql/queries/groups.sql b/sql/queries/groups.sql deleted file mode 100644 index 07fe5e7..0000000 --- a/sql/queries/groups.sql +++ /dev/null @@ -1,27 +0,0 @@ --- name: GetGroupIDDescByPath :one -WITH RECURSIVE group_path_cte AS ( - SELECT - id, - parent_group, - name, - 1 AS depth - FROM groups - WHERE name = ($1::text[])[1] - AND parent_group IS NULL - - UNION ALL - - SELECT - g.id, - g.parent_group, - g.name, - group_path_cte.depth + 1 - FROM groups g - JOIN group_path_cte ON g.parent_group = group_path_cte.id - WHERE g.name = ($1::text[])[group_path_cte.depth + 1] - AND group_path_cte.depth + 1 <= cardinality($1::text[]) -) -SELECT c.id, COALESCE(g.description, '') -FROM group_path_cte c -JOIN groups g ON g.id = c.id -WHERE c.depth = cardinality($1::text[]); diff --git a/sql/schema.sql b/sql/schema.sql deleted file mode 100644 index f7216f3..0000000 --- a/sql/schema.sql +++ /dev/null @@ -1,225 +0,0 @@ --- SPDX-License-Identifier: AGPL-3.0-only --- SPDX-FileCopyrightText: Copyright (c) 2025 Runxi Yu - --- Currently, slugs accept arbitrary unicode text. We should --- look into normalization options later. --- May consider using citext and limiting it to safe characters. - -CREATE TABLE groups ( - id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - name TEXT NOT NULL, - parent_group BIGINT REFERENCES groups(id) ON DELETE RESTRICT, - description TEXT, - UNIQUE NULLS NOT DISTINCT (parent_group, name) -); -CREATE INDEX IF NOT EXISTS groups_parent_idx ON groups(parent_group); - -DO $$ BEGIN - CREATE TYPE contrib_requirement AS ENUM ('closed','registered_user','federated','ssh_pubkey','open'); - -- closed means only those with direct access; each layer adds that level of user -EXCEPTION WHEN duplicate_object THEN END $$; -CREATE TABLE repos ( - id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - group_id BIGINT NOT NULL REFERENCES groups(id) ON DELETE RESTRICT, -- I mean, should be CASCADE but deleting Git repos on disk also needs to be considered - name TEXT NOT NULL, - description TEXT, - contrib_requirements contrib_requirement NOT NULL, - filesystem_path TEXT NOT NULL, -- does not have to be unique, double-mounting is allowed - UNIQUE(group_id, name) -); -CREATE INDEX IF NOT EXISTS repos_group_idx ON repos(group_id); - -CREATE TABLE mailing_lists ( - id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - group_id BIGINT NOT NULL REFERENCES groups(id) ON DELETE RESTRICT, - name TEXT NOT NULL, - description TEXT, - UNIQUE(group_id, name) -); -CREATE INDEX IF NOT EXISTS mailing_lists_group_idx ON mailing_lists(group_id); - -CREATE TABLE mailing_list_emails ( - id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - list_id BIGINT NOT NULL REFERENCES mailing_lists(id) ON DELETE CASCADE, - title TEXT NOT NULL, - sender TEXT NOT NULL, - date TIMESTAMPTZ NOT NULL, -- everything must be in UTC - message_id TEXT, -- no uniqueness guarantee as it's arbitrarily set by senders - content BYTEA NOT NULL -); - -DO $$ BEGIN - CREATE TYPE user_type AS ENUM ('pubkey_only','federated','registered','admin'); -EXCEPTION WHEN duplicate_object THEN END $$; -CREATE TABLE users ( - id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - username TEXT UNIQUE, -- NULL when, for example, pubkey_only - type user_type NOT NULL, - password_hash TEXT, - created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() -); - -CREATE TABLE ssh_public_keys ( - id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, - key_string TEXT NOT NULL, - CONSTRAINT unique_key_string EXCLUDE USING HASH (key_string WITH =) -- because apparently some haxxor like using rsa16384 keys which are too long for a simple UNIQUE constraint :D -); -CREATE INDEX IF NOT EXISTS ssh_keys_user_idx ON ssh_public_keys(user_id); - -CREATE TABLE sessions ( - session_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, - token_hash BYTEA UNIQUE NOT NULL, - created_at TIMESTAMPTZ NOT NULL DEFAULT now(), - expires_at TIMESTAMPTZ NOT NULL -); -CREATE INDEX IF NOT EXISTS sessions_user_idx ON sessions(user_id); - -DO $$ BEGIN - CREATE TYPE group_role AS ENUM ('owner'); -- just owner for now, might need to rethink ACL altogether later; might consider using a join table if we need it to be dynamic, but enum suffices for now -EXCEPTION WHEN duplicate_object THEN END $$; -CREATE TABLE user_group_roles ( - group_id BIGINT NOT NULL REFERENCES groups(id) ON DELETE CASCADE, - user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, - role group_role NOT NULL, - PRIMARY KEY(user_id, group_id) -); -CREATE INDEX IF NOT EXISTS ugr_group_idx ON user_group_roles(group_id); - -CREATE TABLE federated_identities ( - user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT, - service TEXT NOT NULL, -- might need to constrain - remote_username TEXT NOT NULL, - PRIMARY KEY(user_id, service), - UNIQUE(service, remote_username) -); - -CREATE TABLE ticket_trackers ( - id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - group_id BIGINT NOT NULL REFERENCES groups(id) ON DELETE RESTRICT, - name TEXT NOT NULL, - description TEXT, - UNIQUE(group_id, name) -); - -CREATE TABLE tickets ( - id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - tracker_id BIGINT NOT NULL REFERENCES ticket_trackers(id) ON DELETE CASCADE, - tracker_local_id BIGINT 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 := format('tracker_ticket_seq_%s', NEW.id); -BEGIN - EXECUTE format('CREATE SEQUENCE IF NOT EXISTS %I', seq_name); - RETURN NEW; -END; -$$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION drop_tracker_ticket_sequence() -RETURNS TRIGGER AS $$ -DECLARE - seq_name TEXT := format('tracker_ticket_seq_%s', OLD.id); -BEGIN - EXECUTE format('DROP SEQUENCE IF EXISTS %I', seq_name); - RETURN OLD; -END; -$$ LANGUAGE plpgsql; -DROP TRIGGER IF EXISTS after_insert_ticket_tracker ON ticket_trackers; -CREATE TRIGGER after_insert_ticket_tracker -AFTER INSERT ON ticket_trackers -FOR EACH ROW -EXECUTE FUNCTION create_tracker_ticket_sequence(); -DROP TRIGGER IF EXISTS before_delete_ticket_tracker ON ticket_trackers; -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 := format('tracker_ticket_seq_%s', 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; -DROP TRIGGER IF EXISTS before_insert_ticket ON tickets; -CREATE TRIGGER before_insert_ticket -BEFORE INSERT ON tickets -FOR EACH ROW -EXECUTE FUNCTION assign_tracker_local_id(); -CREATE INDEX IF NOT EXISTS tickets_tracker_idx ON tickets(tracker_id); - -DO $$ BEGIN - CREATE TYPE mr_status AS ENUM ('open','merged','closed'); -EXCEPTION WHEN duplicate_object THEN END $$; - -CREATE TABLE merge_requests ( - id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - repo_id BIGINT NOT NULL REFERENCES repos(id) ON DELETE CASCADE, - repo_local_id BIGINT NOT NULL, - title TEXT NOT NULL, - creator BIGINT REFERENCES users(id) ON DELETE SET NULL, - source_repo BIGINT NOT NULL REFERENCES repos(id) ON DELETE RESTRICT, - source_ref TEXT NOT NULL, - destination_branch TEXT, - status mr_status NOT NULL, - UNIQUE (repo_id, repo_local_id) -); -CREATE UNIQUE INDEX IF NOT EXISTS mr_open_src_dst_uniq - ON merge_requests (repo_id, source_repo, source_ref, coalesce(destination_branch, '')) - WHERE status = 'open'; -CREATE INDEX IF NOT EXISTS mr_repo_idx ON merge_requests(repo_id); -CREATE INDEX IF NOT EXISTS mr_creator_idx ON merge_requests(creator); -CREATE OR REPLACE FUNCTION create_repo_mr_sequence() -RETURNS TRIGGER AS $$ -DECLARE - seq_name TEXT := format('repo_mr_seq_%s', NEW.id); -BEGIN - EXECUTE format('CREATE SEQUENCE IF NOT EXISTS %I', seq_name); - RETURN NEW; -END; -$$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION drop_repo_mr_sequence() -RETURNS TRIGGER AS $$ -DECLARE - seq_name TEXT := format('repo_mr_seq_%s', OLD.id); -BEGIN - EXECUTE format('DROP SEQUENCE IF EXISTS %I', seq_name); - RETURN OLD; -END; -$$ LANGUAGE plpgsql; -DROP TRIGGER IF EXISTS after_insert_repo ON repos; -CREATE TRIGGER after_insert_repo -AFTER INSERT ON repos -FOR EACH ROW -EXECUTE FUNCTION create_repo_mr_sequence(); -DROP TRIGGER IF EXISTS before_delete_repo ON repos; -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 := format('repo_mr_seq_%s', 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; -DROP TRIGGER IF EXISTS before_insert_merge_request ON merge_requests; -CREATE TRIGGER before_insert_merge_request -BEFORE INSERT ON merge_requests -FOR EACH ROW -EXECUTE FUNCTION assign_repo_local_id(); diff --git a/sqlc.yaml b/sqlc.yaml deleted file mode 100644 index ed5c158..0000000 --- a/sqlc.yaml +++ /dev/null @@ -1,15 +0,0 @@ -version: "2" -sql: - - engine: "postgresql" - schema: "sql/schema.sql" - queries: "sql/queries" - gen: - go: - package: "queries" - out: "forged/internal/database/queries" - sql_package: "pgx/v5" - emit_json_tags: true - emit_db_tags: true - emit_prepared_queries: true - emit_pointers_for_null_types: true - emit_enum_valid_method: true -- cgit v1.2.3