文件导入到数据库——复杂版

需求

• 有若干个模块setup，smsdelivery，release，connect，smssubmit，callin，finish；每个模块对应一个目录，在目录中有01到31个子目录(文件夹)，每个子目录中是需要导入到数据库中去的数据文件(txt文件)
• 已经导入的文件不再导

代码实现

#!/bin/bash
source /etc/profile
source ~/.bash_profile

export NLS_LANG=American_America.UTF8
###########################################################################
# SCRIPT: axg_logfile_load.sh
# AUTHOR: lxm
# DATE  : 2018-2-8
# REV   : 1.0.A
#               (Valid are A, B, D, T and P)
#         (For Alpha, Beta, Dev, Test and Production)
#
# PLATFORM: Linux
# PURPOSE:
# REV LIST:
# sh axg_logfile_load.sh aslog &
##### lxm modified at 2017-10-30#####
# running command now is as follows:  
# sh axg_aslogfile_load.sh sa A &


#
###########################################################################
#programing path
prog_path=/utxt/softnew/scripts/axg_jk/as

#The name we show while starting
progname=axg_aslogfile_load

## The log file after starting
logfile=$prog_path/proglog/$progname.log

## The spid after starting
pid=$$

#work type(sa)
work_type=$1
platform=`echo $2 | tr '[:lower:]' '[:upper:]'`
#source constant "$platform"
prefix_work_path="/uloc/axg/"${work_type}
#file type(sub,unsub,cdrpush,callreleasepush)
work_path="${prefix_work_path}/setup/ ${prefix_work_path}/smsdelivery/ ${prefix_work_path}/release/ ${prefix_work_path}/connect/ ${prefix_work_path}/smssubmit/ ${prefix_work_path}/callin/ ${prefix_work_path}/finish/ "

# only for test
#work_path=${prefix_work_path}/callcdr/

#oracle database service
db_srv="dfjr_new_axgmt_jk/czty_dfjr_new_axgmt_jk@AXG_JK"
#rm one day data
rm_day=`date +%d -d "-8 day"`


##################################


#Failed loadding files Path
error_path=${prefix_work_path}/error/

## The lock file path name.
lockfile=$prog_path/proglog/$progname".lock"

#stopping flag
flag=0
#suffix_flag=$1
row_num=50

#backup path(simfilter[0-9]
backup_path="${prefix_work_path}/backup/"
rm_path=$backup_path"$rm_day"

mkdir -p $prog_path/proglog/
mkdir -p ${backup_path}setup/{01..31}
mkdir -p ${backup_path}smsdelivery/{01..31}
mkdir -p ${backup_path}release/{01..31}
mkdir -p ${backup_path}connect/{01..31}
mkdir -p ${backup_path}smssubmit/{01..31}
mkdir -p ${backup_path}callin/{01..31}
mkdir -p ${backup_path}finish/{01..31}
mkdir -p ${error_path}

#sleep time in second
sleep_time=1

echo ${work_path} >>$logfile

#sqlldr file type
log_f="$prog_path"/proglog/dfjr_msg_load${pid}.log
bad_f="$prog_path"/proglog/dfjr_msg_load${pid}.bad
lv_rows=10000
lv_bindsize=8192000
lv_readsize=8192000

#declare function 
Create_Sqlldr_Ctl(){
file_type=$1
ctl_file=""
case $file_type in
    20)
    file_type="setup"
      if [ ! -f t_axg_${file_type}.ctl ]; then
            cat>t_axg_${file_type}.ctl <<EOF
           load data            
           infile *
           append
           into table t_axg_setup
           FIELDS TERMINATED BY ','
           TRAILING NULLCOLS
           (     
TIME          TIMESTAMP"yyyyMMddHH24MISSFF3",
CALLID        ,
CALLERNUMBER  ,
CALLEDNUMBER  ,
SESSIONID     ,
RESULT        ,
CODE          ,
MESSAGE       ,
DELAY         ,
SOURCE constant "$platform"        ,
PRT1TYPE      ,
CALLTYPE  
)
EOF
      fi
      ctl_file=t_axg_${file_type}.ctl
      ;;

   21)
      file_type="smsdelivery"
      if [ ! -f t_axg_${file_type}.ctl ]; then
            cat>t_axg_${file_type}.ctl <<EOF
           load data
           infile *
           append
           into table t_axg_smsdelivery
           FIELDS TERMINATED BY ','
           TRAILING NULLCOLS
           (
TIME         TIMESTAMP"yyyyMMddHH24MISSFF3",
CALLINGDN     ,
CALLEDDN      ,
MSGCODING       ,
MSGCONTENT char(256),
CODE        , 
MESSAGE       , 
DELAY 
         )
EOF
      fi
      ctl_file=t_axg_${file_type}.ctl
      ;;

    22)
      file_type="release"
      if [ ! -f t_axg_${file_type}.ctl ]; then
            cat>t_axg_${file_type}.ctl <<EOF
           load data
           infile *
           append
           into table t_axg_release
           FIELDS TERMINATED BY ','
           TRAILING NULLCOLS
           (
TIME   TIMESTAMP"yyyyMMddHH24MISSFF3",
CALLID        ,
SESSIONID     ,
RELEASECAUSE  ,
RELEASEDIRECT ,
STARTTIME     ,
ALERTINGTIME  ,
CONNECTINGTIME,
RELEASETIME   ,
CODE          ,
MESSAGE       ,
DELAY         ,
SOURCE constant "$platform"      ,
INVOKEID      ,
PLAN      ,
REASON
         )
EOF
      fi
      ctl_file=t_axg_${file_type}.ctl
      ;;

        40)
      file_type="connect"
      if [ ! -f t_axg_${file_type}.ctl ]; then
            cat>t_axg_${file_type}.ctl <<EOF
           load data
           infile *
           append
           into table t_axg_connect
           FIELDS TERMINATED BY ','
           TRAILING NULLCOLS
           (
TIME  TIMESTAMP"yyyyMMddHH24MISSFF3",
CALLID ,
SESSIONID ,
DISPLAYDN,
CALLEDDN,
CODEC ,
CODE ,
MESSAGE,
DELAY,
SOURCE constant "$platform"      ,
INVOKEID,
CALLHANDLE
         )
EOF
      fi
      ctl_file=t_axg_${file_type}.ctl
      ;;


  41)
      file_type="smssubmit"
      if [ ! -f t_axg_${file_type}.ctl ]; then
            cat>t_axg_${file_type}.ctl <<EOF
           load data
           infile *
           append
           into table t_axg_smssubmit
           FIELDS TERMINATED BY ','
           TRAILING NULLCOLS
           (
TIME   TIMESTAMP"yyyyMMddHH24MISSFF3",
CALLINGDN,
CALLEDDN ,
MSGCODING ,
MSGCONTENT char(256),
WAY,
SMCGT,
CODE,
MESSAGE,
DELAY 
         )
EOF
      fi
      ctl_file=t_axg_${file_type}.ctl
      ;;


  91)
      file_type="callin"
      if [ ! -f t_axg_${file_type}.ctl ]; then
            cat>t_axg_${file_type}.ctl <<EOF
           load data
           infile *
           append
           into table t_axg_callin
           FIELDS TERMINATED BY ','
           TRAILING NULLCOLS
           (

TIME  TIMESTAMP"yyyyMMddHH24MISSFF3",
APPKEY,
TS     TIMESTAMP"yyyyMMddHH24MISSFF3",
SUBID,
APPNAME,
SERVICETYPE,
CALLTYPE ,
TELX   ,
FROMDN ,
TODN ,
CALLID,
CODE  ,
MESSAGE,
DELAY 
         )
EOF
      fi
      ctl_file=t_axg_${file_type}.ctl
      ;;


  92)
      file_type="finish"
      if [ ! -f t_axg_${file_type}.ctl ]; then
            cat>t_axg_${file_type}.ctl <<EOF
           load data
           infile *
           append
           into table t_axg_finish
           FIELDS TERMINATED BY ','
           TRAILING NULLCOLS
           (
TIME  TIMESTAMP"yyyyMMddHH24MISSFF3",
  APPKEY ,
  TS    TIMESTAMP"yyyyMMddHH24MISSFF3",
  SUBID,
  MSGDGT ,
  APPNAME ,
  APPTRANSID,
  SERVICETYPE,
  TELX   ,
  FROMDN ,
  TODN ,
  CALLSTART,
  CALLANSWER,
  CALLEND ,
  CALLID ,
  STATUS ,
  READURL ,
  CALLTIME ,
  CALLTYPE ,
  GROUPID ,
  CODE  ,
  MESSAGE,
  DELAY 
         )
