Packages

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

Classes

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