
-- remove Quartz tables / SYNCOPE-93

-- various changes in the Task table / SYNCOPE-144 SYNCOPE-186
UPDATE Task SET name=id;
UPDATE Task SET jobClassName = 'org.apache.syncope.core.sync.impl.SyncJob' WHERE jobClassName = 'org.apache.syncope.core.scheduling.SyncJob';
UPDATE Task SET jobClassName = 'org.apache.syncope.core.quartz.SampleJob' WHERE jobClassName = 'org.apache.syncope.core.scheduling.SampleJob';
UPDATE Task SET actionsClassName=jobActionsClassName;
ALTER TABLE Task DROP COLUMN jobActionsClassName;
UPDATE Task SET objectClassName='__ACCOUNT__', subjectType='USER' WHERE DTYPE='PropagationTask';
UPDATE Task SET subjectId=SYNCOPEUSER_ID;
-- the foreign key id was found via `SHOW CREATE TABLE Task`
ALTER TABLE Task DROP FOREIGN KEY Task_ibfk_2;
ALTER TABLE Task DROP COLUMN SYNCOPEUSER_ID;

-- upgrade ConnId connector bundles to latest versions
UPDATE ConnInstance SET version='1.2.6' WHERE connectorName='org.connid.bundles.soap.WebServiceConnector';
UPDATE ConnInstance SET version='2.1.5' WHERE connectorName='org.connid.bundles.db.table.DatabaseTableConnector';
UPDATE ConnInstance SET version='0.7' WHERE connectorName='org.connid.bundles.csvdir.CSVDirConnector';
UPDATE ConnInstance SET version='1.3.5' WHERE connectorName='org.connid.bundles.ldap.LdapConnector';
UPDATE ConnInstance SET version='1.1.0',connectorName='org.connid.bundles.ad.ADConnector' WHERE connectorName='org.connid.ad.ADConnector';
-- SYNCOPE-279
UPDATE ConnInstance SET connRequestTimeout=10;

-- field renaming in ExternalResource
UPDATE ExternalResource SET enforceMandatoryCondition=forceMandatoryConstraint;
ALTER TABLE ExternalResource DROP COLUMN forceMandatoryConstraint;
UPDATE ExternalResource SET userializedSyncToken=serializedSyncToken;
ALTER TABLE ExternalResource DROP COLUMN serializedSyncToken;

-- Migrating data from old SchemaMapping (removed) to new UMapping, UMappingItem / SYNCOPE-172 and related
-- the temporary AUTO_INCREMENT change for UMapping.id is working for MySQL only and is needed in order to generate ids for new rows in UMapping: for different DBMS you might need to find alternative ways to obtain a similar result
ALTER TABLE UMapping CHANGE COLUMN id id BIGINT(20) AUTO_INCREMENT;
INSERT INTO UMapping SELECT DISTINCT NULL,accountLink,RESOURCE_NAME FROM SchemaMapping,ExternalResource WHERE ExternalResource.name = RESOURCE_NAME;
ALTER TABLE UMapping CHANGE COLUMN id id BIGINT(20);
INSERT INTO UMappingItem SELECT SchemaMapping.id,accountId,extAttrName,intAttrName,intMappingType,mandatoryCondition,password,'BOTH',UMapping.id FROM SchemaMapping,UMapping WHERE UMapping.RESOURCE_NAME=SchemaMapping.RESOURCE_NAME;
UPDATE UMappingItem SET intMappingType='UserId' WHERE intMappingType='SyncopeUserId';
DROP TABLE SchemaMapping;

-- MD5 not supported any more / SYNCOPE-51
-- this change is working alongside with SyncopeMD5FallbackAuthenticationProvider
UPDATE SyncopeUser SET cipherAlgorithm='SMD5' WHERE cipherAlgorithm='MD5';

-- entitlement rename / SYNCOPE-331
DELETE FROM Entitlement WHERE name='RESOURCE_GETOBJECT';
INSERT INTO Entitlement VALUES('RESOURCE_GETCONNECTOROBJECT', null);
-- new entitlement / SYNCOPE-319
INSERT INTO Entitlement VALUES('CONNECTOR_RELOAD', null);

-- remove search views, to be re-created by ContentUpgrader
DROP VIEW user_search;
DROP VIEW user_search_attr;
DROP VIEW user_search_membership;
DROP VIEW user_search_resource;

