/** * 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; } }