migration.py 4.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475
  1. # app/core/migration.py
  2. from sqlalchemy import text
  3. from sqlalchemy.ext.asyncio import AsyncConnection
  4. from app.core.logger import logger
  5. async def run_settlement_migrations(conn: AsyncConnection):
  6. """
  7. 检查并为已有数据表增量添加字段,保证已有生产/开发数据库无缝平滑升级
  8. """
  9. logger.info("⏳ [MIGRATION] Checking database schema for settlement fields...")
  10. try:
  11. # 1. 检查 vas_order 表的列
  12. res = await conn.execute(text("SHOW COLUMNS FROM vas_order"))
  13. existing_order_cols = {row[0] for row in res.fetchall()}
  14. # 待添加字段及其 DDL
  15. order_cols_to_add = [
  16. ("channel_type", "ALTER TABLE vas_order ADD COLUMN channel_type VARCHAR(32) NOT NULL DEFAULT 'direct' COMMENT 'direct:社交自收直客, agency:中介渠道, website:官网公海'"),
  17. ("agency_id", "ALTER TABLE vas_order ADD COLUMN agency_id VARCHAR(64) NULL COMMENT '中介机构ID'"),
  18. ("handler_id", "ALTER TABLE vas_order ADD COLUMN handler_id VARCHAR(128) NULL COMMENT '40%销售提成归属人ID'"),
  19. ("developer_id", "ALTER TABLE vas_order ADD COLUMN developer_id VARCHAR(128) NULL COMMENT '20%技术版权归属人ID'"),
  20. ("settlement_status", "ALTER TABLE vas_order ADD COLUMN settlement_status VARCHAR(32) NOT NULL DEFAULT 'unsettled' COMMENT '结算状态: unsettled, risk_period, eligible, settled'"),
  21. ("completed_at", "ALTER TABLE vas_order ADD COLUMN completed_at DATETIME NULL COMMENT '履约完成时间'"),
  22. ("risk_cleared_at", "ALTER TABLE vas_order ADD COLUMN risk_cleared_at DATETIME NULL COMMENT '退款风险期截止时间'"),
  23. ("settlement_period_id", "ALTER TABLE vas_order ADD COLUMN settlement_period_id VARCHAR(32) NULL COMMENT '结算账期ID'"),
  24. ]
  25. for col_name, ddl in order_cols_to_add:
  26. if col_name not in existing_order_cols:
  27. logger.info(f"➕ [MIGRATION] Adding column '{col_name}' to table 'vas_order'...")
  28. await conn.execute(text(ddl))
  29. # 为 handler_id 补建索引(若未建)
  30. try:
  31. await conn.execute(text("CREATE INDEX idx_vas_order_handler ON vas_order (handler_id)"))
  32. except Exception:
  33. pass # 索引已存在时忽略
  34. # 为 settlement_status 补建索引
  35. try:
  36. await conn.execute(text("CREATE INDEX idx_vas_order_settlement_status ON vas_order (settlement_status)"))
  37. except Exception:
  38. pass
  39. # 2. 检查 vas_product 是否有 min_floor_price
  40. res_prod = await conn.execute(text("SHOW COLUMNS FROM vas_product"))
  41. existing_prod_cols = {row[0] for row in res_prod.fetchall()}
  42. if "min_floor_price" not in existing_prod_cols:
  43. logger.info("➕ [MIGRATION] Adding column 'min_floor_price' to table 'vas_product'...")
  44. await conn.execute(text("ALTER TABLE vas_product ADD COLUMN min_floor_price INT NOT NULL DEFAULT 0 COMMENT '内部最低底价(分)'"))
  45. if "developer_id" not in existing_prod_cols:
  46. logger.info("➕ [MIGRATION] Adding column 'developer_id' to table 'vas_product'...")
  47. await conn.execute(text("ALTER TABLE vas_product ADD COLUMN developer_id VARCHAR(128) NULL COMMENT '该产品默认脚本开发者'"))
  48. # 3. 检查 plugins 是否有 class_name 和 protocol
  49. res_plug = await conn.execute(text("SHOW COLUMNS FROM plugins"))
  50. existing_plug_cols = {row[0] for row in res_plug.fetchall()}
  51. if "class_name" not in existing_plug_cols:
  52. logger.info("➕ [MIGRATION] Adding column 'class_name' to table 'plugins'...")
  53. await conn.execute(text("ALTER TABLE plugins ADD COLUMN class_name VARCHAR(128) NOT NULL DEFAULT '' COMMENT '插件入口类名' AFTER filename"))
  54. if "protocol" not in existing_plug_cols:
  55. logger.info("➕ [MIGRATION] Adding column 'protocol' to table 'plugins'...")
  56. await conn.execute(text("ALTER TABLE plugins ADD COLUMN protocol VARCHAR(64) NOT NULL DEFAULT 'IPlugin' COMMENT '插件协议标识' AFTER class_name"))
  57. # 4. 检查 vas_payment 表 provider 列类型是否已扩充支持 creem 等新渠道
  58. res_pay = await conn.execute(text("SHOW COLUMNS FROM vas_payment LIKE 'provider'"))
  59. pay_col = res_pay.fetchone()
  60. if pay_col and "enum" in str(pay_col[1]).lower():
  61. logger.info("➕ [MIGRATION] Modifying 'provider' column in 'vas_payment' to VARCHAR(64)...")
  62. await conn.execute(text("ALTER TABLE vas_payment MODIFY COLUMN provider VARCHAR(64) NOT NULL COMMENT '支付服务商: stripe, wechat, alipay, creem'"))
  63. logger.info("✅ [MIGRATION] Settlement & Plugin schema migration completed successfully.")
  64. except Exception as e:
  65. logger.error(f"❌ [MIGRATION ERROR] Failed to run settlement migrations: {e}", exc_info=True)