Raised DBAL version from 1.1.7 to 1.1.8
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / tests / dbOracleTest.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 require_once('FakeDbConnection.php');
28
29 /**
30 * Testcase for class ux_t3lib_db. Testing Oracle database handling.
31 *
32 * $Id$
33 *
34 * @author Xavier Perseguers <typo3@perseguers.ch>
35 *
36 * @package TYPO3
37 * @subpackage dbal
38 */
39 class dbOracleTest extends BaseTestCase {
40
41 /**
42 * @var t3lib_db
43 */
44 protected $db;
45
46 /**
47 * @var array
48 */
49 protected $dbalConfig;
50
51 /**
52 * Prepares the environment before running a test.
53 */
54 public function setUp() {
55 // Backup DBAL configuration
56 $this->dbalConfig = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'];
57 // Backup database connection
58 $this->db = $GLOBALS['TYPO3_DB'];
59 // Reconfigure DBAL to use Oracle
60 require('fixtures/oci8.config.php');
61
62 $className = self::buildAccessibleProxy('ux_t3lib_db');
63 $GLOBALS['TYPO3_DB'] = new $className;
64 $parserClassName = self::buildAccessibleProxy('ux_t3lib_sqlparser');
65 $GLOBALS['TYPO3_DB']->SQLparser = new $parserClassName;
66
67 $this->assertFalse($GLOBALS['TYPO3_DB']->isConnected());
68
69 // Initialize a fake Oracle connection
70 FakeDbConnection::connect($GLOBALS['TYPO3_DB'], 'oci8');
71
72 $this->assertTrue($GLOBALS['TYPO3_DB']->isConnected());
73 }
74
75 /**
76 * Cleans up the environment after running a test.
77 */
78 public function tearDown() {
79 // Clear DBAL-generated cache files
80 $GLOBALS['TYPO3_DB']->clearCachedFieldInfo();
81 // Restore DBAL configuration
82 $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'] = $this->dbalConfig;
83 // Restore DB connection
84 $GLOBALS['TYPO3_DB'] = $this->db;
85 }
86
87 /**
88 * Cleans a SQL query.
89 *
90 * @param mixed $sql
91 * @return mixed (string or array)
92 */
93 private function cleanSql($sql) {
94 if (!is_string($sql)) {
95 return $sql;
96 }
97
98 $sql = str_replace("\n", ' ', $sql);
99 $sql = preg_replace('/\s+/', ' ', $sql);
100 return trim($sql);
101 }
102
103 /**
104 * @test
105 */
106 public function configurationIsUsingAdodbAndDriverOci8() {
107 $configuration = $GLOBALS['TYPO3_DB']->conf['handlerCfg'];
108 $this->assertTrue(is_array($configuration) && count($configuration) > 0, 'No configuration found');
109 $this->assertEquals('adodb', $configuration['_DEFAULT']['type']);
110 $this->assertTrue($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') !== FALSE, 'Not using oci8 driver');
111 }
112
113 /**
114 * @test
115 */
116 public function tablesWithMappingAreDetected() {
117 $tablesWithMapping = array_keys($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping']);
118
119 foreach ($GLOBALS['TYPO3_DB']->cache_fieldType as $table => $fieldTypes) {
120 $tableDef = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table);
121
122 if (in_array($table, $tablesWithMapping)) {
123 self::assertTrue(is_array($tableDef), 'Table ' . $table . ' was expected to need mapping');
124 } else {
125 self::assertFalse($tableDef, 'Table ' . $table . ' was not expected to need mapping');
126 }
127 }
128 }
129
130 /**
131 * @test
132 * @see http://bugs.typo3.org/view.php?id=12897
133 */
134 public function sqlHintIsRemoved() {
135 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
136 '/*! SQL_NO_CACHE */ content',
137 'tx_realurl_urlencodecache',
138 '1=1'
139 ));
140 $expected = 'SELECT "content" FROM "tx_realurl_urlencodecache" WHERE 1 = 1';
141 $this->assertEquals($expected, $query);
142 }
143
144 /**
145 * @test
146 */
147 public function canCompileInsertWithFields() {
148 $parseString = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
149 $parseString .= "VALUES ('1', '0', '2', '0', 'Africa');";
150 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseINSERT', $parseString);
151
152 $this->assertTrue(is_array($components), $components);
153 $insert = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('compileINSERT', $components);
154
155 $expected = array(
156 'uid' => '1',
157 'pid' => '0',
158 'tr_iso_nr' => '2',
159 'tr_parent_iso_nr' => '0',
160 'tr_name_en' => 'Africa',
161 );
162 $this->assertEquals($expected, $insert);
163 }
164
165 /**
166 * @test
167 * http://bugs.typo3.org/view.php?id=13209
168 */
169 public function canCompileExtendedInsert() {
170 $parseString = "INSERT INTO static_territories VALUES ('1', '0', '2', '0', 'Africa'),('2', '0', '9', '0', 'Oceania')," .
171 "('3', '0', '19', '0', 'Americas'),('4', '0', '142', '0', 'Asia');";
172 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseINSERT', $parseString);
173
174 $this->assertTrue(is_array($components), $components);
175 $insert = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('compileINSERT', $components);
176
177 $this->assertEquals(4, count($insert));
178
179 for ($i = 0; $i < count($insert); $i++) {
180 foreach (t3lib_div::trimExplode(',', 'uid,pid,tr_iso_nr,tr_parent_iso_nr,tr_name_en') as $field) {
181 $this->assertTrue(isset($insert[$i][$field]), 'Could not find ' . $field . ' column');
182 }
183 }
184 }
185
186 /**
187 * @test
188 * http://bugs.typo3.org/view.php?id=12858
189 */
190 public function sqlForInsertWithMultipleRowsIsValid() {
191 $fields = array('uid', 'pid', 'title', 'body');
192 $rows = array(
193 array('1', '2', 'Title #1', 'Content #1'),
194 array('3', '4', 'Title #2', 'Content #2'),
195 array('5', '6', 'Title #3', 'Content #3'),
196 );
197 $query = $GLOBALS['TYPO3_DB']->INSERTmultipleRows('tt_content', $fields, $rows);
198
199 $expected[0] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'1\', \'2\', \'Title #1\', \'Content #1\' )';
200 $expected[1] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'3\', \'4\', \'Title #2\', \'Content #2\' )';
201 $expected[2] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'5\', \'6\', \'Title #3\', \'Content #3\' )';
202
203 $this->assertEquals(count($expected), count($query));
204 for ($i = 0; $i < count($query); $i++) {
205 $this->assertTrue(is_array($query[$i]), 'Expected array: ' . $query[$i]);
206 $this->assertEquals(1, count($query[$i]));
207 $this->assertEquals($expected[$i], $this->cleanSql($query[$i][0]));
208 }
209 }
210
211 ///////////////////////////////////////
212 // Tests concerning quoting
213 ///////////////////////////////////////
214
215 /**
216 * @test
217 */
218 public function selectQueryIsProperlyQuoted() {
219 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
220 'uid', // select fields
221 'tt_content', // from table
222 'pid=1', // where clause
223 'cruser_id', // group by
224 'tstamp' // order by
225 ));
226 $expected = 'SELECT "uid" FROM "tt_content" WHERE "pid" = 1 GROUP BY "cruser_id" ORDER BY "tstamp"';
227 $this->assertEquals($expected, $query);
228 }
229
230 /**
231 * @test
232 * http://bugs.typo3.org/view.php?id=13504
233 */
234 public function truncateQueryIsProperlyQuoted() {
235 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->TRUNCATEquery('be_users'));
236 $expected = 'TRUNCATE TABLE "be_users"';
237 $this->assertEquals($expected, $query);
238 }
239
240 /**
241 * @test
242 * @see http://bugs.typo3.org/view.php?id=2438
243 */
244 public function distinctFieldIsProperlyQuoted() {
245 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
246 'COUNT(DISTINCT pid)', // select fields
247 'tt_content', // from table
248 '1=1' // where clause
249 ));
250 $expected = 'SELECT COUNT(DISTINCT "pid") FROM "tt_content" WHERE 1 = 1';
251 $this->assertEquals($expected, $query);
252 }
253
254 /**
255 * @test
256 * @see http://bugs.typo3.org/view.php?id=10411
257 * @remark Remapping is not expected here
258 */
259 public function multipleInnerJoinsAreProperlyQuoted() {
260 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
261 '*',
262 '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',
263 '1=1'
264 ));
265 $expected = 'SELECT * FROM "tt_news_cat"';
266 $expected .= ' INNER JOIN "tt_news_cat_mm" ON "tt_news_cat"."uid"="tt_news_cat_mm"."uid_foreign"';
267 $expected .= ' INNER JOIN "tt_news" ON "tt_news"."uid"="tt_news_cat_mm"."uid_local"';
268 $expected .= ' WHERE 1 = 1';
269 $this->assertEquals($expected, $query);
270 }
271
272 /**
273 * @test
274 * @see http://bugs.typo3.org/view.php?id=6198
275 */
276 public function stringsWithinInClauseAreProperlyQuoted() {
277 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
278 'COUNT(DISTINCT tx_dam.uid) AS count',
279 'tx_dam',
280 'tx_dam.pid IN (1) AND tx_dam.file_type IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND tx_dam.deleted = 0'
281 ));
282 $expected = 'SELECT COUNT(DISTINCT "tx_dam"."uid") AS "count" FROM "tx_dam"';
283 $expected .= ' WHERE "tx_dam"."pid" IN (1) AND "tx_dam"."file_type" IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND "tx_dam"."deleted" = 0';
284 $this->assertEquals($expected, $query);
285 }
286
287 /**
288 * @test
289 * @see http://bugs.typo3.org/view.php?id=12515
290 * @remark Remapping is not expected here
291 */
292 public function concatAfterLikeOperatorIsProperlyQuoted() {
293 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
294 '*',
295 'sys_refindex, tx_dam_file_tracking',
296 'sys_refindex.tablename = \'tx_dam_file_tracking\''
297 . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)'
298 ));
299 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
300 $expected .= ' AND (instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."file_path","tx_dam_file_tracking"."file_name"),1,1) > 0)';
301 $this->assertEquals($expected, $query);
302 }
303
304 /**
305 * @test
306 * @see http://bugs.typo3.org/view.php?id=12231
307 */
308 public function cachingFrameworkQueryIsProperlyQuoted() {
309 $currentTime = time();
310 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
311 'content',
312 'cache_hash',
313 'identifier = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('abbbabaf2d4b3f9a63e8dde781f1c106', 'cache_hash') .
314 ' AND (crdate + lifetime >= ' . $currentTime . ' OR lifetime = 0)'
315 ));
316 $expected = 'SELECT "content" FROM "cache_hash" WHERE "identifier" = \'abbbabaf2d4b3f9a63e8dde781f1c106\' AND ("crdate"+"lifetime" >= ' . $currentTime . ' OR "lifetime" = 0)';
317 $this->assertEquals($expected, $query);
318 }
319
320 /**
321 * @test
322 * @see http://bugs.typo3.org/view.php?id=12231
323 */
324 public function calculatedFieldsAreProperlyQuoted() {
325 $currentTime = time();
326 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
327 'identifier',
328 'cachingframework_cache_pages',
329 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
330 ));
331 $expected = 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
332 $this->assertEquals($expected, $query);
333 }
334
335 /**
336 * @test
337 * http://bugs.typo3.org/view.php?id=13422
338 */
339 public function numericColumnsAreNotQuoted() {
340 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
341 '1',
342 'be_users',
343 'username = \'_cli_scheduler\' AND admin = 0 AND be_users.deleted = 0'
344 ));
345 $expected = 'SELECT 1 FROM "be_users" WHERE "username" = \'_cli_scheduler\' AND "admin" = 0 AND "be_users"."deleted" = 0';
346 $this->assertEquals($expected, $query);
347 }
348
349 ///////////////////////////////////////
350 // Tests concerning remapping
351 ///////////////////////////////////////
352
353 /**
354 * @test
355 * @see http://bugs.typo3.org/view.php?id=10411
356 * @remark Remapping is expected here
357 */
358 public function tablesAndFieldsAreRemappedInMultipleJoins() {
359 $selectFields = '*';
360 $fromTables = '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';
361 $whereClause = '1=1';
362 $groupBy = '';
363 $orderBy = '';
364
365 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
366 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
367
368 $expected = 'SELECT * FROM "ext_tt_news_cat"';
369 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
370 $expected .= ' INNER JOIN "ext_tt_news" ON "ext_tt_news"."news_uid"="ext_tt_news_cat_mm"."local_uid"';
371 $expected .= ' WHERE 1 = 1';
372 $this->assertEquals($expected, $query);
373 }
374
375 /**
376 * @test
377 * @see http://bugs.typo3.org/view.php?id=6953
378 */
379 public function fieldWithinSqlFunctionIsRemapped() {
380 $selectFields = 'tstamp, script, SUM(exec_time) AS calc_sum, COUNT(*) AS qrycount, MAX(errorFlag) AS error';
381 $fromTables = 'tx_dbal_debuglog';
382 $whereClause = '1=1';
383 $groupBy = '';
384 $orderBy = '';
385
386 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
387 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
388
389 $expected = 'SELECT "tstamp", "script", SUM("exec_time") AS "calc_sum", COUNT(*) AS "qrycount", MAX("errorflag") AS "error" FROM "tx_dbal_debuglog" WHERE 1 = 1';
390 $this->assertEquals($expected, $query);
391 }
392
393 /**
394 * @test
395 * @see http://bugs.typo3.org/view.php?id=6953
396 */
397 public function tableAndFieldWithinSqlFunctionIsRemapped() {
398 $selectFields = 'MAX(tt_news_cat.uid) AS biggest_id';
399 $fromTables = 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign';
400 $whereClause = 'tt_news_cat_mm.uid_local > 50';
401 $groupBy = '';
402 $orderBy = '';
403
404 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
405 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
406
407 $expected = 'SELECT MAX("ext_tt_news_cat"."cat_uid") AS "biggest_id" FROM "ext_tt_news_cat"';
408 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
409 $expected .= ' WHERE "ext_tt_news_cat_mm"."local_uid" > 50';
410 $this->assertEquals($expected, $query);
411 }
412
413 /**
414 * @test
415 * @see http://bugs.typo3.org/view.php?id=12515
416 * @remark Remapping is expected here
417 */
418 public function concatAfterLikeOperatorIsRemapped() {
419 $selectFields = '*';
420 $fromTables = 'sys_refindex, tx_dam_file_tracking';
421 $whereClause = 'sys_refindex.tablename = \'tx_dam_file_tracking\''
422 . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)';
423 $groupBy = '';
424 $orderBy = '';
425
426 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
427 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
428
429 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
430 $expected .= ' AND (instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."path","tx_dam_file_tracking"."filename"),1,1) > 0)';
431 $this->assertEquals($expected, $query);
432 }
433
434 /**
435 * @test
436 * @see http://bugs.typo3.org/view.php?id=5708
437 */
438 public function fieldIsMappedOnRightSideOfAJoinCondition() {
439 $selectFields = 'cpg_categories.uid, cpg_categories.name';
440 $fromTables = 'cpg_categories, pages';
441 $whereClause = 'pages.uid = cpg_categories.pid AND pages.deleted = 0 AND 1 = 1';
442 $groupBy = '';
443 $orderBy = 'cpg_categories.pos';
444
445 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
446 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
447
448 $expected = 'SELECT "cpg_categories"."uid", "cpg_categories"."name" FROM "cpg_categories", "my_pages" WHERE "my_pages"."page_uid" = "cpg_categories"."page_id"';
449 $expected .= ' AND "my_pages"."deleted" = 0 AND 1 = 1 ORDER BY "cpg_categories"."pos"';
450 $this->assertEquals($expected, $query);
451 }
452
453 /**
454 * @test
455 * @see http://bugs.typo3.org/view.php?id=14372
456 */
457 public function fieldFromAliasIsRemapped() {
458 $selectFields = 'news.uid';
459 $fromTables = 'tt_news AS news';
460 $whereClause = 'news.uid = 1';
461 $groupBy = '';
462 $orderBy = '';
463
464 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
465 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
466
467 $expected = 'SELECT "news"."news_uid" FROM "ext_tt_news" AS "news" WHERE "news"."news_uid" = 1';
468 $this->assertEquals($expected, $query);
469 }
470
471 /**
472 * Trick here is that we already have a mapping for both table tt_news and table tt_news_cat
473 * (see tests/fixtures/oci8.config.php) which is used as alias name.
474 *
475 * @test
476 * @see http://bugs.typo3.org/view.php?id=14372
477 */
478 public function fieldFromAliasIsRemappedWithoutBeingTricked() {
479 $selectFields = 'tt_news_cat.uid';
480 $fromTables = 'tt_news AS tt_news_cat';
481 $whereClause = 'tt_news_cat.uid = 1';
482 $groupBy = '';
483 $orderBy = '';
484
485 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
486 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
487
488 $expected = 'SELECT "tt_news_cat"."news_uid" FROM "ext_tt_news" AS "tt_news_cat" WHERE "tt_news_cat"."news_uid" = 1';
489 $this->assertEquals($expected, $query);
490 }
491
492 /**
493 * @test
494 * @see http://bugs.typo3.org/view.php?id=14372
495 */
496 public function aliasRemappingDoesNotAlterFurtherQueries() {
497 $selectFields = 'foo.uid';
498 $fromTables = 'tt_news AS foo';
499 $whereClause = 'foo.uid = 1';
500 $groupBy = '';
501 $orderBy = '';
502
503 // First call to possibly alter (in memory) the mapping from localconf.php
504 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
505
506 $selectFields = 'uid';
507 $fromTables = 'foo';
508 $whereClause = 'uid = 1';
509 $groupBy = '';
510 $orderBy = '';
511
512 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
513 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
514
515 $expected = 'SELECT "uid" FROM "foo" WHERE "uid" = 1';
516 $this->assertEquals($expected, $query);
517 }
518
519 /**
520 * @test
521 * @see http://bugs.typo3.org/view.php?id=14372
522 */
523 public function fieldFromAliasInJoinIsRemapped() {
524 $selectFields = 'cat.uid, cat_mm.uid_local, news.uid';
525 $fromTables = 'tt_news_cat AS cat' .
526 ' INNER JOIN tt_news_cat_mm AS cat_mm ON cat.uid = cat_mm.uid_foreign' .
527 ' INNER JOIN tt_news AS news ON news.uid = cat_mm.uid_local';
528 $whereClause = '1=1';
529 $groupBy = '';
530 $orderBy = '';
531
532 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
533 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
534
535 $expected = 'SELECT "cat"."cat_uid", "cat_mm"."local_uid", "news"."news_uid"';
536 $expected .= ' FROM "ext_tt_news_cat" AS "cat"';
537 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat"."cat_uid"="cat_mm"."uid_foreign"';
538 $expected .= ' INNER JOIN "ext_tt_news" AS "news" ON "news"."news_uid"="cat_mm"."local_uid"';
539 $expected .= ' WHERE 1 = 1';
540 $this->assertEquals($expected, $query);
541 }
542
543 /**
544 * @test
545 * @see http://bugs.typo3.org/view.php?id=14372
546 */
547 public function aliasRemappingWithInSubqueryDoesNotAffectMainQuery() {
548 $selectFields = 'foo.uid';
549 $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid';
550 $whereClause = 'tt_news_cat_mm.uid_foreign IN (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)';
551 $groupBy = '';
552 $orderBy = 'foo.uid';
553
554 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
555 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
556
557 $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
558 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"';
559 $expected .= ' WHERE "ext_tt_news_cat_mm"."uid_foreign" IN (';
560 $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0';
561 $expected .= ')';
562 $expected .= ' ORDER BY "foo"."news_uid"';
563 $this->assertEquals($expected, $query);
564 }
565
566 /**
567 * @test
568 * @see http://bugs.typo3.org/view.php?id=14372
569 */
570 public function aliasRemappingWithExistsSubqueryDoesNotAffectMainQuery() {
571 $selectFields = 'foo.uid';
572 $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid';
573 $whereClause = 'EXISTS (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)';
574 $groupBy = '';
575 $orderBy = 'foo.uid';
576
577 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
578 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
579
580 $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
581 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"';
582 $expected .= ' WHERE EXISTS (';
583 $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0';
584 $expected .= ')';
585 $expected .= ' ORDER BY "foo"."news_uid"';
586 $this->assertEquals($expected, $query);
587 }
588
589 /**
590 * @test
591 * @see http://bugs.typo3.org/view.php?id=14372
592 */
593 public function aliasRemappingSupportsNestedSubqueries() {
594 $selectFields = 'foo.uid';
595 $fromTables = 'tt_news AS foo';
596 $whereClause = 'uid IN (' .
597 'SELECT foobar.uid_local FROM tt_news_cat_mm AS foobar WHERE uid_foreign IN (' .
598 'SELECT uid FROM tt_news_cat WHERE deleted = 0' .
599 '))';
600 $groupBy = '';
601 $orderBy = '';
602
603 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
604 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
605
606 $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
607 $expected .= ' WHERE "news_uid" IN (';
608 $expected .= 'SELECT "foobar"."local_uid" FROM "ext_tt_news_cat_mm" AS "foobar" WHERE "uid_foreign" IN (';
609 $expected .= 'SELECT "cat_uid" FROM "ext_tt_news_cat" WHERE "deleted" = 0';
610 $expected .= ')';
611 $expected .= ')';
612 $this->assertEquals($expected, $query);
613 }
614
615 /**
616 * @test
617 * @see http://bugs.typo3.org/view.php?id=14372
618 */
619 public function remappingDoesNotMixUpAliasesInSubquery() {
620 $selectFields = 'pages.uid';
621 $fromTables = 'tt_news AS pages INNER JOIN tt_news_cat_mm AS cat_mm ON cat_mm.uid_local = pages.uid';
622 $whereClause = 'pages.pid IN (SELECT uid FROM pages WHERE deleted = 0 AND cat_mm.uid_local != 100)';
623 $groupBy = '';
624 $orderBy = 'pages.uid';
625
626 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
627 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
628
629 $expected = 'SELECT "pages"."news_uid" FROM "ext_tt_news" AS "pages"';
630 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat_mm"."local_uid"="pages"."news_uid"';
631 $expected .= ' WHERE "pages"."pid" IN (';
632 $expected .= 'SELECT "page_uid" FROM "my_pages" WHERE "deleted" = 0 AND "cat_mm"."local_uid" != 100';
633 $expected .= ')';
634 $expected .= ' ORDER BY "pages"."news_uid"';
635 $this->assertEquals($expected, $query);
636 }
637
638 /**
639 * @test
640 * @see http://bugs.typo3.org/view.php?id=14479
641 */
642 public function likeIsRemappedAccordingToFieldType() {
643 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
644 '*',
645 'tt_content',
646 'tt_content.bodytext LIKE \'foo%\''
647 ));
648 $expected = 'SELECT * FROM "tt_content" WHERE (dbms_lob.instr("tt_content"."bodytext", \'foo\',1,1) > 0)';
649 $this->assertEquals($expected, $select);
650
651 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
652 '*',
653 'fe_users',
654 'fe_users.usergroup LIKE \'2\''
655 ));
656 $expected = 'SELECT * FROM "fe_users" WHERE (instr("fe_users"."usergroup", \'2\',1,1) > 0)';
657 $this->assertEquals($expected, $select);
658 }
659
660 /**
661 * @test
662 * @see http://bugs.typo3.org/view.php?id=15253
663 */
664 public function notLikeIsRemappedAccordingToFieldType() {
665 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
666 '*',
667 'tt_content',
668 'tt_content.bodytext NOT LIKE \'foo%\''
669 ));
670 $expected = 'SELECT * FROM "tt_content" WHERE NOT (dbms_lob.instr("tt_content"."bodytext", \'foo\',1,1) > 0)';
671 $this->assertEquals($expected, $select);
672
673 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
674 '*',
675 'fe_users',
676 'fe_users.usergroup NOT LIKE \'2\''
677 ));
678 $expected = 'SELECT * FROM "fe_users" WHERE NOT (instr("fe_users"."usergroup", \'2\',1,1) > 0)';
679 $this->assertEquals($expected, $select);
680 }
681
682 /**
683 * @test
684 * @see http://bugs.typo3.org/view.php?id=14479
685 */
686 public function instrIsUsedForCEOnPages() {
687 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
688 '*',
689 'tt_content',
690 'uid IN (62) AND tt_content.deleted=0 AND tt_content.t3ver_state<=0' .
691 ' AND tt_content.hidden=0 AND (tt_content.starttime<=1264487640)' .
692 ' AND (tt_content.endtime=0 OR tt_content.endtime>1264487640)' .
693 ' AND (tt_content.fe_group=\'\' OR tt_content.fe_group IS NULL OR tt_content.fe_group=\'0\'' .
694 ' OR (tt_content.fe_group LIKE \'%,0,%\' OR tt_content.fe_group LIKE \'0,%\' OR tt_content.fe_group LIKE \'%,0\'' .
695 ' OR tt_content.fe_group=\'0\')' .
696 ' OR (tt_content.fe_group LIKE\'%,-1,%\' OR tt_content.fe_group LIKE \'-1,%\' OR tt_content.fe_group LIKE \'%,-1\'' .
697 ' OR tt_content.fe_group=\'-1\'))'
698 ));
699 $expected = 'SELECT * FROM "tt_content"';
700 $expected .= ' WHERE "uid" IN (62) AND "tt_content"."deleted" = 0 AND "tt_content"."t3ver_state" <= 0';
701 $expected .= ' AND "tt_content"."hidden" = 0 AND ("tt_content"."starttime" <= 1264487640)';
702 $expected .= ' AND ("tt_content"."endtime" = 0 OR "tt_content"."endtime" > 1264487640)';
703 $expected .= ' AND ("tt_content"."fe_group" = \'\' OR "tt_content"."fe_group" IS NULL OR "tt_content"."fe_group" = \'0\'';
704 $expected .= ' OR ((instr("tt_content"."fe_group", \',0,\',1,1) > 0)';
705 $expected .= ' OR (instr("tt_content"."fe_group", \'0,\',1,1) > 0)';
706 $expected .= ' OR (instr("tt_content"."fe_group", \',0\',1,1) > 0)';
707 $expected .= ' OR "tt_content"."fe_group" = \'0\')';
708 $expected .= ' OR ((instr("tt_content"."fe_group", \',-1,\',1,1) > 0)';
709 $expected .= ' OR (instr("tt_content"."fe_group", \'-1,\',1,1) > 0)';
710 $expected .= ' OR (instr("tt_content"."fe_group", \',-1\',1,1) > 0)';
711 $expected .= ' OR "tt_content"."fe_group" = \'-1\'))';
712 $this->assertEquals($expected, $select);
713 }
714
715 ///////////////////////////////////////
716 // Tests concerning DB management
717 ///////////////////////////////////////
718
719 /**
720 * @test
721 * @see http://bugs.typo3.org/view.php?id=12670
722 */
723 public function notNullableColumnsWithDefaultEmptyStringAreCreatedAsNullable() {
724 $parseString = '
725 CREATE TABLE tx_realurl_uniqalias (
726 uid int(11) NOT NULL auto_increment,
727 tstamp int(11) DEFAULT \'0\' NOT NULL,
728 tablename varchar(60) DEFAULT \'\' NOT NULL,
729 field_alias varchar(255) DEFAULT \'\' NOT NULL,
730 field_id varchar(60) DEFAULT \'\' NOT NULL,
731 value_alias varchar(255) DEFAULT \'\' NOT NULL,
732 value_id int(11) DEFAULT \'0\' NOT NULL,
733 lang int(11) DEFAULT \'0\' NOT NULL,
734 expire int(11) DEFAULT \'0\' NOT NULL,
735
736 PRIMARY KEY (uid),
737 KEY tablename (tablename),
738 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
739 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
740 );
741 ';
742
743 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
744 $this->assertTrue(is_array($components), 'Not an array: ' . $components);
745
746 $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
747 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
748 $this->assertEquals(4, count($sqlCommands));
749
750 $expected = $this->cleanSql('
751 CREATE TABLE "tx_realurl_uniqalias" (
752 "uid" NUMBER(20) NOT NULL,
753 "tstamp" NUMBER(20) DEFAULT 0,
754 "tablename" VARCHAR(60) DEFAULT \'\',
755 "field_alias" VARCHAR(255) DEFAULT \'\',
756 "field_id" VARCHAR(60) DEFAULT \'\',
757 "value_alias" VARCHAR(255) DEFAULT \'\',
758 "value_id" NUMBER(20) DEFAULT 0,
759 "lang" NUMBER(20) DEFAULT 0,
760 "expire" NUMBER(20) DEFAULT 0,
761 PRIMARY KEY ("uid")
762 )
763 ');
764 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
765 }
766
767 /**
768 * @test
769 * @see http://bugs.typo3.org/view.php?id=11142
770 * @see http://bugs.typo3.org/view.php?id=12670
771 */
772 public function defaultValueIsProperlyQuotedInCreateTable() {
773 $parseString = '
774 CREATE TABLE tx_test (
775 uid int(11) NOT NULL auto_increment,
776 lastname varchar(60) DEFAULT \'unknown\' NOT NULL,
777 firstname varchar(60) DEFAULT \'\' NOT NULL,
778 language varchar(2) NOT NULL,
779 tstamp int(11) DEFAULT \'0\' NOT NULL,
780
781 PRIMARY KEY (uid),
782 KEY name (name)
783 );
784 ';
785
786 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
787 $this->assertTrue(is_array($components), 'Not an array: ' . $components);
788
789 $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
790 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
791 $this->assertEquals(2, count($sqlCommands));
792
793 $expected = $this->cleanSql('
794 CREATE TABLE "tx_test" (
795 "uid" NUMBER(20) NOT NULL,
796 "lastname" VARCHAR(60) DEFAULT \'unknown\',
797 "firstname" VARCHAR(60) DEFAULT \'\',
798 "language" VARCHAR(2) DEFAULT \'\',
799 "tstamp" NUMBER(20) DEFAULT 0,
800 PRIMARY KEY ("uid")
801 )
802 ');
803 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
804 }
805
806 ///////////////////////////////////////
807 // Tests concerning subqueries
808 ///////////////////////////////////////
809
810 /**
811 * @test
812 * @see http://bugs.typo3.org/view.php?id=12758
813 */
814 public function inWhereClauseWithSubqueryIsProperlyQuoted() {
815 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
816 '*',
817 'tx_crawler_queue',
818 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)'
819 ));
820 $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "process_id" FROM "tx_crawler_process" WHERE "active" = 0 AND "deleted" = 0)';
821 $this->assertEquals($expected, $query);
822 }
823
824 /**
825 * @test
826 * @see http://bugs.typo3.org/view.php?id=12758
827 */
828 public function subqueryIsRemappedForInWhereClause() {
829 $selectFields = '*';
830 $fromTables = 'tx_crawler_queue';
831 $whereClause = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
832 $groupBy = '';
833 $orderBy = '';
834
835 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
836 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
837
838 $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "ps_id" FROM "tx_crawler_ps" WHERE "is_active" = 0 AND "deleted" = 0)';
839 $this->assertEquals($expected, $query);
840 }
841
842 /**
843 * @test
844 * @see http://bugs.typo3.org/view.php?id=12800
845 */
846 public function cachingFrameworkQueryIsSupported() {
847 $currentTime = time();
848 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery(
849 'cachingframework_cache_hash_tags',
850 'identifier IN (' .
851 $GLOBALS['TYPO3_DB']->SELECTsubquery(
852 'identifier',
853 'cachingframework_cache_pages',
854 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
855 ) .
856 ')'
857 ));
858 $expected = 'DELETE FROM "cachingframework_cache_hash_tags" WHERE "identifier" IN (';
859 $expected .= 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
860 $expected .= ')';
861 $this->assertEquals($expected, $query);
862 }
863
864 /**
865 * @test
866 * @see http://bugs.typo3.org/view.php?id=12800
867 */
868 public function cachingFrameworkQueryIsRemapped() {
869 $currentTime = time();
870 $table = 'cachingframework_cache_hash_tags';
871 $where = 'identifier IN (' .
872 $GLOBALS['TYPO3_DB']->SELECTsubquery(
873 'identifier',
874 'cachingframework_cache_pages',
875 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
876 ) .
877 ')';
878
879 // Perform remapping (as in method exec_DELETEquery)
880 if ($tableArray = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table)) {
881 // Where clause:
882 $whereParts = $GLOBALS['TYPO3_DB']->SQLparser->parseWhereClause($where);
883 $GLOBALS['TYPO3_DB']->_callRef('map_sqlParts', $whereParts, $tableArray[0]['table']);
884 $where = $GLOBALS['TYPO3_DB']->SQLparser->compileWhereClause($whereParts, FALSE);
885
886 // Table name:
887 if ($GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName']) {
888 $table = $GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName'];
889 }
890 }
891
892 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery($table, $where));
893 $expected = 'DELETE FROM "cf_cache_hash_tags" WHERE "identifier" IN (';
894 $expected .= 'SELECT "identifier" FROM "cf_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
895 $expected .= ')';
896 $this->assertEquals($expected, $query);
897 }
898
899 /**
900 * @test
901 * @see http://bugs.typo3.org/view.php?id=12758
902 */
903 public function existsWhereClauseIsProperlyQuoted() {
904 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
905 '*',
906 'tx_crawler_process',
907 'active = 0 AND NOT EXISTS (' .
908 $GLOBALS['TYPO3_DB']->SELECTsubquery(
909 '*',
910 'tx_crawler_queue',
911 'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)'
912 ) .
913 ')'
914 ));
915 $expected = 'SELECT * FROM "tx_crawler_process" WHERE "active" = 0 AND NOT EXISTS (';
916 $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_process"."process_id" AND "tx_crawler_queue"."exec_time" = 0';
917 $expected .= ')';
918 $this->assertEquals($expected, $query);
919 }
920
921 /**
922 * @test
923 * @see http://bugs.typo3.org/view.php?id=12758
924 */
925 public function subqueryIsRemappedForExistsWhereClause() {
926 $selectFields = '*';
927 $fromTables = 'tx_crawler_process';
928 $whereClause = 'active = 0 AND NOT EXISTS (' .
929 $GLOBALS['TYPO3_DB']->SELECTsubquery(
930 '*',
931 'tx_crawler_queue',
932 'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0'
933 ) .
934 ')';
935 $groupBy = '';
936 $orderBy = '';
937
938 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
939 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
940
941 $expected = 'SELECT * FROM "tx_crawler_ps" WHERE "is_active" = 0 AND NOT EXISTS (';
942 $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_ps"."ps_id" AND "tx_crawler_queue"."exec_time" = 0';
943 $expected .= ')';
944 $this->assertEquals($expected, $query);
945 }
946
947 ///////////////////////////////////////
948 // Tests concerning advanced operators
949 ///////////////////////////////////////
950
951 /**
952 * @test
953 * @see http://bugs.typo3.org/view.php?id=13135
954 */
955 public function caseStatementIsProperlyQuoted() {
956 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
957 'process_id, CASE active' .
958 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') .
959 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') .
960 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') .
961 ' END AS number',
962 'tx_crawler_process',
963 '1=1'
964 ));
965 $expected = 'SELECT "process_id", CASE "active" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" FROM "tx_crawler_process" WHERE 1 = 1';
966 $this->assertEquals($expected, $query);
967 }
968
969 /**
970 * @test
971 * @see http://bugs.typo3.org/view.php?id=13135
972 */
973 public function caseStatementIsProperlyRemapped() {
974 $selectFields = 'process_id, CASE active' .
975 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') .
976 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') .
977 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') .
978 ' END AS number';
979 $fromTables = 'tx_crawler_process';
980 $whereClause = '1=1';
981 $groupBy = '';
982 $orderBy = '';
983
984 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
985 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
986
987 $expected = 'SELECT "ps_id", CASE "is_active" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" ';
988 $expected .= 'FROM "tx_crawler_ps" WHERE 1 = 1';
989 $this->assertEquals($expected, $query);
990 }
991
992 /**
993 * @test
994 * @see http://bugs.typo3.org/view.php?id=13135
995 */
996 public function caseStatementWithExternalTableIsProperlyRemapped() {
997 $selectFields = 'process_id, CASE tt_news.uid' .
998 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tt_news') .
999 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tt_news') .
1000 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tt_news') .
1001 ' END AS number';
1002 $fromTables = 'tx_crawler_process, tt_news';
1003 $whereClause = '1=1';
1004 $groupBy = '';
1005 $orderBy = '';
1006
1007 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
1008 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
1009
1010 $expected = 'SELECT "ps_id", CASE "ext_tt_news"."news_uid" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" ';
1011 $expected .= 'FROM "tx_crawler_ps", "ext_tt_news" WHERE 1 = 1';
1012 $this->assertEquals($expected, $query);
1013 }
1014
1015 /**
1016 * @test
1017 * @see http://bugs.typo3.org/view.php?id=13134
1018 */
1019 public function locateStatementIsProperlyQuoted() {
1020 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
1021 '*, CASE WHEN' .
1022 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure)>0 THEN 2' .
1023 ' ELSE 1' .
1024 ' END AS scope',
1025 'tx_templavoila_tmplobj',
1026 '1=1'
1027 ));
1028 $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\') > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
1029 $this->assertEquals($expected, $query);
1030 }
1031
1032 /**
1033 * @test
1034 * @see http://bugs.typo3.org/view.php?id=13134
1035 */
1036 public function locateStatementWithPositionIsProperlyQuoted() {
1037 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
1038 '*, CASE WHEN' .
1039 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' .
1040 ' ELSE 1' .
1041 ' END AS scope',
1042 'tx_templavoila_tmplobj',
1043 '1=1'
1044 ));
1045 $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\', 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
1046 $this->assertEquals($expected, $query);
1047 }
1048
1049 /**
1050 * @test
1051 * @see http://bugs.typo3.org/view.php?id=6196
1052 */
1053 public function IfNullIsProperlyRemapped() {
1054 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
1055 '*',
1056 'tt_news_cat_mm',
1057 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)'
1058 ));
1059 $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE NVL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
1060 $this->assertEquals($expected, $query);
1061 }
1062 }
1063 ?>