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