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

Neevo Public API API documentation generated by ApiGen 2.8.0