1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Nip\Database\Query; |
4
|
|
|
|
5
|
|
|
use Nip\Database\Connections\Connection; |
6
|
|
|
use Nip\Database\Query\Condition\Condition; |
7
|
|
|
use Nip\Database\Result; |
8
|
|
|
|
9
|
|
|
/** |
10
|
|
|
* Class AbstractQuery |
11
|
|
|
* @package Nip\Database\Query |
12
|
|
|
* |
13
|
|
|
* @method $this setCols() setCols(array | string $cols = null) |
14
|
|
|
* @method $this setWhere() setWhere(array | string $cols = null) |
15
|
|
|
* |
16
|
|
|
* @method $this cols() cols(array | string $cols) |
17
|
|
|
* @method $this count() count(string $col, string $alias = null) |
18
|
|
|
* @method $this sum() sum(array | string $cols) |
19
|
|
|
* @method $this from() from(array | string $from) |
20
|
|
|
* @method $this data() data(array $data) |
21
|
|
|
* @method $this table() table(array | string $table) |
22
|
|
|
* @method $this order() order(array | string $order)\ |
23
|
|
|
* @method $this group() group(array | string $group, $rollup = false)\ |
24
|
|
|
*/ |
25
|
|
|
abstract class AbstractQuery |
26
|
|
|
{ |
27
|
|
|
|
28
|
|
|
/** |
29
|
|
|
* @var Connection |
30
|
|
|
*/ |
31
|
|
|
protected $db; |
32
|
|
|
|
33
|
|
|
protected $parts = [ |
34
|
|
|
'where' => null, |
35
|
|
|
]; |
36
|
|
|
|
37
|
|
|
protected $string = null; |
38
|
|
|
|
39
|
|
|
/** |
40
|
|
|
* @param Connection $manager |
41
|
|
|
* @return $this |
42
|
|
|
*/ |
43
|
|
|
public function setManager(Connection $manager) |
44
|
|
|
{ |
45
|
|
|
$this->db = $manager; |
46
|
|
|
|
47
|
|
|
return $this; |
48
|
|
|
} |
49
|
|
|
|
50
|
|
|
/** |
51
|
|
|
* @param $name |
52
|
|
|
* @param $arguments |
53
|
|
|
* @return $this |
54
|
|
|
*/ |
55
|
|
|
public function __call($name, $arguments) |
56
|
|
|
{ |
57
|
|
|
if (strpos($name, 'set') === 0) { |
58
|
|
|
$name = str_replace('set', '', $name); |
59
|
|
|
$name[0] = strtolower($name[0]); |
60
|
|
|
$this->initPart($name); |
61
|
|
|
} |
62
|
|
|
|
63
|
|
|
foreach ($arguments as $argument) { |
64
|
|
|
$this->addPart($name, $argument); |
65
|
|
|
} |
66
|
|
|
|
67
|
|
|
return $this; |
68
|
|
|
} |
69
|
|
|
|
70
|
|
|
/** |
71
|
|
|
* @param $name |
72
|
|
|
* @return $this |
73
|
|
|
*/ |
74
|
|
|
protected function initPart($name) |
75
|
|
|
{ |
76
|
|
|
$this->isGenerated(false); |
77
|
|
|
$this->parts[$name] = []; |
78
|
|
|
|
79
|
|
|
return $this; |
80
|
|
|
} |
81
|
|
|
|
82
|
|
|
/** |
83
|
|
|
* @param boolean $generated |
84
|
|
|
* @return bool |
85
|
|
|
*/ |
86
|
|
|
public function isGenerated($generated = null) |
87
|
|
|
{ |
88
|
|
|
if ($generated === false) { |
89
|
|
|
$this->string = null; |
90
|
|
|
} |
91
|
|
|
|
92
|
|
|
return $this->string !== null; |
93
|
|
|
} |
94
|
|
|
|
95
|
|
|
/** |
96
|
|
|
* @param $name |
97
|
|
|
* @param $value |
98
|
|
|
* @return $this |
99
|
|
|
*/ |
100
|
|
|
protected function addPart($name, $value) |
101
|
|
|
{ |
102
|
|
|
if (!isset($this->parts[$name])) { |
103
|
|
|
$this->initPart($name); |
104
|
|
|
} |
105
|
|
|
|
106
|
|
|
$this->isGenerated(false); |
107
|
|
|
$this->parts[$name][] = $value; |
108
|
|
|
|
109
|
|
|
return $this; |
110
|
|
|
} |
111
|
|
|
|
112
|
|
|
/** |
113
|
|
|
* @param $params |
114
|
|
|
*/ |
115
|
|
|
public function addParams($params) |
116
|
|
|
{ |
117
|
|
|
$this->checkParamSelect($params); |
118
|
|
|
$this->checkParamFrom($params); |
119
|
|
|
$this->checkParamWhere($params); |
120
|
|
|
$this->checkParamOrder($params); |
121
|
|
|
$this->checkParamGroup($params); |
122
|
|
|
$this->checkParamHaving($params); |
123
|
|
|
$this->checkParamLimit($params); |
124
|
|
|
} |
125
|
|
|
|
126
|
|
|
/** |
127
|
|
|
* @param $params |
128
|
|
|
*/ |
129
|
|
|
protected function checkParamSelect($params) |
130
|
|
|
{ |
131
|
|
View Code Duplication |
if (isset($params['select']) && is_array($params['select'])) { |
|
|
|
|
132
|
|
|
call_user_func_array([$this, 'cols'], $params['select']); |
133
|
|
|
} |
134
|
|
|
} |
135
|
|
|
|
136
|
|
|
/** |
137
|
|
|
* @param $params |
138
|
|
|
*/ |
139
|
|
|
protected function checkParamFrom($params) |
140
|
|
|
{ |
141
|
|
|
if (isset($params['from']) && !empty($params['from'])) { |
142
|
|
|
$this->from($params['from']); |
|
|
|
|
143
|
|
|
} |
144
|
|
|
} |
145
|
|
|
|
146
|
|
|
/** |
147
|
|
|
* @param $params |
148
|
|
|
*/ |
149
|
|
|
protected function checkParamWhere($params) |
150
|
|
|
{ |
151
|
|
|
if (isset($params['where']) && is_array($params['where'])) { |
152
|
|
|
foreach ($params['where'] as $condition) { |
153
|
|
|
$condition = (array)$condition; |
154
|
|
|
$this->where( |
155
|
|
|
$condition[0], |
156
|
|
|
isset($condition[1]) ? $condition[1] : null |
157
|
|
|
); |
158
|
|
|
} |
159
|
|
|
} |
160
|
|
|
} |
161
|
|
|
|
162
|
|
|
/** |
163
|
|
|
* @param $string |
164
|
|
|
* @param array $values |
165
|
|
|
* @return $this |
166
|
|
|
*/ |
167
|
|
View Code Duplication |
public function where($string, $values = []) |
|
|
|
|
168
|
|
|
{ |
169
|
|
|
/** @var Condition $this ->_parts[] */ |
170
|
|
|
if ($string) { |
171
|
|
|
if (isset($this->parts['where']) && $this->parts['where'] instanceof Condition) { |
172
|
|
|
$this->parts['where'] = $this->parts['where']->and_($this->getCondition($string, $values)); |
173
|
|
|
} else { |
174
|
|
|
$this->parts['where'] = $this->getCondition($string, $values); |
175
|
|
|
} |
176
|
|
|
} |
177
|
|
|
|
178
|
|
|
return $this; |
179
|
|
|
} |
180
|
|
|
|
181
|
|
|
/** |
182
|
|
|
* @param string $string |
183
|
|
|
* @param array $values |
184
|
|
|
* @return Condition |
185
|
|
|
*/ |
186
|
|
|
public function getCondition($string, $values = []) |
187
|
|
|
{ |
188
|
|
|
if (!is_object($string)) { |
189
|
|
|
$condition = new Condition($string, $values); |
190
|
|
|
$condition->setQuery($this); |
191
|
|
|
} else { |
192
|
|
|
$condition = $string; |
193
|
|
|
} |
194
|
|
|
|
195
|
|
|
return $condition; |
196
|
|
|
} |
197
|
|
|
|
198
|
|
|
/** |
199
|
|
|
* @param $params |
200
|
|
|
*/ |
201
|
|
|
protected function checkParamOrder($params) |
202
|
|
|
{ |
203
|
|
View Code Duplication |
if (isset($params['order']) && !empty($params['order'])) { |
|
|
|
|
204
|
|
|
call_user_func_array([$this, 'order'], $params['order']); |
205
|
|
|
} |
206
|
|
|
} |
207
|
|
|
|
208
|
|
|
/** |
209
|
|
|
* @param $params |
210
|
|
|
*/ |
211
|
|
|
protected function checkParamGroup($params) |
212
|
|
|
{ |
213
|
|
View Code Duplication |
if (isset($params['group']) && !empty($params['group'])) { |
|
|
|
|
214
|
|
|
call_user_func_array([$this, 'group'], [$params['group']]); |
215
|
|
|
} |
216
|
|
|
} |
217
|
|
|
|
218
|
|
|
/** |
219
|
|
|
* @param $params |
220
|
|
|
*/ |
221
|
|
|
protected function checkParamHaving($params) |
222
|
|
|
{ |
223
|
|
View Code Duplication |
if (isset($params['having']) && !empty($params['having'])) { |
|
|
|
|
224
|
|
|
call_user_func_array([$this, 'having'], [$params['having']]); |
225
|
|
|
} |
226
|
|
|
} |
227
|
|
|
|
228
|
|
|
/** |
229
|
|
|
* @param $params |
230
|
|
|
*/ |
231
|
|
|
protected function checkParamLimit($params) |
232
|
|
|
{ |
233
|
|
View Code Duplication |
if (isset($params['limit']) && !empty($params['limit'])) { |
|
|
|
|
234
|
|
|
call_user_func_array([$this, 'limit'], [$params['limit']]); |
235
|
|
|
} |
236
|
|
|
} |
237
|
|
|
|
238
|
|
|
/** |
239
|
|
|
* @param integer $start |
240
|
|
|
* @param bool $offset |
241
|
|
|
* @return $this |
242
|
|
|
*/ |
243
|
|
|
public function limit($start, $offset = false) |
244
|
|
|
{ |
245
|
|
|
$this->parts['limit'] = $start; |
246
|
|
|
if ($offset) { |
247
|
|
|
$this->parts['limit'] .= ','.$offset; |
248
|
|
|
} |
249
|
|
|
|
250
|
|
|
return $this; |
251
|
|
|
} |
252
|
|
|
|
253
|
|
|
/** |
254
|
|
|
* @param $string |
255
|
|
|
* @param array $values |
256
|
|
|
* @return $this |
257
|
|
|
*/ |
258
|
|
View Code Duplication |
public function orWhere($string, $values = []) |
|
|
|
|
259
|
|
|
{ |
260
|
|
|
if ($string) { |
261
|
|
|
if ($this->parts['where'] instanceof Condition) { |
262
|
|
|
$this->parts['where'] = $this->parts['where']->or_($this->getCondition($string, $values)); |
263
|
|
|
} else { |
264
|
|
|
$this->parts['where'] = $this->getCondition($string, $values); |
265
|
|
|
} |
266
|
|
|
} |
267
|
|
|
|
268
|
|
|
return $this; |
269
|
|
|
} |
270
|
|
|
|
271
|
|
|
/** |
272
|
|
|
* @param $string |
273
|
|
|
* @param array $values |
274
|
|
|
* @return $this |
275
|
|
|
*/ |
276
|
|
View Code Duplication |
public function having($string, $values = []) |
|
|
|
|
277
|
|
|
{ |
278
|
|
|
if ($string) { |
279
|
|
|
if ($this->parts['having'] instanceof Condition) { |
280
|
|
|
$this->parts['having'] = $this->parts['having']->and_($this->getCondition($string, $values)); |
281
|
|
|
} else { |
282
|
|
|
$this->parts['having'] = $this->getCondition($string, $values); |
283
|
|
|
} |
284
|
|
|
} |
285
|
|
|
|
286
|
|
|
return $this; |
287
|
|
|
} |
288
|
|
|
|
289
|
|
|
/** |
290
|
|
|
* Escapes data for safe use in SQL queries |
291
|
|
|
* |
292
|
|
|
* @param string $data |
293
|
|
|
* @return string |
294
|
|
|
*/ |
295
|
|
|
public function cleanData($data) |
296
|
|
|
{ |
297
|
|
|
return $this->getManager()->getAdapter()->cleanData($data); |
298
|
|
|
} |
299
|
|
|
|
300
|
|
|
/** |
301
|
|
|
* @return Connection |
302
|
|
|
*/ |
303
|
|
|
public function getManager() |
304
|
|
|
{ |
305
|
|
|
return $this->db; |
306
|
|
|
} |
307
|
|
|
|
308
|
|
|
/** |
309
|
|
|
* @return Result |
310
|
|
|
*/ |
311
|
|
|
public function execute() |
312
|
|
|
{ |
313
|
|
|
return $this->getManager()->execute($this); |
314
|
|
|
} |
315
|
|
|
|
316
|
|
|
/** |
317
|
|
|
* Implements magic method. |
318
|
|
|
* |
319
|
|
|
* @return string This object as a Query string. |
320
|
|
|
*/ |
321
|
|
|
public function __toString() |
322
|
|
|
{ |
323
|
|
|
return $this->getString(); |
324
|
|
|
} |
325
|
|
|
|
326
|
|
|
/** |
327
|
|
|
* @return string |
328
|
|
|
*/ |
329
|
|
|
public function getString() |
330
|
|
|
{ |
331
|
|
|
if ($this->string === null) { |
332
|
|
|
$this->string = (string)$this->assemble(); |
333
|
|
|
} |
334
|
|
|
|
335
|
|
|
return $this->string; |
336
|
|
|
} |
337
|
|
|
|
338
|
|
|
/** |
339
|
|
|
* @return null |
340
|
|
|
*/ |
341
|
|
|
abstract public function assemble(); |
342
|
|
|
|
343
|
|
|
/** |
344
|
|
|
* @return array |
345
|
|
|
*/ |
346
|
|
|
public function getParts() |
347
|
|
|
{ |
348
|
|
|
return $this->parts; |
349
|
|
|
} |
350
|
|
|
|
351
|
|
|
/** |
352
|
|
|
* @return null|string |
353
|
|
|
*/ |
354
|
|
|
protected function assembleWhere() |
355
|
|
|
{ |
356
|
|
|
$where = $this->parseWhere(); |
357
|
|
|
|
358
|
|
|
if (!empty($where)) { |
359
|
|
|
return " WHERE $where"; |
360
|
|
|
} |
361
|
|
|
|
362
|
|
|
return null; |
363
|
|
|
} |
364
|
|
|
|
365
|
|
|
/** |
366
|
|
|
* @return string |
367
|
|
|
*/ |
368
|
|
|
protected function parseWhere() |
369
|
|
|
{ |
370
|
|
|
return is_object($this->parts['where']) ? (string)$this->parts['where'] : ''; |
371
|
|
|
} |
372
|
|
|
|
373
|
|
|
/** |
374
|
|
|
* @return null|string |
375
|
|
|
*/ |
376
|
|
|
protected function assembleLimit() |
377
|
|
|
{ |
378
|
|
|
$limit = $this->getPart('limit'); |
379
|
|
|
if (!empty($limit)) { |
380
|
|
|
return " LIMIT {$this->parts['limit']}"; |
381
|
|
|
} |
382
|
|
|
|
383
|
|
|
return null; |
384
|
|
|
} |
385
|
|
|
|
386
|
|
|
/** |
387
|
|
|
* @param string $name |
388
|
|
|
* @return mixed|null |
389
|
|
|
*/ |
390
|
|
|
public function getPart($name) |
391
|
|
|
{ |
392
|
|
|
return $this->hasPart($name) ? $this->parts[$name] : null; |
393
|
|
|
} |
394
|
|
|
|
395
|
|
|
/** |
396
|
|
|
* @param $name |
397
|
|
|
* @return bool |
398
|
|
|
*/ |
399
|
|
|
public function hasPart($name) |
400
|
|
|
{ |
401
|
|
|
if (!isset($this->parts[$name])) { |
402
|
|
|
return false; |
403
|
|
|
} |
404
|
|
|
if (is_array($this->parts[$name]) && count($this->parts[$name]) < 1) { |
405
|
|
|
return false; |
406
|
|
|
} |
407
|
|
|
if (is_string($this->parts[$name]) && empty($this->parts[$name])) { |
408
|
|
|
return false; |
409
|
|
|
} |
410
|
|
|
|
411
|
|
|
return true; |
412
|
|
|
} |
413
|
|
|
|
414
|
|
|
/** |
415
|
|
|
* @param $name |
416
|
|
|
* @param $value |
417
|
|
|
* @return $this |
418
|
|
|
*/ |
419
|
|
|
protected function setPart($name, $value) |
420
|
|
|
{ |
421
|
|
|
$this->initPart($name); |
422
|
|
|
$this->addPart($name, $value); |
423
|
|
|
|
424
|
|
|
return $this; |
425
|
|
|
} |
426
|
|
|
|
427
|
|
|
/** |
428
|
|
|
* @return string |
429
|
|
|
*/ |
430
|
|
|
protected function getTable() |
431
|
|
|
{ |
432
|
|
|
if (!is_array($this->parts['table']) && count($this->parts['table']) < 1) { |
433
|
|
|
trigger_error("No Table defined", E_USER_WARNING); |
434
|
|
|
} |
435
|
|
|
|
436
|
|
|
return reset($this->parts['table']); |
437
|
|
|
} |
438
|
|
|
|
439
|
|
|
/** |
440
|
|
|
* @return string |
441
|
|
|
*/ |
442
|
|
|
protected function parseHaving() |
443
|
|
|
{ |
444
|
|
|
if (isset($this->parts['having'])) { |
445
|
|
|
return (string)$this->parts['having']; |
446
|
|
|
} |
447
|
|
|
|
448
|
|
|
return ''; |
449
|
|
|
} |
450
|
|
|
|
451
|
|
|
/** |
452
|
|
|
* Parses ORDER BY entries |
453
|
|
|
* |
454
|
|
|
* @return string |
455
|
|
|
*/ |
456
|
|
|
protected function parseOrder() |
457
|
|
|
{ |
458
|
|
|
if (!isset($this->parts['order']) || !is_array($this->parts['order']) || count($this->parts['order']) < 1) { |
459
|
|
|
return false; |
460
|
|
|
} |
461
|
|
|
|
462
|
|
|
$orderParts = []; |
463
|
|
|
|
464
|
|
|
foreach ($this->parts['order'] as $itemOrder) { |
465
|
|
|
if ($itemOrder) { |
466
|
|
|
if (!is_array($itemOrder)) { |
467
|
|
|
$itemOrder = [$itemOrder]; |
468
|
|
|
} |
469
|
|
|
|
470
|
|
|
$column = isset($itemOrder[0]) ? $itemOrder[0] : false; |
471
|
|
|
$type = isset($itemOrder[1]) ? $itemOrder[1] : ''; |
472
|
|
|
$protected = isset($itemOrder[2]) ? $itemOrder[2] : true; |
473
|
|
|
|
474
|
|
|
$column = ($protected ? $this->protect($column) : $column).' '.strtoupper($type); |
475
|
|
|
|
476
|
|
|
$orderParts[] = trim($column); |
477
|
|
|
} |
478
|
|
|
} |
479
|
|
|
|
480
|
|
|
return implode(', ', $orderParts); |
481
|
|
|
} |
482
|
|
|
|
483
|
|
|
/** |
484
|
|
|
* Adds backticks to input |
485
|
|
|
* |
486
|
|
|
* @param string $input |
487
|
|
|
* @return string |
488
|
|
|
*/ |
489
|
|
|
protected function protect($input) |
490
|
|
|
{ |
491
|
|
|
return strpos($input, '(') !== false ? $input : str_replace("`*`", "*", |
492
|
|
|
'`'.str_replace('.', '`.`', $input).'`'); |
493
|
|
|
} |
494
|
|
|
|
495
|
|
|
/** |
496
|
|
|
* Prefixes table names |
497
|
|
|
* |
498
|
|
|
* @param string $table |
499
|
|
|
* @return string |
500
|
|
|
*/ |
501
|
|
|
protected function tableName($table = '') |
502
|
|
|
{ |
503
|
|
|
return $this->getManager()->tableName($table); |
504
|
|
|
} |
505
|
|
|
|
506
|
|
|
/** |
507
|
|
|
* Removes backticks from input |
508
|
|
|
* |
509
|
|
|
* @param string $input |
510
|
|
|
* @return string |
511
|
|
|
*/ |
512
|
|
|
protected function cleanProtected($input) |
513
|
|
|
{ |
514
|
|
|
return str_replace('`', '', $input); |
515
|
|
|
} |
516
|
|
|
} |
517
|
|
|
|
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.