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