wuzilong
/
shanxia2.0


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303
							#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from __future__ import annotations

import argparse
import dataclasses
import html
import re
from pathlib import Path
from typing import Dict, List, Tuple


@dataclasses.dataclass(frozen=True)
class Column:
    name: str
    raw_def: str  # definition text after column name, normalized


@dataclasses.dataclass(frozen=True)
class Table:
    name: str
    columns: Dict[str, Column]


CREATE_TABLE_RE = re.compile(r"^\s*CREATE\s+TABLE\s+`(?P<name>crm_[^`]+)`\s*\(\s*$", re.IGNORECASE)
COL_RE = re.compile(r"^\s*`(?P<col>[^`]+)`\s+(?P<rest>.+)$")


def _normalize_ws(s: str) -> str:
    s = s.strip()
    s = re.sub(r"\s+", " ", s)
    return s


def parse_crm_tables(sql_text: str) -> Dict[str, Table]:
    """
    Parse Navicat-style MySQL DDL and extract CREATE TABLE blocks for crm_*.
    Only captures column lines beginning with backticked column names.
    """
    lines = sql_text.splitlines()
    tables: Dict[str, Table] = {}

    i = 0
    while i < len(lines):
        m = CREATE_TABLE_RE.match(lines[i])
        if not m:
            i += 1
            continue

        table_name = m.group("name")
        i += 1

        cols: Dict[str, Column] = {}
        while i < len(lines):
            line = lines[i].rstrip("\n")
            # End of CREATE TABLE block
            if re.search(r"^\s*\)\s*ENGINE=.*;\s*$", line, flags=re.IGNORECASE):
                break

            cm = COL_RE.match(line)
            if cm:
                col_name = cm.group("col").strip()
                rest = cm.group("rest").strip()
                # strip trailing comma
                if rest.endswith(","):
                    rest = rest[:-1].rstrip()
                cols[col_name] = Column(name=col_name, raw_def=_normalize_ws(rest))

            i += 1

        tables[table_name] = Table(name=table_name, columns=cols)

        # move past ENGINE line if present
        while i < len(lines) and not lines[i].strip().endswith(";"):
            i += 1
        if i < len(lines):
            i += 1

    return tables


def diff_tables(
    left: Dict[str, Table],
    right: Dict[str, Table],
) -> Tuple[List[str], List[str], Dict[str, Dict[str, Tuple[str | None, str | None]]]]:
    """
    Returns:
    - left_only_tables
    - right_only_tables
    - per_table_column_diffs: {table: {col: (left_def, right_def)}}
      where any of left_def/right_def can be None (missing), or both present but different.
    """
    left_only = sorted(set(left.keys()) - set(right.keys()))
    right_only = sorted(set(right.keys()) - set(left.keys()))

    common = sorted(set(left.keys()) & set(right.keys()))
    per_table: Dict[str, Dict[str, Tuple[str | None, str | None]]] = {}

    for t in common:
        lcols = left[t].columns
        rcols = right[t].columns

        all_cols = sorted(set(lcols.keys()) | set(rcols.keys()))
        diffs: Dict[str, Tuple[str | None, str | None]] = {}
        for c in all_cols:
            ldef = lcols.get(c).raw_def if c in lcols else None
            rdef = rcols.get(c).raw_def if c in rcols else None
            if ldef != rdef:
                diffs[c] = (ldef, rdef)
        if diffs:
            per_table[t] = diffs

    return left_only, right_only, per_table


def _h(s: str) -> str:
    return html.escape(s, quote=True)


