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