Latest
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342import SqlString from "./sqlString.ts";import { assertStrictEq } from "./deps.ts";/** * 判断是否为 QueryBuilder 实例 * @param query */export function isQueryBuilder(query: any): boolean { return query && typeof query.build === "function";}
/** * 格式化SQL字符串 * @param tpl 模板字符串 * @param values 模板变量 */export function sqlFormat(tpl: string, values: any[] = []): string { values = values.slice(); let index = -1; tpl = tpl.replace(/\?+/g, (text, pos) => { index++; const v = values[index]; if (text !== "???") return text;
if (typeof v === "string") { values.splice(index, 1); index--; return v; } if (isQueryBuilder(v)) { const sql = v.build(); assertStrictEq( typeof sql, "string", `sqlFormat: values[${index}].build() must returns a string`, ); values.splice(index, 1); index--; return `(${sql})`; } throw new Error( `sqlFormat: values[${index}] for ??? must be a string or QueryBuilder instance but got ${v}`, ); }); return SqlString.format(tpl, values);}
/** * 返回格式化后的 SQL 语句 * 格式： SELECT * FROM ::table WHERE `title`=:title AND `id` IN :::ids * @param sql SQL 模板语句 * @param values 参数对象 * @param disable$ 是否没有 $ 开头的 key */export function sqlFormatObject( sql: string, values: Record<string, any> = {}, disable$: boolean = false,): string { return sql.replace(/:((:){0,2}[\w$]+)/g, (txt, key) => { let type = "value"; let name = key; if (key.slice(0, 2) === "::") { type = "raw"; name = key.slice(2); } else if (key.slice(0, 1) === ":") { type = "id"; name = key.slice(1); } if (values.hasOwnProperty(name)) { if (disable$) { return values[name]; } switch (type) { case "id": return sqlEscapeId(values[name]); case "raw": if (typeof values[name] === "string") return values[name]; if (isQueryBuilder(values[name])) { const sql = values[name].build(); assertStrictEq( typeof sql, "string", `sqlFormatObject: values["${name}"].build() must returns a string`, ); return `(${sql})`; } throw new Error( `sqlFormatObject: value for :::${name} must be a string or QueryBuilder instance but got ${ values[name] }`, ); default: return sqlEscape(values[name]); } } return txt; });}
/** * 转义SQL值 * @param value 值 */export function sqlEscape(value: string): string { return SqlString.escape(value);}
/** * 转义SQL标识符 * @param value 标识符 */export function sqlEscapeId(value: string): string { return SqlString.escapeId(value);}
/** * 查找值为undefined的key列表 * @param data */export function findKeysForUndefinedValue(data: Record<string, any>): string[] { return Object.keys(data).filter((k) => typeof data[k] === "undefined");}
/** * 返回根据对象生成的 SQL UPDATE 语句 * @param data 键值对对象 */export function sqlUpdateString(data: Record<string, any>): string { return Object.keys(data) .map((name) => { const info = data[name]; const escapedName = sqlEscapeId(name); if (info && typeof info === "object" && Object.keys(info).length === 1) { const op = Object.keys(info)[0]; switch (op) { case "$incr": return `${escapedName}=${escapedName}+(${sqlEscape(info.$incr)})`; case "$decr": return `${escapedName}=${escapedName}-(${sqlEscape(info.$decr)})`; case "$raw": return `${escapedName}=${info.$raw}`; default: throw new Error(`update type ${op} does not supported`); } } else { return `${escapedName}=${sqlEscape(data[name])}`; } }) .join(", ");}
/** * 返回根据对象生成的 SQL WHERE 语句 * @param self QueryBuilder实例 * @param condition 查询条件 */export function sqlConditionStrings(condition: Record<string, any>): string[] { const ret: string[] = []; const isPureConditionObject = (info: any) => { if (info && typeof info === "object") { const keys = Object.keys(info); return ( keys.length > 0 && keys.filter((v) => v[0] === "$").length === keys.length ); } return false; };
for (const name in condition as any) { const info = (condition as any)[name]; const escapedName = sqlEscapeId(name); if (isPureConditionObject(info)) { Object.keys(info).forEach((op) => { switch (op) { case "$isNull": assertStrictEq( true, info.$isNull, `value of $isNull property must be true`, ); ret.push(`${escapedName} IS NULL`); break; case "$isNotNull": assertStrictEq( true, info.$isNotNull, `value of $isNotNull property must be true`, ); ret.push(`${escapedName} IS NOT NULL`); break; case "$lt": ret.push(`${escapedName}<${sqlEscape(info.$lt)}`); break; case "$lte": ret.push(`${escapedName}<=${sqlEscape(info.$lte)}`); break; case "$gt": ret.push(`${escapedName}>${sqlEscape(info.$gt)}`); break; case "$gte": ret.push(`${escapedName}>=${sqlEscape(info.$gte)}`); break; case "$eq": ret.push(`${escapedName}=${sqlEscape(info.$eq)}`); break; case "$ne": ret.push(`${escapedName}<>${sqlEscape(info.$ne)}`); break; case "$in": if (isQueryBuilder(info.$in)) { const sql = info.$in.build(); assertStrictEq( typeof sql, "string", `sqlConditionStrings: values["${name}"].$in.build() must returns a string`, ); ret.push(`${escapedName} IN (${sql})`); } else if (Array.isArray(info.$in)) { const line = `${escapedName} IN (${ info.$in .map((v: any) => sqlEscape(v)) .join(", ") })`; if (info.$in.length > 0) { ret.push(line); } else { ret.push(`0 /* empty list warn: ${line} */`); } } else { throw new Error( `value for condition type $in in field ${name} must be an array`, ); } break; case "$notIn": if (isQueryBuilder(info.$notIn)) { const sql = info.$notIn.build(); assertStrictEq( typeof sql, "string", `sqlConditionStrings: values["${name}"].$notIn.build() must returns a string`, ); ret.push(`${escapedName} NOT IN (${sql})`); } else if (Array.isArray(info.$notIn)) { const line = `${escapedName} NOT IN (${ info.$notIn .map((v: any) => sqlEscape(v)) .join(", ") })`; if (info.$notIn.length > 0) { ret.push(line); } else { ret.push(`1 /* empty list warn: ${line} */`); } } else { throw new Error( `value for condition type $notIn in field ${name} must be an array`, ); } break; case "$like": assertStrictEq( typeof info.$like, "string", `value for condition type $like in ${name} must be a string`, ); ret.push(`${escapedName} LIKE ${sqlEscape(info.$like)}`); break; case "$notLike": assertStrictEq( typeof info.$notLike, "string", `value for condition type $notLike in ${name} must be a string`, ); ret.push(`${escapedName} NOT LIKE ${sqlEscape(info.$notLike)}`); break; case "$raw": ret.push(`${escapedName}=${info.$raw}`); break; default: throw new Error(`condition type ${op} does not supported`); } }); } else { ret.push(`${escapedName}=${sqlEscape((condition as any)[name])}`); } } return ret;}
/** * 返回生成 SQL LIMIT 语句 * @param offset 跳过的行数 * @param limit 返回的行数 */export function sqlLimitString(offset: number, limit: number): string { offset = Number(offset); limit = Number(limit); if (limit > 0) { if (offset > 0) { return `LIMIT ${offset},${limit}`; } return `LIMIT ${limit}`; } return `LIMIT ${offset},18446744073709551615`;}
/** * 合并多段文本 * @param strs 文本数组 */export function joinMultiString(...strs: string[]): string { return strs .map((v) => v.trim()) .filter((v) => v) .join(" ");}
/** * 对象深拷贝 * @param data */export function deepCopy<T = any>(data: T): T { return JSON.parse(JSON.stringify(data));}
/** * 格式化字段列表 * @param table * @param fields */export function formatFields(table: string, fields: string[]) { const prefix = table ? `${table}.` : ""; return fields.map((n) => { if (n === "*") return `${prefix}*`; if (n.toLowerCase().indexOf(" as ") !== -1) return n; if (n[0] === "`") return `${prefix}${n}`; return `${prefix}${sqlEscapeId(n)}`; });}