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