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: $clone->where($this->primary, $key);
228: return $clone->fetch();
229: }
230:
231:
232:
233: 234: 235: 236:
237: public function fetch()
238: {
239: $this->execute();
240: $return = current($this->data);
241: next($this->data);
242: return $return;
243: }
244:
245:
246:
247: 248: 249: 250: 251: 252:
253: public function fetchPairs($key, $value = NULL)
254: {
255: $return = array();
256: foreach ($this as $row) {
257: $return[is_object($row[$key]) ? (string) $row[$key] : $row[$key]] = ($value ? $row[$value] : $row);
258: }
259: return $return;
260: }
261:
262:
263:
264:
265:
266:
267:
268: 269: 270: 271: 272:
273: public function select($columns)
274: {
275: $this->emptyResultSet();
276: $this->sqlBuilder->addSelect($columns);
277: return $this;
278: }
279:
280:
281:
282: 283: 284: 285: 286:
287: public function find($key)
288: {
289: return $this->where($this->primary, $key);
290: }
291:
292:
293:
294: 295: 296: 297: 298: 299: 300:
301: public function where($condition, $parameters = array())
302: {
303: if (is_array($condition)) {
304: foreach ($condition as $key => $val) {
305: if (is_int($key)) {
306: $this->where($val);
307: } else {
308: $this->where($key, $val);
309: }
310: }
311: return $this;
312: }
313:
314: $args = func_get_args();
315: if (call_user_func_array(array($this->sqlBuilder, 'addWhere'), $args)) {
316: $this->emptyResultSet();
317: }
318:
319: return $this;
320: }
321:
322:
323:
324: 325: 326: 327: 328:
329: public function order($columns)
330: {
331: $this->emptyResultSet();
332: $this->sqlBuilder->addOrder($columns);
333: return $this;
334: }
335:
336:
337:
338: 339: 340: 341: 342: 343:
344: public function limit($limit, $offset = NULL)
345: {
346: $this->emptyResultSet();
347: $this->sqlBuilder->setLimit($limit, $offset);
348: return $this;
349: }
350:
351:
352:
353: 354: 355: 356: 357: 358:
359: public function page($page, $itemsPerPage)
360: {
361: return $this->limit($itemsPerPage, ($page - 1) * $itemsPerPage);
362: }
363:
364:
365:
366: 367: 368: 369: 370: 371:
372: public function group($columns, $having = NULL)
373: {
374: $this->emptyResultSet();
375: $this->sqlBuilder->setGroup($columns, $having);
376: return $this;
377: }
378:
379:
380:
381:
382:
383:
384:
385: 386: 387: 388: 389:
390: public function aggregation($function)
391: {
392: $selection = $this->createSelectionInstance();
393: $selection->getSqlBuilder()->importConditions($this->getSqlBuilder());
394: $selection->select($function);
395: foreach ($selection->fetch() as $val) {
396: return $val;
397: }
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:
423: public function min($column)
424: {
425: return $this->aggregation("MIN($column)");
426: }
427:
428:
429:
430: 431: 432: 433: 434:
435: public function max($column)
436: {
437: return $this->aggregation("MAX($column)");
438: }
439:
440:
441:
442: 443: 444: 445: 446:
447: public function sum($column)
448: {
449: return $this->aggregation("SUM($column)");
450: }
451:
452:
453:
454:
455:
456:
457:
458: protected function execute()
459: {
460: if ($this->rows !== NULL) {
461: return;
462: }
463:
464: $this->observeCache = TRUE;
465:
466: try {
467: $result = $this->query($this->sqlBuilder->buildSelectQuery());
468:
469: } catch (\PDOException $exception) {
470: if (!$this->sqlBuilder->getSelect() && $this->prevAccessed) {
471: $this->prevAccessed = '';
472: $this->accessed = array();
473: $result = $this->query($this->sqlBuilder->buildSelectQuery());
474: } else {
475: throw $exception;
476: }
477: }
478:
479: $this->rows = array();
480: $result->setFetchMode(PDO::FETCH_ASSOC);
481: foreach ($result as $key => $row) {
482: $row = $result->normalizeRow($row);
483: $this->rows[isset($row[$this->primary]) ? $row[$this->primary] : $key] = $this->createRow($row);
484: }
485: $this->data = $this->rows;
486:
487: if (isset($row[$this->primary]) && !is_string($this->accessed)) {
488: $this->accessed[$this->primary] = TRUE;
489: }
490: }
491:
492:
493:
494: protected function createRow(array $row)
495: {
496: return new ActiveRow($row, $this);
497: }
498:
499:
500:
501: protected function createSelectionInstance($table = NULL)
502: {
503: return new Selection($table ?: $this->name, $this->connection);
504: }
505:
506:
507:
508: protected function createGroupedSelectionInstance($table, $column)
509: {
510: return new GroupedSelection($this, $table, $column);
511: }
512:
513:
514:
515: protected function query($query)
516: {
517: return $this->connection->queryArgs($query, $this->sqlBuilder->getParameters());
518: }
519:
520:
521:
522: protected function emptyResultSet()
523: {
524: if ($this->rows === NULL) {
525: return;
526: }
527:
528: $this->rows = NULL;
529: $this->saveCacheState();
530: }
531:
532:
533:
534: protected function saveCacheState()
535: {
536: if ($this->observeCache && ($cache = $this->connection->getCache()) && !$this->sqlBuilder->getSelect() && $this->accessed != $this->prevAccessed) {
537: $cache->save(array(__CLASS__, $this->name, $this->sqlBuilder->getConditions()), $this->accessed);
538: }
539: }
540:
541:
542:
543: 544: 545: 546:
547: protected function getRefTable(& $refPath)
548: {
549: return $this;
550: }
551:
552:
553:
554: 555: 556: 557: 558: 559:
560: public function access($key, $cache = TRUE)
561: {
562: if ($cache === NULL) {
563: if (is_array($this->accessed)) {
564: $this->accessed[$key] = FALSE;
565: }
566: return FALSE;
567: }
568:
569: if ($key === NULL) {
570: $this->accessed = '';
571:
572: } elseif (!is_string($this->accessed)) {
573: $this->accessed[$key] = $cache;
574: }
575:
576: if ($cache && !$this->sqlBuilder->getSelect() && $this->prevAccessed && ($key === NULL || !isset($this->prevAccessed[$key]))) {
577: $this->prevAccessed = '';
578: $this->emptyResultSet();
579: return TRUE;
580: }
581:
582: return FALSE;
583: }
584:
585:
586:
587:
588:
589:
590:
591: 592: 593: 594: 595:
596: public function insert($data)
597: {
598: if ($data instanceof Selection) {
599: $data = $data->getSql();
600:
601: } elseif ($data instanceof \Traversable) {
602: $data = iterator_to_array($data);
603: }
604:
605: $return = $this->connection->query($this->sqlBuilder->buildInsertQuery(), $data);
606: $this->checkReferenced = TRUE;
607:
608: if (!is_array($data)) {
609: return $return->rowCount();
610: }
611:
612: if (!isset($data[$this->primary]) && ($id = $this->connection->lastInsertId($this->getPrimarySequence()))) {
613: $data[$this->primary] = $id;
614: return $this->rows[$id] = $this->createRow($data);
615:
616: } else {
617: return $this->createRow($data);
618:
619: }
620: }
621:
622:
623:
624: 625: 626: 627: 628: 629:
630: public function update($data)
631: {
632: if ($data instanceof \Traversable) {
633: $data = iterator_to_array($data);
634:
635: } elseif (!is_array($data)) {
636: throw new Nette\InvalidArgumentException;
637: }
638:
639: if (!$data) {
640: return 0;
641: }
642:
643: return $this->connection->queryArgs(
644: $this->sqlBuilder->buildUpdateQuery(),
645: array_merge(array($data), $this->sqlBuilder->getParameters())
646: )->rowCount();
647: }
648:
649:
650:
651: 652: 653: 654:
655: public function delete()
656: {
657: return $this->query($this->sqlBuilder->buildDeleteQuery())->rowCount();
658: }
659:
660:
661:
662:
663:
664:
665:
666: 667: 668: 669: 670: 671: 672:
673: public function getReferencedTable($table, $column, $checkReferenced = FALSE)
674: {
675: $referenced = & $this->getRefTable($refPath)->referenced[$refPath . "$table.$column"];
676: if ($referenced === NULL || $checkReferenced || $this->checkReferenced) {
677: $this->execute();
678: $this->checkReferenced = FALSE;
679: $keys = array();
680: foreach ($this->rows as $row) {
681: if ($row[$column] === NULL)
682: continue;
683:
684: $key = $row[$column] instanceof ActiveRow ? $row[$column]->getPrimary() : $row[$column];
685: $keys[$key] = TRUE;
686: }
687:
688: if ($referenced !== NULL && array_keys($keys) === array_keys($referenced->rows)) {
689: return $referenced;
690: }
691:
692: if ($keys) {
693: $referenced = $this->createSelectionInstance($table);
694: $referenced->where($referenced->primary, array_keys($keys));
695: } else {
696: $referenced = array();
697: }
698: }
699:
700: return $referenced;
701: }
702:
703:
704:
705: 706: 707: 708: 709: 710: 711:
712: public function getReferencingTable($table, $column, $active = NULL)
713: {
714: $prototype = & $this->getRefTable($refPath)->referencingPrototype[$refPath . "$table.$column"];
715: if (!$prototype) {
716: $prototype = $this->createGroupedSelectionInstance($table, $column);
717: $prototype->where("$table.$column", array_keys((array) $this->rows));
718: }
719:
720: $clone = clone $prototype;
721: $clone->setActive($active);
722: return $clone;
723: }
724:
725:
726:
727:
728:
729:
730:
731: public function rewind()
732: {
733: $this->execute();
734: $this->keys = array_keys($this->data);
735: reset($this->keys);
736: }
737:
738:
739:
740:
741: public function current()
742: {
743: return $this->data[current($this->keys)];
744: }
745:
746:
747:
748: 749: 750:
751: public function key()
752: {
753: return current($this->keys);
754: }
755:
756:
757:
758: public function next()
759: {
760: next($this->keys);
761: }
762:
763:
764:
765: public function valid()
766: {
767: return current($this->keys) !== FALSE;
768: }
769:
770:
771:
772:
773:
774:
775:
776: 777: 778: 779: 780: 781:
782: public function offsetSet($key, $value)
783: {
784: $this->execute();
785: $this->data[$key] = $value;
786: }
787:
788:
789:
790: 791: 792: 793: 794:
795: public function offsetGet($key)
796: {
797: $this->execute();
798: return $this->data[$key];
799: }
800:
801:
802:
803: 804: 805: 806: 807:
808: public function offsetExists($key)
809: {
810: $this->execute();
811: return isset($this->data[$key]);
812: }
813:
814:
815:
816: 817: 818: 819: 820:
821: public function offsetUnset($key)
822: {
823: $this->execute();
824: unset($this->data[$key]);
825: }
826:
827: }
828: