From 34981c30a78e8bbd7791131059a9210f9928b62c Mon Sep 17 00:00:00 2001
From: 刘光辉 <347230014@qq.com>
Date: 星期四, 17 九月 2026 09:24:09 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master' into master
---
docker/postgres/migration/schema-drift-check.sh | 147 +++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 147 insertions(+), 0 deletions(-)
diff --git a/docker/postgres/migration/schema-drift-check.sh b/docker/postgres/migration/schema-drift-check.sh
new file mode 100644
index 0000000..63b138e
--- /dev/null
+++ b/docker/postgres/migration/schema-drift-check.sh
@@ -0,0 +1,147 @@
+#!/usr/bin/env bash
+# 鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�
+# schema 婕傜Щ妫�鏌ワ細瀵圭収杩滅▼ MySQL锛堜簨瀹炴簮锛変笌鏈湴 PG 鐨勮〃绾� + 鍒楃骇宸紓
+#
+# 鑳屾櫙锛歴elf_built_tables_pg.sql 鏄縼绉绘湡蹇収锛岃繙绔槸娲昏穬寮�鍙戝簱锛圠035锛夆�斺�旀鍚�
+# 杩滅鎸佺画鍔犲垪/寤鸿〃銆傚悎骞� main 甯︿笟鍔¤〃鍙樻洿銆佹垨鏁版嵁鍒锋柊锛坢igrate-data.sh锛変箣鍓嶏紝
+# 鍏堣窇鏈剼鏈紱鍚﹀垯婕傜Щ浼氶潤榛樻簻杩涗氦浠樺寘锛堝惎鍔�/鍐掔儫鍏ㄨ繃锛岀偣鍒版柊鍔熻兘鎵�
+# "column does not exist"锛夈�傚凡鍙戠敓涓や緥锛氱幆澧冪洃娴� 31 鍒楋紙update_lims_huanjing_
+# 20260717.sql锛夈�乻xt/绋冲畾鎬� 7 鍒� + 6 琛紙update_lims_sxt_wendingxing_20260717.sql锛夈��
+#
+# 鐢ㄦ硶锛堝嚟鎹粡鐜鍙橀噺浼犲叆锛岀粷涓嶅啓杩涜剼鏈紱杩滅▼搴撹繛鎺ュ弬鏁拌椤圭洰鍐呴儴鎵嬪唽锛夛細
+# MYSQL_HOST=<host> MYSQL_PASSWORD=<pw> ./schema-drift-check.sh
+#
+# 鍙傛暟锛堢幆澧冨彉閲忥級锛�
+# MYSQL_HOST 蹇呭～
+# MYSQL_PASSWORD 蹇呭～
+# MYSQL_PORT=3306 MYSQL_USER=root MYSQL_DB=jnpf_init
+# MYSQL_CLIENT_IMAGE=mysql:8.0 鏈湴 mysql 瀹㈡埛绔槸 9.x锛岃蛋瀹瑰櫒瀹㈡埛绔紙椤圭洰绾﹀畾锛孡010 utf8mb4锛�
+# CONTAINER=cx-postgres PG_DB=jnpf_init
+# EXCLUDE_RE= 鎸夎〃鍚嶆鍒欐帓闄わ紙濡� '_bak$|_tmp$'锛夛紝涓や晶鍚屾椂鐢熸晥
+#
+# 鍒ゅ畾涓庨��鍑虹爜锛�
+# MySQL 鏈夎�� PG 缂猴紙琛�/鍒楋級锛濋樆鏂�ф紓绉� 鈫� 鎵撳嵃鏄庣粏鍚被鍨�/娉ㄩ噴锛堝彲鐩存帴鐓ф妱鍐欏閲� SQL锛夛紝exit 1
+# PG 鏈夎�� MySQL 缂� 锛濊鍛婏紙杩滅宸插垹鍒�/鏈湴淇濈暀锛屽垹鍒楁湁鏁版嵁椋庨櫓锛岄粯璁や笉澶勭悊锛夛紝exit 0
+# 涓や晶鍒楀悕缁熶竴灏忓啓姣旇緝锛圥G 鏈姞寮曞彿鏍囪瘑绗︽姌鍙犲皬鍐欙紝L035 澶у皬鍐欐姌鍙犲潙锛�
+#
+# 鍙戠幇闃绘柇鎬ф紓绉诲悗鐨勫缃紙SOP 璇﹁ docs/docker-compose-guide.md 搂18锛夛細
+# 1. 鎸夎緭鍑烘槑缁嗗啓澧為噺 SQL锛堝箓绛� ADD COLUMN IF NOT EXISTS锛夊叆 docs/pg-migration-ddl/
+# 2. 鎺ョ嚎 install.sh + assemble-package.sh锛堢収 update_lims_huanjing_20260717.sql 鍏堜緥锛�
+# 3. 鏈湴 PG 搴旂敤 鈫� 閲嶈窇鏈剼鏈簲杞豢 鈫� make jars && make up && make smoke
+# 鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�
+set -euo pipefail
+
+# 鈹�鈹� 閫�褰规姢鏍忥紙2026-07-25锛夆攢鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�
+# 鏈剼鏈互銆岃繙绋� MySQL = 浜嬪疄婧愩�嶄负鍓嶆彁鍋� schema 瀵圭収锛岃鍓嶆彁鑷� 2026-07-20 璧锋秷澶憋細
+# 鐜颁簨瀹炴簮鏄吘璁簯 CVM 鍏变韩 PG锛孧ySQL 鍋滃湪 07-16 涓斾笉鍐嶆湁浜烘敼瀹冦��
+# 鑴氭湰鏈韩鍙鏃犵牬鍧忔�э紝浣嗗叾缁撹宸插け鏁� 鈥斺�� 浼氭妸"PG 姣� MySQL 澶氬嚭鏉ョ殑鏂拌〃/鏂板垪"鎶ユ垚婕傜Щ锛�
+# 鐓х潃鍘�"琛�"鍙嶈�屼細鐮村潖褰撳墠 schema銆傛晠榛樿鎷掔粷锛岄伩鍏嶆湁浜烘嬁澶辨晥缁撹琛屽姩銆�
+# 鐜板湪瑕佹煡 schema 宸紓锛屽簲鏀逛负瀵圭収銆屾湰鍦� PG 鈫� CVM 鍏变韩 PG銆嶏紝鑰岄潪 MySQL銆�
+if [ "${I_KNOW_MIGRATION_IS_RETIRED:-}" != "1" ]; then
+ printf '\033[1;31m[宸查��褰孤锋嫆缁濇墽琛宂\033[0m %s\n' "schema-drift-check.sh 鐨勫鐓у熀鍑嗭紙杩滅▼ MySQL锛夊凡涓嶆槸浜嬪疄婧愩��" >&2
+ printf ' 瀹冧細鎶� PG 渚ф柊澧炵殑琛�/鍒楄鎶ユ垚"缂哄垪婕傜Щ"锛岀収姝や慨鏀逛細鐮村潖褰撳墠 schema銆俓n' >&2
+ printf ' 鏌� schema 宸紓璇锋敼涓哄鐓� 鏈湴 PG 鈫� CVM 鍏变韩 PG(YOUR_DB_HOST:15432)銆俓n' >&2
+ printf ' 浠呴渶涓庡巻鍙� MySQL 鍋氳�冨彜瀵圭収鏃讹細I_KNOW_MIGRATION_IS_RETIRED=1 %s\n' "$0" >&2
+ exit 1
+fi
+
+MYSQL_HOST="${MYSQL_HOST:?蹇呭～锛氳繙绋� MySQL 鍦板潃锛堣繛鎺ュ弬鏁拌椤圭洰鍐呴儴鎵嬪唽锛墋"
+MYSQL_PASSWORD="${MYSQL_PASSWORD:?蹇呭～锛氳繙绋� MySQL 瀵嗙爜锛堢粡鐜鍙橀噺浼犲叆锛屽嬁鍐欒剼鏈級}"
+MYSQL_PORT="${MYSQL_PORT:-3306}"
+MYSQL_USER="${MYSQL_USER:-root}"
+MYSQL_DB="${MYSQL_DB:-jnpf_init}"
+MYSQL_CLIENT_IMAGE="${MYSQL_CLIENT_IMAGE:-mysql:8.0}"
+CONTAINER="${CONTAINER:-cx-postgres}"
+PG_DB="${PG_DB:-jnpf_init}"
+EXCLUDE_RE="${EXCLUDE_RE:-}"
+
+log() { printf '\033[1;34m[drift]\033[0m %s\n' "$*"; }
+warn() { printf '\033[1;33m[drift][warn]\033[0m %s\n' "$*"; }
+die() { printf '\033[1;31m[drift][err]\033[0m %s\n' "$*" >&2; exit 1; }
+
+TMP="$(mktemp -d)"
+cleanup() { rm -rf "$TMP"; }
+trap cleanup EXIT
+trap 'exit 130' INT
+trap 'exit 143' TERM
+
+docker exec "$CONTAINER" true >/dev/null 2>&1 || die "PG 瀹瑰櫒 ${CONTAINER} 涓嶅湪杩愯"
+
+# 鈹�鈹� 1. 涓や晶鍚勪竴娆� information_schema 鍏ㄩ噺瀵煎嚭锛堝彧鍙� BASE TABLE锛屾帓闄よ鍥�/鎵╁睍瑙嗗浘锛� 鈹�鈹�
+log "瀵煎嚭杩滅▼ MySQL schema锛�${MYSQL_HOST}:${MYSQL_PORT}/${MYSQL_DB}锛夆��"
+docker run --rm "$MYSQL_CLIENT_IMAGE" mysql --default-character-set=utf8mb4 \
+ -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" \
+ information_schema --batch -sN -e "
+ SELECT LOWER(c.table_name), LOWER(c.column_name), c.column_type, c.is_nullable, c.column_comment
+ FROM columns c JOIN tables t
+ ON t.table_schema=c.table_schema AND t.table_name=c.table_name
+ WHERE c.table_schema='$MYSQL_DB' AND t.table_type='BASE TABLE'
+ ORDER BY 1,2" 2>/dev/null > "$TMP/mysql.tsv" \
+ || die "杩滅▼ MySQL 瀵煎嚭澶辫触锛堢綉缁�/鍑嵁锛燂級"
+
+log "瀵煎嚭鏈湴 PG schema锛�${CONTAINER}/${PG_DB}锛夆��"
+docker exec "$CONTAINER" psql -U postgres -d "$PG_DB" -tA -F$'\t' -c "
+ SELECT lower(c.table_name), lower(c.column_name)
+ FROM information_schema.columns c JOIN information_schema.tables t
+ ON t.table_schema=c.table_schema AND t.table_name=c.table_name
+ WHERE c.table_schema='public' AND t.table_type='BASE TABLE'
+ ORDER BY 1,2" > "$TMP/pg.tsv" \
+ || die "鏈湴 PG 瀵煎嚭澶辫触"
+
+# 琛�.鍒� 閿泦鍚堬紙鍙�夋帓闄わ級
+mkkeys() { # $1=in tsv $2=out keys
+ if [ -n "$EXCLUDE_RE" ]; then
+ awk -F'\t' -v re="$EXCLUDE_RE" '$1 !~ re {print $1"."$2}' "$1" > "$2"
+ else
+ awk -F'\t' '{print $1"."$2}' "$1" > "$2"
+ fi
+}
+mkkeys "$TMP/mysql.tsv" "$TMP/mysql.keys"
+mkkeys "$TMP/pg.tsv" "$TMP/pg.keys"
+cut -d. -f1 "$TMP/mysql.keys" | sort -u > "$TMP/mysql.tables"
+cut -d. -f1 "$TMP/pg.keys" | sort -u > "$TMP/pg.tables"
+
+N_MY_T=$(wc -l < "$TMP/mysql.tables" | tr -d ' '); N_PG_T=$(wc -l < "$TMP/pg.tables" | tr -d ' ')
+log "MySQL 琛� ${N_MY_T} / PG 琛� ${N_PG_T}锛堝垪閿� $(wc -l < "$TMP/mysql.keys" | tr -d ' ') / $(wc -l < "$TMP/pg.keys" | tr -d ' ')锛�"
+
+# 鈹�鈹� 2. 琛ㄧ骇宸泦 鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�
+comm -23 "$TMP/mysql.tables" "$TMP/pg.tables" > "$TMP/tables.missing" # 闃绘柇
+comm -13 "$TMP/mysql.tables" "$TMP/pg.tables" > "$TMP/tables.extra" # 璀﹀憡
+
+# 鈹�鈹� 3. 鍒楃骇宸泦锛堝彧鐪嬩袱渚ч兘鏈夌殑琛紝鏁磋〃缂哄け宸插湪琛ㄧ骇鎶ヨ繃锛� 鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�
+comm -12 "$TMP/mysql.tables" "$TMP/pg.tables" > "$TMP/tables.common"
+grep -F -f <(sed 's/$/./' "$TMP/tables.common") "$TMP/mysql.keys" 2>/dev/null \
+ | comm -23 - "$TMP/pg.keys" > "$TMP/cols.missing" || true # 闃绘柇
+grep -F -f <(sed 's/$/./' "$TMP/tables.common") "$TMP/pg.keys" 2>/dev/null \
+ | comm -13 "$TMP/mysql.keys" - > "$TMP/cols.extra" || true # 璀﹀憡
+
+# 鈹�鈹� 4. 鎶ュ憡 鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�
+BLOCKING=0
+echo
+if [ -s "$TMP/tables.missing" ]; then
+ BLOCKING=1
+ printf '\033[1;31m[drift] 鉁� MySQL 鏈夎�� PG 缂虹殑琛紙%s 寮狅紝闃绘柇锛夛細\033[0m\n' "$(wc -l < "$TMP/tables.missing" | tr -d ' ')"
+ sed 's/^/ /' "$TMP/tables.missing"
+fi
+if [ -s "$TMP/cols.missing" ]; then
+ BLOCKING=1
+ printf '\033[1;31m[drift] 鉁� MySQL 鏈夎�� PG 缂虹殑鍒楋紙%s 涓紝闃绘柇锛夆�斺�斿惈绫诲瀷/娉ㄩ噴锛屽彲鐓ф妱鍐欏閲� SQL锛歕033[0m\n' "$(wc -l < "$TMP/cols.missing" | tr -d ' ')"
+ # 浠� mysql.tsv 鍙嶆煡绫诲瀷涓庢敞閲�
+ awk -F'\t' 'NR==FNR{miss[$0]=1; next} (($1"."$2) in miss){printf " %-40s %-30s %-14s %s %s\n", $1, $2, $3, ($4=="YES"?"NULL":"NOT NULL"), $5}' \
+ "$TMP/cols.missing" "$TMP/mysql.tsv"
+fi
+if [ -s "$TMP/tables.extra" ]; then
+ warn "PG 鏈夎�� MySQL 缂虹殑琛紙$(wc -l < "$TMP/tables.extra" | tr -d ' ') 寮狅紝閫氬父=杩滅宸插垹鎴栨湰鍦拌嚜寤猴紝榛樿淇濈暀锛夛細"
+ sed 's/^/ /' "$TMP/tables.extra"
+fi
+if [ -s "$TMP/cols.extra" ]; then
+ warn "PG 鏈夎�� MySQL 缂虹殑鍒楋紙$(wc -l < "$TMP/cols.extra" | tr -d ' ') 涓紝杩滅宸插垹/鏈湴淇濈暀锛屽垹鍒楁湁鏁版嵁椋庨櫓锛岄粯璁や笉澶勭悊锛夛細"
+ sed 's/^/ /' "$TMP/cols.extra"
+fi
+
+echo
+if [ "$BLOCKING" = 1 ]; then
+ printf '\033[1;31m[drift][err]\033[0m 瀛樺湪闃绘柇鎬ф紓绉伙細鎸変笂鏂规槑缁嗗啓澧為噺 SQL锛堢収 docs/pg-migration-ddl/update_lims_huanjing_20260717.sql 鍏堜緥锛氬箓绛� ADD COLUMN IF NOT EXISTS + 鎺ョ嚎 install.sh/assemble-package.sh锛夛紝SOP 瑙� docs/docker-compose-guide.md 搂18\n' >&2
+ exit 1
+fi
+log "鏃犻樆鏂�ф紓绉� 鉁擄紙璀﹀憡椤瑰涓婏紝鍧囦负 PG 渚у鍑猴紝鏃犲锛�"
--
Gitblit v1.8.0