def render_html_report(
    left_name: str,
    right_name: str,
    left_only_tables: List[str],
    right_only_tables: List[str],
    per_table_column_diffs: Dict[str, Dict[str, Tuple[str | None, str | None]]],
) -> str:
    diff_table_count = len(per_table_column_diffs)
    diff_col_count = sum(len(v) for v in per_table_column_diffs.values())

    parts: List[str] = []
    parts.append("<!doctype html>")
    parts.append('<html lang="zh-CN">')
    parts.append("<head>")
    parts.append('<meta charset="utf-8" />')
    parts.append('<meta name="viewport" content="width=device-width,initial-scale=1" />')
    parts.append("<title>CRM 表结构差异报告</title>")
    parts.append(
        """
<style>
  :root{
    --bg:#0b1220;
    --panel:#0f1b2d;
    --panel2:#0c1626;
    --text:#e5e7eb;
    --muted:#94a3b8;
    --border:#1f2a3b;
    --danger:#ef4444;
    --danger-bg:rgba(239,68,68,.12);
    --ok:#10b981;
  }
  body{margin:0;background:var(--bg);color:var(--text);font:14px/1.5 -apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,Helvetica,Arial,"PingFang SC","Hiragino Sans GB","Microsoft YaHei",sans-serif}
  .wrap{max-width:1200px;margin:0 auto;padding:24px}
  .header{display:flex;align-items:flex-end;justify-content:space-between;gap:16px;margin-bottom:16px}
  .title{font-size:20px;font-weight:700}
  .sub{color:var(--muted);font-size:12px}
  .card{background:linear-gradient(180deg,var(--panel),var(--panel2));border:1px solid var(--border);border-radius:12px;padding:16px;margin:12px 0}
  .grid{display:grid;grid-template-columns:1fr 1fr;gap:12px}
  @media (max-width: 900px){.grid{grid-template-columns:1fr}}
  .kpi{display:flex;gap:12px;flex-wrap:wrap}
  .kpi .item{background:rgba(255,255,255,.03);border:1px solid var(--border);border-radius:10px;padding:10px 12px}
  .kpi .num{font-weight:800;font-size:18px}
  .kpi .lab{color:var(--muted);font-size:12px;margin-top:2px}
  h2{margin:0 0 10px 0;font-size:16px}
  ul{margin:0;padding-left:18px}
  a{color:#60a5fa;text-decoration:none}
  a:hover{text-decoration:underline}
  table{width:100%;border-collapse:collapse;border:1px solid var(--border);border-radius:10px;overflow:hidden}
  th,td{padding:10px 10px;border-bottom:1px solid var(--border);vertical-align:top}
  th{background:rgba(255,255,255,.04);text-align:left;color:#cbd5e1;font-size:12px;letter-spacing:.02em}
  tr:last-child td{border-bottom:none}
  .diff-row td{background:var(--danger-bg)}
  .diff{color:var(--danger);font-weight:700}
  .mono{font-family:ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,"Liberation Mono","Courier New",monospace;white-space:pre-wrap;word-break:break-word}
  .pill{display:inline-block;padding:2px 8px;border-radius:999px;border:1px solid var(--border);color:var(--muted);font-size:12px}
  .toplinks{display:flex;gap:10px;flex-wrap:wrap}
</style>
""".strip()
    )
    parts.append("</head>")
    parts.append("<body>")
    parts.append('<div class="wrap">')
    parts.append('<div class="header">')
    parts.append('<div>')
    parts.append('<div class="title">CRM 表结构差异报告</div>')
    parts.append(
        f'<div class="sub">仅比较 <span class="pill">crm_*</span> 表的字段定义（类型/长度、NULL、DEFAULT、COMMENT 等属性）。</div>'
    )
    parts.append("</div>")
    parts.append(
        f'<div class="sub">{_h(left_name)} vs {_h(right_name)}</div>'
    )
    parts.append("</div>")

    parts.append('<div class="card kpi">')
    parts.append(
        f'<div class="item"><div class="num">{len(left_only_tables)}</div><div class="lab">{_h(left_name)} 独有表</div></div>'
    )
    parts.append(
        f'<div class="item"><div class="num">{len(right_only_tables)}</div><div class="lab">{_h(right_name)} 独有表</div></div>'
    )
    parts.append(
        f'<div class="item"><div class="num">{diff_table_count}</div><div class="lab">同名但字段有差异的表</div></div>'
    )
    parts.append(
        f'<div class="item"><div class="num">{diff_col_count}</div><div class="lab">字段差异条目</div></div>'
    )
    parts.append("</div>")

    parts.append('<div class="card grid">')
    parts.append("<div>")
    parts.append(f"<h2>{_h(left_name)} 比 {_h(right_name)} 多的表</h2>")
    if left_only_tables:
        parts.append("<ul>")
        for t in left_only_tables:
            parts.append(f"<li class='mono'>{_h(t)}</li>")
        parts.append("</ul>")
    else:
        parts.append(f"<div class='sub'>无</div>")
    parts.append("</div>")
    parts.append("<div>")
    parts.append(f"<h2>{_h(right_name)} 比 {_h(left_name)} 多的表</h2>")
    if right_only_tables:
        parts.append("<ul>")
        for t in right_only_tables:
            parts.append(f"<li class='mono'>{_h(t)}</li>")
        parts.append("</ul>")
    else:
        parts.append(f"<div class='sub'>无</div>")
    parts.append("</div>")
    parts.append("</div>")

    if per_table_column_diffs:
        parts.append('<div class="card">')
        parts.append("<h2>同名表字段差异（红色为差异）</h2>")
        parts.append('<div class="toplinks sub">')
        for t in sorted(per_table_column_diffs.keys()):
            parts.append(f"<a href='#{_h(t)}' class='mono'>{_h(t)}</a>")
        parts.append("</div>")
        parts.append("</div>")

    for t in sorted(per_table_column_diffs.keys()):
        diffs = per_table_column_diffs[t]
        parts.append(f"<div class='card' id='{_h(t)}'>")
        parts.append(f"<h2 class='mono'>{_h(t)}</h2>")
        parts.append("<table>")
        parts.append("<thead>")
        parts.append(
            f"<tr><th style='width:220px'>字段</th><th>{_h(left_name)}</th><th>{_h(right_name)}</th></tr>"
        )
        parts.append("</thead>")
        parts.append("<tbody>")
        for col in sorted(diffs.keys()):
            ldef, rdef = diffs[col]
            ltxt = ldef if ldef is not None else "(缺失)"
            rtxt = rdef if rdef is not None else "(缺失)"
            parts.append("<tr class='diff-row'>")
            parts.append(f"<td class='mono diff'>{_h(col)}</td>")
            parts.append(f"<td class='mono diff'>{_h(ltxt)}</td>")
            parts.append(f"<td class='mono diff'>{_h(rtxt)}</td>")
            parts.append("</tr>")
        parts.append("</tbody>")
        parts.append("</table>")
        parts.append("</div>")

    parts.append("</div>")
    parts.append("</body>")
    parts.append("</html>")
    return "\n".join(parts)


