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
  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
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:         $clone->where($this->primary, $key);
228:         return $clone->fetch();
229:     }
230: 
231: 
232: 
233:     /**
234:      * Returns next row of result.
235:      * @return ActiveRow or FALSE if there is no row
236:      */
237:     public function fetch()
238:     {
239:         $this->execute();
240:         $return = current($this->data);
241:         next($this->data);
242:         return $return;
243:     }
244: 
245: 
246: 
247:     /**
248:      * Returns all rows as associative array.
249:      * @param  string
250:      * @param  string column name used for an array value or NULL for the whole row
251:      * @return array
252:      */
253:     public function fetchPairs($key, $value = NULL)
254:     {
255:         $return = array();
256:         foreach ($this as $row) {
257:             $return[is_object($row[$key]) ? (string) $row[$key] : $row[$key]] = ($value ? $row[$value] : $row);
258:         }
259:         return $return;
260:     }
261: 
262: 
263: 
264:     /********************* sql selectors ****************d*g**/
265: 
266: 
267: 
268:     /**
269:      * Adds select clause, more calls appends to the end.
270:      * @param  string for example "column, MD5(column) AS column_md5"
271:      * @return Selection provides a fluent interface
272:      */
273:     public function select($columns)
274:     {
275:         $this->emptyResultSet();
276:         $this->sqlBuilder->addSelect($columns);
277:         return $this;
278:     }
279: 
280: 
281: 
282:     /**
283:      * Selects by primary key.
284:      * @param  mixed
285:      * @return Selection provides a fluent interface
286:      */
287:     public function find($key)
288:     {
289:         return $this->where($this->primary, $key);
290:     }
291: 
292: 
293: 
294:     /**
295:      * Adds where condition, more calls appends with AND.
296:      * @param  string condition possibly containing ?
297:      * @param  mixed
298:      * @param  mixed ...
299:      * @return Selection provides a fluent interface
300:      */
301:     public function where($condition, $parameters = array())
302:     {
303:         if (is_array($condition)) { // where(array('column1' => 1, 'column2 > ?' => 2))
304:             foreach ($condition as $key => $val) {
305:                 if (is_int($key)) {
306:                     $this->where($val); // where('full condition')
307:                 } else {
308:                     $this->where($key, $val);   // where('column', 1)
309:                 }
310:             }
311:             return $this;
312:         }
313: 
314:         $args = func_get_args();
315:         if (call_user_func_array(array($this->sqlBuilder, 'addWhere'), $args)) {
316:             $this->emptyResultSet();
317:         }
318: 
319:         return $this;
320:     }
321: 
322: 
323: 
324:     /**
325:      * Adds order clause, more calls appends to the end.
326:      * @param  string for example 'column1, column2 DESC'
327:      * @return Selection provides a fluent interface
328:      */
329:     public function order($columns)
330:     {
331:         $this->emptyResultSet();
332:         $this->sqlBuilder->addOrder($columns);
333:         return $this;
334:     }
335: 
336: 
337: 
338:     /**
339:      * Sets limit clause, more calls rewrite old values.
340:      * @param  int
341:      * @param  int
342:      * @return Selection provides a fluent interface
343:      */
344:     public function limit($limit, $offset = NULL)
345:     {
346:         $this->emptyResultSet();
347:         $this->sqlBuilder->setLimit($limit, $offset);
348:         return $this;
349:     }
350: 
351: 
352: 
353:     /**
354:      * Sets offset using page number, more calls rewrite old values.
355:      * @param  int
356:      * @param  int
357:      * @return Selection provides a fluent interface
358:      */
359:     public function page($page, $itemsPerPage)
360:     {
361:         return $this->limit($itemsPerPage, ($page - 1) * $itemsPerPage);
362:     }
363: 
364: 
365: 
366:     /**
367:      * Sets group clause, more calls rewrite old values.
368:      * @param  string
369:      * @param  string
370:      * @return Selection provides a fluent interface
371:      */
372:     public function group($columns, $having = NULL)
373:     {
374:         $this->emptyResultSet();
375:         $this->sqlBuilder->setGroup($columns, $having);
376:         return $this;
377:     }
378: 
379: 
380: 
381:     /********************* aggregations ****************d*g**/
382: 
383: 
384: 
385:     /**
386:      * Executes aggregation function.
387:      * @param  string select call in "FUNCTION(column)" format
388:      * @return string
389:      */
390:     public function aggregation($function)
391:     {
392:         $selection = $this->createSelectionInstance();
393:         $selection->getSqlBuilder()->importConditions($this->getSqlBuilder());
394:         $selection->select($function);
395:         foreach ($selection->fetch() as $val) {
396:             return $val;
397:         }
398:     }
399: 
400: 
401: 
402:     /**
403:      * Counts number of rows.
404:      * @param  string  if it is not provided returns count of result rows, otherwise runs new sql counting query
405:      * @return int
406:      */
407:     public function count($column = NULL)
408:     {
409:         if (!$column) {
410:             $this->execute();
411:             return count($this->data);
412:         }
413:         return $this->aggregation("COUNT($column)");
414:     }
415: 
416: 
417: 
418:     /**
419:      * Returns minimum value from a column.
420:      * @param  string
421:      * @return int
422:      */
423:     public function min($column)
424:     {
425:         return $this->aggregation("MIN($column)");
426:     }
427: 
428: 
429: 
430:     /**
431:      * Returns maximum value from a column.
432:      * @param  string
433:      * @return int
434:      */
435:     public function max($column)
436:     {
437:         return $this->aggregation("MAX($column)");
438:     }
439: 
440: 
441: 
442:     /**
443:      * Returns sum of values in a column.
444:      * @param  string
445:      * @return int
446:      */
447:     public function sum($column)
448:     {
449:         return $this->aggregation("SUM($column)");
450:     }
451: 
452: 
453: 
454:     /********************* internal ****************d*g**/
455: 
456: 
457: 
458:     protected function execute()
459:     {
460:         if ($this->rows !== NULL) {
461:             return;
462:         }
463: 
464:         $this->observeCache = TRUE;
465: 
466:         try {
467:             $result = $this->query($this->sqlBuilder->buildSelectQuery());
468: 
469:         } catch (\PDOException $exception) {
470:             if (!$this->sqlBuilder->getSelect() && $this->prevAccessed) {
471:                 $this->prevAccessed = '';
472:                 $this->accessed = array();
473:                 $result = $this->query($this->sqlBuilder->buildSelectQuery());
474:             } else {
475:                 throw $exception;
476:             }
477:         }
478: 
479:         $this->rows = array();
480:         $result->setFetchMode(PDO::FETCH_ASSOC);
481:         foreach ($result as $key => $row) {
482:             $row = $result->normalizeRow($row);
483:             $this->rows[isset($row[$this->primary]) ? $row[$this->primary] : $key] = $this->createRow($row);
484:         }
485:         $this->data = $this->rows;
486: 
487:         if (isset($row[$this->primary]) && !is_string($this->accessed)) {
488:             $this->accessed[$this->primary] = TRUE;
489:         }
490:     }
491: 
492: 
493: 
494:     protected function createRow(array $row)
495:     {
496:         return new ActiveRow($row, $this);
497:     }
498: 
499: 
500: 
501:     protected function createSelectionInstance($table = NULL)
502:     {
503:         return new Selection($table ?: $this->name, $this->connection);
504:     }
505: 
506: 
507: 
508:     protected function createGroupedSelectionInstance($table, $column)
509:     {
510:         return new GroupedSelection($this, $table, $column);
511:     }
512: 
513: 
514: 
515:     protected function query($query)
516:     {
517:         return $this->connection->queryArgs($query, $this->sqlBuilder->getParameters());
518:     }
519: 
520: 
521: 
522:     protected function emptyResultSet()
523:     {
524:         if ($this->rows === NULL) {
525:             return;
526:         }
527: 
528:         $this->rows = NULL;
529:         $this->saveCacheState();
530:     }
531: 
532: 
533: 
534:     protected function saveCacheState()
535:     {
536:         if ($this->observeCache && ($cache = $this->connection->getCache()) && !$this->sqlBuilder->getSelect() && $this->accessed != $this->prevAccessed) {
537:             $cache->save(array(__CLASS__, $this->name, $this->sqlBuilder->getConditions()), $this->accessed);
538:         }
539:     }
540: 
541: 
542: 
543:     /**
544:      * Returns Selection parent for caching.
545:      * @return Selection
546:      */
547:     protected function getRefTable(& $refPath)
548:     {
549:         return $this;
550:     }
551: 
552: 
553: 
554:     /**
555:      * @internal
556:      * @param  string column name
557:      * @param  bool|NULL TRUE - cache, FALSE - don't cache, NULL - remove
558:      * @return bool
559:      */
560:     public function access($key, $cache = TRUE)
561:     {
562:         if ($cache === NULL) {
563:             if (is_array($this->accessed)) {
564:                 $this->accessed[$key] = FALSE;
565:             }
566:             return FALSE;
567:         }
568: 
569:         if ($key === NULL) {
570:             $this->accessed = '';
571: 
572:         } elseif (!is_string($this->accessed)) {
573:             $this->accessed[$key] = $cache;
574:         }
575: 
576:         if ($cache && !$this->sqlBuilder->getSelect() && $this->prevAccessed && ($key === NULL || !isset($this->prevAccessed[$key]))) {
577:             $this->prevAccessed = '';
578:             $this->emptyResultSet();
579:             return TRUE;
580:         }
581: 
582:         return FALSE;
583:     }
584: 
585: 
586: 
587:     /********************* manipulation ****************d*g**/
588: 
589: 
590: 
591:     /**
592:      * Inserts row in a table.
593:      * @param  mixed array($column => $value)|Traversable for single row insert or Selection|string for INSERT ... SELECT
594:      * @return ActiveRow or FALSE in case of an error or number of affected rows for INSERT ... SELECT
595:      */
596:     public function insert($data)
597:     {
598:         if ($data instanceof Selection) {
599:             $data = $data->getSql();
600: 
601:         } elseif ($data instanceof \Traversable) {
602:             $data = iterator_to_array($data);
603:         }
604: 
605:         $return = $this->connection->query($this->sqlBuilder->buildInsertQuery(), $data);
606:         $this->checkReferenced = TRUE;
607: 
608:         if (!is_array($data)) {
609:             return $return->rowCount();
610:         }
611: 
612:         if (!isset($data[$this->primary]) && ($id = $this->connection->lastInsertId($this->getPrimarySequence()))) {
613:             $data[$this->primary] = $id;
614:             return $this->rows[$id] = $this->createRow($data);
615: 
616:         } else {
617:             return $this->createRow($data);
618: 
619:         }
620:     }
621: 
622: 
623: 
624:     /**
625:      * Updates all rows in result set.
626:      * Joins in UPDATE are supported only in MySQL
627:      * @param  array|\Traversable ($column => $value)
628:      * @return int number of affected rows or FALSE in case of an error
629:      */
630:     public function update($data)
631:     {
632:         if ($data instanceof \Traversable) {
633:             $data = iterator_to_array($data);
634: 
635:         } elseif (!is_array($data)) {
636:             throw new Nette\InvalidArgumentException;
637:         }
638: 
639:         if (!$data) {
640:             return 0;
641:         }
642: 
643:         return $this->connection->queryArgs(
644:             $this->sqlBuilder->buildUpdateQuery(),
645:             array_merge(array($data), $this->sqlBuilder->getParameters())
646:         )->rowCount();
647:     }
648: 
649: 
650: 
651:     /**
652:      * Deletes all rows in result set.
653:      * @return int number of affected rows or FALSE in case of an error
654:      */
655:     public function delete()
656:     {
657:         return $this->query($this->sqlBuilder->buildDeleteQuery())->rowCount();
658:     }
659: 
660: 
661: 
662:     /********************* references ****************d*g**/
663: 
664: 
665: 
666:     /**
667:      * Returns referenced row.
668:      * @param  string
669:      * @param  string
670:      * @param  bool  checks if rows contains the same primary value relations
671:      * @return Selection or array() if the row does not exist
672:      */
673:     public function getReferencedTable($table, $column, $checkReferenced = FALSE)
674:     {
675:         $referenced = & $this->getRefTable($refPath)->referenced[$refPath . "$table.$column"];
676:         if ($referenced === NULL || $checkReferenced || $this->checkReferenced) {
677:             $this->execute();
678:             $this->checkReferenced = FALSE;
679:             $keys = array();
680:             foreach ($this->rows as $row) {
681:                 if ($row[$column] === NULL)
682:                     continue;
683: 
684:                 $key = $row[$column] instanceof ActiveRow ? $row[$column]->getPrimary() : $row[$column];
685:                 $keys[$key] = TRUE;
686:             }
687: 
688:             if ($referenced !== NULL && array_keys($keys) === array_keys($referenced->rows)) {
689:                 return $referenced;
690:             }
691: 
692:             if ($keys) {
693:                 $referenced = $this->createSelectionInstance($table);
694:                 $referenced->where($referenced->primary, array_keys($keys));
695:             } else {
696:                 $referenced = array();
697:             }
698:         }
699: 
700:         return $referenced;
701:     }
702: 
703: 
704: 
705:     /**
706:      * Returns referencing rows.
707:      * @param  string
708:      * @param  string
709:      * @param  int primary key
710:      * @return GroupedSelection
711:      */
712:     public function getReferencingTable($table, $column, $active = NULL)
713:     {
714:         $prototype = & $this->getRefTable($refPath)->referencingPrototype[$refPath . "$table.$column"];
715:         if (!$prototype) {
716:             $prototype = $this->createGroupedSelectionInstance($table, $column);
717:             $prototype->where("$table.$column", array_keys((array) $this->rows));
718:         }
719: 
720:         $clone = clone $prototype;
721:         $clone->setActive($active);
722:         return $clone;
723:     }
724: 
725: 
726: 
727:     /********************* interface Iterator ****************d*g**/
728: 
729: 
730: 
731:     public function rewind()
732:     {
733:         $this->execute();
734:         $this->keys = array_keys($this->data);
735:         reset($this->keys);
736:     }
737: 
738: 
739: 
740:     /** @return ActiveRow */
741:     public function current()
742:     {
743:         return $this->data[current($this->keys)];
744:     }
745: 
746: 
747: 
748:     /**
749:      * @return string row ID
750:      */
751:     public function key()
752:     {
753:         return current($this->keys);
754:     }
755: 
756: 
757: 
758:     public function next()
759:     {
760:         next($this->keys);
761:     }
762: 
763: 
764: 
765:     public function valid()
766:     {
767:         return current($this->keys) !== FALSE;
768:     }
769: 
770: 
771: 
772:     /********************* interface ArrayAccess ****************d*g**/
773: 
774: 
775: 
776:     /**
777:      * Mimic row.
778:      * @param  string row ID
779:      * @param  ActiveRow
780:      * @return NULL
781:      */
782:     public function offsetSet($key, $value)
783:     {
784:         $this->execute();
785:         $this->data[$key] = $value;
786:     }
787: 
788: 
789: 
790:     /**
791:      * Returns specified row.
792:      * @param  string row ID
793:      * @return ActiveRow or NULL if there is no such row
794:      */
795:     public function offsetGet($key)
796:     {
797:         $this->execute();
798:         return $this->data[$key];
799:     }
800: 
801: 
802: 
803:     /**
804:      * Tests if row exists.
805:      * @param  string row ID
806:      * @return bool
807:      */
808:     public function offsetExists($key)
809:     {
810:         $this->execute();
811:         return isset($this->data[$key]);
812:     }
813: 
814: 
815: 
816:     /**
817:      * Removes row from result set.
818:      * @param  string row ID
819:      * @return NULL
820:      */
821:     public function offsetUnset($key)
822:     {
823:         $this->execute();
824:         unset($this->data[$key]);
825:     }
826: 
827: }
828: 
Nette Framework 2.0.6 API API documentation generated by ApiGen 2.7.0