Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
99.07% |
321 / 324 |
|
85.71% |
18 / 21 |
CRAP | |
0.00% |
0 / 1 |
| Expression | |
99.07% |
321 / 324 |
|
85.71% |
18 / 21 |
164 | |
0.00% |
0 / 1 |
| parse | |
100.00% |
47 / 47 |
|
100.00% |
1 / 1 |
14 | |||
| unwrapExpression | |
95.24% |
20 / 21 |
|
0.00% |
0 / 1 |
13 | |||
| getColumnEnd | |
94.74% |
18 / 19 |
|
0.00% |
0 / 1 |
12.02 | |||
| parseExpressions | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
2 | |||
| prepareExpression | |
100.00% |
29 / 29 |
|
100.00% |
1 / 1 |
14 | |||
| prepareExpressions | |
100.00% |
18 / 18 |
|
100.00% |
1 / 1 |
8 | |||
| convertExpressionToShorthand | |
100.00% |
30 / 30 |
|
100.00% |
1 / 1 |
15 | |||
| convertExpressionsToShorthand | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
2 | |||
| convertExpressionsToStructured | |
100.00% |
25 / 25 |
|
100.00% |
1 / 1 |
13 | |||
| splitBetweenValues | |
83.33% |
5 / 6 |
|
0.00% |
0 / 1 |
4.07 | |||
| isShorthand | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
7 | |||
| parseShorthand | |
100.00% |
46 / 46 |
|
100.00% |
1 / 1 |
15 | |||
| applyExpression | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
2 | |||
| applyParam | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
2 | |||
| buildNullExpression | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
2 | |||
| buildInExpression | |
100.00% |
20 / 20 |
|
100.00% |
1 / 1 |
8 | |||
| buildBetweenExpression | |
100.00% |
18 / 18 |
|
100.00% |
1 / 1 |
7 | |||
| buildComparisonExpression | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
6 | |||
| stripIdQuotes | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
7 | |||
| stripQuotes | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
5 | |||
| quote | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
6 | |||
| 1 | <?php |
| 2 | declare(strict_types=1); |
| 3 | /** |
| 4 | * Pop PHP Framework (https://www.popphp.org/) |
| 5 | * |
| 6 | * @link https://github.com/popphp/popphp-framework |
| 7 | * @author Nick Sagona, III <nick@popphp.org> |
| 8 | * @copyright Copyright (c) 2009-2026 Nick Sagona, III |
| 9 | * @license https://www.popphp.org/license New BSD License |
| 10 | */ |
| 11 | |
| 12 | /** |
| 13 | * @namespace |
| 14 | */ |
| 15 | namespace Pop\Db\Sql\Parser; |
| 16 | |
| 17 | use Pop\Db\Sql\AbstractSql; |
| 18 | |
| 19 | /** |
| 20 | * Predicate expression parser class |
| 21 | * |
| 22 | * @category Pop |
| 23 | * @package Pop\Db |
| 24 | * @author Nick Sagona, III <nick@popphp.org> |
| 25 | * @copyright Copyright (c) 2009-2026 Nick Sagona, III |
| 26 | * @license https://www.popphp.org/license New BSD License |
| 27 | * @version 7.0.0 |
| 28 | */ |
| 29 | class Expression |
| 30 | { |
| 31 | |
| 32 | /** |
| 33 | * Allowed operators |
| 34 | * @var array |
| 35 | */ |
| 36 | protected static array $operators = [ |
| 37 | '>=', '<=', '!=', '=', '>', '<', |
| 38 | 'NOT LIKE', 'LIKE', 'NOT BETWEEN', 'BETWEEN', |
| 39 | 'NOT IN', 'IN', 'IS NOT NULL', 'IS NULL' |
| 40 | ]; |
| 41 | |
| 42 | /** |
| 43 | * Method to parse a predicate string expression into its components |
| 44 | * |
| 45 | * @param string $expression |
| 46 | * @throws Exception |
| 47 | * @return array |
| 48 | */ |
| 49 | public static function parse(string $expression): array |
| 50 | { |
| 51 | $column = null; |
| 52 | $operator = null; |
| 53 | $value = null; |
| 54 | |
| 55 | // A predicate set renders its predicates wrapped in parentheses, so an expression can |
| 56 | // come back in that form - unwrap it before looking for the column |
| 57 | $expression = self::unwrapExpression($expression); |
| 58 | $columnEnd = self::getColumnEnd($expression); |
| 59 | $columnString = ($columnEnd !== false) ? substr($expression, 0, $columnEnd) : $expression; |
| 60 | |
| 61 | if (Keyword::indexOf($expression, ' NULL') !== false) { |
| 62 | $column = self::stripIdQuotes(trim($columnString)); |
| 63 | $operator = (Keyword::indexOf($expression, ' IS NOT NULL') !== false) ? 'IS NOT NULL' : 'IS NULL'; |
| 64 | } else if (Keyword::indexOf($expression, ' IN ') !== false) { |
| 65 | $column = self::stripIdQuotes(trim($columnString)); |
| 66 | $operator = (Keyword::indexOf($expression, ' NOT IN ') !== false) ? 'NOT IN' : 'IN'; |
| 67 | // Look for the value list after the column, so that a function call in the column |
| 68 | // does not get mistaken for it |
| 69 | $values = substr($expression, (strpos($expression, '(', strlen($columnString)) + 1)); |
| 70 | $values = substr($values, 0, strrpos($values, ')')); |
| 71 | $values = array_map(function($value) { |
| 72 | return \Pop\Db\Sql\Parser\Expression::stripQuotes(trim($value)); |
| 73 | }, explode(',', $values)); |
| 74 | $value = $values; |
| 75 | } else if (Keyword::indexOf($expression, ' BETWEEN ') !== false) { |
| 76 | $column = self::stripIdQuotes(trim($columnString)); |
| 77 | $operator = (Keyword::indexOf($expression, ' NOT BETWEEN ') !== false) ? 'NOT BETWEEN' : 'BETWEEN'; |
| 78 | $value1 = substr($expression, (stripos($expression, 'BETWEEN ', strlen($columnString)) + 8)); |
| 79 | $value1 = trim(substr($value1, 0, stripos($value1, ' AND '))); |
| 80 | $value2 = trim(substr($expression, (stripos($expression, ' AND ', strlen($columnString)) + 5))); |
| 81 | $value = '(' . self::stripQuotes($value1) . ' AND ' . self::stripQuotes($value2) . ')'; |
| 82 | } else if (Keyword::indexOf($expression, ' LIKE ') !== false) { |
| 83 | $column = self::stripIdQuotes(trim($columnString)); |
| 84 | $operator = (Keyword::indexOf($expression, ' NOT LIKE ') !== false) ? 'NOT LIKE' : 'LIKE'; |
| 85 | $value = self::stripQuotes(trim(substr($expression, (stripos($expression, ' LIKE ', strlen($columnString)) + 6)))); |
| 86 | } else { |
| 87 | $column = $columnString; |
| 88 | $operator = ltrim(substr($expression, strlen($columnString))); |
| 89 | $operatorEnd = strpos($operator, ' '); |
| 90 | $operator = ($operatorEnd !== false) ? substr($operator, 0, $operatorEnd) : $operator; |
| 91 | $value = self::stripQuotes( |
| 92 | trim(substr($expression, (strpos($expression, $operator, strlen($columnString)) + strlen($operator)))) |
| 93 | ); |
| 94 | } |
| 95 | |
| 96 | if (!in_array($operator, self::$operators)) { |
| 97 | throw new Exception("Error: The operator '" . $operator . "' is not allowed."); |
| 98 | } |
| 99 | |
| 100 | // A column shaped like a function call has to be one the SQL function whitelist accepts. |
| 101 | // Anything else would be handed to quoteId() and come back as a bogus identifier such as |
| 102 | // `SUM(id + 1)`, which errors on MySQL/PostgreSQL and silently reads as a text literal on |
| 103 | // SQLite. AbstractSql::isSupportedFunctionCall() is deliberately strict and is not |
| 104 | // loosened here, so an argument list carrying operators or comment markers is refused. |
| 105 | if ((str_contains($column, '(')) && (!AbstractSql::isSupportedFunctionCall($column))) { |
| 106 | throw new Exception( |
| 107 | "Error: The column '" . $column . "' is not a valid column name or supported function call." |
| 108 | ); |
| 109 | } |
| 110 | |
| 111 | return [ |
| 112 | 'column' => $column, |
| 113 | 'operator' => $operator, |
| 114 | 'value' => $value |
| 115 | ]; |
| 116 | } |
| 117 | |
| 118 | /** |
| 119 | * Strip one layer of enclosing parentheses from a predicate expression |
| 120 | * |
| 121 | * Only strips when the opening parenthesis is matched by the very last character, so that |
| 122 | * '(id = 1)' is unwrapped while '(a = 1) AND (b = 2)' and 'COUNT(*) > 1' are left alone. |
| 123 | * |
| 124 | * @param string $expression |
| 125 | * @return string |
| 126 | */ |
| 127 | protected static function unwrapExpression(string $expression): string |
| 128 | { |
| 129 | $expression = trim($expression); |
| 130 | |
| 131 | if ((!str_starts_with($expression, '(')) || (!str_ends_with($expression, ')'))) { |
| 132 | return $expression; |
| 133 | } |
| 134 | |
| 135 | $depth = 0; |
| 136 | $inQuote = null; |
| 137 | $length = strlen($expression); |
| 138 | |
| 139 | for ($i = 0; $i < $length; $i++) { |
| 140 | $char = $expression[$i]; |
| 141 | |
| 142 | if ($inQuote !== null) { |
| 143 | if ($char === $inQuote) { |
| 144 | $inQuote = null; |
| 145 | } |
| 146 | continue; |
| 147 | } |
| 148 | |
| 149 | if (($char === "'") || ($char === '"') || ($char === '`')) { |
| 150 | $inQuote = $char; |
| 151 | } else if ($char === '(') { |
| 152 | $depth++; |
| 153 | } else if ($char === ')') { |
| 154 | $depth--; |
| 155 | // The opening parenthesis closes before the end, so the expression is not |
| 156 | // wrapped as a whole |
| 157 | if ($depth === 0) { |
| 158 | return ($i === ($length - 1)) ? trim(substr($expression, 1, -1)) : $expression; |
| 159 | } |
| 160 | } |
| 161 | } |
| 162 | |
| 163 | return $expression; |
| 164 | } |
| 165 | |
| 166 | /** |
| 167 | * Locate the end of the column at the start of a predicate expression |
| 168 | * |
| 169 | * The column runs up to the first whitespace that is not inside parentheses and not |
| 170 | * inside a quoted string, so that the arguments of a function call are kept with the |
| 171 | * column instead of being read as the operator - 'COUNT(DISTINCT id) > 1' has to yield |
| 172 | * the column 'COUNT(DISTINCT id)', not 'COUNT(DISTINCT' and the operator 'id)'. |
| 173 | * |
| 174 | * @param string $expression |
| 175 | * @return int|false |
| 176 | */ |
| 177 | protected static function getColumnEnd(string $expression): int|false |
| 178 | { |
| 179 | $depth = 0; |
| 180 | $inQuote = null; |
| 181 | $length = strlen($expression); |
| 182 | |
| 183 | for ($i = 0; $i < $length; $i++) { |
| 184 | $char = $expression[$i]; |
| 185 | |
| 186 | if ($inQuote !== null) { |
| 187 | if ($char === $inQuote) { |
| 188 | $inQuote = null; |
| 189 | } |
| 190 | continue; |
| 191 | } |
| 192 | |
| 193 | if (($char === "'") || ($char === '"') || ($char === '`')) { |
| 194 | $inQuote = $char; |
| 195 | } else if ($char === '(') { |
| 196 | $depth++; |
| 197 | } else if ($char === ')') { |
| 198 | if ($depth > 0) { |
| 199 | $depth--; |
| 200 | } |
| 201 | } else if (($depth === 0) && (trim($char) === '')) { |
| 202 | return $i; |
| 203 | } |
| 204 | } |
| 205 | |
| 206 | return false; |
| 207 | } |
| 208 | |
| 209 | /** |
| 210 | * Method to parse predicate string expressions into its components |
| 211 | * |
| 212 | * @param array $expressions |
| 213 | * @return array |
| 214 | */ |
| 215 | public static function parseExpressions(array $expressions): array |
| 216 | { |
| 217 | $components = []; |
| 218 | |
| 219 | foreach ($expressions as $expression) { |
| 220 | $components[] = self::parse($expression); |
| 221 | } |
| 222 | |
| 223 | return $components; |
| 224 | } |
| 225 | |
| 226 | /** |
| 227 | * Prepare a basic expression as a direct prepared predicate clause |
| 228 | * |
| 229 | * @param string $expression |
| 230 | * @param ?AbstractSql $sql |
| 231 | * @param bool $withParams |
| 232 | * @return array |
| 233 | */ |
| 234 | public static function prepareExpression( |
| 235 | string $expression, ?AbstractSql $sql = null, bool $withParams = true |
| 236 | ): array |
| 237 | { |
| 238 | ['column' => $column, 'operator' => $operator, 'value' => $value] = self::parse($expression); |
| 239 | |
| 240 | $clause = $sql->quoteId($column) . ' ' . $operator; |
| 241 | $params = []; |
| 242 | |
| 243 | if ($value !== null) { |
| 244 | if ((stripos($operator, 'BETWEEN') !== false) && str_contains($value, 'AND')) { |
| 245 | $value = array_map('trim', explode('AND', $value)); |
| 246 | if (count($value) == 2) { |
| 247 | if (str_starts_with($value[0], '(')) { |
| 248 | $value[0] = substr($value[0], 1); |
| 249 | } |
| 250 | if (str_ends_with($value[1], ')')) { |
| 251 | $value[1] = substr($value[1], 0, -1); |
| 252 | } |
| 253 | } |
| 254 | } |
| 255 | if (is_array($value)) { |
| 256 | if ((stripos($operator, 'BETWEEN') !== false) && (count($value) == 2)) { |
| 257 | if ($withParams) { |
| 258 | $clause .= ' ' . $sql->getPlaceholder() . ' AND ' . $sql->getPlaceholder(); |
| 259 | $params[$column] = $value; |
| 260 | } else { |
| 261 | $clause .= ' ' . $sql->quote($value[0]) . ' AND ' . $sql->quote($value[1]); |
| 262 | } |
| 263 | } else { |
| 264 | if ($withParams) { |
| 265 | $clause .= ' (' . implode(', ', array_fill(0, count($value), $sql->getPlaceholder())) . ')'; |
| 266 | $params[$column] = $value; |
| 267 | } else { |
| 268 | $quotedValues = []; |
| 269 | foreach ($value as $val) { |
| 270 | $quotedValues[] = $sql->quote($val); |
| 271 | } |
| 272 | $clause .= ' (' . implode(', ', $quotedValues) . ')'; |
| 273 | } |
| 274 | } |
| 275 | } else { |
| 276 | if ($withParams) { |
| 277 | $clause .= ' ' . $sql->getPlaceholder(); |
| 278 | $params[$column] = $value; |
| 279 | } else { |
| 280 | $clause .= ' ' . $sql->quote($value); |
| 281 | } |
| 282 | } |
| 283 | } |
| 284 | |
| 285 | return ['clause' => $clause, 'params' => $params]; |
| 286 | } |
| 287 | |
| 288 | /** |
| 289 | * Prepare basic expressions as direct prepared predicate clauses |
| 290 | * |
| 291 | * @param array $expressions |
| 292 | * @param ?AbstractSql $sql |
| 293 | * @param bool $withParams |
| 294 | * @param bool $flatten |
| 295 | * @return array |
| 296 | */ |
| 297 | public static function prepareExpressions( |
| 298 | array $expressions, ?AbstractSql $sql = null, bool $withParams = true, bool $flatten = true |
| 299 | ): array |
| 300 | { |
| 301 | $clauses = []; |
| 302 | |
| 303 | foreach ($expressions as $expression) { |
| 304 | $clauses[] = self::prepareExpression($expression, $sql, $withParams); |
| 305 | } |
| 306 | |
| 307 | if ($flatten) { |
| 308 | $flattenClauses = []; |
| 309 | $flattenParams = []; |
| 310 | $placeholder = $sql->getPlaceholder(); |
| 311 | |
| 312 | foreach ($clauses as $clause) { |
| 313 | $flattenClauses[] = $clause['clause']; |
| 314 | if (!empty($clause['params'])) { |
| 315 | if (is_array($clause['params'])) { |
| 316 | $i = 1; |
| 317 | foreach ($clause['params'] as $k => $v) { |
| 318 | if ($placeholder == ':') { |
| 319 | $flattenParams[$k . ($i++)] = $v; |
| 320 | } else { |
| 321 | $flattenParams[] = $v; |
| 322 | } |
| 323 | } |
| 324 | } |
| 325 | } |
| 326 | } |
| 327 | |
| 328 | return ['clauses' => $flattenClauses, 'params' => $flattenParams]; |
| 329 | } else { |
| 330 | return $clauses; |
| 331 | } |
| 332 | } |
| 333 | |
| 334 | /** |
| 335 | * Convert to expression to shorthand value |
| 336 | * |
| 337 | * @deprecated Produces the legacy shorthand format, which Condition::parseConditions() |
| 338 | * only accepts by triggering an E_USER_DEPRECATED notice. Use |
| 339 | * convertExpressionsToStructured() instead. Kept for callers still pinned to |
| 340 | * the legacy format; will be removed in pop-db v8. |
| 341 | * @param string $expression |
| 342 | * @return array |
| 343 | */ |
| 344 | public static function convertExpressionToShorthand(string $expression): array |
| 345 | { |
| 346 | ['column' => $column, 'operator' => $operator, 'value' => $value] = self::parse($expression); |
| 347 | |
| 348 | switch ($operator) { |
| 349 | case '>=': |
| 350 | case '<=': |
| 351 | case '!=': |
| 352 | case '>': |
| 353 | case '<': |
| 354 | $column .= $operator; |
| 355 | break; |
| 356 | case 'LIKE': |
| 357 | if (str_starts_with($value, '%')) { |
| 358 | $column = '%' . $column; |
| 359 | $value = substr($value, 1); |
| 360 | } |
| 361 | if (str_ends_with($value, '%')) { |
| 362 | $column .= '%'; |
| 363 | $value = substr($value, 0, -1); |
| 364 | } |
| 365 | break; |
| 366 | case 'NOT LIKE': |
| 367 | if (str_starts_with($value, '%')) { |
| 368 | $column = '-%' . $column; |
| 369 | $value = substr($value, 1); |
| 370 | } |
| 371 | if (str_ends_with($value, '%')) { |
| 372 | $column .= '%-'; |
| 373 | $value = substr($value, 0, -1); |
| 374 | } |
| 375 | break; |
| 376 | case 'NOT IN': |
| 377 | case 'NOT BETWEEN': |
| 378 | case 'IS NOT NULL': |
| 379 | $column .= '-'; |
| 380 | break; |
| 381 | } |
| 382 | |
| 383 | return [$column => $value]; |
| 384 | } |
| 385 | |
| 386 | /** |
| 387 | * Convert to expression to shorthand value |
| 388 | * |
| 389 | * @deprecated Produces the legacy shorthand format, which Condition::parseConditions() |
| 390 | * only accepts by triggering an E_USER_DEPRECATED notice. Use |
| 391 | * convertExpressionsToStructured() instead. Kept for callers still pinned to |
| 392 | * the legacy format; will be removed in pop-db v8. |
| 393 | * @param array $expressions |
| 394 | * @return array |
| 395 | */ |
| 396 | public static function convertExpressionsToShorthand(array $expressions): array |
| 397 | { |
| 398 | $conditions = []; |
| 399 | |
| 400 | foreach ($expressions as $expression) { |
| 401 | $conditions = array_merge($conditions, self::convertExpressionToShorthand($expression)); |
| 402 | } |
| 403 | |
| 404 | return $conditions; |
| 405 | } |
| 406 | |
| 407 | /** |
| 408 | * Convert expressions into the structured operator-tuple format that |
| 409 | * Sql\Parser\Condition::parseConditions() accepts natively - the modern counterpart to |
| 410 | * convertExpressionsToShorthand(), which produces the legacy format that triggers |
| 411 | * Condition::parseLegacy()'s deprecation notice. |
| 412 | * |
| 413 | * Unlike convertExpressionsToShorthand(), string keys are treated as already-prepared |
| 414 | * column => value pairs and passed through untouched, an array entry is treated as an |
| 415 | * already-structured condition and merged as-is, and a column produced by more than one |
| 416 | * expression is routed into a nested 'AND' group instead of silently overwriting the |
| 417 | * earlier condition - the collision legacy shorthand avoided only because the operator |
| 418 | * was folded into the key (e.g. 'due_date>=' vs 'due_date<='). |
| 419 | * |
| 420 | * @param array $expressions |
| 421 | * @throws Exception |
| 422 | * @return array |
| 423 | */ |
| 424 | public static function convertExpressionsToStructured(array $expressions): array |
| 425 | { |
| 426 | $conditions = []; |
| 427 | $repeated = []; |
| 428 | |
| 429 | foreach ($expressions as $key => $expression) { |
| 430 | // A string key is an already-prepared column => value pair rather than an |
| 431 | // expression string - pass it through untouched. |
| 432 | if (!is_int($key)) { |
| 433 | $conditions[$key] = $expression; |
| 434 | continue; |
| 435 | } |
| 436 | |
| 437 | // Anything already in array form is a structured condition - merge it as-is. |
| 438 | if (!is_string($expression)) { |
| 439 | if (is_array($expression)) { |
| 440 | $conditions = array_merge($conditions, $expression); |
| 441 | } |
| 442 | continue; |
| 443 | } |
| 444 | |
| 445 | ['column' => $column, 'operator' => $operator, 'value' => $value] = self::parse($expression); |
| 446 | |
| 447 | $operator = strtoupper($operator); |
| 448 | |
| 449 | $tuple = match ($operator) { |
| 450 | 'IS NULL', 'IS NOT NULL' => [$operator], |
| 451 | 'IN', 'NOT IN' => [$operator, (array)$value], |
| 452 | 'BETWEEN', 'NOT BETWEEN' => array_merge([$operator], self::splitBetweenValues($value)), |
| 453 | // A bare '=' against null keeps pop-db's documented IS NULL semantics rather |
| 454 | // than rendering the never-true 'column = NULL'. |
| 455 | '=' => ($value === null) ? ['IS NULL'] : ['=', $value], |
| 456 | default => [$operator, $value], |
| 457 | }; |
| 458 | |
| 459 | if (array_key_exists($column, $conditions)) { |
| 460 | $repeated[] = [$column => $tuple]; |
| 461 | } else { |
| 462 | $conditions[$column] = $tuple; |
| 463 | } |
| 464 | } |
| 465 | |
| 466 | if (!empty($repeated)) { |
| 467 | $conditions['AND'] = $repeated; |
| 468 | } |
| 469 | |
| 470 | return $conditions; |
| 471 | } |
| 472 | |
| 473 | /** |
| 474 | * Split a packed BETWEEN/NOT BETWEEN value - '(v1 AND v2)' - into its two operands |
| 475 | * |
| 476 | * Expression::parse() returns BETWEEN's operands packed into one string, not a pair, so |
| 477 | * a straight array cast yields a one-value tuple that Condition::parseTuple() rejects |
| 478 | * (BETWEEN has arity 2). |
| 479 | * |
| 480 | * @param mixed $value |
| 481 | * @return array |
| 482 | */ |
| 483 | private static function splitBetweenValues(mixed $value): array |
| 484 | { |
| 485 | if (is_array($value)) { |
| 486 | return array_values($value); |
| 487 | } |
| 488 | |
| 489 | $value = trim((string)$value); |
| 490 | |
| 491 | while (str_starts_with($value, '(') && str_ends_with($value, ')')) { |
| 492 | $value = trim(substr($value, 1, -1)); |
| 493 | } |
| 494 | |
| 495 | return array_map('trim', preg_split('/\s+AND\s+/i', $value, 2)); |
| 496 | } |
| 497 | |
| 498 | /** |
| 499 | * Method to check if the column is shorthand |
| 500 | * |
| 501 | * @param string $column |
| 502 | * @return bool |
| 503 | */ |
| 504 | public static function isShorthand(string $column): bool |
| 505 | { |
| 506 | return str_contains($column, '%') || str_ends_with($column, '-') || str_ends_with($column, '>=') || |
| 507 | str_ends_with($column, '<=') || str_ends_with($column, '!=') || str_ends_with($column, '>') || |
| 508 | str_ends_with($column, '<'); |
| 509 | } |
| 510 | |
| 511 | /** |
| 512 | * Method to parse the shorthand columns to create expressions and their parameters |
| 513 | * |
| 514 | * @param array $columns |
| 515 | * @param ?string $placeholder |
| 516 | * @param bool $flatten |
| 517 | * @return array |
| 518 | */ |
| 519 | public static function parseShorthand(array $columns, ?string $placeholder = null, bool $flatten = true): array |
| 520 | { |
| 521 | $expressions = []; |
| 522 | $params = []; |
| 523 | $i = 1; |
| 524 | $j = 0; |
| 525 | |
| 526 | foreach ($columns as $column => $value) { |
| 527 | ['column' => $parsedColumn, 'operator' => $operator] = Operator::parse($column); |
| 528 | |
| 529 | $pHolder = $placeholder; |
| 530 | if ($placeholder == ':') { |
| 531 | $pHolder .= $parsedColumn; |
| 532 | } else if ($placeholder == '$') { |
| 533 | $pHolder .= $i; |
| 534 | } |
| 535 | |
| 536 | // IS NULL/IS NOT NULL |
| 537 | if ($value === null) { |
| 538 | $expressions = self::applyExpression( |
| 539 | $expressions, $parsedColumn, $placeholder, self::buildNullExpression($parsedColumn, $operator) |
| 540 | ); |
| 541 | // IN/NOT IN |
| 542 | } else if (is_array($value)) { |
| 543 | [$newExpression, $paramValue] = self::buildInExpression( |
| 544 | $parsedColumn, $operator, $value, $placeholder, $pHolder, $i |
| 545 | ); |
| 546 | $expressions = self::applyExpression($expressions, $parsedColumn, $placeholder, $newExpression); |
| 547 | $params = self::applyParam($params, $parsedColumn, $placeholder, $j, $paramValue); |
| 548 | // BETWEEN/NOT BETWEEN |
| 549 | } else if (is_string($value) && str_starts_with($value, '(') && str_ends_with($value, ')')) { |
| 550 | [$newExpression, $paramValue] = self::buildBetweenExpression( |
| 551 | $parsedColumn, $operator, $value, $placeholder, $pHolder, $i |
| 552 | ); |
| 553 | $expressions = self::applyExpression($expressions, $parsedColumn, $placeholder, $newExpression); |
| 554 | $params = self::applyParam($params, $parsedColumn, $placeholder, $j, $paramValue); |
| 555 | // LIKE/NOT LIKE or Standard Operators |
| 556 | } else { |
| 557 | [$newExpression, $paramValue] = self::buildComparisonExpression( |
| 558 | $column, $parsedColumn, $operator, $value, $placeholder, $pHolder, $i |
| 559 | ); |
| 560 | $expressions = self::applyExpression($expressions, $parsedColumn, $placeholder, $newExpression); |
| 561 | $params = self::applyParam($params, $parsedColumn, $placeholder, $j, $paramValue); |
| 562 | } |
| 563 | |
| 564 | $j++; |
| 565 | } |
| 566 | |
| 567 | if ($flatten) { |
| 568 | $flattenParams = []; |
| 569 | |
| 570 | foreach ($params as $key => $value) { |
| 571 | if (is_array($value)) { |
| 572 | foreach ($value as $k => $v) { |
| 573 | if ($placeholder == ':') { |
| 574 | $flattenParams[$key . ($k + 1)] = $v; |
| 575 | } else { |
| 576 | $flattenParams[] = $v; |
| 577 | } |
| 578 | } |
| 579 | } else { |
| 580 | if ($placeholder == ':') { |
| 581 | $flattenParams[$key] = $value; |
| 582 | } else { |
| 583 | $flattenParams[] = $value; |
| 584 | } |
| 585 | } |
| 586 | } |
| 587 | |
| 588 | return ['expressions' => $expressions, 'params' => $flattenParams]; |
| 589 | } else { |
| 590 | return ['expressions' => $expressions, 'params' => $params]; |
| 591 | } |
| 592 | } |
| 593 | |
| 594 | /** |
| 595 | * Add a rendered expression fragment to the expressions array, keyed by column name when |
| 596 | * using named (':') placeholders, or appended positionally otherwise |
| 597 | * |
| 598 | * @param array $expressions |
| 599 | * @param string $parsedColumn |
| 600 | * @param ?string $placeholder |
| 601 | * @param string $newExpression |
| 602 | * @return array |
| 603 | */ |
| 604 | private static function applyExpression(array $expressions, string $parsedColumn, ?string $placeholder, string $newExpression): array |
| 605 | { |
| 606 | if ($placeholder == ':') { |
| 607 | $expressions[$parsedColumn] = $newExpression; |
| 608 | } else { |
| 609 | $expressions[] = $newExpression; |
| 610 | } |
| 611 | |
| 612 | return $expressions; |
| 613 | } |
| 614 | |
| 615 | /** |
| 616 | * Add a bound parameter value to the params array, keyed by column name when using named |
| 617 | * (':') placeholders, or by the running column index otherwise |
| 618 | * |
| 619 | * @param array $params |
| 620 | * @param string $parsedColumn |
| 621 | * @param ?string $placeholder |
| 622 | * @param int $j |
| 623 | * @param mixed $paramValue |
| 624 | * @return array |
| 625 | */ |
| 626 | private static function applyParam(array $params, string $parsedColumn, ?string $placeholder, int $j, mixed $paramValue): array |
| 627 | { |
| 628 | if ($placeholder == ':') { |
| 629 | $params[$parsedColumn] = $paramValue; |
| 630 | } else { |
| 631 | $params[$j] = $paramValue; |
| 632 | } |
| 633 | |
| 634 | return $params; |
| 635 | } |
| 636 | |
| 637 | /** |
| 638 | * Build an IS NULL/IS NOT NULL expression fragment |
| 639 | * |
| 640 | * @param string $parsedColumn |
| 641 | * @param string $operator |
| 642 | * @return string |
| 643 | */ |
| 644 | private static function buildNullExpression(string $parsedColumn, string $operator): string |
| 645 | { |
| 646 | return $parsedColumn . ' IS ' . (($operator == 'NOT') ? 'NOT ' : '') . 'NULL'; |
| 647 | } |
| 648 | |
| 649 | /** |
| 650 | * Build an IN/NOT IN expression fragment and its bound parameter value(s) |
| 651 | * |
| 652 | * @param string $parsedColumn |
| 653 | * @param string $operator |
| 654 | * @param array $value |
| 655 | * @param ?string $placeholder |
| 656 | * @param ?string $pHolder |
| 657 | * @param int $i |
| 658 | * @return array [string $newExpression, array $paramValue] |
| 659 | */ |
| 660 | private static function buildInExpression( |
| 661 | string $parsedColumn, string $operator, array $value, ?string $placeholder, ?string $pHolder, int &$i |
| 662 | ): array |
| 663 | { |
| 664 | $p = []; |
| 665 | |
| 666 | if ($placeholder == ':') { |
| 667 | $pHolders = []; |
| 668 | foreach ($value as $k => $val) { |
| 669 | $pHolders[] = $pHolder . ($k + 1); |
| 670 | $p[] = $val; |
| 671 | } |
| 672 | } else if ($placeholder == '$') { |
| 673 | $pHolders = []; |
| 674 | foreach ($value as $val) { |
| 675 | $pHolders[] = $placeholder . $i++; |
| 676 | $p[] = $val; |
| 677 | } |
| 678 | } else { |
| 679 | $pHolders = array_fill(0, count($value), $pHolder); |
| 680 | $p = $value; |
| 681 | $i++; |
| 682 | } |
| 683 | |
| 684 | if ($placeholder !== null) { |
| 685 | $newExpression = $parsedColumn . (($operator == 'NOT') ? ' NOT ' : ' ') . 'IN (' . |
| 686 | implode(', ', $pHolders) . ')'; |
| 687 | } else { |
| 688 | $newExpression = $parsedColumn . (($operator == 'NOT') ? ' NOT ' : ' ') . 'IN (' . |
| 689 | implode(', ', array_map('Pop\Db\Sql\Parser\Expression::quote', $value)) . ')'; |
| 690 | } |
| 691 | |
| 692 | return [$newExpression, $p]; |
| 693 | } |
| 694 | |
| 695 | /** |
| 696 | * Build a BETWEEN/NOT BETWEEN expression fragment and its two bound boundary values |
| 697 | * |
| 698 | * @param string $parsedColumn |
| 699 | * @param string $operator |
| 700 | * @param string $value |
| 701 | * @param ?string $placeholder |
| 702 | * @param ?string $pHolder |
| 703 | * @param int $i |
| 704 | * @return array [string $newExpression, array $paramValue] |
| 705 | */ |
| 706 | private static function buildBetweenExpression( |
| 707 | string $parsedColumn, string $operator, string $value, ?string $placeholder, ?string $pHolder, int &$i |
| 708 | ): array |
| 709 | { |
| 710 | $values = substr($value, (strpos($value, '(') + 1)); |
| 711 | $values = substr($values, 0, strpos($values, ')')); |
| 712 | $delimiter = (str_contains($values, ',')) ? ',' : 'AND'; |
| 713 | [$value1, $value2] = array_map('trim', explode($delimiter, $values)); |
| 714 | |
| 715 | $p = [$value1, $value2]; |
| 716 | |
| 717 | if ($placeholder == ':') { |
| 718 | $pHolder2 = $pHolder . 2; |
| 719 | $pHolder .= 1; |
| 720 | } else if ($placeholder == '$') { |
| 721 | $pHolder2 = $placeholder . ++$i; |
| 722 | } else { |
| 723 | $pHolder2 = $pHolder; |
| 724 | } |
| 725 | |
| 726 | if ($placeholder !== null) { |
| 727 | $newExpression = $parsedColumn . (($operator == 'NOT') ? ' NOT ' : ' ') . |
| 728 | 'BETWEEN ' . $pHolder . ' AND ' . $pHolder2; |
| 729 | } else { |
| 730 | $newExpression = $parsedColumn . (($operator == 'NOT') ? ' NOT ' : ' ') . |
| 731 | 'BETWEEN ' . self::quote($value1) . ' AND ' . self::quote($value2); |
| 732 | } |
| 733 | |
| 734 | $i++; |
| 735 | |
| 736 | return [$newExpression, $p]; |
| 737 | } |
| 738 | |
| 739 | /** |
| 740 | * Build a LIKE/NOT LIKE or standard comparison-operator expression fragment and its bound value |
| 741 | * |
| 742 | * @param string $column |
| 743 | * @param string $parsedColumn |
| 744 | * @param string $operator |
| 745 | * @param mixed $value |
| 746 | * @param ?string $placeholder |
| 747 | * @param ?string $pHolder |
| 748 | * @param int $i |
| 749 | * @return array [string $newExpression, mixed $paramValue] |
| 750 | */ |
| 751 | private static function buildComparisonExpression( |
| 752 | string $column, string $parsedColumn, string $operator, mixed $value, ?string $placeholder, ?string $pHolder, int &$i |
| 753 | ): array |
| 754 | { |
| 755 | if ((str_starts_with($column, '%')) || (str_starts_with($column, '-%'))) { |
| 756 | $value = '%' . $value; |
| 757 | } |
| 758 | if ((str_ends_with($column, '%')) || (str_ends_with($column, '%-'))) { |
| 759 | $value .= '%'; |
| 760 | } |
| 761 | |
| 762 | if ($placeholder !== null) { |
| 763 | $newExpression = $parsedColumn . ' ' . $operator . ' ' . $pHolder; |
| 764 | } else { |
| 765 | $newExpression = $parsedColumn . ' ' . $operator . ' ' . self::quote($value); |
| 766 | } |
| 767 | |
| 768 | $i++; |
| 769 | |
| 770 | return [$newExpression, $value]; |
| 771 | } |
| 772 | |
| 773 | /** |
| 774 | * Strip ID quotes |
| 775 | * |
| 776 | * @param string $identifier |
| 777 | * @return string |
| 778 | */ |
| 779 | public static function stripIdQuotes(string $identifier): string |
| 780 | { |
| 781 | if (((str_starts_with($identifier, '"')) && (str_ends_with($identifier, '"'))) || |
| 782 | ((str_starts_with($identifier, '`')) && (str_ends_with($identifier, '`'))) || |
| 783 | ((str_starts_with($identifier, '[')) && (str_ends_with($identifier, ']')))) { |
| 784 | $identifier = substr($identifier, 1); |
| 785 | $identifier = substr($identifier, 0, -1); |
| 786 | } |
| 787 | |
| 788 | return $identifier; |
| 789 | } |
| 790 | |
| 791 | /** |
| 792 | * Strip quotes |
| 793 | * |
| 794 | * @param string $value |
| 795 | * @return string |
| 796 | */ |
| 797 | public static function stripQuotes(string $value): string |
| 798 | { |
| 799 | if (((str_starts_with($value, '"')) && (str_ends_with($value, '"'))) || |
| 800 | ((str_starts_with($value, "'")) && (str_ends_with($value, "'")))) { |
| 801 | $value = substr($value, 1); |
| 802 | $value = substr($value, 0, -1); |
| 803 | } |
| 804 | |
| 805 | return $value; |
| 806 | } |
| 807 | |
| 808 | /** |
| 809 | * Quote the value (if it is not a numeric value) |
| 810 | * |
| 811 | * @param string $value |
| 812 | * @return string |
| 813 | */ |
| 814 | public static function quote(string $value): string |
| 815 | { |
| 816 | if (($value == '') || |
| 817 | (($value != '?') && (!str_starts_with($value, ':')) && (preg_match('/^\$\d*\d$/', $value) == 0) && |
| 818 | (preg_match('/^\d*$/', $value) == 0))) { |
| 819 | $value = "'" . $value . "'"; |
| 820 | } |
| 821 | return $value; |
| 822 | } |
| 823 | |
| 824 | } |