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