mes-client/UAS_MES_STW/scripts/extract_db_templates.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
从 Oracle PL/SQL 存储过程源码中提取 v_o_errmsg 赋值语句，
生成 I18n/Templates.xml 可用的正则模板条目。

用法：
    python extract_db_templates.py <input.sql> [<input2.sql> ...] -o output.xml

生成的 XML 形如：
    <Template zh="序列号:(.*?) 已被工单(.*?)绑定"
              en="SN:{0} has already been bound to WO {1}"
              vi="SN:{0} đã được gán vào WO {1}"
              id="SN:{0} sudah terikat ke WO {1}"/>
"""
import argparse
import re
import os
import sys
from xml.sax.saxutils import escape


def tokenize_assignment(expr: str):
    """
    把形如 'xxx'||var||'yyy'||var||'zzz' 的表达式拆成：
    [('txt', 'xxx'), ('var', 'var'), ('txt', 'yyy'), ...]
    """
    tokens = []
    i = 0
    expr = expr.strip()
    while i < len(expr):
        # 跳过空白和 ||
        if expr[i] in " \t\r\n":
            i += 1
            continue
        if expr.startswith("||", i):
            i += 2
            continue

        # 字符串字面量
        if expr[i] == "'":
            j = i + 1
            literal = []
            while j < len(expr):
                if expr[j] == "'" and j + 1 < len(expr) and expr[j + 1] == "'":
                    literal.append("'")
                    j += 2
                elif expr[j] == "'":
                    j += 1
                    break
                else:
                    literal.append(expr[j])
                    j += 1
            tokens.append(("txt", "".join(literal)))
            i = j
            continue

        # 变量 / 函数调用 / 表达式
        # 取到下一个 || 或字符串字面量或分号为止
        j = i
        while j < len(expr):
            if expr[j] in " \t\r\n":
                pass
            elif expr.startswith("||", j):
                break
            elif expr[j] == "'":
                break
            elif expr[j] == ";":
                break
            j += 1
        var_expr = expr[i:j].strip()
        if var_expr:
            tokens.append(("var", var_expr))
        i = j

    return tokens


def build_template(tokens):
    """
    根据 token 序列生成中文正则模式和英文/越文/印尼文占位符模板。
    """
    fixed_parts = []
    var_count = 0
    has_variable = False

    for kind, value in tokens:
        if kind == "txt":
            fixed_parts.append(re.escape(value))
        else:
            has_variable = True
            fixed_parts.append(r"(.*?)")
            var_count += 1

    if not has_variable:
        return None, None  # 纯静态提示，不需要模板

    zh_pattern = "".join(fixed_parts)
    placeholders = "{" + "} {".join(str(i) for i in range(var_count)) + "}"

    # 默认英文模板：保留固定中文占位，后续需人工翻译
    fixed_texts = [v for k, v in tokens if k == "txt"]
    en_parts = []
    var_idx = 0
    for kind, value in tokens:
        if kind == "txt":
            # 这里先用原中文占位，实际使用时应替换为英文
            en_parts.append(value)
        else:
            en_parts.append("{" + str(var_idx) + "}")
            var_idx += 1
    en_template = "".join(en_parts)

    return zh_pattern, en_template


def extract_assignments(sql_text: str):
    """提取所有 v_o_errmsg := ... ; 语句。"""
    # 去掉 PL/SQL 行注释
    sql_text = re.sub(r"--.*?\n", "\n", sql_text)
    # 去掉 /* */ 块注释
    sql_text = re.sub(r"/\*.*?\*/", "", sql_text, flags=re.DOTALL)

    # 匹配 v_o_errmsg:=...;（允许跨行）
    pattern = re.compile(
        r"v_o_errmsg\s*:=\s*(.*?);",
        re.IGNORECASE | re.DOTALL,
    )
    return [m.group(1).strip() for m in pattern.finditer(sql_text)]


def escape_attr(value: str) -> str:
    """XML 属性转义。"""
    return escape(value, {'"': "&quot;"})


def main():
    parser = argparse.ArgumentParser(description="从 PL/SQL 提取动态错误模板")
    parser.add_argument("sql_files", nargs="+", help="输入的 .sql 文件路径")
    parser.add_argument("-o", "--output", default="Templates.xml", help="输出 XML 文件")
    args = parser.parse_args()

    all_templates = []
    seen = set()

    for path in args.sql_files:
        if not os.path.exists(path):
            print(f"[WARN] File not found: {path}", file=sys.stderr)
            continue
        with open(path, "r", encoding="utf-8") as f:
            text = f.read()

        for expr in extract_assignments(text):
            # 跳过纯函数调用，如 SQLERRM、dbms_utility.format_error_backtrace
            if re.match(r"^[A-Za-z0-9_\(\)\.\s\|\|]+$", expr) and "'" not in expr:
                continue
            tokens = tokenize_assignment(expr)
            zh_pattern, en_template = build_template(tokens)
            if zh_pattern and zh_pattern not in seen:
                seen.add(zh_pattern)
                all_templates.append((zh_pattern, en_template))

    lines = [
        '<?xml version="1.0" encoding="utf-8"?>',
        '<!-- 由 scripts/extract_db_templates.py 从存储过程自动生成 -->',
        '<!-- 请把 en/vi/id 属性中的中文占位文本替换为真实翻译 -->',
        '<Templates>',
    ]
    for zh, en in all_templates:
        lines.append(
            f'  <Template zh="{escape_attr(zh)}"'
            f' en="{escape_attr(en)}"'
            f' vi="{escape_attr(en)}"'
            f' id="{escape_attr(en)}"/>'
        )
    lines.append("</Templates>")

    with open(args.output, "w", encoding="utf-8") as f:
        f.write("\n".join(lines))

    print(f"Generated {len(all_templates)} templates -> {args.output}")


if __name__ == "__main__":
    main()