From 7e807facb43f146d462a6f4fc67d08e919886a06 Mon Sep 17 00:00:00 2001
From: Christopher Baines <mail@cbaines.net>
Date: Wed, 19 Feb 2025 11:24:21 +0000
Subject: Replace the builds_unprocessed index

With builds_live, for all unprocessed and non canceled builds. This should be
more useful, especially for queries to cancel builds.
---
 sqitch/pg/deploy/builds_replace_unprocessed_index.sql     | 7 +++++++
 sqitch/pg/revert/builds_replace_unprocessed_index.sql     | 7 +++++++
 sqitch/pg/verify/builds_replace_unprocessed_index.sql     | 7 +++++++
 sqitch/sqitch.plan                                        | 1 +
 sqitch/sqlite/deploy/builds_replace_unprocessed_index.sql | 9 +++++++++
 sqitch/sqlite/revert/builds_replace_unprocessed_index.sql | 7 +++++++
 sqitch/sqlite/verify/builds_replace_unprocessed_index.sql | 7 +++++++
 7 files changed, 45 insertions(+)
 create mode 100644 sqitch/pg/deploy/builds_replace_unprocessed_index.sql
 create mode 100644 sqitch/pg/revert/builds_replace_unprocessed_index.sql
 create mode 100644 sqitch/pg/verify/builds_replace_unprocessed_index.sql
 create mode 100644 sqitch/sqlite/deploy/builds_replace_unprocessed_index.sql
 create mode 100644 sqitch/sqlite/revert/builds_replace_unprocessed_index.sql
 create mode 100644 sqitch/sqlite/verify/builds_replace_unprocessed_index.sql

diff --git a/sqitch/pg/deploy/builds_replace_unprocessed_index.sql b/sqitch/pg/deploy/builds_replace_unprocessed_index.sql
new file mode 100644
index 0000000..782cf6b
--- /dev/null
+++ b/sqitch/pg/deploy/builds_replace_unprocessed_index.sql
@@ -0,0 +1,7 @@
+-- Deploy guix-build-coordinator:builds_replace_unprocessed_index to pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/revert/builds_replace_unprocessed_index.sql b/sqitch/pg/revert/builds_replace_unprocessed_index.sql
new file mode 100644
index 0000000..4395f90
--- /dev/null
+++ b/sqitch/pg/revert/builds_replace_unprocessed_index.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:builds_replace_unprocessed_index from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/verify/builds_replace_unprocessed_index.sql b/sqitch/pg/verify/builds_replace_unprocessed_index.sql
new file mode 100644
index 0000000..050ef75
--- /dev/null
+++ b/sqitch/pg/verify/builds_replace_unprocessed_index.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:builds_replace_unprocessed_index on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 83de06b..ee9dd7a 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -47,3 +47,4 @@ remove_build_allocation_plan 2023-04-23T19:50:23Z Chris <chris@felis> # Remove b
 system_uptime 2023-05-05T18:18:35Z Chris <chris@felis> # Add system uptime
 build_starts_index 2023-11-24T16:30:13Z Chris <chris@felis> # build_starts index
 background-jobs-queue 2025-02-06T10:49:08Z Chris <chris@fang> # Add background_jobs_queue
+builds_replace_unprocessed_index 2025-02-19T11:19:42Z Chris <chris@fang> # Replace builds_unprocessed
diff --git a/sqitch/sqlite/deploy/builds_replace_unprocessed_index.sql b/sqitch/sqlite/deploy/builds_replace_unprocessed_index.sql
new file mode 100644
index 0000000..a404f31
--- /dev/null
+++ b/sqitch/sqlite/deploy/builds_replace_unprocessed_index.sql
@@ -0,0 +1,9 @@
+-- Deploy guix-build-coordinator:builds_replace_unprocessed_index to sqlite
+
+BEGIN;
+
+DROP INDEX builds_unprocessed;
+
+CREATE INDEX builds_live ON builds (id) WHERE processed = 0 AND canceled = 0;
+
+COMMIT;
diff --git a/sqitch/sqlite/revert/builds_replace_unprocessed_index.sql b/sqitch/sqlite/revert/builds_replace_unprocessed_index.sql
new file mode 100644
index 0000000..b02dd3c
--- /dev/null
+++ b/sqitch/sqlite/revert/builds_replace_unprocessed_index.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:builds_replace_unprocessed_index from sqlite
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqlite/verify/builds_replace_unprocessed_index.sql b/sqitch/sqlite/verify/builds_replace_unprocessed_index.sql
new file mode 100644
index 0000000..d0dd086
--- /dev/null
+++ b/sqitch/sqlite/verify/builds_replace_unprocessed_index.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:builds_replace_unprocessed_index on sqlite
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
-- 
cgit v1.2.3