1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* |
4
|
|
|
* This file is part of Aura for PHP. |
5
|
|
|
* |
6
|
|
|
* @license http://opensource.org/licenses/bsd-license.php BSD |
7
|
|
|
* |
8
|
|
|
*/ |
9
|
|
|
namespace Aura\SqlQuery; |
10
|
|
|
|
11
|
|
|
use Aura\SqlQuery\Common\LimitInterface; |
12
|
|
|
use Aura\SqlQuery\Common\LimitOffsetInterface; |
13
|
|
|
use Aura\SqlQuery\Common\SubselectInterface; |
14
|
|
|
|
15
|
|
|
/** |
16
|
|
|
* |
17
|
|
|
* Abstract query object. |
18
|
|
|
* |
19
|
|
|
* @package Aura.SqlQuery |
20
|
|
|
* |
21
|
|
|
*/ |
22
|
|
|
abstract class AbstractQuery |
23
|
|
|
{ |
24
|
|
|
/** |
25
|
|
|
* |
26
|
|
|
* Data to be bound to the query. |
27
|
|
|
* |
28
|
|
|
* @var array |
29
|
|
|
* |
30
|
|
|
*/ |
31
|
|
|
protected $bind_values = array(); |
32
|
|
|
|
33
|
|
|
/** |
34
|
|
|
* |
35
|
|
|
* The list of WHERE conditions. |
36
|
|
|
* |
37
|
|
|
* @var array |
38
|
|
|
* |
39
|
|
|
*/ |
40
|
|
|
protected $where = array(); |
41
|
|
|
|
42
|
|
|
/** |
43
|
|
|
* |
44
|
|
|
* ORDER BY these columns. |
45
|
|
|
* |
46
|
|
|
* @var array |
47
|
|
|
* |
48
|
|
|
*/ |
49
|
|
|
protected $order_by = array(); |
50
|
|
|
|
51
|
|
|
/** |
52
|
|
|
* |
53
|
|
|
* The number of rows to select |
54
|
|
|
* |
55
|
|
|
* @var int |
56
|
|
|
* |
57
|
|
|
*/ |
58
|
|
|
protected $limit = 0; |
59
|
|
|
|
60
|
|
|
/** |
61
|
|
|
* |
62
|
|
|
* Return rows after this offset. |
63
|
|
|
* |
64
|
|
|
* @var int |
65
|
|
|
* |
66
|
|
|
*/ |
67
|
|
|
protected $offset = 0; |
68
|
|
|
|
69
|
|
|
/** |
70
|
|
|
* |
71
|
|
|
* The list of flags. |
72
|
|
|
* |
73
|
|
|
* @var array |
74
|
|
|
* |
75
|
|
|
*/ |
76
|
|
|
protected $flags = array(); |
77
|
|
|
|
78
|
|
|
/** |
79
|
|
|
* |
80
|
|
|
* A helper for quoting identifier names. |
81
|
|
|
* |
82
|
|
|
* @var Quoter |
83
|
|
|
* |
84
|
|
|
*/ |
85
|
|
|
protected $quoter; |
86
|
|
|
|
87
|
|
|
/** |
88
|
|
|
* |
89
|
|
|
* Prefix to use on placeholders for "sequential" bound values; used for |
90
|
|
|
* deconfliction when merging bound values from sub-selects, etc. |
91
|
|
|
* |
92
|
|
|
* @var mixed |
93
|
|
|
* |
94
|
|
|
*/ |
95
|
|
|
protected $seq_bind_prefix = ''; |
96
|
|
|
|
97
|
|
|
/** |
98
|
|
|
* |
99
|
|
|
* Constructor. |
100
|
|
|
* |
101
|
|
|
* @param Quoter $quoter A helper for quoting identifier names. |
102
|
|
|
* |
103
|
|
|
* @param string $seq_bind_prefix A prefix for rewritten sequential-binding |
104
|
|
|
* placeholders (@see getSeqPlaceholder()). |
105
|
|
|
* |
106
|
|
|
*/ |
107
|
397 |
|
public function __construct(Quoter $quoter, $seq_bind_prefix = '') |
108
|
|
|
{ |
109
|
397 |
|
$this->quoter = $quoter; |
110
|
397 |
|
$this->seq_bind_prefix = $seq_bind_prefix; |
111
|
397 |
|
} |
112
|
|
|
|
113
|
|
|
/** |
114
|
|
|
* |
115
|
|
|
* Returns the prefix for rewritten sequential-binding placeholders |
116
|
|
|
* (@see getSeqPlaceholder()). |
117
|
|
|
* |
118
|
|
|
* @return string |
119
|
|
|
* |
120
|
|
|
*/ |
121
|
1 |
|
public function getSeqBindPrefix() |
122
|
|
|
{ |
123
|
1 |
|
return $this->seq_bind_prefix; |
124
|
|
|
} |
125
|
|
|
|
126
|
|
|
/** |
127
|
|
|
* |
128
|
|
|
* Returns this query object as an SQL statement string. |
129
|
|
|
* |
130
|
|
|
* @return string |
131
|
|
|
* |
132
|
|
|
*/ |
133
|
242 |
|
public function __toString() |
134
|
|
|
{ |
135
|
242 |
|
return $this->getStatement(); |
136
|
|
|
} |
137
|
|
|
|
138
|
|
|
/** |
139
|
|
|
* |
140
|
|
|
* Returns this query object as an SQL statement string. |
141
|
|
|
* |
142
|
|
|
* @return string |
143
|
|
|
* |
144
|
|
|
*/ |
145
|
68 |
|
public function getStatement() |
146
|
|
|
{ |
147
|
68 |
|
return $this->build(); |
148
|
|
|
} |
149
|
|
|
|
150
|
|
|
/** |
151
|
|
|
* |
152
|
|
|
* Builds this query object into a string. |
153
|
|
|
* |
154
|
|
|
* @return string |
155
|
|
|
* |
156
|
|
|
*/ |
157
|
|
|
abstract protected function build(); |
158
|
|
|
|
159
|
|
|
/** |
160
|
|
|
* |
161
|
|
|
* Returns the prefix to use when quoting identifier names. |
162
|
|
|
* |
163
|
|
|
* @return string |
164
|
|
|
* |
165
|
|
|
*/ |
166
|
251 |
|
public function getQuoteNamePrefix() |
167
|
|
|
{ |
168
|
251 |
|
return $this->quoter->getQuoteNamePrefix(); |
169
|
|
|
} |
170
|
|
|
|
171
|
|
|
/** |
172
|
|
|
* |
173
|
|
|
* Returns the suffix to use when quoting identifier names. |
174
|
|
|
* |
175
|
|
|
* @return string |
176
|
|
|
* |
177
|
|
|
*/ |
178
|
251 |
|
public function getQuoteNameSuffix() |
179
|
|
|
{ |
180
|
251 |
|
return $this->quoter->getQuoteNameSuffix(); |
181
|
|
|
} |
182
|
|
|
|
183
|
|
|
/** |
184
|
|
|
* |
185
|
|
|
* Returns an array as an indented comma-separated values string. |
186
|
|
|
* |
187
|
|
|
* @param array $list The values to convert. |
188
|
|
|
* |
189
|
|
|
* @return string |
190
|
|
|
* |
191
|
|
|
*/ |
192
|
219 |
|
protected function indentCsv(array $list) |
193
|
|
|
{ |
194
|
219 |
|
return PHP_EOL . ' ' |
195
|
219 |
|
. implode(',' . PHP_EOL . ' ', $list); |
196
|
|
|
} |
197
|
|
|
|
198
|
|
|
/** |
199
|
|
|
* |
200
|
|
|
* Returns an array as an indented string. |
201
|
|
|
* |
202
|
|
|
* @param array $list The values to convert. |
203
|
|
|
* |
204
|
|
|
* @return string |
205
|
|
|
* |
206
|
|
|
*/ |
207
|
71 |
|
protected function indent(array $list) |
208
|
|
|
{ |
209
|
71 |
|
return PHP_EOL . ' ' |
210
|
71 |
|
. implode(PHP_EOL . ' ', $list); |
211
|
|
|
} |
212
|
|
|
|
213
|
|
|
/** |
214
|
|
|
* |
215
|
|
|
* Binds multiple values to placeholders; merges with existing values. |
216
|
|
|
* |
217
|
|
|
* @param array $bind_values Values to bind to placeholders. |
218
|
|
|
* |
219
|
|
|
* @return $this |
220
|
|
|
* |
221
|
|
|
*/ |
222
|
31 |
|
public function bindValues(array $bind_values) |
223
|
|
|
{ |
224
|
|
|
// array_merge() renumbers integer keys, which is bad for |
225
|
|
|
// question-mark placeholders |
226
|
31 |
|
foreach ($bind_values as $key => $val) { |
227
|
31 |
|
$this->bindValue($key, $val); |
228
|
|
|
} |
229
|
31 |
|
return $this; |
230
|
|
|
} |
231
|
|
|
|
232
|
|
|
/** |
233
|
|
|
* |
234
|
|
|
* Binds a single value to the query. |
235
|
|
|
* |
236
|
|
|
* @param string $name The placeholder name or number. |
237
|
|
|
* |
238
|
|
|
* @param mixed $value The value to bind to the placeholder. |
239
|
|
|
* |
240
|
|
|
* @return $this |
241
|
|
|
* |
242
|
|
|
*/ |
243
|
72 |
|
public function bindValue($name, $value) |
244
|
|
|
{ |
245
|
72 |
|
$this->bind_values[$name] = $value; |
246
|
72 |
|
return $this; |
247
|
|
|
} |
248
|
|
|
|
249
|
|
|
/** |
250
|
|
|
* |
251
|
|
|
* Gets the values to bind to placeholders. |
252
|
|
|
* |
253
|
|
|
* @return array |
254
|
|
|
* |
255
|
|
|
*/ |
256
|
126 |
|
public function getBindValues() |
257
|
|
|
{ |
258
|
126 |
|
return $this->bind_values; |
259
|
|
|
} |
260
|
|
|
|
261
|
|
|
/** |
262
|
|
|
* |
263
|
|
|
* Reset all values bound to named placeholders. |
264
|
|
|
* |
265
|
|
|
* @return $this |
266
|
|
|
* |
267
|
|
|
*/ |
268
|
|
|
public function resetBindValues() |
269
|
|
|
{ |
270
|
|
|
$this->bind_values = array(); |
271
|
|
|
return $this; |
272
|
|
|
} |
273
|
|
|
|
274
|
|
|
/** |
275
|
|
|
* |
276
|
|
|
* Builds the flags as a space-separated string. |
277
|
|
|
* |
278
|
|
|
* @return string |
279
|
|
|
* |
280
|
|
|
*/ |
281
|
252 |
|
protected function buildFlags() |
282
|
|
|
{ |
283
|
252 |
|
if (! $this->flags) { |
|
|
|
|
284
|
214 |
|
return ''; // not applicable |
285
|
|
|
} |
286
|
|
|
|
287
|
38 |
|
return ' ' . implode(' ', array_keys($this->flags)); |
288
|
|
|
} |
289
|
|
|
|
290
|
|
|
/** |
291
|
|
|
* |
292
|
|
|
* Sets or unsets specified flag. |
293
|
|
|
* |
294
|
|
|
* @param string $flag Flag to set or unset |
295
|
|
|
* |
296
|
|
|
* @param bool $enable Flag status - enabled or not (default true) |
297
|
|
|
* |
298
|
|
|
* @return null |
299
|
|
|
* |
300
|
|
|
*/ |
301
|
43 |
|
protected function setFlag($flag, $enable = true) |
302
|
|
|
{ |
303
|
43 |
|
if ($enable) { |
304
|
43 |
|
$this->flags[$flag] = true; |
305
|
|
|
} else { |
306
|
5 |
|
unset($this->flags[$flag]); |
307
|
|
|
} |
308
|
43 |
|
} |
309
|
|
|
|
310
|
|
|
/** |
311
|
|
|
* |
312
|
|
|
* Reset all query flags. |
313
|
|
|
* |
314
|
|
|
* @return $this |
315
|
|
|
* |
316
|
|
|
*/ |
317
|
15 |
|
public function resetFlags() |
318
|
|
|
{ |
319
|
15 |
|
$this->flags = array(); |
320
|
15 |
|
return $this; |
321
|
|
|
} |
322
|
|
|
|
323
|
|
|
/** |
324
|
|
|
* |
325
|
|
|
* Adds a WHERE condition to the query by AND or OR. If the condition has |
326
|
|
|
* ?-placeholders, additional arguments to the method will be bound to |
327
|
|
|
* those placeholders sequentially. |
328
|
|
|
* |
329
|
|
|
* @param string $andor Add the condition using this operator, typically |
330
|
|
|
* 'AND' or 'OR'. |
331
|
|
|
* |
332
|
|
|
* @param array $args Arguments for adding the condition. |
333
|
|
|
* |
334
|
|
|
* @return $this |
335
|
|
|
* |
336
|
|
|
*/ |
337
|
60 |
|
protected function addWhere($andor, $args) |
338
|
|
|
{ |
339
|
60 |
|
$this->addClauseCondWithBind('where', $andor, $args); |
340
|
60 |
|
return $this; |
341
|
|
|
} |
342
|
|
|
|
343
|
|
|
/** |
344
|
|
|
* |
345
|
|
|
* Adds conditions and binds values to a clause. |
346
|
|
|
* |
347
|
|
|
* @param string $clause The clause to work with, typically 'where' or |
348
|
|
|
* 'having'. |
349
|
|
|
* |
350
|
|
|
* @param string $andor Add the condition using this operator, typically |
351
|
|
|
* 'AND' or 'OR'. |
352
|
|
|
* |
353
|
|
|
* @param array $args Arguments for adding the condition. |
354
|
|
|
* |
355
|
|
|
* @return null |
356
|
|
|
* |
357
|
|
|
*/ |
358
|
70 |
|
protected function addClauseCondWithBind($clause, $andor, $args) |
359
|
|
|
{ |
360
|
|
|
// remove the condition from the args and quote names in it |
361
|
70 |
|
$cond = array_shift($args); |
362
|
70 |
|
$cond = $this->rebuildCondAndBindValues($cond, $args); |
363
|
|
|
|
364
|
|
|
// add condition to clause; $this->where |
365
|
70 |
|
$clause =& $this->$clause; |
366
|
70 |
|
if ($clause) { |
367
|
49 |
|
$clause[] = "$andor $cond"; |
368
|
|
|
} else { |
369
|
70 |
|
$clause[] = $cond; |
370
|
|
|
} |
371
|
70 |
|
} |
372
|
|
|
|
373
|
|
|
/** |
374
|
|
|
* |
375
|
|
|
* Rebuilds a condition string, replacing sequential placeholders with |
376
|
|
|
* named placeholders, and binding the sequential values to the named |
377
|
|
|
* placeholders. |
378
|
|
|
* |
379
|
|
|
* @param string $cond The condition with sequential placeholders. |
380
|
|
|
* |
381
|
|
|
* @param array $bind_values The values to bind to the sequential |
382
|
|
|
* placeholders under their named versions. |
383
|
|
|
* |
384
|
|
|
* @return string The rebuilt condition string. |
385
|
|
|
* |
386
|
|
|
*/ |
387
|
120 |
|
protected function rebuildCondAndBindValues($cond, array $bind_values) |
388
|
|
|
{ |
389
|
120 |
|
$cond = $this->quoter->quoteNamesIn($cond); |
390
|
|
|
|
391
|
|
|
// bind values against ?-mark placeholders, but because PDO is finicky |
392
|
|
|
// about the numbering of sequential placeholders, convert each ?-mark |
393
|
|
|
// to a named placeholder |
394
|
120 |
|
$parts = preg_split('/(\?)/', $cond, null, PREG_SPLIT_DELIM_CAPTURE); |
395
|
120 |
|
foreach ($parts as $key => $val) { |
396
|
120 |
|
if ($val != '?') { |
397
|
120 |
|
continue; |
398
|
|
|
} |
399
|
|
|
|
400
|
80 |
|
$bind_value = array_shift($bind_values); |
401
|
80 |
|
if ($bind_value instanceof SubselectInterface) { |
402
|
10 |
|
$parts[$key] = $bind_value->getStatement(); |
403
|
10 |
|
$this->bind_values = array_merge( |
404
|
10 |
|
$this->bind_values, |
405
|
10 |
|
$bind_value->getBindValues() |
406
|
|
|
); |
407
|
10 |
|
continue; |
408
|
|
|
} |
409
|
|
|
|
410
|
75 |
|
if (is_array($bind_value) && !empty($bind_value)) { |
411
|
5 |
|
$part = ''; |
412
|
5 |
|
$ind = 0; |
413
|
5 |
|
$subCount = count($bind_value); |
414
|
5 |
|
foreach ($bind_value as $subValue) { |
415
|
5 |
|
$seqPlaceholder = $this->getSeqPlaceholder(); |
416
|
5 |
|
$part .= ':' . $seqPlaceholder; |
417
|
5 |
|
if ($subCount > $ind + 1) $part .= ', '; |
418
|
5 |
|
$this->bind_values[$seqPlaceholder] = $subValue; |
419
|
5 |
|
$ind++; |
420
|
|
|
} |
421
|
5 |
|
$parts[$key] = $part; |
422
|
5 |
|
continue; |
423
|
|
|
} |
424
|
|
|
|
425
|
75 |
|
$placeholder = $this->getSeqPlaceholder(); |
426
|
75 |
|
$parts[$key] = ':' . $placeholder; |
427
|
75 |
|
$this->bind_values[$placeholder] = $bind_value; |
428
|
|
|
} |
429
|
|
|
|
430
|
120 |
|
$cond = implode('', $parts); |
431
|
120 |
|
return $cond; |
432
|
|
|
} |
433
|
|
|
|
434
|
|
|
/** |
435
|
|
|
* |
436
|
|
|
* Gets the current sequential placeholder name. |
437
|
|
|
* |
438
|
|
|
* @return string |
439
|
|
|
* |
440
|
|
|
*/ |
441
|
75 |
|
protected function getSeqPlaceholder() |
442
|
|
|
{ |
443
|
75 |
|
$i = count($this->bind_values) + 1; |
444
|
75 |
|
return $this->seq_bind_prefix . "_{$i}_"; |
445
|
|
|
} |
446
|
|
|
|
447
|
|
|
/** |
448
|
|
|
* |
449
|
|
|
* Builds the `WHERE` clause of the statement. |
450
|
|
|
* |
451
|
|
|
* @return string |
452
|
|
|
* |
453
|
|
|
*/ |
454
|
206 |
|
protected function buildWhere() |
455
|
|
|
{ |
456
|
206 |
|
if (! $this->where) { |
|
|
|
|
457
|
151 |
|
return ''; // not applicable |
458
|
|
|
} |
459
|
|
|
|
460
|
60 |
|
return PHP_EOL . 'WHERE' . $this->indent($this->where); |
461
|
|
|
} |
462
|
|
|
|
463
|
|
|
/** |
464
|
|
|
* |
465
|
|
|
* Adds a column order to the query. |
466
|
|
|
* |
467
|
|
|
* @param array $spec The columns and direction to order by. |
468
|
|
|
* |
469
|
|
|
* @return $this |
470
|
|
|
* |
471
|
|
|
*/ |
472
|
9 |
|
protected function addOrderBy(array $spec) |
473
|
|
|
{ |
474
|
9 |
|
foreach ($spec as $col) { |
475
|
9 |
|
$this->order_by[] = $this->quoter->quoteNamesIn($col); |
476
|
|
|
} |
477
|
9 |
|
return $this; |
478
|
|
|
} |
479
|
|
|
|
480
|
|
|
/** |
481
|
|
|
* |
482
|
|
|
* Builds the `ORDER BY ...` clause of the statement. |
483
|
|
|
* |
484
|
|
|
* @return string |
485
|
|
|
* |
486
|
|
|
*/ |
487
|
206 |
|
protected function buildOrderBy() |
488
|
|
|
{ |
489
|
206 |
|
if (! $this->order_by) { |
|
|
|
|
490
|
197 |
|
return ''; // not applicable |
491
|
|
|
} |
492
|
|
|
|
493
|
9 |
|
return PHP_EOL . 'ORDER BY' . $this->indentCsv($this->order_by); |
494
|
|
|
} |
495
|
|
|
|
496
|
|
|
/** |
497
|
|
|
* |
498
|
|
|
* Builds the `LIMIT ... OFFSET` clause of the statement. |
499
|
|
|
* |
500
|
|
|
* Note that this will allow OFFSET values with a LIMIT. |
501
|
|
|
* |
502
|
|
|
* @return string |
503
|
|
|
* |
504
|
|
|
*/ |
505
|
172 |
|
protected function buildLimit() |
506
|
|
|
{ |
507
|
172 |
|
$clause = ''; |
508
|
172 |
|
$limit = $this instanceof LimitInterface && $this->limit; |
509
|
172 |
|
$offset = $this instanceof LimitOffsetInterface && $this->offset; |
510
|
|
|
|
511
|
172 |
|
if ($limit) { |
512
|
19 |
|
$clause .= "LIMIT {$this->limit}"; |
513
|
|
|
} |
514
|
|
|
|
515
|
172 |
|
if ($offset) { |
516
|
10 |
|
$clause .= " OFFSET {$this->offset}"; |
517
|
|
|
} |
518
|
|
|
|
519
|
172 |
|
if ($clause) { |
520
|
19 |
|
$clause = PHP_EOL . trim($clause); |
521
|
|
|
} |
522
|
|
|
|
523
|
172 |
|
return $clause; |
524
|
|
|
} |
525
|
|
|
} |
526
|
|
|
|
This check marks implicit conversions of arrays to boolean values in a comparison. While in PHP an empty array is considered to be equal (but not identical) to false, this is not always apparent.
Consider making the comparison explicit by using
empty(..)
or! empty(...)
instead.