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