oms/backend/app/lib/shopDatabaseClient.js
liufumingren eb8c94eacf feat(shop): 新增数据库店铺类型与连接/选表/字段映射配置
- 迁移 shop_database_configs:一店一条,存数据库类型/IP/端口/库名/账号/密码、
  商品表/订单表/订单明细表名、三份字段映射与最近校验结果
- 新增 shopDatabase 控制器与 lib:服务端用 mysql2 连接客户库(8s 超时、utf8mb4),
  提供配置读取/保存、测试连通、连接拉表、拉表列表与表字段;表名走白名单校验
- 保存时连得上则校验表与列真实存在(不存在拒绝保存),连不上则保存并提示未校验
- 店铺新增支持 type=3 数据库店铺;删除店铺时一并清理数据库配置
- 前端:新增店铺可选「数据库店铺」,创建后自动打开配置弹窗;列表对数据库店铺
  显示「配置」,弹窗内填写连接信息、测试连通、连接拉表、选三张表并逐个字段映射
- 契约与路由:补数据库店铺配置相关接口定义

Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
2026-11-05 15:44:06 +08:00

147 lines
4.8 KiB
JavaScript
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.

'use strict';
const mysql = require('mysql2/promise');
const { DEFAULT_DB_TYPE, DEFAULT_PORT } = require('./shopDatabaseFields');
/** 连接超时(毫秒):客户自建库可能不可达,避免请求被长时间挂住 */
const CONNECT_TIMEOUT = 8000;
/** 表名 / 库名允许的字符(拉取到 SHOW TABLES 结果后会再次比对,这里是第一道校验) */
const SAFE_IDENTIFIER = /^[\w$.-]{1,64}$/;
/**
* 规范化连接参数:端口缺省 3306,去掉空白
* @param {Object} params 原始参数
* @return {Object} 规范化后的连接参数
*/
function normalizeParams(params = {}) {
const port = Number(params.port);
return {
db_type: String(params.db_type || DEFAULT_DB_TYPE).trim() || DEFAULT_DB_TYPE,
host: String(params.host || '').trim(),
port: Number.isInteger(port) && port > 0 && port <= 65535 ? port : DEFAULT_PORT,
database_name: String(params.database_name || '').trim(),
username: String(params.username || '').trim(),
password: params.password == null ? '' : String(params.password),
};
}
/**
* 校验连接参数是否齐备
* @param {Object} params normalizeParams 的结果
* @return {string|null} 错误提示,校验通过返回 null
*/
function validateParams(params) {
if (params.db_type !== 'mysql') return '数据库类型暂只支持 MySQL';
if (!params.host) return '请填写数据库地址(IP 或域名)';
if (!/^[\w.-]{1,255}$/.test(params.host)) return '数据库地址格式不正确';
if (!params.database_name) return '请填写数据库名';
if (!SAFE_IDENTIFIER.test(params.database_name)) return '数据库名格式不正确';
if (!params.username) return '请填写数据库账号';
return null;
}
/**
* 建立连接(仅用于测试连通 / 拉取表结构,用完必须关闭)
* @param {Object} params normalizeParams 的结果
* @return {Promise<Object>} mysql2 连接
*/
async function openConnection(params) {
return mysql.createConnection({
host: params.host,
port: params.port,
user: params.username,
password: params.password,
database: params.database_name,
connectTimeout: CONNECT_TIMEOUT,
multipleStatements: false,
// 客户库字符集不确定,统一按 utf8mb4 读取,避免中文收件人乱码
charset: 'utf8mb4',
});
}
/**
* 关闭连接(忽略关闭异常,避免掩盖原始错误)
* @param {Object} conn mysql2 连接
* @return {Promise<void>} 无
*/
async function closeQuietly(conn) {
if (!conn) return;
try {
await conn.end();
} catch {
// 忽略
}
}
/**
* 测试连通:取服务器版本,确认账号可读该库
* @param {Object} params normalizeParams 的结果
* @return {Promise<{success: boolean, message: string, server_version?: string}>} 测试结果
*/
async function testConnection(params) {
let conn = null;
const startedAt = Date.now();
try {
conn = await openConnection(params);
const [ rows ] = await conn.query('SELECT VERSION() AS version');
const version = rows && rows[0] ? String(rows[0].version) : '';
return {
success: true,
message: `连接成功(${Date.now() - startedAt} ms)`,
server_version: version,
};
} catch (err) {
return { success: false, message: `连接失败:${err && err.message ? err.message : '未知错误'}` };
} finally {
await closeQuietly(conn);
}
}
/**
* 列出库内所有表
* @param {Object} conn mysql2 连接
* @param {Object} params normalizeParams 的结果(用于限定库名)
* @return {Promise<Array<{name: string, comment: string}>>} 表名与注释列表
*/
async function listTables(conn, params) {
const [ rows ] = await conn.query(
'SELECT TABLE_NAME AS name, TABLE_COMMENT AS comment FROM information_schema.TABLES'
+ ' WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = ? ORDER BY TABLE_NAME ASC',
[ params.database_name, 'BASE TABLE' ]
);
return rows.map(row => ({ name: String(row.name), comment: String(row.comment || '') }));
}
/**
* 列出表字段
* @param {Object} conn mysql2 连接
* @param {Object} params normalizeParams 的结果
* @param {string} table 表名(必须已在 listTables 结果中出现)
* @return {Promise<Array<{name: string, type: string, comment: string}>>} 字段列表
*/
async function listColumns(conn, params, table) {
const [ rows ] = await conn.query(
'SELECT COLUMN_NAME AS name, COLUMN_TYPE AS type, COLUMN_COMMENT AS comment'
+ ' FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ORDER BY ORDINAL_POSITION ASC',
[ params.database_name, table ]
);
return rows.map(row => ({
name: String(row.name),
type: String(row.type || ''),
comment: String(row.comment || ''),
}));
}
module.exports = {
CONNECT_TIMEOUT,
SAFE_IDENTIFIER,
normalizeParams,
validateParams,
openConnection,
closeQuietly,
testConnection,
listTables,
listColumns,
};