def main() -> int:
    p = argparse.ArgumentParser(description="Compare crm_* tables between two SQL dumps and output an HTML report.")
    p.add_argument("--left", required=True, help="Left SQL file path (e.g. shanxia2.0.sql)")
    p.add_argument("--right", required=True, help="Right SQL file path (e.g. sx.sql)")
    p.add_argument("--out", required=True, help="Output HTML path")
    p.add_argument("--left-name", default="shanxia2.0", help="Label for left side")
    p.add_argument("--right-name", default="crm(sx)", help="Label for right side")
    args = p.parse_args()

    left_text = Path(args.left).read_text(encoding="utf-8", errors="ignore")
    right_text = Path(args.right).read_text(encoding="utf-8", errors="ignore")

    left_tables = parse_crm_tables(left_text)
    right_tables = parse_crm_tables(right_text)

    left_only, right_only, per_table = diff_tables(left_tables, right_tables)
    report = render_html_report(args.left_name, args.right_name, left_only, right_only, per_table)

    out_path = Path(args.out)
    out_path.parent.mkdir(parents=True, exist_ok=True)
    out_path.write_text(report, encoding="utf-8")

    print(f"Parsed tables: left={len(left_tables)} right={len(right_tables)}")
    print(f"Left-only tables: {len(left_only)} Right-only tables: {len(right_only)}")
    print(f"Tables with column diffs: {len(per_table)}; diff items: {sum(len(v) for v in per_table.values())}")
    print(f"Wrote HTML: {out_path}")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())