TYPO3 Core

* @param boolean Whether function mapping should take place

public function compileFieldList($selectFields, $compileComments = TRUE, $functionMapping = TRUE) {

switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {

case 'native':

$output = parent::compileFieldList($selectFields, $compileComments);

break;

case 'adodb':

$output = '';

// Traverse the selectFields if any:

if (is_array($selectFields)) {

$outputParts = array();

foreach ($selectFields as $k => $v) {

// Detecting type:

switch($v['type']) {

case 'function':

$outputParts[$k] = $v['function'] . '(' . $v['func_content'] . ')';

break;

case 'flow-control':

if ($v['flow-control']['type'] === 'CASE') {

$outputParts[$k] = $this->compileCaseStatement($v['flow-control'], $functionMapping);

}

break;

case 'field':

$outputParts[$k] = ($v['distinct'] ? $v['distinct'] : '') . ($v['table'] ? $v['table'] . '.' : '') . $v['field'];

break;

}

// Alias:

if ($v['as']) {

$outputParts[$k] .= ' ' . $v['as_keyword'] . ' ' . $v['as'];

}

// Specifically for ORDER BY and GROUP BY field lists:

if ($v['sortDir']) {

$outputParts[$k] .= ' ' . $v['sortDir'];

}

}

// TODO: Handle SQL hints in comments according to current DBMS

if (/* $compileComments */ FALSE && $selectFields[0]['comments']) {

$output = $selectFields[0]['comments'] . ' ';

}

$output .= implode(', ', $outputParts);

}

break;

}

return $output;

/**

* Compiles a CASE ... WHEN flow-control construct based on input array (made with ->parseCaseStatement())

*

* @param array Array of case components, (made with ->parseCaseStatement())

* @param boolean Whether function mapping should take place

* @return string case when string

* @see parseCaseStatement()

*/

protected function compileCaseStatement(array $components, $functionMapping = TRUE) {

switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {

case 'native':

$output = parent::compileCaseStatement($components);

break;

case 'adodb':

$statement = 'CASE';

if (isset($components['case_field'])) {

$statement .= ' ' . $components['case_field'];

} elseif (isset($components['case_value'])) {

$statement .= ' ' . $components['case_value'][1] . $components['case_value'][0] . $components['case_value'][1];

}

foreach ($components['when'] as $when) {

$statement .= ' WHEN ';

$statement .= $this->compileWhereClause($when['when_value'], $functionMapping);

$statement .= ' THEN ';

$statement .= $when['then_value'][1] . $when['then_value'][0] . $when['then_value'][1];

}

if (isset($components['else'])) {

$statement .= ' ELSE ';

$statement .= $components['else'][1] . $components['else'][0] . $components['else'][1];

}

$statement .= ' END';

$output = $statement;

break;

}

return $output;

}

if (isset($v['func']) && $v['func']['type'] === 'LOCATE') {

$output .= ' ' . trim($v['modifier']);

switch (TRUE) {

case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('mssql') && $functionMapping):

$output .= ' CHARINDEX(';

$output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];

$output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

$output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';

$output .= ')';

break;

case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $functionMapping):

$output .= ' INSTR(';

$output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

$output .= ', ' . $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];

$output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';

$output .= ')';

break;

default:

$output .= ' LOCATE(';

$output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];

$output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

$output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';

$output .= ')';

break;

}

} else {

// Set field/table with modifying prefix if any:

$output .= ' ' . trim($v['modifier']) . ' ';

// DBAL-specific: Set calculation, if any:

if ($v['calc'] === '&' && $functionMapping) {

switch(TRUE) {

case $GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8'):

// Oracle only knows BITAND(x,y) - sigh

$output .= 'BITAND(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ',' . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1] . ')';

break;

default:

// MySQL, MS SQL Server, PostgreSQL support the &-syntax

$output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];

break;

}

} elseif ($v['calc']) {

$output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'];

if (isset($v['calc_table'])) {

$output .= trim(($v['calc_table'] ? $v['calc_table'] . '.' : '') . $v['calc_field']);

} else {

$output .= $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];

}

} elseif (!($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $v['comparator'] === 'LIKE' && $functionMapping)) {

$output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);

case 'LOCATE':

if ($where_clause[$k]['func']['table'] != '') {

$where_clause[$k]['func']['table'] = $this->quoteName($v['func']['table']);

}

if ($where_clause[$k]['func']['field'] != '') {

$where_clause[$k]['func']['field'] = $this->quoteName($v['func']['field']);

}

break;

$select_fields = $this->SQLparser->compileFieldList($expFields, FALSE, FALSE);

if (isset($sqlPartArray[$k]['type'])) {

switch ($sqlPartArray[$k]['type']) {

case 'flow-control':

$temp = array($sqlPartArray[$k]['flow-control']);

$this->map_sqlParts($temp, $defaultTable); // Call recursively!

$sqlPartArray[$k]['flow-control'] = $temp[0];

break;

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]];

}

elseif (count($fieldArray) == 2) {

// Map the external table

$table = $fieldArray[0];

if (isset($this->mapping[$fieldArray[0]]['mapTableName'])) {

$table = $this->mapping[$fieldArray[0]]['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]];

}

$sqlPartArray[$k]['case_field'] = $table . '.' . $field;

}

}

foreach ($sqlPartArray[$k]['when'] as $key => $when) {

$this->map_sqlParts($sqlPartArray[$k]['when'][$key]['when_value'], $defaultTable);

}

break;

}

}

case 'LOCATE':

// For the field, look for table mapping (generic):

$t = $sqlPartArray[$k]['func']['table'] ? $sqlPartArray[$k]['func']['table'] : $defaultTable;

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']];

}

if ($this->mapping[$t]['mapTableName']) {

$sqlPartArray[$k]['func']['table'] = $this->mapping[$t]['mapTableName'];

}

break;

if (isset($sqlPartArray[$k]['flow-control']['type'])) {

$temp = array($sqlPartArray[$k]['flow-control']);

$this->map_sqlParts($temp, $t); // Call recursively!

$sqlPartArray[$k]['flow-control'] = $temp[0];

}

-- LOCATE()

CREATE OR REPLACE FUNCTION locate(text, text, integer)

RETURNS integer AS $$

SELECT POSITION($1 IN SUBSTRING ($2 FROM $3)) + $3 - 1

$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE OR REPLACE FUNCTION locate(text, text)

RETURNS integer AS $$

SELECT locate($1, $2, 1)

$$ IMMUTABLE STRICT LANGUAGE SQL;

--DROP FUNCTION locate(text, text);

--DROP FUNCTION locate(text, text, integer);

<?php

/***************************************************************

* Copyright notice

*

* (c) 2009 Xavier Perseguers <typo3@perseguers.ch>

* All rights reserved

*

* This script is part of the TYPO3 project. The TYPO3 project is

* free software; you can redistribute it and/or modify

* it under the terms of the GNU General Public License as published by

* the Free Software Foundation; either version 2 of the License, or

* (at your option) any later version.

*

* The GNU General Public License can be found at

* http://www.gnu.org/copyleft/gpl.html.

*

* This script is distributed in the hope that it will be useful,

* but WITHOUT ANY WARRANTY; without even the implied warranty of

* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

* GNU General Public License for more details.

*

* This copyright notice MUST APPEAR in all copies of the script!

***************************************************************/

require_once('BaseTestCase.php');

require_once('FakeDbConnection.php');

/**

* Testcase for class ux_t3lib_db. Testing MS SQL database handling.

*

* $Id$

*

* @author Xavier Perseguers <typo3@perseguers.ch>

*

* @package TYPO3

* @subpackage dbal

*/

class db_mssql_testcase extends BaseTestCase {

/**

* @var t3lib_db

*/

protected $db;

/**

* @var array

*/

protected $dbalConfig;

/**

* Prepares the environment before running a test.

*/

public function setUp() {

// Backup DBAL configuration

$this->dbalConfig = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'];

// Backup database connection

$this->db = $GLOBALS['TYPO3_DB'];

// Reconfigure DBAL to use MS SQL

require('fixtures/mssql.config.php');

$className = self::buildAccessibleProxy('ux_t3lib_db');

$GLOBALS['TYPO3_DB'] = new $className;

$parserClassName = self::buildAccessibleProxy('ux_t3lib_sqlparser');

$GLOBALS['TYPO3_DB']->SQLparser = new $parserClassName;

// Initialize a fake MS SQL connection

FakeDbConnection::connect($GLOBALS['TYPO3_DB'], 'mssql');

$this->assertTrue($GLOBALS['TYPO3_DB']->handlerInstance['_DEFAULT']->isConnected());

}

/**

* Cleans up the environment after running a test.

*/

public function tearDown() {

// Clear DBAL-generated cache files

$GLOBALS['TYPO3_DB']->clearCachedFieldInfo();

// Restore DBAL configuration

$GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'] = $this->dbalConfig;

// Restore DB connection

$GLOBALS['TYPO3_DB'] = $this->db;

}

/**

* Cleans a SQL query.

*

* @param mixed $sql

* @return mixed (string or array)

*/

private function cleanSql($sql) {

if (!is_string($sql)) {

return $sql;

}

$sql = str_replace("\n", ' ', $sql);

$sql = preg_replace('/\s+/', ' ', $sql);

return trim($sql);

}

/**

* @test

*/

public function configurationIsUsingAdodbAndDriverMssql() {

$configuration = $GLOBALS['TYPO3_DB']->conf['handlerCfg'];

$this->assertTrue(is_array($configuration) && count($configuration) > 0, 'No configuration found');

$this->assertEquals('adodb', $configuration['_DEFAULT']['type']);

$this->assertTrue($GLOBALS['TYPO3_DB']->runningADOdbDriver('mssql') !== FALSE, 'Not using mssql driver');

}

/**

* @test

*/

public function tablesWithMappingAreDetected() {

$tablesWithMapping = array_keys($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping']);

foreach ($GLOBALS['TYPO3_DB']->cache_fieldType as $table => $fieldTypes) {

$tableDef = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table);

if (in_array($table, $tablesWithMapping)) {

self::assertTrue(is_array($tableDef), 'Table ' . $table . ' was expected to need mapping');

} else {

self::assertFalse($tableDef, 'Table ' . $table . ' was not expected to need mapping');

}

}

}

///////////////////////////////////////

// Tests concerning advanced operators

///////////////////////////////////////

/**

* @test

* @see http://bugs.typo3.org/view.php?id=13134

*/

public function locateStatementIsProperlyQuoted() {

$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'

));

$expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "datastructure") > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';

$this->assertEquals($expected, $query);

}

/**

* @test

* @see http://bugs.typo3.org/view.php?id=13134

*/

public function locateStatementWithPositionIsProperlyQuoted() {

$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'

));

$expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "datastructure", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';

$this->assertEquals($expected, $query);

}

/**

* @test

* @see http://bugs.typo3.org/view.php?id=13134

*/

public function locateStatementIsProperlyRemapped() {

$selectFields = '*, CASE WHEN' .

' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' .

' ELSE 1' .

' END AS scope';

$fromTables = 'tx_templavoila_tmplobj';

$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 *, CASE WHEN CHARINDEX(\'(fce)\', "ds", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';

$this->assertEquals($expected, $query);

}

/**

* @test

* @see http://bugs.typo3.org/view.php?id=13134

*/

public function locateStatementWithExternalTableIsProperlyRemapped() {

$selectFields = '*, CASE WHEN' .

' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', tx_templavoila_tmplobj.datastructure, 4)>0 THEN 2' .

' ELSE 1' .

' END AS scope';

$fromTables = 'tx_templavoila_tmplobj';

$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 *, CASE WHEN CHARINDEX(\'(fce)\', "tx_templavoila_tmplobj"."ds", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';

$this->assertEquals($expected, $query);

}

}

?>

<?php

/**

* MS SQL configuration

*

* $Id$

*

* @author Xavier Perseguers <typo3@perseguers.ch>

*

* @package TYPO3

* @subpackage dbal

*/

global $TYPO3_CONF_VARS;

$TYPO3_CONF_VARS['EXTCONF']['dbal']['handlerCfg'] = array(

'_DEFAULT' => array(

'type' => 'adodb',

'config' => array(

'driver' => 'mssql',

'useNameQuote' => FALSE,

),

),

);

$TYPO3_CONF_VARS['EXTCONF']['dbal']['mapping'] = array(

'tx_templavoila_tmplobj' => array(

'mapFieldNames' => array(

'datastructure' => 'ds',

),

),

);

?>

/**

* @test

* @see http://bugs.typo3.org/view.php?id=13134

*/

public function locateIsSupported() {

$sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure)>0';

$expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure) > 0';

$actual = $this->cleanSql($this->fixture->debug_testSQL($sql));

$this->assertEquals($expected, $actual);

}

/**

* @test

* @see http://bugs.typo3.org/view.php?id=13134

*/

public function locateWithPositionIsSupported() {

$sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\' , datastructure ,10)>0';

$expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure, 10) > 0';

$actual = $this->cleanSql($this->fixture->debug_testSQL($sql));

$this->assertEquals($expected, $actual);

}

/**

* @test

* @see http://bugs.typo3.org/view.php?id=13134

* @see http://bugs.typo3.org/view.php?id=13135

*/

public function locateWithinCaseIsSupported() {

$sql = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure)>0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';

$expected = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure) > 0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';

$actual = $this->cleanSql($this->fixture->debug_testSQL($sql));

$this->assertEquals($expected, $actual);

}

/**

* @test

* @see http://bugs.typo3.org/view.php?id=13134

*/

public function locateStatementIsProperlyQuoted() {

$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'

));

$expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\') > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';

$this->assertEquals($expected, $query);

}

/**

* @test

* @see http://bugs.typo3.org/view.php?id=13134

*/

public function locateStatementWithPositionIsProperlyQuoted() {

$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'

));

$expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\', 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';

$this->assertEquals($expected, $query);

}