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: public function __construct($tableName, NConnection $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(NSqlBuilder $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|^)(?:ALL|AND|ANY|BETWEEN|EXISTS|IN|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 NTableSelection) {
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 NSqlLiteral) {
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 = $match[2][0] . '?';
205: } else {
206: $replace = '= ?';
207: }
208: $this->parameters[] = $arg;
209: }
210:
211: if ($replace) {
212: $condition = substr_replace($condition, $replace, $match[1][1], strlen($match[1][0]));
213: $replace = NULL;
214: }
215:
216: if ($arg !== NULL) {
217: $placeholderNum++;
218: }
219: }
220:
221: $this->where[] = $condition;
222: return TRUE;
223: }
224:
225:
226: public function getConditions()
227: {
228: return array_values($this->conditions);
229: }
230:
231:
232: public function addOrder($columns)
233: {
234: $this->order[] = $columns;
235: }
236:
237:
238: public function getOrder()
239: {
240: return $this->order;
241: }
242:
243:
244: public function setLimit($limit, $offset)
245: {
246: $this->limit = $limit;
247: $this->offset = $offset;
248: }
249:
250:
251: public function getLimit()
252: {
253: return $this->limit;
254: }
255:
256:
257: public function getOffset()
258: {
259: return $this->offset;
260: }
261:
262:
263: public function setGroup($columns, $having)
264: {
265: $this->group = $columns;
266: $this->having = $having;
267: }
268:
269:
270: public function getGroup()
271: {
272: return $this->group;
273: }
274:
275:
276: public function getHaving()
277: {
278: return $this->having;
279: }
280:
281:
282:
283:
284:
285: 286: 287: 288: 289:
290: public function buildSelectQuery($columns = NULL)
291: {
292: $join = $this->buildJoins(implode(',', $this->conditions), TRUE);
293: $join += $this->buildJoins(implode(',', $this->select) . ",{$this->group},{$this->having}," . implode(',', $this->order));
294:
295: $prefix = $join ? "{$this->delimitedTable}." : '';
296: if ($this->select) {
297: $cols = $this->tryDelimite($this->removeExtraTables(implode(', ', $this->select)));
298:
299: } elseif ($columns) {
300: $cols = array_map(array($this->driver, 'delimite'), $columns);
301: $cols = $prefix . implode(', ' . $prefix, $cols);
302:
303: } elseif ($this->group && !$this->driver->isSupported(ISupplementalDriver::SUPPORT_SELECT_UNGROUPED_COLUMNS)) {
304: $cols = $this->tryDelimite($this->removeExtraTables($this->group));
305:
306: } else {
307: $cols = $prefix . '*';
308:
309: }
310:
311: return "SELECT{$this->buildTopClause()} {$cols} FROM {$this->delimitedTable}" . implode($join) . $this->buildConditions();
312: }
313:
314:
315: public function getParameters()
316: {
317: return $this->parameters;
318: }
319:
320:
321: protected function buildJoins($val, $inner = FALSE)
322: {
323: $joins = array();
324: preg_match_all('~\\b([a-z][\\w.:]*[.:])([a-z]\\w*|\*)(\\s+IS\\b|\\s*<=>)?~i', $val, $matches);
325: foreach ($matches[1] as $names) {
326: $parent = $parentAlias = $this->tableName;
327: if ($names !== "$parent.") {
328: preg_match_all('~\\b([a-z][\\w]*|\*)([.:])~i', $names, $matches, PREG_SET_ORDER);
329: foreach ($matches as $match) {
330: list(, $name, $delimiter) = $match;
331:
332: if ($delimiter === ':') {
333: list($table, $primary) = $this->databaseReflection->getHasManyReference($parent, $name);
334: $column = $this->databaseReflection->getPrimary($parent);
335: } else {
336: list($table, $column) = $this->databaseReflection->getBelongsToReference($parent, $name);
337: $primary = $this->databaseReflection->getPrimary($table);
338: }
339:
340: $joins[$name] = ' '
341: . (!isset($joins[$name]) && $inner && !isset($match[3]) ? 'INNER' : 'LEFT')
342: . ' JOIN ' . $this->driver->delimite($table) . ($table !== $name ? ' AS ' . $this->driver->delimite($name) : '')
343: . ' ON ' . $this->driver->delimite($parentAlias) . '.' . $this->driver->delimite($column)
344: . ' = ' . $this->driver->delimite($name) . '.' . $this->driver->delimite($primary);
345:
346: $parent = $table;
347: $parentAlias = $name;
348: }
349: }
350: }
351: return $joins;
352: }
353:
354:
355: protected function buildConditions()
356: {
357: $return = '';
358: $where = $this->where;
359: if ($this->limit !== NULL && $this->driverName === 'oci') {
360: $where[] = ($this->offset ? "rownum > $this->offset AND " : '') . 'rownum <= ' . ($this->limit + $this->offset);
361: }
362: if ($where) {
363: $return .= ' WHERE (' . implode(') AND (', $where) . ')';
364: }
365: if ($this->group) {
366: $return .= ' GROUP BY '. $this->tryDelimite($this->removeExtraTables($this->group));
367: }
368: if ($this->having) {
369: $return .= ' HAVING '. $this->tryDelimite($this->removeExtraTables($this->having));
370: }
371: if ($this->order) {
372: $return .= ' ORDER BY ' . $this->tryDelimite($this->removeExtraTables(implode(', ', $this->order)));
373: }
374: if ($this->limit !== NULL && $this->driverName !== 'oci' && $this->driverName !== 'dblib') {
375: $return .= " LIMIT $this->limit";
376: if ($this->offset !== NULL) {
377: $return .= " OFFSET $this->offset";
378: }
379: }
380: return $return;
381: }
382:
383:
384: protected function buildTopClause()
385: {
386: if ($this->limit !== NULL && $this->driverName === 'dblib') {
387: return " TOP ($this->limit)";
388: }
389: return '';
390: }
391:
392:
393: protected function tryDelimite($s)
394: {
395: $driver = $this->driver;
396: 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);
397: return strtoupper($m[0]) === $m[0] ? $m[0] : $driver->delimite($m[0]);
398: '), $s);
399: }
400:
401:
402: protected function ($expression)
403: {
404: return preg_replace('~(?:\\b[a-z_][a-z0-9_.:]*[.:])?([a-z_][a-z0-9_]*)[.:]([a-z_*])~i', '\\1.\\2', $expression);
405: }
406:
407: }
408: