xiaoxia-saas/scripts/ci/validate_migration.sh

#!/bin/bash
# CI Validate: Alembic迁移验证（升级版）
# 检查项:
#   1. migration文件命名规范检查
#   2. migration编号链完整性检查
#   3. upgrade head 升级验证（真实PG执行）
#   4. downgrade -1 回滚验证
#   5. alembic check 检测未生成migration的model变更
#
# 需要PostgreSQL数据库（共享PG或临时容器）

set -eu

SCRIPT_DIR="$(dirname "${BASH_SOURCE[0]}")"
# shellcheck source=ci_env.sh
source "${SCRIPT_DIR}/ci_env.sh"

echo "=== CI Validate: Alembic迁移验证（升级版）==="
echo ""

# ============================================================
# 阶段0: 静态检查（不需要数据库，先快速失败）
# ============================================================

echo "📋 阶段0: 静态检查（命名规范 + 链完整性）"
echo ""

STATIC_FAILED=0

echo "0.1 检查 migration 文件命名规范..."
if python3 scripts/ci/check_migration_naming.py alembic/versions; then
    echo "   ✅ 命名规范检查通过"
else
    echo "   ❌ 命名规范检查失败"
    STATIC_FAILED=1
fi

echo ""
echo "0.2 检查 migration 编号链完整性..."
if python3 scripts/ci/check_migration_chain.py alembic/versions; then
    echo "   ✅ 编号链完整性检查通过"
else
    echo "   ❌ 编号链完整性检查失败"
    STATIC_FAILED=1
fi

if [ "$STATIC_FAILED" -ne 0 ]; then
    echo ""
    echo "❌ 静态检查失败，请修复上述问题后重试"
    exit 1
fi

echo ""
echo "✅ 静态检查全部通过"
echo ""

# ============================================================
# DooD模式检测：确定宿主机访问地址
# ============================================================

detect_docker_host() {
  local test_port="${1:-${CI_LOCAL_PG_PORT}}"

  local candidates=()

  # 1. host.docker.internal
  if python3 -c "import socket; socket.gethostbyname('host.docker.internal')" 2>/dev/null; then
    candidates+=("host.docker.internal")
  fi

  # 2. docker0 桥接网关
  candidates+=("172.17.0.1")

  # 3. 默认网关
  local gw=""
  gw=$(ip route 2>/dev/null | grep default | awk '{print $3}' | head -1)
  if [ -n "$gw" ] && [ "$gw" != "127.0.0.1" ]; then
    candidates+=("$gw")
  fi

  # 4. 宿主机同网段的.1或.254
  local my_ip=""
  my_ip=$(hostname -I 2>/dev/null | awk '{print $1}')
  if [ -n "$my_ip" ]; then
    local subnet=$(echo "$my_ip" | cut -d. -f1-3)
    candidates+=("${subnet}.1")
    candidates+=("${subnet}.254")
  fi

  # 5. 127.0.0.1 最后尝试
  candidates+=("127.0.0.1")

  for candidate in "${candidates[@]}"; do
    if python3 -c "
import socket
s = socket.socket()
s.settimeout(2)
try:
    s.connect(('$candidate', $test_port))
    s.close()
    print('ok')
except:
    pass
" 2>/dev/null | grep -q ok; then
      echo "$candidate"
      return 0
    fi
  done

  echo "127.0.0.1"
  return 1
}

# 指数退避TCP连接检查
wait_tcp_ready() {
  local host="$1"
  local port="$2"
  local max_attempts="${3:-5}"
  local delay=1
  local attempt=1
  while [ "$attempt" -le "$max_attempts" ]; do
    if python3 -c "import socket; s=socket.socket(); s.settimeout(3); s.connect(('$host', $port)); s.close()" 2>/dev/null; then
      return 0
    fi
    echo "TCP连接尝试 $attempt/$max_attempts 失败，${delay}s后重试..."
    sleep "$delay"
    delay=$((delay * 2))
    attempt=$((attempt + 1))
  done
  return 1
}

# 获取宿主机IP
if [ -S /var/run/docker.sock ]; then
  DOCKER_HOST_IP=$(detect_docker_host "${CI_SHARED_PG_PORT}")
  if [ "$DOCKER_HOST_IP" = "127.0.0.1" ]; then
    DOCKER_HOST_IP=$(detect_docker_host 22)
  fi
  echo "检测到DooD模式，宿主机地址: $DOCKER_HOST_IP"
