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