Packages

  • Nette
    • Application
      • Application\Diagnostics
      • Application\Responses
      • Application\Routers
      • Application\UI
    • Caching
      • Caching\Storages
    • ComponentModel
    • Config
    • Database
      • Database\Diagnostics
      • Database\Drivers
      • Database\Reflection
      • Database\Table
    • DI
    • Diagnostics
    • Forms
      • Forms\Controls
      • Forms\Rendering
    • Http
    • Iterators
    • Latte
      • Latte\Macros
    • Loaders
    • Localization
    • Mail
    • Reflection
    • Security
    • Templating
    • Utils
  • NetteModule
  • None
  • PHP

Classes

  • NGroupedTableSelection
  • NTableRow
  • NTableSelection
  • Overview
  • Package
  • Class
  • Tree
  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:  * @package Nette\Database\Table
 11:  */
 12: 
 13: 
 14: 
 15: /**
 16:  * Filtered table representation.
 17:  * Selector is based on the great library NotORM http://www.notorm.com written by Jakub Vrana.
 18:  *
 19:  * @author     Jakub Vrana
 20:  * @package Nette\Database\Table
 21:  */
 22: class NTableSelection extends NObject implements Iterator, ArrayAccess, Countable
 23: {
 24:     /** @var NConnection */
 25:     public $connection;
 26: 
 27:     /** @var string table name */
 28:     public $name;
 29: 
 30:     /** @var string primary key field name */
 31:     public $primary;
 32: 
 33:     /** @var array of [primary key => TableRow] readed from database */
 34:     protected $rows;
 35: 
 36:     /** @var array of [primary key => TableRow] modifiable */
 37:     protected $data;
 38: 
 39:     /** @var array of column to select */
 40:     protected $select = array();
 41: 
 42:     /** @var array of where conditions */
 43:     protected $where = array();
 44: 
 45:     /** @var array of where conditions for caching */
 46:     protected $conditions = array();
 47: 
 48:     /** @var array of parameters passed to where conditions */
 49:     protected $parameters = array();
 50: 
 51:     /** @var array or columns to order by */
 52:     protected $order = array();
 53: 
 54:     /** @var int number of rows to fetch */
 55:     protected $limit = NULL;
 56: 
 57:     /** @var int first row to fetch */
 58:     protected $offset = NULL;
 59: 
 60:     /** @var string columns to grouping */
 61:     protected $group = '';
 62: 
 63:     /** @var string grouping condition */
 64:     protected $having = '';
 65: 
 66:     /** @var array of referenced TableSelection */
 67:     protected $referenced = array();
 68: 
 69:     /** @var array of [sql => [column => [key => TableRow]]] used by GroupedTableSelection */
 70:     protected $referencing = array();
 71: 
 72:     /** @var array of [sql => [key => TableRow]] used by GroupedTableSelection */
 73:     protected $aggregation = array();
 74: 
 75:     /** @var array of touched columns */
 76:     protected $accessed;
 77: 
 78:     /** @var array of earlier touched columns */
 79:     protected $prevAccessed;
 80: 
 81:     /** @var array of primary key values */
 82:     protected $keys = array();
 83: 
 84:     /** @var string */
 85:     protected $delimitedName;
 86: 
 87:     /** @var string */
 88:     protected $delimitedPrimary;
 89: 
 90: 
 91: 
 92:     /**
 93:      * @param  string
 94:      * @param
 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:      * Saves data to cache and empty result.
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:      * Returns row specified by primary key.
128:      * @param  mixed
129:      * @return NTableRow 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 NTableSelection 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 NTableSelection 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 NTableSelection 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;
186:         $condition = $this->tryDelimite($condition);
187: 
188:         $args = func_num_args();
189:         if ($args !== 2 || strpbrk($condition, '?:')) { // where('column < ? OR column > ?', array(1, 2))
190:             if ($args !== 2 || !is_array($parameters)) { // where('column < ? OR column > ?', 1, 2)
191:                 $parameters = func_get_args();
192:                 array_shift($parameters);
193:             }
194:             $this->parameters = array_merge($this->parameters, $parameters);
195: 
196:         } elseif ($parameters === NULL) { // where('column', NULL)
197:             $condition .= ' IS NULL';
198: 
199:         } elseif ($parameters instanceof NTableSelection) { // where('column', $db->$table())
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)) { // where('column', 'x')
216:             $condition .= ' = ?';
217:             $this->parameters[] = $parameters;
218: 
219:         } else { // where('column', array(1, 2))
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:      * Adds order clause, more calls appends to the end.
236:      * @param  string for example 'column1, column2 DESC'
237:      * @return NTableSelection provides a fluent interface
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:      * Sets limit clause, more calls rewrite old values.
250:      * @param  int
251:      * @param  int
252:      * @return NTableSelection provides a fluent interface
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:      * Sets offset using page number, more calls rewrite old values.
266:      * @param  int
267:      * @param  int
268:      * @return NTableSelection provides a fluent interface
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:      * Sets group clause, more calls rewrite old values.
282:      * @param  string
283:      * @param  string
284:      * @return NTableSelection provides a fluent interface
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:      * Executes aggregation function.
298:      * @param  string
299:      * @return string
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:      * Counts number of rows.
317:      * @param  string
318:      * @return int
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:      * Returns minimum value from a column.
333:      * @param  string
334:      * @return int
335:      */
336:     public function min($column)
337:     {
338:         return $this->aggregation("MIN({$this->tryDelimite($column)})");
339:     }
340: 
341: 
342: 
343:     /**
344:      * Returns maximum value from a column.
345:      * @param  string
346:      * @return int
347:      */
348:     public function max($column)
349:     {
350:         return $this->aggregation("MAX({$this->tryDelimite($column)})");
351:     }
352: 
353: 
354: 
355:     /**
356:      * Returns sum of values in a column.
357:      * @param  string
358:      * @return int
359:      */
360:     public function sum($column)
361:     {
362:         return $this->aggregation("SUM({$this->tryDelimite($column)})");
363:     }
364: 
365: 
366: 
367:     /**
368:      * Returns SQL query.
369:      * @return string
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) { // case-sensitive
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:      * Executes built query.
423:      * @return NULL
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)"; //! offset is not supported
494:         }
495:         return '';
496:     }
497: 
498: 
499: 
500:     protected function tryDelimite($s)
501:     {
502:         return preg_match('#^[a-z_][a-z0-9_.]*$#i', $s) // is identifier?
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:     /********************* manipulation ****************d*g**/
543: 
544: 
545: 
546:     /**
547:      * Inserts row in a table.
548:      * @param  mixed array($column => $value)|Traversable for single row insert or TableSelection|string for INSERT ... SELECT
549:      * @return NTableRow or FALSE in case of an error or number of affected rows for INSERT ... SELECT
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:      * Updates all rows in result set.
577:      * @param  array ($column => $value)
578:      * @return int number of affected rows or FALSE in case of an error
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:         // joins in UPDATE are supported only in MySQL
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:      * Deletes all rows in result set.
603:      * @return int number of affected rows or FALSE in case of an error
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:     /********************* references ****************d*g**/
615: 
616: 
617: 
618:     /**
619:      * Returns referenced row.
620:      * @param  string
621:      * @return NTableRow or NULL if the row does not exist
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:      * Returns referencing rows.
649:      * @param  string table name
650:      * @return NGroupedTableSelection
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)); // (array) - is NULL after insert
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:     /********************* interface Iterator ****************d*g**/
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:     /** @return NTableRow */
683:     public function current()
684:     {
685:         return $this->data[current($this->keys)];
686:     }
687: 
688: 
689: 
690:     /**
691:      * @return string row ID
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:     /********************* interface ArrayAccess ****************d*g**/
715: 
716: 
717: 
718:     /**
719:      * Mimic row.
720:      * @param  string row ID
721:      * @param  NTableRow
722:      * @return NULL
723:      */
724:     public function offsetSet($key, $value)
725:     {
726:         $this->execute();
727:         $this->data[$key] = $value;
728:     }
729: 
730: 
731: 
732:     /**
733:      * Returns specified row.
734:      * @param  string row ID
735:      * @return NTableRow or NULL if there is no such row
736:      */
737:     public function offsetGet($key)
738:     {
739:         $this->execute();
740:         return $this->data[$key];
741:     }
742: 
743: 
744: 
745:     /**
746:      * Tests if row exists.
747:      * @param  string row ID
748:      * @return bool
749:      */
750:     public function offsetExists($key)
751:     {
752:         $this->execute();
753:         return isset($this->data[$key]);
754:     }
755: 
756: 
757: 
758:     /**
759:      * Removes row from result set.
760:      * @param  string row ID
761:      * @return NULL
762:      */
763:     public function offsetUnset($key)
764:     {
765:         $this->execute();
766:         unset($this->data[$key]);
767:     }
768: 
769: 
770: 
771:     /**
772:      * Returns next row of result.
773:      * @return NTableRow or FALSE if there is no row
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:      * Returns all rows as associative array.
787:      * @param  string
788:      * @param  string column name used for an array value or an empty string for the whole row
789:      * @return array
790:      */
791:     public function fetchPairs($key, $value = '')
792:     {
793:         $return = array();
794:         // no $clone->select = array($key, $value) to allow efficient caching with repetitive calls with different parameters
795:         foreach ($this as $row) {
796:             $return[$row[$key]] = ($value !== '' ? $row[$value] : $row);
797:         }
798:         return $return;
799:     }
800: 
801: }
802: 
Nette Framework 2.0beta1 (for PHP 5.2) API API documentation generated by ApiGen 2.3.0