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->wherePrimary($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: public function find($key)
292: {
293: return $this->wherePrimary($key);
294: }
295:
296:
297:
298: 299: 300: 301: 302:
303: public function wherePrimary($key)
304: {
305: if (is_array($this->primary) && Nette\Utils\Validators::isList($key)) {
306: foreach ($this->primary as $i => $primary) {
307: $this->where($primary, $key[$i]);
308: }
309: } elseif (is_array($key)) {
310: $this->where($key);
311: } else {
312: $this->where($this->primary, $key);
313: }
314:
315: return $this;
316: }
317:
318:
319:
320: 321: 322: 323: 324: 325: 326:
327: public function where($condition, $parameters = array())
328: {
329: if (is_array($condition)) {
330: foreach ($condition as $key => $val) {
331: if (is_int($key)) {
332: $this->where($val);
333: } else {
334: $this->where($key, $val);
335: }
336: }
337: return $this;
338: }
339:
340: $args = func_get_args();
341: if (call_user_func_array(array($this->sqlBuilder, 'addWhere'), $args)) {
342: $this->emptyResultSet();
343: }
344:
345: return $this;
346: }
347:
348:
349:
350: 351: 352: 353: 354:
355: public function order($columns)
356: {
357: $this->emptyResultSet();
358: $this->sqlBuilder->addOrder($columns);
359: return $this;
360: }
361:
362:
363:
364: 365: 366: 367: 368: 369:
370: public function limit($limit, $offset = NULL)
371: {
372: $this->emptyResultSet();
373: $this->sqlBuilder->setLimit($limit, $offset);
374: return $this;
375: }
376:
377:
378:
379: 380: 381: 382: 383: 384:
385: public function page($page, $itemsPerPage)
386: {
387: return $this->limit($itemsPerPage, ($page - 1) * $itemsPerPage);
388: }
389:
390:
391:
392: 393: 394: 395: 396: 397:
398: public function group($columns, $having = NULL)
399: {
400: $this->emptyResultSet();
401: $this->sqlBuilder->setGroup($columns, $having);
402: return $this;
403: }
404:
405:
406:
407:
408:
409:
410:
411: 412: 413: 414: 415:
416: public function aggregation($function)
417: {
418: $selection = $this->createSelectionInstance();
419: $selection->getSqlBuilder()->importConditions($this->getSqlBuilder());
420: $selection->select($function);
421: foreach ($selection->fetch() as $val) {
422: return $val;
423: }
424: }
425:
426:
427:
428: 429: 430: 431: 432:
433: public function count($column = NULL)
434: {
435: if (!$column) {
436: $this->execute();
437: return count($this->data);
438: }
439: return $this->aggregation("COUNT($column)");
440: }
441:
442:
443:
444: 445: 446: 447: 448:
449: public function min($column)
450: {
451: return $this->aggregation("MIN($column)");
452: }
453:
454:
455:
456: 457: 458: 459: 460:
461: public function max($column)
462: {
463: return $this->aggregation("MAX($column)");
464: }
465:
466:
467:
468: 469: 470: 471: 472:
473: public function sum($column)
474: {
475: return $this->aggregation("SUM($column)");
476: }
477:
478:
479:
480:
481:
482:
483:
484: protected function execute()
485: {
486: if ($this->rows !== NULL) {
487: return;
488: }
489:
490: $this->observeCache = TRUE;
491:
492: try {
493: $result = $this->query($this->getSql());
494:
495: } catch (\PDOException $exception) {
496: if (!$this->sqlBuilder->getSelect() && $this->previousAccessedColumns) {
497: $this->previousAccessedColumns = FALSE;
498: $this->accessedColumns = array();
499: $result = $this->query($this->getSql());
500: } else {
501: throw $exception;
502: }
503: }
504:
505: $this->rows = array();
506: $usedPrimary = TRUE;
507: $result->setFetchMode(PDO::FETCH_ASSOC);
508: foreach ($result as $key => $row) {
509: $row = $this->createRow($result->normalizeRow($row));
510: $primary = $row->getSignature(FALSE);
511: $usedPrimary = $usedPrimary && $primary;
512: $this->rows[$primary ?: $key] = $row;
513: }
514: $this->data = $this->rows;
515:
516: if ($usedPrimary && $this->accessedColumns !== FALSE) {
517: foreach ((array) $this->primary as $primary) {
518: $this->accessedColumns[$primary] = TRUE;
519: }
520: }
521: }
522:
523:
524:
525: protected function createRow(array $row)
526: {
527: return new ActiveRow($row, $this);
528: }
529:
530:
531:
532: protected function createSelectionInstance($table = NULL)
533: {
534: return new Selection($table ?: $this->name, $this->connection);
535: }
536:
537:
538:
539: protected function createGroupedSelectionInstance($table, $column)
540: {
541: return new GroupedSelection($this, $table, $column);
542: }
543:
544:
545:
546: protected function query($query)
547: {
548: return $this->connection->queryArgs($query, $this->sqlBuilder->getParameters());
549: }
550:
551:
552:
553: protected function emptyResultSet()
554: {
555: if ($this->rows === NULL) {
556: return;
557: }
558:
559: $this->rows = NULL;
560: $this->saveCacheState();
561: }
562:
563:
564:
565: protected function saveCacheState()
566: {
567: if ($this->observeCache && $this->cache && !$this->sqlBuilder->getSelect() && $this->accessedColumns != $this->previousAccessedColumns) {
568: $this->cache->save(array(__CLASS__, $this->name, $this->sqlBuilder->getConditions()), $this->accessedColumns);
569: }
570: }
571:
572:
573:
574: 575: 576: 577:
578: protected function getRefTable(& $refPath)
579: {
580: return $this;
581: }
582:
583:
584:
585: 586: 587: 588: 589:
590: public function accessColumn($key, $selectColumn = TRUE)
591: {
592: if (!$this->cache) {
593: return;
594: }
595:
596: if ($key === NULL) {
597: $this->accessedColumns = FALSE;
598: $currentKey = key($this->data);
599: } elseif ($this->accessedColumns !== FALSE) {
600: $this->accessedColumns[$key] = $selectColumn;
601: }
602:
603: if ($selectColumn && !$this->sqlBuilder->getSelect() && $this->previousAccessedColumns && ($key === NULL || !isset($this->previousAccessedColumns[$key]))) {
604: $this->previousAccessedColumns = FALSE;
605: $this->emptyResultSet();
606: $this->dataRefreshed = TRUE;
607:
608: if ($key === NULL) {
609:
610: $this->execute();
611: while (key($this->data) !== $currentKey) {
612: next($this->data);
613: }
614: }
615: }
616: }
617:
618:
619:
620: 621: 622: 623:
624: public function removeAccessColumn($key)
625: {
626: if ($this->cache && is_array($this->accessedColumns)) {
627: $this->accessedColumns[$key] = FALSE;
628: }
629: }
630:
631:
632:
633: 634: 635: 636:
637: public function getDataRefreshed()
638: {
639: return $this->dataRefreshed;
640: }
641:
642:
643:
644:
645:
646:
647:
648: 649: 650: 651: 652:
653: public function insert($data)
654: {
655: if ($data instanceof Selection) {
656: $data = $data->getSql();
657:
658: } elseif ($data instanceof \Traversable) {
659: $data = iterator_to_array($data);
660: }
661:
662: $return = $this->connection->query($this->sqlBuilder->buildInsertQuery(), $data);
663: $this->checkReferenced = TRUE;
664:
665: if (!is_array($data)) {
666: return $return->rowCount();
667: }
668:
669: if (!is_array($this->primary) && !isset($data[$this->primary]) && ($id = $this->connection->lastInsertId($this->getPrimarySequence()))) {
670: $data[$this->primary] = $id;
671: }
672:
673: $row = $this->createRow($data);
674: if ($signature = $row->getSignature(FALSE)) {
675: $this->rows[$signature] = $row;
676: }
677:
678: return $row;
679: }
680:
681:
682:
683: 684: 685: 686: 687: 688:
689: public function update($data)
690: {
691: if ($data instanceof \Traversable) {
692: $data = iterator_to_array($data);
693:
694: } elseif (!is_array($data)) {
695: throw new Nette\InvalidArgumentException;
696: }
697:
698: if (!$data) {
699: return 0;
700: }
701:
702: return $this->connection->queryArgs(
703: $this->sqlBuilder->buildUpdateQuery(),
704: array_merge(array($data), $this->sqlBuilder->getParameters())
705: )->rowCount();
706: }
707:
708:
709:
710: 711: 712: 713:
714: public function delete()
715: {
716: return $this->query($this->sqlBuilder->buildDeleteQuery())->rowCount();
717: }
718:
719:
720:
721:
722:
723:
724:
725: 726: 727: 728: 729: 730: 731:
732: public function getReferencedTable($table, $column, $checkReferenced = FALSE)
733: {
734: $referenced = & $this->getRefTable($refPath)->referenced[$refPath . "$table.$column"];
735: if ($referenced === NULL || $checkReferenced || $this->checkReferenced) {
736: $this->execute();
737: $this->checkReferenced = FALSE;
738: $keys = array();
739: foreach ($this->rows as $row) {
740: if ($row[$column] === NULL) {
741: continue;
742: }
743:
744: $key = $row[$column] instanceof ActiveRow ? $row[$column]->getPrimary() : $row[$column];
745: $keys[$key] = TRUE;
746: }
747:
748: if ($referenced !== NULL && array_keys($keys) === array_keys($referenced->rows)) {
749: return $referenced;
750: }
751:
752: if ($keys) {
753: $referenced = $this->createSelectionInstance($table);
754: $referenced->where($referenced->primary, array_keys($keys));
755: } else {
756: $referenced = array();
757: }
758: }
759:
760: return $referenced;
761: }
762:
763:
764:
765: 766: 767: 768: 769: 770: 771:
772: public function getReferencingTable($table, $column, $active = NULL)
773: {
774: $prototype = & $this->getRefTable($refPath)->referencingPrototype[$refPath . "$table.$column"];
775: if (!$prototype) {
776: $prototype = $this->createGroupedSelectionInstance($table, $column);
777: $prototype->where("$table.$column", array_keys((array) $this->rows));
778: }
779:
780: $clone = clone $prototype;
781: $clone->setActive($active);
782: return $clone;
783: }
784:
785:
786:
787:
788:
789:
790:
791: public function rewind()
792: {
793: $this->execute();
794: $this->keys = array_keys($this->data);
795: reset($this->keys);
796: }
797:
798:
799:
800:
801: public function current()
802: {
803: if (($key = current($this->keys)) !== FALSE) {
804: return $this->data[$key];
805: } else {
806: return FALSE;
807: }
808: }
809:
810:
811:
812: 813: 814:
815: public function key()
816: {
817: return current($this->keys);
818: }
819:
820:
821:
822: public function next()
823: {
824: next($this->keys);
825: }
826:
827:
828:
829: public function valid()
830: {
831: return current($this->keys) !== FALSE;
832: }
833:
834:
835:
836:
837:
838:
839:
840: 841: 842: 843: 844: 845:
846: public function offsetSet($key, $value)
847: {
848: $this->execute();
849: $this->rows[$key] = $value;
850: }
851:
852:
853:
854: 855: 856: 857: 858:
859: public function offsetGet($key)
860: {
861: $this->execute();
862: return $this->rows[$key];
863: }
864:
865:
866:
867: 868: 869: 870: 871:
872: public function offsetExists($key)
873: {
874: $this->execute();
875: return isset($this->rows[$key]);
876: }
877:
878:
879:
880: 881: 882: 883: 884:
885: public function offsetUnset($key)
886: {
887: $this->execute();
888: unset($this->rows[$key], $this->data[$key]);
889: }
890:
891: }
892: