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