shell脚本：对开发入库的sql脚本进行检查

#！/bin/bash
export LANG=en_US.UTF-8
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
source /etc/profile
source /root/.bash_profile

HOME=$(cd $(dirname $0); pwd)
DOC_HOME=${HOME}/releasedoc
DOC_SVN_PATH=http://192.168.xx.xx/bushiahoren/bushiahoren/haoren/doc # 脚本SVN库地址
log_path=${HOME}/tmplog
xls_path=${HOME}/results
keyword=( truncate DELETE "insert.*tables" STORAGE )
DMUSERDIR='ECMS SPG_ATM SPG_SC SPG_TOP XXQA'
curTime=`date +%Y%m%d-%H%M`
xlsname=${xls_path}/inspect_${curTime}.xls
filenames='checkdocdir.txt blankspace.txt checkfiletype.txt enderror.txt partition.txt format.txt catalogue.txt keyword.txt commit.txt sizecomparison.txt'

# 检查目录与拉取代码
function INIT() {
  [ -d ${DOC_HOME} ] || mkdir -p ${DOC_HOME}
  if [ -d ${DOC_HOME}/脚本和操作票 ]; then
    svn update ${DOC_HOME} --username=qinwengang --password=TECHnology@522
  else
    cd ${DOC_HOME}
    svn checkout ${DOC_SVN_PATH} ${DOC_HOME} --username=qinwengang --password=TECHnology@522
  fi

  # 获取未增量到现场的所有文件名
  all_sql_file=$(find ${DOC_HOME}/脚本和操作票/业务脚本 -type f -iname "*.sql" |grep -Ev "已发现场| ")  # 不包含空格文件名
  all_doc_file=$(find ${DOC_HOME}/脚本和操作票/操作票 -type f -iname "*.do" |grep -Ev "已发现场| ")  # 不包含空格文件名

  # 清理已存在文件内容
  for i in $filenames; do
    [ -f ${log_path}/$i ] && echo > ${log_path}/$i
  done

  [ -f ${log_path}/tmpall.txt ] && echo > ${log_path}/tmpall.txt  # 用于xls文件制作
  [ -f ${log_path}/all.txt ] && echo > ${log_path}/all.txt  # 用于xls文件制作
}


# 检查脚本目录是否有新增
function CHECKDOCDIR() {
  for i in $(ls ${DOC_HOME}/脚本和操作票/业务脚本); do
      echo  ${DMUSERDIR} | grep  ${i} >/dev/null 2>&1
      if [ $? != 0 ]; then
          echo "$curTime,新创建得脚本目录请确认,${DOC_HOME}/脚本和操作票/业务脚本/$i" >> ${log_path}/checkdocdir.txt
      fi
  done
}

# 检查文件或者目录是否有空格
function BLANKSPACE(){
  sql_file=$(find ${DOC_HOME} -type f -iname "*.sql" |grep -v '已发现场' | grep -E " ")  # 空格文件sql
  info='包含空格'
  if [ ! -e $sql_file ]; then
    for i in $sql_file; do
      echo "$curTime,$info,$1" >> ${log_path}/blankspace.txt${log_path}/blankspace.txt
    done
  fi

  doc_file=$(find ${DOC_HOME} -type f -iname "*.do*" |grep -v '已发现场' | grep -E " ")  # 空格文件doc
  if [ ! -e $doc_file ]; then
    for i in $doc_file; do
       echo "$curTime,$info,$1" >> ${log_path}/blankspace.txt
    done
  fi
}

# 检查操作票目录下含.sql/业务脚本目录下含.doc
function CHECKFILETYPE(){
  errsql_file=$(find ${DOC_HOME}/脚本和操作票/操作票 -type f -iname "*.sql" |grep -Ev "已发现场| ")  # 不包含空格文件名
  info='文件目录存放错误'
  if [ ! -e $errsql_file ]; then
    for i in $errsql_file; do
      echo "$curTime,$info,$1" >> ${log_path}/checkfiletype.txt
    done
  fi

  errdoc_file=$(find ${DOC_HOME}/脚本和操作票/业务脚本 -type f -iname "*.do" |grep -Ev "已发现场| ")  # 不包含空格文件名
  if [ ! -e $errdoc_file ]; then
    for i in $errdoc_file; do
      echo "$curTime,$info,$1" >> ${log_path}/checkfiletype.txt
    done
  fi
}


