X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=migrations%2F104-down-noderole.sql;h=b8b4adba8f74d2a8f1bd8fc79c7dba4a1a8e4ce0;hb=981421fbc4d90301b4d1866f2695684ad4b7d616;hp=f7d722087f06940e2c760f23a76406f533a28fe0;hpb=0c28b6c095054293cc35c75a7a601486a4c249ff;p=plcapi.git

diff --git a/migrations/104-down-noderole.sql b/migrations/104-down-noderole.sql
index f7d7220..b8b4adb 100644
--- a/migrations/104-down-noderole.sql
+++ b/migrations/104-down-noderole.sql
@@ -1,20 +1,28 @@
--- reverting....
--- DELETE from roles WHERE name='node';
-
 -- recreate the min_role_id column
 ALTER TABLE tag_types ADD COLUMN min_role_id integer REFERENCES roles;
 
 -- compute the highest role available for each tag_type and store it as min_role_id
--- xxx todo
+CREATE OR REPLACE VIEW tag_type_max_role_id AS
+SELECT tag_type_id, max(role_id) from tag_type_role GROUP BY tag_type_id;
+
+-- tag_types that have at least one role in the new model get the max
+UPDATE tag_types 
+SET min_role_id = tag_type_max_role_id.max
+FROM tag_type_max_role_id WHERE tag_type_max_role_id.tag_type_id = tag_types.tag_type_id;
 
---- tmp - set to something so we can run down&up again
-UPDATE tag_types SET min_role_id=10;
-UPDATE tag_types SET min_role_id=20 WHERE tag_type_id%2=0;
+-- the ones with no roles end up with min_role_id=10
+UPDATE tag_types
+SET min_role_id=10
+WHERE min_role_id IS NULL;
+
+DELETE VIEW tag_type_max_role_id;
 
 DROP TABLE tag_type_role CASCADE;
 -- done by cascade
 --DROP VIEW view_tag_types;
 --DROP VIEW tag_type_roles;
 
+DELETE from roles WHERE name='node';
+
 --------------------
 UPDATE plc_db_version SET subversion = 103;