[TASK] Make core/Tests/Unit/Database/ notice free
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Tests / Unit / Database / Query / QueryHelperTest.php
1 <?php
2 declare(strict_types = 1);
3
4 namespace TYPO3\CMS\Core\Tests\Unit\Database\Query;
5
6 /*
7 * This file is part of the TYPO3 CMS project.
8 *
9 * It is free software; you can redistribute it and/or modify it under
10 * the terms of the GNU General Public License, either version 2
11 * of the License, or any later version.
12 *
13 * For the full copyright and license information, please read the
14 * LICENSE.txt file that was distributed with this source code.
15 *
16 * The TYPO3 project - inspiring people to share!
17 */
18
19 use Prophecy\Argument;
20 use TYPO3\CMS\Core\Database\Connection;
21 use TYPO3\CMS\Core\Database\Query\QueryHelper;
22 use TYPO3\TestingFramework\Core\Unit\UnitTestCase;
23
24 /**
25 * Test case
26 */
27 class QueryHelperTest extends UnitTestCase
28 {
29 /**
30 * Test cases for stripping of leading logical operators in where constraints.
31 *
32 * @return array
33 */
34 public function stripLogicalOperatorPrefixDataProvider(): array
35 {
36 return [
37 'unprefixed input' => ['1=1', '1=1'],
38 'leading/trailing whitespace is removed' => [' 1=1 ', '1=1'],
39 'AND' => ['AND 1=1', '1=1'],
40 'AND with leading space' => [' AND 1=1', '1=1'],
41 'AND with mixed whitespace' => [' AND 1<>1', '1<>1'],
42 'AND with opening bracket' => ['AND (1=1)', '(1=1)'],
43 'AND without whitespace before bracket' => ['AND(1=1)', '(1=1)'],
44 'AND within input' => ['1=1 AND 2=2', '1=1 AND 2=2'],
45 'OR' => ['OR 1=1', '1=1'],
46 'OR with leading space' => [' OR 1=1', '1=1'],
47 'OR with mixed whitespace' => [' OR 1<>1', '1<>1'],
48 'OR with opening bracket' => ['OR (1=1)', '(1=1)'],
49 'OR without whitespace before bracket' => ['OR(1=1)', '(1=1)'],
50 'OR within input' => ['1=1 OR 2=2', '1=1 OR 2=2'],
51 ];
52 }
53
54 /**
55 * @test
56 * @dataProvider stripLogicalOperatorPrefixDataProvider
57 * @param string $input
58 * @param string $expectedSql
59 */
60 public function stripLogicalOperatorPrefixRemovesConstraintPrefixes(string $input, string $expectedSql): void
61 {
62 $this->assertSame($expectedSql, QueryHelper::stripLogicalOperatorPrefix($input));
63 }
64
65 /**
66 * Test cases for parsing ORDER BY SQL fragments
67 *
68 * @return array
69 */
70 public function parseOrderByDataProvider(): array
71 {
72 return [
73 'empty string' => [
74 '',
75 [],
76 ],
77 'single field' => [
78 'aField',
79 [
80 ['aField', null],
81 ],
82 ],
83 'single field with leading whitespace' => [
84 ' aField',
85 [
86 ['aField', null],
87 ],
88 ],
89 'prefixed single field' => [
90 'ORDER BY aField',
91 [
92 ['aField', null],
93 ],
94 ],
95 'prefixed single field with leading whitespace' => [
96 ' ORDER BY aField',
97 [
98 ['aField', null],
99 ],
100 ],
101 'single field with direction' => [
102 'aField DESC',
103 [
104 ['aField', 'DESC'],
105 ],
106 ],
107 'multiple fields' => [
108 'aField,anotherField, aThirdField',
109 [
110 ['aField', null],
111 ['anotherField', null],
112 ['aThirdField', null]
113 ],
114 ],
115 'multiple fields with direction' => [
116 'aField ASC,anotherField, aThirdField DESC',
117 [
118 ['aField', 'ASC'],
119 ['anotherField', null],
120 ['aThirdField', 'DESC']
121 ],
122 ],
123 'prefixed multiple fields with direction' => [
124 'ORDER BY aField ASC,anotherField, aThirdField DESC',
125 [
126 ['aField', 'ASC'],
127 ['anotherField', null],
128 ['aThirdField', 'DESC']
129 ],
130 ],
131 'with table prefix' => [
132 'ORDER BY be_groups.title',
133 [
134 ['be_groups.title', null]
135 ]
136 ],
137 ];
138 }
139
140 /**
141 * @test
142 * @dataProvider parseOrderByDataProvider
143 * @param string $input
144 * @param array $expectedResult
145 */
146 public function parseOrderByTest(string $input, array $expectedResult): void
147 {
148 $this->assertSame($expectedResult, QueryHelper::parseOrderBy($input));
149 }
150
151 /**
152 * Test cases for parsing FROM tableList SQL fragments
153 *
154 * @return array
155 */
156 public function parseTableListDataProvider(): array
157 {
158 return [
159 'single table' => [
160 'aTable',
161 [
162 ['aTable', null],
163 ],
164 ],
165 'single table with leading whitespace' => [
166 ' aTable',
167 [
168 ['aTable', null],
169 ],
170 ],
171 'prefixed single table' => [
172 'FROM aTable',
173 [
174 ['aTable', null],
175 ],
176 ],
177 'prefixed single table with leading whitespace' => [
178 ' FROM aTable',
179 [
180 ['aTable', null],
181 ],
182 ],
183 'single table with alias' => [
184 'aTable a',
185 [
186 ['aTable', 'a'],
187 ],
188 ],
189 'multiple tables' => [
190 'aTable,anotherTable, aThirdTable',
191 [
192 ['aTable', null],
193 ['anotherTable', null],
194 ['aThirdTable', null]
195 ],
196 ],
197 'multiple tables with aliases' => [
198 'aTable a,anotherTable, aThirdTable AS c',
199 [
200 ['aTable', 'a'],
201 ['anotherTable', null],
202 ['aThirdTable', 'c']
203 ],
204 ],
205 'prefixed multiple tables with aliases' => [
206 'FROM aTable a,anotherTable, aThirdTable AS c',
207 [
208 ['aTable', 'a'],
209 ['anotherTable', null],
210 ['aThirdTable', 'c']
211 ],
212 ]
213 ];
214 }
215
216 /**
217 * @test
218 * @dataProvider parseTableListDataProvider
219 * @param string $input
220 * @param array $expectedResult
221 */
222 public function parseTableListTest(string $input, array $expectedResult): void
223 {
224 $this->assertSame($expectedResult, QueryHelper::parseTableList($input));
225 }
226
227 /**
228 * Test cases for parsing ORDER BY SQL fragments
229 *
230 * @return array
231 */
232 public function parseGroupByDataProvider(): array
233 {
234 return [
235 'single field' => [
236 'aField',
237 ['aField'],
238 ],
239 'single field with leading whitespace' => [
240 ' aField',
241 ['aField'],
242 ],
243 'prefixed single field' => [
244 'GROUP BY aField',
245 ['aField'],
246 ],
247 'prefixed single field with leading whitespace' => [
248 ' GROUP BY aField',
249 ['aField'],
250 ],
251 'multiple fields' => [
252 'aField,anotherField, aThirdField',
253 ['aField', 'anotherField', 'aThirdField']
254 ],
255 'prefixed multiple fields' => [
256 'GROUP BY aField,anotherField, aThirdField',
257 ['aField', 'anotherField', 'aThirdField']
258 ],
259 'with table prefix' => [
260 'GROUP BY be_groups.title',
261 ['be_groups.title']
262 ],
263 ];
264 }
265
266 /**
267 * @test
268 * @dataProvider parseGroupByDataProvider
269 * @param string $input
270 * @param array $expectedResult
271 */
272 public function parseGroupByTest(string $input, array $expectedResult): void
273 {
274 $this->assertSame($expectedResult, QueryHelper::parseGroupBy($input));
275 }
276
277 /**
278 * Test cases for parsing JOIN fragments into table name, alias and conditions
279 *
280 * @return array
281 */
282 public function parseJoinDataProvider(): array
283 {
284 return [
285 'unquoted tableName' => [
286 'aTable ON aTable.uid = anotherTable.uid_foreign',
287 [
288 'tableName' => 'aTable',
289 'tableAlias' => 'aTable',
290 'joinCondition' => 'aTable.uid = anotherTable.uid_foreign'
291 ],
292 ],
293 'quoted tableName' => [
294 '`aTable` ON aTable.uid = anotherTable.uid_foreign',
295 [
296 'tableName' => 'aTable',
297 'tableAlias' => 'aTable',
298 'joinCondition' => 'aTable.uid = anotherTable.uid_foreign'
299 ],
300 ],
301 'quoted tableName with alias' => [
302 '`aTable` a ON a.uid = anotherTable.uid_foreign',
303 [
304 'tableName' => 'aTable',
305 'tableAlias' => 'a',
306 'joinCondition' => 'a.uid = anotherTable.uid_foreign'
307 ],
308 ],
309 'quoted tableName with quoted alias' => [
310 '`aTable` `a` ON a.uid = anotherTable.uid_foreign',
311 [
312 'tableName' => 'aTable',
313 'tableAlias' => 'a',
314 'joinCondition' => 'a.uid = anotherTable.uid_foreign'
315 ],
316 ],
317 'quoted tableName with AS alias' => [
318 '`aTable` AS anAlias ON anAlias.uid = anotherTable.uid_foreign',
319 [
320 'tableName' => 'aTable',
321 'tableAlias' => 'anAlias',
322 'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign'
323 ],
324 ],
325 'quoted tableName with AS quoted alias' => [
326 '`aTable` AS `anAlias` ON anAlias.uid = anotherTable.uid_foreign',
327 [
328 'tableName' => 'aTable',
329 'tableAlias' => 'anAlias',
330 'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign'
331 ],
332 ],
333 'unquoted tableName with AS quoted alias' => [
334 'aTable AS `anAlias` ON anAlias.uid = anotherTable.uid_foreign',
335 [
336 'tableName' => 'aTable',
337 'tableAlias' => 'anAlias',
338 'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign'
339 ],
340 ],
341 ];
342 }
343
344 /**
345 * @test
346 * @dataProvider parseJoinDataProvider
347 * @param string $input
348 * @param array $expected
349 */
350 public function parseJoinSplitsStatement(string $input, array $expected): void
351 {
352 $this->assertSame($expected, QueryHelper::parseJoin($input));
353 }
354
355 /**
356 * Test cases for quoting column/table name identifiers in SQL fragments
357 *
358 * @return array
359 */
360 public function quoteDatabaseIdentifierDataProvider(): array
361 {
362 return [
363 'no marked identifiers' => [
364 'colPos=0',
365 'colPos=0',
366 ],
367 'single fieldname' => [
368 '{#colPos}=0',
369 '"colPos"=0',
370 ],
371 'tablename and fieldname' => [
372 '{#tt_content.colPos}=0',
373 '"tt_content"."colPos"=0',
374 ],
375 'multiple fieldnames' => [
376 '{#colPos}={#aField}',
377 '"colPos"="aField"',
378 ],
379 ];
380 }
381
382 /**
383 * @test
384 * @dataProvider quoteDatabaseIdentifierDataProvider
385 * @param string $input
386 * @param string $expected
387 */
388 public function quoteDatabaseIdentifiers(string $input, string $expected): void
389 {
390 $connectionProphet = $this->prophesize(Connection::class);
391 $connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
392 $parts = array_map(
393 function ($identifier) {
394 return '"' . $identifier . '"';
395 },
396 explode('.', $args[0])
397 );
398
399 return implode('.', $parts);
400 });
401
402 $this->assertSame($expected, QueryHelper::quoteDatabaseIdentifiers($connectionProphet->reveal(), $input));
403 }
404 }