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