/**
* Tests that it is possible to nest expression objects and pass them as arguments
* In particular nesting multiple FunctionExpression
*
* @return void
*/
public function testFunctionNesting()
{
$binder = new ValueBinder();
$f = new FunctionExpression('MyFunction', ['foo', 'bar']);
$g = new FunctionExpression('Wrapper', ['bar' => 'literal', $f]);
$this->assertEquals("Wrapper(bar, MyFunction(:c0, :c1))", $g->sql($binder));
}
/**
* Receives a FunctionExpression and changes it so that it conforms to this
* SQL dialect.
*
* @param \Cake\Database\Expression\FunctionExpression $expression The function expression
* to translate for SQLite.
* @return void
*/
protected function _transformFunctionExpression(FunctionExpression $expression)
{
switch ($expression->name()) {
case 'CONCAT':
// CONCAT function is expressed as exp1 || exp2
$expression->name('')->type(' ||');
break;
case 'DATEDIFF':
$expression->name('ROUND')->type('-')->iterateParts(function ($p) {
return new FunctionExpression('JULIANDAY', [$p['value']], [$p['type']]);
});
break;
case 'NOW':
$expression->name('DATETIME')->add(["'now'" => 'literal']);
break;
case 'CURRENT_DATE':
$expression->name('DATE')->add(["'now'" => 'literal']);
break;
case 'CURRENT_TIME':
$expression->name('TIME')->add(["'now'" => 'literal']);
break;
case 'EXTRACT':
$expression->name('STRFTIME')->type(' ,')->iterateParts(function ($p, $key) {
if ($key === 0) {
$value = rtrim(strtolower($p), 's');
if (isset($this->_dateParts[$value])) {
$p = ['value' => '%' . $this->_dateParts[$value], 'type' => null];
}
}
return $p;
});
break;
case 'DATE_ADD':
$expression->name('DATE')->type(',')->iterateParts(function ($p, $key) {
if ($key === 1) {
$p = ['value' => $p, 'type' => null];
}
return $p;
});
break;
case 'DAYOFWEEK':
$expression->name('STRFTIME')->type(' ')->add(["'%w', " => 'literal'], [], true)->add([') + (1' => 'literal']);
// Sqlite starts on index 0 but Sunday should be 1
break;
}
}
/**
* Receives a FunctionExpression and changes it so that it conforms to this
* SQL dialect.
*
* @param \Cake\Database\Expression\FunctionExpression
* @return void
*/
protected function _transformFunctionExpression(FunctionExpression $expression)
{
switch ($expression->name()) {
case 'CONCAT':
// CONCAT function is expressed as exp1 || exp2
$expression->name('')->type(' ||');
break;
case 'DATEDIFF':
$expression->name('ROUND')->type('-')->iterateParts(function ($p) {
return new FunctionExpression('JULIANDAY', [$p['value']], [$p['type']]);
});
break;
case 'NOW':
$expression->name('DATETIME')->add(["'now'" => 'literal']);
break;
case 'CURRENT_DATE':
$expression->name('DATE')->add(["'now'" => 'literal']);
break;
case 'CURRENT_TIME':
$expression->name('TIME')->add(["'now'" => 'literal']);
break;
}
}
/**
* Receives a FunctionExpression and changes it so that it conforms to this
* SQL dialect.
*
* @param \Cake\Database\Expression\FunctionExpression $expression The function expression to convert to TSQL.
* @return void
*/
protected function _transformFunctionExpression(FunctionExpression $expression)
{
switch ($expression->name()) {
case 'CONCAT':
// CONCAT function is expressed as exp1 + exp2
$expression->name('')->tieWith(' +');
break;
case 'DATEDIFF':
$hasDay = false;
$visitor = function ($value) use(&$hasDay) {
if ($value === 'day') {
$hasDay = true;
}
return $value;
};
$expression->iterateParts($visitor);
if (!$hasDay) {
$expression->add(['day' => 'literal'], [], true);
}
break;
case 'CURRENT_DATE':
$time = new FunctionExpression('GETUTCDATE');
$expression->name('CONVERT')->add(['date' => 'literal', $time]);
break;
case 'CURRENT_TIME':
$time = new FunctionExpression('GETUTCDATE');
$expression->name('CONVERT')->add(['time' => 'literal', $time]);
break;
case 'NOW':
$expression->name('GETUTCDATE');
break;
case 'EXTRACT':
$expression->name('DATEPART')->tieWith(' ,');
break;
case 'DATE_ADD':
$params = [];
$visitor = function ($p, $key) use(&$params) {
if ($key === 0) {
$params[2] = $p;
} else {
$valueUnit = explode(' ', $p);
$params[0] = rtrim($valueUnit[1], 's');
$params[1] = $valueUnit[0];
}
return $p;
};
$manipulator = function ($p, $key) use(&$params) {
return $params[$key];
};
$expression->name('DATEADD')->tieWith(',')->iterateParts($visitor)->iterateParts($manipulator)->add([$params[2] => 'literal']);
break;
case 'DAYOFWEEK':
$expression->name('DATEPART')->tieWith(' ')->add(['weekday, ' => 'literal'], [], true);
break;
}
}
/**
* Receives a FunctionExpression and changes it so that it conforms to this
* SQL dialect.
*
* @param \Cake\Database\Expression\FunctionExpression $expression The function expression to convert
* to postgres SQL.
* @return void
*/
protected function _transformFunctionExpression(FunctionExpression $expression)
{
switch ($expression->name()) {
case 'CONCAT':
// CONCAT function is expressed as exp1 || exp2
$expression->name('')->type(' ||');
break;
case 'DATEDIFF':
$expression->name('')->type('-')->iterateParts(function ($p) {
if (is_string($p)) {
$p = ['value' => [$p => 'literal'], 'type' => null];
} else {
$p['value'] = [$p['value']];
}
return new FunctionExpression('DATE', $p['value'], [$p['type']]);
});
break;
case 'CURRENT_DATE':
$time = new FunctionExpression('LOCALTIMESTAMP', [' 0 ' => 'literal']);
$expression->name('CAST')->type(' AS ')->add([$time, 'date' => 'literal']);
break;
case 'CURRENT_TIME':
$time = new FunctionExpression('LOCALTIMESTAMP', [' 0 ' => 'literal']);
$expression->name('CAST')->type(' AS ')->add([$time, 'time' => 'literal']);
break;
case 'NOW':
$expression->name('LOCALTIMESTAMP')->add([' 0 ' => 'literal']);
break;
case 'DATE_ADD':
$expression->name('')->type(' + INTERVAL')->iterateParts(function ($p, $key) {
if ($key === 1) {
$p = sprintf("'%s'", $p);
}
return $p;
});
break;
case 'DAYOFWEEK':
$expression->name('EXTRACT')->type(' ')->add(['DOW FROM' => 'literal'], [], true)->add([') + (1' => 'literal']);
// Postgres starts on index 0 but Sunday should be 1
break;
}
}
/**
* Receives a FunctionExpression and changes it so that it conforms to this SQL dialect.
*
* @param \Cake\Database\Expression\FunctionExpression $expression The function expression
* to convert to oracle SQL.
* @return void
*/
protected function _transformFunctionExpression(FunctionExpression $expression)
{
switch ($expression->name()) {
case 'CONCAT':
$expression->name('')->type(' ||');
break;
case 'DATEDIFF':
$expression->name('')->type('-')->iterateParts(function ($p) {
if (is_string($p)) {
$p = ['value' => [$p => 'literal'], 'type' => null];
} else {
$p['value'] = [$p['value']];
}
return new FunctionExpression('TO_DATE', $p['value'], [$p['type']]);
});
break;
case 'CURRENT_DATE':
$time = new FunctionExpression('LOCALTIMESTAMP', [' 0 ' => 'literal']);
$expression->name('TO_CHAR')->add([$time, 'YYYY-MM-DD']);
break;
case 'CURRENT_TIME':
$time = new FunctionExpression('LOCALTIMESTAMP', [' 0 ' => 'literal']);
$expression->name('TO_CHAR')->add([$time, 'YYYY-MM-DD HH24:MI:SS']);
break;
case 'NOW':
$expression->name('LOCALTIMESTAMP')->add([' 0 ' => 'literal']);
break;
case 'DATE_ADD':
$expression->name('TO_CHAR')->type(' + INTERVAL')->iterateParts(function ($p, $key) {
if ($key === 1) {
$keys = explode(' ', $p);
$unit = array_pop($keys);
$value = implode(' ', $keys);
$value = str_replace("'", '', $value);
$p = sprintf("'%s' %s", $value, $unit);
}
return $p;
});
break;
case 'DAYOFWEEK':
$expression->name('TO_CHAR')->add(['d']);
break;
}
}
/**
* Receives a FunctionExpression and changes it so that it conforms to this
* SQL dialect.
*
* @param \Cake\Database\Expression\FunctionExpression $expression The function expression to convert to TSQL.
* @return void
*/
protected function _transformFunctionExpression(FunctionExpression $expression)
{
switch ($expression->name()) {
case 'CONCAT':
// CONCAT function is expressed as exp1 + exp2
$expression->name('')->type(' +');
break;
case 'DATEDIFF':
$hasDay = false;
$visitor = function ($value) use(&$hasDay) {
if ($value === 'day') {
$hasDay = true;
}
return $value;
};
$expression->iterateParts($visitor);
if (!$hasDay) {
$expression->add(['day' => 'literal'], [], true);
}
break;
case 'CURRENT_DATE':
$time = new FunctionExpression('GETUTCDATE');
$expression->name('CONVERT')->add(['date' => 'literal', $time]);
break;
case 'CURRENT_TIME':
$time = new FunctionExpression('GETUTCDATE');
$expression->name('CONVERT')->add(['time' => 'literal', $time]);
break;
case 'NOW':
$expression->name('GETUTCDATE');
break;
}
}
/**
* Tests that it is possible to use a number as a literal in a function.
*
* @return void
*/
public function testNumericLiteral()
{
$binder = new ValueBinder();
$f = new FunctionExpression('MyFunction', ['a_field' => 'literal', '32' => 'literal']);
$this->assertEquals('MyFunction(a_field, 32)', $f->sql($binder));
$f = new FunctionExpression('MyFunction', ['a_field' => 'literal', 32 => 'literal']);
$this->assertEquals('MyFunction(a_field, 32)', $f->sql($binder));
}
/**
* Receives a FunctionExpression and changes it so that it conforms to this
* SQL dialect.
*
* @param \Cake\Database\Expression\FunctionExpression $expression The function expression to convert
* to postgres SQL.
* @return void
*/
protected function _transformFunctionExpression(FunctionExpression $expression)
{
switch ($expression->name()) {
case 'CONCAT':
// CONCAT function is expressed as exp1 || exp2
$expression->name('')->type(' ||');
break;
case 'DATEDIFF':
$expression->name('')->type('-')->iterateParts(function ($p) {
if (is_string($p)) {
$p = ['value' => [$p => 'literal'], 'type' => null];
} else {
$p['value'] = [$p['value']];
}
return new FunctionExpression('DATE', $p['value'], [$p['type']]);
});
break;
case 'CURRENT_DATE':
$time = new FunctionExpression('LOCALTIMESTAMP', [' 0 ' => 'literal']);
$expression->name('CAST')->type(' AS ')->add([$time, 'date' => 'literal']);
break;
case 'CURRENT_TIME':
$time = new FunctionExpression('LOCALTIMESTAMP', [' 0 ' => 'literal']);
$expression->name('CAST')->type(' AS ')->add([$time, 'time' => 'literal']);
break;
case 'NOW':
$expression->name('LOCALTIMESTAMP')->add([' 0 ' => 'literal']);
break;
}
}
/**
* Receives a FunctionExpression and changes it so that it conforms to this
* SQL dialect.
*
* @param Cake\Database\Expression\FunctionExpression
* @return void
*/
protected function _transformFunctionExpression(FunctionExpression $expression)
{
switch ($expression->name()) {
case 'CONCAT':
// CONCAT function is expressed as exp1 + exp2
$expression->name('')->type(' +');
break;
case 'DATEDIFF':
$expression->add(['day' => 'literal'], [], true);
break;
case 'CURRENT_DATE':
$time = new FunctionExpression('GETUTCDATE');
$expression->name('CONVERT')->add(['date' => 'literal', $time]);
break;
case 'CURRENT_TIME':
$time = new FunctionExpression('GETUTCDATE');
$expression->name('CONVERT')->add(['time' => 'literal', $time]);
break;
case 'NOW':
$expression->name('GETUTCDATE');
break;
}
}
