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