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