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