[PATCH 07/12] [db] Distinguish between arches with buildds, and the Architecture field

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

Previously, the architectures table was holding real architectures like
"alpha" and "mipsel", and other values like "any" and "all". However, the
architectures table is referenced from places like build_jobs and builder,
where only the former group makes sense.

This commit splits the two groups, keeping the architectures table for the
real architectures (or, rather, architectures supported by each wanna-build
instance), and creates a new table package_architectures for values found in
the Architecture field of Sources/Packages files.

Additionally, a trigger is created to populate package_architectures upon
insertion on source_architectures and binaries. It is not feasible to
populate this table in advance: at the time of this writing, there are 38
different names in the Architecture field in Sources.gz, with more appearing
in an unforeseeable manner.
---
 db/db.sql |   43 +++++++++++++++++++++++++++++++++++++------
 1 files changed, 37 insertions(+), 6 deletions(-)

diff --git a/db/db.sql b/db/db.sql
index fbe1110..0677ab2 100644
--- a/db/db.sql
+++ b/db/db.sql
@@ -30,11 +30,9 @@ CREATE TABLE architectures (
 	  CONSTRAINT arch_pkey PRIMARY KEY
 );
 
-COMMENT ON TABLE architectures IS 'Valid architectures';
+COMMENT ON TABLE architectures IS 'Architectures supported by this wanna-build instance';
 COMMENT ON COLUMN architectures.name IS 'Architecture name';
 
-INSERT INTO architectures (name) VALUES ('all');
-INSERT INTO architectures (name) VALUES ('any');
 INSERT INTO architectures (name) VALUES ('alpha');
 INSERT INTO architectures (name) VALUES ('amd64');
 INSERT INTO architectures (name) VALUES ('arm');
@@ -86,6 +84,14 @@ INSERT INTO components (name) VALUES ('main');
 INSERT INTO components (name) VALUES ('contrib');
 INSERT INTO components (name) VALUES ('non-free');
 
+CREATE TABLE package_architectures (
+	name text
+	  CONSTRAINT pkg_arch_pkey PRIMARY KEY
+);
+
+COMMENT ON TABLE package_architectures IS 'Possible values for the Architecture field';
+COMMENT ON COLUMN package_architectures.name IS 'Architecture name';
+
 CREATE TABLE package_priorities (
 	name text
 	  CONSTRAINT pkg_pri_pkey PRIMARY KEY,
@@ -202,7 +208,8 @@ CREATE TABLE source_architectures (
 	  NOT NULL,
 	source_version debversion NOT NULL,
 	arch_name text
-	  CONSTRAINT source_arch_arch_fkey REFERENCES architectures(name)
+	  CONSTRAINT source_arch_arch_fkey
+	  REFERENCES package_architectures(name)
 	  ON DELETE CASCADE
 	  NOT NULL,
 	UNIQUE (source_name,source_version,arch_name),
@@ -220,7 +227,7 @@ CREATE TABLE binaries (
 	name text NOT NULL,
 	version debversion NOT NULL,
 	arch_name text
-	  CONSTRAINT bin_arch_fkey REFERENCES architectures(name)
+	  CONSTRAINT bin_arch_fkey REFERENCES package_architectures(name)
 	  ON DELETE CASCADE
 	  NOT NULL,
        	source_name text
@@ -295,7 +302,7 @@ CREATE TABLE suite_binaries (
 	  NOT NULL,
 	binary_version debversion NOT NULL,
 	arch_name text
-	  CONSTRAINT suite_bin_arch_fkey REFERENCES architectures(name)
+	  CONSTRAINT suite_bin_arch_fkey REFERENCES package_architectures(name)
           ON DELETE CASCADE
 	  NOT NULL,
 	suite_name text
@@ -414,3 +421,27 @@ CREATE TRIGGER checkrel BEFORE INSERT OR UPDATE ON binaries
   FOR EACH ROW EXECUTE PROCEDURE package_checkrel();
 COMMENT ON TRIGGER checkrel ON binaries
   IS 'Check foreign key references (package sections and priorities) exist';
+
+
+CREATE OR REPLACE FUNCTION package_check_arch() RETURNS trigger AS $package_check_arch$
+BEGIN
+  PERFORM name FROM package_architectures WHERE (name = NEW.arch_name);
+  IF FOUND = 'f' THEN
+    INSERT INTO package_architectures (name) VALUES (NEW.arch_name);
+  END IF;
+  RETURN NEW;
+END;
+$package_check_arch$ LANGUAGE plpgsql;
+
+COMMENT ON FUNCTION package_check_arch ()
+  IS 'Insert missing values into package_architectures (from NEW.arch_name)';
+
+CREATE TRIGGER check_arch BEFORE INSERT OR UPDATE ON source_architectures
+  FOR EACH ROW EXECUTE PROCEDURE package_check_arch();
+COMMENT ON TRIGGER check_arch ON source_architectures
+  IS 'Ensure foreign key references (arch_name) exist';
+
+CREATE TRIGGER check_arch BEFORE INSERT OR UPDATE ON binaries
+  FOR EACH ROW EXECUTE PROCEDURE package_check_arch();
+COMMENT ON TRIGGER check_arch ON binaries
+  IS 'Ensure foreign key references (arch_name) exist';
-- 
1.6.1.1.352.g2c729