1 -- Fix up after population of the database...
3 -- First of all readd the constraints (takes ~1:30 on auric)
5 ALTER TABLE files ADD CONSTRAINT files_location FOREIGN KEY (location) REFERENCES location(id) MATCH FULL;
7 ALTER TABLE source ADD CONSTRAINT source_maintainer FOREIGN KEY (maintainer) REFERENCES maintainer(id) MATCH FULL;
8 ALTER TABLE source ADD CONSTRAINT source_file FOREIGN KEY (file) REFERENCES files(id) MATCH FULL;
9 ALTER TABLE source ADD CONSTRAINT source_sig_fpr FOREIGN KEY (sig_fpr) REFERENCES fingerprint(id) MATCH FULL;
11 ALTER TABLE dsc_files ADD CONSTRAINT dsc_files_source FOREIGN KEY (source) REFERENCES source(id) MATCH FULL;
12 ALTER TABLE dsc_files ADD CONSTRAINT dsc_files_file FOREIGN KEY (file) REFERENCES files(id) MATCH FULL;
14 ALTER TABLE binaries ADD CONSTRAINT binaries_maintainer FOREIGN KEY (maintainer) REFERENCES maintainer(id) MATCH FULL;
15 ALTER TABLE binaries ADD CONSTRAINT binaries_source FOREIGN KEY (source) REFERENCES source(id) MATCH FULL;
16 ALTER TABLE binaries ADD CONSTRAINT binaries_architecture FOREIGN KEY (architecture) REFERENCES architecture(id) MATCH FULL;
17 ALTER TABLE binaries ADD CONSTRAINT binaries_file FOREIGN KEY (file) REFERENCES files(id) MATCH FULL;
18 ALTER TABLE binaries ADD CONSTRAINT binaries_sig_fpr FOREIGN KEY (sig_fpr) REFERENCES fingerprint(id) MATCH FULL;
20 ALTER TABLE suite_architectures ADD CONSTRAINT suite_architectures_suite FOREIGN KEY (suite) REFERENCES suite(id) MATCH FULL;
21 ALTER TABLE suite_architectures ADD CONSTRAINT suite_architectures_architecture FOREIGN KEY (architecture) REFERENCES architecture(id) MATCH FULL;
23 ALTER TABLE bin_associations ADD CONSTRAINT bin_associations_suite FOREIGN KEY (suite) REFERENCES suite(id) MATCH FULL;
24 ALTER TABLE bin_associations ADD CONSTRAINT bin_associations_bin FOREIGN KEY (bin) REFERENCES binaries(id) MATCH FULL;
26 ALTER TABLE src_associations ADD CONSTRAINT src_associations_suite FOREIGN KEY (suite) REFERENCES suite(id) MATCH FULL;
27 ALTER TABLE src_associations ADD CONSTRAINT src_associations_source FOREIGN KEY (source) REFERENCES source(id) MATCH FULL;
29 ALTER TABLE override ADD CONSTRAINT override_suite FOREIGN KEY (suite) REFERENCES suite(id) MATCH FULL;
30 ALTER TABLE override ADD CONSTRAINT override_component FOREIGN KEY (component) REFERENCES component(id) MATCH FULL;
31 ALTER TABLE override ADD CONSTRAINT override_priority FOREIGN KEY (priority) REFERENCES priority(id) MATCH FULL;
32 ALTER TABLE override ADD CONSTRAINT override_section FOREIGN KEY (section) REFERENCES section(id) MATCH FULL;
33 ALTER TABLE override ADD CONSTRAINT override_type FOREIGN KEY (type) REFERENCES override_type(id) MATCH FULL;
35 ALTER TABLE accepted_autobuild ADD CONSTRAINT accepted_autobuild_suite FOREIGN KEY (suite) REFERENCES suite(id) MATCH FULL;
37 -- Then correct all the id SERIAL PRIMARY KEY columns...
39 CREATE FUNCTION files_id_max() RETURNS INT4
40 AS 'SELECT max(id) FROM files'
42 CREATE FUNCTION source_id_max() RETURNS INT4
43 AS 'SELECT max(id) FROM source'
45 CREATE FUNCTION src_associations_id_max() RETURNS INT4
46 AS 'SELECT max(id) FROM src_associations'
48 CREATE FUNCTION dsc_files_id_max() RETURNS INT4
49 AS 'SELECT max(id) FROM dsc_files'
51 CREATE FUNCTION binaries_id_max() RETURNS INT4
52 AS 'SELECT max(id) FROM binaries'
54 CREATE FUNCTION bin_associations_id_max() RETURNS INT4
55 AS 'SELECT max(id) FROM bin_associations'
57 CREATE FUNCTION section_id_max() RETURNS INT4
58 AS 'SELECT max(id) FROM section'
60 CREATE FUNCTION priority_id_max() RETURNS INT4
61 AS 'SELECT max(id) FROM priority'
63 CREATE FUNCTION override_type_id_max() RETURNS INT4
64 AS 'SELECT max(id) FROM override_type'
66 CREATE FUNCTION maintainer_id_max() RETURNS INT4
67 AS 'SELECT max(id) FROM maintainer'
70 SELECT setval('files_id_seq', files_id_max());
71 SELECT setval('source_id_seq', source_id_max());
72 SELECT setval('src_associations_id_seq', src_associations_id_max());
73 SELECT setval('dsc_files_id_seq', dsc_files_id_max());
74 SELECT setval('binaries_id_seq', binaries_id_max());
75 SELECT setval('bin_associations_id_seq', bin_associations_id_max());
76 SELECT setval('section_id_seq', section_id_max());
77 SELECT setval('priority_id_seq', priority_id_max());
78 SELECT setval('override_type_id_seq', override_type_id_max());
79 SELECT setval('maintainer_id_seq', maintainer_id_max());
81 -- Vacuum the tables for efficency
93 VACUUM suite_architectures;
94 VACUUM bin_associations;
95 VACUUM src_associations;
101 -- FIXME: has to be a better way to do this
102 GRANT ALL ON architecture, architecture_id_seq, archive,
103 archive_id_seq, bin_associations, bin_associations_id_seq, binaries,
104 binaries_id_seq, component, component_id_seq, dsc_files,
105 dsc_files_id_seq, files, files_id_seq, fingerprint,
106 fingerprint_id_seq, location, location_id_seq, maintainer,
107 maintainer_id_seq, override, override_type, override_type_id_seq,
108 priority, priority_id_seq, section, section_id_seq, source,
109 source_id_seq, src_associations, src_associations_id_seq, suite,
110 suite_architectures, suite_id_seq, accepted_autobuild, uid,
111 uid_id_seq TO GROUP ftpmaster;
113 -- Read only access to user 'nobody'
114 GRANT SELECT ON architecture, architecture_id_seq, archive,
115 archive_id_seq, bin_associations, bin_associations_id_seq, binaries,
116 binaries_id_seq, component, component_id_seq, dsc_files,
117 dsc_files_id_seq, files, files_id_seq, fingerprint,
118 fingerprint_id_seq, location, location_id_seq, maintainer,
119 maintainer_id_seq, override, override_type, override_type_id_seq,
120 priority, priority_id_seq, section, section_id_seq, source,
121 source_id_seq, src_associations, src_associations_id_seq, suite,
122 suite_architectures, suite_id_seq, accepted_autobuild, uid,
123 uid_id_seq TO PUBLIC;