1<?php 2/** 3 * CakePHP(tm) : Rapid Development Framework (https://cakephp.org) 4 * Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org) 5 * 6 * Licensed under The MIT License 7 * For full copyright and license information, please see the LICENSE.txt 8 * Redistributions of files must retain the above copyright notice. 9 * 10 * @copyright Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org) 11 * @link https://cakephp.org CakePHP(tm) Project 12 * @since 3.0.0 13 * @license https://opensource.org/licenses/mit-license.php MIT License 14 */ 15namespace Cake\Database; 16 17use Cake\Database\Expression\FunctionExpression; 18 19/** 20 * Contains methods related to generating FunctionExpression objects 21 * with most commonly used SQL functions. 22 * This acts as a factory for FunctionExpression objects. 23 */ 24class FunctionsBuilder 25{ 26 /** 27 * Returns a new instance of a FunctionExpression. This is used for generating 28 * arbitrary function calls in the final SQL string. 29 * 30 * @param string $name the name of the SQL function to constructed 31 * @param array $params list of params to be passed to the function 32 * @param array $types list of types for each function param 33 * @param string $return The return type of the function expression 34 * @return \Cake\Database\Expression\FunctionExpression 35 */ 36 protected function _build($name, $params = [], $types = [], $return = 'string') 37 { 38 return new FunctionExpression($name, $params, $types, $return); 39 } 40 41 /** 42 * Helper function to build a function expression that only takes one literal 43 * argument. 44 * 45 * @param string $name name of the function to build 46 * @param string|\Cake\Database\ExpressionInterface $expression the function argument 47 * @param array $types list of types to bind to the arguments 48 * @param string $return The return type for the function 49 * @return \Cake\Database\Expression\FunctionExpression 50 */ 51 protected function _literalArgumentFunction($name, $expression, $types = [], $return = 'string') 52 { 53 if (!is_string($expression)) { 54 $expression = [$expression]; 55 } else { 56 $expression = [$expression => 'literal']; 57 } 58 59 return $this->_build($name, $expression, $types, $return); 60 } 61 62 /** 63 * Returns a FunctionExpression representing a call to SQL RAND function. 64 * 65 * @return \Cake\Database\Expression\FunctionExpression 66 */ 67 public function rand() 68 { 69 return $this->_build('RAND', [], [], 'float'); 70 } 71 72 /** 73 * Returns a FunctionExpression representing a call to SQL SUM function. 74 * 75 * @param string|\Cake\Database\ExpressionInterface $expression the function argument 76 * @param array $types list of types to bind to the arguments 77 * @return \Cake\Database\Expression\FunctionExpression 78 */ 79 public function sum($expression, $types = []) 80 { 81 $returnType = 'float'; 82 if (current($types) === 'integer') { 83 $returnType = 'integer'; 84 } 85 86 return $this->_literalArgumentFunction('SUM', $expression, $types, $returnType); 87 } 88 89 /** 90 * Returns a FunctionExpression representing a call to SQL AVG function. 91 * 92 * @param string|\Cake\Database\ExpressionInterface $expression the function argument 93 * @param array $types list of types to bind to the arguments 94 * @return \Cake\Database\Expression\FunctionExpression 95 */ 96 public function avg($expression, $types = []) 97 { 98 return $this->_literalArgumentFunction('AVG', $expression, $types, 'float'); 99 } 100 101 /** 102 * Returns a FunctionExpression representing a call to SQL MAX function. 103 * 104 * @param string|\Cake\Database\ExpressionInterface $expression the function argument 105 * @param array $types list of types to bind to the arguments 106 * @return \Cake\Database\Expression\FunctionExpression 107 */ 108 public function max($expression, $types = []) 109 { 110 return $this->_literalArgumentFunction('MAX', $expression, $types, current($types) ?: 'string'); 111 } 112 113 /** 114 * Returns a FunctionExpression representing a call to SQL MIN function. 115 * 116 * @param string|\Cake\Database\ExpressionInterface $expression the function argument 117 * @param array $types list of types to bind to the arguments 118 * @return \Cake\Database\Expression\FunctionExpression 119 */ 120 public function min($expression, $types = []) 121 { 122 return $this->_literalArgumentFunction('MIN', $expression, $types, current($types) ?: 'string'); 123 } 124 125 /** 126 * Returns a FunctionExpression representing a call to SQL COUNT function. 127 * 128 * @param string|\Cake\Database\ExpressionInterface $expression the function argument 129 * @param array $types list of types to bind to the arguments 130 * @return \Cake\Database\Expression\FunctionExpression 131 */ 132 public function count($expression, $types = []) 133 { 134 return $this->_literalArgumentFunction('COUNT', $expression, $types, 'integer'); 135 } 136 137 /** 138 * Returns a FunctionExpression representing a string concatenation 139 * 140 * @param array $args List of strings or expressions to concatenate 141 * @param array $types list of types to bind to the arguments 142 * @return \Cake\Database\Expression\FunctionExpression 143 */ 144 public function concat($args, $types = []) 145 { 146 return $this->_build('CONCAT', $args, $types, 'string'); 147 } 148 149 /** 150 * Returns a FunctionExpression representing a call to SQL COALESCE function. 151 * 152 * @param array $args List of expressions to evaluate as function parameters 153 * @param array $types list of types to bind to the arguments 154 * @return \Cake\Database\Expression\FunctionExpression 155 */ 156 public function coalesce($args, $types = []) 157 { 158 return $this->_build('COALESCE', $args, $types, current($types) ?: 'string'); 159 } 160 161 /** 162 * Returns a FunctionExpression representing a call to SQL CAST function. 163 * 164 * @param string|\Cake\Database\ExpressionInterface $field Field or expression to cast. 165 * @param string $type The target data type 166 * @return \Cake\Database\Expression\FunctionExpression 167 */ 168 public function cast($field, $type = '') 169 { 170 if (is_array($field)) { 171 deprecationWarning( 172 'Build cast function by FunctionsBuilder::cast(array $args) is deprecated. ' . 173 'Use FunctionsBuilder::cast($field, string $type) instead.' 174 ); 175 176 return $this->_build('CAST', $field); 177 } 178 179 $expression = $this->_literalArgumentFunction('CAST', $field); 180 $expression->setConjunction(' AS')->add([$type => 'literal']); 181 182 return $expression; 183 } 184 185 /** 186 * Returns a FunctionExpression representing the difference in days between 187 * two dates. 188 * 189 * @param array $args List of expressions to obtain the difference in days. 190 * @param array $types list of types to bind to the arguments 191 * @return \Cake\Database\Expression\FunctionExpression 192 */ 193 public function dateDiff($args, $types = []) 194 { 195 return $this->_build('DATEDIFF', $args, $types, 'integer'); 196 } 197 198 /** 199 * Returns the specified date part from the SQL expression. 200 * 201 * @param string $part Part of the date to return. 202 * @param string|\Cake\Database\ExpressionInterface $expression Expression to obtain the date part from. 203 * @param array $types list of types to bind to the arguments 204 * @return \Cake\Database\Expression\FunctionExpression 205 */ 206 public function datePart($part, $expression, $types = []) 207 { 208 return $this->extract($part, $expression, $types); 209 } 210 211 /** 212 * Returns the specified date part from the SQL expression. 213 * 214 * @param string $part Part of the date to return. 215 * @param string|\Cake\Database\ExpressionInterface $expression Expression to obtain the date part from. 216 * @param array $types list of types to bind to the arguments 217 * @return \Cake\Database\Expression\FunctionExpression 218 */ 219 public function extract($part, $expression, $types = []) 220 { 221 $expression = $this->_literalArgumentFunction('EXTRACT', $expression, $types, 'integer'); 222 $expression->setConjunction(' FROM')->add([$part => 'literal'], [], true); 223 224 return $expression; 225 } 226 227 /** 228 * Add the time unit to the date expression 229 * 230 * @param string|\Cake\Database\ExpressionInterface $expression Expression to obtain the date part from. 231 * @param string|int $value Value to be added. Use negative to subtract. 232 * @param string $unit Unit of the value e.g. hour or day. 233 * @param array $types list of types to bind to the arguments 234 * @return \Cake\Database\Expression\FunctionExpression 235 */ 236 public function dateAdd($expression, $value, $unit, $types = []) 237 { 238 if (!is_numeric($value)) { 239 $value = 0; 240 } 241 $interval = $value . ' ' . $unit; 242 $expression = $this->_literalArgumentFunction('DATE_ADD', $expression, $types, 'datetime'); 243 $expression->setConjunction(', INTERVAL')->add([$interval => 'literal']); 244 245 return $expression; 246 } 247 248 /** 249 * Returns a FunctionExpression representing a call to SQL WEEKDAY function. 250 * 1 - Sunday, 2 - Monday, 3 - Tuesday... 251 * 252 * @param string|\Cake\Database\ExpressionInterface $expression the function argument 253 * @param array $types list of types to bind to the arguments 254 * @return \Cake\Database\Expression\FunctionExpression 255 */ 256 public function dayOfWeek($expression, $types = []) 257 { 258 return $this->_literalArgumentFunction('DAYOFWEEK', $expression, $types, 'integer'); 259 } 260 261 /** 262 * Returns a FunctionExpression representing a call to SQL WEEKDAY function. 263 * 1 - Sunday, 2 - Monday, 3 - Tuesday... 264 * 265 * @param string|\Cake\Database\ExpressionInterface $expression the function argument 266 * @param array $types list of types to bind to the arguments 267 * @return \Cake\Database\Expression\FunctionExpression 268 */ 269 public function weekday($expression, $types = []) 270 { 271 return $this->dayOfWeek($expression, $types); 272 } 273 274 /** 275 * Returns a FunctionExpression representing a call that will return the current 276 * date and time. By default it returns both date and time, but you can also 277 * make it generate only the date or only the time. 278 * 279 * @param string $type (datetime|date|time) 280 * @return \Cake\Database\Expression\FunctionExpression 281 */ 282 public function now($type = 'datetime') 283 { 284 if ($type === 'datetime') { 285 return $this->_build('NOW')->setReturnType('datetime'); 286 } 287 if ($type === 'date') { 288 return $this->_build('CURRENT_DATE')->setReturnType('date'); 289 } 290 if ($type === 'time') { 291 return $this->_build('CURRENT_TIME')->setReturnType('time'); 292 } 293 } 294 295 /** 296 * Magic method dispatcher to create custom SQL function calls 297 * 298 * @param string $name the SQL function name to construct 299 * @param array $args list with up to 3 arguments, first one being an array with 300 * parameters for the SQL function, the second one a list of types to bind to those 301 * params, and the third one the return type of the function 302 * @return \Cake\Database\Expression\FunctionExpression 303 */ 304 public function __call($name, $args) 305 { 306 switch (count($args)) { 307 case 0: 308 return $this->_build($name); 309 case 1: 310 return $this->_build($name, $args[0]); 311 case 2: 312 return $this->_build($name, $args[0], $args[1]); 313 default: 314 return $this->_build($name, $args[0], $args[1], $args[2]); 315 } 316 } 317} 318