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