Presto 3.5 Schema Updates for MS SQL Databases

-- REMOVE DANGLING RECORDS FROM RO_FILE_ASSOC WHERE THE FILE IS GONE
DELETE RO_FILE_ASSOC
FROM RO_FILE_ASSOC
LEFT JOIN "FILE"
ON
(
RO_FILE_ASSOC.FILE_ID = "FILE".ID
)
WHERE "FILE".ID IS NULL;

-- ALTER RO_ID TO MATCH REGISTRYOBJECT.ID
ALTER TABLE RO_FILE_ASSOC ALTER COLUMN RO_ID VARCHAR(700);

-- REMOVE DANGLING RECORDS FROM RO_FILE_ASSOC WHERE REGISTRY OBJECT IS GONE
DELETE RO_FILE_ASSOC
FROM RO_FILE_ASSOC
LEFT JOIN REGISTRYOBJECT
ON
(
RO_FILE_ASSOC.RO_ID = REGISTRYOBJECT.ID
)
WHERE REGISTRYOBJECT.ID IS NULL;

-- ADD THE CONSTRAINTS
ALTER TABLE RO_FILE_ASSOC ADD CONSTRAINT RO_FILE_FILE_FK1 FOREIGN KEY (FILE_ID) REFERENCES "FILE" (ID) ON DELETE CASCADE;
ALTER TABLE RO_FILE_ASSOC ADD CONSTRAINT RO_FILE_RO_FK2 FOREIGN KEY (RO_ID) REFERENCES REGISTRYOBJECT (ID) ON DELETE CASCADE;
-- ALTER EXISTING VALUE
UPDATE CONFIGURATION SET CONFIGVALUE='<html><body>Your password has been updated. Your new password is {1}.
If you have any questions about this change, please contact your administrator.</body></html>'
WHERE CONFIGKEY = 'email.template.body.passwordchanged';

-- INSERT NEW VALUE
INSERT INTO CONFIGURATION (CONFIGKEY, CONFIGVALUE) VALUES ('auditlog.on.excludeActions', 'USER_LOGIN');

-- FORCE RELOAD SECURITY PROFILES ON NEXT RESTART TO PICKUP NEW PROFILES
UPDATE INIT_TRACKING SET REQUIRE_INIT=TRUE WHERE COMPONENT_NAME = 'SecurityProfileDefinitions'