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