304 lines
11 KiB
Python
304 lines
11 KiB
Python
#!/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())
|
|
|