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