记录一下自己写的小工具：shell 调度 SQL 批处理，递归查找调度路径

写这段代码的背景：

1：老项目，因为一些原因没有使用一些流行的DAG调度平台，而是通过使用Linux定时任务调度SHELL的方式来执行每日的批处理调度

2：整个的批处理调度一共有三个入口SHELL，也就是直接和Linux 定时任务交互的SHELL，big_main.sh,run_main.sh,run_ten.sh。而这三个脚本也是我的递归方法的出口

3：一共有两个目录，一个目录专门放所有的shell脚本，一个目录专门放所有的SQL脚本

４：SHELL和SHELL之间互相调用，没有清晰的分层，多少层都有可能。但是最终一定是以调用具体SQL 脚本作为结束

 

下面是我的脚本代码

#!/bin/bash

#目前所有的开发默认的是脚本放在 /home/jtaom/test/script/ 目录中，
#如果向做自己的定制，直接把目录替换下就好


shell_path='/data/dcos/big/shell'
sql_path='/data/dcos/big/sql'
table_shell_call_log="/home/jtaom/test/script/table_shell_call.log"
table_have_caller="/home/jtaom/test/script/table_have_caller.log"
debug_log="/home/jtaom/test/script/debug.log"

echo > ${debug_log}

function extract_all_caller()
{
    local folder=$1
    local key=$2
    local flag=$3

    #进入对应目录
    cd $folder
    if [ $? -ne 0 ]
    then  
        echo "can not access sql_path"
        exit 1
    fi

    #切换for循环的分割符
    local OLDIFS=$IFS
    local IFS=$'\n'
    #对表名进行精确匹配，防止相似标名匹配
    local related_script=""
    local lines=`grep -rinw ${key}`
    echo "-----------------------------------------" >> ${debug_log} 2>&1
    echo "！！！ extract_all_caller current grepd lines:[${lines}]" >> ${debug_log} 2>&1
    echo "-----------------------------------------" >> ${debug_log} 2>&1 
    for line in ${lines}
    do

        ####对于log文件，直接放弃
        local script_temp=`echo $line | cut -d ":" -f 1`
        #当前文件为logw文件，放弃
        if [[ ${script_temp} = *\.log ]]
        then  
            echo "！！！ extract_all_caller 当前文件为log文件，放弃当前行 ${script_temp}" >> ${debug_log} 2>&1
            continue
        fi 

        if [[ ${flag} == "shell" ]] && [[ ${script_temp} = *\.sql ]]
        then  
            echo "！！！ extract_all_caller 当前文件为sql文件,需要为shell文件，放弃当前行 ${script_temp}" >> ${debug_log} 2>&1
            continue
        fi 


        ####对于被注解掉的行，直接放弃
        #如果包含的结果中，有--则取消这行
        local query_piece_temp=`echo $line | cut -d ":" -f 3 | python -c "s=raw_input();print(s.strip())"`

        if [[ ${query_piece_temp##*([[:blank:]])} == --* ]]
        then  
            echo "！！！ extract_all_caller 注解掉的SQL代码，放弃当前行 ${query_piece_temp}" >> ${debug_log} 2>&1
            continue
        fi 

        #如果包含的结果中，有##则取消这行
        if [[ ${query_piece_temp##*([[:blank:]])} == \#* ]]
        then   
            echo "！！！ extract_all_caller 注解掉的SHELL代码，放弃当前行 ${query_piece_temp}" >> ${debug_log} 2>&1
            continue
        fi 

        #如果包含的结果中，有echo开头则取消这行
        if [[ ${query_piece_temp##*([[:blank:]])} == echo* ]]
        then  
            echo "！！！ extract_all_caller ECHO开头代码，放弃当前行 ${query_piece_temp}" >> ${debug_log} 2>&1
            continue
        fi 

        
        ###去除掉所有行，留下来的都是有效行
        echo "extract_all_caller 有效的当前行 ${query_piece_temp}" >> ${debug_log} 2>&1

       
        ### 把找到的调用脚本拼接起来，并且去重
        local contain_res=$(echo $related_script | grep -w "${script_temp}")
        if [[ "$contain_res" != "" ]]
        then
            #已经包含了，则忽略
            continue
        else
            related_script="$related_script $script_temp"
        fi
       

    done
    local IFS=$OLDIFS

    echo $related_script
}

function extract_shell_call_path()
{
    local path=$1
    local key=$2
    local call_line=$3
    local flag='shell'
    local related_shell_script=`extract_all_caller $path $key ${flag}`

    # echo "related_shell_script ${related_shell_script}"

    if [ -z "$related_shell_script" ]; then
        # echo "!!!!!bad call line, line: ${call_line}"
        echo "！！！ extract_shell_call_path 调用路径结束，脚本：${related_shell_script}" >> ${debug_log} 2>&1
        return 0
    fi


    
    for script_name in ${related_shell_script}
    do
        if [ -z "$script_name" ]
        then
            echo "script_name is empty"

            continue
        fi


        if [ "big_main.sh" == "$script_name" ]
        then
            local call_line="${call_line}:big_main.sh"
            echo "${call_line}"
            echo "！！！ extract_shell_call_path 调用路径成功，${call_line}" >> ${debug_log} 2>&1
            break
        fi
        if [ "run_main.sh" == "$script_name" ]
        then
            local call_line="${call_line}:run_main.sh"
            echo "${call_line}"
            echo "！！！ extract_shell_call_path 调用路径成功，${call_line}" >> ${debug_log} 2>&1
            break
        fi
        if [ "run_ten.sh" == "$script_name" ]
        then
            local call_line="${call_line}:run_ten.sh"
            echo "${call_line}"
            echo "！！！ extract_shell_call_path 调用路径成功，${call_line}" >> ${debug_log} 2>&1
            break
        fi
        
        local call_line="${call_line}:$script_name"
        
        echo "！！！ extract_shell_call_path 调用路径继续查找中，${call_line}" >> ${debug_log} 2>&1
        extract_shell_call_path $path $script_name $call_line
    done

}


################################### 代码开始执行 ########################

echo '' > $table_shell_call_log 2>&1
echo '' > $table_have_caller 2>&1

# 表a2_source.pty_indv_20200226 对应的SQL脚本有: 
# 表a2_source.rel_pty_con_pi_nonauto_20200226 对应的SQL脚本有: 
# 表a2_source.gxb_yu_report_0 对应的SQL脚本有: gxb_sp_in_gxb_bus_report.sql
# 在此把所有需要查询的表放进来
# db_name='a2_source'
# table_names='a2_cpic_boss_1 a2_cpic_boss_all a2_cpic_boss_all_1 a2_cpic_boss_all_2 a2_cpic_boss_all_3 a2_cpic_boss_all_4 a2_cpic_boss_all_5 a2_cpic_boss_big a2_cpic_boss_test a2_cpic_boss_zl a2_cpic_gold_coin a2_cpic_gold_coin_cl a2_cpic_gold_coin_policy_no a2_cpic_gold_coin_step_0918 a2_cpic_gold_coin_step_0918_1 a2_cpic_gold_coin_step_1 a2_cpic_gold_coin_step_2 a2_cpic_gold_coin_step_3 a2_cpic_gold_coin_third_step_1 a2_cpic_gold_coin_third_step_1_0918_1 a2_cpic_gold_coin_third_tbyh a2_cpic_gold_coin_third_tbyh_1 a2_cpic_gold_coin_third_tbyh_2 a2_cpic_gold_coin_third_tbyh_3 a2_cpic_gold_policy all_month all_month_ql all_month_week all_month_week_ql auto_detail auto_detail_20200907 auto_detail_backup auto_detail_cx03 auto_detail_cx03_1 auto_detail_out auto_detail_out_date auto_detail_test auto_insurance auto_insurance_01 auto_insurance_01_all auto_insurance_all_bak auto_insurance_allot_cust_01 auto_insurance_allot_cust_02 auto_insurance_allot_cust_03 auto_insurance_allot_cust_04 auto_insurance_allot_cust_06 auto_insurance_allot_cust_1 auto_insurance_extend_01 auto_insurance_partition_01 auto_insurance_partition_02 auto_insurance_partition_03 auto_insurance_partition_03_724 auto_insurance_partition_04 auto_insurance_partition_0530 auto_insurance_partition_066 auto_insurance_partition_077 auto_insurance_partition_088 auto_insurance_partition_089 auto_insurance_partition_all_0604 auto_insurance_partition_bak auto_insurance_partition_bak_06 auto_insurance_partition_date auto_insurance_partition_from_mysql auto_insurance_partition_pre auto_insurance_partition_pre_001 auto_insurance_partition_pre_002 auto_insurance_partition_pre_01 auto_insurance_partition_pre_02 auto_insurance_partition_pre_03 auto_insurance_partition_pre_04 auto_insurance_partition_pre_05 auto_insurance_partition_pre_06 auto_insurance_partition_pre_shzmq auto_insurance_partition_pre_shzmq_01 auto_insurance_partition_pre_shzmq_02 auto_insurance_partition_to_oracle auto_insurance_target1 auto_insurance_xiu auto_insurance_xiu_201910_01 auto_insurance_xiu_201910_02 auto_insurance_xiu_201910_03 auto_insurance_xiu_201911_01 auto_insurance_xiu_201911_02 auto_insurance_xiu_201911_03 auto_insurance_xiu_201912_01 auto_insurance_xiu_201912_02 auto_insurance_xiu_201912_03 auto_insurance_xiu_202001_01 auto_insurance_xiu_202001_02 auto_insurance_xiu_202001_03 auto_insurance_xiu_202002_01 auto_insurance_xiu_202002_02 auto_insurance_xiu_202002_03 auto_insurance_xiu_202003_01 auto_insurance_xiu_202003_02 auto_insurance_xiu_202003_03 auto_insurance_xiu_202004_01 auto_insurance_xiu_202004_02 auto_insurance_xiu_202004_03 auto_insurance_xiu_pre auto_insurance_xiu_pre_01 auto_premium_t b2_01_p_1 b2_01_p_2 b2_01_p_3 b2_01_p_4 caf_achievements_2_tmp caf_car_detailed_1 caf_car_detailed_10 caf_car_detailed_11 caf_car_detailed_12 caf_car_detailed_13 caf_car_detailed_14 caf_car_detailed_15 caf_car_detailed_16 caf_car_detailed_2 caf_car_detailed_3 caf_car_detailed_4 caf_car_detailed_5 caf_car_detailed_6 caf_car_detailed_7 caf_car_detailed_8 caf_car_detailed_9 caf_car_enjoyment_detail_1 caf_car_enjoyment_detail_2 caf_car_enjoyment_detail_3 caf_car_enjoyment_detail_4 caf_car_enjoyment_detail_5 caf_car_enjoyment_detail_6 caf_car_enjoyment_detail_7 caf_car_enjoyment_detail_mf caf_car_long_detailed_1 caf_car_long_detailed_1_1 caf_cardimension_table caf_cardimension_table_1 caf_cardimension_table_10 caf_cardimension_table_11 caf_cardimension_table_12 caf_cardimension_table_2 caf_cardimension_table_3 caf_cardimension_table_4 caf_cardimension_table_5 caf_cardimension_table_6 caf_cardimension_table_7 caf_cardimension_table_7_1 caf_cardimension_table_7_2 caf_cardimension_table_8 caf_cardimension_table_9 caf_cardimension_table_tmp caf_claim_insurance_01 caf_claim_insurance_02 caf_claim_insurance_03 caf_claim_insurance_04 caf_claim_insurance_05 caf_claim_insurance_06 caf_claim_insurance_07 caf_claim_insurance_08 caf_claim_insurance_cust_01 caf_claim_insurance_cust_02 caf_claim_insurance_cust_03 caf_claim_insurance_cust_04 caf_claim_insurance_cust_05 caf_claim_insurance_cust_06 caf_claim_insurance_cust_07 caf_claim_insurance_cust_1_1 caf_claim_insurance_cust_1_2 caf_claim_insurance_cust_1_3 caf_claim_insurance_cust_1_4 caf_claim_insurance_cust_1_5 caf_commissioner_cx_01 caf_commissioner_cx_02 caf_commissioner_cx_03 caf_commissioner_cx_04 caf_commissioner_cx_05 caf_commissioner_cx_05_1 caf_commissioner_cx_06 caf_commissioner_cx_07 caf_commissioner_cx_08 caf_commissioner_cx_end caf_commissioner_cx_qd_01 caf_commissioner_date caf_commissioner_jj caf_commissioner_jj_001 caf_commissioner_jj_002 caf_commissioner_jj_01 caf_commissioner_jj_02 caf_commissioner_jj_03 caf_commissioner_jj_cd caf_commissioner_track_01 caf_commissioner_track_02 caf_commissioner_track_03 caf_commissioner_track_04 caf_commissioner_track_05 caf_commissioner_track_06 caf_commissioner_track_07 caf_commissioner_track_08 caf_commissioner_track_09 caf_commissioner_track_10 caf_commissioner_track_11 caf_commissioner_track_12 caf_commissioner_track_13 caf_commissioner_track_14 caf_commissioner_track_date caf_commissioner_track_end caf_commissioner_xb caf_customer_info_01 caf_customer_info_02 caf_customer_info_03 caf_customer_info_04 caf_customer_info_05 caf_customer_info_06 caf_customer_info_07 caf_customer_info_08 caf_customer_info_tb_01 caf_cx_dim_all_day caf_cx_dim_all_day_no_day caf_cx_healthinsur_csfb caf_cx_healthinsur_daily_table_01 caf_cx_healthinsur_inter_org caf_cx_healthinsur_txlf caf_cxbdtz caf_cxxb_kjgx_daily_0619 caf_cxxbjb_cjc_new_daily_1 caf_cxxbjb_cjc_new_daily_2 caf_cxxbjb_cjc_new_daily_3 caf_cxxbjb_cjc_new_daily_4 caf_cxxbjb_cjc_new_daily_5 caf_date_table_1 caf_date_table_2519 caf_date_table_2519_test caf_emp_cx_detail caf_fc_detailed_1 caf_fc_policy_list_1 caf_fc_policy_list_2 caf_fc_policy_list_3 caf_fc_policy_list_4 caf_fc_policy_list_5 caf_fc_policy_list_6 caf_fc_policy_list_7 caf_fcjb_detail_1 caf_fcjb_detail_2 caf_fcjb_detail_3 caf_fcjb_detail_4 caf_fcjb_detail_5 caf_orphan_commonsuccessors_01 caf_orphan_commonsuccessors_02 caf_orphan_commonsuccessors_03 caf_orphan_distribution_01 caf_orphan_distribution_02 caf_orphan_distribution_03 caf_orphan_distribution_04 caf_orphan_distribution_05 caf_orphan_distribution_06 caf_orphan_distribution_07 caf_orphan_distribution_08 caf_orphan_distribution_09 caf_orphan_distribution_10 caf_orphan_distribution_10_tmp caf_orphan_distribution_11 caf_orphan_distribution_12 caf_orphan_distribution_13 caf_orphan_distribution_14 caf_orphan_distribution_15 caf_orphan_distribution_16 caf_orphan_distribution_17 caf_orphan_distribution_18 caf_orphan_distribution_19 caf_orphan_distribution_20 caf_orphan_distribution_21 caf_orphan_distribution_22 caf_orphan_distribution_23 caf_orphan_distribution_24 caf_orphan_distribution_25 caf_orphan_distribution_26 caf_orphan_distribution_27 caf_orphan_distribution_28 caf_orphan_distribution_29 caf_orphan_distribution_30 caf_orphan_distribution_31 caf_orphan_distribution_32 caf_orphan_distribution_33 caf_orphan_distribution_bak caf_orphan_distribution_tmp_01 caf_orphan_distribution_tmp_02 caf_orphan_distribution_tmp_03 caf_orphan_distribution_tmp_04 caf_orphan_distribution_tmp_05 caf_orphan_order_1 caf_orphan_order_all caf_orphan_order_premium caf_orphan_order_premium_1 caf_orphan_order_premium_1_0 caf_orphan_order_premium_1_0_0 caf_orphan_order_premium_1_1 caf_orphan_order_premium_1_2 caf_orphan_order_premium_1_3 caf_orphan_order_premium_2 caf_orphan_order_premium_2_0 caf_orphan_order_premium_2_0_0 caf_orphan_order_premium_2_1 caf_orphan_order_premium_2_2 caf_orphan_order_update_1_1 caf_orphan_order_update_1_2 caf_orphan_order_update_2_1 caf_orphan_order_update_2_2 caf_orphan_order_update_3_1 caf_orphan_order_update_3_2 caf_orphan_order_update_4_1 caf_orphan_order_update_4_2 caf_orphan_order_update_5_1 caf_orphan_order_update_5_2 caf_orphan_order_update_6_1 caf_orphan_order_update_6_2 caf_orphan_order_update_7_1 caf_orphan_order_update_7_2 caf_orphan_qqrate caf_orphan_qqrate_1_1 caf_orphan_qqrate_1_2 caf_orphan_qqrate_1_3 caf_orphan_qqrate_1_4 caf_orphan_qqrate_1_5 caf_orphan_qqrate_1_6 caf_orphan_qqrate_sum_tmp caf_orphan_reassignment_01 caf_orphan_reassignment_02 caf_p_customer_list_1 caf_p_customer_list_2 caf_p_customer_list_all caf_policy_xb_report_1 caf_policy_xb_report_10 caf_policy_xb_report_11 caf_policy_xb_report_2 caf_policy_xb_report_3 caf_policy_xb_report_4 caf_policy_x