Database\Selector\TableSelection.php

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

Classes

Interfaces
