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