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