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