Files
aibridge/modules/data_interface.py
2026-05-18 09:28:15 +08:00

579 lines
21 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
"""数据接口模块
对应前端源码: api/systemData/dataInterface.ts
关键机制(基于后端源码分析 DataInterfaceServiceImpl.java):
- SQL 占位符: {参数名},最终替换为 ? 由 PreparedStatement 执行
- 参数通过 Preview 端点的 paramList 传递(sourceType=1 用 defaultValue)
- action=1 为增删改(不检查敏感词),action=3 为查询
- 系统变量: @snowFlakeID(雪花ID), @userId, @organizeId, @keyword, @pageSize, @offsetSize 等
- isPostPosition 必须为 0,否则 Preview 时 handlePostVariate 因 null 拆箱 NPE
- SQL 模板中不能包含 {} 空字面量(会被当作参数占位符解析)
- 多租户隔离: DI Preview 通过 dbLinkId 指定的数据库连接执行 SQL,
dbLinkId=0 在多租户环境下连接主库,部分表(如 base_data_interface)
的数据可能仅通过 MyBatis-Plus 的租户过滤可访问
- type: 1=SQL操作, 2=静态数据, 3=API操作
- hasPage=1 启用分页,需同时配置 dataCountJson 数量统计SQL
- 分页变量: @offsetSize=(当前页-1)*每页条数, @pageSize=每页条数, @keyword=关键词
参考:
- 后端: DataInterfaceServiceImpl.java(executeSql/customizationParameter/infoToId)
- 前端: api/systemData/dataInterface.ts
- Swagger: http://127.0.0.1:30000/doc.html#/home(仅本地可用)
- 文档: aibridge/docs/datainterface/
28个示例接口 (base_data_interface f_creator_time < 2026-10-1):
1. 83ae1f5e5c26447b8c4849ead352b048 - 当前用户 (SQL, {@userId})
2. a0c3186292df4fe2842f66dee0ea7729 - 查询当前组织 (SQL, 多表JOIN)
3. fdfd02c7628a4ea4a0b125463e7933e0 - 查询当前组织以及子组织 (SQL)
4. ebcc44be142e43b795c0d769abd6d25a - 用户列表 (SQL, 多表JOIN)
5. cd6fe911f90048f0bba7ef9f38251d96 - 柱状图 (静态)
6. be312b18a1664d8787e0dfcd2412969f - 折线图 (静态)
7. 79e5455c2f0d4d8ba2b444249bae0490 - 饼图 (静态)
8. 8ce8ccf7a3bf48a79a0c2bff20b46726 - 象形图 (静态)
9. 78814d0a32fa41ae8427b708bfadb808 - 雷达图 (静态)
10. 04d910fb47f4469e907a5c85949329cb - 散点图 (静态)
11. 3044325436d347af9b59a617f10e3ab6 - 漏斗图 (静态)
12. bd993fb8dc914076a3848b0c121b17ca - 查询当前用户以及下属 (SQL)
13. 360711881444294853 - 获取订单商品接口 (API, GET)
14. 395176542063304069 - 视图表数据 (SQL, 视图查询)
15. 395178064327220613 - 列表树形表格 (SQL, F_Id/F_PId)
16. 395538362724780101 - 访问jnpf接口认证示例 (API, 内部调用)
17. 398809921702874629 - 菜单 (SQL, base_module)
18. 398826053293854213 - 系统菜单接口(示例)(API, 8参数)
19. 400305221865071237 - 测试 (SQL, 带参数)
20. 420587863533428357 - 行政区划 (SQL, {parentId})
21. 469125039078305029 - API真分页 (API, hasPage=1)
22. 469125798494792965 - SQL真分页 (SQL, LIMIT分页)
23. 469471490920740869 - 添加公告接口 (API, POST)
24. 469492643924081285 - 树形sql_字段列表 (SQL, 树形)
25. 469493539378625157 - 下拉多参_字段列表 (SQL, 多参数)
26. 469493805800815237 - 区域下拉补全_字段列表 (SQL, 联想)
27. 469493919109937797 - 测试级联_字段列表 (静态, 级联)
28. 469500704067746437 - 当前组织_字段列表 (SQL, {@organizeId})
"""
import json
from typing import Any, Dict, List, Optional
from aibridge.client import JNPFClient
from aibridge.models import APIResponse
class DataInterfaceAPI:
"""数据接口 API
提供数据接口的 CRUD 管理,以及通过 Preview 端点执行参数化 SQL 的能力。
典型用法::
di = DataInterfaceAPI(client)
# 创建查询接口
di_id = di.create_sql_interface(
full_name="按编码查询",
en_code="query_by_code",
sql="SELECT * FROM base_table WHERE f_code = {code} AND f_delete_mark = 0",
parameters=[{"field": "code", "fieldName": "编码", "dataType": "varchar",
"required": 1, "sourceType": 1}],
action=3,
)
# 执行查询
result = di.execute(di_id, {"code": "ABC001"})
for row in result:
print(row)
"""
def __init__(self, client: JNPFClient):
self._client = client
self._prefix = "/api/system/DataInterface"
# ==================== 基础 CRUD ====================
def get_list(self, data: dict = None) -> APIResponse:
"""获取接口列表(分页)
注意: 必须传 category 参数,否则返回空列表。
Args:
data: 查询参数,常用字段:
- currentPage, pageSize: 分页
- category: 分类 ID(必填)
- keyword: 关键字搜索
"""
return self._client.request("GET", self._prefix, params=data)
def get_selector(self) -> APIResponse:
"""获取接口列表下拉框"""
return self._client.request("GET", f"{self._prefix}/Selector")
def create(self, data: dict) -> APIResponse:
"""新建接口"""
return self._client.request("POST", self._prefix, data=data)
def update(self, data: dict) -> APIResponse:
"""修改接口"""
di_id = data.get("id")
return self._client.request("PUT", f"{self._prefix}/{di_id}", data=data)
def get_info(self, di_id: str) -> APIResponse:
"""获取接口信息"""
return self._client.request("GET", f"{self._prefix}/{di_id}")
def delete(self, di_id: str) -> APIResponse:
"""删除接口"""
return self._client.request("DELETE", f"{self._prefix}/{di_id}")
def copy(self, di_id: str) -> APIResponse:
"""复制接口"""
return self._client.request("POST", f"{self._prefix}/{di_id}/Actions/Copy")
# ==================== 执行与预览 ====================
def preview(self, di_id: str, data: dict = None) -> APIResponse:
"""预览/执行接口
Args:
di_id: 接口 ID
data: 请求体,查询接口通常传:
{"paramList": [{"field": "参数名", "defaultValue": "值",
"dataType": "varchar", "sourceType": 1}]}
"""
return self._client.request("POST", f"{self._prefix}/{di_id}/Actions/Preview", data=data or {})
def execute(self, di_id: str, params: dict) -> List[Dict[str, Any]]:
"""通过 Preview 端点带参数执行数据接口(简化版)
自动将 params dict 转换为 paramList 格式。
Args:
di_id: 接口 ID
params: 参数字典,如 {"code": "ABC001", "name": "测试"}
Returns:
查询接口返回行列表 (action=3);
增删改接口返回空列表 (action=1,正常行为)。
Raises:
RuntimeError: 执行失败时抛出
"""
param_list = [
{
"field": key,
"defaultValue": str(value) if value is not None else "",
"dataType": "varchar",
"sourceType": 3,
"required": 0,
}
for key, value in params.items()
]
resp = self.preview(di_id, {"paramList": param_list})
if resp.code != 200:
raise RuntimeError(f"接口执行失败: code={resp.code}, msg={resp.msg}")
data = resp.data if resp.data else []
return data if isinstance(data, list) else [data]
def get_param(self, di_id: str) -> APIResponse:
"""获取数据接口参数"""
return self._client.request("GET", f"{self._prefix}/GetParam/{di_id}")
def export_data(self, di_id: str) -> APIResponse:
"""导出数据接口数据"""
return self._client.request("GET", f"{self._prefix}/{di_id}/Actions/Export")
def get_log(self, di_id: str, data: dict = None) -> APIResponse:
"""获取数据接口调用日志"""
return self._client.request("GET", f"{self._prefix}Log/{di_id}", params=data)
def get_data_select(self, data: dict) -> APIResponse:
"""获取接口列表数据"""
interface_id = data.get("interfaceId")
return self._client.request("POST", f"{self._prefix}/{interface_id}/Actions/List", data=data)
def get_data_info_by_ids(self, di_id: str, data: dict) -> APIResponse:
"""获取多条接口详情数据"""
return self._client.request("POST", f"{self._prefix}/{di_id}/Actions/InfoByIds", data=data)
def get_selector_list(self, data: dict = None) -> APIResponse:
"""获取接口列表(工作流选择用,带分页)"""
return self._client.request("GET", f"{self._prefix}/getList", params=data)
def get_fields(self, di_id: str, data: dict = None) -> APIResponse:
"""获取接口字段"""
return self._client.request("POST", f"{self._prefix}/{di_id}/Actions/GetFields", data=data or {})
# ==================== 验证示例 ====================
# 场景1: SQL无参数查询 (当前用户)
# result = execute("83ae1f5e5c26447b8c4849ead352b048", {})
# 返回: [{'f_real_name': '管理员'}]
# 场景2: SQL带参数 (行政区划)
# result = execute("420587863533428357", {"parentId": "0"})
# 场景3: SQL分页查询
# result = execute("469125798494792965", {"keyword": ""})
# 返回分页格式: {"list": [...], "pagination": {"currentPage": 1, "pageSize": 20, "total": 100}}
# 场景4: 静态数据 (饼图)
# result = execute("79e5455c2f0d4d8ba2b444249bae0490", {})
# 返回: {"title": {...}, "series": [...]}
# 场景5: API调用 (需外部系统支持)
# result = execute("360711881444294853", {"keyword": ""})
# ==================== 扩展接口机制 ====================
# dataExceptionJson(异常验证脚本): 仅 type=3 (API) 接口支持
# - 返回 true: 继续处理结果
# - 返回 false: 重新调用API(最多3次重试)
# - 错误码: SYS121=HTTP失败, SYS122=结果为空, SYS123=JS异常
#
# dataJsJson(数据处理脚本): 所有类型(SQL/静态/API)都支持
# - 在最终返回前执行,可对结果进行转换、计算字段等处理
#
# 执行流程: checkRequestParams → callHTTP → handlePostVariate → dataExceptionJson → dataJsJson → 返回
# ==================== 高阶方法 ====================
@staticmethod
def build_payload(
full_name: str,
en_code: str,
sql: str,
parameters: List[dict],
category: str,
action: int = 1,
db_link_id: str = "0",
) -> dict:
"""构建数据接口创建 payload
固化了以下关键经验:
- isPostPosition 必须为 0(避免后端 NPE)
- dataConfigJson 包含完整的 sqlData/apiData/staticData 结构
- action: 1=增删改, 3=查询
Args:
full_name: 接口名称
en_code: 接口编码(同 category 下唯一)
sql: 参数化 SQL,使用 {参数名} 作为占位符。
注意: SQL 中不能包含 {} 空字面量(会被解析为参数占位符)。
系统变量: @snowFlakeID, @userId, @organizeId 等。
parameters: 参数定义列表,每个元素:
{"field": "参数名", "fieldName": "中文名", "dataType": "varchar",
"required": 0/1, "sourceType": 1}
sourceType: 1=字段(defaultValue), 2=自定义(relationField),
3=空, 4=系统变量
category: 分类 ID
action: 1=增删改, 3=查询
db_link_id: 数据库连接 ID,"0" 表示主数据库。
注意: 多租户环境下 dbLinkId=0 连接主库,部分业务表可能不在主库中。
"""
return {
"fullName": full_name,
"enCode": en_code,
"type": 1,
"action": action,
"category": category,
"enabledMark": 1,
"hasPage": 0,
"isPostPosition": 0, # 必须为 0,否则 Preview 时后端 NPE
"dbLinkId": db_link_id,
"dataConfigJson": json.dumps({
"staticData": "",
"sqlData": {"dbLinkId": db_link_id, "sql": sql},
"apiData": {
"method": 1, "url": "",
"header": [], "query": [], "body": "", "bodyType": 0,
},
}, ensure_ascii=False),
"parameterJson": json.dumps(parameters, ensure_ascii=False),
"dataCountJson": "",
"dataEchoJson": "",
"dataJsJson": "",
"dataExceptionJson": "",
}
def create_sql_interface(
self,
full_name: str,
en_code: str,
sql: str,
parameters: List[dict],
category: str,
action: int = 1,
db_link_id: str = "0",
) -> str:
"""创建 SQL 数据接口并返回其 ID
封装了 create + list 查找的两步流程。
Args:
同 build_payload() 参数。
Returns:
创建成功后的接口 ID。
Raises:
RuntimeError: 创建失败或列表中未找到。
"""
payload = self.build_payload(
full_name=full_name,
en_code=en_code,
sql=sql,
parameters=parameters,
category=category,
action=action,
db_link_id=db_link_id,
)
resp = self.create(payload)
if not resp.success:
raise RuntimeError(f"创建接口失败: {resp.msg}")
# 创建 API 不直接返回 ID,需要通过列表查找
list_resp = self.get_list({
"category": category,
"keyword": en_code,
"pageSize": 10,
})
if not list_resp.success:
raise RuntimeError(f"查询接口列表失败: {list_resp.msg}")
for item in list_resp.data.get("list", []):
if item.get("enCode") == en_code:
return item.get("id")
raise RuntimeError(f"接口创建成功但列表中未找到: {en_code}")
def safe_delete(self, di_id: str) -> bool:
"""安全删除接口(不抛异常)
Returns:
True 删除成功,False 删除失败。
"""
try:
resp = self.delete(di_id)
return resp.success
except Exception:
return False
# ==================== 增删改扩展方法 ====================
def build_insert_payload(
self,
full_name: str,
en_code: str,
table_name: str,
fields: List[str],
parameters: List[dict],
category: str,
db_link_id: str = "0",
) -> dict:
"""构建 INSERT 数据接口 payload
Args:
full_name: 接口名称
en_code: 接口编码
table_name: 表名(不含前缀)
fields: 要插入的字段列表,如 ["f_name", "f_value"]
parameters: 参数定义列表
category: 分类 ID
db_link_id: 数据库连接 ID
"""
field_str = ", ".join(fields)
value_placeholders = ", ".join([f"{{{f[2:]}}}" for f in fields]) # f_name -> {name}
sql = f"INSERT INTO {table_name} ({field_str}) VALUES ({value_placeholders})"
return self.build_payload(
full_name=full_name,
en_code=en_code,
sql=sql,
parameters=parameters,
category=category,
action=1,
db_link_id=db_link_id,
)
def build_update_payload(
self,
full_name: str,
en_code: str,
table_name: str,
set_fields: List[str],
where_field: str,
parameters: List[dict],
category: str,
db_link_id: str = "0",
) -> dict:
"""构建 UPDATE 数据接口 payload
Args:
full_name: 接口名称
en_code: 接口编码
table_name: 表名
set_fields: 要更新的字段列表,如 ["f_name", "f_value"]
where_field: WHERE 条件字段(主键),如 "f_id"
parameters: 参数定义列表
category: 分类 ID
db_link_id: 数据库连接 ID
"""
set_clause = ", ".join([f"{f} = {{{f[2:]}}}" for f in set_fields])
sql = f"UPDATE {table_name} SET {set_clause} WHERE {where_field} = {{{where_field[2:]}}}"
return self.build_payload(
full_name=full_name,
en_code=en_code,
sql=sql,
parameters=parameters,
category=category,
action=2,
db_link_id=db_link_id,
)
def build_delete_payload(
self,
full_name: str,
en_code: str,
table_name: str,
where_field: str,
parameters: List[dict],
category: str,
db_link_id: str = "0",
) -> dict:
"""构建 DELETE 数据接口 payload
Args:
full_name: 接口名称
en_code: 接口编码
table_name: 表名
where_field: WHERE 条件字段(主键),如 "f_id"
parameters: 参数定义列表
category: 分类 ID
db_link_id: 数据库连接 ID
"""
sql = f"DELETE FROM {table_name} WHERE {where_field} = {{{where_field[2:]}}}"
return self.build_payload(
full_name=full_name,
en_code=en_code,
sql=sql,
parameters=parameters,
category=category,
action=4,
db_link_id=db_link_id,
)
def build_select_payload(
self,
full_name: str,
en_code: str,
sql: str,
parameters: List[dict],
category: str,
has_page: int = 0,
db_link_id: str = "0",
) -> dict:
"""构建 SELECT 数据接口 payload(支持分页)
Args:
full_name: 接口名称
en_code: 接口编码
sql: 查询 SQL(可包含 LIMIT 和分页变量)
parameters: 参数定义列表
category: 分类 ID
has_page: 是否分页(1=分页,0=不分页)
db_link_id: 数据库连接 ID
"""
payload = self.build_payload(
full_name=full_name,
en_code=en_code,
sql=sql,
parameters=parameters,
category=category,
action=3,
db_link_id=db_link_id,
)
payload["hasPage"] = has_page
return payload
def create_insert_interface(
self,
full_name: str,
en_code: str,
table_name: str,
fields: List[str],
parameters: List[dict],
category: str,
db_link_id: str = "0",
) -> str:
"""创建 INSERT 数据接口"""
payload = self.build_insert_payload(
full_name, en_code, table_name, fields, parameters, category, db_link_id
)
resp = self.create(payload)
if not resp.success:
raise RuntimeError(f"创建INSERT接口失败: {resp.msg}")
return self._find_di_id(en_code, category)
def create_update_interface(
self,
full_name: str,
en_code: str,
table_name: str,
set_fields: List[str],
where_field: str,
parameters: List[dict],
category: str,
db_link_id: str = "0",
) -> str:
"""创建 UPDATE 数据接口"""
payload = self.build_update_payload(
full_name, en_code, table_name, set_fields, where_field, parameters, category, db_link_id
)
resp = self.create(payload)
if not resp.success:
raise RuntimeError(f"创建UPDATE接口失败: {resp.msg}")
return self._find_di_id(en_code, category)
def create_delete_interface(
self,
full_name: str,
en_code: str,
table_name: str,
where_field: str,
parameters: List[dict],
category: str,
db_link_id: str = "0",
) -> str:
"""创建 DELETE 数据接口"""
payload = self.build_delete_payload(
full_name, en_code, table_name, where_field, parameters, category, db_link_id
)
resp = self.create(payload)
if not resp.success:
raise RuntimeError(f"创建DELETE接口失败: {resp.msg}")
return self._find_di_id(en_code, category)
def create_select_interface(
self,
full_name: str,
en_code: str,
sql: str,
parameters: List[dict],
category: str,
has_page: int = 0,
db_link_id: str = "0",
) -> str:
"""创建 SELECT 数据接口"""
payload = self.build_select_payload(
full_name, en_code, sql, parameters, category, has_page, db_link_id
)
resp = self.create(payload)
if not resp.success:
raise RuntimeError(f"创建SELECT接口失败: {resp.msg}")
return self._find_di_id(en_code, category)
def _find_di_id(self, en_code: str, category: str) -> str:
"""根据编码和分类查找 DI ID"""
list_resp = self.get_list({"category": category, "keyword": en_code, "pageSize": 10})
if not list_resp.success:
raise RuntimeError(f"查询接口列表失败: {list_resp.msg}")
for item in list_resp.data.get("list", []):
if item.get("enCode") == en_code:
return item.get("id")
raise RuntimeError(f"接口创建成功但列表中未找到: {en_code}")