Merge ~cjwatson/launchpad:db-builder-resources into launchpad:db-devel

[Colin Watson]

Add columns for builder resources and constraints

This will allow us to mark some builders as having extra hardware resources available to them, and to mark certain Git repositories as requiring those for their CI builds. (If necessary, this could be extended to other build types in future.) This amounts to partitioning the build farm, and we should do that cautiously to avoid reducing overall throughput too much; however, we don't always have a practical alternative, and our build farm is much better-provisioned than it used to be.

The distinction between open and restricted resources allows us to mark some builders as having extra features that might be required by some builds but as being available for use by other builds (e.g. we might put "large" builders in this category), while also having some builders that may only be used by builds that need their features (e.g. we might put specialized GPU hardware in this category).

[Andrey Fedoseev (andrey-fedoseev)]

review: Approve

[Andrey Fedoseev (andrey-fedoseev)]

Colin,

Have you considered using arrays instead of `jsonb` for the new columns?

[Colin Watson (cjwatson)]

It mostly just didn't really occur to me, because my basic mental model nowadays has `jsonb` as being sufficiently capable that I can drop it in as a replacement for things like that.

Are you thinking of arrays because they're more strongly-typed? One concern I have is that I'm not 100% sure that this simple resources/constraints system will be entirely sufficient in the long term; with `jsonb` it would be fairly easy to extend the array elements to be something more sophisticated than strings if we needed to.

[Andrey Fedoseev (andrey-fedoseev)]

I don't have strong preference for using arrays here.

I actually used `jsonb` in a similar case (https://code.launchpad.net/~andrey-fedoseev/launchpad/+git/launchpad/+merge/423916), but then changed it to `text[]` following William's suggestion here https://chat.canonical.com/canonical/pl/pztnxzshp7gqpxtigi1xh5y1hc

I defer this to William :)

review: Approve

[William Grant (wgrant)]

review: Approve (db)

diff --git a/database/schema/patch-2211-13-0.sql b/database/schema/patch-2211-13-0.sql
new file mode 100644
index 0000000..56fa9a1
--- /dev/null
+++ b/database/schema/patch-2211-13-0.sql
@@ -0,0 +1,19 @@
+-- Copyright 2022 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+ALTER TABLE Builder
+    ADD COLUMN open_resources jsonb,
+    ADD COLUMN restricted_resources jsonb;
+ALTER TABLE BuildQueue ADD COLUMN builder_constraints jsonb;
+ALTER TABLE CIBuild ADD COLUMN builder_constraints jsonb;
+ALTER TABLE GitRepository ADD COLUMN builder_constraints jsonb;
+
+COMMENT ON COLUMN Builder.open_resources IS 'An array of resource tags offered by this builder, that can be required by a build and if required must match.';
+COMMENT ON COLUMN Builder.restricted_resources IS 'An array of resource tags offered by this builder, indicating that the builder may only be used by builds that explicitly require these tags.';
+COMMENT ON COLUMN BuildQueue.builder_constraints IS 'An array of builder resource tags required by the associated build farm job.';
+COMMENT ON COLUMN CIBuild.builder_constraints IS 'An array of builder resource tags required by this CI build.';
+COMMENT ON COLUMN GitRepository.builder_constraints IS 'An array of builder resource tags required by builds of this repository.';
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 13, 0);