X-Git-Url: https://git.decadent.org.uk/gitweb/?a=blobdiff_plain;f=add_constraints.sql;h=f25165c1b1dbaffd27977d9a188fe70464e7a5ee;hb=9540d873fa78598454af57f5f8a4875969ed0439;hp=b52b103b510ef3d70be40eeab7eede78293a9796;hpb=1d485ddb6826f2b3be65a9df5d8684ef699db46a;p=dak.git

diff --git a/add_constraints.sql b/add_constraints.sql
index b52b103b..f25165c1 100644
--- a/add_constraints.sql
+++ b/add_constraints.sql
@@ -6,6 +6,7 @@ ALTER TABLE files ADD CONSTRAINT files_location FOREIGN KEY (location) REFERENCE
 
 ALTER TABLE source ADD CONSTRAINT source_maintainer FOREIGN KEY (maintainer) REFERENCES maintainer(id) MATCH FULL;
 ALTER TABLE source ADD CONSTRAINT source_file FOREIGN KEY (file) REFERENCES files(id) MATCH FULL;
+ALTER TABLE source ADD CONSTRAINT source_sig_fpr FOREIGN KEY (sig_fpr) REFERENCES fingerprint(id) MATCH FULL;
 
 ALTER TABLE dsc_files ADD CONSTRAINT dsc_files_source FOREIGN KEY (source) REFERENCES source(id) MATCH FULL;
 ALTER TABLE dsc_files ADD CONSTRAINT dsc_files_file FOREIGN KEY (file) REFERENCES files(id) MATCH FULL;
@@ -14,13 +15,14 @@ ALTER TABLE binaries ADD CONSTRAINT binaries_maintainer FOREIGN KEY (maintainer)
 ALTER TABLE binaries ADD CONSTRAINT binaries_source FOREIGN KEY (source) REFERENCES source(id) MATCH FULL;
 ALTER TABLE binaries ADD CONSTRAINT binaries_architecture FOREIGN KEY (architecture) REFERENCES architecture(id) MATCH FULL;
 ALTER TABLE binaries ADD CONSTRAINT binaries_file FOREIGN KEY (file) REFERENCES files(id) MATCH FULL;
+ALTER TABLE binaries ADD CONSTRAINT binaries_sig_fpr FOREIGN KEY (sig_fpr) REFERENCES fingerprint(id) MATCH FULL;
 
 ALTER TABLE suite_architectures ADD CONSTRAINT suite_architectures_suite FOREIGN KEY (suite) REFERENCES suite(id) MATCH FULL;
 ALTER TABLE suite_architectures ADD CONSTRAINT suite_architectures_architecture FOREIGN KEY (architecture) REFERENCES architecture(id) MATCH FULL;
 
 ALTER TABLE bin_associations ADD CONSTRAINT bin_associations_suite FOREIGN KEY (suite) REFERENCES suite(id) MATCH FULL;
 ALTER TABLE bin_associations ADD CONSTRAINT bin_associations_bin FOREIGN KEY (bin) REFERENCES binaries(id) MATCH FULL;
-  
+
 ALTER TABLE src_associations ADD CONSTRAINT src_associations_suite FOREIGN KEY (suite) REFERENCES suite(id) MATCH FULL;
 ALTER TABLE src_associations ADD CONSTRAINT src_associations_source FOREIGN KEY (source) REFERENCES source(id) MATCH FULL;
 
@@ -30,34 +32,40 @@ ALTER TABLE override ADD CONSTRAINT override_priority FOREIGN KEY (priority) REF
 ALTER TABLE override ADD CONSTRAINT override_section FOREIGN KEY (section) REFERENCES section(id) MATCH FULL;
 ALTER TABLE override ADD CONSTRAINT override_type FOREIGN KEY (type) REFERENCES override_type(id) MATCH FULL;
 
+ALTER TABLE queue_build ADD CONSTRAINT queue_build_suite FOREIGN KEY (suite) REFERENCES suite(id) MATCH FULL;
+ALTER TABLE queue_build ADD CONSTRAINT queue_build_queue FOREIGN KEY (queue) REFERENCES queue(id) MATCH FULL;
+
 -- Then correct all the id SERIAL PRIMARY KEY columns...
 
 CREATE FUNCTION files_id_max() RETURNS INT4
-    AS 'SELECT max(id) FROM files' 
+    AS 'SELECT max(id) FROM files'
     LANGUAGE 'sql';
 CREATE FUNCTION source_id_max() RETURNS INT4
-    AS 'SELECT max(id) FROM source' 
+    AS 'SELECT max(id) FROM source'
     LANGUAGE 'sql';
 CREATE FUNCTION src_associations_id_max() RETURNS INT4
