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