1: <?php
2:
3: 4: 5: 6: 7: 8: 9: 10:
11:
12: namespace Nette\Database\Table;
13:
14: use Nette,
15: Nette\Database\Connection,
16: Nette\Database\IReflection,
17: Nette\Database\ISupplementalDriver,
18: Nette\Database\SqlLiteral;
19:
20:
21: 22: 23: 24: 25: 26: 27:
28: class SqlBuilder extends Nette\Object
29: {
30:
31: private $driver;
32:
33:
34: private $driverName;
35:
36:
37: protected $tableName;
38:
39:
40: protected $databaseReflection;
41:
42:
43: protected $delimitedTable;
44:
45:
46: protected $select = array();
47:
48:
49: protected $where = array();
50:
51:
52: protected $conditions = array();
53:
54:
55: protected $parameters = array();
56:
57:
58: protected $order = array();
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: public function __construct($tableName, Connection $connection, IReflection $reflection)
74: {
75: $this->tableName = $tableName;
76: $this->databaseReflection = $reflection;
77: $this->driver = $connection->getSupplementalDriver();
78: $this->driverName = $connection->getAttribute(\PDO::ATTR_DRIVER_NAME);
79: $this->delimitedTable = $this->tryDelimite($tableName);
80: }
81:
82:
83: public function buildInsertQuery()
84: {
85: return "INSERT INTO {$this->delimitedTable}";
86: }
87:
88:
89: public function buildUpdateQuery()
90: {
91: return "UPDATE{$this->buildTopClause()} {$this->delimitedTable} SET ?" . $this->buildConditions();
92: }
93:
94:
95: public function buildDeleteQuery()
96: {
97: return "DELETE{$this->buildTopClause()} FROM {$this->delimitedTable}" . $this->buildConditions();
98: }
99:
100:
101: public function importConditions(SqlBuilder $builder)
102: {
103: $this->where = $builder->where;
104: $this->parameters = $builder->parameters;
105: $this->conditions = $builder->conditions;
106: }
107:
108:
109:
110:
111:
112: public function addSelect($columns)
113: {
114: if (is_array($columns)) {
115: throw new Nette\InvalidArgumentException('Select column must be a string.');
116: }
117: $this->select[] = $columns;
118: }
119:
120:
121: public function getSelect()
122: {
123: return $this->select;
124: }
125:
126:
127: public function addWhere($condition, $parameters = array())
128: {
129: $args = func_get_args();
130: $hash = md5(json_encode($args));
131: if (isset($this->conditions[$hash])) {
132: return FALSE;
133: }
134:
135: $this->conditions[$hash] = $condition;
136: $condition = $this->removeExtraTables($condition);
137: $condition = $this->tryDelimite($condition);
138:
139: $placeholderCount = substr_count($condition, '?');
140: if ($placeholderCount > 1 && count($args) === 2 && is_array($parameters)) {
141: $args = $parameters;
142: } else {
143: array_shift($args);
144: }
145:
146: $condition = trim($condition);
147: if ($placeholderCount === 0 && count($args) === 1) {
148: $condition .= ' ?';
149: } elseif ($placeholderCount !== count($args)) {
150: throw new Nette\InvalidArgumentException('Argument count does not match placeholder count.');
151: }
152:
153: $replace = NULL;
154: $placeholderNum = 0;
155: foreach ($args as $arg) {
156: preg_match('#(?:.*?\?.*?){' . $placeholderNum . '}(((?:&|\||^|~|\+|-|\*|/|%|\(|,|<|>|=|(?<=\W|^)(?:REGEXP|ALL|AND|ANY|BETWEEN|EXISTS|IN|R?LIKE|OR|NOT|SOME))\s*)?(?:\(\?\)|\?))#s', $condition, $match, PREG_OFFSET_CAPTURE);
157: $hasOperator = ($match[1][0] === '?' && $match[1][1] === 0) ? TRUE : !empty($match[2][0]);
158:
159: if ($arg === NULL) {
160: if ($hasOperator) {
161: throw new Nette\InvalidArgumentException('Column operator does not accept NULL argument.');
162: }
163: $replace = 'IS NULL';
164: } elseif ($arg instanceof Selection) {
165: $clone = clone $arg;
166: if (!$clone->getSqlBuilder()->select) {
167: try {
168: $clone->select($clone->getPrimary());
169: } catch (\LogicException $e) {
170: throw new Nette\InvalidArgumentException('Selection argument must have defined a select column.', 0, $e);
171: }
172: }
173:
174: if ($this->driverName !== 'mysql') {
175: $replace = 'IN (' . $clone->getSql() . ')';
176: $this->parameters = array_merge($this->parameters, $clone->getSqlBuilder()->getParameters());
177: } else {
178: $parameter = array();
179: foreach ($clone as $row) {
180: $parameter[] = array_values(iterator_to_array($row));
181: }
182:
183: if (!$parameter) {
184: $replace = 'IN (NULL)';
185: } else {
186: $replace = 'IN (?)';
187: $this->parameters[] = $parameter;
188: }
189: }
190: } elseif ($arg instanceof SqlLiteral) {
191: $this->parameters[] = $arg;
192: } elseif (is_array($arg)) {
193: if ($hasOperator) {
194: if (trim($match[2][0]) !== 'IN') {
195: throw new Nette\InvalidArgumentException('Column operator does not accept array argument.');
196: }
197: } else {
198: $match[2][0] = 'IN ';
199: }
200:
201: if (!$arg) {
202: $replace = $match[2][0] . '(NULL)';
203: } else {
204: $replace = $match[2][0] . '(?)';
205: $this->parameters[] = array_values($arg);
206: }
207: } else {
208: if (!$hasOperator) {
209: $replace = '= ?';
210: }
211: $this->parameters[] = $arg;
212: }
213:
214: if ($replace) {
215: $condition = substr_replace($condition, $replace, $match[1][1], strlen($match[1][0]));
216: $replace = NULL;
217: }
218:
219: if ($arg !== NULL) {
220: $placeholderNum++;
221: }
222: }
223:
224: $this->where[] = $condition;
225: return TRUE;
226: }
227:
228:
229: public function getConditions()
230: {
231: return array_values($this->conditions);
232: }
233:
234:
235: public function addOrder($columns)
236: {
237: $this->order[] = $columns;
238: }
239:
240:
241: public function getOrder()
242: {
243: return $this->order;
244: }
245:
246:
247: public function setLimit($limit, $offset)
248: {
249: $this->limit = $limit;
250: $this->offset = $offset;
251: }
252:
253:
254: public function getLimit()
255: {
256: return $this->limit;
257: }
258:
259:
260: public function getOffset()
261: {
262: return $this->offset;
263: }
264:
265:
266: public function setGroup($columns, $having)
267: {
268: $this->group = $columns;
269: $this->having = $having;
270: }
271:
272:
273: public function getGroup()
274: {
275: return $this->group;
276: }
277:
278:
279: public function getHaving()
280: {
281: return $this->having;
282: }
283:
284:
285:
286:
287:
288: 289: 290: 291: 292:
293: public function buildSelectQuery($columns = NULL)
294: {
295: $join = $this->buildJoins(implode(',', $this->conditions), TRUE);
296: $join += $this->buildJoins(implode(',', $this->select) . ",{$this->group},{$this->having}," . implode(',', $this->order));
297:
298: $prefix = $join ? "{$this->delimitedTable}." : '';
299: if ($this->select) {
300: $cols = $this->tryDelimite($this->removeExtraTables(implode(', ', $this->select)));
301:
302: } elseif ($columns) {
303: $cols = array_map(array($this->driver, 'delimite'), $columns);
304: $cols = $prefix . implode(', ' . $prefix, $cols);
305:
306: } elseif ($this->group && !$this->driver->isSupported(ISupplementalDriver::SUPPORT_SELECT_UNGROUPED_COLUMNS)) {
307: $cols = $this->tryDelimite($this->removeExtraTables($this->group));
308:
309: } else {
310: $cols = $prefix . '*';
311:
312: }
313:
314: return "SELECT{$this->buildTopClause()} {$cols} FROM {$this->delimitedTable}" . implode($join) . $this->buildConditions();
315: }
316:
317:
318: public function getParameters()
319: {
320: return $this->parameters;
321: }
322:
323:
324: protected function buildJoins($val, $inner = FALSE)
325: {
326: $joins = array();
327: preg_match_all('~\\b([a-z][\\w.:]*[.:])([a-z]\\w*|\*)(\\s+IS\\b|\\s*<=>)?~i', $val, $matches);
328: foreach ($matches[1] as $names) {
329: $parent = $parentAlias = $this->tableName;
330: if ($names !== "$parent.") {
331: preg_match_all('~\\b([a-z][\\w]*|\*)([.:])~i', $names, $matches, PREG_SET_ORDER);
332: foreach ($matches as $match) {
333: list(, $name, $delimiter) = $match;
334:
335: if ($delimiter === ':') {
336: list($table, $primary) = $this->databaseReflection->getHasManyReference($parent, $name);
337: $column = $this->databaseReflection->getPrimary($parent);
338: } else {
339: list($table, $column) = $this->databaseReflection->getBelongsToReference($parent, $name);
340: $primary = $this->databaseReflection->getPrimary($table);
341: }
342:
343: $joins[$name] = ' '
344: . (!isset($joins[$name]) && $inner && !isset($match[3]) ? 'INNER' : 'LEFT')
345: . ' JOIN ' . $this->driver->delimite($table) . ($table !== $name ? ' AS ' . $this->driver->delimite($name) : '')
346: . ' ON ' . $this->driver->delimite($parentAlias) . '.' . $this->driver->delimite($column)
347: . ' = ' . $this->driver->delimite($name) . '.' . $this->driver->delimite($primary);
348:
349: $parent = $table;
350: $parentAlias = $name;
351: }
352: }
353: }
354: return $joins;
355: }
356:
357:
358: protected function buildConditions()
359: {
360: $return = '';
361: $where = $this->where;
362: if ($this->limit !== NULL && $this->driverName === 'oci') {
363: $where[] = ($this->offset ? "rownum > $this->offset AND " : '') . 'rownum <= ' . ($this->limit + $this->offset);
364: }
365: if ($where) {
366: $return .= ' WHERE (' . implode(') AND (', $where) . ')';
367: }
368: if ($this->group) {
369: $return .= ' GROUP BY '. $this->tryDelimite($this->removeExtraTables($this->group));
370: }
371: if ($this->having) {
372: $return .= ' HAVING '. $this->tryDelimite($this->removeExtraTables($this->having));
373: }
374: if ($this->order) {
375: $return .= ' ORDER BY ' . $this->tryDelimite($this->removeExtraTables(implode(', ', $this->order)));
376: }
377: if ($this->limit !== NULL && $this->driverName !== 'oci' && $this->driverName !== 'dblib') {
378: $return .= " LIMIT $this->limit";
379: if ($this->offset !== NULL) {
380: $return .= " OFFSET $this->offset";
381: }
382: }
383: return $return;
384: }
385:
386:
387: protected function buildTopClause()
388: {
389: if ($this->limit !== NULL && $this->driverName === 'dblib') {
390: return " TOP ($this->limit)";
391: }
392: return '';
393: }
394:
395:
396: protected function tryDelimite($s)
397: {
398: $driver = $this->driver;
399: return preg_replace_callback('#(?<=[^\w`"\[]|^)[a-z_][a-z0-9_]*(?=[^\w`"(\]]|\z)#i', function($m) use ($driver) {
400: return strtoupper($m[0]) === $m[0] ? $m[0] : $driver->delimite($m[0]);
401: }, $s);
402: }
403:
404:
405: protected function ($expression)
406: {
407: return preg_replace('~(?:\\b[a-z_][a-z0-9_.:]*[.:])?([a-z_][a-z0-9_]*)[.:]([a-z_*])~i', '\\1.\\2', $expression);
408: }
409:
410: }
411: