1: <?php
2:
3: 4: 5: 6: 7: 8: 9: 10:
11:
12: namespace Nette\Database\Table;
13:
14: use Nette,
15: PDO;
16:
17:
18:
19: 20: 21: 22: 23: 24: 25: 26: 27:
28: class Selection extends Nette\Object implements \Iterator, \ArrayAccess, \Countable
29: {
30:
31: protected $connection;
32:
33:
34: protected $name;
35:
36:
37: protected $primary;
38:
39:
40: protected $rows;
41:
42:
43: protected $data;
44:
45:
46: protected $select = array();
47:
48:
49: protected $where = array();
50:
51:
52: protected $conditions = array();
53:
54:
55: protected $parameters = array();
56:
57:
58: protected $order = array();
59:
60:
61: protected $limit = NULL;
62:
63:
64: protected $offset = NULL;
65:
66:
67: protected $group = '';
68:
69:
70: protected $having = '';
71:
72:
73: protected $checkReferenceNewKeys = FALSE;
74:
75:
76: protected $referenced = array();
77:
78:
79: protected $referencing = array();
80:
81:
82: protected $accessed;
83:
84:
85: protected $prevAccessed;
86:
87:
88: protected $keys = array();
89:
90:
91: protected $delimitedName;
92:
93:
94: protected $delimitedPrimary;
95:
96:
97:
98: public function __construct($table, Nette\Database\Connection $connection)
99: {
100: $this->name = $table;
101: $this->connection = $connection;
102: $this->primary = $connection->getDatabaseReflection()->getPrimary($table);
103: $this->delimitedName = $this->tryDelimite($this->name);
104: $this->delimitedPrimary = $connection->getSupplementalDriver()->delimite($this->primary);
105: }
106:
107:
108:
109: 110: 111:
112: public function __destruct()
113: {
114: $cache = $this->connection->getCache();
115: if ($cache && !$this->select && $this->rows !== NULL) {
116: $cache->save(array(__CLASS__, $this->name, $this->conditions), $this->accessed);
117: }
118: $this->rows = NULL;
119: }
120:
121:
122:
123: 124: 125:
126: public function getConnection()
127: {
128: return $this->connection;
129: }
130:
131:
132:
133: 134: 135:
136: public function getName()
137: {
138: return $this->name;
139: }
140:
141:
142:
143: 144: 145:
146: public function getPrimary()
147: {
148: return $this->primary;
149: }
150:
151:
152:
153: 154: 155: 156: 157:
158: public function get($key)
159: {
160:
161: $clone = clone $this;
162: $clone->where($this->delimitedPrimary, $key);
163: return $clone->fetch();
164: }
165:
166:
167:
168: 169: 170: 171: 172:
173: public function select($columns)
174: {
175: $this->__destruct();
176: $this->select[] = $columns;
177: return $this;
178: }
179:
180:
181:
182: 183: 184: 185: 186:
187: public function find($key)
188: {
189: return $this->where($this->delimitedPrimary, $key);
190: }
191:
192:
193:
194: 195: 196: 197: 198: 199: 200:
201: public function where($condition, $parameters = array())
202: {
203: if (is_array($condition)) {
204: foreach ($condition as $key => $val) {
205: if (is_int($key)) {
206: $this->where($val);
207: } else {
208: $this->where($key, $val);
209: }
210: }
211: return $this;
212: }
213:
214: $hash = md5(json_encode(func_get_args()));
215: if (isset($this->conditions[$hash])) {
216: return $this;
217: }
218:
219: $this->__destruct();
220:
221: $this->conditions[$hash] = $condition;
222: $condition = $this->removeExtraTables($condition);
223: $condition = $this->tryDelimite($condition);
224:
225: $args = func_num_args();
226: if ($args !== 2 || strpbrk($condition, '?:')) {
227: if ($args !== 2 || !is_array($parameters)) {
228: $parameters = func_get_args();
229: array_shift($parameters);
230: }
231: $this->parameters = array_merge($this->parameters, $parameters);
232:
233: } elseif ($parameters === NULL) {
234: $condition .= ' IS NULL';
235:
236: } elseif ($parameters instanceof Selection) {
237: $clone = clone $parameters;
238: if (!$clone->select) {
239: $clone->select = array($clone->primary);
240: }
241: if ($this->connection->getAttribute(PDO::ATTR_DRIVER_NAME) !== 'mysql') {
242: $condition .= ' IN (' . $clone->getSql() . ')';
243: } else {
244: $in = array();
245: foreach ($clone as $row) {
246: $this->parameters[] = array_values(iterator_to_array($row));
247: $in[] = (count($row) === 1 ? '?' : '(?)');
248: }
249: $condition .= ' IN (' . ($in ? implode(', ', $in) : 'NULL') . ')';
250: }
251:
252: } elseif (!is_array($parameters)) {
253: $condition .= ' = ?';
254: $this->parameters[] = $parameters;
255:
256: } else {
257: if ($parameters) {
258: $condition .= " IN (?)";
259: $this->parameters[] = $parameters;
260: } else {
261: $condition .= " IN (NULL)";
262: }
263: }
264:
265: $this->where[] = $condition;
266: return $this;
267: }
268:
269:
270:
271: 272: 273: 274: 275:
276: public function order($columns)
277: {
278: $this->rows = NULL;
279: $this->order[] = $columns;
280: return $this;
281: }
282:
283:
284:
285: 286: 287: 288: 289: 290:
291: public function limit($limit, $offset = NULL)
292: {
293: $this->rows = NULL;
294: $this->limit = $limit;
295: $this->offset = $offset;
296: return $this;
297: }
298:
299:
300:
301: 302: 303: 304: 305: 306:
307: public function page($page, $itemsPerPage)
308: {
309: $this->rows = NULL;
310: $this->limit = $itemsPerPage;
311: $this->offset = ($page - 1) * $itemsPerPage;
312: return $this;
313: }
314:
315:
316:
317: 318: 319: 320: 321: 322:
323: public function group($columns, $having = '')
324: {
325: $this->__destruct();
326: $this->group = $columns;
327: $this->having = $having;
328: return $this;
329: }
330:
331:
332:
333: 334: 335: 336: 337:
338: public function aggregation($function)
339: {
340: $selection = new Selection($this->name, $this->connection);
341: $selection->where = $this->where;
342: $selection->parameters = $this->parameters;
343: $selection->conditions = $this->conditions;
344:
345: $selection->select($function);
346:
347: foreach ($selection->fetch() as $val) {
348: return $val;
349: }
350: }
351:
352:
353:
354: 355: 356: 357: 358:
359: public function count($column = '')
360: {
361: if (!$column) {
362: $this->execute();
363: return count($this->data);
364: }
365: return $this->aggregation("COUNT($column)");
366: }
367:
368:
369:
370: 371: 372: 373: 374:
375: public function min($column)
376: {
377: return $this->aggregation("MIN($column)");
378: }
379:
380:
381:
382: 383: 384: 385: 386:
387: public function max($column)
388: {
389: return $this->aggregation("MAX($column)");
390: }
391:
392:
393:
394: 395: 396: 397: 398:
399: public function sum($column)
400: {
401: return $this->aggregation("SUM($column)");
402: }
403:
404:
405:
406: 407: 408: 409:
410: public function getSql()
411: {
412: $join = $this->createJoins(implode(',', $this->conditions), TRUE)
413: + $this->createJoins(implode(',', $this->select) . ",$this->group,$this->having," . implode(',', $this->order));
414:
415: $cache = $this->connection->getCache();
416: if ($this->rows === NULL && $cache && !is_string($this->prevAccessed)) {
417: $this->accessed = $this->prevAccessed = $cache->load(array(__CLASS__, $this->name, $this->conditions));
418: }
419:
420: $prefix = $join ? "$this->delimitedName." : '';
421: if ($this->select) {
422: $cols = $this->tryDelimite($this->removeExtraTables(implode(', ', $this->select)));
423:
424: } elseif ($this->prevAccessed) {
425: $cols = array_map(array($this->connection->getSupplementalDriver(), 'delimite'), array_keys(array_filter($this->prevAccessed)));
426: $cols = $prefix . implode(', ' . $prefix, $cols);
427:
428: } else {
429: $cols = $prefix . '*';
430: }
431:
432: return "SELECT{$this->topString()} $cols FROM $this->delimitedName" . implode($join) . $this->whereString();
433: }
434:
435:
436:
437: protected function createJoins($val, $inner = FALSE)
438: {
439: $driver = $this->connection->getSupplementalDriver();
440: $reflection = $this->connection->getDatabaseReflection();
441: $joins = array();
442: preg_match_all('~\\b([a-z][\\w.:]*[.:])([a-z]\\w*|\*)(\\s+IS\\b|\\s*<=>)?~i', $val, $matches);
443: foreach ($matches[1] as $names) {
444: $parent = $this->name;
445: if ($names !== "$parent.") {
446: preg_match_all('~\\b([a-z][\\w]*|\*)([.:])~i', $names, $matches, PREG_SET_ORDER);
447: foreach ($matches as $match) {
448: list(, $name, $delimiter) = $match;
449:
450: if ($delimiter === ':') {
451: list($table, $primary) = $reflection->getHasManyReference($parent, $name);
452: $column = $reflection->getPrimary($parent);
453: } else {
454: list($table, $column) = $reflection->getBelongsToReference($parent, $name);
455: $primary = $reflection->getPrimary($table);
456: }
457:
458: $joins[$name] = ' '
459: . (!isset($joins[$name]) && $inner && !isset($match[3]) ? 'INNER' : 'LEFT')
460: . ' JOIN ' . $driver->delimite($table) . ($table !== $name ? ' AS ' . $driver->delimite($name) : '')
461: . ' ON ' . $driver->delimite($parent) . '.' . $driver->delimite($column)
462: . ' = ' . $driver->delimite($name) . '.' . $driver->delimite($primary);
463:
464: $parent = $name;
465: }
466: }
467: }
468: return $joins;
469: }
470:
471:
472:
473: 474: 475: 476:
477: protected function execute()
478: {
479: if ($this->rows !== NULL) {
480: return;
481: }
482:
483: try {
484: $result = $this->query($this->getSql());
485:
486: } catch (\PDOException $exception) {
487: if (!$this->select && $this->prevAccessed) {
488: $this->prevAccessed = '';
489: $this->accessed = array();
490: $result = $this->query($this->getSql());
491: } else {
492: throw $exception;
493: }
494: }
495:
496: $this->rows = array();
497: $result->setFetchMode(PDO::FETCH_ASSOC);
498: foreach ($result as $key => $row) {
499: $row = $result->normalizeRow($row);
500: $this->rows[isset($row[$this->primary]) ? $row[$this->primary] : $key] = $this->createRow($row);
501: }
502: $this->data = $this->rows;
503:
504: if (isset($row[$this->primary]) && !is_string($this->accessed)) {
505: $this->accessed[$this->primary] = TRUE;
506: }
507: }
508:
509:
510:
511: protected function createRow(array $row)
512: {
513: return new ActiveRow($row, $this);
514: }
515:
516:
517:
518: protected function whereString()
519: {
520: $return = '';
521: $driver = $this->connection->getAttribute(PDO::ATTR_DRIVER_NAME);
522: $where = $this->where;
523: if ($this->limit !== NULL && $driver === 'oci') {
524: $where[] = ($this->offset ? "rownum > $this->offset AND " : '') . 'rownum <= ' . ($this->limit + $this->offset);
525: }
526: if ($where) {
527: $return .= ' WHERE (' . implode(') AND (', $where) . ')';
528: }
529: if ($this->group) {
530: $return .= ' GROUP BY '. $this->tryDelimite($this->removeExtraTables($this->group));
531: }
532: if ($this->having) {
533: $return .= ' HAVING '. $this->tryDelimite($this->removeExtraTables($this->having));
534: }
535: if ($this->order) {
536: $return .= ' ORDER BY ' . $this->tryDelimite($this->removeExtraTables(implode(', ', $this->order)));
537: }
538: if ($this->limit !== NULL && $driver !== 'oci' && $driver !== 'dblib') {
539: $return .= " LIMIT $this->limit";
540: if ($this->offset !== NULL) {
541: $return .= " OFFSET $this->offset";
542: }
543: }
544: return $return;
545: }
546:
547:
548:
549: protected function topString()
550: {
551: if ($this->limit !== NULL && $this->connection->getAttribute(PDO::ATTR_DRIVER_NAME) === 'dblib') {
552: return " TOP ($this->limit)";
553: }
554: return '';
555: }
556:
557:
558:
559: protected function tryDelimite($s)
560: {
561: $driver = $this->connection->getSupplementalDriver();
562: return preg_replace_callback('#(?<=[^\w`"\[]|^)[a-z_][a-z0-9_]*(?=[^\w`"(\]]|$)#i', function($m) use ($driver) {
563: return strtoupper($m[0]) === $m[0] ? $m[0] : $driver->delimite($m[0]);
564: }, $s);
565: }
566:
567:
568:
569: protected function removeExtraTables($expression)
570: {
571: return preg_replace('~(?:\\b[a-z_][a-z0-9_.:]*[.:])?([a-z_][a-z0-9_]*)[.:]([a-z_*])~i', '\\1.\\2', $expression);
572: }
573:
574:
575:
576: protected function query($query)
577: {
578: return $this->connection->queryArgs($query, $this->parameters);
579: }
580:
581:
582:
583: 584: 585: 586: 587: 588:
589: public function access($key, $cache = TRUE)
590: {
591: if ($cache === NULL) {
592: if (is_array($this->accessed)) {
593: $this->accessed[$key] = FALSE;
594: }
595: return FALSE;
596: }
597:
598: if ($key === NULL) {
599: $this->accessed = '';
600:
601: } elseif (!is_string($this->accessed)) {
602: $this->accessed[$key] = $cache;
603: }
604:
605: if ($cache && !$this->select && $this->prevAccessed && ($key === NULL || !isset($this->prevAccessed[$key]))) {
606: $this->prevAccessed = '';
607: $this->__destruct();
608: return TRUE;
609: }
610: return FALSE;
611: }
612:
613:
614:
615:
616:
617:
618:
619: 620: 621: 622: 623:
624: public function insert($data)
625: {
626: if ($data instanceof Selection) {
627: $data = $data->getSql();
628:
629: } elseif ($data instanceof \Traversable) {
630: $data = iterator_to_array($data);
631: }
632:
633: $return = $this->connection->query("INSERT INTO $this->delimitedName", $data);
634:
635: if (!is_array($data)) {
636: return $return->rowCount();
637: }
638:
639: $this->checkReferenceNewKeys = TRUE;
640:
641: if (!isset($data[$this->primary]) && ($id = $this->connection->lastInsertId())) {
642: $data[$this->primary] = $id;
643: return $this->rows[$id] = new ActiveRow($data, $this);
644:
645: } else {
646: return new ActiveRow($data, $this);
647:
648: }
649: }
650:
651:
652:
653: 654: 655: 656: 657:
658: public function update($data)
659: {
660: if ($data instanceof \Traversable) {
661: $data = iterator_to_array($data);
662:
663: } elseif (!is_array($data)) {
664: throw new Nette\InvalidArgumentException;
665: }
666:
667: if (!$data) {
668: return 0;
669: }
670:
671: return $this->connection->queryArgs(
672: 'UPDATE' . $this->topString() . " $this->delimitedName SET ?" . $this->whereString(),
673: array_merge(array($data), $this->parameters)
674: )->rowCount();
675: }
676:
677:
678:
679: 680: 681: 682:
683: public function delete()
684: {
685: return $this->query(
686: 'DELETE' . $this->topString() . " FROM $this->delimitedName" . $this->whereString()
687: )->rowCount();
688: }
689:
690:
691:
692:
693:
694:
695:
696: 697: 698: 699: 700: 701: 702:
703: public function getReferencedTable($table, $column, $checkReferenceNewKeys = FALSE)
704: {
705: $referenced = & $this->referenced["$table.$column"];
706: if ($referenced === NULL || $checkReferenceNewKeys || $this->checkReferenceNewKeys) {
707: $keys = array();
708: foreach ($this->rows as $row) {
709: if ($row[$column] === NULL)
710: continue;
711:
712: $key = $row[$column] instanceof ActiveRow ? $row[$column]->getPrimary() : $row[$column];
713: $keys[$key] = TRUE;
714: }
715:
716: if ($referenced !== NULL && $keys === array_keys($this->rows)) {
717: $this->checkReferenceNewKeys = FALSE;
718: return $referenced;
719: }
720:
721: if ($keys) {
722: $referenced = new Selection($table, $this->connection);
723: $referenced->where($table . '.' . $referenced->primary, array_keys($keys));
724: } else {
725: $referenced = array();
726: }
727: }
728:
729: return $referenced;
730: }
731:
732:
733:
734: 735: 736: 737: 738: 739: 740: 741:
742: public function getReferencingTable($table, $column, $active = NULL, $forceNewInstance = FALSE)
743: {
744: $referencing = & $this->referencing["$table:$column"];
745: if (!$referencing || $forceNewInstance) {
746: $referencing = new GroupedSelection($table, $this, $column);
747: $referencing->where("$table.$column", array_keys((array) $this->rows));
748: }
749:
750: return $referencing->setActive($active);
751: }
752:
753:
754:
755:
756:
757:
758:
759: public function rewind()
760: {
761: $this->execute();
762: $this->keys = array_keys($this->data);
763: reset($this->keys);
764: }
765:
766:
767:
768:
769: public function current()
770: {
771: return $this->data[current($this->keys)];
772: }
773:
774:
775:
776: 777: 778:
779: public function key()
780: {
781: return current($this->keys);
782: }
783:
784:
785:
786: public function next()
787: {
788: next($this->keys);
789: }
790:
791:
792:
793: public function valid()
794: {
795: return current($this->keys) !== FALSE;
796: }
797:
798:
799:
800:
801:
802:
803:
804: 805: 806: 807: 808: 809:
810: public function offsetSet($key, $value)
811: {
812: $this->execute();
813: $this->data[$key] = $value;
814: }
815:
816:
817:
818: 819: 820: 821: 822:
823: public function offsetGet($key)
824: {
825: $this->execute();
826: return $this->data[$key];
827: }
828:
829:
830:
831: 832: 833: 834: 835:
836: public function offsetExists($key)
837: {
838: $this->execute();
839: return isset($this->data[$key]);
840: }
841:
842:
843:
844: 845: 846: 847: 848:
849: public function offsetUnset($key)
850: {
851: $this->execute();
852: unset($this->data[$key]);
853: }
854:
855:
856:
857: 858: 859: 860:
861: public function fetch()
862: {
863: $this->execute();
864: $return = current($this->data);
865: next($this->data);
866: return $return;
867: }
868:
869:
870:
871: 872: 873: 874: 875: 876:
877: public function fetchPairs($key, $value = '')
878: {
879: $return = array();
880:
881: foreach ($this as $row) {
882: $return[is_object($row[$key]) ? (string) $row[$key] : $row[$key]] = ($value !== '' ? $row[$value] : $row);
883: }
884: return $return;
885: }
886:
887: }
888: