Packages

  • 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

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