else
  DOCKER_HOST_IP="127.0.0.1"
  echo "非DooD模式，使用 127.0.0.1"
fi
PG_HOST="$DOCKER_HOST_IP"
echo "PG host: $PG_HOST"
echo ""

USE_SHARED_PG="${CI_USE_SHARED_PG:-false}"

# ============================================================
# 准备数据库
# ============================================================

echo "🗄️  阶段1: 准备测试数据库"
echo ""

CI_DB_NAME="ci_migrate_${GITHUB_RUN_ID:-$$}"

if [ "$USE_SHARED_PG" = "true" ]; then
  # 使用常驻共享PG实例
  echo "使用常驻共享PG实例（CI_USE_SHARED_PG=true）"
  SHARED_PG_HOST="$PG_HOST"
  SHARED_PG_PORT="${CI_SHARED_PG_PORT}"
  SHARED_PG_USER="${CI_SHARED_PG_USER}"
  SHARED_PG_PASSWORD="${CI_SHARED_PG_PASSWORD}"

  echo "等待共享PG连接就绪..."
  wait_tcp_ready "$SHARED_PG_HOST" "$SHARED_PG_PORT" 5

  echo "创建测试数据库: $CI_DB_NAME"
  PGPASSWORD="$SHARED_PG_PASSWORD" python3 -c "
import psycopg2
conn = psycopg2.connect(host='$SHARED_PG_HOST', port=$SHARED_PG_PORT, user='$SHARED_PG_USER', password='$SHARED_PG_PASSWORD', dbname='postgres')
conn.autocommit = True
cur = conn.cursor()
cur.execute(f'DROP DATABASE IF EXISTS \"$CI_DB_NAME\" WITH (FORCE)')
cur.execute(f'CREATE DATABASE \"$CI_DB_NAME\"')
cur.close()
conn.close()
"
  export DATABASE_URL="postgresql+psycopg://${SHARED_PG_USER}:${SHARED_PG_PASSWORD}@${SHARED_PG_HOST}:${SHARED_PG_PORT}/${CI_DB_NAME}"
  echo "✅ 共享PG数据库已创建: $CI_DB_NAME"

  cleanup_db() {
    echo ""
    echo "清理测试数据库: $CI_DB_NAME"
    PGPASSWORD="$SHARED_PG_PASSWORD" python3 -c "
import psycopg2
conn = psycopg2.connect(host='$SHARED_PG_HOST', port=$SHARED_PG_PORT, user='$SHARED_PG_USER', password='$SHARED_PG_PASSWORD', dbname='postgres')
conn.autocommit = True
cur = conn.cursor()
cur.execute(f'DROP DATABASE IF EXISTS \"$CI_DB_NAME\" WITH (FORCE)')
cur.close()
conn.close()
" 2>/dev/null || echo "WARN: 数据库清理失败"
    echo "✅ 数据库已清理"
  }
else
  # 使用临时PG容器（默认模式）
  echo "使用临时PG容器模式"
  PG_CONTAINER=ci-pg-validate-migration-${GITHUB_RUN_ID:-$$}
  docker rm -f "$PG_CONTAINER" 2>/dev/null || true
  docker run -d --name "$PG_CONTAINER" \
    --shm-size=256m \
    -e POSTGRES_USER=postgres \
    -e POSTGRES_PASSWORD=postgres \
    -e POSTGRES_DB=xiaoxia_saas \
    -P \
    --health-cmd "pg_isready -U postgres" \
    --health-interval 3s \
    --health-timeout 3s \
    --health-retries 20 \
    postgres:16-alpine
  PG_PORT=$(docker port "$PG_CONTAINER" ${CI_LOCAL_PG_PORT}/tcp | cut -d: -f2)
  echo "PostgreSQL port: $PG_PORT"
  export DATABASE_URL="postgresql+psycopg://${CI_SHARED_PG_USER}:${CI_SHARED_PG_PASSWORD}@${PG_HOST}:${PG_PORT}/${CI_DEFAULT_DB}"

  # 等待容器健康
  for i in $(seq 1 30); do
    if docker inspect --format='{{.State.Health.Status}}' "$PG_CONTAINER" 2>/dev/null | grep -q healthy; then
      echo "PostgreSQL container is healthy on port $PG_PORT"
      break
    fi
    echo "Waiting for PostgreSQL container health... ($i/30)"
    sleep 2
  done
  docker inspect --format='{{.State.Health.Status}}' "$PG_CONTAINER" | grep -q healthy

  # TCP连通性检查
  echo "验证TCP连通性 ($PG_HOST:$PG_PORT)..."
  wait_tcp_ready "$PG_HOST" "$PG_PORT" 5
  echo "TCP connectivity to PostgreSQL confirmed on port $PG_PORT"

  cleanup_db() {
    docker rm -f "$PG_CONTAINER" 2>/dev/null || true
  }
