Per-repo merge request sequences

5 files changed, 79 insertions, 28 deletions

diff --git a/git_hooks_handle_linux.go b/git_hooks_handle_linux.go
index 686d560..812a429 100644
--- a/git_hooks_handle_linux.go
+++ b/git_hooks_handle_linux.go
@@ -229,25 +229,26 @@ func hooksHandler(conn net.Conn) {
 				if strings.HasPrefix(refName, "refs/heads/contrib/") {
 					if allZero(oldOID) { // New branch
 						fmt.Fprintln(sshStderr, ansiec.Blue+"POK"+ansiec.Reset, refName)
-						var newMRID int
+						var newMRLocalID int
 
 						if packPass.userID != 0 {
 							err = database.QueryRow(ctx,
-								"INSERT INTO merge_requests (repo_id, creator, source_ref, status) VALUES ($1, $2, $3, 'open') RETURNING id",
+								"INSERT INTO merge_requests (repo_id, creator, source_ref, status) VALUES ($1, $2, $3, 'open') RETURNING repo_local_id",
 								packPass.repoID, packPass.userID, strings.TrimPrefix(refName, "refs/heads/"),
-							).Scan(&newMRID)
+							).Scan(&newMRLocalID)
 						} else {
 							err = database.QueryRow(ctx,
-								"INSERT INTO merge_requests (repo_id, source_ref, status) VALUES ($1, $2, 'open') RETURNING id",
+								"INSERT INTO merge_requests (repo_id, source_ref, status) VALUES ($1, $2, 'open') RETURNING repo_local_id",
 								packPass.repoID, strings.TrimPrefix(refName, "refs/heads/"),
-							).Scan(&newMRID)
+							).Scan(&newMRLocalID)
 						}
 						if err != nil {
 							writeRedError(sshStderr, "Error creating merge request: %v", err)
 							return 1
 						}
-						mergeRequestWebURL := fmt.Sprintf("%s/contrib/%d/", genHTTPRemoteURL(packPass.groupPath, packPass.repoName), newMRID)
+						mergeRequestWebURL := fmt.Sprintf("%s/contrib/%d/", genHTTPRemoteURL(packPass.groupPath, packPass.repoName), newMRLocalID)
 						fmt.Fprintln(sshStderr, ansiec.Blue+"Created merge request at", mergeRequestWebURL+ansiec.Reset)
+
 						select {
 						case ircSendBuffered <- "PRIVMSG #chat :New merge request at " + mergeRequestWebURL:
 						default:
diff --git a/git_hooks_handle_other.go b/git_hooks_handle_other.go
index 0d197a3..f1b2e04 100644
--- a/git_hooks_handle_other.go
+++ b/git_hooks_handle_other.go
@@ -207,25 +207,26 @@ func hooksHandler(conn net.Conn) {
 				if strings.HasPrefix(refName, "refs/heads/contrib/") {
 					if allZero(oldOID) { // New branch
 						fmt.Fprintln(sshStderr, ansiec.Blue+"POK"+ansiec.Reset, refName)
-						var newMRID int
+						var newMRLocalID int
 
 						if packPass.userID != 0 {
 							err = database.QueryRow(ctx,
-								"INSERT INTO merge_requests (repo_id, creator, source_ref, status) VALUES ($1, $2, $3, 'open') RETURNING id",
+								"INSERT INTO merge_requests (repo_id, creator, source_ref, status) VALUES ($1, $2, $3, 'open') RETURNING repo_local_id",
 								packPass.repoID, packPass.userID, strings.TrimPrefix(refName, "refs/heads/"),
-							).Scan(&newMRID)
+							).Scan(&newMRLocalID)
 						} else {
 							err = database.QueryRow(ctx,
-								"INSERT INTO merge_requests (repo_id, source_ref, status) VALUES ($1, $2, 'open') RETURNING id",
+								"INSERT INTO merge_requests (repo_id, source_ref, status) VALUES ($1, $2, 'open') RETURNING repo_local_id",
 								packPass.repoID, strings.TrimPrefix(refName, "refs/heads/"),
-							).Scan(&newMRID)
+							).Scan(&newMRLocalID)
 						}
 						if err != nil {
 							writeRedError(sshStderr, "Error creating merge request: %v", err)
 							return 1
 						}
-						mergeRequestWebURL := fmt.Sprintf("%s/contrib/%d/", genHTTPRemoteURL(packPass.groupPath, packPass.repoName), newMRID)
+						mergeRequestWebURL := fmt.Sprintf("%s/contrib/%d/", genHTTPRemoteURL(packPass.groupPath, packPass.repoName), newMRLocalID)
 						fmt.Fprintln(sshStderr, ansiec.Blue+"Created merge request at", mergeRequestWebURL+ansiec.Reset)
+
 						select {
 						case ircSendBuffered <- "PRIVMSG #chat :New merge request at " + mergeRequestWebURL:
 						default:
diff --git a/http_handle_repo_contrib_index.go b/http_handle_repo_contrib_index.go
index 4f54836..c199bfa 100644
--- a/http_handle_repo_contrib_index.go
+++ b/http_handle_repo_contrib_index.go
@@ -21,7 +21,7 @@ func httpHandleRepoContribIndex(writer http.ResponseWriter, request *http.Reques
 	var err error
 
 	if rows, err = database.Query(request.Context(),
-		"SELECT id, COALESCE(title, 'Untitled'), status FROM merge_requests WHERE repo_id = $1",
+		"SELECT repo_local_id, COALESCE(title, 'Untitled'), status FROM merge_requests WHERE repo_id = $1",
 		params["repo_id"],
 	); err != nil {
 		errorPage500(writer, params, "Error querying merge requests: "+err.Error())
diff --git a/http_handle_repo_contrib_one.go b/http_handle_repo_contrib_one.go
index 485362d..0fc56fa 100644
--- a/http_handle_repo_contrib_one.go
+++ b/http_handle_repo_contrib_one.go
@@ -32,8 +32,8 @@ func httpHandleRepoContribOne(writer http.ResponseWriter, request *http.Request,
 	mrIDInt = int(mrIDInt64)
 
 	if err = database.QueryRow(request.Context(),
-		"SELECT COALESCE(title, ''), status, source_ref, COALESCE(destination_branch, '') FROM merge_requests WHERE id = $1",
-		mrIDInt,
+		"SELECT COALESCE(title, ''), status, source_ref, COALESCE(destination_branch, '') FROM merge_requests WHERE repo_id = $1 AND repo_local_id = $2",
+		params["repo_id"], mrIDInt,
 	).Scan(&title, &status, &srcRefStr, &dstBranchStr); err != nil {
 		errorPage500(writer, params, "Error querying merge request: "+err.Error())
 		return
diff --git a/sql/schema.sql b/sql/schema.sql
index 0213cea..a6efc39 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -56,19 +56,6 @@ CREATE TABLE sessions (
 	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,
@@ -82,6 +69,7 @@ CREATE TABLE federated_identities (
 	PRIMARY KEY(user_id, service)
 );
 
+-- Ticket tracking
 
 CREATE TABLE ticket_trackers (
 	id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
@@ -144,3 +132,64 @@ CREATE TRIGGER before_insert_ticket
 BEFORE INSERT ON tickets
 FOR EACH ROW
 EXECUTE FUNCTION assign_tracker_local_id();
+
+-- Merge requests
+
+CREATE TABLE merge_requests (
+	id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+	repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
+	repo_local_id INTEGER NOT NULL,
+	title TEXT,
+	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, repo_local_id),
+	UNIQUE (repo_id, source_ref, destination_branch)
+);
+
+CREATE OR REPLACE FUNCTION create_repo_mr_sequence()
+RETURNS TRIGGER AS $$
+DECLARE
+	seq_name TEXT := 'repo_mr_seq_' || NEW.id;
+BEGIN
+	EXECUTE format('CREATE SEQUENCE %I', seq_name);
+	RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+CREATE TRIGGER after_insert_repo
+AFTER INSERT ON repos
+FOR EACH ROW
+EXECUTE FUNCTION create_repo_mr_sequence();
+
+CREATE OR REPLACE FUNCTION drop_repo_mr_sequence()
+RETURNS TRIGGER AS $$
+DECLARE
+	seq_name TEXT := 'repo_mr_seq_' || OLD.id;
+BEGIN
+	EXECUTE format('DROP SEQUENCE IF EXISTS %I', seq_name);
+	RETURN OLD;
+END;
+$$ LANGUAGE plpgsql;
+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 := 'repo_mr_seq_' || 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;
+CREATE TRIGGER before_insert_merge_request
+BEFORE INSERT ON merge_requests
+FOR EACH ROW
+EXECUTE FUNCTION assign_repo_local_id();