From 5f80856ad1587aea38ee4e7b54db558cdb43f6b7 Mon Sep 17 00:00:00 2001
From: Christopher Baines <mail@cbaines.net>
Date: Thu, 7 Mar 2024 09:44:47 +0000
Subject: Allow prioritising jobs from specific repositories

To help the QA data service prioritise branches over patches.
---
 guix-data-service/jobs/load-new-guix-revision.scm | 16 +++++++++++++---
 sqitch/deploy/git_repositories_job_priority.sql   |  7 +++++++
 sqitch/revert/git_repositories_job_priority.sql   |  7 +++++++
 sqitch/sqitch.plan                                |  1 +
 sqitch/verify/git_repositories_job_priority.sql   |  7 +++++++
 5 files changed, 35 insertions(+), 3 deletions(-)
 create mode 100644 sqitch/deploy/git_repositories_job_priority.sql
 create mode 100644 sqitch/revert/git_repositories_job_priority.sql
 create mode 100644 sqitch/verify/git_repositories_job_priority.sql

diff --git a/guix-data-service/jobs/load-new-guix-revision.scm b/guix-data-service/jobs/load-new-guix-revision.scm
index 8531edf..b0493ca 100644
--- a/guix-data-service/jobs/load-new-guix-revision.scm
+++ b/guix-data-service/jobs/load-new-guix-revision.scm
@@ -1898,6 +1898,9 @@ SELECT
     ) branches_and_latest_commits
   ) AS latest_branch_commit
 FROM load_new_guix_revision_jobs
+INNER JOIN git_repositories
+  ON load_new_guix_revision_jobs.git_repository_id =
+     git_repositories.id
 WHERE
   succeeded_at IS NULL AND
   (
@@ -1909,7 +1912,9 @@ WHERE
     FROM load_new_guix_revision_job_events
     WHERE job_id = load_new_guix_revision_jobs.id AND event = 'failure'
   )
-ORDER BY latest_branch_commit DESC, id DESC")
+ORDER BY latest_branch_commit DESC,
+         git_repositories.job_priority DESC,
+         id DESC")
 
   (map
    (match-lambda
@@ -2021,7 +2026,7 @@ WHERE id = $1 ")
 (define (fetch-unlocked-jobs conn)
   (define query "
 SELECT
-  id,
+  load_new_guix_revision_jobs.id,
   commit IN (
     SELECT commit FROM (
       SELECT DISTINCT ON (name)
@@ -2035,6 +2040,9 @@ SELECT
     ) branches_and_latest_commits
   ) AS latest_branch_commit
 FROM load_new_guix_revision_jobs
+INNER JOIN git_repositories
+  ON load_new_guix_revision_jobs.git_repository_id =
+     git_repositories.id
 WHERE
   succeeded_at IS NULL AND
   (
@@ -2046,7 +2054,9 @@ WHERE
     FROM load_new_guix_revision_job_events
     WHERE job_id = load_new_guix_revision_jobs.id AND event = 'failure'
   )
-ORDER BY latest_branch_commit DESC, id DESC
+ORDER BY latest_branch_commit DESC,
+         git_repositories.job_priority DESC,
+         load_new_guix_revision_jobs.id DESC
 FOR NO KEY UPDATE OF load_new_guix_revision_jobs
 SKIP LOCKED")
 
diff --git a/sqitch/deploy/git_repositories_job_priority.sql b/sqitch/deploy/git_repositories_job_priority.sql
new file mode 100644
index 0000000..65f44e3
--- /dev/null
+++ b/sqitch/deploy/git_repositories_job_priority.sql
@@ -0,0 +1,7 @@
+-- Deploy guix-data-service:git_repositories_job_priority to pg
+
+BEGIN;
+
+ALTER TABLE git_repositories ADD COLUMN job_priority INTEGER NOT NULL DEFAULT 0;
+
+COMMIT;
diff --git a/sqitch/revert/git_repositories_job_priority.sql b/sqitch/revert/git_repositories_job_priority.sql
new file mode 100644
index 0000000..9e80121
--- /dev/null
+++ b/sqitch/revert/git_repositories_job_priority.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:git_repositories_job_priority from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index a4f14e8..14232e3 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -97,3 +97,4 @@ guix_revision_package_derivation_distribution_counts 2023-03-08T16:53:44Z Chris
 cascade_nar_foreign_keys 2023-08-01T09:42:33Z Chris <chris@felis> # Make it easier to delete nars entries
 nar_indexes 2023-08-01T11:37:35Z Chris <chris@felis> # Add nar related indexes
 git_repositories_poll_interval 2023-10-08T20:36:09Z Chris <chris@felis> # Add git_repositories.poll_interval
+git_repositories_job_priority 2024-03-07T09:39:27Z Chris <chris@felis> # Add git_repositories.job_priority
diff --git a/sqitch/verify/git_repositories_job_priority.sql b/sqitch/verify/git_repositories_job_priority.sql
new file mode 100644
index 0000000..1ffd45e
--- /dev/null
+++ b/sqitch/verify/git_repositories_job_priority.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:git_repositories_job_priority on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
-- 
cgit v1.2.3