103 lines
5.0 KiB
Python
103 lines
5.0 KiB
Python
#!/usr/bin/env python3
|
||
"""修复 Phase1 迁移 - 添加缺失的列"""
|
||
|
||
import asyncio
|
||
from sqlalchemy.ext.asyncio import create_async_engine
|
||
from sqlalchemy import text
|
||
|
||
DB_CONFIG = {
|
||
'host': '123.57.81.127',
|
||
'port': 3306,
|
||
'user': 'cpy_admin',
|
||
'password': 'Tgzz2025+',
|
||
'database': 'ruoyi-fastapi'
|
||
}
|
||
|
||
DATABASE_URL = (
|
||
f"mysql+asyncmy://{DB_CONFIG['user']}:{DB_CONFIG['password']}@"
|
||
f"{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}"
|
||
)
|
||
|
||
ALTER_STATEMENTS = [
|
||
# test_category 扩展
|
||
("test_category", "ALTER TABLE test_category ADD COLUMN parent_id INT NULL COMMENT '父级分类ID'"),
|
||
("test_category", "ALTER TABLE test_category ADD COLUMN level INT NULL DEFAULT 2 COMMENT '层级: 1=大类, 2=测试项目'"),
|
||
("test_category", "ALTER TABLE test_category ADD COLUMN category_code VARCHAR(50) NULL COMMENT '类别编码'"),
|
||
("test_category", "ALTER TABLE test_category ADD COLUMN sort_order INT DEFAULT 0 COMMENT '排序'"),
|
||
("test_category", "ALTER TABLE test_category ADD COLUMN report_config_type VARCHAR(20) NULL DEFAULT 'CATEGORY' COMMENT '报告配置: CATEGORY=按大类, ITEM=按子项'"),
|
||
|
||
# test_work_order 扩展 - Phase1
|
||
("test_work_order", "ALTER TABLE test_work_order ADD COLUMN priority INT DEFAULT 1 COMMENT '优先级: 1=最低, 5=最高'"),
|
||
("test_work_order", "ALTER TABLE test_work_order ADD COLUMN expected_finish_date DATE NULL COMMENT '预计完成日期'"),
|
||
("test_work_order", "ALTER TABLE test_work_order ADD COLUMN actual_finish_date DATE NULL COMMENT '实际完成日期'"),
|
||
("test_work_order", "ALTER TABLE test_work_order ADD COLUMN claimed_by INT NULL COMMENT '领取人ID'"),
|
||
("test_work_order", "ALTER TABLE test_work_order ADD COLUMN claimed_at DATETIME NULL COMMENT '领取时间'"),
|
||
|
||
# test_work_order 扩展 - Phase2
|
||
("test_work_order", "ALTER TABLE test_work_order ADD COLUMN condition_data JSON NULL COMMENT '测试条件实际值（JSON）'"),
|
||
("test_work_order", "ALTER TABLE test_work_order ADD COLUMN result_data JSON NULL COMMENT '测试结果数据（JSON）'"),
|
||
("test_work_order", "ALTER TABLE test_work_order ADD COLUMN raw_data JSON NULL COMMENT '原始测试数据（JSON）'"),
|
||
("test_work_order", "ALTER TABLE test_work_order ADD COLUMN attachments JSON NULL COMMENT '附件列表（JSON）'"),
|
||
("test_work_order", "ALTER TABLE test_work_order ADD COLUMN retest_count INT DEFAULT 0 COMMENT '重测次数'"),
|
||
("test_work_order", "ALTER TABLE test_work_order ADD COLUMN is_retest BOOLEAN DEFAULT FALSE COMMENT '是否重测'"),
|
||
("test_work_order", "ALTER TABLE test_work_order ADD COLUMN original_work_order_id INT NULL COMMENT '原始工单ID'"),
|
||
("test_work_order", "ALTER TABLE test_work_order ADD COLUMN retest_reason VARCHAR(500) NULL COMMENT '重测原因'"),
|
||
|
||
# 修改 tester_id 为可空
|
||
("test_work_order", "ALTER TABLE test_work_order MODIFY COLUMN tester_id INT NULL COMMENT '测试人ID（可空，支持自动领取）'"),
|
||
]
|
||
|
||
INDEX_STATEMENTS = [
|
||
("test_category", "CREATE INDEX idx_parent_id ON test_category(parent_id)"),
|
||
("test_category", "CREATE INDEX idx_level ON test_category(level)"),
|
||
("test_work_order", "CREATE INDEX idx_priority ON test_work_order(priority)"),
|
||
("test_work_order", "CREATE INDEX idx_expected_finish_date ON test_work_order(expected_finish_date)"),
|
||
("test_work_order", "CREATE INDEX idx_claimed_by ON test_work_order(claimed_by)"),
|
||
("test_work_order", "CREATE INDEX idx_test_status_tester ON test_work_order(test_status, tester_id)"),
|
||
]
|
||
|
||
async def execute_alter():
|
||
engine = create_async_engine(DATABASE_URL, echo=False)
|
||
|
||
async with engine.connect() as conn:
|
||
print("=" * 60)
|
||
print("Fixing Phase1 Migration")
|
||
print("=" * 60)
|
||
|
||
# Execute ALTER statements
|
||
print("\n[1] Adding columns...")
|
||
for table, sql in ALTER_STATEMENTS:
|
||
try:
|
||
await conn.execute(text(sql))
|
||
await conn.commit()
|
||
print(f" OK: {table} - {sql[:50]}...")
|
||
except Exception as e:
|
||
error_msg = str(e).lower()
|
||
if 'duplicate' in error_msg or 'already exists' in error_msg:
|
||
print(f" SKIP: {table} - already exists")
|
||
else:
|
||
print(f" FAIL: {table} - {e}")
|
||
|
||
# Execute INDEX statements
|
||
print("\n[2] Creating indexes...")
|
||
for table, sql in INDEX_STATEMENTS:
|
||
try:
|
||
await conn.execute(text(sql))
|
||
await conn.commit()
|
||
print(f" OK: {sql[:50]}...")
|
||
except Exception as e:
|
||
error_msg = str(e).lower()
|
||
if 'duplicate' in error_msg or 'already exists' in error_msg:
|
||
print(f" SKIP: already exists")
|
||
else:
|
||
print(f" FAIL: {e}")
|
||
|
||
print("\n" + "=" * 60)
|
||
print("Fix Complete")
|
||
print("=" * 60)
|
||
|
||
await engine.dispose()
|
||
|
||
if __name__ == '__main__':
|
||
asyncio.run(execute_alter())
|