fi

trap cleanup_db EXIT

echo ""

# ============================================================
# 阶段2: upgrade head 升级验证
# ============================================================

echo "⬆️  阶段2: upgrade head 升级验证"
echo ""

PYTHONPATH="$PWD/apps/api:$PWD" python3 -m alembic upgrade head
echo "✅ upgrade head 通过"
echo ""

# ============================================================
# 阶段3: downgrade -1 回滚验证
# ============================================================

echo "⬇️  阶段3: downgrade -1 回滚验证"
echo ""

# 获取当前head版本号
HEAD_REV=$(PYTHONPATH="$PWD/apps/api:$PWD" python3 -m alembic current 2>&1 | awk '{print $1}' | head -1)
echo "当前版本 (head): $HEAD_REV"

# 检查是否只有1个migration（baseline），downgrade -1会到base
TOTAL_REVS=$(PYTHONPATH="$PWD/apps/api:$PWD" python3 -c "
from alembic.config import Config
from alembic.script import ScriptDirectory
config = Config('alembic.ini')
script = ScriptDirectory.from_config(config)
print(len(list(script.walk_revisions())))
")

echo "总 migration 数量: $TOTAL_REVS"

if [ "$TOTAL_REVS" -le 1 ]; then
    echo "⚠️  只有1个migration，跳过 downgrade 回滚验证（没有可回滚的版本）"
else
    echo "执行 downgrade -1..."
    PYTHONPATH="$PWD/apps/api:$PWD" python3 -m alembic downgrade -1
    echo "✅ downgrade -1 通过"

    # 回滚后再升级回去，确保双向都通
    echo ""
    echo "重新 upgrade head 验证双向一致性..."
    PYTHONPATH="$PWD/apps/api:$PWD" python3 -m alembic upgrade head
    echo "✅ 重新 upgrade head 通过（双向验证完成）"
fi

echo ""

# ============================================================
# 阶段4: alembic check - 检测未生成migration的model变更
# ============================================================

echo "🔍 阶段4: 检查是否有未生成migration的model变更"
echo ""

# alembic check: 没有待生成的migration时退出码0，有变更时退出码1
# 这里只检测，不阻断（警告模式），因为有些场景model变更不需要migration
set +e
CHECK_OUTPUT=$(PYTHONPATH="$PWD/apps/api:$PWD" python3 -m alembic check 2>&1)
CHECK_EXIT=$?
set -e

if [ "$CHECK_EXIT" -eq 0 ]; then
    echo "✅ 没有检测到未生成migration的model变更"
else
    if echo "$CHECK_OUTPUT" | grep -q "New upgrade operations detected"; then
        echo "⚠️  检测到未生成migration的model变更！"
        echo ""
        echo "$CHECK_OUTPUT"
        echo ""
        echo "提示: 如果model变更是有意的且需要生成migration，请运行:"
        echo "  alembic revision --autogenerate -m \"description\""
        echo "如果model变更不涉及数据库schema（如仅索引/约束重命名或纯业务逻辑），请确认后忽略此警告。"
        # 暂时不阻断，避免误报
        echo "（当前为警告模式，不阻断CI，后续稳定后可升级为阻断）"
    else
        echo "⚠️  alembic check 执行出错（非阻断）"
        echo "$CHECK_OUTPUT"
    fi
fi

echo ""
echo "=== CI Validate: Alembic迁移验证 全部通过 ✅ ==="