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