1: <?php
  2: /**
  3:  * ark.database
  4:  * @copyright 2015 Liu Dong <ddliuhb@gmail.com>
  5:  * @license MIT
  6:  */
  7: 
  8: namespace Ark\Database;
  9: 
 10: /**
 11:  * Database query builder
 12:  */
 13: class QueryBuilder
 14: {
 15:     protected $db;
 16:     protected $statement;
 17:     protected $params = array();
 18:     protected $positionParams = array();
 19:     protected $query = array();
 20:     protected $sql;
 21:     
 22:     /**
 23:      * Constructor
 24:      * 
 25:      * @param \Ark\Database\Connection $db Database connection
 26:      */
 27:     public function __construct($db){
 28:         $this->db = $db;
 29:     }
 30:     
 31:     protected function matchAlias($entry){
 32:         if(preg_match('#^(.*?)(?i:\s+as\s+|\s+)(.*)$#', $entry, $matches)){
 33:             return array(
 34:                 $matches[1],
 35:                 $matches[2],
 36:             );
 37:         }
 38:         else{
 39:             return false;
 40:         }
 41:     }
 42:     
 43:     protected function splitParts($parts){
 44:         return preg_split('#\s*,\s*#', trim($parts), -1, PREG_SPLIT_NO_EMPTY);
 45:     }
 46:     
 47:     /**
 48:      * Reset builder
 49:      * 
 50:      * @return \Ark\Database\QueryBuilder
 51:      */
 52:     public function reset(){
 53:         $this->statement = null;
 54:         $this->params = array();
 55:         $this->positionParams = array();
 56:         $this->query = array();
 57:         $this->sql = null;
 58:         
 59:         return $this;
 60:     }
 61:     
 62:     /**
 63:      * Prepare PDO statement
 64:      *
 65:      * @return \Ark\Database\QueryBuilder
 66:      */
 67:     public function prepare(){
 68:         if(null === $this->statement){
 69:             $this->statement = $this->db->prepare($this->getSql());
 70:         }
 71:         
 72:         return $this;
 73:     }
 74:     
 75:     /**
 76:      * @see http://php.net/manual/en/pdostatement.bindparam.php
 77:      * @return \Ark\Database\QueryBuilder
 78:      */
 79:     public function bindParam(){
 80:         $this->prepare();
 81:         call_user_func_array(array($this->statement, 'bindParam'), func_get_args());
 82: 
 83:         return $this;
 84:     }
 85:     
 86:     /**
 87:      * @see http://php.net/manual/en/pdostatement.bindvalue.php
 88:      * @return \Ark\Database\QueryBuilder
 89:      */
 90:     public function bindValue(){
 91:         $this->prepare();
 92:         call_user_func_array(array($this->statement, 'bindValue'), func_get_args());
 93:         
 94:         return $this;
 95:     }
 96:     
 97:     /**
 98:      * Bind values
 99:      * 
100:      * @param  array $values
101:      * @return \Ark\Database\QueryBuilder
102:      */
103:     public function bindValues($values){
104:         $this->prepare();
105:         
106:         foreach($values as $k=> $value){
107:             $this->statement->bindValue($k, $value);
108:         }
109:         
110:         return $this;
111:     }
112:     
113:     /**
114:      * Merge params
115:      * 
116:      * @param  array $params
117:      * @return \Ark\Database\QueryBuilder
118:      */
119:     public function mergeParams($params){
120:         foreach($params as $k => $v){
121:             if (is_int($k)) {
122:                 $this->positionParams[] = $v;
123:             } else {
124:                 $this->params[$k] = $v;
125:             }
126:         }
127: 
128:         return $this;
129:     }
130:     
131:     /**
132:      * SELECT statement
133:      * example:
134:      *  select('contact.*, user.email)
135:      *  select(array('contact.*', 'user.email'))
136:      *  
137:      * @param string|array $fields
138:      * @return \Ark\Database\QueryBuilder
139:      */
140:     public function select($fields = '*'){
141:         if(is_string($fields) && strpos($fields, '(') !== false){
142:             $this->query['select'] = $fields;
143:         }
144:         else{
145:             if(!is_array($fields)){
146:                 $fields = $this->splitParts($fields);
147:             }
148:             foreach($fields as $k => $field){
149:                 if(false === strpos($field, '(')){
150:                     if($alias = $this->matchAlias($field)){
151:                         $fields[$k] = $this->db->quoteColumn($alias[0]).' AS '.$this->db->quoteColumn($alias[1]);
152:                     }
153:                     else{
154:                         $fields[$k] = $this->db->quoteColumn($field);
155:                     }
156:                 }
157:             }
158:             $this->query['select'] = implode(', ', $fields);
159:         }
160:         
161:         return $this;
162:     }
163:     
164:     /**
165:      * DISTINCT statement
166:      * 
167:      * @return \Ark\Database\QueryBuilder
168:      */
169:     public function distinct(){
170:         $this->query['distinct'] = true;
171:         
172:         return $this;
173:     }
174:     
175:     /**
176:      * FROM statement
177:      * example:
178:      *  from('contact, user AS U')
179:      *  from(array('contact', 'user AS U'))
180:      *  
181:      * @param string|array $tables
182:      * @return \Ark\Database\QueryBuilder
183:      */
184:     public function from($tables){
185:         if(is_string($tables)){
186:             $tables = $this->splitParts($tables);
187:         }
188:         foreach($tables as $k => $table){
189:             if($alias = $this->matchAlias($table)){
190:                 $tables[$k] = $this->db->quoteTable($alias[0]).' AS '.$this->db->quoteTable($alias[1]);
191:             }
192:             else{
193:                 $tables[$k] = $this->db->quoteTable($table);
194:             }
195:         }
196:         
197:         $this->query['from'] = implode(', ', $tables);
198:         
199:         return $this;
200:     }
201:     
202:     /**
203:      * Build condition SQL
204:      * 
205:      * @param string|array $conditions
206:      * @return string
207:      */
208:     public function buildConditions($conditions){
209:         if(!is_array($conditions)){
210:             if(null === $conditions){
211:                 return '';
212:             }
213:             return $conditions;
214:         }
215:         elseif($conditions === array()){
216:             return '';
217:         }
218:         
219:         $n = count($conditions);
220:         $operator = strtoupper($conditions[0]);
221:         if($operator === 'AND' || $operator === 'OR'){
222:             $result = array();
223:             for($i = 1; $i < $n; $i++){
224:                 $condition = $this->buildConditions($conditions[$i]);
225:                 if('' !== $condition){
226:                     $result[] = '('.$condition.')';
227:                 }
228:             }
229:             if($result === array()){
230:                 return '';
231:             }
232:             else{
233:                 return implode(' '.$operator.' ', $result);
234:             }
235:         }
236:         
237:         if(!isset($conditions[1], $conditions[2])){
238:             return '';
239:         }
240:         
241:         $column = $this->db->quoteColumn($conditions[1]);
242:         $values = $conditions[2];
243:         if(!is_array($values)){
244:             $values = array($values);
245:         }
246:         if($operator === 'IN' || $operator === 'NOT IN'){
247:             if($values === array()){
248:                 return $operator === 'IN'?'0':'';
249:             }
250:             foreach($values as $k => $value){
251:                 $values[$k] = $this->db->quote($value);
252:             }
253:             
254:             return $column.' '.$operator.' ('.implode(',', $values).')';
255:         }
256:         elseif($operator === 'LIKE' || $operator === 'NOT LIKE' || $operator === 'OR LIKE' || $operator === 'OR NOT LIKE'){
257:             if($values === array()){
258:                 return ($operator === 'LIKE' || $operator === 'OR LIKE')?'0':'';
259:             }
260:             if($operator === 'LIKE' || $operator === 'NOT LIKE'){
261:                 $andor = 'AND';
262:             }
263:             else{
264:                 $andor = 'OR';
265:                 $operator = $operator === 'OR LIKE'?'LIKE':'NOT LIKE';
266:             }
267:             
268:             $result = array();
269:             foreach($values as $k => $value){
270:                 $result[] = $column.' '.$operator.' '.$this->db->quote($value);
271:             }
272:             
273:             return implode($andor, $result);
274:         }
275:         else{
276:             throw new Exception('Invalid operator "'.$operator.'"');
277:         }
278:     }
279:     
280:     /**
281:      * WHERE statement
282:      * 
283:      * @param string|array $conditions
284:      * @param array $params
285:      * @return \Ark\Database\QueryBuilder
286:      */
287:     public function where($conditions, $params = array()){
288:         $this->mergeParams($params);
289:         $this->query['where'] = $this->buildConditions($conditions);
290:         
291:         return $this;
292:     }
293:     
294:     /**
295:      * ORDER BY statement
296:      * example:
297:      *  orderBy('id DESC')
298:      *  orderBy('firstname, lastname DESC')
299:      *  
300:      * @param string|array $fields
301:      * @return \Ark\Database\QueryBuilder
302:      */
303:     public function orderBy($fields){
304:         if(!is_array($fields)){
305:             $fields = $this->splitParts($fields);
306:         }
307:         
308:         foreach($fields as $k => $field){
309:             if(preg_match('#^(.*?)\s+(asc|desc)$#i', $field, $matches)){
310:                 $fields[$k] = $this->db->quoteColumn($matches[1]).' '.$matches[2];
311:             }
312:             else{
313:                 $fields[$k] = $this->db->quoteColumn($field);
314:             }
315:         }
316:         
317:         $this->query['order'] = implode(',', $fields);
318:         
319:         return $this;
320:     }
321:     
322:     /**
323:      * LIMIT statement
324:      * 
325:      * @param int $limit
326:      * @param int $offset
327:      * @return \Ark\Database\QueryBuilder
328:      */
329:     public function limit($limit, $offset = null){
330:         $this->query['limit'] = $limit;
331:         if(null !== $offset){
332:             $this->offset($offset);
333:         }
334:         
335:         return $this;
336:     }
337:     
338:     /**
339:      * OFFSET statement
340:      * 
341:      * @param int $offset
342:      * @return \Ark\Database\QueryBuilder
343:      */
344:     public function offset($offset){
345:         $this->query['offset'] = $offset;
346:         
347:         return $this;
348:     }
349:     
350:     /**
351:      * * JOIN statement
352:      * 
353:      * @param string $type LEFT JOIN|RIGHT JOIN...
354:      * @param string $table
355:      * @param string|array $conditions
356:      * @param array $params
357:      * @return \Ark\Database\QueryBuilder
358:      */
359:     protected function anyJoin($type, $table, $conditions = '', $params = array()){
360:         $this->mergeParams($params);
361:         if($alias = $this->matchAlias($table)){
362:             $table = $this->db->quoteTable($alias[0]).' AS '.$this->db->quoteTable($alias[1]);
363:         }
364:         else{
365:             $table = $this->db->quoteTable($table);
366:         }
367:         $conditions = $this->buildConditions($conditions);
368:         if('' !== $conditions){
369:             $conditions = ' ON '.$conditions;
370:         }
371:         
372:         if(isset($this->query['join']) && is_string($this->query['join'])){
373:             $this->query['join'] = array($this->query['join']);
374:         }
375:         
376:         $this->query['join'][] = $type.' '.$table.' '.$conditions;
377:         
378:         return $this;
379:     }
380:     
381:     /**
382:      * JOIN statement
383:      * 
384:      * @param string $table
385:      * @param string|array $conditions
386:      * @param array $params
387:      * @return \Ark\Database\QueryBuilder
388:      */
389:     public function join($table, $conditions = '', $params = array()){
390:         return $this->anyJoin('JOIN', $table, $conditions, $params);
391:     }
392:     
393:     /**
394:      * LEFT JOIN statement
395:      * 
396:      * @param string $table
397:      * @param string|array $conditions
398:      * @param array $params
399:      * @return \Ark\Database\QueryBuilder
400:      */
401:     public function leftJoin($table, $conditions = '', $params = array()){
402:         return $this->anyJoin('LEFT JOIN', $table, $conditions, $params);
403:     }
404:     
405:     /**
406:      * RIGHT JOIN statement
407:      * 
408:      * @param string $table
409:      * @param string|array $conditions
410:      * @param array $params
411:      * @return \Ark\Database\QueryBuilder
412:      */
413:     public function rightJoin($table, $condtions = '', $params = array()){
414:         return $this->anyJoin('RIGHT JOIN', $table, $conditions, $params);
415:     }
416:     
417:     /**
418:      * GROUP BY statement
419:      * 
420:      * @param string|array $fields
421:      * @return \Ark\Database\QueryBuilder
422:      */
423:     public function groupBy($fields){
424:         if(!is_array($fields)){
425:             $fields = $this->splitParts($fields);
426:         }
427:         
428:         foreach($fields as $k => $field){
429:             $fields[$k] = $this->db->quoteColumn($field);
430:         }
431:         
432:         $this->query['group'] = implode(',', $fields);
433:         
434:         return $this;
435:     }
436:     
437:     /**
438:      * HAVING statement
439:      * 
440:      * @param string|array $conditions
441:      * @param array $params
442:      * @return \Ark\Database\QueryBuilder
443:      */
444:     public function having($conditions, $params = array()){
445:         $this->mergeParams($params);
446:         $this->query['having'] = $this->buildConditions($conditions);
447:         
448:         return $this;
449:     }
450:     
451:     /**
452:      * UNION statement
453:      * 
454:      * @param string $sql
455:      * @return \Ark\Database\QueryBuilder
456:      */
457:     public function union($sql){
458:         if(isset($this->query['union']) && is_string($this->query['union'])){
459:             $this->query['union'][] = $this->query['union'];
460:         }
461:         
462:         $this->query['union'][] = $sql;
463:         
464:         return $this;
465:     }
466:     
467:     /**
468:      * Build query SQL
469:      * 
470:      * @param array $query use $this->query if not specified
471:      * @return string
472:      */
473:     public function buildQuery($query = null){
474:         if(null === $query){
475:             $query = $this->query;
476:         }
477:         
478:         $sql = "SELECT ";
479:         if(isset($query['distinct']) && $query['distinct']){
480:             $sql .= 'DISTINCT ';
481:         }
482:         $sql .= isset($query['select'])?$query['select']:'*';
483:         
484:         if(!isset($query['from'])){
485:             return false;
486:         }
487:         
488:         $sql .= "\nFROM ".$query['from'];
489:         if(isset($query['join'])){
490:             $sql .= "\n".(is_array($query['join'])?implode("\n", $query['join']):$query['join']);
491:         }
492:         if(isset($query['where']) && $query['where'] !== ''){
493:             $sql .= "\nWHERE ".$query['where'];
494:         }
495:         if(isset($query['group'])){
496:             $sql .= "\nGROUP BY ".$query['group'];
497:             if(isset($query['having'])){
498:                 $sql .= "\nHAVING ".$query['having'];
499:             }
500:         }
501:         if(isset($query['order'])){
502:             $sql .= "\n ORDER BY ".$query['order'];
503:         }
504:         $limit = isset($query['limit'])?$query['limit']:0;
505:         $offset = isset($query['offset'])?$query['offset']:0;
506:         $sql = $this->db->buildLimitOffset($sql, $limit, $offset);
507:         
508:         if(isset($query['union'])){
509:             $sql .= "\n".(is_array($query['union'])?implode("\n", $query['union']):$query['union']);
510:         }
511:         
512:         return $sql;
513:     }
514:     
515:     /**
516:      * Set SQL for this command
517:      * 
518:      * @param string $sql
519:      * @return \Ark\Database\QueryBuilder
520:      */
521:     public function setSql($sql){
522:         if('' !== $prefix = $this->db->getPrefix()){
523:             $sql = preg_replace('#{{(.*?)}}#', $prefix.'\1', $sql);
524:         }
525:         $this->sql = $sql;
526:         
527:         return $this;
528:     }
529:     
530:     /**
531:      * Get SQL for this command
532:      * 
533:      * @return string
534:      */
535:     public function getSql(){
536:         if(null === $this->sql){
537:             if(!empty($this->query)){
538:                 $this->setSql($this->buildQuery());
539:             }
540:             else{
541:                 return false;
542:             }
543:         }
544:         
545:         return $this->sql;
546:     }
547:     
548:     /**
549:      * Prepare statement before query
550:      */
551:     protected function beginQuery(){
552:         $autoSlave = $this->db->isAutoSlave();
553:         if ($autoSlave) {
554:             $this->db->switchConnection('slave');
555:         }
556: 
557:         $this->prepare();
558: 
559:         if ($autoSlave) {
560:             $this->db->switchConnection();
561:         }
562: 
563:         if(false === $this->statement->execute($this->params?$this->params:$this->positionParams)){
564:             $info = $this->statement->errorInfo();
565:             throw new Exception(sprintf('Statement error #%s: %s', $info[0], $info[2]));
566:         }
567:     }
568:     
569:     /**
570:      * Query
571:      * Statement cursor should be closed after fetching data($statement->closeCursor)
572:      * 
573:      * @param array $params
574:      * @return \PDOStatement
575:      */
576:     public function query($params = array()){
577:         $this->mergeParams($params);
578:         $this->beginQuery();
579:         return $this->statement;
580:     }
581:     
582:     /**
583:      * Get query result as array
584:      * 
585:      * @param array $params
586:      * @return array
587:      */
588:     public function queryAll($params = array()){
589:         $this->mergeParams($params);
590:         $this->beginQuery();
591:         $rst = $this->statement->fetchAll(\PDO::FETCH_ASSOC);
592:         $this->statement->closeCursor();
593:         return $rst;
594:     }
595:     
596:     /**
597:      * Get first row of result
598:      * 
599:      * @param array $params
600:      * @return array
601:      */
602:     public function queryRow($params = array()){
603:         $this->mergeParams($params);
604:         $this->beginQuery();
605:         $rst = $this->statement->fetch(\PDO::FETCH_ASSOC);
606:         $this->statement->closeCursor();
607:         return $rst;
608:     }
609:     
610:     /**
611:      * Get first column of result set
612:      * 
613:      * @param array $params
614:      * @return array
615:      */
616:     public function queryColumn($params = array()){
617:         $this->mergeParams($params);
618:         $this->beginQuery();
619:         $rst = $this->statement->fetchAll(\PDO::FETCH_COLUMN);
620:         $this->statement->closeCursor();
621:         return $rst;
622:     }
623:     
624:     /**
625:      * Get first column of first row of result set
626:      * 
627:      * @param array $params
628:      * @return string
629:      */
630:     public function queryValue($params = array()){
631:         $this->mergeParams($params);
632:         $this->beginQuery();
633:         $rst = $this->statement->fetchColumn();
634:         $this->statement->closeCursor();
635:         
636:         return $rst;
637:     }
638:     
639:     /**
640:      * Execute statement and return rows affected
641:      * 
642:      * @param array $params
643:      * @return  int|boolean
644:      */
645:     public function execute($params = array()){
646:         $this->prepare();
647:         $this->mergeParams($params);
648: 
649:         foreach ($this->positionParams as $index => $value) {
650:             $this->statement->bindValue($index + 1, $value);
651:         }
652: 
653:         if(false === $rst = $this->statement->execute($this->params)){
654:             return false;
655:         }
656:         
657:         return $this->statement->rowCount();
658:     }
659:     
660:     /**
661:      * Insert data
662:      * 
663:      * @param string $table
664:      * @param array $values
665:      * @return last insert id, false on error
666:      */
667:     public function insert($table, $values){
668:         $keys = array();
669:         $params = array();
670:         $placeholders = array();
671:         foreach($values as $k => $v){
672:             $keys[] = $this->db->quoteColumn($k);
673:             $params[':'.$k] = $v;
674:             $placeholders[] = ':'.$k;
675:         }
676:         $sql = "INSERT INTO ".$this->db->quoteTable($table).' ('.implode(', ',$keys).') VALUES ('.implode(',', $placeholders).')';
677:         $result = $this->setSql($sql)->execute($params);
678:         if (!$result) {
679:             return false;
680:         }
681: 
682:         return $this->db->lastInsertId();
683:     }
684:     
685:     /**
686:      * Update table
687:      * 
688:      * @param string $table
689:      * @param string|array $values
690:      * @param string|array $conditions
691:      * @param array $params
692:      * @return int Rows affected, false on error
693:      */
694:     public function update($table, $values, $conditions = '', $params = array()){
695:         $updates = array();
696:         if (!is_array($values)) {
697:             $values = array($values);
698:         }
699:         foreach($values as $k => $value){
700:             if (is_integer($k)) {
701:                 $updates[] = $value;
702:             } else {
703:                 $updates[] = $this->db->quoteColumn($k).' = :'.$k;
704:                 $params[':'.$k] = $value;
705:             }
706:         }
707:         
708:         $sql = "UPDATE ".$this->db->quoteTable($table).' SET '.implode(', ', $updates);
709:         $conditions = $this->buildConditions($conditions);
710:         if('' !== $conditions){
711:             $sql .= ' WHERE '.$conditions;
712:         }
713:         
714:         return $this->setSql($sql)->execute($params);
715:     }
716:     
717:     /**
718:      * Delete
719:      * 
720:      * @param string $table
721:      * @param array $values
722:      * @param string|array $conditions
723:      * @param array $params
724:      * @return int Rows affected, false on error
725:      */
726:     public function delete($table, $conditions = '', $params = array()){
727:         $sql = 'DELETE FROM '.$this->db->quoteTable($table);
728:         $conditions = $this->buildConditions($conditions);
729:         if('' !== $conditions){
730:             $sql .= ' WHERE '.$conditions;
731:         }
732:         
733:         return $this->setSql($sql)->execute($params);
734:     }
735: }
736: