Total Complexity | 52 |
Total Lines | 612 |
Duplicated Lines | 0 % |
Changes | 24 | ||
Bugs | 1 | Features | 0 |
Complex classes like Statement often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use Statement, and based on these observations, apply Extract Interface, too.
1 | <?php |
||
12 | class Statement |
||
13 | { |
||
14 | /** @var Bindings $bindings */ |
||
15 | private $bindings; |
||
16 | |||
17 | /** @var int $bindPos Position for SQL binds. */ |
||
18 | private $bindPos = 0; |
||
19 | |||
20 | /** @var array $named Named binding values. */ |
||
21 | private $named = []; |
||
22 | |||
23 | /** @var array $SQL SQL Statement. */ |
||
24 | private $SQL = []; |
||
25 | |||
26 | /** @var int Position holder for statement processing. */ |
||
27 | private $sqlPos = 0; |
||
28 | |||
29 | /** @var array Raw named placeholders. */ |
||
30 | private $rawNamed = []; |
||
31 | |||
32 | /** @var int $rawPos Position holder for raw statement processing. */ |
||
33 | private $rawPos = 0; |
||
34 | |||
35 | /** @var array $rawSql SQL Statement. */ |
||
36 | private $rawSql = []; |
||
37 | |||
38 | public function __construct(Bindings $bindings) |
||
39 | { |
||
40 | $this->bindings = $bindings; |
||
41 | } |
||
42 | |||
43 | /** |
||
44 | * Due to an outstanding bug (https://bugs.php.net/bug.php?id=70409) |
||
45 | * where filter_var + FILTER_NULL_ON_FAILURE doesn't return null on null, |
||
46 | * I have to do this and I feel bad about it. |
||
47 | * |
||
48 | * @param $value |
||
49 | * |
||
50 | * @return bool|null |
||
51 | */ |
||
52 | private function filterValidateBool($value): ?bool |
||
53 | { |
||
54 | return $value === null |
||
55 | ? null |
||
56 | : filter_var($value, FILTER_VALIDATE_BOOLEAN, FILTER_NULL_ON_FAILURE); |
||
57 | } |
||
58 | |||
59 | |||
60 | /** |
||
61 | * Replace the raw placeholder with raw values. |
||
62 | * |
||
63 | * @param string $sql |
||
64 | * |
||
65 | * @return string |
||
66 | */ |
||
67 | private function rawPlaceholderFill(string $sql): string |
||
68 | { |
||
69 | foreach ($this->rawNamed as $name => $rVal) { |
||
70 | $sql = (string) preg_replace('/' . $name . '\b/', $rVal, $sql); |
||
71 | } |
||
72 | |||
73 | return $sql; |
||
74 | } |
||
75 | |||
76 | /** |
||
77 | * Bind a value to a named parameter. |
||
78 | * |
||
79 | * @param string $name |
||
80 | * @param string|int|float|bool|null $value |
||
81 | * @param int $type |
||
82 | * |
||
83 | * @return Statement |
||
84 | */ |
||
85 | public function bind(string $name, $value, int $type = PDO::PARAM_STR): self |
||
86 | { |
||
87 | $this->named[$name] = array( |
||
88 | 'type' => $type, |
||
89 | 'value' => $value |
||
90 | ); |
||
91 | |||
92 | return $this; |
||
93 | } |
||
94 | |||
95 | /** |
||
96 | * Bind a raw value to a named parameter. |
||
97 | * |
||
98 | * @param string $name |
||
99 | * @param string|int|float|bool $value |
||
100 | * @return Statement |
||
101 | */ |
||
102 | public function rawBind(string $name, $value): self |
||
103 | { |
||
104 | $this->rawNamed[$name] = $value; |
||
105 | |||
106 | return $this; |
||
107 | } |
||
108 | |||
109 | // Bind types |
||
110 | |||
111 | /** |
||
112 | * Bind a boolean value as bool or null. |
||
113 | * Knock, knock. Who's there? Tri-state. |
||
114 | * |
||
115 | * @param int|bool|null $value |
||
116 | * |
||
117 | * @return Statement |
||
118 | * @throws TypeError |
||
119 | */ |
||
120 | public function bBoolNullable($value = null): self |
||
121 | { |
||
122 | $binding = $this->bindings->bBoolNullable($this->filterValidateBool($value)); |
||
123 | $this->bind($this->getNextName(), $binding[0], $binding[1]); |
||
124 | return $this; |
||
125 | } |
||
126 | |||
127 | /** |
||
128 | * Bind a boolean value as bool. |
||
129 | * |
||
130 | * @param int|bool $value |
||
131 | * |
||
132 | * @return Statement |
||
133 | * @throws TypeError |
||
134 | */ |
||
135 | public function bBool($value): self |
||
136 | { |
||
137 | $binding = $this->bindings->bBool($this->filterValidateBool($value)); |
||
138 | $this->bind($this->getNextName(), $binding[0], $binding[1]); |
||
139 | return $this; |
||
140 | } |
||
141 | |||
142 | /** |
||
143 | * Bind a boolean value as int or null. |
||
144 | * Tri-state who? Tri-state Boolean... |
||
145 | * |
||
146 | * @param int|bool|null $value |
||
147 | * |
||
148 | * @return Statement |
||
149 | * @throws TypeError |
||
150 | */ |
||
151 | public function bBoolIntNullable($value = null): self |
||
152 | { |
||
153 | $binding = $this->bindings->bBoolIntNullable($this->filterValidateBool($value)); |
||
154 | $this->bind($this->getNextName(), $binding[0], $binding[1]); |
||
155 | return $this; |
||
156 | } |
||
157 | |||
158 | /** |
||
159 | * Bind a boolean value as int. |
||
160 | * |
||
161 | * @param int|bool $value |
||
162 | * |
||
163 | * @return Statement |
||
164 | * @throws TypeError |
||
165 | */ |
||
166 | public function bBoolInt($value): self |
||
167 | { |
||
168 | $binding = $this->bindings->bBoolInt($this->filterValidateBool($value)); |
||
169 | $this->bind($this->getNextName(), $binding[0], $binding[1]); |
||
170 | return $this; |
||
171 | } |
||
172 | |||
173 | /** |
||
174 | * Bind a date value as date or null. |
||
175 | * YYYY-MM-DD is the proper date format. |
||
176 | * |
||
177 | * @param string|null $value |
||
178 | * |
||
179 | * @return Statement |
||
180 | * @throws TypeError |
||
181 | */ |
||
182 | public function bDateNullable(?string $value = null): self |
||
183 | { |
||
184 | $binding = $this->bindings->bDateNullable($this->filterValidateBool($value)); |
||
|
|||
185 | $this->bind($this->getNextName(), $binding[0], $binding[1]); |
||
186 | return $this; |
||
187 | } |
||
188 | |||
189 | /** |
||
190 | * Bind a date value as date. |
||
191 | * YYYY-MM-DD is the proper date format. |
||
192 | * |
||
193 | * @param string $value |
||
194 | * |
||
195 | * @return Statement |
||
196 | * @throws TypeError |
||
197 | */ |
||
198 | public function bDate(string $value): self |
||
199 | { |
||
200 | $binding = $this->bindings->bDate($value); |
||
201 | $this->bind($this->getNextName(), $binding[0], $binding[1]); |
||
202 | return $this; |
||
203 | } |
||
204 | |||
205 | /** |
||
206 | * Bind a date time value as date time or null. |
||
207 | * YYYY-MM-DD HH:MM:SS is the proper date format. |
||
208 | * |
||
209 | * @param string|null $value |
||
210 | * |
||
211 | * @return Statement |
||
212 | * @throws TypeError |
||
213 | */ |
||
214 | public function bDateTimeNullable(?string $value = null): self |
||
215 | { |
||
216 | $binding = $this->bindings->bDateTimeNullable($value); |
||
217 | $this->bind($this->getNextName(), $binding[0], $binding[1]); |
||
218 | return $this; |
||
219 | } |
||
220 | |||
221 | /** |
||
222 | * Bind a date time value as date time. |
||
223 | * YYYY-MM-DD HH:MM:SS is the proper date format. |
||
224 | * |
||
225 | * @param string $value |
||
226 | * |
||
227 | * @return Statement |
||
228 | * @throws TypeError |
||
229 | */ |
||
230 | public function bDateTime(string $value): self |
||
231 | { |
||
232 | $binding = $this->bindings->bDateTime($value); |
||
233 | $this->bind($this->getNextName(), $binding[0], $binding[1]); |
||
234 | return $this; |
||
235 | } |
||
236 | |||
237 | |||
238 | /** |
||
239 | * Bind a float. |
||
240 | * |
||
241 | * @param string|int|float|null $value |
||
242 | * @param int $decimals |
||
243 | * |
||
244 | * @return Statement |
||
245 | * @throws TypeError |
||
246 | */ |
||
247 | public function bFloatNullable($value = null, $decimals = 3): self |
||
248 | { |
||
249 | $binding = $this->bindings->bFloatNullable($value, $decimals); |
||
250 | $this->rawBind($this->getNextName('raw'), $binding[0]); |
||
251 | return $this; |
||
252 | } |
||
253 | |||
254 | /** |
||
255 | * Bind a float. |
||
256 | * |
||
257 | * @param string|int|float| $value |
||
258 | * @param int $decimals |
||
259 | * |
||
260 | * @return Statement |
||
261 | * @throws TypeError |
||
262 | */ |
||
263 | public function bFloat($value = null, $decimals = 3): self |
||
264 | { |
||
265 | $binding = $this->bindings->bFloat($value, $decimals); |
||
266 | $this->rawBind($this->getNextName('raw'), $binding[0]); |
||
267 | return $this; |
||
268 | } |
||
269 | |||
270 | /** |
||
271 | * Bind an integer or null. |
||
272 | * |
||
273 | * @param string|int|float|bool|null $value |
||
274 | * |
||
275 | * @return Statement |
||
276 | * @throws TypeError |
||
277 | */ |
||
278 | public function bIntNullable($value = null): self |
||
279 | { |
||
280 | $binding = $this->bindings->bIntNullable($value); |
||
281 | $this->bind($this->getNextName(), $binding[0], $binding[1]); |
||
282 | return $this; |
||
283 | } |
||
284 | |||
285 | /** |
||
286 | * Bind an integer. |
||
287 | * |
||
288 | * @param string|int|float|bool $value |
||
289 | * |
||
290 | * @return Statement |
||
291 | * @throws TypeError |
||
292 | */ |
||
293 | public function bInt($value = null): self |
||
294 | { |
||
295 | $binding = $this->bindings->bInt($value); |
||
296 | $this->bind($this->getNextName(), $binding[0], $binding[1]); |
||
297 | return $this; |
||
298 | } |
||
299 | |||
300 | /** |
||
301 | * Convert array of integers to comma separated values. Uses %% |
||
302 | * Great for IN() statements. |
||
303 | * |
||
304 | * @param array $data |
||
305 | * @param int $default |
||
306 | * |
||
307 | * @return Statement |
||
308 | * @throws TypeError |
||
309 | */ |
||
310 | public function bIntArray(array $data, int $default = 0): self |
||
311 | { |
||
312 | $binding = $this->bindings->bIntArray($data, $default); |
||
313 | $this->rawBind($this->getNextName('raw'), $binding[0]); |
||
314 | return $this; |
||
315 | } |
||
316 | |||
317 | /** |
||
318 | * Bind a object or JSON string to a string |
||
319 | * |
||
320 | * @param string|object|null $value |
||
321 | * @param bool $null |
||
322 | * |
||
323 | * @return Statement |
||
324 | * @throws Exception |
||
325 | */ |
||
326 | public function bJSON($value, bool $null = false): self |
||
327 | { |
||
328 | $binding = $this->bindings->bJSON($value, $null); |
||
329 | $this->bind($this->getNextName(), $binding[0], $binding[1]); |
||
330 | return $this; |
||
331 | } |
||
332 | |||
333 | /** |
||
334 | * Create and bind string for LIKE() statements. |
||
335 | * |
||
336 | * @param string $value |
||
337 | * @param bool $ends Ends with? |
||
338 | * @param bool $starts Starts with? |
||
339 | * |
||
340 | * @return Statement |
||
341 | */ |
||
342 | public function bLike(string $value, bool $ends = false, bool $starts = false): self |
||
343 | { |
||
344 | $binding = $this->bindings->bLike($value, $ends, $starts); |
||
345 | $this->bind($this->getNextName(), $binding[0]); |
||
346 | return $this; |
||
347 | } |
||
348 | |||
349 | /** |
||
350 | * !!!DANGER!!! |
||
351 | * Bind a raw value. |
||
352 | * |
||
353 | * @param string|int|float|bool $value |
||
354 | * |
||
355 | * @return Statement |
||
356 | */ |
||
357 | public function bRaw($value): self |
||
358 | { |
||
359 | $binding = $this->bindings->bRaw($value); |
||
360 | $this->rawBind($this->getNextName('raw'), $binding[0]); |
||
361 | return $this; |
||
362 | } |
||
363 | |||
364 | /** |
||
365 | * Bind a string value. |
||
366 | * |
||
367 | * @param string|int|float|bool|null $value |
||
368 | * @param bool $null |
||
369 | * @param int $type |
||
370 | * |
||
371 | * @return Statement |
||
372 | * @throws Exception |
||
373 | */ |
||
374 | public function bStr($value, bool $null = false, int $type = PDO::PARAM_STR): self |
||
375 | { |
||
376 | $binding = $this->bindings->bStr($value, $null, $type); |
||
377 | $this->bind($this->getNextName(), $binding[0], $binding[1]); |
||
378 | return $this; |
||
379 | } |
||
380 | |||
381 | /** |
||
382 | * Convert an array into a string and bind it. |
||
383 | * Great for IN() statements. |
||
384 | * |
||
385 | * @param array $values |
||
386 | * @param string|int|float|bool $default |
||
387 | * |
||
388 | * @return Statement |
||
389 | */ |
||
390 | public function bStrArr(array $values, $default = ''): self |
||
391 | { |
||
392 | $binding = $this->bindings->bStrArr($values, $default); |
||
393 | $this->rawBind($this->getNextName('raw'), $binding[0]); |
||
394 | return $this; |
||
395 | } |
||
396 | |||
397 | // The rest of the helpers |
||
398 | |||
399 | /** |
||
400 | * Name the positions for binding in PDO. |
||
401 | * |
||
402 | * @param string $type |
||
403 | * |
||
404 | * @return string |
||
405 | */ |
||
406 | private function getNextName(string $type = 'bind'): string |
||
407 | { |
||
408 | switch ($type) { |
||
409 | case 'sql': |
||
410 | // sql statement syntax |
||
411 | $ret = sprintf(':pos%d', $this->sqlPos++); |
||
412 | |||
413 | return $ret; |
||
414 | |||
415 | case 'rawSql': |
||
416 | //$ret = sprintf(':raw%d', $this->_rawSql++); |
||
417 | $ret = sprintf(':raw%d', $this->rawPos); |
||
418 | |||
419 | return $ret; |
||
420 | |||
421 | case 'raw': |
||
422 | // raw statement syntax |
||
423 | $ret = sprintf(':raw%d', $this->rawPos++); |
||
424 | |||
425 | return $ret; |
||
426 | |||
427 | case 'bind': |
||
428 | default: |
||
429 | // bind/filling values |
||
430 | $ret = sprintf(':pos%d', $this->bindPos++); |
||
431 | |||
432 | return $ret; |
||
433 | } |
||
434 | } |
||
435 | |||
436 | /** |
||
437 | * Prepare and Execute the SQL statement. |
||
438 | * |
||
439 | * @param PDO $pdo |
||
440 | * |
||
441 | * @return PDOStatement |
||
442 | * @throws Exception |
||
443 | */ |
||
444 | public function execute(PDO $pdo): PDOStatement |
||
445 | { |
||
446 | // Prepare the SQL, force to string in case of null. |
||
447 | // Then replace raw placements with raw values. |
||
448 | $sql = $this->rawPlaceholderFill((string) implode(' ', $this->SQL)); |
||
449 | |||
450 | /** @var PDOStatement $stmt */ |
||
451 | $stmt = $pdo->prepare($sql); |
||
452 | |||
453 | // Bind named parameters. |
||
454 | foreach ($this->named as $name => $sVal) { |
||
455 | switch ($sVal['type']) { |
||
456 | case PDO::PARAM_BOOL: |
||
457 | $stmt->bindValue($name, (bool) $sVal['value'], $sVal['type']); |
||
458 | break; |
||
459 | |||
460 | case PDO::PARAM_NULL: |
||
461 | $stmt->bindValue($name, null); |
||
462 | break; |
||
463 | |||
464 | case PDO::PARAM_INT: |
||
465 | $stmt->bindValue($name, (int) $sVal['value'], $sVal['type']); |
||
466 | break; |
||
467 | |||
468 | case PDO::PARAM_STR: |
||
469 | default: |
||
470 | $stmt->bindValue($name, (string) $sVal['value'], $sVal['type']); |
||
471 | break; |
||
472 | } |
||
473 | } |
||
474 | |||
475 | $stmt->execute(); |
||
476 | return $stmt; |
||
477 | } |
||
478 | |||
479 | /** |
||
480 | * Use for building out what a might look like when it's pass to the DB. |
||
481 | * Used by Statement::getComputed() |
||
482 | * |
||
483 | * @param array $matches |
||
484 | * |
||
485 | * @return mixed |
||
486 | * @throws Exception |
||
487 | */ |
||
488 | private function placeholderFill(array $matches) |
||
489 | { |
||
490 | $key = $matches[0]; |
||
491 | |||
492 | // Can't fill this param. |
||
493 | if (!isset($this->named[$key]) && !isset($this->rawNamed[$key])) { |
||
494 | return $key; |
||
495 | } |
||
496 | |||
497 | if (isset($this->named[$key])) { |
||
498 | // here is the param |
||
499 | $sVal = $this->named[$key]; |
||
500 | |||
501 | switch ($sVal['type']) { |
||
502 | case PDO::PARAM_BOOL: |
||
503 | return $sVal['value'] ? 'TRUE' : 'FALSE'; |
||
504 | |||
505 | case PDO::PARAM_NULL: |
||
506 | return 'NULL'; |
||
507 | |||
508 | case PDO::PARAM_INT: |
||
509 | return (int) $sVal['value']; |
||
510 | |||
511 | case PDO::PARAM_STR: |
||
512 | default: |
||
513 | return "'" . $sVal['value'] . "'"; |
||
514 | } |
||
515 | } |
||
516 | |||
517 | // Since it's not named, it must be raw. |
||
518 | return $this->rawNamed[$key]; |
||
519 | } |
||
520 | |||
521 | /** |
||
522 | * Get name of the placeholder. |
||
523 | * |
||
524 | * @return string |
||
525 | */ |
||
526 | private function placeholderGetName(): string |
||
527 | { |
||
528 | return $this->getNextName('sql'); |
||
529 | } |
||
530 | |||
531 | /** |
||
532 | * Get name of the raw placeholder. |
||
533 | * |
||
534 | * @return string |
||
535 | */ |
||
536 | private function rawPlaceHolderGetName(): string |
||
537 | { |
||
538 | return $this->getNextName('rawSql'); |
||
539 | } |
||
540 | |||
541 | /** |
||
542 | * Builds up the SQL parameterized statement. |
||
543 | * |
||
544 | * @param string $text |
||
545 | * |
||
546 | * @return Statement |
||
547 | */ |
||
548 | public function sql(string $text): self |
||
549 | { |
||
550 | // Replace positioned placeholders with named placeholders (first value). |
||
551 | // Force to string, in the case of null. |
||
552 | $text = (string) preg_replace_callback('/\?/m', function () { |
||
553 | return $this->placeholderGetName(); |
||
554 | }, $text); |
||
555 | |||
556 | $text = (string) preg_replace_callback('/%%/m', function () { |
||
557 | return $this->rawPlaceholderGetName(); |
||
558 | }, $text); |
||
559 | |||
560 | $this->SQL[] = $text; |
||
561 | |||
562 | return $this; |
||
563 | } |
||
564 | |||
565 | /** |
||
566 | * Reset / Clear out properties. |
||
567 | * |
||
568 | * @return Statement |
||
569 | */ |
||
570 | public function reset(): self |
||
571 | { |
||
572 | $this->bindPos = 0; |
||
573 | $this->named = []; |
||
574 | $this->SQL = []; |
||
575 | $this->sqlPos = 0; |
||
576 | $this->rawNamed = array(); |
||
577 | $this->rawPos = 0; |
||
578 | $this->rawSql = array(); |
||
579 | |||
580 | return $this; |
||
581 | } |
||
582 | |||
583 | /** |
||
584 | * Create what the SQL query string might look like. |
||
585 | * Great for debugging. YMMV though. |
||
586 | * |
||
587 | * @return string |
||
588 | */ |
||
589 | public function getComputed(): string |
||
590 | { |
||
591 | // Merge SQL together |
||
592 | $sql = implode("\n", $this->SQL); |
||
593 | |||
594 | // Replace positioned placeholders with named placeholders (first value). |
||
595 | // Force to string, in the case of null. |
||
596 | $sql = (string) preg_replace_callback('/:[a-z0-9_]+/m', function ($matches) { |
||
597 | return $this->placeholderFill($matches); |
||
598 | }, $sql); |
||
599 | |||
600 | return $sql; |
||
601 | } |
||
602 | |||
603 | /** |
||
604 | * Return the SQL as a string. |
||
605 | * |
||
606 | * @return string |
||
607 | */ |
||
608 | public function __toString(): string |
||
611 | } |
||
612 | |||
613 | /** |
||
614 | * Magic Method for debugging. |
||
615 | * |
||
616 | * @return array |
||
617 | */ |
||
618 | public function __debugInfo(): array |
||
624 | ]; |
||
625 | } |
||
626 | } |
||
627 |