1 <?php
  2 /**
  3  * Cross - a micro PHP 5 framework
  4  *
  5  * @link        http://www.crossphp.com
  6  * @license     MIT License
  7  */
  8 
  9 namespace Cross\DB\SQLAssembler;
 10 
 11 use Cross\Exception\CoreException;
 12 
 13 /**
 14  * @author wonli <wonli@live.com>
 15  * Class SQLAssembler
 16  * @package Cross\DB\SQLAssembler
 17  */
 18 class SQLAssembler
 19 {
 20     /**
 21      * @var string
 22      */
 23     protected $sql;
 24 
 25     /**
 26      * @var string
 27      */
 28     protected $params;
 29 
 30     /**
 31      * 表前缀
 32      *
 33      * @var string
 34      */
 35     protected $table_prefix;
 36 
 37     /**
 38      * offset()在limit()中已经传递了第二个参数时不再生效
 39      *
 40      * @var bool
 41      */
 42     protected $offset_is_valid = true;
 43 
 44     /**
 45      * 初始化时可以指定表前缀
 46      *
 47      * @param string $table_prefix
 48      */
 49     function __construct($table_prefix = '')
 50     {
 51         $this->table_prefix = $table_prefix;
 52     }
 53 
 54     /**
 55      * 插入
 56      *
 57      * @param string $table 表名称
 58      * @param array $data 要处理的数据关联数组
 59      * @param bool $multi 是否批量插入数据
 60      * <pre>
 61      *  批量插入数据时$data的结构如下:
 62      *      $data = array(
 63      *          'fields' => array(字段1,字段2,...),
 64      *          'values' => array(
 65      *                      array(字段1的值, 字段2的值),
 66      *                      array(字段1的值, 字段2的值))
 67      *      );
 68      * </pre>
 69      */
 70     public function add($table, &$data, $multi = false)
 71     {
 72         $params = array();
 73         if (true === $multi) {
 74             $field_str = $value_str = '';
 75             if (empty($data['fields']) || empty($data['values'])) {
 76                 $data = $this->arrayToMultiAddFormat($data);
 77             }
 78 
 79             $params = $data['values'];
 80             foreach ($data['fields'] as $d) {
 81                 $field_str .= "`{$d}`,";
 82                 $value_str .= '?,';
 83             }
 84 
 85             $fields = trim($field_str, ',');
 86             $values = trim($value_str, ',');
 87             $into_fields = "({$fields}) VALUES ({$values})";
 88         } else {
 89             $into_fields = $this->parseData($data, $params, 'insert');
 90         }
 91 
 92         $this->setSQL("INSERT INTO {$table} {$into_fields}");
 93         $this->setParams($params);
 94     }
 95 
 96     /**
 97      * 带分页功能的查询
 98      *
 99      * @param string $table 表名称, 复杂情况下, 以LEFT JOIN为例: table_a a LEFT JOIN table_b b ON a.id=b.aid
100      * @param string $fields 要查询的字段 所有字段的时候为'*'
101      * @param string $where 查询条件
102      * @param int|string $order 排序
103      * @param array $page 分页参数 默认返回50条记录
104      * @param int|string $group_by
105      * @return mixed|void
106      * @throws CoreException
107      */
108     public function find($table, $fields, $where, $order = 1, array &$page = array('p' => 1, 'limit' => 50), $group_by = 1)
109     {
110         $params = array();
111         $field_str = $this->parseFields($fields);
112         $where_str = $this->parseWhere($where, $params);
113         $order_str = $this->parseOrder($order);
114 
115         $p = ($page['p'] - 1) * $page['limit'];
116         if (1 !== $group_by) {
117             $group_str = $this->parseGroup($group_by);
118             $sql = "SELECT {$field_str} FROM {$table} WHERE {$where_str} GROUP BY {$group_str} ORDER BY {$order_str} LIMIT {$p}, {$page['limit']}";
119         } else {
120             $sql = "SELECT {$field_str} FROM {$table} WHERE {$where_str} ORDER BY {$order_str} LIMIT {$p}, {$page['limit']}";
121         }
122 
123         $this->setSQL($sql);
124         $this->setParams($params);
125     }
126 
127     /**
128      * 更新
129      *
130      * @param string $table
131      * @param string $data
132      * @param string $where
133      * @return mixed|void
134      * @throws CoreException
135      */
136     public function update($table, $data, $where)
137     {
138         $params = array();
139         $fields = $this->parseData($data, $params);
140         $where_str = $this->parseWhere($where, $params);
141 
142         $fields = trim($fields, ',');
143         $this->setSQL("UPDATE {$table} SET {$fields} WHERE {$where_str}");
144         $this->setParams($params);
145     }
146 
147     /**
148      * 删除
149      *
150      * @param string $table
151      * @param string|array $where
152      * @param bool $multi 是否批量删除数据
153      *      $where = array(
154      *          'fields' => array(字段1,字段2,...),
155      *          'values' => array(
156      *                      array(字段1的值, 字段2的值),
157      *                      array(字段1的值, 字段2的值))
158      *      );
159      * @return mixed|void
160      * @throws CoreException
161      */
162     public function del($table, $where, $multi = false)
163     {
164         $params = array();
165         if (true === $multi) {
166             if (empty($where ['fields']) || empty($where ['values'])) {
167                 throw new CoreException('data format error!');
168             }
169 
170             $where_condition = array();
171             foreach ($where ['fields'] as $d) {
172                 $where_condition[] = "{$d} = ?";
173             }
174 
175             $where_str = implode(' AND ', $where_condition);
176             foreach ($where ['values'] as $p) {
177                 $params[] = $p;
178             }
179 
180         } else {
181             $where_str = $this->parseWhere($where, $params);
182         }
183 
184         $this->setSQL("DELETE FROM {$table} WHERE {$where_str}");
185         $this->setParams($params);
186     }
187 
188     /**
189      * select
190      *
191      * @param string $fields
192      * @param string $modifier
193      * @return string
194      */
195     public function select($fields = '*', $modifier = '')
196     {
197         return "SELECT {$modifier} {$this->parseFields($fields)} ";
198     }
199 
200     /**
201      * insert
202      *
203      * @param string $table
204      * @param string $modifier
205      * @param array $data
206      * @param array $params
207      * @return string
208      */
209     public function insert($table, $modifier = '', $data, array &$params = array())
210     {
211         return "INSERT {$modifier} INTO {$table} {$this->parseData($data, $params, 'insert')} ";
212     }
213 
214     /**
215      * replace
216      *
217      * @param string $table
218      * @param string $modifier
219      * @return string
220      */
221     public function replace($table, $modifier = '')
222     {
223         return "REPLACE {$modifier} {$table} ";
224     }
225 
226     /**
227      * @param $table
228      * @return string
229      */
230     public function from($table)
231     {
232         return "FROM {$table} ";
233     }
234 
235     /**
236      * @param string|array $where
237      * @param array $params
238      * @return string
239      * @throws CoreException
240      */
241     public function where($where, array &$params)
242     {
243         return "WHERE {$this->parseWhere($where, $params)} ";
244     }
245 
246     /**
247      * @param int $start
248      * @param bool|int $end
249      * @return string
250      */
251     public function limit($start, $end = false)
252     {
253         if ($end) {
254             $end = (int)$end;
255             $this->offset_is_valid = false;
256             return "LIMIT {$start}, {$end} ";
257         }
258 
259         $start = (int)$start;
260         return "LIMIT {$start} ";
261     }
262 
263     /**
264      * @param int $offset
265      * @return string
266      */
267     public function offset($offset)
268     {
269         if ($this->offset_is_valid) {
270             return "OFFSET {$offset} ";
271         }
272 
273         return "";
274     }
275 
276     /**
277      * @param $order
278      * @return string
279      */
280     public function orderBy($order)
281     {
282         return "ORDER BY {$this->parseOrder($order)} ";
283     }
284 
285     /**
286      * @param $group
287      * @return string
288      */
289     public function groupBy($group)
290     {
291         return "GROUP BY {$this->parseGroup($group)} ";
292     }
293 
294     /**
295      * @param $having
296      * @return string
297      */
298     public function having($having)
299     {
300         return "HAVING {$having} ";
301     }
302 
303     /**
304      * @param $procedure
305      * @return string
306      */
307     public function procedure($procedure)
308     {
309         return "PROCEDURE {$procedure} ";
310     }
311 
312     /**
313      * @param $var_name
314      * @return string
315      */
316     public function into($var_name)
317     {
318         return "INTO {$var_name} ";
319     }
320 
321     /**
322      * @param string $data
323      * @param array $params
324      * @return string
325      */
326     public function set($data, array &$params = array())
327     {
328         return "SET {$this->parseData($data, $params)} ";
329     }
330 
331     /**
332      * @param string $on
333      * @return string
334      */
335     public function on($on)
336     {
337         return "ON {$on} ";
338     }
339 
340     /**
341      * 解析字段
342      *
343      * @param string|array $fields
344      * @return string
345      */
346     public function parseFields($fields)
347     {
348         if (empty($fields)) {
349             $field_str = '*';
350         } else {
351             if (is_array($fields)) {
352                 $field_str = implode(',', $fields);
353             } else {
354                 $field_str = $fields;
355             }
356         }
357 
358         return $field_str;
359     }
360 
361     /**
362      * 解析where
363      *
364      * @param string|array $where
365      * @param array $params
366      * @return string
367      * @throws CoreException
368      */
369     public function parseWhere($where, array &$params)
370     {
371         if (!empty($where)) {
372             if (is_array($where)) {
373                 if (isset($where[1])) {
374                     $where_str = $where[0];
375                     if (!is_array($where[1])) {
376                         $params[] = $where[1];
377                     } else {
378                         foreach ($where[1] as $w) {
379                             $params[] = $w;
380                         }
381                     }
382                 } else {
383                     $where_str = $this->parseWhereFromHashMap($where, $params);
384                 }
385             } else {
386                 $where_str = $where;
387             }
388         } else {
389             $where_str = '1=1';
390         }
391         return $where_str;
392     }
393 
394     /**
395      * 解析order
396      *
397      * @param string $order
398      * @return int|string
399      */
400     public function parseOrder($order)
401     {
402         if (!empty($order)) {
403             if (is_array($order)) {
404                 $order_str = implode(',', $order);
405             } else {
406                 $order_str = $order;
407             }
408         } else {
409             $order_str = 1;
410         }
411 
412         return $order_str;
413     }
414 
415     /**
416      * 解析group by
417      *
418      * @param string $group_by
419      * @return int
420      */
421     public function parseGroup($group_by)
422     {
423         if (!empty($group_by)) {
424             $group_str = $group_by;
425         } else {
426             $group_str = 1;
427         }
428 
429         return $group_str;
430     }
431 
432     /**
433      * @return string
434      */
435     public function getSQL()
436     {
437         return $this->sql;
438     }
439 
440     /**
441      * @param $sql
442      */
443     protected function setSQL($sql)
444     {
445         $this->sql = $sql;
446     }
447 
448     /**
449      * @return string|array
450      */
451     public function getParams()
452     {
453         return $this->params;
454     }
455 
456     /**
457      * 获取表前缀
458      *
459      * @return string
460      */
461     public function getPrefix()
462     {
463         return $this->table_prefix;
464     }
465 
466     /**
467      * @param $params
468      */
469     protected function setParams($params)
470     {
471         $this->params = $params;
472     }
473 
474     /**
475      * 解析where条件
476      *
477      * @param string $operator 字段和值之间的操作符
478      * @param string $field 字段名
479      * @param string|array $field_config 字段值配置
480      * @param bool $is_mixed_field 区别默认字段和复合字段(带括号的字段)
481      * @param string $condition_connector 每个条件之间的连接符
482      * @param string $connector 每个字段之间的连接符
483      * @param array $params 包含字段值的数组(prepare之后传递的参数)
484      * @return array
485      * @throws CoreException
486      */
487     protected function parseCondition($operator, $field, $field_config, $is_mixed_field, $condition_connector, $connector, array &$params)
488     {
489         $condition = array();
490         switch ($connector) {
491             case 'OR':
492                 if (!is_array($field_config)) {
493                     $field_config = array($field_config);
494                 }
495                 foreach ($field_config as $field_single_config) {
496                     if (is_array($field_single_config)) {
497                         list($operator, $single_field_value) = $field_single_config;
498                         $params [] = $single_field_value;
499                     } else {
500                         $params [] = $field_single_config;
501                     }
502                     $condition[' OR '][] = "{$field} {$operator} ?";
503                 }
504                 break;
505 
506             case 'AND':
507                 if ($is_mixed_field) {
508                     $condition[" {$condition_connector} "][] = $field;
509                     if (is_array($field_config)) {
510                         foreach ($field_config as $f) {
511                             $params [] = $f;
512                         }
513                     } else {
514                         $params[] = $field_config;
515                     }
516                 } else {
517                     if (is_array($field_config)) {
518                         foreach ($field_config as $and_exp_val) {
519                             $ex_operator = '=';
520                             if (is_array($and_exp_val)) {
521                                 list($ex_operator, $n_value) = $and_exp_val;
522                                 $and_exp_val = $n_value;
523                             }
524                             $condition[' AND '][] = "{$field} {$ex_operator} ?";
525                             $params [] = $and_exp_val;
526                         }
527                     } else {
528                         $params [] = $field_config;
529                         $condition[' AND '][] = "{$field} {$operator} ?";
530                     }
531                 }
532                 break;
533 
534             case 'IN':
535             case 'NOT IN':
536                 if (!is_array($field_config)) {
537                     throw new CoreException('IN or NOT IN need a array parameter');
538                 }
539 
540                 $in_where_condition = array();
541                 foreach ($field_config as $in_field_val) {
542                     $params[] = $in_field_val;
543                     $in_where_condition [] = '?';
544                 }
545 
546                 $in_where_condition_string = implode(',', $in_where_condition);
547                 $condition[" {$condition_connector} "][] = "{$field} {$connector} ($in_where_condition_string)";
548                 break;
549 
550             case 'BETWEEN':
551             case 'NOT BETWEEN':
552                 if (!is_array($field_config)) {
553                     throw new CoreException('BETWEEN need a array parameter');
554                 }
555 
556                 if (!isset($field_config[0]) || !isset($field_config[1])) {
557                     throw new CoreException('BETWEEN parameter error!');
558                 }
559 
560                 $condition[" {$condition_connector} "][] = "{$field} {$connector} ? AND ?";
561                 $params[] = $field_config[0];
562                 $params[] = $field_config[1];
563                 break;
564 
565             case '#SQL#':
566                 if (is_array($field_config)) {
567                     list($operator, $sql_segment) = $field_config;
568                 } else {
569                     $sql_segment = $field_config;
570                 }
571 
572                 $condition[" {$condition_connector} "][] = "{$field} {$operator} {$sql_segment}";
573                 break;
574 
575             default:
576                 $operator = $connector;
577                 $condition[" {$condition_connector} "][] = "{$field} {$operator} ?";
578                 $params [] = $field_config;
579         }
580 
581         return $condition;
582     }
583 
584     /**
585      * 解析数据
586      *
587      * @param $data
588      * @param array $params
589      * @param string $format
590      * @return string
591      */
592     private function parseData($data, array &$params, $format = 'normal')
593     {
594         if (!empty($data)) {
595             if (is_array($data)) {
596                 if (isset($data[1])) {
597                     $sql_segment = $data[0];
598                     if (!is_array($data[1])) {
599                         $params[] = $data[1];
600                     } else {
601                         foreach ($data[1] as $d) {
602                             $params[] = $d;
603                         }
604                     }
605                 } else {
606                     if ('insert' === $format) {
607                         $data_keys = $data_values = array();
608                         foreach ($data as $key => $value) {
609                             $data_keys[] = $key;
610                             $data_values[] = '?';
611                             $params[] = $value;
612                         }
613 
614                         $fields = implode(',', $data_keys);
615                         $values = implode(',', $data_values);
616                         $sql_segment = "({$fields}) VALUES ({$values})";
617                     } else {
618                         $segment = '';
619                         foreach ($data as $key => $value) {
620                             if (is_array($value)) {
621                                 if (isset($value[1])) {
622                                     $segment .= ", {$key} = {$value[0]}";
623                                     $params[] = $value[1];
624                                 } else {
625                                     $segment .= ", {$key} = {$value[0]}";
626                                 }
627                             } else {
628                                 $segment .= ", {$key} = ?";
629                                 $params[] = $value;
630                             }
631                         }
632 
633                         $sql_segment = trim($segment, ',');
634                     }
635                 }
636             } else {
637                 $sql_segment = $data;
638             }
639         } else {
640             $sql_segment = '';
641         }
642         return $sql_segment;
643     }
644 
645     /**
646      * 解析关联数组
647      *
648      * @param array $where
649      * @param array $params
650      * @return string
651      * @throws CoreException
652      */
653     private function parseWhereFromHashMap(array $where, array &$params)
654     {
655         $all_condition = array();
656         foreach ($where as $field => $field_config) {
657             $operator = '=';
658             $field = trim($field);
659             $is_mixed_field = false;
660             $condition_connector = $connector = 'AND';
661 
662             if ($field[0] == '(' && $field[strlen($field) - 1] == ')') {
663                 $is_mixed_field = true;
664             }
665 
666             if ($is_mixed_field === false && is_array($field_config)) {
667                 if (count($field_config) == 3) {
668                     list($connector, $field_true_value, $condition_connector) = $field_config;
669                 } else {
670                     list($connector, $field_true_value) = $field_config;
671                 }
672 
673                 $condition_connector = strtoupper(trim($condition_connector));
674                 $connector = strtoupper(trim($connector));
675                 $field_config = $field_true_value;
676             }
677 
678             $condition = $this->parseCondition($operator, $field, $field_config, $is_mixed_field, $condition_connector, $connector, $params);
679             $all_condition[] = $condition;
680         }
681 
682         return $this->combineWhereCondition($all_condition);
683     }
684 
685     /**
686      * 组合where条件
687      *
688      * @param $where_condition
689      * @return string
690      */
691     private function combineWhereCondition($where_condition)
692     {
693         $where = '';
694         foreach ($where_condition as $condition) {
695             foreach ($condition as $where_connector => $where_condition) {
696                 if (isset($where_condition[1])) {
697                     $where_snippet_string = implode($where_connector, $where_condition);
698                     $where_snippet = "($where_snippet_string)";
699                     $where_connector = ' AND ';
700                 } else {
701                     $where_snippet = $where_condition[0];
702                 }
703 
704                 if ('' === $where) {
705                     $where = $where_snippet;
706                 } else {
707                     $where .= $where_connector . $where_snippet;
708                 }
709             }
710         }
711         return $where;
712     }
713 
714     /**
715      * 将数组格式化成批量添加的格式
716      *
717      * @param array $data
718      * @return array
719      */
720     private function arrayToMultiAddFormat(array $data)
721     {
722         $fields = $values = array();
723         if (!empty($data)) {
724             while ($d = array_shift($data)) {
725                 $keys = array_keys($d);
726                 if (empty($fields)) {
727                     $fields = $keys;
728                 } elseif ($keys !== $fields) {
729                     continue;
730                 }
731 
732                 $values[] = array_values($d);
733             }
734         }
735 
736         return array('fields' => $fields, 'values' => $values);
737     }
738 }
739 

CrossPHP API documentation generated by ApiGen