From 61ab25281202b45572379adfc0a6653ffe500406 Mon Sep 17 00:00:00 2001 From: James Troup Date: Tue, 12 Feb 2002 23:12:54 +0000 Subject: [PATCH] add fingerprints and install date (source). --- add_constraints.sql | 26 ++++++++++++++------------ init_pool.sql | 8 ++++++++ 2 files changed, 22 insertions(+), 12 deletions(-) diff --git a/add_constraints.sql b/add_constraints.sql index b52b103b..9352eeaa 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; @@ -33,31 +35,31 @@ ALTER TABLE override ADD CONSTRAINT override_type FOREIGN KEY (type) REFERENCES -- 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'; SELECT setval('files_id_seq', files_id_max()); @@ -91,7 +93,7 @@ VACUUM override_type; VACUUM override; -- FIXME: has to be a better way to do this -GRANT ALL ON +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, @@ -103,7 +105,7 @@ GRANT ALL ON TO GROUP ftpmaster; -- Read only access to user 'nobody' -GRANT SELECT ON +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, diff --git a/init_pool.sql b/init_pool.sql index 6d3b6e49..0e5658df 100644 --- a/init_pool.sql +++ b/init_pool.sql @@ -28,6 +28,11 @@ CREATE TABLE maintainer ( name TEXT UNIQUE NOT NULL ); +CREATE TABLE fingerprint ( + id SERIAL PRIMARY KEY, + fingerprint TEXT UNIQUE NOT NULL +); + CREATE TABLE location ( id SERIAL PRIMARY KEY, path TEXT NOT NULL, @@ -54,6 +59,8 @@ CREATE TABLE source ( version TEXT NOT NULL, maintainer INT4 NOT NULL, -- REFERENCES maintainer file INT4 UNIQUE NOT NULL, -- REFERENCES files + install_date TIMESTAMP NOT NULL, + sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint unique (source, version) ); @@ -74,6 +81,7 @@ CREATE TABLE binaries ( file INT4 UNIQUE NOT NULL, -- REFERENCES files, type TEXT NOT NULL, -- joeyh@ doesn't want .udebs and .debs with the same name, which is why the unique () doesn't mention type + sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint unique (package, version, architecture) ); -- 2.39.5