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