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