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