darya /
framework
| Total Complexity | 108 |
| Total Lines | 933 |
| Duplicated Lines | 0 % |
| Changes | 11 | ||
| Bugs | 0 | Features | 0 |
Complex classes like AbstractSqlTranslator 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 AbstractSqlTranslator, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 15 | abstract class AbstractSqlTranslator implements Translator |
||
| 16 | { |
||
| 17 | /** |
||
| 18 | * Filter comparison operators. |
||
| 19 | * |
||
| 20 | * @var array |
||
| 21 | */ |
||
| 22 | protected $operators = array( |
||
| 23 | '>=', '<=', '>', '<', '=', '!=', '<>', 'in', 'not in', 'is', 'is not', |
||
| 24 | 'like', 'not like' |
||
| 25 | ); |
||
| 26 | |||
| 27 | /** |
||
| 28 | * Placeholder for values in prepared queries. |
||
| 29 | * |
||
| 30 | * @var string |
||
| 31 | */ |
||
| 32 | protected $placeholder = '?'; |
||
| 33 | |||
| 34 | /** |
||
| 35 | * Concatenates the given set of strings that aren't empty. |
||
| 36 | * |
||
| 37 | * Runs implode() after filtering out empty elements. |
||
| 38 | * |
||
| 39 | * Delimiter defaults to a single whitespace character. |
||
| 40 | * |
||
| 41 | * @param array $strings |
||
| 42 | * @param string $delimiter [optional] |
||
| 43 | * @return string |
||
| 44 | */ |
||
| 45 | protected static function concatenate($strings, $delimiter = ' ') |
||
| 46 | { |
||
| 47 | $strings = array_filter($strings, function ($value) { |
||
| 48 | return !empty($value); |
||
| 49 | }); |
||
| 50 | |||
| 51 | return implode($delimiter, $strings); |
||
| 52 | } |
||
| 53 | |||
| 54 | /** |
||
| 55 | * Determine whether the given limit and offset will make a difference to |
||
| 56 | * a statement. |
||
| 57 | * |
||
| 58 | * Simply determines whether either is a non-zero integers. |
||
| 59 | * |
||
| 60 | * @param int $limit |
||
| 61 | * @param int $offset |
||
| 62 | * @return bool |
||
| 63 | */ |
||
| 64 | protected static function limitIsUseful($limit, $offset) |
||
| 65 | { |
||
| 66 | return (int) $limit !== 0 || (int) $offset !== 0; |
||
| 67 | } |
||
| 68 | |||
| 69 | /** |
||
| 70 | * Translate the given storage query into an SQL query. |
||
| 71 | * |
||
| 72 | * @param Storage\Query $storageQuery |
||
| 73 | * @return Database\Query |
||
| 74 | * @throws InvalidArgumentException |
||
| 75 | */ |
||
| 76 | public function translate(Storage\Query $storageQuery) |
||
| 77 | { |
||
| 78 | $type = $storageQuery->type; |
||
| 79 | |||
| 80 | $method = 'translate' . ucfirst($type); |
||
| 81 | |||
| 82 | if (!method_exists($this, $method)) { |
||
| 83 | throw new InvalidArgumentException("Could not translate query of unknown type '$type'"); |
||
| 84 | } |
||
| 85 | |||
| 86 | $query = call_user_func_array(array($this, $method), array($storageQuery)); |
||
| 87 | |||
| 88 | return $query; |
||
| 89 | } |
||
| 90 | |||
| 91 | /** |
||
| 92 | * Translate a query that creates a record. |
||
| 93 | * |
||
| 94 | * @param Storage\Query $storageQuery |
||
| 95 | * @return Database\Query |
||
| 96 | */ |
||
| 97 | protected function translateCreate(Storage\Query $storageQuery) |
||
| 98 | { |
||
| 99 | if ($storageQuery instanceof Database\Storage\Query && $storageQuery->insertSubquery) { |
||
| 100 | return new Database\Query( |
||
| 101 | $this->prepareInsertSelect($storageQuery->resource, $storageQuery->fields, $storageQuery->insertSubquery), |
||
| 102 | $this->parameters($storageQuery->insertSubquery) |
||
| 103 | ); |
||
| 104 | } |
||
| 105 | |||
| 106 | return new Database\Query( |
||
| 107 | $this->prepareInsert($storageQuery->resource, $storageQuery->data), |
||
| 108 | $this->parameters($storageQuery) |
||
| 109 | ); |
||
| 110 | } |
||
| 111 | |||
| 112 | /** |
||
| 113 | * Translate a query that reads records. |
||
| 114 | * |
||
| 115 | * @param Storage\Query $storageQuery |
||
| 116 | * @return Database\Query |
||
| 117 | */ |
||
| 118 | protected function translateRead(Storage\Query $storageQuery) |
||
| 119 | { |
||
| 120 | if ($storageQuery instanceof Database\Storage\Query) { |
||
| 121 | return $this->translateDatabaseRead($storageQuery); |
||
| 122 | } |
||
| 123 | |||
| 124 | return new Database\Query( |
||
| 125 | $this->prepareSelect( |
||
| 126 | $storageQuery->resource, |
||
| 127 | $this->prepareColumns($storageQuery->fields), |
||
| 128 | null, |
||
| 129 | $this->prepareWhere($storageQuery->filter), |
||
| 130 | $this->prepareOrderBy($storageQuery->order), |
||
| 131 | $this->prepareLimit($storageQuery->limit, $storageQuery->offset), |
||
| 132 | null, |
||
| 133 | null, |
||
| 134 | $storageQuery->distinct |
||
| 135 | ), |
||
| 136 | $this->parameters($storageQuery) |
||
| 137 | ); |
||
| 138 | } |
||
| 139 | |||
| 140 | /** |
||
| 141 | * Translate a database storage query that reads records. |
||
| 142 | * |
||
| 143 | * @param Database\Storage\Query $storageQuery |
||
| 144 | * @return Database\Query |
||
| 145 | */ |
||
| 146 | protected function translateDatabaseRead(Database\Storage\Query $storageQuery) |
||
| 147 | { |
||
| 148 | return new Database\Query( |
||
| 149 | $this->prepareSelect( |
||
| 150 | $storageQuery->resource, |
||
| 151 | $this->prepareColumns($storageQuery->fields), |
||
| 152 | $this->prepareJoins($storageQuery->joins), |
||
| 153 | $this->prepareWhere($storageQuery->filter), |
||
| 154 | $this->prepareOrderBy($storageQuery->order), |
||
| 155 | $this->prepareLimit($storageQuery->limit, $storageQuery->offset), |
||
| 156 | $this->prepareGroupBy($storageQuery->groupings), |
||
| 157 | $this->prepareHaving($storageQuery->having), |
||
| 158 | $storageQuery->distinct |
||
| 159 | ), |
||
| 160 | $this->parameters($storageQuery) |
||
| 161 | ); |
||
| 162 | } |
||
| 163 | |||
| 164 | /** |
||
| 165 | * Translate a query that updates records. |
||
| 166 | * |
||
| 167 | * @param Storage\Query $storageQuery |
||
| 168 | * @return Database\Query |
||
| 169 | */ |
||
| 170 | protected function translateUpdate(Storage\Query $storageQuery) |
||
| 171 | { |
||
| 172 | return new Database\Query( |
||
| 173 | $this->prepareUpdate( |
||
| 174 | $storageQuery->resource, |
||
| 175 | $storageQuery->data, |
||
| 176 | $this->prepareWhere($storageQuery->filter), |
||
| 177 | $this->prepareLimit($storageQuery->limit, $storageQuery->offset) |
||
| 178 | ), |
||
| 179 | $this->parameters($storageQuery) |
||
| 180 | ); |
||
| 181 | } |
||
| 182 | |||
| 183 | /** |
||
| 184 | * Translate a query that deletes records. |
||
| 185 | * |
||
| 186 | * @param Storage\Query $storageQuery |
||
| 187 | * @return Database\Query |
||
| 188 | */ |
||
| 189 | protected function translateDelete(Storage\Query $storageQuery) |
||
| 190 | { |
||
| 191 | return new Database\Query( |
||
| 192 | $this->prepareDelete( |
||
| 193 | $storageQuery->resource, |
||
| 194 | $this->prepareWhere($storageQuery->filter), |
||
| 195 | $this->prepareLimit($storageQuery->limit, $storageQuery->offset) |
||
| 196 | ), |
||
| 197 | $this->parameters($storageQuery) |
||
| 198 | ); |
||
| 199 | } |
||
| 200 | |||
| 201 | /** |
||
| 202 | * Resolve the given value as an identifier. |
||
| 203 | * |
||
| 204 | * @param mixed $identifier |
||
| 205 | * @return string |
||
| 206 | */ |
||
| 207 | abstract protected function resolveIdentifier($identifier); |
||
| 208 | |||
| 209 | /** |
||
| 210 | * Prepare the given identifier. |
||
| 211 | * |
||
| 212 | * If the value is translatable, it is translated. |
||
| 213 | * |
||
| 214 | * If the value is an array, it is recursively prepared. |
||
| 215 | * |
||
| 216 | * @param mixed $identifier |
||
| 217 | * @return mixed |
||
| 218 | */ |
||
| 219 | protected function identifier($identifier) |
||
| 220 | { |
||
| 221 | if (is_array($identifier)) { |
||
| 222 | return array_map(array($this, 'identifier'), $identifier); |
||
| 223 | } |
||
| 224 | |||
| 225 | if ($this->translatable($identifier)) { |
||
| 226 | return $this->translateValue($identifier); |
||
| 227 | } |
||
| 228 | |||
| 229 | return $this->resolveIdentifier($identifier); |
||
| 230 | } |
||
| 231 | |||
| 232 | /** |
||
| 233 | * Determine whether the given value is translatable. |
||
| 234 | * |
||
| 235 | * @param mixed $value |
||
| 236 | * @return bool |
||
| 237 | */ |
||
| 238 | protected function translatable($value) |
||
| 239 | { |
||
| 240 | return $value instanceof Storage\Query\Builder || $value instanceof Storage\Query; |
||
| 241 | } |
||
| 242 | |||
| 243 | /** |
||
| 244 | * Translate the given translatable query. |
||
| 245 | * |
||
| 246 | * Helper for handling the translation of query objects from query builders. |
||
| 247 | * |
||
| 248 | * @param mixed $query |
||
| 249 | * @return Database\Query |
||
| 250 | * @throws InvalidArgumentException |
||
| 251 | */ |
||
| 252 | protected function translateTranslatable($query) |
||
| 253 | { |
||
| 254 | if (!$this->translatable($query)) { |
||
| 255 | throw new InvalidArgumentException("Cannot translate query of type '" . get_class($query) . "'"); |
||
| 256 | } |
||
| 257 | |||
| 258 | if ($query instanceof Storage\Query\Builder) { |
||
| 259 | $query = $query->query; |
||
| 260 | } |
||
| 261 | |||
| 262 | if ($query instanceof Storage\Query) { |
||
| 263 | return $this->translate($query); |
||
| 264 | } |
||
| 265 | |||
| 266 | throw new InvalidArgumentException("Cannot translate query of type '" . get_class($query) . "'"); |
||
| 267 | } |
||
| 268 | |||
| 269 | /** |
||
| 270 | * Translate the given value if it is a query or query builder. |
||
| 271 | * |
||
| 272 | * Returns the argument as is otherwise. |
||
| 273 | * |
||
| 274 | * @param mixed $value |
||
| 275 | * @return string |
||
| 276 | */ |
||
| 277 | protected function translateValue($value) |
||
| 278 | { |
||
| 279 | $query = $this->translateTranslatable($value); |
||
| 280 | |||
| 281 | return "($query)"; |
||
| 282 | } |
||
| 283 | |||
| 284 | /** |
||
| 285 | * Prepare the given value for a prepared query. |
||
| 286 | * |
||
| 287 | * If the value translatable, it is translated. |
||
| 288 | * |
||
| 289 | * If the value is an array, it is recursively prepared. |
||
| 290 | * |
||
| 291 | * @param mixed $value |
||
| 292 | * @return array|string |
||
| 293 | */ |
||
| 294 | protected function value($value) |
||
| 295 | { |
||
| 296 | if (is_array($value)) { |
||
| 297 | return array_map(array($this, 'value'), $value); |
||
| 298 | } |
||
| 299 | |||
| 300 | if ($this->translatable($value)) { |
||
| 301 | return $this->translateValue($value); |
||
| 302 | } |
||
| 303 | |||
| 304 | return $this->resolveValue($value); |
||
| 305 | } |
||
| 306 | |||
| 307 | /** |
||
| 308 | * Resolve a placeholder or constant for the given parameter value. |
||
| 309 | * |
||
| 310 | * @param mixed $value |
||
| 311 | * @return string |
||
| 312 | */ |
||
| 313 | protected function resolveValue($value) |
||
| 314 | { |
||
| 315 | if ($value === null) { |
||
| 316 | return 'NULL'; |
||
| 317 | } |
||
| 318 | |||
| 319 | if (is_bool($value)) { |
||
| 320 | return $value ? 'TRUE' : 'FALSE'; |
||
| 321 | } |
||
| 322 | |||
| 323 | return $this->placeholder; |
||
| 324 | } |
||
| 325 | |||
| 326 | /** |
||
| 327 | * Determine whether the given value resolves a placeholder. |
||
| 328 | * |
||
| 329 | * @param mixed $value |
||
| 330 | * @return bool |
||
| 331 | */ |
||
| 332 | protected function resolvesPlaceholder($value) |
||
| 333 | { |
||
| 334 | return $this->resolveValue($value) === $this->placeholder; |
||
| 335 | } |
||
| 336 | |||
| 337 | /** |
||
| 338 | * Prepare a set of column aliases. |
||
| 339 | * |
||
| 340 | * Uses the keys of the given array as identifiers and appends them to their |
||
| 341 | * values. |
||
| 342 | * |
||
| 343 | * @param array $columns |
||
| 344 | * @return array |
||
| 345 | */ |
||
| 346 | protected function prepareColumnAliases(array $columns) |
||
| 347 | { |
||
| 348 | foreach ($columns as $alias => &$column) { |
||
| 349 | if (is_string($alias) && preg_match('/^[\w]/', $alias)) { |
||
| 350 | $aliasIdentifier = $this->identifier($alias); |
||
| 351 | $column = "$column $aliasIdentifier"; |
||
| 352 | } |
||
| 353 | } |
||
| 354 | |||
| 355 | return $columns; |
||
| 356 | } |
||
| 357 | |||
| 358 | /** |
||
| 359 | * Prepare the given columns as a string. |
||
| 360 | * |
||
| 361 | * @param array|string $columns |
||
| 362 | * @return string |
||
| 363 | */ |
||
| 364 | protected function prepareColumns($columns) |
||
| 365 | { |
||
| 366 | if (empty($columns)) { |
||
| 367 | return '*'; |
||
| 368 | } |
||
| 369 | |||
| 370 | $columns = (array) $this->identifier($columns); |
||
| 371 | |||
| 372 | $columns = $this->prepareColumnAliases($columns); |
||
| 373 | |||
| 374 | return implode(', ', $columns); |
||
| 375 | } |
||
| 376 | |||
| 377 | /** |
||
| 378 | * Determine whether the given operator is valid. |
||
| 379 | * |
||
| 380 | * @param string $operator |
||
| 381 | * @return bool |
||
| 382 | */ |
||
| 383 | protected function validOperator($operator) |
||
| 384 | { |
||
| 385 | $operator = trim($operator); |
||
| 386 | |||
| 387 | return in_array(strtolower($operator), $this->operators); |
||
| 388 | } |
||
| 389 | |||
| 390 | /** |
||
| 391 | * Prepare the given conditional operator. |
||
| 392 | * |
||
| 393 | * Returns the equals operator if given value is not in the set of valid |
||
| 394 | * operators. |
||
| 395 | * |
||
| 396 | * @param string $operator |
||
| 397 | * @return string |
||
| 398 | */ |
||
| 399 | protected function prepareRawOperator($operator) |
||
| 404 | } |
||
| 405 | |||
| 406 | /** |
||
| 407 | * Prepare an appropriate conditional operator for the given value. |
||
| 408 | * |
||
| 409 | * @param string $operator |
||
| 410 | * @param mixed $value [optional] |
||
| 411 | * @return string |
||
| 412 | */ |
||
| 413 | protected function prepareOperator($operator, $value = null) |
||
| 414 | { |
||
| 415 | $operator = $this->prepareRawOperator($operator); |
||
| 416 | |||
| 417 | if (!$this->resolvesPlaceholder($value)) { |
||
| 418 | if ($operator === '=') { |
||
| 419 | $operator = 'IS'; |
||
| 420 | } |
||
| 421 | |||
| 422 | if ($operator === '!=') { |
||
| 423 | $operator = 'IS NOT'; |
||
| 424 | } |
||
| 425 | } |
||
| 426 | |||
| 427 | if (is_array($value)) { |
||
| 428 | if ($operator === '=') { |
||
| 429 | $operator = 'IN'; |
||
| 430 | } |
||
| 431 | |||
| 432 | if ($operator === '!=') { |
||
| 433 | $operator = 'NOT IN'; |
||
| 434 | } |
||
| 435 | } |
||
| 436 | |||
| 437 | return $operator; |
||
| 438 | } |
||
| 439 | |||
| 440 | /** |
||
| 441 | * Prepare a join type. |
||
| 442 | * |
||
| 443 | * @param string $type |
||
| 444 | * @return string |
||
| 445 | */ |
||
| 446 | protected function prepareJoinType($type) |
||
| 447 | { |
||
| 448 | if (in_array($type, array('left', 'right'))) { |
||
| 449 | return strtoupper($type) . ' JOIN'; |
||
| 450 | } |
||
| 451 | |||
| 452 | return 'JOIN'; |
||
| 453 | } |
||
| 454 | |||
| 455 | /** |
||
| 456 | * Prepare a join table. |
||
| 457 | * |
||
| 458 | * @param Join $join |
||
| 459 | * @return string |
||
| 460 | */ |
||
| 461 | protected function prepareJoinTable(Join $join) |
||
| 462 | { |
||
| 463 | $table = $this->identifier($join->resource); |
||
| 464 | $alias = $this->identifier($join->alias); |
||
| 465 | |||
| 466 | return $alias ? "$table $alias" : $table; |
||
| 467 | } |
||
| 468 | |||
| 469 | /** |
||
| 470 | * Prepare a single join condition. |
||
| 471 | * |
||
| 472 | * TODO: Make this generic for WHERE or JOIN clauses. prepareCondition()? |
||
| 473 | * |
||
| 474 | * @param string $condition |
||
| 475 | * @return string |
||
| 476 | */ |
||
| 477 | protected function prepareJoinCondition($condition) |
||
| 478 | { |
||
| 479 | $parts = preg_split('/\s+/', $condition, 3); |
||
| 480 | |||
| 481 | if (count($parts) < 3) { |
||
| 482 | // TODO: Return $this->prepareFilterCondition([0], [1])? |
||
|
|
|||
| 483 | return null; |
||
| 484 | } |
||
| 485 | |||
| 486 | list($first, $operator, $second) = $parts; |
||
| 487 | |||
| 488 | return static::concatenate(array( |
||
| 489 | $this->identifier($first), |
||
| 490 | $this->prepareRawOperator($operator), |
||
| 491 | $this->identifier($second) |
||
| 492 | )); |
||
| 493 | } |
||
| 494 | |||
| 495 | /** |
||
| 496 | * Prepare a join's conditions. |
||
| 497 | * |
||
| 498 | * @param Join $join |
||
| 499 | * @return string |
||
| 500 | */ |
||
| 501 | protected function prepareJoinConditions(Join $join) |
||
| 502 | { |
||
| 503 | $conditions = array(); |
||
| 504 | |||
| 505 | foreach ($join->conditions as $condition) { |
||
| 506 | $conditions[] = $this->prepareJoinCondition($condition); |
||
| 507 | } |
||
| 508 | |||
| 509 | $conditions = array_merge($conditions, $this->prepareFilter($join->filter)); |
||
| 510 | |||
| 511 | return static::concatenate($conditions, ' AND '); |
||
| 512 | } |
||
| 513 | |||
| 514 | /** |
||
| 515 | * Prepare an individual table join. |
||
| 516 | * |
||
| 517 | * @param Join $join |
||
| 518 | * @return string |
||
| 519 | */ |
||
| 520 | protected function prepareJoin(Join $join) |
||
| 521 | { |
||
| 522 | $table = $this->prepareJoinTable($join); |
||
| 523 | $conditions = $this->prepareJoinConditions($join); |
||
| 524 | |||
| 525 | $clause = $table && $conditions ? "$table ON $conditions" : $table; |
||
| 526 | |||
| 527 | if (empty($clause)) { |
||
| 528 | return null; |
||
| 529 | } |
||
| 530 | |||
| 531 | $type = $this->prepareJoinType($join->type); |
||
| 532 | |||
| 533 | return "$type $clause"; |
||
| 534 | } |
||
| 535 | |||
| 536 | /** |
||
| 537 | * Prepare table joins. |
||
| 538 | * |
||
| 539 | * @param array $joins |
||
| 540 | * @return string |
||
| 541 | */ |
||
| 542 | protected function prepareJoins(array $joins) |
||
| 543 | { |
||
| 544 | $clauses = array(); |
||
| 545 | |||
| 546 | foreach ($joins as $join) { |
||
| 547 | $clauses[] = $this->prepareJoin($join); |
||
| 548 | } |
||
| 549 | |||
| 550 | return static::concatenate($clauses); |
||
| 551 | } |
||
| 552 | |||
| 553 | /** |
||
| 554 | * Prepare an individual filter condition. |
||
| 555 | * |
||
| 556 | * @param string $column |
||
| 557 | * @param mixed $given |
||
| 558 | * @return string |
||
| 559 | */ |
||
| 560 | protected function prepareFilterCondition($column, $given) |
||
| 561 | { |
||
| 562 | list($left, $right) = array_pad(preg_split('/\s+/', $column, 2), 2, null); |
||
| 563 | |||
| 564 | $column = $this->prepareColumns($left); |
||
| 565 | |||
| 566 | $operator = $this->prepareOperator($right, $given); |
||
| 567 | $value = $this->value($given); |
||
| 568 | |||
| 569 | // If the given value is null and whatever's on the right isn't a valid |
||
| 570 | // operator we can attempt to split again and find a second identifier |
||
| 571 | if ($given === null && !empty($right) && !$this->validOperator($right)) { |
||
| 572 | list($operator, $identifier) = array_pad(preg_split('/\s+([\w\.]+)$/', $right, 2, PREG_SPLIT_DELIM_CAPTURE), 2, null); |
||
| 573 | |||
| 574 | if (!empty($identifier)) { |
||
| 575 | $operator = $this->prepareRawOperator($operator); |
||
| 576 | $value = $this->identifier($identifier); |
||
| 577 | } |
||
| 578 | } |
||
| 579 | |||
| 580 | if (is_array($value)) { |
||
| 581 | $value = "(" . implode(", ", $value) . ")"; |
||
| 582 | } |
||
| 583 | |||
| 584 | return "$column $operator $value"; |
||
| 585 | } |
||
| 586 | |||
| 587 | /** |
||
| 588 | * Prepare a filter as a set of query conditions. |
||
| 589 | * |
||
| 590 | * TODO: Could numeric keys be dealt with by prepareJoinCondition()? |
||
| 591 | * |
||
| 592 | * @param array $filter |
||
| 593 | * @return array |
||
| 594 | */ |
||
| 595 | protected function prepareFilter(array $filter) |
||
| 596 | { |
||
| 597 | $conditions = array(); |
||
| 598 | |||
| 599 | foreach ($filter as $column => $value) { |
||
| 600 | if (strtolower($column) == 'or') { |
||
| 601 | $conditions[] = '(' . $this->prepareWhere($value, 'OR', true) . ')'; |
||
| 602 | } else { |
||
| 603 | $conditions[] = $this->prepareFilterCondition($column, $value); |
||
| 604 | } |
||
| 605 | } |
||
| 606 | |||
| 607 | return $conditions; |
||
| 608 | } |
||
| 609 | |||
| 610 | /** |
||
| 611 | * Prepare a WHERE clause using the given filter and comparison operator. |
||
| 612 | * |
||
| 613 | * Example filter key-values and their SQL equivalents: |
||
| 614 | * 'id' => 1, // id = '1' |
||
| 615 | * 'name like' => 'Chris', // name LIKE 'Chris' |
||
| 616 | * 'count >' => 10, // count > '10' |
||
| 617 | * 'type in' => [1, 2], // type IN (1, 2) |
||
| 618 | * 'type' => [3, 4] // type IN (3, 4) |
||
| 619 | * |
||
| 620 | * Comparison operator between conditions defaults to 'AND'. |
||
| 621 | * |
||
| 622 | * @param array $filter |
||
| 623 | * @param string $comparison [optional] |
||
| 624 | * @param bool $excludeWhere [optional] |
||
| 625 | * @return string |
||
| 626 | */ |
||
| 627 | protected function prepareWhere(array $filter, $comparison = 'AND', $excludeWhere = false) |
||
| 628 | { |
||
| 629 | $conditions = $this->prepareFilter($filter); |
||
| 630 | |||
| 631 | if (empty($conditions)) { |
||
| 632 | return null; |
||
| 633 | } |
||
| 634 | |||
| 635 | $clause = implode(" $comparison ", $conditions); |
||
| 636 | |||
| 637 | return !$excludeWhere ? "WHERE $clause" : $clause; |
||
| 638 | } |
||
| 639 | |||
| 640 | /** |
||
| 641 | * Prepare an individual order condition. |
||
| 642 | * |
||
| 643 | * @param string $column |
||
| 644 | * @param string $direction [optional] |
||
| 645 | * @return string |
||
| 646 | */ |
||
| 647 | protected function prepareOrder($column, $direction = null) |
||
| 648 | { |
||
| 649 | $column = $this->identifier($column); |
||
| 650 | $direction = $direction !== null ? strtoupper($direction) : 'ASC'; |
||
| 651 | |||
| 652 | return !empty($column) ? "$column $direction" : null; |
||
| 653 | } |
||
| 654 | |||
| 655 | /** |
||
| 656 | * Prepare an ORDER BY clause using the given order. |
||
| 657 | * |
||
| 658 | * Example order key-values: |
||
| 659 | * 'column', |
||
| 660 | * 'other_column' => 'ASC', |
||
| 661 | * 'another_column' => 'DESC |
||
| 662 | * |
||
| 663 | * Ordered ascending by default. |
||
| 664 | * |
||
| 665 | * @param array|string $order |
||
| 666 | * @return string |
||
| 667 | */ |
||
| 668 | protected function prepareOrderBy($order) |
||
| 669 | { |
||
| 670 | $conditions = array(); |
||
| 671 | |||
| 672 | foreach ((array) $order as $key => $value) { |
||
| 673 | if (is_numeric($key)) { |
||
| 674 | $conditions[] = $this->prepareOrder($value); |
||
| 675 | } else { |
||
| 676 | $conditions[] = $this->prepareOrder($key, $value); |
||
| 677 | } |
||
| 678 | } |
||
| 679 | |||
| 680 | return count($conditions) ? 'ORDER BY ' . implode(', ', $conditions) : null; |
||
| 681 | } |
||
| 682 | |||
| 683 | /** |
||
| 684 | * Prepare a LIMIT clause using the given limit and offset. |
||
| 685 | * |
||
| 686 | * @param int $limit [optional] |
||
| 687 | * @param int $offset [optional] |
||
| 688 | * @return string |
||
| 689 | */ |
||
| 690 | abstract protected function prepareLimit($limit = 0, $offset = 0); |
||
| 691 | |||
| 692 | /** |
||
| 693 | * Prepare a GROUP BY clause using the given groupings. |
||
| 694 | * |
||
| 695 | * @param string[] $groupings |
||
| 696 | * @return string |
||
| 697 | */ |
||
| 698 | protected function prepareGroupBy(array $groupings) |
||
| 699 | { |
||
| 700 | return count($groupings) ? 'GROUP BY ' . implode(', ', $this->identifier($groupings)) : null; |
||
| 701 | } |
||
| 702 | |||
| 703 | /** |
||
| 704 | * Prepare a HAVING clause using the given filter. |
||
| 705 | * |
||
| 706 | * @param array $filter |
||
| 707 | * @return string |
||
| 708 | */ |
||
| 709 | protected function prepareHaving(array $filter) |
||
| 710 | { |
||
| 711 | $clause = $this->prepareWhere($filter, 'AND', true); |
||
| 712 | |||
| 713 | if (empty($clause)) { |
||
| 714 | return null; |
||
| 715 | } |
||
| 716 | |||
| 717 | return "HAVING $clause"; |
||
| 718 | } |
||
| 719 | |||
| 720 | /** |
||
| 721 | * Prepare a SELECT statement using the given columns, table, clauses and |
||
| 722 | * options. |
||
| 723 | * |
||
| 724 | * TODO: Simplify this so that prepareSelect only actually prepares the |
||
| 725 | * SELECT and FROM clauses. The rest could be concatenated by |
||
| 726 | * translateRead(). |
||
| 727 | * |
||
| 728 | * @param string $table |
||
| 729 | * @param array|string $columns |
||
| 730 | * @param string $joins [optional] |
||
| 731 | * @param string $where [optional] |
||
| 732 | * @param string $order [optional] |
||
| 733 | * @param string $limit [optional] |
||
| 734 | * @param string $groupings [optional] |
||
| 735 | * @param string $having [optional] |
||
| 736 | * @param bool $distinct [optional] |
||
| 737 | * @return string |
||
| 738 | */ |
||
| 739 | abstract protected function prepareSelect( |
||
| 740 | $table, |
||
| 741 | $columns, |
||
| 742 | $joins = null, |
||
| 743 | $where = null, |
||
| 744 | $order = null, |
||
| 745 | $limit = null, |
||
| 746 | $groupings = null, |
||
| 747 | $having = null, |
||
| 748 | $distinct = false |
||
| 749 | ); |
||
| 750 | |||
| 751 | /** |
||
| 752 | * Prepare an INSERT INTO statement using the given table and data. |
||
| 753 | * |
||
| 754 | * @param string $table |
||
| 755 | * @param array $data |
||
| 756 | * @return string |
||
| 757 | */ |
||
| 758 | protected function prepareInsert($table, array $data) |
||
| 759 | { |
||
| 760 | $table = $this->identifier($table); |
||
| 761 | |||
| 762 | $columns = $this->identifier(array_keys($data)); |
||
| 763 | $values = $this->value(array_values($data)); |
||
| 764 | |||
| 765 | $columns = '(' . implode(', ', $columns) . ')'; |
||
| 766 | $values = '(' . implode(', ', $values) . ')'; |
||
| 767 | |||
| 768 | return static::concatenate(array('INSERT INTO', $table, $columns, 'VALUES', $values)); |
||
| 769 | } |
||
| 770 | |||
| 771 | /** |
||
| 772 | * Prepare an INSERT SELECT statement using the given table and |
||
| 773 | * subquery. |
||
| 774 | * |
||
| 775 | * @param string $table |
||
| 776 | * @param array $columns |
||
| 777 | * @param Storage\Query $subquery |
||
| 778 | * @return string |
||
| 779 | */ |
||
| 780 | public function prepareInsertSelect($table, array $columns, Storage\Query $subquery) |
||
| 781 | { |
||
| 782 | $table = $this->identifier($table); |
||
| 783 | |||
| 784 | if (!empty($columns)) { |
||
| 785 | $columns = $this->identifier($columns); |
||
| 786 | $columns = "(" . implode(", ", $columns) . ")"; |
||
| 787 | } |
||
| 788 | |||
| 789 | $subquery = (string) $this->translate($subquery); |
||
| 790 | |||
| 791 | return static::concatenate(array('INSERT INTO', $table, $columns, $subquery)); |
||
| 792 | } |
||
| 793 | |||
| 794 | /** |
||
| 795 | * Prepare an UPDATE statement with the given table, data and clauses. |
||
| 796 | * |
||
| 797 | * @param string $table |
||
| 798 | * @param array $data |
||
| 799 | * @param string $where [optional] |
||
| 800 | * @param string $limit [optional] |
||
| 801 | * @return string |
||
| 802 | */ |
||
| 803 | abstract protected function prepareUpdate($table, $data, $where = null, $limit = null); |
||
| 804 | |||
| 805 | /** |
||
| 806 | * Prepare a DELETE statement with the given table and clauses. |
||
| 807 | * |
||
| 808 | * @param string $table |
||
| 809 | * @param string $where [optional] |
||
| 810 | * @param string $limit [optional] |
||
| 811 | * @return string |
||
| 812 | */ |
||
| 813 | abstract protected function prepareDelete($table, $where = null, $limit = null); |
||
| 814 | |||
| 815 | /** |
||
| 816 | * Prepare a set of query parameters from the given set of columns. |
||
| 817 | * |
||
| 818 | * @param array $columns |
||
| 819 | * @return array |
||
| 820 | */ |
||
| 821 | protected function columnParameters($columns) |
||
| 822 | { |
||
| 823 | $parameters = array(); |
||
| 824 | |||
| 825 | foreach ($columns as $column) { |
||
| 826 | if ($column instanceof Storage\Query\Builder) { |
||
| 827 | $column = $column->query; |
||
| 828 | } |
||
| 829 | |||
| 830 | if ($column instanceof Storage\Query) { |
||
| 831 | $parameters = array_merge($parameters, $this->parameters($column)); |
||
| 832 | } |
||
| 833 | } |
||
| 834 | |||
| 835 | return $parameters; |
||
| 836 | } |
||
| 837 | |||
| 838 | /** |
||
| 839 | * Prepare a set of query parameters from the given data. |
||
| 840 | * |
||
| 841 | * @param array $data |
||
| 842 | * @return array |
||
| 843 | */ |
||
| 844 | protected function dataParameters($data) |
||
| 855 | } |
||
| 856 | |||
| 857 | /** |
||
| 858 | * Prepare a set of query parameters from the given set of joins. |
||
| 859 | * |
||
| 860 | * @param Join[] $joins |
||
| 861 | * @return array |
||
| 862 | */ |
||
| 863 | protected function joinParameters($joins) |
||
| 872 | } |
||
| 873 | |||
| 874 | /** |
||
| 875 | * Prepare a set of query parameters from the given filter. |
||
| 876 | * |
||
| 877 | * @param array $filter |
||
| 878 | * @return array |
||
| 879 | */ |
||
| 880 | protected function filterParameters($filter) |
||
| 881 | { |
||
| 882 | $parameters = array(); |
||
| 883 | |||
| 884 | foreach ($filter as $index => $value) { |
||
| 885 | if (is_array($value)) { |
||
| 886 | if (strtolower($index) === 'or') { |
||
| 887 | $parameters = array_merge($parameters, $this->filterParameters($value)); |
||
| 888 | } else { |
||
| 889 | foreach ($value as $in) { |
||
| 890 | if ($this->resolvesPlaceholder($in)) { |
||
| 891 | $parameters[] = $in; |
||
| 915 | } |
||
| 916 | |||
| 917 | /** |
||
| 918 | * Retrieve an array of parameters from the given query for executing a |
||
| 919 | * prepared query. |
||
| 920 | * |
||
| 921 | * @param Storage\Query $storageQuery |
||
| 922 | * @return array |
||
| 923 | */ |
||
| 924 | public function parameters(Storage\Query $storageQuery) |
||
| 948 | } |
||
| 949 | } |
||
| 950 |
Sometimes obsolete code just ends up commented out instead of removed. In this case it is better to remove the code once you have checked you do not need it.
The code might also have been commented out for debugging purposes. In this case it is vital that someone uncomments it again or your project may behave in very unexpected ways in production.
This check looks for comments that seem to be mostly valid code and reports them.