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:  * dibi SQL builder via fluent interfaces. EXPERIMENTAL!
 16:  *
 17:  * @author     David Grudl
 18:  * @package    dibi
 19:  *
 20:  * @property-read string $command
 21:  * @property-read DibiConnection $connection
 22:  * @property-read DibiResultIterator $iterator
 23:  * @method DibiFluent select($field)
 24:  * @method DibiFluent distinct()
 25:  * @method DibiFluent from($table)
 26:  * @method DibiFluent where($cond)
 27:  * @method DibiFluent groupBy($field)
 28:  * @method DibiFluent having($cond)
 29:  * @method DibiFluent orderBy($field)
 30:  * @method DibiFluent limit(int $limit)
 31:  * @method DibiFluent offset(int $offset)
 32:  */
 33: class DibiFluent extends DibiObject implements IDataSource
 34: {
 35:     const REMOVE = FALSE;
 36: 
 37:     /** @var array */
 38:     public static $masks = array(
 39:         'SELECT' => array('SELECT', 'DISTINCT', 'FROM', 'WHERE', 'GROUP BY',
 40:             'HAVING', 'ORDER BY', 'LIMIT', 'OFFSET'),
 41:         'UPDATE' => array('UPDATE', 'SET', 'WHERE', 'ORDER BY', 'LIMIT'),
 42:         'INSERT' => array('INSERT', 'INTO', 'VALUES', 'SELECT'),
 43:         'DELETE' => array('DELETE', 'FROM', 'USING', 'WHERE', 'ORDER BY', 'LIMIT'),
 44:     );
 45: 
 46:     /** @var array  default modifiers for arrays */
 47:     public static $modifiers = array(
 48:         'SELECT' => '%n',
 49:         'FROM' => '%n',
 50:         'IN' => '%in',
 51:         'VALUES' => '%l',
 52:         'SET' => '%a',
 53:         'WHERE' => '%and',
 54:         'HAVING' => '%and',
 55:         'ORDER BY' => '%by',
 56:         'GROUP BY' => '%by',
 57:     );
 58: 
 59:     /** @var array  clauses separators */
 60:     public static $separators = array(
 61:         'SELECT' => ',',
 62:         'FROM' => ',',
 63:         'WHERE' => 'AND',
 64:         'GROUP BY' => ',',
 65:         'HAVING' => 'AND',
 66:         'ORDER BY' => ',',
 67:         'LIMIT' => FALSE,
 68:         'OFFSET' => FALSE,
 69:         'SET' => ',',
 70:         'VALUES' => ',',
 71:         'INTO' => FALSE,
 72:     );
 73: 
 74:     /** @var array  clauses */
 75:     public static $clauseSwitches = array(
 76:         'JOIN' => 'FROM',
 77:         'INNER JOIN' => 'FROM',
 78:         'LEFT JOIN' => 'FROM',
 79:         'RIGHT JOIN' => 'FROM',
 80:     );
 81: 
 82:     /** @var DibiConnection */
 83:     private $connection;
 84: 
 85:     /** @var string */
 86:     private $command;
 87: 
 88:     /** @var array */
 89:     private $clauses = array();
 90: 
 91:     /** @var array */
 92:     private $flags = array();
 93: 
 94:     /** @var array */
 95:     private $cursor;
 96: 
 97:     /** @var DibiHashMap  normalized clauses */
 98:     private static $normalizer;
 99: 
100: 
101: 
102:     /**
103:      * @param  DibiConnection
104:      */
105:     public function __construct(DibiConnection $connection)
106:     {
107:         $this->connection = $connection;
108: 
109:         if (self::$normalizer === NULL) {
110:             self::$normalizer = new DibiHashMap(array(__CLASS__, '_formatClause'));
111:         }
112:     }
113: 
114: 
115: 
116:     /**
117:      * Appends new argument to the clause.
118:      * @param  string clause name
119:      * @param  array  arguments
120:      * @return DibiFluent  provides a fluent interface
121:      */
122:     public function __call($clause, $args)
123:     {
124:         $clause = self::$normalizer->$clause;
125: 
126:         // lazy initialization
127:         if ($this->command === NULL) {
128:             if (isset(self::$masks[$clause])) {
129:                 $this->clauses = array_fill_keys(self::$masks[$clause], NULL);
130:             }
131:             $this->cursor = & $this->clauses[$clause];
132:             $this->cursor = array();
133:             $this->command = $clause;
134:         }
135: 
136:         // auto-switch to a clause
137:         if (isset(self::$clauseSwitches[$clause])) {
138:             $this->cursor = & $this->clauses[self::$clauseSwitches[$clause]];
139:         }
140: 
141:         if (array_key_exists($clause, $this->clauses)) {
142:             // append to clause
143:             $this->cursor = & $this->clauses[$clause];
144: 
145:             // TODO: really delete?
146:             if ($args === array(self::REMOVE)) {
147:                 $this->cursor = NULL;
148:                 return $this;
149:             }
150: 
151:             if (isset(self::$separators[$clause])) {
152:                 $sep = self::$separators[$clause];
153:                 if ($sep === FALSE) { // means: replace
154:                     $this->cursor = array();
155: 
156:                 } elseif (!empty($this->cursor)) {
157:                     $this->cursor[] = $sep;
158:                 }
159:             }
160: 
161:         } else {
162:             // append to currect flow
163:             if ($args === array(self::REMOVE)) {
164:                 return $this;
165:             }
166: 
167:             $this->cursor[] = $clause;
168:         }
169: 
170:         if ($this->cursor === NULL) {
171:             $this->cursor = array();
172:         }
173: 
174:         // special types or argument
175:         if (count($args) === 1) {
176:             $arg = $args[0];
177:             // TODO: really ignore TRUE?
178:             if ($arg === TRUE) { // flag
179:                 return $this;
180: 
181:             } elseif (is_string($arg) && preg_match('#^[a-z:_][a-z0-9_.:]*$#i', $arg)) { // identifier
182:                 $args = array('%n', $arg);
183: 
184:             } elseif (is_array($arg) || ($arg instanceof Traversable && !$arg instanceof self)) { // any array
185:                 if (isset(self::$modifiers[$clause])) {
186:                     $args = array(self::$modifiers[$clause], $arg);
187: 
188:                 } elseif (is_string(key($arg))) { // associative array
189:                     $args = array('%a', $arg);
190:                 }
191:             } // case $arg === FALSE is handled above
192:         }
193: 
194:         foreach ($args as $arg) {
195:             if ($arg instanceof self) {
196:                 $arg = "($arg)";
197:             }
198:             $this->cursor[] = $arg;
199:         }
200: 
201:         return $this;
202:     }
203: 
204: 
205: 
206:     /**
207:      * Switch to a clause.
208:      * @param  string clause name
209:      * @return DibiFluent  provides a fluent interface
210:      */
211:     public function clause($clause, $remove = FALSE)
212:     {
213:         $this->cursor = & $this->clauses[self::$normalizer->$clause];
214: 
215:         if ($remove) { // deprecated, use removeClause
216:             trigger_error(__METHOD__ . '(..., TRUE) is deprecated; use removeClause() instead.', E_USER_NOTICE);
217:             $this->cursor = NULL;
218: 
219:         } elseif ($this->cursor === NULL) {
220:             $this->cursor = array();
221:         }
222: 
223:         return $this;
224:     }
225: 
226: 
227: 
228:     /**
229:      * Removes a clause.
230:      * @param  string clause name
231:      * @return DibiFluent  provides a fluent interface
232:      */
233:     public function removeClause($clause)
234:     {
235:         $this->clauses[self::$normalizer->$clause] = NULL;
236:         return $this;
237:     }
238: 
239: 
240: 
241:     /**
242:      * Change a SQL flag.
243:      * @param  string  flag name
244:      * @param  bool  value
245:      * @return DibiFluent  provides a fluent interface
246:      */
247:     public function setFlag($flag, $value = TRUE)
248:     {
249:         $flag = strtoupper($flag);
250:         if ($value) {
251:             $this->flags[$flag] = TRUE;
252:         } else {
253:             unset($this->flags[$flag]);
254:         }
255:         return $this;
256:     }
257: 
258: 
259: 
260:     /**
261:      * Is a flag set?
262:      * @param  string  flag name
263:      * @return bool
264:      */
265:     final public function getFlag($flag)
266:     {
267:         return isset($this->flags[strtoupper($flag)]);
268:     }
269: 
270: 
271: 
272:     /**
273:      * Returns SQL command.
274:      * @return string
275:      */
276:     final public function getCommand()
277:     {
278:         return $this->command;
279:     }
280: 
281: 
282: 
283:     /**
284:      * Returns the dibi connection.
285:      * @return DibiConnection
286:      */
287:     final public function getConnection()
288:     {
289:         return $this->connection;
290:     }
291: 
292: 
293: 
294:     /********************* executing ****************d*g**/
295: 
296: 
297: 
298:     /**
299:      * Generates and executes SQL query.
300:      * @param  mixed what to return?
301:      * @return DibiResult|int  result set object (if any)
302:      * @throws DibiException
303:      */
304:     public function execute($return = NULL)
305:     {
306:         $res = $this->connection->query($this->_export());
307:         return $return === dibi::IDENTIFIER ? $this->connection->getInsertId() : $res;
308:     }
309: 
310: 
311: 
312:     /**
313:      * Generates, executes SQL query and fetches the single row.
314:      * @return DibiRow|FALSE  array on success, FALSE if no next record
315:      */
316:     public function fetch()
317:     {
318:         if ($this->command === 'SELECT') {
319:             return $this->connection->query($this->_export(NULL, array('%lmt', 1)))->fetch();
320:         } else {
321:             return $this->connection->query($this->_export())->fetch();
322:         }
323:     }
324: 
325: 
326: 
327:     /**
328:      * Like fetch(), but returns only first field.
329:      * @return mixed  value on success, FALSE if no next record
330:      */
331:     public function fetchSingle()
332:     {
333:         if ($this->command === 'SELECT') {
334:             return $this->connection->query($this->_export(NULL, array('%lmt', 1)))->fetchSingle();
335:         } else {
336:             return $this->connection->query($this->_export())->fetchSingle();
337:         }
338:     }
339: 
340: 
341: 
342:     /**
343:      * Fetches all records from table.
344:      * @param  int  offset
345:      * @param  int  limit
346:      * @return array
347:      */
348:     public function fetchAll($offset = NULL, $limit = NULL)
349:     {
350:         return $this->connection->query($this->_export(NULL, array('%ofs %lmt', $offset, $limit)))->fetchAll();
351:     }
352: 
353: 
354: 
355:     /**
356:      * Fetches all records from table and returns associative tree.
357:      * @param  string  associative descriptor
358:      * @return array
359:      */
360:     public function fetchAssoc($assoc)
361:     {
362:         return $this->connection->query($this->_export())->fetchAssoc($assoc);
363:     }
364: 
365: 
366: 
367:     /**
368:      * Fetches all records from table like $key => $value pairs.
369:      * @param  string  associative key
370:      * @param  string  value
371:      * @return array
372:      */
373:     public function fetchPairs($key = NULL, $value = NULL)
374:     {
375:         return $this->connection->query($this->_export())->fetchPairs($key, $value);
376:     }
377: 
378: 
379: 
380:     /**
381:      * Required by the IteratorAggregate interface.
382:      * @param  int  offset
383:      * @param  int  limit
384:      * @return DibiResultIterator
385:      */
386:     public function getIterator($offset = NULL, $limit = NULL)
387:     {
388:         return $this->connection->query($this->_export(NULL, array('%ofs %lmt', $offset, $limit)))->getIterator();
389:     }
390: 
391: 
392: 
393:     /**
394:      * Generates and prints SQL query or it's part.
395:      * @param  string clause name
396:      * @return bool
397:      */
398:     public function test($clause = NULL)
399:     {
400:         return $this->connection->test($this->_export($clause));
401:     }
402: 
403: 
404: 
405:     /**
406:      * @return int
407:      */
408:     public function count()
409:     {
410:         return (int) $this->connection->query(
411:             'SELECT COUNT(*) FROM (%ex', $this->_export(), ') AS [data]'
412:         )->fetchSingle();
413:     }
414: 
415: 
416: 
417:     /********************* exporting ****************d*g**/
418: 
419: 
420: 
421:     /**
422:      * @return DibiDataSource
423:      */
424:     public function toDataSource()
425:     {
426:         return new DibiDataSource($this->connection->translate($this->_export()), $this->connection);
427:     }
428: 
429: 
430: 
431:     /**
432:      * Returns SQL query.
433:      * @return string
434:      */
435:     final public function __toString()
436:     {
437:         return $this->connection->translate($this->_export());
438:     }
439: 
440: 
441: 
442:     /**
443:      * Generates parameters for DibiTranslator.
444:      * @param  string clause name
445:      * @return array
446:      */
447:     protected function _export($clause = NULL, $args = array())
448:     {
449:         if ($clause === NULL) {
450:             $data = $this->clauses;
451: 
452:         } else {
453:             $clause = self::$normalizer->$clause;
454:             if (array_key_exists($clause, $this->clauses)) {
455:                 $data = array($clause => $this->clauses[$clause]);
456:             } else {
457:                 return array();
458:             }
459:         }
460: 
461:         foreach ($data as $clause => $statement) {
462:             if ($statement !== NULL) {
463:                 $args[] = $clause;
464:                 if ($clause === $this->command && $this->flags) {
465:                     $args[] = implode(' ', array_keys($this->flags));
466:                 }
467:                 foreach ($statement as $arg) $args[] = $arg;
468:             }
469:         }
470: 
471:         return $args;
472:     }
473: 
474: 
475: 
476:     /**
477:      * Format camelCase clause name to UPPER CASE.
478:      * @param  string
479:      * @return string
480:      * @internal
481:      */
482:     public static function _formatClause($s)
483:     {
484:         if ($s === 'order' || $s === 'group') {
485:             $s .= 'By';
486:             trigger_error("Did you mean '$s'?", E_USER_NOTICE);
487:         }
488:         return strtoupper(preg_replace('#[a-z](?=[A-Z])#', '$0 ', $s));
489:     }
490: 
491: 
492: 
493:     public function __clone()
494:     {
495:         // remove references
496:         foreach ($this->clauses as $clause => $val) {
497:             $this->clauses[$clause] = & $val;
498:             unset($val);
499:         }
500:         $this->cursor = & $foo;
501:     }
502: 
503: }
504: