#!/bin/bash

# SQL情報
PREFIX=
USER=
PASSWORD=
SCHEMA=

# 最終保存日
DATE=`date '+%Y-%m-%d'`

mysql -u${USER} -D${SCHEMA} -p${PASSWORD} <<EOF
begin;

DELETE FROM ${PREFIX}_STEP_EXECUTION_CONTEXT WHERE STEP_EXECUTION_ID
 IN (SELECT STEP_EXECUTION_ID FROM ${PREFIX}_STEP_EXECUTION WHERE JOB_EXECUTION_ID
 IN (SELECT JOB_EXECUTION_ID FROM  ${PREFIX}_JOB_EXECUTION where CREATE_TIME < '${DATE}'));

DELETE FROM ${PREFIX}_STEP_EXECUTION WHERE JOB_EXECUTION_ID
 IN (SELECT JOB_EXECUTION_ID FROM ${PREFIX}_JOB_EXECUTION where CREATE_TIME < '${DATE}');

DELETE FROM ${PREFIX}_JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID
 IN (SELECT JOB_EXECUTION_ID FROM  ${PREFIX}_JOB_EXECUTION where CREATE_TIME < '${DATE}');

DELETE FROM ${PREFIX}_JOB_EXECUTION_PARAMS WHERE JOB_EXECUTION_ID
 IN (SELECT JOB_EXECUTION_ID FROM ${PREFIX}_JOB_EXECUTION where CREATE_TIME < '${DATE}');

DELETE FROM ${PREFIX}_JOB_EXECUTION where CREATE_TIME < '${DATE}';

DELETE FROM ${PREFIX}_JOB_INSTANCE WHERE JOB_INSTANCE_ID
 NOT IN (SELECT JOB_INSTANCE_ID FROM ${PREFIX}_JOB_EXECUTION);

commit;
EOF