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