all-project/用户信息同步统一身份认证/verify_sync.sh

#!/bin/bash
# ===========================================
# 统一身份认证同步结果校验脚本
# ===========================================

set -euo pipefail
export LANG=C
export LC_ALL=C

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
CONFIG_FILE="${SCRIPT_DIR}/data.conf"
[[ -f "$CONFIG_FILE" ]] || { echo "配置文件未找到"; exit 1; }
source "$CONFIG_FILE"

mysql_q() {
    python3 "${SCRIPT_DIR}/db_helper.py" \
        --host "$1" --port "$2" --user "$3" --password "$4" --database "$5" \
        scalar "$6" 2>/dev/null
}

oracle_counts() {
    if ! command -v sqlplus >/dev/null 2>&1; then
        echo "ERROR: 未找到 sqlplus，无法校验 Oracle 目标库" >&2
        return 1
    fi
    local conn="${TARGET_DB_USER}/${TARGET_DB_PASS}@${TARGET_ORACLE_CONNECT:-${TARGET_DB_HOST}:${TARGET_DB_PORT}/${TARGET_DB_NAME}}"
    sqlplus -S "$conn" <<'EOF'
SET PAGESIZE 100
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SELECT 'GXXX_YXSDWJBSJZL=' || COUNT(*) FROM GXXX_YXSDWJBSJZL;
SELECT 'GXJG_JZGJCSJZLB=' || COUNT(*) FROM GXJG_JZGJCSJZLB;
SELECT 'GXJG_GRTXFSZL=' || COUNT(*) FROM GXJG_GRTXFSZL;
SELECT 'GXXS_XSJBSJZL=' || COUNT(*) FROM GXXS_XSJBSJZL;
SELECT 'GXXS_GRTXZL=' || COUNT(*) FROM GXXS_GRTXZL;
EXIT;
EOF
}

echo "========== 统一身份认证同步校验 =========="
echo "时间: $(date '+%Y-%m-%d %H:%M:%S')"
echo ""

# 源库统计
SRC_DEPT=$(mysql_q "$SOURCE_DB_HOST" "$SOURCE_DB_PORT" "$SOURCE_DB_USER" "$SOURCE_DB_PASS" "$SOURCE_DB_NAME" "SELECT COUNT(*) FROM sys_dept;")
SRC_TEACHER=$(mysql_q "$SOURCE_DB_HOST" "$SOURCE_DB_PORT" "$SOURCE_DB_USER" "$SOURCE_DB_PASS" "$SOURCE_DB_NAME" "SELECT COUNT(*) FROM sys_user WHERE account_type='1';")
SRC_STUDENT=$(mysql_q "$SOURCE_DB_HOST" "$SOURCE_DB_PORT" "$SOURCE_DB_USER" "$SOURCE_DB_PASS" "$SOURCE_DB_NAME" "SELECT COUNT(*) FROM xg_student_msg WHERE deleted=0;")

echo "[源库] sys_dept:              ${SRC_DEPT}"
echo "[源库] sys_user(教职工):      ${SRC_TEACHER}"
echo "[源库] xg_student_msg(有效): ${SRC_STUDENT}"
echo ""

