AiDOPWarehouse/ai-dop-platform/tools/apply_formula_structuring_ddl.py

# -*- coding: utf-8 -*-
"""第三批：公式结构化 DDL — 幂等可重跑。

1) 新建 ado_smart_ops_business_fact 业务事实字典表
2) 为 ado_smart_ops_kpi_master 增加 3 列：FormulaExpr / FormulaPreview / FormulaRefs

保留旧 Formula / CalcRule / DopFields 字段不动（Q3-B 向下兼容）。
"""
from __future__ import annotations
import sys
import pymysql

sys.stdout.reconfigure(encoding='utf-8')

CONN = dict(
    host='123.60.180.165', port=3306,
    user='aidopremote', password='1234567890aiDOP#',
    database='aidopdev', charset='utf8mb4', autocommit=True,
)

FACT_TABLE_DDL = """
CREATE TABLE IF NOT EXISTS `ado_smart_ops_business_fact` (
  `Id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `FactCode` varchar(64) NOT NULL COMMENT '事实编码（英文，稳定）',
  `FactName` varchar(200) NOT NULL COMMENT '事实名称（中文，展示）',
  `ModuleCode` varchar(20) NULL COMMENT '归属模块 S1~S9 或 COMMON',
  `Unit` varchar(50) NULL COMMENT '单位',
  `AggType` varchar(20) NULL COMMENT '聚合类型 COUNT/SUM/AVG/MAX/MIN/LATEST',
  `DataSource` varchar(200) NULL COMMENT '预期数据来源（文字说明）',
  `Description` text NULL COMMENT '说明/备注',
  `IsEnabled` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
  `SortNo` int NOT NULL DEFAULT 0 COMMENT '排序号',
  `TenantId` bigint NOT NULL COMMENT '租户 ID',
  `CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `UpdatedAt` datetime NULL COMMENT '更新时间',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `uk_fact_code_tenant` (`FactCode`, `TenantId`),
  KEY `idx_fact_module_tenant` (`ModuleCode`, `TenantId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='业务事实字典（指标公式引用的分子/分母来源）';
"""

KPIM_COLS = [
    ("FormulaExpr", "ALTER TABLE `ado_smart_ops_kpi_master` ADD COLUMN `FormulaExpr` varchar(500) NULL COMMENT '结构化表达式 @MetricCode/$FactCode 及算符常量'"),
    ("FormulaPreview", "ALTER TABLE `ado_smart_ops_kpi_master` ADD COLUMN `FormulaPreview` varchar(1000) NULL COMMENT '人读预览（引用替换为中文名）'"),
    ("FormulaRefs", "ALTER TABLE `ado_smart_ops_kpi_master` ADD COLUMN `FormulaRefs` varchar(500) NULL COMMENT '引用清单 JSON {metrics:[],facts:[]}'"),
]


def table_exists(cur, table: str) -> bool:
    cur.execute("SELECT COUNT(*) AS c FROM information_schema.tables WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=%s", (table,))
    return cur.fetchone()[0] > 0


def col_exists(cur, table: str, col: str) -> bool:
    cur.execute("SELECT COUNT(*) AS c FROM information_schema.columns WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=%s AND COLUMN_NAME=%s", (table, col))
    return cur.fetchone()[0] > 0


def main() -> None:
    conn = pymysql.connect(**CONN)
    with conn.cursor() as cur:
        # 1) business fact
        if table_exists(cur, 'ado_smart_ops_business_fact'):
            print("[SKIP] ado_smart_ops_business_fact 已存在")
        else:
            cur.execute(FACT_TABLE_DDL)
            print("[OK]   创建 ado_smart_ops_business_fact")

        # 2) kpi master new columns
        for col, ddl in KPIM_COLS:
            if col_exists(cur, 'ado_smart_ops_kpi_master', col):
                print(f"[SKIP] kpi_master.{col} 已存在")
            else:
                cur.execute(ddl)
                print(f"[OK]   新增 kpi_master.{col}")

        # 验证
        cur.execute("SHOW COLUMNS FROM `ado_smart_ops_kpi_master`")
        cols = {r[0] for r in cur.fetchall()}
        missing = [c for c, _ in KPIM_COLS if c not in cols]
        if missing:
            raise RuntimeError(f"新增列缺失：{missing}")
        print("\n[DONE] DDL 全部就绪。")
    conn.close()


if __name__ == "__main__":
    main()