[BUGFIX] dbal: Cast field to CHAR for FIND_IN_SET()
[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/67155
275 */
276 public function canParseCastOperator() {
277 $parseString = 'CAST(parent AS CHAR) != \'\'';
278 $result = $this->subject->parseWhereClause($parseString);
279 $this->assertInternalType('array', $result);
280 $this->assertEmpty($parseString);
281 }
282
283 /**
284 * @test
285 * @see http://forge.typo3.org/issues/67155
286 */
287 public function canCompileCastOperator() {
288 $parseString = 'SELECT * FROM sys_category WHERE CAST(parent AS CHAR) != \'\'';
289 $components = $this->subject->_callRef('parseSELECT', $parseString);
290 $this->assertInternalType('array', $components);
291
292 $result = $this->subject->_callRef('compileSELECT', $components);
293 $expected = 'SELECT * FROM sys_category WHERE CAST(parent AS CHAR) != \'\'';
294 $this->assertEquals($expected, $this->cleanSql($result));
295 }
296
297 /**
298 * @test
299 * @see http://forge.typo3.org/issues/22695
300 */
301 public function canParseAlterEngineStatement() {
302 $parseString = 'ALTER TABLE tx_realurl_pathcache ENGINE=InnoDB';
303 $components = $this->subject->_callRef('parseALTERTABLE', $parseString);
304 $this->assertInternalType('array', $components);
305
306 $result = $this->subject->_callRef('compileALTERTABLE', $components);
307 $expected = 'ALTER TABLE tx_realurl_pathcache ENGINE = InnoDB';
308 $this->assertEquals($expected, $this->cleanSql($result));
309 }
310
311 /**
312 * @test
313 * @see http://forge.typo3.org/issues/22731
314 */
315 public function canParseAlterCharacterSetStatement() {
316 $parseString = 'ALTER TABLE `index_phash` DEFAULT CHARACTER SET utf8';
317 $components = $this->subject->_callRef('parseALTERTABLE', $parseString);
318 $this->assertInternalType('array', $components);
319
320 $result = $this->subject->_callRef('compileALTERTABLE', $components);
321 $expected = 'ALTER TABLE index_phash DEFAULT CHARACTER SET utf8';
322 $this->assertEquals($expected, $this->cleanSql($result));
323 }
324
325 /**
326 * @test
327 * @see http://forge.typo3.org/issues/67445
328 */
329 public function canParseAlterTableAddKeyStatement() {
330 $parseString = 'ALTER TABLE sys_collection ADD KEY parent (pid,deleted)';
331 $components = $this->subject->_callRef('parseALTERTABLE', $parseString);
332 $this->assertInternalType('array', $components);
333
334 $result = $this->subject->_callRef('compileALTERTABLE', $components);
335 $expected = 'ALTER TABLE sys_collection ADD KEY parent (pid,deleted)';
336 $this->assertSame($expected, $this->cleanSql($result));
337 }
338
339 /**
340 * @test
341 * @see http://forge.typo3.org/issues/67445
342 */
343 public function canParseAlterTableDropKeyStatement() {
344 $parseString = 'ALTER TABLE sys_collection DROP KEY parent';
345 $components = $this->subject->_callRef('parseALTERTABLE', $parseString);
346 $this->assertInternalType('array', $components);
347
348 $result = $this->subject->_callRef('compileALTERTABLE', $components);
349 $expected = 'ALTER TABLE sys_collection DROP KEY parent';
350 $this->assertSame($expected, $this->cleanSql($result));
351 }
352
353 /**
354 * @test
355 * @see http://forge.typo3.org/issues/23087
356 */
357 public function canParseFindInSetStatement() {
358 $parseString = 'SELECT * FROM fe_users WHERE FIND_IN_SET(10, usergroup)';
359 $components = $this->subject->_callRef('parseSELECT', $parseString);
360 $this->assertInternalType('array', $components);
361
362 $result = $this->subject->_callRef('compileSELECT', $components);
363 $expected = 'SELECT * FROM fe_users WHERE FIND_IN_SET(10, usergroup)';
364 $this->assertEquals($expected, $this->cleanSql($result));
365 }
366
367 /**
368 * @test
369 * @see http://forge.typo3.org/issues/27858
370 */
371 public function canParseSingleQuote() {
372 $parseString = 'SELECT * FROM pages WHERE title=\'1\\\'\' AND deleted=0';
373 $result = $this->subject->_callRef('parseSELECT', $parseString);
374 $this->assertInternalType('array', $result);
375 $this->assertEmpty($result['parseString']);
376 }
377
378 ///////////////////////////////////////
379 // Tests concerning JOINs
380 ///////////////////////////////////////
381 /**
382 * @test
383 */
384 public function parseFromTablesWithInnerJoinReturnsArray() {
385 $parseString = 'be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
386
387 $result = $this->subject->parseFromTables($parseString);
388 $this->assertInternalType('array', $result);
389 $this->assertEmpty($parseString);
390 }
391
392 /**
393 * @test
394 */
395 public function parseFromTablesWithLeftOuterJoinReturnsArray() {
396 $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id';
397
398 $result = $this->subject->parseFromTables($parseString);
399 $this->assertInternalType('array', $result);
400 $this->assertEmpty($parseString);
401 }
402
403 /**
404 * @test
405 * @see http://forge.typo3.org/issues/21555
406 */
407 public function parseFromTablesWithRightOuterJoinReturnsArray() {
408 $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid';
409
410 $result = $this->subject->parseFromTables($parseString);
411 $this->assertInternalType('array', $result);
412 $this->assertEmpty($parseString);
413 }
414
415 /**
416 * @test
417 */
418 public function parseFromTablesWithMultipleJoinsReturnsArray() {
419 $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';
420 $result = $this->subject->parseFromTables($parseString);
421 $this->assertInternalType('array', $result);
422 $this->assertEmpty($parseString);
423 }
424
425 /**
426 * @test
427 * @see http://forge.typo3.org/issues/21555
428 */
429 public function parseFromTablesWithMultipleJoinsAndParenthesesReturnsArray() {
430 $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';
431 $result = $this->subject->parseFromTables($parseString);
432 $this->assertInternalType('array', $result);
433 $this->assertEmpty($parseString);
434 }
435
436 /**
437 * @test
438 */
439 public function canUseInnerJoinInSelect() {
440 $sql = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
441
442 $result = $this->subject->debug_testSQL($sql);
443 $expected = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id=be_users.uid';
444 $this->assertEquals($expected, $this->cleanSql($result));
445 }
446
447 /**
448 * @test
449 */
450 public function canUseMultipleInnerJoinsInSelect() {
451 $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';
452
453 $result = $this->subject->debug_testSQL($sql);
454 $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';
455 $this->assertEquals($expected, $this->cleanSql($result));
456 }
457
458 /**
459 * @test
460 * @see http://forge.typo3.org/issues/22501
461 */
462 public function canParseMultipleJoinConditions() {
463 $sql = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.pid = T1.uid AND T2.size = 4 WHERE T1.cr_userid = 1';
464
465 $result = $this->subject->debug_testSQL($sql);
466 $expected = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.pid=T1.uid AND T2.size=4 WHERE T1.cr_userid = 1';
467 $this->assertEquals($expected, $this->cleanSql($result));
468 }
469
470 /**
471 * @test
472 * @see http://forge.typo3.org/issues/67385
473 */
474 public function canParseMultiJoinConditionsWithStrings() {
475 $sql = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key = sys_file_processedfile.uid AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
476
477 $result = $this->subject->debug_testSQL($sql);
478 $expected = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key=sys_file_processedfile.uid AND entry_namespace=\'ProcessedFileChecksumUpdate\'';
479 $this->assertEquals($expected, $this->cleanSql($result));
480 }
481
482 /**
483 * @test
484 * @see http://forge.typo3.org/issues/67708
485 */
486 public function canParseMultiJoinConditionsWithStringsAndLeftCast() {
487 $sql = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON CAST(entry_key AS INTEGER) = sys_file_processedfile.uid AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
488
489 $result = $this->subject->debug_testSQL($sql);
490 $expected = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON CAST(entry_key AS INTEGER)=sys_file_processedfile.uid AND entry_namespace=\'ProcessedFileChecksumUpdate\'';
491 $this->assertEquals($expected, $this->cleanSql($result));
492 }
493
494 /**
495 * @test
496 * @see http://forge.typo3.org/issues/67708
497 */
498 public function canParseMultiJoinConditionsWithStringsAndRightCast() {
499 $sql = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key = CAST(sys_file_processedfile.uid AS CHAR) AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
500
501 $result = $this->subject->debug_testSQL($sql);
502 $expected = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key=CAST(sys_file_processedfile.uid AS CHAR) AND entry_namespace=\'ProcessedFileChecksumUpdate\'';
503 $this->assertEquals($expected, $this->cleanSql($result));
504 }
505
506 /**
507 * @test
508 * @see http://forge.typo3.org/issues/22501
509 */
510 public function canParseMultipleJoinConditionsWithLessThanOperator() {
511 $sql = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size < 4 OR T2.pid = T1.uid WHERE T1.cr_userid = 1';
512
513 $result = $this->subject->debug_testSQL($sql);
514 $expected = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size<4 OR T2.pid=T1.uid WHERE T1.cr_userid = 1';
515 $this->assertEquals($expected, $this->cleanSql($result));
516 }
517
518 ///////////////////////////////////////
519 // Tests concerning DB management
520 ///////////////////////////////////////
521 /**
522 * @test
523 * @see http://forge.typo3.org/issues/16689
524 */
525 public function indexMayContainALengthRestrictionInCreateTable() {
526 $parseString = '
527 CREATE TABLE tx_realurl_uniqalias (
528 uid int(11) NOT NULL auto_increment,
529 tstamp int(11) DEFAULT \'0\' NOT NULL,
530 tablename varchar(60) DEFAULT \'\' NOT NULL,
531 field_alias varchar(255) DEFAULT \'\' NOT NULL,
532 field_id varchar(60) DEFAULT \'\' NOT NULL,
533 value_alias varchar(255) DEFAULT \'\' NOT NULL,
534 value_id int(11) DEFAULT \'0\' NOT NULL,
535 lang int(11) DEFAULT \'0\' NOT NULL,
536 expire int(11) DEFAULT \'0\' NOT NULL,
537
538 PRIMARY KEY (uid),
539 KEY tablename (tablename),
540 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
541 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
542 );
543 ';
544 $result = $this->subject->_callRef('parseCREATETABLE', $parseString);
545 $this->assertInternalType('array', $result);
546 }
547
548 /**
549 * @test
550 * @see http://forge.typo3.org/issues/21730
551 */
552 public function indexMayContainALengthRestrictionInAlterTable() {
553 $parseString = 'ALTER TABLE tx_realurl_uniqalias ADD KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)';
554 $result = $this->subject->_callRef('parseALTERTABLE', $parseString);
555 $this->assertInternalType('array', $result);
556 }
557
558 /**
559 * @test
560 * @see http://forge.typo3.org/issues/15366
561 */
562 public function canParseUniqueIndexCreation() {
563 $sql = 'ALTER TABLE static_territories ADD UNIQUE uid (uid)';
564 $expected = $sql;
565 $alterTables = $this->subject->_callRef('parseALTERTABLE', $sql);
566 $queries = $this->subject->compileSQL($alterTables);
567 $this->assertEquals($expected, $queries);
568 }
569
570 ///////////////////////////////////////
571 // Tests concerning subqueries
572 ///////////////////////////////////////
573 /**
574 * @test
575 * @see http://forge.typo3.org/issues/21688
576 */
577 public function inWhereClauseSupportsSubquery() {
578 $parseString = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
579 $result = $this->subject->parseWhereClause($parseString);
580 $this->assertInternalType('array', $result);
581 $this->assertEmpty($parseString);
582 }
583
584 /**
585 * @test
586 * @see http://forge.typo3.org/issues/21688
587 */
588 public function inWhereClauseWithSubqueryIsProperlyCompiled() {
589 $sql = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
590
591 $result = $this->subject->debug_testSQL($sql);
592 $expected = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active = 0 AND deleted = 0)';
593 $this->assertEquals($expected, $this->cleanSql($result));
594 }
595
596 /**
597 * @test
598 * @see http://forge.typo3.org/issues/21688
599 */
600 public function whereClauseSupportsExistsKeyword() {
601 $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)';
602 $result = $this->subject->parseWhereClause($parseString);
603 $this->assertInternalType('array', $result);
604 $this->assertEmpty($parseString);
605 }
606
607 /**
608 * @test
609 * @see http://forge.typo3.org/issues/21688
610 */
611 public function existsClauseIsProperlyCompiled() {
612 $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)';
613
614 $result = $this->subject->debug_testSQL($sql);
615 $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)';
616 $this->assertEquals($expected, $this->cleanSql($result));
617 }
618
619 ///////////////////////////////////////
620 // Tests concerning advanced operators
621 ///////////////////////////////////////
622 /**
623 * @test
624 * @see http://forge.typo3.org/issues/21903
625 */
626 public function caseWithBooleanConditionIsSupportedInFields() {
627 $parseString = 'CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column';
628 $result = $this->subject->parseFieldList($parseString);
629 $this->assertInternalType('array', $result);
630 $this->assertEmpty($parseString);
631 }
632
633 /**
634 * @test
635 * @see http://forge.typo3.org/issues/21903
636 */
637 public function caseWithBooleanConditionIsProperlyCompiled() {
638 $sql = 'SELECT CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
639
640 $result = $this->subject->debug_testSQL($sql);
641 $expected = 'SELECT CASE WHEN 1 > 0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
642 $this->assertEquals($expected, $this->cleanSql($result));
643 }
644
645 /**
646 * @test
647 * @see http://forge.typo3.org/issues/21903
648 */
649 public function caseWithMultipleWhenIsSupportedInFields() {
650 $parseString = 'CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number';
651 $result = $this->subject->parseFieldList($parseString);
652 $this->assertInternalType('array', $result);
653 $this->assertEmpty($parseString);
654 }
655
656 /**
657 * @test
658 * @see http://forge.typo3.org/issues/21903
659 */
660 public function caseWithMultipleWhenIsProperlyCompiled() {
661 $sql = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
662
663 $result = $this->subject->debug_testSQL($sql);
664 $expected = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
665 $this->assertEquals($expected, $this->cleanSql($result));
666 }
667
668 /**
669 * @test
670 * @see http://forge.typo3.org/issues/21902
671 */
672 public function locateIsSupported() {
673 $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure)>0';
674
675 $result = $this->subject->debug_testSQL($sql);
676 $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure) > 0';
677 $this->assertEquals($expected, $this->cleanSql($result));
678 }
679
680 /**
681 * @test
682 * @see http://forge.typo3.org/issues/21902
683 */
684 public function locateWithPositionIsSupported() {
685 $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\' , datastructure ,10)>0';
686 $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure, 10) > 0';
687 $result = $this->cleanSql($this->subject->debug_testSQL($sql));
688 $this->assertEquals($expected, $result);
689 }
690
691 /**
692 * @test
693 * @see http://forge.typo3.org/issues/21902
694 * @see http://forge.typo3.org/issues/21903
695 */
696 public function locateWithinCaseIsSupported() {
697 $sql = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure)>0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
698
699 $result = $this->subject->debug_testSQL($sql);
700 $expected = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure) > 0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
701 $this->assertEquals($expected, $this->cleanSql($result));
702 }
703
704 ///////////////////////////////////////
705 // Tests concerning prepared queries
706 ///////////////////////////////////////
707 /**
708 * @test
709 * @see http://forge.typo3.org/issues/23374
710 */
711 public function namedPlaceholderIsSupported() {
712 $sql = 'SELECT * FROM pages WHERE pid = :pid ORDER BY title';
713
714 $result = $this->subject->debug_testSQL($sql);
715 $expected = 'SELECT * FROM pages WHERE pid = :pid ORDER BY title';
716 $this->assertEquals($expected, $this->cleanSql($result));
717 }
718
719 /**
720 * @test
721 * @see http://forge.typo3.org/issues/23374
722 */
723 public function questionMarkPlaceholderIsSupported() {
724 $sql = 'SELECT * FROM pages WHERE pid = ? ORDER BY title';
725
726 $result = $this->subject->debug_testSQL($sql);
727 $expected = 'SELECT * FROM pages WHERE pid = ? ORDER BY title';
728 $this->assertEquals($expected, $this->cleanSql($result));
729 }
730
731 /**
732 * @test
733 * @see http://forge.typo3.org/issues/23374
734 */
735 public function parametersAreReferenced() {
736 $sql = 'SELECT * FROM pages WHERE pid = :pid1 OR pid = :pid2';
737 $components = $this->subject->_callRef('parseSELECT', $sql);
738 $this->assertInternalType('array', $components['parameters']);
739 $this->assertEquals(2, count($components['parameters']));
740 $this->assertTrue(isset($components['parameters'][':pid1']));
741 $this->assertTrue(isset($components['parameters'][':pid2']));
742 }
743
744 /**
745 * @test
746 * @see http://forge.typo3.org/issues/23374
747 */
748 public function sameParameterIsReferencedInSubquery() {
749 $sql = 'SELECT * FROM pages WHERE uid = :pageId OR uid IN (SELECT uid FROM pages WHERE pid = :pageId)';
750 $pageId = 12;
751 $components = $this->subject->_callRef('parseSELECT', $sql);
752 $components['parameters'][':pageId'][0] = $pageId;
753
754 $result = $this->subject->_callRef('compileSELECT', $components);
755 $expected = 'SELECT * FROM pages WHERE uid = 12 OR uid IN (SELECT uid FROM pages WHERE pid = 12)';
756 $this->assertEquals($expected, $this->cleanSql($result));
757 }
758
759 /**
760 * @test
761 * @see http://forge.typo3.org/issues/23374
762 */
763 public function namedParametersMayBeSafelyReplaced() {
764 $sql = 'SELECT * FROM pages WHERE pid = :pid AND title NOT LIKE \':pid\'';
765 $pid = 12;
766 $components = $this->subject->_callRef('parseSELECT', $sql);
767 $components['parameters'][':pid'][0] = $pid;
768
769 $result = $this->subject->_callRef('compileSELECT', $components);
770 $expected = 'SELECT * FROM pages WHERE pid = ' . $pid . ' AND title NOT LIKE \':pid\'';
771 $this->assertEquals($expected, $this->cleanSql($result));
772 }
773
774 /**
775 * @test
776 * @see http://forge.typo3.org/issues/23374
777 */
778 public function questionMarkParametersMayBeSafelyReplaced() {
779 $sql = 'SELECT * FROM pages WHERE pid = ? AND timestamp < ? AND title != \'How to test?\'';
780 $parameterValues = array(12, 1281782690);
781 $components = $this->subject->_callRef('parseSELECT', $sql);
782 $questionMarkParamCount = count($components['parameters']['?']);
783 for ($i = 0; $i < $questionMarkParamCount; $i++) {
784 $components['parameters']['?'][$i][0] = $parameterValues[$i];
785 }
786
787 $result = $this->subject->_callRef('compileSELECT', $components);
788 $expected = 'SELECT * FROM pages WHERE pid = 12 AND timestamp < 1281782690 AND title != \'How to test?\'';
789 $this->assertEquals($expected, $this->cleanSql($result));
790 }
791
792 }