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