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