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