Project

General

Profile

Download (15.1 KB)

Bug #21718 » 12800-dbal_v2.diff

Administrator Admin, 2009-11-29 15:23

View differences:

class.ux_t3lib_sqlparser.php (working copy)
*
* (c) 2004-2009 Kasper Skaarhoj (kasperYYYY@typo3.com)
* (c) 2004-2009 Karsten Dambekalns <karsten@typo3.org>
* (c) 2009 Xavier Perseguers <typo3@perseguers.ch>
* All rights reserved
*
* This script is part of the TYPO3 project. The TYPO3 project is
......
*
* @author Kasper Skaarhoj <kasperYYYY@typo3.com>
* @author Karsten Dambekalns <k.dambekalns@fishfarm.de>
* @author Xavier Perseguers <typo3@perseguers.ch>
*/
......
// Look for ending parenthesis:
$this->nextPart($parseString, '([)])');
$stack[$level][$pnt[$level]]['value'] = $values;
} else if (t3lib_div::inList('IN,NOT IN', $stack[$level][$pnt[$level]]['comparator']) && preg_match('/^[(][[:space:]]*SELECT[[:space:]]+/', $parseString)) {
$this->nextPart($parseString, '^([(])');
$stack[$level][$pnt[$level]]['subquery'] = $this->parseSELECT($parseString);
// Seek to new position in parseString after parsing of the subquery
$parseString = $stack[$level][$pnt[$level]]['subquery']['parseString'];
unset($stack[$level][$pnt[$level]]['subquery']['parseString']);
if (!$this->nextPart($parseString, '^([)])')) {
return 'No ) parenthesis at end of subquery';
}
} else {
// Finding value for comparator:
$stack[$level][$pnt[$level]]['value'] = $this->getValue($parseString, $stack[$level][$pnt[$level]]['comparator']);
......
// Finished, increase pointer:
$pnt[$level]++;
// Checking if we are back to level 0 and we should still decrease level,
// meaning we were probably parsing as subquery and should return here:
if ($level === 0 && preg_match('/^[)]/', $parseString)) {
// Return the stacks lowest level:
return $stack[0];
}
// Checking if the current level is ended, in that case do stack management:
while ($this->nextPart($parseString,'^([)])')) {
$level--; // Decrease level:
......
// Detecting value type; list or plain:
if (t3lib_div::inList('NOTIN,IN', strtoupper(str_replace(array(' ', "\t", "\r", "\n"), '', $v['comparator'])))) {
$valueBuffer = array();
foreach ($v['value'] as $realValue) {
$valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1];
if (isset($v['subquery'])) {
$output .= ' (' . $this->compileSELECT($v['subquery']) . ')';
} else {
$valueBuffer = array();
foreach ($v['value'] as $realValue) {
$valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1];
}
$output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
}
$output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
} else if (isset($v['value']['operator'])) {
$values = array();
foreach ($v['value']['args'] as $fieldDef) {
......
// Detecting value type; list or plain:
if (t3lib_div::inList('NOTIN,IN', strtoupper(str_replace(array(' ', "\t", "\r", "\n"), '', $v['comparator'])))) {
$valueBuffer = array();
foreach ($v['value'] as $realValue) {
$valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1];
if (isset($v['subquery'])) {
$output .= ' (' . $this->compileSELECT($v['subquery']) . ')';
} else {
$valueBuffer = array();
foreach ($v['value'] as $realValue) {
$valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1];
}
$output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
}
$output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
} else if (isset($v['value']['operator'])) {
$values = array();
foreach ($v['value']['args'] as $fieldDef) {
class.ux_t3lib_db.php (working copy)
*
* (c) 2004-2009 Kasper Skaarhoj (kasperYYYY@typo3.com)
* (c) 2004-2009 Karsten Dambekalns <karsten@typo3.org>
* (c) 2009 Xavier Perseguers <typo3@perseguers.ch>
* All rights reserved
*
* This script is part of the TYPO3 project. The TYPO3 project is
......
**************************************/
/**
* Quotes components of a SELECT subquery.
*
* @param array $components Array of SQL query components
* @return array
*/
protected function quoteSELECTsubquery(array $components) {
$components['SELECT'] = $this->_quoteFieldNames($components['SELECT']);
$components['FROM'] = $this->_quoteFromTables($components['FROM']);
$components['WHERE'] = $this->_quoteWhereClause($components['WHERE']);
return $components;
}
/**
* Quotes field (and table) names with the quote character suitable for the DB being used
* Use quoteFieldNames instead!
*
......
if ($this->runningNative()) return $select_fields;
$select_fields = $this->SQLparser->parseFieldList($select_fields);
$select_fields = $this->_quoteFieldNames($select_fields);
return $this->SQLparser->compileFieldList($select_fields);
}
/**
* Quotes field (and table) names in a SQL SELECT clause acccording to DB rules
*
* @param array $select_fields The parsed fields to quote
* @return array
* @see quoteFieldNames()
*/
protected function _quoteFieldNames(array $select_fields) {
foreach ($select_fields as $k => $v) {
if ($select_fields[$k]['field'] != '' && $select_fields[$k]['field'] != '*') {
$select_fields[$k]['field'] = $this->quoteName($select_fields[$k]['field']);
......
}
}
return $this->SQLparser->compileFieldList($select_fields);
return $select_fields;
}
/**
......
if ($this->runningNative()) return $from_table;
$from_table = $this->SQLparser->parseFromTables($from_table);
$from_table = $this->_quoteFromTables($from_table);
return $this->SQLparser->compileFromTables($from_table);
}
/**
* Quotes table names in a SQL FROM clause acccording to DB rules
*
* @param array $from_table The parsed FROM clause to quote
* @return array
* @see quoteFromTables()
*/
protected function _quoteFromTables(array $from_table) {
foreach ($from_table as $k => $v) {
$from_table[$k]['table'] = $this->quoteName($from_table[$k]['table']);
if ($from_table[$k]['as'] != '') {
......
}
}
}
return $this->SQLparser->compileFromTables($from_table);
return $from_table;
}
/**
......
* @return array
* @see quoteWhereClause()
*/
protected function _quoteWhereClause($where_clause) {
protected function _quoteWhereClause(array $where_clause) {
foreach ($where_clause as $k => $v) {
// Look for sublevel:
if (is_array($where_clause[$k]['sub'])) {
......
}
} else {
// Detecting value type; list or plain:
if ((!isset($where_clause[$k]['value'][1]) || $where_clause[$k]['value'][1] == '') && is_string($where_clause[$k]['value'][0]) && strstr($where_clause[$k]['value'][0], '.') && !t3lib_div::inList('NOTIN,IN',strtoupper(str_replace(array(" ","\n","\r","\t"),'',$where_clause[$k]['comparator'])))) {
$where_clause[$k]['value'][0] = $this->quoteFieldNames($where_clause[$k]['value'][0]);
if (t3lib_div::inList('NOTIN,IN', strtoupper(str_replace(array(' ',"\n", "\r", "\t"), '', $where_clause[$k]['comparator'])))) {
if (isset($v['subquery'])) {
$where_clause[$k]['subquery'] = $this->quoteSELECTsubquery($v['subquery']);
}
} else {
if ((!isset($where_clause[$k]['value'][1]) || $where_clause[$k]['value'][1] == '') && is_string($where_clause[$k]['value'][0]) && strstr($where_clause[$k]['value'][0], '.')) {
$where_clause[$k]['value'][0] = $this->quoteFieldNames($where_clause[$k]['value'][0]);
}
}
}
}
......
}
}
// Do we have a subquery (WHERE parts only)?
if (isset($sqlPartArray[$k]['subquery'])) {
$subqueryDefaultTable = $sqlPartArray[$k]['subquery']['FROM'][0]['table'];
$this->map_sqlParts($sqlPartArray[$k]['subquery']['SELECT'], $subqueryDefaultTable);
$this->map_sqlParts($sqlPartArray[$k]['subquery']['FROM'], $subqueryDefaultTable);
$this->map_sqlParts($sqlPartArray[$k]['subquery']['WHERE'], $subqueryDefaultTable);
}
// do we have a field name in the value?
// this is a very simplistic check, beware
if (!is_numeric($sqlPartArray[$k]['value'][0]) && !isset($sqlPartArray[$k]['value'][1])) {
tests/fixtures/oci8.config.php (working copy)
'cachingframework_cache_hash' => array(
'mapTableName' => 'cf_cache_hash',
),
'cachingframework_cache_hash_tags' => array(
'mapTableName' => 'cf_cache_hash_tags',
),
'cachingframework_cache_pages' => array(
'mapTableName' => 'cf_cache_pages',
),
'cpg_categories' => array(
'mapFieldNames' => array(
'pid' => 'page_id',
......
'uid_local' => 'local_uid',
),
),
'tx_crawler_process' => array(
'mapTableName' => 'tx_crawler_ps',
'mapFieldNames' => array(
'process_id' => 'ps_id',
'active' => 'is_active',
),
),
'tx_dam_file_tracking' => array(
'mapFieldNames' => array(
'file_name' => 'filename',
tests/sqlparser_general_testcase.php (working copy)
$createTables = $this->fixture->_callRef('parseCREATETABLE', $parseString);
$this->assertTrue(is_array($createTables), $createTables);
}
///////////////////////////////////////
// Tests concerning subqueries
///////////////////////////////////////
/**
* @test
* @see http://bugs.typo3.org/view.php?id=12758
*/
public function inWhereClauseSupportsSubquery() {
$parseString = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
$whereParts = $this->fixture->parseWhereClause($parseString);
$this->assertTrue(is_array($whereParts), $whereParts);
$this->assertTrue(empty($parseString), 'parseString is not empty');
}
/**
* @test
* @see http://bugs.typo3.org/view.php?id=12758
*/
public function inWhereClauseWithSubqueryIsProperlyCompiled() {
$sql = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
$expected = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active = 0 AND deleted = 0)';
$actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
$this->assertEquals($expected, $actual);
}
}
?>
tests/db_oracle_testcase.php (working copy)
)
');
$this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
}
}
///////////////////////////////////////
// Tests concerning subqueries
///////////////////////////////////////
/**
* @test
* @see http://bugs.typo3.org/view.php?id=12758
*/
public function inWhereClauseWithSubqueryIsProperlyQuoted() {
$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)'
));
$expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "process_id" FROM "tx_crawler_process" WHERE "active" = 0 AND "deleted" = 0)';
$this->assertEquals($expected, $query);
}
/**
* @test
* @see http://bugs.typo3.org/view.php?id=12758
*/
public function subqueryIsRemappedForInWhereClause() {
$selectFields = '*';
$fromTables = 'tx_crawler_queue';
$whereClause = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
$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 * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "ps_id" FROM "tx_crawler_ps" WHERE "is_active" = 0 AND "deleted" = 0)';
$this->assertEquals($expected, $query);
}
/**
* @test
* @see http://bugs.typo3.org/view.php?id=12800
*/
public function cachingFrameworkQueryIsSupported() {
$currentTime = time();
$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'
) .
')'
));
$expected = 'DELETE FROM "cachingframework_cache_hash_tags" WHERE "identifier" IN (';
$expected .= 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
$expected .= ')';
$this->assertEquals($expected, $query);
}
/**
* @test
* @see http://bugs.typo3.org/view.php?id=12800
*/
public function cachingFrameworkQueriesIsRemapped() {
$currentTime = time();
$table = 'cachingframework_cache_hash_tags';
$where = 'identifier IN (' .
$GLOBALS['TYPO3_DB']->SELECTsubquery(
'identifier',
'cachingframework_cache_pages',
'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
) .
')';
// Perform remapping (as in method exec_DELETEquery)
if ($tableArray = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table)) {
// Where clause:
$whereParts = $GLOBALS['TYPO3_DB']->SQLparser->parseWhereClause($where);
$GLOBALS['TYPO3_DB']->_callRef('map_sqlParts', $whereParts, $tableArray[0]['table']);
$where = $GLOBALS['TYPO3_DB']->SQLparser->compileWhereClause($whereParts, FALSE);
// Table name:
if ($GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName']) {
$table = $GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName'];
}
}
$query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery($table, $where));
$expected = 'DELETE FROM "cf_cache_hash_tags" WHERE "identifier" IN (';
$expected .= 'SELECT "identifier" FROM "cf_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
$expected .= ')';
$this->assertEquals($expected, $query);
}
}
?>
(3-3/3)