1: <?php
2:
3: 4: 5: 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: 20: 21: 22: 23: 24:
25: class SqlBuilder extends Nette\Object
26: {
27:
28:
29: protected $tableName;
30:
31:
32: protected $conventions;
33:
34:
35: protected $delimitedTable;
36:
37:
38: protected $select = array();
39:
40:
41: protected $where = array();
42:
43:
44: protected $conditions = array();
45:
46:
47: protected $parameters = array(
48: 'select' => array(),
49: 'where' => array(),
50: 'group' => array(),
51: 'having' => array(),
52: 'order' => array(),
53: );
54:
55:
56: protected $order = array();
57:
58:
59: protected $limit = NULL;
60:
61:
62: protected $offset = NULL;
63:
64:
65: protected $group = '';
66:
67:
68: protected $having = '';
69:
70:
71: private $driver;
72:
73:
74: private $structure;
75:
76:
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: 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: 126: 127: 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:
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:
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;
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:
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:
453:
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:
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: