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($need = TRUE)
127: {
128: if ($this->primary === NULL && $need) {
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: protected function createRow(array $row)
595: {
596: return new ActiveRow($row, $this);
597: }
598:
599:
600: public function createSelectionInstance($table = NULL)
601: {
602: return new self($this->context, $this->conventions, $table ?: $this->name, $this->cache ? $this->cache->getStorage() : NULL);
603: }
604:
605:
606: protected function createGroupedSelectionInstance($table, $column)
607: {
608: return new GroupedSelection($this->context, $this->conventions, $table, $column, $this, $this->cache ? $this->cache->getStorage() : NULL);
609: }
610:
611:
612: protected function query($query)
613: {
614: return $this->context->queryArgs($query, $this->sqlBuilder->getParameters());
615: }
616:
617:
618: protected function emptyResultSet($clearCache = TRUE, $deleteRererencedCache = TRUE)
619: {
620: if ($this->rows !== NULL && $clearCache) {
621: $this->saveCacheState();
622: }
623:
624: if ($clearCache) {
625:
626: $this->previousAccessedColumns = NULL;
627: $this->generalCacheKey = NULL;
628: }
629:
630: $this->rows = NULL;
631: $this->specificCacheKey = NULL;
632: $this->refCache['referencingPrototype'] = [];
633: if ($deleteRererencedCache) {
634: $this->refCache['referenced'] = [];
635: }
636: }
637:
638:
639: protected function saveCacheState()
640: {
641: if ($this->observeCache === $this && $this->cache && !$this->sqlBuilder->getSelect() && $this->accessedColumns !== $this->previousAccessedColumns) {
642: $previousAccessed = $this->cache->load($this->getGeneralCacheKey());
643: $accessed = $this->accessedColumns;
644: $needSave = is_array($accessed) && is_array($previousAccessed)
645: ? array_intersect_key($accessed, $previousAccessed) !== $accessed
646: : $accessed !== $previousAccessed;
647:
648: if ($needSave) {
649: $save = is_array($accessed) && is_array($previousAccessed) ? $previousAccessed + $accessed : $accessed;
650: $this->cache->save($this->getGeneralCacheKey(), $save);
651: $this->previousAccessedColumns = NULL;
652: }
653: }
654: }
655:
656:
657: 658: 659: 660:
661: protected function getRefTable(& $refPath)
662: {
663: return $this;
664: }
665:
666:
667: 668: 669:
670: protected function loadRefCache()
671: {
672: }
673:
674:
675: 676: 677: 678: 679:
680: protected function getGeneralCacheKey()
681: {
682: if ($this->generalCacheKey) {
683: return $this->generalCacheKey;
684: }
685:
686: $key = [__CLASS__, $this->name, $this->sqlBuilder->getConditions()];
687: $trace = [];
688: foreach (debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS) as $item) {
689: $trace[] = isset($item['file'], $item['line']) ? $item['file'] . $item['line'] : NULL;
690: };
691:
692: $key[] = $trace;
693: return $this->generalCacheKey = md5(serialize($key));
694: }
695:
696:
697: 698: 699: 700: 701:
702: protected function getSpecificCacheKey()
703: {
704: if ($this->specificCacheKey) {
705: return $this->specificCacheKey;
706: }
707:
708: return $this->specificCacheKey = $this->sqlBuilder->getSelectQueryHash($this->getPreviousAccessedColumns());
709: }
710:
711:
712: 713: 714: 715: 716: 717:
718: public function accessColumn($key, $selectColumn = TRUE)
719: {
720: if (!$this->cache) {
721: return FALSE;
722: }
723:
724: if ($key === NULL) {
725: $this->accessedColumns = FALSE;
726: $currentKey = key((array) $this->data);
727: } elseif ($this->accessedColumns !== FALSE) {
728: $this->accessedColumns[$key] = $selectColumn;
729: }
730:
731: if ($selectColumn && $this->previousAccessedColumns && ($key === NULL || !isset($this->previousAccessedColumns[$key])) && !$this->sqlBuilder->getSelect()) {
732: if ($this->sqlBuilder->getLimit()) {
733: $generalCacheKey = $this->generalCacheKey;
734: $sqlBuilder = $this->sqlBuilder;
735:
736: $primaryValues = [];
737: foreach ((array) $this->rows as $row) {
738: $primary = $row->getPrimary();
739: $primaryValues[] = is_array($primary) ? array_values($primary) : $primary;
740: }
741:
742: $this->emptyResultSet(FALSE);
743: $this->sqlBuilder = clone $this->sqlBuilder;
744: $this->sqlBuilder->setLimit(NULL, NULL);
745: $this->wherePrimary($primaryValues);
746:
747: $this->generalCacheKey = $generalCacheKey;
748: $this->previousAccessedColumns = [];
749: $this->execute();
750: $this->sqlBuilder = $sqlBuilder;
751: } else {
752: $this->emptyResultSet(FALSE);
753: $this->previousAccessedColumns = [];
754: $this->execute();
755: }
756:
757: $this->dataRefreshed = TRUE;
758:
759:
760: if (isset($currentKey)) {
761: while (key($this->data) !== NULL && key($this->data) !== $currentKey) {
762: next($this->data);
763: }
764: }
765: }
766: return $this->dataRefreshed;
767: }
768:
769:
770: 771: 772: 773:
774: public function removeAccessColumn($key)
775: {
776: if ($this->cache && is_array($this->accessedColumns)) {
777: $this->accessedColumns[$key] = FALSE;
778: }
779: }
780:
781:
782: 783: 784: 785:
786: public function getDataRefreshed()
787: {
788: return $this->dataRefreshed;
789: }
790:
791:
792:
793:
794:
795: 796: 797: 798: 799:
800: public function insert($data)
801: {
802: if ($data instanceof self) {
803: $return = $this->context->queryArgs($this->sqlBuilder->buildInsertQuery() . ' ' . $data->getSql(), $data->getSqlBuilder()->getParameters());
804:
805: } else {
806: if ($data instanceof \Traversable) {
807: $data = iterator_to_array($data);
808: }
809: $return = $this->context->query($this->sqlBuilder->buildInsertQuery() . ' ?values', $data);
810: }
811:
812: $this->loadRefCache();
813:
814: if ($data instanceof self || $this->primary === NULL) {
815: unset($this->refCache['referencing'][$this->getGeneralCacheKey()][$this->getSpecificCacheKey()]);
816: return $return->getRowCount();
817: }
818:
819: $primaryKey = $this->context->getInsertId(
820: ($tmp = $this->getPrimarySequence())
821: ? implode('.', array_map([$this->context->getConnection()->getSupplementalDriver(), 'delimite'], explode('.', $tmp)))
822: : NULL
823: );
824: if (!$primaryKey) {
825: unset($this->refCache['referencing'][$this->getGeneralCacheKey()][$this->getSpecificCacheKey()]);
826: return $return->getRowCount();
827: }
828:
829: if (is_array($this->getPrimary())) {
830: $primaryKey = [];
831:
832: foreach ((array) $this->getPrimary() as $key) {
833: if (!isset($data[$key])) {
834: return $data;
835: }
836:
837: $primaryKey[$key] = $data[$key];
838: }
839: if (count($primaryKey) === 1) {
840: $primaryKey = reset($primaryKey);
841: }
842: }
843:
844: $row = $this->createSelectionInstance()
845: ->select('*')
846: ->wherePrimary($primaryKey)
847: ->fetch();
848:
849: if ($this->rows !== NULL) {
850: if ($signature = $row->getSignature(FALSE)) {
851: $this->rows[$signature] = $row;
852: $this->data[$signature] = $row;
853: } else {
854: $this->rows[] = $row;
855: $this->data[] = $row;
856: }
857: }
858:
859: return $row;
860: }
861:
862:
863: 864: 865: 866: 867: 868:
869: public function update($data)
870: {
871: if ($data instanceof \Traversable) {
872: $data = iterator_to_array($data);
873:
874: } elseif (!is_array($data)) {
875: throw new Nette\InvalidArgumentException;
876: }
877:
878: if (!$data) {
879: return 0;
880: }
881:
882: return $this->context->queryArgs(
883: $this->sqlBuilder->buildUpdateQuery(),
884: array_merge([$data], $this->sqlBuilder->getParameters())
885: )->getRowCount();
886: }
887:
888:
889: 890: 891: 892:
893: public function delete()
894: {
895: return $this->query($this->sqlBuilder->buildDeleteQuery())->getRowCount();
896: }
897:
898:
899:
900:
901:
902: 903: 904: 905: 906: 907: 908:
909: public function getReferencedTable(ActiveRow $row, $table, $column = NULL)
910: {
911: if (!$column) {
912: $belongsTo = $this->conventions->getBelongsToReference($this->name, $table);
913: if (!$belongsTo) {
914: return FALSE;
915: }
916: list($table, $column) = $belongsTo;
917: }
918: if (!$row->accessColumn($column)) {
919: return FALSE;
920: }
921:
922: $checkPrimaryKey = $row[$column];
923:
924: $referenced = & $this->refCache['referenced'][$this->getSpecificCacheKey()]["$table.$column"];
925: $selection = & $referenced['selection'];
926: $cacheKeys = & $referenced['cacheKeys'];
927: if ($selection === NULL || ($checkPrimaryKey !== NULL && !isset($cacheKeys[$checkPrimaryKey]))) {
928: $this->execute();
929: $cacheKeys = [];
930: foreach ($this->rows as $row) {
931: if ($row[$column] === NULL) {
932: continue;
933: }
934:
935: $key = $row[$column];
936: $cacheKeys[$key] = TRUE;
937: }
938:
939: if ($cacheKeys) {
940: $selection = $this->createSelectionInstance($table);
941: $selection->where($selection->getPrimary(), array_keys($cacheKeys));
942: } else {
943: $selection = [];
944: }
945: }
946:
947: return isset($selection[$checkPrimaryKey]) ? $selection[$checkPrimaryKey] : NULL;
948: }
949:
950:
951: 952: 953: 954: 955: 956: 957:
958: public function getReferencingTable($table, $column, $active = NULL)
959: {
960: if (strpos($table, '.') !== FALSE) {
961: list($table, $column) = explode('.', $table);
962: } elseif (!$column) {
963: $hasMany = $this->conventions->getHasManyReference($this->name, $table);
964: if (!$hasMany) {
965: return FALSE;
966: }
967: list($table, $column) = $hasMany;
968: }
969:
970: $prototype = & $this->refCache['referencingPrototype'][$this->getSpecificCacheKey()]["$table.$column"];
971: if (!$prototype) {
972: $prototype = $this->createGroupedSelectionInstance($table, $column);
973: $prototype->where("$table.$column", array_keys((array) $this->rows));
974: }
975:
976: $clone = clone $prototype;
977: $clone->setActive($active);
978: return $clone;
979: }
980:
981:
982:
983:
984:
985: public function rewind()
986: {
987: $this->execute();
988: $this->keys = array_keys($this->data);
989: reset($this->keys);
990: }
991:
992:
993:
994: public function current()
995: {
996: if (($key = current($this->keys)) !== FALSE) {
997: return $this->data[$key];
998: } else {
999: return FALSE;
1000: }
1001: }
1002:
1003:
1004: 1005: 1006:
1007: public function key()
1008: {
1009: return current($this->keys);
1010: }
1011:
1012:
1013: public function next()
1014: {
1015: do {
1016: next($this->keys);
1017: } while (($key = current($this->keys)) !== FALSE && !isset($this->data[$key]));
1018: }
1019:
1020:
1021: public function valid()
1022: {
1023: return current($this->keys) !== FALSE;
1024: }
1025:
1026:
1027:
1028:
1029:
1030: 1031: 1032: 1033: 1034: 1035:
1036: public function offsetSet($key, $value)
1037: {
1038: $this->execute();
1039: $this->rows[$key] = $value;
1040: }
1041:
1042:
1043: 1044: 1045: 1046: 1047:
1048: public function offsetGet($key)
1049: {
1050: $this->execute();
1051: return $this->rows[$key];
1052: }
1053:
1054:
1055: 1056: 1057: 1058: 1059:
1060: public function offsetExists($key)
1061: {
1062: $this->execute();
1063: return isset($this->rows[$key]);
1064: }
1065:
1066:
1067: 1068: 1069: 1070: 1071:
1072: public function offsetUnset($key)
1073: {
1074: $this->execute();
1075: unset($this->rows[$key], $this->data[$key]);
1076: }
1077:
1078: }
1079: