TYPO3  7.6
DatabaseConnectionOracleTest.php
Go to the documentation of this file.
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 
20 class DatabaseConnectionOracleTest extends AbstractTestCase
21 {
25  protected $subject;
26 
32  protected function setUp()
33  {
34  $configuration = array(
35  'handlerCfg' => array(
36  '_DEFAULT' => array(
37  'type' => 'adodb',
38  'config' => array(
39  'driver' => 'oci8',
40  ),
41  ),
42  ),
43  'mapping' => array(
44  'cachingframework_cache_hash' => array(
45  'mapTableName' => 'cf_cache_hash',
46  ),
47  'cachingframework_cache_hash_tags' => array(
48  'mapTableName' => 'cf_cache_hash_tags',
49  ),
50  'cachingframework_cache_pages' => array(
51  'mapTableName' => 'cf_cache_pages',
52  ),
53  'cpg_categories' => array(
54  'mapFieldNames' => array(
55  'pid' => 'page_id',
56  ),
57  ),
58  'pages' => array(
59  'mapTableName' => 'my_pages',
60  'mapFieldNames' => array(
61  'uid' => 'page_uid',
62  ),
63  ),
64  'tt_news' => array(
65  'mapTableName' => 'ext_tt_news',
66  'mapFieldNames' => array(
67  'uid' => 'news_uid',
68  'fe_group' => 'usergroup',
69  ),
70  ),
71  'tt_news_cat' => array(
72  'mapTableName' => 'ext_tt_news_cat',
73  'mapFieldNames' => array(
74  'uid' => 'cat_uid',
75  ),
76  ),
77  'tt_news_cat_mm' => array(
78  'mapTableName' => 'ext_tt_news_cat_mm',
79  'mapFieldNames' => array(
80  'uid_local' => 'local_uid',
81  ),
82  ),
83  'tx_crawler_process' => array(
84  'mapTableName' => 'tx_crawler_ps',
85  'mapFieldNames' => array(
86  'process_id' => 'ps_id',
87  'active' => 'is_active',
88  ),
89  ),
90  'tx_dam_file_tracking' => array(
91  'mapFieldNames' => array(
92  'file_name' => 'filename',
93  'file_path' => 'path',
94  ),
95  ),
96  'tx_dbal_debuglog' => array(
97  'mapFieldNames' => array(
98  'errorFlag' => 'errorflag',
99  ),
100  ),
101  'tx_templavoila_datastructure' => array(
102  'mapTableName' => 'tx_templavoila_ds',
103  ),
104  ),
105  );
106 
107  $this->subject = $this->prepareSubject('oci8', $configuration);
108  }
109 
113  public function runningADOdbDriverReturnsTrueWithOci8ForOci8DefaultDriverConfiguration()
114  {
115  $this->assertTrue($this->subject->runningADOdbDriver('oci8'));
116  }
117 
122  public function sqlHintIsRemoved()
123  {
124  $result = $this->subject->SELECTquery('/*! SQL_NO_CACHE */ content', 'tx_realurl_urlencodecache', '1=1');
125  $expected = 'SELECT "content" FROM "tx_realurl_urlencodecache" WHERE 1 = 1';
126  $this->assertEquals($expected, $this->cleanSql($result));
127  }
128 
132  public function canCompileInsertWithFields()
133  {
134  $parseString = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
135  $parseString .= 'VALUES (\'1\', \'0\', \'2\', \'0\', \'Africa\');';
136  $components = $this->subject->SQLparser->_callRef('parseINSERT', $parseString);
137  $this->assertTrue(is_array($components), $components);
138  $insert = $this->subject->SQLparser->compileSQL($components);
139  $expected = array(
140  'uid' => '1',
141  'pid' => '0',
142  'tr_iso_nr' => '2',
143  'tr_parent_iso_nr' => '0',
144  'tr_name_en' => 'Africa'
145  );
146  $this->assertEquals($expected, $insert);
147  }
148 
152  public function canCompileExtendedInsert()
153  {
154  $tableFields = array('uid', 'pid', 'tr_iso_nr', 'tr_parent_iso_nr', 'tr_name_en');
155  $this->subject->cache_fieldType['static_territories'] = array_flip($tableFields);
156  $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\');';
157  $components = $this->subject->SQLparser->_callRef('parseINSERT', $parseString);
158  $this->assertTrue(is_array($components), $components);
159  $insert = $this->subject->SQLparser->compileSQL($components);
160  $insertCount = count($insert);
161  $this->assertEquals(4, $insertCount);
162  for ($i = 0; $i < $insertCount; $i++) {
163  foreach ($tableFields as $field) {
164  $this->assertTrue(isset($insert[$i][$field]), 'Could not find ' . $field . ' column');
165  }
166  }
167  }
168 
172  public function sqlForInsertWithMultipleRowsIsValid()
173  {
174  $fields = array('uid', 'pid', 'title', 'body');
175  $rows = array(
176  array('1', '2', 'Title #1', 'Content #1'),
177  array('3', '4', 'Title #2', 'Content #2'),
178  array('5', '6', 'Title #3', 'Content #3')
179  );
180  $result = $this->subject->INSERTmultipleRows('tt_content', $fields, $rows);
181  $expected[0] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'1\', \'2\', \'Title #1\', \'Content #1\' )';
182  $expected[1] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'3\', \'4\', \'Title #2\', \'Content #2\' )';
183  $expected[2] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'5\', \'6\', \'Title #3\', \'Content #3\' )';
184  $resultCount = count($result);
185  $this->assertEquals(count($expected), $resultCount);
186  for ($i = 0; $i < $resultCount; $i++) {
187  $this->assertTrue(is_array($result[$i]), 'Expected array: ' . $result[$i]);
188  $this->assertEquals(1, count($result[$i]));
189  $this->assertEquals($expected[$i], $this->cleanSql($result[$i][0]));
190  }
191  }
192 
197  public function groupConditionsAreProperlyTransformed()
198  {
199  $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))');
200  $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,%\')';
201  $this->assertEquals($expected, $this->cleanSql($result));
202  }
203 
205  // Tests concerning quoting
207 
210  public function selectQueryIsProperlyQuoted()
211  {
212  $result = $this->subject->SELECTquery('uid', 'tt_content', 'pid=1', 'cruser_id', 'tstamp');
213  $expected = 'SELECT "uid" FROM "tt_content" WHERE "pid" = 1 GROUP BY "cruser_id" ORDER BY "tstamp"';
214  $this->assertEquals($expected, $this->cleanSql($result));
215  }
216 
220  public function truncateQueryIsProperlyQuoted()
221  {
222  $result = $this->subject->TRUNCATEquery('be_users');
223  $expected = 'TRUNCATE TABLE "be_users"';
224  $this->assertEquals($expected, $this->cleanSql($result));
225  }
226 
231  public function distinctFieldIsProperlyQuoted()
232  {
233  $result = $this->subject->SELECTquery('COUNT(DISTINCT pid)', 'tt_content', '1=1');
234  $expected = 'SELECT COUNT(DISTINCT "pid") FROM "tt_content" WHERE 1 = 1';
235  $this->assertEquals($expected, $this->cleanSql($result));
236  }
237 
243  public function multipleInnerJoinsAreProperlyQuoted()
244  {
245  $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');
246  $expected = 'SELECT * FROM "tt_news_cat"';
247  $expected .= ' INNER JOIN "tt_news_cat_mm" ON "tt_news_cat"."uid"="tt_news_cat_mm"."uid_foreign"';
248  $expected .= ' INNER JOIN "tt_news" ON "tt_news"."uid"="tt_news_cat_mm"."uid_local"';
249  $expected .= ' WHERE 1 = 1';
250  $this->assertEquals($expected, $this->cleanSql($result));
251  }
252 
257  public function stringsWithinInClauseAreProperlyQuoted()
258  {
259  $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');
260  $expected = 'SELECT COUNT(DISTINCT "tx_dam"."uid") AS "count" FROM "tx_dam"';
261  $expected .= ' WHERE "tx_dam"."pid" IN (1) AND "tx_dam"."file_type" IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND "tx_dam"."deleted" = 0';
262  $this->assertEquals($expected, $this->cleanSql($result));
263  }
264 
270  public function concatAfterLikeOperatorIsProperlyQuoted()
271  {
272  $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)');
273  $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
274  $expected .= ' AND (instr(LOWER("sys_refindex"."ref_string"), concat("tx_dam_file_tracking"."file_path","tx_dam_file_tracking"."file_name"),1,1) > 0)';
275  $this->assertEquals($expected, $this->cleanSql($result));
276  }
277 
282  public function cachingFrameworkQueryIsProperlyQuoted()
283  {
284  $currentTime = time();
285  $result = $this->subject->SELECTquery('content', 'cache_hash', 'identifier = ' . $this->subject->fullQuoteStr('abbbabaf2d4b3f9a63e8dde781f1c106', 'cache_hash') . ' AND (crdate + lifetime >= ' . $currentTime . ' OR lifetime = 0)');
286  $expected = 'SELECT "content" FROM "cache_hash" WHERE "identifier" = \'abbbabaf2d4b3f9a63e8dde781f1c106\' AND ("crdate"+"lifetime" >= ' . $currentTime . ' OR "lifetime" = 0)';
287  $this->assertEquals($expected, $this->cleanSql($result));
288  }
289 
294  public function calculatedFieldsAreProperlyQuoted()
295  {
296  $currentTime = time();
297  $result = $this->subject->SELECTquery('identifier', 'cachingframework_cache_pages', 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0');
298  $expected = 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
299  $this->assertEquals($expected, $this->cleanSql($result));
300  }
301 
305  public function numericColumnsAreNotQuoted()
306  {
307  $result = $this->subject->SELECTquery('1', 'be_users', 'username = \'_cli_scheduler\' AND admin = 0 AND be_users.deleted = 0');
308  $expected = 'SELECT 1 FROM "be_users" WHERE "username" = \'_cli_scheduler\' AND "admin" = 0 AND "be_users"."deleted" = 0';
309  $this->assertEquals($expected, $this->cleanSql($result));
310  }
311 
313  // Tests concerning remapping
315 
320  public function tablesAndFieldsAreRemappedInMultipleJoins()
321  {
322  $selectFields = '*';
323  $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';
324  $whereClause = '1=1';
325  $groupBy = '';
326  $orderBy = '';
327  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
328 
329  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
330  $expected = 'SELECT * FROM "ext_tt_news_cat"';
331  $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
332  $expected .= ' INNER JOIN "ext_tt_news" ON "ext_tt_news"."news_uid"="ext_tt_news_cat_mm"."local_uid"';
333  $expected .= ' WHERE 1 = 1';
334  $this->assertEquals($expected, $this->cleanSql($result));
335  }
336 
341  public function tablesAreUnmappedInAdminGetTables()
342  {
343  $handlerMock = $this->getMock('\ADODB_mock', array('MetaTables'), array(), '', false);
344  $handlerMock->expects($this->any())->method('MetaTables')->will($this->returnValue(array('cf_cache_hash')));
345  $this->subject->handlerInstance['_DEFAULT'] = $handlerMock;
346 
347  $actual = $this->subject->admin_get_tables();
348  $expected = array('cachingframework_cache_hash' => array('Name' => 'cachingframework_cache_hash'));
349  $this->assertSame($expected, $actual);
350  }
351 
356  public function fieldWithinSqlFunctionIsRemapped()
357  {
358  $selectFields = 'tstamp, script, SUM(exec_time) AS calc_sum, COUNT(*) AS qrycount, MAX(errorFlag) AS error';
359  $fromTables = 'tx_dbal_debuglog';
360  $whereClause = '1=1';
361  $groupBy = '';
362  $orderBy = '';
363  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
364 
365  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
366  $expected = 'SELECT "tstamp", "script", SUM("exec_time") AS "calc_sum", COUNT(*) AS "qrycount", MAX("errorflag") AS "error" FROM "tx_dbal_debuglog" WHERE 1 = 1';
367  $this->assertEquals($expected, $this->cleanSql($result));
368  }
369 
374  public function tableAndFieldWithinSqlFunctionIsRemapped()
375  {
376  $selectFields = 'MAX(tt_news_cat.uid) AS biggest_id';
377  $fromTables = 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign';
378  $whereClause = 'tt_news_cat_mm.uid_local > 50';
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 MAX("ext_tt_news_cat"."cat_uid") AS "biggest_id" FROM "ext_tt_news_cat"';
385  $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
386  $expected .= ' WHERE "ext_tt_news_cat_mm"."local_uid" > 50';
387  $this->assertEquals($expected, $this->cleanSql($result));
388  }
389 
395  public function concatAfterLikeOperatorIsRemapped()
396  {
397  $selectFields = '*';
398  $fromTables = 'sys_refindex, tx_dam_file_tracking';
399  $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)';
400  $groupBy = '';
401  $orderBy = '';
402  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
403 
404  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
405  $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
406  $expected .= ' AND (instr(LOWER("sys_refindex"."ref_string"), concat("tx_dam_file_tracking"."path","tx_dam_file_tracking"."filename"),1,1) > 0)';
407  $this->assertEquals($expected, $this->cleanSql($result));
408  }
409 
414  public function fieldIsMappedOnRightSideOfAJoinCondition()
415  {
416  $selectFields = 'cpg_categories.uid, cpg_categories.name';
417  $fromTables = 'cpg_categories, pages';
418  $whereClause = 'pages.uid = cpg_categories.pid AND pages.deleted = 0 AND 1 = 1';
419  $groupBy = '';
420  $orderBy = 'cpg_categories.pos';
421  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
422 
423  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
424  $expected = 'SELECT "cpg_categories"."uid", "cpg_categories"."name" FROM "cpg_categories", "my_pages" WHERE "my_pages"."page_uid" = "cpg_categories"."page_id"';
425  $expected .= ' AND "my_pages"."deleted" = 0 AND 1 = 1 ORDER BY "cpg_categories"."pos"';
426  $this->assertEquals($expected, $this->cleanSql($result));
427  }
428 
433  public function fieldFromAliasIsRemapped()
434  {
435  $selectFields = 'news.uid';
436  $fromTables = 'tt_news AS news';
437  $whereClause = 'news.uid = 1';
438  $groupBy = '';
439  $orderBy = '';
440  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
441 
442  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
443  $expected = 'SELECT "news"."news_uid" FROM "ext_tt_news" AS "news" WHERE "news"."news_uid" = 1';
444  $this->assertEquals($expected, $this->cleanSql($result));
445  }
446 
454  public function fieldFromAliasIsRemappedWithoutBeingTricked()
455  {
456  $selectFields = 'tt_news_cat.uid';
457  $fromTables = 'tt_news AS tt_news_cat';
458  $whereClause = 'tt_news_cat.uid = 1';
459  $groupBy = '';
460  $orderBy = '';
461  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
462 
463  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
464  $expected = 'SELECT "tt_news_cat"."news_uid" FROM "ext_tt_news" AS "tt_news_cat" WHERE "tt_news_cat"."news_uid" = 1';
465  $this->assertEquals($expected, $this->cleanSql($result));
466  }
467 
472  public function fieldFromAliasInJoinIsRemapped()
473  {
474  $selectFields = 'cat.uid, cat_mm.uid_local, news.uid';
475  $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';
476  $whereClause = '1=1';
477  $groupBy = '';
478  $orderBy = '';
479  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
480 
481  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
482  $expected = 'SELECT "cat"."cat_uid", "cat_mm"."local_uid", "news"."news_uid"';
483  $expected .= ' FROM "ext_tt_news_cat" AS "cat"';
484  $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat"."cat_uid"="cat_mm"."uid_foreign"';
485  $expected .= ' INNER JOIN "ext_tt_news" AS "news" ON "news"."news_uid"="cat_mm"."local_uid"';
486  $expected .= ' WHERE 1 = 1';
487 
488  $this->assertEquals($expected, $this->cleanSql($result));
489  }
490 
495  public function aliasRemappingWithInSubqueryDoesNotAffectMainQuery()
496  {
497  $selectFields = 'foo.uid';
498  $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid';
499  $whereClause = 'tt_news_cat_mm.uid_foreign IN (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)';
500  $groupBy = '';
501  $orderBy = 'foo.uid';
502  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
503  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
504  $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
505  $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"';
506  $expected .= ' WHERE "ext_tt_news_cat_mm"."uid_foreign" IN (';
507  $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0';
508  $expected .= ')';
509  $expected .= ' ORDER BY "foo"."news_uid"';
510  $this->assertEquals($expected, $this->cleanSql($result));
511  }
512 
517  public function aliasRemappingWithExistsSubqueryDoesNotAffectMainQuery()
518  {
519  $selectFields = 'foo.uid';
520  $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid';
521  $whereClause = 'EXISTS (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)';
522  $groupBy = '';
523  $orderBy = 'foo.uid';
524  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
525  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
526  $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
527  $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"';
528  $expected .= ' WHERE EXISTS (';
529  $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0';
530  $expected .= ')';
531  $expected .= ' ORDER BY "foo"."news_uid"';
532  $this->assertEquals($expected, $this->cleanSql($result));
533  }
534 
539  public function aliasRemappingSupportsNestedSubqueries()
540  {
541  $selectFields = 'foo.uid';
542  $fromTables = 'tt_news AS foo';
543  $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' . '))';
544  $groupBy = '';
545  $orderBy = '';
546  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
547 
548  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
549  $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
550  $expected .= ' WHERE "news_uid" IN (';
551  $expected .= 'SELECT "foobar"."local_uid" FROM "ext_tt_news_cat_mm" AS "foobar" WHERE "uid_foreign" IN (';
552  $expected .= 'SELECT "cat_uid" FROM "ext_tt_news_cat" WHERE "deleted" = 0';
553  $expected .= ')';
554  $expected .= ')';
555  $this->assertEquals($expected, $this->cleanSql($result));
556  }
557 
562  public function remappingDoesNotMixUpAliasesInSubquery()
563  {
564  $selectFields = 'pages.uid';
565  $fromTables = 'tt_news AS pages INNER JOIN tt_news_cat_mm AS cat_mm ON cat_mm.uid_local = pages.uid';
566  $whereClause = 'pages.pid IN (SELECT uid FROM pages WHERE deleted = 0 AND cat_mm.uid_local != 100)';
567  $groupBy = '';
568  $orderBy = 'pages.uid';
569  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
570 
571  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
572  $expected = 'SELECT "pages"."news_uid" FROM "ext_tt_news" AS "pages"';
573  $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat_mm"."local_uid"="pages"."news_uid"';
574  $expected .= ' WHERE "pages"."pid" IN (';
575  $expected .= 'SELECT "page_uid" FROM "my_pages" WHERE "deleted" = 0 AND "cat_mm"."local_uid" != 100';
576  $expected .= ')';
577  $expected .= ' ORDER BY "pages"."news_uid"';
578  $this->assertEquals($expected, $this->cleanSql($result));
579  }
580 
585  public function likeIsRemappedAccordingToFieldTypeWithString()
586  {
587  $this->subject->cache_fieldType['tt_content']['bodytext']['metaType'] = 'B';
588  $result = $this->subject->SELECTquery('*', 'tt_content', 'tt_content.bodytext LIKE \'foo%\'');
589  $expected = 'SELECT * FROM "tt_content" WHERE (dbms_lob.instr(LOWER("tt_content"."bodytext"), \'foo\',1,1) > 0)';
590  $this->assertEquals($expected, $this->cleanSql($result));
591  }
592 
597  public function likeIsRemappedAccordingToFieldTypeWithInteger()
598  {
599  $this->subject->cache_fieldType['tt_content']['bodytext']['metaType'] = 'B';
600  $result = $this->subject->SELECTquery('*', 'fe_users', 'fe_users.usergroup LIKE \'2\'');
601  $expected = 'SELECT * FROM "fe_users" WHERE (instr(LOWER("fe_users"."usergroup"), \'2\',1,1) > 0)';
602  $this->assertEquals($expected, $this->cleanSql($result));
603  }
604 
609  public function notLikeIsRemappedAccordingToFieldTypeWithString()
610  {
611  $this->subject->cache_fieldType['tt_content']['bodytext']['metaType'] = 'B';
612  $result = $this->subject->SELECTquery('*', 'tt_content', 'tt_content.bodytext NOT LIKE \'foo%\'');
613  $expected = 'SELECT * FROM "tt_content" WHERE NOT (dbms_lob.instr(LOWER("tt_content"."bodytext"), \'foo\',1,1) > 0)';
614  $this->assertEquals($expected, $this->cleanSql($result));
615  }
616 
621  public function notLikeIsRemappedAccordingToFieldTypeWithInteger()
622  {
623  $this->subject->cache_fieldType['tt_content']['bodytext']['metaType'] = 'B';
624  $result = $this->subject->SELECTquery('*', 'fe_users', 'fe_users.usergroup NOT LIKE \'2\'');
625  $expected = 'SELECT * FROM "fe_users" WHERE NOT (instr(LOWER("fe_users"."usergroup"), \'2\',1,1) > 0)';
626  $this->assertEquals($expected, $this->cleanSql($result));
627  }
628 
633  public function instrIsUsedForCEOnPages()
634  {
635  $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\'))');
636  $expected = 'SELECT * FROM "tt_content"';
637  $expected .= ' WHERE "uid" IN (62) AND "tt_content"."deleted" = 0 AND "tt_content"."t3ver_state" <= 0';
638  $expected .= ' AND "tt_content"."hidden" = 0 AND ("tt_content"."starttime" <= 1264487640)';
639  $expected .= ' AND ("tt_content"."endtime" = 0 OR "tt_content"."endtime" > 1264487640)';
640  $expected .= ' AND ("tt_content"."fe_group" = \'\' OR "tt_content"."fe_group" IS NULL OR "tt_content"."fe_group" = \'0\'';
641  $expected .= ' OR ((instr(LOWER("tt_content"."fe_group"), \',0,\',1,1) > 0)';
642  $expected .= ' OR (instr(LOWER("tt_content"."fe_group"), \'0,\',1,1) > 0)';
643  $expected .= ' OR (instr(LOWER("tt_content"."fe_group"), \',0\',1,1) > 0)';
644  $expected .= ' OR "tt_content"."fe_group" = \'0\')';
645  $expected .= ' OR ((instr(LOWER("tt_content"."fe_group"), \',-1,\',1,1) > 0)';
646  $expected .= ' OR (instr(LOWER("tt_content"."fe_group"), \'-1,\',1,1) > 0)';
647  $expected .= ' OR (instr(LOWER("tt_content"."fe_group"), \',-1\',1,1) > 0)';
648  $expected .= ' OR "tt_content"."fe_group" = \'-1\'))';
649  $this->assertEquals($expected, $this->cleanSql($result));
650  }
651 
653  // Tests concerning DB management
655 
659  public function notNullableColumnsWithDefaultEmptyStringAreCreatedAsNullable()
660  {
661  $parseString = '
662  CREATE TABLE tx_realurl_uniqalias (
663  uid int(11) NOT NULL auto_increment,
664  tstamp int(11) DEFAULT \'0\' NOT NULL,
665  tablename varchar(60) DEFAULT \'\' NOT NULL,
666  field_alias varchar(255) DEFAULT \'\' NOT NULL,
667  field_id varchar(60) DEFAULT \'\' NOT NULL,
668  value_alias varchar(255) DEFAULT \'\' NOT NULL,
669  value_id int(11) DEFAULT \'0\' NOT NULL,
670  lang int(11) DEFAULT \'0\' NOT NULL,
671  expire int(11) DEFAULT \'0\' NOT NULL,
672 
673  PRIMARY KEY (uid),
674  KEY tablename (tablename),
675  KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
676  KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
677  );
678  ';
679  $components = $this->subject->SQLparser->_callRef('parseCREATETABLE', $parseString);
680  $this->assertTrue(is_array($components), 'Not an array: ' . $components);
681  $sqlCommands = $this->subject->SQLparser->compileSQL($components);
682  $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
683  $this->assertEquals(6, count($sqlCommands));
684  $expected = $this->cleanSql('
685  CREATE TABLE "tx_realurl_uniqalias" (
686  "uid" NUMBER(20) NOT NULL,
687  "tstamp" NUMBER(20) DEFAULT 0,
688  "tablename" VARCHAR(60) DEFAULT \'\',
689  "field_alias" VARCHAR(255) DEFAULT \'\',
690  "field_id" VARCHAR(60) DEFAULT \'\',
691  "value_alias" VARCHAR(255) DEFAULT \'\',
692  "value_id" NUMBER(20) DEFAULT 0,
693  "lang" NUMBER(20) DEFAULT 0,
694  "expire" NUMBER(20) DEFAULT 0,
695  PRIMARY KEY ("uid")
696  )
697  ');
698  $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
699  }
700 
706  public function defaultValueIsProperlyQuotedInCreateTable()
707  {
708  $parseString = '
709  CREATE TABLE tx_test (
710  uid int(11) NOT NULL auto_increment,
711  lastname varchar(60) DEFAULT \'unknown\' NOT NULL,
712  firstname varchar(60) DEFAULT \'\' NOT NULL,
713  language varchar(2) NOT NULL,
714  tstamp int(11) DEFAULT \'0\' NOT NULL,
715 
716  PRIMARY KEY (uid),
717  KEY name (name)
718  );
719  ';
720  $components = $this->subject->SQLparser->_callRef('parseCREATETABLE', $parseString);
721  $this->assertTrue(is_array($components), 'Not an array: ' . $components);
722  $sqlCommands = $this->subject->SQLparser->compileSQL($components);
723  $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
724  $this->assertEquals(4, count($sqlCommands));
725  $expected = $this->cleanSql('
726  CREATE TABLE "tx_test" (
727  "uid" NUMBER(20) NOT NULL,
728  "lastname" VARCHAR(60) DEFAULT \'unknown\',
729  "firstname" VARCHAR(60) DEFAULT \'\',
730  "language" VARCHAR(2) DEFAULT \'\',
731  "tstamp" NUMBER(20) DEFAULT 0,
732  PRIMARY KEY ("uid")
733  )
734  ');
735  $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
736  }
737 
739  // Tests concerning subqueries
741 
745  public function inWhereClauseWithSubqueryIsProperlyQuoted()
746  {
747  $result = $this->subject->SELECTquery('*', 'tx_crawler_queue', 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)');
748  $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "process_id" FROM "tx_crawler_process" WHERE "active" = 0 AND "deleted" = 0)';
749  $this->assertEquals($expected, $this->cleanSql($result));
750  }
751 
756  public function subqueryIsRemappedForInWhereClause()
757  {
758  $selectFields = '*';
759  $fromTables = 'tx_crawler_queue';
760  $whereClause = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
761  $groupBy = '';
762  $orderBy = '';
763  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
764  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
765  $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "ps_id" FROM "tx_crawler_ps" WHERE "is_active" = 0 AND "deleted" = 0)';
766  $this->assertEquals($expected, $this->cleanSql($result));
767  }
768 
773  public function cachingFrameworkQueryIsSupported()
774  {
775  $currentTime = time();
776  $result = $this->subject->DELETEquery('cachingframework_cache_hash_tags', 'identifier IN (' . $this->subject->SELECTsubquery('identifier', 'cachingframework_cache_pages', ('crdate + lifetime < ' . $currentTime . ' AND lifetime > 0')) . ')');
777  $expected = 'DELETE FROM "cachingframework_cache_hash_tags" WHERE "identifier" IN (';
778  $expected .= 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
779  $expected .= ')';
780  $this->assertEquals($expected, $this->cleanSql($result));
781  }
782 
787  public function cachingFrameworkQueryIsRemapped()
788  {
789  $currentTime = time();
790  $table = 'cachingframework_cache_hash_tags';
791  $where = 'identifier IN (' . $this->subject->SELECTsubquery('identifier', 'cachingframework_cache_pages', ('crdate + lifetime < ' . $currentTime . ' AND lifetime > 0')) . ')';
792 
793  // Perform remapping (as in method exec_DELETEquery)
794  $tableArray = $this->subject->_call('map_needMapping', $table);
795  // Where clause:
796  $whereParts = $this->subject->SQLparser->parseWhereClause($where);
797  $this->subject->_callRef('map_sqlParts', $whereParts, $tableArray[0]['table']);
798  $where = $this->subject->SQLparser->compileWhereClause($whereParts, false);
799  // Table name:
800  if ($this->subject->mapping[$table]['mapTableName']) {
801  $table = $this->subject->mapping[$table]['mapTableName'];
802  }
803 
804  $result = $this->subject->DELETEquery($table, $where);
805  $expected = 'DELETE FROM "cf_cache_hash_tags" WHERE "identifier" IN (';
806  $expected .= 'SELECT "identifier" FROM "cf_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
807  $expected .= ')';
808  $this->assertEquals($expected, $this->cleanSql($result));
809  }
810 
815  public function existsWhereClauseIsProperlyQuoted()
816  {
817  $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)') . ')');
818  $expected = 'SELECT * FROM "tx_crawler_process" WHERE "active" = 0 AND NOT EXISTS (';
819  $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_process"."process_id" AND "tx_crawler_queue"."exec_time" = 0';
820  $expected .= ')';
821  $this->assertEquals($expected, $this->cleanSql($result));
822  }
823 
828  public function subqueryIsRemappedForExistsWhereClause()
829  {
830  $selectFields = '*';
831  $fromTables = 'tx_crawler_process';
832  $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') . ')';
833  $groupBy = '';
834  $orderBy = '';
835  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
836 
837  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
838  $expected = 'SELECT * FROM "tx_crawler_ps" WHERE "is_active" = 0 AND NOT EXISTS (';
839  $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_ps"."ps_id" AND "tx_crawler_queue"."exec_time" = 0';
840  $expected .= ')';
841  $this->assertEquals($expected, $this->cleanSql($result));
842  }
843 
845  // Tests concerning advanced operators
847 
851  public function caseStatementIsProperlyQuoted()
852  {
853  $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');
854  $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';
855 
856  $this->assertEquals($expected, $this->cleanSql($result));
857  }
858 
863  public function caseStatementIsProperlyRemapped()
864  {
865  $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';
866  $fromTables = 'tx_crawler_process';
867  $whereClause = '1=1';
868  $groupBy = '';
869  $orderBy = '';
870  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
871 
872  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
873  $expected = 'SELECT "ps_id", CASE "is_active" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" ';
874  $expected .= 'FROM "tx_crawler_ps" WHERE 1 = 1';
875  $this->assertEquals($expected, $this->cleanSql($result));
876  }
877 
882  public function caseStatementWithExternalTableIsProperlyRemapped()
883  {
884  $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';
885  $fromTables = 'tx_crawler_process, tt_news';
886  $whereClause = '1=1';
887  $groupBy = '';
888  $orderBy = '';
889  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
890 
891  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
892  $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" ';
893  $expected .= 'FROM "tx_crawler_ps", "ext_tt_news" WHERE 1 = 1';
894  $this->assertEquals($expected, $this->cleanSql($result));
895  }
896 
901  public function locateStatementIsProperlyQuoted()
902  {
903  $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');
904  $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\') > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
905  $this->assertEquals($expected, $this->cleanSql($result));
906  }
907 
912  public function locateStatementWithPositionIsProperlyQuoted()
913  {
914  $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');
915  $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\', 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
916  $this->assertEquals($expected, $this->cleanSql($result));
917  }
918 
923  public function IfNullIsProperlyRemapped()
924  {
925  $result = $this->subject->SELECTquery('*', 'tt_news_cat_mm', 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)');
926  $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE NVL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
927  $this->assertEquals($expected, $this->cleanSql($result));
928  }
929 
934  public function findInSetIsProperlyRemapped()
935  {
936  $result = $this->subject->SELECTquery('*', 'fe_users', 'FIND_IN_SET(10, usergroup)');
937  $expected = 'SELECT * FROM "fe_users" WHERE \',\'||"usergroup"||\',\' LIKE \'%,10,%\'';
938  $this->assertEquals($expected, $this->cleanSql($result));
939  }
940 
945  public function findInSetFieldIsProperlyRemapped()
946  {
947  $selectFields = 'fe_group';
948  $fromTables = 'tt_news';
949  $whereClause = 'FIND_IN_SET(10, fe_group)';
950  $groupBy = '';
951  $orderBy = '';
952  $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
953 
954  $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
955  $expected = 'SELECT "usergroup" FROM "ext_tt_news" WHERE \',\'||"ext_tt_news"."usergroup"||\',\' LIKE \'%,10,%\'';
956  $this->assertEquals($expected, $this->cleanSql($result));
957  }
958 
963  public function listQueryIsProperlyRemapped()
964  {
965  $result = $this->subject->SELECTquery('*', 'fe_users', $this->subject->listQuery('usergroup', 10, 'fe_users'));
966  $expected = 'SELECT * FROM "fe_users" WHERE \',\'||"usergroup"||\',\' LIKE \'%,10,%\'';
967  $this->assertEquals($expected, $this->cleanSql($result));
968  }
969 
974  public function likeBinaryOperatorIsRemoved()
975  {
976  $result = $this->subject->SELECTquery('*', 'tt_content', 'bodytext LIKE BINARY \'test\'');
977  $expected = 'SELECT * FROM "tt_content" WHERE (dbms_lob.instr("bodytext", \'test\',1,1) > 0)';
978  $this->assertEquals($expected, $this->cleanSql($result));
979  }
980 
984  public function expressionListWithNotInIsConcatenatedWithAnd()
985  {
986  $listMaxExpressions = 1000;
987 
988  $mockSpecificsOci8 = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\Specifics\Oci8Specifics::class, array(), array(), '', false);
989  $mockSpecificsOci8->expects($this->any())->method('getSpecific')->will($this->returnValue($listMaxExpressions));
990 
991  $items = range(0, 1250);
992  $where = 'uid NOT IN(' . implode(',', $items) . ')';
993  $result = $this->subject->SELECTquery('*', 'tt_content', $where);
994 
995  $chunks = array_chunk($items, $listMaxExpressions);
996  $whereExpr = array();
997  foreach ($chunks as $chunk) {
998  $whereExpr[] = '"uid" NOT IN (' . implode(',', $chunk) . ')';
999  }
1000 
1008  $expectedWhere = '(' . implode(' AND ', $whereExpr) . ')';
1009  $expectedQuery = 'SELECT * FROM "tt_content" WHERE ' . $expectedWhere;
1010  $this->assertEquals($expectedQuery, $this->cleanSql($result));
1011  }
1012 
1016  public function expressionListWithInIsConcatenatedWithOr()
1017  {
1018  $listMaxExpressions = 1000;
1019 
1020  $mockSpecificsOci8 = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\Specifics\Oci8Specifics::class, array(), array(), '', false);
1021  $mockSpecificsOci8->expects($this->any())->method('getSpecific')->will($this->returnValue($listMaxExpressions));
1022 
1023  $items = range(0, 1250);
1024  $where = 'uid IN(' . implode(',', $items) . ')';
1025  $result = $this->subject->SELECTquery('*', 'tt_content', $where);
1026 
1027  $chunks = array_chunk($items, $listMaxExpressions);
1028  $whereExpr = array();
1029  foreach ($chunks as $chunk) {
1030  $whereExpr[] = '"uid" IN (' . implode(',', $chunk) . ')';
1031  }
1032 
1040  $expectedWhere = '(' . implode(' OR ', $whereExpr) . ')';
1041  $expectedQuery = 'SELECT * FROM "tt_content" WHERE ' . $expectedWhere;
1042  $this->assertEquals($expectedQuery, $this->cleanSql($result));
1043  }
1044 
1048  public function expressionListIsUnchanged()
1049  {
1050  $listMaxExpressions = 1000;
1051 
1052  $mockSpecificsOci8 = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\Specifics\Oci8Specifics::class, array(), array(), '', false);
1053  $mockSpecificsOci8->expects($this->any())->method('getSpecific')->will($this->returnValue($listMaxExpressions));
1054 
1055  $result = $this->subject->SELECTquery('*', 'tt_content', 'uid IN (0,1,2,3,4,5,6,7,8,9,10)');
1056 
1057  $expectedQuery = 'SELECT * FROM "tt_content" WHERE "uid" IN (0,1,2,3,4,5,6,7,8,9,10)';
1058  $this->assertEquals($expectedQuery, $this->cleanSql($result));
1059  }
1060 
1064  public function expressionListBracesAreSetCorrectly()
1065  {
1066  $listMaxExpressions = 1000;
1067 
1068  $mockSpecificsOci8 = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\Specifics\Oci8Specifics::class, array(), array(), '', false);
1069  $mockSpecificsOci8->expects($this->any())->method('getSpecific')->will($this->returnValue($listMaxExpressions));
1070 
1071  $items = range(0, 1250);
1072  $where = 'uid = 1981 AND uid IN(' . implode(',', $items) . ') OR uid = 42';
1073  $result = $this->subject->SELECTquery('uid, pid', 'tt_content', $where);
1074 
1075  $chunks = array_chunk($items, $listMaxExpressions);
1076  $whereExpr = array();
1077  foreach ($chunks as $chunk) {
1078  $whereExpr[] = '"uid" IN (' . implode(',', $chunk) . ')';
1079  }
1080 
1088  $expectedWhere = '"uid" = 1981 AND (' . implode(' OR ', $whereExpr) . ') OR "uid" = 42';
1089  $expectedQuery = 'SELECT "uid", "pid" FROM "tt_content" WHERE ' . $expectedWhere;
1090  $this->assertEquals($expectedQuery, $this->cleanSql($result));
1091  }
1092 
1096  public function multipleExpressiosInWhereClauseAreBracedCorrectly()
1097  {
1098  $listMaxExpressions = 1000;
1099 
1100  $mockSpecificsOci8 = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\Specifics\Oci8Specifics::class, array(), array(), '', false);
1101  $mockSpecificsOci8->expects($this->any())->method('getSpecific')->will($this->returnValue($listMaxExpressions));
1102 
1103  $INitems = range(0, 1250);
1104  $NOTINItems = range(0, 1001);
1105  $where = 'uid = 1981 AND uid IN(' . implode(',', $INitems) . ') OR uid = 42 AND uid NOT IN(' . implode(',', $NOTINItems) . ')';
1106  $result = $this->subject->SELECTquery('uid, pid', 'tt_content', $where);
1107 
1108  $chunks = array_chunk($INitems, $listMaxExpressions);
1109  $INItemsWhereExpr = array();
1110  foreach ($chunks as $chunk) {
1111  $INItemsWhereExpr[] = '"uid" IN (' . implode(',', $chunk) . ')';
1112  }
1113 
1114  $chunks = array_chunk($NOTINItems, $listMaxExpressions);
1115  $NOTINItemsWhereExpr = array();
1116  foreach ($chunks as $chunk) {
1117  $NOTINItemsWhereExpr[] = '"uid" NOT IN (' . implode(',', $chunk) . ')';
1118  }
1119 
1130  $expectedWhere = '"uid" = 1981 AND (' . implode(' OR ', $INItemsWhereExpr) . ') OR "uid" = 42 AND (' . implode(' AND ', $NOTINItemsWhereExpr) . ')';
1131  $expectedQuery = 'SELECT "uid", "pid" FROM "tt_content" WHERE ' . $expectedWhere;
1132  $this->assertEquals($expectedQuery, $this->cleanSql($result));
1133  }
1134 }
Generated on Wed Nov 11 2015 01:51:53 for TYPO3 by   doxygen 1.8.3