From: Stephen Soltesz Date: Fri, 13 Nov 2009 20:31:08 +0000 (+0000) Subject: add site and person tags to the planetlab5 schema. Update the subversion to 1. X-Git-Tag: PLCAPI-4.3-31~14 X-Git-Url: http://git.onelab.eu/?p=plcapi.git;a=commitdiff_plain;h=8fa2c23822e1bf280351d09b917e3c549951cf5b add site and person tags to the planetlab5 schema. Update the subversion to 1. --- diff --git a/migrations/001-down-site-and-person-tags.sql b/migrations/001-down-site-and-person-tags.sql new file mode 100644 index 0000000..c70424d --- /dev/null +++ b/migrations/001-down-site-and-person-tags.sql @@ -0,0 +1,16 @@ +-- +-- purpose: provide a way to delete the additions added by the up script. +-- NOTE: this does not leave the DB in a usable state, since it drops the view_sites and view_persons; +-- + +DROP VIEW view_site_tags; +DROP VIEW view_sites; +DROP VIEW site_tags; +DROP TABLE site_tag; + +DROP VIEW view_person_tags; +DROP VIEW view_persons; +DROP VIEW person_tags; +DROP TABLE person_tag; + +UPDATE plc_db_version SET subversion = 0; diff --git a/migrations/001-up-site-and-person-tags.sql b/migrations/001-up-site-and-person-tags.sql new file mode 100644 index 0000000..ccb5d13 --- /dev/null +++ b/migrations/001-up-site-and-person-tags.sql @@ -0,0 +1,129 @@ +-- +-- PlanetLab +-- +-- migration 001 +-- +-- purpose: provide tags on site and person objects in db +-- +-- + +-- SITES + +CREATE TABLE site_tag ( + site_tag_id serial PRIMARY KEY, -- ID + site_id integer REFERENCES sites NOT NULL, -- site id + tag_type_id integer REFERENCES tag_types, -- tag type id + value text -- value attached +) WITH OIDS; + +CREATE OR REPLACE VIEW site_tags AS +SELECT site_id, +array_accum(site_tag_id) AS site_tag_ids +FROM site_tag +GROUP BY site_id; + +CREATE OR REPLACE VIEW view_site_tags AS +SELECT +site_tag.site_tag_id, +site_tag.site_id, +sites.login_base, +tag_types.tag_type_id, +tag_types.tagname, +tag_types.description, +tag_types.category, +tag_types.min_role_id, +site_tag.value +FROM site_tag +INNER JOIN tag_types USING (tag_type_id) +INNER JOIN sites USING (site_id); + +DROP VIEW view_sites; +CREATE OR REPLACE VIEW view_sites AS +SELECT +sites.site_id, +sites.login_base, +sites.name, +sites.abbreviated_name, +sites.deleted, +sites.enabled, +sites.is_public, +sites.max_slices, +sites.max_slivers, +sites.latitude, +sites.longitude, +sites.url, +sites.ext_consortium_id, +CAST(date_part('epoch', sites.date_created) AS bigint) AS date_created, +CAST(date_part('epoch', sites.last_updated) AS bigint) AS last_updated, +peer_site.peer_id, +peer_site.peer_site_id, +COALESCE((SELECT person_ids FROM site_persons WHERE site_persons.site_id = sites.site_id), '{}') AS person_ids, +COALESCE((SELECT node_ids FROM site_nodes WHERE site_nodes.site_id = sites.site_id), '{}') AS node_ids, +COALESCE((SELECT address_ids FROM site_addresses WHERE site_addresses.site_id = sites.site_id), '{}') AS address_ids, +COALESCE((SELECT slice_ids FROM site_slices WHERE site_slices.site_id = sites.site_id), '{}') AS slice_ids, +COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id), '{}') AS pcu_ids, +COALESCE((SELECT site_tag_ids FROM site_tags WHERE site_tags.site_id = sites.site_id), '{}') AS site_tag_ids +FROM sites +LEFT JOIN peer_site USING (site_id); + +-- PERSONS + +CREATE TABLE person_tag ( + person_tag_id serial PRIMARY KEY, -- ID + person_id integer REFERENCES persons NOT NULL, -- person id + tag_type_id integer REFERENCES tag_types, -- tag type id + value text -- value attached +) WITH OIDS; + +CREATE OR REPLACE VIEW person_tags AS +SELECT person_id, +array_accum(person_tag_id) AS person_tag_ids +FROM person_tag +GROUP BY person_id; + +CREATE OR REPLACE VIEW view_person_tags AS +SELECT +person_tag.person_tag_id, +person_tag.person_id, +persons.email, +tag_types.tag_type_id, +tag_types.tagname, +tag_types.description, +tag_types.category, +tag_types.min_role_id, +person_tag.value +FROM person_tag +INNER JOIN tag_types USING (tag_type_id) +INNER JOIN persons USING (person_id); + +DROP VIEW view_persons; +CREATE OR REPLACE VIEW view_persons AS +SELECT +persons.person_id, +persons.email, +persons.first_name, +persons.last_name, +persons.deleted, +persons.enabled, +persons.password, +persons.verification_key, +CAST(date_part('epoch', persons.verification_expires) AS bigint) AS verification_expires, +persons.title, +persons.phone, +persons.url, +persons.bio, +CAST(date_part('epoch', persons.date_created) AS bigint) AS date_created, +CAST(date_part('epoch', persons.last_updated) AS bigint) AS last_updated, +peer_person.peer_id, +peer_person.peer_person_id, +COALESCE((SELECT role_ids FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS role_ids, +COALESCE((SELECT roles FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS roles, +COALESCE((SELECT site_ids FROM person_sites WHERE person_sites.person_id = persons.person_id), '{}') AS site_ids, +COALESCE((SELECT key_ids FROM person_keys WHERE person_keys.person_id = persons.person_id), '{}') AS key_ids, +COALESCE((SELECT slice_ids FROM person_slices WHERE person_slices.person_id = persons.person_id), '{}') AS slice_ids, +COALESCE((SELECT person_tag_ids FROM person_tags WHERE person_tags.person_id = persons.person_id), '{}') AS person_tag_ids +FROM persons +LEFT JOIN peer_person USING (person_id); + + +UPDATE plc_db_version SET subversion = 1;