1 -- recreate the min_role_id column
2 ALTER TABLE tag_types ADD COLUMN min_role_id integer REFERENCES roles;
4 -- compute the highest role available for each tag_type and store it as min_role_id
5 CREATE OR REPLACE VIEW tag_type_max_role_id AS
6 SELECT tag_type_id, max(role_id) from tag_type_role GROUP BY tag_type_id;
8 -- tag_types that have at least one role in the new model get the max
10 SET min_role_id = tag_type_max_role_id.max
11 FROM tag_type_max_role_id WHERE tag_type_max_role_id.tag_type_id = tag_types.tag_type_id;
13 -- the ones with no roles end up with min_role_id=10
16 WHERE min_role_id IS NULL;
18 DELETE VIEW tag_type_max_role_id;
20 DROP TABLE tag_type_role CASCADE;
22 --DROP VIEW view_tag_types;
23 --DROP VIEW tag_type_roles;
25 DELETE from roles WHERE name='node';
28 UPDATE plc_db_version SET subversion = 103;