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