51db3d7a380479cd87f2de9324f8c0cc153372ec
[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 public function setUp() {
31 $subject = $this->getAccessibleMock('TYPO3\\CMS\\Dbal\\Database\\SqlParser', array('dummy'), array(), '', FALSE);
32
33 $mockDatabaseConnection = $this->getMock('TYPO3\\CMS\\Dbal\\Database\\DatabaseConnection', 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/22501
420 */
421 public function canParseMultipleJoinConditionsWithLessThanOperator() {
422 $sql = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size < 4 OR T2.pid = T1.uid WHERE T1.cr_userid = 1';
423
424 $result = $this->subject->debug_testSQL($sql);
425 $expected = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size<4 OR T2.pid=T1.uid WHERE T1.cr_userid = 1';
426 $this->assertEquals($expected, $this->cleanSql($result));
427 }
428
429 ///////////////////////////////////////
430 // Tests concerning DB management
431 ///////////////////////////////////////
432 /**
433 * @test
434 * @see http://forge.typo3.org/issues/16689
435 */
436 public function indexMayContainALengthRestrictionInCreateTable() {
437 $parseString = '
438 CREATE TABLE tx_realurl_uniqalias (
439 uid int(11) NOT NULL auto_increment,
440 tstamp int(11) DEFAULT \'0\' NOT NULL,
441 tablename varchar(60) DEFAULT \'\' NOT NULL,
442 field_alias varchar(255) DEFAULT \'\' NOT NULL,
443 field_id varchar(60) DEFAULT \'\' NOT NULL,
444 value_alias varchar(255) DEFAULT \'\' NOT NULL,
445 value_id int(11) DEFAULT \'0\' NOT NULL,
446 lang int(11) DEFAULT \'0\' NOT NULL,
447 expire int(11) DEFAULT \'0\' NOT NULL,
448
449 PRIMARY KEY (uid),
450 KEY tablename (tablename),
451 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
452 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
453 );
454 ';
455 $result = $this->subject->_callRef('parseCREATETABLE', $parseString);
456 $this->assertInternalType('array', $result);
457 }
458
459 /**
460 * @test
461 * @see http://forge.typo3.org/issues/21730
462 */
463 public function indexMayContainALengthRestrictionInAlterTable() {
464 $parseString = 'ALTER TABLE tx_realurl_uniqalias ADD KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)';
465 $result = $this->subject->_callRef('parseALTERTABLE', $parseString);
466 $this->assertInternalType('array', $result);
467 }
468
469 /**
470 * @test
471 * @see http://forge.typo3.org/issues/15366
472 */
473 public function canParseUniqueIndexCreation() {
474 $sql = 'ALTER TABLE static_territories ADD UNIQUE uid (uid)';
475 $expected = $sql;
476 $alterTables = $this->subject->_callRef('parseALTERTABLE', $sql);
477 $queries = $this->subject->compileSQL($alterTables);
478 $this->assertEquals($expected, $queries);
479 }
480
481 ///////////////////////////////////////
482 // Tests concerning subqueries
483 ///////////////////////////////////////
484 /**
485 * @test
486 * @see http://forge.typo3.org/issues/21688
487 */
488 public function inWhereClauseSupportsSubquery() {
489 $parseString = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
490 $result = $this->subject->parseWhereClause($parseString);
491 $this->assertInternalType('array', $result);
492 $this->assertEmpty($parseString);
493 }
494
495 /**
496 * @test
497 * @see http://forge.typo3.org/issues/21688
498 */
499 public function inWhereClauseWithSubqueryIsProperlyCompiled() {
500 $sql = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
501
502 $result = $this->subject->debug_testSQL($sql);
503 $expected = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active = 0 AND deleted = 0)';
504 $this->assertEquals($expected, $this->cleanSql($result));
505 }
506
507 /**
508 * @test
509 * @see http://forge.typo3.org/issues/21688
510 */
511 public function whereClauseSupportsExistsKeyword() {
512 $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)';
513 $result = $this->subject->parseWhereClause($parseString);
514 $this->assertInternalType('array', $result);
515 $this->assertEmpty($parseString);
516 }
517
518 /**
519 * @test
520 * @see http://forge.typo3.org/issues/21688
521 */
522 public function existsClauseIsProperlyCompiled() {
523 $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)';
524
525 $result = $this->subject->debug_testSQL($sql);
526 $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)';
527 $this->assertEquals($expected, $this->cleanSql($result));
528 }
529
530 ///////////////////////////////////////
531 // Tests concerning advanced operators
532 ///////////////////////////////////////
533 /**
534 * @test
535 * @see http://forge.typo3.org/issues/21903
536 */
537 public function caseWithBooleanConditionIsSupportedInFields() {
538 $parseString = 'CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column';
539 $result = $this->subject->parseFieldList($parseString);
540 $this->assertInternalType('array', $result);
541 $this->assertEmpty($parseString);
542 }
543
544 /**
545 * @test
546 * @see http://forge.typo3.org/issues/21903
547 */
548 public function caseWithBooleanConditionIsProperlyCompiled() {
549 $sql = 'SELECT CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
550
551 $result = $this->subject->debug_testSQL($sql);
552 $expected = 'SELECT CASE WHEN 1 > 0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
553 $this->assertEquals($expected, $this->cleanSql($result));
554 }
555
556 /**
557 * @test
558 * @see http://forge.typo3.org/issues/21903
559 */
560 public function caseWithMultipleWhenIsSupportedInFields() {
561 $parseString = 'CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number';
562 $result = $this->subject->parseFieldList($parseString);
563 $this->assertInternalType('array', $result);
564 $this->assertEmpty($parseString);
565 }
566
567 /**
568 * @test
569 * @see http://forge.typo3.org/issues/21903
570 */
571 public function caseWithMultipleWhenIsProperlyCompiled() {
572 $sql = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
573
574 $result = $this->subject->debug_testSQL($sql);
575 $expected = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
576 $this->assertEquals($expected, $this->cleanSql($result));
577 }
578
579 /**
580 * @test
581 * @see http://forge.typo3.org/issues/21902
582 */
583 public function locateIsSupported() {
584 $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure)>0';
585
586 $result = $this->subject->debug_testSQL($sql);
587 $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure) > 0';
588 $this->assertEquals($expected, $this->cleanSql($result));
589 }
590
591 /**
592 * @test
593 * @see http://forge.typo3.org/issues/21902
594 */
595 public function locateWithPositionIsSupported() {
596 $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\' , datastructure ,10)>0';
597 $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure, 10) > 0';
598 $result = $this->cleanSql($this->subject->debug_testSQL($sql));
599 $this->assertEquals($expected, $result);
600 }
601
602 /**
603 * @test
604 * @see http://forge.typo3.org/issues/21902
605 * @see http://forge.typo3.org/issues/21903
606 */
607 public function locateWithinCaseIsSupported() {
608 $sql = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure)>0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
609
610 $result = $this->subject->debug_testSQL($sql);
611 $expected = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure) > 0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
612 $this->assertEquals($expected, $this->cleanSql($result));
613 }
614
615 ///////////////////////////////////////
616 // Tests concerning prepared queries
617 ///////////////////////////////////////
618 /**
619 * @test
620 * @see http://forge.typo3.org/issues/23374
621 */
622 public function namedPlaceholderIsSupported() {
623 $sql = 'SELECT * FROM pages WHERE pid = :pid ORDER BY title';
624
625 $result = $this->subject->debug_testSQL($sql);
626 $expected = 'SELECT * FROM pages WHERE pid = :pid ORDER BY title';
627 $this->assertEquals($expected, $this->cleanSql($result));
628 }
629
630 /**
631 * @test
632 * @see http://forge.typo3.org/issues/23374
633 */
634 public function questionMarkPlaceholderIsSupported() {
635 $sql = 'SELECT * FROM pages WHERE pid = ? ORDER BY title';
636
637 $result = $this->subject->debug_testSQL($sql);
638 $expected = 'SELECT * FROM pages WHERE 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 parametersAreReferenced() {
647 $sql = 'SELECT * FROM pages WHERE pid = :pid1 OR pid = :pid2';
648 $components = $this->subject->_callRef('parseSELECT', $sql);
649 $this->assertInternalType('array', $components['parameters']);
650 $this->assertEquals(2, count($components['parameters']));
651 $this->assertTrue(isset($components['parameters'][':pid1']));
652 $this->assertTrue(isset($components['parameters'][':pid2']));
653 }
654
655 /**
656 * @test
657 * @see http://forge.typo3.org/issues/23374
658 */
659 public function sameParameterIsReferencedInSubquery() {
660 $sql = 'SELECT * FROM pages WHERE uid = :pageId OR uid IN (SELECT uid FROM pages WHERE pid = :pageId)';
661 $pageId = 12;
662 $components = $this->subject->_callRef('parseSELECT', $sql);
663 $components['parameters'][':pageId'][0] = $pageId;
664
665 $result = $this->subject->_callRef('compileSELECT', $components);
666 $expected = 'SELECT * FROM pages WHERE uid = 12 OR uid IN (SELECT uid FROM pages WHERE pid = 12)';
667 $this->assertEquals($expected, $this->cleanSql($result));
668 }
669
670 /**
671 * @test
672 * @see http://forge.typo3.org/issues/23374
673 */
674 public function namedParametersMayBeSafelyReplaced() {
675 $sql = 'SELECT * FROM pages WHERE pid = :pid AND title NOT LIKE \':pid\'';
676 $pid = 12;
677 $components = $this->subject->_callRef('parseSELECT', $sql);
678 $components['parameters'][':pid'][0] = $pid;
679
680 $result = $this->subject->_callRef('compileSELECT', $components);
681 $expected = 'SELECT * FROM pages WHERE pid = ' . $pid . ' AND title NOT LIKE \':pid\'';
682 $this->assertEquals($expected, $this->cleanSql($result));
683 }
684
685 /**
686 * @test
687 * @see http://forge.typo3.org/issues/23374
688 */
689 public function questionMarkParametersMayBeSafelyReplaced() {
690 $sql = 'SELECT * FROM pages WHERE pid = ? AND timestamp < ? AND title != \'How to test?\'';
691 $parameterValues = array(12, 1281782690);
692 $components = $this->subject->_callRef('parseSELECT', $sql);
693 for ($i = 0; $i < count($components['parameters']['?']); $i++) {
694 $components['parameters']['?'][$i][0] = $parameterValues[$i];
695 }
696
697 $result = $this->subject->_callRef('compileSELECT', $components);
698 $expected = 'SELECT * FROM pages WHERE pid = 12 AND timestamp < 1281782690 AND title != \'How to test?\'';
699 $this->assertEquals($expected, $this->cleanSql($result));
700 }
701 }