740 lines
23 KiB
TypeScript
740 lines
23 KiB
TypeScript
|
/**
|
|||
|
|
* SQL查询构建与执行工具
|
|||
|
|
* SQL Query Builder & Executor
|
|||
|
|
*
|
|||
|
|
* 提供两类能力：
|
|||
|
|
* 1) 基础的SQL字符串构建（用于预览）
|
|||
|
|
* 2) 基于 Prisma 的查询执行与表字段获取
|
|||
|
|
*/
|
|||
|
|
|
|||
|
|
import type { PrismaClient } from '@prisma/client'
|
|||
|
|
import { Prisma } from '@prisma/client'
|
|||
|
|
import type { AdvancedQueryOptions } from '@nuxt4crud/shared/types'
|
|||
|
|
|
|||
|
|
export interface QueryBuilderOptions {
|
|||
|
|
table: string
|
|||
|
|
select?: string[]
|
|||
|
|
where?: Record<string, any>
|
|||
|
|
orderBy?: string
|
|||
|
|
orderDirection?: 'asc' | 'desc'
|
|||
|
|
limit?: number
|
|||
|
|
offset?: number
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* SQL查询构建器类
|
|||
|
|
* SQL Query Builder Class
|
|||
|
|
*/
|
|||
|
|
// 仅用于生成基础 SQL 预览字符串的构建器（不直接执行）
|
|||
|
|
export class SqlQueryBuilder {
|
|||
|
|
private options: QueryBuilderOptions
|
|||
|
|
|
|||
|
|
constructor(options: QueryBuilderOptions) {
|
|||
|
|
this.options = options
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 构建SELECT查询
|
|||
|
|
* Build SELECT query
|
|||
|
|
*/
|
|||
|
|
buildSelectQuery(): { sql: string; params: any[] } {
|
|||
|
|
const params: any[] = []
|
|||
|
|
let sql = 'SELECT '
|
|||
|
|
|
|||
|
|
// SELECT 子句
|
|||
|
|
if (this.options.select && this.options.select.length > 0) {
|
|||
|
|
sql += this.options.select.join(', ')
|
|||
|
|
} else {
|
|||
|
|
sql += '*'
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// FROM 子句
|
|||
|
|
sql += ` FROM ${this.escapeIdentifier(this.options.table)}`
|
|||
|
|
|
|||
|
|
// WHERE 子句
|
|||
|
|
const whereClause = this.buildWhereClause(params)
|
|||
|
|
if (whereClause) {
|
|||
|
|
sql += ` WHERE ${whereClause}`
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// ORDER BY 子句
|
|||
|
|
if (this.options.orderBy) {
|
|||
|
|
sql += ` ORDER BY ${this.escapeIdentifier(this.options.orderBy)} ${this.options.orderDirection || 'ASC'}`
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// LIMIT 子句
|
|||
|
|
if (this.options.limit) {
|
|||
|
|
sql += ` LIMIT ${this.options.limit}`
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// OFFSET 子句
|
|||
|
|
if (this.options.offset) {
|
|||
|
|
sql += ` OFFSET ${this.options.offset}`
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return { sql, params }
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 构建COUNT查询
|
|||
|
|
* Build COUNT query
|
|||
|
|
*/
|
|||
|
|
buildCountQuery(): { sql: string; params: any[] } {
|
|||
|
|
const params: any[] = []
|
|||
|
|
let sql = `SELECT COUNT(*) as count FROM ${this.escapeIdentifier(this.options.table)}`
|
|||
|
|
|
|||
|
|
// WHERE 子句
|
|||
|
|
const whereClause = this.buildWhereClause(params)
|
|||
|
|
if (whereClause) {
|
|||
|
|
sql += ` WHERE ${whereClause}`
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return { sql, params }
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 构建WHERE子句
|
|||
|
|
* Build WHERE clause
|
|||
|
|
*/
|
|||
|
|
private buildWhereClause(params: any[]): string {
|
|||
|
|
if (!this.options.where || Object.keys(this.options.where).length === 0) {
|
|||
|
|
return ''
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
const conditions: string[] = []
|
|||
|
|
|
|||
|
|
Object.entries(this.options.where).forEach(([key, value]) => {
|
|||
|
|
if (value !== undefined && value !== null) {
|
|||
|
|
conditions.push(`${this.escapeIdentifier(key)} = ?`)
|
|||
|
|
params.push(value)
|
|||
|
|
}
|
|||
|
|
})
|
|||
|
|
|
|||
|
|
return conditions.join(' AND ')
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 转义标识符（防止SQL注入）
|
|||
|
|
* Escape identifier (prevent SQL injection)
|
|||
|
|
*/
|
|||
|
|
private escapeIdentifier(identifier: string): string {
|
|||
|
|
// 简单的标识符转义，移除特殊字符
|
|||
|
|
return identifier.replace(/[^a-zA-Z0-9_]/g, '')
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 获取分页查询
|
|||
|
|
* Get paginated query
|
|||
|
|
*/
|
|||
|
|
buildPaginatedQueries(): {
|
|||
|
|
selectQuery: { sql: string; params: any[] }
|
|||
|
|
countQuery: { sql: string; params: any[] }
|
|||
|
|
} {
|
|||
|
|
return {
|
|||
|
|
selectQuery: this.buildSelectQuery(),
|
|||
|
|
countQuery: this.buildCountQuery(),
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* Prisma 驱动的查询构建器
|
|||
|
|
* Prisma-driven query builder and executor
|
|||
|
|
*/
|
|||
|
|
export class PrismaSqlQueryBuilder {
|
|||
|
|
constructor(private prisma: PrismaClient) {}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 获取当前数据库方言（postgres/mysql/sqlite）
|
|||
|
|
* Get current database dialect from DATABASE_URL
|
|||
|
|
*/
|
|||
|
|
private getDialect(): 'postgres' | 'mysql' | 'sqlite' {
|
|||
|
|
const url = process.env.DATABASE_URL || 'file:./dev.db'
|
|||
|
|
if (url.startsWith('postgres') || url.includes('postgresql')) return 'postgres'
|
|||
|
|
if (url.startsWith('mysql')) return 'mysql'
|
|||
|
|
// Prisma sqlite 通常是 file:./xxx.db
|
|||
|
|
return 'sqlite'
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 获取指定表的字段列表（通过数据库系统表）
|
|||
|
|
* Get table column names using database system views
|
|||
|
|
*/
|
|||
|
|
async getTableFields(tableName: string): Promise<string[]> {
|
|||
|
|
const dialect = this.getDialect()
|
|||
|
|
try {
|
|||
|
|
if (dialect === 'postgres') {
|
|||
|
|
const rows: Array<{ column_name: string }> = await (this.prisma.$queryRawUnsafe as any)(
|
|||
|
|
`SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '${tableName}' ORDER BY ordinal_position;`
|
|||
|
|
)
|
|||
|
|
return rows.map(r => r.column_name)
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (dialect === 'mysql') {
|
|||
|
|
const rows: Array<{ Field: string }> = await (this.prisma.$queryRawUnsafe as any)(
|
|||
|
|
`SHOW COLUMNS FROM \`${tableName}\`;`
|
|||
|
|
)
|
|||
|
|
return rows.map(r => r.Field)
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// sqlite
|
|||
|
|
const rows: Array<{ name: string }> = await (this.prisma.$queryRawUnsafe as any)(
|
|||
|
|
`PRAGMA table_info('${tableName}');`
|
|||
|
|
)
|
|||
|
|
return rows.map(r => r.name)
|
|||
|
|
} catch (e) {
|
|||
|
|
console.warn('getTableFields failed, falling back to defaults:', e)
|
|||
|
|
// 尝试返回常见字段集合作为降级方案
|
|||
|
|
if (tableName === 'users') {
|
|||
|
|
return ['id', 'name', 'email', 'age', 'createdAt', 'updatedAt']
|
|||
|
|
}
|
|||
|
|
return ['id', 'createdAt', 'updatedAt']
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 验证高级查询选项的合法性
|
|||
|
|
* Validate AdvancedQueryOptions against available fields
|
|||
|
|
*/
|
|||
|
|
validateQueryOptions(options: AdvancedQueryOptions, availableFields: string[]): string[] {
|
|||
|
|
const errors: string[] = []
|
|||
|
|
const fieldSet = new Set(availableFields)
|
|||
|
|
|
|||
|
|
// where 条件校验
|
|||
|
|
if (Array.isArray(options.where)) {
|
|||
|
|
for (const cond of options.where) {
|
|||
|
|
if (!cond || !cond.field) {
|
|||
|
|
errors.push('存在缺少字段的条件')
|
|||
|
|
continue
|
|||
|
|
}
|
|||
|
|
if (!fieldSet.has(cond.field)) {
|
|||
|
|
errors.push(`字段不存在: ${cond.field}`)
|
|||
|
|
}
|
|||
|
|
if (!cond.operator) {
|
|||
|
|
errors.push(`字段 ${cond.field} 缺少操作符`)
|
|||
|
|
}
|
|||
|
|
const nullOps = new Set(['is_null', 'is_not_null'])
|
|||
|
|
const multiOps = new Set(['in', 'not_in', 'between', 'date_between'])
|
|||
|
|
if (!nullOps.has(cond.operator)) {
|
|||
|
|
if (multiOps.has(cond.operator)) {
|
|||
|
|
if (!Array.isArray(cond.value) || cond.value.length === 0) {
|
|||
|
|
errors.push(`字段 ${cond.field} 的操作符 ${cond.operator} 需要数组值`)
|
|||
|
|
}
|
|||
|
|
} else if (cond.value === undefined || cond.value === null || cond.value === '') {
|
|||
|
|
errors.push(`字段 ${cond.field} 的操作符 ${cond.operator} 需要提供值`)
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 排序校验
|
|||
|
|
if (Array.isArray(options.orderBy)) {
|
|||
|
|
for (const s of options.orderBy) {
|
|||
|
|
if (!s || !s.field) {
|
|||
|
|
errors.push('存在缺少字段的排序项')
|
|||
|
|
continue
|
|||
|
|
}
|
|||
|
|
if (!fieldSet.has(s.field)) errors.push(`排序字段不存在: ${s.field}`)
|
|||
|
|
const dir = String(s.direction || '').toUpperCase()
|
|||
|
|
if (dir !== 'ASC' && dir !== 'DESC') errors.push(`排序方向非法: ${s.direction}`)
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 分页校验
|
|||
|
|
const page = Number(options.pagination?.page || 1)
|
|||
|
|
const limit = Number(options.pagination?.limit || 20)
|
|||
|
|
if (!Number.isFinite(page) || page < 1) errors.push('分页页码必须为正整数')
|
|||
|
|
if (!Number.isFinite(limit) || limit < 1 || limit > 1000)
|
|||
|
|
errors.push('分页数量必须在1-1000之间')
|
|||
|
|
|
|||
|
|
return errors
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 将条件映射为 Prisma where 片段
|
|||
|
|
* Map one condition to Prisma where fragment
|
|||
|
|
*/
|
|||
|
|
private mapConditionToPrisma(cond: any): any {
|
|||
|
|
const { field, operator, value } = cond || {}
|
|||
|
|
|
|||
|
|
// 如果值为null、undefined或空字符串（除了isNull/isNotNull操作符），返回空对象
|
|||
|
|
if (
|
|||
|
|
(value === null || value === undefined || value === '') &&
|
|||
|
|
operator !== 'is_null' &&
|
|||
|
|
operator !== 'is_not_null'
|
|||
|
|
) {
|
|||
|
|
return {}
|
|||
|
|
}
|
|||
|
|
switch (operator) {
|
|||
|
|
case 'equals':
|
|||
|
|
case 'eq': // 支持简写形式
|
|||
|
|
return { [field]: value }
|
|||
|
|
case 'not_equals':
|
|||
|
|
case 'ne': // 支持简写形式
|
|||
|
|
return { [field]: { not: value } }
|
|||
|
|
case 'contains':
|
|||
|
|
case 'like': // 支持简写形式
|
|||
|
|
// 确保值不为空字符串，否则会导致不匹配任何记录
|
|||
|
|
if (String(value) === '') return {}
|
|||
|
|
return { [field]: { contains: String(value) } }
|
|||
|
|
case 'not_contains':
|
|||
|
|
case 'notLike': // 支持简写形式
|
|||
|
|
// 确保值不为空字符串，否则会导致不匹配任何记录
|
|||
|
|
if (String(value) === '') return {}
|
|||
|
|
return { [field]: { not: { contains: String(value) } } }
|
|||
|
|
case 'starts_with':
|
|||
|
|
// 确保值不为空字符串，否则会导致不匹配任何记录
|
|||
|
|
if (String(value) === '') return {}
|
|||
|
|
return { [field]: { startsWith: String(value) } }
|
|||
|
|
case 'ends_with':
|
|||
|
|
// 确保值不为空字符串，否则会导致不匹配任何记录
|
|||
|
|
if (String(value) === '') return {}
|
|||
|
|
return { [field]: { endsWith: String(value) } }
|
|||
|
|
case 'gt':
|
|||
|
|
case 'greater_than':
|
|||
|
|
return { [field]: { gt: value } }
|
|||
|
|
case 'gte':
|
|||
|
|
case 'greater_than_or_equal':
|
|||
|
|
return { [field]: { gte: value } }
|
|||
|
|
case 'lt':
|
|||
|
|
case 'less_than':
|
|||
|
|
return { [field]: { lt: value } }
|
|||
|
|
case 'lte':
|
|||
|
|
case 'less_than_or_equal':
|
|||
|
|
return { [field]: { lte: value } }
|
|||
|
|
case 'in': {
|
|||
|
|
const arr = Array.isArray(value)
|
|||
|
|
? value
|
|||
|
|
: String(value)
|
|||
|
|
.split(',')
|
|||
|
|
.map(v => v.trim())
|
|||
|
|
// 确保数组不为空
|
|||
|
|
if (arr.length === 0 || (arr.length === 1 && arr[0] === '')) return {}
|
|||
|
|
return { [field]: { in: arr } }
|
|||
|
|
}
|
|||
|
|
case 'not_in': {
|
|||
|
|
const arr = Array.isArray(value)
|
|||
|
|
? value
|
|||
|
|
: String(value)
|
|||
|
|
.split(',')
|
|||
|
|
.map(v => v.trim())
|
|||
|
|
// 确保数组不为空
|
|||
|
|
if (arr.length === 0 || (arr.length === 1 && arr[0] === '')) return {}
|
|||
|
|
return { [field]: { notIn: arr } }
|
|||
|
|
}
|
|||
|
|
case 'is_null':
|
|||
|
|
return { [field]: null }
|
|||
|
|
case 'is_not_null':
|
|||
|
|
return { [field]: { not: null } }
|
|||
|
|
case 'between': {
|
|||
|
|
const [from, to] = Array.isArray(value) ? value : []
|
|||
|
|
// 确保from和to都有效
|
|||
|
|
if (from === undefined || to === undefined || from === '' || to === '') return {}
|
|||
|
|
return { [field]: { gte: from, lte: to } }
|
|||
|
|
}
|
|||
|
|
case 'date_between': {
|
|||
|
|
const [from, to] = Array.isArray(value) ? value : []
|
|||
|
|
// 确保from和to都有效
|
|||
|
|
if (from === undefined || to === undefined || from === '' || to === '') return {}
|
|||
|
|
return { [field]: { gte: new Date(from), lte: new Date(to) } }
|
|||
|
|
}
|
|||
|
|
default:
|
|||
|
|
return {}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 根据查询选项构建 Prisma where 条件及 SQL 预览
|
|||
|
|
* Build Prisma where clause and a basic SQL preview from options
|
|||
|
|
*/
|
|||
|
|
private buildWhereFromOptions(
|
|||
|
|
tableName: string,
|
|||
|
|
options: AdvancedQueryOptions,
|
|||
|
|
searchFields: string[]
|
|||
|
|
): { where: any; sqlPreview: string } {
|
|||
|
|
const where: any = {}
|
|||
|
|
const andConditions: any[] = []
|
|||
|
|
const orConditions: any[] = []
|
|||
|
|
const sqlPreviewParts: string[] = []
|
|||
|
|
|
|||
|
|
// 搜索条件
|
|||
|
|
const search = options.search?.trim()
|
|||
|
|
if (search && searchFields.length > 0) {
|
|||
|
|
const searchOr = searchFields.map(f => ({ [f]: { contains: search } }))
|
|||
|
|
// 默认与其它条件 AND 组合
|
|||
|
|
andConditions.push({ OR: searchOr })
|
|||
|
|
sqlPreviewParts.push(`(${searchFields.map(f => `${f} LIKE '%${search}%'`).join(' OR ')})`)
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// where 条件
|
|||
|
|
if (Array.isArray(options.where)) {
|
|||
|
|
for (const cond of options.where) {
|
|||
|
|
const { field, operator, value } = cond
|
|||
|
|
|
|||
|
|
// 特殊处理posts表的title字段，因为在Prisma模型中title是必填字段（String）
|
|||
|
|
if (tableName === 'posts' && field === 'title') {
|
|||
|
|
if (operator === 'isNull' || operator === 'is_null') {
|
|||
|
|
// posts表的title字段不可能为null，所以如果有这个条件，直接返回空结果
|
|||
|
|
return { where: { id: -1 }, sqlPreview: 'WHERE title IS NULL' }
|
|||
|
|
} else if (operator === 'isNotNull' || operator === 'is_not_null') {
|
|||
|
|
// posts表的title字段总是不为null，所以这个条件可以忽略
|
|||
|
|
continue
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
const frag = this.mapConditionToPrisma(cond)
|
|||
|
|
if (!frag || Object.keys(frag).length === 0) continue
|
|||
|
|
|
|||
|
|
// 普通条件处理
|
|||
|
|
if (String(cond.logicalOperator || 'AND').toUpperCase() === 'OR') {
|
|||
|
|
orConditions.push(frag)
|
|||
|
|
} else {
|
|||
|
|
andConditions.push(frag)
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 生成SQL预览
|
|||
|
|
let sqlPart = ''
|
|||
|
|
switch (operator) {
|
|||
|
|
case 'equals':
|
|||
|
|
sqlPart = `${field} = '${value}'`
|
|||
|
|
break
|
|||
|
|
case 'not_equals':
|
|||
|
|
sqlPart = `${field} <> '${value}'`
|
|||
|
|
break
|
|||
|
|
case 'contains':
|
|||
|
|
sqlPart = `${field} LIKE '%${value}%'`
|
|||
|
|
break
|
|||
|
|
case 'not_contains':
|
|||
|
|
sqlPart = `${field} NOT LIKE '%${value}%'`
|
|||
|
|
break
|
|||
|
|
case 'starts_with':
|
|||
|
|
sqlPart = `${field} LIKE '${value}%'`
|
|||
|
|
break
|
|||
|
|
case 'ends_with':
|
|||
|
|
sqlPart = `${field} LIKE '%${value}'`
|
|||
|
|
break
|
|||
|
|
case 'gt':
|
|||
|
|
sqlPart = `${field} > ${value}`
|
|||
|
|
break
|
|||
|
|
case 'gte':
|
|||
|
|
sqlPart = `${field} >= ${value}`
|
|||
|
|
break
|
|||
|
|
case 'lt':
|
|||
|
|
sqlPart = `${field} < ${value}`
|
|||
|
|
break
|
|||
|
|
case 'lte':
|
|||
|
|
sqlPart = `${field} <= ${value}`
|
|||
|
|
break
|
|||
|
|
case 'in':
|
|||
|
|
sqlPart = `${field} IN (${Array.isArray(value) ? value.map(v => `'${v}'`).join(', ') : `'${value}'`})`
|
|||
|
|
break
|
|||
|
|
case 'not_in':
|
|||
|
|
sqlPart = `${field} NOT IN (${Array.isArray(value) ? value.map(v => `'${v}'`).join(', ') : `'${value}'`})`
|
|||
|
|
break
|
|||
|
|
case 'is_null':
|
|||
|
|
sqlPart = `${field} IS NULL`
|
|||
|
|
break
|
|||
|
|
case 'is_not_null':
|
|||
|
|
sqlPart = `${field} IS NOT NULL`
|
|||
|
|
break
|
|||
|
|
case 'between':
|
|||
|
|
const [from, to] = Array.isArray(value) ? value : []
|
|||
|
|
sqlPart = `${field} BETWEEN ${from} AND ${to}`
|
|||
|
|
break
|
|||
|
|
}
|
|||
|
|
if (sqlPart) {
|
|||
|
|
if (sqlPreviewParts.length > 0) {
|
|||
|
|
sqlPreviewParts.push(String(cond.logicalOperator || 'AND').toUpperCase())
|
|||
|
|
}
|
|||
|
|
sqlPreviewParts.push(sqlPart)
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
let finalConditions: any[] = []
|
|||
|
|
|
|||
|
|
// 处理搜索条件
|
|||
|
|
if (search && searchFields.length > 0) {
|
|||
|
|
const searchOr = searchFields.map(f => ({ [f]: { contains: search } }))
|
|||
|
|
finalConditions.push({ OR: searchOr })
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 正确处理逻辑操作符序列
|
|||
|
|
// 前端发送的条件数组中，每个条件（除了第一个）都有一个logicalOperator
|
|||
|
|
// 表示该条件与前一个条件的连接方式
|
|||
|
|
if (Array.isArray(options.where) && options.where.length > 0) {
|
|||
|
|
// 第一个条件
|
|||
|
|
let currentCondition = this.mapConditionToPrisma(options.where[0])
|
|||
|
|
|
|||
|
|
// 如果只有一个条件，直接使用它
|
|||
|
|
if (options.where.length === 1) {
|
|||
|
|
if (currentCondition && Object.keys(currentCondition).length > 0) {
|
|||
|
|
finalConditions.push(currentCondition)
|
|||
|
|
}
|
|||
|
|
} else {
|
|||
|
|
// 处理多个条件
|
|||
|
|
for (let i = 1; i < options.where.length; i++) {
|
|||
|
|
const nextCondition = this.mapConditionToPrisma(options.where[i])
|
|||
|
|
const logicalOperator = String(options.where[i].logicalOperator || 'AND').toUpperCase()
|
|||
|
|
|
|||
|
|
if (!currentCondition || Object.keys(currentCondition).length === 0) {
|
|||
|
|
currentCondition = nextCondition
|
|||
|
|
continue
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (!nextCondition || Object.keys(nextCondition).length === 0) {
|
|||
|
|
continue
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 根据逻辑操作符构建新的条件结构
|
|||
|
|
if (logicalOperator === 'OR') {
|
|||
|
|
// 如果当前条件已经是OR结构，添加新条件
|
|||
|
|
if (currentCondition.OR) {
|
|||
|
|
currentCondition.OR.push(nextCondition)
|
|||
|
|
} else {
|
|||
|
|
// 否则创建新的OR结构
|
|||
|
|
currentCondition = {
|
|||
|
|
OR: [currentCondition, nextCondition],
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
} else {
|
|||
|
|
// AND
|
|||
|
|
// 如果当前条件已经是AND结构，添加新条件
|
|||
|
|
if (currentCondition.AND) {
|
|||
|
|
currentCondition.AND.push(nextCondition)
|
|||
|
|
} else {
|
|||
|
|
// 否则创建新的AND结构
|
|||
|
|
currentCondition = {
|
|||
|
|
AND: [currentCondition, nextCondition],
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (currentCondition && Object.keys(currentCondition).length > 0) {
|
|||
|
|
finalConditions.push(currentCondition)
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 组合所有条件
|
|||
|
|
if (finalConditions.length > 1) {
|
|||
|
|
where.AND = finalConditions
|
|||
|
|
} else if (finalConditions.length === 1) {
|
|||
|
|
Object.assign(where, finalConditions[0])
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 构建非常基础的 SQL 预览
|
|||
|
|
const whereParts: string[] = []
|
|||
|
|
if (search && searchFields.length > 0) {
|
|||
|
|
const like = search.replace(/'/g, "''")
|
|||
|
|
whereParts.push('(' + searchFields.map(f => `${f} ILIKE '%${like}%'`).join(' OR ') + ')')
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (Array.isArray(options.where)) {
|
|||
|
|
for (const cond of options.where) {
|
|||
|
|
const op = String(cond.operator || '').toLowerCase()
|
|||
|
|
const f = cond.field
|
|||
|
|
const v = cond.value
|
|||
|
|
const logic = String(cond.logicalOperator || 'AND').toUpperCase()
|
|||
|
|
let exp = ''
|
|||
|
|
switch (op) {
|
|||
|
|
case 'equals':
|
|||
|
|
exp = `${f} = '${v}'`
|
|||
|
|
break
|
|||
|
|
case 'not_equals':
|
|||
|
|
exp = `${f} != '${v}'`
|
|||
|
|
break
|
|||
|
|
case 'contains':
|
|||
|
|
exp = `${f} ILIKE '%${String(v)}%'`
|
|||
|
|
break
|
|||
|
|
case 'not_contains':
|
|||
|
|
exp = `${f} NOT ILIKE '%${String(v)}%'`
|
|||
|
|
break
|
|||
|
|
case 'starts_with':
|
|||
|
|
exp = `${f} ILIKE '${String(v)}%'`
|
|||
|
|
break
|
|||
|
|
case 'ends_with':
|
|||
|
|
exp = `${f} ILIKE '%${String(v)}'`
|
|||
|
|
break
|
|||
|
|
case 'gt':
|
|||
|
|
case 'greater_than':
|
|||
|
|
exp = `${f} > ${v}`
|
|||
|
|
break
|
|||
|
|
case 'gte':
|
|||
|
|
case 'greater_than_or_equal':
|
|||
|
|
exp = `${f} >= ${v}`
|
|||
|
|
break
|
|||
|
|
case 'lt':
|
|||
|
|
case 'less_than':
|
|||
|
|
exp = `${f} < ${v}`
|
|||
|
|
break
|
|||
|
|
case 'lte':
|
|||
|
|
case 'less_than_or_equal':
|
|||
|
|
exp = `${f} <= ${v}`
|
|||
|
|
break
|
|||
|
|
case 'in': {
|
|||
|
|
const arr = Array.isArray(v)
|
|||
|
|
? v
|
|||
|
|
: String(v)
|
|||
|
|
.split(',')
|
|||
|
|
.map(s => s.trim())
|
|||
|
|
exp = `${f} IN (${arr.map(x => `'${x}'`).join(', ')})`
|
|||
|
|
break
|
|||
|
|
}
|
|||
|
|
case 'not_in': {
|
|||
|
|
const arr = Array.isArray(v)
|
|||
|
|
? v
|
|||
|
|
: String(v)
|
|||
|
|
.split(',')
|
|||
|
|
.map(s => s.trim())
|
|||
|
|
exp = `${f} NOT IN (${arr.map(x => `'${x}'`).join(', ')})`
|
|||
|
|
break
|
|||
|
|
}
|
|||
|
|
case 'is_null':
|
|||
|
|
exp = `${f} IS NULL`
|
|||
|
|
break
|
|||
|
|
case 'is_not_null':
|
|||
|
|
exp = `${f} IS NOT NULL`
|
|||
|
|
break
|
|||
|
|
case 'between': {
|
|||
|
|
const [from, to] = Array.isArray(v) ? v : []
|
|||
|
|
exp = `${f} BETWEEN '${from}' AND '${to}'`
|
|||
|
|
break
|
|||
|
|
}
|
|||
|
|
case 'date_between': {
|
|||
|
|
const [from, to] = Array.isArray(v) ? v : []
|
|||
|
|
exp = `${f} BETWEEN '${from}' AND '${to}'`
|
|||
|
|
break
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (exp) whereParts.push((whereParts.length > 0 ? logic + ' ' : '') + exp)
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
const whereSql = whereParts.length > 0 ? ' WHERE ' + whereParts.join(' ') : ''
|
|||
|
|
const baseSql = `SELECT * FROM ${tableName}${whereSql}`
|
|||
|
|
return { where, sqlPreview: baseSql }
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 执行普通查询，返回分页数据以及SQL预览
|
|||
|
|
* Execute normal query with pagination and a SQL preview
|
|||
|
|
*/
|
|||
|
|
async executeQuery(
|
|||
|
|
tableName: string,
|
|||
|
|
options: AdvancedQueryOptions,
|
|||
|
|
searchFields: string[]
|
|||
|
|
): Promise<{
|
|||
|
|
data: any[]
|
|||
|
|
total: number
|
|||
|
|
pagination: { page: number; limit: number; totalPages: number }
|
|||
|
|
executedAt: string
|
|||
|
|
table: string
|
|||
|
|
query: { sql: string }
|
|||
|
|
}> {
|
|||
|
|
const page = Number(options.pagination?.page || 1)
|
|||
|
|
const limit = Number(options.pagination?.limit || 20)
|
|||
|
|
const skip = (page - 1) * limit
|
|||
|
|
|
|||
|
|
const { where, sqlPreview } = this.buildWhereFromOptions(tableName, options, searchFields)
|
|||
|
|
|
|||
|
|
// 如果where条件包含id: -1，说明是posts表的title IS NULL查询，直接返回空结果
|
|||
|
|
if (where.id === -1) {
|
|||
|
|
return {
|
|||
|
|
data: [],
|
|||
|
|
total: 0,
|
|||
|
|
pagination: {
|
|||
|
|
page: Number(options.pagination?.page || 1),
|
|||
|
|
limit: Number(options.pagination?.limit || 20),
|
|||
|
|
totalPages: 0,
|
|||
|
|
},
|
|||
|
|
executedAt: new Date().toISOString(),
|
|||
|
|
table: tableName,
|
|||
|
|
query: { sql: sqlPreview },
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 映射到 Prisma 模型名称（简单复数到单数映射）
|
|||
|
|
// 将表名转换为Prisma模型名（通常是单数形式）
|
|||
|
|
const modelName = tableName === 'users' ? 'user' : tableName === 'posts' ? 'post' : tableName
|
|||
|
|
const model = (this.prisma as any)[modelName]
|
|||
|
|
|
|||
|
|
// 检查模型是否存在
|
|||
|
|
if (!model) {
|
|||
|
|
const err: any = new Error(`未找到 Prisma 模型: ${modelName}`)
|
|||
|
|
err.statusCode = 400
|
|||
|
|
throw err
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 排序处理
|
|||
|
|
const orderBy =
|
|||
|
|
Array.isArray(options.orderBy) && options.orderBy.length > 0
|
|||
|
|
? options.orderBy.map(s => ({
|
|||
|
|
[s.field]: String(s.direction || 'ASC').toLowerCase(),
|
|||
|
|
}))
|
|||
|
|
: undefined
|
|||
|
|
|
|||
|
|
// 执行查询
|
|||
|
|
const [data, total] = await Promise.all([
|
|||
|
|
model.findMany({ where, skip, take: limit, orderBy }),
|
|||
|
|
model.count({ where }),
|
|||
|
|
])
|
|||
|
|
|
|||
|
|
return {
|
|||
|
|
data,
|
|||
|
|
total,
|
|||
|
|
pagination: { page, limit, totalPages: Math.max(Math.ceil(total / limit), 1) },
|
|||
|
|
executedAt: new Date().toISOString(),
|
|||
|
|
table: tableName,
|
|||
|
|
query: { sql: sqlPreview + ` ORDER BY ... LIMIT ${limit} OFFSET ${skip}` },
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 执行聚合查询（count/sum/avg/min/max）
|
|||
|
|
* Execute aggregate query (count/sum/avg/min/max)
|
|||
|
|
*/
|
|||
|
|
async executeAggregateQuery(tableName: string, options: AdvancedQueryOptions): Promise<any> {
|
|||
|
|
const { where } = this.buildWhereFromOptions(tableName, options, [])
|
|||
|
|
|
|||
|
|
const modelName = tableName === 'users' ? 'user' : tableName
|
|||
|
|
const model = (this.prisma as any)[modelName]
|
|||
|
|
if (!model) {
|
|||
|
|
const err: any = new Error(`未找到 Prisma 模型: ${modelName}`)
|
|||
|
|
err.statusCode = 400
|
|||
|
|
throw err
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
const agg = options.aggregate || {}
|
|||
|
|
const params: any = { where }
|
|||
|
|
|
|||
|
|
if (agg.count) params._count = { _all: true }
|
|||
|
|
|
|||
|
|
const toFieldObj = (fields?: string[]) => {
|
|||
|
|
if (!fields || fields.length === 0) return undefined
|
|||
|
|
return Object.fromEntries(fields.map(f => [f, true]))
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
params._sum = toFieldObj(agg.sum)
|
|||
|
|
params._avg = toFieldObj(agg.avg)
|
|||
|
|
params._min = toFieldObj(agg.min)
|
|||
|
|
params._max = toFieldObj(agg.max)
|
|||
|
|
|
|||
|
|
const result = await model.aggregate(params)
|
|||
|
|
|
|||
|
|
return {
|
|||
|
|
executedAt: new Date().toISOString(),
|
|||
|
|
result,
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 创建基于 Prisma 的查询构建器实例
|
|||
|
|
* Create Prisma-based SQL query builder instance
|
|||
|
|
*/
|
|||
|
|
export function createSqlQueryBuilder(prisma: PrismaClient): PrismaSqlQueryBuilder {
|
|||
|
|
return new PrismaSqlQueryBuilder(prisma)
|
|||
|
|
}
|