1 <?php
2 3 4 5 6 7
8
9 namespace Cross\DB\SQLAssembler;
10
11 use Cross\Exception\CoreException;
12
13 14 15 16 17
18 class SQLAssembler
19 {
20 21 22
23 protected $sql;
24
25 26 27
28 protected $params;
29
30 31 32 33 34
35 protected $table_prefix;
36
37 38 39 40 41
42 protected $offset_is_valid = true;
43
44 45 46 47 48
49 function __construct($table_prefix = '')
50 {
51 $this->table_prefix = $table_prefix;
52 }
53
54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 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 100 101 102 103 104 105 106 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 131 132 133 134 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 151 152 153 154 155 156 157 158 159 160 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 190 191 192 193 194
195 public function select($fields = '*', $modifier = '')
196 {
197 return "SELECT {$modifier} {$this->parseFields($fields)} ";
198 }
199
200 201 202 203 204 205 206 207 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 216 217 218 219 220
221 public function replace($table, $modifier = '')
222 {
223 return "REPLACE {$modifier} {$table} ";
224 }
225
226 227 228 229
230 public function from($table)
231 {
232 return "FROM {$table} ";
233 }
234
235 236 237 238 239 240
241 public function where($where, array &$params)
242 {
243 return "WHERE {$this->parseWhere($where, $params)} ";
244 }
245
246 247 248 249 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 265 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 278 279
280 public function orderBy($order)
281 {
282 return "ORDER BY {$this->parseOrder($order)} ";
283 }
284
285 286 287 288
289 public function groupBy($group)
290 {
291 return "GROUP BY {$this->parseGroup($group)} ";
292 }
293
294 295 296 297
298 public function having($having)
299 {
300 return "HAVING {$having} ";
301 }
302
303 304 305 306
307 public function procedure($procedure)
308 {
309 return "PROCEDURE {$procedure} ";
310 }
311
312 313 314 315
316 public function into($var_name)
317 {
318 return "INTO {$var_name} ";
319 }
320
321 322 323 324 325
326 public function set($data, array &$params = array())
327 {
328 return "SET {$this->parseData($data, $params)} ";
329 }
330
331 332 333 334
335 public function on($on)
336 {
337 return "ON {$on} ";
338 }
339
340 341 342 343 344 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 363 364 365 366 367 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 396 397 398 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 417 418 419 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 434
435 public function getSQL()
436 {
437 return $this->sql;
438 }
439
440 441 442
443 protected function setSQL($sql)
444 {
445 $this->sql = $sql;
446 }
447
448 449 450
451 public function getParams()
452 {
453 return $this->params;
454 }
455
456 457 458 459 460
461 public function getPrefix()
462 {
463 return $this->table_prefix;
464 }
465
466 467 468
469 protected function setParams($params)
470 {
471 $this->params = $params;
472 }
473
474 475 476 477 478 479 480 481 482 483 484 485 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 588 589 590 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 649 650 651 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 687 688 689 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 718 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