if [[ "$TARGET_DB_TYPE" == "mysql" ]]; then
    TGT_DEPT=$(mysql_q "$TARGET_DB_HOST" "$TARGET_DB_PORT" "$TARGET_DB_USER" "$TARGET_DB_PASS" "$TARGET_DB_NAME" "SELECT COUNT(*) FROM GXXX_YXSDWJBSJZL;")
    TGT_TEACHER=$(mysql_q "$TARGET_DB_HOST" "$TARGET_DB_PORT" "$TARGET_DB_USER" "$TARGET_DB_PASS" "$TARGET_DB_NAME" "SELECT COUNT(*) FROM GXJG_JZGJCSJZLB;")
    TGT_TEACHER_TX=$(mysql_q "$TARGET_DB_HOST" "$TARGET_DB_PORT" "$TARGET_DB_USER" "$TARGET_DB_PASS" "$TARGET_DB_NAME" "SELECT COUNT(*) FROM GXJG_GRTXFSZL;")
    TGT_STUDENT=$(mysql_q "$TARGET_DB_HOST" "$TARGET_DB_PORT" "$TARGET_DB_USER" "$TARGET_DB_PASS" "$TARGET_DB_NAME" "SELECT COUNT(*) FROM GXXS_XSJBSJZL;")
    TGT_STUDENT_TX=$(mysql_q "$TARGET_DB_HOST" "$TARGET_DB_PORT" "$TARGET_DB_USER" "$TARGET_DB_PASS" "$TARGET_DB_NAME" "SELECT COUNT(*) FROM GXXS_GRTXZL;")
    TGT_ERR=$(mysql_q "$TARGET_DB_HOST" "$TARGET_DB_PORT" "$TARGET_DB_USER" "$TARGET_DB_PASS" "$TARGET_DB_NAME" "SELECT COUNT(*) FROM ZFTAL_XTGL_ERRLOG;")

    echo "[目标库] GXXX_YXSDWJBSJZL:    ${TGT_DEPT}"
    echo "[目标库] GXJG_JZGJCSJZLB:     ${TGT_TEACHER}"
    echo "[目标库] GXJG_GRTXFSZL:       ${TGT_TEACHER_TX}"
    echo "[目标库] GXXS_XSJBSJZL:       ${TGT_STUDENT}"
    echo "[目标库] GXXS_GRTXZL:         ${TGT_STUDENT_TX}"
    echo "[目标库] ZFTAL_XTGL_ERRLOG:   ${TGT_ERR}"
    echo ""

    echo "---------- 抽样数据对比 ----------"
    python3 "${SCRIPT_DIR}/db_helper.py" \
        --host "$TARGET_DB_HOST" --port "$TARGET_DB_PORT" \
        --user "$TARGET_DB_USER" --password "$TARGET_DB_PASS" --database "$TARGET_DB_NAME" \
        query "SELECT gh, xm, dwh, scbz FROM GXJG_JZGJCSJZLB LIMIT 3" 2>/dev/null
    python3 "${SCRIPT_DIR}/db_helper.py" \
        --host "$TARGET_DB_HOST" --port "$TARGET_DB_PORT" \
        --user "$TARGET_DB_USER" --password "$TARGET_DB_PASS" --database "$TARGET_DB_NAME" \
        query "SELECT xh, xm, dwh, szbh, scbz FROM GXXS_XSJBSJZL LIMIT 3" 2>/dev/null

    echo ""
    PASS=true
    [[ "$TGT_DEPT" -ge 1 ]] || { echo "WARN: 部门同步数量为0"; PASS=false; }
    [[ "$TGT_TEACHER" -ge 1 ]] || { echo "WARN: 教职工同步数量为0"; PASS=false; }
    [[ "$TGT_STUDENT" -ge 1 ]] || { echo "WARN: 学生同步数量为0"; PASS=false; }
    [[ "$TGT_TEACHER" == "$TGT_TEACHER_TX" ]] || echo "WARN: 教职工基础表与通讯表数量不一致"
    [[ "$TGT_STUDENT" == "$TGT_STUDENT_TX" ]] || echo "WARN: 学生基础表与通讯表数量不一致"

    if [[ "$PASS" == true ]]; then
        echo "校验结果: 通过（基础数据已写入）"
        exit 0
    else
        echo "校验结果: 未通过"
        exit 1
    fi
elif [[ "$TARGET_DB_TYPE" == "oracle" ]]; then
    echo "[目标库 Oracle] 记录数:"
    ORACLE_OUT=$(oracle_counts) || { echo "校验结果: 未通过（sqlplus 失败）"; exit 1; }
    echo "$ORACLE_OUT"

    TGT_DEPT=$(echo "$ORACLE_OUT" | sed -n 's/.*GXXX_YXSDWJBSJZL=\([0-9][0-9]*\).*/\1/p' | head -1)
    TGT_TEACHER=$(echo "$ORACLE_OUT" | sed -n 's/.*GXJG_JZGJCSJZLB=\([0-9][0-9]*\).*/\1/p' | head -1)
    TGT_TEACHER_TX=$(echo "$ORACLE_OUT" | sed -n 's/.*GXJG_GRTXFSZL=\([0-9][0-9]*\).*/\1/p' | head -1)
    TGT_STUDENT=$(echo "$ORACLE_OUT" | sed -n 's/.*GXXS_XSJBSJZL=\([0-9][0-9]*\).*/\1/p' | head -1)
    TGT_STUDENT_TX=$(echo "$ORACLE_OUT" | sed -n 's/.*GXXS_GRTXZL=\([0-9][0-9]*\).*/\1/p' | head -1)

    echo ""
    echo "---------- 与源库对照 ----------"
    echo "部门:   源=${SRC_DEPT}  目标=${TGT_DEPT:-0}"
    echo "教职工: 源=${SRC_TEACHER}  目标=${TGT_TEACHER:-0} / 通讯=${TGT_TEACHER_TX:-0}"
    echo "学生:   源=${SRC_STUDENT} 目标=${TGT_STUDENT:-0} / 通讯=${TGT_STUDENT_TX:-0}"
    echo ""

    PASS=true
    [[ "${TGT_DEPT:-0}" -ge 1 ]] || { echo "WARN: 部门同步数量为0"; PASS=false; }
    [[ "${TGT_TEACHER:-0}" -ge 1 ]] || { echo "WARN: 教职工同步数量为0"; PASS=false; }
    [[ "${TGT_STUDENT:-0}" -ge 1 ]] || { echo "WARN: 学生同步数量为0"; PASS=false; }

    if [[ "$PASS" == true ]]; then
        echo "校验结果: 通过（Oracle 目标库已有数据）"
        exit 0
    else
        echo "校验结果: 未通过"
        exit 1
    fi
else
    echo "不支持的目标库类型: $TARGET_DB_TYPE"
    exit 1
fi