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