#!/bin/sh

help() {
  echo ""  
  echo ""
  echo Usage: "${0} -d -i -t -h"
  echo "  -d : absolute path to the IS installation"
  echo "  -i : Integration Server instance name."
  echo "       The default value is 'default'"
  echo "  -t : list of tables to compress."
  echo "       The default value is 'IS_MONITOR, IS_SERVER_STATS, IS_SERVICE_STATS IS_SERVER_DETAILS'" 
  echo "  -ij: Run the Derby ij SQL client"
  echo "  -h : This help text"
  echo "" 
}

script_dir="$(dirname $0)"
instance="default"
install_dir=
run_ij=
tables="IS_MONITOR, IS_SERVICE_STATS, IS_SERVER_STATS, IS_SERVER_DETAILS"

while [ $# -gt 0 ]
do
   case "$1" in
     -d) shift
         install_dir="$1"
         ;;
     -i) shift
         instance="$1"
         ;;
     -t) shift
         tables="$1"
         ;;
    -ij) run_ij="true"
         ;;    
     -h) shift 
         help
         echo ""
         exit 1
         ;;    
    *)  echo "$1 is an invalid argument."
        help
        echo ""
        exit 1
         ;;
   esac
   shift
done

if [ -z "${run_ij}" ]
then

    echo ""  
    echo ""
    echo "Welcome to the Apache Derby embedded database compression script."
    echo ""
    echo "This utility must be run while the Integration Server is running."
    echo "The required Apache derby jars must be present in the same directory as this script."
    echo "The necessary instructions are included in the Tech Community article with this script."
    echo ""
    echo "This script creates a backup of the existing embedded database prior to compression."
    echo "It does not remove the backup when compression completes."
    echo "Verify the Integration Server start up is successful and there are no unexpected database"
    echo "     errors in the server.log before removing the backup copy."
    echo ""
    echo "The Derby diagnostic command shows disk space allocation for all tables in the schema."
    echo "These details are provided before and after the compression to determine which tables"
    echo "     produced the most benefit."
    echo ""
    echo ""
fi

if [ -z "${install_dir}" ]
then
  echo "The IS installation directory path is required. (-d <path>)"
  echo ""
  exit 1
fi

if [ ! -d "${install_dir}" ]
then
  echo "${install_dir} does not exist"
  echo ""
  exit 1
fi

bin_dir="${install_dir}/profiles/IS_${instance}/bin"
if [ ! -d "${bin_dir}" ]
then 
   echo "${bin_dir} does not exist."
   echo ""
   exit 1
fi

ls ${bin_dir}/*.pid > /dev/null 2>&1
status=$? 
if [ $status -eq 0 ]
then    
   echo "The Integration Server is running."
   echo "Stop the server and ensure there are no '*.pid' files in the instance's bin directory before running this utility."
   echo ""
   exit 1      
fi 


db_dir="${install_dir}/IntegrationServer/instances/${instance}/db"
db_dir=$(echo $db_dir | tr -s '/')

if [ ! -d "${db_dir}" ]
then
  echo "No embedded database in this location: ${db_dir}"
  echo ""
  exit 1
fi

db_backup="${install_dir}/IntegrationServer/instances/${instance}/db"
db_backup=$(echo $db_backup | tr -s '/')

java_dir="${install_dir}/jvm/jvm"
java_dir=$(echo $java_dir | tr -s '/')

if [ ! -d "${java_dir}" ] 
then 
  echo "The Java installation directory does not exist: ${java_dir}"
  echo ""
  exit 1
fi

java_cmd="${java_dir}/bin/java"
java_cmd=$(echo $java_cmd | tr -s '/')

# one line and space delimited
derby_jars="$(find ${script_dir} -name '*derby*.jar' -print | xargs echo)"

if [ -z "${derby_jars}" ]
then
    echo "Required Apache Derby jar files are missing in ${script_dir}"
    echo ""
    exit 1
fi
derby_cp=$(echo "${derby_jars}" | tr 'jar ' 'jar:')



tables=$(echo $tables | sed 's/ *//g')


db_size=$(du -hs ${db_dir} | cut -d ' ' -f 1)

echo ""
echo "     IS installation directory: ${install_dir}"
echo "                   IS instance: ${instance}"
echo "            Tables to compress: ${tables}"
echo "         db directory and size: ${db_size}"
echo ""

if [ -z "${run_ij}" ]
then 
    echo "     Available space for db backup."
    df -h ${db_dir}
    echo ""


    if [ -z "${DERBY_UNATTENDED_COMPRESS}" ] 
    then
       echo -n 'Continue? (y/n) <n> '
       read cont
       if [ "${cont}" != "y" ]
       then
         echo "Quitting"
          echo ""
          exit 1
       fi
    fi

    echo ""
    echo ""

    db_backup_dir="${install_dir}/IntegrationServer/instances/${instance}/db_$(date '+%Y%m%d_%H%M%S')"
    db_backup_dir=$(echo $db_backup_dir | tr -s '/')
    db_backup_tar="${db_backup_dir}.tar"

    db_error_dir="${install_dir}/IntegrationServer/instances/${instance}/db-failed-compression_$(date '+%Y%m%d_%H%M%S')"
    db_error_dir=$(echo ${db_error_dir} | tr -s '/')
    db_error_tar="${db_error_dir}.tar"

    echo "Creating db backup: ${db_backup_dir}"
    echo ""
    cp -rf "${db_dir}" "${db_backup_dir}"
    status=$?

    if [ "${status}" -eq 0 ]
    then 
    echo "Backup successful."
    echo "If compression fails, restore the db directory using this backup."
    echo ""
    else
    echo "Quitting due to error creating backup."
    echo ""
    exit 1
    fi 
fi

derby_run_script="${script_dir}/derby-run.sql"
rm -f "${derby_run_script}"

if [ -z "${run_ij}" ]
then 
    echo "Creating ${derby_run_script} ..."
    echo ""

    echo "SHOW SCHEMAS;" > ${derby_run_script}
    echo "SET SCHEMA APP;" >> ${derby_run_script}
    echo "elapsedtime on;" >> ${derby_run_script}

    echo "SELECT CONGLOMERATENAME, ESTIMSPACESAVING, NUMALLOCATEDPAGES, NUMFREEPAGES \
        FROM SYS.SYSTABLES systabs, \
        TABLE (SYSCS_DIAG.SPACE_TABLE( systabs.tablename )) AS T2 \
        WHERE systabs.tabletype = 'T' and ISINDEX=0 ORDER BY CONGLOMERATENAME;" >> ${derby_run_script} 

    ifs_default=$IFS
    IFS=","

    for i in ${tables}
    do
        echo "select count(*) from ${i};" >> ${derby_run_script}
        echo "call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', '${i}', 1);" >> ${derby_run_script}  
    done
    IFS=${ifs}

    echo "SELECT CONGLOMERATENAME, ESTIMSPACESAVING, NUMALLOCATEDPAGES, NUMFREEPAGES \
        FROM SYS.SYSTABLES systabs, \
        TABLE (SYSCS_DIAG.SPACE_TABLE( systabs.tablename )) AS T2 \
        WHERE systabs.tabletype = 'T' and ISINDEX=0 ORDER BY CONGLOMERATENAME;" >> ${derby_run_script} 

    echo "EXIT;" >> ${derby_run_script}
fi

if [ -z "${run_ij}" ]
then 
    echo "--------------------------"
    cat "${derby_run_script}"
    echo "--------------------------"
    echo ""
fi

ij_cmd="${java_cmd} -cp ${derby_cp} -Dij.protocol=\""jdbc:derby:\"" \
   -Dij.connection.IS_${instance}=\""${db_dir}/embedded\;create=false\;databaseName=APP\"" \
   -Dderby.storage.indexStats.log=true\; \
   -Dderby.storage.indexStats.trace=log \
   -Dderby.storage.indexStats.auto=true \
   -Dderby.language.logQueryPlan=true \
   -Dderby.stream.error.logSeverityLevel=0 \
   -Dderby.locks.deadlockTrace=true \
   -Dderby.locks.waitTimeout=300 \
   -Dderby.debug.true=LogTrace \
   org.apache.derby.tools.ij"

if [ -z "${run_ij}" ] 
then
    ij_cmd="${ij_cmd} <  ${derby_run_script}"
    echo ${ij_cmd}
    echo "Executing SQL script."
    echo ""

    trap_signals="2"
    trap "echo 'Derby stored procedure is still running'" ${trap_signals}
else 
    echo ""
    echo "Useful commands: show tables; show schemas; describe <name>; exit; help; "    
    echo ""
fi 

eval ${ij_cmd}
status=$?
echo ""
echo ""

if [ -z "${run_ij}" ] 
then

    trap ${trap_signals}

    if [ "${status}" -eq 0 ]
    then 

        echo "Compression completed."
        echo ""
        echo "Creating ${db_backup_tar}. This may take a while."
        echo ""
        tar zcf "${db_backup_tar}" "${db_backup_dir}"
        status=$?
        if [ "${status}" -eq 0 ]
        then 
            rm -rf "${db_backup_dir}"
        fi  

    else

        echo "Compression returned a failure."
        echo ""
        echo "Creating ${db_error_tar}. This may take a while."
        echo ""
        eval mv ${db_dir} ${db_error_dir}
        tar zcf "${db_error_tar}" "${db_error_dir}"
        status=$?
        if [ "${status}" -eq 0 ]
        then 
            rm -rf "${db_error_dir}"
        fi  
        echo "Restoring the db directory with ${db_backup_dir}"
        mv ${db_backup_dir} ${db_dir}
        echo "Quitting"
        echo ""
        exit 1

    fi 
fi
echo ""
echo ""
echo "exit status = ${status}"
echo "Execution completed."