Presto 3.5 Schema Updates for Oracle 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 OUTER 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 MODIFY RO_ID VARCHAR2(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 OUTER 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'