[TASK] Turn todos into @todo to find them easier
[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 $sql['where'][] = $tableName . '.uid IN (SELECT ' . $columnMap->getParentKeyFieldName() . ' FROM ' . $relationTableName . ' WHERE ' . $columnMap->getChildKeyFieldName() . '=' . $parameterIdentifier . $additionalWhereForMatchFields . ')';
383 } elseif ($typeOfRelation === ColumnMap::RELATION_HAS_MANY) {
384 $parentKeyFieldName = $columnMap->getParentKeyFieldName();
385 if (isset($parentKeyFieldName)) {
386 $childTableName = $columnMap->getChildTableName();
387 $sql['where'][] = $tableName . '.uid=(SELECT ' . $childTableName . '.' . $parentKeyFieldName . ' FROM ' . $childTableName . ' WHERE ' . $childTableName . '.uid=' . $parameterIdentifier . ')';
388 } else {
389 $sql['where'][] = 'FIND_IN_SET(' . $parameterIdentifier . ', ' . $tableName . '.' . $columnName . ')';
390 }
391 } else {
392 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\RepositoryException('Unsupported or non-existing property name "' . $propertyName . '" used in relation matching.', 1327065745);
393 }
394 }
395 } else {
396 $this->parseDynamicOperand($comparison, $source, $sql);
397 }
398 }
399
400 /**
401 * Parse a DynamicOperand into SQL and parameter arrays.
402 *
403 * @param Qom\ComparisonInterface $comparison
404 * @param Qom\SourceInterface $source The source
405 * @param array &$sql The query parts
406 * @return void
407 */
408 protected function parseDynamicOperand(Qom\ComparisonInterface $comparison, Qom\SourceInterface $source, array &$sql) {
409 $operator = $this->resolveOperator($comparison->getOperator());
410 $operand = $comparison->getOperand1();
411
412 $constraintSQL = $this->parseOperand($operand, $source, $sql) . ' ' . $operator . ' ';
413
414 $parameterIdentifier = $this->normalizeParameterIdentifier($comparison->getParameterIdentifier());
415 if ($operator === 'IN') {
416 $parameterIdentifier = '(' . $parameterIdentifier . ')';
417 }
418 $constraintSQL .= $parameterIdentifier;
419
420 $sql['where'][] = $constraintSQL;
421 }
422
423 /**
424 * @param Qom\DynamicOperandInterface $operand
425 * @param Qom\SourceInterface $source The source
426 * @param array &$sql The query parts
427 * @return string
428 * @throws \InvalidArgumentException
429 */
430 protected function parseOperand(Qom\DynamicOperandInterface $operand, Qom\SourceInterface $source, array &$sql) {
431 if ($operand instanceof Qom\LowerCaseInterface) {
432 $constraintSQL = 'LOWER(' . $this->parseOperand($operand->getOperand(), $source, $sql) . ')';
433 } elseif ($operand instanceof Qom\UpperCaseInterface) {
434 $constraintSQL = 'UPPER(' . $this->parseOperand($operand->getOperand(), $source, $sql) . ')';
435 } elseif ($operand instanceof Qom\PropertyValueInterface) {
436 $propertyName = $operand->getPropertyName();
437 $className = '';
438 if ($source instanceof Qom\SelectorInterface) {
439 // @todo Only necessary to differ from Join
440 $className = $source->getNodeTypeName();
441 $tableName = $this->dataMapper->convertClassNameToTableName($className);
442 while (strpos($propertyName, '.') !== FALSE) {
443 $this->addUnionStatement($className, $tableName, $propertyName, $sql);
444 }
445 } elseif ($source instanceof Qom\JoinInterface) {
446 $tableName = $source->getJoinCondition()->getSelector1Name();
447 }
448 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
449 $constraintSQL = (!empty($tableName) ? $tableName . '.' : '') . $columnName;
450 } else {
451 throw new \InvalidArgumentException('Given operand has invalid type "' . get_class($operand) . '".', 1395710211);
452 }
453 return $constraintSQL;
454 }
455
456 /**
457 * Add a constraint to ensure that the record type of the returned tuples is matching the data type of the repository.
458 *
459 * @param string $className The class name
460 * @param array &$sql The query parts
461 * @return void
462 */
463 protected function addRecordTypeConstraint($className, &$sql) {
464 if ($className !== NULL) {
465 $dataMap = $this->dataMapper->getDataMap($className);
466 if ($dataMap->getRecordTypeColumnName() !== NULL) {
467 $recordTypes = array();
468 if ($dataMap->getRecordType() !== NULL) {
469 $recordTypes[] = $dataMap->getRecordType();
470 }
471 foreach ($dataMap->getSubclasses() as $subclassName) {
472 $subclassDataMap = $this->dataMapper->getDataMap($subclassName);
473 if ($subclassDataMap->getRecordType() !== NULL) {
474 $recordTypes[] = $subclassDataMap->getRecordType();
475 }
476 }
477 if (!empty($recordTypes)) {
478 $recordTypeStatements = array();
479 foreach ($recordTypes as $recordType) {
480 $tableName = $dataMap->getTableName();
481 $recordTypeStatements[] = $tableName . '.' . $dataMap->getRecordTypeColumnName() . '=' . $this->databaseHandle->fullQuoteStr($recordType, $tableName);
482 }
483 $sql['additionalWhereClause'][] = '(' . implode(' OR ', $recordTypeStatements) . ')';
484 }
485 }
486 }
487 }
488
489 /**
490 * Adds additional WHERE statements according to the query settings.
491 *
492 * @param QuerySettingsInterface $querySettings The TYPO3 CMS specific query settings
493 * @param string $tableName The table name to add the additional where clause for
494 * @param string &$sql
495 * @return void
496 */
497 protected function addAdditionalWhereClause(QuerySettingsInterface $querySettings, $tableName, &$sql) {
498 if ($querySettings->getRespectSysLanguage()) {
499 $this->addSysLanguageStatement($tableName, $sql, $querySettings);
500 }
501 if ($querySettings->getRespectStoragePage()) {
502 $this->addPageIdStatement($tableName, $sql, $querySettings->getStoragePageIds());
503 }
504 }
505
506 /**
507 * Adds enableFields and deletedClause to the query if necessary
508 *
509 * @param QuerySettingsInterface $querySettings
510 * @param string $tableName The database table name
511 * @param array &$sql The query parts
512 * @return void
513 */
514 protected function addVisibilityConstraintStatement(QuerySettingsInterface $querySettings, $tableName, array &$sql) {
515 $statement = '';
516 if (is_array($GLOBALS['TCA'][$tableName]['ctrl'])) {
517 $ignoreEnableFields = $querySettings->getIgnoreEnableFields();
518 $enableFieldsToBeIgnored = $querySettings->getEnableFieldsToBeIgnored();
519 $includeDeleted = $querySettings->getIncludeDeleted();
520 if ($this->environmentService->isEnvironmentInFrontendMode()) {
521 $statement .= $this->getFrontendConstraintStatement($tableName, $ignoreEnableFields, $enableFieldsToBeIgnored, $includeDeleted);
522 } else {
523 // TYPO3_MODE === 'BE'
524 $statement .= $this->getBackendConstraintStatement($tableName, $ignoreEnableFields, $includeDeleted);
525 }
526 if (!empty($statement)) {
527 $statement = strtolower(substr($statement, 1, 3)) === 'and' ? substr($statement, 5) : $statement;
528 $sql['additionalWhereClause'][] = $statement;
529 }
530 }
531 }
532
533 /**
534 * Returns constraint statement for frontend context
535 *
536 * @param string $tableName
537 * @param bool $ignoreEnableFields A flag indicating whether the enable fields should be ignored
538 * @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.
539 * @param bool $includeDeleted A flag indicating whether deleted records should be included
540 * @return string
541 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InconsistentQuerySettingsException
542 */
543 protected function getFrontendConstraintStatement($tableName, $ignoreEnableFields, array $enableFieldsToBeIgnored = array(), $includeDeleted) {
544 $statement = '';
545 if ($ignoreEnableFields && !$includeDeleted) {
546 if (count($enableFieldsToBeIgnored)) {
547 // array_combine() is necessary because of the way \TYPO3\CMS\Frontend\Page\PageRepository::enableFields() is implemented
548 $statement .= $this->getPageRepository()->enableFields($tableName, -1, array_combine($enableFieldsToBeIgnored, $enableFieldsToBeIgnored));
549 } else {
550 $statement .= $this->getPageRepository()->deleteClause($tableName);
551 }
552 } elseif (!$ignoreEnableFields && !$includeDeleted) {
553 $statement .= $this->getPageRepository()->enableFields($tableName);
554 } elseif (!$ignoreEnableFields && $includeDeleted) {
555 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);
556 }
557 return $statement;
558 }
559
560 /**
561 * Returns constraint statement for backend context
562 *
563 * @param string $tableName
564 * @param bool $ignoreEnableFields A flag indicating whether the enable fields should be ignored
565 * @param bool $includeDeleted A flag indicating whether deleted records should be included
566 * @return string
567 */
568 protected function getBackendConstraintStatement($tableName, $ignoreEnableFields, $includeDeleted) {
569 $statement = '';
570 if (!$ignoreEnableFields) {
571 $statement .= BackendUtility::BEenableFields($tableName);
572 }
573 if (!$includeDeleted) {
574 $statement .= BackendUtility::deleteClause($tableName);
575 }
576 return $statement;
577 }
578
579 /**
580 * Builds the language field statement
581 *
582 * @param string $tableName The database table name
583 * @param array &$sql The query parts
584 * @param QuerySettingsInterface $querySettings The TYPO3 CMS specific query settings
585 * @return void
586 */
587 protected function addSysLanguageStatement($tableName, array &$sql, $querySettings) {
588 if (is_array($GLOBALS['TCA'][$tableName]['ctrl'])) {
589 if (!empty($GLOBALS['TCA'][$tableName]['ctrl']['languageField'])) {
590 // Select all entries for the current language
591 $additionalWhereClause = $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . ' IN (' . (int)$querySettings->getLanguageUid() . ',-1)';
592 // If any language is set -> get those entries which are not translated yet
593 // They will be removed by t3lib_page::getRecordOverlay if not matching overlay mode
594 if (isset($GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'])
595 && $querySettings->getLanguageUid() > 0
596 ) {
597
598 $mode = $querySettings->getLanguageMode();
599 if ($mode === 'strict') {
600 $additionalWhereClause = $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=-1' .
601 ' OR (' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . ' = ' . (int)$querySettings->getLanguageUid() .
602 ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] . '=0' .
603 ') OR (' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=0' .
604 ' AND ' . $tableName . '.uid IN (SELECT ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] .
605 ' FROM ' . $tableName .
606 ' WHERE ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] . '>0' .
607 ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=' . (int)$querySettings->getLanguageUid();
608 } else {
609 $additionalWhereClause .= ' OR (' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=0' .
610 ' AND ' . $tableName . '.uid NOT IN (SELECT ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] .
611 ' FROM ' . $tableName .
612 ' WHERE ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['transOrigPointerField'] . '>0' .
613 ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['languageField'] . '=' . (int)$querySettings->getLanguageUid();
614 }
615
616 // Add delete clause to ensure all entries are loaded
617 if (isset($GLOBALS['TCA'][$tableName]['ctrl']['delete'])) {
618 $additionalWhereClause .= ' AND ' . $tableName . '.' . $GLOBALS['TCA'][$tableName]['ctrl']['delete'] . '=0';
619 }
620 $additionalWhereClause .= '))';
621 }
622 $sql['additionalWhereClause'][] = '(' . $additionalWhereClause . ')';
623 }
624 }
625 }
626
627 /**
628 * Builds the page ID checking statement
629 *
630 * @param string $tableName The database table name
631 * @param array &$sql The query parts
632 * @param array $storagePageIds list of storage page ids
633 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InconsistentQuerySettingsException
634 * @return void
635 */
636 protected function addPageIdStatement($tableName, array &$sql, array $storagePageIds) {
637 $tableColumns = $this->tableColumnCache->get($tableName);
638 if ($tableColumns === FALSE) {
639 $tableColumns = $this->databaseHandle->admin_get_fields($tableName);
640 $this->tableColumnCache->set($tableName, $tableColumns);
641 }
642 if (is_array($GLOBALS['TCA'][$tableName]['ctrl']) && array_key_exists('pid', $tableColumns)) {
643 $rootLevel = (int)$GLOBALS['TCA'][$tableName]['ctrl']['rootLevel'];
644 if ($rootLevel) {
645 if ($rootLevel === 1) {
646 $sql['additionalWhereClause'][] = $tableName . '.pid = 0';
647 }
648 } else {
649 if (empty($storagePageIds)) {
650 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InconsistentQuerySettingsException('Missing storage page ids.', 1365779762);
651 }
652 $sql['additionalWhereClause'][] = $tableName . '.pid IN (' . implode(', ', $storagePageIds) . ')';
653 }
654 }
655 }
656
657 /**
658 * Transforms a Join into SQL and parameter arrays
659 *
660 * @param Qom\JoinInterface $join The join
661 * @param array &$sql The query parts
662 * @return void
663 */
664 protected function parseJoin(Qom\JoinInterface $join, array &$sql) {
665 $leftSource = $join->getLeft();
666 $leftClassName = $leftSource->getNodeTypeName();
667 $leftTableName = $leftSource->getSelectorName();
668 $this->addRecordTypeConstraint($leftClassName, $sql);
669 $rightSource = $join->getRight();
670 if ($rightSource instanceof Qom\JoinInterface) {
671 $left = $rightSource->getLeft();
672 $rightClassName = $left->getNodeTypeName();
673 $rightTableName = $left->getSelectorName();
674 } else {
675 $rightClassName = $rightSource->getNodeTypeName();
676 $rightTableName = $rightSource->getSelectorName();
677 $sql['fields'][$leftTableName] = $rightTableName . '.*';
678 }
679 $this->addRecordTypeConstraint($rightClassName, $sql);
680 $sql['tables'][$leftTableName] = $leftTableName;
681 $sql['unions'][$rightTableName] = 'LEFT JOIN ' . $rightTableName;
682 $joinCondition = $join->getJoinCondition();
683 if ($joinCondition instanceof Qom\EquiJoinCondition) {
684 $column1Name = $this->dataMapper->convertPropertyNameToColumnName($joinCondition->getProperty1Name(), $leftClassName);
685 $column2Name = $this->dataMapper->convertPropertyNameToColumnName($joinCondition->getProperty2Name(), $rightClassName);
686 $sql['unions'][$rightTableName] .= ' ON ' . $joinCondition->getSelector1Name() . '.' . $column1Name . ' = ' . $joinCondition->getSelector2Name() . '.' . $column2Name;
687 }
688 if ($rightSource instanceof Qom\JoinInterface) {
689 $this->parseJoin($rightSource, $sql);
690 }
691 }
692
693 /**
694 * adds a union statement to the query, mostly for tables referenced in the where condition.
695 *
696 * @param string &$className
697 * @param string &$tableName
698 * @param array &$propertyPath
699 * @param array &$sql
700 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception
701 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InvalidRelationConfigurationException
702 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\MissingColumnMapException
703 */
704 protected function addUnionStatement(&$className, &$tableName, &$propertyPath, array &$sql) {
705 $explodedPropertyPath = explode('.', $propertyPath, 2);
706 $propertyName = $explodedPropertyPath[0];
707 $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
708 $tableName = $this->dataMapper->convertClassNameToTableName($className);
709 $columnMap = $this->dataMapper->getDataMap($className)->getColumnMap($propertyName);
710
711 if ($columnMap === NULL) {
712 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\MissingColumnMapException('The ColumnMap for property "' . $propertyName . '" of class "' . $className . '" is missing.', 1355142232);
713 }
714
715 $parentKeyFieldName = $columnMap->getParentKeyFieldName();
716 $childTableName = $columnMap->getChildTableName();
717
718 if ($childTableName === NULL) {
719 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\InvalidRelationConfigurationException('The relation information for property "' . $propertyName . '" of class "' . $className . '" is missing.', 1353170925);
720 }
721
722 if ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_ONE) {
723 if (isset($parentKeyFieldName)) {
724 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $tableName . '.uid=' . $childTableName . '.' . $parentKeyFieldName;
725 } else {
726 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $tableName . '.' . $columnName . '=' . $childTableName . '.uid';
727 }
728 $className = $this->dataMapper->getType($className, $propertyName);
729 } elseif ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_MANY) {
730 if (isset($parentKeyFieldName)) {
731 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $tableName . '.uid=' . $childTableName . '.' . $parentKeyFieldName;
732 } else {
733 $onStatement = '(FIND_IN_SET(' . $childTableName . '.uid, ' . $tableName . '.' . $columnName . '))';
734 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $onStatement;
735 }
736 $className = $this->dataMapper->getType($className, $propertyName);
737 } elseif ($columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_AND_BELONGS_TO_MANY) {
738 $relationTableName = $columnMap->getRelationTableName();
739 $sql['unions'][$relationTableName] = 'LEFT JOIN ' . $relationTableName . ' ON ' . $tableName . '.uid=' . $relationTableName . '.' . $columnMap->getParentKeyFieldName();
740 $sql['unions'][$childTableName] = 'LEFT JOIN ' . $childTableName . ' ON ' . $relationTableName . '.' . $columnMap->getChildKeyFieldName() . '=' . $childTableName . '.uid';
741 $className = $this->dataMapper->getType($className, $propertyName);
742 } else {
743 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception('Could not determine type of relation.', 1252502725);
744 }
745 // @todo check if there is another solution for this
746 $sql['keywords']['distinct'] = 'DISTINCT';
747 $propertyPath = $explodedPropertyPath[1];
748 $tableName = $childTableName;
749 }
750
751 /**
752 * Returns the SQL operator for the given JCR operator type.
753 *
754 * @param string $operator One of the JCR_OPERATOR_* constants
755 * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception
756 * @return string an SQL operator
757 */
758 protected function resolveOperator($operator) {
759 switch ($operator) {
760 case QueryInterface::OPERATOR_IN:
761 $operator = 'IN';
762 break;
763 case QueryInterface::OPERATOR_EQUAL_TO:
764 $operator = '=';
765 break;
766 case QueryInterface::OPERATOR_EQUAL_TO_NULL:
767 $operator = 'IS';
768 break;
769 case QueryInterface::OPERATOR_NOT_EQUAL_TO:
770 $operator = '!=';
771 break;
772 case QueryInterface::OPERATOR_NOT_EQUAL_TO_NULL:
773 $operator = 'IS NOT';
774 break;
775 case QueryInterface::OPERATOR_LESS_THAN:
776 $operator = '<';
777 break;
778 case QueryInterface::OPERATOR_LESS_THAN_OR_EQUAL_TO:
779 $operator = '<=';
780 break;
781 case QueryInterface::OPERATOR_GREATER_THAN:
782 $operator = '>';
783 break;
784 case QueryInterface::OPERATOR_GREATER_THAN_OR_EQUAL_TO:
785 $operator = '>=';
786 break;
787 case QueryInterface::OPERATOR_LIKE:
788 $operator = 'LIKE';
789 break;
790 default:
791 throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception('Unsupported operator encountered.', 1242816073);
792 }
793 return $operator;
794 }
795
796 /**
797 * @return \TYPO3\CMS\Frontend\Page\PageRepository
798 */
799 protected function getPageRepository() {
800 if (!$this->pageRepository instanceof \TYPO3\CMS\Frontend\Page\PageRepository) {
801 if ($this->environmentService->isEnvironmentInFrontendMode() && is_object($GLOBALS['TSFE'])) {
802 $this->pageRepository = $GLOBALS['TSFE']->sys_page;
803 } else {
804 $this->pageRepository = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance(\TYPO3\CMS\Frontend\Page\PageRepository::class);
805 }
806 }
807
808 return $this->pageRepository;
809 }
810 }