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