6b0821d466cf9aaeed58b94f86d183d735789372
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / Tests / Unit / Database / SqlParserTest.php
1 <?php
2 namespace TYPO3\CMS\Dbal\Tests\Unit\Database;
3
4 /*
5 * This file is part of the TYPO3 CMS project.
6 *
7 * It is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU General Public License, either version 2
9 * of the License, or any later version.
10 *
11 * For the full copyright and license information, please read the
12 * LICENSE.txt file that was distributed with this source code.
13 *
14 * The TYPO3 project - inspiring people to share!
15 */
16
17 use TYPO3\CMS\Core\Utility\GeneralUtility;
18
19 /**
20 * Test case
21 */
22 class SqlParserTest extends AbstractTestCase
23 {
24 /**
25 * @var \TYPO3\CMS\Dbal\Database\SqlParser|\PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface
26 */
27 protected $subject;
28
29 /**
30 * Prepares the environment before running a test.
31 */
32 protected function setUp()
33 {
34 $subject = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\SqlParser::class, array('dummy'), array(), '', false);
35
36 $mockDatabaseConnection = $this->getMock(\TYPO3\CMS\Dbal\Database\DatabaseConnection::class, array(), array(), '', false);
37 $mockDatabaseConnection->lastHandlerKey = '_DEFAULT';
38 $subject->_set('databaseConnection', $mockDatabaseConnection);
39 $subject->_set('sqlCompiler', GeneralUtility::makeInstance(\TYPO3\CMS\Dbal\Database\SqlCompilers\Adodb::class, $mockDatabaseConnection));
40 $subject->_set('nativeSqlCompiler', GeneralUtility::makeInstance(\TYPO3\CMS\Dbal\Database\SqlCompilers\Mysql::class, $mockDatabaseConnection));
41
42 $this->subject = $subject;
43 }
44
45 /**
46 * Regression test
47 *
48 * @test
49 */
50 public function compileWhereClauseDoesNotDropClauses()
51 {
52 $clauses = array(
53 0 => array(
54 'modifier' => '',
55 'table' => 'pages',
56 'field' => 'fe_group',
57 'calc' => '',
58 'comparator' => '=',
59 'value' => array(
60 0 => '',
61 1 => '\''
62 )
63 ),
64 1 => array(
65 'operator' => 'OR',
66 'modifier' => '',
67 'func' => array(
68 'type' => 'IFNULL',
69 'default' => array(
70 0 => '1',
71 1 => '\''
72 ),
73 'table' => 'pages',
74 'field' => 'fe_group'
75 )
76 ),
77 2 => array(
78 'operator' => 'OR',
79 'modifier' => '',
80 'table' => 'pages',
81 'field' => 'fe_group',
82 'calc' => '',
83 'comparator' => '=',
84 'value' => array(
85 0 => '0',
86 1 => '\''
87 )
88 ),
89 3 => array(
90 'operator' => 'OR',
91 'modifier' => '',
92 'func' => array(
93 'type' => 'FIND_IN_SET',
94 'str' => array(
95 0 => '0',
96 1 => '\''
97 ),
98 'table' => 'pages',
99 'field' => 'fe_group'
100 ),
101 'comparator' => ''
102 ),
103 4 => array(
104 'operator' => 'OR',
105 'modifier' => '',
106 'func' => array(
107 'type' => 'FIND_IN_SET',
108 'str' => array(
109 0 => '-1',
110 1 => '\''
111 ),
112 'table' => 'pages',
113 'field' => 'fe_group'
114 ),
115 'comparator' => ''
116 ),
117 5 => array(
118 'operator' => 'OR',
119 'modifier' => '',
120 'func' => array(
121 'type' => 'CAST',
122 'table' => 'pages',
123 'field' => 'fe_group',
124 'datatype' => 'CHAR'
125 ),
126 'comparator' => '=',
127 'value' => array(
128 0 => '',
129 1 => '\''
130 )
131 )
132 );
133 $output = $this->subject->compileWhereClause($clauses);
134 $parts = explode(' OR ', $output);
135 $this->assertSame(count($clauses), count($parts));
136 $this->assertContains('IFNULL', $output);
137 }
138
139 /**
140 * Data provider for trimSqlReallyTrimsAllWhitespace
141 *
142 * @see trimSqlReallyTrimsAllWhitespace
143 */
144 public function trimSqlReallyTrimsAllWhitespaceDataProvider()
145 {
146 return array(
147 'Nothing to trim' => array('SELECT * FROM test WHERE 1=1;', 'SELECT * FROM test WHERE 1=1 '),
148 'Space after ;' => array('SELECT * FROM test WHERE 1=1; ', 'SELECT * FROM test WHERE 1=1 '),
149 'Space before ;' => array('SELECT * FROM test WHERE 1=1 ;', 'SELECT * FROM test WHERE 1=1 '),
150 'Space before and after ;' => array('SELECT * FROM test WHERE 1=1 ; ', 'SELECT * FROM test WHERE 1=1 '),
151 'Linefeed after ;' => array('SELECT * FROM test WHERE 1=1' . LF . ';', 'SELECT * FROM test WHERE 1=1 '),
152 'Linefeed before ;' => array('SELECT * FROM test WHERE 1=1;' . LF, 'SELECT * FROM test WHERE 1=1 '),
153 'Linefeed before and after ;' => array('SELECT * FROM test WHERE 1=1' . LF . ';' . LF, 'SELECT * FROM test WHERE 1=1 '),
154 'Tab after ;' => array('SELECT * FROM test WHERE 1=1' . TAB . ';', 'SELECT * FROM test WHERE 1=1 '),
155 'Tab before ;' => array('SELECT * FROM test WHERE 1=1;' . TAB, 'SELECT * FROM test WHERE 1=1 '),
156 'Tab before and after ;' => array('SELECT * FROM test WHERE 1=1' . TAB . ';' . TAB, 'SELECT * FROM test WHERE 1=1 '),
157 );
158 }
159
160 /**
161 * @test
162 * @dataProvider trimSqlReallyTrimsAllWhitespaceDataProvider
163 * @param string $sql The SQL to trim
164 * @param string $expected The expected trimmed SQL with single space at the end
165 */
166 public function trimSqlReallyTrimsAllWhitespace($sql, $expected)
167 {
168 $result = $this->subject->_call('trimSQL', $sql);
169 $this->assertSame($expected, $result);
170 }
171
172 /**
173 * Data provider for getValueReturnsCorrectValues
174 *
175 * @see getValueReturnsCorrectValues
176 */
177 public function getValueReturnsCorrectValuesDataProvider()
178 {
179 return array(
180 // description => array($parseString, $comparator, $mode, $expected)
181 'key definition without length' => array('(pid,input_1), ', '_LIST', 'INDEX', array('pid', 'input_1')),
182 'key definition with length' => array('(pid,input_1(30)), ', '_LIST', 'INDEX', array('pid', 'input_1(30)')),
183 'key definition without length (no mode)' => array('(pid,input_1), ', '_LIST', '', array('pid', 'input_1')),
184 'key definition with length (no mode)' => array('(pid,input_1(30)), ', '_LIST', '', array('pid', 'input_1(30)')),
185 'test1' => array('input_1 varchar(255) DEFAULT \'\' NOT NULL,', '', '', array('input_1')),
186 'test2' => array('varchar(255) DEFAULT \'\' NOT NULL,', '', '', array('varchar(255)')),
187 'test3' => array('DEFAULT \'\' NOT NULL,', '', '', array('DEFAULT')),
188 'test4' => array('\'\' NOT NULL,', '', '', array('', '\'')),
189 'test5' => array('NOT NULL,', '', '', array('NOT')),
190 'test6' => array('NULL,', '', '', array('NULL')),
191 'getValueOrParameter' => array('NULL,', '', '', array('NULL')),
192 );
193 }
194
195 /**
196 * @test
197 * @dataProvider getValueReturnsCorrectValuesDataProvider
198 * @param string $parseString the string to parse
199 * @param string $comparator The comparator used before. If "NOT IN" or "IN" then the value is expected to be a list of values. Otherwise just an integer (un-quoted) or string (quoted)
200 * @param string $mode The mode, eg. "INDEX
201 * @param string $expected
202 */
203 public function getValueReturnsCorrectValues($parseString, $comparator, $mode, $expected)
204 {
205 $result = $this->subject->_callRef('getValue', $parseString, $comparator, $mode);
206 $this->assertSame($expected, $result);
207 }
208
209 /**
210 * Data provider for parseSQL
211 *
212 * @see parseSQL
213 */
214 public function parseSQLDataProvider()
215 {
216 $testSql = array();
217 $testSql[] = 'CREATE TABLE tx_demo (';
218 $testSql[] = ' uid int(11) NOT NULL auto_increment,';
219 $testSql[] = ' pid int(11) DEFAULT \'0\' NOT NULL,';
220
221 $testSql[] = ' tstamp int(11) unsigned DEFAULT \'0\' NOT NULL,';
222 $testSql[] = ' crdate int(11) unsigned DEFAULT \'0\' NOT NULL,';
223 $testSql[] = ' cruser_id int(11) unsigned DEFAULT \'0\' NOT NULL,';
224 $testSql[] = ' deleted tinyint(4) unsigned DEFAULT \'0\' NOT NULL,';
225 $testSql[] = ' hidden tinyint(4) unsigned DEFAULT \'0\' NOT NULL,';
226 $testSql[] = ' starttime int(11) unsigned DEFAULT \'0\' NOT NULL,';
227 $testSql[] = ' endtime int(11) unsigned DEFAULT \'0\' NOT NULL,';
228
229 $testSql[] = ' input_1 varchar(255) DEFAULT \'\' NOT NULL,';
230 $testSql[] = ' input_2 varchar(255) DEFAULT \'\' NOT NULL,';
231 $testSql[] = ' select_child int(11) unsigned DEFAULT \'0\' NOT NULL,';
232
233 $testSql[] = ' PRIMARY KEY (uid),';
234 $testSql[] = ' KEY parent (pid,input_1),';
235 $testSql[] = ' KEY bar (tstamp,input_1(200),input_2(100),endtime)';
236 $testSql[] = ');';
237 $testSql = implode("\n", $testSql);
238 $expected = array(
239 'type' => 'CREATETABLE',
240 'TABLE' => 'tx_demo',
241 'FIELDS' => array(
242 'uid' => array(
243 'definition' => array(
244 'fieldType' => 'int',
245 'value' => '11',
246 'featureIndex' => array(
247 'NOTNULL' => array(
248 'keyword' => 'NOT NULL'
249 ),
250 'AUTO_INCREMENT' => array(
251 'keyword' => 'auto_increment'
252 )
253 )
254 )
255 ),
256 'pid' => array(
257 'definition' => array(
258 'fieldType' => 'int',
259 'value' => '11',
260 'featureIndex' => array(
261 'DEFAULT' => array(
262 'keyword' => 'DEFAULT',
263 'value' => array(
264 0 => '0',
265 1 => '\'',
266 )
267 ),
268 'NOTNULL' => array(
269 'keyword' => 'NOT NULL'
270 )
271 )
272 )
273 ),
274 'tstamp' => array(
275 'definition' => array(
276 'fieldType' => 'int',
277 'value' => '11',
278 'featureIndex' => array(
279 'UNSIGNED' => array(
280 'keyword' => 'unsigned'
281 ),
282 'DEFAULT' => array(
283 'keyword' => 'DEFAULT',
284 'value' => array(
285 0 => '0',
286 1 => '\''
287 )
288 ),
289 'NOTNULL' => array(
290 'keyword' => 'NOT NULL'
291 )
292 )
293 )
294 ),
295 'crdate' => array(
296 'definition' => array(
297 'fieldType' => 'int',
298 'value' => '11',
299 'featureIndex' => array(
300 'UNSIGNED' => array(
301 'keyword' => 'unsigned'
302 ),
303 'DEFAULT' => array(
304 'keyword' => 'DEFAULT',
305 'value' => array(
306 0 => '0',
307 1 => '\''
308 )
309 ),
310 'NOTNULL' => array(
311 'keyword' => 'NOT NULL'
312 )
313 )
314 )
315 ),
316 'cruser_id' => array(
317 'definition' => array(
318 'fieldType' => 'int',
319 'value' => '11',
320 'featureIndex' => array(
321 'UNSIGNED' => array(
322 'keyword' => 'unsigned'
323 ),
324 'DEFAULT' => array(
325 'keyword' => 'DEFAULT',
326 'value' => array(
327 0 => '0',
328 1 => '\'',
329 )
330 ),
331 'NOTNULL' => array(
332 'keyword' => 'NOT NULL'
333 )
334 )
335 )
336 ),
337 'deleted' => array(
338 'definition' => array(
339 'fieldType' => 'tinyint',
340 'value' => '4',
341 'featureIndex' => array(
342 'UNSIGNED' => array(
343 'keyword' => 'unsigned'
344 ),
345 'DEFAULT' => array(
346 'keyword' => 'DEFAULT',
347 'value' => array(
348 0 => '0',
349 1 => '\''
350 )
351 ),
352 'NOTNULL' => array(
353 'keyword' => 'NOT NULL'
354 )
355 )
356 )
357 ),
358 'hidden' => array(
359 'definition' => array(
360 'fieldType' => 'tinyint',
361 'value' => '4',
362 'featureIndex' => array(
363 'UNSIGNED' => array(
364 'keyword' => 'unsigned'
365 ),
366 'DEFAULT' => array(
367 'keyword' => 'DEFAULT',
368 'value' => array(
369 0 => '0',
370 1 => '\''
371 )
372 ),
373 'NOTNULL' => array(
374 'keyword' => 'NOT NULL'
375 )
376 )
377 )
378 ),
379 'starttime' => array(
380 'definition' => array(
381 'fieldType' => 'int',
382 'value' => '11',
383 'featureIndex' => array(
384 'UNSIGNED' => array(
385 'keyword' => 'unsigned'
386 ),
387 'DEFAULT' => array(
388 'keyword' => 'DEFAULT',
389 'value' => array(
390 0 => '0',
391 1 => '\''
392 )
393 ),
394 'NOTNULL' => array(
395 'keyword' => 'NOT NULL'
396 )
397 )
398 )
399 ),
400 'endtime' => array(
401 'definition' => array(
402 'fieldType' => 'int',
403 'value' => '11',
404 'featureIndex' => array(
405 'UNSIGNED' => array(
406 'keyword' => 'unsigned'
407 ),
408 'DEFAULT' => array(
409 'keyword' => 'DEFAULT',
410 'value' => array(
411 0 => '0',
412 1 => '\'',
413 )
414 ),
415 'NOTNULL' => array(
416 'keyword' => 'NOT NULL'
417 )
418 )
419 )
420 ),
421 'input_1' => array(
422 'definition' => array(
423 'fieldType' => 'varchar',
424 'value' => '255',
425 'featureIndex' => array(
426 'DEFAULT' => array(
427 'keyword' => 'DEFAULT',
428 'value' => array(
429 0 => '',
430 1 => '\'',
431 )
432 ),
433 'NOTNULL' => array(
434 'keyword' => 'NOT NULL'
435 )
436 )
437 )
438 ),
439 'input_2' => array(
440 'definition' => array(
441 'fieldType' => 'varchar',
442 'value' => '255',
443 'featureIndex' => array(
444 'DEFAULT' => array(
445 'keyword' => 'DEFAULT',
446 'value' => array(
447 0 => '',
448 1 => '\'',
449 )
450 ),
451 'NOTNULL' => array(
452 'keyword' => 'NOT NULL'
453 )
454 )
455 )
456 ),
457 'select_child' => array(
458 'definition' => array(
459 'fieldType' => 'int',
460 'value' => '11',
461 'featureIndex' => array(
462 'UNSIGNED' => array(
463 'keyword' => 'unsigned'
464 ),
465 'DEFAULT' => array(
466 'keyword' => 'DEFAULT',
467 'value' => array(
468 0 => '0',
469 1 => '\''
470 )
471 ),
472 'NOTNULL' => array(
473 'keyword' => 'NOT NULL'
474 )
475 )
476 )
477 )
478 ),
479 'KEYS' => array(
480 'PRIMARYKEY' => array(
481 0 => 'uid'
482 ),
483 'parent' => array(
484 0 => 'pid',
485 1 => 'input_1',
486 ),
487 'bar' => array(
488 0 => 'tstamp',
489 1 => 'input_1(200)',
490 2 => 'input_2(100)',
491 3 => 'endtime',
492 )
493 )
494 );
495
496 return array(
497 'test1' => array($testSql, $expected)
498 );
499 }
500
501 /**
502 * @test
503 * @dataProvider parseSQLDataProvider
504 * @param string $sql The SQL to trim
505 * @param array $expected The expected trimmed SQL with single space at the end
506 */
507 public function parseSQL($sql, $expected)
508 {
509 $result = $this->subject->_callRef('parseSQL', $sql);
510 $this->assertSame($expected, $result);
511 }
512
513 /**
514 * @test
515 */
516 public function canExtractPartsOfAQuery()
517 {
518 $parseString = 'SELECT *' . LF . 'FROM pages WHERE pid IN (1,2,3,4)';
519 $regex = '^SELECT[[:space:]]+(.*)[[:space:]]+';
520 $trimAll = true;
521 $fields = $this->subject->_callRef('nextPart', $parseString, $regex, $trimAll);
522 $this->assertEquals('*', $fields);
523 $this->assertEquals('FROM pages WHERE pid IN (1,2,3,4)', $parseString);
524 $regex = '^FROM ([^)]+) WHERE';
525 $table = $this->subject->_callRef('nextPart', $parseString, $regex);
526 $this->assertEquals('pages', $table);
527 $this->assertEquals('pages WHERE pid IN (1,2,3,4)', $parseString);
528 }
529
530 /**
531 * @test
532 */
533 public function canGetIntegerValue()
534 {
535 $parseString = '1024';
536 $result = $this->subject->_callRef('getValue', $parseString);
537 $expected = array(1024);
538 $this->assertEquals($expected, $result);
539 }
540
541 /**
542 * @test
543 * @see http://forge.typo3.org/issues/21887
544 */
545 public function canGetStringValue()
546 {
547 $parseString = '"some owner\\\'s string"';
548 $result = $this->subject->_callRef('getValue', $parseString);
549 $expected = array('some owner\'s string', '"');
550 $this->assertEquals($expected, $result);
551 }
552
553 /**
554 * @test
555 * @see http://forge.typo3.org/issues/21887
556 */
557 public function canGetStringValueWithSingleQuote()
558 {
559 $parseString = '\'some owner\\\'s string\'';
560 $result = $this->subject->_callRef('getValue', $parseString);
561 $expected = array('some owner\'s string', '\'');
562 $this->assertEquals($expected, $result);
563 }
564
565 /**
566 * @test
567 * @see http://forge.typo3.org/issues/21887
568 */
569 public function canGetStringValueWithDoubleQuote()
570 {
571 $parseString = '"the \\"owner\\" is here"';
572 $result = $this->subject->_callRef('getValue', $parseString);
573 $expected = array('the "owner" is here', '"');
574 $this->assertEquals($expected, $result);
575 }
576
577 /**
578 * @test
579 */
580 public function canGetListOfValues()
581 {
582 $parseString = '( 1, 2, 3 ,4)';
583 $operator = 'IN';
584 $result = $this->subject->_callRef('getValue', $parseString, $operator);
585 $expected = array(
586 array(1),
587 array(2),
588 array(3),
589 array(4)
590 );
591 $this->assertEquals($expected, $result);
592 }
593
594 /**
595 * @test
596 */
597 public function parseWhereClauseReturnsArray()
598 {
599 $parseString = 'uid IN (1,2) AND (starttime < ' . time() . ' OR cruser_id + 10 < 20)';
600 $result = $this->subject->parseWhereClause($parseString);
601 $this->assertInternalType('array', $result);
602 $this->assertEmpty($parseString);
603 }
604
605 /**
606 * @test
607 */
608 public function canSelectAllFieldsFromPages()
609 {
610 $sql = 'SELECT * FROM pages';
611 $expected = $sql;
612 $result = $this->subject->debug_testSQL($sql);
613 $this->assertEquals($expected, $this->cleanSql($result));
614 }
615
616 /**
617 * @test
618 */
619 public function canParseTruncateTable()
620 {
621 $sql = 'TRUNCATE TABLE be_users';
622 $expected = $sql;
623 $result = $this->subject->debug_testSQL($sql);
624 $this->assertEquals($expected, $this->cleanSql($result));
625 }
626
627 /**
628 * @test
629 * @see http://forge.typo3.org/issues/22049
630 */
631 public function canParseAndCompileBetweenOperator()
632 {
633 $parseString = '((scheduled BETWEEN 1265068628 AND 1265068828 ) OR scheduled <= 1265068728) AND NOT exec_time AND NOT process_id AND page_id=1 AND parameters_hash = \'854e9a2a77\'';
634 $result = $this->subject->parseWhereClause($parseString);
635 $this->assertInternalType('array', $result);
636 $this->assertEmpty($parseString);
637
638 $result = $this->subject->compileWhereClause($result);
639 $expected = '((scheduled BETWEEN 1265068628 AND 1265068828) OR scheduled <= 1265068728) AND NOT exec_time AND NOT process_id AND page_id = 1 AND parameters_hash = \'854e9a2a77\'';
640 $this->assertEquals($expected, $this->cleanSql($result));
641 }
642
643 /**
644 * @test
645 */
646 public function canParseInsertWithoutSpaceAfterValues()
647 {
648 $parseString = 'INSERT INTO static_country_zones VALUES(\'483\', \'0\', \'NL\', \'NLD\', \'528\', \'DR\', \'Drenthe\', \'\');';
649 $components = $this->subject->_callRef('parseINSERT', $parseString);
650 $this->assertInternalType('array', $components);
651
652 $result = $this->subject->compileSQL($components);
653 $expected = 'INSERT INTO static_country_zones VALUES (\'483\',\'0\',\'NL\',\'NLD\',\'528\',\'DR\',\'Drenthe\',\'\')';
654 $this->assertEquals($expected, $this->cleanSql($result));
655 }
656
657 /**
658 * @test
659 */
660 public function canParseInsertWithSpaceAfterValues()
661 {
662 $parseString = 'INSERT INTO static_country_zones VALUES (\'483\', \'0\', \'NL\', \'NLD\', \'528\', \'DR\', \'Drenthe\', \'\');';
663 $components = $this->subject->_callRef('parseINSERT', $parseString);
664 $this->assertInternalType('array', $components);
665
666 $result = $this->subject->compileSQL($components);
667 $expected = 'INSERT INTO static_country_zones VALUES (\'483\',\'0\',\'NL\',\'NLD\',\'528\',\'DR\',\'Drenthe\',\'\')';
668 $this->assertEquals($expected, $this->cleanSql($result));
669 }
670
671 /**
672 * @test
673 */
674 public function canParseInsertWithFields()
675 {
676 $parseString = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
677 $parseString .= 'VALUES (\'1\', \'0\', \'2\', \'0\', \'Africa\');';
678 $components = $this->subject->_callRef('parseINSERT', $parseString);
679 $this->assertInternalType('array', $components);
680
681 $result = $this->subject->compileSQL($components);
682 $expected = 'INSERT INTO static_territories (uid,pid,tr_iso_nr,tr_parent_iso_nr,tr_name_en) ';
683 $expected .= 'VALUES (\'1\',\'0\',\'2\',\'0\',\'Africa\')';
684 $this->assertEquals($expected, $this->cleanSql($result));
685 }
686
687 /**
688 * @test
689 */
690 public function canParseExtendedInsert()
691 {
692 $parseString = 'INSERT INTO static_territories VALUES (\'1\', \'0\', \'2\', \'0\', \'Africa\'),(\'2\', \'0\', \'9\', \'0\', \'Oceania\'),' . '(\'3\', \'0\', \'19\', \'0\', \'Americas\'),(\'4\', \'0\', \'142\', \'0\', \'Asia\');';
693 $components = $this->subject->_callRef('parseINSERT', $parseString);
694 $this->assertInternalType('array', $components);
695
696 $result = $this->subject->compileSQL($components);
697 $expected = 'INSERT INTO static_territories VALUES (\'1\',\'0\',\'2\',\'0\',\'Africa\'),(\'2\',\'0\',\'9\',\'0\',\'Oceania\'),(\'3\',\'0\',\'19\',\'0\',\'Americas\'),(\'4\',\'0\',\'142\',\'0\',\'Asia\')';
698 $this->assertEquals($expected, $this->cleanSql($result));
699 }
700
701 /**
702 * @test
703 */
704 public function canParseExtendedInsertWithFields()
705 {
706 $parseString = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
707 $parseString .= 'VALUES (\'1\', \'0\', \'2\', \'0\', \'Africa\'),(\'2\', \'0\', \'9\', \'0\', \'Oceania\');';
708 $components = $this->subject->_callRef('parseINSERT', $parseString);
709 $this->assertInternalType('array', $components);
710
711 $result = $this->subject->compileSQL($components);
712 $expected = 'INSERT INTO static_territories (uid,pid,tr_iso_nr,tr_parent_iso_nr,tr_name_en) ';
713 $expected .= 'VALUES (\'1\',\'0\',\'2\',\'0\',\'Africa\'),(\'2\',\'0\',\'9\',\'0\',\'Oceania\')';
714 $this->assertEquals($expected, $this->cleanSql($result));
715 }
716
717 /**
718 * @test
719 * @see http://forge.typo3.org/issues/17552
720 */
721 public function canParseIfNullOperator()
722 {
723 $parseString = 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)';
724 $result = $this->subject->parseWhereClause($parseString);
725 $this->assertInternalType('array', $result);
726 $this->assertEmpty($parseString);
727 }
728
729 /**
730 * @test
731 * @see http://forge.typo3.org/issues/17552
732 */
733 public function canParseIfNullOperatorWithAdditionalClauses()
734 {
735 $parseString = '1=1 AND IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22) AND tt_news.sys_language_uid IN (0,-1) ';
736 $parseString .= 'AND tt_news.pid > 0 AND tt_news.pid IN (61) AND tt_news.deleted=0 AND tt_news.t3ver_state<=0 ';
737 $parseString .= 'AND tt_news.hidden=0 AND tt_news.starttime<=1266065460 AND (tt_news.endtime=0 OR tt_news.endtime>1266065460) ';
738 $parseString .= 'AND (tt_news.fe_group=\'\' OR tt_news.fe_group IS NULL OR tt_news.fe_group=\'0\' ';
739 $parseString .= 'OR (tt_news.fe_group LIKE \'%,0,%\' OR tt_news.fe_group LIKE \'0,%\' OR tt_news.fe_group LIKE \'%,0\' ';
740 $parseString .= 'OR tt_news.fe_group=\'0\') OR (tt_news.fe_group LIKE \'%,-1,%\' OR tt_news.fe_group LIKE \'-1,%\' ';
741 $parseString .= 'OR tt_news.fe_group LIKE \'%,-1\' OR tt_news.fe_group=\'-1\'))';
742
743 $result = $this->subject->parseWhereClause($parseString);
744 $this->assertInternalType('array', $result);
745 $this->assertEmpty($parseString);
746 }
747
748 /**
749 * @test
750 * @see http://forge.typo3.org/issues/17552
751 */
752 public function canCompileIfNullOperator()
753 {
754 $parseString = 'SELECT * FROM tx_irfaq_q_cat_mm WHERE IFNULL(tx_irfaq_q_cat_mm.uid_foreign,0) = 1';
755 $components = $this->subject->_callRef('parseSELECT', $parseString);
756 $this->assertInternalType('array', $components);
757
758 $result = $this->subject->compileSQL($components);
759 $expected = 'SELECT * FROM tx_irfaq_q_cat_mm WHERE IFNULL(tx_irfaq_q_cat_mm.uid_foreign, 0) = 1';
760 $this->assertEquals($expected, $this->cleanSql($result));
761 }
762
763 /**
764 * @test
765 * @see http://forge.typo3.org/issues/67155
766 */
767 public function canParseCastOperator()
768 {
769 $parseString = 'CAST(parent AS CHAR) != \'\'';
770 $result = $this->subject->parseWhereClause($parseString);
771 $this->assertInternalType('array', $result);
772 $this->assertEmpty($parseString);
773 }
774
775 /**
776 * @test
777 * @see http://forge.typo3.org/issues/67155
778 */
779 public function canCompileCastOperator()
780 {
781 $parseString = 'SELECT * FROM sys_category WHERE CAST(parent AS CHAR) != \'\'';
782 $components = $this->subject->_callRef('parseSELECT', $parseString);
783 $this->assertInternalType('array', $components);
784
785 $result = $this->subject->compileSQL($components);
786 $expected = 'SELECT * FROM sys_category WHERE CAST(parent AS CHAR) != \'\'';
787 $this->assertEquals($expected, $this->cleanSql($result));
788 }
789
790 /**
791 * @test
792 * @see http://forge.typo3.org/issues/22695
793 */
794 public function canParseAlterEngineStatement()
795 {
796 $parseString = 'ALTER TABLE tx_realurl_pathcache ENGINE=InnoDB';
797 $components = $this->subject->_callRef('parseALTERTABLE', $parseString);
798 $this->assertInternalType('array', $components);
799
800 $result = $this->subject->compileSQL($components);
801 $expected = 'ALTER TABLE tx_realurl_pathcache ENGINE = InnoDB';
802 $this->assertEquals($expected, $this->cleanSql($result));
803 }
804
805 /**
806 * @test
807 * @see http://forge.typo3.org/issues/22731
808 */
809 public function canParseAlterCharacterSetStatement()
810 {
811 $parseString = 'ALTER TABLE `index_phash` DEFAULT CHARACTER SET utf8';
812 $components = $this->subject->_callRef('parseALTERTABLE', $parseString);
813 $this->assertInternalType('array', $components);
814
815 $result = $this->subject->compileSQL($components);
816 $expected = 'ALTER TABLE index_phash DEFAULT CHARACTER SET utf8';
817 $this->assertEquals($expected, $this->cleanSql($result));
818 }
819
820 /**
821 * @test
822 * @see http://forge.typo3.org/issues/67445
823 */
824 public function canParseAlterTableAddKeyStatement()
825 {
826 $parseString = 'ALTER TABLE sys_collection ADD KEY parent (pid,deleted)';
827 $components = $this->subject->_callRef('parseALTERTABLE', $parseString);
828 $this->assertInternalType('array', $components);
829
830 $result = $this->subject->compileSQL($components);
831 $expected = 'ALTER TABLE sys_collection ADD KEY parent (pid,deleted)';
832 $this->assertSame($expected, $this->cleanSql($result));
833 }
834
835 /**
836 * @test
837 * @see http://forge.typo3.org/issues/67445
838 */
839 public function canParseAlterTableDropKeyStatement()
840 {
841 $parseString = 'ALTER TABLE sys_collection DROP KEY parent';
842 $components = $this->subject->_callRef('parseALTERTABLE', $parseString);
843 $this->assertInternalType('array', $components);
844
845 $result = $this->subject->compileSQL($components);
846 $expected = 'ALTER TABLE sys_collection DROP KEY parent';
847 $this->assertSame($expected, $this->cleanSql($result));
848 }
849
850 /**
851 * @test
852 * @see http://forge.typo3.org/issues/23087
853 */
854 public function canParseFindInSetStatement()
855 {
856 $parseString = 'SELECT * FROM fe_users WHERE FIND_IN_SET(10, usergroup)';
857 $components = $this->subject->_callRef('parseSELECT', $parseString);
858 $this->assertInternalType('array', $components);
859
860 $result = $this->subject->compileSQL($components);
861 $expected = 'SELECT * FROM fe_users WHERE FIND_IN_SET(10, usergroup)';
862 $this->assertEquals($expected, $this->cleanSql($result));
863 }
864
865 /**
866 * @test
867 * @see http://forge.typo3.org/issues/27858
868 */
869 public function canParseSingleQuote()
870 {
871 $parseString = 'SELECT * FROM pages WHERE title=\'1\\\'\' AND deleted=0';
872 $result = $this->subject->_callRef('parseSELECT', $parseString);
873 $this->assertInternalType('array', $result);
874 $this->assertEmpty($result['parseString']);
875 }
876
877 ///////////////////////////////////////
878 // Tests concerning JOINs
879 ///////////////////////////////////////
880 /**
881 * @test
882 */
883 public function parseFromTablesWithInnerJoinReturnsArray()
884 {
885 $parseString = 'be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
886
887 $result = $this->subject->parseFromTables($parseString);
888 $this->assertInternalType('array', $result);
889 $this->assertEmpty($parseString);
890 }
891
892 /**
893 * @test
894 */
895 public function parseFromTablesWithLeftOuterJoinReturnsArray()
896 {
897 $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id';
898
899 $result = $this->subject->parseFromTables($parseString);
900 $this->assertInternalType('array', $result);
901 $this->assertEmpty($parseString);
902 }
903
904 /**
905 * @test
906 * @see http://forge.typo3.org/issues/21555
907 */
908 public function parseFromTablesWithRightOuterJoinReturnsArray()
909 {
910 $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid';
911
912 $result = $this->subject->parseFromTables($parseString);
913 $this->assertInternalType('array', $result);
914 $this->assertEmpty($parseString);
915 }
916
917 /**
918 * @test
919 */
920 public function parseFromTablesWithMultipleJoinsReturnsArray()
921 {
922 $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id INNER JOIN cache_pages cp ON cp.page_id = pages.uid';
923 $result = $this->subject->parseFromTables($parseString);
924 $this->assertInternalType('array', $result);
925 $this->assertEmpty($parseString);
926 }
927
928 /**
929 * @test
930 * @see http://forge.typo3.org/issues/21555
931 */
932 public function parseFromTablesWithMultipleJoinsAndParenthesesReturnsArray()
933 {
934 $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid LEFT JOIN tx_powermail_fields ON tx_powermail_fieldsets.uid = tx_powermail_fields.fieldset';
935 $result = $this->subject->parseFromTables($parseString);
936 $this->assertInternalType('array', $result);
937 $this->assertEmpty($parseString);
938 }
939
940 /**
941 * @test
942 */
943 public function canUseInnerJoinInSelect()
944 {
945 $sql = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
946
947 $result = $this->subject->debug_testSQL($sql);
948 $expected = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id=be_users.uid';
949 $this->assertEquals($expected, $this->cleanSql($result));
950 }
951
952 /**
953 * @test
954 */
955 public function canUseMultipleInnerJoinsInSelect()
956 {
957 $sql = 'SELECT * FROM tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid = tt_news_cat_mm.uid_local';
958
959 $result = $this->subject->debug_testSQL($sql);
960 $expected = 'SELECT * FROM tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid=tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid=tt_news_cat_mm.uid_local';
961 $this->assertEquals($expected, $this->cleanSql($result));
962 }
963
964 /**
965 * @test
966 * @see http://forge.typo3.org/issues/22501
967 */
968 public function canParseMultipleJoinConditions()
969 {
970 $sql = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.pid = T1.uid AND T2.size = 4 WHERE T1.cr_userid = 1';
971
972 $result = $this->subject->debug_testSQL($sql);
973 $expected = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.pid=T1.uid AND T2.size=4 WHERE T1.cr_userid = 1';
974 $this->assertEquals($expected, $this->cleanSql($result));
975 }
976
977 /**
978 * @test
979 * @see http://forge.typo3.org/issues/67385
980 */
981 public function canParseMultiJoinConditionsWithStrings()
982 {
983 $sql = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key = sys_file_processedfile.uid AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
984
985 $result = $this->subject->debug_testSQL($sql);
986 $expected = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key=sys_file_processedfile.uid AND entry_namespace=\'ProcessedFileChecksumUpdate\'';
987 $this->assertEquals($expected, $this->cleanSql($result));
988 }
989
990 /**
991 * @test
992 * @see http://forge.typo3.org/issues/67708
993 */
994 public function canParseMultiJoinConditionsWithStringsAndLeftCast()
995 {
996 $sql = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON CAST(entry_key AS INTEGER) = sys_file_processedfile.uid AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
997
998 $result = $this->subject->debug_testSQL($sql);
999 $expected = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON CAST(entry_key AS INTEGER)=sys_file_processedfile.uid AND entry_namespace=\'ProcessedFileChecksumUpdate\'';
1000 $this->assertEquals($expected, $this->cleanSql($result));
1001 }
1002
1003 /**
1004 * @test
1005 * @see http://forge.typo3.org/issues/67708
1006 */
1007 public function canParseMultiJoinConditionsWithStringsAndRightCast()
1008 {
1009 $sql = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key = CAST(sys_file_processedfile.uid AS CHAR) AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
1010
1011 $result = $this->subject->debug_testSQL($sql);
1012 $expected = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key=CAST(sys_file_processedfile.uid AS CHAR) AND entry_namespace=\'ProcessedFileChecksumUpdate\'';
1013 $this->assertEquals($expected, $this->cleanSql($result));
1014 }
1015
1016 /**
1017 * @test
1018 * @see http://forge.typo3.org/issues/22501
1019 */
1020 public function canParseMultipleJoinConditionsWithLessThanOperator()
1021 {
1022 $sql = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size < 4 OR T2.pid = T1.uid WHERE T1.cr_userid = 1';
1023
1024 $result = $this->subject->debug_testSQL($sql);
1025 $expected = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size<4 OR T2.pid=T1.uid WHERE T1.cr_userid = 1';
1026 $this->assertEquals($expected, $this->cleanSql($result));
1027 }
1028
1029 ///////////////////////////////////////
1030 // Tests concerning DB management
1031 ///////////////////////////////////////
1032 /**
1033 * @test
1034 * @see http://forge.typo3.org/issues/16689
1035 */
1036 public function indexMayContainALengthRestrictionInCreateTable()
1037 {
1038 $parseString = '
1039 CREATE TABLE tx_realurl_uniqalias (
1040 uid int(11) NOT NULL auto_increment,
1041 tstamp int(11) DEFAULT \'0\' NOT NULL,
1042 tablename varchar(60) DEFAULT \'\' NOT NULL,
1043 field_alias varchar(255) DEFAULT \'\' NOT NULL,
1044 field_id varchar(60) DEFAULT \'\' NOT NULL,
1045 value_alias varchar(255) DEFAULT \'\' NOT NULL,
1046 value_id int(11) DEFAULT \'0\' NOT NULL,
1047 lang int(11) DEFAULT \'0\' NOT NULL,
1048 expire int(11) DEFAULT \'0\' NOT NULL,
1049
1050 PRIMARY KEY (uid),
1051 KEY tablename (tablename),
1052 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
1053 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
1054 );
1055 ';
1056 $result = $this->subject->_callRef('parseCREATETABLE', $parseString);
1057 $this->assertInternalType('array', $result);
1058 }
1059
1060 /**
1061 * @test
1062 * @see http://forge.typo3.org/issues/66929
1063 */
1064 public function createTableSupportsDateAndTimeTypes()
1065 {
1066 $parseString = 'CREATE TABLE fe_users (' .
1067 'testdate date DEFAULT \'0000-00-00\',' .
1068 'testdatetime datetime DEFAULT \'0000-00-00 00:00:00\',' .
1069 'testtimestamp timestamp DEFAULT \'0000-00-00 00:00:00\',' .
1070 'testtime time DEFAULT \'00:00:00\',' .
1071 'testyear year DEFAULT \'0000\')';
1072
1073 $components = $this->subject->_callRef('parseCREATETABLE', $parseString);
1074 $actual = $this->subject->compileSQL($components);
1075 $this->assertEquals($this->cleanSql($parseString), $actual);
1076 }
1077
1078 /**
1079 * @test
1080 * @see http://forge.typo3.org/issues/21730
1081 */
1082 public function indexMayContainALengthRestrictionInAlterTable()
1083 {
1084 $parseString = 'ALTER TABLE tx_realurl_uniqalias ADD KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)';
1085 $result = $this->subject->_callRef('parseALTERTABLE', $parseString);
1086 $this->assertInternalType('array', $result);
1087 }
1088
1089 /**
1090 * @test
1091 * @see http://forge.typo3.org/issues/15366
1092 */
1093 public function canParseUniqueIndexCreation()
1094 {
1095 $sql = 'ALTER TABLE static_territories ADD UNIQUE uid (uid)';
1096 $expected = $sql;
1097 $alterTables = $this->subject->_callRef('parseALTERTABLE', $sql);
1098 $queries = $this->subject->compileSQL($alterTables);
1099 $this->assertEquals($expected, $queries);
1100 }
1101
1102 ///////////////////////////////////////
1103 // Tests concerning subqueries
1104 ///////////////////////////////////////
1105 /**
1106 * @test
1107 * @see http://forge.typo3.org/issues/21688
1108 */
1109 public function inWhereClauseSupportsSubquery()
1110 {
1111 $parseString = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
1112 $result = $this->subject->parseWhereClause($parseString);
1113 $this->assertInternalType('array', $result);
1114 $this->assertEmpty($parseString);
1115 }
1116
1117 /**
1118 * @test
1119 * @see http://forge.typo3.org/issues/21688
1120 */
1121 public function inWhereClauseWithSubqueryIsProperlyCompiled()
1122 {
1123 $sql = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
1124
1125 $result = $this->subject->debug_testSQL($sql);
1126 $expected = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active = 0 AND deleted = 0)';
1127 $this->assertEquals($expected, $this->cleanSql($result));
1128 }
1129
1130 /**
1131 * @test
1132 * @see http://forge.typo3.org/issues/21688
1133 */
1134 public function whereClauseSupportsExistsKeyword()
1135 {
1136 $parseString = 'EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
1137 $result = $this->subject->parseWhereClause($parseString);
1138 $this->assertInternalType('array', $result);
1139 $this->assertEmpty($parseString);
1140 }
1141
1142 /**
1143 * @test
1144 * @see http://forge.typo3.org/issues/21688
1145 */
1146 public function existsClauseIsProperlyCompiled()
1147 {
1148 $sql = 'SELECT * FROM tx_crawler_process WHERE active = 0 AND NOT EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
1149
1150 $result = $this->subject->debug_testSQL($sql);
1151 $expected = 'SELECT * FROM tx_crawler_process WHERE active = 0 AND NOT EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
1152 $this->assertEquals($expected, $this->cleanSql($result));
1153 }
1154
1155 ///////////////////////////////////////
1156 // Tests concerning advanced operators
1157 ///////////////////////////////////////
1158 /**
1159 * @test
1160 * @see http://forge.typo3.org/issues/21903
1161 */
1162 public function caseWithBooleanConditionIsSupportedInFields()
1163 {
1164 $parseString = 'CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column';
1165 $result = $this->subject->parseFieldList($parseString);
1166 $this->assertInternalType('array', $result);
1167 $this->assertEmpty($parseString);
1168 }
1169
1170 /**
1171 * @test
1172 * @see http://forge.typo3.org/issues/21903
1173 */
1174 public function caseWithBooleanConditionIsProperlyCompiled()
1175 {
1176 $sql = 'SELECT CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
1177
1178 $result = $this->subject->debug_testSQL($sql);
1179 $expected = 'SELECT CASE WHEN 1 > 0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
1180 $this->assertEquals($expected, $this->cleanSql($result));
1181 }
1182
1183 /**
1184 * @test
1185 * @see http://forge.typo3.org/issues/21903
1186 */
1187 public function caseWithMultipleWhenIsSupportedInFields()
1188 {
1189 $parseString = 'CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number';
1190 $result = $this->subject->parseFieldList($parseString);
1191 $this->assertInternalType('array', $result);
1192 $this->assertEmpty($parseString);
1193 }
1194
1195 /**
1196 * @test
1197 * @see http://forge.typo3.org/issues/21903
1198 */
1199 public function caseWithMultipleWhenIsProperlyCompiled()
1200 {
1201 $sql = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
1202
1203 $result = $this->subject->debug_testSQL($sql);
1204 $expected = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
1205 $this->assertEquals($expected, $this->cleanSql($result));
1206 }
1207
1208 /**
1209 * @test
1210 * @see http://forge.typo3.org/issues/21902
1211 */
1212 public function locateIsSupported()
1213 {
1214 $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure)>0';
1215
1216 $result = $this->subject->debug_testSQL($sql);
1217 $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure) > 0';
1218 $this->assertEquals($expected, $this->cleanSql($result));
1219 }
1220
1221 /**
1222 * @test
1223 * @see http://forge.typo3.org/issues/21902
1224 */
1225 public function locateWithPositionIsSupported()
1226 {
1227 $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\' , datastructure ,10)>0';
1228 $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure, 10) > 0';
1229 $result = $this->cleanSql($this->subject->debug_testSQL($sql));
1230 $this->assertEquals($expected, $result);
1231 }
1232
1233 /**
1234 * @test
1235 * @see http://forge.typo3.org/issues/21902
1236 * @see http://forge.typo3.org/issues/21903
1237 */
1238 public function locateWithinCaseIsSupported()
1239 {
1240 $sql = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure)>0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
1241
1242 $result = $this->subject->debug_testSQL($sql);
1243 $expected = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure) > 0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
1244 $this->assertEquals($expected, $this->cleanSql($result));
1245 }
1246
1247 ///////////////////////////////////////
1248 // Tests concerning prepared queries
1249 ///////////////////////////////////////
1250 /**
1251 * @test
1252 * @see http://forge.typo3.org/issues/23374
1253 */
1254 public function namedPlaceholderIsSupported()
1255 {
1256 $sql = 'SELECT * FROM pages WHERE pid = :pid ORDER BY title';
1257
1258 $result = $this->subject->debug_testSQL($sql);
1259 $expected = 'SELECT * FROM pages WHERE pid = :pid ORDER BY title';
1260 $this->assertEquals($expected, $this->cleanSql($result));
1261 }
1262
1263 /**
1264 * @test
1265 * @see http://forge.typo3.org/issues/23374
1266 */
1267 public function questionMarkPlaceholderIsSupported()
1268 {
1269 $sql = 'SELECT * FROM pages WHERE pid = ? ORDER BY title';
1270
1271 $result = $this->subject->debug_testSQL($sql);
1272 $expected = 'SELECT * FROM pages WHERE pid = ? ORDER BY title';
1273 $this->assertEquals($expected, $this->cleanSql($result));
1274 }
1275
1276 /**
1277 * @test
1278 * @see http://forge.typo3.org/issues/23374
1279 */
1280 public function parametersAreReferenced()
1281 {
1282 $sql = 'SELECT * FROM pages WHERE pid = :pid1 OR pid = :pid2';
1283 $components = $this->subject->_callRef('parseSELECT', $sql);
1284 $this->assertInternalType('array', $components['parameters']);
1285 $this->assertEquals(2, count($components['parameters']));
1286 $this->assertTrue(isset($components['parameters'][':pid1']));
1287 $this->assertTrue(isset($components['parameters'][':pid2']));
1288 }
1289
1290 /**
1291 * @test
1292 * @see http://forge.typo3.org/issues/23374
1293 */
1294 public function sameParameterIsReferencedInSubquery()
1295 {
1296 $sql = 'SELECT * FROM pages WHERE uid = :pageId OR uid IN (SELECT uid FROM pages WHERE pid = :pageId)';
1297 $pageId = 12;
1298 $components = $this->subject->_callRef('parseSELECT', $sql);
1299 $components['parameters'][':pageId'][0] = $pageId;
1300
1301 $result = $this->subject->compileSQL($components);
1302 $expected = 'SELECT * FROM pages WHERE uid = 12 OR uid IN (SELECT uid FROM pages WHERE pid = 12)';
1303 $this->assertEquals($expected, $this->cleanSql($result));
1304 }
1305
1306 /**
1307 * @test
1308 * @see http://forge.typo3.org/issues/23374
1309 */
1310 public function namedParametersMayBeSafelyReplaced()
1311 {
1312 $sql = 'SELECT * FROM pages WHERE pid = :pid AND title NOT LIKE \':pid\'';
1313 $pid = 12;
1314 $components = $this->subject->_callRef('parseSELECT', $sql);
1315 $components['parameters'][':pid'][0] = $pid;
1316
1317 $result = $this->subject->compileSQL($components);
1318 $expected = 'SELECT * FROM pages WHERE pid = ' . $pid . ' AND title NOT LIKE \':pid\'';
1319 $this->assertEquals($expected, $this->cleanSql($result));
1320 }
1321
1322 /**
1323 * @test
1324 * @see http://forge.typo3.org/issues/23374
1325 */
1326 public function questionMarkParametersMayBeSafelyReplaced()
1327 {
1328 $sql = 'SELECT * FROM pages WHERE pid = ? AND timestamp < ? AND title != \'How to test?\'';
1329 $parameterValues = array(12, 1281782690);
1330 $components = $this->subject->_callRef('parseSELECT', $sql);
1331 $questionMarkParamCount = count($components['parameters']['?']);
1332 for ($i = 0; $i < $questionMarkParamCount; $i++) {
1333 $components['parameters']['?'][$i][0] = $parameterValues[$i];
1334 }
1335
1336 $result = $this->subject->compileSQL($components);
1337 $expected = 'SELECT * FROM pages WHERE pid = 12 AND timestamp < 1281782690 AND title != \'How to test?\'';
1338 $this->assertEquals($expected, $this->cleanSql($result));
1339 }
1340 }