1: <?php
2:
3: 4: 5: 6: 7: 8: 9: 10:
11:
12: namespace Nette\Database\Table;
13:
14: use Nette,
15: Nette\Database\ISupplementalDriver,
16: PDO;
17:
18:
19:
20: 21: 22: 23: 24: 25: 26: 27: 28:
29: class Selection extends Nette\Object implements \Iterator, \ArrayAccess, \Countable
30: {
31:
32: protected $connection;
33:
34:
35: protected $sqlBuilder;
36:
37:
38: protected $name;
39:
40:
41: protected $primary;
42:
43:
44: protected $primarySequence = FALSE;
45:
46:
47: protected $rows;
48:
49:
50: protected $data;
51:
52:
53: protected $referenced = array();
54:
55:
56: protected $referencing = array();
57:
58:
59: protected $referencingPrototype = array();
60:
61:
62: protected $aggregation = array();
63:
64:
65: protected $accessed;
66:
67:
68: protected $prevAccessed;
69:
70:
71: protected $observeCache = FALSE;
72:
73:
74: protected $checkReferenced = FALSE;
75:
76:
77: protected $keys = array();
78:
79:
80:
81: 82: 83: 84: 85:
86: public function __construct($table, Nette\Database\Connection $connection)
87: {
88: $this->name = $table;
89: $this->connection = $connection;
90: $this->primary = $connection->getDatabaseReflection()->getPrimary($table);
91: $this->sqlBuilder = new SqlBuilder($this);
92: }
93:
94:
95:
96: public function __destruct()
97: {
98: $this->saveCacheState();
99: }
100:
101:
102:
103: public function __clone()
104: {
105: $this->sqlBuilder = clone $this->sqlBuilder;
106: $this->sqlBuilder->setSelection($this);
107: }
108:
109:
110:
111: 112: 113:
114: public function getConnection()
115: {
116: return $this->connection;
117: }
118:
119:
120:
121: 122: 123:
124: public function getName()
125: {
126: return $this->name;
127: }
128:
129:
130:
131: 132: 133:
134: public function getPrimary()
135: {
136: return $this->primary;
137: }
138:
139:
140:
141: 142: 143:
144: public function getPrimarySequence()
145: {
146: if ($this->primarySequence === FALSE) {
147: $this->primarySequence = NULL;
148:
149: $driver = $this->connection->getSupplementalDriver();
150: if ($driver->isSupported(ISupplementalDriver::SUPPORT_SEQUENCE)) {
151: foreach ($driver->getColumns($this->name) as $column) {
152: if ($column['name'] === $this->primary) {
153: $this->primarySequence = $column['vendor']['sequence'];
154: break;
155: }
156: }
157: }
158: }
159:
160: return $this->primarySequence;
161: }
162:
163:
164:
165: 166: 167: 168:
169: public function setPrimarySequence($sequence)
170: {
171: $this->primarySequence = $sequence;
172: return $this;
173: }
174:
175:
176:
177: 178: 179:
180: public function getSql()
181: {
182: return $this->sqlBuilder->buildSelectQuery();
183: }
184:
185:
186:
187: 188: 189: 190: 191:
192: public function getPreviousAccessed()
193: {
194: $cache = $this->connection->getCache();
195: if ($this->rows === NULL && $cache && !is_string($this->prevAccessed)) {
196: $this->accessed = $this->prevAccessed = $cache->load(array(__CLASS__, $this->name, $this->sqlBuilder->getConditions()));
197: }
198:
199: return $this->prevAccessed;
200: }
201:
202:
203:
204: 205: 206: 207:
208: public function getSqlBuilder()
209: {
210: return $this->sqlBuilder;
211: }
212:
213:
214:
215:
216:
217:
218:
219: 220: 221: 222: 223:
224: public function get($key)
225: {
226: $clone = clone $this;
227: return $clone->find($key)->fetch();
228: }
229:
230:
231:
232: 233: 234: 235:
236: public function fetch()
237: {
238: $this->execute();
239: $return = current($this->data);
240: next($this->data);
241: return $return;
242: }
243:
244:
245:
246: 247: 248: 249: 250: 251:
252: public function fetchPairs($key, $value = NULL)
253: {
254: $return = array();
255: foreach ($this as $row) {
256: $return[is_object($row[$key]) ? (string) $row[$key] : $row[$key]] = ($value ? $row[$value] : $row);
257: }
258: return $return;
259: }
260:
261:
262:
263:
264:
265:
266:
267: 268: 269: 270: 271:
272: public function select($columns)
273: {
274: $this->emptyResultSet();
275: $this->sqlBuilder->addSelect($columns);
276: return $this;
277: }
278:
279:
280:
281: 282: 283: 284: 285:
286: public function find($key)
287: {
288: if (is_array($this->primary) && Nette\Utils\Validators::isList($key)) {
289: foreach ($this->primary as $i => $primary) {
290: $this->where($primary, $key[$i]);
291: }
292: } elseif (is_array($key)) {
293: $this->where($key);
294: } else {
295: $this->where($this->primary, $key);
296: }
297:
298: return $this;
299: }
300:
301:
302:
303: 304: 305: 306: 307: 308: 309:
310: public function where($condition, $parameters = array())
311: {
312: if (is_array($condition)) {
313: foreach ($condition as $key => $val) {
314: if (is_int($key)) {
315: $this->where($val);
316: } else {
317: $this->where($key, $val);
318: }
319: }
320: return $this;
321: }
322:
323: $args = func_get_args();
324: if (call_user_func_array(array($this->sqlBuilder, 'addWhere'), $args)) {
325: $this->emptyResultSet();
326: }
327:
328: return $this;
329: }
330:
331:
332:
333: 334: 335: 336: 337:
338: public function order($columns)
339: {
340: $this->emptyResultSet();
341: $this->sqlBuilder->addOrder($columns);
342: return $this;
343: }
344:
345:
346:
347: 348: 349: 350: 351: 352:
353: public function limit($limit, $offset = NULL)
354: {
355: $this->emptyResultSet();
356: $this->sqlBuilder->setLimit($limit, $offset);
357: return $this;
358: }
359:
360:
361:
362: 363: 364: 365: 366: 367:
368: public function page($page, $itemsPerPage)
369: {
370: return $this->limit($itemsPerPage, ($page - 1) * $itemsPerPage);
371: }
372:
373:
374:
375: 376: 377: 378: 379: 380:
381: public function group($columns, $having = NULL)
382: {
383: $this->emptyResultSet();
384: $this->sqlBuilder->setGroup($columns, $having);
385: return $this;
386: }
387:
388:
389:
390:
391:
392:
393:
394: 395: 396: 397: 398:
399: public function aggregation($function)
400: {
401: $selection = $this->createSelectionInstance();
402: $selection->getSqlBuilder()->importConditions($this->getSqlBuilder());
403: $selection->select($function);
404: foreach ($selection->fetch() as $val) {
405: return $val;
406: }
407: }
408:
409:
410:
411: 412: 413: 414: 415:
416: public function count($column = NULL)
417: {
418: if (!$column) {
419: $this->execute();
420: return count($this->data);
421: }
422: return $this->aggregation("COUNT($column)");
423: }
424:
425:
426:
427: 428: 429: 430: 431:
432: public function min($column)
433: {
434: return $this->aggregation("MIN($column)");
435: }
436:
437:
438:
439: 440: 441: 442: 443:
444: public function max($column)
445: {
446: return $this->aggregation("MAX($column)");
447: }
448:
449:
450:
451: 452: 453: 454: 455:
456: public function sum($column)
457: {
458: return $this->aggregation("SUM($column)");
459: }
460:
461:
462:
463:
464:
465:
466:
467: protected function execute()
468: {
469: if ($this->rows !== NULL) {
470: return;
471: }
472:
473: $this->observeCache = TRUE;
474:
475: try {
476: $result = $this->query($this->sqlBuilder->buildSelectQuery());
477:
478: } catch (\PDOException $exception) {
479: if (!$this->sqlBuilder->getSelect() && $this->prevAccessed) {
480: $this->prevAccessed = '';
481: $this->accessed = array();
482: $result = $this->query($this->sqlBuilder->buildSelectQuery());
483: } else {
484: throw $exception;
485: }
486: }
487:
488: $this->rows = array();
489: $usedPrimary = TRUE;
490: $result->setFetchMode(PDO::FETCH_ASSOC);
491: foreach ($result as $key => $row) {
492: $row = $this->createRow($result->normalizeRow($row));
493: $primary = $row->getSignature(FALSE);
494: $usedPrimary = $usedPrimary && $primary;
495: $this->rows[$primary ?: $key] = $row;
496: }
497: $this->data = $this->rows;
498:
499: if ($usedPrimary && !is_string($this->accessed)) {
500: foreach ((array) $this->primary as $primary) {
501: $this->accessed[$primary] = TRUE;
502: }
503: }
504: }
505:
506:
507:
508: protected function createRow(array $row)
509: {
510: return new ActiveRow($row, $this);
511: }
512:
513:
514:
515: protected function createSelectionInstance($table = NULL)
516: {
517: return new Selection($table ?: $this->name, $this->connection);
518: }
519:
520:
521:
522: protected function createGroupedSelectionInstance($table, $column)
523: {
524: return new GroupedSelection($this, $table, $column);
525: }
526:
527:
528:
529: protected function query($query)
530: {
531: return $this->connection->queryArgs($query, $this->sqlBuilder->getParameters());
532: }
533:
534:
535:
536: protected function emptyResultSet()
537: {
538: if ($this->rows === NULL) {
539: return;
540: }
541:
542: $this->rows = NULL;
543: $this->saveCacheState();
544: }
545:
546:
547:
548: protected function saveCacheState()
549: {
550: if ($this->observeCache && ($cache = $this->connection->getCache()) && !$this->sqlBuilder->getSelect() && $this->accessed != $this->prevAccessed) {
551: $cache->save(array(__CLASS__, $this->name, $this->sqlBuilder->getConditions()), $this->accessed);
552: }
553: }
554:
555:
556:
557: 558: 559: 560:
561: protected function getRefTable(& $refPath)
562: {
563: return $this;
564: }
565:
566:
567:
568: 569: 570: 571: 572: 573:
574: public function access($key, $cache = TRUE)
575: {
576: if ($cache === NULL) {
577: if (is_array($this->accessed)) {
578: $this->accessed[$key] = FALSE;
579: }
580: return FALSE;
581: }
582:
583: if ($key === NULL) {
584: $this->accessed = '';
585:
586: } elseif (!is_string($this->accessed)) {
587: $this->accessed[$key] = $cache;
588: }
589:
590: if ($cache && !$this->sqlBuilder->getSelect() && $this->prevAccessed && ($key === NULL || !isset($this->prevAccessed[$key]))) {
591: $this->prevAccessed = '';
592: $this->emptyResultSet();
593: return TRUE;
594: }
595:
596: return FALSE;
597: }
598:
599:
600:
601:
602:
603:
604:
605: 606: 607: 608: 609:
610: public function insert($data)
611: {
612: if ($data instanceof Selection) {
613: $data = $data->getSql();
614:
615: } elseif ($data instanceof \Traversable) {
616: $data = iterator_to_array($data);
617: }
618:
619: $return = $this->connection->query($this->sqlBuilder->buildInsertQuery(), $data);
620: $this->checkReferenced = TRUE;
621:
622: if (!is_array($data)) {
623: return $return->rowCount();
624: }
625:
626: if (!is_array($this->primary) && !isset($data[$this->primary]) && ($id = $this->connection->lastInsertId($this->getPrimarySequence()))) {
627: $data[$this->primary] = $id;
628: }
629:
630: $row = $this->createRow($data);
631: if ($signature = $row->getSignature(FALSE)) {
632: $this->rows[$signature] = $row;
633: }
634:
635: return $row;
636: }
637:
638:
639:
640: 641: 642: 643: 644: 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: 668: 669: 670:
671: public function delete()
672: {
673: return $this->query($this->sqlBuilder->buildDeleteQuery())->rowCount();
674: }
675:
676:
677:
678:
679:
680:
681:
682: 683: 684: 685: 686: 687: 688:
689: public function getReferencedTable($table, $column, $checkReferenced = FALSE)
690: {
691: $referenced = & $this->getRefTable($refPath)->referenced[$refPath . "$table.$column"];
692: if ($referenced === NULL || $checkReferenced || $this->checkReferenced) {
693: $this->execute();
694: $this->checkReferenced = FALSE;
695: $keys = array();
696: foreach ($this->rows as $row) {
697: if ($row[$column] === NULL)
698: continue;
699:
700: $key = $row[$column] instanceof ActiveRow ? $row[$column]->getPrimary() : $row[$column];
701: $keys[$key] = TRUE;
702: }
703:
704: if ($referenced !== NULL && array_keys($keys) === array_keys($referenced->rows)) {
705: return $referenced;
706: }
707:
708: if ($keys) {
709: $referenced = $this->createSelectionInstance($table);
710: $referenced->where($referenced->primary, array_keys($keys));
711: } else {
712: $referenced = array();
713: }
714: }
715:
716: return $referenced;
717: }
718:
719:
720:
721: 722: 723: 724: 725: 726: 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:
743:
744:
745:
746:
747: public function rewind()
748: {
749: $this->execute();
750: $this->keys = array_keys($this->data);
751: reset($this->keys);
752: }
753:
754:
755:
756:
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: 769: 770:
771: public function key()
772: {
773: return current($this->keys);
774: }
775:
776:
777:
778: public function next()
779: {
780: next($this->keys);
781: }
782:
783:
784:
785: public function valid()
786: {
787: return current($this->keys) !== FALSE;
788: }
789:
790:
791:
792:
793:
794:
795:
796: 797: 798: 799: 800: 801:
802: public function offsetSet($key, $value)
803: {
804: $this->execute();
805: $this->data[$key] = $value;
806: }
807:
808:
809:
810: 811: 812: 813: 814:
815: public function offsetGet($key)
816: {
817: $this->execute();
818: return $this->data[$key];
819: }
820:
821:
822:
823: 824: 825: 826: 827:
828: public function offsetExists($key)
829: {
830: $this->execute();
831: return isset($this->data[$key]);
832: }
833:
834:
835:
836: 837: 838: 839: 840:
841: public function offsetUnset($key)
842: {
843: $this->execute();
844: unset($this->data[$key]);
845: }
846:
847: }
848: