1: <?php
  2: 
  3: /**
  4:  * This file is part of the Nette Framework (https://nette.org)
  5:  * Copyright (c) 2004 David Grudl (https://davidgrudl.com)
  6:  */
  7: 
  8: namespace Nette\Database\Table;
  9: 
 10: use Nette;
 11: use Nette\Database\Connection;
 12: use Nette\Database\IReflection;
 13: use Nette\Database\ISupplementalDriver;
 14: use Nette\Database\SqlLiteral;
 15: 
 16: 
 17: /**
 18:  * Builds SQL query.
 19:  * SqlBuilder is based on great library NotORM http://www.notorm.com written by Jakub Vrana.
 20:  *
 21:  * @author     Jakub Vrana
 22:  * @author     Jan Skrasek
 23:  */
 24: class SqlBuilder extends Nette\Object
 25: {
 26:     /** @var Nette\Database\ISupplementalDriver */
 27:     private $driver;
 28: 
 29:     /** @var string */
 30:     protected $tableName;
 31: 
 32:     /** @var IReflection */
 33:     protected $databaseReflection;
 34: 
 35:     /** @var string delimited table name */
 36:     protected $delimitedTable;
 37: 
 38:     /** @var array of column to select */
 39:     protected $select = array();
 40: 
 41:     /** @var array of where conditions */
 42:     protected $where = array();
 43: 
 44:     /** @var array of where conditions for caching */
 45:     protected $conditions = array();
 46: 
 47:     /** @var array of parameters passed to where conditions */
 48:     protected $parameters = array(
 49:         'select' => array(),
 50:         'where' => array(),
 51:         'group' => array(),
 52:         'having' => array(),
 53:         'order' => array(),
 54:     );
 55: 
 56:     /** @var array or columns to order by */
 57:     protected $order = array();
 58: 
 59:     /** @var int number of rows to fetch */
 60:     protected $limit = NULL;
 61: 
 62:     /** @var int first row to fetch */
 63:     protected $offset = NULL;
 64: 
 65:     /** @var string columns to grouping */
 66:     protected $group = '';
 67: 
 68:     /** @var string grouping condition */
 69:     protected $having = '';
 70: 
 71: 
 72:     public function __construct($tableName, Connection $connection, IReflection $reflection)
 73:     {
 74:         $this->tableName = $tableName;
 75:         $this->databaseReflection = $reflection;
 76:         $this->driver = $connection->getSupplementalDriver();
 77:         $this->delimitedTable = $this->tryDelimite($tableName);
 78:     }
 79: 
 80: 
 81:     public function buildInsertQuery()
 82:     {
 83:         return "INSERT INTO {$this->delimitedTable}";
 84:     }
 85: 
 86: 
 87:     public function buildUpdateQuery()
 88:     {
 89:         if ($this->limit !== NULL || $this->offset) {
 90:             throw new Nette\NotSupportedException('LIMIT clause is not supported in UPDATE query.');
 91:         }
 92:         return $this->tryDelimite("UPDATE {$this->tableName} SET ?" . $this->buildConditions());
 93:     }
 94: 
 95: 
 96:     public function buildDeleteQuery()
 97:     {
 98:         if ($this->limit !== NULL || $this->offset) {
 99:             throw new Nette\NotSupportedException('LIMIT clause is not supported in DELETE query.');
100:         }
101:         return $this->tryDelimite("DELETE FROM {$this->tableName}" . $this->buildConditions());
102:     }
103: 
104: 
105:     /**
106:      * Returns SQL query.
107:      * @param  string list of columns
108:      * @return string
109:      */
110:     public function buildSelectQuery($columns = NULL)
111:     {
112:         $queryCondition = $this->buildConditions();
113:         $queryEnd       = $this->buildQueryEnd();
114: 
115:         $joins = array();
116:         $this->parseJoins($joins, $queryCondition);
117:         $this->parseJoins($joins, $queryEnd);
118: 
119:         if ($this->select) {
120:             $querySelect = $this->buildSelect($this->select);
121:             $this->parseJoins($joins, $querySelect);
122: 
123:         } elseif ($columns) {
124:             $prefix = $joins ? "{$this->delimitedTable}." : '';
125:             $cols = array();
126:             foreach ($columns as $col) {
127:                 $cols[] = $prefix . $col;
128:             }
129:             $querySelect = $this->buildSelect($cols);
130: 
131:         } elseif ($this->group && !$this->driver->isSupported(ISupplementalDriver::SUPPORT_SELECT_UNGROUPED_COLUMNS)) {
132:             $querySelect = $this->buildSelect(array($this->group));
133:             $this->parseJoins($joins, $querySelect);
134: 
135:         } else {
136:             $prefix = $joins ? "{$this->delimitedTable}." : '';
137:             $querySelect = $this->buildSelect(array($prefix . '*'));
138: 
139:         }
140: 
141:         $queryJoins = $this->buildQueryJoins($joins);
142:         $query = "{$querySelect} FROM {$this->tableName}{$queryJoins}{$queryCondition}{$queryEnd}";
143: 
144:         if ($this->limit !== NULL || $this->offset) {
145:             $this->driver->applyLimit($query, $this->limit, $this->offset);
146:         }
147: 
148:         return $this->tryDelimite($query);
149:     }
150: 
151: 
152:     public function getParameters()
153:     {
154:         return array_merge(
155:             $this->parameters['select'],
156:             $this->parameters['where'],
157:             $this->parameters['group'],
158:             $this->parameters['having'],
159:             $this->parameters['order']
160:         );
161:     }
162: 
163: 
164:     public function importConditions(SqlBuilder $builder)
165:     {
166:         $this->where = $builder->where;
167:         $this->parameters['where'] = $builder->parameters['where'];
168:         $this->conditions = $builder->conditions;
169:     }
170: 
171: 
172:     /********************* SQL selectors ****************d*g**/
173: 
174: 
175:     public function addSelect($columns)
176:     {
177:         if (is_array($columns)) {
178:             throw new Nette\InvalidArgumentException('Select column must be a string.');
179:         }
180:         $this->select[] = $columns;
181:         $this->parameters['select'] = array_merge($this->parameters['select'], array_slice(func_get_args(), 1));
182:     }
183: 
184: 
185:     public function getSelect()
186:     {
187:         return $this->select;
188:     }
189: 
190: 
191:     public function addWhere($condition, $parameters = array())
192:     {
193:         if (is_array($condition) && is_array($parameters) && !empty($parameters)) {
194:             return $this->addWhereComposition($condition, $parameters);
195:         }
196: 
197:         $args = func_get_args();
198:         $hash = md5(json_encode($args));
199:         if (isset($this->conditions[$hash])) {
200:             return FALSE;
201:         }
202: 
203:         $this->conditions[$hash] = $condition;
204:         $placeholderCount = substr_count($condition, '?');
205:         if ($placeholderCount > 1 && count($args) === 2 && is_array($parameters)) {
206:             $args = $parameters;
207:         } else {
208:             array_shift($args);
209:         }
210: 
211:         $condition = trim($condition);
212:         if ($placeholderCount === 0 && count($args) === 1) {
213:             $condition .= ' ?';
214:         } elseif ($placeholderCount !== count($args)) {
215:             throw new Nette\InvalidArgumentException('Argument count does not match placeholder count.');
216:         }
217: 
218:         $replace = NULL;
219:         $placeholderNum = 0;
220:         foreach ($args as $arg) {
221:             preg_match('#(?:.*?\?.*?){' . $placeholderNum . '}(((?:&|\||^|~|\+|-|\*|/|%|\(|,|<|>|=|(?<=\W|^)(?:REGEXP|ALL|AND|ANY|BETWEEN|EXISTS|IN|[IR]?LIKE|OR|NOT|SOME|INTERVAL))\s*)?(?:\(\?\)|\?))#s', $condition, $match, PREG_OFFSET_CAPTURE);
222:             $hasOperator = ($match[1][0] === '?' && $match[1][1] === 0) ? TRUE : !empty($match[2][0]);
223: 
224:             if ($arg === NULL) {
225:                 if ($hasOperator) {
226:                     throw new Nette\InvalidArgumentException('Column operator does not accept NULL argument.');
227:                 }
228:                 $replace = 'IS NULL';
229:             } elseif (is_array($arg) || $arg instanceof Selection) {
230:                 if ($hasOperator) {
231:                     if (trim($match[2][0]) === 'NOT') {
232:                         $match[2][0] = rtrim($match[2][0]) . ' IN ';
233:                     } elseif (trim($match[2][0]) !== 'IN') {
234:                         throw new Nette\InvalidArgumentException('Column operator does not accept array argument.');
235:                     }
236:                 } else {
237:                     $match[2][0] = 'IN ';
238:                 }
239: 
240:                 if ($arg instanceof Selection) {
241:                     $clone = clone $arg;
242:                     if (!$clone->getSqlBuilder()->select) {
243:                         try {
244:                             $clone->select($clone->getPrimary());
245:                         } catch (\LogicException $e) {
246:                             throw new Nette\InvalidArgumentException('Selection argument must have defined a select column.', 0, $e);
247:                         }
248:                     }
249: 
250:                     if ($this->driver->isSupported(ISupplementalDriver::SUPPORT_SUBSELECT)) {
251:                         $arg = NULL;
252:                         $replace = $match[2][0] . '(' . $clone->getSql() . ')';
253:                         $this->parameters['where'] = array_merge($this->parameters['where'], $clone->getSqlBuilder()->parameters['where']);
254:                     } else {
255:                         $arg = array();
256:                         foreach ($clone as $row) {
257:                             $arg[] = array_values(iterator_to_array($row));
258:                         }
259:                     }
260:                 }
261: 
262:                 if ($arg !== NULL) {
263:                     if (!$arg) {
264:                         $hasBrackets = strpos($condition, '(') !== FALSE;
265:                         $hasOperators = preg_match('#AND|OR#', $condition);
266:                         $hasNot = strpos($condition, 'NOT') !== FALSE;
267:                         $hasPrefixNot = strpos($match[2][0], 'NOT') !== FALSE;
268:                         if (!$hasBrackets && ($hasOperators || ($hasNot && !$hasPrefixNot))) {
269:                             throw new Nette\InvalidArgumentException('Possible SQL query corruption. Add parentheses around operators.');
270:                         }
271:                         if ($hasPrefixNot) {
272:                             $replace = 'IS NULL OR TRUE';
273:                         } else {
274:                             $replace = 'IS NULL AND FALSE';
275:                         }
276:                         $arg = NULL;
277:                     } else {
278:                         $replace = $match[2][0] . '(?)';
279:                         $this->parameters['where'][] = $arg;
280:                     }
281:                 }
282:             } elseif ($arg instanceof SqlLiteral) {
283:                 $this->parameters['where'][] = $arg;
284:             } else {
285:                 if (!$hasOperator) {
286:                     $replace = '= ?';
287:                 }
288:                 $this->parameters['where'][] = $arg;
289:             }
290: 
291:             if ($replace) {
292:                 $condition = substr_replace($condition, $replace, $match[1][1], strlen($match[1][0]));
293:                 $replace = NULL;
294:             }
295: 
296:             if ($arg !== NULL) {
297:                 $placeholderNum++;
298:             }
299:         }
300: 
301:         $this->where[] = $condition;
302:         return TRUE;
303:     }
304: 
305: 
306:     public function getConditions()
307:     {
308:         return array_values($this->conditions);
309:     }
310: 
311: 
312:     public function addOrder($columns)
313:     {
314:         $this->order[] = $columns;
315:         $this->parameters['order'] = array_merge($this->parameters['order'], array_slice(func_get_args(), 1));
316:     }
317: 
318: 
319:     public function getOrder()
320:     {
321:         return $this->order;
322:     }
323: 
324: 
325:     public function setLimit($limit, $offset)
326:     {
327:         $this->limit = $limit;
328:         $this->offset = $offset;
329:     }
330: 
331: 
332:     public function getLimit()
333:     {
334:         return $this->limit;
335:     }
336: 
337: 
338:     public function getOffset()
339:     {
340:         return $this->offset;
341:     }
342: 
343: 
344:     public function setGroup($columns)
345:     {
346:         $this->group = $columns;
347:         $this->parameters['group'] = array_slice(func_get_args(), 1);
348:     }
349: 
350: 
351:     public function getGroup()
352:     {
353:         return $this->group;
354:     }
355: 
356: 
357:     public function setHaving($having)
358:     {
359:         $this->having = $having;
360:         $this->parameters['having'] = array_slice(func_get_args(), 1);
361:     }
362: 
363: 
364:     public function getHaving()
365:     {
366:         return $this->having;
367:     }
368: 
369: 
370:     /********************* SQL building ****************d*g**/
371: 
372: 
373:     protected function buildSelect(array $columns)
374:     {
375:         return 'SELECT ' . implode(', ', $columns);
376:     }
377: 
378: 
379:     protected function parseJoins(& $joins, & $query)
380:     {
381:         $builder = $this;
382:         $query = preg_replace_callback('~
383:             (?(DEFINE)
384:                 (?P<word> [a-z][\w_]* )
385:                 (?P<del> [.:] )
386:                 (?P<node> (?&del)? (?&word) (\((?&word)\))? )
387:             )
388:             (?P<chain> (?!\.) (?&node)*)  \. (?P<column> (?&word) | \*  )
389:         ~xi', function ($match) use (& $joins, $builder) {
390:             return $builder->parseJoinsCb($joins, $match);
391:         }, $query);
392:     }
393: 
394: 
395:     public function parseJoinsCb(& $joins, $match)
396:     {
397:         $chain = $match['chain'];
398:         if (!empty($chain[0]) && ($chain[0] !== '.' || $chain[0] !== ':')) {
399:             $chain = '.' . $chain;  // unified chain format
400:         }
401: 
402:         $parent = $parentAlias = $this->tableName;
403:         if ($chain == ".{$parent}") { // case-sensitive
404:             return "{$parent}.{$match['column']}";
405:         }
406: 
407:         preg_match_all('~
408:             (?(DEFINE)
409:                 (?P<word> [a-z][\w_]* )
410:             )
411:             (?P<del> [.:])?(?P<key> (?&word))(\((?P<throughColumn> (?&word))\))?
412:         ~xi', $chain, $keyMatches, PREG_SET_ORDER);
413: 
414:         foreach ($keyMatches as $keyMatch) {
415:             if ($keyMatch['del'] === ':') {
416:                 if (isset($keyMatch['throughColumn'])) {
417:                     $table = $keyMatch['key'];
418:                     list(, $primary) = $this->databaseReflection->getBelongsToReference($table, $keyMatch['throughColumn']);
419:                 } else {
420:                     list($table, $primary) = $this->databaseReflection->getHasManyReference($parent, $keyMatch['key']);
421:                 }
422:                 $column = $this->databaseReflection->getPrimary($parent);
423:             } else {
424:                 list($table, $column) = $this->databaseReflection->getBelongsToReference($parent, $keyMatch['key']);
425:                 $primary = $this->databaseReflection->getPrimary($table);
426:             }
427: 
428:             $joins[$table . $column] = array($table, $keyMatch['key'] ?: $table, $parentAlias, $column, $primary);
429:             $parent = $table;
430:             $parentAlias = $keyMatch['key'];
431:         }
432: 
433:         return ($keyMatch['key'] ?: $table) . ".{$match['column']}";
434:     }
435: 
436: 
437:     protected function buildQueryJoins(array $joins)
438:     {
439:         $return = '';
440:         foreach ($joins as $join) {
441:             list($joinTable, $joinAlias, $table, $tableColumn, $joinColumn) = $join;
442: 
443:             $return .=
444:                 " LEFT JOIN {$joinTable}" . ($joinTable !== $joinAlias ? " AS {$joinAlias}" : '') .
445:                 " ON {$table}.{$tableColumn} = {$joinAlias}.{$joinColumn}";
446:         }
447: 
448:         return $return;
449:     }
450: 
451: 
452:     protected function buildConditions()
453:     {
454:         return $this->where ? ' WHERE (' . implode(') AND (', $this->where) . ')' : '';
455:     }
456: 
457: 
458:     protected function buildQueryEnd()
459:     {
460:         $return = '';
461:         if ($this->group) {
462:             $return .= ' GROUP BY '. $this->group;
463:         }
464:         if ($this->having) {
465:             $return .= ' HAVING '. $this->having;
466:         }
467:         if ($this->order) {
468:             $return .= ' ORDER BY ' . implode(', ', $this->order);
469:         }
470:         return $return;
471:     }
472: 
473: 
474:     protected function tryDelimite($s)
475:     {
476:         $driver = $this->driver;
477:         return preg_replace_callback('#(?<=[^\w`"\[]|^)[a-z_][a-z0-9_]*(?=[^\w`"(\]]|\z)#i', function ($m) use ($driver) {
478:             return strtoupper($m[0]) === $m[0] ? $m[0] : $driver->delimite($m[0]);
479:         }, $s);
480:     }
481: 
482: 
483:     protected function addWhereComposition(array $columns, array $parameters)
484:     {
485:         if ($this->driver->isSupported(ISupplementalDriver::SUPPORT_MULTI_COLUMN_AS_OR_COND)) {
486:             $conditionFragment = '(' . implode(' = ? AND ', $columns) . ' = ?) OR ';
487:             $condition = substr(str_repeat($conditionFragment, count($parameters)), 0, -4);
488:             return $this->addWhere($condition, Nette\Utils\Arrays::flatten($parameters));
489:         } else {
490:             return $this->addWhere('(' . implode(', ', $columns) . ') IN', $parameters);
491:         }
492:     }
493: 
494: }
495: