#!/bin/ksh

# v3.3, Michael Wang, 2002-05-07, <xw73@columbia.edu>.
# * Change _grep "ERROR"_ to _grep "^ERROR"_ as the SQL source
#   may contain the magic word "ERROR".
#
# v3.2, Michael Wang, 2002-02-14, <xw73@columbia.edu>.
# * Change table name from LOAD_LOG to JOBS_LOG. The new table
#   has different definition from the old one.
#
# v3.1, Michael Wang, 2002-02-06, <xw73@columbia.edu>.
# * Updated man page to include table creation SQL script.
# * Made load_log_table optional.
# * Generate unique log file name.
# * Made the length of keeping the log files adjustable.
#
# v3.0, Michael Wang, 2002-02-03, <xw73@columbia.edu>.
# * Simplied, user friendly argument specification (new style).
#   Eliminated derivable arguments. Backward compatible with old style.
# * Pushed application specific info in job configuration file,
#   becomes a generally useful utility for the first time.
# * Allow each login to have its own passwd, default to the common passwd.
# * Added help=y man page.
#
# v2.1, Michael Wang, 2001-08-17, <xw73@columbia.edu>.
# * Changed logfile format from *.out.$THE_DATE to *.$THE_DATE.out
#   to be consistent with process_feed.ksh (ordered by date).
#
# v2.0, Michael Wang, 2001-06-11, <xw73@columbia.edu>.
# * Allow SQL scripts to have arguments.
# * Consolidated all program/log files under a common dir.
# * Eliminated all hardcoded path except that used by autosys.
# * Substantially reduced the number of paramters sourced.
# * Eliminated source from multilple programs.
# * No password shows in ps.
# * Allow sql name to have or not to have .sql extention.
#
# v1.0, Prior to 2001, Author Unknown.

set +x

function my_getopts {
# version 3.141592, 2002-02-02, Michael Wang <xw73@columbia.edu>.
  typeset PATH= SEP= i
  typeset -u _I
  for i; do
    _I=$i
    eval ${_I%%?(:)=*}= unset ${_I%%?(:)=*}
    case $_I in
      *[!:]=*) eval $(IFS=$SEP; print ${_I%%=*}=\"${_I#*=}\")   ;;
      *:=*)    eval $(IFS=$SEP; print ${_I%%:=*}=\"${i#*:=}\")  ;;
    esac
  done
}

ARG_STYLE= unset ARG_STYLE
ARG_STYLE=NEW
for i; do
  [[ $i == *=* ]] || { ARG_STYLE=OLD; break; }
done

if [[ $ARG_STYLE == NEW ]]; then
  my_getopts myname:=   \
             login=     \
             sql:=      \
             log_table= \
             help=      \
             "$@"
  LOADER_LOGON=$LOGIN
  SQL_FILE="$SQL"
else
  SQL_FILE=$1
  LOADER_LOGON=${2%%/*}
  SQLDIR=$3
  PROGRAM_NAME=$4
  MYNAME=$SQLDIR/$PROGRAM_NAME
fi

[[ $HELP == Y || $HELP == PDF ]] && {
  i= t= unset i t
  i=$(basename $0 .ksh)
  t=/tmp/$b.$$.$RANDOM
  mkdir $t && trap "rm -rf $t" EXIT INT
  sed -n "s:^## \{0,1\}::p" $0 > $t/$i.pod
  [[ $HELP == Y   ]] && perldoc $t/$i.pod
  [[ $HELP == PDF ]] && pod2man $t/$i.pod | groff -man | ps2pdf - > $i.pdf
  exit 0
}

BINDIR=${0%/*}
ETCDIR=$BINDIR/../etc
. $ETCDIR/.jobs_env

typeset -u LOADER_LOGON  # needed for LOADER_PASS

[[ -n $LOG_TABLE ]] && LOAD_LOG_TABLE=$LOG_TABLE
: ${LOAD_LOG_TABLE:=Y}

[[ -n $MYNAME ]] || {
  print "ERROR: Caller program (myname:=...) is not specified."
  print "Type \"$0 help=y\" for more info."
  exit $ARG_MISMATCH
}

PROGRAM_NAME=$(basename $MYNAME)
PROGRAM_DIR=$(cd $(dirname $MYNAME) >/dev/null && pwd -P)
PROGRAM_PATH=$PROGRAM_DIR/$PROGRAM_NAME

: ${SQL_FILE:=${PROGRAM_PATH%.ksh}.sql}
: ${LOADER_LOGON:=${PROGRAM_DIR##*/}}

