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