1 <?php
  2 /**
  3  * Neevo - Tiny database layer for PHP. (http://neevo.smasty.net)
  4  *
  5  * This source file is subject to the MIT license that is bundled
  6  * with this package in the file license.txt.
  7  *
  8  * Copyright (c) 2012 Smasty (http://smasty.net)
  9  *
 10  */
 11 
 12 namespace Neevo;
 13 
 14 use Countable;
 15 use DateTime;
 16 use InvalidArgumentException;
 17 use IteratorAggregate;
 18 use Traversable;
 19 
 20 
 21 /**
 22  * Represents a result. Can be iterated, counted and provides fluent interface.
 23  *
 24  * @method Result as($alias)
 25  *
 26  * @author Smasty
 27  */
 28 class Result extends BaseStatement implements IteratorAggregate, Countable {
 29 
 30 
 31     /** @var string */
 32     protected $grouping;
 33 
 34     /** @var array */
 35     protected $columns = array();
 36 
 37     /** @var array */
 38     protected $joins;
 39 
 40     /** @var string */
 41     protected $tableAlias;
 42 
 43     /** @var resource */
 44     protected $resultSet;
 45 
 46     /** @var array */
 47     protected $columnTypes = array();
 48 
 49     /** @var bool */
 50     protected $detectTypes;
 51 
 52     /** @var string */
 53     private $rowClass = 'Neevo\\Row';
 54 
 55     /** @var ResultIterator */
 56     private $iterator;
 57 
 58 
 59     /**
 60      * Creates SELECT statement.
 61      * @param Connection $connection
 62      * @param string|array|Traversable $columns
 63      * @param string|Result $source Table name or subquery
 64      * @throws InvalidArgumentException
 65      */
 66     public function __construct(Connection $connection, $columns = null, $source = null){
 67         parent::__construct($connection);
 68 
 69         // Input check
 70         if($columns === null && $source === null)
 71             throw new InvalidArgumentException('Missing select source.');
 72         if($source === null){
 73             $source = $columns;
 74             $columns = '*';
 75         }
 76         if(!is_string($source) && !$source instanceof self)
 77             throw new InvalidArgumentException('Source must be a string or Neevo\\Result.');
 78 
 79         $columns = is_string($columns)
 80             ? explode(',', $columns) : ($columns instanceof Traversable
 81                 ? iterator_to_array($columns) : (array) $columns);
 82 
 83         if(empty($columns))
 84             throw new InvalidArgumentException('No columns given.');
 85 
 86         if($source instanceof self)
 87             $this->subqueries[] = $source;
 88 
 89         $this->type = Manager::STMT_SELECT;
 90         $this->columns = array_map('trim', $columns);
 91         $this->source = $source;
 92         $this->detectTypes = (bool) $connection['result']['detectTypes'];
 93         $this->setRowClass($connection['rowClass']);
 94     }
 95 
 96 
 97     /**
 98      * Destroys the result set resource and free memory.
 99      */
100     public function __destruct(){
101         try{
102             $this->connection->getDriver()->freeResultSet($this->resultSet);
103         } catch(ImplementationException $e){}
104 
105         $this->resultSet = null;
106     }
107 
108 
109     public function __call($name, $args){
110         $name = strtolower($name);
111         if($name === 'as')
112             return $this->setAlias(isset($args[0]) ? $args[0] : null);
113         return parent::__call($name, $args);
114     }
115 
116 
117     /**
118      * Defines grouping rule.
119      * @param string $rule
120      * @param string $having Optional
121      * @return Result fluent interface
122      */
123     public function group($rule, $having = null){
124         if($this->validateConditions())
125             return $this;
126 
127         $this->resetState();
128         $this->grouping = array($rule, $having);
129         return $this;
130     }
131 
132 
133     /**
134      * Performs JOIN on tables.
135      * @param string|Result|Literal $source Table name or subquery
136      * @param string|Literal $condition
137      * @return Result fluent interface
138      */
139     public function join($source, $condition){
140         if($this->validateConditions())
141             return $this;
142 
143         if(!(is_string($source) || $source instanceof self || $source instanceof Literal))
144             throw new InvalidArgumentException('Source must be a string, Neevo\\Literal or Neevo\\Result.');
145         if(!(is_string($condition) || $condition instanceof Literal))
146             throw new InvalidArgumentException('Condition must be a string or Neevo\\Literal.');
147 
148         if($source instanceof self)
149             $this->subqueries[] = $source;
150 
151         $this->resetState();
152         $type = (func_num_args() > 2) ? func_get_arg(2) : '';
153 
154         $this->joins[] = array($source, $condition, $type);
155 
156         return $this;
157     }
158 
159 
160     /**
161      * Performs LEFT JOIN on tables.
162      * @param string|Result|Literal $source Table name or subquery
163      * @param string|Literal $condition
164      * @return Result fluent interface
165      */
166     public function leftJoin($source, $condition){
167         return $this->join($source, $condition, Manager::JOIN_LEFT);
168     }
169 
170 
171     /**
172      * Performs INNER JOIN on tables.
173      * @param string|Result|Literal $source Table name or subquery
174      * @param string|Literal $condition
175      * @return Result fluent interface
176      */
177     public function innerJoin($source, $condition){
178         return $this->join($source, $condition, Manager::JOIN_INNER);
179     }
180 
181 
182     /**
183      * Adjusts the LIMIT and OFFSET clauses according to defined page number and number of items per page.
184      * @param int $page Page number
185      * @param int $items Number of items per page
186      * @return Result fluent interface
187      * @throws InvalidArgumentException
188      */
189     public function page($page, $items){
190         if($page < 1 || $items < 1)
191             throw new InvalidArgumentException('Both arguments must be positive integers.');
192         return $this->limit((int) $items, (int) ($items * --$page));
193     }
194 
195 
196     /**
197      * Fetches the row on current position.
198      * @return Row|bool
199      */
200     public function fetch(){
201         $this->performed || $this->run();
202 
203         $row = $this->connection->getDriver()->fetch($this->resultSet);
204         if(!is_array($row))
205             return false;
206 
207         // Type converting
208         if($this->detectTypes)
209             $this->detectTypes();
210         if(!empty($this->columnTypes)){
211             foreach($this->columnTypes as $col => $type){
212                 if(isset($row[$col]))
213                     $row[$col] = $this->convertType($row[$col], $type);
214             }
215         }
216         return new $this->rowClass($row, $this);
217     }
218 
219 
220     /**
221      * Fetches all rows in result set.
222      * @param int $limit Limit number of returned rows
223      * @param int $offset Seek to offset (fails on unbuffered results)
224      * @return Row[]
225      */
226     public function fetchAll($limit = null, $offset = null){
227         $limit = $limit === null ? -1 : (int) $limit;
228         if($offset !== null)
229             $this->seek((int) $offset);
230 
231         $row = $this->fetch();
232         if(!$row)
233             return array();
234 
235         $rows = array();
236         do{
237             if($limit === 0)
238                 break;
239             $rows[] = $row;
240             $limit--;
241         } while($row = $this->fetch());
242 
243         return $rows;
244     }
245 
246 
247     /**
248      * Fetches the first value from current row.
249      * @return mixed
250      */
251     public function fetchSingle(){
252         $this->performed || $this->run();
253         $row = $this->connection->getDriver()->fetch($this->resultSet);
254 
255         if(!$row)
256             return false;
257         $value = reset($row);
258 
259         // Type converting
260         if($this->detectTypes)
261             $this->detectTypes();
262         if(!empty($this->columnTypes)){
263             $key = key($row);
264             if(isset($this->columnTypes[$key]))
265                 $value = $this->convertType($value, $this->columnTypes[$key]);
266         }
267 
268         return $value;
269     }
270 
271 
272     /**
273      * Fetches rows as $key=>$value pairs.
274      * @param string $key Key column
275      * @param string $value Value column. NULL for whole row.
276      * @return Row[]
277      */
278     public function fetchPairs($key, $value = null){
279         $clone = clone $this;
280 
281         // If executed w/o needed cols, force exec w/ them.
282         if(!in_array('*', $clone->columns)){
283             if($value !== null)
284                 $clone->columns = array($key, $value);
285             elseif(!in_array($key, $clone->columns))
286                 $clone->columns[] = $key;
287         }
288         $k = substr($key, ($pk = strrpos($key, '.')) ? $pk+1 : 0);
289         $v = $value === null ? null : substr($value, ($pv = strrpos($value, '.')) ? $pv+1 : 0);
290 
291         $rows = array();
292         while($row = $clone->fetch()){
293             if(!$row)
294                 return array();
295             $rows[$row[$k]] = $value === null ? $row : $row->$v;
296         }
297 
298         return $rows;
299     }
300 
301 
302     /**
303      * Moves internal result pointer.
304      * @param int $offset
305      * @return bool
306      * @throws NeevoException
307      */
308     public function seek($offset){
309         $this->performed || $this->run();
310         $seek = $this->connection->getDriver()->seek($this->resultSet, $offset);
311         if($seek)
312             return $seek;
313         throw new NeevoException("Cannot seek to offset $offset.");
314     }
315 
316 
317     public function rows(){
318         return $this->count();
319     }
320 
321 
322     /**
323      * Counts number of rows.
324      * @param string $column
325      * @return int
326      * @throws DriverException
327      */
328     public function count($column = null){
329         if($column === null){
330             $this->performed || $this->run();
331             return (int) $this->connection->getDriver()->getNumRows($this->resultSet);
332         }
333         return $this->aggregation("COUNT(:$column)");
334     }
335 
336 
337     /**
338      * Executes aggregation function.
339      * @param string $function
340      * @return mixed
341      */
342     public function aggregation($function){
343         $clone = clone $this;
344         $clone->columns = (array) $function;
345         return $clone->fetchSingle();
346     }
347 
348 
349     /**
350      * Returns the sum of column values.
351      * @param string $column
352      * @return mixed
353      */
354     public function sum($column){
355         return $this->aggregation("SUM($column)");
356     }
357 
358 
359     /**
360      * Returns the minimum value of column.
361      * @param string $column
362      * @return mixed
363      */
364     public function min($column){
365         return $this->aggregation("MIN($column)");
366     }
367 
368 
369     /**
370      * Returns the maximum value of column.
371      * @param string $column
372      * @return mixed
373      */
374     public function max($column){
375         return $this->aggregation("MAX($column)");
376     }
377 
378 
379     /**
380      * Explains performed query.
381      * @return array
382      */
383     public function explain(){
384         $driver = $this->getConnection()->getDriver();
385         $query = $driver->runQuery("EXPLAIN $this");
386 
387         $rows = array();
388         while($row = $driver->fetch($query)){
389             $rows[] = $row;
390         }
391 
392         return $rows;
393     }
394 
395 
396     /**
397      * Sets column type.
398      * @param string $column
399      * @param string $type
400      * @return Result fluent interface
401      */
402     public function setType($column, $type){
403         $this->columnTypes[$column] = $type;
404         return $this;
405     }
406 
407 
408     /**
409      * Sets multiple column types at once.
410      * @param array|Traversable $types
411      * @return Result fluent interface
412      */
413     public function setTypes($types){
414         if(!($types instanceof Traversable || is_array($types)))
415             throw new InvalidArgumentException('Types must be an array or Traversable.');
416         foreach($types as $column => $type){
417             $this->setType($column, $type);
418         }
419         return $this;
420     }
421 
422 
423     /**
424      * Detects column types.
425      * @return Result fluent interface
426      */
427     public function detectTypes(){
428         $table = $this->getTable();
429         $this->performed || $this->run();
430 
431         // Try fetch from cache
432         $types = (array) $this->connection->getCache()->fetch($table . '_detectedTypes');
433 
434         if(empty($types)){
435             try{
436                 $types = $this->connection->getDriver()->getColumnTypes($this->resultSet, $table);
437             } catch(NeevoException $e){
438                 return $this;
439             }
440         }
441 
442         foreach((array) $types as $col => $type){
443             $this->columnTypes[$col] = $this->resolveType($type);
444         }
445 
446         $this->connection->getCache()->store($table . '_detectedTypes', $this->columnTypes);
447         return $this;
448     }
449 
450 
451     /**
452      * Resolves vendor column type.
453      * @param string $type
454      * @return string
455      */
456     protected function resolveType($type){
457         static $patterns = array(
458             'bool|bit' => Manager::BOOL,
459             'bin|blob|bytea' => Manager::BINARY,
460             'string|char|text|bigint|longlong' => Manager::TEXT,
461             'int|long|byte|serial|counter' => Manager::INT,
462             'float|real|double|numeric|number|decimal|money|currency' => Manager::FLOAT,
463             'time|date|year' => Manager::DATETIME
464         );
465 
466         foreach($patterns as $vendor => $universal){
467             if(preg_match("~$vendor~i", $type))
468                 return $universal;
469         }
470         return Manager::TEXT;
471     }
472 
473 
474     /**
475      * Converts value to a specified type.
476      * @param mixed $value
477      * @param string $type
478      * @return mixed
479      */
480     protected function convertType($value, $type){
481         $dateFormat = $this->connection['result']['formatDate'];
482         if($value === null)
483             return null;
484         switch($type){
485             case Manager::TEXT:
486                 return (string) $value;
487 
488             case Manager::INT:
489                 return (int) $value;
490 
491             case Manager::FLOAT:
492                 return (float) $value;
493 
494             case Manager::BOOL:
495                 return ((bool) $value) && $value !== 'f' && $value !== 'F';
496 
497             case Manager::BINARY:
498                 return $this->connection->getDriver()->unescape($value, $type);
499 
500             case Manager::DATETIME:
501                 if((int) $value === 0)
502                     return null;
503                 elseif(!$dateFormat)
504                     return new DateTime(is_numeric($value) ? date('Y-m-d H:i:s', $value) : $value);
505                 elseif($dateFormat == 'U')
506                     return is_numeric($value) ? (int) $value : strtotime($value);
507                 elseif(is_numeric($value))
508                     return date($dateFormat, $value);
509                 else{
510                     $d = new DateTime($value);
511                     return $d->format($dateFormat);
512                 }
513 
514             default:
515                 return $value;
516         }
517     }
518 
519 
520     /**
521      * Sets table alias to be used when in subquery.
522      * @param string $alias
523      * @return Result fluent interface
524      */
525     public function setAlias($alias){
526         $this->tableAlias = $alias;
527         return $this;
528     }
529 
530 
531     /**
532      * Returns table alias used in subquery.
533      * @return string|null
534      */
535     public function getAlias(){
536         return $this->tableAlias ? $this->tableAlias : null;
537     }
538 
539 
540     /**
541      * Sets class to use as a row class.
542      * @param string $className
543      * @return Result fluent interface
544      * @throws NeevoException
545      */
546     public function setRowClass($className){
547         if(!class_exists($className))
548             throw new NeevoException("Cannot set row class '$className'.");
549         $this->rowClass = $className;
550         return $this;
551     }
552 
553 
554     /**
555      * Returns the result iterator.
556      * @return ResultIterator
557      */
558     public function getIterator(){
559         if(!isset($this->iterator))
560             $this->iterator = new ResultIterator($this);
561         return $this->iterator;
562     }
563 
564 
565     public function getGrouping(){
566         return $this->grouping;
567     }
568 
569 
570     public function getColumns(){
571         return $this->columns;
572     }
573 
574 
575     public function getJoins(){
576         if(!empty($this->joins))
577             return $this->joins;
578         return array();
579     }
580 
581 
582     /**
583      * Returns full table name (with prefix) if available.
584      * @return string|null
585      */
586     public function getTable(){
587         if($this->source instanceof self)
588             return null;
589         return parent::getTable();
590     }
591 
592 
593     /**
594      * Returns the source for the statement.
595      * @return string|Result
596      */
597     public function getSource(){
598         return $this->source;
599     }
600 
601 
602 }
603 

Neevo Public API API documentation generated by ApiGen 2.8.0