1<?php
2/**
3 *
4 * This file is part of Aura for PHP.
5 *
6 * @license https://opensource.org/licenses/MIT MIT
7 *
8 */
9namespace Aura\Sql;
10
11use Aura\Sql\Exception;
12use Aura\Sql\Parser\ParserInterface;
13use Aura\Sql\Profiler\ProfilerInterface;
14use BadMethodCallException;
15use PDO;
16use PDOStatement;
17
18/**
19 *
20 * Provides array quoting, profiling, a new `perform()` method, new `fetch*()`
21 * methods, and new `yield*()` methods.
22 *
23 * @package Aura.Sql
24 *
25 */
26abstract class AbstractExtendedPdo extends PDO implements ExtendedPdoInterface
27{
28    /**
29     *
30     * The internal PDO connection.
31     *
32     * @var PDO
33     *
34     */
35    protected $pdo;
36
37    /**
38     *
39     * Tracks and logs query profiles.
40     *
41     * @var ProfilerInterface
42     *
43     */
44    protected $profiler;
45
46    /**
47     *
48     * Parses queries to rebuild them for easier parameter binding.
49     *
50     * @var ParserInterface
51     *
52     */
53    protected $parser;
54
55    /**
56     *
57     * Prefix to use when quoting identifier names.
58     *
59     * @var string
60     *
61     */
62    protected $quoteNamePrefix = '"';
63
64    /**
65     *
66     * Suffix to use when quoting identifier names.
67     *
68     * @var string
69     *
70     */
71    protected $quoteNameSuffix = '"';
72
73    /**
74     *
75     * Find this string when escaping identifier names.
76     *
77     * @var string
78     *
79     */
80    protected $quoteNameEscapeFind = '"';
81
82    /**
83     *
84     * Use this as the replacement when escaping identifier names.
85     *
86     * @var string
87     *
88     */
89    protected $quoteNameEscapeRepl = '""';
90
91    /**
92     *
93     * Proxies to PDO methods created for specific drivers; in particular,
94     * `sqlite` and `pgsql`.
95     *
96     * @param string $name The PDO method to call; e.g. `sqliteCreateFunction`
97     * or `pgsqlGetPid`.
98     *
99     * @param array $arguments Arguments to pass to the called method.
100     *
101     * @return mixed
102     *
103     * @throws BadMethodCallException when the method does not exist.
104     *
105     */
106    public function __call($name, array $arguments)
107    {
108        $this->connect();
109
110        if (! method_exists($this->pdo, $name)) {
111            $class = get_class($this);
112            $message = "Class '{$class}' does not have a method '{$name}'";
113            throw new BadMethodCallException($message);
114        }
115
116        return call_user_func_array([$this->pdo, $name], $arguments);
117    }
118
119    /**
120     *
121     * Begins a transaction and turns off autocommit mode.
122     *
123     * @return bool True on success, false on failure.
124     *
125     * @see http://php.net/manual/en/pdo.begintransaction.php
126     *
127     */
128    public function beginTransaction()
129    {
130        $this->connect();
131        $this->profiler->start(__FUNCTION__);
132        $result = $this->pdo->beginTransaction();
133        $this->profiler->finish();
134        return $result;
135    }
136
137    /**
138     *
139     * Commits the existing transaction and restores autocommit mode.
140     *
141     * @return bool True on success, false on failure.
142     *
143     * @see http://php.net/manual/en/pdo.commit.php
144     *
145     */
146    public function commit()
147    {
148        $this->connect();
149        $this->profiler->start(__FUNCTION__);
150        $result = $this->pdo->commit();
151        $this->profiler->finish();
152        return $result;
153    }
154
155    /**
156     *
157     * Connects to the database.
158     *
159     * @return null
160     *
161     */
162    abstract public function connect();
163
164    /**
165     *
166     * Disconnects from the database.
167     *
168     * @return null
169     *
170     */
171    abstract public function disconnect();
172
173    /**
174     *
175     * Gets the most recent error code.
176     *
177     * @return mixed
178     *
179     */
180    public function errorCode()
181    {
182        $this->connect();
183        return $this->pdo->errorCode();
184    }
185
186    /**
187     *
188     * Gets the most recent error info.
189     *
190     * @return array
191     *
192     */
193    public function errorInfo()
194    {
195        $this->connect();
196        return $this->pdo->errorInfo();
197    }
198
199    /**
200     *
201     * Executes an SQL statement and returns the number of affected rows.
202     *
203     * @param string $statement The SQL statement to prepare and execute.
204     *
205     * @return int The number of affected rows.
206     *
207     * @see http://php.net/manual/en/pdo.exec.php
208     *
209     */
210    public function exec($statement)
211    {
212        $this->connect();
213        $this->profiler->start(__FUNCTION__);
214        $affectedRows = $this->pdo->exec($statement);
215        $this->profiler->finish($statement);
216        return $affectedRows;
217    }
218
219    /**
220     *
221     * Performs a statement and returns the number of affected rows.
222     *
223     * @param string $statement The SQL statement to prepare and execute.
224     *
225     * @param array $values Values to bind to the query.
226     *
227     * @return int
228     *
229     */
230    public function fetchAffected($statement, array $values = [])
231    {
232        $sth = $this->perform($statement, $values);
233        return $sth->rowCount();
234    }
235
236    /**
237     *
238     * Fetches a sequential array of rows from the database; the rows
239     * are returned as associative arrays.
240     *
241     * @param string $statement The SQL statement to prepare and execute.
242     *
243     * @param array $values Values to bind to the query.
244     *
245     * @return array
246     *
247     */
248    public function fetchAll($statement, array $values = [])
249    {
250        $sth = $this->perform($statement, $values);
251        return $sth->fetchAll(self::FETCH_ASSOC);
252    }
253
254    /**
255     *
256     * Fetches an associative array of rows from the database; the rows
257     * are returned as associative arrays, and the array of rows is keyed
258     * on the first column of each row.
259     *
260     * N.b.: If multiple rows have the same first column value, the last
261     * row with that value will override earlier rows.
262     *
263     * @param string $statement The SQL statement to prepare and execute.
264     *
265     * @param array $values Values to bind to the query.
266     *
267     * @return array
268     *
269     */
270    public function fetchAssoc($statement, array $values = [])
271    {
272        $sth  = $this->perform($statement, $values);
273        $data = [];
274        while ($row = $sth->fetch(self::FETCH_ASSOC)) {
275            $data[current($row)] = $row;
276        }
277        return $data;
278    }
279
280    /**
281     *
282     * Fetches the first column of rows as a sequential array.
283     *
284     * @param string $statement The SQL statement to prepare and execute.
285     *
286     * @param array $values Values to bind to the query.
287     *
288     * @return array
289     *
290     */
291    public function fetchCol($statement, array $values = [])
292    {
293        $sth = $this->perform($statement, $values);
294        return $sth->fetchAll(self::FETCH_COLUMN, 0);
295    }
296
297    /**
298     *
299     * Fetches multiple from the database as an associative array. The first
300     * column will be the index key.
301     *
302     * @param string $statement The SQL statement to prepare and execute.
303     *
304     * @param array $values Values to bind to the query.
305     *
306     * @param int $style a fetch style defaults to PDO::FETCH_COLUMN for single
307     * values, use PDO::FETCH_NAMED when fetching a multiple columns
308     *
309     * @return array
310     *
311     */
312    public function fetchGroup(
313        $statement,
314        array $values = [],
315        $style = PDO::FETCH_COLUMN
316    ) {
317        $sth = $this->perform($statement, $values);
318        return $sth->fetchAll(self::FETCH_GROUP | $style);
319    }
320
321    /**
322     *
323     * Fetches one row from the database as an object where the column values
324     * are mapped to object properties.
325     *
326     * Warning: PDO "injects property-values BEFORE invoking the constructor -
327     * in other words, if your class initializes property-values to defaults
328     * in the constructor, you will be overwriting the values injected by
329     * fetchObject() !"
330     *
331     * <http://www.php.net/manual/en/pdostatement.fetchobject.php#111744>
332     *
333     * @param string $statement The SQL statement to prepare and execute.
334     *
335     * @param array $values Values to bind to the query.
336     *
337     * @param string $class The name of the class to create.
338     *
339     * @param array $args Arguments to pass to the object constructor.
340     *
341     * @return object
342     *
343     */
344    public function fetchObject(
345        $statement,
346        array $values = [],
347        $class = 'stdClass',
348        array $args = []
349    ) {
350        $sth = $this->perform($statement, $values);
351
352        if (! empty($args)) {
353            return $sth->fetchObject($class, $args);
354        }
355
356        return $sth->fetchObject($class);
357    }
358
359    /**
360     *
361     * Fetches a sequential array of rows from the database; the rows
362     * are returned as objects where the column values are mapped to
363     * object properties.
364     *
365     * Warning: PDO "injects property-values BEFORE invoking the constructor -
366     * in other words, if your class initializes property-values to defaults
367     * in the constructor, you will be overwriting the values injected by
368     * fetchObject() !"
369     *
370     * <http://www.php.net/manual/en/pdostatement.fetchobject.php#111744>
371     *
372     * @param string $statement The SQL statement to prepare and execute.
373     *
374     * @param array $values Values to bind to the query.
375     *
376     * @param string $class The name of the class to create from each
377     * row.
378     *
379     * @param array $args Arguments to pass to each object constructor.
380     *
381     * @return array
382     *
383     */
384    public function fetchObjects(
385        $statement,
386        array $values = [],
387        $class = 'stdClass',
388        array $args = []
389    ) {
390        $sth = $this->perform($statement, $values);
391
392        if (! empty($args)) {
393            return $sth->fetchAll(self::FETCH_CLASS, $class, $args);
394        }
395
396        return $sth->fetchAll(self::FETCH_CLASS, $class);
397    }
398
399    /**
400     *
401     * Fetches one row from the database as an associative array.
402     *
403     * @param string $statement The SQL statement to prepare and execute.
404     *
405     * @param array $values Values to bind to the query.
406     *
407     * @return array
408     *
409     */
410    public function fetchOne($statement, array $values = [])
411    {
412        $sth = $this->perform($statement, $values);
413        return $sth->fetch(self::FETCH_ASSOC);
414    }
415
416    /**
417     *
418     * Fetches an associative array of rows as key-value pairs (first
419     * column is the key, second column is the value).
420     *
421     * @param string $statement The SQL statement to prepare and execute.
422     *
423     * @param array $values Values to bind to the query.
424     *
425     * @return array
426     *
427     */
428    public function fetchPairs($statement, array $values = [])
429    {
430        $sth = $this->perform($statement, $values);
431        return $sth->fetchAll(self::FETCH_KEY_PAIR);
432    }
433
434    /**
435     *
436     * Fetches the very first value (i.e., first column of the first row).
437     *
438     * @param string $statement The SQL statement to prepare and execute.
439     *
440     * @param array $values Values to bind to the query.
441     *
442     * @return mixed
443     *
444     */
445    public function fetchValue($statement, array $values = [])
446    {
447        $sth = $this->perform($statement, $values);
448        return $sth->fetchColumn(0);
449    }
450
451    /**
452     *
453     * Returns the Parser instance.
454     *
455     * @return ParserInterface
456     *
457     */
458    public function getParser()
459    {
460        return $this->parser;
461    }
462
463    /**
464     *
465     * Return the inner PDO (if any)
466     *
467     * @return \PDO
468     *
469     */
470    public function getPdo()
471    {
472        return $this->pdo;
473    }
474
475    /**
476     *
477     * Returns the Profiler instance.
478     *
479     * @return ProfilerInterface
480     *
481     */
482    public function getProfiler()
483    {
484        return $this->profiler;
485    }
486
487    /**
488     *
489     * Is a transaction currently active?
490     *
491     * @return bool
492     *
493     * @see http://php.net/manual/en/pdo.intransaction.php
494     *
495     */
496    public function inTransaction()
497    {
498        $this->connect();
499        $this->profiler->start(__FUNCTION__);
500        $result = $this->pdo->inTransaction();
501        $this->profiler->finish();
502        return $result;
503    }
504
505    /**
506     *
507     * Is the PDO connection active?
508     *
509     * @return bool
510     *
511     */
512    public function isConnected()
513    {
514        return (bool) $this->pdo;
515    }
516
517    /**
518     *
519     * Returns the last inserted autoincrement sequence value.
520     *
521     * @param string $name The name of the sequence to check; typically needed
522     * only for PostgreSQL, where it takes the form of `<table>_<column>_seq`.
523     *
524     * @return string
525     *
526     * @see http://php.net/manual/en/pdo.lastinsertid.php
527     *
528     */
529    public function lastInsertId($name = null)
530    {
531        $this->connect();
532        $this->profiler->start(__FUNCTION__);
533        $result = $this->pdo->lastInsertId($name);
534        $this->profiler->finish();
535        return $result;
536    }
537
538    /**
539     *
540     * Performs a query with bound values and returns the resulting
541     * PDOStatement; array values will be passed through `quote()` and their
542     * respective placeholders will be replaced in the query string.
543     *
544     * @param string $statement The SQL statement to perform.
545     *
546     * @param array $values Values to bind to the query
547     *
548     * @return PDOStatement
549     *
550     * @see quote()
551     *
552     */
553    public function perform($statement, array $values = [])
554    {
555        $this->connect();
556        $sth = $this->prepareWithValues($statement, $values);
557        $this->profiler->start(__FUNCTION__);
558        $sth->execute();
559        $this->profiler->finish($statement, $values);
560        return $sth;
561    }
562
563    /**
564     *
565     * Prepares an SQL statement for execution.
566     *
567     * @param string $statement The SQL statement to prepare for execution.
568     *
569     * @param array $options Set these attributes on the returned
570     * PDOStatement.
571     *
572     * @return PDOStatement
573     *
574     * @see http://php.net/manual/en/pdo.prepare.php
575     *
576     */
577    public function prepare($statement, $options = [])
578    {
579        $this->connect();
580        $sth = $this->pdo->prepare($statement, $options);
581        return $sth;
582    }
583
584    /**
585     *
586     * Prepares an SQL statement with bound values.
587     *
588     * This method only binds values that have placeholders in the
589     * statement, thereby avoiding errors from PDO regarding too many bound
590     * values. It also binds all sequential (question-mark) placeholders.
591     *
592     * If a placeholder value is an array, the array is converted to a string
593     * of comma-separated quoted values; e.g., for an `IN (...)` condition.
594     * The quoted string is replaced directly into the statement instead of
595     * using `PDOStatement::bindValue()` proper.
596     *
597     * @param string $statement The SQL statement to prepare for execution.
598     *
599     * @param array $values The values to bind to the statement, if any.
600     *
601     * @return PDOStatement
602     *
603     * @see http://php.net/manual/en/pdo.prepare.php
604     *
605     */
606    public function prepareWithValues($statement, array $values = [])
607    {
608        // if there are no values to bind ...
609        if (empty($values)) {
610            // ... use the normal preparation
611            return $this->prepare($statement);
612        }
613
614        $this->connect();
615
616        // rebuild the statement and values
617        $parser = clone $this->parser;
618        list ($statement, $values) = $parser->rebuild($statement, $values);
619
620        // prepare the statement
621        $sth = $this->pdo->prepare($statement);
622
623        // for the placeholders we found, bind the corresponding data values
624        foreach ($values as $key => $val) {
625            $this->bindValue($sth, $key, $val);
626        }
627
628        // done
629        return $sth;
630    }
631
632    /**
633     *
634     * Queries the database and returns a PDOStatement.
635     *
636     * @param string $statement The SQL statement to prepare and execute.
637     *
638     * @param mixed ...$fetch Optional fetch-related parameters.
639     *
640     * @return PDOStatement
641     *
642     * @see http://php.net/manual/en/pdo.query.php
643     *
644     */
645    public function query($statement, ...$fetch)
646    {
647        $this->connect();
648        $this->profiler->start(__FUNCTION__);
649        $sth = $this->pdo->query($statement, ...$fetch);
650        $this->profiler->finish($sth->queryString);
651        return $sth;
652    }
653
654    /**
655     *
656     * Quotes a value for use in an SQL statement.
657     *
658     * This differs from `PDO::quote()` in that it will convert an array into
659     * a string of comma-separated quoted values.
660     *
661     * @param mixed $value The value to quote.
662     *
663     * @param int $type A data type hint for the database driver.
664     *
665     * @return string The quoted value.
666     *
667     * @see http://php.net/manual/en/pdo.quote.php
668     *
669     */
670    public function quote($value, $type = self::PARAM_STR)
671    {
672        $this->connect();
673
674        // non-array quoting
675        if (! is_array($value)) {
676            return $this->pdo->quote($value, $type);
677        }
678
679        // quote array values, not keys, then combine with commas
680        foreach ($value as $k => $v) {
681            $value[$k] = $this->pdo->quote($v, $type);
682        }
683        return implode(', ', $value);
684    }
685
686    /**
687     *
688     * Quotes a multi-part (dotted) identifier name.
689     *
690     * @param string $name The multi-part identifier name.
691     *
692     * @return string The multi-part identifier name, quoted.
693     *
694     */
695    public function quoteName($name)
696    {
697        if (strpos($name, '.') === false) {
698            return $this->quoteSingleName($name);
699        }
700
701        return implode(
702            '.',
703            array_map(
704                [$this, 'quoteSingleName'],
705                explode('.', $name)
706            )
707        );
708    }
709
710    /**
711     *
712     * Quotes a single identifier name.
713     *
714     * @param string $name The identifier name.
715     *
716     * @return string The quoted identifier name.
717     *
718     */
719    public function quoteSingleName($name)
720    {
721        $name = str_replace(
722            $this->quoteNameEscapeFind,
723            $this->quoteNameEscapeRepl,
724            $name
725        );
726        return $this->quoteNamePrefix
727            . $name
728            . $this->quoteNameSuffix;
729    }
730
731    /**
732     *
733     * Rolls back the current transaction, and restores autocommit mode.
734     *
735     * @return bool True on success, false on failure.
736     *
737     * @see http://php.net/manual/en/pdo.rollback.php
738     *
739     */
740    public function rollBack()
741    {
742        $this->connect();
743        $this->profiler->start(__FUNCTION__);
744        $result = $this->pdo->rollBack();
745        $this->profiler->finish();
746        return $result;
747    }
748
749    /**
750     *
751     * Sets the Parser instance.
752     *
753     * @param ParserInterface $parser The Parser instance.
754     *
755     */
756    public function setParser(ParserInterface $parser)
757    {
758        $this->parser = $parser;
759    }
760
761    /**
762     *
763     * Sets the Profiler instance.
764     *
765     * @param ProfilerInterface $profiler The Profiler instance.
766     *
767     */
768    public function setProfiler(ProfilerInterface $profiler)
769    {
770        $this->profiler = $profiler;
771    }
772
773    /**
774     *
775     * Yields rows from the database.
776     *
777     * @param string $statement The SQL statement to prepare and execute.
778     *
779     * @param array $values Values to bind to the query.
780     *
781     * @return \Generator
782     *
783     */
784    public function yieldAll($statement, array $values = [])
785    {
786        $sth = $this->perform($statement, $values);
787        while ($row = $sth->fetch(self::FETCH_ASSOC)) {
788            yield $row;
789        }
790    }
791
792    /**
793     *
794     * Yields rows from the database keyed on the first column of each row.
795     *
796     * @param string $statement The SQL statement to prepare and execute.
797     *
798     * @param array $values Values to bind to the query.
799     *
800     * @return \Generator
801     *
802     */
803    public function yieldAssoc($statement, array $values = [])
804    {
805        $sth = $this->perform($statement, $values);
806        while ($row = $sth->fetch(self::FETCH_ASSOC)) {
807            $key = current($row);
808            yield $key => $row;
809        }
810    }
811
812    /**
813     *
814     * Yields the first column of each row.
815     *
816     * @param string $statement The SQL statement to prepare and execute.
817     *
818     * @param array $values Values to bind to the query.
819     *
820     * @return \Generator
821     *
822     */
823    public function yieldCol($statement, array $values = [])
824    {
825        $sth = $this->perform($statement, $values);
826        while ($row = $sth->fetch(self::FETCH_NUM)) {
827            yield $row[0];
828        }
829    }
830
831    /**
832     *
833     * Yields objects where the column values are mapped to object properties.
834     *
835     * Warning: PDO "injects property-values BEFORE invoking the constructor -
836     * in other words, if your class initializes property-values to defaults
837     * in the constructor, you will be overwriting the values injected by
838     * fetchObject() !"
839     * <http://www.php.net/manual/en/pdostatement.fetchobject.php#111744>
840     *
841     * @param string $statement The SQL statement to prepare and execute.
842     *
843     * @param array $values Values to bind to the query.
844     *
845     * @param string $class The name of the class to create from each
846     * row.
847     *
848     * @param array $args Arguments to pass to each object constructor.
849     *
850     * @return \Generator
851     *
852     */
853    public function yieldObjects(
854        $statement,
855        array $values = [],
856        $class = 'stdClass',
857        array $args = []
858    ) {
859        $sth = $this->perform($statement, $values);
860
861        if (empty($args)) {
862            while ($instance = $sth->fetchObject($class)) {
863                yield $instance;
864            }
865        } else {
866            while ($instance = $sth->fetchObject($class, $args)) {
867                yield $instance;
868            }
869        }
870    }
871
872    /**
873     *
874     * Yields key-value pairs (first column is the key, second column is the
875     * value).
876     *
877     * @param string $statement The SQL statement to prepare and execute.
878     *
879     * @param array $values Values to bind to the query.
880     *
881     * @return \Generator
882     *
883     */
884    public function yieldPairs($statement, array $values = [])
885    {
886        $sth = $this->perform($statement, $values);
887        while ($row = $sth->fetch(self::FETCH_NUM)) {
888            yield $row[0] => $row[1];
889        }
890    }
891
892    /**
893     *
894     * Bind a value using the proper PDO::PARAM_* type.
895     *
896     * @param PDOStatement $sth The statement to bind to.
897     *
898     * @param mixed $key The placeholder key.
899     *
900     * @param mixed $val The value to bind to the statement.
901     *
902     * @return boolean
903     *
904     * @throws Exception\CannotBindValue when the value to be bound is not
905     * bindable (e.g., array, object, or resource).
906     *
907     */
908    protected function bindValue(PDOStatement $sth, $key, $val)
909    {
910        if (is_int($val)) {
911            return $sth->bindValue($key, $val, self::PARAM_INT);
912        }
913
914        if (is_bool($val)) {
915            return $sth->bindValue($key, $val, self::PARAM_BOOL);
916        }
917
918        if (is_null($val)) {
919            return $sth->bindValue($key, $val, self::PARAM_NULL);
920        }
921
922        if (! is_scalar($val)) {
923            $type = gettype($val);
924            throw new Exception\CannotBindValue(
925                "Cannot bind value of type '{$type}' to placeholder '{$key}'"
926            );
927        }
928
929        return $sth->bindValue($key, $val);
930    }
931
932    /**
933     *
934     * Returns a new Parser instance.
935     *
936     * @param string $driver Return a parser for this driver.
937     *
938     * @return ParserInterface
939     *
940     */
941    protected function newParser($driver)
942    {
943        $class = 'Aura\Sql\Parser\\' . ucfirst($driver) . 'Parser';
944        if (! class_exists($class)) {
945            $class = 'Aura\Sql\Parser\SqliteParser';
946        }
947        return new $class();
948    }
949
950    /**
951     *
952     * Sets quoting properties based on the PDO driver.
953     *
954     * @param string $driver The PDO driver name.
955     *
956     * @return null
957     *
958     */
959    protected function setQuoteName($driver)
960    {
961        switch ($driver) {
962            case 'mysql':
963                $this->quoteNamePrefix = '`';
964                $this->quoteNameSuffix = '`';
965                $this->quoteNameEscapeFind = '`';
966                $this->quoteNameEscapeRepl = '``';
967                return;
968            case 'sqlsrv':
969                $this->quoteNamePrefix = '[';
970                $this->quoteNameSuffix = ']';
971                $this->quoteNameEscapeFind = ']';
972                $this->quoteNameEscapeRepl = '][';
973                return;
974            default:
975                $this->quoteNamePrefix = '"';
976                $this->quoteNameSuffix = '"';
977                $this->quoteNameEscapeFind = '"';
978                $this->quoteNameEscapeRepl = '""';
979                return;
980        }
981    }
982
983    /**
984     *
985     * Retrieve a database connection attribute
986     *
987     * @param int $attribute
988     * @return mixed
989     */
990    public function getAttribute($attribute)
991    {
992        $this->connect();
993        return $this->pdo->getAttribute($attribute);
994    }
995
996    /**
997     *
998     * Set a database connection attribute
999     *
1000     * @param int $attribute
1001     * @param mixed $value
1002     * @return bool
1003     */
1004    public function setAttribute($attribute, $value)
1005    {
1006        $this->connect();
1007        return $this->pdo->setAttribute($attribute, $value);
1008    }
1009}
1010