Store build and build results counts in the database

Rather than trying to count all builds and build results at startup. This
should speed up the coordinator starting up, as currently it gets slower the
more builds and build results are in the database.

6 files changed, 50 insertions, 0 deletions

diff --git a/sqitch/sqlite/deploy/build_counts.sql b/sqitch/sqlite/deploy/build_counts.sql
new file mode 100644
index 0000000..0d14776
--- /dev/null
+++ b/sqitch/sqlite/deploy/build_counts.sql
@@ -0,0 +1,12 @@
+-- Deploy guix-build-coordinator:build_counts to sqlite
+
+BEGIN;
+
+CREATE TABLE builds_counts AS
+SELECT derivations.system_id, COUNT(*) AS count
+FROM builds
+INNER JOIN derivations
+  ON builds.derivation_id = derivations.id
+GROUP BY derivations.system_id;
+
+COMMIT;
diff --git a/sqitch/sqlite/deploy/build_results_counts.sql b/sqitch/sqlite/deploy/build_results_counts.sql
new file mode 100644
index 0000000..14e7f9e
--- /dev/null
+++ b/sqitch/sqlite/deploy/build_results_counts.sql
@@ -0,0 +1,10 @@
+-- Deploy guix-build-coordinator:build_results_counts to sqlite
+
+BEGIN;
+
+CREATE TABLE build_results_counts AS
+SELECT agent_id, result, COUNT(*) AS count
+FROM build_results
+GROUP BY agent_id, result;
+
+COMMIT;
diff --git a/sqitch/sqlite/revert/build_counts.sql b/sqitch/sqlite/revert/build_counts.sql
new file mode 100644
index 0000000..232f813
--- /dev/null
+++ b/sqitch/sqlite/revert/build_counts.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:build_counts from sqlite
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqlite/revert/build_results_counts.sql b/sqitch/sqlite/revert/build_results_counts.sql
new file mode 100644
index 0000000..ed4338a
--- /dev/null
+++ b/sqitch/sqlite/revert/build_results_counts.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:build_results_counts from sqlite
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqlite/verify/build_counts.sql b/sqitch/sqlite/verify/build_counts.sql
new file mode 100644
index 0000000..6194b45
--- /dev/null
+++ b/sqitch/sqlite/verify/build_counts.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:build_counts on sqlite
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
diff --git a/sqitch/sqlite/verify/build_results_counts.sql b/sqitch/sqlite/verify/build_results_counts.sql
new file mode 100644
index 0000000..6781084
--- /dev/null
+++ b/sqitch/sqlite/verify/build_results_counts.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:build_results_counts on sqlite
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;