HaHafeng
88cc049fb3
- Implement 5 core API endpoints (create task, get progress, get results, update decision, export Excel) - Add FulltextScreeningController with Zod validation (652 lines) - Implement ExcelExporter service with 4-sheet report generation (352 lines) - Register routes under /api/v1/asl/fulltext-screening - Create 31 REST Client test cases - Add automated integration test script - Fix PDF extraction fallback mechanism in LLM12FieldsService - Update API design documentation to v3.0 - Update development plan to v1.2 - Create Day 5 development record - Clean up temporary test files
281 lines
9.2 KiB
PowerShell
281 lines
9.2 KiB
PowerShell
# ========================================
|
||
# execute-migration.ps1
|
||
# Schema迁移执行脚本（Windows PowerShell）
|
||
# ========================================
|
||
# 功能：
|
||
# 1. 备份当前数据库
|
||
# 2. 依次执行5个迁移脚本
|
||
# 3. 验证迁移结果
|
||
#
|
||
# 使用方法：
|
||
# .\execute-migration.ps1
|
||
# ========================================
|
||
|
||
# 设置错误时停止
|
||
$ErrorActionPreference = "Stop"
|
||
|
||
# 数据库连接信息
|
||
$DB_HOST = "localhost"
|
||
$DB_PORT = "5432"
|
||
$DB_NAME = "ai_clinical_research"
|
||
$DB_USER = "postgres"
|
||
$DB_PASS = "postgres"
|
||
|
||
# 设置PostgreSQL密码环境变量
|
||
$env:PGPASSWORD = $DB_PASS
|
||
|
||
# 脚本目录
|
||
$SCRIPT_DIR = Split-Path -Parent $MyInvocation.MyCommand.Path
|
||
|
||
Write-Host "========================================" -ForegroundColor Cyan
|
||
Write-Host "Schema迁移执行脚本 - V1.0" -ForegroundColor Cyan
|
||
Write-Host "========================================" -ForegroundColor Cyan
|
||
Write-Host ""
|
||
|
||
# ========================================
|
||
# 第一步：检查PostgreSQL连接
|
||
# ========================================
|
||
Write-Host "[1/6] 检查PostgreSQL连接..." -ForegroundColor Yellow
|
||
|
||
try {
|
||
$testConnection = psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT version();" 2>&1
|
||
if ($LASTEXITCODE -eq 0) {
|
||
Write-Host "✅ PostgreSQL连接成功" -ForegroundColor Green
|
||
} else {
|
||
throw "PostgreSQL连接失败"
|
||
}
|
||
} catch {
|
||
Write-Host "❌ 无法连接到PostgreSQL数据库" -ForegroundColor Red
|
||
Write-Host "错误信息: $_" -ForegroundColor Red
|
||
Write-Host ""
|
||
Write-Host "请检查：" -ForegroundColor Yellow
|
||
Write-Host "1. PostgreSQL服务是否正在运行" -ForegroundColor Yellow
|
||
Write-Host "2. 数据库连接信息是否正确" -ForegroundColor Yellow
|
||
Write-Host "3. psql命令是否在PATH中" -ForegroundColor Yellow
|
||
exit 1
|
||
}
|
||
|
||
Write-Host ""
|
||
|
||
# ========================================
|
||
# 第二步：备份当前数据库
|
||
# ========================================
|
||
Write-Host "[2/6] 备份当前数据库..." -ForegroundColor Yellow
|
||
|
||
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
|
||
$backupFile = Join-Path $SCRIPT_DIR "backup_before_migration_$timestamp.sql"
|
||
|
||
Write-Host "备份文件: $backupFile" -ForegroundColor Gray
|
||
|
||
try {
|
||
$output = pg_dump -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -f $backupFile 2>&1
|
||
|
||
if (Test-Path $backupFile) {
|
||
$fileSize = (Get-Item $backupFile).Length / 1KB
|
||
Write-Host "✅ 数据库备份成功 (大小: $([math]::Round($fileSize, 2)) KB)" -ForegroundColor Green
|
||
} else {
|
||
throw "备份文件未生成"
|
||
}
|
||
} catch {
|
||
Write-Host "❌ 数据库备份失败" -ForegroundColor Red
|
||
Write-Host "错误信息: $_" -ForegroundColor Red
|
||
exit 1
|
||
}
|
||
|
||
Write-Host ""
|
||
|
||
# ========================================
|
||
# 第三步：确认执行迁移
|
||
# ========================================
|
||
Write-Host "[3/6] 迁移确认" -ForegroundColor Yellow
|
||
Write-Host ""
|
||
Write-Host "即将执行以下操作：" -ForegroundColor White
|
||
Write-Host " 1. 创建10个Schema" -ForegroundColor Gray
|
||
Write-Host " 2. 迁移platform_schema（1个表）" -ForegroundColor Gray
|
||
Write-Host " 3. 迁移aia_schema（5个表）" -ForegroundColor Gray
|
||
Write-Host " 4. 迁移pkb_schema（5个表）" -ForegroundColor Gray
|
||
Write-Host " 5. 全局验证" -ForegroundColor Gray
|
||
Write-Host ""
|
||
Write-Host "⚠️ 这将修改数据库结构！" -ForegroundColor Red
|
||
Write-Host "✅ 数据库已备份到: $backupFile" -ForegroundColor Green
|
||
Write-Host ""
|
||
|
||
$confirmation = Read-Host "确认执行迁移？(输入 YES 继续，其他键取消)"
|
||
|
||
if ($confirmation -ne "YES") {
|
||
Write-Host "❌ 迁移已取消" -ForegroundColor Yellow
|
||
exit 0
|
||
}
|
||
|
||
Write-Host ""
|
||
|
||
# ========================================
|
||
# 第四步：执行迁移脚本
|
||
# ========================================
|
||
Write-Host "[4/6] 执行迁移脚本..." -ForegroundColor Yellow
|
||
Write-Host ""
|
||
|
||
# 迁移脚本列表
|
||
$migrationScripts = @(
|
||
@{Name="001-create-all-10-schemas.sql"; Description="创建10个Schema"},
|
||
@{Name="002-migrate-platform.sql"; Description="迁移platform_schema"},
|
||
@{Name="003-migrate-aia.sql"; Description="迁移aia_schema"},
|
||
@{Name="004-migrate-pkb.sql"; Description="迁移pkb_schema"},
|
||
@{Name="005-validate-all.sql"; Description="全局验证"}
|
||
)
|
||
|
||
$successCount = 0
|
||
$failedScripts = @()
|
||
|
||
foreach ($script in $migrationScripts) {
|
||
$scriptPath = Join-Path $SCRIPT_DIR $script.Name
|
||
Write-Host " 执行: $($script.Name) - $($script.Description)..." -ForegroundColor Cyan
|
||
|
||
if (-not (Test-Path $scriptPath)) {
|
||
Write-Host " ❌ 脚本文件不存在: $scriptPath" -ForegroundColor Red
|
||
$failedScripts += $script.Name
|
||
continue
|
||
}
|
||
|
||
try {
|
||
# 执行SQL脚本
|
||
$output = psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -f $scriptPath 2>&1
|
||
|
||
if ($LASTEXITCODE -eq 0) {
|
||
Write-Host " ✅ 成功" -ForegroundColor Green
|
||
$successCount++
|
||
} else {
|
||
throw "脚本执行返回错误代码: $LASTEXITCODE"
|
||
}
|
||
} catch {
|
||
Write-Host " ❌ 失败" -ForegroundColor Red
|
||
Write-Host " 错误: $_" -ForegroundColor Red
|
||
$failedScripts += $script.Name
|
||
}
|
||
|
||
Write-Host ""
|
||
}
|
||
|
||
# ========================================
|
||
# 第五步：迁移结果总结
|
||
# ========================================
|
||
Write-Host "[5/6] 迁移结果总结" -ForegroundColor Yellow
|
||
Write-Host ""
|
||
|
||
Write-Host "总计: $($migrationScripts.Count) 个脚本" -ForegroundColor White
|
||
Write-Host "成功: $successCount 个" -ForegroundColor Green
|
||
Write-Host "失败: $($failedScripts.Count) 个" -ForegroundColor $(if ($failedScripts.Count -gt 0) {"Red"} else {"Green"})
|
||
|
||
if ($failedScripts.Count -gt 0) {
|
||
Write-Host ""
|
||
Write-Host "失败的脚本：" -ForegroundColor Red
|
||
foreach ($failed in $failedScripts) {
|
||
Write-Host " - $failed" -ForegroundColor Red
|
||
}
|
||
Write-Host ""
|
||
Write-Host "⚠️ 迁移未完全成功！" -ForegroundColor Red
|
||
Write-Host "建议：检查错误日志，修复问题后重新执行" -ForegroundColor Yellow
|
||
Write-Host "回滚：可使用备份文件恢复 -> $backupFile" -ForegroundColor Yellow
|
||
} else {
|
||
Write-Host ""
|
||
Write-Host "✅ 所有迁移脚本执行成功！" -ForegroundColor Green
|
||
}
|
||
|
||
Write-Host ""
|
||
|
||
# ========================================
|
||
# 第六步：验证迁移结果
|
||
# ========================================
|
||
if ($successCount -eq $migrationScripts.Count) {
|
||
Write-Host "[6/6] 验证迁移结果..." -ForegroundColor Yellow
|
||
Write-Host ""
|
||
|
||
# 验证Schema数量
|
||
Write-Host "验证Schema创建..." -ForegroundColor Cyan
|
||
$schemaQuery = @"
|
||
SELECT COUNT(*) FROM information_schema.schemata
|
||
WHERE schema_name IN (
|
||
'platform_schema', 'aia_schema', 'pkb_schema',
|
||
'asl_schema', 'common_schema', 'dc_schema',
|
||
'rvw_schema', 'admin_schema', 'ssa_schema', 'st_schema'
|
||
);
|
||
"@
|
||
|
||
try {
|
||
$schemaCount = psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c $schemaQuery 2>&1
|
||
$schemaCount = $schemaCount.Trim()
|
||
|
||
if ($schemaCount -eq "10") {
|
||
Write-Host "✅ 10个Schema全部创建成功" -ForegroundColor Green
|
||
} else {
|
||
Write-Host "⚠️ Schema数量异常: 预期10个，实际${schemaCount}个" -ForegroundColor Yellow
|
||
}
|
||
} catch {
|
||
Write-Host "⚠️ 无法验证Schema数量" -ForegroundColor Yellow
|
||
}
|
||
|
||
Write-Host ""
|
||
|
||
# 验证数据迁移
|
||
Write-Host "验证数据迁移..." -ForegroundColor Cyan
|
||
|
||
$tables = @(
|
||
@{Schema="platform_schema"; Table="users"},
|
||
@{Schema="aia_schema"; Table="projects"},
|
||
@{Schema="aia_schema"; Table="conversations"},
|
||
@{Schema="pkb_schema"; Table="knowledge_bases"},
|
||
@{Schema="pkb_schema"; Table="documents"}
|
||
)
|
||
|
||
foreach ($tbl in $tables) {
|
||
try {
|
||
$countQuery = "SELECT COUNT(*) FROM $($tbl.Schema).$($tbl.Table);"
|
||
$count = psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c $countQuery 2>&1
|
||
$count = $count.Trim()
|
||
Write-Host " $($tbl.Schema).$($tbl.Table): $count 条记录" -ForegroundColor Gray
|
||
} catch {
|
||
Write-Host " ⚠️ 无法查询 $($tbl.Schema).$($tbl.Table)" -ForegroundColor Yellow
|
||
}
|
||
}
|
||
|
||
Write-Host ""
|
||
Write-Host "========================================" -ForegroundColor Cyan
|
||
Write-Host "✅ Schema迁移执行完成！" -ForegroundColor Green
|
||
Write-Host "========================================" -ForegroundColor Cyan
|
||
Write-Host ""
|
||
Write-Host "下一步操作：" -ForegroundColor Yellow
|
||
Write-Host "1. 更新Prisma配置 (backend/prisma/schema.prisma)" -ForegroundColor Gray
|
||
Write-Host "2. 生成Prisma Client (npx prisma generate)" -ForegroundColor Gray
|
||
Write-Host "3. 更新代码以使用新Schema" -ForegroundColor Gray
|
||
Write-Host "4. 测试现有功能" -ForegroundColor Gray
|
||
Write-Host ""
|
||
Write-Host "备份文件保存在: $backupFile" -ForegroundColor Cyan
|
||
Write-Host ""
|
||
} else {
|
||
Write-Host "[6/6] 跳过验证（因为迁移未完全成功）" -ForegroundColor Yellow
|
||
Write-Host ""
|
||
}
|
||
|
||
# 清理环境变量
|
||
Remove-Item Env:\PGPASSWORD
|
||
|
||
Write-Host "脚本执行完成！" -ForegroundColor Green
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|