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\QueryExpression; 18 19/** 20 * Responsible for compiling a Query object into its SQL representation 21 * 22 * @internal 23 */ 24class QueryCompiler 25{ 26 /** 27 * List of sprintf templates that will be used for compiling the SQL for 28 * this query. There are some clauses that can be built as just as the 29 * direct concatenation of the internal parts, those are listed here. 30 * 31 * @var array 32 */ 33 protected $_templates = [ 34 'delete' => 'DELETE', 35 'where' => ' WHERE %s', 36 'group' => ' GROUP BY %s ', 37 'having' => ' HAVING %s ', 38 'order' => ' %s', 39 'limit' => ' LIMIT %s', 40 'offset' => ' OFFSET %s', 41 'epilog' => ' %s', 42 ]; 43 44 /** 45 * The list of query clauses to traverse for generating a SELECT statement 46 * 47 * @var array 48 */ 49 protected $_selectParts = [ 50 'select', 'from', 'join', 'where', 'group', 'having', 'order', 'limit', 51 'offset', 'union', 'epilog', 52 ]; 53 54 /** 55 * The list of query clauses to traverse for generating an UPDATE statement 56 * 57 * @var array 58 */ 59 protected $_updateParts = ['update', 'set', 'where', 'epilog']; 60 61 /** 62 * The list of query clauses to traverse for generating a DELETE statement 63 * 64 * @var array 65 */ 66 protected $_deleteParts = ['delete', 'modifier', 'from', 'where', 'epilog']; 67 68 /** 69 * The list of query clauses to traverse for generating an INSERT statement 70 * 71 * @var array 72 */ 73 protected $_insertParts = ['insert', 'values', 'epilog']; 74 75 /** 76 * Indicate whether or not this query dialect supports ordered unions. 77 * 78 * Overridden in subclasses. 79 * 80 * @var bool 81 */ 82 protected $_orderedUnion = true; 83 84 /** 85 * Returns the SQL representation of the provided query after generating 86 * the placeholders for the bound values using the provided generator 87 * 88 * @param \Cake\Database\Query $query The query that is being compiled 89 * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions 90 * @return \Closure 91 */ 92 public function compile(Query $query, ValueBinder $generator) 93 { 94 $sql = ''; 95 $type = $query->type(); 96 $query->traverse( 97 $this->_sqlCompiler($sql, $query, $generator), 98 $this->{'_' . $type . 'Parts'} 99 ); 100 101 // Propagate bound parameters from sub-queries if the 102 // placeholders can be found in the SQL statement. 103 if ($query->getValueBinder() !== $generator) { 104 foreach ($query->getValueBinder()->bindings() as $binding) { 105 $placeholder = ':' . $binding['placeholder']; 106 if (preg_match('/' . $placeholder . '(?:\W|$)/', $sql) > 0) { 107 $generator->bind($placeholder, $binding['value'], $binding['type']); 108 } 109 } 110 } 111 112 return $sql; 113 } 114 115 /** 116 * Returns a callable object that can be used to compile a SQL string representation 117 * of this query. 118 * 119 * @param string $sql initial sql string to append to 120 * @param \Cake\Database\Query $query The query that is being compiled 121 * @param \Cake\Database\ValueBinder $generator The placeholder and value binder object 122 * @return \Closure 123 */ 124 protected function _sqlCompiler(&$sql, $query, $generator) 125 { 126 return function ($parts, $name) use (&$sql, $query, $generator) { 127 if ( 128 !isset($parts) || 129 ((is_array($parts) || $parts instanceof \Countable) && !count($parts)) 130 ) { 131 return; 132 } 133 if ($parts instanceof ExpressionInterface) { 134 $parts = [$parts->sql($generator)]; 135 } 136 if (isset($this->_templates[$name])) { 137 $parts = $this->_stringifyExpressions((array)$parts, $generator); 138 139 return $sql .= sprintf($this->_templates[$name], implode(', ', $parts)); 140 } 141 142 return $sql .= $this->{'_build' . ucfirst($name) . 'Part'}($parts, $query, $generator); 143 }; 144 } 145 146 /** 147 * Helper function used to build the string representation of a SELECT clause, 148 * it constructs the field list taking care of aliasing and 149 * converting expression objects to string. This function also constructs the 150 * DISTINCT clause for the query. 151 * 152 * @param array $parts list of fields to be transformed to string 153 * @param \Cake\Database\Query $query The query that is being compiled 154 * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions 155 * @return string 156 */ 157 protected function _buildSelectPart($parts, $query, $generator) 158 { 159 $driver = $query->getConnection()->getDriver(); 160 $select = 'SELECT%s %s%s'; 161 if ($this->_orderedUnion && $query->clause('union')) { 162 $select = '(SELECT%s %s%s'; 163 } 164 $distinct = $query->clause('distinct'); 165 $modifiers = $this->_buildModifierPart($query->clause('modifier'), $query, $generator); 166 167 $normalized = []; 168 $parts = $this->_stringifyExpressions($parts, $generator); 169 foreach ($parts as $k => $p) { 170 if (!is_numeric($k)) { 171 $p = $p . ' AS ' . $driver->quoteIdentifier($k); 172 } 173 $normalized[] = $p; 174 } 175 176 if ($distinct === true) { 177 $distinct = 'DISTINCT '; 178 } 179 180 if (is_array($distinct)) { 181 $distinct = $this->_stringifyExpressions($distinct, $generator); 182 $distinct = sprintf('DISTINCT ON (%s) ', implode(', ', $distinct)); 183 } 184 185 return sprintf($select, $modifiers, $distinct, implode(', ', $normalized)); 186 } 187 188 /** 189 * Helper function used to build the string representation of a FROM clause, 190 * it constructs the tables list taking care of aliasing and 191 * converting expression objects to string. 192 * 193 * @param array $parts list of tables to be transformed to string 194 * @param \Cake\Database\Query $query The query that is being compiled 195 * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions 196 * @return string 197 */ 198 protected function _buildFromPart($parts, $query, $generator) 199 { 200 $select = ' FROM %s'; 201 $normalized = []; 202 $parts = $this->_stringifyExpressions($parts, $generator); 203 foreach ($parts as $k => $p) { 204 if (!is_numeric($k)) { 205 $p = $p . ' ' . $k; 206 } 207 $normalized[] = $p; 208 } 209 210 return sprintf($select, implode(', ', $normalized)); 211 } 212 213 /** 214 * Helper function used to build the string representation of multiple JOIN clauses, 215 * it constructs the joins list taking care of aliasing and converting 216 * expression objects to string in both the table to be joined and the conditions 217 * to be used. 218 * 219 * @param array $parts list of joins to be transformed to string 220 * @param \Cake\Database\Query $query The query that is being compiled 221 * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions 222 * @return string 223 */ 224 protected function _buildJoinPart($parts, $query, $generator) 225 { 226 $joins = ''; 227 foreach ($parts as $join) { 228 $subquery = $join['table'] instanceof Query || $join['table'] instanceof QueryExpression; 229 if ($join['table'] instanceof ExpressionInterface) { 230 $join['table'] = $join['table']->sql($generator); 231 } 232 233 if ($subquery) { 234 $join['table'] = '(' . $join['table'] . ')'; 235 } 236 237 $joins .= sprintf(' %s JOIN %s %s', $join['type'], $join['table'], $join['alias']); 238 239 $condition = ''; 240 if (isset($join['conditions']) && $join['conditions'] instanceof ExpressionInterface) { 241 $condition = $join['conditions']->sql($generator); 242 } 243 if (strlen($condition)) { 244 $joins .= " ON {$condition}"; 245 } else { 246 $joins .= ' ON 1 = 1'; 247 } 248 } 249 250 return $joins; 251 } 252 253 /** 254 * Helper function to generate SQL for SET expressions. 255 * 256 * @param array $parts List of keys & values to set. 257 * @param \Cake\Database\Query $query The query that is being compiled 258 * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions 259 * @return string 260 */ 261 protected function _buildSetPart($parts, $query, $generator) 262 { 263 $set = []; 264 foreach ($parts as $part) { 265 if ($part instanceof ExpressionInterface) { 266 $part = $part->sql($generator); 267 } 268 if ($part[0] === '(') { 269 $part = substr($part, 1, -1); 270 } 271 $set[] = $part; 272 } 273 274 return ' SET ' . implode('', $set); 275 } 276 277 /** 278 * Builds the SQL string for all the UNION clauses in this query, when dealing 279 * with query objects it will also transform them using their configured SQL 280 * dialect. 281 * 282 * @param array $parts list of queries to be operated with UNION 283 * @param \Cake\Database\Query $query The query that is being compiled 284 * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions 285 * @return string 286 */ 287 protected function _buildUnionPart($parts, $query, $generator) 288 { 289 $parts = array_map(function ($p) use ($generator) { 290 $p['query'] = $p['query']->sql($generator); 291 $p['query'] = $p['query'][0] === '(' ? trim($p['query'], '()') : $p['query']; 292 $prefix = $p['all'] ? 'ALL ' : ''; 293 if ($this->_orderedUnion) { 294 return "{$prefix}({$p['query']})"; 295 } 296 297 return $prefix . $p['query']; 298 }, $parts); 299 300 if ($this->_orderedUnion) { 301 return sprintf(")\nUNION %s", implode("\nUNION ", $parts)); 302 } 303 304 return sprintf("\nUNION %s", implode("\nUNION ", $parts)); 305 } 306 307 /** 308 * Builds the SQL fragment for INSERT INTO. 309 * 310 * @param array $parts The insert parts. 311 * @param \Cake\Database\Query $query The query that is being compiled 312 * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions 313 * @return string SQL fragment. 314 */ 315 protected function _buildInsertPart($parts, $query, $generator) 316 { 317 $table = $parts[0]; 318 $columns = $this->_stringifyExpressions($parts[1], $generator); 319 $modifiers = $this->_buildModifierPart($query->clause('modifier'), $query, $generator); 320 321 return sprintf('INSERT%s INTO %s (%s)', $modifiers, $table, implode(', ', $columns)); 322 } 323 324 /** 325 * Builds the SQL fragment for INSERT INTO. 326 * 327 * @param array $parts The values parts. 328 * @param \Cake\Database\Query $query The query that is being compiled 329 * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions 330 * @return string SQL fragment. 331 */ 332 protected function _buildValuesPart($parts, $query, $generator) 333 { 334 return implode('', $this->_stringifyExpressions($parts, $generator)); 335 } 336 337 /** 338 * Builds the SQL fragment for UPDATE. 339 * 340 * @param array $parts The update parts. 341 * @param \Cake\Database\Query $query The query that is being compiled 342 * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions 343 * @return string SQL fragment. 344 */ 345 protected function _buildUpdatePart($parts, $query, $generator) 346 { 347 $table = $this->_stringifyExpressions($parts, $generator); 348 $modifiers = $this->_buildModifierPart($query->clause('modifier'), $query, $generator); 349 350 return sprintf('UPDATE%s %s', $modifiers, implode(',', $table)); 351 } 352 353 /** 354 * Builds the SQL modifier fragment 355 * 356 * @param array $parts The query modifier parts 357 * @param \Cake\Database\Query $query The query that is being compiled 358 * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions 359 * @return string SQL fragment. 360 */ 361 protected function _buildModifierPart($parts, $query, $generator) 362 { 363 if ($parts === []) { 364 return ''; 365 } 366 367 return ' ' . implode(' ', $this->_stringifyExpressions($parts, $generator, false)); 368 } 369 370 /** 371 * Helper function used to covert ExpressionInterface objects inside an array 372 * into their string representation. 373 * 374 * @param array $expressions list of strings and ExpressionInterface objects 375 * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions 376 * @param bool $wrap Whether to wrap each expression object with parenthesis 377 * @return array 378 */ 379 protected function _stringifyExpressions($expressions, $generator, $wrap = true) 380 { 381 $result = []; 382 foreach ($expressions as $k => $expression) { 383 if ($expression instanceof ExpressionInterface) { 384 $value = $expression->sql($generator); 385 $expression = $wrap ? '(' . $value . ')' : $value; 386 } 387 $result[$k] = $expression; 388 } 389 390 return $result; 391 } 392} 393