file1.txt
/* get the list of synced issues - has to be unquoted for mysql */ SELECT LR.ISSUE_URN AS this_issue, RR.ISSUE_URN AS remote_issue FROM AO_08F1AF_TWIN_TRACE TT LEFT JOIN AO_08F1AF_REPLICA LR ON TT.LOCAL_REPLICA_ID = LR.ID LEFT JOIN AO_08F1AF_REPLICA RR ON TT.REMOTE_REPLICA_ID = RR.ID JOIN AO_08F1AF_RELATION R ON TT.RELATION_ID = R.ID WHERE R.NAME IN ('CONNECTION NAME'); // ------
cleanup-connection.sql
/* execute these only once that you've obtained and recorded the list of synced issues via the previous query */ /* TRACES FROM TWIN_TRACE, ISSUE_URN - LOCAL */ DELETE FROM AO_08F1AF_TRACE WHERE TWIN_TRACE_ID IN (SELECT TT.ID FROM AO_08F1AF_TWIN_TRACE TT JOIN AO_08F1AF_RELATION R ON TT.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME'); /* TRACES FROM SYNC EVENT, ISSUE_URN - LOCAL */ DELETE FROM AO_08F1AF_TRACE WHERE EVENT_ID IN (SELECT SE.ID FROM AO_08F1AF_SYNC_EVENT SE JOIN AO_08F1AF_RELATION R ON SE.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME'); /* REMOTE TRACES FROM SYNC REQUESTS, ISSUE_URN - REMOTE */ DELETE FROM AO_08F1AF_TRACE WHERE REQUEST_ID IN (SELECT SR.ID FROM AO_08F1AF_SYNC_REQUEST SR JOIN AO_08F1AF_RELATION R ON SR.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME'); /* CONNECT CONTEXT FROM SYNC EVENT, ISSUE_URN - LOCAL */ DELETE FROM AO_08F1AF_CONNECT_CONTEXT WHERE ID IN (SELECT SE.CONNECT_CONTEXT_ID FROM AO_08F1AF_SYNC_EVENT SE JOIN AO_08F1AF_RELATION R ON SE.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME'); /* CONNECT CONTEXT FROM SYNC REQUEST, ISSUE_URN - REMOTE */ DELETE FROM AO_08F1AF_CONNECT_CONTEXT WHERE ID IN (SELECT SR.CONNECT_CONTEXT_ID FROM AO_08F1AF_SYNC_REQUEST SR JOIN AO_08F1AF_RELATION R ON SR.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME'); /* ERRORS for LOCAL ISSUE, ISSUE_URN - LOCAL - rectified */ DELETE FROM AO_08F1AF_ERROR WHERE ID IN (select eid from (SELECT E.ID as eid FROM AO_08F1AF_ERROR E JOIN AO_08F1AF_RELATION R ON E.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME') as e); /* BLOB EVENTS, ISSUE_URN - LOCAL */ DELETE FROM AO_08F1AF_BLOB_EVENT WHERE SYNC_EVENT_ID IN ( SELECT SE.ID FROM AO_08F1AF_SYNC_EVENT SE JOIN AO_08F1AF_RELATION R ON SE.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME' ); /* BLOB REQUESTS, ISSUE_URN - REMOTE */ DELETE FROM AO_08F1AF_BLOB_REQUEST WHERE SYNC_REQUEST_ID IN ( SELECT SR.ID FROM AO_08F1AF_SYNC_REQUEST SR JOIN AO_08F1AF_RELATION R ON SR.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME' ); /* blob metadata for sync events, ISSUE_URN - LOCAL */ DELETE FROM AO_08F1AF_BLOB_METADATA WHERE SYNC_EVENT_FOR_BLOB_ID IN ( SELECT SE.ID FROM AO_08F1AF_SYNC_EVENT SE JOIN AO_08F1AF_RELATION R ON SE.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME' ); /* blob metadata for sync requests, ISSUE_URN - REMOTE */ DELETE FROM AO_08F1AF_BLOB_METADATA WHERE SYNC_REQUEST_FOR_BLOB_ID IN ( SELECT SR.ID FROM AO_08F1AF_SYNC_REQUEST SR JOIN AO_08F1AF_RELATION R ON SR.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME' ); /* sync requests, ISSUE_URN - REMOTE */ DELETE FROM AO_08F1AF_SYNC_REQUEST WHERE RELATION_ID IN ( SELECT R.ID FROM AO_08F1AF_RELATION R WHERE R.NAME = 'CONNECTION NAME' ); /* sync event, ISSUE_URN - LOCAL */ DELETE FROM AO_08F1AF_SYNC_EVENT WHERE RELATION_ID IN ( SELECT R.ID FROM AO_08F1AF_RELATION R WHERE R.NAME = 'CONNECTION NAME' ); /* TWIN_TRACE, ISSUE_URN - LOCAL */ DELETE FROM AO_08F1AF_TWIN_TRACE WHERE RELATION_ID IN ( SELECT R.ID FROM AO_08F1AF_RELATION R WHERE R.NAME = 'CONNECTION NAME' );