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