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