[TASK] Doctrine: Migrate remaining parts of ContentObjectRenderer
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Classes / Database / Query / QueryHelper.php
1 <?php
2 declare(strict_types=1);
3 namespace TYPO3\CMS\Core\Database\Query;
4
5 /*
6 * This file is part of the TYPO3 CMS project.
7 *
8 * It is free software; you can redistribute it and/or modify it under
9 * the terms of the GNU General Public License, either version 2
10 * of the License, or any later version.
11 *
12 * For the full copyright and license information, please read the
13 * LICENSE.txt file that was distributed with this source code.
14 *
15 * The TYPO3 project - inspiring people to share!
16 */
17
18 use TYPO3\CMS\Core\Utility\GeneralUtility;
19
20 /**
21 * Contains misc helper methods to build syntactically valid SQL queries.
22 * Most helper functions are required to deal with legacy data where the
23 * format of the input is not strict enough to reliably use the SQL parts
24 * in queries directly.
25 *
26 * @internal
27 */
28 class QueryHelper
29 {
30 /**
31 * Takes an input, possibly prefixed with ORDER BY, and explodes it into
32 * and array of arrays where each item consists of a fieldName and a order
33 * direction.
34 *
35 * Each of the resulting fieldName/direction pairs can be used passed into
36 * QueryBuilder::orderBy() so sort a query result set.
37 *
38 * @param string $input eg . "ORDER BY title, uid
39 * @return array|array[] Array of arrays containing fieldName/direction pairs
40 */
41 public static function parseOrderBy(string $input): array
42 {
43 $input = preg_replace('/^(?:ORDER[[:space:]]*BY[[:space:]]*)+/i', '', trim($input)) ?: '';
44 $orderExpressions = GeneralUtility::trimExplode(',', $input, true);
45
46 return array_map(
47 function ($expression) {
48 list($fieldName, $order) = GeneralUtility::trimExplode(' ', $expression, true);
49
50 return [$fieldName, $order];
51 },
52 $orderExpressions
53 );
54 }
55
56 /**
57 * Takes an input, possibly prefixed with FROM, and explodes it into
58 * and array of arrays where each item consists of a tableName and an
59 * optional alias name.
60 *
61 * Each of the resulting pairs can be used with QueryBuilder::from()
62 * to select from one or more tables.
63 *
64 * @param string $input eg . "FROM aTable, anotherTable AS b, aThirdTable c"
65 * @return array|array[] Array of arrays containing tableName/alias pairs
66 */
67 public static function parseTableList(string $input): array
68 {
69 $input = preg_replace('/^(?:FROM[[:space:]]+)+/i', '', trim($input)) ?: '';
70 $tableExpressions = GeneralUtility::trimExplode(',', $input, true);
71
72 return array_map(
73 function ($expression) {
74 list($tableName, $as, $alias) = GeneralUtility::trimExplode(' ', $expression, true);
75
76 if (!empty($as) && strtolower($as) === 'as' && !empty($alias)) {
77 return [$tableName, $alias];
78 } elseif (!empty($as) && empty($alias)) {
79 return [$tableName, $as];
80 } else {
81 return [$tableName, null];
82 }
83 },
84 $tableExpressions
85 );
86 }
87
88 /**
89 * Removes the prefix "GROUP BY" from the input string.
90 *
91 * This function should be used when you can't guarantee that the string
92 * that you want to use as a GROUP BY fragment is not prefixed.
93 *
94 * @param string $input eg. "GROUP BY title, uid
95 * @return array|string[] column names to group by
96 */
97 public static function parseGroupBy(string $input): array
98 {
99 $input = preg_replace('/^(?:GROUP[[:space:]]*BY[[:space:]]*)+/i', '', trim($input)) ?: '';
100
101 return GeneralUtility::trimExplode(',', $input, true);
102 }
103
104 /**
105 * Split a JOIN SQL fragment into table name, alias and join conditions.
106 *
107 * @param string $input eg. "JOIN tableName AS a ON a.uid = anotherTable.uid_foreign"
108 * @return array assoc array consisting of the keys tableName, tableAlias and joinCondition
109 */
110 public static function parseJoin(string $input): array
111 {
112 $input = trim($input);
113 $quoteCharacter = ' ';
114 // Check if the tableName is quoted
115 if ($input[0] === '`' || $input[0] === '"') {
116 $quoteCharacter .= $input[0];
117 $input = substr($input, 1);
118 $tableName = strtok($input, $quoteCharacter);
119 } else {
120 $tableName = strtok($input, $quoteCharacter);
121 }
122
123 $tableAlias = strtok($quoteCharacter);
124 if (strtolower($tableAlias) === 'as') {
125 $tableAlias = strtok($quoteCharacter);
126 // Skip the next token which must be ON
127 strtok(' ');
128 $joinCondition = strtok('');
129 } elseif (strtolower($tableAlias) === 'on') {
130 $tableAlias = null;
131 $joinCondition = strtok('');
132 } else {
133 // Skip the next token which must be ON
134 strtok(' ');
135 $joinCondition = strtok('');
136 }
137
138 // Catch the edge case that the table name is unquoted and the
139 // table alias is actually quoted. This will not work in the case
140 // that the quoted table alias contains whitespace.
141 if ($tableAlias[0] === '`' || $tableAlias[0] === '"') {
142 $tableAlias = substr($tableAlias, 1, -1);
143 }
144
145 $tableAlias = $tableAlias ?: $tableName;
146
147 return ['tableName' => $tableName, 'tableAlias' => $tableAlias, 'joinCondition' => $joinCondition];
148 }
149
150 /**
151 * Removes the prefixes AND/OR from the input string.
152 *
153 * This function should be used when you can't guarantee that the string
154 * that you want to use as a WHERE fragment is not prefixed.
155 *
156 * @param string $constraint The where part fragment with a possible leading AND or OR operator
157 * @return string The modified where part without leading operator
158 */
159 public static function stripLogicalOperatorPrefix(string $constraint): string
160 {
161 return preg_replace('/^(?:(AND|OR)[[:space:]]*)+/i', '', trim($constraint)) ?: '';
162 }
163
164 /**
165 * Returns the date and time formats compatible with the given database.
166 *
167 * This simple method should probably be deprecated and removed later.
168 *
169 * @return array
170 */
171 public static function getDateTimeFormats()
172 {
173 return [
174 'date' => [
175 'empty' => '0000-00-00',
176 'format' => 'Y-m-d'
177 ],
178 'datetime' => [
179 'empty' => '0000-00-00 00:00:00',
180 'format' => 'Y-m-d H:i:s'
181 ]
182 ];
183 }
184 }