From 3768aec91daebb8db58e28cffe481e8878b59700 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Fri, 28 Oct 2022 11:50:45 +0100 Subject: 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. --- sqitch/sqlite/deploy/build_counts.sql | 12 ++++++++++++ sqitch/sqlite/deploy/build_results_counts.sql | 10 ++++++++++ sqitch/sqlite/revert/build_counts.sql | 7 +++++++ sqitch/sqlite/revert/build_results_counts.sql | 7 +++++++ sqitch/sqlite/verify/build_counts.sql | 7 +++++++ sqitch/sqlite/verify/build_results_counts.sql | 7 +++++++ 6 files changed, 50 insertions(+) create mode 100644 sqitch/sqlite/deploy/build_counts.sql create mode 100644 sqitch/sqlite/deploy/build_results_counts.sql create mode 100644 sqitch/sqlite/revert/build_counts.sql create mode 100644 sqitch/sqlite/revert/build_results_counts.sql create mode 100644 sqitch/sqlite/verify/build_counts.sql create mode 100644 sqitch/sqlite/verify/build_results_counts.sql (limited to 'sqitch/sqlite') 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; -- cgit v1.2.3