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