[PATCH] [db] Create a "suite_arches" table to list the architectures in a suite

[Adeodato Simó <dato@net.com.org.es>]

The database schema was missing a list of architectures in each suite.
This is needed, because one can't simply use the "architectures" table
to decide what arches to act on, eg. when importing packages
information, or when creating a web interface: that table may contain no
longer active architectures (which are kept around for build_logs), and
many actions really need the list of architectures specific to a suite.

This commit introduces a "suite_arches" table, with a relation of what
architectures are available in each suite. It also adds foreign keys
towards this table from suite_binaries and build_status; this way, it is
ensured that dropping an architecture from a suite will clean up those
tables from no longer relevant entries.
---
Hello,

as discussed on IRC, we need this.

Cheers,

 db/db.sql |   22 +++++++++++++++++++++-
 1 files changed, 21 insertions(+), 1 deletions(-)

diff --git a/db/db.sql b/db/db.sql
index 2858f94..d9a3dc0 100644
--- a/db/db.sql
+++ b/db/db.sql
@@ -50,7 +50,7 @@ CREATE TABLE architectures (
 	  CONSTRAINT arch_pkey PRIMARY KEY
 );
 
-COMMENT ON TABLE architectures IS 'Architectures supported by this wanna-build instance';
+COMMENT ON TABLE architectures IS 'Architectures known by this wanna-build instance';
 COMMENT ON COLUMN architectures.arch IS 'Architecture name';
 
 INSERT INTO architectures (arch) VALUES ('alpha');
@@ -94,6 +94,20 @@ INSERT INTO suites (suite, priority, depwait, hidden)
 	VALUES ('stable-security', 1, 'f', 't');
 INSERT INTO suites (suite, priority) VALUES ('oldstable', 1);
 
+CREATE TABLE suite_arches (
+	suite text
+	  NOT NULL
+	  CONSTRAINT suite_arches_suite_fkey REFERENCES suites(suite),
+	arch text
+	  NOT NULL
+	  CONSTRAINT suite_arches_arch_fkey REFERENCES architectures(arch),
+	CONSTRAINT suite_arches_pkey PRIMARY KEY (suite, arch)
+);
+
+COMMENT ON TABLE suite_arches IS 'List of architectures in each suite';
+COMMENT ON COLUMN suite_arches.suite IS 'Suite name';
+COMMENT ON COLUMN suite_arches.arch IS 'Architecture name';
+
 CREATE TABLE components (
 	component text
 	  CONSTRAINT component_pkey PRIMARY KEY
@@ -327,6 +341,9 @@ CREATE TABLE suite_binaries (
 	CONSTRAINT suite_bin_pkey PRIMARY KEY (package, arch, suite),
 	CONSTRAINT suite_bin_bin_fkey FOREIGN KEY (package, version, arch)
 	  REFERENCES binaries (package, version, arch)
+	  ON DELETE CASCADE,
+	CONSTRAINT suite_bin_suite_arch_fkey FOREIGN KEY (suite, arch)
+	  REFERENCES suite_arches (suite, arch)
 	  ON DELETE CASCADE
 );
 
@@ -368,6 +385,9 @@ CREATE TABLE build_status (
 	  UNIQUE (source, source_version, suite, arch),
 	CONSTRAINT build_status_src_fkey FOREIGN KEY(source, source_version)
 	  REFERENCES sources(source, source_version)
+	  ON DELETE CASCADE,
+	CONSTRAINT suite_bin_suite_arch_fkey FOREIGN KEY (suite, arch)
+	  REFERENCES suite_arches (suite, arch)
 	  ON DELETE CASCADE
 );
 
-- 
1.6.2.272.g44c5b