Namespaces

  • Nette
    • Application
      • Diagnostics
      • Responses
      • Routers
      • UI
    • Caching
      • Storages
    • ComponentModel
    • Config
      • Adapters
      • Extensions
    • Database
      • Diagnostics
      • Drivers
      • Reflection
      • Table
    • DI
      • Diagnostics
    • Diagnostics
    • Forms
      • Controls
      • Rendering
    • Http
    • Iterators
    • Latte
      • Macros
    • Loaders
    • Localization
    • Mail
    • Reflection
    • Security
      • Diagnostics
    • Templating
    • Utils
      • PhpGenerator
  • NetteModule
  • None
  • PHP

Classes

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