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 -- Then correct all the id SERIAL PRIMARY KEY columns...
37 CREATE FUNCTION files_id_max() RETURNS INT4
38 AS 'SELECT max(id) FROM files'
40 CREATE FUNCTION source_id_max() RETURNS INT4
41 AS 'SELECT max(id) FROM source'
43 CREATE FUNCTION src_associations_id_max() RETURNS INT4
44 AS 'SELECT max(id) FROM src_associations'
46 CREATE FUNCTION dsc_files_id_max() RETURNS INT4
47 AS 'SELECT max(id) FROM dsc_files'
49 CREATE FUNCTION binaries_id_max() RETURNS INT4
50 AS 'SELECT max(id) FROM binaries'
52 CREATE FUNCTION bin_associations_id_max() RETURNS INT4
53 AS 'SELECT max(id) FROM bin_associations'
55 CREATE FUNCTION section_id_max() RETURNS INT4
56 AS 'SELECT max(id) FROM section'
58 CREATE FUNCTION priority_id_max() RETURNS INT4
59 AS 'SELECT max(id) FROM priority'
61 CREATE FUNCTION override_type_id_max() RETURNS INT4
62 AS 'SELECT max(id) FROM override_type'
65 SELECT setval('files_id_seq', files_id_max());
66 SELECT setval('source_id_seq', source_id_max());
67 SELECT setval('src_associations_id_seq', src_associations_id_max());
68 SELECT setval('dsc_files_id_seq', dsc_files_id_max());
69 SELECT setval('binaries_id_seq', binaries_id_max());
70 SELECT setval('bin_associations_id_seq', bin_associations_id_max());
71 SELECT setval('section_id_seq', section_id_max());
72 SELECT setval('priority_id_seq', priority_id_max());
73 SELECT setval('override_type_id_seq', override_type_id_max());
75 -- Vacuum the tables for efficency
87 VACUUM suite_architectures;
88 VACUUM bin_associations;
89 VACUUM src_associations;
95 -- FIXME: has to be a better way to do this
96 GRANT ALL ON architecture, architecture_id_seq, archive,
97 archive_id_seq, bin_associations, bin_associations_id_seq, binaries,
98 binaries_id_seq, component, component_id_seq, dsc_files,
99 dsc_files_id_seq, files, files_id_seq, fingerprint,
100 fingerprint_id_seq, location, location_id_seq, maintainer,
101 maintainer_id_seq, override, override_type, override_type_id_seq,
102 priority, priority_id_seq, section, section_id_seq, source,
103 source_id_seq, src_associations, src_associations_id_seq, suite,
104 suite_architectures, suite_id_seq, unstable_accepted TO GROUP ftpmaster;
106 -- Read only access to user 'nobody'
107 GRANT SELECT ON architecture, architecture_id_seq, archive,
108 archive_id_seq, bin_associations, bin_associations_id_seq, binaries,
109 binaries_id_seq, component, component_id_seq, dsc_files,
110 dsc_files_id_seq, files, files_id_seq, fingerprint,
111 fingerprint_id_seq, location, location_id_seq, maintainer,
112 maintainer_id_seq, override, override_type, override_type_id_seq,
113 priority, priority_id_seq, section, section_id_seq, source,
114 source_id_seq, src_associations, src_associations_id_seq, suite,
115 suite_architectures, suite_id_seq, unstable_accepted TO PUBLIC;