372053eb652e9cd3af8b8c9d3f63f4827a3d2e53
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Tests / Unit / Database / Query / Expression / ExpressionBuilderTest.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 Doctrine\DBAL\Platforms\AbstractPlatform;
19 use Prophecy\Argument;
20 use Prophecy\Prophecy\ObjectProphecy;
21 use TYPO3\CMS\Core\Database\Connection;
22 use TYPO3\CMS\Core\Database\Query\Expression\CompositeExpression;
23 use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
24 use TYPO3\CMS\Core\Tests\Unit\Database\Mocks\MockPlatform;
25
26 class ExpressionBuilderTest extends \TYPO3\TestingFramework\Core\Unit\UnitTestCase
27 {
28 /**
29 * @var Connection
30 */
31 protected $connectionProphet;
32
33 /**
34 * @var ExpressionBuilder
35 */
36 protected $subject;
37
38 /**
39 * @var string
40 */
41 protected $testTable = 'testTable';
42
43 /**
44 * Create a new database connection mock object for every test.
45 *
46 * @return void
47 */
48 protected function setUp()
49 {
50 parent::setUp();
51
52 /** @var Connection|ObjectProphecy $connectionProphet */
53 $this->connectionProphet = $this->prophesize(Connection::class);
54 $this->connectionProphet->quoteIdentifier(Argument::cetera())->willReturnArgument(0);
55
56 $this->subject = new ExpressionBuilder($this->connectionProphet->reveal());
57 }
58
59 /**
60 * @test
61 */
62 public function andXReturnType()
63 {
64 $result = $this->subject->andX('"uid" = 1', '"pid" = 0');
65
66 $this->assertInstanceOf(CompositeExpression::class, $result);
67 $this->assertSame(CompositeExpression::TYPE_AND, $result->getType());
68 }
69
70 /**
71 * @test
72 */
73 public function orXReturnType()
74 {
75 $result = $this->subject->orX('"uid" = 1', '"uid" = 7');
76
77 $this->assertInstanceOf(CompositeExpression::class, $result);
78 $this->assertSame(CompositeExpression::TYPE_OR, $result->getType());
79 }
80
81 /**
82 * @test
83 */
84 public function eqQuotesIdentifier()
85 {
86 $result = $this->subject->eq('aField', 1);
87
88 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
89 $this->assertSame('aField = 1', $result);
90 }
91
92 /**
93 * @test
94 */
95 public function neqQuotesIdentifier()
96 {
97 $result = $this->subject->neq('aField', 1);
98
99 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
100 $this->assertSame('aField <> 1', $result);
101 }
102
103 /**
104 * @test
105 */
106 public function ltQuotesIdentifier()
107 {
108 $result = $this->subject->lt('aField', 1);
109
110 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
111 $this->assertSame('aField < 1', $result);
112 }
113
114 /**
115 * @test
116 */
117 public function lteQuotesIdentifier()
118 {
119 $result = $this->subject->lte('aField', 1);
120
121 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
122 $this->assertSame('aField <= 1', $result);
123 }
124
125 /**
126 * @test
127 */
128 public function gtQuotesIdentifier()
129 {
130 $result = $this->subject->gt('aField', 1);
131
132 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
133 $this->assertSame('aField > 1', $result);
134 }
135
136 /**
137 * @test
138 */
139 public function gteQuotesIdentifier()
140 {
141 $result = $this->subject->gte('aField', 1);
142
143 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
144 $this->assertSame('aField >= 1', $result);
145 }
146
147 /**
148 * @test
149 */
150 public function isNullQuotesIdentifier()
151 {
152 $result = $this->subject->isNull('aField');
153
154 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
155 $this->assertSame('aField IS NULL', $result);
156 }
157
158 /**
159 * @test
160 */
161 public function isNotNullQuotesIdentifier()
162 {
163 $result = $this->subject->isNotNull('aField');
164
165 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
166 $this->assertSame('aField IS NOT NULL', $result);
167 }
168
169 /**
170 * @test
171 */
172 public function likeQuotesIdentifier()
173 {
174 $result = $this->subject->like('aField', "'aValue%'");
175
176 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
177 $this->assertSame("aField LIKE 'aValue%'", $result);
178 }
179
180 /**
181 * @test
182 */
183 public function notLikeQuotesIdentifier()
184 {
185 $result = $this->subject->notLike('aField', "'aValue%'");
186
187 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
188 $this->assertSame("aField NOT LIKE 'aValue%'", $result);
189 }
190
191 /**
192 * @test
193 */
194 public function inWithStringQuotesIdentifier()
195 {
196 $result = $this->subject->in('aField', '1,2,3');
197
198 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
199 $this->assertSame('aField IN (1,2,3)', $result);
200 }
201
202 /**
203 * @test
204 */
205 public function inWithArrayQuotesIdentifier()
206 {
207 $result = $this->subject->in('aField', [1, 2, 3]);
208
209 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
210 $this->assertSame('aField IN (1, 2, 3)', $result);
211 }
212
213 /**
214 * @test
215 */
216 public function notInWithStringQuotesIdentifier()
217 {
218 $result = $this->subject->notIn('aField', '1,2,3');
219
220 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
221 $this->assertSame('aField NOT IN (1,2,3)', $result);
222 }
223
224 /**
225 * @test
226 */
227 public function notInWithArrayQuotesIdentifier()
228 {
229 $result = $this->subject->notIn('aField', [1, 2, 3]);
230
231 $this->connectionProphet->quoteIdentifier('aField')->shouldHaveBeenCalled();
232 $this->assertSame('aField NOT IN (1, 2, 3)', $result);
233 }
234
235 /**
236 * @test
237 */
238 public function inSetForMySQL()
239 {
240 $databasePlatform = $this->prophesize(MockPlatform::class);
241 $databasePlatform->getName()->willReturn('mysql');
242
243 $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
244 return '`' . $args[0] . '`';
245 });
246
247 $this->connectionProphet->getDatabasePlatform()->willReturn($databasePlatform->reveal());
248
249 $result = $this->subject->inSet('aField', "'1'");
250
251 $this->assertSame('FIND_IN_SET(\'1\', `aField`)', $result);
252 }
253
254 /**
255 * @test
256 */
257 public function inSetForPostgreSQL()
258 {
259 $databasePlatform = $this->prophesize(MockPlatform::class);
260 $databasePlatform->getName()->willReturn('postgresql');
261
262 $this->connectionProphet->quote(',', Argument::cetera())->shouldBeCalled()->willReturn("','");
263 $this->connectionProphet->quote('\'1\'', Argument::cetera())->shouldBeCalled()->willReturn("'1'");
264 $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
265 return '"' . $args[0] . '"';
266 });
267
268 $this->connectionProphet->getDatabasePlatform()->willReturn($databasePlatform->reveal());
269
270 $result = $this->subject->inSet('aField', "'1'");
271
272 $this->assertSame('\'1\' = ANY(string_to_array("aField"::text, \',\'))', $result);
273 }
274
275 /**
276 * @test
277 */
278 public function inSetForSQLite()
279 {
280 $databasePlatform = $this->prophesize(MockPlatform::class);
281 $databasePlatform->getName()->willReturn('sqlite');
282 $databasePlatform->getStringLiteralQuoteCharacter()->willReturn("'");
283
284 $this->connectionProphet->quote(',', Argument::cetera())->shouldBeCalled()->willReturn("','");
285 $this->connectionProphet->quote('%,1,%', Argument::cetera())->shouldBeCalled()->willReturn("'%,1,%'");
286 $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
287 return '"' . $args[0] . '"';
288 });
289
290 $this->connectionProphet->getDatabasePlatform()->willReturn($databasePlatform->reveal());
291
292 $result = $this->subject->inSet('aField', "'1'");
293
294 $this->assertSame('\',\'||"aField"||\',\' LIKE \'%,1,%\'', $result);
295 }
296
297 /**
298 * @test
299 */
300 public function inSetForSQLiteWithQuoteCharactersInValue()
301 {
302 $databasePlatform = $this->prophesize(MockPlatform::class);
303 $databasePlatform->getName()->willReturn('sqlite');
304 $databasePlatform->getStringLiteralQuoteCharacter()->willReturn("'");
305
306 $this->connectionProphet->quote(',', Argument::cetera())->shouldBeCalled()->willReturn("','");
307 $this->connectionProphet->quote('%,\'Some\'Value,%', Argument::cetera())->shouldBeCalled()
308 ->willReturn("'%,''Some''Value,%'");
309 $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
310 return '"' . $args[0] . '"';
311 });
312
313 $this->connectionProphet->getDatabasePlatform()->willReturn($databasePlatform->reveal());
314
315 $result = $this->subject->inSet('aField', "'''Some''Value'");
316
317 $this->assertSame('\',\'||"aField"||\',\' LIKE \'%,\'\'Some\'\'Value,%\'', $result);
318 }
319
320 /**
321 * @test
322 */
323 public function inSetForSQLiteThrowsExceptionOnPositionalPlaceholder()
324 {
325 $databasePlatform = $this->prophesize(MockPlatform::class);
326 $databasePlatform->getName()->willReturn('sqlite');
327 $databasePlatform->getStringLiteralQuoteCharacter()->willReturn("'");
328
329 $this->connectionProphet->getDatabasePlatform()->willReturn($databasePlatform->reveal());
330
331 $this->expectException('InvalidArgumentException');
332 $this->expectExceptionCode(1476029421);
333
334 $this->subject->inSet('aField', '?');
335 }
336
337 /**
338 * @test
339 */
340 public function inSetForSQLiteThrowsExceptionOnNamedPlaceholder()
341 {
342 $databasePlatform = $this->prophesize(MockPlatform::class);
343 $databasePlatform->getName()->willReturn('sqlite');
344 $databasePlatform->getStringLiteralQuoteCharacter()->willReturn("'");
345
346 $this->connectionProphet->getDatabasePlatform()->willReturn($databasePlatform->reveal());
347
348 $this->expectException('InvalidArgumentException');
349 $this->expectExceptionCode(1476029421);
350
351 $this->subject->inSet('aField', ':dcValue1');
352 }
353
354 /**
355 * @test
356 */
357 public function defaultBitwiseAnd()
358 {
359 $databasePlatform = $this->prophesize(MockPlatform::class);
360
361 $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
362 return '"' . $args[0] . '"';
363 });
364
365 $this->connectionProphet->getDatabasePlatform()->willReturn($databasePlatform->reveal());
366
367 $this->assertSame('"aField" & 1', $this->subject->bitAnd('aField', 1));
368 }
369
370 /**
371 * @test
372 */
373 public function bitwiseAndForOracle()
374 {
375 $databasePlatform = $this->prophesize(MockPlatform::class);
376 $databasePlatform->getName()->willReturn('pdo_oracle');
377
378 $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
379 return '"' . $args[0] . '"';
380 });
381
382 $this->connectionProphet->getDatabasePlatform()->willReturn($databasePlatform->reveal());
383
384 $this->assertSame('BITAND("aField", 1)', $this->subject->bitAnd('aField', 1));
385 }
386
387 /**
388 * @test
389 */
390 public function maxQuotesIdentifier()
391 {
392 $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
393 $platform = new MockPlatform();
394 return $platform->quoteIdentifier($args[0]);
395 });
396
397 $this->assertSame('MAX("tableName"."fieldName")', $this->subject->max('tableName.fieldName'));
398 $this->assertSame(
399 'MAX("tableName"."fieldName") AS "anAlias"',
400 $this->subject->max('tableName.fieldName', 'anAlias')
401 );
402 }
403
404 /**
405 * @test
406 */
407 public function minQuotesIdentifier()
408 {
409 $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
410 $platform = new MockPlatform();
411 return $platform->quoteIdentifier($args[0]);
412 });
413
414 $this->assertSame('MIN("tableName"."fieldName")', $this->subject->min('tableName.fieldName'));
415 $this->assertSame(
416 'MIN("tableName"."fieldName") AS "anAlias"',
417 $this->subject->min('tableName.fieldName', 'anAlias')
418 );
419 }
420
421 /**
422 * @test
423 */
424 public function sumQuotesIdentifier()
425 {
426 $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
427 $platform = new MockPlatform();
428 return $platform->quoteIdentifier($args[0]);
429 });
430
431 $this->assertSame('SUM("tableName"."fieldName")', $this->subject->sum('tableName.fieldName'));
432 $this->assertSame(
433 'SUM("tableName"."fieldName") AS "anAlias"',
434 $this->subject->sum('tableName.fieldName', 'anAlias')
435 );
436 }
437
438 /**
439 * @test
440 */
441 public function avgQuotesIdentifier()
442 {
443 $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
444 $platform = new MockPlatform();
445 return $platform->quoteIdentifier($args[0]);
446 });
447
448 $this->assertSame('AVG("tableName"."fieldName")', $this->subject->avg('tableName.fieldName'));
449 $this->assertSame(
450 'AVG("tableName"."fieldName") AS "anAlias"',
451 $this->subject->avg('tableName.fieldName', 'anAlias')
452 );
453 }
454
455 /**
456 * @test
457 */
458 public function countQuotesIdentifier()
459 {
460 $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
461 $platform = new MockPlatform();
462 return $platform->quoteIdentifier($args[0]);
463 });
464
465 $this->assertSame('COUNT("tableName"."fieldName")', $this->subject->count('tableName.fieldName'));
466 $this->assertSame(
467 'COUNT("tableName"."fieldName") AS "anAlias"',
468 $this->subject->count('tableName.fieldName', 'anAlias')
469 );
470 }
471
472 /**
473 * @test
474 */
475 public function trimQuotesIdentifierWithDefaultValues()
476 {
477 $platform = new MockPlatform();
478 $this->connectionProphet->getDatabasePlatform(Argument::cetera())
479 ->shouldBeCalled()
480 ->willReturn($platform);
481 $this->connectionProphet->quoteIdentifier(Argument::cetera())
482 ->shouldBeCalled()
483 ->will(
484 function ($args) use ($platform) {
485 return $platform->quoteIdentifier($args[0]);
486 }
487 );
488
489 $this->assertSame(
490 'TRIM("tableName"."fieldName")',
491 $this->subject->trim('tableName.fieldName')
492 );
493 }
494
495 /**
496 * @return array
497 */
498 public function trimQuotesIdentifierDataProvider()
499 {
500 return [
501 'trim leading character' => [
502 AbstractPlatform::TRIM_LEADING,
503 'x',
504 'TRIM(LEADING "x" FROM "tableName"."fieldName")'
505 ],
506 'trim trailing character' => [
507 AbstractPlatform::TRIM_TRAILING,
508 'x',
509 'TRIM(TRAILING "x" FROM "tableName"."fieldName")',
510 ],
511 'trim character' => [
512 AbstractPlatform::TRIM_BOTH,
513 'x',
514 'TRIM(BOTH "x" FROM "tableName"."fieldName")',
515 ],
516 'trim space' => [
517 AbstractPlatform::TRIM_BOTH,
518 ' ',
519 'TRIM(BOTH " " FROM "tableName"."fieldName")',
520 ]
521 ];
522 }
523
524 /**
525 * @param int $position
526 * @param string $char
527 * @param string $expected
528 *
529 * @test
530 * @dataProvider trimQuotesIdentifierDataProvider
531 */
532 public function trimQuotesIdentifier(int $position, string $char, string $expected)
533 {
534 $platform = new MockPlatform();
535 $this->connectionProphet->getDatabasePlatform(Argument::cetera())
536 ->shouldBeCalled()
537 ->willReturn($platform);
538 $this->connectionProphet->quoteIdentifier(Argument::cetera())
539 ->shouldBeCalled()
540 ->will(
541 function ($args) use ($platform) {
542 return $platform->quoteIdentifier($args[0]);
543 }
544 );
545 $this->connectionProphet->quote(Argument::cetera())
546 ->shouldBeCalled()
547 ->will(
548 function ($args) {
549 return '"' . $args[0] . '"';
550 }
551 );
552
553 $this->assertSame(
554 $expected,
555 $this->subject->trim('tableName.fieldName', $position, $char)
556 );
557 }
558
559 /**
560 * @test
561 */
562 public function literalQuotesValue()
563 {
564 $this->connectionProphet->quote('aField', 'Doctrine\DBAL\Types\StringType')
565 ->shouldBeCalled()
566 ->willReturn('"aField"');
567 $result = $this->subject->literal('aField', 'Doctrine\DBAL\Types\StringType');
568
569 $this->assertSame('"aField"', $result);
570 }
571 }