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