Presto 3.5 Schema Updates for PostGres Databases

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

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

-- REMOVE DANGLING RECORDS FROM RO_FILE_ASSOC WHERE REGISTRY OBJECT IS GONE
DELETE FROM RO_FILE_ASSOC
WHERE RO_FILE_ASSOC.ID IN (
SELECT RO_FILE_ASSOC.ID
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'