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