1 -- changing the permission model on tags
2 -- we replace the single 'min_role_id' field attached to tag_types
6 -- create a separate table to keep the tag-type x role relationship
7 CREATE TABLE tag_type_role (
8 tag_type_id integer REFERENCES tag_types NOT NULL, -- tag_type ID
9 role_id integer REFERENCES roles NOT NULL, -- role ID
10 PRIMARY KEY (tag_type_id, role_id)
12 CREATE INDEX tag_type_role_tag_type_id_idx ON tag_type_role (tag_type_id);
13 CREATE INDEX tag_type_role_role_id_idx ON tag_type_role (role_id);
15 -- fill this from the former min_role_id field in the tag_types table
16 -- add all roles lower or equal to the min_role_id
17 INSERT INTO tag_type_role ("tag_type_id","role_id") SELECT tag_type_id,role_id FROM tag_types,roles where role_id<=min_role_id;
19 -- we can now drop the min_role_id column
20 ALTER TABLE tag_types DROP COLUMN min_role_id CASCADE;
22 -- create views to expose roles
23 CREATE OR REPLACE VIEW tag_type_roles AS
25 array_accum(role_id) AS role_ids,
26 array_accum(roles.name) AS roles
28 LEFT JOIN roles USING (role_id)
31 CREATE OR REPLACE VIEW view_tag_types AS
33 tag_types.tag_type_id,
35 tag_types.description,
37 COALESCE((SELECT role_ids FROM tag_type_roles WHERE tag_type_roles.tag_type_id = tag_types.tag_type_id), '{}') AS role_ids,
38 COALESCE((SELECT roles FROM tag_type_roles WHERE tag_type_roles.tag_type_id = tag_types.tag_type_id), '{}') AS roles
42 -- remove min_role_id from the object views
43 CREATE OR REPLACE VIEW view_person_tags AS
45 person_tag.person_tag_id,
48 tag_types.tag_type_id,
50 tag_types.description,
54 INNER JOIN tag_types USING (tag_type_id)
55 INNER JOIN persons USING (person_id);
57 CREATE OR REPLACE VIEW view_site_tags AS
62 tag_types.tag_type_id,
64 tag_types.description,
68 INNER JOIN tag_types USING (tag_type_id)
69 INNER JOIN sites USING (site_id);
71 CREATE OR REPLACE VIEW view_interface_tags AS
73 interface_tag.interface_tag_id,
74 interface_tag.interface_id,
76 tag_types.tag_type_id,
78 tag_types.description,
82 INNER JOIN tag_types USING (tag_type_id)
83 INNER JOIN interfaces USING (interface_id);
85 CREATE OR REPLACE VIEW view_node_tags AS
90 tag_types.tag_type_id,
92 tag_types.description,
96 INNER JOIN tag_types USING (tag_type_id)
97 INNER JOIN nodes USING (node_id);
99 CREATE OR REPLACE VIEW view_slice_tags AS
101 slice_tag.slice_tag_id,
104 slice_tag.nodegroup_id,
105 tag_types.tag_type_id,
107 tag_types.description,
112 INNER JOIN tag_types USING (tag_type_id)
113 INNER JOIN slices USING (slice_id);
116 CREATE OR REPLACE VIEW view_ilinks AS
117 SELECT * FROM tag_types
118 INNER JOIN ilink USING (tag_type_id);
120 -- use this to allow nodes to set slice tags
121 INSERT INTO roles (role_id, name) VALUES (50, 'node');
124 UPDATE plc_db_version SET subversion = 104;