[[ -z $LOADER_LOGON ]] && {
  print "ERROR: login name is not specified."
  print "Type \"$0 help=y\" for more info."
  exit $ARG_MISMATCH
}

i= j= FOUND= unset i j FOUND
for i in "$SQL_FILE" "$PROGRAM_DIR/$SQL_FILE"
do
  j=${i%% *}
  [[ -r $j ]] || [[ -r $j.sql ]] && {
    SQL_FILE=$i
    FOUND="set"
    break
  }
done

[[ -z "$FOUND" ]] && {
  print "ERROR: Could not find SQL file ${SQL_FILE%% *}."
  print "Type \"$0 help=y\" for more info."
  exit $ARG_MISMATCH
}

SQL_BASE=$(basename ${SQL_FILE%% *} .sql)
MODULE_NAME=$SQL_BASE

LOGDIR=$PROGRAM_DIR/log/$SQL_BASE
[[ -d $LOGDIR ]] || mkdir -p $LOGDIR

THE_LOGFILE= unset THE_LOGFILE
while [[ -z $THE_LOGFILE || -f $THE_LOGFILE ]]
do
  [[ -z $THE_LOGFILE ]] || sleep $(( RANDOM%10 ))
  THE_DATE=$(date +%m-%d.%H:%M)
  THE_LOGFILE=$LOGDIR/$SQL_BASE.$THE_DATE.out
done

print "Please check the log file: $THE_LOGFILE"
exec > $THE_LOGFILE 2>&1

function load_log_table {
  sqlplus << EOF
    $idlog/$pwdlog
    INSERT INTO JOBS_LOG (
      PROGRAM_NAME,
      MODULE_NAME,
      LOAD_STATUS,
      PROCESS_DATE
    ) VALUES (
     '$PROGRAM_NAME',
     '$MODULE_NAME',
      TO_NUMBER('$LOAD_STATUS'),
      SYSDATE);
     EXIT
EOF
}

notify_contact() {
  { print "$ORACLE_SID"
    date
    print "ERROR IN processing $PROGRAM_NAME"
    print "Exit Code=$LOAD_STATUS"
  } | if [[ -n $JOB_PAGER ]]; then
        mailx -s "ERROR: $ORACLE_SID $PROGRAM_NAME" $JOB_PAGER
      else
        cat
      fi
}

eval LOADER_PASS=\$${LOADER_LOGON}PASS
: ${LOADER_PASS:=$pwd}

print "INFO: Running sqlfile and arguments ($SQL_FILE) as user ($LOADER_LOGON)."

sqlplus << EOF
  $LOADER_LOGON/$LOADER_PASS
  set time on
  set timing on
  set echo on
  @$SQL_FILE
  exit
EOF

if grep "^ERROR" $THE_LOGFILE; then
  (( LOAD_STATUS = SQL_PROCESSING_ERROR ))
  print "ERROR: $PROGRAM_NAME failed ($LOAD_STATUS)."
  notify_contact
else
  (( LOAD_STATUS = SUCCESS ))
fi

[[ $LOAD_LOG_TABLE == Y ]] && load_log_table
: ${LOG_KEEP:=7}
(( LOG_KEEP >= 0 )) && find $LOGDIR -mtime +$LOG_KEEP -exec rm {} \;
exit $LOAD_STATUS

## =head1 NAME
##
## process_sqlfile - process sqlfile (what else it could be)
##
## =head1 SYNOPSIS
##
## B<process_sqlfile.ksh>
##   myname:=I<caller_full_pathname>
##   [login=I<database_login>]
##   [sql:="I<sql_file args>"]
##   [log_table=y|n]
##   [help=y]
##
## B<process_sqlfile.ksh>
##   P1: SQL file to be processed (using quotes with arguments)
##   P2: Login name to connect to the database
##   P3: Program directory
##   P4: Program name
##
## =head1 DESCRIPTION
##
## B<process_sqlfile.ksh> processes SQL file for you, and
## does the error checking.
##
## The new key=val argument style requires only one mandatory argment,
## myname, which is the program name that calls B<process_sqlfile.ksh>.
## (the rest can be either derived or have default values). However,
## the caller name is available as "$0" in the caller's program, this is
## not user replaceable part. If we can rely on the default, and derivable
## values, then the caller program is maintenance free. For different jobs,
## the content of the caller program is identical, only the names differ.
##
## And if you do need to supply additional parameters, it can be in any
## order. ":=" is used for case sensitive options, myname and sql, and
## "=" is used for other options, which are case insensitive.
##
## The old argument style requires exactly 4 arguments, in the order shown
## above.
##
## The new B<process_sqlfile.ksh> is backward compatible, ie, it accepts both
## new and old argument style. I hope you find the new style is easier to
## use and maintain.
##
## =head1 COMMAND LINE OPTIONS
##
## SQL file is needed in old argument style. It is the SQL filename
## to be processed. If the SQL file has arguments, the file and arguments
## should be quoted, like this, "I<SQL_file> I<arg1> I<arg2> ...".
##
## It is optional in new argument style. If not specified, the SQL file
## is assumed to have the same name as the caller program, with .sql extention.
## For example, if caller program is my_job.ksh, the SQL file is assumed
## to be my_job.sql. It is specified with "sql:=" option. Again use quotes
## if there are arguments.
##
## Login name is second argument to B<process_sqlfile> in old argument style.
## It is optional in new argument style. If not specified, it is assumed to be
## the same as immediate directory containing the caller program, as the caller
## programs are organized by schemas.
##
## "Program directory", and "Program name" are only needed in old argument style.
## The new argument style do not need these parameters.
##
## "log_table=y|n" specifies whether to load the log table. The info is collected 
## regardless. The use of log table allows to generate a nice looking report
## (which most managers love to see, and it helps you with getting good bonus).
## The precedence is the command line option, job configuration file, and the
## default which is Y (who don't like to get good bonus?).
##
## If you do use log_table, the creation script is
##
##   create table jobs_log (
##     PROGRAM_NAME     VARCHAR2(200)  NOT NULL,
##     FEED_NAME        VARCHAR2(200),
##     HDR_LABEL        VARCHAR2(200),
##     TRL_LABEL        VARCHAR2(200),
##     TRLR_RECS_RCVD   NUMBER,
##     FILE_RECS_COUNT  NUMBER,
##     FILE_RCVD_DATE   DATE,
##     MODULE_NAME      VARCHAR2(200),
##     LOAD_STATUS      NUMBER,
##     RECS_LOADED      NUMBER,
##     RECS_REJ         NUMBER,
##     RECS_DSC         NUMBER,
##     LOGFILE          VARCHAR2(200),
##     PROCESS_DATE     DATE           NOT NULL,
##   )
##
## "help=y" prints out the man page you are reading.
##
## =head1 CONFIGURATION FILE
##
## B<process_sqlfile.ksh> shares the same configuration file with B<process_feed.ksh>.
##
## A sample configuration file is shown below.
##
##   # BASIC ENVIRONMENT VARIABLES FOR ORACLE
##
##   typeset -x ORACLE_SID=BCCS1
##   typeset -x ORACLE_HOME=/bcc-bccs1-s/ora01/app/oracle/product/8.1.7
##   typeset -x PATH=$(/bin/getconf PATH):/usr/local/bin:$ORACLE_HOME/bin
##   typeset -x TNS_ADMIN=/var/opt/oracle
##   typeset -x LD_LIBRARY_PATH=
##
##   # USERS AND PASSWORDS
##
##   idlog=<login>           # user name and
##   pwdlog=<passwd>         # passwd for "INSERT INTO JOBS_LOG"
##   <SCHEMA>PASS=<passwd>   # <SCHEMA> passwd for process feed and sqlfile
##   pwd=<passwd)            # common passwd in the absense of <SCHEMA>PASS
##
##   # EXIT STATUS CODES
##
##   (( SUCCESS                 =  0 ))
##   (( FEED_NOT_EXISTS         =  1 ))
##   (( FEED_HDR_NOT_EXISTS     =  2 ))
##   (( FEED_TRL_NOT_EXISTS     =  3 ))
##   (( FEED_COUNT_MISMATCH     =  4 ))
##   (( LOAD_COUNT_ZERO         =  5 ))
##   (( LOAD_COUNT_MISMATCH     =  6 ))
##   #
##   (( SQL_PROCESSING_ERROR    =  9 ))
##   (( ARG_MISMATCH            = 10 ))
##   (( DIRECTORY_NOT_EXISTS    = 11 ))
##   (( SQLLOAD_EXECUTION_ERROR = 12 ))
##   (( EXCEED_MAX_ERRORS       = 13 ))
##   (( EXCEED_MAX_DISCARDS     = 14 ))
##
##   # FEED
##
##   INCOMING_DIR=~ftpstars
##   BACKUP_DIR=~ftpstars/backup
##   (( DATA_KEEP = -1 ))  # days to keep data file (negative number = forever)
##   ((  LOG_KEEP = -1 ))  # days to keep log file  (negative number = forever)
##
##   # NOTIFICATION
##
##   JOB_EMAIL="foo@my.com,bar@my.com"
##   JOB_PAGER="1234567@pager.com,7654321@pager.com"
##
## =head1 DIRECTORY STRUCTURE
##
## The setup is similar to B<process_feed.ksh>.
##
## I described below a preferred directory structure. No abosolute path is hard coded
## in this setup, which makes the program very portable.
##
## First, you setup the top level job directory, I<whatever_dir>/jobs, which we
## will refer as I<TOP_DIR>.
##
## Secondly, you setup bin directory under I<TOP_DIR>, that is where this program
## B<process_sqlfile.ksh> should reside. And its configuration file should be in
## I<TOP_DIR>/etc and shuld be named ".jobs_env". It should be be read/write by owner
## only.
##
## Thirdly, the caller programs should be organized by schema. For example,
##
##   bc_load_del_pending_trades.ksh
##   bc_load_del_pending_trades.sql
##
## are placed under I<TOP_DIR>/trade. B<process_sqlfile.ksh> will put the log files
## under:
##
##   I<TOP_DIR>/trade/bc_load_del_pending_trades:
##
##   log/bc_load_del_pending_trades/bc_load_del_pending_trades.02-03_22:41.out
##   log/bc_load_del_pending_trades/bc_load_del_pending_trades.02-03_22:46.out
##   log/bc_load_del_pending_trades/bc_load_del_pending_trades.02-03_22:53.out
##
## The *.out is the unix stdout and stderr, which includes SQL code and output.
##
## The autosys command line would be:
##
##   command: I<TOP_DIR>/trade/bc_load_del_pending_trades.ksh
##
## =head1 SECURITY
##
## The user program does not handle passwd, I<process_sqlfile.ksh> retrieve
## the passwd for the login. The passwd file should be read only by
## the program, and the program disables trace, and is only modifiable
## by the owner.
##
## The passwd is not part of sqlplus command arguments and therefore
## does not show up in ps output.
##
## =head1 CVS REPOSITORY
##
## By design, all programs, control files, sql files are under the same
## top level directory, it is easier to put them under version control system
## CVS. This avoids the clutter we had on the production system.
##
## Here is an example to commit a change to CVS:
##
##   $ cvs ci -m "ver 3.0 tested"  process_feed.ksh
##   Checking in process_feed.ksh;
##   /bcc-bccs1-s/ora01/home/oracle/jobs-repo/jobs/bin/process_feed.ksh,v
##   +  <--  process_feed.ksh
##   new revision: 1.6; previous revision: 1.5
##   done
##
## =head1 EXAMPLES
##
## New style:
##
##   $ cat bc_load_del_pending_trades_a.ksh
##
##   #!/bin/ksh
##   [[ $0 == /* ]] || exec $(pwd -P)/$0 "$@"
##   ${0%%/jobs*}/jobs/bin/process_sqlfile.ksh myname:=$0
##
##   $ cat bc_load_del_pending_trades_a.sql
##
##   delete from pending_trades where trade_date=20011208;
##
##   $ cat bc_load_del_pending_trades_b.ksh
##
##   #!/bin/ksh
##   [[ $0 == /* ]] || exec $(pwd -P)/$0 "$@"
##   ${0%%/jobs*}/jobs/bin/process_sqlfile.ksh myname:=$0 sql:="${0%.ksh} 20011208"
##
##   $ cat bc_load_del_pending_trades_b.sql
##
##   delete from pending_trades where trade_date=&1;
##
## Old style:
##
##   $ cat bc_load_del_pending_trades_b.ksh
##
##   #!/bin/ksh
##   [[ $0 == /* ]] || exec $(pwd -P)/$0 "$@"
##   ${0%%/jobs*}/jobs/bin/process_sqlfile.ksh \
##     "${0%.ksh} 20011208" \
##     trade/ignored        \
##     ${0%/*}              \
##     ${0##*/}
##
## Please note that "/ignored" part was supposed to be "/passwd". As this is not secure,
## the passwd handling is moved from the user program to the driver program, and moved from
## command line to here document. However, we keep the syntax for backward compatibility.
##
## As you can see, the parameter in 3rd and 4th position can be derived from "$0", myname.
## That is why these parameters are not needed in the new argument style.
##
## Please also note that no path is hard coded in the new argument style.
##
## =head1 SEE ALSO
##
## process_feed.ksh help=y.
##
## =head1 AUTHORS
##
## Michael Wang, 2001-2002, <xw73@columbia.edu>.
##
## Author Unknow, prior to 2001.