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