-    AS 'SELECT max(id) FROM src_associations' 
+    AS 'SELECT max(id) FROM src_associations'
     LANGUAGE 'sql';
 CREATE FUNCTION dsc_files_id_max() RETURNS INT4
-    AS 'SELECT max(id) FROM dsc_files' 
+    AS 'SELECT max(id) FROM dsc_files'
     LANGUAGE 'sql';
 CREATE FUNCTION binaries_id_max() RETURNS INT4
-    AS 'SELECT max(id) FROM binaries' 
+    AS 'SELECT max(id) FROM binaries'
     LANGUAGE 'sql';
 CREATE FUNCTION bin_associations_id_max() RETURNS INT4
-    AS 'SELECT max(id) FROM bin_associations' 
+    AS 'SELECT max(id) FROM bin_associations'
     LANGUAGE 'sql';
 CREATE FUNCTION section_id_max() RETURNS INT4
-    AS 'SELECT max(id) FROM section' 
+    AS 'SELECT max(id) FROM section'
     LANGUAGE 'sql';
 CREATE FUNCTION priority_id_max() RETURNS INT4
-    AS 'SELECT max(id) FROM priority' 
+    AS 'SELECT max(id) FROM priority'
     LANGUAGE 'sql';
 CREATE FUNCTION override_type_id_max() RETURNS INT4
-    AS 'SELECT max(id) FROM override_type' 
+    AS 'SELECT max(id) FROM override_type'
+    LANGUAGE 'sql';
+CREATE FUNCTION maintainer_id_max() RETURNS INT4
+    AS 'SELECT max(id) FROM maintainer'
     LANGUAGE 'sql';
 
 SELECT setval('files_id_seq', files_id_max());
@@ -69,6 +77,7 @@ SELECT setval('bin_associations_id_seq', bin_associations_id_max());
 SELECT setval('section_id_seq', section_id_max());
 SELECT setval('priority_id_seq', priority_id_max());
 SELECT setval('override_type_id_seq', override_type_id_max());
+SELECT setval('maintainer_id_seq', maintainer_id_max());
 
 -- Vacuum the tables for efficency
 
@@ -91,25 +100,25 @@ VACUUM override_type;
 VACUUM override;
 
 -- FIXME: has to be a better way to do this
-GRANT ALL ON 
-  architecture, architecture_id_seq, archive, archive_id_seq,
-  bin_associations, bin_associations_id_seq, binaries,
+GRANT ALL ON architecture, architecture_id_seq, archive,
+  archive_id_seq, bin_associations, bin_associations_id_seq, binaries,
   binaries_id_seq, component, component_id_seq, dsc_files,
-  dsc_files_id_seq, files, files_id_seq, location, location_id_seq,
-  maintainer, maintainer_id_seq, override, override_type,
-  override_type_id_seq, priority, priority_id_seq, section,
-  section_id_seq, source, source_id_seq, src_associations,
-  src_associations_id_seq, suite, suite_architectures, suite_id_seq
-     TO GROUP ftpmaster;
+  dsc_files_id_seq, files, files_id_seq, fingerprint,
+  fingerprint_id_seq, location, location_id_seq, maintainer,
+  maintainer_id_seq, override, override_type, override_type_id_seq,
+  priority, priority_id_seq, section, section_id_seq, source,
+  source_id_seq, src_associations, src_associations_id_seq, suite,
+  suite_architectures, suite_id_seq, queue_build, uid,
+  uid_id_seq TO GROUP ftpmaster;
 
 -- Read only access to user 'nobody'
-GRANT SELECT ON 
-  architecture, architecture_id_seq, archive, archive_id_seq,
-  bin_associations, bin_associations_id_seq, binaries,
+GRANT SELECT ON architecture, architecture_id_seq, archive,
+  archive_id_seq, bin_associations, bin_associations_id_seq, binaries,
   binaries_id_seq, component, component_id_seq, dsc_files,
-  dsc_files_id_seq, files, files_id_seq, location, location_id_seq,
-  maintainer, maintainer_id_seq, override, override_type,
-  override_type_id_seq, priority, priority_id_seq, section,
-  section_id_seq, source, source_id_seq, src_associations,
-  src_associations_id_seq, suite, suite_architectures, suite_id_seq
-     TO PUBLIC;
+  dsc_files_id_seq, files, files_id_seq, fingerprint,
+  fingerprint_id_seq, location, location_id_seq, maintainer,
+  maintainer_id_seq, override, override_type, override_type_id_seq,
+  priority, priority_id_seq, section, section_id_seq, source,
+  source_id_seq, src_associations, src_associations_id_seq, suite,
+  suite_architectures, suite_id_seq, queue_build, uid,
+  uid_id_seq TO PUBLIC;