| 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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275 | 5x
5x
5x
5x
5x
5x
5x
36x
36x
36x
12x
24x
4x
20x
36x
36x
36x
324x
472x
472x
384x
88x
8x
80x
472x
552x
360x
360x
192x
472x
184x
184x
184x
184x
192x
192x
192x
104x
88x
32x
56x
192x
164x
164x
164x
172x
172x
164x
20x
20x
20x
20x
20x
20x
20x
20x
16x
16x
16x
16x
80x
16x
16x
16x
196x
192x
196x
192x
196x
196x
20x
20x
20x
20x
20x
212x
212x
848x
848x
424x
344x
424x
212x
60x
212x
212x
212x
12x
12x
12x
12x
12x
16x
16x
16x
16x
168x
168x
92x
168x
212x
216x
5x
| const Helpers = require('./Helpers');
const QueryParser = require('./QueryParser');
const State = require('./State');
class QueryBuilderBase {
/**
* @private
* @constructor
* @param {Driver} Driver - The syntax driver for the database
* @param {Adapter} Adapter - The database module adapter for running queries
*/
constructor (Driver, Adapter) {
this.driver = Driver;
this.adapter = Adapter;
this.parser = new QueryParser(this.driver);
this.state = new State();
}
_like (field, val, pos, like, conj) {
field = this.driver.quoteIdentifiers(field);
like = `${field} ${like} ?`;
if (pos === 'before') {
val = `%${val}`;
} else if (pos === 'after') {
val = `${val}%`;
} else {
val = `%${val}%`;
}
conj = (this.state.queryMap.length < 1) ? ' WHERE ' : ` ${conj} `;
this._appendMap(conj, like, 'like');
this.state.whereValues.push(val);
}
/**
* Append a clause to the query map
*
* @private
* @param {String} conjunction - linking keyword for the clause
* @param {String} string - pre-compiled sql fragment
* @param {String} type - type of sql clause
* @return {void}
*/
_appendMap (conjunction, string, type) {
this.state.queryMap.push({
type: type,
conjunction: conjunction,
string: string
});
}
/**
* Handle key/value pairs in an object the same way as individual arguments,
* when appending to state
*
* @private
* @param {mixed} letName Lorem Ipsum
* @param {mixed} valType Lorem Ipsum
* @param {mixed} key Lorem Ipsum
* @param {mixed} val Lorem Ipsum
* @return {Array} - modified state array
*/
_mixedSet (letName, valType, key, val) {
let obj = {};
if (Helpers.isScalar(key) && !Helpers.isUndefined(val)) {
// Convert key/val pair to a simple object
obj[key] = val;
} else if (Helpers.isScalar(key) && Helpers.isUndefined(val)) {
// If just a string for the key, and no value, create a simple object with duplicate key/val
obj[key] = key;
} else {
obj = key;
}
Object.keys(obj).forEach(k => {
// If a single value for the return
if (['key', 'value'].indexOf(valType) !== -1) {
const pushVal = (valType === 'key') ? k : obj[k];
this.state[letName].push(pushVal);
} else {
this.state[letName][k] = obj[k];
}
});
return this.state[letName];
}
_whereMixedSet (key, val) {
this.state.whereMap = [];
this.state.rawWhereValues = [];
this._mixedSet('whereMap', 'both', key, val);
this._mixedSet('rawWhereValues', 'value', key, val);
}
_fixConjunction (conj) {
const lastItem = this.state.queryMap[this.state.queryMap.length - 1];
const conjunctionList = Helpers.arrayPluck(this.state.queryMap, 'conjunction');
if (this.state.queryMap.length === 0 || (!Helpers.regexInArray(conjunctionList, /^ ?WHERE/i))) {
conj = ' WHERE ';
} else if (lastItem.type === 'groupStart') {
conj = '';
} else {
conj = ` ${conj} `;
}
return conj;
}
_where (key, val, defaultConj) {
// Normalize key and value and insert into this.state.whereMap
this._whereMixedSet(key, val);
// Parse the where condition to account for operators,
// functions, identifiers, and literal values
this.state = this.parser.parseWhere(this.driver, this.state);
this.state.whereMap.forEach(clause => {
const conj = this._fixConjunction(defaultConj);
this._appendMap(conj, clause, 'where');
});
this.state.whereMap = {};
}
_whereNull (field, stmt, conj) {
field = this.driver.quoteIdentifiers(field);
const item = `${field} ${stmt}`;
this._appendMap(this._fixConjunction(conj), item, 'whereNull');
}
_having (key, val = null, conj = 'AND') {
// Normalize key/val and put in state.whereMap
this._whereMixedSet(key, val);
// Parse the having condition to account for operators,
// functions, identifiers, and literal values
this.state = this.parser.parseWhere(this.driver, this.state);
this.state.whereMap.forEach(clause => {
// Put in the having map
this.state.havingMap.push({
conjunction: (this.state.havingMap.length > 0) ? ` ${conj} ` : ' HAVING ',
string: clause
});
});
// Clear the where Map
this.state.whereMap = {};
}
_whereIn (key, val, inClause, conj) {
key = this.driver.quoteIdentifiers(key);
const params = Array(val.length);
params.fill('?');
val.forEach(value => {
this.state.whereValues.push(value);
});
conj = (this.state.queryMap.length > 0) ? ` ${conj} ` : ' WHERE ';
const str = `${key} ${inClause} (${params.join(',')}) `;
this._appendMap(conj, str, 'whereIn');
}
_run (type, table, sql, vals) {
if (!sql) {
sql = this._compile(type, table);
}
if (!vals) {
vals = this.state.values.concat(this.state.whereValues);
}
// Reset the state so another query can be built
this._resetState();
// Pass the sql and values to the adapter to run on the database
return this.query(sql, vals);
}
_getCompile (type, table, reset) {
reset = reset || false;
const sql = this._compile(type, table);
Eif (reset) {
this._resetState();
}
return sql;
}
/**
* Complete the sql building based on the type provided
*
* @private
* @param {String} type - Type of SQL query
* @param {String} table - The table to run the query on
* @return {String} - The compiled sql
*/
_compile (type, table) {
// Put together the basic query
let sql = this._compileType(type, table);
// Set each subClause
['queryMap', 'groupString', 'orderString', 'havingMap'].forEach(clause => {
const param = this.state[clause];
if (!Helpers.isScalar(param)) {
Object.keys(param).forEach(part => {
sql += param[part].conjunction + param[part].string;
});
} else {
sql += param;
}
});
// Append the limit, if it exists
if (Helpers.isNumber(this.state.limit)) {
sql = this.driver.limit(sql, this.state.limit, this.state.offset);
}
return sql;
}
_compileType (type, table) {
let sql = '';
switch (type) {
case 'insert':
const params = Array(this.state.setArrayKeys.length).fill('?');
sql = `INSERT INTO ${table} (`;
sql += this.state.setArrayKeys.join(',');
sql += `) VALUES (${params.join(',')})`;
break;
case 'update':
sql = `UPDATE ${table} SET ${this.state.setString}`;
break;
case 'delete':
sql = `DELETE FROM ${table}`;
break;
default:
sql = `SELECT * FROM ${this.state.fromString}`;
// Set the select string
if (this.state.selectString.length > 0) {
// Replace the star with the selected fields
sql = sql.replace('*', this.state.selectString);
}
break;
}
return sql;
}
_resetState () {
this.state = new State();
}
}
module.exports = QueryBuilderBase;
|