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:     PDO;
 17: 
 18: 
 19: 
 20: /**
 21:  * Filtered table representation.
 22:  * Selection is based on the great library NotORM http://www.notorm.com written by Jakub Vrana.
 23:  *
 24:  * @author     Jakub Vrana
 25:  * @author     Jan Skrasek
 26:  *
 27:  * @property-read string $sql
 28:  */
 29: class Selection extends Nette\Object implements \Iterator, \ArrayAccess, \Countable
 30: {
 31:     /** @var Nette\Database\Connection */
 32:     protected $connection;
 33: 
 34:     /** @var Nette\Caching\Cache */
 35:     protected $cache;
 36: 
 37:     /** @var SqlBuilder */
 38:     protected $sqlBuilder;
 39: 
 40:     /** @var string table name */
 41:     protected $name;
 42: 
 43:     /** @var string primary key field name */
 44:     protected $primary;
 45: 
 46:     /** @var string|bool primary column sequence name, FALSE for autodetection */
 47:     protected $primarySequence = FALSE;
 48: 
 49:     /** @var ActiveRow[] data read from database in [primary key => ActiveRow] format */
 50:     protected $rows;
 51: 
 52:     /** @var ActiveRow[] modifiable data in [primary key => ActiveRow] format */
 53:     protected $data;
 54: 
 55:     /** @var bool */
 56:     protected $dataRefreshed = FALSE;
 57: 
 58:     /** @var Selection[] */
 59:     protected $referenced = array();
 60: 
 61:     /** @var array of [sqlQuery-hash => grouped data]; used by GroupedSelection */
 62:     protected $referencing = array();
 63: 
 64:     /** @var GroupedSelection[] cached array of GroupedSelection prototypes */
 65:     protected $referencingPrototype = array();
 66: 
 67:     /** @var array of [conditions => [key => ActiveRow]]; used by GroupedSelection */
 68:     protected $aggregation = array();
 69: 
 70:     /** @var array of touched columns */
 71:     protected $accessedColumns;
 72: 
 73:     /** @var array of earlier touched columns */
 74:     protected $previousAccessedColumns;
 75: 
 76:     /** @var bool should instance observe accessed columns caching */
 77:     protected $observeCache = FALSE;
 78: 
 79:     /** @var bool recheck referencing keys */
 80:     protected $checkReferenced = FALSE;
 81: 
 82:     /** @var array of primary key values */
 83:     protected $keys = array();
 84: 
 85: 
 86: 
 87:     /**
 88:      * Creates filtered table representation.
 89:      * @param  string  database table name
 90:      * @param  Nette\Database\Connection
 91:      */
 92:     public function __construct($table, Nette\Database\Connection $connection)
 93:     {
 94:         $this->name = $table;
 95:         $this->connection = $connection;
 96:         $reflection = $connection->getDatabaseReflection();
 97:         $this->primary = $reflection->getPrimary($table);
 98:         $this->sqlBuilder = new SqlBuilder($table, $connection, $reflection);
 99:         $this->cache = $connection->getCache();
100:     }
101: 
102: 
103: 
104:     public function __destruct()
105:     {
106:         $this->saveCacheState();
107:     }
108: 
109: 
110: 
111:     public function __clone()
112:     {
113:         $this->sqlBuilder = clone $this->sqlBuilder;
114:     }
115: 
116: 
117: 
118:     /**
119:      * @return Nette\Database\Connection
120:      */
121:     public function getConnection()
122:     {
123:         return $this->connection;
124:     }
125: 
126: 
127: 
128:     /**
129:      * @return string
130:      */
131:     public function getName()
132:     {
133:         return $this->name;
134:     }
135: 
136: 
137: 
138:     /**
139:      * @return string|array
140:      */
141:     public function getPrimary()
142:     {
143:         return $this->primary;
144:     }
145: 
146: 
147: 
148:     /**
149:      * @return string
150:      */
151:     public function getPrimarySequence()
152:     {
153:         if ($this->primarySequence === FALSE) {
154:             $this->primarySequence = NULL;
155: 
156:             $driver = $this->connection->getSupplementalDriver();
157:             if ($driver->isSupported(ISupplementalDriver::SUPPORT_SEQUENCE)) {
158:                 foreach ($driver->getColumns($this->name) as $column) {
159:                     if ($column['name'] === $this->primary) {
160:                         $this->primarySequence = $column['vendor']['sequence'];
161:                         break;
162:                     }
163:                 }
164:             }
165:         }
166: 
167:         return $this->primarySequence;
168:     }
169: 
170: 
171: 
172:     /**
173:      * @param  string
174:      * @return Selection provides a fluent interface
175:      */
176:     public function setPrimarySequence($sequence)
177:     {
178:         $this->primarySequence = $sequence;
179:         return $this;
180:     }
181: 
182: 
183: 
184:     /**
185:      * @return string
186:      */
187:     public function getSql()
188:     {
189:         return $this->sqlBuilder->buildSelectQuery($this->getPreviousAccessedColumns());
190:     }
191: 
192: 
193: 
194:     /**
195:      * Loads cache of previous accessed columns and returns it.
196:      * @internal
197:      * @return array|false
198:      */
199:     public function getPreviousAccessedColumns()
200:     {
201:         if ($this->cache && $this->previousAccessedColumns === NULL) {
202:             $this->accessedColumns = $this->previousAccessedColumns = $this->cache->load(array(__CLASS__, $this->name, $this->sqlBuilder->getConditions()));
203:         }
204: 
205:         return array_keys(array_filter((array) $this->previousAccessedColumns));
206:     }
207: 
208: 
209: 
210:     /**
211:      * @internal
212:      * @return SqlBuilder
213:      */
214:     public function getSqlBuilder()
215:     {
216:         return $this->sqlBuilder;
217:     }
218: 
219: 
220: 
221:     /********************* quick access ****************d*g**/
222: 
223: 
224: 
225:     /**
226:      * Returns row specified by primary key.
227:      * @param  mixed primary key
228:      * @return ActiveRow or FALSE if there is no such row
229:      */
230:     public function get($key)
231:     {
232:         $clone = clone $this;
233:         return $clone->wherePrimary($key)->fetch();
234:     }
235: 
236: 
237: 
238:     /**
239:      * Returns next row of result.
240:      * @return ActiveRow or FALSE if there is no row
241:      */
242:     public function fetch()
243:     {
244:         $this->execute();
245:         $return = current($this->data);
246:         next($this->data);
247:         return $return;
248:     }
249: 
250: 
251: 
252:     /**
253:      * Returns all rows as associative array.
254:      * @param  string
255:      * @param  string column name used for an array value or NULL for the whole row
256:      * @return array
257:      */
258:     public function fetchPairs($key, $value = NULL)
259:     {
260:         $return = array();
261:         foreach ($this as $row) {
262:             $return[is_object($row[$key]) ? (string) $row[$key] : $row[$key]] = ($value ? $row[$value] : $row);
263:         }
264:         return $return;
265:     }
266: 
267: 
268: 
269:     /********************* sql selectors ****************d*g**/
270: 
271: 
272: 
273:     /**
274:      * Adds select clause, more calls appends to the end.
275:      * @param  string for example "column, MD5(column) AS column_md5"
276:      * @return Selection provides a fluent interface
277:      */
278:     public function select($columns)
279:     {
280:         $this->emptyResultSet();
281:         $this->sqlBuilder->addSelect($columns);
282:         return $this;
283:     }
284: 
285: 
286: 
287:     /**
288:      * Method is deprecated, use wherePrimary() instead.
289:      * @return Selection provides a fluent interface
290:      */
291:     public function find($key)
292:     {
293:         return $this->wherePrimary($key);
294:     }
295: 
296: 
297: 
298:     /**
299:      * Adds condition for primary key.
300:      * @param  mixed
301:      * @return Selection provides a fluent interface
302:      */
303:     public function wherePrimary($key)
304:     {
305:         if (is_array($this->primary) && Nette\Utils\Validators::isList($key)) {
306:             foreach ($this->primary as $i => $primary) {
307:                 $this->where($primary, $key[$i]);
308:             }
309:         } elseif (is_array($key)) { // key contains column names
310:             $this->where($key);
311:         } else {
312:             $this->where($this->primary, $key);
313:         }
314: 
315:         return $this;
316:     }
317: 
318: 
319: 
320:     /**
321:      * Adds where condition, more calls appends with AND.
322:      * @param  string condition possibly containing ?
323:      * @param  mixed
324:      * @param  mixed ...
325:      * @return Selection provides a fluent interface
326:      */
327:     public function where($condition, $parameters = array())
328:     {
329:         if (is_array($condition)) { // where(array('column1' => 1, 'column2 > ?' => 2))
330:             foreach ($condition as $key => $val) {
331:                 if (is_int($key)) {
332:                     $this->where($val); // where('full condition')
333:                 } else {
334:                     $this->where($key, $val);   // where('column', 1)
335:                 }
336:             }
337:             return $this;
338:         }
339: 
340:         $args = func_get_args();
341:         if (call_user_func_array(array($this->sqlBuilder, 'addWhere'), $args)) {
342:             $this->emptyResultSet();
343:         }
344: 
345:         return $this;
346:     }
347: 
348: 
349: 
350:     /**
351:      * Adds order clause, more calls appends to the end.
352:      * @param  string for example 'column1, column2 DESC'
353:      * @return Selection provides a fluent interface
354:      */
355:     public function order($columns)
356:     {
357:         $this->emptyResultSet();
358:         $this->sqlBuilder->addOrder($columns);
359:         return $this;
360:     }
361: 
362: 
363: 
364:     /**
365:      * Sets limit clause, more calls rewrite old values.
366:      * @param  int
367:      * @param  int
368:      * @return Selection provides a fluent interface
369:      */
370:     public function limit($limit, $offset = NULL)
371:     {
372:         $this->emptyResultSet();
373:         $this->sqlBuilder->setLimit($limit, $offset);
374:         return $this;
375:     }
376: 
377: 
378: 
379:     /**
380:      * Sets offset using page number, more calls rewrite old values.
381:      * @param  int
382:      * @param  int
383:      * @return Selection provides a fluent interface
384:      */
385:     public function page($page, $itemsPerPage)
386:     {
387:         return $this->limit($itemsPerPage, ($page - 1) * $itemsPerPage);
388:     }
389: 
390: 
391: 
392:     /**
393:      * Sets group clause, more calls rewrite old values.
394:      * @param  string
395:      * @param  string
396:      * @return Selection provides a fluent interface
397:      */
398:     public function group($columns, $having = NULL)
399:     {
400:         $this->emptyResultSet();
401:         $this->sqlBuilder->setGroup($columns, $having);
402:         return $this;
403:     }
404: 
405: 
406: 
407:     /********************* aggregations ****************d*g**/
408: 
409: 
410: 
411:     /**
412:      * Executes aggregation function.
413:      * @param  string select call in "FUNCTION(column)" format
414:      * @return string
415:      */
416:     public function aggregation($function)
417:     {
418:         $selection = $this->createSelectionInstance();
419:         $selection->getSqlBuilder()->importConditions($this->getSqlBuilder());
420:         $selection->select($function);
421:         foreach ($selection->fetch() as $val) {
422:             return $val;
423:         }
424:     }
425: 
426: 
427: 
428:     /**
429:      * Counts number of rows.
430:      * @param  string  if it is not provided returns count of result rows, otherwise runs new sql counting query
431:      * @return int
432:      */
433:     public function count($column = NULL)
434:     {
435:         if (!$column) {
436:             $this->execute();
437:             return count($this->data);
438:         }
439:         return $this->aggregation("COUNT($column)");
440:     }
441: 
442: 
443: 
444:     /**
445:      * Returns minimum value from a column.
446:      * @param  string
447:      * @return int
448:      */
449:     public function min($column)
450:     {
451:         return $this->aggregation("MIN($column)");
452:     }
453: 
454: 
455: 
456:     /**
457:      * Returns maximum value from a column.
458:      * @param  string
459:      * @return int
460:      */
461:     public function max($column)
462:     {
463:         return $this->aggregation("MAX($column)");
464:     }
465: 
466: 
467: 
468:     /**
469:      * Returns sum of values in a column.
470:      * @param  string
471:      * @return int
472:      */
473:     public function sum($column)
474:     {
475:         return $this->aggregation("SUM($column)");
476:     }
477: 
478: 
479: 
480:     /********************* internal ****************d*g**/
481: 
482: 
483: 
484:     protected function execute()
485:     {
486:         if ($this->rows !== NULL) {
487:             return;
488:         }
489: 
490:         $this->observeCache = TRUE;
491: 
492:         try {
493:             $result = $this->query($this->getSql());
494: 
495:         } catch (\PDOException $exception) {
496:             if (!$this->sqlBuilder->getSelect() && $this->previousAccessedColumns) {
497:                 $this->previousAccessedColumns = FALSE;
498:                 $this->accessedColumns = array();
499:                 $result = $this->query($this->getSql());
500:             } else {
501:                 throw $exception;
502:             }
503:         }
504: 
505:         $this->rows = array();
506:         $usedPrimary = TRUE;
507:         $result->setFetchMode(PDO::FETCH_ASSOC);
508:         foreach ($result as $key => $row) {
509:             $row = $this->createRow($result->normalizeRow($row));
510:             $primary = $row->getSignature(FALSE);
511:             $usedPrimary = $usedPrimary && $primary;
512:             $this->rows[$primary ?: $key] = $row;
513:         }
514:         $this->data = $this->rows;
515: 
516:         if ($usedPrimary && $this->accessedColumns !== FALSE) {
517:             foreach ((array) $this->primary as $primary) {
518:                 $this->accessedColumns[$primary] = TRUE;
519:             }
520:         }
521:     }
522: 
523: 
524: 
525:     protected function createRow(array $row)
526:     {
527:         return new ActiveRow($row, $this);
528:     }
529: 
530: 
531: 
532:     protected function createSelectionInstance($table = NULL)
533:     {
534:         return new Selection($table ?: $this->name, $this->connection);
535:     }
536: 
537: 
538: 
539:     protected function createGroupedSelectionInstance($table, $column)
540:     {
541:         return new GroupedSelection($this, $table, $column);
542:     }
543: 
544: 
545: 
546:     protected function query($query)
547:     {
548:         return $this->connection->queryArgs($query, $this->sqlBuilder->getParameters());
549:     }
550: 
551: 
552: 
553:     protected function emptyResultSet()
554:     {
555:         if ($this->rows === NULL) {
556:             return;
557:         }
558: 
559:         $this->rows = NULL;
560:         $this->saveCacheState();
561:     }
562: 
563: 
564: 
565:     protected function saveCacheState()
566:     {
567:         if ($this->observeCache && $this->cache && !$this->sqlBuilder->getSelect() && $this->accessedColumns != $this->previousAccessedColumns) {
568:             $this->cache->save(array(__CLASS__, $this->name, $this->sqlBuilder->getConditions()), $this->accessedColumns);
569:         }
570:     }
571: 
572: 
573: 
574:     /**
575:      * Returns Selection parent for caching.
576:      * @return Selection
577:      */
578:     protected function getRefTable(& $refPath)
579:     {
580:         return $this;
581:     }
582: 
583: 
584: 
585:     /**
586:      * @internal
587:      * @param  string|NULL column name or (NULL to reload all columns & disable columns cache)
588:      * @param  bool
589:      */
590:     public function accessColumn($key, $selectColumn = TRUE)
591:     {
592:         if (!$this->cache) {
593:             return;
594:         }
595: 
596:         if ($key === NULL) {
597:             $this->accessedColumns = FALSE;
598:             $currentKey = key($this->data);
599:         } elseif ($this->accessedColumns !== FALSE) {
600:             $this->accessedColumns[$key] = $selectColumn;
601:         }
602: 
603:         if ($selectColumn && !$this->sqlBuilder->getSelect() && $this->previousAccessedColumns && ($key === NULL || !isset($this->previousAccessedColumns[$key]))) {
604:             $this->previousAccessedColumns = FALSE;
605:             $this->emptyResultSet();
606:             $this->dataRefreshed = TRUE;
607: 
608:             if ($key === NULL) {
609:                 // we need to move iterator in resultset
610:                 $this->execute();
611:                 while (key($this->data) !== $currentKey) {
612:                     next($this->data);
613:                 }
614:             }
615:         }
616:     }
617: 
618: 
619: 
620:     /**
621:      * @internal
622:      * @param  string
623:      */
624:     public function removeAccessColumn($key)
625:     {
626:         if ($this->cache && is_array($this->accessedColumns)) {
627:             $this->accessedColumns[$key] = FALSE;
628:         }
629:     }
630: 
631: 
632: 
633:     /**
634:      * Returns if selection requeried for more columns.
635:      * @return bool
636:      */
637:     public function getDataRefreshed()
638:     {
639:         return $this->dataRefreshed;
640:     }
641: 
642: 
643: 
644:     /********************* manipulation ****************d*g**/
645: 
646: 
647: 
648:     /**
649:      * Inserts row in a table.
650:      * @param  mixed array($column => $value)|Traversable for single row insert or Selection|string for INSERT ... SELECT
651:      * @return ActiveRow or FALSE in case of an error or number of affected rows for INSERT ... SELECT
652:      */
653:     public function insert($data)
654:     {
655:         if ($data instanceof Selection) {
656:             $data = $data->getSql();
657: 
658:         } elseif ($data instanceof \Traversable) {
659:             $data = iterator_to_array($data);
660:         }
661: 
662:         $return = $this->connection->query($this->sqlBuilder->buildInsertQuery(), $data);
663:         $this->checkReferenced = TRUE;
664: 
665:         if (!is_array($data)) {
666:             return $return->rowCount();
667:         }
668: 
669:         if (!is_array($this->primary) && !isset($data[$this->primary]) && ($id = $this->connection->lastInsertId($this->getPrimarySequence()))) {
670:             $data[$this->primary] = $id;
671:         }
672: 
673:         $row = $this->createRow($data);
674:         if ($signature = $row->getSignature(FALSE)) {
675:             $this->rows[$signature] = $row;
676:         }
677: 
678:         return $row;
679:     }
680: 
681: 
682: 
683:     /**
684:      * Updates all rows in result set.
685:      * Joins in UPDATE are supported only in MySQL
686:      * @param  array|\Traversable ($column => $value)
687:      * @return int number of affected rows or FALSE in case of an error
688:      */
689:     public function update($data)
690:     {
691:         if ($data instanceof \Traversable) {
692:             $data = iterator_to_array($data);
693: 
694:         } elseif (!is_array($data)) {
695:             throw new Nette\InvalidArgumentException;
696:         }
697: 
698:         if (!$data) {
699:             return 0;
700:         }
701: 
702:         return $this->connection->queryArgs(
703:             $this->sqlBuilder->buildUpdateQuery(),
704:             array_merge(array($data), $this->sqlBuilder->getParameters())
705:         )->rowCount();
706:     }
707: 
708: 
709: 
710:     /**
711:      * Deletes all rows in result set.
712:      * @return int number of affected rows or FALSE in case of an error
713:      */
714:     public function delete()
715:     {
716:         return $this->query($this->sqlBuilder->buildDeleteQuery())->rowCount();
717:     }
718: 
719: 
720: 
721:     /********************* references ****************d*g**/
722: 
723: 
724: 
725:     /**
726:      * Returns referenced row.
727:      * @param  string
728:      * @param  string
729:      * @param  bool  checks if rows contains the same primary value relations
730:      * @return Selection or array() if the row does not exist
731:      */
732:     public function getReferencedTable($table, $column, $checkReferenced = FALSE)
733:     {
734:         $referenced = & $this->getRefTable($refPath)->referenced[$refPath . "$table.$column"];
735:         if ($referenced === NULL || $checkReferenced || $this->checkReferenced) {
736:             $this->execute();
737:             $this->checkReferenced = FALSE;
738:             $keys = array();
739:             foreach ($this->rows as $row) {
740:                 if ($row[$column] === NULL) {
741:                     continue;
742:                 }
743: 
744:                 $key = $row[$column] instanceof ActiveRow ? $row[$column]->getPrimary() : $row[$column];
745:                 $keys[$key] = TRUE;
746:             }
747: 
748:             if ($referenced !== NULL && array_keys($keys) === array_keys($referenced->rows)) {
749:                 return $referenced;
750:             }
751: 
752:             if ($keys) {
753:                 $referenced = $this->createSelectionInstance($table);
754:                 $referenced->where($referenced->primary, array_keys($keys));
755:             } else {
756:                 $referenced = array();
757:             }
758:         }
759: 
760:         return $referenced;
761:     }
762: 
763: 
764: 
765:     /**
766:      * Returns referencing rows.
767:      * @param  string
768:      * @param  string
769:      * @param  int primary key
770:      * @return GroupedSelection
771:      */
772:     public function getReferencingTable($table, $column, $active = NULL)
773:     {
774:         $prototype = & $this->getRefTable($refPath)->referencingPrototype[$refPath . "$table.$column"];
775:         if (!$prototype) {
776:             $prototype = $this->createGroupedSelectionInstance($table, $column);
777:             $prototype->where("$table.$column", array_keys((array) $this->rows));
778:         }
779: 
780:         $clone = clone $prototype;
781:         $clone->setActive($active);
782:         return $clone;
783:     }
784: 
785: 
786: 
787:     /********************* interface Iterator ****************d*g**/
788: 
789: 
790: 
791:     public function rewind()
792:     {
793:         $this->execute();
794:         $this->keys = array_keys($this->data);
795:         reset($this->keys);
796:     }
797: 
798: 
799: 
800:     /** @return ActiveRow */
801:     public function current()
802:     {
803:         if (($key = current($this->keys)) !== FALSE) {
804:             return $this->data[$key];
805:         } else {
806:             return FALSE;
807:         }
808:     }
809: 
810: 
811: 
812:     /**
813:      * @return string row ID
814:      */
815:     public function key()
816:     {
817:         return current($this->keys);
818:     }
819: 
820: 
821: 
822:     public function next()
823:     {
824:         next($this->keys);
825:     }
826: 
827: 
828: 
829:     public function valid()
830:     {
831:         return current($this->keys) !== FALSE;
832:     }
833: 
834: 
835: 
836:     /********************* interface ArrayAccess ****************d*g**/
837: 
838: 
839: 
840:     /**
841:      * Mimic row.
842:      * @param  string row ID
843:      * @param  ActiveRow
844:      * @return NULL
845:      */
846:     public function offsetSet($key, $value)
847:     {
848:         $this->execute();
849:         $this->rows[$key] = $value;
850:     }
851: 
852: 
853: 
854:     /**
855:      * Returns specified row.
856:      * @param  string row ID
857:      * @return ActiveRow or NULL if there is no such row
858:      */
859:     public function offsetGet($key)
860:     {
861:         $this->execute();
862:         return $this->rows[$key];
863:     }
864: 
865: 
866: 
867:     /**
868:      * Tests if row exists.
869:      * @param  string row ID
870:      * @return bool
871:      */
872:     public function offsetExists($key)
873:     {
874:         $this->execute();
875:         return isset($this->rows[$key]);
876:     }
877: 
878: 
879: 
880:     /**
881:      * Removes row from result set.
882:      * @param  string row ID
883:      * @return NULL
884:      */
885:     public function offsetUnset($key)
886:     {
887:         $this->execute();
888:         unset($this->rows[$key], $this->data[$key]);
889:     }
890: 
891: }
892: 
Nette Framework 2.0.10 API API documentation generated by ApiGen 2.8.0