62999a9ac6e95a9958762e5dd7ce991361544ac4
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Tests / Unit / Database / Query / QueryHelperTest.php
1 <?php
2 declare(strict_types=1);
3 namespace TYPO3\CMS\Core\Tests\Unit\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\Database\Query\QueryHelper;
19
20 /**
21 * Query helper test
22 */
23 class QueryHelperTest extends \TYPO3\Components\TestingFramework\Core\UnitTestCase
24 {
25 /**
26 * Test cases for stripping of leading logical operators in where constraints.
27 *
28 * @return array
29 */
30 public function stripLogicalOperatorPrefixDataProvider(): array
31 {
32 return [
33 'unprefixed input' => ['1=1', '1=1'],
34 'leading/trailing whitespace is removed' => [' 1=1 ', '1=1'],
35 'AND' => ['AND 1=1', '1=1'],
36 'AND with leading space' => [' AND 1=1', '1=1'],
37 'AND with mixed whitespace' => [' AND 1<>1', '1<>1'],
38 'AND with opening bracket' => ['AND (1=1)', '(1=1)'],
39 'AND without whitespace before bracket' => ['AND(1=1)', '(1=1)'],
40 'AND within input' => ['1=1 AND 2=2', '1=1 AND 2=2'],
41 'OR' => ['OR 1=1', '1=1'],
42 'OR with leading space' => [' OR 1=1', '1=1'],
43 'OR with mixed whitespace' => [' OR 1<>1', '1<>1'],
44 'OR with opening bracket' => ['OR (1=1)', '(1=1)'],
45 'OR without whitespace before bracket' => ['OR(1=1)', '(1=1)'],
46 'OR within input' => ['1=1 OR 2=2', '1=1 OR 2=2'],
47 ];
48 }
49
50 /**
51 * @test
52 * @dataProvider stripLogicalOperatorPrefixDataProvider
53 * @param string $input
54 * @param string $expectedSql
55 */
56 public function stripLogicalOperatorPrefixRemovesConstraintPrefixes(string $input, string $expectedSql)
57 {
58 $this->assertSame($expectedSql, QueryHelper::stripLogicalOperatorPrefix($input));
59 }
60
61 /**
62 * Test cases for parsing ORDER BY SQL fragments
63 *
64 * @return array
65 */
66 public function parseOrderByDataProvider(): array
67 {
68 return [
69 'empty string' => [
70 '',
71 [],
72 ],
73 'single field' => [
74 'aField',
75 [
76 ['aField', null],
77 ],
78 ],
79 'single field with leading whitespace' => [
80 ' aField',
81 [
82 ['aField', null],
83 ],
84 ],
85 'prefixed single field' => [
86 'ORDER BY aField',
87 [
88 ['aField', null],
89 ],
90 ],
91 'prefixed single field with leading whitespace' => [
92 ' ORDER BY aField',
93 [
94 ['aField', null],
95 ],
96 ],
97 'single field with direction' => [
98 'aField DESC',
99 [
100 ['aField', 'DESC'],
101 ],
102 ],
103 'multiple fields' => [
104 'aField,anotherField, aThirdField',
105 [
106 ['aField', null],
107 ['anotherField', null],
108 ['aThirdField', null]
109 ],
110 ],
111 'multiple fields with direction' => [
112 'aField ASC,anotherField, aThirdField DESC',
113 [
114 ['aField', 'ASC'],
115 ['anotherField', null],
116 ['aThirdField', 'DESC']
117 ],
118 ],
119 'prefixed multiple fields with direction' => [
120 'ORDER BY aField ASC,anotherField, aThirdField DESC',
121 [
122 ['aField', 'ASC'],
123 ['anotherField', null],
124 ['aThirdField', 'DESC']
125 ],
126 ],
127 'with table prefix' => [
128 'ORDER BY be_groups.title',
129 [
130 ['be_groups.title', null]
131 ]
132 ],
133 ];
134 }
135
136 /**
137 * @test
138 * @dataProvider parseOrderByDataProvider
139 * @param string $input
140 * @param array $expectedResult
141 */
142 public function parseOrderByTest(string $input, array $expectedResult)
143 {
144 $this->assertSame($expectedResult, QueryHelper::parseOrderBy($input));
145 }
146
147 /**
148 * Test cases for parsing FROM tableList SQL fragments
149 *
150 * @return array
151 */
152 public function parseTableListDataProvider(): array
153 {
154 return [
155 'single table' => [
156 'aTable',
157 [
158 ['aTable', null],
159 ],
160 ],
161 'single table with leading whitespace' => [
162 ' aTable',
163 [
164 ['aTable', null],
165 ],
166 ],
167 'prefixed single table' => [
168 'FROM aTable',
169 [
170 ['aTable', null],
171 ],
172 ],
173 'prefixed single table with leading whitespace' => [
174 ' FROM aTable',
175 [
176 ['aTable', null],
177 ],
178 ],
179 'single table with alias' => [
180 'aTable a',
181 [
182 ['aTable', 'a'],
183 ],
184 ],
185 'multiple tables' => [
186 'aTable,anotherTable, aThirdTable',
187 [
188 ['aTable', null],
189 ['anotherTable', null],
190 ['aThirdTable', null]
191 ],
192 ],
193 'multiple tables with aliases' => [
194 'aTable a,anotherTable, aThirdTable AS c',
195 [
196 ['aTable', 'a'],
197 ['anotherTable', null],
198 ['aThirdTable', 'c']
199 ],
200 ],
201 'prefixed multiple tables with aliases' => [
202 'FROM aTable a,anotherTable, aThirdTable AS c',
203 [
204 ['aTable', 'a'],
205 ['anotherTable', null],
206 ['aThirdTable', 'c']
207 ],
208 ]
209 ];
210 }
211
212 /**
213 * @test
214 * @dataProvider parseTableListDataProvider
215 * @param string $input
216 * @param array $expectedResult
217 */
218 public function parseTableListTest(string $input, array $expectedResult)
219 {
220 $this->assertSame($expectedResult, QueryHelper::parseTableList($input));
221 }
222
223 /**
224 * Test cases for parsing ORDER BY SQL fragments
225 *
226 * @return array
227 */
228 public function parseGroupByDataProvider(): array
229 {
230 return [
231 'single field' => [
232 'aField',
233 ['aField'],
234 ],
235 'single field with leading whitespace' => [
236 ' aField',
237 ['aField'],
238 ],
239 'prefixed single field' => [
240 'GROUP BY aField',
241 ['aField'],
242 ],
243 'prefixed single field with leading whitespace' => [
244 ' GROUP BY aField',
245 ['aField'],
246 ],
247 'multiple fields' => [
248 'aField,anotherField, aThirdField',
249 ['aField', 'anotherField', 'aThirdField']
250 ],
251 'prefixed multiple fields' => [
252 'GROUP BY aField,anotherField, aThirdField',
253 ['aField', 'anotherField', 'aThirdField']
254 ],
255 'with table prefix' => [
256 'GROUP BY be_groups.title',
257 ['be_groups.title']
258 ],
259 ];
260 }
261
262 /**
263 * @test
264 * @dataProvider parseGroupByDataProvider
265 * @param string $input
266 * @param array $expectedResult
267 */
268 public function parseGroupByTest(string $input, array $expectedResult)
269 {
270 $this->assertSame($expectedResult, QueryHelper::parseGroupBy($input));
271 }
272
273 /**
274 * Test cases for parsing JOIN fragments into table name, alias and conditions
275 *
276 * @return array
277 */
278 public function parseJoinDataProvider(): array
279 {
280 return [
281 'unquoted tableName' => [
282 'aTable ON aTable.uid = anotherTable.uid_foreign',
283 [
284 'tableName' => 'aTable',
285 'tableAlias' => 'aTable',
286 'joinCondition' => 'aTable.uid = anotherTable.uid_foreign'
287 ],
288 ],
289 'quoted tableName' => [
290 '`aTable` ON aTable.uid = anotherTable.uid_foreign',
291 [
292 'tableName' => 'aTable',
293 'tableAlias' => 'aTable',
294 'joinCondition' => 'aTable.uid = anotherTable.uid_foreign'
295 ],
296 ],
297 'quoted tableName with alias' => [
298 '`aTable` a ON a.uid = anotherTable.uid_foreign',
299 [
300 'tableName' => 'aTable',
301 'tableAlias' => 'a',
302 'joinCondition' => 'a.uid = anotherTable.uid_foreign'
303 ],
304 ],
305 'quoted tableName with quoted alias' => [
306 '`aTable` `a` ON a.uid = anotherTable.uid_foreign',
307 [
308 'tableName' => 'aTable',
309 'tableAlias' => 'a',
310 'joinCondition' => 'a.uid = anotherTable.uid_foreign'
311 ],
312 ],
313 'quoted tableName with AS alias' => [
314 '`aTable` AS anAlias ON anAlias.uid = anotherTable.uid_foreign',
315 [
316 'tableName' => 'aTable',
317 'tableAlias' => 'anAlias',
318 'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign'
319 ],
320 ],
321 'quoted tableName with AS quoted alias' => [
322 '`aTable` AS `anAlias` ON anAlias.uid = anotherTable.uid_foreign',
323 [
324 'tableName' => 'aTable',
325 'tableAlias' => 'anAlias',
326 'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign'
327 ],
328 ],
329 'unquoted tableName with AS quoted alias' => [
330 'aTable AS `anAlias` ON anAlias.uid = anotherTable.uid_foreign',
331 [
332 'tableName' => 'aTable',
333 'tableAlias' => 'anAlias',
334 'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign'
335 ],
336 ],
337 ];
338 }
339
340 /**
341 * @test
342 * @dataProvider parseJoinDataProvider
343 * @param string $input
344 * @param array $expected
345 */
346 public function parseJoinSplitsStatement(string $input, array $expected)
347 {
348 $this->assertSame($expected, QueryHelper::parseJoin($input));
349 }
350 }