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