1: <?php
2:
3: 4: 5: 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: 20: 21:
22: class SqlBuilder
23: {
24: use Nette\SmartObject;
25:
26:
27: protected $tableName;
28:
29:
30: protected $conventions;
31:
32:
33: protected $delimitedTable;
34:
35:
36: protected $select = [];
37:
38:
39: protected $where = [];
40:
41:
42: protected $joinCondition = [];
43:
44:
45: protected $conditions = [];
46:
47:
48: protected $parameters = [
49: 'select' => [],
50: 'joinCondition' => [],
51: 'where' => [],
52: 'group' => [],
53: 'having' => [],
54: 'order' => [],
55: ];
56:
57:
58: protected $order = [];
59:
60:
61: protected $limit = NULL;
62:
63:
64: protected $offset = NULL;
65:
66:
67: protected $group = '';
68:
69:
70: protected $having = '';
71:
72:
73: protected $reservedTableNames = [];
74:
75:
76: protected $aliases = [];
77:
78:
79: protected $currentAlias = NULL;
80:
81:
82: private $driver;
83:
84:
85: private $structure;
86:
87:
88: private $cacheTableList;
89:
90:
91: private $expandingJoins = [];
92:
93:
94: public function __construct($tableName, Context $context)
95: {
96: $this->tableName = $tableName;
97: $this->driver = $context->getConnection()->getSupplementalDriver();
98: $this->conventions = $context->getConventions();
99: $this->structure = $context->getStructure();
100: $tableNameParts = explode('.', $tableName);
101: $this->delimitedTable = implode('.', array_map([$this->driver, 'delimite'], $tableNameParts));
102: $this->checkUniqueTableName(end($tableNameParts), $tableName);
103: }
104:
105:
106: 107: 108:
109: public function getTableName()
110: {
111: return $this->tableName;
112: }
113:
114:
115: 116: 117:
118: public function buildInsertQuery()
119: {
120: return "INSERT INTO {$this->delimitedTable}";
121: }
122:
123:
124: 125: 126:
127: public function buildUpdateQuery()
128: {
129: $query = "UPDATE {$this->delimitedTable} SET ?set" . $this->tryDelimite($this->buildConditions());
130: if ($this->limit !== NULL || $this->offset) {
131: $this->driver->applyLimit($query, $this->limit, $this->offset);
132: }
133:
134: return $query;
135: }
136:
137:
138: 139: 140:
141: public function buildDeleteQuery()
142: {
143: $query = "DELETE FROM {$this->delimitedTable}" . $this->tryDelimite($this->buildConditions());
144: if ($this->limit !== NULL || $this->offset) {
145: $this->driver->applyLimit($query, $this->limit, $this->offset);
146: }
147:
148: return $query;
149: }
150:
151:
152: 153: 154: 155:
156: public function getSelectQueryHash(array $columns = NULL)
157: {
158: $parts = [
159: 'delimitedTable' => $this->delimitedTable,
160: 'queryCondition' => $this->buildConditions(),
161: 'queryEnd' => $this->buildQueryEnd(),
162: $this->aliases,
163: $this->limit, $this->offset,
164: ];
165: if ($this->select) {
166: $parts[] = $this->select;
167: } elseif ($columns) {
168: $parts[] = [$this->delimitedTable, $columns];
169: } elseif ($this->group && !$this->driver->isSupported(ISupplementalDriver::SUPPORT_SELECT_UNGROUPED_COLUMNS)) {
170: $parts[] = [$this->group];
171: } else {
172: $parts[] = "{$this->delimitedTable}.*";
173: }
174: return $this->getConditionHash(json_encode($parts), [
175: $this->parameters['select'],
176: $this->parameters['joinCondition'],
177: $this->parameters['where'],
178: $this->parameters['group'],
179: $this->parameters['having'],
180: $this->parameters['order']
181: ]);
182: }
183:
184:
185: 186: 187: 188: 189:
190: public function buildSelectQuery(array $columns = NULL)
191: {
192: if (!$this->order && ($this->limit !== NULL || $this->offset)) {
193: $this->order = array_map(
194: function ($col) { return "$this->tableName.$col"; },
195: (array) $this->conventions->getPrimary($this->tableName)
196: );
197: }
198:
199: $queryJoinConditions = $this->buildJoinConditions();
200: $queryCondition = $this->buildConditions();
201: $queryEnd = $this->buildQueryEnd();
202:
203: $joins = [];
204: $finalJoinConditions = $this->parseJoinConditions($joins, $queryJoinConditions);
205: $this->parseJoins($joins, $queryCondition);
206: $this->parseJoins($joins, $queryEnd);
207:
208: if ($this->select) {
209: $querySelect = $this->buildSelect($this->select);
210: $this->parseJoins($joins, $querySelect);
211:
212: } elseif ($columns) {
213: $prefix = $joins ? "{$this->delimitedTable}." : '';
214: $cols = [];
215: foreach ($columns as $col) {
216: $cols[] = $prefix . $col;
217: }
218: $querySelect = $this->buildSelect($cols);
219:
220: } elseif ($this->group && !$this->driver->isSupported(ISupplementalDriver::SUPPORT_SELECT_UNGROUPED_COLUMNS)) {
221: $querySelect = $this->buildSelect([$this->group]);
222: $this->parseJoins($joins, $querySelect);
223:
224: } else {
225: $prefix = $joins ? "{$this->delimitedTable}." : '';
226: $querySelect = $this->buildSelect([$prefix . '*']);
227: }
228:
229: $queryJoins = $this->buildQueryJoins($joins, $finalJoinConditions);
230: $query = "{$querySelect} FROM {$this->delimitedTable}{$queryJoins}{$queryCondition}{$queryEnd}";
231:
232: $this->driver->applyLimit($query, $this->limit, $this->offset);
233:
234: return $this->tryDelimite($query);
235: }
236:
237:
238: 239: 240:
241: public function getParameters()
242: {
243: if (!isset($this->parameters['joinConditionSorted'])) {
244: $this->buildSelectQuery();
245: }
246: return array_merge(
247: $this->parameters['select'],
248: $this->parameters['joinConditionSorted'] ? call_user_func_array('array_merge', $this->parameters['joinConditionSorted']) : [],
249: $this->parameters['where'],
250: $this->parameters['group'],
251: $this->parameters['having'],
252: $this->parameters['order']
253: );
254: }
255:
256:
257: public function importConditions(SqlBuilder $builder)
258: {
259: $this->where = $builder->where;
260: $this->joinCondition = $builder->joinCondition;
261: $this->parameters['where'] = $builder->parameters['where'];
262: $this->parameters['joinCondition'] = $builder->parameters['joinCondition'];
263: $this->conditions = $builder->conditions;
264: $this->aliases = $builder->aliases;
265: $this->reservedTableNames = $builder->reservedTableNames;
266: }
267:
268:
269:
270:
271:
272: public function addSelect($columns, ...$params)
273: {
274: if (is_array($columns)) {
275: throw new Nette\InvalidArgumentException('Select column must be a string.');
276: }
277: $this->select[] = $columns;
278: $this->parameters['select'] = array_merge($this->parameters['select'], $params);
279: }
280:
281:
282: 283: 284:
285: public function getSelect()
286: {
287: return $this->select;
288: }
289:
290:
291: 292: 293:
294: public function addWhere($condition, ...$params)
295: {
296: return $this->addCondition($condition, $params, $this->where, $this->parameters['where']);
297: }
298:
299:
300: 301: 302:
303: public function addJoinCondition($tableChain, $condition, ...$params)
304: {
305: $this->parameters['joinConditionSorted'] = NULL;
306: if (!isset($this->joinCondition[$tableChain])) {
307: $this->joinCondition[$tableChain] = $this->parameters['joinCondition'][$tableChain] = [];
308: }
309: return $this->addCondition($condition, $params, $this->joinCondition[$tableChain], $this->parameters['joinCondition'][$tableChain]);
310: }
311:
312:
313: 314: 315:
316: protected function addCondition($condition, array $params, array &$conditions, array &$conditionsParameters)
317: {
318: if (is_array($condition) && !empty($params[0]) && is_array($params[0])) {
319: return $this->addConditionComposition($condition, $params[0], $conditions, $conditionsParameters);
320: }
321:
322: $hash = $this->getConditionHash($condition, $params);
323: if (isset($this->conditions[$hash])) {
324: return FALSE;
325: }
326:
327: $this->conditions[$hash] = $condition;
328: $placeholderCount = substr_count($condition, '?');
329: if ($placeholderCount > 1 && count($params) === 1 && is_array($params[0])) {
330: $params = $params[0];
331: }
332:
333: $condition = trim($condition);
334: if ($placeholderCount === 0 && count($params) === 1) {
335: $condition .= ' ?';
336: } elseif ($placeholderCount !== count($params)) {
337: throw new Nette\InvalidArgumentException('Argument count does not match placeholder count.');
338: }
339:
340: $replace = NULL;
341: $placeholderNum = 0;
342: foreach ($params as $arg) {
343: preg_match('#(?:.*?\?.*?){' . $placeholderNum . '}(((?:&|\||^|~|\+|-|\*|/|%|\(|,|<|>|=|(?<=\W|^)(?:REGEXP|ALL|AND|ANY|BETWEEN|EXISTS|IN|[IR]?LIKE|OR|NOT|SOME|INTERVAL))\s*)?(?:\(\?\)|\?))#s', $condition, $match, PREG_OFFSET_CAPTURE);
344: $hasOperator = ($match[1][0] === '?' && $match[1][1] === 0) ? TRUE : !empty($match[2][0]);
345:
346: if ($arg === NULL) {
347: $replace = 'IS NULL';
348: if ($hasOperator) {
349: if (trim($match[2][0]) === 'NOT') {
350: $replace = 'IS NOT NULL';
351: } else {
352: throw new Nette\InvalidArgumentException('Column operator does not accept NULL argument.');
353: }
354: }
355: } elseif (is_array($arg) || $arg instanceof Selection) {
356: if ($hasOperator) {
357: if (trim($match[2][0]) === 'NOT') {
358: $match[2][0] = rtrim($match[2][0]) . ' IN ';
359: } elseif (trim($match[2][0]) !== 'IN') {
360: throw new Nette\InvalidArgumentException('Column operator does not accept array argument.');
361: }
362: } else {
363: $match[2][0] = 'IN ';
364: }
365:
366: if ($arg instanceof Selection) {
367: $clone = clone $arg;
368: if (!$clone->getSqlBuilder()->select) {
369: try {
370: $clone->select($clone->getPrimary());
371: } catch (\LogicException $e) {
372: throw new Nette\InvalidArgumentException('Selection argument must have defined a select column.', 0, $e);
373: }
374: }
375:
376: if ($this->driver->isSupported(ISupplementalDriver::SUPPORT_SUBSELECT)) {
377: $arg = NULL;
378: $replace = $match[2][0] . '(' . $clone->getSql() . ')';
379: $conditionsParameters = array_merge($conditionsParameters, $clone->getSqlBuilder()->getParameters());
380: } else {
381: $arg = [];
382: foreach ($clone as $row) {
383: $arg[] = array_values(iterator_to_array($row));
384: }
385: }
386: }
387:
388: if ($arg !== NULL) {
389: if (!$arg) {
390: $hasBrackets = strpos($condition, '(') !== FALSE;
391: $hasOperators = preg_match('#AND|OR#', $condition);
392: $hasNot = strpos($condition, 'NOT') !== FALSE;
393: $hasPrefixNot = strpos($match[2][0], 'NOT') !== FALSE;
394: if (!$hasBrackets && ($hasOperators || ($hasNot && !$hasPrefixNot))) {
395: throw new Nette\InvalidArgumentException('Possible SQL query corruption. Add parentheses around operators.');
396: }
397: if ($hasPrefixNot) {
398: $replace = 'IS NULL OR TRUE';
399: } else {
400: $replace = 'IS NULL AND FALSE';
401: }
402: $arg = NULL;
403: } else {
404: $replace = $match[2][0] . '(?)';
405: $conditionsParameters[] = $arg;
406: }
407: }
408: } elseif ($arg instanceof SqlLiteral) {
409: $conditionsParameters[] = $arg;
410: } else {
411: if (!$hasOperator) {
412: $replace = '= ?';
413: }
414: $conditionsParameters[] = $arg;
415: }
416:
417: if ($replace) {
418: $condition = substr_replace($condition, $replace, $match[1][1], strlen($match[1][0]));
419: $replace = NULL;
420: }
421:
422: if ($arg !== NULL) {
423: $placeholderNum++;
424: }
425: }
426:
427: $conditions[] = $condition;
428: return TRUE;
429: }
430:
431:
432: 433: 434:
435: public function getConditions()
436: {
437: return array_values($this->conditions);
438: }
439:
440:
441: 442: 443: 444: 445: 446:
447: public function addAlias($chain, $alias)
448: {
449: if (isset($chain[0]) && $chain[0] !== '.' && $chain[0] !== ':') {
450: $chain = '.' . $chain;
451: }
452: $this->checkUniqueTableName($alias, $chain);
453: $this->aliases[$alias] = $chain;
454: }
455:
456:
457: 458: 459: 460: 461:
462: protected function checkUniqueTableName($tableName, $chain)
463: {
464: if (isset($this->aliases[$tableName]) && ('.' . $tableName === $chain)) {
465: $chain = $this->aliases[$tableName];
466: }
467: if (isset($this->reservedTableNames[$tableName])) {
468: if ($this->reservedTableNames[$tableName] === $chain) {
469: return;
470: }
471: throw new Nette\InvalidArgumentException("Table alias '$tableName' from chain '$chain' is already in use by chain '{$this->reservedTableNames[$tableName]}'. Please add/change alias for one of them.");
472: }
473: $this->reservedTableNames[$tableName] = $chain;
474: }
475:
476:
477: public function addOrder($columns, ...$params)
478: {
479: $this->order[] = $columns;
480: $this->parameters['order'] = array_merge($this->parameters['order'], $params);
481: }
482:
483:
484: public function setOrder(array $columns, array $parameters)
485: {
486: $this->order = $columns;
487: $this->parameters['order'] = $parameters;
488: }
489:
490:
491: 492: 493:
494: public function getOrder()
495: {
496: return $this->order;
497: }
498:
499:
500: 501: 502: 503: 504:
505: public function setLimit($limit, $offset)
506: {
507: $this->limit = $limit;
508: $this->offset = $offset;
509: }
510:
511:
512: 513: 514:
515: public function getLimit()
516: {
517: return $this->limit;
518: }
519:
520:
521: 522: 523:
524: public function getOffset()
525: {
526: return $this->offset;
527: }
528:
529:
530: public function setGroup($columns, ...$params)
531: {
532: $this->group = $columns;
533: $this->parameters['group'] = $params;
534: }
535:
536:
537: 538: 539:
540: public function getGroup()
541: {
542: return $this->group;
543: }
544:
545:
546: public function setHaving($having, ...$params)
547: {
548: $this->having = $having;
549: $this->parameters['having'] = $params;
550: }
551:
552:
553: 554: 555:
556: public function getHaving()
557: {
558: return $this->having;
559: }
560:
561:
562:
563:
564:
565: 566: 567:
568: protected function buildSelect(array $columns)
569: {
570: return 'SELECT ' . implode(', ', $columns);
571: }
572:
573:
574: 575: 576:
577: protected function parseJoinConditions(&$joins, $joinConditions)
578: {
579: $tableJoins = $leftJoinDependency = $finalJoinConditions = [];
580: foreach ($joinConditions as $tableChain => &$joinCondition) {
581: $fooQuery = $tableChain . '.foo';
582: $requiredJoins = [];
583: $this->parseJoins($requiredJoins, $fooQuery);
584: $tableAlias = substr($fooQuery, 0, -4);
585: $tableJoins[$tableAlias] = $requiredJoins;
586: $leftJoinDependency[$tableAlias] = [];
587: $finalJoinConditions[$tableAlias] = preg_replace_callback($this->getColumnChainsRegxp(), function ($match) use ($tableAlias, &$tableJoins, &$leftJoinDependency) {
588: $requiredJoins = [];
589: $query = $this->parseJoinsCb($requiredJoins, $match);
590: $queryParts = explode('.', $query);
591: $tableJoins[$queryParts[0]] = $requiredJoins;
592: if ($queryParts[0] !== $tableAlias) {
593: foreach (array_keys($requiredJoins) as $requiredTable) {
594: $leftJoinDependency[$tableAlias][$requiredTable] = $requiredTable;
595: }
596: }
597: return $query;
598: }, $joinCondition);
599: }
600: $this->parameters['joinConditionSorted'] = [];
601: if (count($joinConditions)) {
602: while (reset($tableJoins)) {
603: $this->getSortedJoins(key($tableJoins), $leftJoinDependency, $tableJoins, $joins);
604: }
605: }
606: return $finalJoinConditions;
607: }
608:
609:
610: protected function getSortedJoins($table, &$leftJoinDependency, &$tableJoins, &$finalJoins)
611: {
612: if (isset($this->expandingJoins[$table])) {
613: $path = implode("' => '", array_map(function($value) { return $this->reservedTableNames[$value]; }, array_merge(array_keys($this->expandingJoins), [$table])));
614: throw new Nette\InvalidArgumentException("Circular reference detected at left join conditions (tables '$path').");
615: }
616: if (isset($tableJoins[$table])) {
617: $this->expandingJoins[$table] = TRUE;
618: if (isset($leftJoinDependency[$table])) {
619: foreach ($leftJoinDependency[$table] as $requiredTable) {
620: if ($requiredTable === $table) {
621: continue;
622: }
623: $this->getSortedJoins($requiredTable, $leftJoinDependency, $tableJoins, $finalJoins);
624: }
625: }
626: if ($tableJoins[$table]) {
627: foreach ($tableJoins[$table] as $requiredTable => $tmp) {
628: if ($requiredTable === $table) {
629: continue;
630: }
631: $this->getSortedJoins($requiredTable, $leftJoinDependency, $tableJoins, $finalJoins);
632: }
633: }
634: $finalJoins += $tableJoins[$table];
635: $key = isset($this->aliases[$table]) ? $table : $this->reservedTableNames[$table];
636: $this->parameters['joinConditionSorted'] += isset($this->parameters['joinCondition'][$key])
637: ? [$table => $this->parameters['joinCondition'][$key]]
638: : [];
639: unset($tableJoins[$table]);
640: unset($this->expandingJoins[$table]);
641: }
642: }
643:
644:
645: protected function parseJoins(&$joins, &$query)
646: {
647: $query = preg_replace_callback($this->getColumnChainsRegxp(), function ($match) use (&$joins) {
648: return $this->parseJoinsCb($joins, $match);
649: }, $query);
650: }
651:
652:
653: 654: 655:
656: private function getColumnChainsRegxp()
657: {
658: return '~
659: (?(DEFINE)
660: (?P<word> [\w_]*[a-z][\w_]* )
661: (?P<del> [.:] )
662: (?P<node> (?&del)? (?&word) (\((?&word)\))? )
663: )
664: (?P<chain> (?!\.) (?&node)*) \. (?P<column> (?&word) | \* )
665: ~xi';
666: }
667:
668:
669: 670: 671:
672: public function parseJoinsCb(&$joins, $match)
673: {
674: $chain = $match['chain'];
675: if (!empty($chain[0]) && ($chain[0] !== '.' && $chain[0] !== ':')) {
676: $chain = '.' . $chain;
677: }
678:
679: preg_match_all('~
680: (?(DEFINE)
681: (?P<word> [\w_]*[a-z][\w_]* )
682: )
683: (?P<del> [.:])?(?P<key> (?&word))(\((?P<throughColumn> (?&word))\))?
684: ~xi', $chain, $keyMatches, PREG_SET_ORDER);
685:
686: $parent = $this->tableName;
687: $parentAlias = preg_replace('#^(.*\.)?(.*)$#', '$2', $this->tableName);
688:
689:
690: if ($this->driver->isSupported(ISupplementalDriver::SUPPORT_SCHEMA) && count($keyMatches) > 1) {
691: $tables = $this->getCachedTableList();
692: if (!isset($tables[$keyMatches[0]['key']]) && isset($tables[$keyMatches[0]['key'] . '.' . $keyMatches[1]['key']])) {
693: $keyMatch = array_shift($keyMatches);
694: $keyMatches[0]['key'] = $keyMatch['key'] . '.' . $keyMatches[0]['key'];
695: $keyMatches[0]['del'] = $keyMatch['del'];
696: }
697: }
698:
699:
700:
701: if ($keyMatches[0]['del'] === '.') {
702: if (count($keyMatches) > 1 && ($parent === $keyMatches[0]['key'] || $parentAlias === $keyMatches[0]['key'])) {
703: throw new Nette\InvalidArgumentException("Do not prefix table chain with origin table name '{$keyMatches[0]['key']}'. If you want to make self reference, please add alias.");
704: }
705: if ($parent === $keyMatches[0]['key']) {
706: return "{$parent}.{$match['column']}";
707: } elseif ($parentAlias === $keyMatches[0]['key']) {
708: return "{$parentAlias}.{$match['column']}";
709: }
710: }
711: $tableChain = NULL;
712: foreach ($keyMatches as $index => $keyMatch) {
713: $isLast = !isset($keyMatches[$index + 1]);
714: if (!$index && isset($this->aliases[$keyMatch['key']])) {
715: if ($keyMatch['del'] === ':') {
716: throw new Nette\InvalidArgumentException("You are using has many syntax with alias (':{$keyMatch['key']}'). You have to move it to alias definition.");
717: } else {
718: $previousAlias = $this->currentAlias;
719: $this->currentAlias = $keyMatch['key'];
720: $requiredJoins = [];
721: $query = $this->aliases[$keyMatch['key']] . '.foo';
722: $this->parseJoins($requiredJoins, $query);
723: $aliasJoin = array_pop($requiredJoins);
724: $joins += $requiredJoins;
725: list($table, , $parentAlias, $column, $primary) = $aliasJoin;
726: $this->currentAlias = $previousAlias;
727: }
728: } elseif ($keyMatch['del'] === ':') {
729: if (isset($keyMatch['throughColumn'])) {
730: $table = $keyMatch['key'];
731: $belongsTo = $this->conventions->getBelongsToReference($table, $keyMatch['throughColumn']);
732: if (!$belongsTo) {
733: throw new Nette\InvalidArgumentException("No reference found for \${$parent}->{$keyMatch['key']}.");
734: }
735: list(, $primary) = $belongsTo;
736:
737: } else {
738: $hasMany = $this->conventions->getHasManyReference($parent, $keyMatch['key']);
739: if (!$hasMany) {
740: throw new Nette\InvalidArgumentException("No reference found for \${$parent}->related({$keyMatch['key']}).");
741: }
742: list($table, $primary) = $hasMany;
743: }
744: $column = $this->conventions->getPrimary($parent);
745:
746: } else {
747: $belongsTo = $this->conventions->getBelongsToReference($parent, $keyMatch['key']);
748: if (!$belongsTo) {
749: throw new Nette\InvalidArgumentException("No reference found for \${$parent}->{$keyMatch['key']}.");
750: }
751: list($table, $column) = $belongsTo;
752: $primary = $this->conventions->getPrimary($table);
753: }
754:
755: if ($this->currentAlias && $isLast) {
756: $tableAlias = $this->currentAlias;
757: } elseif ($parent === $table) {
758: $tableAlias = $parentAlias . '_ref';
759: } elseif ($keyMatch['key']) {
760: $tableAlias = $keyMatch['key'];
761: } else {
762: $tableAlias = preg_replace('#^(.*\.)?(.*)$#', '$2', $table);
763: }
764:
765: $tableChain .= $keyMatch[0];
766: if (!$isLast || !$this->currentAlias) {
767: $this->checkUniqueTableName($tableAlias, $tableChain);
768: }
769: $joins[$tableAlias] = [$table, $tableAlias, $parentAlias, $column, $primary];
770: $parent = $table;
771: $parentAlias = $tableAlias;
772: }
773:
774: return $tableAlias . ".{$match['column']}";
775: }
776:
777:
778: 779: 780:
781: protected function buildQueryJoins(array $joins, array $leftJoinConditions = [])
782: {
783: $return = '';
784: foreach ($joins as list($joinTable, $joinAlias, $table, $tableColumn, $joinColumn)) {
785: $return .=
786: " LEFT JOIN {$joinTable}" . ($joinTable !== $joinAlias ? " {$joinAlias}" : '') .
787: " ON {$table}.{$tableColumn} = {$joinAlias}.{$joinColumn}" .
788: (isset($leftJoinConditions[$joinAlias]) ? " {$leftJoinConditions[$joinAlias]}" : '');
789: }
790: return $return;
791: }
792:
793:
794: 795: 796:
797: protected function buildJoinConditions()
798: {
799: $conditions = [];
800: foreach ($this->joinCondition as $tableChain => $joinConditions) {
801: $conditions[$tableChain] = 'AND (' . implode(') AND (', $joinConditions) . ')';
802: }
803: return $conditions;
804: }
805:
806:
807: 808: 809:
810: protected function buildConditions()
811: {
812: return $this->where ? ' WHERE (' . implode(') AND (', $this->where) . ')' : '';
813: }
814:
815:
816: 817: 818:
819: protected function buildQueryEnd()
820: {
821: $return = '';
822: if ($this->group) {
823: $return .= ' GROUP BY '. $this->group;
824: }
825: if ($this->having) {
826: $return .= ' HAVING '. $this->having;
827: }
828: if ($this->order) {
829: $return .= ' ORDER BY ' . implode(', ', $this->order);
830: }
831: return $return;
832: }
833:
834:
835: 836: 837:
838: protected function tryDelimite($s)
839: {
840: return preg_replace_callback('#(?<=[^\w`"\[?]|^)[a-z_][a-z0-9_]*(?=[^\w`"(\]]|\z)#i', function ($m) {
841: return strtoupper($m[0]) === $m[0] ? $m[0] : $this->driver->delimite($m[0]);
842: }, $s);
843: }
844:
845:
846: 847: 848:
849: protected function addConditionComposition(array $columns, array $parameters, array &$conditions, array &$conditionsParameters)
850: {
851: if ($this->driver->isSupported(ISupplementalDriver::SUPPORT_MULTI_COLUMN_AS_OR_COND)) {
852: $conditionFragment = '(' . implode(' = ? AND ', $columns) . ' = ?) OR ';
853: $condition = substr(str_repeat($conditionFragment, count($parameters)), 0, -4);
854: return $this->addCondition($condition, [Nette\Utils\Arrays::flatten($parameters)], $conditions, $conditionsParameters);
855: } else {
856: return $this->addCondition('(' . implode(', ', $columns) . ') IN', [$parameters], $conditions, $conditionsParameters);
857: }
858: }
859:
860:
861: 862: 863:
864: private function getConditionHash($condition, array $parameters)
865: {
866: foreach ($parameters as $key => &$parameter) {
867: if ($parameter instanceof Selection) {
868: $parameter = $this->getConditionHash($parameter->getSql(), $parameter->getSqlBuilder()->getParameters());
869: } elseif ($parameter instanceof SqlLiteral) {
870: $parameter = $this->getConditionHash($parameter->__toString(), $parameter->getParameters());
871: } elseif (is_object($parameter) && method_exists($parameter, '__toString')) {
872: $parameter = $parameter->__toString();
873: } elseif (is_array($parameter) || $parameter instanceof \ArrayAccess) {
874: $parameter = $this->getConditionHash($key, $parameter);
875: }
876: }
877: return md5($condition . json_encode($parameters));
878: }
879:
880:
881: 882: 883:
884: private function getCachedTableList()
885: {
886: if (!$this->cacheTableList) {
887: $this->cacheTableList = array_flip(array_map(function ($pair) {
888: return isset($pair['fullName']) ? $pair['fullName'] : $pair['name'];
889: }, $this->structure->getTables()));
890: }
891:
892: return $this->cacheTableList;
893: }
894:
895: }
896: