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