|
TYPO3 API
SVNRelease
|
00001 <?php 00002 /*************************************************************** 00003 * Copyright notice 00004 * 00005 * (c) 2009 Xavier Perseguers <typo3@perseguers.ch> 00006 * All rights reserved 00007 * 00008 * This script is part of the TYPO3 project. The TYPO3 project is 00009 * free software; you can redistribute it and/or modify 00010 * it under the terms of the GNU General Public License as published by 00011 * the Free Software Foundation; either version 2 of the License, or 00012 * (at your option) any later version. 00013 * 00014 * The GNU General Public License can be found at 00015 * http://www.gnu.org/copyleft/gpl.html. 00016 * 00017 * This script is distributed in the hope that it will be useful, 00018 * but WITHOUT ANY WARRANTY; without even the implied warranty of 00019 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 00020 * GNU General Public License for more details. 00021 * 00022 * This copyright notice MUST APPEAR in all copies of the script! 00023 ***************************************************************/ 00024 00025 00026 require_once('BaseTestCase.php'); 00027 require_once('FakeDbConnection.php'); 00028 00029 /** 00030 * Testcase for class ux_t3lib_db. Testing Oracle database handling. 00031 * 00032 * $Id$ 00033 * 00034 * @author Xavier Perseguers <typo3@perseguers.ch> 00035 * 00036 * @package TYPO3 00037 * @subpackage dbal 00038 */ 00039 class dbOracleTest extends BaseTestCase { 00040 00041 /** 00042 * @var t3lib_db 00043 */ 00044 protected $db; 00045 00046 /** 00047 * @var array 00048 */ 00049 protected $dbalConfig; 00050 00051 /** 00052 * Prepares the environment before running a test. 00053 */ 00054 public function setUp() { 00055 // Backup DBAL configuration 00056 $this->dbalConfig = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']; 00057 // Backup database connection 00058 $this->db = $GLOBALS['TYPO3_DB']; 00059 // Reconfigure DBAL to use Oracle 00060 require('fixtures/oci8.config.php'); 00061 00062 $className = self::buildAccessibleProxy('ux_t3lib_db'); 00063 $GLOBALS['TYPO3_DB'] = new $className; 00064 $parserClassName = self::buildAccessibleProxy('ux_t3lib_sqlparser'); 00065 $GLOBALS['TYPO3_DB']->SQLparser = new $parserClassName; 00066 00067 $this->assertFalse($GLOBALS['TYPO3_DB']->isConnected()); 00068 00069 // Initialize a fake Oracle connection 00070 FakeDbConnection::connect($GLOBALS['TYPO3_DB'], 'oci8'); 00071 00072 $this->assertTrue($GLOBALS['TYPO3_DB']->isConnected()); 00073 } 00074 00075 /** 00076 * Cleans up the environment after running a test. 00077 */ 00078 public function tearDown() { 00079 // Clear DBAL-generated cache files 00080 $GLOBALS['TYPO3_DB']->clearCachedFieldInfo(); 00081 // Restore DBAL configuration 00082 $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'] = $this->dbalConfig; 00083 // Restore DB connection 00084 $GLOBALS['TYPO3_DB'] = $this->db; 00085 } 00086 00087 /** 00088 * Cleans a SQL query. 00089 * 00090 * @param mixed $sql 00091 * @return mixed (string or array) 00092 */ 00093 private function cleanSql($sql) { 00094 if (!is_string($sql)) { 00095 return $sql; 00096 } 00097 00098 $sql = str_replace("\n", ' ', $sql); 00099 $sql = preg_replace('/\s+/', ' ', $sql); 00100 return trim($sql); 00101 } 00102 00103 /** 00104 * @test 00105 */ 00106 public function configurationIsUsingAdodbAndDriverOci8() { 00107 $configuration = $GLOBALS['TYPO3_DB']->conf['handlerCfg']; 00108 $this->assertTrue(is_array($configuration) && count($configuration) > 0, 'No configuration found'); 00109 $this->assertEquals('adodb', $configuration['_DEFAULT']['type']); 00110 $this->assertTrue($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') !== FALSE, 'Not using oci8 driver'); 00111 } 00112 00113 /** 00114 * @test 00115 */ 00116 public function tablesWithMappingAreDetected() { 00117 $tablesWithMapping = array_keys($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping']); 00118 00119 foreach ($GLOBALS['TYPO3_DB']->cache_fieldType as $table => $fieldTypes) { 00120 $tableDef = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table); 00121 00122 if (in_array($table, $tablesWithMapping)) { 00123 self::assertTrue(is_array($tableDef), 'Table ' . $table . ' was expected to need mapping'); 00124 } else { 00125 self::assertFalse($tableDef, 'Table ' . $table . ' was not expected to need mapping'); 00126 } 00127 } 00128 } 00129 00130 /** 00131 * @test 00132 * @see http://bugs.typo3.org/view.php?id=12897 00133 */ 00134 public function sqlHintIsRemoved() { 00135 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00136 '/*! SQL_NO_CACHE */ content', 00137 'tx_realurl_urlencodecache', 00138 '1=1' 00139 )); 00140 $expected = 'SELECT "content" FROM "tx_realurl_urlencodecache" WHERE 1 = 1'; 00141 $this->assertEquals($expected, $query); 00142 } 00143 00144 /** 00145 * @test 00146 */ 00147 public function canCompileInsertWithFields() { 00148 $parseString = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) '; 00149 $parseString .= "VALUES ('1', '0', '2', '0', 'Africa');"; 00150 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseINSERT', $parseString); 00151 00152 $this->assertTrue(is_array($components), $components); 00153 $insert = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('compileINSERT', $components); 00154 00155 $expected = array( 00156 'uid' => '1', 00157 'pid' => '0', 00158 'tr_iso_nr' => '2', 00159 'tr_parent_iso_nr' => '0', 00160 'tr_name_en' => 'Africa', 00161 ); 00162 $this->assertEquals($expected, $insert); 00163 } 00164 00165 /** 00166 * @test 00167 * http://bugs.typo3.org/view.php?id=13209 00168 */ 00169 public function canCompileExtendedInsert() { 00170 $parseString = "INSERT INTO static_territories VALUES ('1', '0', '2', '0', 'Africa'),('2', '0', '9', '0', 'Oceania')," . 00171 "('3', '0', '19', '0', 'Americas'),('4', '0', '142', '0', 'Asia');"; 00172 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseINSERT', $parseString); 00173 00174 $this->assertTrue(is_array($components), $components); 00175 $insert = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('compileINSERT', $components); 00176 00177 $this->assertEquals(4, count($insert)); 00178 00179 for ($i = 0; $i < count($insert); $i++) { 00180 foreach (t3lib_div::trimExplode(',', 'uid,pid,tr_iso_nr,tr_parent_iso_nr,tr_name_en') as $field) { 00181 $this->assertTrue(isset($insert[$i][$field]), 'Could not find ' . $field . ' column'); 00182 } 00183 } 00184 } 00185 00186 /** 00187 * @test 00188 * http://bugs.typo3.org/view.php?id=12858 00189 */ 00190 public function sqlForInsertWithMultipleRowsIsValid() { 00191 $fields = array('uid', 'pid', 'title', 'body'); 00192 $rows = array( 00193 array('1', '2', 'Title #1', 'Content #1'), 00194 array('3', '4', 'Title #2', 'Content #2'), 00195 array('5', '6', 'Title #3', 'Content #3'), 00196 ); 00197 $query = $GLOBALS['TYPO3_DB']->INSERTmultipleRows('tt_content', $fields, $rows); 00198 00199 $expected[0] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'1\', \'2\', \'Title #1\', \'Content #1\' )'; 00200 $expected[1] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'3\', \'4\', \'Title #2\', \'Content #2\' )'; 00201 $expected[2] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'5\', \'6\', \'Title #3\', \'Content #3\' )'; 00202 00203 $this->assertEquals(count($expected), count($query)); 00204 for ($i = 0; $i < count($query); $i++) { 00205 $this->assertTrue(is_array($query[$i]), 'Expected array: ' . $query[$i]); 00206 $this->assertEquals(1, count($query[$i])); 00207 $this->assertEquals($expected[$i], $this->cleanSql($query[$i][0])); 00208 } 00209 } 00210 00211 /** 00212 * @test 00213 * @see http://bugs.typo3.org/view.php?id=15535 00214 */ 00215 public function groupConditionsAreProperlyTransformed() { 00216 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00217 '*', 00218 'pages', 00219 'pid=0 AND pages.deleted=0 AND pages.hidden=0 AND pages.starttime<=1281620460 ' 00220 . 'AND (pages.endtime=0 OR pages.endtime>1281620460) AND NOT pages.t3ver_state>0 ' 00221 . 'AND pages.doktype<200 AND (pages.fe_group=\'\' OR pages.fe_group IS NULL OR ' 00222 . 'pages.fe_group=\'0\' OR FIND_IN_SET(\'0\',pages.fe_group) OR FIND_IN_SET(\'-1\',pages.fe_group))' 00223 )); 00224 $expected = 'SELECT * FROM "pages" WHERE "pid" = 0 AND "pages"."deleted" = 0 AND "pages"."hidden" = 0 ' 00225 . 'AND "pages"."starttime" <= 1281620460 AND ("pages"."endtime" = 0 OR "pages"."endtime" > 1281620460) ' 00226 . 'AND NOT "pages"."t3ver_state" > 0 AND "pages"."doktype" < 200 AND ("pages"."fe_group" = \'\' ' 00227 . 'OR "pages"."fe_group" IS NULL OR "pages"."fe_group" = \'0\' OR \',\'||"pages"."fe_group"||\',\' LIKE \'%,0,%\' ' 00228 . 'OR \',\'||"pages"."fe_group"||\',\' LIKE \'%,-1,%\')'; 00229 $this->assertEquals($expected, $query); 00230 } 00231 00232 /////////////////////////////////////// 00233 // Tests concerning quoting 00234 /////////////////////////////////////// 00235 00236 /** 00237 * @test 00238 */ 00239 public function selectQueryIsProperlyQuoted() { 00240 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00241 'uid', // select fields 00242 'tt_content', // from table 00243 'pid=1', // where clause 00244 'cruser_id', // group by 00245 'tstamp' // order by 00246 )); 00247 $expected = 'SELECT "uid" FROM "tt_content" WHERE "pid" = 1 GROUP BY "cruser_id" ORDER BY "tstamp"'; 00248 $this->assertEquals($expected, $query); 00249 } 00250 00251 /** 00252 * @test 00253 * http://bugs.typo3.org/view.php?id=13504 00254 */ 00255 public function truncateQueryIsProperlyQuoted() { 00256 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->TRUNCATEquery('be_users')); 00257 $expected = 'TRUNCATE TABLE "be_users"'; 00258 $this->assertEquals($expected, $query); 00259 } 00260 00261 /** 00262 * @test 00263 * @see http://bugs.typo3.org/view.php?id=2438 00264 */ 00265 public function distinctFieldIsProperlyQuoted() { 00266 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00267 'COUNT(DISTINCT pid)', // select fields 00268 'tt_content', // from table 00269 '1=1' // where clause 00270 )); 00271 $expected = 'SELECT COUNT(DISTINCT "pid") FROM "tt_content" WHERE 1 = 1'; 00272 $this->assertEquals($expected, $query); 00273 } 00274 00275 /** 00276 * @test 00277 * @see http://bugs.typo3.org/view.php?id=10411 00278 * @remark Remapping is not expected here 00279 */ 00280 public function multipleInnerJoinsAreProperlyQuoted() { 00281 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00282 '*', 00283 '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', 00284 '1=1' 00285 )); 00286 $expected = 'SELECT * FROM "tt_news_cat"'; 00287 $expected .= ' INNER JOIN "tt_news_cat_mm" ON "tt_news_cat"."uid"="tt_news_cat_mm"."uid_foreign"'; 00288 $expected .= ' INNER JOIN "tt_news" ON "tt_news"."uid"="tt_news_cat_mm"."uid_local"'; 00289 $expected .= ' WHERE 1 = 1'; 00290 $this->assertEquals($expected, $query); 00291 } 00292 00293 /** 00294 * @test 00295 * @see http://bugs.typo3.org/view.php?id=6198 00296 */ 00297 public function stringsWithinInClauseAreProperlyQuoted() { 00298 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00299 'COUNT(DISTINCT tx_dam.uid) AS count', 00300 'tx_dam', 00301 'tx_dam.pid IN (1) AND tx_dam.file_type IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND tx_dam.deleted = 0' 00302 )); 00303 $expected = 'SELECT COUNT(DISTINCT "tx_dam"."uid") AS "count" FROM "tx_dam"'; 00304 $expected .= ' WHERE "tx_dam"."pid" IN (1) AND "tx_dam"."file_type" IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND "tx_dam"."deleted" = 0'; 00305 $this->assertEquals($expected, $query); 00306 } 00307 00308 /** 00309 * @test 00310 * @see http://bugs.typo3.org/view.php?id=12515 00311 * @remark Remapping is not expected here 00312 */ 00313 public function concatAfterLikeOperatorIsProperlyQuoted() { 00314 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00315 '*', 00316 'sys_refindex, tx_dam_file_tracking', 00317 'sys_refindex.tablename = \'tx_dam_file_tracking\'' 00318 . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)' 00319 )); 00320 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\''; 00321 $expected .= ' AND (instr(LOWER("sys_refindex"."ref_string"), CONCAT("tx_dam_file_tracking"."file_path","tx_dam_file_tracking"."file_name"),1,1) > 0)'; 00322 $this->assertEquals($expected, $query); 00323 } 00324 00325 /** 00326 * @test 00327 * @see http://bugs.typo3.org/view.php?id=12231 00328 */ 00329 public function cachingFrameworkQueryIsProperlyQuoted() { 00330 $currentTime = time(); 00331 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00332 'content', 00333 'cache_hash', 00334 'identifier = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('abbbabaf2d4b3f9a63e8dde781f1c106', 'cache_hash') . 00335 ' AND (crdate + lifetime >= ' . $currentTime . ' OR lifetime = 0)' 00336 )); 00337 $expected = 'SELECT "content" FROM "cache_hash" WHERE "identifier" = \'abbbabaf2d4b3f9a63e8dde781f1c106\' AND ("crdate"+"lifetime" >= ' . $currentTime . ' OR "lifetime" = 0)'; 00338 $this->assertEquals($expected, $query); 00339 } 00340 00341 /** 00342 * @test 00343 * @see http://bugs.typo3.org/view.php?id=12231 00344 */ 00345 public function calculatedFieldsAreProperlyQuoted() { 00346 $currentTime = time(); 00347 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00348 'identifier', 00349 'cachingframework_cache_pages', 00350 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0' 00351 )); 00352 $expected = 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0'; 00353 $this->assertEquals($expected, $query); 00354 } 00355 00356 /** 00357 * @test 00358 * http://bugs.typo3.org/view.php?id=13422 00359 */ 00360 public function numericColumnsAreNotQuoted() { 00361 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00362 '1', 00363 'be_users', 00364 'username = \'_cli_scheduler\' AND admin = 0 AND be_users.deleted = 0' 00365 )); 00366 $expected = 'SELECT 1 FROM "be_users" WHERE "username" = \'_cli_scheduler\' AND "admin" = 0 AND "be_users"."deleted" = 0'; 00367 $this->assertEquals($expected, $query); 00368 } 00369 00370 /////////////////////////////////////// 00371 // Tests concerning remapping 00372 /////////////////////////////////////// 00373 00374 /** 00375 * @test 00376 * @see http://bugs.typo3.org/view.php?id=10411 00377 * @remark Remapping is expected here 00378 */ 00379 public function tablesAndFieldsAreRemappedInMultipleJoins() { 00380 $selectFields = '*'; 00381 $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'; 00382 $whereClause = '1=1'; 00383 $groupBy = ''; 00384 $orderBy = ''; 00385 00386 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00387 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00388 00389 $expected = 'SELECT * FROM "ext_tt_news_cat"'; 00390 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"'; 00391 $expected .= ' INNER JOIN "ext_tt_news" ON "ext_tt_news"."news_uid"="ext_tt_news_cat_mm"."local_uid"'; 00392 $expected .= ' WHERE 1 = 1'; 00393 $this->assertEquals($expected, $query); 00394 } 00395 00396 /** 00397 * @test 00398 * @see http://bugs.typo3.org/view.php?id=6953 00399 */ 00400 public function fieldWithinSqlFunctionIsRemapped() { 00401 $selectFields = 'tstamp, script, SUM(exec_time) AS calc_sum, COUNT(*) AS qrycount, MAX(errorFlag) AS error'; 00402 $fromTables = 'tx_dbal_debuglog'; 00403 $whereClause = '1=1'; 00404 $groupBy = ''; 00405 $orderBy = ''; 00406 00407 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00408 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00409 00410 $expected = 'SELECT "tstamp", "script", SUM("exec_time") AS "calc_sum", COUNT(*) AS "qrycount", MAX("errorflag") AS "error" FROM "tx_dbal_debuglog" WHERE 1 = 1'; 00411 $this->assertEquals($expected, $query); 00412 } 00413 00414 /** 00415 * @test 00416 * @see http://bugs.typo3.org/view.php?id=6953 00417 */ 00418 public function tableAndFieldWithinSqlFunctionIsRemapped() { 00419 $selectFields = 'MAX(tt_news_cat.uid) AS biggest_id'; 00420 $fromTables = 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign'; 00421 $whereClause = 'tt_news_cat_mm.uid_local > 50'; 00422 $groupBy = ''; 00423 $orderBy = ''; 00424 00425 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00426 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00427 00428 $expected = 'SELECT MAX("ext_tt_news_cat"."cat_uid") AS "biggest_id" FROM "ext_tt_news_cat"'; 00429 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"'; 00430 $expected .= ' WHERE "ext_tt_news_cat_mm"."local_uid" > 50'; 00431 $this->assertEquals($expected, $query); 00432 } 00433 00434 /** 00435 * @test 00436 * @see http://bugs.typo3.org/view.php?id=12515 00437 * @remark Remapping is expected here 00438 */ 00439 public function concatAfterLikeOperatorIsRemapped() { 00440 $selectFields = '*'; 00441 $fromTables = 'sys_refindex, tx_dam_file_tracking'; 00442 $whereClause = 'sys_refindex.tablename = \'tx_dam_file_tracking\'' 00443 . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)'; 00444 $groupBy = ''; 00445 $orderBy = ''; 00446 00447 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00448 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00449 00450 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\''; 00451 $expected .= ' AND (instr(LOWER("sys_refindex"."ref_string"), CONCAT("tx_dam_file_tracking"."path","tx_dam_file_tracking"."filename"),1,1) > 0)'; 00452 $this->assertEquals($expected, $query); 00453 } 00454 00455 /** 00456 * @test 00457 * @see http://bugs.typo3.org/view.php?id=5708 00458 */ 00459 public function fieldIsMappedOnRightSideOfAJoinCondition() { 00460 $selectFields = 'cpg_categories.uid, cpg_categories.name'; 00461 $fromTables = 'cpg_categories, pages'; 00462 $whereClause = 'pages.uid = cpg_categories.pid AND pages.deleted = 0 AND 1 = 1'; 00463 $groupBy = ''; 00464 $orderBy = 'cpg_categories.pos'; 00465 00466 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00467 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00468 00469 $expected = 'SELECT "cpg_categories"."uid", "cpg_categories"."name" FROM "cpg_categories", "my_pages" WHERE "my_pages"."page_uid" = "cpg_categories"."page_id"'; 00470 $expected .= ' AND "my_pages"."deleted" = 0 AND 1 = 1 ORDER BY "cpg_categories"."pos"'; 00471 $this->assertEquals($expected, $query); 00472 } 00473 00474 /** 00475 * @test 00476 * @see http://bugs.typo3.org/view.php?id=14372 00477 */ 00478 public function fieldFromAliasIsRemapped() { 00479 $selectFields = 'news.uid'; 00480 $fromTables = 'tt_news AS news'; 00481 $whereClause = 'news.uid = 1'; 00482 $groupBy = ''; 00483 $orderBy = ''; 00484 00485 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00486 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00487 00488 $expected = 'SELECT "news"."news_uid" FROM "ext_tt_news" AS "news" WHERE "news"."news_uid" = 1'; 00489 $this->assertEquals($expected, $query); 00490 } 00491 00492 /** 00493 * Trick here is that we already have a mapping for both table tt_news and table tt_news_cat 00494 * (see tests/fixtures/oci8.config.php) which is used as alias name. 00495 * 00496 * @test 00497 * @see http://bugs.typo3.org/view.php?id=14372 00498 */ 00499 public function fieldFromAliasIsRemappedWithoutBeingTricked() { 00500 $selectFields = 'tt_news_cat.uid'; 00501 $fromTables = 'tt_news AS tt_news_cat'; 00502 $whereClause = 'tt_news_cat.uid = 1'; 00503 $groupBy = ''; 00504 $orderBy = ''; 00505 00506 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00507 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00508 00509 $expected = 'SELECT "tt_news_cat"."news_uid" FROM "ext_tt_news" AS "tt_news_cat" WHERE "tt_news_cat"."news_uid" = 1'; 00510 $this->assertEquals($expected, $query); 00511 } 00512 00513 /** 00514 * @test 00515 * @see http://bugs.typo3.org/view.php?id=14372 00516 */ 00517 public function aliasRemappingDoesNotAlterFurtherQueries() { 00518 $selectFields = 'foo.uid'; 00519 $fromTables = 'tt_news AS foo'; 00520 $whereClause = 'foo.uid = 1'; 00521 $groupBy = ''; 00522 $orderBy = ''; 00523 00524 // First call to possibly alter (in memory) the mapping from localconf.php 00525 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00526 00527 $selectFields = 'uid'; 00528 $fromTables = 'foo'; 00529 $whereClause = 'uid = 1'; 00530 $groupBy = ''; 00531 $orderBy = ''; 00532 00533 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00534 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00535 00536 $expected = 'SELECT "uid" FROM "foo" WHERE "uid" = 1'; 00537 $this->assertEquals($expected, $query); 00538 } 00539 00540 /** 00541 * @test 00542 * @see http://bugs.typo3.org/view.php?id=14372 00543 */ 00544 public function fieldFromAliasInJoinIsRemapped() { 00545 $selectFields = 'cat.uid, cat_mm.uid_local, news.uid'; 00546 $fromTables = 'tt_news_cat AS cat' . 00547 ' INNER JOIN tt_news_cat_mm AS cat_mm ON cat.uid = cat_mm.uid_foreign' . 00548 ' INNER JOIN tt_news AS news ON news.uid = cat_mm.uid_local'; 00549 $whereClause = '1=1'; 00550 $groupBy = ''; 00551 $orderBy = ''; 00552 00553 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00554 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00555 00556 $expected = 'SELECT "cat"."cat_uid", "cat_mm"."local_uid", "news"."news_uid"'; 00557 $expected .= ' FROM "ext_tt_news_cat" AS "cat"'; 00558 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat"."cat_uid"="cat_mm"."uid_foreign"'; 00559 $expected .= ' INNER JOIN "ext_tt_news" AS "news" ON "news"."news_uid"="cat_mm"."local_uid"'; 00560 $expected .= ' WHERE 1 = 1'; 00561 $this->assertEquals($expected, $query); 00562 } 00563 00564 /** 00565 * @test 00566 * @see http://bugs.typo3.org/view.php?id=14372 00567 */ 00568 public function aliasRemappingWithInSubqueryDoesNotAffectMainQuery() { 00569 $selectFields = 'foo.uid'; 00570 $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid'; 00571 $whereClause = 'tt_news_cat_mm.uid_foreign IN (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)'; 00572 $groupBy = ''; 00573 $orderBy = 'foo.uid'; 00574 00575 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00576 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00577 00578 $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"'; 00579 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"'; 00580 $expected .= ' WHERE "ext_tt_news_cat_mm"."uid_foreign" IN ('; 00581 $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0'; 00582 $expected .= ')'; 00583 $expected .= ' ORDER BY "foo"."news_uid"'; 00584 $this->assertEquals($expected, $query); 00585 } 00586 00587 /** 00588 * @test 00589 * @see http://bugs.typo3.org/view.php?id=14372 00590 */ 00591 public function aliasRemappingWithExistsSubqueryDoesNotAffectMainQuery() { 00592 $selectFields = 'foo.uid'; 00593 $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid'; 00594 $whereClause = 'EXISTS (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)'; 00595 $groupBy = ''; 00596 $orderBy = 'foo.uid'; 00597 00598 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00599 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00600 00601 $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"'; 00602 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"'; 00603 $expected .= ' WHERE EXISTS ('; 00604 $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0'; 00605 $expected .= ')'; 00606 $expected .= ' ORDER BY "foo"."news_uid"'; 00607 $this->assertEquals($expected, $query); 00608 } 00609 00610 /** 00611 * @test 00612 * @see http://bugs.typo3.org/view.php?id=14372 00613 */ 00614 public function aliasRemappingSupportsNestedSubqueries() { 00615 $selectFields = 'foo.uid'; 00616 $fromTables = 'tt_news AS foo'; 00617 $whereClause = 'uid IN (' . 00618 'SELECT foobar.uid_local FROM tt_news_cat_mm AS foobar WHERE uid_foreign IN (' . 00619 'SELECT uid FROM tt_news_cat WHERE deleted = 0' . 00620 '))'; 00621 $groupBy = ''; 00622 $orderBy = ''; 00623 00624 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00625 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00626 00627 $expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"'; 00628 $expected .= ' WHERE "news_uid" IN ('; 00629 $expected .= 'SELECT "foobar"."local_uid" FROM "ext_tt_news_cat_mm" AS "foobar" WHERE "uid_foreign" IN ('; 00630 $expected .= 'SELECT "cat_uid" FROM "ext_tt_news_cat" WHERE "deleted" = 0'; 00631 $expected .= ')'; 00632 $expected .= ')'; 00633 $this->assertEquals($expected, $query); 00634 } 00635 00636 /** 00637 * @test 00638 * @see http://bugs.typo3.org/view.php?id=14372 00639 */ 00640 public function remappingDoesNotMixUpAliasesInSubquery() { 00641 $selectFields = 'pages.uid'; 00642 $fromTables = 'tt_news AS pages INNER JOIN tt_news_cat_mm AS cat_mm ON cat_mm.uid_local = pages.uid'; 00643 $whereClause = 'pages.pid IN (SELECT uid FROM pages WHERE deleted = 0 AND cat_mm.uid_local != 100)'; 00644 $groupBy = ''; 00645 $orderBy = 'pages.uid'; 00646 00647 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00648 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00649 00650 $expected = 'SELECT "pages"."news_uid" FROM "ext_tt_news" AS "pages"'; 00651 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat_mm"."local_uid"="pages"."news_uid"'; 00652 $expected .= ' WHERE "pages"."pid" IN ('; 00653 $expected .= 'SELECT "page_uid" FROM "my_pages" WHERE "deleted" = 0 AND "cat_mm"."local_uid" != 100'; 00654 $expected .= ')'; 00655 $expected .= ' ORDER BY "pages"."news_uid"'; 00656 $this->assertEquals($expected, $query); 00657 } 00658 00659 /** 00660 * @test 00661 * @see http://bugs.typo3.org/view.php?id=14479 00662 */ 00663 public function likeIsRemappedAccordingToFieldType() { 00664 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00665 '*', 00666 'tt_content', 00667 'tt_content.bodytext LIKE \'foo%\'' 00668 )); 00669 $expected = 'SELECT * FROM "tt_content" WHERE (dbms_lob.instr(LOWER("tt_content"."bodytext"), \'foo\',1,1) > 0)'; 00670 $this->assertEquals($expected, $select); 00671 00672 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00673 '*', 00674 'fe_users', 00675 'fe_users.usergroup LIKE \'2\'' 00676 )); 00677 $expected = 'SELECT * FROM "fe_users" WHERE (instr(LOWER("fe_users"."usergroup"), \'2\',1,1) > 0)'; 00678 $this->assertEquals($expected, $select); 00679 } 00680 00681 /** 00682 * @test 00683 * @see http://bugs.typo3.org/view.php?id=15253 00684 */ 00685 public function notLikeIsRemappedAccordingToFieldType() { 00686 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00687 '*', 00688 'tt_content', 00689 'tt_content.bodytext NOT LIKE \'foo%\'' 00690 )); 00691 $expected = 'SELECT * FROM "tt_content" WHERE NOT (dbms_lob.instr(LOWER("tt_content"."bodytext"), \'foo\',1,1) > 0)'; 00692 $this->assertEquals($expected, $select); 00693 00694 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00695 '*', 00696 'fe_users', 00697 'fe_users.usergroup NOT LIKE \'2\'' 00698 )); 00699 $expected = 'SELECT * FROM "fe_users" WHERE NOT (instr(LOWER("fe_users"."usergroup"), \'2\',1,1) > 0)'; 00700 $this->assertEquals($expected, $select); 00701 } 00702 00703 /** 00704 * @test 00705 * @see http://bugs.typo3.org/view.php?id=14479 00706 */ 00707 public function instrIsUsedForCEOnPages() { 00708 $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00709 '*', 00710 'tt_content', 00711 'uid IN (62) AND tt_content.deleted=0 AND tt_content.t3ver_state<=0' . 00712 ' AND tt_content.hidden=0 AND (tt_content.starttime<=1264487640)' . 00713 ' AND (tt_content.endtime=0 OR tt_content.endtime>1264487640)' . 00714 ' AND (tt_content.fe_group=\'\' OR tt_content.fe_group IS NULL OR tt_content.fe_group=\'0\'' . 00715 ' OR (tt_content.fe_group LIKE \'%,0,%\' OR tt_content.fe_group LIKE \'0,%\' OR tt_content.fe_group LIKE \'%,0\'' . 00716 ' OR tt_content.fe_group=\'0\')' . 00717 ' OR (tt_content.fe_group LIKE\'%,-1,%\' OR tt_content.fe_group LIKE \'-1,%\' OR tt_content.fe_group LIKE \'%,-1\'' . 00718 ' OR tt_content.fe_group=\'-1\'))' 00719 )); 00720 $expected = 'SELECT * FROM "tt_content"'; 00721 $expected .= ' WHERE "uid" IN (62) AND "tt_content"."deleted" = 0 AND "tt_content"."t3ver_state" <= 0'; 00722 $expected .= ' AND "tt_content"."hidden" = 0 AND ("tt_content"."starttime" <= 1264487640)'; 00723 $expected .= ' AND ("tt_content"."endtime" = 0 OR "tt_content"."endtime" > 1264487640)'; 00724 $expected .= ' AND ("tt_content"."fe_group" = \'\' OR "tt_content"."fe_group" IS NULL OR "tt_content"."fe_group" = \'0\''; 00725 $expected .= ' OR ((instr(LOWER("tt_content"."fe_group"), \',0,\',1,1) > 0)'; 00726 $expected .= ' OR (instr(LOWER("tt_content"."fe_group"), \'0,\',1,1) > 0)'; 00727 $expected .= ' OR (instr(LOWER("tt_content"."fe_group"), \',0\',1,1) > 0)'; 00728 $expected .= ' OR "tt_content"."fe_group" = \'0\')'; 00729 $expected .= ' OR ((instr(LOWER("tt_content"."fe_group"), \',-1,\',1,1) > 0)'; 00730 $expected .= ' OR (instr(LOWER("tt_content"."fe_group"), \'-1,\',1,1) > 0)'; 00731 $expected .= ' OR (instr(LOWER("tt_content"."fe_group"), \',-1\',1,1) > 0)'; 00732 $expected .= ' OR "tt_content"."fe_group" = \'-1\'))'; 00733 $this->assertEquals($expected, $select); 00734 } 00735 00736 /////////////////////////////////////// 00737 // Tests concerning DB management 00738 /////////////////////////////////////// 00739 00740 /** 00741 * @test 00742 * @see http://bugs.typo3.org/view.php?id=12670 00743 */ 00744 public function notNullableColumnsWithDefaultEmptyStringAreCreatedAsNullable() { 00745 $parseString = ' 00746 CREATE TABLE tx_realurl_uniqalias ( 00747 uid int(11) NOT NULL auto_increment, 00748 tstamp int(11) DEFAULT \'0\' NOT NULL, 00749 tablename varchar(60) DEFAULT \'\' NOT NULL, 00750 field_alias varchar(255) DEFAULT \'\' NOT NULL, 00751 field_id varchar(60) DEFAULT \'\' NOT NULL, 00752 value_alias varchar(255) DEFAULT \'\' NOT NULL, 00753 value_id int(11) DEFAULT \'0\' NOT NULL, 00754 lang int(11) DEFAULT \'0\' NOT NULL, 00755 expire int(11) DEFAULT \'0\' NOT NULL, 00756 00757 PRIMARY KEY (uid), 00758 KEY tablename (tablename), 00759 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire), 00760 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire) 00761 ); 00762 '; 00763 00764 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString); 00765 $this->assertTrue(is_array($components), 'Not an array: ' . $components); 00766 00767 $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components); 00768 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands); 00769 $this->assertEquals(4, count($sqlCommands)); 00770 00771 $expected = $this->cleanSql(' 00772 CREATE TABLE "tx_realurl_uniqalias" ( 00773 "uid" NUMBER(20) NOT NULL, 00774 "tstamp" NUMBER(20) DEFAULT 0, 00775 "tablename" VARCHAR(60) DEFAULT \'\', 00776 "field_alias" VARCHAR(255) DEFAULT \'\', 00777 "field_id" VARCHAR(60) DEFAULT \'\', 00778 "value_alias" VARCHAR(255) DEFAULT \'\', 00779 "value_id" NUMBER(20) DEFAULT 0, 00780 "lang" NUMBER(20) DEFAULT 0, 00781 "expire" NUMBER(20) DEFAULT 0, 00782 PRIMARY KEY ("uid") 00783 ) 00784 '); 00785 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0])); 00786 } 00787 00788 /** 00789 * @test 00790 * @see http://bugs.typo3.org/view.php?id=11142 00791 * @see http://bugs.typo3.org/view.php?id=12670 00792 */ 00793 public function defaultValueIsProperlyQuotedInCreateTable() { 00794 $parseString = ' 00795 CREATE TABLE tx_test ( 00796 uid int(11) NOT NULL auto_increment, 00797 lastname varchar(60) DEFAULT \'unknown\' NOT NULL, 00798 firstname varchar(60) DEFAULT \'\' NOT NULL, 00799 language varchar(2) NOT NULL, 00800 tstamp int(11) DEFAULT \'0\' NOT NULL, 00801 00802 PRIMARY KEY (uid), 00803 KEY name (name) 00804 ); 00805 '; 00806 00807 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString); 00808 $this->assertTrue(is_array($components), 'Not an array: ' . $components); 00809 00810 $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components); 00811 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands); 00812 $this->assertEquals(2, count($sqlCommands)); 00813 00814 $expected = $this->cleanSql(' 00815 CREATE TABLE "tx_test" ( 00816 "uid" NUMBER(20) NOT NULL, 00817 "lastname" VARCHAR(60) DEFAULT \'unknown\', 00818 "firstname" VARCHAR(60) DEFAULT \'\', 00819 "language" VARCHAR(2) DEFAULT \'\', 00820 "tstamp" NUMBER(20) DEFAULT 0, 00821 PRIMARY KEY ("uid") 00822 ) 00823 '); 00824 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0])); 00825 } 00826 00827 /////////////////////////////////////// 00828 // Tests concerning subqueries 00829 /////////////////////////////////////// 00830 00831 /** 00832 * @test 00833 * @see http://bugs.typo3.org/view.php?id=12758 00834 */ 00835 public function inWhereClauseWithSubqueryIsProperlyQuoted() { 00836 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00837 '*', 00838 'tx_crawler_queue', 00839 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)' 00840 )); 00841 $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "process_id" FROM "tx_crawler_process" WHERE "active" = 0 AND "deleted" = 0)'; 00842 $this->assertEquals($expected, $query); 00843 } 00844 00845 /** 00846 * @test 00847 * @see http://bugs.typo3.org/view.php?id=12758 00848 */ 00849 public function subqueryIsRemappedForInWhereClause() { 00850 $selectFields = '*'; 00851 $fromTables = 'tx_crawler_queue'; 00852 $whereClause = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)'; 00853 $groupBy = ''; 00854 $orderBy = ''; 00855 00856 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00857 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00858 00859 $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "ps_id" FROM "tx_crawler_ps" WHERE "is_active" = 0 AND "deleted" = 0)'; 00860 $this->assertEquals($expected, $query); 00861 } 00862 00863 /** 00864 * @test 00865 * @see http://bugs.typo3.org/view.php?id=12800 00866 */ 00867 public function cachingFrameworkQueryIsSupported() { 00868 $currentTime = time(); 00869 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery( 00870 'cachingframework_cache_hash_tags', 00871 'identifier IN (' . 00872 $GLOBALS['TYPO3_DB']->SELECTsubquery( 00873 'identifier', 00874 'cachingframework_cache_pages', 00875 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0' 00876 ) . 00877 ')' 00878 )); 00879 $expected = 'DELETE FROM "cachingframework_cache_hash_tags" WHERE "identifier" IN ('; 00880 $expected .= 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0'; 00881 $expected .= ')'; 00882 $this->assertEquals($expected, $query); 00883 } 00884 00885 /** 00886 * @test 00887 * @see http://bugs.typo3.org/view.php?id=12800 00888 */ 00889 public function cachingFrameworkQueryIsRemapped() { 00890 $currentTime = time(); 00891 $table = 'cachingframework_cache_hash_tags'; 00892 $where = 'identifier IN (' . 00893 $GLOBALS['TYPO3_DB']->SELECTsubquery( 00894 'identifier', 00895 'cachingframework_cache_pages', 00896 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0' 00897 ) . 00898 ')'; 00899 00900 // Perform remapping (as in method exec_DELETEquery) 00901 if ($tableArray = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table)) { 00902 // Where clause: 00903 $whereParts = $GLOBALS['TYPO3_DB']->SQLparser->parseWhereClause($where); 00904 $GLOBALS['TYPO3_DB']->_callRef('map_sqlParts', $whereParts, $tableArray[0]['table']); 00905 $where = $GLOBALS['TYPO3_DB']->SQLparser->compileWhereClause($whereParts, FALSE); 00906 00907 // Table name: 00908 if ($GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName']) { 00909 $table = $GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName']; 00910 } 00911 } 00912 00913 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery($table, $where)); 00914 $expected = 'DELETE FROM "cf_cache_hash_tags" WHERE "identifier" IN ('; 00915 $expected .= 'SELECT "identifier" FROM "cf_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0'; 00916 $expected .= ')'; 00917 $this->assertEquals($expected, $query); 00918 } 00919 00920 /** 00921 * @test 00922 * @see http://bugs.typo3.org/view.php?id=12758 00923 */ 00924 public function existsWhereClauseIsProperlyQuoted() { 00925 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00926 '*', 00927 'tx_crawler_process', 00928 'active = 0 AND NOT EXISTS (' . 00929 $GLOBALS['TYPO3_DB']->SELECTsubquery( 00930 '*', 00931 'tx_crawler_queue', 00932 'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)' 00933 ) . 00934 ')' 00935 )); 00936 $expected = 'SELECT * FROM "tx_crawler_process" WHERE "active" = 0 AND NOT EXISTS ('; 00937 $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_process"."process_id" AND "tx_crawler_queue"."exec_time" = 0'; 00938 $expected .= ')'; 00939 $this->assertEquals($expected, $query); 00940 } 00941 00942 /** 00943 * @test 00944 * @see http://bugs.typo3.org/view.php?id=12758 00945 */ 00946 public function subqueryIsRemappedForExistsWhereClause() { 00947 $selectFields = '*'; 00948 $fromTables = 'tx_crawler_process'; 00949 $whereClause = 'active = 0 AND NOT EXISTS (' . 00950 $GLOBALS['TYPO3_DB']->SELECTsubquery( 00951 '*', 00952 'tx_crawler_queue', 00953 'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0' 00954 ) . 00955 ')'; 00956 $groupBy = ''; 00957 $orderBy = ''; 00958 00959 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 00960 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 00961 00962 $expected = 'SELECT * FROM "tx_crawler_ps" WHERE "is_active" = 0 AND NOT EXISTS ('; 00963 $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_ps"."ps_id" AND "tx_crawler_queue"."exec_time" = 0'; 00964 $expected .= ')'; 00965 $this->assertEquals($expected, $query); 00966 } 00967 00968 /////////////////////////////////////// 00969 // Tests concerning advanced operators 00970 /////////////////////////////////////// 00971 00972 /** 00973 * @test 00974 * @see http://bugs.typo3.org/view.php?id=13135 00975 */ 00976 public function caseStatementIsProperlyQuoted() { 00977 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 00978 'process_id, CASE active' . 00979 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') . 00980 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') . 00981 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') . 00982 ' END AS number', 00983 'tx_crawler_process', 00984 '1=1' 00985 )); 00986 $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'; 00987 $this->assertEquals($expected, $query); 00988 } 00989 00990 /** 00991 * @test 00992 * @see http://bugs.typo3.org/view.php?id=13135 00993 */ 00994 public function caseStatementIsProperlyRemapped() { 00995 $selectFields = 'process_id, CASE active' . 00996 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') . 00997 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') . 00998 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') . 00999 ' END AS number'; 01000 $fromTables = 'tx_crawler_process'; 01001 $whereClause = '1=1'; 01002 $groupBy = ''; 01003 $orderBy = ''; 01004 01005 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 01006 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 01007 01008 $expected = 'SELECT "ps_id", CASE "is_active" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" '; 01009 $expected .= 'FROM "tx_crawler_ps" WHERE 1 = 1'; 01010 $this->assertEquals($expected, $query); 01011 } 01012 01013 /** 01014 * @test 01015 * @see http://bugs.typo3.org/view.php?id=13135 01016 */ 01017 public function caseStatementWithExternalTableIsProperlyRemapped() { 01018 $selectFields = 'process_id, CASE tt_news.uid' . 01019 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tt_news') . 01020 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tt_news') . 01021 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tt_news') . 01022 ' END AS number'; 01023 $fromTables = 'tx_crawler_process, tt_news'; 01024 $whereClause = '1=1'; 01025 $groupBy = ''; 01026 $orderBy = ''; 01027 01028 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 01029 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 01030 01031 $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" '; 01032 $expected .= 'FROM "tx_crawler_ps", "ext_tt_news" WHERE 1 = 1'; 01033 $this->assertEquals($expected, $query); 01034 } 01035 01036 /** 01037 * @test 01038 * @see http://bugs.typo3.org/view.php?id=13134 01039 */ 01040 public function locateStatementIsProperlyQuoted() { 01041 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 01042 '*, CASE WHEN' . 01043 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure)>0 THEN 2' . 01044 ' ELSE 1' . 01045 ' END AS scope', 01046 'tx_templavoila_tmplobj', 01047 '1=1' 01048 )); 01049 $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\') > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1'; 01050 $this->assertEquals($expected, $query); 01051 } 01052 01053 /** 01054 * @test 01055 * @see http://bugs.typo3.org/view.php?id=13134 01056 */ 01057 public function locateStatementWithPositionIsProperlyQuoted() { 01058 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 01059 '*, CASE WHEN' . 01060 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' . 01061 ' ELSE 1' . 01062 ' END AS scope', 01063 'tx_templavoila_tmplobj', 01064 '1=1' 01065 )); 01066 $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\', 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1'; 01067 $this->assertEquals($expected, $query); 01068 } 01069 01070 /** 01071 * @test 01072 * @see http://bugs.typo3.org/view.php?id=6196 01073 */ 01074 public function IfNullIsProperlyRemapped() { 01075 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 01076 '*', 01077 'tt_news_cat_mm', 01078 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)' 01079 )); 01080 $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE NVL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)'; 01081 $this->assertEquals($expected, $query); 01082 } 01083 01084 /** 01085 * @test 01086 * @see http://bugs.typo3.org/view.php?id=14985 01087 */ 01088 public function findInSetIsProperlyRemapped() { 01089 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 01090 '*', 01091 'fe_users', 01092 'FIND_IN_SET(10, usergroup)' 01093 )); 01094 $expected = 'SELECT * FROM "fe_users" WHERE \',\'||"usergroup"||\',\' LIKE \'%,10,%\''; 01095 $this->assertEquals($expected, $query); 01096 } 01097 01098 /** 01099 * @test 01100 * @see http://bugs.typo3.org/view.php?id=14985 01101 */ 01102 public function findInSetFieldIsProperlyRemapped() { 01103 $selectFields = 'fe_group'; 01104 $fromTables = 'tt_news'; 01105 $whereClause = 'FIND_IN_SET(10, fe_group)'; 01106 $groupBy = ''; 01107 $orderBy = ''; 01108 01109 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy); 01110 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters)); 01111 01112 $expected = 'SELECT "usergroup" FROM "ext_tt_news" WHERE \',\'||"ext_tt_news"."usergroup"||\',\' LIKE \'%,10,%\''; 01113 $this->assertEquals($expected, $query); 01114 } 01115 01116 /** 01117 * @test 01118 * @see http://bugs.typo3.org/view.php?id=14818 01119 */ 01120 public function listQueryIsProperlyRemapped() { 01121 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 01122 '*', 01123 'fe_users', 01124 $GLOBALS['TYPO3_DB']->listQuery('usergroup', 10, 'fe_users') 01125 )); 01126 $expected = 'SELECT * FROM "fe_users" WHERE \',\'||"usergroup"||\',\' LIKE \'%,10,%\''; 01127 $this->assertEquals($expected, $query); 01128 } 01129 01130 /** 01131 * @test 01132 * @see http://bugs.typo3.org/view.php?id=12535 01133 */ 01134 public function likeBinaryOperatorIsRemoved() { 01135 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery( 01136 '*', 01137 'tt_content', 01138 'bodytext LIKE BINARY \'test\'' 01139 )); 01140 $expected = 'SELECT * FROM "tt_content" WHERE (dbms_lob.instr(LOWER("bodytext"), \'test\',1,1) > 0)'; 01141 $this->assertEquals($expected, $query); 01142 } 01143 } 01144 01145 ?>
1.8.0