Resynchronized DBAL after fixing blocking bug #15535
[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 * @test
213 * @see http://bugs.typo3.org/view.php?id=15535
214 */
215 public function groupConditionsAreProperlyTransformed() {
216 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
217 '*',
218 'pages',
219 'pid=0 AND pages.deleted=0 AND pages.hidden=0 AND pages.starttime<=1281620460 '
220 . 'AND (pages.endtime=0 OR pages.endtime>1281620460) AND NOT pages.t3ver_state>0 '
221 . 'AND pages.doktype<200 AND (pages.fe_group=\'\' OR pages.fe_group IS NULL OR '
222 . 'pages.fe_group=\'0\' OR FIND_IN_SET(\'0\',pages.fe_group) OR FIND_IN_SET(\'-1\',pages.fe_group))'
223 ));
224 $expected = 'SELECT * FROM "pages" WHERE "pid" = 0 AND "pages"."deleted" = 0 AND "pages"."hidden" = 0 '
225 . 'AND "pages"."starttime" <= 1281620460 AND ("pages"."endtime" = 0 OR "pages"."endtime" > 1281620460) '
226 . 'AND NOT "pages"."t3ver_state" > 0 AND "pages"."doktype" < 200 AND ("pages"."fe_group" = \'\' '
227 . 'OR "pages"."fe_group" IS NULL OR "pages"."fe_group" = \'0\' OR \',\'||"pages"."fe_group"||\',\' LIKE \'%,0,%\' '
228 . 'OR \',\'||"pages"."fe_group"||\',\' LIKE \'%,-1,%\')';
229 $this->assertEquals($expected, $query);
230 }
231
232 ///////////////////////////////////////
233 // Tests concerning quoting
234 ///////////////////////////////////////
235
236 /**
237 * @test
238 */
239 public function selectQueryIsProperlyQuoted() {
240 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
241 'uid', // select fields
242 'tt_content', // from table
243 'pid=1', // where clause
244 'cruser_id', // group by
245 'tstamp' // order by
246 ));
247 $expected = 'SELECT "uid" FROM "tt_content" WHERE "pid" = 1 GROUP BY "cruser_id" ORDER BY "tstamp"';
248 $this->assertEquals($expected, $query);
249 }
250
251 /**
252 * @test
253 * http://bugs.typo3.org/view.php?id=13504
254 */
255 public function truncateQueryIsProperlyQuoted() {
256 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->TRUNCATEquery('be_users'));
257 $expected = 'TRUNCATE TABLE "be_users"';
258 $this->assertEquals($expected, $query);
259 }
260
261 /**
262 * @test
263 * @see http://bugs.typo3.org/view.php?id=2438
264 */
265 public function distinctFieldIsProperlyQuoted() {
266 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
267 'COUNT(DISTINCT pid)', // select fields
268 'tt_content', // from table
269 '1=1' // where clause
270 ));
271 $expected = 'SELECT COUNT(DISTINCT "pid") FROM "tt_content" WHERE 1 = 1';
272 $this->assertEquals($expected, $query);
273 }
274
275 /**
276 * @test
277 * @see http://bugs.typo3.org/view.php?id=10411
278 * @remark Remapping is not expected here
279 */
280 public function multipleInnerJoinsAreProperlyQuoted() {
281 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
282 '*',
283 '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',
284 '1=1'
285 ));
286 $expected = 'SELECT * FROM "tt_news_cat"';
287 $expected .= ' INNER JOIN "tt_news_cat_mm" ON "tt_news_cat"."uid"="tt_news_cat_mm"."uid_foreign"';
288 $expected .= ' INNER JOIN "tt_news" ON "tt_news"."uid"="tt_news_cat_mm"."uid_local"';
289 $expected .= ' WHERE 1 = 1';
290 $this->assertEquals($expected, $query);
291 }
292
293 /**
294 * @test
295 * @see http://bugs.typo3.org/view.php?id=6198
296 */
297 public function stringsWithinInClauseAreProperlyQuoted() {
298 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
299 'COUNT(DISTINCT tx_dam.uid) AS count',
300 'tx_dam',
301 'tx_dam.pid IN (1) AND tx_dam.file_type IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND tx_dam.deleted = 0'
302 ));
303 $expected = 'SELECT COUNT(DISTINCT "tx_dam"."uid") AS "count" FROM "tx_dam"';
304 $expected .= ' WHERE "tx_dam"."pid" IN (1) AND "tx_dam"."file_type" IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND "tx_dam"."deleted" = 0';
305 $this->assertEquals($expected, $query);
306 }
307
308 /**
309 * @test
310 * @see http://bugs.typo3.org/view.php?id=12515
311 * @remark Remapping is not expected here
312 */
313 public function concatAfterLikeOperatorIsProperlyQuoted() {
314 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
315 '*',
316 'sys_refindex, tx_dam_file_tracking',
317 'sys_refindex.tablename = \'tx_dam_file_tracking\''
318 . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)'
319 ));
320 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
321 $expected .= ' AND (instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."file_path","tx_dam_file_tracking"."file_name"),1,1) > 0)';
322 $this->assertEquals($expected, $query);
323 }
324
325 /**
326 * @test
327 * @see http://bugs.typo3.org/view.php?id=12231
328 */
329 public function cachingFrameworkQueryIsProperlyQuoted() {
330 $currentTime = time();
331 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
332 'content',
333 'cache_hash',
334 'identifier = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('abbbabaf2d4b3f9a63e8dde781f1c106', 'cache_hash') .
335 ' AND (crdate + lifetime >= ' . $currentTime . ' OR lifetime = 0)'
336 ));
337 $expected = 'SELECT "content" FROM "cache_hash" WHERE "identifier" = \'abbbabaf2d4b3f9a63e8dde781f1c106\' AND ("crdate"+"lifetime" >= ' . $currentTime . ' OR "lifetime" = 0)';
338 $this->assertEquals($expected, $query);
339 }
340
341 /**
342 * @test
343 * @see http://bugs.typo3.org/view.php?id=12231
344 */
345 public function calculatedFieldsAreProperlyQuoted() {
346 $currentTime = time();
347 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
348 'identifier',
349 'cachingframework_cache_pages',
350 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
351 ));
352 $expected = 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
353 $this->assertEquals($expected, $query);
354 }
355
356 /**
357 * @test
358 * http://bugs.typo3.org/view.php?id=13422
359 */
360 public function numericColumnsAreNotQuoted() {
361 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
362 '1',
363 'be_users',
364 'username = \'_cli_scheduler\' AND admin = 0 AND be_users.deleted = 0'
365 ));
366 $expected = 'SELECT 1 FROM "be_users" WHERE "username" = \'_cli_scheduler\' AND "admin" = 0 AND "be_users"."deleted" = 0';
367 $this->assertEquals($expected, $query);
368 }
369
370 ///////////////////////////////////////
371 // Tests concerning remapping
372 ///////////////////////////////////////
373
374 /**
375 * @test
376 * @see http://bugs.typo3.org/view.php?id=10411
377 * @remark Remapping is expected here
378 */
379 public function tablesAndFieldsAreRemappedInMultipleJoins() {
380 $selectFields = '*';
381 $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';
382 $whereClause = '1=1';
383 $groupBy = '';
384 $orderBy = '';
385
386 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
387 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
388
389 $expected = 'SELECT * FROM "ext_tt_news_cat"';
390 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
391 $expected .= ' INNER JOIN "ext_tt_news" ON "ext_tt_news"."news_uid"="ext_tt_news_cat_mm"."local_uid"';
392 $expected .= ' WHERE 1 = 1';
393 $this->assertEquals($expected, $query);
394 }
395
396 /**
397 * @test
398 * @see http://bugs.typo3.org/view.php?id=6953
399 */
400 public function fieldWithinSqlFunctionIsRemapped() {
401 $selectFields = 'tstamp, script, SUM(exec_time) AS calc_sum, COUNT(*) AS qrycount, MAX(errorFlag) AS error';
402 $fromTables = 'tx_dbal_debuglog';
403 $whereClause = '1=1';
404 $groupBy = '';
405 $orderBy = '';
406
407 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
408 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
409
410 $expected = 'SELECT "tstamp", "script", SUM("exec_time") AS "calc_sum", COUNT(*) AS "qrycount", MAX("errorflag") AS "error" FROM "tx_dbal_debuglog" WHERE 1 = 1';
411 $this->assertEquals($expected, $query);
412 }
413
414 /**
415 * @test
416 * @see http://bugs.typo3.org/view.php?id=6953
417 */
418 public function tableAndFieldWithinSqlFunctionIsRemapped() {
419 $selectFields = 'MAX(tt_news_cat.uid) AS biggest_id';
420 $fromTables = 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign';
421 $whereClause = 'tt_news_cat_mm.uid_local > 50';
422 $groupBy = '';
423 $orderBy = '';
424
425 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
426 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
427
428 $expected = 'SELECT MAX("ext_tt_news_cat"."cat_uid") AS "biggest_id" FROM "ext_tt_news_cat"';
429 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
430 $expected .= ' WHERE "ext_tt_news_cat_mm"."local_uid" > 50';
431 $this->assertEquals($expected, $query);
432 }
433
434 /**
435 * @test
436 * @see http://bugs.typo3.org/view.php?id=12515
437 * @remark Remapping is expected here
438 */
439 public function concatAfterLikeOperatorIsRemapped() {
440 $selectFields = '*';
441 $fromTables = 'sys_refindex, tx_dam_file_tracking';
442 $whereClause = 'sys_refindex.tablename = \'tx_dam_file_tracking\''
443 . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)';
444 $groupBy = '';
445 $orderBy = '';
446
447 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
448 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
449
450 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
451 $expected .= ' AND (instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."path","tx_dam_file_tracking"."filename"),1,1) > 0)';
452 $this->assertEquals($expected, $query);
453 }
454
455 /**
456 * @test
457 * @see http://bugs.typo3.org/view.php?id=5708
458 */
459 public function fieldIsMappedOnRightSideOfAJoinCondition() {
460 $selectFields = 'cpg_categories.uid, cpg_categories.name';
461 $fromTables = 'cpg_categories, pages';
462 $whereClause = 'pages.uid = cpg_categories.pid AND pages.deleted = 0 AND 1 = 1';
463 $groupBy = '';
464 $orderBy = 'cpg_categories.pos';
465
466 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
467 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
468
469 $expected = 'SELECT "cpg_categories"."uid", "cpg_categories"."name" FROM "cpg_categories", "my_pages" WHERE "my_pages"."page_uid" = "cpg_categories"."page_id"';
470 $expected .= ' AND "my_pages"."deleted" = 0 AND 1 = 1 ORDER BY "cpg_categories"."pos"';
471 $this->assertEquals($expected, $query);
472 }
473
474 /**
475 * @test
476 * @see http://bugs.typo3.org/view.php?id=14372
477 */
478 public function fieldFromAliasIsRemapped() {
479 $selectFields = 'news.uid';
480 $fromTables = 'tt_news AS news';
481 $whereClause = 'news.uid = 1';
482 $groupBy = '';
483 $orderBy = '';
484
485 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
486 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
487
488 $expected = 'SELECT "news"."news_uid" FROM "ext_tt_news" AS "news" WHERE "news"."news_uid" = 1';
489 $this->assertEquals($expected, $query);
490 }
491
492 /**
493 * Trick here is that we already have a mapping for both table tt_news and table tt_news_cat
494 * (see tests/fixtures/oci8.config.php) which is used as alias name.
495 *
496 * @test
497 * @see http://bugs.typo3.org/view.php?id=14372
498 */
499 public function fieldFromAliasIsRemappedWithoutBeingTricked() {
500 $selectFields = 'tt_news_cat.uid';
501 $fromTables = 'tt_news AS tt_news_cat';
502 $whereClause = 'tt_news_cat.uid = 1';
503 $groupBy = '';
504 $orderBy = '';
505
506 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
507 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
508
509 $expected = 'SELECT "tt_news_cat"."news_uid" FROM "ext_tt_news" AS "tt_news_cat" WHERE "tt_news_cat"."news_uid" = 1';
510 $this->assertEquals($expected, $query);
511 }
512
513 /**
514 * @test
515 * @see http://bugs.typo3.org/view.php?id=14372
516 */
517 public function aliasRemappingDoesNotAlterFurtherQueries() {
518 $selectFields = 'foo.uid';
519 $fromTables = 'tt_news AS foo';
520 $whereClause = 'foo.uid = 1';
521 $groupBy = '';
522 $orderBy = '';
523
524 // First call to possibly alter (in memory) the mapping from localconf.php
525 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
526
527 $selectFields = 'uid';
528 $fromTables = 'foo';
529 $whereClause = 'uid = 1';
530 $groupBy = '';
531 $orderBy = '';
532
533 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
534 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
535
536 $expected = 'SELECT "uid" FROM "foo" WHERE "uid" = 1';
537 $this->assertEquals($expected, $query);
538 }
539
540 /**
541 * @test
542 * @see http://bugs.typo3.org/view.php?id=14372
543 */
544 public function fieldFromAliasInJoinIsRemapped() {
545 $selectFields = 'cat.uid, cat_mm.uid_local, news.uid';
546 $fromTables = 'tt_news_cat AS cat' .
547 ' INNER JOIN tt_news_cat_mm AS cat_mm ON cat.uid = cat_mm.uid_foreign' .
548 ' INNER JOIN tt_news AS news ON news.uid = cat_mm.uid_local';
549 $whereClause = '1=1';
550 $groupBy = '';
551 $orderBy = '';
552
553 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
554 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
555
556 $expected = 'SELECT "cat"."cat_uid", "cat_mm"."local_uid", "news"."news_uid"';
557 $expected .= ' FROM "ext_tt_news_cat" AS "cat"';
558 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat"."cat_uid"="cat_mm"."uid_foreign"';
559 $expected .= ' INNER JOIN "ext_tt_news" AS "news" ON "news"."news_uid"="cat_mm"."local_uid"';
560 $expected .= ' WHERE 1 = 1';
561 $this->assertEquals($expected, $query);
562 }
563
564 /**
565 * @test
566 * @see http://bugs.typo3.org/view.php?id=14372
567 */
568 public function aliasRemappingWithInSubqueryDoesNotAffectMainQuery() {
569 $selectFields = 'foo.uid';
570 $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid';
571 $whereClause = 'tt_news_cat_mm.uid_foreign IN (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)';
572 $groupBy = '';
573 $orderBy = 'foo.uid';
574
575 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
576 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
577
578 $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
579 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"';
580 $expected .= ' WHERE "ext_tt_news_cat_mm"."uid_foreign" IN (';
581 $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0';
582 $expected .= ')';
583 $expected .= ' ORDER BY "foo"."news_uid"';
584 $this->assertEquals($expected, $query);
585 }
586
587 /**
588 * @test
589 * @see http://bugs.typo3.org/view.php?id=14372
590 */
591 public function aliasRemappingWithExistsSubqueryDoesNotAffectMainQuery() {
592 $selectFields = 'foo.uid';
593 $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid';
594 $whereClause = 'EXISTS (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)';
595 $groupBy = '';
596 $orderBy = 'foo.uid';
597
598 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
599 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
600
601 $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
602 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"';
603 $expected .= ' WHERE EXISTS (';
604 $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0';
605 $expected .= ')';
606 $expected .= ' ORDER BY "foo"."news_uid"';
607 $this->assertEquals($expected, $query);
608 }
609
610 /**
611 * @test
612 * @see http://bugs.typo3.org/view.php?id=14372
613 */
614 public function aliasRemappingSupportsNestedSubqueries() {
615 $selectFields = 'foo.uid';
616 $fromTables = 'tt_news AS foo';
617 $whereClause = 'uid IN (' .
618 'SELECT foobar.uid_local FROM tt_news_cat_mm AS foobar WHERE uid_foreign IN (' .
619 'SELECT uid FROM tt_news_cat WHERE deleted = 0' .
620 '))';
621 $groupBy = '';
622 $orderBy = '';
623
624 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
625 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
626
627 $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
628 $expected .= ' WHERE "news_uid" IN (';
629 $expected .= 'SELECT "foobar"."local_uid" FROM "ext_tt_news_cat_mm" AS "foobar" WHERE "uid_foreign" IN (';
630 $expected .= 'SELECT "cat_uid" FROM "ext_tt_news_cat" WHERE "deleted" = 0';
631 $expected .= ')';
632 $expected .= ')';
633 $this->assertEquals($expected, $query);
634 }
635
636 /**
637 * @test
638 * @see http://bugs.typo3.org/view.php?id=14372
639 */
640 public function remappingDoesNotMixUpAliasesInSubquery() {
641 $selectFields = 'pages.uid';
642 $fromTables = 'tt_news AS pages INNER JOIN tt_news_cat_mm AS cat_mm ON cat_mm.uid_local = pages.uid';
643 $whereClause = 'pages.pid IN (SELECT uid FROM pages WHERE deleted = 0 AND cat_mm.uid_local != 100)';
644 $groupBy = '';
645 $orderBy = 'pages.uid';
646
647 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
648 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
649
650 $expected = 'SELECT "pages"."news_uid" FROM "ext_tt_news" AS "pages"';
651 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat_mm"."local_uid"="pages"."news_uid"';
652 $expected .= ' WHERE "pages"."pid" IN (';
653 $expected .= 'SELECT "page_uid" FROM "my_pages" WHERE "deleted" = 0 AND "cat_mm"."local_uid" != 100';
654 $expected .= ')';
655 $expected .= ' ORDER BY "pages"."news_uid"';
656 $this->assertEquals($expected, $query);
657 }
658
659 /**
660 * @test
661 * @see http://bugs.typo3.org/view.php?id=14479
662 */
663 public function likeIsRemappedAccordingToFieldType() {
664 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
665 '*',
666 'tt_content',
667 'tt_content.bodytext LIKE \'foo%\''
668 ));
669 $expected = 'SELECT * FROM "tt_content" WHERE (dbms_lob.instr("tt_content"."bodytext", \'foo\',1,1) > 0)';
670 $this->assertEquals($expected, $select);
671
672 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
673 '*',
674 'fe_users',
675 'fe_users.usergroup LIKE \'2\''
676 ));
677 $expected = 'SELECT * FROM "fe_users" WHERE (instr("fe_users"."usergroup", \'2\',1,1) > 0)';
678 $this->assertEquals($expected, $select);
679 }
680
681 /**
682 * @test
683 * @see http://bugs.typo3.org/view.php?id=15253
684 */
685 public function notLikeIsRemappedAccordingToFieldType() {
686 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
687 '*',
688 'tt_content',
689 'tt_content.bodytext NOT LIKE \'foo%\''
690 ));
691 $expected = 'SELECT * FROM "tt_content" WHERE NOT (dbms_lob.instr("tt_content"."bodytext", \'foo\',1,1) > 0)';
692 $this->assertEquals($expected, $select);
693
694 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
695 '*',
696 'fe_users',
697 'fe_users.usergroup NOT LIKE \'2\''
698 ));
699 $expected = 'SELECT * FROM "fe_users" WHERE NOT (instr("fe_users"."usergroup", \'2\',1,1) > 0)';
700 $this->assertEquals($expected, $select);
701 }
702
703 /**
704 * @test
705 * @see http://bugs.typo3.org/view.php?id=14479
706 */
707 public function instrIsUsedForCEOnPages() {
708 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
709 '*',
710 'tt_content',
711 'uid IN (62) AND tt_content.deleted=0 AND tt_content.t3ver_state<=0' .
712 ' AND tt_content.hidden=0 AND (tt_content.starttime<=1264487640)' .
713 ' AND (tt_content.endtime=0 OR tt_content.endtime>1264487640)' .
714 ' AND (tt_content.fe_group=\'\' OR tt_content.fe_group IS NULL OR tt_content.fe_group=\'0\'' .
715 ' OR (tt_content.fe_group LIKE \'%,0,%\' OR tt_content.fe_group LIKE \'0,%\' OR tt_content.fe_group LIKE \'%,0\'' .
716 ' OR tt_content.fe_group=\'0\')' .
717 ' OR (tt_content.fe_group LIKE\'%,-1,%\' OR tt_content.fe_group LIKE \'-1,%\' OR tt_content.fe_group LIKE \'%,-1\'' .
718 ' OR tt_content.fe_group=\'-1\'))'
719 ));
720 $expected = 'SELECT * FROM "tt_content"';
721 $expected .= ' WHERE "uid" IN (62) AND "tt_content"."deleted" = 0 AND "tt_content"."t3ver_state" <= 0';
722 $expected .= ' AND "tt_content"."hidden" = 0 AND ("tt_content"."starttime" <= 1264487640)';
723 $expected .= ' AND ("tt_content"."endtime" = 0 OR "tt_content"."endtime" > 1264487640)';
724 $expected .= ' AND ("tt_content"."fe_group" = \'\' OR "tt_content"."fe_group" IS NULL OR "tt_content"."fe_group" = \'0\'';
725 $expected .= ' OR ((instr("tt_content"."fe_group", \',0,\',1,1) > 0)';
726 $expected .= ' OR (instr("tt_content"."fe_group", \'0,\',1,1) > 0)';
727 $expected .= ' OR (instr("tt_content"."fe_group", \',0\',1,1) > 0)';
728 $expected .= ' OR "tt_content"."fe_group" = \'0\')';
729 $expected .= ' OR ((instr("tt_content"."fe_group", \',-1,\',1,1) > 0)';
730 $expected .= ' OR (instr("tt_content"."fe_group", \'-1,\',1,1) > 0)';
731 $expected .= ' OR (instr("tt_content"."fe_group", \',-1\',1,1) > 0)';
732 $expected .= ' OR "tt_content"."fe_group" = \'-1\'))';
733 $this->assertEquals($expected, $select);
734 }
735
736 ///////////////////////////////////////
737 // Tests concerning DB management
738 ///////////////////////////////////////
739
740 /**
741 * @test
742 * @see http://bugs.typo3.org/view.php?id=12670
743 */
744 public function notNullableColumnsWithDefaultEmptyStringAreCreatedAsNullable() {
745 $parseString = '
746 CREATE TABLE tx_realurl_uniqalias (
747 uid int(11) NOT NULL auto_increment,
748 tstamp int(11) DEFAULT \'0\' NOT NULL,
749 tablename varchar(60) DEFAULT \'\' NOT NULL,
750 field_alias varchar(255) DEFAULT \'\' NOT NULL,
751 field_id varchar(60) DEFAULT \'\' NOT NULL,
752 value_alias varchar(255) DEFAULT \'\' NOT NULL,
753 value_id int(11) DEFAULT \'0\' NOT NULL,
754 lang int(11) DEFAULT \'0\' NOT NULL,
755 expire int(11) DEFAULT \'0\' NOT NULL,
756
757 PRIMARY KEY (uid),
758 KEY tablename (tablename),
759 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
760 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
761 );
762 ';
763
764 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
765 $this->assertTrue(is_array($components), 'Not an array: ' . $components);
766
767 $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
768 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
769 $this->assertEquals(4, count($sqlCommands));
770
771 $expected = $this->cleanSql('
772 CREATE TABLE "tx_realurl_uniqalias" (
773 "uid" NUMBER(20) NOT NULL,
774 "tstamp" NUMBER(20) DEFAULT 0,
775 "tablename" VARCHAR(60) DEFAULT \'\',
776 "field_alias" VARCHAR(255) DEFAULT \'\',
777 "field_id" VARCHAR(60) DEFAULT \'\',
778 "value_alias" VARCHAR(255) DEFAULT \'\',
779 "value_id" NUMBER(20) DEFAULT 0,
780 "lang" NUMBER(20) DEFAULT 0,
781 "expire" NUMBER(20) DEFAULT 0,
782 PRIMARY KEY ("uid")
783 )
784 ');
785 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
786 }
787
788 /**
789 * @test
790 * @see http://bugs.typo3.org/view.php?id=11142
791 * @see http://bugs.typo3.org/view.php?id=12670
792 */
793 public function defaultValueIsProperlyQuotedInCreateTable() {
794 $parseString = '
795 CREATE TABLE tx_test (
796 uid int(11) NOT NULL auto_increment,
797 lastname varchar(60) DEFAULT \'unknown\' NOT NULL,
798 firstname varchar(60) DEFAULT \'\' NOT NULL,
799 language varchar(2) NOT NULL,
800 tstamp int(11) DEFAULT \'0\' NOT NULL,
801
802 PRIMARY KEY (uid),
803 KEY name (name)
804 );
805 ';
806
807 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
808 $this->assertTrue(is_array($components), 'Not an array: ' . $components);
809
810 $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
811 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
812 $this->assertEquals(2, count($sqlCommands));
813
814 $expected = $this->cleanSql('
815 CREATE TABLE "tx_test" (
816 "uid" NUMBER(20) NOT NULL,
817 "lastname" VARCHAR(60) DEFAULT \'unknown\',
818 "firstname" VARCHAR(60) DEFAULT \'\',
819 "language" VARCHAR(2) DEFAULT \'\',
820 "tstamp" NUMBER(20) DEFAULT 0,
821 PRIMARY KEY ("uid")
822 )
823 ');
824 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
825 }
826
827 ///////////////////////////////////////
828 // Tests concerning subqueries
829 ///////////////////////////////////////
830
831 /**
832 * @test
833 * @see http://bugs.typo3.org/view.php?id=12758
834 */
835 public function inWhereClauseWithSubqueryIsProperlyQuoted() {
836 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
837 '*',
838 'tx_crawler_queue',
839 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)'
840 ));
841 $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "process_id" FROM "tx_crawler_process" WHERE "active" = 0 AND "deleted" = 0)';
842 $this->assertEquals($expected, $query);
843 }
844
845 /**
846 * @test
847 * @see http://bugs.typo3.org/view.php?id=12758
848 */
849 public function subqueryIsRemappedForInWhereClause() {
850 $selectFields = '*';
851 $fromTables = 'tx_crawler_queue';
852 $whereClause = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
853 $groupBy = '';
854 $orderBy = '';
855
856 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
857 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
858
859 $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "ps_id" FROM "tx_crawler_ps" WHERE "is_active" = 0 AND "deleted" = 0)';
860 $this->assertEquals($expected, $query);
861 }
862
863 /**
864 * @test
865 * @see http://bugs.typo3.org/view.php?id=12800
866 */
867 public function cachingFrameworkQueryIsSupported() {
868 $currentTime = time();
869 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery(
870 'cachingframework_cache_hash_tags',
871 'identifier IN (' .
872 $GLOBALS['TYPO3_DB']->SELECTsubquery(
873 'identifier',
874 'cachingframework_cache_pages',
875 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
876 ) .
877 ')'
878 ));
879 $expected = 'DELETE FROM "cachingframework_cache_hash_tags" WHERE "identifier" IN (';
880 $expected .= 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
881 $expected .= ')';
882 $this->assertEquals($expected, $query);
883 }
884
885 /**
886 * @test
887 * @see http://bugs.typo3.org/view.php?id=12800
888 */
889 public function cachingFrameworkQueryIsRemapped() {
890 $currentTime = time();
891 $table = 'cachingframework_cache_hash_tags';
892 $where = 'identifier IN (' .
893 $GLOBALS['TYPO3_DB']->SELECTsubquery(
894 'identifier',
895 'cachingframework_cache_pages',
896 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
897 ) .
898 ')';
899
900 // Perform remapping (as in method exec_DELETEquery)
901 if ($tableArray = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table)) {
902 // Where clause:
903 $whereParts = $GLOBALS['TYPO3_DB']->SQLparser->parseWhereClause($where);
904 $GLOBALS['TYPO3_DB']->_callRef('map_sqlParts', $whereParts, $tableArray[0]['table']);
905 $where = $GLOBALS['TYPO3_DB']->SQLparser->compileWhereClause($whereParts, FALSE);
906
907 // Table name:
908 if ($GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName']) {
909 $table = $GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName'];
910 }
911 }
912
913 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery($table, $where));
914 $expected = 'DELETE FROM "cf_cache_hash_tags" WHERE "identifier" IN (';
915 $expected .= 'SELECT "identifier" FROM "cf_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
916 $expected .= ')';
917 $this->assertEquals($expected, $query);
918 }
919
920 /**
921 * @test
922 * @see http://bugs.typo3.org/view.php?id=12758
923 */
924 public function existsWhereClauseIsProperlyQuoted() {
925 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
926 '*',
927 'tx_crawler_process',
928 '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 ));
936 $expected = 'SELECT * FROM "tx_crawler_process" WHERE "active" = 0 AND NOT EXISTS (';
937 $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_process"."process_id" AND "tx_crawler_queue"."exec_time" = 0';
938 $expected .= ')';
939 $this->assertEquals($expected, $query);
940 }
941
942 /**
943 * @test
944 * @see http://bugs.typo3.org/view.php?id=12758
945 */
946 public function subqueryIsRemappedForExistsWhereClause() {
947 $selectFields = '*';
948 $fromTables = 'tx_crawler_process';
949 $whereClause = 'active = 0 AND NOT EXISTS (' .
950 $GLOBALS['TYPO3_DB']->SELECTsubquery(
951 '*',
952 'tx_crawler_queue',
953 'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0'
954 ) .
955 ')';
956 $groupBy = '';
957 $orderBy = '';
958
959 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
960 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
961
962 $expected = 'SELECT * FROM "tx_crawler_ps" WHERE "is_active" = 0 AND NOT EXISTS (';
963 $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_ps"."ps_id" AND "tx_crawler_queue"."exec_time" = 0';
964 $expected .= ')';
965 $this->assertEquals($expected, $query);
966 }
967
968 ///////////////////////////////////////
969 // Tests concerning advanced operators
970 ///////////////////////////////////////
971
972 /**
973 * @test
974 * @see http://bugs.typo3.org/view.php?id=13135
975 */
976 public function caseStatementIsProperlyQuoted() {
977 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
978 'process_id, CASE active' .
979 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') .
980 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') .
981 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') .
982 ' END AS number',
983 'tx_crawler_process',
984 '1=1'
985 ));
986 $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';
987 $this->assertEquals($expected, $query);
988 }
989
990 /**
991 * @test
992 * @see http://bugs.typo3.org/view.php?id=13135
993 */
994 public function caseStatementIsProperlyRemapped() {
995 $selectFields = 'process_id, CASE active' .
996 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') .
997 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') .
998 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') .
999 ' END AS number';
1000 $fromTables = 'tx_crawler_process';
1001 $whereClause = '1=1';
1002 $groupBy = '';
1003 $orderBy = '';
1004
1005 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
1006 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
1007
1008 $expected = 'SELECT "ps_id", CASE "is_active" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" ';
1009 $expected .= 'FROM "tx_crawler_ps" WHERE 1 = 1';
1010 $this->assertEquals($expected, $query);
1011 }
1012
1013 /**
1014 * @test
1015 * @see http://bugs.typo3.org/view.php?id=13135
1016 */
1017 public function caseStatementWithExternalTableIsProperlyRemapped() {
1018 $selectFields = 'process_id, CASE tt_news.uid' .
1019 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tt_news') .
1020 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tt_news') .
1021 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tt_news') .
1022 ' END AS number';
1023 $fromTables = 'tx_crawler_process, tt_news';
1024 $whereClause = '1=1';
1025 $groupBy = '';
1026 $orderBy = '';
1027
1028 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
1029 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
1030
1031 $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" ';
1032 $expected .= 'FROM "tx_crawler_ps", "ext_tt_news" WHERE 1 = 1';
1033 $this->assertEquals($expected, $query);
1034 }
1035
1036 /**
1037 * @test
1038 * @see http://bugs.typo3.org/view.php?id=13134
1039 */
1040 public function locateStatementIsProperlyQuoted() {
1041 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
1042 '*, CASE WHEN' .
1043 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure)>0 THEN 2' .
1044 ' ELSE 1' .
1045 ' END AS scope',
1046 'tx_templavoila_tmplobj',
1047 '1=1'
1048 ));
1049 $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\') > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
1050 $this->assertEquals($expected, $query);
1051 }
1052
1053 /**
1054 * @test
1055 * @see http://bugs.typo3.org/view.php?id=13134
1056 */
1057 public function locateStatementWithPositionIsProperlyQuoted() {
1058 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
1059 '*, CASE WHEN' .
1060 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' .
1061 ' ELSE 1' .
1062 ' END AS scope',
1063 'tx_templavoila_tmplobj',
1064 '1=1'
1065 ));
1066 $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\', 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
1067 $this->assertEquals($expected, $query);
1068 }
1069
1070 /**
1071 * @test
1072 * @see http://bugs.typo3.org/view.php?id=6196
1073 */
1074 public function IfNullIsProperlyRemapped() {
1075 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
1076 '*',
1077 'tt_news_cat_mm',
1078 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)'
1079 ));
1080 $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE NVL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
1081 $this->assertEquals($expected, $query);
1082 }
1083
1084 /**
1085 * @test
1086 * @see http://bugs.typo3.org/view.php?id=14985
1087 */
1088 public function findInSetIsProperlyRemapped() {
1089 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
1090 '*',
1091 'fe_users',
1092 'FIND_IN_SET(10, usergroup)'
1093 ));
1094 $expected = 'SELECT * FROM "fe_users" WHERE \',\'||"usergroup"||\',\' LIKE \'%,10,%\'';
1095 $this->assertEquals($expected, $query);
1096 }
1097
1098 /**
1099 * @test
1100 * @see http://bugs.typo3.org/view.php?id=14985
1101 */
1102 public function findInSetFieldIsProperlyRemapped() {
1103 $selectFields = 'fe_group';
1104 $fromTables = 'tt_news';
1105 $whereClause = 'FIND_IN_SET(10, fe_group)';
1106 $groupBy = '';
1107 $orderBy = '';
1108
1109 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
1110 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
1111
1112 $expected = 'SELECT "usergroup" FROM "ext_tt_news" WHERE \',\'||"ext_tt_news"."usergroup"||\',\' LIKE \'%,10,%\'';
1113 $this->assertEquals($expected, $query);
1114 }
1115
1116 /**
1117 * @test
1118 * @see http://bugs.typo3.org/view.php?id=14818
1119 */
1120 public function listQueryIsProperlyRemapped() {
1121 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
1122 '*',
1123 'fe_users',
1124 $GLOBALS['TYPO3_DB']->listQuery('usergroup', 10, 'fe_users')
1125 ));
1126 $expected = 'SELECT * FROM "fe_users" WHERE \',\'||"usergroup"||\',\' LIKE \'%,10,%\'';
1127 $this->assertEquals($expected, $query);
1128 }
1129
1130 /**
1131 * @test
1132 * @see http://bugs.typo3.org/view.php?id=12535
1133 */
1134 public function likeBinaryOperatorIsRemoved() {
1135 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
1136 '*',
1137 'tt_content',
1138 'bodytext LIKE BINARY \'test\''
1139 ));
1140 $expected = 'SELECT * FROM "tt_content" WHERE (dbms_lob.instr("bodytext", \'test\',1,1) > 0)';
1141 $this->assertEquals($expected, $query);
1142 }
1143 }
1144 ?>