-
Notifications
You must be signed in to change notification settings - Fork 9
/
sql.ts
167 lines (144 loc) · 4.86 KB
/
sql.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
const parseValue = (v: any) => {
return !!v && v[symbol]
? { text: v.text, values: v.values}
: { text: '??', values: [v] };
}
const raw = (texts: TemplateStringsArray | string[], ...vs: any[]) => {
let text = texts[0] || '';
let values: any[] = [];
let parseResult = null;
vs.forEach((v, idx) => {
parseResult = parseValue(v);
text += parseResult.text;
values.push(...parseResult.values)
text += texts[idx + 1] || "";
});
return { [symbol]: true, text, values };
};
export const sql = (texts: TemplateStringsArray, ...vs: any[]) => raw(texts, ...vs);
export default sql;
const symbol = (sql.symbol = Symbol("sql"));
sql.raw = raw;
sql.cond = (condition: boolean) => (condition ? raw : (..._: any[]) => raw``);
// const to_and = {m: undefined, n: undefined};
// no first and
// sql`select * from a where ${sql.and(to_and)}`
// with first and
// sql`select * from a where (1=1 ${sql.and(to_and)}) or (${sql.and(another_to_and)})`
// sql`select * from a where 1=1 and ${sql.and(to_and)}`
// 东西加多了是硬伤, 加少了可以有 sql.raw, 所以尽量少加
sql.and = (obj: object) => {
let kvs = Object.entries(obj)
.filter(([k, v]) => v !== undefined)
.sort(([ka, va], [kb, vb]) => (ka < kb ? -1 : ka > kb ? 1 : 0));
let values: any[] = [];
if (kvs.length === 0) {
return { [symbol]: true, text: "", values };
}
let text = kvs
.map(([k, v]) => {
values.push(v);
return validate_identifier(k) + " ??";
})
.join(" AND ");
return { [symbol]: true, text, values };
};
sql.or = <T extends any[]>(objs: T) => {
return objs
.map(obj => sql.and(obj))
.reduce(
(acc, cv, idx) => {
acc.text += `${idx === 0 ? "" : " OR"} (${cv.text})`;
acc.values = acc.values.concat(cv.values);
return acc;
},
{ [symbol]: true, text: "", values: [] },
);
};
sql.ins = (obj_or_objs: object | object[]) => {
let objs: any[] = [].concat(obj_or_objs as any);
let keys = Object.keys(Object.assign({}, ...objs)).sort();
let values: any[] = [];
let parseResult = null;
let text = `(${keys.map(k => validate_identifier(k).split(" ")[0]).join(", ")}) VALUES ${objs
.map(
obj =>
`(${keys
.map(k => {
parseResult = parseValue(obj[k])
values.push(...parseResult.values);
return parseResult.text;
})
.join(", ")})`,
)
.join(", ")}`;
return { [symbol]: true, text, values };
};
sql.upd = (obj: object) => {
let kvs = Object.entries(obj)
.filter(([k, v]) => v !== undefined)
.sort(([ka, va], [kb, vb]) => (ka < kb ? -1 : ka > kb ? 1 : 0));
let values: any[] = [];
let parseResult = null
let text = kvs
.map(([k, v]) => {
parseResult = parseValue(v);
values.push(...parseResult.values);
return validate_identifier(k) + `${parseResult.text}`;
})
.join(", ");
return { [symbol]: true, text, values };
};
sql.mock = <M extends string>(value: any) => value;
function validate_identifier(identifier: string) {
// we can believe a functionnal sql (ignore it's good or bad) has to include more than one space, so forbid it
const match_space = identifier.match(/\s/g);
if (!match_space) {
return identifier + ' =';
}
if (match_space.length === 1) {
return identifier;
}
throw Error("ts-sql-plugin sql param object key can not have more than one space");
}
// ? 有想过把所有数据都放在类型系统上, 这样 sql.raw`` 得到的结果就可以作为变量到处传递了, 不需要限制死在 sql`` 内部使用, 与运行时等同...但问题是 TemplateStringsArray 把字符串模板的 const 字符串信息丢失了, 这里只能 typescript 上游去解决, 这样在类型上根本无法得到 raw 里面的字符串, 至于从变量传递作用域上, 那结果就是完全不确定的
// interface AAA<TSA, VS> {
// __texts: TSA;
// __values: VS;
// }
// function abc<TSA extends TemplateStringsArray, VS extends any[]>(texts: TSA, ...vs: VS): AAA<TSA, VS> {
// return {__texts: texts, __values: vs}
// }
// var a = abc`select * from ${123} and good ${new Date()} ${window}`;
// // var a: AAA<['select * from ', ' and good ', ' ', ''], [number, Date, Window]>
// enum ExpressionKind {
// RAW,
// SQL,
// AND,
// INS,
// UPD,
// };
// interface Expression {
// __kind__: ExpressionKind;
// text: string;
// values: any[];
// }
// interface RawExpression extends Expression {
// __kind__: ExpressionKind.RAW;
// }
// interface SqlExpression extends Expression {
// __kind__: ExpressionKind.SQL;
// }
// interface AndExpression extends Expression {
// __kind__: ExpressionKind.AND;
// }
// interface InsExpression extends Expression {
// __kind__: ExpressionKind.INS;
// }
// interface UpdExpression extends Expression {
// __kind__: ExpressionKind.UPD;
// }
// // raw: raw
// // and: and<T>
// // ins: ins<T>
// // upd: upd<T>