Packages

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

Classes

  • NGroupedTableSelection
  • NSqlBuilder
  • 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 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:  * Selection is based on the great library NotORM http://www.notorm.com written by Jakub Vrana.
 18:  *
 19:  * @author     Jakub Vrana
 20:  * @author     Jan Skrasek
 21:  *
 22:  * @property-read string $sql
 23:  * @package Nette\Database\Table
 24:  */
 25: class NTableSelection extends NObject implements Iterator, ArrayAccess, Countable
 26: {
 27:     /** @var NConnection */
 28:     protected $connection;
 29: 
 30:     /** @var NSqlBuilder */
 31:     protected $sqlBuilder;
 32: 
 33:     /** @var string table name */
 34:     protected $name;
 35: 
 36:     /** @var string primary key field name */
 37:     protected $primary;
 38: 
 39:     /** @var string|bool primary column sequence name, FALSE for autodetection */
 40:     protected $primarySequence = FALSE;
 41: 
 42:     /** @var NTableRow[] data read from database in [primary key => ActiveRow] format */
 43:     protected $rows;
 44: 
 45:     /** @var NTableRow[] modifiable data in [primary key => ActiveRow] format */
 46:     protected $data;
 47: 
 48:     /** @var NTableSelection[] */
 49:     protected $referenced = array();
 50: 
 51:     /** @var array of [sqlQuery-hash => grouped data]; used by GroupedSelection */
 52:     protected $referencing = array();
 53: 
 54:     /** @var NGroupedTableSelection[] cached array of GroupedSelection prototypes */
 55:     protected $referencingPrototype = array();
 56: 
 57:     /** @var array of [conditions => [key => ActiveRow]]; used by GroupedSelection */
 58:     protected $aggregation = array();
 59: 
 60:     /** @var array of touched columns */
 61:     protected $accessed;
 62: 
 63:     /** @var array of earlier touched columns */
 64:     protected $prevAccessed;
 65: 
 66:     /** @var bool should instance observe accessed columns caching */
 67:     protected $observeCache = FALSE;
 68: 
 69:     /** @var bool recheck referencing keys */
 70:     protected $checkReferenced = FALSE;
 71: 
 72:     /** @var array of primary key values */
 73:     protected $keys = array();
 74: 
 75: 
 76: 
 77:     /**
 78:      * Creates filtered table representation.
 79:      * @param  string  database table name
 80:      * @param  NConnection
 81:      */
 82:     public function __construct($table, NConnection $connection)
 83:     {
 84:         $this->name = $table;
 85:         $this->connection = $connection;
 86:         $this->primary = $connection->getDatabaseReflection()->getPrimary($table);
 87:         $this->sqlBuilder = new NSqlBuilder($this);
 88:     }
 89: 
 90: 
 91: 
 92:     public function __destruct()
 93:     {
 94:         $this->saveCacheState();
 95:     }
 96: 
 97: 
 98: 
 99:     public function __clone()
100:     {
101:         $this->sqlBuilder = clone $this->sqlBuilder;
102:         $this->sqlBuilder->setSelection($this);
103:     }
104: 
105: 
106: 
107:     /**
108:      * @return NConnection
109:      */
110:     public function getConnection()
111:     {
112:         return $this->connection;
113:     }
114: 
115: 
116: 
117:     /**
118:      * @return string
119:      */
120:     public function getName()
121:     {
122:         return $this->name;
123:     }
124: 
125: 
126: 
127:     /**
128:      * @return string
129:      */
130:     public function getPrimary()
131:     {
132:         return $this->primary;
133:     }
134: 
135: 
136: 
137:     /**
138:      * @return string
139:      */
140:     public function getPrimarySequence()
141:     {
142:         if ($this->primarySequence === FALSE) {
143:             $this->primarySequence = NULL;
144: 
145:             $driver = $this->connection->getSupplementalDriver();
146:             if ($driver->isSupported(ISupplementalDriver::SUPPORT_SEQUENCE)) {
147:                 foreach ($driver->getColumns($this->name) as $column) {
148:                     if ($column['name'] === $this->primary) {
149:                         $this->primarySequence = $column['vendor']['sequence'];
150:                         break;
151:                     }
152:                 }
153:             }
154:         }
155: 
156:         return $this->primarySequence;
157:     }
158: 
159: 
160: 
161:     /**
162:      * @param  string
163:      * @return NTableSelection provides a fluent interface
164:      */
165:     public function setPrimarySequence($sequence)
166:     {
167:         $this->primarySequence = $sequence;
168:         return $this;
169:     }
170: 
171: 
172: 
173:     /**
174:      * @return string
175:      */
176:     public function getSql()
177:     {
178:         return $this->sqlBuilder->buildSelectQuery();
179:     }
180: 
181: 
182: 
183:     /**
184:      * Loads cache of previous accessed columns and returns it.
185:      * @internal
186:      * @return array|false
187:      */
188:     public function getPreviousAccessed()
189:     {
190:         $cache = $this->connection->getCache();
191:         if ($this->rows === NULL && $cache && !is_string($this->prevAccessed)) {
192:             $this->accessed = $this->prevAccessed = $cache->load(array(__CLASS__, $this->name, $this->sqlBuilder->getConditions()));
193:         }
194: 
195:         return $this->prevAccessed;
196:     }
197: 
198: 
199: 
200:     /**
201:      * @internal
202:      * @return NSqlBuilder
203:      */
204:     public function getSqlBuilder()
205:     {
206:         return $this->sqlBuilder;
207:     }
208: 
209: 
210: 
211:     /********************* quick access ****************d*g**/
212: 
213: 
214: 
215:     /**
216:      * Returns row specified by primary key.
217:      * @param  mixed primary key
218:      * @return NTableRow or FALSE if there is no such row
219:      */
220:     public function get($key)
221:     {
222:         $clone = clone $this;
223:         $clone->where($this->primary, $key);
224:         return $clone->fetch();
225:     }
226: 
227: 
228: 
229:     /**
230:      * Returns next row of result.
231:      * @return NTableRow or FALSE if there is no row
232:      */
233:     public function fetch()
234:     {
235:         $this->execute();
236:         $return = current($this->data);
237:         next($this->data);
238:         return $return;
239:     }
240: 
241: 
242: 
243:     /**
244:      * Returns all rows as associative array.
245:      * @param  string
246:      * @param  string column name used for an array value or NULL for the whole row
247:      * @return array
248:      */
249:     public function fetchPairs($key, $value = NULL)
250:     {
251:         $return = array();
252:         foreach ($this as $row) {
253:             $return[is_object($row[$key]) ? (string) $row[$key] : $row[$key]] = ($value ? $row[$value] : $row);
254:         }
255:         return $return;
256:     }
257: 
258: 
259: 
260:     /********************* sql selectors ****************d*g**/
261: 
262: 
263: 
264:     /**
265:      * Adds select clause, more calls appends to the end.
266:      * @param  string for example "column, MD5(column) AS column_md5"
267:      * @return NTableSelection provides a fluent interface
268:      */
269:     public function select($columns)
270:     {
271:         $this->emptyResultSet();
272:         $this->sqlBuilder->addSelect($columns);
273:         return $this;
274:     }
275: 
276: 
277: 
278:     /**
279:      * Selects by primary key.
280:      * @param  mixed
281:      * @return NTableSelection provides a fluent interface
282:      */
283:     public function find($key)
284:     {
285:         return $this->where($this->primary, $key);
286:     }
287: 
288: 
289: 
290:     /**
291:      * Adds where condition, more calls appends with AND.
292:      * @param  string condition possibly containing ?
293:      * @param  mixed
294:      * @param  mixed ...
295:      * @return NTableSelection provides a fluent interface
296:      */
297:     public function where($condition, $parameters = array())
298:     {
299:         if (is_array($condition)) { // where(array('column1' => 1, 'column2 > ?' => 2))
300:             foreach ($condition as $key => $val) {
301:                 if (is_int($key)) {
302:                     $this->where($val); // where('full condition')
303:                 } else {
304:                     $this->where($key, $val);   // where('column', 1)
305:                 }
306:             }
307:             return $this;
308:         }
309: 
310:         $args = func_get_args();
311:         if (call_user_func_array(array($this->sqlBuilder, 'addWhere'), $args)) {
312:             $this->emptyResultSet();
313:         }
314: 
315:         return $this;
316:     }
317: 
318: 
319: 
320:     /**
321:      * Adds order clause, more calls appends to the end.
322:      * @param  string for example 'column1, column2 DESC'
323:      * @return NTableSelection provides a fluent interface
324:      */
325:     public function order($columns)
326:     {
327:         $this->emptyResultSet();
328:         $this->sqlBuilder->addOrder($columns);
329:         return $this;
330:     }
331: 
332: 
333: 
334:     /**
335:      * Sets limit clause, more calls rewrite old values.
336:      * @param  int
337:      * @param  int
338:      * @return NTableSelection provides a fluent interface
339:      */
340:     public function limit($limit, $offset = NULL)
341:     {
342:         $this->emptyResultSet();
343:         $this->sqlBuilder->setLimit($limit, $offset);
344:         return $this;
345:     }
346: 
347: 
348: 
349:     /**
350:      * Sets offset using page number, more calls rewrite old values.
351:      * @param  int
352:      * @param  int
353:      * @return NTableSelection provides a fluent interface
354:      */
355:     public function page($page, $itemsPerPage)
356:     {
357:         return $this->limit($itemsPerPage, ($page - 1) * $itemsPerPage);
358:     }
359: 
360: 
361: 
362:     /**
363:      * Sets group clause, more calls rewrite old values.
364:      * @param  string
365:      * @param  string
366:      * @return NTableSelection provides a fluent interface
367:      */
368:     public function group($columns, $having = NULL)
369:     {
370:         $this->emptyResultSet();
371:         $this->sqlBuilder->setGroup($columns, $having);
372:         return $this;
373:     }
374: 
375: 
376: 
377:     /********************* aggregations ****************d*g**/
378: 
379: 
380: 
381:     /**
382:      * Executes aggregation function.
383:      * @param  string select call in "FUNCTION(column)" format
384:      * @return string
385:      */
386:     public function aggregation($function)
387:     {
388:         $selection = $this->createSelectionInstance();
389:         $selection->getSqlBuilder()->importConditions($this->getSqlBuilder());
390:         $selection->select($function);
391:         foreach ($selection->fetch() as $val) {
392:             return $val;
393:         }
394:     }
395: 
396: 
397: 
398:     /**
399:      * Counts number of rows.
400:      * @param  string  if it is not provided returns count of result rows, otherwise runs new sql counting query
401:      * @return int
402:      */
403:     public function count($column = NULL)
404:     {
405:         if (!$column) {
406:             $this->execute();
407:             return count($this->data);
408:         }
409:         return $this->aggregation("COUNT($column)");
410:     }
411: 
412: 
413: 
414:     /**
415:      * Returns minimum value from a column.
416:      * @param  string
417:      * @return int
418:      */
419:     public function min($column)
420:     {
421:         return $this->aggregation("MIN($column)");
422:     }
423: 
424: 
425: 
426:     /**
427:      * Returns maximum value from a column.
428:      * @param  string
429:      * @return int
430:      */
431:     public function max($column)
432:     {
433:         return $this->aggregation("MAX($column)");
434:     }
435: 
436: 
437: 
438:     /**
439:      * Returns sum of values in a column.
440:      * @param  string
441:      * @return int
442:      */
443:     public function sum($column)
444:     {
445:         return $this->aggregation("SUM($column)");
446:     }
447: 
448: 
449: 
450:     /********************* internal ****************d*g**/
451: 
452: 
453: 
454:     protected function execute()
455:     {
456:         if ($this->rows !== NULL) {
457:             return;
458:         }
459: 
460:         $this->observeCache = TRUE;
461: 
462:         try {
463:             $result = $this->query($this->sqlBuilder->buildSelectQuery());
464: 
465:         } catch (PDOException $exception) {
466:             if (!$this->sqlBuilder->getSelect() && $this->prevAccessed) {
467:                 $this->prevAccessed = '';
468:                 $this->accessed = array();
469:                 $result = $this->query($this->sqlBuilder->buildSelectQuery());
470:             } else {
471:                 throw $exception;
472:             }
473:         }
474: 
475:         $this->rows = array();
476:         $result->setFetchMode(PDO::FETCH_ASSOC);
477:         foreach ($result as $key => $row) {
478:             $row = $result->normalizeRow($row);
479:             $this->rows[isset($row[$this->primary]) ? $row[$this->primary] : $key] = $this->createRow($row);
480:         }
481:         $this->data = $this->rows;
482: 
483:         if (isset($row[$this->primary]) && !is_string($this->accessed)) {
484:             $this->accessed[$this->primary] = TRUE;
485:         }
486:     }
487: 
488: 
489: 
490:     protected function createRow(array $row)
491:     {
492:         return new NTableRow($row, $this);
493:     }
494: 
495: 
496: 
497:     protected function createSelectionInstance($table = NULL)
498:     {
499:         return new NTableSelection(($tmp=$table) ? $tmp : $this->name, $this->connection);
500:     }
501: 
502: 
503: 
504:     protected function createGroupedSelectionInstance($table, $column)
505:     {
506:         return new NGroupedTableSelection($this, $table, $column);
507:     }
508: 
509: 
510: 
511:     protected function query($query)
512:     {
513:         return $this->connection->queryArgs($query, $this->sqlBuilder->getParameters());
514:     }
515: 
516: 
517: 
518:     protected function emptyResultSet()
519:     {
520:         if ($this->rows === NULL) {
521:             return;
522:         }
523: 
524:         $this->rows = NULL;
525:         $this->saveCacheState();
526:     }
527: 
528: 
529: 
530:     protected function saveCacheState()
531:     {
532:         if ($this->observeCache && ($cache = $this->connection->getCache()) && !$this->sqlBuilder->getSelect() && $this->accessed != $this->prevAccessed) {
533:             $cache->save(array(__CLASS__, $this->name, $this->sqlBuilder->getConditions()), $this->accessed);
534:         }
535:     }
536: 
537: 
538: 
539:     /**
540:      * Returns Selection parent for caching.
541:      * @return NTableSelection
542:      */
543:     protected function getRefTable(& $refPath)
544:     {
545:         return $this;
546:     }
547: 
548: 
549: 
550:     /**
551:      * @internal
552:      * @param  string column name
553:      * @param  bool|NULL TRUE - cache, FALSE - don't cache, NULL - remove
554:      * @return bool
555:      */
556:     public function access($key, $cache = TRUE)
557:     {
558:         if ($cache === NULL) {
559:             if (is_array($this->accessed)) {
560:                 $this->accessed[$key] = FALSE;
561:             }
562:             return FALSE;
563:         }
564: 
565:         if ($key === NULL) {
566:             $this->accessed = '';
567: 
568:         } elseif (!is_string($this->accessed)) {
569:             $this->accessed[$key] = $cache;
570:         }
571: 
572:         if ($cache && !$this->sqlBuilder->getSelect() && $this->prevAccessed && ($key === NULL || !isset($this->prevAccessed[$key]))) {
573:             $this->prevAccessed = '';
574:             $this->emptyResultSet();
575:             return TRUE;
576:         }
577: 
578:         return FALSE;
579:     }
580: 
581: 
582: 
583:     /********************* manipulation ****************d*g**/
584: 
585: 
586: 
587:     /**
588:      * Inserts row in a table.
589:      * @param  mixed array($column => $value)|Traversable for single row insert or Selection|string for INSERT ... SELECT
590:      * @return NTableRow or FALSE in case of an error or number of affected rows for INSERT ... SELECT
591:      */
592:     public function insert($data)
593:     {
594:         if ($data instanceof NTableSelection) {
595:             $data = $data->getSql();
596: 
597:         } elseif ($data instanceof Traversable) {
598:             $data = iterator_to_array($data);
599:         }
600: 
601:         $return = $this->connection->query($this->sqlBuilder->buildInsertQuery(), $data);
602:         $this->checkReferenced = TRUE;
603: 
604:         if (!is_array($data)) {
605:             return $return->rowCount();
606:         }
607: 
608:         if (!isset($data[$this->primary]) && ($id = $this->connection->lastInsertId($this->getPrimarySequence()))) {
609:             $data[$this->primary] = $id;
610:             return $this->rows[$id] = $this->createRow($data);
611: 
612:         } else {
613:             return $this->createRow($data);
614: 
615:         }
616:     }
617: 
618: 
619: 
620:     /**
621:      * Updates all rows in result set.
622:      * Joins in UPDATE are supported only in MySQL
623:      * @param  array|Traversable ($column => $value)
624:      * @return int number of affected rows or FALSE in case of an error
625:      */
626:     public function update($data)
627:     {
628:         if ($data instanceof Traversable) {
629:             $data = iterator_to_array($data);
630: 
631:         } elseif (!is_array($data)) {
632:             throw new InvalidArgumentException;
633:         }
634: 
635:         if (!$data) {
636:             return 0;
637:         }
638: 
639:         return $this->connection->queryArgs(
640:             $this->sqlBuilder->buildUpdateQuery(),
641:             array_merge(array($data), $this->sqlBuilder->getParameters())
642:         )->rowCount();
643:     }
644: 
645: 
646: 
647:     /**
648:      * Deletes all rows in result set.
649:      * @return int number of affected rows or FALSE in case of an error
650:      */
651:     public function delete()
652:     {
653:         return $this->query($this->sqlBuilder->buildDeleteQuery())->rowCount();
654:     }
655: 
656: 
657: 
658:     /********************* references ****************d*g**/
659: 
660: 
661: 
662:     /**
663:      * Returns referenced row.
664:      * @param  string
665:      * @param  string
666:      * @param  bool  checks if rows contains the same primary value relations
667:      * @return NTableSelection or array() if the row does not exist
668:      */
669:     public function getReferencedTable($table, $column, $checkReferenced = FALSE)
670:     {
671:         $referenced = & $this->getRefTable($refPath)->referenced[$refPath . "$table.$column"];
672:         if ($referenced === NULL || $checkReferenced || $this->checkReferenced) {
673:             $this->execute();
674:             $this->checkReferenced = FALSE;
675:             $keys = array();
676:             foreach ($this->rows as $row) {
677:                 if ($row[$column] === NULL)
678:                     continue;
679: 
680:                 $key = $row[$column] instanceof NTableRow ? $row[$column]->getPrimary() : $row[$column];
681:                 $keys[$key] = TRUE;
682:             }
683: 
684:             if ($referenced !== NULL && array_keys($keys) === array_keys($referenced->rows)) {
685:                 return $referenced;
686:             }
687: 
688:             if ($keys) {
689:                 $referenced = $this->createSelectionInstance($table);
690:                 $referenced->where($referenced->primary, array_keys($keys));
691:             } else {
692:                 $referenced = array();
693:             }
694:         }
695: 
696:         return $referenced;
697:     }
698: 
699: 
700: 
701:     /**
702:      * Returns referencing rows.
703:      * @param  string
704:      * @param  string
705:      * @param  int primary key
706:      * @return NGroupedTableSelection
707:      */
708:     public function getReferencingTable($table, $column, $active = NULL)
709:     {
710:         $prototype = & $this->getRefTable($refPath)->referencingPrototype[$refPath . "$table.$column"];
711:         if (!$prototype) {
712:             $prototype = $this->createGroupedSelectionInstance($table, $column);
713:             $prototype->where("$table.$column", array_keys((array) $this->rows));
714:         }
715: 
716:         $clone = clone $prototype;
717:         $clone->setActive($active);
718:         return $clone;
719:     }
720: 
721: 
722: 
723:     /********************* interface Iterator ****************d*g**/
724: 
725: 
726: 
727:     public function rewind()
728:     {
729:         $this->execute();
730:         $this->keys = array_keys($this->data);
731:         reset($this->keys);
732:     }
733: 
734: 
735: 
736:     /** @return NTableRow */
737:     public function current()
738:     {
739:         return $this->data[current($this->keys)];
740:     }
741: 
742: 
743: 
744:     /**
745:      * @return string row ID
746:      */
747:     public function key()
748:     {
749:         return current($this->keys);
750:     }
751: 
752: 
753: 
754:     public function next()
755:     {
756:         next($this->keys);
757:     }
758: 
759: 
760: 
761:     public function valid()
762:     {
763:         return current($this->keys) !== FALSE;
764:     }
765: 
766: 
767: 
768:     /********************* interface ArrayAccess ****************d*g**/
769: 
770: 
771: 
772:     /**
773:      * Mimic row.
774:      * @param  string row ID
775:      * @param  NTableRow
776:      * @return NULL
777:      */
778:     public function offsetSet($key, $value)
779:     {
780:         $this->execute();
781:         $this->data[$key] = $value;
782:     }
783: 
784: 
785: 
786:     /**
787:      * Returns specified row.
788:      * @param  string row ID
789:      * @return NTableRow or NULL if there is no such row
790:      */
791:     public function offsetGet($key)
792:     {
793:         $this->execute();
794:         return $this->data[$key];
795:     }
796: 
797: 
798: 
799:     /**
800:      * Tests if row exists.
801:      * @param  string row ID
802:      * @return bool
803:      */
804:     public function offsetExists($key)
805:     {
806:         $this->execute();
807:         return isset($this->data[$key]);
808:     }
809: 
810: 
811: 
812:     /**
813:      * Removes row from result set.
814:      * @param  string row ID
815:      * @return NULL
816:      */
817:     public function offsetUnset($key)
818:     {
819:         $this->execute();
820:         unset($this->data[$key]);
821:     }
822: 
823: }
824: 
Nette Framework 2.0.6 (for PHP 5.2, prefixed) API API documentation generated by ApiGen 2.7.0