1: <?php
  2: 
  3: /**
  4:  * This file is part of the "dibi" - smart database abstraction layer.
  5:  *
  6:  * Copyright (c) 2005 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: 
 13: 
 14: /**
 15:  * Default implementation of IDataSource for dibi.
 16:  *
 17:  * @author     David Grudl
 18:  * @package    dibi
 19:  *
 20:  * @property-read DibiConnection $connection
 21:  * @property-read DibiResult $result
 22:  * @property-read DibiResultIterator $iterator
 23:  * @property-read int $totalCount
 24:  */
 25: class DibiDataSource extends DibiObject implements IDataSource
 26: {
 27:     /** @var DibiConnection */
 28:     private $connection;
 29: 
 30:     /** @var string */
 31:     private $sql;
 32: 
 33:     /** @var DibiResult */
 34:     private $result;
 35: 
 36:     /** @var int */
 37:     private $count;
 38: 
 39:     /** @var int */
 40:     private $totalCount;
 41: 
 42:     /** @var array */
 43:     private $cols = array();
 44: 
 45:     /** @var array */
 46:     private $sorting = array();
 47: 
 48:     /** @var array */
 49:     private $conds = array();
 50: 
 51:     /** @var int */
 52:     private $offset;
 53: 
 54:     /** @var int */
 55:     private $limit;
 56: 
 57: 
 58: 
 59:     /**
 60:      * @param  string  SQL command or table or view name, as data source
 61:      * @param  DibiConnection  connection
 62:      */
 63:     public function __construct($sql, DibiConnection $connection)
 64:     {
 65:         if (strpbrk($sql, " \t\r\n") === FALSE) {
 66:             $this->sql = $connection->getDriver()->escape($sql, dibi::IDENTIFIER); // table name
 67:         } else {
 68:             $this->sql = '(' . $sql . ') t'; // SQL command
 69:         }
 70:         $this->connection = $connection;
 71:     }
 72: 
 73: 
 74: 
 75:     /**
 76:      * Selects columns to query.
 77:      * @param  string|array  column name or array of column names
 78:      * @param  string        column alias
 79:      * @return DibiDataSource  provides a fluent interface
 80:      */
 81:     public function select($col, $as = NULL)
 82:     {
 83:         if (is_array($col)) {
 84:             $this->cols = $col;
 85:         } else {
 86:             $this->cols[$col] = $as;
 87:         }
 88:         $this->result = NULL;
 89:         return $this;
 90:     }
 91: 
 92: 
 93: 
 94:     /**
 95:      * Adds conditions to query.
 96:      * @param  mixed  conditions
 97:      * @return DibiDataSource  provides a fluent interface
 98:      */
 99:     public function where($cond)
100:     {
101:         if (is_array($cond)) {
102:             // TODO: not consistent with select and orderBy
103:             $this->conds[] = $cond;
104:         } else {
105:             $this->conds[] = func_get_args();
106:         }
107:         $this->result = $this->count = NULL;
108:         return $this;
109:     }
110: 
111: 
112: 
113:     /**
114:      * Selects columns to order by.
115:      * @param  string|array  column name or array of column names
116:      * @param  string        sorting direction
117:      * @return DibiDataSource  provides a fluent interface
118:      */
119:     public function orderBy($row, $sorting = 'ASC')
120:     {
121:         if (is_array($row)) {
122:             $this->sorting = $row;
123:         } else {
124:             $this->sorting[$row] = $sorting;
125:         }
126:         $this->result = NULL;
127:         return $this;
128:     }
129: 
130: 
131: 
132:     /**
133:      * Limits number of rows.
134:      * @param  int limit
135:      * @param  int offset
136:      * @return DibiDataSource  provides a fluent interface
137:      */
138:     public function applyLimit($limit, $offset = NULL)
139:     {
140:         $this->limit = $limit;
141:         $this->offset = $offset;
142:         $this->result = $this->count = NULL;
143:         return $this;
144:     }
145: 
146: 
147: 
148:     /**
149:      * Returns the dibi connection.
150:      * @return DibiConnection
151:      */
152:     final public function getConnection()
153:     {
154:         return $this->connection;
155:     }
156: 
157: 
158: 
159:     /********************* executing ****************d*g**/
160: 
161: 
162: 
163:     /**
164:      * Returns (and queries) DibiResult.
165:      * @return DibiResult
166:      */
167:     public function getResult()
168:     {
169:         if ($this->result === NULL) {
170:             $this->result = $this->connection->nativeQuery($this->__toString());
171:         }
172:         return $this->result;
173:     }
174: 
175: 
176: 
177:     /**
178:      * @return DibiResultIterator
179:      */
180:     public function getIterator()
181:     {
182:         return $this->getResult()->getIterator();
183:     }
184: 
185: 
186: 
187:     /**
188:      * Generates, executes SQL query and fetches the single row.
189:      * @return DibiRow|FALSE  array on success, FALSE if no next record
190:      */
191:     public function fetch()
192:     {
193:         return $this->getResult()->fetch();
194:     }
195: 
196: 
197: 
198:     /**
199:      * Like fetch(), but returns only first field.
200:      * @return mixed  value on success, FALSE if no next record
201:      */
202:     public function fetchSingle()
203:     {
204:         return $this->getResult()->fetchSingle();
205:     }
206: 
207: 
208: 
209:     /**
210:      * Fetches all records from table.
211:      * @return array
212:      */
213:     public function fetchAll()
214:     {
215:         return $this->getResult()->fetchAll();
216:     }
217: 
218: 
219: 
220:     /**
221:      * Fetches all records from table and returns associative tree.
222:      * @param  string  associative descriptor
223:      * @return array
224:      */
225:     public function fetchAssoc($assoc)
226:     {
227:         return $this->getResult()->fetchAssoc($assoc);
228:     }
229: 
230: 
231: 
232:     /**
233:      * Fetches all records from table like $key => $value pairs.
234:      * @param  string  associative key
235:      * @param  string  value
236:      * @return array
237:      */
238:     public function fetchPairs($key = NULL, $value = NULL)
239:     {
240:         return $this->getResult()->fetchPairs($key, $value);
241:     }
242: 
243: 
244: 
245:     /**
246:      * Discards the internal cache.
247:      * @return void
248:      */
249:     public function release()
250:     {
251:         $this->result = $this->count = $this->totalCount = NULL;
252:     }
253: 
254: 
255: 
256:     /********************* exporting ****************d*g**/
257: 
258: 
259: 
260:     /**
261:      * Returns this data source wrapped in DibiFluent object.
262:      * @return DibiFluent
263:      */
264:     public function toFluent()
265:     {
266:         return $this->connection->select('*')->from('(%SQL) t', $this->__toString());
267:     }
268: 
269: 
270: 
271:     /**
272:      * Returns this data source wrapped in DibiDataSource object.
273:      * @return DibiDataSource
274:      */
275:     public function toDataSource()
276:     {
277:         return new self($this->__toString(), $this->connection);
278:     }
279: 
280: 
281: 
282:     /**
283:      * Returns SQL query.
284:      * @return string
285:      */
286:     public function __toString()
287:     {
288:         return $this->connection->translate('
289:             SELECT %n', (empty($this->cols) ? '*' : $this->cols), '
290:             FROM %SQL', $this->sql, '
291:             %ex', $this->conds ? array('WHERE %and', $this->conds) : NULL, '
292:             %ex', $this->sorting ? array('ORDER BY %by', $this->sorting) : NULL, '
293:             %ofs %lmt', $this->offset, $this->limit
294:         );
295:     }
296: 
297: 
298: 
299:     /********************* counting ****************d*g**/
300: 
301: 
302: 
303:     /**
304:      * Returns the number of rows in a given data source.
305:      * @return int
306:      */
307:     public function count()
308:     {
309:         if ($this->count === NULL) {
310:             $this->count = $this->conds || $this->offset || $this->limit
311:                 ? (int) $this->connection->nativeQuery(
312:                     'SELECT COUNT(*) FROM (' . $this->__toString() . ') t'
313:                 )->fetchSingle()
314:                 : $this->getTotalCount();
315:         }
316:         return $this->count;
317:     }
318: 
319: 
320: 
321:     /**
322:      * Returns the number of rows in a given data source.
323:      * @return int
324:      */
325:     public function getTotalCount()
326:     {
327:         if ($this->totalCount === NULL) {
328:             $this->totalCount = (int) $this->connection->nativeQuery(
329:                 'SELECT COUNT(*) FROM ' . $this->sql
330:             )->fetchSingle();
331:         }
332:         return $this->totalCount;
333:     }
334: 
335: }
336: