Bug #22640 » 14372_v2.diff
| class.ux_t3lib_db.php (working copy) | ||
|---|---|---|
|
protected function map_assocArray($input, $tables, $rev = FALSE) {
|
||
|
// Traverse tables from query (hopefully only one table):
|
||
|
foreach ($tables as $tableCfg) {
|
||
|
if (is_array($this->mapping[$tableCfg['table']]['mapFieldNames'])) {
|
||
|
$tableKey = $this->getMappingKey($tableCfg['table']);
|
||
|
if (is_array($this->mapping[$tableKey]['mapFieldNames'])) {
|
||
|
// Get the map (reversed if needed):
|
||
|
if ($rev) {
|
||
|
$theMap = array_flip($this->mapping[$tableCfg['table']]['mapFieldNames']);
|
||
|
$theMap = array_flip($this->mapping[$tableKey]['mapFieldNames']);
|
||
|
} else {
|
||
|
$theMap = $this->mapping[$tableCfg['table']]['mapFieldNames'];
|
||
|
$theMap = $this->mapping[$tableKey]['mapFieldNames'];
|
||
|
}
|
||
|
// Traverse selected record, map fieldnames:
|
||
| ... | ... | |
|
* @see exec_SELECTquery()
|
||
|
*/
|
||
|
protected function map_remapSELECTQueryParts(&$select_fields, &$from_table, &$where_clause, &$groupBy, &$orderBy) {
|
||
|
// Backup current mapping as it may be altered if aliases on mapped tables are found
|
||
|
$backupMapping = $this->mapping;
|
||
|
// Tables:
|
||
|
$tables = $this->SQLparser->parseFromTables($from_table);
|
||
|
$defaultTable = $tables[0]['table'];
|
||
|
// Prepare mapping for aliased tables. This will copy the definition of the original table name.
|
||
|
// The alias is prefixed with a database-incompatible character to prevent naming clash with real table name
|
||
|
// Further access to $this->mapping should be made through $this->getMappingKey() method
|
||
|
foreach ($tables as $k => $v) {
|
||
|
if ($this->mapping[$v['table']]['mapTableName']) {
|
||
|
$tables[$k]['table'] = $this->mapping[$v['table']]['mapTableName'];
|
||
|
if ($v['as'] && is_array($this->mapping[$v['table']]['mapFieldNames'])) {
|
||
|
$this->mapping['*' . $v['as']]['mapFieldNames'] =& $this->mapping[$v['table']]['mapFieldNames'];
|
||
|
}
|
||
|
if (is_array($v['JOIN'])) {
|
||
|
foreach ($v['JOIN'] as $joinCnt => $join) {
|
||
|
if ($join['as'] && is_array($this->mapping[$join['withTable']]['mapFieldNames'])) {
|
||
|
$this->mapping['*' . $join['as']]['mapFieldNames'] =& $this->mapping[$join['withTable']]['mapFieldNames'];
|
||
|
}
|
||
|
}
|
||
|
}
|
||
|
}
|
||
|
foreach ($tables as $k => $v) {
|
||
|
$tableKey = $this->getMappingKey($v['table']);
|
||
|
if ($this->mapping[$tableKey]['mapTableName']) {
|
||
|
$tables[$k]['table'] = $this->mapping[$tableKey]['mapTableName'];
|
||
|
}
|
||
|
// Mapping JOINS
|
||
|
if (is_array($v['JOIN'])) {
|
||
|
foreach($v['JOIN'] as $joinCnt => $join) {
|
||
|
// Mapping withTable of the JOIN
|
||
|
if ($this->mapping[$join['withTable']]['mapTableName']) {
|
||
|
$tables[$k]['JOIN'][$joinCnt]['withTable'] = $this->mapping[$join['withTable']]['mapTableName'];
|
||
|
$withTableKey = $this->getMappingKey($join['withTable']);
|
||
|
if ($this->mapping[$withTableKey]['mapTableName']) {
|
||
|
$tables[$k]['JOIN'][$joinCnt]['withTable'] = $this->mapping[$withTableKey]['mapTableName'];
|
||
|
}
|
||
|
$onPartsArray = array();
|
||
|
// Mapping ON parts of the JOIN
|
||
|
if (is_array($tables[$k]['JOIN'][$joinCnt]['ON'])) {
|
||
|
foreach ($tables[$k]['JOIN'][$joinCnt]['ON'] as &$condition) {
|
||
|
// Left side of the comparator
|
||
|
if (isset($this->mapping[$condition['left']['table']]['mapFieldNames'][$condition['left']['field']])) {
|
||
|
$condition['left']['field'] = $this->mapping[$condition['left']['table']]['mapFieldNames'][$condition['left']['field']];
|
||
|
$leftTableKey = $this->getMappingKey($condition['left']['table']);
|
||
|
if (isset($this->mapping[$leftTableKey]['mapFieldNames'][$condition['left']['field']])) {
|
||
|
$condition['left']['field'] = $this->mapping[$leftTableKey]['mapFieldNames'][$condition['left']['field']];
|
||
|
}
|
||
|
if (isset($this->mapping[$condition['left']['table']]['mapTableName'])) {
|
||
|
$condition['left']['table'] = $this->mapping[$condition['left']['table']]['mapTableName'];
|
||
|
if (isset($this->mapping[$leftTableKey]['mapTableName'])) {
|
||
|
$condition['left']['table'] = $this->mapping[$leftTableKey]['mapTableName'];
|
||
|
}
|
||
|
// Right side of the comparator
|
||
|
if (isset($this->mapping[$condition['right']['table']]['mapFieldNames'][$condition['right']['field']])) {
|
||
|
$condition['right']['field'] = $this->mapping[$condition['right']['table']]['mapFieldNames'][$condition['right']['field']];
|
||
|
$rightTableKey = $this->getMappingKey($condition['right']['table']);
|
||
|
if (isset($this->mapping[$rightTableKey]['mapFieldNames'][$condition['right']['field']])) {
|
||
|
$condition['right']['field'] = $this->mapping[$rightTableKey]['mapFieldNames'][$condition['right']['field']];
|
||
|
}
|
||
|
if (isset($this->mapping[$condition['right']['table']]['mapTableName'])) {
|
||
|
$condition['right']['table'] = $this->mapping[$condition['right']['table']]['mapTableName'];
|
||
|
if (isset($this->mapping[$rightTableKey]['mapTableName'])) {
|
||
|
$condition['right']['table'] = $this->mapping[$rightTableKey]['mapTableName'];
|
||
|
}
|
||
|
}
|
||
|
}
|
||
| ... | ... | |
|
$expFields = $this->SQLparser->parseFieldList($orderBy);
|
||
|
$this->map_sqlParts($expFields,$defaultTable);
|
||
|
$orderBy = $this->SQLparser->compileFieldList($expFields);
|
||
|
// Restore the original mapping
|
||
|
$this->mapping = $backupMapping;
|
||
|
}
|
||
|
/**
|
||
|
* Returns the key to be used when retrieving information from $this->mapping. This ensures
|
||
|
* that mapping from aliased tables is properly retrieved.
|
||
|
*
|
||
|
* @param string $tableName
|
||
|
* @return string
|
||
|
*/
|
||
|
protected function getMappingKey($tableName) {
|
||
|
return (isset($this->mapping['*' . $tableName]) ? '*' . $tableName : $tableName);
|
||
|
}
|
||
|
/**
|
||
|
* Generic mapping of table/field names arrays (as parsed by tx_dbal_sqlengine)
|
||
|
*
|
||
|
* @param array Array with parsed SQL parts; Takes both fields, tables, where-parts, group and order-by. Passed by reference.
|
||
| ... | ... | |
|
* @see map_remapSELECTQueryParts()
|
||
|
*/
|
||
|
protected function map_sqlParts(&$sqlPartArray, $defaultTable) {
|
||
|
$defaultTableKey = $this->getMappingKey($defaultTable);
|
||
|
// Traverse sql Part array:
|
||
|
if (is_array($sqlPartArray)) {
|
||
|
foreach ($sqlPartArray as $k => $v) {
|
||
| ... | ... | |
|
case 'CASE':
|
||
|
if (isset($sqlPartArray[$k]['case_field'])) {
|
||
|
$fieldArray = explode('.', $sqlPartArray[$k]['case_field']);
|
||
|
if (count($fieldArray) == 1 && is_array($this->mapping[$defaultTable]['mapFieldNames']) && isset($this->mapping[$defaultTable]['mapFieldNames'][$fieldArray[0]])) {
|
||
|
$sqlPartArray[$k]['case_field'] = $this->mapping[$defaultTable]['mapFieldNames'][$fieldArray[0]];
|
||
|
if (count($fieldArray) == 1 && is_array($this->mapping[$defaultTableKey]['mapFieldNames']) && isset($this->mapping[$defaultTableKey]['mapFieldNames'][$fieldArray[0]])) {
|
||
|
$sqlPartArray[$k]['case_field'] = $this->mapping[$defaultTableKey]['mapFieldNames'][$fieldArray[0]];
|
||
|
}
|
||
|
elseif (count($fieldArray) == 2) {
|
||
|
// Map the external table
|
||
|
$table = $fieldArray[0];
|
||
|
if (isset($this->mapping[$fieldArray[0]]['mapTableName'])) {
|
||
|
$table = $this->mapping[$fieldArray[0]]['mapTableName'];
|
||
|
$tableKey = $this->getMappingKey($table);
|
||
|
if (isset($this->mapping[$tableKey]['mapTableName'])) {
|
||
|
$table = $this->mapping[$tableKey]['mapTableName'];
|
||
|
}
|
||
|
// Map the field itself
|
||
|
$field = $fieldArray[1];
|
||
|
if (is_array($this->mapping[$fieldArray[0]]['mapFieldNames']) && isset($this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]])) {
|
||
|
$field = $this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]];
|
||
|
if (is_array($this->mapping[$tableKey]['mapFieldNames']) && isset($this->mapping[$tableKey]['mapFieldNames'][$fieldArray[1]])) {
|
||
|
$field = $this->mapping[$tableKey]['mapFieldNames'][$fieldArray[1]];
|
||
|
}
|
||
|
$sqlPartArray[$k]['case_field'] = $table . '.' . $field;
|
||
|
}
|
||
| ... | ... | |
|
case 'LOCATE':
|
||
|
// For the field, look for table mapping (generic):
|
||
|
$t = $sqlPartArray[$k]['func']['table'] ? $sqlPartArray[$k]['func']['table'] : $defaultTable;
|
||
|
$t = $this->getMappingKey($t);
|
||
|
if (is_array($this->mapping[$t]['mapFieldNames']) && $this->mapping[$t]['mapFieldNames'][$sqlPartArray[$k]['func']['field']]) {
|
||
|
$sqlPartArray[$k]['func']['field'] = $this->mapping[$t]['mapFieldNames'][$sqlPartArray[$k]['func']['field']];
|
||
|
}
|
||
| ... | ... | |
|
} else {
|
||
|
// For the field, look for table mapping (generic):
|
||
|
$t = $sqlPartArray[$k]['table'] ? $sqlPartArray[$k]['table'] : $defaultTable;
|
||
|
$t = $this->getMappingKey($t);
|
||
|
// Mapping field name, if set:
|
||
|
if (is_array($this->mapping[$t]['mapFieldNames']) && isset($this->mapping[$t]['mapFieldNames'][$sqlPartArray[$k]['field']])) {
|
||
| ... | ... | |
|
elseif (count($fieldArray) == 2) {
|
||
|
// Map the external table
|
||
|
$table = $fieldArray[0];
|
||
|
if (isset($this->mapping[$fieldArray[0]]['mapTableName'])) {
|
||
|
$table = $this->mapping[$fieldArray[0]]['mapTableName'];
|
||
|
$tableKey = $this->getMappingKey($table);
|
||
|
if (isset($this->mapping[$tableKey]['mapTableName'])) {
|
||
|
$table = $this->mapping[$tableKey]['mapTableName'];
|
||
|
}
|
||
|
// Map the field itself
|
||
|
$field = $fieldArray[1];
|
||
|
if (is_array($this->mapping[$fieldArray[0]]['mapFieldNames']) && isset($this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]])) {
|
||
|
$field = $this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]];
|
||
|
if (is_array($this->mapping[$tableKey]['mapFieldNames']) && isset($this->mapping[$tableKey]['mapFieldNames'][$fieldArray[1]])) {
|
||
|
$field = $this->mapping[$tableKey]['mapFieldNames'][$fieldArray[1]];
|
||
|
}
|
||
|
$sqlPartArray[$k]['func_content.'][0]['func_content'] = $table . '.' . $field;
|
||
|
$sqlPartArray[$k]['func_content'] = $table . '.' . $field;
|
||
| ... | ... | |
|
// Do we have a function (e.g., CONCAT)
|
||
|
if (isset($v['value']['operator'])) {
|
||
|
foreach ($sqlPartArray[$k]['value']['args'] as $argK => $fieldDef) {
|
||
|
if (isset($this->mapping[$fieldDef['table']]['mapTableName'])) {
|
||
|
$sqlPartArray[$k]['value']['args'][$argK]['table'] = $this->mapping[$fieldDef['table']]['mapTableName'];
|
||
|
$tableKey = $this->getMappingKey($fieldDef['table']);
|
||
|
if (isset($this->mapping[$tableKey]['mapTableName'])) {
|
||
|
$sqlPartArray[$k]['value']['args'][$argK]['table'] = $this->mapping[$tableKey]['mapTableName'];
|
||
|
}
|
||
|
if (is_array($this->mapping[$fieldDef['table']]['mapFieldNames']) && isset($this->mapping[$fieldDef['table']]['mapFieldNames'][$fieldDef['field']])) {
|
||
|
$sqlPartArray[$k]['value']['args'][$argK]['field'] = $this->mapping[$fieldDef['table']]['mapFieldNames'][$fieldDef['field']];
|
||
|
if (is_array($this->mapping[$tableKey]['mapFieldNames']) && isset($this->mapping[$tableKey]['mapFieldNames'][$fieldDef['field']])) {
|
||
|
$sqlPartArray[$k]['value']['args'][$argK]['field'] = $this->mapping[$tableKey]['mapFieldNames'][$fieldDef['field']];
|
||
|
}
|
||
|
}
|
||
|
}
|
||
| ... | ... | |
|
} elseif (count($fieldArray) == 2) {
|
||
|
// Map the external table
|
||
|
$table = $fieldArray[0];
|
||
|
if (isset($this->mapping[$fieldArray[0]]['mapTableName'])) {
|
||
|
$table = $this->mapping[$fieldArray[0]]['mapTableName'];
|
||
|
$tableKey = $this->getMappingKey($table);
|
||
|
if (isset($this->mapping[$tableKey]['mapTableName'])) {
|
||
|
$table = $this->mapping[$tableKey]['mapTableName'];
|
||
|
}
|
||
|
// Map the field itself
|
||
|
$field = $fieldArray[1];
|
||
|
if (is_array($this->mapping[$fieldArray[0]]['mapFieldNames']) && isset($this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]])) {
|
||
|
$field = $this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]];
|
||
|
if (is_array($this->mapping[$tableKey]['mapFieldNames']) && isset($this->mapping[$tableKey]['mapFieldNames'][$fieldArray[1]])) {
|
||
|
$field = $this->mapping[$tableKey]['mapFieldNames'][$fieldArray[1]];
|
||
|
}
|
||
|
$sqlPartArray[$k]['value'][0] = $table . '.' . $field;
|
||
|
}
|
||
|
}
|
||
|
// Map table?
|
||
|
if ($sqlPartArray[$k]['table'] && $this->mapping[$sqlPartArray[$k]['table']]['mapTableName']) {
|
||
|
$sqlPartArray[$k]['table'] = $this->mapping[$sqlPartArray[$k]['table']]['mapTableName'];
|
||
|
$tableKey = $this->getMappingKey($sqlPartArray[$k]['table']);
|
||
|
if ($sqlPartArray[$k]['table'] && $this->mapping[$tableKey]['mapTableName']) {
|
||
|
$sqlPartArray[$k]['table'] = $this->mapping[$tableKey]['mapTableName'];
|
||
|
}
|
||
|
}
|
||
|
}
|
||
| tests/db_oracle_testcase.php (working copy) | ||
|---|---|---|
|
$this->assertEquals($expected, $query);
|
||
|
}
|
||
|
/**
|
||
|
* @test
|
||
|
* @see http://bugs.typo3.org/view.php?id=14372
|
||
|
*/
|
||
|
public function fieldFromAliasIsRemapped() {
|
||
|
$selectFields = 'news.uid';
|
||
|
$fromTables = 'tt_news AS news';
|
||
|
$whereClause = 'news.uid = 1';
|
||
|
$groupBy = '';
|
||
|
$orderBy = '';
|
||
|
$GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
|
||
|
$query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
|
||
|
$expected = 'SELECT "news"."news_uid" FROM "ext_tt_news" AS "news" WHERE "news"."news_uid" = 1';
|
||
|
$this->assertEquals($expected, $query);
|
||
|
}
|
||
|
/**
|
||
|
* Trick here is that we already have a mapping for both table tt_news and table tt_news_cat
|
||
|
* (see tests/fixtures/oci8.config.php) which is used as alias name.
|
||
|
*
|
||
|
* @test
|
||
|
* @see http://bugs.typo3.org/view.php?id=14372
|
||
|
*/
|
||
|
public function fieldFromAliasIsRemappedWithoutBeingTricked() {
|
||
|
$selectFields = 'tt_news_cat.uid';
|
||
|
$fromTables = 'tt_news AS tt_news_cat';
|
||
|
$whereClause = 'tt_news_cat.uid = 1';
|
||
|
$groupBy = '';
|
||
|
$orderBy = '';
|
||
|
$GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
|
||
|
$query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
|
||
|
$expected = 'SELECT "tt_news_cat"."news_uid" FROM "ext_tt_news" AS "tt_news_cat" WHERE "tt_news_cat"."news_uid" = 1';
|
||
|
$this->assertEquals($expected, $query);
|
||
|
}
|
||
|
/**
|
||
|
* @test
|
||
|
* @see http://bugs.typo3.org/view.php?id=14372
|
||
|
*/
|
||
|
public function aliasRemappingDoesNotAlterFurtherQueries() {
|
||
|
$selectFields = 'foo.uid';
|
||
|
$fromTables = 'tt_news AS foo';
|
||
|
$whereClause = 'foo.uid = 1';
|
||
|
$groupBy = '';
|
||
|
$orderBy = '';
|
||
|
// First call to possibly alter (in memory) the mapping from localconf.php
|
||
|
$GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
|
||
|
$selectFields = 'uid';
|
||
|
$fromTables = 'foo';
|
||
|
$whereClause = 'uid = 1';
|
||
|
$groupBy = '';
|
||
|
$orderBy = '';
|
||
|
$GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
|
||
|
$query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
|
||
|
$expected = 'SELECT "uid" FROM "foo" WHERE "uid" = 1';
|
||
|
$this->assertEquals($expected, $query);
|
||
|
}
|
||
|
/**
|
||
|
* @test
|
||
|
* @see http://bugs.typo3.org/view.php?id=14372
|
||
|
*/
|
||
|
public function fieldFromAliasInJoinIsRemapped() {
|
||
|
$selectFields = 'cat.uid, cat_mm.uid_local, news.uid';
|
||
|
$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';
|
||
|
$whereClause = '1=1';
|
||
|
$groupBy = '';
|
||
|
$orderBy = '';
|
||
|
$GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
|
||
|
$query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
|
||
|
$expected = 'SELECT "cat"."cat_uid", "cat_mm"."local_uid", "news"."news_uid"';
|
||
|
$expected .= ' FROM "ext_tt_news_cat" AS "cat"';
|
||
|
$expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat"."cat_uid"="cat_mm"."uid_foreign"';
|
||
|
$expected .= ' INNER JOIN "ext_tt_news" AS "news" ON "news"."news_uid"="cat_mm"."local_uid"';
|
||
|
$expected .= ' WHERE 1 = 1';
|
||
|
$this->assertEquals($expected, $query);
|
||
|
}
|
||
|
///////////////////////////////////////
|
||
|
// Tests concerning DB management
|
||
|
///////////////////////////////////////
|
||