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