# -*- coding: utf-8 -*-
|
"""重新生成 docs/sql/config_export_all_20260928.sql（全量重生成，替换 0918 版）。
|
|
阶段一：当前源库 ruoyi-vue-pro 全库 416 张表 CREATE TABLE + 6 类配置数据（mysqldump 字面量）。
|
阶段二：沿用 0918 文件的跨库身份/菜单/OAuth2 补导段（逐字复用）。
|
"""
|
import re
|
import subprocess
|
import sys
|
|
MYSQL_BIN = r"D:\MySQL\mysql-8.0.31-winx64\bin"
|
DB = "ruoyi-vue-pro"
|
USER = "root"
|
PWD = "1234"
|
|
BASE_DIR = r"D:\develop\XingRuan\MOM\mom-pro2-after\docs\sql"
|
SRC_0918 = BASE_DIR + r"\config_export_all_20260918.sql"
|
OUT = BASE_DIR + r"\config_export_all_20260928.sql"
|
|
MYSQL = MYSQL_BIN + r"\mysql.exe"
|
MYSQLDUMP = MYSQL_BIN + r"\mysqldump.exe"
|
|
COMMON = ["--default-character-set=utf8mb4", "--no-tablespaces", "--skip-comments",
|
"--skip-add-locks", "--skip-disable-keys", "--skip-triggers",
|
"--column-statistics=0", "--set-gtid-purged=OFF", "--single-transaction"]
|
|
# ---------------- 6 类配置表（表名, 过滤条件 None=全表） ----------------
|
SECTIONS = [
|
("① 协同审批流程（引擎元数据+定义，仅激活流程，剔历史/运行/身份）", [
|
("act_ge_property", None),
|
("act_id_property", None),
|
("act_re_procdef", "SUSPENSION_STATE_=1"),
|
("act_re_deployment", "ID_ IN (SELECT DEPLOYMENT_ID_ FROM act_re_procdef WHERE SUSPENSION_STATE_=1)"),
|
("act_ge_bytearray",
|
"DEPLOYMENT_ID_ IN (SELECT DEPLOYMENT_ID_ FROM act_re_procdef WHERE SUSPENSION_STATE_=1) "
|
"OR NAME_ IN ('source','source-extra')"),
|
("act_re_model", None),
|
("bpm_process_definition_info", "deleted=0"),
|
]),
|
("② 协同（业务附件配置 + 分类 + 表单 + 扩展配置）", [
|
("bpm_business_attach", "deleted=0"),
|
("bpm_category", "deleted=0"),
|
("bpm_form", "deleted=0"),
|
("bpm_user_group", "deleted=0"),
|
("bpm_process_listener", "deleted=0"),
|
("bpm_process_expression", "deleted=0"),
|
]),
|
("③ BI 决策大屏 / go-view", [
|
("bi_dashboard", "deleted=0"),
|
("bi_chart_config", "deleted=0"),
|
("bi_data_source_config", "deleted=0"),
|
("bi_warehouse_coordinate", "deleted=0"),
|
("report_go_view_project", "deleted=0"),
|
]),
|
("④ MES 编码规则（剔流水记录）", [
|
("mes_md_auto_code_rule", "deleted=0"),
|
("mes_md_auto_code_part", "deleted=0"),
|
]),
|
("⑤ 系统配置主数据（字典/租户/公司配置；剔身份/菜单/日志）", [
|
("system_dict_type", "deleted=0"),
|
("system_dict_data", "deleted=0"),
|
("system_tenant", "deleted=0"),
|
("system_tenant_package", "deleted=0"),
|
("system_company_config", "deleted=0"),
|
]),
|
("⑥ AI 配置（仅模型+key）+ 基础设施配置管理", [
|
("ai_model", "deleted=0"),
|
("ai_api_key", "deleted=0"),
|
("infra_config", "deleted=0"),
|
("infra_job", "deleted=0"),
|
("infra_file_config", "deleted=0"),
|
("infra_data_source_config", "deleted=0"),
|
("infra_codegen_table", "deleted=0"),
|
("infra_codegen_column", "deleted=0"),
|
]),
|
]
|
|
|
def run(cmd):
|
p = subprocess.run(cmd, capture_output=True)
|
if p.returncode != 0:
|
sys.stderr.write("FAILED: %s\n%s\n" % (" ".join(cmd), p.stderr.decode("utf-8", "replace")))
|
sys.exit(1)
|
return p.stdout.decode("utf-8")
|
|
|
def strip_mysqldump_noise(text):
|
out = []
|
for line in text.splitlines():
|
if line.startswith("/*!") or line.startswith("--"):
|
continue
|
out.append(line)
|
return out
|
|
|
def fetch_tables():
|
sql = ("SELECT table_name FROM information_schema.tables "
|
"WHERE table_schema='%s' AND table_type='BASE TABLE' ORDER BY table_name" % DB)
|
out = run([MYSQL, "-u" + USER, "-p" + PWD, "--default-character-set=utf8mb4", "-N", "-B", "-e", sql])
|
return [l.strip() for l in out.splitlines() if l.strip()]
|
|
|
def fetch_view_count():
|
sql = ("SELECT COUNT(*) FROM information_schema.views WHERE table_schema='%s'" % DB)
|
out = run([MYSQL, "-u" + USER, "-p" + PWD, "--default-character-set=utf8mb4", "-N", "-B", "-e", sql])
|
return int(out.strip())
|
|
|
def dump_ddl(tables):
|
"""一次导出全部表结构，切分成 {表名: ddl块}。"""
|
out = run([MYSQLDUMP, "-u" + USER, "-p" + PWD] + COMMON + ["--no-data", DB] + tables)
|
lines = strip_mysqldump_noise(out)
|
blocks = {}
|
cur = None
|
buf = []
|
for line in lines:
|
m = re.match(r"^DROP TABLE IF EXISTS `([^`]+)`;", line)
|
if m:
|
if cur:
|
blocks[cur] = "\n".join(buf).rstrip()
|
cur = m.group(1)
|
buf = [line]
|
elif cur is not None:
|
buf.append(line)
|
if cur:
|
blocks[cur] = "\n".join(buf).rstrip()
|
return blocks
|
|
|
def dump_data(tables_filters):
|
"""tables_filters: [(table, where)] 相同 where 的分组调用；返回 {表名: insert语句}。"""
|
groups = {}
|
for t, w in tables_filters:
|
if w is None:
|
groups.setdefault(None, []).append(t)
|
else:
|
groups.setdefault(w, []).append(t)
|
data = {}
|
for where, tables in groups.items():
|
cmd = [MYSQLDUMP, "-u" + USER, "-p" + PWD] + COMMON + \
|
["--no-create-info", "--complete-insert", "--hex-blob", "--order-by-primary"]
|
if where is not None:
|
cmd.append("--where=" + where)
|
cmd.append(DB)
|
cmd += tables
|
out = run(cmd)
|
lines = strip_mysqldump_noise(out)
|
stmt_lines = []
|
for line in lines:
|
if not line.strip():
|
continue
|
if line.startswith("INSERT INTO "):
|
stmt_lines = [line]
|
elif stmt_lines:
|
stmt_lines.append(line)
|
else:
|
sys.stderr.write("UNEXPECTED line: %s\n" % line[:120])
|
continue
|
stmt = "\n".join(stmt_lines)
|
if stmt.rstrip().endswith(";"):
|
m = re.match(r"^INSERT INTO `([^`]+)`", stmt)
|
data[m.group(1)] = stmt.rstrip()
|
stmt_lines = []
|
return data
|
|
|
def fetch_counts(tables_filters):
|
parts = []
|
for t, w in tables_filters:
|
parts.append("SELECT '%s' AS t, COUNT(*) AS c FROM `%s`%s"
|
% (t, t, (" WHERE " + w) if w else ""))
|
sql = " UNION ALL ".join(parts)
|
out = run([MYSQL, "-u" + USER, "-p" + PWD, "--default-character-set=utf8mb4", "-N", "-B", DB, "-e", sql])
|
counts = {}
|
for line in out.splitlines():
|
if not line.strip():
|
continue
|
k, v = line.split("\t")
|
counts[k] = int(v)
|
return counts
|
|
|
def load_stage2_block():
|
with open(SRC_0918, encoding="utf-8") as f:
|
lines = f.read().splitlines()
|
for i, line in enumerate(lines):
|
if "阶段二：补导身份" in line:
|
start = i - 1 # 上方 =============== 横线
|
return "\n".join(lines[start:]).rstrip() + "\n"
|
raise RuntimeError("0918 文件未找到阶段二段落")
|
|
|
HEADER = """-- =======================================================================
|
-- 文件：config_export_all_20260928.sql
|
-- 用途：ruoyi-vue-pro「全库表结构 + 6 类配置数据」导出，供同结构全新库初始化
|
-- 阶段一：全库 {n_tables} 张表 CREATE TABLE + 6 类配置数据（字面量，自包含，不依赖源库在线）
|
-- 阶段二：跨库补导 菜单/角色/用户/OAuth2 等身份数据（执行期从源库拷贝）
|
-- 来源库：ruoyi-vue-pro
|
-- 基线：2026-09-18 版（419 张表）→ 本次为**全量重生成**（不再基于旧文件打补丁）：
|
-- 直接按当前源库最新状态重导，一次性覆盖 0918 之后全部结构与配置漂移。
|
-- 本次变更（相对 0918，2026-09-28）：
|
-- ① 表结构：419 → {n_tables} 张
|
-- - 删除 6 张历史备份表（bpm_bak_20260904_bytearray/category/deployment/model/procdef/procinfo，源库已无）
|
-- + 新增 3 张表（after_sale_problem、after_sale_visit、bpm_process_auto_approve_config，仅结构，源库无数据）
|
-- + 10 张表 DDL 与 0918 不同，以当前源库为准（after_sale_ticket、mes_qc_indicator、mes_qc_indicator_result_detail、
|
-- mes_qc_ipqc、mes_qc_iqc、mes_qc_oqc、mes_qc_rqc、mes_qc_template、mes_qc_template_indicator、mes_wm_return_issue_line）
|
-- ② 配置数据以当前源库为准重导（下列为 0918 声明值 → 本次导出值）：
|
-- - system_dict_type 241→{n_dicttype}、system_dict_data 1157→{n_dictdata}（含 hrm_holiday_type 节假日类型等新增字典）
|
-- - system_company_config 0→1、bi_chart_config 36→37、bi_warehouse_coordinate 1→2、ai_api_key 1→3、bpm_user_group 3→4
|
-- - bpm_business_attach 11→4、bpm_category 22→15、bpm_form 4→3、bpm_process_listener 1→0（源库已删除的行不再导出）
|
-- - 协同流程定义按源库当前**激活**集合重导（{n_procdef} 个流程定义 + 对应部署/字节资源 + 设计态模型）
|
-- ③ 菜单/权限节点（节假日配置 6014/60141-60145、各模块导入按钮 1075311/1075417-1075420 等）由阶段二
|
-- 跨库全量拷贝自动携带，无需改动本文件数据段；在此登记以说明本版文件确实覆盖这些变更。
|
-- 验证：{verify}
|
-- 范围：全库 BASE TABLE 共 {n_tables} 张，全部输出 CREATE TABLE；
|
-- 仅以下配置类表带数据(INSERT)，其余业务/系统/运行历史表只保留空表结构：
|
-- ① 协同审批流程（引擎元数据+定义，仅激活流程，剔历史/运行/身份）：act_ge_property、act_id_property、act_re_procdef、act_re_deployment、act_ge_bytearray、act_re_model、bpm_process_definition_info
|
-- ② 协同（业务附件配置 + 分类 + 表单 + 扩展配置）：bpm_business_attach、bpm_category、bpm_form、bpm_user_group、bpm_process_listener、bpm_process_expression
|
-- ③ BI 决策大屏 / go-view：bi_dashboard、bi_chart_config、bi_data_source_config、bi_warehouse_coordinate、report_go_view_project
|
-- ④ MES 编码规则（剔流水记录）：mes_md_auto_code_rule、mes_md_auto_code_part
|
-- ⑤ 系统配置主数据（字典/租户/公司配置；剔身份/菜单/日志）：system_dict_type、system_dict_data、system_tenant、system_tenant_package、system_company_config
|
-- ⚠ 身份/权限类表（system_users/role/menu/role_menu/user_role/dept/post/oauth2_client）此处仅建空表结构，数据由文件末尾"阶段二"跨库补导。
|
-- ⑥ AI 配置（仅模型+key）+ 基础设施配置管理：ai_model、ai_api_key、infra_config、infra_job、infra_file_config、infra_data_source_config、infra_codegen_table、infra_codegen_column
|
-- 过滤：协同流程仅保留激活(SUSPENSION_STATE_=1)定义及其部署资源（bpmn/png 字节）；
|
-- 设计态模型 act_re_model 与模型源码字节（NAME_=source/source-extra）按源库全量导出（含个别已删除流程残留的模型，
|
-- 源库实际存在即保留，供流程设计器打开）；运行期变量字节（var-*/hist.var-*）一律不导出；
|
-- 其余配置表仅导 deleted=0；编码规则剔除流水记录 mes_md_auto_code_record。
|
-- ⚠ 脚本含全库 DROP TABLE IF EXISTS，会把除本脚本建表外的所有表删除重建，
|
-- 业务表数据将全部清空，仅可在全新/可覆盖的空库执行！
|
-- =======================================================================
|
"""
|
|
VERIFY_PLACEHOLDER = "（待空库整份执行验证后回填；见文件末尾生成备注）"
|
|
|
def main():
|
tables = fetch_tables()
|
views = fetch_view_count()
|
if views:
|
print("WARNING: 源库存在 %d 个视图，本生成器未处理" % views)
|
|
ddl = dump_ddl(tables)
|
missing = [t for t in tables if t not in ddl]
|
extra = [t for t in ddl if t not in tables]
|
if missing or extra:
|
print("DDL 集合不一致 missing=%s extra=%s" % (missing, extra))
|
sys.exit(1)
|
print("tables=%d ddl_blocks=%d" % (len(tables), len(ddl)))
|
|
all_cfg = [tf for _, tfs in SECTIONS for tf in tfs]
|
cfg_names = [t for t, _ in all_cfg]
|
counts = fetch_counts(all_cfg)
|
data = dump_data(all_cfg)
|
for t in cfg_names:
|
if counts.get(t, 0) > 0 and t not in data:
|
print("MISSING data for %s (count=%d)" % (t, counts[t]))
|
sys.exit(1)
|
print("config tables=%d, with data=%d" % (len(cfg_names), len(data)))
|
|
rest = [t for t in tables if t not in set(cfg_names)]
|
print("rest tables=%d" % len(rest))
|
|
parts = []
|
header = HEADER.format(
|
n_tables=len(tables),
|
n_dicttype=counts["system_dict_type"],
|
n_dictdata=counts["system_dict_data"],
|
n_procdef=counts["act_re_procdef"],
|
verify=VERIFY_PLACEHOLDER,
|
)
|
parts.append(header)
|
parts.append("SET NAMES utf8mb4;\nSET FOREIGN_KEY_CHECKS = 0;\n"
|
"SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\n\n")
|
|
for title, tfs in SECTIONS:
|
parts.append("\n-- ***********************************************************************\n")
|
parts.append("-- %s（DDL + 数据）\n" % title)
|
parts.append("-- ***********************************************************************\n\n")
|
for t, w in tfs:
|
n = counts.get(t, 0)
|
parts.append("-- ============ 表：%s（导出 %d 行） ============\n" % (t, n))
|
parts.append(ddl[t] + "\n")
|
if n > 0:
|
parts.append(data[t] + "\n")
|
parts.append("\n")
|
|
parts.append("\n-- ***********************************************************************\n")
|
parts.append("-- 其余 %d 张表（仅表结构，无数据）\n" % len(rest))
|
parts.append("-- ***********************************************************************\n\n")
|
for t in rest:
|
parts.append("-- ============ 表：%s（仅 DDL，无数据） ============\n" % t)
|
parts.append(ddl[t] + "\n\n")
|
|
parts.append("\nSET FOREIGN_KEY_CHECKS = 1;\nSET SQL_MODE = @OLD_SQL_MODE;\n\n\n")
|
parts.append(load_stage2_block())
|
|
text = "".join(parts)
|
with open(OUT, "w", encoding="utf-8", newline="\n") as f:
|
f.write(text)
|
print("WROTE %s bytes=%d lines=%d" % (OUT, len(text.encode("utf-8")), text.count("\n")))
|
|
|
if __name__ == "__main__":
|
main()
|
