1: <?php
2:
3: 4: 5: 6:
7:
8: namespace Nette\Database\Table;
9:
10: use Nette;
11: use Nette\Database\Context;
12: use Nette\Database\IConventions;
13:
14:
15: 16: 17: 18: 19: 20:
21: class Selection extends Nette\Object implements \Iterator, IRowContainer, \ArrayAccess, \Countable
22: {
23:
24: protected $context;
25:
26:
27: protected $conventions;
28:
29:
30: protected $cache;
31:
32:
33: protected $sqlBuilder;
34:
35:
36: protected $name;
37:
38:
39: protected $primary;
40:
41:
42: protected $primarySequence = FALSE;
43:
44:
45: protected $rows;
46:
47:
48: protected $data;
49:
50:
51: protected $dataRefreshed = FALSE;
52:
53:
54: protected $globalRefCache;
55:
56:
57: protected $refCache;
58:
59:
60: protected $generalCacheKey;
61:
62:
63: protected $generalCacheTraceKey;
64:
65:
66: protected $specificCacheKey;
67:
68:
69: protected $aggregation = array();
70:
71:
72: protected $accessedColumns;
73:
74:
75: protected $previousAccessedColumns;
76:
77:
78: protected $observeCache = FALSE;
79:
80:
81: protected $keys = array();
82:
83:
84: 85: 86: 87: 88: 89: 90:
91: public function __construct(Context $context, IConventions $conventions, $tableName, Nette\Caching\IStorage $cacheStorage = NULL)
92: {
93: $this->context = $context;
94: $this->conventions = $conventions;
95: $this->name = $tableName;
96:
97: $this->cache = $cacheStorage ? new Nette\Caching\Cache($cacheStorage, 'Nette.Database.' . md5($context->getConnection()->getDsn())) : NULL;
98: $this->primary = $conventions->getPrimary($tableName);
99: $this->sqlBuilder = new SqlBuilder($tableName, $context);
100: $this->refCache = & $this->getRefTable($refPath)->globalRefCache[$refPath];
101: }
102:
103:
104: public function __destruct()
105: {
106: $this->saveCacheState();
107: }
108:
109:
110: public function __clone()
111: {
112: $this->sqlBuilder = clone $this->sqlBuilder;
113: }
114:
115:
116:
117: public function getConnection()
118: {
119: trigger_error(__METHOD__ . '() is deprecated; use DI container to autowire Nette\Database\Connection instead.', E_USER_DEPRECATED);
120: return $this->context->getConnection();
121: }
122:
123:
124:
125: public function getDatabaseReflection()
126: {
127: trigger_error(__METHOD__ . '() is deprecated; use DI container to autowire Nette\Database\IConventions instead.', E_USER_DEPRECATED);
128: return $this->conventions;
129: }
130:
131:
132: 133: 134:
135: public function getName()
136: {
137: return $this->name;
138: }
139:
140:
141: 142: 143: 144:
145: public function getPrimary($need = TRUE)
146: {
147: if ($this->primary === NULL && $need) {
148: throw new \LogicException("Table '{$this->name}' does not have a primary key.");
149: }
150: return $this->primary;
151: }
152:
153:
154: 155: 156:
157: public function getPrimarySequence()
158: {
159: if ($this->primarySequence === FALSE) {
160: $this->primarySequence = $this->context->getStructure()->getPrimaryKeySequence($this->name);
161: }
162:
163: return $this->primarySequence;
164: }
165:
166:
167: 168: 169: 170:
171: public function setPrimarySequence($sequence)
172: {
173: $this->primarySequence = $sequence;
174: return $this;
175: }
176:
177:
178: 179: 180:
181: public function getSql()
182: {
183: return $this->sqlBuilder->buildSelectQuery($this->getPreviousAccessedColumns());
184: }
185:
186:
187: 188: 189: 190: 191:
192: public function getPreviousAccessedColumns()
193: {
194: if ($this->cache && $this->previousAccessedColumns === NULL) {
195: $this->accessedColumns = $this->previousAccessedColumns = $this->cache->load($this->getGeneralCacheKey());
196: if ($this->previousAccessedColumns === NULL) {
197: $this->previousAccessedColumns = array();
198: }
199: }
200:
201: return array_keys(array_filter((array) $this->previousAccessedColumns));
202: }
203:
204:
205: 206: 207: 208:
209: public function getSqlBuilder()
210: {
211: return $this->sqlBuilder;
212: }
213:
214:
215:
216:
217:
218: 219: 220: 221: 222:
223: public function get($key)
224: {
225: $clone = clone $this;
226: return $clone->wherePrimary($key)->fetch();
227: }
228:
229:
230: 231: 232:
233: public function fetch()
234: {
235: $this->execute();
236: $return = current($this->data);
237: next($this->data);
238: return $return;
239: }
240:
241:
242: 243: 244:
245: public function fetchPairs($key = NULL, $value = NULL)
246: {
247: return Nette\Database\Helpers::toPairs($this->fetchAll(), $key, $value);
248: }
249:
250:
251: 252: 253:
254: public function fetchAll()
255: {
256: return iterator_to_array($this);
257: }
258:
259:
260: 261: 262:
263: public function fetchAssoc($path)
264: {
265: $rows = array_map('iterator_to_array', $this->fetchAll());
266: return Nette\Utils\Arrays::associate($rows, $path);
267: }
268:
269:
270:
271:
272:
273: 274: 275: 276: 277:
278: public function select($columns)
279: {
280: $this->emptyResultSet();
281: call_user_func_array(array($this->sqlBuilder, 'addSelect'), func_get_args());
282: return $this;
283: }
284:
285:
286: 287: 288: 289: 290:
291: public function wherePrimary($key)
292: {
293: if (is_array($this->primary) && Nette\Utils\Arrays::isList($key)) {
294: if (isset($key[0]) && is_array($key[0])) {
295: $this->where($this->primary, $key);
296: } else {
297: foreach ($this->primary as $i => $primary) {
298: $this->where($this->name . '.' . $primary, $key[$i]);
299: }
300: }
301: } elseif (is_array($key) && !Nette\Utils\Arrays::isList($key)) {
302: $this->where($key);
303: } else {
304: $this->where($this->name . '.' . $this->getPrimary(), $key);
305: }
306:
307: return $this;
308: }
309:
310:
311: 312: 313: 314: 315: 316: 317:
318: public function where($condition, $parameters = array())
319: {
320: if (is_array($condition) && $parameters === array()) {
321: foreach ($condition as $key => $val) {
322: if (is_int($key)) {
323: $this->where($val);
324: } else {
325: $this->where($key, $val);
326: }
327: }
328: return $this;
329: }
330:
331: $this->emptyResultSet();
332: call_user_func_array(array($this->sqlBuilder, 'addWhere'), func_get_args());
333: return $this;
334: }
335:
336:
337: 338: 339: 340: 341:
342: public function order($columns)
343: {
344: $this->emptyResultSet();
345: call_user_func_array(array($this->sqlBuilder, 'addOrder'), func_get_args());
346: return $this;
347: }
348:
349:
350: 351: 352: 353: 354: 355:
356: public function limit($limit, $offset = NULL)
357: {
358: $this->emptyResultSet();
359: $this->sqlBuilder->setLimit($limit, $offset);
360: return $this;
361: }
362:
363:
364: 365: 366: 367: 368: 369:
370: public function page($page, $itemsPerPage, & $numOfPages = NULL)
371: {
372: if (func_num_args() > 2) {
373: $numOfPages = (int) ceil($this->count('*') / $itemsPerPage);
374: }
375: return $this->limit($itemsPerPage, ($page - 1) * $itemsPerPage);
376: }
377:
378:
379: 380: 381: 382: 383:
384: public function group($columns)
385: {
386: $this->emptyResultSet();
387: call_user_func_array(array($this->sqlBuilder, 'setGroup'), func_get_args());
388: return $this;
389: }
390:
391:
392: 393: 394: 395: 396:
397: public function having($having)
398: {
399: $this->emptyResultSet();
400: call_user_func_array(array($this->sqlBuilder, 'setHaving'), func_get_args());
401: return $this;
402: }
403:
404:
405:
406:
407:
408: 409: 410: 411: 412:
413: public function aggregation($function)
414: {
415: $selection = $this->createSelectionInstance();
416: $selection->getSqlBuilder()->importConditions($this->getSqlBuilder());
417: $selection->select($function);
418: foreach ($selection->fetch() as $val) {
419: return $val;
420: }
421: }
422:
423:
424: 425: 426: 427: 428:
429: public function count($column = NULL)
430: {
431: if (!$column) {
432: $this->execute();
433: return count($this->data);
434: }
435: return $this->aggregation("COUNT($column)");
436: }
437:
438:
439: 440: 441: 442: 443:
444: public function min($column)
445: {
446: return $this->aggregation("MIN($column)");
447: }
448:
449:
450: 451: 452: 453: 454:
455: public function max($column)
456: {
457: return $this->aggregation("MAX($column)");
458: }
459:
460:
461: 462: 463: 464: 465:
466: public function sum($column)
467: {
468: return $this->aggregation("SUM($column)");
469: }
470:
471:
472:
473:
474:
475: protected function execute()
476: {
477: if ($this->rows !== NULL) {
478: return;
479: }
480:
481: $this->observeCache = $this;
482:
483: if ($this->primary === NULL && $this->sqlBuilder->getSelect() === NULL) {
484: throw new Nette\InvalidStateException('Table with no primary key requires an explicit select clause.');
485: }
486:
487: try {
488: $result = $this->query($this->getSql());
489:
490: } catch (Nette\Database\DriverException $exception) {
491: if (!$this->sqlBuilder->getSelect() && $this->previousAccessedColumns) {
492: $this->previousAccessedColumns = FALSE;
493: $this->accessedColumns = array();
494: $result = $this->query($this->getSql());
495: } else {
496: throw $exception;
497: }
498: }
499:
500: $this->rows = array();
501: $usedPrimary = TRUE;
502: foreach ($result->getPdoStatement() as $key => $row) {
503: $row = $this->createRow($result->normalizeRow($row));
504: $primary = $row->getSignature(FALSE);
505: $usedPrimary = $usedPrimary && $primary;
506: $this->rows[$primary ?: $key] = $row;
507: }
508: $this->data = $this->rows;
509:
510: if ($usedPrimary && $this->accessedColumns !== FALSE) {
511: foreach ((array) $this->primary as $primary) {
512: $this->accessedColumns[$primary] = TRUE;
513: }
514: }
515: }
516:
517:
518: protected function createRow(array $row)
519: {
520: return new ActiveRow($row, $this);
521: }
522:
523:
524: public function createSelectionInstance($table = NULL)
525: {
526: return new self($this->context, $this->conventions, $table ?: $this->name, $this->cache ? $this->cache->getStorage() : NULL);
527: }
528:
529:
530: protected function createGroupedSelectionInstance($table, $column)
531: {
532: return new GroupedSelection($this->context, $this->conventions, $table, $column, $this, $this->cache ? $this->cache->getStorage() : NULL);
533: }
534:
535:
536: protected function query($query)
537: {
538: return $this->context->queryArgs($query, $this->sqlBuilder->getParameters());
539: }
540:
541:
542: protected function emptyResultSet($saveCache = TRUE)
543: {
544: if ($this->rows !== NULL && $saveCache) {
545: $this->saveCacheState();
546: }
547:
548: if ($saveCache) {
549:
550: $this->generalCacheTraceKey = NULL;
551: }
552:
553: $this->rows = NULL;
554: $this->specificCacheKey = NULL;
555: $this->generalCacheKey = NULL;
556: $this->refCache['referencingPrototype'] = array();
557: }
558:
559:
560: protected function saveCacheState()
561: {
562: if ($this->observeCache === $this && $this->cache && !$this->sqlBuilder->getSelect() && $this->accessedColumns !== $this->previousAccessedColumns) {
563: $previousAccessed = $this->cache->load($this->getGeneralCacheKey());
564: $accessed = $this->accessedColumns;
565: $needSave = is_array($accessed) && is_array($previousAccessed)
566: ? array_intersect_key($accessed, $previousAccessed) !== $accessed
567: : $accessed !== $previousAccessed;
568:
569: if ($needSave) {
570: $save = is_array($accessed) && is_array($previousAccessed) ? $previousAccessed + $accessed : $accessed;
571: $this->cache->save($this->getGeneralCacheKey(), $save);
572: $this->previousAccessedColumns = NULL;
573: }
574: }
575: }
576:
577:
578: 579: 580: 581:
582: protected function getRefTable(& $refPath)
583: {
584: return $this;
585: }
586:
587:
588: 589: 590:
591: protected function loadRefCache()
592: {
593: }
594:
595:
596: 597: 598: 599: 600:
601: protected function getGeneralCacheKey()
602: {
603: if ($this->generalCacheKey) {
604: return $this->generalCacheKey;
605: }
606:
607: $key = array(__CLASS__, $this->name, $this->sqlBuilder->getConditions());
608: if (!$this->generalCacheTraceKey) {
609: $trace = array();
610: foreach (debug_backtrace(PHP_VERSION_ID >= 50306 ? DEBUG_BACKTRACE_IGNORE_ARGS : FALSE) as $item) {
611: $trace[] = isset($item['file'], $item['line']) ? $item['file'] . $item['line'] : NULL;
612: };
613: $this->generalCacheTraceKey = $trace;
614: }
615:
616: $key[] = $this->generalCacheTraceKey;
617: return $this->generalCacheKey = md5(serialize($key));
618: }
619:
620:
621: 622: 623: 624: 625:
626: protected function getSpecificCacheKey()
627: {
628: if ($this->specificCacheKey) {
629: return $this->specificCacheKey;
630: }
631:
632: return $this->specificCacheKey = md5($this->getSql() . json_encode($this->sqlBuilder->getParameters()));
633: }
634:
635:
636: 637: 638: 639: 640:
641: public function accessColumn($key, $selectColumn = TRUE)
642: {
643: if (!$this->cache) {
644: return;
645: }
646:
647: if ($key === NULL) {
648: $this->accessedColumns = FALSE;
649: $currentKey = key((array) $this->data);
650: } elseif ($this->accessedColumns !== FALSE) {
651: $this->accessedColumns[$key] = $selectColumn;
652: }
653:
654: if ($selectColumn && !$this->sqlBuilder->getSelect() && $this->previousAccessedColumns && ($key === NULL || !isset($this->previousAccessedColumns[$key]))) {
655: $this->previousAccessedColumns = array();
656:
657: if ($this->sqlBuilder->getLimit()) {
658: $generalCacheKey = $this->generalCacheKey;
659: $sqlBuilder = $this->sqlBuilder;
660:
661: $primaryValues = array();
662: foreach ((array) $this->rows as $row) {
663: $primary = $row->getPrimary();
664: $primaryValues[] = is_array($primary) ? array_values($primary) : $primary;
665: }
666:
667: $this->emptyResultSet(FALSE);
668: $this->sqlBuilder = clone $this->sqlBuilder;
669: $this->sqlBuilder->setLimit(NULL, NULL);
670: $this->wherePrimary($primaryValues);
671:
672: $this->generalCacheKey = $generalCacheKey;
673: $this->execute();
674: $this->sqlBuilder = $sqlBuilder;
675: } else {
676: $this->emptyResultSet(FALSE);
677: $this->execute();
678: }
679:
680: $this->dataRefreshed = TRUE;
681:
682:
683: if (isset($currentKey)) {
684: while (key($this->data) !== $currentKey) {
685: next($this->data);
686: }
687: }
688: }
689: }
690:
691:
692: 693: 694: 695:
696: public function removeAccessColumn($key)
697: {
698: if ($this->cache && is_array($this->accessedColumns)) {
699: $this->accessedColumns[$key] = FALSE;
700: }
701: }
702:
703:
704: 705: 706: 707:
708: public function getDataRefreshed()
709: {
710: return $this->dataRefreshed;
711: }
712:
713:
714:
715:
716:
717: 718: 719: 720: 721:
722: public function insert($data)
723: {
724: if ($data instanceof self) {
725: $return = $this->context->queryArgs($this->sqlBuilder->buildInsertQuery() . ' ' . $data->getSql(), $data->getSqlBuilder()->getParameters());
726:
727: } else {
728: if ($data instanceof \Traversable) {
729: $data = iterator_to_array($data);
730: }
731: $return = $this->context->query($this->sqlBuilder->buildInsertQuery() . ' ?values', $data);
732: }
733:
734: $this->loadRefCache();
735:
736: if ($data instanceof self || $this->primary === NULL) {
737: unset($this->refCache['referencing'][$this->getGeneralCacheKey()][$this->getSpecificCacheKey()]);
738: return $return->getRowCount();
739: }
740:
741: $primarySequenceName = $this->getPrimarySequence();
742: $primaryKey = $this->context->getInsertId(
743: !empty($primarySequenceName)
744: ? $this->context->getConnection()->getSupplementalDriver()->delimite($primarySequenceName)
745: : $primarySequenceName
746: );
747: if ($primaryKey === FALSE) {
748: unset($this->refCache['referencing'][$this->getGeneralCacheKey()][$this->getSpecificCacheKey()]);
749: return $return->getRowCount();
750: }
751:
752: if (is_array($this->getPrimary())) {
753: $primaryKey = array();
754:
755: foreach ((array) $this->getPrimary() as $key) {
756: if (!isset($data[$key])) {
757: return $data;
758: }
759:
760: $primaryKey[$key] = $data[$key];
761: }
762: if (count($primaryKey) === 1) {
763: $primaryKey = reset($primaryKey);
764: }
765: }
766:
767: $row = $this->createSelectionInstance()
768: ->select('*')
769: ->wherePrimary($primaryKey)
770: ->fetch();
771:
772: if ($this->rows !== NULL) {
773: if ($signature = $row->getSignature(FALSE)) {
774: $this->rows[$signature] = $row;
775: $this->data[$signature] = $row;
776: } else {
777: $this->rows[] = $row;
778: $this->data[] = $row;
779: }
780: }
781:
782: return $row;
783: }
784:
785:
786: 787: 788: 789: 790: 791:
792: public function update($data)
793: {
794: if ($data instanceof \Traversable) {
795: $data = iterator_to_array($data);
796:
797: } elseif (!is_array($data)) {
798: throw new Nette\InvalidArgumentException;
799: }
800:
801: if (!$data) {
802: return 0;
803: }
804:
805: return $this->context->queryArgs(
806: $this->sqlBuilder->buildUpdateQuery(),
807: array_merge(array($data), $this->sqlBuilder->getParameters())
808: )->getRowCount();
809: }
810:
811:
812: 813: 814: 815:
816: public function delete()
817: {
818: return $this->query($this->sqlBuilder->buildDeleteQuery())->getRowCount();
819: }
820:
821:
822:
823:
824:
825: 826: 827: 828: 829: 830: 831:
832: public function getReferencedTable(ActiveRow $row, $table, $column = NULL)
833: {
834: if (!$column) {
835: $belongsTo = $this->conventions->getBelongsToReference($this->name, $table);
836: if (!$belongsTo) {
837: return FALSE;
838: }
839: list($table, $column) = $belongsTo;
840: }
841: if (!$row->accessColumn($column)) {
842: return FALSE;
843: }
844:
845: $checkPrimaryKey = $row[$column];
846:
847: $referenced = & $this->refCache['referenced'][$this->getSpecificCacheKey()]["$table.$column"];
848: $selection = & $referenced['selection'];
849: $cacheKeys = & $referenced['cacheKeys'];
850: if ($selection === NULL || ($checkPrimaryKey !== NULL && !isset($cacheKeys[$checkPrimaryKey]))) {
851: $this->execute();
852: $cacheKeys = array();
853: foreach ($this->rows as $row) {
854: if ($row[$column] === NULL) {
855: continue;
856: }
857:
858: $key = $row[$column];
859: $cacheKeys[$key] = TRUE;
860: }
861:
862: if ($cacheKeys) {
863: $selection = $this->createSelectionInstance($table);
864: $selection->where($selection->getPrimary(), array_keys($cacheKeys));
865: } else {
866: $selection = array();
867: }
868: }
869:
870: return isset($selection[$checkPrimaryKey]) ? $selection[$checkPrimaryKey] : NULL;
871: }
872:
873:
874: 875: 876: 877: 878: 879: 880:
881: public function getReferencingTable($table, $column, $active = NULL)
882: {
883: if (strpos($table, '.') !== FALSE) {
884: list($table, $column) = explode('.', $table);
885: } elseif (!$column) {
886: $hasMany = $this->conventions->getHasManyReference($this->name, $table);
887: if (!$hasMany) {
888: return FALSE;
889: }
890: list($table, $column) = $hasMany;
891: }
892:
893: $prototype = & $this->refCache['referencingPrototype'][$this->getSpecificCacheKey()]["$table.$column"];
894: if (!$prototype) {
895: $prototype = $this->createGroupedSelectionInstance($table, $column);
896: $prototype->where("$table.$column", array_keys((array) $this->rows));
897: }
898:
899: $clone = clone $prototype;
900: $clone->setActive($active);
901: return $clone;
902: }
903:
904:
905:
906:
907:
908: public function rewind()
909: {
910: $this->execute();
911: $this->keys = array_keys($this->data);
912: reset($this->keys);
913: }
914:
915:
916:
917: public function current()
918: {
919: if (($key = current($this->keys)) !== FALSE) {
920: return $this->data[$key];
921: } else {
922: return FALSE;
923: }
924: }
925:
926:
927: 928: 929:
930: public function key()
931: {
932: return current($this->keys);
933: }
934:
935:
936: public function next()
937: {
938: next($this->keys);
939: }
940:
941:
942: public function valid()
943: {
944: return current($this->keys) !== FALSE;
945: }
946:
947:
948:
949:
950:
951: 952: 953: 954: 955: 956:
957: public function offsetSet($key, $value)
958: {
959: $this->execute();
960: $this->rows[$key] = $value;
961: }
962:
963:
964: 965: 966: 967: 968:
969: public function offsetGet($key)
970: {
971: $this->execute();
972: return $this->rows[$key];
973: }
974:
975:
976: 977: 978: 979: 980:
981: public function offsetExists($key)
982: {
983: $this->execute();
984: return isset($this->rows[$key]);
985: }
986:
987:
988: 989: 990: 991: 992:
993: public function offsetUnset($key)
994: {
995: $this->execute();
996: unset($this->rows[$key], $this->data[$key]);
997: }
998:
999: }
1000: