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