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