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 (file)
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 (file)
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;