[BUGFIX] SQL parser does not support string as join condition
[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 /**
18 * Test case
19 */
20 class SqlParserTest extends AbstractTestCase {
21
22 /**
23 * @var \TYPO3\CMS\Dbal\Database\SqlParser|\PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface
24 */
25 protected $subject;
26
27 /**
28 * Prepares the environment before running a test.
29 */
30 protected function setUp() {
31 $subject = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\SqlParser::class, array('dummy'), array(), '', FALSE);
32
33 $mockDatabaseConnection = $this->getMock(\TYPO3\CMS\Dbal\Database\DatabaseConnection::class, array(), array(), '', FALSE);
34 $mockDatabaseConnection->lastHandlerKey = '_DEFAULT';
35 $subject->_set('databaseConnection', $mockDatabaseConnection);
36
37 $this->subject = $subject;
38 }
39
40 /**
41 * @test
42 */
43 public function canExtractPartsOfAQuery() {
44 $parseString = 'SELECT *' . LF . 'FROM pages WHERE pid IN (1,2,3,4)';
45 $regex = '^SELECT[[:space:]]+(.*)[[:space:]]+';
46 $trimAll = TRUE;
47 $fields = $this->subject->_callRef('nextPart', $parseString, $regex, $trimAll);
48 $this->assertEquals('*', $fields);
49 $this->assertEquals('FROM pages WHERE pid IN (1,2,3,4)', $parseString);
50 $regex = '^FROM ([^)]+) WHERE';
51 $table = $this->subject->_callRef('nextPart', $parseString, $regex);
52 $this->assertEquals('pages', $table);
53 $this->assertEquals('pages WHERE pid IN (1,2,3,4)', $parseString);
54 }
55
56 /**
57 * @test
58 */
59 public function canGetIntegerValue() {
60 $parseString = '1024';
61 $result = $this->subject->_callRef('getValue', $parseString);
62 $expected = array(1024);
63 $this->assertEquals($expected, $result);
64 }
65
66 /**
67 * @test
68 * @see http://forge.typo3.org/issues/21887
69 */
70 public function canGetStringValue() {
71 $parseString = '"some owner\\\'s string"';
72 $result = $this->subject->_callRef('getValue', $parseString);
73 $expected = array('some owner\'s string', '"');
74 $this->assertEquals($expected, $result);
75 }
76
77 /**
78 * @test
79 * @see http://forge.typo3.org/issues/21887
80 */
81 public function canGetStringValueWithSingleQuote() {
82 $parseString = '\'some owner\\\'s string\'';
83 $result = $this->subject->_callRef('getValue', $parseString);
84 $expected = array('some owner\'s string', '\'');
85 $this->assertEquals($expected, $result);
86 }
87
88 /**
89 * @test
90 * @see http://forge.typo3.org/issues/21887
91 */
92 public function canGetStringValueWithDoubleQuote() {
93 $parseString = '"the \\"owner\\" is here"';
94 $result = $this->subject->_callRef('getValue', $parseString);
95 $expected = array('the "owner" is here', '"');
96 $this->assertEquals($expected, $result);
97 }
98
99 /**
100 * @test
101 */
102 public function canGetListOfValues() {
103 $parseString = '( 1, 2, 3 ,4)';
104 $operator = 'IN';
105 $result = $this->subject->_callRef('getValue', $parseString, $operator);
106 $expected = array(
107 array(1),
108 array(2),
109 array(3),
110 array(4)
111 );
112 $this->assertEquals($expected, $result);
113 }
114
115 /**
116 * @test
117 */
118 public function parseWhereClauseReturnsArray() {
119 $parseString = 'uid IN (1,2) AND (starttime < ' . time() . ' OR cruser_id + 10 < 20)';
120 $result = $this->subject->parseWhereClause($parseString);
121 $this->assertInternalType('array', $result);
122 $this->assertEmpty($parseString);
123 }
124
125 /**
126 * @test
127 */
128 public function canSelectAllFieldsFromPages() {
129 $sql = 'SELECT * FROM pages';
130 $expected = $sql;
131 $result = $this->subject->debug_testSQL($sql);
132 $this->assertEquals($expected, $this->cleanSql($result));
133 }
134
135 /**
136 * @test
137 */
138 public function canParseTruncateTable() {
139 $sql = 'TRUNCATE TABLE be_users';
140 $expected = $sql;
141 $result = $this->subject->debug_testSQL($sql);
142 $this->assertEquals($expected, $this->cleanSql($result));
143 }
144
145 /**
146 * @test
147 * @see http://forge.typo3.org/issues/22049
148 */
149 public function canParseAndCompileBetweenOperator() {
150 $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\'';
151 $result = $this->subject->parseWhereClause($parseString);
152 $this->assertInternalType('array', $result);
153 $this->assertEmpty($parseString);
154
155 $result = $this->subject->compileWhereClause($result);
156 $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\'';
157 $this->assertEquals($expected, $this->cleanSql($result));
158 }
159
160 /**
161 * @test
162 */
163 public function canParseInsertWithoutSpaceAfterValues() {
164 $parseString = 'INSERT INTO static_country_zones VALUES(\'483\', \'0\', \'NL\', \'NLD\', \'528\', \'DR\', \'Drenthe\', \'\');';
165 $components = $this->subject->_callRef('parseINSERT', $parseString);
166 $this->assertInternalType('array', $components);
167
168 $result = $this->subject->_callRef('compileINSERT', $components);
169 $expected = 'INSERT INTO static_country_zones VALUES (\'483\',\'0\',\'NL\',\'NLD\',\'528\',\'DR\',\'Drenthe\',\'\')';
170 $this->assertEquals($expected, $this->cleanSql($result));
171 }
172
173 /**
174 * @test
175 */
176 public function canParseInsertWithSpaceAfterValues() {
177 $parseString = 'INSERT INTO static_country_zones VALUES (\'483\', \'0\', \'NL\', \'NLD\', \'528\', \'DR\', \'Drenthe\', \'\');';
178 $components = $this->subject->_callRef('parseINSERT', $parseString);
179 $this->assertInternalType('array', $components);
180
181 $result = $this->subject->_callRef('compileINSERT', $components);
182 $expected = 'INSERT INTO static_country_zones VALUES (\'483\',\'0\',\'NL\',\'NLD\',\'528\',\'DR\',\'Drenthe\',\'\')';
183 $this->assertEquals($expected, $this->cleanSql($result));
184 }
185
186 /**
187 * @test
188 */
189 public function canParseInsertWithFields() {
190 $parseString = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
191 $parseString .= 'VALUES (\'1\', \'0\', \'2\', \'0\', \'Africa\');';
192 $components = $this->subject->_callRef('parseINSERT', $parseString);
193 $this->assertInternalType('array', $components);
194
195 $result = $this->subject->_callRef('compileINSERT', $components);
196 $expected = 'INSERT INTO static_territories (uid,pid,tr_iso_nr,tr_parent_iso_nr,tr_name_en) ';
197 $expected .= 'VALUES (\'1\',\'0\',\'2\',\'0\',\'Africa\')';
198 $this->assertEquals($expected, $this->cleanSql($result));
199 }
200
201 /**
202 * @test
203 */
204 public function canParseExtendedInsert() {
205 $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\');';
206 $components = $this->subject->_callRef('parseINSERT', $parseString);
207 $this->assertInternalType('array', $components);
208
209 $result = $this->subject->_callRef('compileINSERT', $components);
210 $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\')';
211 $this->assertEquals($expected, $this->cleanSql($result));
212 }
213
214 /**
215 * @test
216 */
217 public function canParseExtendedInsertWithFields() {
218 $parseString = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
219 $parseString .= 'VALUES (\'1\', \'0\', \'2\', \'0\', \'Africa\'),(\'2\', \'0\', \'9\', \'0\', \'Oceania\');';
220 $components = $this->subject->_callRef('parseINSERT', $parseString);
221 $this->assertInternalType('array', $components);
222
223 $result = $this->subject->_callRef('compileINSERT', $components);
224 $expected = 'INSERT INTO static_territories (uid,pid,tr_iso_nr,tr_parent_iso_nr,tr_name_en) ';
225 $expected .= 'VALUES (\'1\',\'0\',\'2\',\'0\',\'Africa\'),(\'2\',\'0\',\'9\',\'0\',\'Oceania\')';
226 $this->assertEquals($expected, $this->cleanSql($result));
227 }
228
229 /**
230 * @test
231 * @see http://forge.typo3.org/issues/17552
232 */
233 public function canParseIfNullOperator() {
234 $parseString = 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)';
235 $result = $this->subject->parseWhereClause($parseString);
236 $this->assertInternalType('array', $result);
237 $this->assertEmpty($parseString);
238 }
239
240 /**
241 * @test
242 * @see http://forge.typo3.org/issues/17552
243 */
244 public function canParseIfNullOperatorWithAdditionalClauses() {
245 $parseString = '1=1 AND IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22) AND tt_news.sys_language_uid IN (0,-1) ';
246 $parseString .= 'AND tt_news.pid > 0 AND tt_news.pid IN (61) AND tt_news.deleted=0 AND tt_news.t3ver_state<=0 ';
247 $parseString .= 'AND tt_news.hidden=0 AND tt_news.starttime<=1266065460 AND (tt_news.endtime=0 OR tt_news.endtime>1266065460) ';
248 $parseString .= 'AND (tt_news.fe_group=\'\' OR tt_news.fe_group IS NULL OR tt_news.fe_group=\'0\' ';
249 $parseString .= 'OR (tt_news.fe_group LIKE \'%,0,%\' OR tt_news.fe_group LIKE \'0,%\' OR tt_news.fe_group LIKE \'%,0\' ';
250 $parseString .= 'OR tt_news.fe_group=\'0\') OR (tt_news.fe_group LIKE \'%,-1,%\' OR tt_news.fe_group LIKE \'-1,%\' ';
251 $parseString .= 'OR tt_news.fe_group LIKE \'%,-1\' OR tt_news.fe_group=\'-1\'))';
252
253 $result = $this->subject->parseWhereClause($parseString);
254 $this->assertInternalType('array', $result);
255 $this->assertEmpty($parseString);
256 }
257
258 /**
259 * @test
260 * @see http://forge.typo3.org/issues/17552
261 */
262 public function canCompileIfNullOperator() {
263 $parseString = 'SELECT * FROM tx_irfaq_q_cat_mm WHERE IFNULL(tx_irfaq_q_cat_mm.uid_foreign,0) = 1';
264 $components = $this->subject->_callRef('parseSELECT', $parseString);
265 $this->assertInternalType('array', $components);
266
267 $result = $this->subject->_callRef('compileSELECT', $components);
268 $expected = 'SELECT * FROM tx_irfaq_q_cat_mm WHERE IFNULL(tx_irfaq_q_cat_mm.uid_foreign, 0) = 1';
269 $this->assertEquals($expected, $this->cleanSql($result));
270 }
271
272 /**
273 * @test
274 * @see http://forge.typo3.org/issues/22695
275 */
276 public function canParseAlterEngineStatement() {
277 $parseString = 'ALTER TABLE tx_realurl_pathcache ENGINE=InnoDB';
278 $components = $this->subject->_callRef('parseALTERTABLE', $parseString);
279 $this->assertInternalType('array', $components);
280
281 $result = $this->subject->_callRef('compileALTERTABLE', $components);
282 $expected = 'ALTER TABLE tx_realurl_pathcache ENGINE = InnoDB';
283 $this->assertEquals($expected, $this->cleanSql($result));
284 }
285
286 /**
287 * @test
288 * @see http://forge.typo3.org/issues/22731
289 */
290 public function canParseAlterCharacterSetStatement() {
291 $parseString = 'ALTER TABLE `index_phash` DEFAULT CHARACTER SET utf8';
292 $components = $this->subject->_callRef('parseALTERTABLE', $parseString);
293 $this->assertInternalType('array', $components);
294
295 $result = $this->subject->_callRef('compileALTERTABLE', $components);
296 $expected = 'ALTER TABLE index_phash DEFAULT CHARACTER SET utf8';
297 $this->assertEquals($expected, $this->cleanSql($result));
298 }
299
300 /**
301 * @test
302 * @see http://forge.typo3.org/issues/23087
303 */
304 public function canParseFindInSetStatement() {
305 $parseString = 'SELECT * FROM fe_users WHERE FIND_IN_SET(10, usergroup)';
306 $components = $this->subject->_callRef('parseSELECT', $parseString);
307 $this->assertInternalType('array', $components);
308
309 $result = $this->subject->_callRef('compileSELECT', $components);
310 $expected = 'SELECT * FROM fe_users WHERE FIND_IN_SET(10, usergroup)';
311 $this->assertEquals($expected, $this->cleanSql($result));
312 }
313
314 /**
315 * @test
316 * @see http://forge.typo3.org/issues/27858
317 */
318 public function canParseSingleQuote() {
319 $parseString = 'SELECT * FROM pages WHERE title=\'1\\\'\' AND deleted=0';
320 $result = $this->subject->_callRef('parseSELECT', $parseString);
321 $this->assertInternalType('array', $result);
322 $this->assertEmpty($result['parseString']);
323 }
324
325 ///////////////////////////////////////
326 // Tests concerning JOINs
327 ///////////////////////////////////////
328 /**
329 * @test
330 */
331 public function parseFromTablesWithInnerJoinReturnsArray() {
332 $parseString = 'be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
333
334 $result = $this->subject->parseFromTables($parseString);
335 $this->assertInternalType('array', $result);
336 $this->assertEmpty($parseString);
337 }
338
339 /**
340 * @test
341 */
342 public function parseFromTablesWithLeftOuterJoinReturnsArray() {
343 $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id';
344
345 $result = $this->subject->parseFromTables($parseString);
346 $this->assertInternalType('array', $result);
347 $this->assertEmpty($parseString);
348 }
349
350 /**
351 * @test
352 * @see http://forge.typo3.org/issues/21555
353 */
354 public function parseFromTablesWithRightOuterJoinReturnsArray() {
355 $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid';
356
357 $result = $this->subject->parseFromTables($parseString);
358 $this->assertInternalType('array', $result);
359 $this->assertEmpty($parseString);
360 }
361
362 /**
363 * @test
364 */
365 public function parseFromTablesWithMultipleJoinsReturnsArray() {
366 $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';
367 $result = $this->subject->parseFromTables($parseString);
368 $this->assertInternalType('array', $result);
369 $this->assertEmpty($parseString);
370 }
371
372 /**
373 * @test
374 * @see http://forge.typo3.org/issues/21555
375 */
376 public function parseFromTablesWithMultipleJoinsAndParenthesesReturnsArray() {
377 $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';
378 $result = $this->subject->parseFromTables($parseString);
379 $this->assertInternalType('array', $result);
380 $this->assertEmpty($parseString);
381 }
382
383 /**
384 * @test
385 */
386 public function canUseInnerJoinInSelect() {
387 $sql = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
388
389 $result = $this->subject->debug_testSQL($sql);
390 $expected = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id=be_users.uid';
391 $this->assertEquals($expected, $this->cleanSql($result));
392 }
393
394 /**
395 * @test
396 */
397 public function canUseMultipleInnerJoinsInSelect() {
398 $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';
399
400 $result = $this->subject->debug_testSQL($sql);
401 $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';
402 $this->assertEquals($expected, $this->cleanSql($result));
403 }
404
405 /**
406 * @test
407 * @see http://forge.typo3.org/issues/22501
408 */
409 public function canParseMultipleJoinConditions() {
410 $sql = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.pid = T1.uid AND T2.size = 4 WHERE T1.cr_userid = 1';
411
412 $result = $this->subject->debug_testSQL($sql);
413 $expected = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.pid=T1.uid AND T2.size=4 WHERE T1.cr_userid = 1';
414 $this->assertEquals($expected, $this->cleanSql($result));
415 }
416
417 /**
418 * @test
419 * @see http://forge.typo3.org/issues/67385
420 */
421 public function canParseMultiJoinConditionsWithStrings() {
422 $sql = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key = sys_file_processedfile.uid AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
423
424 $result = $this->subject->debug_testSQL($sql);
425 $expected = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key=sys_file_processedfile.uid AND entry_namespace=\'ProcessedFileChecksumUpdate\'';
426 $this->assertEquals($expected, $this->cleanSql($result));
427 }
428
429 /**
430 * @test
431 * @see http://forge.typo3.org/issues/22501
432 */
433 public function canParseMultipleJoinConditionsWithLessThanOperator() {
434 $sql = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size < 4 OR T2.pid = T1.uid WHERE T1.cr_userid = 1';
435
436 $result = $this->subject->debug_testSQL($sql);
437 $expected = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size<4 OR T2.pid=T1.uid WHERE T1.cr_userid = 1';
438 $this->assertEquals($expected, $this->cleanSql($result));
439 }
440
441 ///////////////////////////////////////
442 // Tests concerning DB management
443 ///////////////////////////////////////
444 /**
445 * @test
446 * @see http://forge.typo3.org/issues/16689
447 */
448 public function indexMayContainALengthRestrictionInCreateTable() {
449 $parseString = '
450 CREATE TABLE tx_realurl_uniqalias (
451 uid int(11) NOT NULL auto_increment,
452 tstamp int(11) DEFAULT \'0\' NOT NULL,
453 tablename varchar(60) DEFAULT \'\' NOT NULL,
454 field_alias varchar(255) DEFAULT \'\' NOT NULL,
455 field_id varchar(60) DEFAULT \'\' NOT NULL,
456 value_alias varchar(255) DEFAULT \'\' NOT NULL,
457 value_id int(11) DEFAULT \'0\' NOT NULL,
458 lang int(11) DEFAULT \'0\' NOT NULL,
459 expire int(11) DEFAULT \'0\' NOT NULL,
460
461 PRIMARY KEY (uid),
462 KEY tablename (tablename),
463 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
464 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
465 );
466 ';
467 $result = $this->subject->_callRef('parseCREATETABLE', $parseString);
468 $this->assertInternalType('array', $result);
469 }
470
471 /**
472 * @test
473 * @see http://forge.typo3.org/issues/21730
474 */
475 public function indexMayContainALengthRestrictionInAlterTable() {
476 $parseString = 'ALTER TABLE tx_realurl_uniqalias ADD KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)';
477 $result = $this->subject->_callRef('parseALTERTABLE', $parseString);
478 $this->assertInternalType('array', $result);
479 }
480
481 /**
482 * @test
483 * @see http://forge.typo3.org/issues/15366
484 */
485 public function canParseUniqueIndexCreation() {
486 $sql = 'ALTER TABLE static_territories ADD UNIQUE uid (uid)';
487 $expected = $sql;
488 $alterTables = $this->subject->_callRef('parseALTERTABLE', $sql);
489 $queries = $this->subject->compileSQL($alterTables);
490 $this->assertEquals($expected, $queries);
491 }
492
493 ///////////////////////////////////////
494 // Tests concerning subqueries
495 ///////////////////////////////////////
496 /**
497 * @test
498 * @see http://forge.typo3.org/issues/21688
499 */
500 public function inWhereClauseSupportsSubquery() {
501 $parseString = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
502 $result = $this->subject->parseWhereClause($parseString);
503 $this->assertInternalType('array', $result);
504 $this->assertEmpty($parseString);
505 }
506
507 /**
508 * @test
509 * @see http://forge.typo3.org/issues/21688
510 */
511 public function inWhereClauseWithSubqueryIsProperlyCompiled() {
512 $sql = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
513
514 $result = $this->subject->debug_testSQL($sql);
515 $expected = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active = 0 AND deleted = 0)';
516 $this->assertEquals($expected, $this->cleanSql($result));
517 }
518
519 /**
520 * @test
521 * @see http://forge.typo3.org/issues/21688
522 */
523 public function whereClauseSupportsExistsKeyword() {
524 $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)';
525 $result = $this->subject->parseWhereClause($parseString);
526 $this->assertInternalType('array', $result);
527 $this->assertEmpty($parseString);
528 }
529
530 /**
531 * @test
532 * @see http://forge.typo3.org/issues/21688
533 */
534 public function existsClauseIsProperlyCompiled() {
535 $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)';
536
537 $result = $this->subject->debug_testSQL($sql);
538 $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)';
539 $this->assertEquals($expected, $this->cleanSql($result));
540 }
541
542 ///////////////////////////////////////
543 // Tests concerning advanced operators
544 ///////////////////////////////////////
545 /**
546 * @test
547 * @see http://forge.typo3.org/issues/21903
548 */
549 public function caseWithBooleanConditionIsSupportedInFields() {
550 $parseString = 'CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column';
551 $result = $this->subject->parseFieldList($parseString);
552 $this->assertInternalType('array', $result);
553 $this->assertEmpty($parseString);
554 }
555
556 /**
557 * @test
558 * @see http://forge.typo3.org/issues/21903
559 */
560 public function caseWithBooleanConditionIsProperlyCompiled() {
561 $sql = 'SELECT CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
562
563 $result = $this->subject->debug_testSQL($sql);
564 $expected = 'SELECT CASE WHEN 1 > 0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
565 $this->assertEquals($expected, $this->cleanSql($result));
566 }
567
568 /**
569 * @test
570 * @see http://forge.typo3.org/issues/21903
571 */
572 public function caseWithMultipleWhenIsSupportedInFields() {
573 $parseString = 'CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number';
574 $result = $this->subject->parseFieldList($parseString);
575 $this->assertInternalType('array', $result);
576 $this->assertEmpty($parseString);
577 }
578
579 /**
580 * @test
581 * @see http://forge.typo3.org/issues/21903
582 */
583 public function caseWithMultipleWhenIsProperlyCompiled() {
584 $sql = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
585
586 $result = $this->subject->debug_testSQL($sql);
587 $expected = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
588 $this->assertEquals($expected, $this->cleanSql($result));
589 }
590
591 /**
592 * @test
593 * @see http://forge.typo3.org/issues/21902
594 */
595 public function locateIsSupported() {
596 $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure)>0';
597
598 $result = $this->subject->debug_testSQL($sql);
599 $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure) > 0';
600 $this->assertEquals($expected, $this->cleanSql($result));
601 }
602
603 /**
604 * @test
605 * @see http://forge.typo3.org/issues/21902
606 */
607 public function locateWithPositionIsSupported() {
608 $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\' , datastructure ,10)>0';
609 $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure, 10) > 0';
610 $result = $this->cleanSql($this->subject->debug_testSQL($sql));
611 $this->assertEquals($expected, $result);
612 }
613
614 /**
615 * @test
616 * @see http://forge.typo3.org/issues/21902
617 * @see http://forge.typo3.org/issues/21903
618 */
619 public function locateWithinCaseIsSupported() {
620 $sql = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure)>0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
621
622 $result = $this->subject->debug_testSQL($sql);
623 $expected = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure) > 0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
624 $this->assertEquals($expected, $this->cleanSql($result));
625 }
626
627 ///////////////////////////////////////
628 // Tests concerning prepared queries
629 ///////////////////////////////////////
630 /**
631 * @test
632 * @see http://forge.typo3.org/issues/23374
633 */
634 public function namedPlaceholderIsSupported() {
635 $sql = 'SELECT * FROM pages WHERE pid = :pid ORDER BY title';
636
637 $result = $this->subject->debug_testSQL($sql);
638 $expected = 'SELECT * FROM pages WHERE pid = :pid ORDER BY title';
639 $this->assertEquals($expected, $this->cleanSql($result));
640 }
641
642 /**
643 * @test
644 * @see http://forge.typo3.org/issues/23374
645 */
646 public function questionMarkPlaceholderIsSupported() {
647 $sql = 'SELECT * FROM pages WHERE pid = ? ORDER BY title';
648
649 $result = $this->subject->debug_testSQL($sql);
650 $expected = 'SELECT * FROM pages WHERE pid = ? ORDER BY title';
651 $this->assertEquals($expected, $this->cleanSql($result));
652 }
653
654 /**
655 * @test
656 * @see http://forge.typo3.org/issues/23374
657 */
658 public function parametersAreReferenced() {
659 $sql = 'SELECT * FROM pages WHERE pid = :pid1 OR pid = :pid2';
660 $components = $this->subject->_callRef('parseSELECT', $sql);
661 $this->assertInternalType('array', $components['parameters']);
662 $this->assertEquals(2, count($components['parameters']));
663 $this->assertTrue(isset($components['parameters'][':pid1']));
664 $this->assertTrue(isset($components['parameters'][':pid2']));
665 }
666
667 /**
668 * @test
669 * @see http://forge.typo3.org/issues/23374
670 */
671 public function sameParameterIsReferencedInSubquery() {
672 $sql = 'SELECT * FROM pages WHERE uid = :pageId OR uid IN (SELECT uid FROM pages WHERE pid = :pageId)';
673 $pageId = 12;
674 $components = $this->subject->_callRef('parseSELECT', $sql);
675 $components['parameters'][':pageId'][0] = $pageId;
676
677 $result = $this->subject->_callRef('compileSELECT', $components);
678 $expected = 'SELECT * FROM pages WHERE uid = 12 OR uid IN (SELECT uid FROM pages WHERE pid = 12)';
679 $this->assertEquals($expected, $this->cleanSql($result));
680 }
681
682 /**
683 * @test
684 * @see http://forge.typo3.org/issues/23374
685 */
686 public function namedParametersMayBeSafelyReplaced() {
687 $sql = 'SELECT * FROM pages WHERE pid = :pid AND title NOT LIKE \':pid\'';
688 $pid = 12;
689 $components = $this->subject->_callRef('parseSELECT', $sql);
690 $components['parameters'][':pid'][0] = $pid;
691
692 $result = $this->subject->_callRef('compileSELECT', $components);
693 $expected = 'SELECT * FROM pages WHERE pid = ' . $pid . ' AND title NOT LIKE \':pid\'';
694 $this->assertEquals($expected, $this->cleanSql($result));
695 }
696
697 /**
698 * @test
699 * @see http://forge.typo3.org/issues/23374
700 */
701 public function questionMarkParametersMayBeSafelyReplaced() {
702 $sql = 'SELECT * FROM pages WHERE pid = ? AND timestamp < ? AND title != \'How to test?\'';
703 $parameterValues = array(12, 1281782690);
704 $components = $this->subject->_callRef('parseSELECT', $sql);
705 for ($i = 0; $i < count($components['parameters']['?']); $i++) {
706 $components['parameters']['?'][$i][0] = $parameterValues[$i];
707 }
708
709 $result = $this->subject->_callRef('compileSELECT', $components);
710 $expected = 'SELECT * FROM pages WHERE pid = 12 AND timestamp < 1281782690 AND title != \'How to test?\'';
711 $this->assertEquals($expected, $this->cleanSql($result));
712 }
713
714 }