MaintenanceDao { @Transactional public void deleteActionAndStateHistory(long workflowInstanceId, DateTime olderThan) { long start = currentTimeMillis(); MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("workflowId", workflowInstanceId); params.addValue("olderThan", sqlVariants.toTimestampObject(olderThan)); Long maxActionId = namedJdbc.queryForObject("select max(id) from nflow_workflow_action where workflow_id = :workflowId and " + sqlVariants.dateLtEqDiff("execution_end", ":olderThan"), params, Long.class); int deletedStates = 0; int deletedActions = 0; if (maxActionId != null) { params.addValue("maxActionId", maxActionId); List<Long> referredActionIds = namedJdbc.queryForList( "select distinct(max(action_id)) from nflow_workflow_state where workflow_id = :workflowId group by state_key", params, Long.class); String deleteStates = "delete from nflow_workflow_state where workflow_id = :workflowId and action_id <= :maxActionId"; if (referredActionIds.isEmpty()) { deletedStates = namedJdbc.update(deleteStates, params); } else { params.addValue("referredActionIds", referredActionIds); deletedStates = namedJdbc.update(deleteStates + " and action_id not in (:referredActionIds)", params); } referredActionIds.addAll(namedJdbc.queryForList( "select distinct parent_action_id from nflow_workflow where parent_workflow_id = :workflowId", params, Long.class)); String deleteActions = "delete from nflow_workflow_action where workflow_id = :workflowId and id <= :maxActionId"; if (referredActionIds.isEmpty()) { deletedActions = namedJdbc.update(deleteActions, params); } else { params.addValue("referredActionIds", referredActionIds); deletedActions = namedJdbc.update(deleteActions + " and id not in (:referredActionIds)", params); } } if (deletedActions > 0 || deletedStates > 0) { logger.info("Deleted {} actions and {} states from workflow instance {} that were older than {}. Took {} ms.", deletedActions, deletedStates, workflowInstanceId, olderThan, currentTimeMillis() - start); } } @Inject  MaintenanceDao(SQLVariants sqlVariants, @NFlow JdbcTemplate jdbcTemplate,
      @NFlow NamedParameterJdbcTemplate nflowNamedParameterJdbcTemplate); List<Long> getOldWorkflowIds(TablePrefix table, DateTime before, int maxWorkflows, Set<String> workflowTypes); @Transactional int archiveWorkflows(Collection<Long> workflowIds); @Transactional int deleteWorkflows(TablePrefix table, Collection<Long> workflowIds); @Transactional void deleteActionAndStateHistory(long workflowInstanceId, DateTime olderThan);  }
@Test public void deleteExpiredWorkflowHistory() { WorkflowInstance parentWorkflow = constructWorkflowInstanceBuilder().build(); long parentWorkflowId = workflowInstanceDao.insertWorkflowInstance(parentWorkflow); long addChildActionId = addWorkflowAction(parentWorkflowId, new WorkflowInstance.Builder(parentWorkflow).build(), now(), now()); WorkflowInstance childWorkflow = constructWorkflowInstanceBuilder().setParentWorkflowId(parentWorkflowId) .setParentActionId(addChildActionId).build(); long childWorkflowId = workflowInstanceDao.insertWorkflowInstance(childWorkflow); addWorkflowAction(parentWorkflowId, new WorkflowInstance.Builder(parentWorkflow).putStateVariable("variable", "deletedValue").build(), now(), now().minusDays(1)); addWorkflowAction(parentWorkflowId, new WorkflowInstance.Builder(parentWorkflow).putStateVariable("variable", "preservedValue").build(), now(), now()); maintenanceDao.deleteActionAndStateHistory(parentWorkflowId, now()); parentWorkflow = workflowInstanceDao.getWorkflowInstance(parentWorkflowId, EnumSet.allOf(WorkflowInstanceInclude.class), null); assertThat(parentWorkflow.getStateVariable("requestData"), equalTo("{ \"parameter\": \"abc\" }")); assertThat(parentWorkflow.getStateVariable("variable"), equalTo("preservedValue")); assertThat(parentWorkflow.actions.size(), equalTo(2)); childWorkflow = workflowInstanceDao.getWorkflowInstance(childWorkflowId, emptySet(), null); assertThat(childWorkflow.parentWorkflowId, equalTo(parentWorkflowId)); }