migrations/011-up-site-and-person-tags.sql

   1 --
   2 -- PlanetLab
   3 --
   4 -- migration 001
   5 --
   6 -- purpose: provide tags on site and person objects in db
   7 --
   8 --
   9
  10 -- SITES
  11
  12 CREATE TABLE site_tag (
  13     site_tag_id serial PRIMARY KEY,                     -- ID
  14     site_id integer REFERENCES sites NOT NULL,          -- site id
  15     tag_type_id integer REFERENCES tag_types,           -- tag type id
  16     value text                                          -- value attached
  17 ) WITH OIDS;
  18
  19 CREATE OR REPLACE VIEW site_tags AS
  20 SELECT site_id,
  21 array_accum(site_tag_id) AS site_tag_ids
  22 FROM site_tag
  23 GROUP BY site_id;
  24
  25 CREATE OR REPLACE VIEW view_site_tags AS
  26 SELECT
  27 site_tag.site_tag_id,
  28 site_tag.site_id,
  29 sites.login_base,
  30 tag_types.tag_type_id,
  31 tag_types.tagname,
  32 tag_types.description,
  33 tag_types.category,
  34 tag_types.min_role_id,
  35 site_tag.value
  36 FROM site_tag
  37 INNER JOIN tag_types USING (tag_type_id)
  38 INNER JOIN sites USING (site_id);
  39
  40 DROP VIEW view_sites;
  41 CREATE OR REPLACE VIEW view_sites AS
  42 SELECT
  43 sites.site_id,
  44 sites.login_base,
  45 sites.name,
  46 sites.abbreviated_name,
  47 sites.deleted,
  48 sites.enabled,
  49 sites.is_public,
  50 sites.max_slices,
  51 sites.max_slivers,
  52 sites.latitude,
  53 sites.longitude,
  54 sites.url,
  55 sites.ext_consortium_id,
  56 CAST(date_part('epoch', sites.date_created) AS bigint) AS date_created,
  57 CAST(date_part('epoch', sites.last_updated) AS bigint) AS last_updated,
  58 peer_site.peer_id,
  59 peer_site.peer_site_id,
  60 COALESCE((SELECT person_ids FROM site_persons WHERE site_persons.site_id = sites.site_id), '{}') AS person_ids,
  61 COALESCE((SELECT node_ids FROM site_nodes WHERE site_nodes.site_id = sites.site_id), '{}') AS node_ids,
  62 COALESCE((SELECT address_ids FROM site_addresses WHERE site_addresses.site_id = sites.site_id), '{}') AS address_ids,
  63 COALESCE((SELECT slice_ids FROM site_slices WHERE site_slices.site_id = sites.site_id), '{}') AS slice_ids,
  64 COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id), '{}') AS pcu_ids,
  65 COALESCE((SELECT site_tag_ids FROM site_tags WHERE site_tags.site_id = sites.site_id), '{}') AS site_tag_ids
  66 FROM sites
  67 LEFT JOIN peer_site USING (site_id);
  68
  69 -- PERSONS
  70
  71 CREATE TABLE person_tag (
  72     person_tag_id serial PRIMARY KEY,                   -- ID
  73     person_id integer REFERENCES persons NOT NULL,              -- person id
  74     tag_type_id integer REFERENCES tag_types,           -- tag type id
  75     value text                                          -- value attached
  76 ) WITH OIDS;
  77
  78 CREATE OR REPLACE VIEW person_tags AS
  79 SELECT person_id,
  80 array_accum(person_tag_id) AS person_tag_ids
  81 FROM person_tag
  82 GROUP BY person_id;
  83
  84 CREATE OR REPLACE VIEW view_person_tags AS
  85 SELECT
  86 person_tag.person_tag_id,
  87 person_tag.person_id,
  88 persons.email,
  89 tag_types.tag_type_id,
  90 tag_types.tagname,
  91 tag_types.description,
  92 tag_types.category,
  93 tag_types.min_role_id,
  94 person_tag.value
  95 FROM person_tag
  96 INNER JOIN tag_types USING (tag_type_id)
  97 INNER JOIN persons USING (person_id);
  98
  99 DROP VIEW view_persons;
 100 CREATE OR REPLACE VIEW view_persons AS
 101 SELECT
 102 persons.person_id,
 103 persons.email,
 104 persons.first_name,
 105 persons.last_name,
 106 persons.deleted,
 107 persons.enabled,
 108 persons.password,
 109 persons.verification_key,
 110 CAST(date_part('epoch', persons.verification_expires) AS bigint) AS verification_expires,
 111 persons.title,
 112 persons.phone,
 113 persons.url,
 114 persons.bio,
 115 CAST(date_part('epoch', persons.date_created) AS bigint) AS date_created,
 116 CAST(date_part('epoch', persons.last_updated) AS bigint) AS last_updated,
 117 peer_person.peer_id,
 118 peer_person.peer_person_id,
 119 COALESCE((SELECT role_ids FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS role_ids,
 120 COALESCE((SELECT roles FROM person_roles WHERE person_roles.person_id = persons.person_id), '{}') AS roles,
 121 COALESCE((SELECT site_ids FROM person_sites WHERE person_sites.person_id = persons.person_id), '{}') AS site_ids,
 122 COALESCE((SELECT key_ids FROM person_keys WHERE person_keys.person_id = persons.person_id), '{}') AS key_ids,
 123 COALESCE((SELECT slice_ids FROM person_slices WHERE person_slices.person_id = persons.person_id), '{}') AS slice_ids,
 124 COALESCE((SELECT person_tag_ids FROM person_tags WHERE person_tags.person_id = persons.person_id), '{}') AS person_tag_ids
 125 FROM persons
 126 LEFT JOIN peer_person USING (person_id);
 127
 128
 129 UPDATE plc_db_version SET subversion = 11;