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