update_role_details.sql

delimiter //
CREATE PROCEDURE `update_role_details`()
begin
DELETE FROM entity_details;
INSERT INTO entity_details (entity_id, displayname) SELECT entity_id,group_concat(concat(lang,';',displayname) ORDER BY lang separator '==') FROM entity_organization GROUP BY entity_id;

INSERT INTO entity_details (entity_id, entity_cat) SELECT entity_id,group_concat(entityattributes_dict_id  separator ';') FROM entity_attributes GROUP BY entity_id ON DUPLICATE KEY UPDATE entity_cat=VALUES(entity_cat);

INSERT INTO entity_details (entity_id,saml2_support) SELECT entity_id, max(saml2) AS saml2_e FROM (SELECT entity_roles.entity_id AS entity_id, max(entity_protocolsupport.value regexp ':2\\\\.0:') AS saml2 FROM entity_protocolsupport JOIN entity_roles ON entity_protocolsupport.entityrole_id = entity_roles.id GROUP BY entity_protocolsupport.entityrole_id) AS r GROUP BY entity_id ON DUPLICATE KEY UPDATE saml2_support=VALUES(saml2_support);

INSERT INTO entity_details (entity_id, clash) SELECT entity_id,count(code) FROM entity_duplication GROUP BY entity_id ON DUPLICATE KEY UPDATE clash=VALUES(clash);

UPDATE entity_details JOIN entities ON entity_details.entity_id=entities.id JOIN eccs_stat ON entities.entityid = eccs_stat.entityid set entity_details.eccs_status=eccs_stat.status, entity_details.eccs_status_date=eccs_stat.update_date;
UPDATE entity_details JOIN entities ON entity_details.entity_id=entities.id JOIN coco_stat ON entities.entityid = coco_stat.entityid set entity_details.coco_status=coco_stat.status, entity_details.coco_id=coco_stat.coco_id;

CREATE TEMPORARY TABLE entity_warn_tmp as (SELECT entities.id as entity_id, BIT_OR(ifnull(1<<entity_role_warnings.warning_id,0))|BIT_OR(ifnull(1<<entity_warnings.warning_id,0)) as warn FROM entities LEFT JOIN entity_warnings ON entities.id=entity_warnings.entity_id LEFT JOIN entity_roles ON entity_roles.entity_id = entities.id LEFT JOIN entity_role_warnings ON entity_role_warnings.entityrole_id=entity_roles.id GROUP BY entities.id);

UPDATE entity_details JOIN entity_warn_tmp ON entity_details.entity_id=entity_warn_tmp.entity_id  set entity_details.validator_status=entity_warn_tmp.warn;

CREATE TEMPORARY TABLE sirtfi_tmp AS (SELECT entity_id from entity_attributes where entityattributes_dict_id = 76);
CREATE TEMPORARY TABLE sirtfi2_tmp AS (SELECT entity_id from entity_attributes where entityattributes_dict_id = 198);

UPDATE entity_details JOIN sirtfi_tmp ON entity_details.entity_id=sirtfi_tmp.entity_id SET entity_details.sirtfi = 1;
UPDATE entity_details JOIN sirtfi2_tmp ON entity_details.entity_id=sirtfi2_tmp.entity_id SET entity_details.sirtfi2 = 1;

UPDATE entity_details JOIN entity_warnings ON entity_details.entity_id = entity_warnings.entity_id SET entity_details.sirtfi=2 WHERE entity_warnings.warning_id >= 50 AND  entity_warnings.warning_id <= 51;

DROP TABLE entity_warn_tmp;
DROP TABLE sirtfi_tmp;
DROP TABLE sirtfi2_tmp;

DELETE FROM federation_warnings;
INSERT INTO federation_warnings (code,warnings) SELECT mds_data.code, bit_or(entity_details.validator_status) FROM mds_data JOIN entities ON mds_data.reg_auth = entities.regauth JOIN entity_details ON entity_details.entity_id = entities.id GROUP BY mds_data.reg_auth;

DELETE FROM role_details;
INSERT INTO role_details (entityrole_id, entity_id, roledesc)  SELECT id,entity_id,roledesc FROM entity_roles;

INSERT INTO role_details (entityrole_id, role_display_name) SELECT entity_roles.id, group_concat(concat(entity_info.lang,';',entity_info.value) ORDER BY lang separator '==') FROM entity_roles JOIN entity_info ON entity_roles.id=entityrole_id WHERE entity_info.type='DisplayName' GROUP BY entity_roles.id ON DUPLICATE KEY UPDATE role_display_name=VALUES(role_display_name);

INSERT INTO role_details (entityrole_id, role_service_name) SELECT entity_roles.id, group_concat(concat(entity_info.lang,';',entity_info.value) ORDER BY lang separator '==') FROM entity_roles JOIN entity_info ON entity_roles.id=entityrole_id WHERE  entity_info.type='ServiceName' GROUP BY entity_roles.entity_id ON DUPLICATE KEY UPDATE role_service_name=VALUES(role_service_name);

INSERT INTO role_details (entityrole_id, scopes) SELECT entityrole_id, group_concat(scope separator '==') FROM entity_scopes GROUP BY entityrole_id ON DUPLICATE KEY UPDATE scopes=VALUES(scopes);

INSERT INTO entity_details (entity_id,roles) SELECT entity_id,sum(role_code) AS entity_roles FROM role_details JOIN role_names ON role_details.roledesc = role_names.roledesc GROUP BY entity_id ON DUPLICATE KEY UPDATE roles=VALUES(roles);
DELETE  FROM entity_fulltext_index WHERE entity_id NOT IN (SELECT id FROM entities);
DELETE  FROM entity_fulltext_index WHERE entity_id in (SELECT id FROM entities WHERE modified = 1);
INSERT INTO entity_fulltext_index (entity_id,txt) SELECT id,ExtractValue(entity_txt,'//*[not(self::ds:X509Certificate) and not(self::mdui:Logo)]')  FROM entities WHERE modified=1;
UPDATE entity_history SET tmp_status=1;
INSERT INTO entity_history (entityid,last_seen,status,tmp_status) SELECT entityid,CURRENT_TIMESTAMP,0,0 FROM entities ON DUPLICATE KEY UPDATE last_seen=CURRENT_TIMESTAMP, status=0, tmp_status=0;
UPDATE entity_history SET status=1 WHERE tmp_status = 1 AND status = 0;
end
//
delimiter ;