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