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\Drivers;
 10 
 11 use Cross\DB\SQLAssembler\SQLAssembler;
 12 use Cross\Exception\CoreException;
 13 use Cross\I\PDOConnecter;
 14 use Cross\I\SqlInterface;
 15 use PDOException;
 16 use PDOStatement;
 17 use Exception;
 18 use PDO;
 19 
 20 /**
 21  * @author wonli <wonli@live.com>
 22  * Class PDOSqlDriver
 23  * @package Cross\DB\Drivers
 24  */
 25 class PDOSqlDriver implements SqlInterface
 26 {
 27     /**
 28      * @var PDOStatement
 29      */
 30     public $stmt;
 31 
 32     /**
 33      * @var PDO
 34      */
 35     public $pdo;
 36 
 37     /**
 38      * @var string
 39      */
 40     public $sql;
 41 
 42     /**
 43      * 链式查询每条语句的标示符
 44      *
 45      * @var int
 46      */
 47     protected $qid = 0;
 48 
 49     /**
 50      * 以qid为key,存储链式查询生成的sql语句
 51      *
 52      * @var array
 53      */
 54     protected $querySQL = array(0);
 55 
 56     /**
 57      * 联系查询生成的参数缓存
 58      *
 59      * @var array
 60      */
 61     protected $queryParams;
 62 
 63     /**
 64      * @var string|array
 65      */
 66     protected $params;
 67 
 68     /**
 69      * @var PDOConnecter
 70      */
 71     protected $connecter;
 72 
 73     /**
 74      * @var SQLAssembler
 75      */
 76     protected $SQLAssembler;
 77 
 78     /**
 79      * 创建数据库连接
 80      *
 81      * @param PDOConnecter $connecter
 82      * @param SQLAssembler $SQLAssembler
 83      * @throws CoreException
 84      */
 85     public function __construct(PDOConnecter $connecter, SQLAssembler $SQLAssembler)
 86     {
 87         $this->setConnecter($connecter);
 88         $this->setSQLAssembler($SQLAssembler);
 89 
 90         $this->pdo = $this->connecter->getPDO();
 91         if (!$this->pdo instanceof PDO) {
 92             throw new CoreException("init pdo failed!");
 93         }
 94     }
 95 
 96     /**
 97      * 获取单条数据
 98      *
 99      * @param string $table
100      * @param string $fields
101      * @param string|array $where
102      * @return mixed
103      * @throws CoreException
104      */
105     public function get($table, $fields, $where)
106     {
107         return $this->select($fields)->from($table)->where($where)->stmt()->fetch(PDO::FETCH_ASSOC);
108     }
109 
110     /**
111      * 获取表中所有数据
112      *
113      * @param string $table
114      * @param string $fields
115      * @param string|array $where
116      * @param int|string $order
117      * @param int|string $group_by
118      * @param int|string $limit 0 表示无限制
119      * @return mixed
120      * @throws CoreException
121      */
122     public function getAll($table, $fields, $where = '', $order = 1, $group_by = 1, $limit = 0)
123     {
124         $data = $this->select($fields)->from($table);
125         if ($where) {
126             $data->where($where);
127         }
128 
129         if (1 !== $group_by) {
130             $data->groupBy($group_by);
131         }
132 
133         if (1 !== $order) {
134             $data->orderBy($order);
135         }
136 
137         if (0 !== $limit) {
138             $data->limit($limit);
139         }
140 
141         return $data->stmt()->fetchAll(PDO::FETCH_ASSOC);
142     }
143 
144     /**
145      * 插入数据
146      *
147      * @see SQLAssembler::add()
148      * @param string $table
149      * @param string|array $data
150      * @param bool $multi 是否批量添加
151      * @param array $insert_data 批量添加的数据
152      * @param bool $openTA 批量添加时是否开启事务
153      * @return bool|mixed
154      * @throws CoreException
155      */
156     public function add($table, $data, $multi = false, &$insert_data = array(), $openTA = false)
157     {
158         $this->SQLAssembler->add($table, $data, $multi);
159         $this->sql = $this->SQLAssembler->getSQL();
160         $this->params = $this->SQLAssembler->getParams();
161 
162         if ($multi) {
163             $add_count = 0;
164             if (!empty($this->params)) {
165                 $inc_name = $this->getAutoIncrementName($table);
166                 $stmt = $this->prepare($this->sql);
167 
168                 if ($openTA) {
169                     $this->beginTA();
170                     try {
171                         if (!empty($this->params)) {
172                             foreach ($this->params as $p) {
173                                 if ($stmt->exec($p, true)) {
174                                     $add_data_info = array_combine($data['fields'], $p);
175                                     if ($inc_name) {
176                                         $add_data_info[$inc_name] = $this->insertId();
177                                     }
178 
179                                     $add_count++;
180                                     $insert_data[] = $add_data_info;
181                                 }
182                             }
183                         }
184                     } catch (Exception $e) {
185                         $insert_data = array();
186                         $this->rollBack();
187                         throw new CoreException($e->getMessage());
188                     }
189                     $this->commit();
190                 } else {
191                     if (!empty($this->params)) {
192                         foreach ($this->params as $p) {
193                             if ($stmt->exec($p, true)) {
194                                 $add_data_info = array_combine($data['fields'], $p);
195                                 if ($inc_name) {
196                                     $add_data_info[$inc_name] = $this->insertId();
197                                 }
198 
199                                 $add_count++;
200                                 $insert_data[] = $add_data_info;
201                             }
202                         }
203                     }
204                 }
205             }
206             return $add_count;
207         } else {
208             $add_count = $this->prepare($this->sql)->exec($this->params, true);
209             $last_insert_id = $this->insertId();
210             if ($last_insert_id > 0) {
211                 return $last_insert_id;
212             }
213 
214             return $add_count;
215         }
216     }
217 
218     /**
219      * 带分页的数据查询
220      *
221      * @param string $table
222      * @param string $fields
223      * @param string|array $where
224      * @param string|int $order
225      * @param array $page
226      * @param int $group_by
227      * @return mixed
228      * @throws CoreException
229      */
230     public function find($table, $fields, $where, $order = 1, array &$page = array('p' => 1, 'limit' => 50), $group_by = 1)
231     {
232         if (!isset($page['result_count'])) {
233             $total = $this->get($table, 'COUNT(*) as total', $where);
234             $page['result_count'] = (int)$total['total'];
235         }
236 
237         $page['limit'] = max(1, (int)$page['limit']);
238         $page['total_page'] = ceil($page['result_count'] / $page['limit']);
239 
240         if ($page['p'] <= $page['total_page']) {
241             $page['p'] = max(1, $page['p']);
242             $this->SQLAssembler->find($table, $fields, $where, $order, $page, $group_by);
243             return $this->getPrepareResult(true);
244         }
245 
246         return array();
247     }
248 
249     /**
250      * 数据更新
251      *
252      * @param string $table
253      * @param string|array $data
254      * @param string|array $where
255      * @return bool
256      * @throws CoreException
257      */
258     public function update($table, $data, $where)
259     {
260         $this->SQLAssembler->update($table, $data, $where);
261         $this->sql = $this->SQLAssembler->getSQL();
262         $this->params = $this->SQLAssembler->getParams();
263 
264         return $this->prepare($this->sql)->exec($this->params, true);
265     }
266 
267     /**
268      * 删除
269      *
270      * @see SQLAssembler::del()
271      * @param string $table
272      * @param string|array $where
273      * @param bool $multi 是否批量删除数据
274      * @param bool $openTA 是否开启事务
275      * @return bool
276      * @throws CoreException
277      */
278     public function del($table, $where, $multi = false, $openTA = false)
279     {
280         $del_count = 0;
281         $this->SQLAssembler->del($table, $where, $multi);
282         $this->sql = $this->SQLAssembler->getSQL();
283         $this->params = $this->SQLAssembler->getParams();
284         if ($multi) {
285             if ($openTA) {
286                 $this->beginTA();
287                 try {
288                     if (!empty($this->params)) {
289                         $stmt = $this->prepare($this->sql);
290                         foreach ($this->params as $p) {
291                             $del_count += $stmt->exec($p, true);
292                         }
293                     }
294                 } catch (Exception $e) {
295                     $this->rollBack();
296                     throw new CoreException($e->getMessage());
297                 }
298                 $this->commit();
299             } else {
300                 if (!empty($this->params)) {
301                     $stmt = $this->prepare($this->sql);
302                     foreach ($this->params as $p) {
303                         $del_count += $stmt->exec($p, true);
304                     }
305                 }
306             }
307         } else {
308             $del_count = $this->prepare($this->sql)->exec($this->params, true);
309         }
310 
311         return $del_count;
312     }
313 
314     /**
315      * 执行一条SQL 语句 并返回结果
316      *
317      * @param string $sql
318      * @param int $fetch_style
319      * @param int $cursor_orientation
320      * @param int $cursor_offset
321      * @return mixed
322      * @throws CoreException
323      */
324     public function fetchOne(
325         $sql,
326         $fetch_style = PDO::FETCH_ASSOC,
327         $cursor_orientation = PDO::FETCH_ORI_NEXT,
328         $cursor_offset = 0
329     )
330     {
331         try {
332             return $this->pdo->query($sql)->fetch($fetch_style, $cursor_orientation, $cursor_offset);
333         } catch (Exception $e) {
334             throw new CoreException($e->getMessage());
335         }
336     }
337 
338     /**
339      * 执行sql 并返回所有结果
340      *
341      * @param string $sql
342      * @param int $fetch_style
343      * @param null $fetch_argument
344      * @param array $ctor_args
345      * <pre>
346      * 当fetch_style为PDO::FETCH_CLASS时, 自定义类的构造函数的参数。
347      * </pre>
348      * @return array
349      * @throws CoreException
350      */
351     public function fetchAll($sql, $fetch_style = PDO::FETCH_ASSOC, $fetch_argument = null, $ctor_args = array())
352     {
353         try {
354             $data = $this->pdo->query($sql);
355             if (null !== $fetch_argument) {
356                 switch ($fetch_style) {
357                     case PDO::FETCH_CLASS:
358                         return $data->fetchAll($fetch_style, $fetch_argument, $ctor_args);
359 
360                     default:
361                         return $data->fetchAll($fetch_style, $fetch_argument);
362                 }
363 
364             } else {
365                 return $data->fetchAll($fetch_style);
366             }
367         } catch (Exception $e) {
368             throw new CoreException($e->getMessage());
369         }
370     }
371 
372     /**
373      * 执行sql 用于无返回值的操作
374      *
375      * @param string $sql
376      * @return int
377      * @throws CoreException
378      */
379     public function execute($sql)
380     {
381         try {
382             return $this->pdo->exec($sql);
383         } catch (Exception $e) {
384             throw new CoreException($e->getMessage());
385         }
386     }
387 
388     /**
389      * 链式查询以select开始,跟原生的sql语句保持一致
390      *
391      * @param string $fields
392      * @param string $modifier
393      * @return PDOSqlDriver
394      */
395     function select($fields = '*', $modifier = '')
396     {
397         $this->generateQueryID();
398         $this->querySQL[$this->qid] = $this->SQLAssembler->select($fields, $modifier);
399         $this->queryParams[$this->qid] = array();
400         return $this;
401     }
402 
403     /**
404      * insert
405      *
406      * @param string $table
407      * @param array $data
408      * @param string $modifier
409      * @return PDOSqlDriver
410      */
411     function insert($table, array $data = array(), $modifier = '')
412     {
413         $params = array();
414         $this->generateQueryID();
415         $this->querySQL[$this->qid] = $this->SQLAssembler->insert($table, $modifier, $data, $params);
416         $this->queryParams[$this->qid] = $params;
417         return $this;
418     }
419 
420     /**
421      * replace
422      *
423      * @param string $table
424      * @param string $modifier
425      * @return PDOSqlDriver
426      */
427     function replace($table, $modifier = '')
428     {
429         $this->generateQueryID();
430         $this->querySQL[$this->qid] = $this->SQLAssembler->replace($table, $modifier);
431         $this->queryParams[$this->qid] = array();
432         return $this;
433     }
434 
435     /**
436      * @param $table
437      * @return PDOSqlDriver
438      */
439     function from($table)
440     {
441         $this->querySQL[$this->qid] .= $this->SQLAssembler->from($table);
442         return $this;
443     }
444 
445     /**
446      * @param $where
447      * @return PDOSqlDriver
448      * @throws CoreException
449      */
450     function where($where)
451     {
452         $params = &$this->queryParams[$this->qid];
453         $this->querySQL[$this->qid] .= $this->SQLAssembler->where($where, $params);
454 
455         return $this;
456     }
457 
458     /**
459      * @param $start
460      * @param bool $end
461      * @return PDOSqlDriver
462      */
463     function limit($start, $end = false)
464     {
465         $this->querySQL[$this->qid] .= $this->SQLAssembler->limit($start, $end);
466         return $this;
467     }
468 
469     /**
470      * @param $offset
471      * @return PDOSqlDriver
472      */
473     function offset($offset)
474     {
475         $this->querySQL[$this->qid] .= $this->SQLAssembler->offset($offset);
476         return $this;
477     }
478 
479     /**
480      * @param $order
481      * @return PDOSqlDriver
482      */
483     function orderBy($order)
484     {
485         $this->querySQL[$this->qid] .= $this->SQLAssembler->orderBy($order);
486         return $this;
487     }
488 
489     /**
490      * @param $group
491      * @return PDOSqlDriver
492      */
493     function groupBy($group)
494     {
495         $this->querySQL[$this->qid] .= $this->SQLAssembler->groupBy($group);
496         return $this;
497     }
498 
499     /**
500      * @param $having
501      * @return PDOSqlDriver
502      */
503     function having($having)
504     {
505         $this->querySQL[$this->qid] .= $this->SQLAssembler->having($having);
506         return $this;
507     }
508 
509     /**
510      * @param $procedure
511      * @return PDOSqlDriver
512      */
513     function procedure($procedure)
514     {
515         $this->querySQL[$this->qid] .= $this->SQLAssembler->procedure($procedure);
516         return $this;
517     }
518 
519     /**
520      * @param $var_name
521      * @return PDOSqlDriver
522      */
523     public function into($var_name)
524     {
525         $this->querySQL[$this->qid] .= $this->SQLAssembler->into($var_name);
526         return $this;
527     }
528 
529     /**
530      * @param $set
531      * @return PDOSqlDriver
532      */
533     public function set($set)
534     {
535         $params = &$this->queryParams[$this->qid];
536         $this->querySQL[$this->qid] .= $this->SQLAssembler->set($set, $params);
537 
538         return $this;
539     }
540 
541     /**
542      * @param string $on
543      * @return PDOSqlDriver
544      */
545     function on($on)
546     {
547         $this->querySQL[$this->qid] .= $this->SQLAssembler->on($on);
548         return $this;
549     }
550 
551     /**
552      * 返回链式查询当前生成的prepare语句
553      *
554      * @param bool $only_sql
555      * @return mixed
556      */
557     function getSQL($only_sql = false)
558     {
559         $this->sql = &$this->querySQL[$this->qid];
560         if ($only_sql) {
561             return $this->sql;
562         }
563 
564         $params = $this->queryParams[$this->qid];
565         return array('sql' => $this->sql, 'params' => $params);
566     }
567 
568     /**
569      * 获取表前缀
570      *
571      * @return string
572      */
573     function getPrefix()
574     {
575         return $this->SQLAssembler->getPrefix();
576     }
577 
578     /**
579      * 绑定链式查询生成的sql语句并返回stmt对象
580      *
581      * @param bool $execute 是否调用stmt的execute
582      * @param array $prepare_params prepare时的参数
583      * @return PDOStatement
584      * @throws CoreException
585      */
586     public function stmt($execute = true, $prepare_params = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY))
587     {
588         if ($this->qid == 0) {
589             throw new CoreException("链式风格的查询必须以->select()开始");
590         }
591 
592         $this->sql = &$this->querySQL[$this->qid];
593         try {
594             $stmt = $this->pdo->prepare($this->sql, $prepare_params);
595             if ($execute) {
596                 $execute_params = $this->queryParams[$this->qid];
597                 $stmt->execute($execute_params);
598             }
599 
600             unset($this->querySQL[$this->qid], $this->queryParams[$this->qid]);
601             return $stmt;
602         } catch (Exception $e) {
603             throw new CoreException($e->getMessage());
604         }
605     }
606 
607     /**
608      * 链式执行操作
609      *
610      * @param array $prepare_params
611      * @return bool
612      * @throws CoreException
613      */
614     public function stmtExecute($prepare_params = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY))
615     {
616         if ($this->qid == 0) {
617             throw new CoreException("无效的执行语句");
618         }
619 
620         $this->sql = &$this->querySQL[$this->qid];
621         try {
622             $stmt = $this->pdo->prepare($this->sql, $prepare_params);
623             $execute_params = $this->queryParams[$this->qid];
624 
625             unset($this->querySQL[$this->qid], $this->queryParams[$this->qid]);
626             return $stmt->execute($execute_params);
627         } catch (Exception $e) {
628             throw new CoreException($e->getMessage());
629         }
630     }
631 
632     /**
633      * 参数绑定
634      *
635      * @param string $statement
636      * @param array $params
637      * @return $this
638      * @throws CoreException
639      */
640     public function prepare($statement, $params = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY))
641     {
642         try {
643             $this->stmt = $this->pdo->prepare($statement, $params);
644             if (!$this->stmt) {
645                 throw new CoreException("PDO prepare failed!");
646             }
647 
648             return $this;
649         } catch (PDOException $e) {
650             throw new CoreException($e->getMessage());
651         }
652     }
653 
654     /**
655      * 执行参数绑定
656      *
657      * @param array $args
658      * @param bool $row_count
659      * @return int|$this
660      * @throws CoreException
661      */
662     public function exec($args = array(), $row_count = false)
663     {
664         try {
665             $this->stmt->execute($args);
666             if ($row_count) {
667                 return $this->stmt->rowCount();
668             }
669 
670             return $this;
671         } catch (PDOException $e) {
672             throw new CoreException($e->getMessage());
673         }
674     }
675 
676     /**
677      * 返回参数绑定结果
678      *
679      * @param bool $_fetchAll
680      * @param int $fetch_style
681      * @return mixed
682      * @throws CoreException
683      */
684     public function stmtFetch($_fetchAll = false, $fetch_style = PDO::FETCH_ASSOC)
685     {
686         if (!$this->stmt) {
687             throw new CoreException('stmt init failed!');
688         }
689 
690         if (true === $_fetchAll) {
691             return $this->stmt->fetchAll($fetch_style);
692         }
693 
694         return $this->stmt->fetch($fetch_style);
695     }
696 
697     /**
698      * 获取表的字段信息
699      *
700      * @param string $table
701      * @param bool $fields_map
702      * @return mixed
703      */
704     public function getMetaData($table, $fields_map = true)
705     {
706         return $this->connecter->getMetaData($table, $fields_map);
707     }
708 
709     /**
710      * 获取自增字段名
711      *
712      * @param string $table_name
713      * @return bool
714      */
715     public function getAutoIncrementName($table_name)
716     {
717         return $this->connecter->getPK($table_name);
718     }
719 
720     /**
721      * @return PDOConnecter
722      */
723     public function getConnecter()
724     {
725         return $this->connecter;
726     }
727 
728     /**
729      * 设置PDOConnecter对象
730      *
731      * @param PDOConnecter $connecter
732      */
733     public function setConnecter(PDOConnecter $connecter)
734     {
735         $this->connecter = $connecter;
736     }
737 
738     /**
739      * @return SQLAssembler
740      */
741     public function getSQLAssembler()
742     {
743         return $this->SQLAssembler;
744     }
745 
746     /**
747      * 设置SQLAssembler对象
748      *
749      * @param SQLAssembler $SQLAssembler
750      */
751     public function setSQLAssembler(SQLAssembler $SQLAssembler)
752     {
753         $this->SQLAssembler = $SQLAssembler;
754     }
755 
756     /**
757      * 解析fields
758      *
759      * @param string|array $fields
760      * @return string
761      */
762     public function parseFields($fields)
763     {
764         return $this->SQLAssembler->parseFields($fields);
765     }
766 
767     /**
768      * 解析where
769      *
770      * @param string|array $where
771      * @param string|array $params
772      * @return string
773      * @throws CoreException
774      */
775     public function parseWhere($where, & $params)
776     {
777         return $this->SQLAssembler->parseWhere($where, $params);
778     }
779 
780     /**
781      * 解析order
782      *
783      * @param string|array $order
784      * @return int|string
785      */
786     public function parseOrder($order)
787     {
788         return $this->SQLAssembler->parseOrder($order);
789     }
790 
791     /**
792      * 解析group
793      *
794      * @param string $group_by
795      * @return int
796      */
797     public function parseGroup($group_by)
798     {
799         return $this->SQLAssembler->parseGroup($group_by);
800     }
801 
802     /**
803      * 开启事务
804      *
805      * @return bool
806      */
807     public function commit()
808     {
809         return $this->pdo->commit();
810     }
811 
812     /**
813      * @return bool
814      */
815     public function beginTA()
816     {
817         return $this->pdo->beginTransaction();
818     }
819 
820     /**
821      * 回滚
822      *
823      * @return bool
824      */
825     public function rollBack()
826     {
827         return $this->pdo->rollBack();
828     }
829 
830     /**
831      * 返回最后操作的id
832      *
833      * @return string
834      */
835     public function insertId()
836     {
837         return $this->connecter->lastInsertID();
838     }
839 
840     /**
841      * 绑定sql语句,执行后给出返回结果
842      *
843      * @param bool $_fetchAll
844      * @param int $fetch_style
845      * @return mixed
846      * @throws CoreException
847      */
848     protected function getPrepareResult($_fetchAll = false, $fetch_style = PDO::FETCH_ASSOC)
849     {
850         $this->sql = $this->SQLAssembler->getSQL();
851         $this->params = $this->SQLAssembler->getParams();
852 
853         return $this->prepare($this->sql)->exec($this->params)->stmtFetch($_fetchAll, $fetch_style);
854     }
855 
856     /**
857      * 生成qid
858      */
859     private function generateQueryID()
860     {
861         do {
862             $qid = mt_rand(1, 99999);
863             if (!isset($this->querySQL[$qid])) {
864                 $this->qid = $qid;
865                 break;
866             }
867         } while (true);
868     }
869 }
870 

CrossPHP API documentation generated by ApiGen