Merge ~cjwatson/launchpad:db-snap-base-arch into launchpad:db-devel

[Colin Watson]

Add SnapBaseArch table

A snap base may not support all the architectures supported by its underlying distroseries: in particular, core20 does not support i386. Extend the model to allow us to support this cleanly.

[Cristian Gonzalez (cristiangsp)]

Looks good! (and also helped me understand the connection between snap bases and distribution series)

review: Approve

[William Grant (wgrant)]

review: Approve (db)

diff --git a/database/schema/patch-2210-30-1.sql b/database/schema/patch-2210-30-1.sql
new file mode 100644
index 0000000..ea50782
--- /dev/null
+++ b/database/schema/patch-2210-30-1.sql
@@ -0,0 +1,23 @@
+-- Copyright 2021 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+CREATE TABLE SnapBaseArch (
+    snap_base integer NOT NULL REFERENCES snapbase,
+    processor integer NOT NULL REFERENCES processor,
+    PRIMARY KEY (snap_base, processor)
+);
+
+COMMENT ON TABLE SnapBaseArch IS 'The architectures that a snap base supports.';
+COMMENT ON COLUMN SnapBaseArch.snap_base IS 'The snap base for which a supported architecture is specified.';
+COMMENT ON COLUMN SnapBaseArch.processor IS 'A supported architecture for this snap base.';
+
+-- Initialize with all possibilities for each corresponding distroseries,
+-- preserving previous behaviour.
+INSERT INTO SnapBaseArch (snap_base, processor)
+    SELECT SnapBase.id, DistroArchSeries.processor
+    FROM SnapBase, DistroArchSeries
+    WHERE
+        SnapBase.distro_series = DistroArchSeries.distroseries
+        AND DistroArchSeries.enabled;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 30, 1);