Namespaces

  • Nette
    • Application
      • Diagnostics
      • Responses
      • Routers
      • UI
    • Caching
      • Storages
    • ComponentModel
    • Config
    • Database
      • Diagnostics
      • Drivers
      • Reflection
      • Table
    • DI
    • Diagnostics
    • Forms
      • Controls
      • Rendering
    • Http
    • Iterators
    • Latte
      • Macros
    • Loaders
    • Localization
    • Mail
    • Reflection
    • Security
    • Templating
    • Utils
  • NetteModule
  • None
  • PHP

Classes

  • ActiveRow
  • GroupedSelection
  • Selection
  • 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, 2011 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:  * Filtered table representation.
 21:  * Selector is based on the great library NotORM http://www.notorm.com written by Jakub Vrana.
 22:  *
 23:  * @author     Jakub Vrana
 24:  */
 25: class Selection extends Nette\Object implements \Iterator, \ArrayAccess, \Countable
 26: {
 27:     /** @var Nette\Database\Connection */
 28:     public $connection;
 29: 
 30:     /** @var string table name */
 31:     public $name;
 32: 
 33:     /** @var string primary key field name */
 34:     public $primary;
 35: 
 36:     /** @var array of [primary key => TableRow] readed from database */
 37:     protected $rows;
 38: 
 39:     /** @var array of [primary key => TableRow] modifiable */
 40:     protected $data;
 41: 
 42:     /** @var array of column to select */
 43:     protected $select = array();
 44: 
 45:     /** @var array of where conditions */
 46:     protected $where = array();
 47: 
 48:     /** @var array of where conditions for caching */
 49:     protected $conditions = array();
 50: 
 51:     /** @var array of parameters passed to where conditions */
 52:     protected $parameters = array();
 53: 
 54:     /** @var array or columns to order by */
 55:     protected $order = array();
 56: 
 57:     /** @var int number of rows to fetch */
 58:     protected $limit = NULL;
 59: 
 60:     /** @var int first row to fetch */
 61:     protected $offset = NULL;
 62: 
 63:     /** @var string columns to grouping */
 64:     protected $group = '';
 65: 
 66:     /** @var string grouping condition */
 67:     protected $having = '';
 68: 
 69:     /** @var array of referenced TableSelection */
 70:     protected $referenced = array();
 71: 
 72:     /** @var array of [sql => [column => [key => TableRow]]] used by GroupedTableSelection */
 73:     protected $referencing = array();
 74: 
 75:     /** @var array of [sql => [key => TableRow]] used by GroupedTableSelection */
 76:     protected $aggregation = array();
 77: 
 78:     /** @var array of touched columns */
 79:     protected $accessed;
 80: 
 81:     /** @var array of earlier touched columns */
 82:     protected $prevAccessed;
 83: 
 84:     /** @var array of primary key values */
 85:     protected $keys = array();
 86: 
 87:     /** @var string */
 88:     protected $delimitedName;
 89: 
 90:     /** @var string */
 91:     protected $delimitedPrimary;
 92: 
 93: 
 94: 
 95:     /**
 96:      * @param  string
 97:      * @param
 98:      */
 99:     public function __construct($table, Nette\Database\Connection $connection)
100:     {
101:         $this->name = $table;
102:         $this->connection = $connection;
103:         $this->primary = $this->getPrimary($table);
104:         $this->delimitedName = $connection->getSupplementalDriver()->delimite($this->name);
105:         $this->delimitedPrimary = $connection->getSupplementalDriver()->delimite($this->primary);
106:     }
107: 
108: 
109: 
110:     /**
111:      * Saves data to cache and empty result.
112:      */
113:     public function __destruct()
114:     {
115:         $cache = $this->connection->getCache();
116:         if ($cache && !$this->select && $this->rows !== NULL) {
117:             $accessed = $this->accessed;
118:             if (is_array($accessed)) {
119:                 $accessed = array_filter($accessed);
120:             }
121:             $cache->save(array(__CLASS__, $this->name, $this->conditions), $accessed);
122:         }
123:         $this->rows = NULL;
124:         $this->data = NULL;
125:     }
126: 
127: 
128: 
129:     /**
130:      * Returns row specified by primary key.
131:      * @param  mixed
132:      * @return ActiveRow or NULL if there is no such row
133:      */
134:     public function get($key)
135:     {
136:         // can also use array_pop($this->where) instead of clone to save memory
137:         $clone = clone $this;
138:         $clone->where($this->delimitedPrimary, $key);
139:         return $clone->fetch();
140:     }
141: 
142: 
143: 
144:     /**
145:      * Adds select clause, more calls appends to the end.
146:      * @param  string for example "column, MD5(column) AS column_md5"
147:      * @return Selection provides a fluent interface
148:      */
149:     public function select($columns)
150:     {
151:         $this->__destruct();
152:         $this->select[] = $this->tryDelimite($columns);
153:         return $this;
154:     }
155: 
156: 
157: 
158:     /**
159:      * Selects by primary key.
160:      * @param  mixed
161:      * @return Selection provides a fluent interface
162:      */
163:     public function find($key)
164:     {
165:         return $this->where($this->delimitedPrimary, $key);
166:     }
167: 
168: 
169: 
170:     /**
171:      * Adds where condition, more calls appends with AND.
172:      * @param  string condition possibly containing ?
173:      * @param  mixed
174:      * @param  mixed ...
175:      * @return Selection provides a fluent interface
176:      */
177:     public function where($condition, $parameters = array())
178:     {
179:         if (is_array($condition)) { // where(array('column1' => 1, 'column2 > ?' => 2))
180:             foreach ($condition as $key => $val) {
181:                 $this->where($key, $val);
182:             }
183:             return $this;
184:         }
185: 
186:         $this->__destruct();
187: 
188:         $this->conditions[] = $condition;
189:         $condition = $this->tryDelimite($condition);
190: 
191:         $args = func_num_args();
192:         if ($args !== 2 || strpbrk($condition, '?:')) { // where('column < ? OR column > ?', array(1, 2))
193:             if ($args !== 2 || !is_array($parameters)) { // where('column < ? OR column > ?', 1, 2)
194:                 $parameters = func_get_args();
195:                 array_shift($parameters);
196:             }
197:             $this->parameters = array_merge($this->parameters, $parameters);
198: 
199:         } elseif ($parameters === NULL) { // where('column', NULL)
200:             $condition .= ' IS NULL';
201: 
202:         } elseif ($parameters instanceof Selection) { // where('column', $db->$table())
203:             $clone = clone $parameters;
204:             if (!$clone->select) {
205:                 $clone->select = array($this->getPrimary($clone->name));
206:             }
207:             if ($this->connection->getAttribute(PDO::ATTR_DRIVER_NAME) !== 'mysql') {
208:                 $condition .= " IN ($clone)";
209:             } else {
210:                 $in = array();
211:                 foreach ($clone as $row) {
212:                     $this->parameters[] = array_values(iterator_to_array($row));
213:                     $in[] = (count($row) === 1 ? '?' : '(?)');
214:                 }
215:                 $condition .= ' IN (' . ($in ? implode(', ', $in) : 'NULL') . ')';
216:             }
217: 
218:         } elseif (!is_array($parameters)) { // where('column', 'x')
219:             $condition .= ' = ?';
220:             $this->parameters[] = $parameters;
221: 
222:         } else { // where('column', array(1, 2))
223:             if ($parameters) {
224:                 $condition .= " IN (?)";
225:                 $this->parameters[] = $parameters;
226:             } else {
227:                 $condition .= " IN (NULL)";
228:             }
229:         }
230: 
231:         $this->where[] = $condition;
232:         return $this;
233:     }
234: 
235: 
236: 
237:     /**
238:      * Adds order clause, more calls appends to the end.
239:      * @param  string for example 'column1, column2 DESC'
240:      * @return Selection provides a fluent interface
241:      */
242:     public function order($columns)
243:     {
244:         $this->rows = NULL;
245:         $this->order[] = $this->tryDelimite($columns);
246:         return $this;
247:     }
248: 
249: 
250: 
251:     /**
252:      * Sets limit clause, more calls rewrite old values.
253:      * @param  int
254:      * @param  int
255:      * @return Selection provides a fluent interface
256:      */
257:     public function limit($limit, $offset = NULL)
258:     {
259:         $this->rows = NULL;
260:         $this->limit = $limit;
261:         $this->offset = $offset;
262:         return $this;
263:     }
264: 
265: 
266: 
267:     /**
268:      * Sets offset using page number, more calls rewrite old values.
269:      * @param  int
270:      * @param  int
271:      * @return Selection provides a fluent interface
272:      */
273:     public function page($page, $itemsPerPage)
274:     {
275:         $this->rows = NULL;
276:         $this->limit = $itemsPerPage;
277:         $this->offset = ($page - 1) * $itemsPerPage;
278:         return $this;
279:     }
280: 
281: 
282: 
283:     /**
284:      * Sets group clause, more calls rewrite old values.
285:      * @param  string
286:      * @param  string
287:      * @return Selection provides a fluent interface
288:      */
289:     public function group($columns, $having = '')
290:     {
291:         $this->__destruct();
292:         $this->group = $columns;
293:         $this->having = $having;
294:         return $this;
295:     }
296: 
297: 
298: 
299:     /**
300:      * Executes aggregation function.
301:      * @param  string
302:      * @return string
303:      */
304:     public function aggregation($function)
305:     {
306:         $join = $this->createJoins(implode(',', $this->conditions), TRUE) + $this->createJoins($function);
307:         $query = "SELECT $function FROM $this->delimitedName" . implode($join);
308:         if ($this->where) {
309:             $query .= ' WHERE (' . implode(') AND (', $this->where) . ')';
310:         }
311:         foreach ($this->query($query)->fetch() as $val) {
312:             return $val;
313:         }
314:     }
315: 
316: 
317: 
318:     /**
319:      * Counts number of rows.
320:      * @param  string
321:      * @return int
322:      */
323:     public function count($column = '')
324:     {
325:         if (!$column) {
326:             $this->execute();
327:             return count($this->data);
328:         }
329:         return $this->aggregation("COUNT({$this->tryDelimite($column)})");
330:     }
331: 
332: 
333: 
334:     /**
335:      * Returns minimum value from a column.
336:      * @param  string
337:      * @return int
338:      */
339:     public function min($column)
340:     {
341:         return $this->aggregation("MIN({$this->tryDelimite($column)})");
342:     }
343: 
344: 
345: 
346:     /**
347:      * Returns maximum value from a column.
348:      * @param  string
349:      * @return int
350:      */
351:     public function max($column)
352:     {
353:         return $this->aggregation("MAX({$this->tryDelimite($column)})");
354:     }
355: 
356: 
357: 
358:     /**
359:      * Returns sum of values in a column.
360:      * @param  string
361:      * @return int
362:      */
363:     public function sum($column)
364:     {
365:         return $this->aggregation("SUM({$this->tryDelimite($column)})");
366:     }
367: 
368: 
369: 
370:     /**
371:      * Returns SQL query.
372:      * @return string
373:      */
374:     public function getSql()
375:     {
376:         $join = $this->createJoins(implode(',', $this->conditions), TRUE)
377:             + $this->createJoins(implode(',', $this->select) . ",$this->group,$this->having," . implode(',', $this->order));
378: 
379:         $cache = $this->connection->getCache();
380:         if ($this->rows === NULL && $cache && !is_string($this->prevAccessed)) {
381:             $this->accessed = $this->prevAccessed = $cache->load(array(__CLASS__, $this->name, $this->conditions));
382:         }
383: 
384:         $prefix = $join ? "$this->delimitedName." : '';
385:         if ($this->select) {
386:             $cols = implode(', ', $this->select);
387: 
388:         } elseif ($this->prevAccessed) {
389:             $cols = $prefix . implode(', ' . $prefix, array_map(array($this->connection->getSupplementalDriver(), 'delimite'), array_keys($this->prevAccessed)));
390: 
391:         } else {
392:             $cols = $prefix . '*';
393:         }
394: 
395:         return "SELECT{$this->topString()} $cols FROM $this->delimitedName" . implode($join) . $this->whereString();
396:     }
397: 
398: 
399: 
400:     protected function createJoins($val, $inner = FALSE)
401:     {
402:         $supplementalDriver = $this->connection->getSupplementalDriver();
403:         $joins = array();
404:         preg_match_all('~\\b([a-z][\\w.]*)\\.([a-z]\\w*)(\\s+IS\\b|\\s*<=>)?~i', $val, $matches, PREG_SET_ORDER);
405:         foreach ($matches as $match) {
406:             if ($match[1] !== $this->name) { // case-sensitive
407:                 foreach (explode('.', $match[1]) as $name) {
408:                     $table = $this->connection->databaseReflection->getReferencedTable($name, $this->name);
409:                     $column = $this->connection->databaseReflection->getReferencedColumn($name, $this->name);
410:                     $primary = $this->getPrimary($table);
411:                     $joins[$name] = ' ' . (!isset($joins[$name]) && $inner && !isset($match[3]) ? 'INNER' : 'LEFT')
412:                         . ' JOIN ' . $supplementalDriver->delimite($table)
413:                         . ($table !== $name ? ' AS ' . $supplementalDriver->delimite($name) : '')
414:                         . " ON $this->delimitedName." . $supplementalDriver->delimite($column)
415:                         . ' = ' . $supplementalDriver->delimite($name) . '.' . $supplementalDriver->delimite($primary);
416:                 }
417:             }
418:         }
419:         return $joins;
420:     }
421: 
422: 
423: 
424:     /**
425:      * Executes built query.
426:      * @return NULL
427:      */
428:     protected function execute()
429:     {
430:         if ($this->rows !== NULL) {
431:             return;
432:         }
433: 
434:         try {
435:             $result = $this->query($this->getSql());
436: 
437:         } catch (\PDOException $exception) {
438:             if (!$this->select && $this->prevAccessed) {
439:                 $this->prevAccessed = '';
440:                 $this->accessed = array();
441:                 $result = $this->query($this->getSql());
442:             } else {
443:                 throw $exception;
444:             }
445:         }
446: 
447:         $this->rows = array();
448:         $result->setFetchMode(PDO::FETCH_ASSOC);
449:         foreach ($result as $key => $row) {
450:             $row = $result->normalizeRow($row);
451:             $this->rows[isset($row[$this->primary]) ? $row[$this->primary] : $key] = new ActiveRow($row, $this);
452:         }
453:         $this->data = $this->rows;
454: 
455:         if (isset($row[$this->primary]) && !is_string($this->accessed)) {
456:             $this->accessed[$this->primary] = TRUE;
457:         }
458:     }
459: 
460: 
461: 
462:     protected function whereString()
463:     {
464:         $return = '';
465:         $driver = $this->connection->getAttribute(PDO::ATTR_DRIVER_NAME);
466:         $where = $this->where;
467:         if ($this->limit !== NULL && $driver === 'oci') {
468:             $where[] = ($this->offset ? "rownum > $this->offset AND " : '') . 'rownum <= ' . ($this->limit + $this->offset);
469:         }
470:         if ($where) {
471:             $return .= ' WHERE (' . implode(') AND (', $where) . ')';
472:         }
473:         if ($this->group) {
474:             $return .= ' GROUP BY '. $this->tryDelimite($this->group);
475:         }
476:         if ($this->having) {
477:             $return .= ' HAVING '. $this->tryDelimite($this->having);
478:         }
479:         if ($this->order) {
480:             $return .= ' ORDER BY ' . implode(', ', $this->order);
481:         }
482:         if ($this->limit !== NULL && $driver !== 'oci' && $driver !== 'dblib') {
483:             $return .= " LIMIT $this->limit";
484:             if ($this->offset !== NULL) {
485:                 $return .= " OFFSET $this->offset";
486:             }
487:         }
488:         return $return;
489:     }
490: 
491: 
492: 
493:     protected function topString()
494:     {
495:         if ($this->limit !== NULL && $this->connection->getAttribute(PDO::ATTR_DRIVER_NAME) === 'dblib') {
496:             return " TOP ($this->limit)"; //! offset is not supported
497:         }
498:         return '';
499:     }
500: 
501: 
502: 
503:     protected function tryDelimite($s)
504:     {
505:         return preg_match('#^[a-z_][a-z0-9_.]*$#i', $s) // is identifier?
506:             ? implode('.', array_map(array($this->connection->getSupplementalDriver(), 'delimite'), explode('.', $s)))
507:             : $s;
508:     }
509: 
510: 
511: 
512:     protected function query($query)
513:     {
514:         return $this->connection->queryArgs($query, $this->parameters);
515:     }
516: 
517: 
518: 
519:     public function access($key, $delete = FALSE)
520:     {
521:         if ($delete) {
522:             if (is_array($this->accessed)) {
523:                 $this->accessed[$key] = FALSE;
524:             }
525:             return FALSE;
526:         }
527: 
528:         if ($key === NULL) {
529:             $this->accessed = '';
530: 
531:         } elseif (!is_string($this->accessed)) {
532:             $this->accessed[$key] = TRUE;
533:         }
534: 
535:         if (!$this->select && $this->prevAccessed && ($key === NULL || !isset($this->prevAccessed[$key]))) {
536:             $this->prevAccessed = '';
537:             $this->rows = NULL;
538:             return TRUE;
539:         }
540:         return FALSE;
541:     }
542: 
543: 
544: 
545:     /********************* manipulation ****************d*g**/
546: 
547: 
548: 
549:     /**
550:      * Inserts row in a table.
551:      * @param  mixed array($column => $value)|Traversable for single row insert or TableSelection|string for INSERT ... SELECT
552:      * @return ActiveRow or FALSE in case of an error or number of affected rows for INSERT ... SELECT
553:      */
554:     public function insert($data)
555:     {
556:         if ($data instanceof Selection) {
557:             $data = $data->getSql();
558: 
559:         } elseif ($data instanceof \Traversable) {
560:             $data = iterator_to_array($data);
561:         }
562: 
563:         $return = $this->connection->query("INSERT INTO $this->delimitedName", $data);
564: 
565:         $this->rows = NULL;
566:         if (!is_array($data)) {
567:             return $return->rowCount();
568:         }
569: 
570:         if (!isset($data[$this->primary]) && ($id = $this->connection->lastInsertId())) {
571:             $data[$this->primary] = $id;
572:         }
573:         return new ActiveRow($data, $this);
574:     }
575: 
576: 
577: 
578:     /**
579:      * Updates all rows in result set.
580:      * @param  array ($column => $value)
581:      * @return int number of affected rows or FALSE in case of an error
582:      */
583:     public function update($data)
584:     {
585:         if ($data instanceof \Traversable) {
586:             $data = iterator_to_array($data);
587: 
588:         } elseif (!is_array($data)) {
589:             throw new Nette\InvalidArgumentException;
590:         }
591: 
592:         if (!$data) {
593:             return 0;
594:         }
595:         // joins in UPDATE are supported only in MySQL
596:         return $this->connection->queryArgs(
597:             'UPDATE' . $this->topString() . " $this->delimitedName SET ?" . $this->whereString(),
598:             array_merge(array($data), $this->parameters)
599:         )->rowCount();
600:     }
601: 
602: 
603: 
604:     /**
605:      * Deletes all rows in result set.
606:      * @return int number of affected rows or FALSE in case of an error
607:      */
608:     public function delete()
609:     {
610:         return $this->query(
611:             'DELETE' . $this->topString() . " FROM $this->delimitedName" . $this->whereString()
612:         )->rowCount();
613:     }
614: 
615: 
616: 
617:     /********************* references ****************d*g**/
618: 
619: 
620: 
621:     /**
622:      * Returns referenced row.
623:      * @param  string
624:      * @return ActiveRow or NULL if the row does not exist
625:      */
626:     public function getReferencedTable($name, & $column = NULL)
627:     {
628:         $column = $this->connection->databaseReflection->getReferencedColumn($name, $this->name);
629:         $referenced = & $this->referenced[$name];
630:         if ($referenced === NULL) {
631:             $keys = array();
632:             foreach ($this->rows as $row) {
633:                 if ($row[$column] !== NULL) {
634:                     $keys[$row[$column]] = NULL;
635:                 }
636:             }
637:             if ($keys) {
638:                 $table = $this->connection->databaseReflection->getReferencedTable($name, $this->name);
639:                 $referenced = new Selection($table, $this->connection);
640:                 $referenced->where($table . '.' . $this->getPrimary($table), array_keys($keys));
641:             } else {
642:                 $referenced = array();
643:             }
644:         }
645:         return $referenced;
646:     }
647: 
648: 
649: 
650:     /**
651:      * Returns referencing rows.
652:      * @param  string table name
653:      * @return GroupedSelection
654:      */
655:     public function getReferencingTable($table)
656:     {
657:         $column = $this->connection->databaseReflection->getReferencingColumn($table, $this->name);
658:         $referencing = new GroupedSelection($table, $this, $column);
659:         $referencing->where("$table.$column", array_keys((array) $this->rows)); // (array) - is NULL after insert
660:         return $referencing;
661:     }
662: 
663: 
664: 
665:     private function getPrimary($table)
666:     {
667:         return $this->connection->databaseReflection->getPrimary($table);
668:     }
669: 
670: 
671: 
672:     /********************* interface Iterator ****************d*g**/
673: 
674: 
675: 
676:     public function rewind()
677:     {
678:         $this->execute();
679:         $this->keys = array_keys($this->data);
680:         reset($this->keys);
681:     }
682: 
683: 
684: 
685:     /** @return ActiveRow */
686:     public function current()
687:     {
688:         return $this->data[current($this->keys)];
689:     }
690: 
691: 
692: 
693:     /**
694:      * @return string row ID
695:      */
696:     public function key()
697:     {
698:         return current($this->keys);
699:     }
700: 
701: 
702: 
703:     public function next()
704:     {
705:         next($this->keys);
706:     }
707: 
708: 
709: 
710:     public function valid()
711:     {
712:         return current($this->keys) !== FALSE;
713:     }
714: 
715: 
716: 
717:     /********************* interface ArrayAccess ****************d*g**/
718: 
719: 
720: 
721:     /**
722:      * Mimic row.
723:      * @param  string row ID
724:      * @param  ActiveRow
725:      * @return NULL
726:      */
727:     public function offsetSet($key, $value)
728:     {
729:         $this->execute();
730:         $this->data[$key] = $value;
731:     }
732: 
733: 
734: 
735:     /**
736:      * Returns specified row.
737:      * @param  string row ID
738:      * @return ActiveRow or NULL if there is no such row
739:      */
740:     public function offsetGet($key)
741:     {
742:         $this->execute();
743:         return $this->data[$key];
744:     }
745: 
746: 
747: 
748:     /**
749:      * Tests if row exists.
750:      * @param  string row ID
751:      * @return bool
752:      */
753:     public function offsetExists($key)
754:     {
755:         $this->execute();
756:         return isset($this->data[$key]);
757:     }
758: 
759: 
760: 
761:     /**
762:      * Removes row from result set.
763:      * @param  string row ID
764:      * @return NULL
765:      */
766:     public function offsetUnset($key)
767:     {
768:         $this->execute();
769:         unset($this->data[$key]);
770:     }
771: 
772: 
773: 
774:     /**
775:      * Returns next row of result.
776:      * @return ActiveRow or FALSE if there is no row
777:      */
778:     public function fetch()
779:     {
780:         $this->execute();
781:         $return = current($this->data);
782:         next($this->data);
783:         return $return;
784:     }
785: 
786: 
787: 
788:     /**
789:      * Returns all rows as associative array.
790:      * @param  string
791:      * @param  string column name used for an array value or an empty string for the whole row
792:      * @return array
793:      */
794:     public function fetchPairs($key, $value = '')
795:     {
796:         $return = array();
797:         // no $clone->select = array($key, $value) to allow efficient caching with repetitive calls with different parameters
798:         foreach ($this as $row) {
799:             $return[$row[$key]] = ($value !== '' ? $row[$value] : $row);
800:         }
801:         return $return;
802:     }
803: 
804: }
805: 
Nette Framework 2.0beta1 API API documentation generated by ApiGen 2.3.0