Files
eip4-platform/tools/local-devdb/build_devdb.sh
zhengwr 4519f6a0e5 F2-15: VIEW_HR_ATTENDLOG 补建 + build_devdb 校验步骤 + 补建报告
VIEW_HR_ATTENDLOG:
- 从 EIP-3.0 提取原始 CREATE VIEW DDL(1-VIEW_HR_ATTENDLOG.sql)
- 补加 createtime/createby/updatetime/updateby/departid/isactive/version
  六个 NULL 占位列,使平台 EntityController list 查询不报
  "column does not exist"(已验证: /api/entity/VIEW_HR_ATTENDLOG/list → 200)

build_devdb.sh:
- 末尾新增 F2-15 VIEW_ 实体 vs pg_views 校验警告步骤
- 缺失时输出 WARN 不 die,bash -n 语法检查通过

docs/P1/F2-15对象补建报告.md:
- 56 个 VIEW_ 缺失:补建 1 / 废弃 4 / 不可补 51
- 11 个非 VIEW_ 缺失表:全部不可补(无 CREATE TABLE DDL)
- 5 页面验证:VIEW_HR_ATTENDLOG 200 ✅,其余 4 个为预存虚拟视图 500

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
2026-06-12 05:42:58 +08:00

332 lines
13 KiB
Bash
Executable File
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
#!/usr/bin/env bash
# ============================================================================
# build_devdb.sh — 构建本地开发库 eip4dev
# 用途:从客户库 5435 按分层口径 dump/restore,完全脱离 5435 日常开发
# 使用:bash tools/local-devdb/build_devdb.sh [选项]
#
# 选项:
# --src-host HOST 源库主机 (默认: 192.168.20.68)
# --src-port PORT 源库端口 (默认: 5435)
# --src-user USER 源库用户 (默认: ceshi)
# --src-pass PASS 源库密码 (默认: ceshi)
# --src-db DB 源库名 (默认: postgres)
# --src-schema SCHEMA 源 schema (默认: eip3)
# --dst-host HOST 目标主机 (默认: localhost)
# --dst-port PORT 目标端口 (默认: 5433)
# --dst-user USER 目标用户 (默认: zhengwr)
# --dst-db DB 目标库名 (默认: eip4dev)
# --threshold MB 全量/schema-only 阈值,MB (默认: 500)
# --tables-config FILE 全量必须表配置文件 (默认: sql/data-tables.txt)
# --work-dir DIR dump 工作目录 (默认: /tmp/eip4dev_build)
# --skip-dump 跳过 dump,直接用 --work-dir 中已有文件 restore
# --drop-target 重建前先 DROP 目标库(幂等重建)
# -h, --help 显示帮助
#
# 安全守门:严禁连接 5435 上的 ceshi 库(只读库副本,结构不完整)
# ============================================================================
set -euo pipefail
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
LOG_PREFIX="[eip4dev]"
START_TS=$(date +%s)
# ---- 默认参数 ----
SRC_HOST="192.168.20.68"
SRC_PORT="5435"
SRC_USER="ceshi"
SRC_PASS="ceshi"
SRC_DB="postgres"
SRC_SCHEMA="eip3"
DST_HOST="localhost"
DST_PORT="5433"
DST_USER="zhengwr"
DST_DB="eip4dev"
THRESHOLD_MB=500
TABLES_CONFIG="${SCRIPT_DIR}/sql/data-tables.txt"
WORK_DIR="/tmp/eip4dev_build"
SKIP_DUMP=0
DROP_TARGET=0
# ---- 参数解析 ----
while [[ $# -gt 0 ]]; do
case "$1" in
--src-host) SRC_HOST="$2"; shift 2 ;;
--src-port) SRC_PORT="$2"; shift 2 ;;
--src-user) SRC_USER="$2"; shift 2 ;;
--src-pass) SRC_PASS="$2"; shift 2 ;;
--src-db) SRC_DB="$2"; shift 2 ;;
--src-schema) SRC_SCHEMA="$2"; shift 2 ;;
--dst-host) DST_HOST="$2"; shift 2 ;;
--dst-port) DST_PORT="$2"; shift 2 ;;
--dst-user) DST_USER="$2"; shift 2 ;;
--dst-db) DST_DB="$2"; shift 2 ;;
--threshold) THRESHOLD_MB="$2"; shift 2 ;;
--tables-config) TABLES_CONFIG="$2"; shift 2 ;;
--work-dir) WORK_DIR="$2"; shift 2 ;;
--skip-dump) SKIP_DUMP=1; shift ;;
--drop-target) DROP_TARGET=1; shift ;;
-h|--help)
sed -n '2,40p' "$0" | grep '^#' | sed 's/^# \{0,1\}//'
exit 0 ;;
*) echo "未知参数: $1"; exit 1 ;;
esac
done
# ---- 工具函数 ----
log() { echo "${LOG_PREFIX} $(date '+%H:%M:%S') $*"; }
die() { echo "${LOG_PREFIX} ERROR: $*" >&2; exit 1; }
elapsed() { echo $(( $(date +%s) - START_TS ))s; }
# ---- 安全守门:严禁连接 5435 上的 ceshi 库 ----
if [[ "$SRC_HOST" == "192.168.20.68" && "$SRC_PORT" == "5435" && "$SRC_DB" == "ceshi" ]]; then
die "安全守门触发:严禁连接 5435 上的 ceshi 库(数据不完整)!目标库应为 postgres(currentSchema=eip3)"
fi
export PGPASSWORD="$SRC_PASS"
SRC_CONN="-h $SRC_HOST -p $SRC_PORT -U $SRC_USER -d $SRC_DB"
DST_CONN="-h $DST_HOST -p $DST_PORT -U $DST_USER"
# ---- 检查依赖工具 ----
for tool in pg_dump pg_restore psql; do
command -v "$tool" > /dev/null 2>&1 || die "缺少工具: ${tool} (需要 PG15 客户端)"
done
PG_VER=$(pg_dump --version | awk '{print $3}' | cut -d. -f1)
log "pg_dump version: $PG_VER (need >= 15)"
[[ "$PG_VER" -ge 15 ]] || die "pg_dump version too old, need PG15 client"
# ---- 验证源库连接 ----
log "验证源库连接 ${SRC_HOST}:${SRC_PORT}/${SRC_DB}..."
SRC_PG_VER=$(PGPASSWORD="$SRC_PASS" psql $SRC_CONN -tAc "SELECT version();" 2>&1 | head -1) || die "源库连接失败"
log "源库: $SRC_PG_VER"
# ---- 统计源库分层 ----
log "统计源库表规模分层..."
TOTAL_TABLES=$(PGPASSWORD="$SRC_PASS" psql $SRC_CONN -tAc \
"SELECT COUNT(*) FROM pg_stat_user_tables WHERE schemaname='${SRC_SCHEMA}';")
ACT_TABLES=$(PGPASSWORD="$SRC_PASS" psql $SRC_CONN -tAc \
"SELECT COUNT(*) FROM pg_stat_user_tables WHERE schemaname='${SRC_SCHEMA}' AND relname LIKE 'act_%';")
GIANT_TABLES=$(PGPASSWORD="$SRC_PASS" psql $SRC_CONN -tAc \
"SELECT COUNT(*) FROM pg_stat_user_tables
WHERE schemaname='${SRC_SCHEMA}'
AND relname NOT LIKE 'act_%'
AND pg_total_relation_size(quote_ident(schemaname)||'.'||quote_ident(relname)) > ${THRESHOLD_MB}*1024*1024;")
FULLDATA_TABLES=$(( TOTAL_TABLES - ACT_TABLES - GIANT_TABLES ))
log "表统计:总计 ${TOTAL_TABLES} / ACT_*(schema-only) ${ACT_TABLES} / 超${THRESHOLD_MB}MB巨表(schema-only) ${GIANT_TABLES} / 全量数据表 ≈${FULLDATA_TABLES}"
# ---- 生成巨表名单(非ACT_,超阈值)----
GIANT_LIST_FILE="${WORK_DIR}/giant_tables.txt"
mkdir -p "$WORK_DIR"
PGPASSWORD="$SRC_PASS" psql $SRC_CONN -tAc \
"SELECT relname FROM pg_stat_user_tables
WHERE schemaname='${SRC_SCHEMA}'
AND relname NOT LIKE 'act_%'
AND pg_total_relation_size(quote_ident(schemaname)||'.'||quote_ident(relname)) > ${THRESHOLD_MB}*1024*1024
ORDER BY pg_total_relation_size(quote_ident(schemaname)||'.'||quote_ident(relname)) DESC;" > "$GIANT_LIST_FILE"
log "超阈值巨表清单 (${GIANT_LIST_FILE}):"
while IFS= read -r t; do
SZ=$(PGPASSWORD="$SRC_PASS" psql $SRC_CONN -tAc \
"SELECT pg_size_pretty(pg_total_relation_size('${SRC_SCHEMA}.${t}'));")
log " ${t}: ${SZ}"
done < "$GIANT_LIST_FILE"
# ---- 准备工作目录 ----
SCHEMA_DUMP="${WORK_DIR}/schema.dump"
DATA_DUMP="${WORK_DIR}/data.dump"
log "工作目录: ${WORK_DIR}"
if [[ $SKIP_DUMP -eq 0 ]]; then
# ---- Step 1: Schema-only dump(全 schema,所有表 DDL)----
log "=== Step 1/4: Schema-only dump 开始 ==="
T1=$(date +%s)
PGPASSWORD="$SRC_PASS" pg_dump \
-h "$SRC_HOST" -p "$SRC_PORT" -U "$SRC_USER" -d "$SRC_DB" \
-n "$SRC_SCHEMA" \
--schema-only \
-Fc \
-f "$SCHEMA_DUMP"
T2=$(date +%s)
SCHEMA_SZ=$(du -sh "$SCHEMA_DUMP" | cut -f1)
log "Schema dump 完成: ${SCHEMA_SZ},耗时 $((T2-T1))s"
# ---- Step 2: 全量数据 dump(排除 ACT_* 和超阈值巨表)----
log "=== Step 2/4: 数据 dump 开始(排除 ACT_* 和超阈值巨表)==="
T1=$(date +%s)
# 构建 --exclude-table-data 参数:ACT_* 全排
EXCLUDE_ARGS=()
EXCLUDE_ARGS+=(--exclude-table-data="${SRC_SCHEMA}.act_*")
# 排除超阈值巨表数据
while IFS= read -r tbl; do
[[ -n "$tbl" ]] && EXCLUDE_ARGS+=(--exclude-table-data="${SRC_SCHEMA}.${tbl}")
done < "$GIANT_LIST_FILE"
PGPASSWORD="$SRC_PASS" pg_dump \
-h "$SRC_HOST" -p "$SRC_PORT" -U "$SRC_USER" -d "$SRC_DB" \
-n "$SRC_SCHEMA" \
--data-only \
"${EXCLUDE_ARGS[@]}" \
-Fc \
-f "$DATA_DUMP"
T2=$(date +%s)
DATA_SZ=$(du -sh "$DATA_DUMP" | cut -f1)
log "数据 dump 完成: ${DATA_SZ},耗时 $((T2-T1))s"
else
log "跳过 dump(--skip-dump 模式),使用已有文件: schema=${SCHEMA_DUMP} data=${DATA_DUMP}"
[[ -f "$SCHEMA_DUMP" ]] || die "schema dump 文件不存在: $SCHEMA_DUMP"
[[ -f "$DATA_DUMP" ]] || die "data dump 文件不存在: $DATA_DUMP"
fi
# ---- Step 3: 准备目标库 ----
log "=== Step 3/4: 准备目标库 ${DST_DB} ==="
unset PGPASSWORD
if [[ $DROP_TARGET -eq 1 ]]; then
log "DROP 并重建目标库 ${DST_DB}..."
psql $DST_CONN -d postgres -c "DROP DATABASE IF EXISTS ${DST_DB};" 2>&1 | grep -v "^$" || true
fi
# 创建库(已存在则跳过)
psql $DST_CONN -d postgres -c "SELECT 1 FROM pg_database WHERE datname='${DST_DB}';" 2>&1 | grep -q "1 row" \
|| psql $DST_CONN -d postgres -c "CREATE DATABASE ${DST_DB} ENCODING 'UTF8';"
log "目标库 ${DST_DB} 就绪"
# 建角色(P0' 口径:restore 需要 eip3/ceshi 角色存在)
psql $DST_CONN -d "$DST_DB" -c "
DO \$\$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='eip3') THEN
CREATE ROLE eip3;
RAISE NOTICE 'role eip3 created';
ELSE
RAISE NOTICE 'role eip3 already exists';
END IF;
END\$\$;" 2>&1 | grep -v "^$"
psql $DST_CONN -d "$DST_DB" -c "
DO \$\$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='ceshi') THEN
CREATE ROLE ceshi LOGIN;
RAISE NOTICE 'role ceshi created';
ELSE
RAISE NOTICE 'role ceshi already exists';
END IF;
END\$\$;" 2>&1 | grep -v "^$"
# 安装 uuid-ossp 扩展(11 个函数默认值依赖)
psql $DST_CONN -d "$DST_DB" -c "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";" 2>&1 | grep -v "^$"
log "前置角色和扩展就绪"
# ---- Step 4: Restore ----
log "=== Step 4/4: Restore 到 ${DST_DB} ==="
# 4a: Schema restore
T1=$(date +%s)
log "4a: restore schema..."
pg_restore \
$DST_CONN \
-d "$DST_DB" \
--no-owner \
--no-privileges \
--disable-triggers \
-e \
"$SCHEMA_DUMP" 2>&1 | grep -v "^$" | grep -v "already exists" | head -50 || true
T2=$(date +%s)
log "Schema restore 完成,耗时 $((T2-T1))s"
# 4b: Data restore(禁用触发器,避免 FK 顺序问题)
T1=$(date +%s)
log "4b: restore data(--disable-triggers)..."
pg_restore \
$DST_CONN \
-d "$DST_DB" \
--no-owner \
--no-privileges \
--disable-triggers \
--single-transaction \
-e \
"$DATA_DUMP" 2>&1 | grep -v "^$" | head -100 || true
T2=$(date +%s)
log "数据 restore 完成,耗时 $((T2-T1))s"
# ---- 验证与统计 ----
log "=== 验证与统计 ==="
DST_TABLE_COUNT=$(psql $DST_CONN -d "$DST_DB" -tAc \
"SELECT COUNT(*) FROM pg_stat_user_tables WHERE schemaname='${SRC_SCHEMA}';")
log "目标库表数: ${DST_TABLE_COUNT}(源库: ${TOTAL_TABLES})"
# 关键引擎元数据行数验证
log "关键表行数验证:"
for tbl in sys_entity sys_entityattribute sys_reference sys_referencevalue sys_user view_template view_page sys_serviceflow; do
CNT=$(psql $DST_CONN -d "$DST_DB" -tAc "SELECT COUNT(*) FROM ${SRC_SCHEMA}.${tbl};" 2>/dev/null || echo "N/A")
log " ${tbl}: ${CNT} 行"
done
# 目标库体量
DB_SIZE=$(psql $DST_CONN -d "$DST_DB" -tAc \
"SELECT pg_size_pretty(pg_database_size('${DST_DB}'));")
log "目标库 ${DST_DB} 总体量: ${DB_SIZE}"
# ---- 现代层平台表回填(F1 新增 2026-06-11)----
# 现代层表(com.eip4.*)不在 5435 客户库,重建后须从本地 eip4m0 回填,否则丢失。
# 目前清单:sys_page_schema(P4-4 批量转换 1,489 产物)。新增现代层表时在此登记。
MODERN_TABLES=(sys_page_schema)
for t in "${MODERN_TABLES[@]}"; do
if psql $DST_CONN -d eip4m0 -tAc "SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name='${t}'" | grep -q 1; then
log "回填现代层表: eip4m0.public.${t} → ${DST_DB}.${SRC_SCHEMA}.${t}"
pg_dump $DST_CONN -d eip4m0 -t "public.${t}" --no-owner \
| sed "s/public\.${t}/${SRC_SCHEMA}.${t}/g; s/^SET search_path = public/SET search_path = ${SRC_SCHEMA}/" \
| psql $DST_CONN -d "$DST_DB" -v ON_ERROR_STOP=1 -q
ROWS=$(psql $DST_CONN -d "$DST_DB" -tAc "SELECT count(*) FROM ${SRC_SCHEMA}.${t}")
log "回填完成: ${SRC_SCHEMA}.${t} = ${ROWS} 行"
else
log "警告: eip4m0 中无 ${t},跳过回填(现代层产物可能缺失)"
fi
done
# ---- F2-15: VIEW_ 实体 vs pg_views 校验警告 ----
# 检查 sys_entity 中 VIEW_ 开头的实体是否在 pg_views 中存在对应视图;
# 缺失时输出 WARN 而不是 die,避免中断构建(部分视图属于虚拟/不可补类型)。
log "=== F2-15 VIEW_ 实体 vs pg_views 校验 ==="
VIEW_MISSING=$(psql $DST_CONN -d "$DST_DB" -tAc "
SET search_path=${SRC_SCHEMA};
SELECT e.tablename
FROM sys_entity e
WHERE e.tablename LIKE 'VIEW_%'
AND NOT EXISTS (
SELECT 1 FROM pg_views v
WHERE v.viewname = lower(e.tablename)
AND v.schemaname = '${SRC_SCHEMA}'
)
ORDER BY e.tablename;
" 2>/dev/null || echo "")
if [[ -z "$VIEW_MISSING" ]]; then
log "VIEW_ 实体校验通过:所有 VIEW_ 实体均有对应 pg_views 记录"
else
MISS_COUNT=$(echo "$VIEW_MISSING" | grep -c '.' || true)
log "WARN: ${MISS_COUNT} 个 VIEW_ 实体在 pg_views 中无对应视图(虚拟/不可补类型,不影响运行):"
echo "$VIEW_MISSING" | while IFS= read -r v; do
[[ -n "$v" ]] && log " MISSING: $v"
done
fi
TOTAL_ELAPSED=$(elapsed)
log "=== 构建完成!总耗时: ${TOTAL_ELAPSED} ==="
log "连接串: jdbc:postgresql://${DST_HOST}:${DST_PORT}/${DST_DB}?currentSchema=${SRC_SCHEMA}"
log "用户: ${DST_USER}(无密码)"
# ---- 分层汇总 ----
echo ""
echo "┌─────────────────────────────────────────────────────┐"
echo "│ eip4dev 构建完成 - 分层汇总 │"
echo "├──────────────────────────┬──────────────────────────┤"
printf "│ %-26s│ %-26s│\n" "总表数(DDL)" "${TOTAL_TABLES}"
printf "│ %-26s│ %-26s│\n" "ACT_*(schema-only)" "${ACT_TABLES}"
printf "│ %-26s│ %-26s│\n" "非ACT_巨表(schema-only)" "${GIANT_TABLES}"
printf "│ %-26s│ %-26s│\n" "全量数据表 ≈" "${FULLDATA_TABLES}"
printf "│ %-26s│ %-26s│\n" "目标库体量" "${DB_SIZE}"
printf "│ %-26s│ %-26s│\n" "总耗时" "${TOTAL_ELAPSED}"
echo "└──────────────────────────┴──────────────────────────┘"