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