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