6 -- purpose: provide tags on site and person objects in db
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
19 CREATE OR REPLACE VIEW site_tags AS
21 array_accum(site_tag_id) AS site_tag_ids
25 CREATE OR REPLACE VIEW view_site_tags AS
30 tag_types.tag_type_id,
32 tag_types.description,
34 tag_types.min_role_id,
37 INNER JOIN tag_types USING (tag_type_id)
38 INNER JOIN sites USING (site_id);
41 CREATE OR REPLACE VIEW view_sites AS
46 sites.abbreviated_name,
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,
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
67 LEFT JOIN peer_site USING (site_id);
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
78 CREATE OR REPLACE VIEW person_tags AS
80 array_accum(person_tag_id) AS person_tag_ids
84 CREATE OR REPLACE VIEW view_person_tags AS
86 person_tag.person_tag_id,
89 tag_types.tag_type_id,
91 tag_types.description,
93 tag_types.min_role_id,
96 INNER JOIN tag_types USING (tag_type_id)
97 INNER JOIN persons USING (person_id);
99 DROP VIEW view_persons;
100 CREATE OR REPLACE VIEW view_persons AS
109 persons.verification_key,
110 CAST(date_part('epoch', persons.verification_expires) AS bigint) AS verification_expires,
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,
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
126 LEFT JOIN peer_person USING (person_id);
129 UPDATE plc_db_version SET subversion = 11;