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