5a541ace8b2ce6a7c6c1b8a66c76d6fb4394807d
[Packages/TYPO3.CMS.git] / typo3 / sysext / extbase / Classes / Persistence / Generic / Storage / Typo3DbQueryParser.php
1 <?php
2 namespace TYPO3\CMS\Extbase\Persistence\Generic\Storage;
3
4 /*
5 * This file is part of the TYPO3 CMS project.
6 *
7 * It is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU General Public License, either version 2
9 * of the License, or any later version.
10 *
11 * For the full copyright and license information, please read the
12 * LICENSE.txt file that was distributed with this source code.
13 *
14 * The TYPO3 project - inspiring people to share!
15 */
16
17 use TYPO3\CMS\Backend\Utility\BackendUtility;
18 use TYPO3\CMS\Extbase\Persistence\Generic\Mapper\ColumnMap;
19 use TYPO3\CMS\Extbase\Persistence\Generic\QuerySettingsInterface;
20 use TYPO3\CMS\Extbase\Persistence\QueryInterface;
21 use TYPO3\CMS\Extbase\Persistence\Generic\Qom;
22
23 /**
24 * QueryParser, converting the qom to string representation
25 */
26 class Typo3DbQueryParser implements \TYPO3\CMS\Core\SingletonInterface {
27
28 /**
29 * The TYPO3 database object
30 *
31 * @var \TYPO3\CMS\Core\Database\DatabaseConnection
32 */
33 protected $databaseHandle;
34
35 /**
36 * @var \TYPO3\CMS\Extbase\Persistence\Generic\Mapper\DataMapper
37 * @inject
38 */
39 protected $dataMapper;
40
41 /**
42 * The TYPO3 page repository. Used for language and workspace overlay
43 *
44 * @var \TYPO3\CMS\Frontend\Page\PageRepository
45 */
46 protected $pageRepository;
47
48 /**
49 * @var \TYPO3\CMS\Core\Cache\CacheManager
50 * @inject
51 */
52 protected $cacheManager;
53
54 /**
55 * @var \TYPO3\CMS\Core\Cache\Frontend\VariableFrontend
56 */
57 protected $tableColumnCache;
58
59 /**
60 * @var \TYPO3\CMS\Extbase\Service\EnvironmentService
61 * @inject
62 */
63 protected $environmentService;
64
65 /**
66 * Constructor. takes the database handle from $GLOBALS['TYPO3_DB']
67 */
68 public function __construct() {
69 $this->databaseHandle = $GLOBALS['TYPO3_DB'];
70 }
71
72 /**
73 * Lifecycle method
74 *
75 * @return void
76 */
77 public function initializeObject() {
78 $this->tableColumnCache = $this->cacheManager->getCache('extbase_typo3dbbackend_tablecolumns');
79 }
80
81 /**
82 * Preparses the query and returns the query's hash and the parameters
83 *
84 * @param QueryInterface $query The query
85 * @return array the hash and the parameters
86 */
87 public function preparseQuery(QueryInterface $query) {
88 list($parameters, $operators) = $this->preparseComparison($query->getConstraint());
89 $hashPartials = array(
90 $query->getQuerySettings(),
91 $query->getSource(),
92 array_keys($parameters),
93 $operators,
94 $query->getOrderings(),
95 );
96 $hash = md5(serialize($hashPartials));
97
98 return array($hash, $parameters);
99 }
100
101 /**
102 * Walks through the qom's constraints and extracts the properties and values.
103 *
104 * In the qom the query structure and values are glued together. This walks through the
105 * qom and only extracts the parts necessary for generating the hash and filling the
106 * statement. It leaves out the actual statement generation, as it is the most time
107 * consuming.
108 *
109 * @param Qom\ConstraintInterface $comparison The constraint. Could be And-, Or-, Not- or ComparisonInterface
110 * @param string $qomPath current position of the child in the qom
111 * @return array Array of parameters and operators
112 * @throws \Exception
113 */
114 protected function preparseComparison($comparison, $qomPath = '') {
115 $parameters = array();
116 $operators = array();
117 $objectsToParse = array();
118
119 $delimiter = '';
120 if ($comparison instanceof Qom\AndInterface) {
121 $delimiter = 'AND';
122 $objectsToParse = array($comparison->getConstraint1(), $comparison->getConstraint2());
123 } elseif ($comparison instanceof Qom\OrInterface) {
124 $delimiter = 'OR';
125 $objectsToParse = array($comparison->getConstraint1(), $comparison->getConstraint2());
126 } elseif ($comparison instanceof Qom\NotInterface) {
127 $delimiter = 'NOT';
128 $objectsToParse = array($comparison->getConstraint());
129 } elseif ($comparison instanceof Qom\ComparisonInterface) {
130 $operand1 = $comparison->getOperand1();
131 $parameterIdentifier = $this->normalizeParameterIdentifier($qomPath . $operand1->getPropertyName());
132 $comparison->setParameterIdentifier($parameterIdentifier);
133 $operator = $comparison->getOperator();
134 $operand2 = $comparison->getOperand2();
135 if ($operator === QueryInterface::OPERATOR_IN) {
136 $items = array();
137 foreach ($operand2 as $value) {
138 $value = $this->dataMapper->getPlainValue($value);
139 if ($value !== NULL) {
140 $items[] = $value;
141 }
142 }
143 $parameters[$parameterIdentifier] = $items;
144 } else {
145 $parameters[$parameterIdentifier] = $operand2;
146 }
147 $operators[] = $operator;
148 } elseif (!is_object($comparison)) {
149 $parameters = array(array(), $comparison);
150 return array($parameters, $operators);
151 } else {
152 throw new \Exception('Can not hash Query Component "' . get_class($comparison) . '".', 1392840462);
153 }
154
155 $childObjectIterator = 0;
156 foreach ($objectsToParse as $objectToParse) {
157 list($preparsedParameters, $preparsedOperators) = $this->preparseComparison($objectToParse, $qomPath . $delimiter . $childObjectIterator++);
158 if (!empty($preparsedParameters)) {
159 $parameters = array_merge($parameters, $preparsedParameters);
160 }
161 if (!empty($preparsedOperators)) {
162 $operators = array_merge($operators, $preparsedOperators);
163 }
164 }
165
166 return array($parameters, $operators);
167 }
168
169 /**
170 * normalizes the parameter's identifier
171 *
172 * @param string $identifier
173 * @return string
174 * @todo come on, clean up that method!
175 */
176 public function normalizeParameterIdentifier($identifier) {
177 return ':' . preg_replace('/[^A-Za-z0-9]/', '', $identifier);
178 }
179
180 /**
181 * Parses the query and returns the SQL statement parts.
182 *
183 * @param QueryInterface $query The query
184 * @return array The SQL statement parts
185 */
186 public function parseQuery(QueryInterface $query) {
187 $sql = array();
188 $sql['keywords'] = array();
189 $sql['tables'] = array();
190 $sql['unions'] = array();
191 $sql['fields'] = array();
192 $sql['where'] = array();
193 $sql['additionalWhereClause'] = array();
194 $sql['orderings'] = array();
195 $sql['limit'] = ((int)$query->getLimit() ?: NULL);
196 $sql['offset'] = ((int)$query->getOffset() ?: NULL);
197 $source = $query->getSource();
198 $this->parseSource($source, $sql);
199 $this->parseConstraint($query->getConstraint(), $source, $sql);
200 $this->parseOrderings($query->getOrderings(), $source, $sql);
201
202 $tableNames = array_unique(array_keys($sql['tables'] + $sql['unions']));
203 foreach ($tableNames as $tableName) {
204 if (is_string($tableName) && !empty($tableName)) {
205 $this->addAdditionalWhereClause($query->getQuerySettings(), $tableName, $sql);
206 }
207 }
208
209 return $sql;
210 }
211
212 /**
213 * Add query parts that MUST NOT be cached.
214 * Call this function for any query
215 *
216 * @param QuerySettingsInterface $querySettings
217 * @param array $sql
218 * @throws \InvalidArgumentException
219 * @return void
220 */
221 public function addDynamicQueryParts(QuerySettingsInterface $querySettings, array &$sql) {
222 if (!isset($sql['additionalWhereClause'])) {
223 throw new \InvalidArgumentException('Invalid statement given.', 1399512421);
224 }
225 $tableNames = array_unique(array_keys($sql['tables'] + $sql['unions']));
226 foreach ($tableNames as $tableName) {
227 if (is_string($tableName) && !empty($tableName)) {
228 $this->addVisibilityConstraintStatement($querySettings, $tableName, $sql);
229 }
230 }
231 }
232
233 /**
234 * Transforms a Query Source into SQL and parameter arrays
235 *
236 * @param Qom\SourceInterface $source The source
237 * @param array &$sql
238 * @return void
239 */
240 protected function parseSource(Qom\SourceInterface $source, array &$sql) {
241 if ($source instanceof Qom\SelectorInterface) {
242 $className = $source->getNodeTypeName();
243 $tableName = $this->dataMapper->getDataMap($className)->getTableName();
244 $this->addRecordTypeConstraint($className, $sql);
245 $sql['fields'][$tableName] = $tableName . '.*';
246 $sql['tables'][$tableName] = $tableName;
247 } elseif ($source instanceof Qom\JoinInterface) {
248 $this->parseJoin($source, $sql);
249 }
250 }
251
252 /**
253 * Transforms a constraint into SQL and parameter arrays
254 *
255 * @param Qom\ConstraintInterface $constraint The constraint
256 * @param Qom\SourceInterface $source The source
257 * @param array &$sql The query parts
258 * @return void
259 */
260 protected function parseConstraint(Qom\ConstraintInterface $constraint = NULL, Qom\SourceInterface $source, array &$sql) {
261 if ($constraint instanceof Qom\AndInterface) {
262 $sql['where'][] = '(';
263 $this->parseConstraint($constraint->getConstraint1(), $source, $sql);
264 $sql['where'][] = ' AND ';
265 $this->parseConstraint($constraint->getConstraint2(), $source, $sql);
266 $sql['where'][] = ')';
267 } elseif ($constraint instanceof Qom\OrInterface) {
268 $sql['where'][] = '(';
269 $this->parseConstraint($constraint->getConstraint1(), $source, $sql);
270 $sql['where'][] = ' OR ';
271 $this->parseConstraint($constraint->getConstraint2(), $source, $sql);
272 $sql['where'][] = ')';
273 } elseif ($constraint instanceof Qom\NotInterface) {
274 $sql['where'][] = 'NOT (';
275 $this->parseConstraint($constraint->getConstraint(), $source, $sql);
276 $sql['where'][] = ')';
277 } elseif ($constraint instanceof Qom\ComparisonInterface) {
278 $this->parseComparison($constraint, $source, $sql);
279 }
280 }
281
282 /**
283 * Transforms orderings into SQL.
284 *
285 * @param array $orderings An array of orderings (Qom\Ordering)
286 * @param Qom\SourceInterface $source The source
287 * @param array &$sql The query parts
288 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnsupportedOrderException
289 * @return void
290 */
291 protected function parseOrderings(array $orderings, Qom\SourceInterface $source, array &$sql) {
292 foreach ($orderings as $propertyName => $order) {
293 switch ($order) {
294 case QueryInterface::ORDER_ASCENDING:
295 $order = 'ASC';
296 break;
297 case QueryInterface::ORDER_DESCENDING:
298 $order = 'DESC';
299 break;
300 default:
301 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnsupportedOrderException('Unsupported order encountered.', 1242816074);
302 }
303 $className = '';
304 $tableName = '';
305 if ($source instanceof Qom\SelectorInterface) {
306 $className = $source->getNodeTypeName();
307 $tableName = $this->dataMapper->convertClassNameToTableName($className);
308 while (strpos($propertyName, '.') !== FALSE) {
309 $this->addUnionStatement($className, $tableName, $propertyName, $sql);
310 }
311 } elseif ($source instanceof Qom\JoinInterface) {
312 $tableName = $source->getLeft()->getSelectorName();
313 }
314 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
315 if ($tableName !== '') {
316 $sql['orderings'][] = $tableName . '.' . $columnName . ' ' . $order;
317 } else {
318 $sql['orderings'][] = $columnName . ' ' . $order;
319 }
320 }
321 }
322
323 /**
324 * Parse a Comparison into SQL and parameter arrays.
325 *
326 * @param Qom\ComparisonInterface $comparison The comparison to parse
327 * @param Qom\SourceInterface $source The source
328 * @param array &$sql SQL query parts to add to
329 * @throws \RuntimeException
330 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\RepositoryException
331 * @return void
332 */
333 protected function parseComparison(Qom\ComparisonInterface $comparison, Qom\SourceInterface $source, array &$sql) {
334 $parameterIdentifier = $this->normalizeParameterIdentifier($comparison->getParameterIdentifier());
335
336 $operator = $comparison->getOperator();
337 $operand2 = $comparison->getOperand2();
338 if ($operator === QueryInterface::OPERATOR_IN) {
339 $hasValue = FALSE;
340 foreach ($operand2 as $value) {
341 $value = $this->dataMapper->getPlainValue($value);
342 if ($value !== NULL) {
343 $parameters[] = $value;
344 $hasValue = TRUE;
345 }
346 }
347 if ($hasValue === FALSE) {
348 $sql['where'][] = '1<>1';
349 } else {
350 $this->parseDynamicOperand($comparison, $source, $sql);
351 }
352 } elseif ($operator === QueryInterface::OPERATOR_CONTAINS) {
353 if ($operand2 === NULL) {
354 $sql['where'][] = '1<>1';
355 } else {
356 if (!$source instanceof Qom\SelectorInterface) {
357 throw new \RuntimeException('Source is not of type "SelectorInterface"', 1395362539);
358 }
359 $className = $source->getNodeTypeName();
360 $tableName = $this->dataMapper->convertClassNameToTableName($className);
361 $operand1 = $comparison->getOperand1();
362 $propertyName = $operand1->getPropertyName();
363 while (strpos($propertyName, '.') !== FALSE) {
364 $this->addUnionStatement($className, $tableName, $propertyName, $sql);
365 }
366 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
367 $dataMap = $this->dataMapper->getDataMap($className);
368 $columnMap = $dataMap->getColumnMap($propertyName);
369 $typeOfRelation = $columnMap instanceof ColumnMap ? $columnMap->getTypeOfRelation() : NULL;
370 if ($typeOfRelation === ColumnMap::RELATION_HAS_AND_BELONGS_TO_MANY) {
371 $relationTableName = $columnMap->getRelationTableName();
372 $relationTableMatchFields = $columnMap->getRelationTableMatchFields();
373 if (is_array($relationTableMatchFields)) {
374 $additionalWhere = array();
375 foreach ($relationTableMatchFields as $fieldName => $value) {
376 $additionalWhere[] = $fieldName . ' = ' . $this->databaseHandle->fullQuoteStr($value, $relationTableName);
377 }
378 $additionalWhereForMatchFields = ' AND ' . implode(' AND ', $additionalWhere);
379 } else {
380 $additionalWhereForMatchFields = '';
381 }
382 $operand2IsMultiValueType = \TYPO3\CMS\Extbase\Utility\TypeHandlingUtility::isValidTypeForMultiValueComparison($operand2);
383 $sql['where'][] = $tableName . '.uid IN (SELECT ' . $columnMap->getParentKeyFieldName()
384 . ' FROM ' . $relationTableName
385 . ' WHERE ' . $columnMap->getChildKeyFieldName()
386 . ($operand2IsMultiValueType
387 ? ' IN (' . $parameterIdentifier . ')'
388 : '=' . $parameterIdentifier
389 )
390 . $additionalWhereForMatchFields . ')';
391 } elseif ($typeOfRelation === ColumnMap::RELATION_HAS_MANY) {
392 $parentKeyFieldName = $columnMap->getParentKeyFieldName();
393 if (isset($parentKeyFieldName)) {
394 $childTableName = $columnMap->getChildTableName();
395 $sql['where'][] = $tableName . '.uid=(SELECT ' . $childTableName . '.' . $parentKeyFieldName . ' FROM ' . $childTableName . ' WHERE ' . $childTableName . '.uid=' . $parameterIdentifier . ')';
396 } else {
397 $sql['where'][] = 'FIND_IN_SET(' . $parameterIdentifier . ', ' . $tableName . '.' . $columnName . ')';
398 }
399 } else {
400 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\RepositoryException('Unsupported or non-existing property name "' . $propertyName . '" used in relation matching.', 1327065745);
401 }
402 }
403 } else {
404 $this->parseDynamicOperand($comparison, $source, $sql);
405 }
406 }
407
408 /**
409 * Parse a DynamicOperand into SQL and parameter arrays.
410 *
411 * @param Qom\ComparisonInterface $comparison
412 * @param Qom\SourceInterface $source The source
413 * @param array &$sql The query parts
414 * @return void
415 */
416 protected function parseDynamicOperand(Qom\ComparisonInterface $comparison, Qom\SourceInterface $source, array &$sql) {
417 $operator = $this->resolveOperator($comparison->getOperator());
418 $operand = $comparison->getOperand1();
419
420 $constraintSQL = $this->parseOperand($operand, $source, $sql) . ' ' . $operator . ' ';
421
422 $parameterIdentifier = $this->normalizeParameterIdentifier($comparison->getParameterIdentifier());
423 if ($operator === 'IN') {
424 $parameterIdentifier = '(' . $parameterIdentifier . ')';
425 }
426 $constraintSQL .= $parameterIdentifier;
427
428 $sql['where'][] = $constraintSQL;
429 }
430
431 /**
432 * @param Qom\DynamicOperandInterface $operand
433 * @param Qom\SourceInterface $source The source
434 * @param array &$sql The query parts
435 * @return string
436 * @throws \InvalidArgumentException
437 */
438 protected function parseOperand(Qom\DynamicOperandInterface $operand, Qom\SourceInterface $source, array &$sql) {
439 if ($operand instanceof Qom\LowerCaseInterface) {
440 $constraintSQL = 'LOWER(' . $this->parseOperand($operand->getOperand(), $source, $sql) . ')';
441 } elseif ($operand instanceof Qom\UpperCaseInterface) {
442 $constraintSQL = 'UPPER(' . $this->parseOperand($operand->getOperand(), $source, $sql) . ')';
443 } elseif ($operand instanceof Qom\PropertyValueInterface) {
444 $propertyName = $operand->getPropertyName();
445 $className = '';
446 if ($source instanceof Qom\SelectorInterface) {
447 // @todo Only necessary to differ from Join
448 $className = $source->getNodeTypeName();
449 $tableName = $this->dataMapper->convertClassNameToTableName($className);
450 while (strpos($propertyName, '.') !== FALSE) {
451 $this->addUnionStatement($className, $tableName, $propertyName, $sql);
452 }
453 } elseif ($source instanceof Qom\JoinInterface) {
454 $tableName = $source->getJoinCondition()->getSelector1Name();
455 }
456 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
457 $constraintSQL = (!empty($tableName) ? $tableName . '.' : '') . $columnName;
458 } else {
459 throw new \InvalidArgumentException('Given operand has invalid type "' . get_class($operand) . '".', 1395710211);
460 }
461 return $constraintSQL;
462 }
463
464 /**
465 * Add a constraint to ensure that the record type of the returned tuples is matching the data type of the repository.
466 *
467 * @param string $className The class name
468 * @param array &$sql The query parts
469 * @return void
470 */
471 protected function addRecordTypeConstraint($className, &$sql) {
472 if ($className !== NULL) {
473 $dataMap = $this->dataMapper->getDataMap($className);
474 if ($dataMap->getRecordTypeColumnName() !== NULL) {
475 $recordTypes = array();
476 if ($dataMap->getRecordType() !== NULL) {
477 $recordTypes[] = $dataMap->getRecordType();
478 }
479 foreach ($dataMap->getSubclasses() as $subclassName) {
480 $subclassDataMap = $this->dataMapper->getDataMap($subclassName);
481 if ($subclassDataMap->getRecordType() !== NULL) {
482 $recordTypes[] = $subclassDataMap->getRecordType();
483 }
484 }
485 if (!empty($recordTypes)) {
486 $recordTypeStatements = array();
487 foreach ($recordTypes as $recordType) {
488 $tableName = $dataMap->getTableName();
489 $recordTypeStatements[] = $tableName . '.' . $dataMap->getRecordTypeColumnName() . '=' . $this->databaseHandle->fullQuoteStr($recordType, $tableName);
490 }
491 $sql['additionalWhereClause'][] = '(' . implode(' OR ', $recordTypeStatements) . ')';
492 }
493 }
494 }
495 }
496
497 /**
498 * Adds additional WHERE statements according to the query settings.
499 *
500 * @param QuerySettingsInterface $querySettings The TYPO3 CMS specific query settings
501 * @param string $tableName The table name to add the additional where clause for
502 * @param string &$sql
503 * @return void
504 */
505 protected function addAdditionalWhereClause(QuerySettingsInterface $querySettings, $tableName, &$sql) {
506 if ($querySettings->getRespectSysLanguage()) {
507 $this->addSysLanguageStatement($tableName, $sql, $querySettings);
508 }
509 if ($querySettings->getRespectStoragePage()) {
510 $this->addPageIdStatement($tableName, $sql, $querySettings->getStoragePageIds());
511 }
512 }
513
514 /**
515 * Adds enableFields and deletedClause to the query if necessary
516 *
517 * @param QuerySettingsInterface $querySettings
518 * @param string $tableName The database table name
519 * @param array &$sql The query parts
520 * @return void
521 */
522 protected function addVisibilityConstraintStatement(QuerySettingsInterface $querySettings, $tableName, array &$sql) {
523 $statement = '';
524 if (is_array($GLOBALS['TCA'][$tableName]['ctrl'])) {
525 $ignoreEnableFields = $querySettings->getIgnoreEnableFields();
526 $enableFieldsToBeIgnored = $querySettings->getEnableFieldsToBeIgnored();
527 $includeDeleted = $querySettings->getIncludeDeleted();
528 if ($this->environmentService->isEnvironmentInFrontendMode()) {
529 $statement .= $this->getFrontendConstraintStatement($tableName, $ignoreEnableFields, $enableFieldsToBeIgnored, $includeDeleted);
530 } else {
531 // TYPO3_MODE === 'BE'
532 $statement .= $this->getBackendConstraintStatement($tableName, $ignoreEnableFields, $includeDeleted);
533 }
534 if (!empty($statement)) {
535 $statement = strtolower(substr($statement, 1, 3)) === 'and' ? substr($statement, 5) : $statement;
536 $sql['additionalWhereClause'][] = $statement;
537 }
538 }
539 }
540
541 /**
542 * Returns constraint statement for frontend context
543 *
544 * @param string $tableName
545 * @param bool $ignoreEnableFields A flag indicating whether the enable fields should be ignored
546 * @param array $enableFieldsToBeIgnored If $ignoreEnableFields is true, this array specifies enable fields to be ignored. If it is NULL or an empty array (default) all enable fields are ignored.
547 * @param bool $includeDeleted A flag indicating whether deleted records should be included
548 * @return string
549 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InconsistentQuerySettingsException
550 */
551 protected function getFrontendConstraintStatement($tableName, $ignoreEnableFields, array $enableFieldsToBeIgnored = array(), $includeDeleted) {
552 $statement = '';
553 if ($ignoreEnableFields && !$includeDeleted) {
554 if (count($enableFieldsToBeIgnored)) {
555 // array_combine() is necessary because of the way \TYPO3\CMS\Frontend\Page\PageRepository::enableFields() is implemented
556 $statement .= $this->getPageRepository()->enableFields($tableName, -1, array_combine($enableFieldsToBeIgnored, $enableFieldsToBeIgnored));
557 } else {
558 $statement .= $this->getPageRepository()->deleteClause($tableName);
559 }
560 } elseif (!$ignoreEnableFields && !$includeDeleted) {
561 $statement .= $this->getPageRepository()->enableFields($tableName);
562 } elseif (!$ignoreEnableFields && $includeDeleted) {
563 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InconsistentQuerySettingsException('Query setting "ignoreEnableFields=FALSE" can not be used together with "includeDeleted=TRUE" in frontend context.', 1327678173);
564 }
565 return $statement;
566 }
567
568 /**
569 * Returns constraint statement for backend context
570 *
571 * @param string $tableName
572 * @param bool $ignoreEnableFields A flag indicating whether the enable fields should be ignored
573 * @param bool $includeDeleted A flag indicating whether deleted records should be included
574 * @return string
575 */
576 protected function getBackendConstraintStatement($tableName, $ignoreEnableFields, $includeDeleted) {
577 $statement = '';
578 if (!$ignoreEnableFields) {
579 $statement .= BackendUtility::BEenableFields($tableName);
580 }
581 if (!$includeDeleted) {
582 $statement .= BackendUtility::deleteClause($tableName);
583 }
584 return $statement;
585 }
586
587 /**
588 * Builds the language field statement
589 *
590 * @param string $tableName The database table name
591 * @param array &$sql The query parts
592 * @param QuerySettingsInterface $querySettings The TYPO3 CMS specific query settings
593 * @return void
594 */
595 protected function addSysLanguageStatement($tableName, array &$sql, $querySettings) {
596 if (is_array($GLOBALS['TCA'][$tableName]['ctrl'])) {
597 if (!empty($GLOBALS['TCA'][$tableName]['ctrl']['languageField'])) {
598 // Select all entries for the current language
599 $additionalWhereClause = $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . ' IN (' . (int)$querySettings->getLanguageUid() . ',-1)';
600 // If any language is set -> get those entries which are not translated yet
601 // They will be removed by t3lib_page::getRecordOverlay if not matching overlay mode
602 if (isset($GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'])
603 && $querySettings->getLanguageUid() > 0
604 ) {
605
606 $mode = $querySettings->getLanguageMode();
607 if ($mode === 'strict') {
608 $additionalWhereClause = $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=-1' .
609 ' OR (' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . ' = ' . (int)$querySettings->getLanguageUid() .
610 ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] . '=0' .
611 ') OR (' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=0' .
612 ' AND ' . $tableName . '.uid IN (SELECT ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] .
613 ' FROM ' . $tableName .
614 ' WHERE ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] . '>0' .
615 ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=' . (int)$querySettings->getLanguageUid();
616 } else {
617 $additionalWhereClause .= ' OR (' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=0' .
618 ' AND ' . $tableName . '.uid NOT IN (SELECT ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] .
619 ' FROM ' . $tableName .
620 ' WHERE ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] . '>0' .
621 ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=' . (int)$querySettings->getLanguageUid();
622 }
623
624 // Add delete clause to ensure all entries are loaded
625 if (isset($GLOBALS['TCA'][$tableName]['ctrl']['delete'])) {
626 $additionalWhereClause .= ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['delete'] . '=0';
627 }
628 $additionalWhereClause .= '))';
629 }
630 $sql['additionalWhereClause'][] = '(' . $additionalWhereClause . ')';
631 }
632 }
633 }
634
635 /**
636 * Builds the page ID checking statement
637 *
638 * @param string $tableName The database table name
639 * @param array &$sql The query parts
640 * @param array $storagePageIds list of storage page ids
641 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InconsistentQuerySettingsException
642 * @return void
643 */
644 protected function addPageIdStatement($tableName, array &$sql, array $storagePageIds) {
645 $tableColumns = $this->tableColumnCache->get($tableName);
646 if ($tableColumns === FALSE) {
647 $tableColumns = $this->databaseHandle->admin_get_fields($tableName);
648 $this->tableColumnCache->set($tableName, $tableColumns);
649 }
650 if (is_array($GLOBALS['TCA'][$tableName]['ctrl']) && array_key_exists('pid', $tableColumns)) {
651 $rootLevel = (int)$GLOBALS['TCA'][$tableName]['ctrl']['rootLevel'];
652 if ($rootLevel) {
653 if ($rootLevel === 1) {
654 $sql['additionalWhereClause'][] = $tableName . '.pid = 0';
655 }
656 } else {
657 if (empty($storagePageIds)) {
658 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InconsistentQuerySettingsException('Missing storage page ids.', 1365779762);
659 }
660 $sql['additionalWhereClause'][] = $tableName . '.pid IN (' . implode(', ', $storagePageIds) . ')';
661 }
662 }
663 }
664
665 /**
666 * Transforms a Join into SQL and parameter arrays
667 *
668 * @param Qom\JoinInterface $join The join
669 * @param array &$sql The query parts
670 * @return void
671 */
672 protected function parseJoin(Qom\JoinInterface $join, array &$sql) {
673 $leftSource = $join->getLeft();
674 $leftClassName = $leftSource->getNodeTypeName();
675 $leftTableName = $leftSource->getSelectorName();
676 $this->addRecordTypeConstraint($leftClassName, $sql);
677 $rightSource = $join->getRight();
678 if ($rightSource instanceof Qom\JoinInterface) {
679 $left = $rightSource->getLeft();
680 $rightClassName = $left->getNodeTypeName();
681 $rightTableName = $left->getSelectorName();
682 } else {
683 $rightClassName = $rightSource->getNodeTypeName();
684 $rightTableName = $rightSource->getSelectorName();
685 $sql['fields'][$leftTableName] = $rightTableName . '.*';
686 }
687 $this->addRecordTypeConstraint($rightClassName, $sql);
688 $sql['tables'][$leftTableName] = $leftTableName;
689 $sql['unions'][$rightTableName] = 'LEFT JOIN ' . $rightTableName;
690 $joinCondition = $join->getJoinCondition();
691 if ($joinCondition instanceof Qom\EquiJoinCondition) {
692 $column1Name = $this->dataMapper->convertPropertyNameToColumnName($joinCondition->getProperty1Name(), $leftClassName);
693 $column2Name = $this->dataMapper->convertPropertyNameToColumnName($joinCondition->getProperty2Name(), $rightClassName);
694 $sql['unions'][$rightTableName] .= ' ON ' . $joinCondition->getSelector1Name() . '.' . $column1Name . ' = ' . $joinCondition->getSelector2Name() . '.' . $column2Name;
695 }
696 if ($rightSource instanceof Qom\JoinInterface) {
697 $this->parseJoin($rightSource, $sql);
698 }
699 }
700
701 /**
702 * adds a union statement to the query, mostly for tables referenced in the where condition.
703 *
704 * @param string &$className
705 * @param string &$tableName
706 * @param array &$propertyPath
707 * @param array &$sql
708 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception
709 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InvalidRelationConfigurationException
710 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\MissingColumnMapException
711 */
712 protected function addUnionStatement(&$className, &$tableName, &$propertyPath, array &$sql) {
713 $explodedPropertyPath = explode('.', $propertyPath, 2);
714 $propertyName = $explodedPropertyPath[0];
715 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
716 $tableName = $this->dataMapper->convertClassNameToTableName($className);
717 $columnMap = $this->dataMapper->getDataMap($className)->getColumnMap($propertyName);
718
719 if ($columnMap === NULL) {
720 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\MissingColumnMapException('The ColumnMap for property "' . $propertyName . '" of class "' . $className . '" is missing.', 1355142232);
721 }
722
723 $parentKeyFieldName = $columnMap->getParentKeyFieldName();
724 $childTableName = $columnMap->getChildTableName();
725
726 if ($childTableName === NULL) {
727 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InvalidRelationConfigurationException('The relation information for property "' . $propertyName . '" of class "' . $className . '" is missing.', 1353170925);
728 }
729
730 if ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_ONE) {
731 if (isset($parentKeyFieldName)) {
732 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $tableName . '.uid=' . $childTableName . '.' . $parentKeyFieldName;
733 } else {
734 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $tableName . '.' . $columnName . '=' . $childTableName . '.uid';
735 }
736 $className = $this->dataMapper->getType($className, $propertyName);
737 } elseif ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_MANY) {
738 if (isset($parentKeyFieldName)) {
739 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $tableName . '.uid=' . $childTableName . '.' . $parentKeyFieldName;
740 } else {
741 $onStatement = '(FIND_IN_SET(' . $childTableName . '.uid, ' . $tableName . '.' . $columnName . '))';
742 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $onStatement;
743 }
744 $className = $this->dataMapper->getType($className, $propertyName);
745 } elseif ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_AND_BELONGS_TO_MANY) {
746 $relationTableName = $columnMap->getRelationTableName();
747 $sql['unions'][$relationTableName] = 'LEFT JOIN ' . $relationTableName . ' ON ' . $tableName . '.uid=' . $relationTableName . '.' . $columnMap->getParentKeyFieldName();
748 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $relationTableName . '.' . $columnMap->getChildKeyFieldName() . '=' . $childTableName . '.uid';
749 $className = $this->dataMapper->getType($className, $propertyName);
750 } else {
751 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception('Could not determine type of relation.', 1252502725);
752 }
753 // @todo check if there is another solution for this
754 $sql['keywords']['distinct'] = 'DISTINCT';
755 $propertyPath = $explodedPropertyPath[1];
756 $tableName = $childTableName;
757 }
758
759 /**
760 * Returns the SQL operator for the given JCR operator type.
761 *
762 * @param string $operator One of the JCR_OPERATOR_* constants
763 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception
764 * @return string an SQL operator
765 */
766 protected function resolveOperator($operator) {
767 switch ($operator) {
768 case QueryInterface::OPERATOR_IN:
769 $operator = 'IN';
770 break;
771 case QueryInterface::OPERATOR_EQUAL_TO:
772 $operator = '=';
773 break;
774 case QueryInterface::OPERATOR_EQUAL_TO_NULL:
775 $operator = 'IS';
776 break;
777 case QueryInterface::OPERATOR_NOT_EQUAL_TO:
778 $operator = '!=';
779 break;
780 case QueryInterface::OPERATOR_NOT_EQUAL_TO_NULL:
781 $operator = 'IS NOT';
782 break;
783 case QueryInterface::OPERATOR_LESS_THAN:
784 $operator = '<';
785 break;
786 case QueryInterface::OPERATOR_LESS_THAN_OR_EQUAL_TO:
787 $operator = '<=';
788 break;
789 case QueryInterface::OPERATOR_GREATER_THAN:
790 $operator = '>';
791 break;
792 case QueryInterface::OPERATOR_GREATER_THAN_OR_EQUAL_TO:
793 $operator = '>=';
794 break;
795 case QueryInterface::OPERATOR_LIKE:
796 $operator = 'LIKE';
797 break;
798 default:
799 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception('Unsupported operator encountered.', 1242816073);
800 }
801 return $operator;
802 }
803
804 /**
805 * @return \TYPO3\CMS\Frontend\Page\PageRepository
806 */
807 protected function getPageRepository() {
808 if (!$this->pageRepository instanceof \TYPO3\CMS\Frontend\Page\PageRepository) {
809 if ($this->environmentService->isEnvironmentInFrontendMode() && is_object($GLOBALS['TSFE'])) {
810 $this->pageRepository = $GLOBALS['TSFE']->sys_page;
811 } else {
812 $this->pageRepository = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance(\TYPO3\CMS\Frontend\Page\PageRepository::class);
813 }
814 }
815
816 return $this->pageRepository;
817 }
818
819 }