Namespaces

  • 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

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