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