Add an unbuilt_outputs table

One of the slow things in the derivation ordered allocator is working out what
outputs are unbuilt, as this requires looking at all the derivation
outputs (of which there are lots), and checking if any build exists which has
succeeded.

7 files changed, 55 insertions, 0 deletions

diff --git a/sqitch/pg/deploy/add_unbuilt_outputs_table.sql b/sqitch/pg/deploy/add_unbuilt_outputs_table.sql
new file mode 100644
index 0000000..430f775
--- /dev/null
+++ b/sqitch/pg/deploy/add_unbuilt_outputs_table.sql
@@ -0,0 +1,7 @@
+-- Deploy guix-build-coordinator:add_unbuilt_outputs_table to pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/revert/add_unbuilt_outputs_table.sql b/sqitch/pg/revert/add_unbuilt_outputs_table.sql
new file mode 100644
index 0000000..2c1bd78
--- /dev/null
+++ b/sqitch/pg/revert/add_unbuilt_outputs_table.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:add_unbuilt_outputs_table from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/verify/add_unbuilt_outputs_table.sql b/sqitch/pg/verify/add_unbuilt_outputs_table.sql
new file mode 100644
index 0000000..1cefc9a
--- /dev/null
+++ b/sqitch/pg/verify/add_unbuilt_outputs_table.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:add_unbuilt_outputs_table on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 7df0842..c0a87ed 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -21,3 +21,4 @@ build_starts 2020-06-27T20:23:38Z Christopher Baines <mail@cbaines.net> # Add bu
 add_builds_end_time 2020-06-27T20:58:01Z Christopher Baines <mail@cbaines.net> # Add builds.end_time field
 build_allocation_agent_requested_systems 2020-07-02T19:45:02Z Christopher Baines <mail@cbaines.net> # Add new build_allocation_agent_requested_systems table
 add_fixed_output_to_derivations 2020-07-18T14:49:51Z Christopher Baines <mail@cbaines.net> # Add derivations.fixed_output
+add_unbuilt_outputs_table 2020-11-06T18:58:08Z Christopher Baines <mail@cbaines.net> # Add unbuilt_outputs
diff --git a/sqitch/sqlite/deploy/add_unbuilt_outputs_table.sql b/sqitch/sqlite/deploy/add_unbuilt_outputs_table.sql
new file mode 100644
index 0000000..b1a0dc5
--- /dev/null
+++ b/sqitch/sqlite/deploy/add_unbuilt_outputs_table.sql
@@ -0,0 +1,19 @@
+-- Deploy guix-build-coordinator:add_unbuilt_outputs_table to sqlite
+
+BEGIN;
+
+CREATE TABLE unbuilt_outputs (output TEXT PRIMARY KEY);
+
+INSERT INTO unbuilt_outputs
+  SELECT output
+  FROM derivation_outputs
+  EXCEPT
+    SELECT derivation_outputs.output
+    FROM builds
+    INNER JOIN build_results
+      ON builds.uuid = build_results.build_id
+    INNER JOIN derivation_outputs
+      ON builds.derivation_name = derivation_outputs.derivation_name
+    WHERE build_results.result = 'success';
+
+COMMIT;
diff --git a/sqitch/sqlite/revert/add_unbuilt_outputs_table.sql b/sqitch/sqlite/revert/add_unbuilt_outputs_table.sql
new file mode 100644
index 0000000..0cd572e
--- /dev/null
+++ b/sqitch/sqlite/revert/add_unbuilt_outputs_table.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:add_unbuilt_outputs_table from sqlite
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqlite/verify/add_unbuilt_outputs_table.sql b/sqitch/sqlite/verify/add_unbuilt_outputs_table.sql
new file mode 100644
index 0000000..ea7fac0
--- /dev/null
+++ b/sqitch/sqlite/verify/add_unbuilt_outputs_table.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:add_unbuilt_outputs_table on sqlite
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;