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