#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ 导出 JNPF 平台库 j7 的结构和"按应用配置"数据备份。 设计原则(参考 jnpf-java-boot/jnpf-system/jnpf-system-controller SystemController#exportFile 涉及的 SystemExportVo): - **平台基础表**(base_user/base_role/base_organize/base_*/...共 123 张): 只导出 schema(CREATE TABLE / INDEX / COMMENT),**不**导出数据。 这些是 JNPF 平台自带的组织/权限/账号等数据,与 SSW 应用配置无关, 不应被打包进代码仓库。 - **SSW 三个应用相关表**:按 `f_system_id IN (...)` 或经 base_module 关联导出数据。 这些是平台"应用导入导出"功能会连带备份的内容,是 SSW 应用在平台上的配置。 涉及的应用 systemId: - sswflow 隐私计算平台:853028206192623813 - 可信数据空间 :855102009768939909 - 数据接入连接器 :855102050973782405 涉及的表(按应用维度): base_system - 应用主表 (按 systemId) base_module / base_module_button / base_module_column / base_module_form / base_module_data / base_module_authorize / base_module_scheme / base_module_link - 模块菜单 + 各种权限配置 (按 f_module_id IN 子查询) base_visual_dev / base_visual_alias / base_visual_release - 功能设计 + 别名 + 历史版本 base_portal - 门户配置 base_print_template / base_print_version - 打印模板 base_flow_template - 流程模板 输出: docs/db/0809/j7/ schema.sql - 123 张表结构 overview.json - 列/类型/注释 + 行数(SSW 应用数据行数;其他 0) data/ base_system.sql - sswflow/tds/tdc 三条应用记录 base_module*.sql - 关联菜单/权限数据 base_visual_*.sql - 功能设计数据 """ from __future__ import annotations import json import os import sys from datetime import datetime from typing import Any import psycopg2 import psycopg2.extras # ============================================================================ # 配置 # ============================================================================ DB_CONFIG = { "host": "127.0.0.1", "port": 15432, "user": "dacli", "password": "dacli123", "dbname": "j7", } SCHEMA = "public" OUTPUT_DIR = "/Users/xs/Desktop/ssw/docs/db/0809/j7" # SSW 三个应用的 systemId SSW_SYSTEM_IDS = ( "853028206192623813", # sswflow "855102009768939909", # tds "855102050973782405", # tdc ) # 应用配置相关表,按 systemId 直接导出 APP_TABLES_BY_SYSTEM_ID = [ "base_system", "base_module_data", # f_system_id 列 "base_visual_dev", "base_visual_release", "base_portal", "base_print_template", "base_print_version", "base_flow_template", "base_visual_screen", ] # 按 module_id 间接关联的表(通过 base_module WHERE f_system_id IN (...) 关联) APP_TABLES_BY_MODULE_ID = [ "base_module", "base_module_button", "base_module_column", "base_module_form", "base_module_authorize", "base_module_scheme", "base_module_link", "base_visual_alias", # 通过 visual_dev.id 关联 ] # ============================================================================ # 工具函数 # ============================================================================ def connect(): conn = psycopg2.connect( host=DB_CONFIG["host"], port=DB_CONFIG["port"], user=DB_CONFIG["user"], password=DB_CONFIG["password"], dbname=DB_CONFIG["dbname"], ) conn.set_session(readonly=True, autocommit=True) return conn def quote_ident(name: str) -> str: return '"' + name.replace('"', '""') + '"' def list_tables(conn) -> list[str]: """列出 public schema 下所有用户表(base_ 前缀的 JNPF 平台表)。""" with conn.cursor() as cur: cur.execute( """ SELECT table_name FROM information_schema.tables WHERE table_schema = %s AND table_type = 'BASE TABLE' AND table_name LIKE 'base\\_%%' ORDER BY table_name """, (SCHEMA,), ) return [r[0] for r in cur.fetchall()] def table_has_column(conn, table: str, column: str) -> bool: with conn.cursor() as cur: cur.execute( """ SELECT 1 FROM information_schema.columns WHERE table_schema = %s AND table_name = %s AND column_name = %s LIMIT 1 """, (SCHEMA, table, column), ) return cur.fetchone() is not None def get_table_columns(conn, table: str) -> list[dict[str, Any]]: """获取表的列信息(列名、类型、是否可空、默认值)。""" with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur: cur.execute( """ SELECT column_name, data_type, udt_name, is_nullable, column_default, character_maximum_length, numeric_precision, numeric_scale FROM information_schema.columns WHERE table_schema = %s AND table_name = %s ORDER BY ordinal_position """, (SCHEMA, table), ) return list(cur.fetchall()) def get_table_comments(conn, table: str) -> tuple[str | None, dict[str, str]]: """获取表注释 + 列注释。""" table_comment = None col_comments: dict[str, str] = {} with conn.cursor() as cur: cur.execute( """ SELECT obj_description(c.oid) AS tbl_comment FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = %s AND c.relname = %s """, (SCHEMA, table), ) row = cur.fetchone() if row: table_comment = row[0] cur.execute( """ SELECT a.attname, pg_catalog.col_description(a.attrelid, a.attnum) FROM pg_attribute a JOIN pg_class c ON c.oid = a.attrelid JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = %s AND c.relname = %s AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum """, (SCHEMA, table), ) for r in cur.fetchall(): if r[1]: col_comments[r[0]] = r[1] return table_comment, col_comments def get_indexes(conn, table: str) -> list[str]: """获取表的索引 DDL(不含主键约束,主键会在 CREATE TABLE 中)。""" with conn.cursor() as cur: cur.execute( """ SELECT indexdef FROM pg_indexes WHERE schemaname = %s AND tablename = %s AND indexname NOT IN ( SELECT constraint_name FROM information_schema.table_constraints WHERE table_schema = %s AND table_name = %s AND constraint_type = 'PRIMARY KEY' ) ORDER BY indexname """, (SCHEMA, table, SCHEMA, table), ) return [r[0] for r in cur.fetchall()] def get_pk_constraint(conn, table: str) -> str | None: """获取主键约束 DDL(可在 CREATE TABLE 中追加)。""" with conn.cursor() as cur: cur.execute( """ SELECT pg_get_constraintdef(c.oid), c.conname FROM pg_constraint c JOIN pg_class t ON t.oid = c.conrelid JOIN pg_namespace n ON n.oid = t.relnamespace WHERE n.nspname = %s AND t.relname = %s AND c.contype = 'p' """, (SCHEMA, table), ) row = cur.fetchone() if not row: return None return f'CONSTRAINT "{row[1]}" {row[0]}' def get_extra_constraints(conn, table: str) -> list[str]: """获取 unique / check 约束 DDL(不含主键、外键)。""" out: list[str] = [] with conn.cursor() as cur: cur.execute( """ SELECT pg_get_constraintdef(c.oid), c.conname, c.contype FROM pg_constraint c JOIN pg_class t ON t.oid = c.conrelid JOIN pg_namespace n ON n.oid = t.relnamespace WHERE n.nspname = %s AND t.relname = %s AND c.contype IN ('u','c') ORDER BY c.contype, c.conname """, (SCHEMA, table), ) for cdef, cname, _ctype in cur.fetchall(): out.append(f'ALTER TABLE {quote_ident(table)} ADD CONSTRAINT "{cname}" {cdef};') return out def get_pg_size_pretty(conn, table: str) -> str: with conn.cursor() as cur: cur.execute( "SELECT pg_size_pretty(pg_total_relation_size(%s))", (f"{SCHEMA}.{table}",), ) return cur.fetchone()[0] or "0 bytes" def count_rows(conn, table: str, where: str = "", params: tuple = ()) -> int: """统计行数。""" sql = f"SELECT count(*) FROM {quote_ident(table)}" if where: sql += " WHERE " + where with conn.cursor() as cur: cur.execute(sql, params) return cur.fetchone()[0] def escape_sql_value(v: Any) -> str: """把 Python 值转成 SQL 字面量。""" if v is None: return "NULL" if isinstance(v, bool): return "TRUE" if v else "FALSE" if isinstance(v, (int, float)): return str(v) if isinstance(v, datetime): return f"'{v.strftime('%Y-%m-%d %H:%M:%S')}'" s = str(v).replace("'", "''") return f"'{s}'" # ============================================================================ # Schema 输出 # ============================================================================ def write_table_schema(f, table: str, columns: list[dict], table_comment: str | None, pk_constraint: str | None = None): """把一张表的 CREATE TABLE 写到文件。""" f.write(f"\n-- Table: {table}\n") f.write(f"CREATE TABLE {quote_ident(table)} (\n") col_lines = [] for col in columns: parts = [f" {quote_ident(col['column_name'])}"] # 类型 if col["character_maximum_length"]: parts.append(f"varchar({col['character_maximum_length']})") elif col["data_type"] == "integer": parts.append("integer") elif col["data_type"] == "bigint": parts.append("bigint") elif col["data_type"] == "smallint": parts.append("smallint") elif col["data_type"] == "numeric": parts.append( f"numeric({col['numeric_precision']},{col['numeric_scale']})" ) elif col["data_type"] == "timestamp without time zone": parts.append("timestamp without time zone") elif col["data_type"] == "text": parts.append("text") elif col["data_type"] == "boolean": parts.append("boolean") elif col["data_type"] == "double precision": parts.append("double precision") elif col["data_type"] == "real": parts.append("real") elif col["data_type"] == "json": parts.append("json") elif col["data_type"] == "jsonb": parts.append("jsonb") else: parts.append(col["udt_name"]) if col["is_nullable"] == "NO": parts.append("NOT NULL") if col["column_default"] is not None: parts.append(f"DEFAULT {col['column_default']}") col_lines.append(" ".join(parts)) if pk_constraint: col_lines.append(f" {pk_constraint}") f.write(",\n".join(col_lines)) f.write("\n);\n\n") if table_comment: f.write(f"COMMENT ON TABLE {quote_ident(table)} IS '{table_comment.replace(chr(39), chr(39)*2)}';\n") def write_indexes(f, table: str, indexes: list[str]): for idx in indexes: f.write(idx + ";\n") def write_column_comments(f, table: str, col_comments: dict[str, str]): for col, comment in col_comments.items(): safe = comment.replace("'", "''") f.write(f"COMMENT ON COLUMN {quote_ident(table)}.{quote_ident(col)} IS '{safe}';\n") # ============================================================================ # 数据导出(按应用维度) # ============================================================================ def export_app_data(conn, table: str, where: str, params: tuple, out_path: str): """把符合 where 的行导出成 INSERT 语句。""" with conn.cursor() as cur: cur.execute( f"SELECT * FROM {quote_ident(table)} WHERE {where}", params, ) cols = [d[0] for d in cur.description] rows = cur.fetchall() with open(out_path, "w", encoding="utf-8") as f: f.write(f"-- j7.{table} rows={len(rows)} exported={datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n") if not rows: return 0 f.write("BEGIN;\n") col_list = ",".join(quote_ident(c) for c in cols) for row in rows: values = ",".join(escape_sql_value(v) for v in row) f.write(f"INSERT INTO {quote_ident(table)} ({col_list}) VALUES ({values});\n") f.write("COMMIT;\n") return len(rows) # ============================================================================ # 主流程 # ============================================================================ def main(): os.makedirs(OUTPUT_DIR, exist_ok=True) os.makedirs(os.path.join(OUTPUT_DIR, "data"), exist_ok=True) conn = connect() tables = list_tables(conn) print(f"Found {len(tables)} JNPF platform tables in j7.{SCHEMA}") overview: dict[str, Any] = { "database": "j7", "schema": SCHEMA, "note": "JNPF 平台库按 SSW 应用配置导出:所有 123 张表只导出 schema;仅 SSW 三个应用(sswflow/tds/tdc)相关的 base_module / base_visual_dev / base_system 等表导出数据。", "ssw_system_ids": list(SSW_SYSTEM_IDS), "exported_at": datetime.now().strftime("%Y-%m-%d %H:%M:%S"), "tables": [], "data_tables": [], } schema_path = os.path.join(OUTPUT_DIR, "schema.sql") with open(schema_path, "w", encoding="utf-8") as f: f.write(f"""-- ============================================================ -- Database: j7 (JNPF 7.0 平台主库) -- Schema snapshot at {overview['exported_at']} -- 仅导出表结构(CREATE TABLE / INDEX / COMMENT),不导出业务数据。 -- 应用相关数据见 data/ 子目录(按 SSW 三个应用 systemId 过滤)。 -- ============================================================ SET client_min_messages = warning; """) # 1. 所有表的 schema for table in tables: cols = get_table_columns(conn, table) tbl_comment, col_comments = get_table_comments(conn, table) indexes = get_indexes(conn, table) pk = get_pk_constraint(conn, table) extras = get_extra_constraints(conn, table) size = get_pg_size_pretty(conn, table) with open(schema_path, "a", encoding="utf-8") as f: f.write(f"\n-- ----------------------------------------------------------\n") f.write(f"-- Table: {table} ({size})\n") f.write(f"-- ----------------------------------------------------------\n") write_table_schema(f, table, cols, tbl_comment, pk) if indexes: write_indexes(f, table, indexes) if extras: f.write("\n") f.write("\n".join(extras) + "\n") if col_comments: write_column_comments(f, table, col_comments) overview["tables"].append({ "name": table, "size": size, "columns": [c["column_name"] for c in cols], "table_comment": tbl_comment, }) # 2. 应用相关数据导出 print("\nExporting SSW application data...") sys_ids_str = ",".join(f"'{s}'" for s in SSW_SYSTEM_IDS) module_ids_subquery = ( f"(SELECT f_id FROM {quote_ident('base_module')} " f"WHERE f_system_id IN ({sys_ids_str}))" ) visual_ids_subquery = ( f"(SELECT f_id FROM {quote_ident('base_visual_dev')} " f"WHERE f_system_id IN ({sys_ids_str}))" ) def emit_data(table: str, where: str, params: tuple): out_path = os.path.join(OUTPUT_DIR, "data", f"{table}.sql") rows = export_app_data(conn, table, where, params, out_path) overview["data_tables"].append({ "table": table, "rows": rows, "path": f"data/{table}.sql", "filter": where, }) print(f" {table:30s} rows={rows}") return rows # base_system(应用主表,只导出 SSW 三条) emit_data("base_system", f"f_id IN ({sys_ids_str})", ()) # base_module(菜单),按 systemId 过滤 emit_data("base_module", f"f_system_id IN ({sys_ids_str})", ()) # base_module_data / base_visual_dev / base_portal / ... 按 systemId # 只处理 j7 库实际存在的表,避免 flowable 子系统的表不存在导致崩溃 CANDIDATE_BY_SYSTEM_ID = [ "base_module_data", "base_visual_dev", "base_visual_release", "base_portal", "base_print_template", ] for t in CANDIDATE_BY_SYSTEM_ID: if t not in tables: print(f" {t:30s} skip (table does not exist in j7)") continue if not table_has_column(conn, t, "f_system_id"): print(f" {t:30s} skip (no f_system_id column)") continue emit_data(t, f"f_system_id IN ({sys_ids_str})", ()) # base_print_version 经 base_print_template 关联 if "base_print_version" in tables: print_tpl_sub = ( f"(SELECT f_id FROM {quote_ident('base_print_template')} " f"WHERE f_system_id IN ({sys_ids_str}))" ) emit_data("base_print_version", f"f_template_id IN {print_tpl_sub}", ()) # base_module_button/column/form/authorize/scheme/link 按 module_id 关联 for t in ["base_module_button", "base_module_column", "base_module_form", "base_module_authorize", "base_module_scheme", "base_module_link"]: try: emit_data(t, f"f_module_id IN {module_ids_subquery}", ()) except psycopg2.errors.UndefinedColumn: print(f" {t:30s} skip (no f_module_id column)") # base_visual_alias 通过 visual_dev.id 关联 if "base_visual_alias" in tables and table_has_column(conn, "base_visual_alias", "f_visual_id"): emit_data("base_visual_alias", f"f_visual_id IN {visual_ids_subquery}", ()) # overview.json with open(os.path.join(OUTPUT_DIR, "overview.json"), "w", encoding="utf-8") as f: json.dump(overview, f, ensure_ascii=False, indent=2) conn.close() print(f"\nDone. {len(tables)} tables schema + {len(overview['data_tables'])} app-data tables.") print(f" schema.sql {schema_path}") print(f" overview.json {os.path.join(OUTPUT_DIR, 'overview.json')}") print(f" data/ {os.path.join(OUTPUT_DIR, 'data')}") if __name__ == "__main__": main()