Namespaces

  • Latte
    • Loaders
    • Macros
    • Runtime
  • Nette
    • Application
      • Responses
      • Routers
      • UI
    • Bridges
      • ApplicationDI
      • ApplicationLatte
      • ApplicationTracy
      • CacheDI
      • CacheLatte
      • DatabaseDI
      • DatabaseTracy
      • DITracy
      • FormsDI
      • FormsLatte
      • Framework
      • HttpDI
      • HttpTracy
      • MailDI
      • ReflectionDI
      • SecurityDI
      • SecurityTracy
    • Caching
      • Storages
    • ComponentModel
    • Database
      • Conventions
      • Drivers
      • Reflection
      • Table
    • DI
      • Config
        • Adapters
      • Extensions
    • Diagnostics
    • Forms
      • Controls
      • Rendering
    • Http
    • Iterators
    • Latte
    • Loaders
    • Localization
    • Mail
    • Neon
    • PhpGenerator
    • Reflection
    • Security
    • Templating
    • Utils
  • NetteModule
  • none
  • Tracy
    • Bridges
      • Nette

Classes

  • ActiveRow
  • GroupedSelection
  • Selection
  • SqlBuilder

Interfaces

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