[CLEANUP] Replace count with empty in multiple extensions, part IV
[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 protected 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 $insertCount = count($insert);
156 $this->assertEquals(4, $insertCount);
157 for ($i = 0; $i < $insertCount; $i++) {
158 foreach ($tableFields as $field) {
159 $this->assertTrue(isset($insert[$i][$field]), 'Could not find ' . $field . ' column');
160 }
161 }
162 }
163
164 /**
165 * @test
166 */
167 public function sqlForInsertWithMultipleRowsIsValid() {
168 $fields = array('uid', 'pid', 'title', 'body');
169 $rows = array(
170 array('1', '2', 'Title #1', 'Content #1'),
171 array('3', '4', 'Title #2', 'Content #2'),
172 array('5', '6', 'Title #3', 'Content #3')
173 );
174 $result = $this->subject->INSERTmultipleRows('tt_content', $fields, $rows);
175 $expected[0] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'1\', \'2\', \'Title #1\', \'Content #1\' )';
176 $expected[1] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'3\', \'4\', \'Title #2\', \'Content #2\' )';
177 $expected[2] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'5\', \'6\', \'Title #3\', \'Content #3\' )';
178 $resultCount = count($result);
179 $this->assertEquals(count($expected), $resultCount);
180 for ($i = 0; $i < $resultCount; $i++) {
181 $this->assertTrue(is_array($result[$i]), 'Expected array: ' . $result[$i]);
182 $this->assertEquals(1, count($result[$i]));
183 $this->assertEquals($expected[$i], $this->cleanSql($result[$i][0]));
184 }
185 }
186
187 /**
188 * @test
189 * @see http://forge.typo3.org/issues/23431
190 */
191 public function groupConditionsAreProperlyTransformed() {
192 $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))');
193 $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,%\')';
194 $this->assertEquals($expected, $this->cleanSql($result));
195 }
196
197 ///////////////////////////////////////
198 // Tests concerning quoting
199 ///////////////////////////////////////
200 /**
201 * @test
202 */
203 public function selectQueryIsProperlyQuoted() {
204 $result = $this->subject->SELECTquery('uid', 'tt_content', 'pid=1', 'cruser_id', 'tstamp');
205 $expected = 'SELECT "uid" FROM "tt_content" WHERE "pid" = 1 GROUP BY "cruser_id" ORDER BY "tstamp"';
206 $this->assertEquals($expected, $this->cleanSql($result));
207 }
208
209 /**
210 * @test
211 */
212 public function truncateQueryIsProperlyQuoted() {
213 $result = $this->subject->TRUNCATEquery('be_users');
214 $expected = 'TRUNCATE TABLE "be_users"';
215 $this->assertEquals($expected, $this->cleanSql($result));
216 }
217
218 /**
219 * @test
220 * @see http://forge.typo3.org/issues/15535
221 */
222 public function distinctFieldIsProperlyQuoted() {
223 $result = $this->subject->SELECTquery('COUNT(DISTINCT pid)', 'tt_content', '1=1');
224 $expected = 'SELECT COUNT(DISTINCT "pid") FROM "tt_content" WHERE 1 = 1';
225 $this->assertEquals($expected, $this->cleanSql($result));
226 }
227
228 /**
229 * @test
230 * @see http://forge.typo3.org/issues/19999
231 * @remark Remapping is not expected here
232 */
233 public function multipleInnerJoinsAreProperlyQuoted() {
234 $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');
235 $expected = 'SELECT * FROM "tt_news_cat"';
236 $expected .= ' INNER JOIN "tt_news_cat_mm" ON "tt_news_cat"."uid"="tt_news_cat_mm"."uid_foreign"';
237 $expected .= ' INNER JOIN "tt_news" ON "tt_news"."uid"="tt_news_cat_mm"."uid_local"';
238 $expected .= ' WHERE 1 = 1';
239 $this->assertEquals($expected, $this->cleanSql($result));
240 }
241
242 /**
243 * @test
244 * @see http://forge.typo3.org/issues/17554
245 */
246 public function stringsWithinInClauseAreProperlyQuoted() {
247 $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');
248 $expected = 'SELECT COUNT(DISTINCT "tx_dam"."uid") AS "count" FROM "tx_dam"';
249 $expected .= ' WHERE "tx_dam"."pid" IN (1) AND "tx_dam"."file_type" IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND "tx_dam"."deleted" = 0';
250 $this->assertEquals($expected, $this->cleanSql($result));
251 }
252
253 /**
254 * @test
255 * @see http://forge.typo3.org/issues/21502
256 * @remark Remapping is not expected here
257 */
258 public function concatAfterLikeOperatorIsProperlyQuoted() {
259 $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)');
260 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
261 $expected .= ' AND (instr(LOWER("sys_refindex"."ref_string"), concat("tx_dam_file_tracking"."file_path","tx_dam_file_tracking"."file_name"),1,1) > 0)';
262 $this->assertEquals($expected, $this->cleanSql($result));
263 }
264
265 /**
266 * @test
267 * @see http://forge.typo3.org/issues/21268
268 */
269 public function cachingFrameworkQueryIsProperlyQuoted() {
270 $currentTime = time();
271 $result = $this->subject->SELECTquery('content', 'cache_hash', 'identifier = ' . $this->subject->fullQuoteStr('abbbabaf2d4b3f9a63e8dde781f1c106', 'cache_hash') . ' AND (crdate + lifetime >= ' . $currentTime . ' OR lifetime = 0)');
272 $expected = 'SELECT "content" FROM "cache_hash" WHERE "identifier" = \'abbbabaf2d4b3f9a63e8dde781f1c106\' AND ("crdate"+"lifetime" >= ' . $currentTime . ' OR "lifetime" = 0)';
273 $this->assertEquals($expected, $this->cleanSql($result));
274 }
275
276 /**
277 * @test
278 * @see http://forge.typo3.org/issues/21268
279 */
280 public function calculatedFieldsAreProperlyQuoted() {
281 $currentTime = time();
282 $result = $this->subject->SELECTquery('identifier', 'cachingframework_cache_pages', 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0');
283 $expected = 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
284 $this->assertEquals($expected, $this->cleanSql($result));
285 }
286
287 /**
288 * @test
289 */
290 public function numericColumnsAreNotQuoted() {
291 $result = $this->subject->SELECTquery('1', 'be_users', 'username = \'_cli_scheduler\' AND admin = 0 AND be_users.deleted = 0');
292 $expected = 'SELECT 1 FROM "be_users" WHERE "username" = \'_cli_scheduler\' AND "admin" = 0 AND "be_users"."deleted" = 0';
293 $this->assertEquals($expected, $this->cleanSql($result));
294 }
295
296 ///////////////////////////////////////
297 // Tests concerning remapping
298 ///////////////////////////////////////
299 /**
300 * @test
301 * @see http://forge.typo3.org/issues/19999
302 * @remark Remapping is expected here
303 */
304 public function tablesAndFieldsAreRemappedInMultipleJoins() {
305 $selectFields = '*';
306 $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';
307 $whereClause = '1=1';
308 $groupBy = '';
309 $orderBy = '';
310 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
311
312 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
313 $expected = 'SELECT * FROM "ext_tt_news_cat"';
314 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
315 $expected .= ' INNER JOIN "ext_tt_news" ON "ext_tt_news"."news_uid"="ext_tt_news_cat_mm"."local_uid"';
316 $expected .= ' WHERE 1 = 1';
317 $this->assertEquals($expected, $this->cleanSql($result));
318 }
319
320 /**
321 * @test
322 * @see https://forge.typo3.org/issues/67067
323 */
324 public function tablesAreUnmappedInAdminGetTables() {
325 $handlerMock = $this->getMock('\ADODB_mock', array('MetaTables'), array(), '', FALSE);
326 $handlerMock->expects($this->any())->method('MetaTables')->will($this->returnValue(array('cf_cache_hash')));
327 $this->subject->handlerInstance['_DEFAULT'] = $handlerMock;
328
329 $actual = $this->subject->admin_get_tables();
330 $expected = array('cachingframework_cache_hash' => array('Name' => 'cachingframework_cache_hash'));
331 $this->assertSame($expected, $actual);
332 }
333
334 /**
335 * @test
336 * @see http://forge.typo3.org/issues/17918
337 */
338 public function fieldWithinSqlFunctionIsRemapped() {
339 $selectFields = 'tstamp, script, SUM(exec_time) AS calc_sum, COUNT(*) AS qrycount, MAX(errorFlag) AS error';
340 $fromTables = 'tx_dbal_debuglog';
341 $whereClause = '1=1';
342 $groupBy = '';
343 $orderBy = '';
344 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
345
346 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
347 $expected = 'SELECT "tstamp", "script", SUM("exec_time") AS "calc_sum", COUNT(*) AS "qrycount", MAX("errorflag") AS "error" FROM "tx_dbal_debuglog" WHERE 1 = 1';
348 $this->assertEquals($expected, $this->cleanSql($result));
349 }
350
351 /**
352 * @test
353 * @see http://forge.typo3.org/issues/17918
354 */
355 public function tableAndFieldWithinSqlFunctionIsRemapped() {
356 $selectFields = 'MAX(tt_news_cat.uid) AS biggest_id';
357 $fromTables = 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign';
358 $whereClause = 'tt_news_cat_mm.uid_local > 50';
359 $groupBy = '';
360 $orderBy = '';
361 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
362
363 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
364 $expected = 'SELECT MAX("ext_tt_news_cat"."cat_uid") AS "biggest_id" FROM "ext_tt_news_cat"';
365 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
366 $expected .= ' WHERE "ext_tt_news_cat_mm"."local_uid" > 50';
367 $this->assertEquals($expected, $this->cleanSql($result));
368 }
369
370 /**
371 * @test
372 * @see http://forge.typo3.org/issues/21502
373 * @remark Remapping is expected here
374 */
375 public function concatAfterLikeOperatorIsRemapped() {
376 $selectFields = '*';
377 $fromTables = 'sys_refindex, tx_dam_file_tracking';
378 $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)';
379 $groupBy = '';
380 $orderBy = '';
381 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
382
383 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
384 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
385 $expected .= ' AND (instr(LOWER("sys_refindex"."ref_string"), concat("tx_dam_file_tracking"."path","tx_dam_file_tracking"."filename"),1,1) > 0)';
386 $this->assertEquals($expected, $this->cleanSql($result));
387 }
388
389 /**
390 * @test
391 * @see http://forge.typo3.org/issues/17341
392 */
393 public function fieldIsMappedOnRightSideOfAJoinCondition() {
394 $selectFields = 'cpg_categories.uid, cpg_categories.name';
395 $fromTables = 'cpg_categories, pages';
396 $whereClause = 'pages.uid = cpg_categories.pid AND pages.deleted = 0 AND 1 = 1';
397 $groupBy = '';
398 $orderBy = 'cpg_categories.pos';
399 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
400
401 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
402 $expected = 'SELECT "cpg_categories"."uid", "cpg_categories"."name" FROM "cpg_categories", "my_pages" WHERE "my_pages"."page_uid" = "cpg_categories"."page_id"';
403 $expected .= ' AND "my_pages"."deleted" = 0 AND 1 = 1 ORDER BY "cpg_categories"."pos"';
404 $this->assertEquals($expected, $this->cleanSql($result));
405 }
406
407 /**
408 * @test
409 * @see http://forge.typo3.org/issues/22640
410 */
411 public function fieldFromAliasIsRemapped() {
412 $selectFields = 'news.uid';
413 $fromTables = 'tt_news AS news';
414 $whereClause = 'news.uid = 1';
415 $groupBy = '';
416 $orderBy = '';
417 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
418
419 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
420 $expected = 'SELECT "news"."news_uid" FROM "ext_tt_news" AS "news" WHERE "news"."news_uid" = 1';
421 $this->assertEquals($expected, $this->cleanSql($result));
422 }
423
424 /**
425 * Trick here is that we already have a mapping for both table tt_news and table tt_news_cat
426 * (see tests/fixtures/oci8.config.php) which is used as alias name.
427 *
428 * @test
429 * @see http://forge.typo3.org/issues/22640
430 */
431 public function fieldFromAliasIsRemappedWithoutBeingTricked() {
432 $selectFields = 'tt_news_cat.uid';
433 $fromTables = 'tt_news AS tt_news_cat';
434 $whereClause = 'tt_news_cat.uid = 1';
435 $groupBy = '';
436 $orderBy = '';
437 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
438
439 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
440 $expected = 'SELECT "tt_news_cat"."news_uid" FROM "ext_tt_news" AS "tt_news_cat" WHERE "tt_news_cat"."news_uid" = 1';
441 $this->assertEquals($expected, $this->cleanSql($result));
442 }
443
444 /**
445 * @test
446 * @see http://forge.typo3.org/issues/22640
447 */
448 public function fieldFromAliasInJoinIsRemapped() {
449 $selectFields = 'cat.uid, cat_mm.uid_local, news.uid';
450 $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';
451 $whereClause = '1=1';
452 $groupBy = '';
453 $orderBy = '';
454 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
455
456 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
457 $expected = 'SELECT "cat"."cat_uid", "cat_mm"."local_uid", "news"."news_uid"';
458 $expected .= ' FROM "ext_tt_news_cat" AS "cat"';
459 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat"."cat_uid"="cat_mm"."uid_foreign"';
460 $expected .= ' INNER JOIN "ext_tt_news" AS "news" ON "news"."news_uid"="cat_mm"."local_uid"';
461 $expected .= ' WHERE 1 = 1';
462
463 $this->assertEquals($expected, $this->cleanSql($result));
464 }
465
466 /**
467 * @test
468 * @see http://forge.typo3.org/issues/22640
469 */
470 public function aliasRemappingWithInSubqueryDoesNotAffectMainQuery() {
471 $selectFields = 'foo.uid';
472 $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid';
473 $whereClause = 'tt_news_cat_mm.uid_foreign IN (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)';
474 $groupBy = '';
475 $orderBy = 'foo.uid';
476 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
477 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
478 $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
479 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"';
480 $expected .= ' WHERE "ext_tt_news_cat_mm"."uid_foreign" IN (';
481 $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0';
482 $expected .= ')';
483 $expected .= ' ORDER BY "foo"."news_uid"';
484 $this->assertEquals($expected, $this->cleanSql($result));
485 }
486
487 /**
488 * @test
489 * @see http://forge.typo3.org/issues/22640
490 */
491 public function aliasRemappingWithExistsSubqueryDoesNotAffectMainQuery() {
492 $selectFields = 'foo.uid';
493 $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid';
494 $whereClause = 'EXISTS (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)';
495 $groupBy = '';
496 $orderBy = 'foo.uid';
497 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
498 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
499 $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
500 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"';
501 $expected .= ' WHERE EXISTS (';
502 $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0';
503 $expected .= ')';
504 $expected .= ' ORDER BY "foo"."news_uid"';
505 $this->assertEquals($expected, $this->cleanSql($result));
506 }
507
508 /**
509 * @test
510 * @see http://forge.typo3.org/issues/22640
511 */
512 public function aliasRemappingSupportsNestedSubqueries() {
513 $selectFields = 'foo.uid';
514 $fromTables = 'tt_news AS foo';
515 $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' . '))';
516 $groupBy = '';
517 $orderBy = '';
518 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
519
520 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
521 $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
522 $expected .= ' WHERE "news_uid" IN (';
523 $expected .= 'SELECT "foobar"."local_uid" FROM "ext_tt_news_cat_mm" AS "foobar" WHERE "uid_foreign" IN (';
524 $expected .= 'SELECT "cat_uid" FROM "ext_tt_news_cat" WHERE "deleted" = 0';
525 $expected .= ')';
526 $expected .= ')';
527 $this->assertEquals($expected, $this->cleanSql($result));
528 }
529
530 /**
531 * @test
532 * @see http://forge.typo3.org/issues/22640
533 */
534 public function remappingDoesNotMixUpAliasesInSubquery() {
535 $selectFields = 'pages.uid';
536 $fromTables = 'tt_news AS pages INNER JOIN tt_news_cat_mm AS cat_mm ON cat_mm.uid_local = pages.uid';
537 $whereClause = 'pages.pid IN (SELECT uid FROM pages WHERE deleted = 0 AND cat_mm.uid_local != 100)';
538 $groupBy = '';
539 $orderBy = 'pages.uid';
540 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
541
542 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
543 $expected = 'SELECT "pages"."news_uid" FROM "ext_tt_news" AS "pages"';
544 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat_mm"."local_uid"="pages"."news_uid"';
545 $expected .= ' WHERE "pages"."pid" IN (';
546 $expected .= 'SELECT "page_uid" FROM "my_pages" WHERE "deleted" = 0 AND "cat_mm"."local_uid" != 100';
547 $expected .= ')';
548 $expected .= ' ORDER BY "pages"."news_uid"';
549 $this->assertEquals($expected, $this->cleanSql($result));
550 }
551
552 /**
553 * @test
554 * @see http://forge.typo3.org/issues/22716
555 */
556 public function likeIsRemappedAccordingToFieldTypeWithString() {
557 $this->subject->cache_fieldType['tt_content']['bodytext']['metaType'] = 'B';
558 $result = $this->subject->SELECTquery('*', 'tt_content', 'tt_content.bodytext LIKE \'foo%\'');
559 $expected = 'SELECT * FROM "tt_content" WHERE (dbms_lob.instr(LOWER("tt_content"."bodytext"), \'foo\',1,1) > 0)';
560 $this->assertEquals($expected, $this->cleanSql($result));
561 }
562
563 /**
564 * @test
565 * @see http://forge.typo3.org/issues/22716
566 */
567 public function likeIsRemappedAccordingToFieldTypeWithInteger() {
568 $this->subject->cache_fieldType['tt_content']['bodytext']['metaType'] = 'B';
569 $result = $this->subject->SELECTquery('*', 'fe_users', 'fe_users.usergroup LIKE \'2\'');
570 $expected = 'SELECT * FROM "fe_users" WHERE (instr(LOWER("fe_users"."usergroup"), \'2\',1,1) > 0)';
571 $this->assertEquals($expected, $this->cleanSql($result));
572 }
573
574 /**
575 * @test
576 * @see http://forge.typo3.org/issues/23282
577 */
578 public function notLikeIsRemappedAccordingToFieldTypeWithString() {
579 $this->subject->cache_fieldType['tt_content']['bodytext']['metaType'] = 'B';
580 $result = $this->subject->SELECTquery('*', 'tt_content', 'tt_content.bodytext NOT LIKE \'foo%\'');
581 $expected = 'SELECT * FROM "tt_content" WHERE NOT (dbms_lob.instr(LOWER("tt_content"."bodytext"), \'foo\',1,1) > 0)';
582 $this->assertEquals($expected, $this->cleanSql($result));
583 }
584
585 /**
586 * @test
587 * @see http://forge.typo3.org/issues/23282
588 */
589 public function notLikeIsRemappedAccordingToFieldTypeWithInteger() {
590 $this->subject->cache_fieldType['tt_content']['bodytext']['metaType'] = 'B';
591 $result = $this->subject->SELECTquery('*', 'fe_users', 'fe_users.usergroup NOT LIKE \'2\'');
592 $expected = 'SELECT * FROM "fe_users" WHERE NOT (instr(LOWER("fe_users"."usergroup"), \'2\',1,1) > 0)';
593 $this->assertEquals($expected, $this->cleanSql($result));
594 }
595
596 /**
597 * @test
598 * @see http://forge.typo3.org/issues/22716
599 */
600 public function instrIsUsedForCEOnPages() {
601 $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\'))');
602 $expected = 'SELECT * FROM "tt_content"';
603 $expected .= ' WHERE "uid" IN (62) AND "tt_content"."deleted" = 0 AND "tt_content"."t3ver_state" <= 0';
604 $expected .= ' AND "tt_content"."hidden" = 0 AND ("tt_content"."starttime" <= 1264487640)';
605 $expected .= ' AND ("tt_content"."endtime" = 0 OR "tt_content"."endtime" > 1264487640)';
606 $expected .= ' AND ("tt_content"."fe_group" = \'\' OR "tt_content"."fe_group" IS NULL OR "tt_content"."fe_group" = \'0\'';
607 $expected .= ' OR ((instr(LOWER("tt_content"."fe_group"), \',0,\',1,1) > 0)';
608 $expected .= ' OR (instr(LOWER("tt_content"."fe_group"), \'0,\',1,1) > 0)';
609 $expected .= ' OR (instr(LOWER("tt_content"."fe_group"), \',0\',1,1) > 0)';
610 $expected .= ' OR "tt_content"."fe_group" = \'0\')';
611 $expected .= ' OR ((instr(LOWER("tt_content"."fe_group"), \',-1,\',1,1) > 0)';
612 $expected .= ' OR (instr(LOWER("tt_content"."fe_group"), \'-1,\',1,1) > 0)';
613 $expected .= ' OR (instr(LOWER("tt_content"."fe_group"), \',-1\',1,1) > 0)';
614 $expected .= ' OR "tt_content"."fe_group" = \'-1\'))';
615 $this->assertEquals($expected, $this->cleanSql($result));
616 }
617
618 ///////////////////////////////////////
619 // Tests concerning DB management
620 ///////////////////////////////////////
621 /**
622 * @test
623 * @see http://forge.typo3.org/issues/21616
624 */
625 public function notNullableColumnsWithDefaultEmptyStringAreCreatedAsNullable() {
626 $parseString = '
627 CREATE TABLE tx_realurl_uniqalias (
628 uid int(11) NOT NULL auto_increment,
629 tstamp int(11) DEFAULT \'0\' NOT NULL,
630 tablename varchar(60) DEFAULT \'\' NOT NULL,
631 field_alias varchar(255) DEFAULT \'\' NOT NULL,
632 field_id varchar(60) DEFAULT \'\' NOT NULL,
633 value_alias varchar(255) DEFAULT \'\' NOT NULL,
634 value_id int(11) DEFAULT \'0\' NOT NULL,
635 lang int(11) DEFAULT \'0\' NOT NULL,
636 expire int(11) DEFAULT \'0\' NOT NULL,
637
638 PRIMARY KEY (uid),
639 KEY tablename (tablename),
640 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
641 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
642 );
643 ';
644 $components = $this->subject->SQLparser->_callRef('parseCREATETABLE', $parseString);
645 $this->assertTrue(is_array($components), 'Not an array: ' . $components);
646 $sqlCommands = $this->subject->SQLparser->_call('compileCREATETABLE', $components);
647 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
648 $this->assertEquals(6, count($sqlCommands));
649 $expected = $this->cleanSql('
650 CREATE TABLE "tx_realurl_uniqalias" (
651 "uid" NUMBER(20) NOT NULL,
652 "tstamp" NUMBER(20) DEFAULT 0,
653 "tablename" VARCHAR(60) DEFAULT \'\',
654 "field_alias" VARCHAR(255) DEFAULT \'\',
655 "field_id" VARCHAR(60) DEFAULT \'\',
656 "value_alias" VARCHAR(255) DEFAULT \'\',
657 "value_id" NUMBER(20) DEFAULT 0,
658 "lang" NUMBER(20) DEFAULT 0,
659 "expire" NUMBER(20) DEFAULT 0,
660 PRIMARY KEY ("uid")
661 )
662 ');
663 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
664 }
665
666 /**
667 * @test
668 * @see http://forge.typo3.org/issues/20470
669 * @see http://forge.typo3.org/issues/21616
670 */
671 public function defaultValueIsProperlyQuotedInCreateTable() {
672 $parseString = '
673 CREATE TABLE tx_test (
674 uid int(11) NOT NULL auto_increment,
675 lastname varchar(60) DEFAULT \'unknown\' NOT NULL,
676 firstname varchar(60) DEFAULT \'\' NOT NULL,
677 language varchar(2) NOT NULL,
678 tstamp int(11) DEFAULT \'0\' NOT NULL,
679
680 PRIMARY KEY (uid),
681 KEY name (name)
682 );
683 ';
684 $components = $this->subject->SQLparser->_callRef('parseCREATETABLE', $parseString);
685 $this->assertTrue(is_array($components), 'Not an array: ' . $components);
686 $sqlCommands = $this->subject->SQLparser->_call('compileCREATETABLE', $components);
687 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
688 $this->assertEquals(4, count($sqlCommands));
689 $expected = $this->cleanSql('
690 CREATE TABLE "tx_test" (
691 "uid" NUMBER(20) NOT NULL,
692 "lastname" VARCHAR(60) DEFAULT \'unknown\',
693 "firstname" VARCHAR(60) DEFAULT \'\',
694 "language" VARCHAR(2) DEFAULT \'\',
695 "tstamp" NUMBER(20) DEFAULT 0,
696 PRIMARY KEY ("uid")
697 )
698 ');
699 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
700 }
701
702 ///////////////////////////////////////
703 // Tests concerning subqueries
704 ///////////////////////////////////////
705 /**
706 * @test
707 * @see http://forge.typo3.org/issues/21688
708 */
709 public function inWhereClauseWithSubqueryIsProperlyQuoted() {
710 $result = $this->subject->SELECTquery('*', 'tx_crawler_queue', 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)');
711 $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "process_id" FROM "tx_crawler_process" WHERE "active" = 0 AND "deleted" = 0)';
712 $this->assertEquals($expected, $this->cleanSql($result));
713 }
714
715 /**
716 * @test
717 * @see http://forge.typo3.org/issues/21688
718 */
719 public function subqueryIsRemappedForInWhereClause() {
720 $selectFields = '*';
721 $fromTables = 'tx_crawler_queue';
722 $whereClause = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
723 $groupBy = '';
724 $orderBy = '';
725 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
726 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
727 $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "ps_id" FROM "tx_crawler_ps" WHERE "is_active" = 0 AND "deleted" = 0)';
728 $this->assertEquals($expected, $this->cleanSql($result));
729 }
730
731 /**
732 * @test
733 * @see http://forge.typo3.org/issues/21718
734 */
735 public function cachingFrameworkQueryIsSupported() {
736 $currentTime = time();
737 $result = $this->subject->DELETEquery('cachingframework_cache_hash_tags', 'identifier IN (' . $this->subject->SELECTsubquery('identifier', 'cachingframework_cache_pages', ('crdate + lifetime < ' . $currentTime . ' AND lifetime > 0')) . ')');
738 $expected = 'DELETE FROM "cachingframework_cache_hash_tags" WHERE "identifier" IN (';
739 $expected .= 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
740 $expected .= ')';
741 $this->assertEquals($expected, $this->cleanSql($result));
742 }
743
744 /**
745 * @test
746 * @see http://forge.typo3.org/issues/21718
747 */
748 public function cachingFrameworkQueryIsRemapped() {
749 $currentTime = time();
750 $table = 'cachingframework_cache_hash_tags';
751 $where = 'identifier IN (' . $this->subject->SELECTsubquery('identifier', 'cachingframework_cache_pages', ('crdate + lifetime < ' . $currentTime . ' AND lifetime > 0')) . ')';
752
753 // Perform remapping (as in method exec_DELETEquery)
754 $tableArray = $this->subject->_call('map_needMapping', $table);
755 // Where clause:
756 $whereParts = $this->subject->SQLparser->parseWhereClause($where);
757 $this->subject->_callRef('map_sqlParts', $whereParts, $tableArray[0]['table']);
758 $where = $this->subject->SQLparser->compileWhereClause($whereParts, FALSE);
759 // Table name:
760 if ($this->subject->mapping[$table]['mapTableName']) {
761 $table = $this->subject->mapping[$table]['mapTableName'];
762 }
763
764 $result = $this->subject->DELETEquery($table, $where);
765 $expected = 'DELETE FROM "cf_cache_hash_tags" WHERE "identifier" IN (';
766 $expected .= 'SELECT "identifier" FROM "cf_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
767 $expected .= ')';
768 $this->assertEquals($expected, $this->cleanSql($result));
769 }
770
771 /**
772 * @test
773 * @see http://forge.typo3.org/issues/21688
774 */
775 public function existsWhereClauseIsProperlyQuoted() {
776 $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)') . ')');
777 $expected = 'SELECT * FROM "tx_crawler_process" WHERE "active" = 0 AND NOT EXISTS (';
778 $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_process"."process_id" AND "tx_crawler_queue"."exec_time" = 0';
779 $expected .= ')';
780 $this->assertEquals($expected, $this->cleanSql($result));
781 }
782
783 /**
784 * @test
785 * @see http://forge.typo3.org/issues/21688
786 */
787 public function subqueryIsRemappedForExistsWhereClause() {
788 $selectFields = '*';
789 $fromTables = 'tx_crawler_process';
790 $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') . ')';
791 $groupBy = '';
792 $orderBy = '';
793 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
794
795 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
796 $expected = 'SELECT * FROM "tx_crawler_ps" WHERE "is_active" = 0 AND NOT EXISTS (';
797 $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_ps"."ps_id" AND "tx_crawler_queue"."exec_time" = 0';
798 $expected .= ')';
799 $this->assertEquals($expected, $this->cleanSql($result));
800 }
801
802 ///////////////////////////////////////
803 // Tests concerning advanced operators
804 ///////////////////////////////////////
805 /**
806 * @test
807 * @see http://forge.typo3.org/issues/21903
808 */
809 public function caseStatementIsProperlyQuoted() {
810 $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');
811 $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';
812
813 $this->assertEquals($expected, $this->cleanSql($result));
814 }
815
816 /**
817 * @test
818 * @see http://forge.typo3.org/issues/21903
819 */
820 public function caseStatementIsProperlyRemapped() {
821 $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';
822 $fromTables = 'tx_crawler_process';
823 $whereClause = '1=1';
824 $groupBy = '';
825 $orderBy = '';
826 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
827
828 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
829 $expected = 'SELECT "ps_id", CASE "is_active" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" ';
830 $expected .= 'FROM "tx_crawler_ps" WHERE 1 = 1';
831 $this->assertEquals($expected, $this->cleanSql($result));
832 }
833
834 /**
835 * @test
836 * @see http://forge.typo3.org/issues/21903
837 */
838 public function caseStatementWithExternalTableIsProperlyRemapped() {
839 $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';
840 $fromTables = 'tx_crawler_process, tt_news';
841 $whereClause = '1=1';
842 $groupBy = '';
843 $orderBy = '';
844 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
845
846 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
847 $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" ';
848 $expected .= 'FROM "tx_crawler_ps", "ext_tt_news" WHERE 1 = 1';
849 $this->assertEquals($expected, $this->cleanSql($result));
850 }
851
852 /**
853 * @test
854 * @see http://forge.typo3.org/issues/21902
855 */
856 public function locateStatementIsProperlyQuoted() {
857 $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');
858 $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\') > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
859 $this->assertEquals($expected, $this->cleanSql($result));
860 }
861
862 /**
863 * @test
864 * @see http://forge.typo3.org/issues/21902
865 */
866 public function locateStatementWithPositionIsProperlyQuoted() {
867 $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');
868 $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\', 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
869 $this->assertEquals($expected, $this->cleanSql($result));
870 }
871
872 /**
873 * @test
874 * @see http://forge.typo3.org/issues/17552
875 */
876 public function IfNullIsProperlyRemapped() {
877 $result = $this->subject->SELECTquery('*', 'tt_news_cat_mm', 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)');
878 $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE NVL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
879 $this->assertEquals($expected, $this->cleanSql($result));
880 }
881
882 /**
883 * @test
884 * @see http://forge.typo3.org/issues/23087
885 */
886 public function findInSetIsProperlyRemapped() {
887 $result = $this->subject->SELECTquery('*', 'fe_users', 'FIND_IN_SET(10, usergroup)');
888 $expected = 'SELECT * FROM "fe_users" WHERE \',\'||"usergroup"||\',\' LIKE \'%,10,%\'';
889 $this->assertEquals($expected, $this->cleanSql($result));
890 }
891
892 /**
893 * @test
894 * @see http://forge.typo3.org/issues/23087
895 */
896 public function findInSetFieldIsProperlyRemapped() {
897 $selectFields = 'fe_group';
898 $fromTables = 'tt_news';
899 $whereClause = 'FIND_IN_SET(10, fe_group)';
900 $groupBy = '';
901 $orderBy = '';
902 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
903
904 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
905 $expected = 'SELECT "usergroup" FROM "ext_tt_news" WHERE \',\'||"ext_tt_news"."usergroup"||\',\' LIKE \'%,10,%\'';
906 $this->assertEquals($expected, $this->cleanSql($result));
907 }
908
909 /**
910 * @test
911 * @see http://forge.typo3.org/issues/22959
912 */
913 public function listQueryIsProperlyRemapped() {
914 $result = $this->subject->SELECTquery('*', 'fe_users', $this->subject->listQuery('usergroup', 10, 'fe_users'));
915 $expected = 'SELECT * FROM "fe_users" WHERE \',\'||"usergroup"||\',\' LIKE \'%,10,%\'';
916 $this->assertEquals($expected, $this->cleanSql($result));
917 }
918
919 /**
920 * @test
921 * @see http://forge.typo3.org/issues/21514
922 */
923 public function likeBinaryOperatorIsRemoved() {
924 $result = $this->subject->SELECTquery('*', 'tt_content', 'bodytext LIKE BINARY \'test\'');
925 $expected = 'SELECT * FROM "tt_content" WHERE (dbms_lob.instr("bodytext", \'test\',1,1) > 0)';
926 $this->assertEquals($expected, $this->cleanSql($result));
927 }
928
929 /**
930 * @test
931 */
932 public function expressionListWithNotInIsConcatenatedWithAnd() {
933 $listMaxExpressions = 1000;
934
935 $mockSpecificsOci8 = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\Specifics\Oci8Specifics::class, array(), array(), '', FALSE);
936 $mockSpecificsOci8->expects($this->any())->method('getSpecific')->will($this->returnValue($listMaxExpressions));
937
938 $items = range(0, 1250);
939 $where = 'uid NOT IN(' . implode(',', $items) . ')';
940 $result = $this->subject->SELECTquery('*', 'tt_content', $where);
941
942 $chunks = array_chunk($items, $listMaxExpressions);
943 $whereExpr = array();
944 foreach ($chunks as $chunk) {
945 $whereExpr[] = '"uid" NOT IN (' . implode(',', $chunk) . ')';
946 }
947
948 /**
949 * $expectedWhere:
950 * (
951 * "uid" NOT IN (1,2,3,4,...,1000)
952 * AND "uid" NOT IN (1001,1002,...,1250)
953 * )
954 */
955 $expectedWhere = '(' . implode(' AND ', $whereExpr) . ')';
956 $expectedQuery = 'SELECT * FROM "tt_content" WHERE ' . $expectedWhere;
957 $this->assertEquals($expectedQuery, $this->cleanSql($result));
958 }
959
960 /**
961 * @test
962 */
963 public function expressionListWithInIsConcatenatedWithOr() {
964 $listMaxExpressions = 1000;
965
966 $mockSpecificsOci8 = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\Specifics\Oci8Specifics::class, array(), array(), '', FALSE);
967 $mockSpecificsOci8->expects($this->any())->method('getSpecific')->will($this->returnValue($listMaxExpressions));
968
969 $items = range(0, 1250);
970 $where = 'uid IN(' . implode(',', $items) . ')';
971 $result = $this->subject->SELECTquery('*', 'tt_content', $where);
972
973 $chunks = array_chunk($items, $listMaxExpressions);
974 $whereExpr = array();
975 foreach ($chunks as $chunk) {
976 $whereExpr[] = '"uid" IN (' . implode(',', $chunk) . ')';
977 }
978
979 /**
980 * $expectedWhere:
981 * (
982 * "uid" IN (1,2,3,4,...,1000)
983 * OR "uid" IN (1001,1002,...,1250)
984 * )
985 */
986 $expectedWhere = '(' . implode(' OR ', $whereExpr) . ')';
987 $expectedQuery = 'SELECT * FROM "tt_content" WHERE ' . $expectedWhere;
988 $this->assertEquals($expectedQuery, $this->cleanSql($result));
989 }
990
991 /**
992 * @test
993 */
994 public function expressionListIsUnchanged() {
995 $listMaxExpressions = 1000;
996
997 $mockSpecificsOci8 = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\Specifics\Oci8Specifics::class, array(), array(), '', FALSE);
998 $mockSpecificsOci8->expects($this->any())->method('getSpecific')->will($this->returnValue($listMaxExpressions));
999
1000 $result = $this->subject->SELECTquery('*', 'tt_content', 'uid IN (0,1,2,3,4,5,6,7,8,9,10)');
1001
1002 $expectedQuery = 'SELECT * FROM "tt_content" WHERE "uid" IN (0,1,2,3,4,5,6,7,8,9,10)';
1003 $this->assertEquals($expectedQuery, $this->cleanSql($result));
1004 }
1005
1006 /**
1007 * @test
1008 */
1009 public function expressionListBracesAreSetCorrectly() {
1010 $listMaxExpressions = 1000;
1011
1012 $mockSpecificsOci8 = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\Specifics\Oci8Specifics::class, array(), array(), '', FALSE);
1013 $mockSpecificsOci8->expects($this->any())->method('getSpecific')->will($this->returnValue($listMaxExpressions));
1014
1015 $items = range(0, 1250);
1016 $where = 'uid = 1981 AND uid IN(' . implode(',', $items) . ') OR uid = 42';
1017 $result = $this->subject->SELECTquery('uid, pid', 'tt_content', $where);
1018
1019 $chunks = array_chunk($items, $listMaxExpressions);
1020 $whereExpr = array();
1021 foreach ($chunks as $chunk) {
1022 $whereExpr[] = '"uid" IN (' . implode(',', $chunk) . ')';
1023 }
1024
1025 /**
1026 * $expectedWhere:
1027 * "uid" = 1981 AND (
1028 * "uid" IN (1,2,3,4,...,1000)
1029 * OR "uid" IN (1001,1002,...,1250)
1030 * ) OR "uid" = 42
1031 */
1032 $expectedWhere = '"uid" = 1981 AND (' . implode(' OR ', $whereExpr) . ') OR "uid" = 42';
1033 $expectedQuery = 'SELECT "uid", "pid" FROM "tt_content" WHERE ' . $expectedWhere;
1034 $this->assertEquals($expectedQuery, $this->cleanSql($result));
1035 }
1036
1037 /**
1038 * @test
1039 */
1040 public function multipleExpressiosInWhereClauseAreBracedCorrectly() {
1041 $listMaxExpressions = 1000;
1042
1043 $mockSpecificsOci8 = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\Specifics\Oci8Specifics::class, array(), array(), '', FALSE);
1044 $mockSpecificsOci8->expects($this->any())->method('getSpecific')->will($this->returnValue($listMaxExpressions));
1045
1046 $INitems = range(0, 1250);
1047 $NOTINItems = range(0, 1001);
1048 $where = 'uid = 1981 AND uid IN(' . implode(',', $INitems) . ') OR uid = 42 AND uid NOT IN(' . implode(',', $NOTINItems) . ')';
1049 $result = $this->subject->SELECTquery('uid, pid', 'tt_content', $where);
1050
1051 $chunks = array_chunk($INitems, $listMaxExpressions);
1052 $INItemsWhereExpr = array();
1053 foreach ($chunks as $chunk) {
1054 $INItemsWhereExpr[] = '"uid" IN (' . implode(',', $chunk) . ')';
1055 }
1056
1057 $chunks = array_chunk($NOTINItems, $listMaxExpressions);
1058 $NOTINItemsWhereExpr = array();
1059 foreach ($chunks as $chunk) {
1060 $NOTINItemsWhereExpr[] = '"uid" NOT IN (' . implode(',', $chunk) . ')';
1061 }
1062
1063 /**
1064 * $expectedWhere:
1065 * "uid" = 1981 AND (
1066 * "uid" IN (1,2,3,4,...,1000)
1067 * OR "uid" IN (1001,1002,...,1250)
1068 * ) OR "uid" = 42 AND (
1069 * "uid" NOT IN (1,2,3,4,...,1000)
1070 * AND "uid" NOT IN (1001)
1071 * )
1072 */
1073 $expectedWhere = '"uid" = 1981 AND (' . implode(' OR ', $INItemsWhereExpr) . ') OR "uid" = 42 AND (' . implode(' AND ', $NOTINItemsWhereExpr) . ')';
1074 $expectedQuery = 'SELECT "uid", "pid" FROM "tt_content" WHERE ' . $expectedWhere;
1075 $this->assertEquals($expectedQuery, $this->cleanSql($result));
1076 }
1077
1078 }