Tolan/backend/migrations/migrate_metadata_column.py

"""
数据库迁移脚本：将 messages 表的 metadata 列重命名为 message_metadata
运行: python migrate_metadata_column.py
"""
import asyncio
import aiomysql
from config import settings
import sys


async def migrate_metadata_column():
    """将 messages 表的 metadata 列重命名为 message_metadata"""
    
    try:
        # 创建数据库连接
        conn = await aiomysql.connect(
            host=settings.MYSQL_HOST,
            port=settings.MYSQL_PORT,
            user=settings.MYSQL_USER,
            password=settings.MYSQL_PASSWORD,
            db=settings.MYSQL_DATABASE,
            charset='utf8mb4'
        )
        
        async with conn.cursor() as cursor:
            # 检查 messages 表是否存在
            await cursor.execute("""
                SELECT COUNT(*) FROM information_schema.tables 
                WHERE table_schema = %s AND table_name = 'messages'
            """, (settings.MYSQL_DATABASE,))
            table_exists = (await cursor.fetchone())[0] > 0
            
            if not table_exists:
                print("❌ messages 表不存在，无需迁移")
                conn.close()
                return True
            
            # 检查是否存在 metadata 列
            await cursor.execute("""
                SELECT COUNT(*) FROM information_schema.columns 
                WHERE table_schema = %s 
                AND table_name = 'messages' 
                AND column_name = 'metadata'
            """, (settings.MYSQL_DATABASE,))
            has_metadata = (await cursor.fetchone())[0] > 0
            
            # 检查是否已存在 message_metadata 列
            await cursor.execute("""
                SELECT COUNT(*) FROM information_schema.columns 
                WHERE table_schema = %s 
                AND table_name = 'messages' 
                AND column_name = 'message_metadata'
            """, (settings.MYSQL_DATABASE,))
            has_message_metadata = (await cursor.fetchone())[0] > 0
            
            if has_message_metadata:
                print("✅ message_metadata 列已存在，无需迁移")
                conn.close()
                return True
            
            if not has_metadata:
                print("⚠️  metadata 列不存在，可能表结构已更新或表为空")
                conn.close()
                return True
            
            # 执行重命名操作
            print("🔄 开始迁移: metadata -> message_metadata")
            await cursor.execute("""
                ALTER TABLE messages 
                CHANGE COLUMN metadata message_metadata JSON 
                COMMENT '扩展信息(音频路径、识别耗时等)'
            """)
            await conn.commit()
            print("✅ 迁移成功: metadata 列已重命名为 message_metadata")
        
        conn.close()
        return True
        
    except Exception as e:
        import traceback
        print(f"❌ 迁移失败: {e}")
        traceback.print_exc()
        return False


if __name__ == "__main__":
    # 确认是否继续
    if len(sys.argv) > 1 and sys.argv[1] == "--force":
        confirm = "y"
    else:
        confirm = input("\n是否继续迁移 messages 表的 metadata 列? (y/n): ")
    
    if confirm.lower() != 'y':
        sys.exit(0)
    
    # 执行迁移
    success = asyncio.run(migrate_metadata_column())
    
    if not success:
        sys.exit(1)