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