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