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