EOF
      fi
      ctl_file=t_axg_${file_type}.ctl
      ;;
  *)
    ctl_file=""
    echo "File type ${file_type} is not exists sqlldr ctl file!" >>$logfile           
  esac
  echo "${ctl_file},${file_type}"
}

#####################################################################################
#
#
#####################################################################################
cd $prog_path
THE_DATE=`date +"%Y-%m-%d %H:%M:%S"`
echo "[$THE_DATE $progname]:Start $progname pid:$pid......" >>  $logfile
#lockfile is not exists,prog exit
lock_flag=`find $prog_path -name $progname.lock|wc -l`
if test $lock_flag -eq "0"; then 
    touch $lockfile
else 
    echo "$lock_flag" >>$logfile
    THE_DATE=`date +"%Y-%m-%d %H:%M:%S"`
  echo "[$THE_DATE $convert_name Abnormal exit]:end $progname pid:$pid......" >>  $logfile
  exit 1
fi


#####################################################################################
#
#
#####################################################################################
while lock_flag=1
do
  lock_flag=`find $prog_path -name $progname.lock|wc -l`
  if test $lock_flag = "0"; then
    echo "$lock_flag $infile" >>$logfile
    THE_DATE=`date +"%Y-%m-%d %H:%M:%S"`
        echo "[$THE_DATE $convert_name Normal exit]:end $progname pid:$pid......" >>  $logfile
    exit 1
  fi
  for par_dir in $work_path
    do
      pre_day=`date +%d -d "-1 day"`
      day=`date +"%d"`
      for infile in `find ${par_dir}${pre_day} ${par_dir}${day} -name \*.txt |sort -t_ -k3`
      do
        infile_full_path=$infile
        infile=`basename $infile`
        first_letter=`echo $infile |cut -d_ -f2 |cut -c2-3`
        file_time=`echo $infile | cut -d_ -f3 | cut -c2-9`
        file_list=$prog_path/"axg_msg_"${file_time}_${first_letter}".list"
        [ -f $file_list ]
        if [ $? -eq 1 ]; then
          >$file_list
          rm_day=`date +%d -d "-8 day"`
          find ${backup_path}*/${rm_day} -name \*.txt -exec rm -f {} \; 
          echo "find ${backup_path}*/${rm_day} -name \*.txt -exec rm -f {} \;" >>$logfile
          rm_list=`date +%Y%m%d -d "-8 day"`
          rm -f "axg_msg_"${rm_list}"*.list"
        fi
        #if infile has loaded
        loaded_flag=`grep $infile $file_list | wc -l`
        if [ "$loaded_flag" -gt "0" ]; then
        #  #echo "The file name:$infile has loaded into the table!" >>$logfile
          continue
        fi
        if [ -s $infile_full_path ]; then
            file_type_ctl_file=`Create_Sqlldr_Ctl ${first_letter}`
            echo ${file_type_ctl_file}
            ctl_file=`echo $file_type_ctl_file | cut -d, -f1`
            file_type=`echo $file_type_ctl_file | cut -d, -f2`
            if [ -f ${ctl_file} ]; then
              dos2unix $infile_full_path >/dev/null 2>&1
              if (('60'==${first_letter}));then
                sqlldr userid="$db_srv_for_callcdr" control="$prog_path"/${ctl_file} data=$infile_full_path log="$log_file_for_callcdr" bad="$bad_file_for_callcdr" rows="$lv_rows" bindsize="$lv_bindsize" readsize="$lv_readsize" direct=n >/dev/null 2>&1              
              else
                sqlldr userid="$db_srv" control="$prog_path"/${ctl_file} data=$infile_full_path log="$log_f" bad="$bad_f" rows="$lv_rows" bindsize="$lv_bindsize" readsize="$lv_readsize" direct=n >/dev/null 2>&1              
              fi
              if [ $? -eq 0 ]; then                       
                echo $infile >>$file_list
                backup_day=`date -d ${file_time} +"%d"`
                cur_backup_path=${backup_path}${file_type}/${backup_day}
                mv -f ${infile_full_path} ${cur_backup_path}
              else
                cat $log_f >>$logfile
                mv -f ${infile_full_path} ${error_path}
              fi
            fi
        fi
        done
    done
     sleep "$sleep_time"
done