# 检查脚本中是否包含PROCEDURE,p_exec,FUNCTION,declare中是否正确以/结尾
# 需要传入脚本名参数
function ENDERROR() {
  # declNum=`grep -vE '^%|^$' $1  |sed  's/^[ \t]*//g'|grep -Ei '^p_exec|PROCEDURE|FUNCTION|^declare|^begin|^end|^/'| grep -ivE 'if;|beginTime|endTime|loop;|/\*|\/\*'|wc -l`
  # 使用关键字+begin+end+'/'的格式进行判断，在出现循环begin+end的特殊情况会出现一些报错
  # 这里使用关键字总数为2的倍数，begin+end数也是2的倍数即可
  tabNum=`grep '\/' $1 |grep -vE '\/\*|\*\/|--'|grep -iEv '[a-z]|[0-9]'|grep -i $'\t'|wc -l`
  info1=""
  if [ ${tabNum} -gt 0 ]; then
    echo "$curTime,'/后带了tab键请删除tab键',$1" >> ${log_path}/enderror.txt
  fi

  declNum=`cat $1 |grep -vE '^%|^$'|sed  's/^[ \t]*//g'|grep -Ei '^p_exec|PROCEDURE|FUNCTION|^declarie'|wc -l`
  if [ $declNum -gt 0 ]; then
    # 该值大于0，表明脚本含有调用数据库函数p_exec,创建procedure或者function,有declare等
    # 这些都表明，脚本必须是正确的结构begin+end;+/
    # 三者排除特殊的字符外加起来行数是3的倍数,存在begin+end的这种组合，排除loop和end if之外
    # 则计数行为3/5/8/11/（只认定begin+end出现一次的情况）
    charNum=`cat $1 | grep -vEi '^%|^$|_end|end_|begin_|_begin|if|loop|beginTime|endTime|end\)|\/\*|\*\/|--' | sed  's/^[ \t]*//g' | grep -iE '^begin|^end.*;$|^/' | wc -l`
    #yushu=$(($charNum % 3))
    if [ $(($charNum % 3)) -gt 0 ]; then
      #if [ $(($charNum % 5)) -gt 0 ]; then
        #if [ $(($charNum % 8)) -gt 0 ]; then
          #if [ $(($charNum % 11)) -gt 0 ]; then
            info1='PROCEDURE或FUNCTION或declare结尾异常'
            echo "$curTime,$info1,$1" >> ${log_path}/enderror.txt
          #fi
        #fi
      #fi
    fi
  fi
}


# 检查创建表是否带分区，不带分区不符合规范,大表才做分区
# 检查创建表或者comment带了双引号，带双引号不符合规范
# 需要闯入脚本名参数
function PARTITION() {
  # 5.cpmment字段出现单引号情况
  comment_num=`cat $1 | sed  's/^[ \t]*//g'|grep -iEn 'COMMENT +ON'|awk '{print $1,$6}'|grep -iE '"'|wc -l`
  if [ $comment_num -gt 0 ]; then
      for i in `cat $1 | sed  's/^[ \t]*//g'|grep -iEn 'COMMENT +ON'|awk '{print $1,$6}'|grep -iE '"'|awk '{print $1}'`
    do
      seq1_num=`echo $i|xargs echo -n|awk -F ':' '{print $1}'`
      echo "$curTime,第$seq_num行的注释使用引号错误,$1" >> ${log_path}/partition.txt
    done
  fi
}


#业务脚本-判断UTF-8格式
# 需要闯入脚本名参数
function FORMAT(){
  grep -P '[\p{Han}]' $1 > /dev/null
  if [[ $? == 0 ]];then
    file_charset=`file -i $1 |awk -F "=" '{print $NF}'`
    [[ ${file_charset} == "utf-8" ]] || echo  "$curTime,为非UTF-8格式:${file_charset},$1" >> ${log_path}/format.txt
  fi
}


#业务脚本-判断目录
# 需要闯入脚本名参数
function CATALOGUE(){
  script_name=`dirname $1`
  dir_name=`dirname $1|awk -F"/" '{print $NF}'`
  [[ ${dir_name} == *hpoms4j-Q* ]] || echo "$curTime,目录不规范,$1" >> ${log_path}/catalogue.txt
}


#业务脚本-判断关键字
# 需要闯入脚本名参数
function KEYWORDS(){
  for j in "${keyword[@]}"; do
    grep -i "$j" $1  >/dev/null
    if [ $? == 0 ]; then
      echo -e "$curTime,含敏感关键字:$j,$1" >> ${log_path}/keyword.txt
      [ $? == 0 ] || exit 111
    fi
  done
}


#业务脚本-commit结尾
# 需要传入脚本名参数
function COMMIT(){
  cat $1 |grep commit >/dev/null
  if [ $? == 0 ];then
    cat $1 |grep commit|grep \; >/dev/null
    if [ $? != 0 ];then
      echo -e "${curTime},commit结尾无分号,$1"  >> ${log_path}/commit.txt
      [ $? == 0 ] || exit 111
    fi
  fi
}


#业务脚本-D、T文件大小对比
# 需要闯入脚本名参数
function SIZE_COMPARISON(){
  ls $1 |grep  "D.sql" > /dev/null && ls $1/ |grep "T.sql" > /dev/null
  if [ $? == 0 ];then
    D_sql=`ls $1 |grep  "D.sql" |sed 's/-//g' |sed 's/_//g'  |awk -F "量" '{print $2}' |awk -F "】" '{print $1}'`
    T_sql=`ls $1 |grep  "T.sql" |sed 's/-//g' |sed 's/_//g'  |awk -F "量" '{print $2}' |awk -F "】" '{print $1}'`
    for i in `echo  "$D_sql"`
    do
      for j in `echo  "$T_sql"`
      do
        if [ $i -le $j ];then
          echo "${curTime},T类型时间戳大于或等于D类型,$1" >> ${log_path}/sizecomparison.txt
          [ $? == 0 ] || exit 111
        fi
      done
    done
  fi
}


# 将检查记录转换成xls格式记录
function RECORDXLS() {
  for i in $filenames; do
    if [ -f $log_path/$i ]; then
      if [ `cat $log_path/$i |grep -ivE '^$'|wc -l` -gt 0 ]; then
        cat $log_path/$i |grep -ivE '^$' >> $log_path/tmpall.txt
      fi
    fi
  done

  if [ -f $log_path/tmpall.txt ]; then
    if [ `cat $log_path/tmpall.txt |grep -ivE '^$'|wc -l` -gt 0 ]; then
      grep -iEv '^==|^&|^%|^$' $log_path/tmpall.txt |sed 's#'"${DOC_HOME}/"'##g' > $log_path/all.txt
      python3.10 $HOME/createexcel.py $log_path/all.txt  $xlsname
    fi
  fi

  find $xls_path -mtime +10 | grep -iE 'xls$' | xargs rm -f
}

#检查是否创建新的脚本目录
function main(){
  # 拉取doc&sql文件
  INIT
  # 检查目录新增
  CHECKDOCDIR
  #检查空格
  BLANKSPACE
  # 检查文件是否位于不该出现得目录
  CHECKFILETYPE

  # 0505
  [[ -e "$all_sql_file" ]] && "echo 没有.sql脚本文件,流程退出 && exit 111"
  for file in $all_sql_file; do
    ENDERROR $file  # 检查某些函数结尾符
    PARTITION $file  # 检查新建表是否分区,大表分区,小表不用分区
    FORMAT  $file  # 检查格式
    CATALOGUE $file  #检查目录 ？？？？？？？？？？？？？
    KEYWORDS $file  # 检查关键字
    COMMIT $file # 检查commit结尾
    SIZE_COMPARISON $file  #D、T文件大小对比
  done

  #打印检查信息
  #PRINT_FILE
  # 将检查记录转换成xls格式记录
  RECORDXLS
}

main