john
818 lines
30 KiB
JavaScript
818 lines
30 KiB
JavaScript
import fs from 'node:fs';
|
|
import path from 'node:path';
|
|
import { execFileSync } from 'node:child_process';
|
|
import { createPostgresUserDataSpaceService } from './postgres-user-data-space-service.mjs';
|
|
|
|
export const DATASET_REGISTRY_TABLE = '__page_data_datasets';
|
|
|
|
const DEFAULT_MAX_BYTES = 20 * 1024 * 1024;
|
|
const DEFAULT_QUERY_TIMEOUT_MS = 5000;
|
|
const DEFAULT_MAX_ROWS = 200;
|
|
|
|
function stripSqlComments(sql) {
|
|
return String(sql ?? '')
|
|
.replace(/--.*$/gm, '')
|
|
.replace(/\/\*[\s\S]*?\*\//g, '')
|
|
.trim();
|
|
}
|
|
|
|
export function assertSafeSqlIdentifier(name, label = '标识符') {
|
|
const value = String(name ?? '').trim();
|
|
if (!/^[A-Za-z_][A-Za-z0-9_]*$/.test(value)) {
|
|
throw Object.assign(new Error(`${label} 格式无效`), { code: 'invalid_identifier' });
|
|
}
|
|
return value;
|
|
}
|
|
|
|
export function rejectDangerousSql(sql, { readonly = false } = {}) {
|
|
const cleaned = stripSqlComments(sql);
|
|
if (!cleaned) throw new Error('SQL 不能为空');
|
|
if (cleaned.length > 20000) throw new Error('SQL 过长');
|
|
if (/^\s*\./m.test(cleaned)) {
|
|
throw new Error('SQL 不允许使用 sqlite3 dot command');
|
|
}
|
|
if (/\b(ATTACH|DETACH|LOAD_EXTENSION|VACUUM\s+INTO)\b/i.test(cleaned)) {
|
|
throw new Error('SQL 包含用户私有数据空间不允许的操作');
|
|
}
|
|
if (/\bPRAGMA\s+writable_schema\b/i.test(cleaned)) {
|
|
throw new Error('SQL 包含不允许的 PRAGMA');
|
|
}
|
|
if (readonly && !/^\s*(SELECT|WITH)\b/i.test(cleaned)) {
|
|
throw new Error('private_data_query 只允许 SELECT/WITH');
|
|
}
|
|
return cleaned;
|
|
}
|
|
|
|
export function normalizeDatasetConfig(raw, { fallbackName = null } = {}) {
|
|
const config = typeof raw === 'string' ? JSON.parse(raw) : raw;
|
|
if (!config || typeof config !== 'object') {
|
|
throw Object.assign(new Error('dataset 配置无效'), { code: 'invalid_dataset_config' });
|
|
}
|
|
const name = assertSafeSqlIdentifier(config.name ?? fallbackName, 'dataset 名称');
|
|
const table = assertSafeSqlIdentifier(config.table ?? config.table_name, 'dataset 表名');
|
|
const actions = Array.isArray(config.actions)
|
|
? config.actions.map((action) => String(action).trim()).filter(Boolean)
|
|
: [];
|
|
const columns = config.columns && typeof config.columns === 'object' ? config.columns : {};
|
|
for (const [action, fields] of Object.entries(columns)) {
|
|
if (!Array.isArray(fields)) {
|
|
throw Object.assign(new Error(`dataset 字段白名单无效：${action}`), { code: 'invalid_dataset_config' });
|
|
}
|
|
columns[action] = fields.map((field) => assertSafeSqlIdentifier(field, `${action} 字段`));
|
|
}
|
|
return {
|
|
name,
|
|
table,
|
|
description: String(config.description ?? '').trim(),
|
|
actions,
|
|
columns,
|
|
limits:
|
|
config.limits && typeof config.limits === 'object'
|
|
? {
|
|
maxRowsPerRead: Number(config.limits.maxRowsPerRead ?? DEFAULT_MAX_ROWS),
|
|
maxInsertBytes: Number(config.limits.maxInsertBytes ?? 8192),
|
|
}
|
|
: {
|
|
maxRowsPerRead: DEFAULT_MAX_ROWS,
|
|
maxInsertBytes: 8192,
|
|
},
|
|
};
|
|
}
|
|
|
|
function sqlLiteral(value) {
|
|
if (value === null || value === undefined) return 'NULL';
|
|
if (typeof value === 'number' && Number.isFinite(value)) return String(value);
|
|
if (typeof value === 'boolean') return value ? '1' : '0';
|
|
return `'${String(value).replace(/'/g, "''")}'`;
|
|
}
|
|
|
|
export function createUserDataSpaceService(options = {}) {
|
|
const workspaceRoot = path.resolve(String(options.workspaceRoot ?? '').trim());
|
|
if (!workspaceRoot) {
|
|
throw new Error('workspaceRoot 不能为空');
|
|
}
|
|
|
|
const userId = options.userId ? String(options.userId).trim() : null;
|
|
const isNodeTest = Boolean(process.env.NODE_TEST_CONTEXT);
|
|
const backend = String(
|
|
options.backend ?? process.env.MINDSPACE_USERDATA_BACKEND ?? (isNodeTest ? 'sqlite' : 'postgres'),
|
|
).trim().toLowerCase();
|
|
if (backend === 'sqlite' && !isNodeTest) {
|
|
throw Object.assign(
|
|
new Error('用户私有数据运行时已禁用 SQLite，只允许使用 PostgreSQL'),
|
|
{ code: 'sqlite_runtime_disabled' },
|
|
);
|
|
}
|
|
if (backend !== 'postgres' && backend !== 'sqlite') {
|
|
throw Object.assign(new Error(`不支持的用户数据后端：${backend}`), { code: 'invalid_userdata_backend' });
|
|
}
|
|
if (backend === 'postgres') {
|
|
if (!userId) throw new Error('PostgreSQL 用户数据空间要求 userId');
|
|
return createPostgresUserDataSpaceService({ ...options, userId });
|
|
}
|
|
const query = typeof options.query === 'function' ? options.query : null;
|
|
const sqliteBin = options.sqliteBin?.trim() || process.env.SQLITE_BIN?.trim() || 'sqlite3';
|
|
const maxBytes = Number(options.maxBytes ?? process.env.PRIVATE_DATA_MAX_BYTES ?? DEFAULT_MAX_BYTES);
|
|
const queryTimeoutMs = Number(
|
|
options.queryTimeoutMs ?? process.env.PRIVATE_DATA_QUERY_TIMEOUT_MS ?? DEFAULT_QUERY_TIMEOUT_MS,
|
|
);
|
|
const maxRows = Number(options.maxRows ?? process.env.PRIVATE_DATA_MAX_ROWS ?? DEFAULT_MAX_ROWS);
|
|
|
|
const privateDataDir = path.join(workspaceRoot, '.mindspace');
|
|
const privateDataDb = path.join(privateDataDir, 'private-data.sqlite');
|
|
|
|
function ensurePrivateDataDb() {
|
|
fs.mkdirSync(privateDataDir, { recursive: true });
|
|
if (!fs.existsSync(privateDataDb)) {
|
|
runSqlite(['-batch', privateDataDb, 'PRAGMA journal_mode=WAL; PRAGMA user_version = 1;']);
|
|
}
|
|
return privateDataDb;
|
|
}
|
|
|
|
function ensureReady() {
|
|
ensurePrivateDataDb();
|
|
return getInfo();
|
|
}
|
|
|
|
function privateDataSize() {
|
|
let total = 0;
|
|
for (const file of fs.existsSync(privateDataDir) ? fs.readdirSync(privateDataDir) : []) {
|
|
if (file === 'private-data.sqlite' || file.startsWith('private-data.sqlite-')) {
|
|
total += fs.statSync(path.join(privateDataDir, file)).size;
|
|
}
|
|
}
|
|
return total;
|
|
}
|
|
|
|
function runSqlite(args) {
|
|
return execFileSync(sqliteBin, args, {
|
|
encoding: 'utf8',
|
|
timeout: queryTimeoutMs,
|
|
maxBuffer: 1024 * 1024,
|
|
});
|
|
}
|
|
|
|
function sqliteScalar(sql) {
|
|
return Number(runSqlite(['-batch', '-noheader', privateDataDb, sql]).trim() || 0);
|
|
}
|
|
|
|
function runJsonQuery(sql) {
|
|
const output = runSqlite(['-json', privateDataDb, sql]);
|
|
return output.trim() ? JSON.parse(output) : [];
|
|
}
|
|
|
|
async function getQuotaState({ forUpdate = false, conn = null } = {}) {
|
|
if (!query || !userId) return null;
|
|
const db = conn ?? { query };
|
|
const [rows] = await db.query(
|
|
`SELECT id, quota_bytes, used_bytes, reserved_bytes, status
|
|
FROM h5_user_spaces
|
|
WHERE user_id = ?
|
|
LIMIT 1 ${forUpdate ? 'FOR UPDATE' : ''}`,
|
|
[userId],
|
|
);
|
|
const row = rows[0];
|
|
if (!row) return null;
|
|
const quotaBytes = Number(row.quota_bytes ?? 0);
|
|
const usedBytes = Number(row.used_bytes ?? 0);
|
|
const reservedBytes = Number(row.reserved_bytes ?? 0);
|
|
return {
|
|
id: row.id,
|
|
status: row.status,
|
|
quotaBytes,
|
|
usedBytes,
|
|
reservedBytes,
|
|
availableBytes: Math.max(0, quotaBytes - usedBytes - reservedBytes),
|
|
};
|
|
}
|
|
|
|
async function sqliteMaxPagePragmaForQuota(currentBytes) {
|
|
const quota = await getQuotaState();
|
|
if (!quota) return '';
|
|
if (quota.status !== 'active') throw new Error('用户空间不可写');
|
|
if (quota.availableBytes <= 0 && currentBytes >= maxBytes) {
|
|
throw new Error('用户私有数据空间配额不足');
|
|
}
|
|
const allowedSize = Math.min(maxBytes, currentBytes + quota.availableBytes);
|
|
const pageSize = sqliteScalar('PRAGMA page_size;') || 4096;
|
|
const currentPages = sqliteScalar('PRAGMA page_count;') || 1;
|
|
const maxPages = Math.max(currentPages, Math.max(1, Math.floor(allowedSize / pageSize)));
|
|
return `PRAGMA max_page_count=${maxPages};`;
|
|
}
|
|
|
|
async function syncPrivateDataQuota(deltaBytes) {
|
|
if (!query || !userId || !deltaBytes) return null;
|
|
const conn = await query.getConnection?.();
|
|
if (!conn?.beginTransaction) {
|
|
return null;
|
|
}
|
|
try {
|
|
await conn.beginTransaction();
|
|
const quota = await getQuotaState({ forUpdate: true, conn });
|
|
if (!quota) {
|
|
await conn.rollback();
|
|
return null;
|
|
}
|
|
if (quota.status !== 'active') throw new Error('用户空间不可写');
|
|
if (deltaBytes > quota.availableBytes) {
|
|
throw new Error(`用户私有数据空间配额不足：需要 ${deltaBytes} 字节，可用 ${quota.availableBytes} 字节`);
|
|
}
|
|
await conn.query(
|
|
`UPDATE h5_user_spaces
|
|
SET used_bytes = GREATEST(0, used_bytes + ?), updated_at = ?
|
|
WHERE id = ? AND user_id = ?`,
|
|
[deltaBytes, Date.now(), quota.id, userId],
|
|
);
|
|
await conn.commit();
|
|
return { deltaBytes };
|
|
} catch (err) {
|
|
await conn.rollback();
|
|
throw err;
|
|
} finally {
|
|
conn.release();
|
|
}
|
|
}
|
|
|
|
function ensureDatasetRegistryTable() {
|
|
ensurePrivateDataDb();
|
|
runSqlite([
|
|
'-batch',
|
|
privateDataDb,
|
|
`CREATE TABLE IF NOT EXISTS ${DATASET_REGISTRY_TABLE} (
|
|
name TEXT PRIMARY KEY,
|
|
table_name TEXT NOT NULL,
|
|
config_json TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);`,
|
|
]);
|
|
}
|
|
|
|
async function getInfo() {
|
|
ensurePrivateDataDb();
|
|
const size = privateDataSize();
|
|
const quota = await getQuotaState().catch(() => null);
|
|
return {
|
|
name: '用户私有数据空间',
|
|
database: '.mindspace/private-data.sqlite',
|
|
maxBytes,
|
|
sizeBytes: size,
|
|
quotaSyncEnabled: Boolean(query && userId),
|
|
quota: quota
|
|
? {
|
|
status: quota.status,
|
|
quotaBytes: quota.quotaBytes,
|
|
usedBytes: quota.usedBytes,
|
|
reservedBytes: quota.reservedBytes,
|
|
availableBytes: quota.availableBytes,
|
|
}
|
|
: null,
|
|
};
|
|
}
|
|
|
|
async function getSchema() {
|
|
ensurePrivateDataDb();
|
|
return runJsonQuery(
|
|
`SELECT m.name AS table_name, p.cid, p.name AS column_name, p.type, p."notnull" AS not_null, p.pk
|
|
FROM sqlite_master m
|
|
JOIN pragma_table_info(m.name) p
|
|
WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%'
|
|
ORDER BY m.name, p.cid`,
|
|
);
|
|
}
|
|
|
|
async function querySql(sql) {
|
|
ensurePrivateDataDb();
|
|
const cleaned = rejectDangerousSql(sql, { readonly: true });
|
|
const limited = `SELECT * FROM (${cleaned.replace(/;\s*$/, '')}) LIMIT ${maxRows}`;
|
|
return runJsonQuery(limited);
|
|
}
|
|
|
|
async function executeSql(sql) {
|
|
ensurePrivateDataDb();
|
|
const before = privateDataSize();
|
|
if (before > maxBytes) throw new Error('用户私有数据空间已超过大小限制');
|
|
const cleaned = rejectDangerousSql(sql);
|
|
const quotaPragma = await sqliteMaxPagePragmaForQuota(before);
|
|
runSqlite(['-batch', privateDataDb, `${quotaPragma}\n${cleaned}`]);
|
|
const after = privateDataSize();
|
|
if (after > maxBytes) {
|
|
throw new Error(`用户私有数据空间超过大小限制：${after}/${maxBytes} 字节`);
|
|
}
|
|
const delta = after - before;
|
|
const quotaSync = await syncPrivateDataQuota(delta);
|
|
return {
|
|
sizeBytes: after,
|
|
deltaBytes: delta,
|
|
quotaSynced: Boolean(quotaSync),
|
|
};
|
|
}
|
|
|
|
function listTableColumns(tableName) {
|
|
const table = assertSafeSqlIdentifier(tableName, '表名');
|
|
return runJsonQuery(`PRAGMA table_info(${table});`).map((row) => ({
|
|
name: row.name,
|
|
type: row.type,
|
|
notNull: Boolean(row.notnull),
|
|
pk: Boolean(row.pk),
|
|
}));
|
|
}
|
|
|
|
function tableHasColumn(tableName, columnName) {
|
|
return listTableColumns(tableName).some((column) => column.name === columnName);
|
|
}
|
|
|
|
function getDatasetRow(name) {
|
|
ensureDatasetRegistryTable();
|
|
const datasetName = assertSafeSqlIdentifier(name, 'dataset 名称');
|
|
const rows = runJsonQuery(
|
|
`SELECT name, table_name, config_json, created_at, updated_at
|
|
FROM ${DATASET_REGISTRY_TABLE}
|
|
WHERE name = ${sqlLiteral(datasetName)}
|
|
LIMIT 1`,
|
|
);
|
|
return rows[0] ?? null;
|
|
}
|
|
|
|
function getDataset(name) {
|
|
const row = getDatasetRow(name);
|
|
if (!row) return null;
|
|
const config = normalizeDatasetConfig(row.config_json, { fallbackName: row.name });
|
|
return {
|
|
...config,
|
|
table: config.table || row.table_name,
|
|
createdAt: row.created_at,
|
|
updatedAt: row.updated_at,
|
|
};
|
|
}
|
|
|
|
function listDatasets() {
|
|
ensureDatasetRegistryTable();
|
|
return runJsonQuery(
|
|
`SELECT name, table_name, config_json, created_at, updated_at
|
|
FROM ${DATASET_REGISTRY_TABLE}
|
|
ORDER BY name`,
|
|
).map((row) => {
|
|
const config = normalizeDatasetConfig(row.config_json, { fallbackName: row.name });
|
|
return {
|
|
...config,
|
|
table: config.table || row.table_name,
|
|
createdAt: row.created_at,
|
|
updatedAt: row.updated_at,
|
|
};
|
|
});
|
|
}
|
|
|
|
async function upsertDataset(configInput) {
|
|
const config = normalizeDatasetConfig(configInput);
|
|
ensureDatasetRegistryTable();
|
|
const columns = listTableColumns(config.table);
|
|
if (!columns.length) {
|
|
throw Object.assign(new Error(`dataset 对应表不存在：${config.table}`), { code: 'table_not_found' });
|
|
}
|
|
const payload = JSON.stringify(config);
|
|
const sql = `INSERT INTO ${DATASET_REGISTRY_TABLE} (name, table_name, config_json, updated_at)
|
|
VALUES (${sqlLiteral(config.name)}, ${sqlLiteral(config.table)}, ${sqlLiteral(payload)}, CURRENT_TIMESTAMP)
|
|
ON CONFLICT(name) DO UPDATE SET
|
|
table_name = excluded.table_name,
|
|
config_json = excluded.config_json,
|
|
updated_at = CURRENT_TIMESTAMP;`;
|
|
await executeSql(sql);
|
|
return getDataset(config.name);
|
|
}
|
|
|
|
function assertDatasetAction(dataset, action) {
|
|
if (!dataset.actions.includes(action)) {
|
|
throw Object.assign(new Error(`dataset 未授权 ${action}`), { code: 'action_not_allowed' });
|
|
}
|
|
}
|
|
|
|
function resolveReadColumns(dataset) {
|
|
const columns = dataset.columns?.read;
|
|
if (!Array.isArray(columns) || !columns.length) {
|
|
throw Object.assign(new Error('dataset 未配置可读字段'), { code: 'columns_not_allowed' });
|
|
}
|
|
return columns.map((column) => assertSafeSqlIdentifier(column, '读字段'));
|
|
}
|
|
|
|
function resolveInsertColumns(dataset, payload) {
|
|
const allowed = dataset.columns?.insert;
|
|
if (!Array.isArray(allowed) || !allowed.length) {
|
|
throw Object.assign(new Error('dataset 未配置可写字段'), { code: 'columns_not_allowed' });
|
|
}
|
|
const allowedSet = new Set(allowed.map((column) => assertSafeSqlIdentifier(column, '写字段')));
|
|
const keys = Object.keys(payload ?? {});
|
|
if (!keys.length) {
|
|
throw Object.assign(new Error('提交数据不能为空'), { code: 'invalid_payload' });
|
|
}
|
|
for (const key of keys) {
|
|
if (!allowedSet.has(key)) {
|
|
throw Object.assign(new Error(`字段未授权：${key}`), { code: 'columns_not_allowed' });
|
|
}
|
|
}
|
|
return keys.map((key) => assertSafeSqlIdentifier(key, '写字段'));
|
|
}
|
|
|
|
function resolveUpdateColumns(dataset, payload) {
|
|
const allowed = dataset.columns?.update;
|
|
if (!Array.isArray(allowed) || !allowed.length) {
|
|
throw Object.assign(new Error('dataset 未配置可更新字段'), { code: 'columns_not_allowed' });
|
|
}
|
|
const allowedSet = new Set(allowed.map((column) => assertSafeSqlIdentifier(column, '更新字段')));
|
|
const keys = Object.keys(payload ?? {});
|
|
if (!keys.length) {
|
|
throw Object.assign(new Error('更新数据不能为空'), { code: 'invalid_payload' });
|
|
}
|
|
for (const key of keys) {
|
|
if (!allowedSet.has(key)) {
|
|
throw Object.assign(new Error(`字段未授权：${key}`), { code: 'columns_not_allowed' });
|
|
}
|
|
}
|
|
return keys.map((key) => assertSafeSqlIdentifier(key, '更新字段'));
|
|
}
|
|
|
|
function buildDatasetWhereClause(table, { includeDeleted = false, rowScope = null } = {}) {
|
|
const parts = [];
|
|
if (!includeDeleted && tableHasColumn(table, 'deleted_at')) {
|
|
parts.push('deleted_at IS NULL');
|
|
}
|
|
if (rowScope?.whereClause) {
|
|
parts.push(rowScope.whereClause);
|
|
}
|
|
return parts.length ? `WHERE ${parts.join(' AND ')}` : '';
|
|
}
|
|
|
|
function readRowByIdForDataset(dataset, rowId, { rowScope = null, includeDeleted = false } = {}) {
|
|
assertDatasetAction(dataset, 'read');
|
|
const table = assertSafeSqlIdentifier(dataset.table, '表名');
|
|
const id = Number(rowId);
|
|
if (!Number.isFinite(id) || id <= 0) {
|
|
throw Object.assign(new Error('行 id 无效'), { code: 'invalid_row_id' });
|
|
}
|
|
const columns = resolveReadColumns(dataset);
|
|
const parts = [`id = ${id}`];
|
|
if (!includeDeleted && tableHasColumn(table, 'deleted_at')) {
|
|
parts.push('deleted_at IS NULL');
|
|
}
|
|
if (rowScope?.whereClause) {
|
|
parts.push(rowScope.whereClause);
|
|
}
|
|
const sql = `SELECT ${columns.map((column) => `"${column}"`).join(', ')}
|
|
FROM "${table}"
|
|
WHERE ${parts.join(' AND ')}
|
|
LIMIT 1`;
|
|
const rows = runJsonQuery(sql);
|
|
if (!rows.length) {
|
|
throw Object.assign(new Error('无权访问该行或行不存在'), { code: 'row_not_allowed' });
|
|
}
|
|
return rows[0];
|
|
}
|
|
|
|
function readRowsForDataset(dataset, { limit, offset, orderBy, orderDir, includeDeleted = false, rowScope = null } = {}) {
|
|
assertDatasetAction(dataset, 'read');
|
|
const columns = resolveReadColumns(dataset);
|
|
const table = assertSafeSqlIdentifier(dataset.table, '表名');
|
|
const maxLimit = Math.min(
|
|
Math.max(1, Number(dataset.limits?.maxRowsPerRead ?? maxRows)),
|
|
maxRows,
|
|
);
|
|
const safeLimit = Math.min(Math.max(1, Number(limit ?? maxLimit)), maxLimit);
|
|
const safeOffset = Math.max(0, Number(offset ?? 0));
|
|
const where = buildDatasetWhereClause(table, { includeDeleted, rowScope });
|
|
let orderClause = '';
|
|
if (orderBy) {
|
|
const column = assertSafeSqlIdentifier(orderBy, '排序字段');
|
|
if (!columns.includes(column)) {
|
|
throw Object.assign(new Error(`排序字段未授权：${column}`), { code: 'columns_not_allowed' });
|
|
}
|
|
const direction = String(orderDir ?? 'asc').toLowerCase() === 'desc' ? 'DESC' : 'ASC';
|
|
orderClause = `ORDER BY "${column}" ${direction}`;
|
|
} else if (columns.includes('id')) {
|
|
orderClause = 'ORDER BY "id" DESC';
|
|
}
|
|
const sql = `SELECT ${columns.map((column) => `"${column}"`).join(', ')}
|
|
FROM "${table}"
|
|
${where}
|
|
${orderClause}
|
|
LIMIT ${safeLimit} OFFSET ${safeOffset}`;
|
|
const rows = runJsonQuery(sql);
|
|
return { dataset, rows, limit: safeLimit, offset: safeOffset };
|
|
}
|
|
|
|
function readDatasetRows(datasetName, options = {}) {
|
|
const dataset = getDataset(datasetName);
|
|
if (!dataset) {
|
|
throw Object.assign(new Error('dataset 不存在'), { code: 'dataset_not_found' });
|
|
}
|
|
return readRowsForDataset(dataset, options);
|
|
}
|
|
|
|
function getStatsForDataset(dataset, { rowScope = null } = {}) {
|
|
assertDatasetAction(dataset, 'read');
|
|
const table = assertSafeSqlIdentifier(dataset.table, '表名');
|
|
const where = buildDatasetWhereClause(table, { rowScope });
|
|
const total = sqliteScalar(`SELECT COUNT(*) FROM "${table}" ${where};`);
|
|
return { dataset: { name: dataset.name, table: dataset.table }, total };
|
|
}
|
|
|
|
function getDatasetStats(datasetName) {
|
|
const dataset = getDataset(datasetName);
|
|
if (!dataset) {
|
|
throw Object.assign(new Error('dataset 不存在'), { code: 'dataset_not_found' });
|
|
}
|
|
return getStatsForDataset(dataset);
|
|
}
|
|
|
|
async function insertRowForDataset(dataset, payload, meta = {}) {
|
|
assertDatasetAction(dataset, 'insert');
|
|
const payloadBytes = Buffer.byteLength(JSON.stringify(payload ?? {}), 'utf8');
|
|
const maxInsertBytes = Number(dataset.limits?.maxInsertBytes ?? 8192);
|
|
if (payloadBytes > maxInsertBytes) {
|
|
throw Object.assign(new Error('提交数据过大'), { code: 'payload_too_large' });
|
|
}
|
|
const table = assertSafeSqlIdentifier(dataset.table, '表名');
|
|
const columns = resolveInsertColumns(dataset, payload);
|
|
const values = { ...payload };
|
|
if (tableHasColumn(table, 'created_at') && values.created_at == null) {
|
|
values.created_at = new Date().toISOString();
|
|
}
|
|
if (tableHasColumn(table, 'created_ip_hash') && values.created_ip_hash == null && meta.createdIpHash) {
|
|
values.created_ip_hash = meta.createdIpHash;
|
|
}
|
|
if (tableHasColumn(table, 'user_agent_hash') && values.user_agent_hash == null && meta.userAgentHash) {
|
|
values.user_agent_hash = meta.userAgentHash;
|
|
}
|
|
if (tableHasColumn(table, 'page_data_session_id') && values.page_data_session_id == null && meta.pageDataSessionId) {
|
|
values.page_data_session_id = meta.pageDataSessionId;
|
|
}
|
|
if (
|
|
meta.rowScope?.ownerColumn &&
|
|
tableHasColumn(table, meta.rowScope.ownerColumn) &&
|
|
values[meta.rowScope.ownerColumn] == null &&
|
|
meta.rowScope.visitorUserId
|
|
) {
|
|
values[meta.rowScope.ownerColumn] = meta.rowScope.visitorUserId;
|
|
}
|
|
let insertColumns = columns.filter((column) => values[column] !== undefined);
|
|
if (
|
|
meta.rowScope?.ownerColumn &&
|
|
tableHasColumn(table, meta.rowScope.ownerColumn) &&
|
|
values[meta.rowScope.ownerColumn] != null &&
|
|
!insertColumns.includes(meta.rowScope.ownerColumn)
|
|
) {
|
|
insertColumns.push(meta.rowScope.ownerColumn);
|
|
}
|
|
if (!insertColumns.length) {
|
|
throw Object.assign(new Error('提交数据不能为空'), { code: 'invalid_payload' });
|
|
}
|
|
const sql = `INSERT INTO "${table}" (${insertColumns.map((column) => `"${column}"`).join(', ')})
|
|
VALUES (${insertColumns.map((column) => sqlLiteral(values[column])).join(', ')});`;
|
|
await executeSql(sql);
|
|
const id = sqliteScalar('SELECT last_insert_rowid();');
|
|
let row = { id };
|
|
if (dataset.actions.includes('read')) {
|
|
const readBack = readRowsForDataset(dataset, {
|
|
limit: 1,
|
|
orderBy: 'id',
|
|
orderDir: 'desc',
|
|
rowScope: meta.rowScope?.whereClause ? meta.rowScope : null,
|
|
});
|
|
row = readBack.rows.find((item) => Number(item.id) === Number(id)) ?? readBack.rows[0] ?? row;
|
|
} else {
|
|
for (const column of insertColumns) {
|
|
row[column] = values[column];
|
|
}
|
|
}
|
|
return { dataset, row };
|
|
}
|
|
|
|
async function updateDatasetRow(datasetName, rowId, payload, meta = {}) {
|
|
const dataset = getDataset(datasetName);
|
|
if (!dataset) {
|
|
throw Object.assign(new Error('dataset 不存在'), { code: 'dataset_not_found' });
|
|
}
|
|
return updateRowForDataset(dataset, rowId, payload, meta);
|
|
}
|
|
|
|
async function softDeleteDatasetRow(datasetName, rowId, meta = {}) {
|
|
const dataset = getDataset(datasetName);
|
|
if (!dataset) {
|
|
throw Object.assign(new Error('dataset 不存在'), { code: 'dataset_not_found' });
|
|
}
|
|
return softDeleteRowForDataset(dataset, rowId, meta);
|
|
}
|
|
|
|
async function insertDatasetRow(datasetName, payload, meta = {}) {
|
|
const dataset = getDataset(datasetName);
|
|
if (!dataset) {
|
|
throw Object.assign(new Error('dataset 不存在'), { code: 'dataset_not_found' });
|
|
}
|
|
return insertRowForDataset(dataset, payload, meta);
|
|
}
|
|
|
|
async function updateRowForDataset(dataset, rowId, payload, meta = {}) {
|
|
assertDatasetAction(dataset, 'update');
|
|
const table = assertSafeSqlIdentifier(dataset.table, '表名');
|
|
const id = Number(rowId);
|
|
if (!Number.isFinite(id) || id <= 0) {
|
|
throw Object.assign(new Error('行 id 无效'), { code: 'invalid_row_id' });
|
|
}
|
|
const columns = resolveUpdateColumns(dataset, payload);
|
|
const values = { ...payload };
|
|
if (tableHasColumn(table, 'updated_at')) {
|
|
values.updated_at = new Date().toISOString();
|
|
}
|
|
if (tableHasColumn(table, 'updated_by_label') && meta.updatedByLabel) {
|
|
values.updated_by_label = meta.updatedByLabel;
|
|
}
|
|
const assignments = columns.map((column) => `"${column}" = ${sqlLiteral(values[column])}`);
|
|
if (tableHasColumn(table, 'updated_at') && !columns.includes('updated_at')) {
|
|
assignments.push(`"updated_at" = ${sqlLiteral(values.updated_at)}`);
|
|
}
|
|
if (tableHasColumn(table, 'updated_by_label') && meta.updatedByLabel && !columns.includes('updated_by_label')) {
|
|
assignments.push(`"updated_by_label" = ${sqlLiteral(meta.updatedByLabel)}`);
|
|
}
|
|
const scopeClause = meta.rowScope?.whereClause ? ` AND ${meta.rowScope.whereClause}` : '';
|
|
const deletedFilter = tableHasColumn(table, 'deleted_at') ? ' AND deleted_at IS NULL' : '';
|
|
const sql = `UPDATE "${table}"
|
|
SET ${assignments.join(', ')}
|
|
WHERE id = ${id}${deletedFilter}${scopeClause};`;
|
|
await executeSql(sql);
|
|
const row = readRowByIdForDataset(dataset, id, { rowScope: meta.rowScope ?? null });
|
|
return { dataset, row };
|
|
}
|
|
|
|
async function softDeleteRowForDataset(dataset, rowId, meta = {}) {
|
|
assertDatasetAction(dataset, 'soft_delete');
|
|
const table = assertSafeSqlIdentifier(dataset.table, '表名');
|
|
const id = Number(rowId);
|
|
if (!Number.isFinite(id) || id <= 0) {
|
|
throw Object.assign(new Error('行 id 无效'), { code: 'invalid_row_id' });
|
|
}
|
|
if (!tableHasColumn(table, 'deleted_at')) {
|
|
throw Object.assign(new Error('目标表不支持软删除'), { code: 'soft_delete_unsupported' });
|
|
}
|
|
const assignments = ['deleted_at = CURRENT_TIMESTAMP'];
|
|
if (tableHasColumn(table, 'deleted_by')) {
|
|
assignments.push(`deleted_by = ${sqlLiteral(meta.deletedBy ?? meta.updatedByLabel ?? 'public')}`);
|
|
}
|
|
const scopeClause = meta.rowScope?.whereClause ? ` AND ${meta.rowScope.whereClause}` : '';
|
|
const sql = `UPDATE "${table}"
|
|
SET ${assignments.join(', ')}
|
|
WHERE id = ${id} AND deleted_at IS NULL${scopeClause};`;
|
|
await executeSql(sql);
|
|
const verifyParts = [`id = ${id}`];
|
|
if (meta.rowScope?.whereClause) {
|
|
verifyParts.push(meta.rowScope.whereClause);
|
|
}
|
|
const deletedRow = runJsonQuery(
|
|
`SELECT id, deleted_at FROM "${table}" WHERE ${verifyParts.join(' AND ')} LIMIT 1`,
|
|
);
|
|
if (!deletedRow.length || !deletedRow[0].deleted_at) {
|
|
throw Object.assign(new Error('无权删除该行或行不存在'), { code: 'row_not_allowed' });
|
|
}
|
|
return { dataset, id, deleted: true };
|
|
}
|
|
|
|
async function hardDeleteRowForDataset(dataset, rowId, meta = {}) {
|
|
assertDatasetAction(dataset, 'hard_delete');
|
|
const table = assertSafeSqlIdentifier(dataset.table, '表名');
|
|
const id = Number(rowId);
|
|
if (!Number.isFinite(id) || id <= 0) {
|
|
throw Object.assign(new Error('行 id 无效'), { code: 'invalid_row_id' });
|
|
}
|
|
const scopeClause = meta.rowScope?.whereClause ? ` AND ${meta.rowScope.whereClause}` : '';
|
|
await executeSql(`DELETE FROM "${table}" WHERE id = ${id}${scopeClause};`);
|
|
return { dataset, id, deleted: true, permanentlyDeleted: true };
|
|
}
|
|
|
|
async function restoreSoftDeletedRowForDataset(dataset, rowId) {
|
|
const table = assertSafeSqlIdentifier(dataset.table, '表名');
|
|
const id = Number(rowId);
|
|
if (!Number.isFinite(id) || id <= 0) {
|
|
throw Object.assign(new Error('行 id 无效'), { code: 'invalid_row_id' });
|
|
}
|
|
if (!tableHasColumn(table, 'deleted_at')) {
|
|
throw Object.assign(new Error('目标表不支持软删除恢复'), { code: 'restore_unsupported' });
|
|
}
|
|
const assignments = ['deleted_at = NULL'];
|
|
if (tableHasColumn(table, 'deleted_by')) {
|
|
assignments.push('deleted_by = NULL');
|
|
}
|
|
const sql = `UPDATE "${table}"
|
|
SET ${assignments.join(', ')}
|
|
WHERE id = ${id} AND deleted_at IS NOT NULL;`;
|
|
await executeSql(sql);
|
|
const readBack = readRowsForDataset(dataset, { limit: 1, orderBy: 'id', orderDir: 'desc', includeDeleted: true });
|
|
const row = readBack.rows.find((item) => Number(item.id) === id) ?? { id };
|
|
return { dataset, row, restored: true };
|
|
}
|
|
|
|
async function restoreSoftDeletedRow(datasetName, rowId) {
|
|
const dataset = getDataset(datasetName);
|
|
if (!dataset) {
|
|
throw Object.assign(new Error('dataset 不存在'), { code: 'dataset_not_found' });
|
|
}
|
|
return restoreSoftDeletedRowForDataset(dataset, rowId);
|
|
}
|
|
|
|
function csvEscape(value) {
|
|
const text = value == null ? '' : String(value);
|
|
if (/[",\n\r]/.test(text)) return `"${text.replace(/"/g, '""')}"`;
|
|
return text;
|
|
}
|
|
|
|
function exportRowsAsCsv(rows, columns) {
|
|
const header = columns.join(',');
|
|
const body = rows.map((row) => columns.map((column) => csvEscape(row[column])).join(','));
|
|
return `${header}\n${body.join('\n')}\n`;
|
|
}
|
|
|
|
function exportDatasetRows(datasetName, { format = 'json', includeDeleted = false, limit = 1000 } = {}) {
|
|
const dataset = getDataset(datasetName);
|
|
if (!dataset) {
|
|
throw Object.assign(new Error('dataset 不存在'), { code: 'dataset_not_found' });
|
|
}
|
|
assertDatasetAction(dataset, 'read');
|
|
const exportLimit = Math.min(Math.max(1, Number(limit ?? 1000)), 5000);
|
|
const result = readRowsForDataset(dataset, {
|
|
limit: exportLimit,
|
|
offset: 0,
|
|
includeDeleted: Boolean(includeDeleted),
|
|
});
|
|
const normalizedFormat = String(format ?? 'json').toLowerCase();
|
|
if (normalizedFormat === 'csv') {
|
|
const columns = resolveReadColumns(dataset);
|
|
return {
|
|
dataset: dataset.name,
|
|
format: 'csv',
|
|
content: exportRowsAsCsv(result.rows, columns),
|
|
rowCount: result.rows.length,
|
|
};
|
|
}
|
|
return {
|
|
dataset: dataset.name,
|
|
format: 'json',
|
|
rows: result.rows,
|
|
rowCount: result.rows.length,
|
|
};
|
|
}
|
|
|
|
function getSchemaForDataset(dataset) {
|
|
const tableColumns = listTableColumns(dataset.table);
|
|
return {
|
|
dataset: {
|
|
name: dataset.name,
|
|
table: dataset.table,
|
|
description: dataset.description,
|
|
actions: dataset.actions,
|
|
columns: dataset.columns,
|
|
limits: dataset.limits,
|
|
},
|
|
tableColumns,
|
|
};
|
|
}
|
|
|
|
function getDatasetSchema(datasetName) {
|
|
const dataset = getDataset(datasetName);
|
|
if (!dataset) {
|
|
throw Object.assign(new Error('dataset 不存在'), { code: 'dataset_not_found' });
|
|
}
|
|
return getSchemaForDataset(dataset);
|
|
}
|
|
|
|
return {
|
|
workspaceRoot,
|
|
privateDataDir,
|
|
privateDataDb,
|
|
ensureReady,
|
|
ensurePrivateDataDb,
|
|
privateDataSize,
|
|
getInfo,
|
|
getSchema,
|
|
querySql,
|
|
executeSql,
|
|
listTableColumns,
|
|
ensureDatasetRegistryTable,
|
|
getDataset,
|
|
listDatasets,
|
|
upsertDataset,
|
|
readDatasetRows,
|
|
readRowsForDataset,
|
|
getDatasetStats,
|
|
getStatsForDataset,
|
|
insertDatasetRow,
|
|
insertRowForDataset,
|
|
updateDatasetRow,
|
|
updateRowForDataset,
|
|
softDeleteDatasetRow,
|
|
softDeleteRowForDataset,
|
|
hardDeleteRowForDataset,
|
|
restoreSoftDeletedRowForDataset,
|
|
restoreSoftDeletedRow,
|
|
exportDatasetRows,
|
|
getDatasetSchema,
|
|
getSchemaForDataset,
|
|
};
|
|
}
|