1 -- Fix up after population off 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;
10 ALTER TABLE dsc_files ADD CONSTRAINT dsc_files_source FOREIGN KEY (source) REFERENCES source(id) MATCH FULL;
11 ALTER TABLE dsc_files ADD CONSTRAINT dsc_files_file FOREIGN KEY (file) REFERENCES files(id) MATCH FULL;
13 ALTER TABLE binaries ADD CONSTRAINT binaries_maintainer FOREIGN KEY (maintainer) REFERENCES maintainer(id) MATCH FULL;
14 ALTER TABLE binaries ADD CONSTRAINT binaries_source FOREIGN KEY (source) REFERENCES source(id) MATCH FULL;
15 ALTER TABLE binaries ADD CONSTRAINT binaries_architecture FOREIGN KEY (architecture) REFERENCES architecture(id) MATCH FULL;
16 ALTER TABLE binaries ADD CONSTRAINT binaries_file FOREIGN KEY (file) REFERENCES files(id) MATCH FULL;
18 ALTER TABLE suite_architectures ADD CONSTRAINT suite_architectures_suite FOREIGN KEY (suite) REFERENCES suite(id) MATCH FULL;
19 ALTER TABLE suite_architectures ADD CONSTRAINT suite_architectures_architecture FOREIGN KEY (architecture) REFERENCES architecture(id) MATCH FULL;
21 ALTER TABLE bin_associations ADD CONSTRAINT bin_associations_suite FOREIGN KEY (suite) REFERENCES suite(id) MATCH FULL;
22 ALTER TABLE bin_associations ADD CONSTRAINT bin_associations_bin FOREIGN KEY (bin) REFERENCES binaries(id) MATCH FULL;
24 ALTER TABLE src_associations ADD CONSTRAINT src_associations_suite FOREIGN KEY (suite) REFERENCES suite(id) MATCH FULL;
25 ALTER TABLE src_associations ADD CONSTRAINT src_associations_source FOREIGN KEY (source) REFERENCES source(id) MATCH FULL;
27 -- Then correct all the id SERIAL PRIMARY KEY columns...
29 CREATE FUNCTION files_id_max() RETURNS INT4
30 AS 'SELECT max(id) FROM files'
32 CREATE FUNCTION source_id_max() RETURNS INT4
33 AS 'SELECT max(id) FROM source'
35 CREATE FUNCTION src_associations_id_max() RETURNS INT4
36 AS 'SELECT max(id) FROM src_associations'
38 CREATE FUNCTION dsc_files_id_max() RETURNS INT4
39 AS 'SELECT max(id) FROM dsc_files'
41 CREATE FUNCTION binaries_id_max() RETURNS INT4
42 AS 'SELECT max(id) FROM binaries'
44 CREATE FUNCTION bin_associations_id_max() RETURNS INT4
45 AS 'SELECT max(id) FROM bin_associations'
48 SELECT setval('files_id_seq', files_id_max());
49 SELECT setval('source_id_seq', source_id_max());
50 SELECT setval('src_associations_id_seq', src_associations_id_max());
51 SELECT setval('dsc_files_id_seq', dsc_files_id_max());
52 SELECT setval('binaries_id_seq', binaries_id_max());
53 SELECT setval('bin_associations_id_seq', bin_associations_id_max());
55 -- Vacuum the tables for efficency
67 VACUUM suite_architectures;
68 VACUUM bin_associations;
69 VACUUM src_associations;
71 -- FIXME: has to be a better way to do this
73 architecture, architecture_id_seq, archive, archive_id_seq,
74 bin_associations, bin_associations_id_seq, binaries,
75 binaries_id_seq, component, component_id_seq, dsc_files,
76 dsc_files_id_seq, files, files_id_seq, location, location_id_seq,
77 maintainer, maintainer_id_seq, source, source_id_seq,
78 src_associations, src_associations_id_seq, suite,
79 suite_architectures, suite_id_seq
82 -- Give write privileges to the associations tables for AJ for the purposes of `testing'
84 binaries, binaries_id_seq,
85 bin_associations, bin_associations_id_seq,
86 source, source_id_seq,
87 src_associations, src_associations_id_seq,
91 -- RO access to AJ for all other tables
93 architecture, archive, binaries, component,
94 dsc_files, files, location, maintainer, source, suite, suite_architectures
97 -- Read only access to user 'nobody'
99 architecture, architecture_id_seq, archive, archive_id_seq,
100 bin_associations, bin_associations_id_seq, binaries,
101 binaries_id_seq, component, component_id_seq, dsc_files,
102 dsc_files_id_seq, files, files_id_seq, location, location_id_seq,
103 maintainer, maintainer_id_seq, source, source_id_seq,
104 src_associations, src_associations_id_seq, suite,
105 suite_architectures, suite_id_seq