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

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