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