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