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