Db::exec() - Code Metrics - Inspection of "fixes check inside DB::executeQuery method if pdo..." - starweb/starlit-db - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#12)

by Patrick

created 2019-02-20 11:52 UTC

Db::exec() A

↳ Parent: Db

Complexity

Conditions	1
Paths	1

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	3
CRAP Score	1

Importance

Changes

Metric	Value
dl	0
loc	6
ccs	3
cts	3
cp	1
rs	10
c	0
b	0
f	0
cc	1
nc	1
nop	2
crap	1

<?php
/**
 * Starlit Db.
 *
 * @copyright Copyright (c) 2016 Starweb AB
 * @license   BSD 3-Clause
 */

namespace Starlit\Db;

use Starlit\Db\Exception\ConnectionException;
use Starlit\Db\Exception\QueryException;
use \PDO;
use \PDOException;
use \PDOStatement;

/**
 * Extended PDO database wrapper.
 *
 * @author Andreas Nilsson <http://github.com/jandreasn>
 */
class Db
{
    /**
     * Database handle/connection.
     *
     * @var PDO
     */
    protected $pdo;

    /**
     * @var string
     */
    protected $dsn;

    /**
     * @var string
     */
    protected $username;

    /**
     * @var string
     */
    protected $password;

    /**
     * @var array
     */
    protected $options;

    /**
     * @var bool
     */
    protected $hasActiveTransaction = false;

    /**
     * Constructor.
     *
     * @param string|PDO  $hostDsnOrPdo A MySQL host, a dsn or an existing PDO instance.
     * @param string|null $username
     * @param string|null $password
     * @param string|null $database
     * @param array       $options
     */
    public function __construct(
        $hostDsnOrPdo,
        $username = null,
        $password = null,
        $database = null,
        array $options = []
    ) {
        if ($hostDsnOrPdo instanceof PDO) {
            $this->pdo = $hostDsnOrPdo;
        } elseif (strpos($hostDsnOrPdo, ':') !== false) {
            $this->dsn = $hostDsnOrPdo;
        } else {
            $this->dsn = "mysql:host={$hostDsnOrPdo}" . ($database ? ";dbname={$database}" : '');
        }

        $this->username = $username;
        $this->password = $password;
        $this->options = $options;
    }

    /**
     */
    public function connect()
    {
        if ($this->isConnected()) {
            return;
        }

        $retries = isset($this->options['connectRetries'])? $this->options['connectRetries'] : 0;
        do {
            try {
                $defaultPdoOptions = [
                    PDO::ATTR_TIMEOUT            => 5,
                    // We want emulation by default (faster for single queries). Disable if you want to
                    // use proper native prepared statements
                    PDO::ATTR_EMULATE_PREPARES   => true,
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                ];
                $pdoOptions = $defaultPdoOptions + (isset($this->options['pdo']) ? $this->options['pdo'] : []);

                $this->pdo = new PDO(
                    $this->dsn,
                    $this->username,
                    $this->password,
                    $pdoOptions
                );

                return;
            } catch (PDOException $e) {
                if ($this->isConnectionExceptionCausedByConnection($e) && $retries > 0) {
                    // Sleep for 100 - 500 ms until next retry
                    usleep(rand(100000, 500000));
                } else {
                    throw new ConnectionException($e);
                }
            }

        } while ($retries-- > 0);
    }

    /**
     * @param PDOException $exception
     * @return bool
     */
    private function isConnectionExceptionCausedByConnection(PDOException $exception)
    {
        return in_array($exception->getCode(), [
            2002, // Can't connect to MySQL server (Socket)
            2003, // Can't connect to MySQL server (TCP)
            2006, // MySQL server has gone away
            2013, // Lost connection to MySQL server during query
        ]);
    }

    /**
     * Close the database connection.
     */
    public function disconnect()
    {
        $this->pdo = null;
    }

    /**
     */
    public function reconnect()
    {
        $this->disconnect();
        $this->connect();
    }

    /**
     * Check if database connection is open.
     *
     * @return bool
     */
    public function isConnected()
    {
        return ($this->pdo instanceof PDO);
    }

    /**
     * Returns the PDO handle.
     *
     * Can be used to gain access to any special PDO methods.
     *
     * @return PDO
     */
    public function getPdo()
    {
        return $this->pdo;
    }

    /**
     * Creates and executes a PDO statement.
     *
     * @param string $sql
     * @param array  $parameters
     * @return PDOStatement
     */
    protected function executeQuery($sql, array $parameters = [])
    {
        $this->connect();

        $dbParameters = $this->prepareParameters($parameters);
        try {
            $pdoStatement = $this->pdo->prepare($sql);
            if(!$pdoStatement->execute($dbParameters)) {
                $errorInfo = $pdoStatement->errorInfo();

                throw new PDOException($errorInfo[2], $errorInfo[1]);
            }

            return $pdoStatement;
        } catch (PDOException $e) {
            throw new QueryException($e, $sql, $dbParameters);
        }
    }

    /**
     * Execute an SQL statement and return the number of affected rows.
     *
     * @param string $sql
     * @param array  $parameters
     * @return int The number of rows affected
     */
    public function exec($sql, array $parameters = [])
    {
        $statement = $this->executeQuery($sql, $parameters);

        return $statement->rowCount();
    }

    /**
     * Execute an SQL statement and return the first row.
     *
     * @param string $sql
     * @param array  $parameters
     * @param bool   $indexedKeys
     * @return array|false
     */
    public function fetchRow($sql, array $parameters = [], $indexedKeys = false)
    {
        $statement = $this->executeQuery($sql, $parameters);

        return $statement->fetch($indexedKeys ? PDO::FETCH_NUM : PDO::FETCH_ASSOC);
    }

    /**
     * Execute an SQL statement and return all rows as an array.
     *
     * @param string $sql
     * @param array  $parameters
     * @param bool   $indexedKeys
     * @return array
     */
    public function fetchRows($sql, array $parameters = [], $indexedKeys = false)
    {
        $statement = $this->executeQuery($sql, $parameters);

        return $statement->fetchAll($indexedKeys ? PDO::FETCH_NUM : PDO::FETCH_ASSOC);
    }

    /**
     * Execute an SQL statement and return the first column of the first row.
     *
     * @param string $sql
     * @param array  $parameters
     * @return string|false
     */
    public function fetchValue($sql, array $parameters = [])
    {
        $statement = $this->executeQuery($sql, $parameters);

        return $statement->fetchColumn(0);
    }

    /**
     * Quote a value for a safe use in query (eg. bla'bla -> 'bla''bla').
     *
     * @param mixed $value
     * @return string
     */
    public function quote($value)
    {
        $this->connect();

        return $this->pdo->quote($value);
    }

    /**
     * Get id of the last inserted row.
     *
     * @return int
     */
    public function getLastInsertId()
    {
        $this->connect();

        return (int) $this->pdo->lastInsertId();
    }

    /**
     * Prepare parameters for database use.
     *
     * @param array $parameters
     * @return array
     */
    protected function prepareParameters(array $parameters = [])
    {
        foreach ($parameters as &$parameterValue) {
            if (is_bool($parameterValue)) {
                $parameterValue = (int) $parameterValue;
            } elseif ($parameterValue instanceof \DateTimeInterface) {
                $parameterValue = $parameterValue->format('Y-m-d H:i:s');
            } elseif (!is_scalar($parameterValue) && $parameterValue !== null) {
                throw new \InvalidArgumentException(
                    sprintf('Invalid db parameter type "%s"', gettype($parameterValue))
                );
            }
        }
        unset($parameterValue);

        return $parameters;
    }

    /**
     * Begin transaction (turns off autocommit mode).
     *
     * @param bool $onlyIfNoActiveTransaction
     * @return bool
     */
    public function beginTransaction($onlyIfNoActiveTransaction = false)
    {
        $this->connect();

        if ($onlyIfNoActiveTransaction && $this->hasActiveTransaction()) {
            return false;
        }

        $this->pdo->beginTransaction();
        $this->hasActiveTransaction = true;

        return true;
    }

    /**
     * Commits current active transaction (restores autocommit mode).
     */
    public function commit()
    {
        $this->connect();

        $this->pdo->commit();
        $this->hasActiveTransaction = false;
    }

    /**
     * Rolls back current active transaction (restores autocommit mode).
     */
    public function rollBack()
    {
        $this->connect();

        $this->pdo->rollBack();
        $this->hasActiveTransaction = false;
    }

    /**
     * @return bool
     */
    public function hasActiveTransaction()
    {
        return $this->hasActiveTransaction;
    }

    /**
     * @param string $table
     * @param array $data
     * @param bool $updateOnDuplicateKey
     * @return int The number of rows affected
     */
    public function insert($table, array $data, $updateOnDuplicateKey = false)
    {
        $sql = 'INSERT INTO `' . $table . '`';
        if (empty($data)) {
            $sql .= "\nVALUES ()";
            $parameters = [];
        } else {
            $fields = array_keys($data);
            $fieldsSql = '`' . implode('`, `', $fields) . '`';
            $placeholdersSql = implode(', ', array_fill(0, count($fields), '?'));

            $sql .= ' (' . $fieldsSql . ")\n";
            $sql .= 'VALUES (' . $placeholdersSql . ")\n";

            $parameters = array_values($data);

            if ($updateOnDuplicateKey) {
                $assignmentSql = $this->getAssignmentSql(array_fill_keys($fields, '?'));
                $sql .= 'ON DUPLICATE KEY UPDATE ' . $assignmentSql;
                $parameters = array_merge($parameters, $parameters);
            }
        }

        return $this->exec($sql, $parameters);
    }

    /**
     * @param array $assignmentData
     * @return string
     */
    protected function getAssignmentSql(array $assignmentData)
    {
        $assignments = [];
        foreach ($assignmentData as $field => $value) {
            $assignments[] = sprintf('`%s` = %s', $field, $value);
        }

        return implode(', ', $assignments);
    }

    /**
     * @param string $table
     * @param array  $data
     * @param string $whereSql
     * @param array  $whereParameters
     * @return int The number of rows affected
     */
    public function update($table, array $data, $whereSql = '', array $whereParameters = [])
    {
        $fields = array_keys($data);
        $assignmentSql = $this->getAssignmentSql(array_fill_keys($fields, '?'));

        $sql = 'UPDATE `' . $table . "`\n"
            . 'SET ' . $assignmentSql
            . ($whereSql ? "\nWHERE " . $whereSql : '')
        ;

        $parameters = array_merge(array_values($data), $whereParameters);

        return $this->exec($sql, $parameters);
    }
}


1		<?php
2		/**
3		* Starlit Db.
4		*
5		* @copyright Copyright (c) 2016 Starweb AB
6		* @license BSD 3-Clause
7		*/
8
9		namespace Starlit\Db;
10
11		use Starlit\Db\Exception\ConnectionException;
12		use Starlit\Db\Exception\QueryException;
13		use \PDO;
14		use \PDOException;
15		use \PDOStatement;
16
17		/**
18		* Extended PDO database wrapper.
19		*
20		* @author Andreas Nilsson <http://github.com/jandreasn>
21		*/
22		class Db
23		{
24		/**
25		* Database handle/connection.
26		*
27		* @var PDO
28		*/
29		protected $pdo;
30
31		/**
32		* @var string
33		*/
34		protected $dsn;
35
36		/**
37		* @var string
38		*/
39		protected $username;
40
41		/**
42		* @var string
43		*/
44		protected $password;
45
46		/**
47		* @var array
48		*/
49		protected $options;
50
51		/**
52		* @var bool
53		*/
54		protected $hasActiveTransaction = false;
55
56		/**
57		* Constructor.
58		*
59		* @param string\|PDO $hostDsnOrPdo A MySQL host, a dsn or an existing PDO instance.
60		* @param string\|null $username
61		* @param string\|null $password
62		* @param string\|null $database
63		* @param array $options
64		*/
65	24	public function __construct(
66		$hostDsnOrPdo,
67		$username = null,
68		$password = null,
69		$database = null,
70		array $options = []
71		) {
72	24	if ($hostDsnOrPdo instanceof PDO) {
73	24	$this->pdo = $hostDsnOrPdo;
74		} elseif (strpos($hostDsnOrPdo, ':') !== false) {
75		$this->dsn = $hostDsnOrPdo;
76		} else {
77		$this->dsn = "mysql:host={$hostDsnOrPdo}" . ($database ? ";dbname={$database}" : '');
78		}
79
80	24	$this->username = $username;
81	24	$this->password = $password;
82	24	$this->options = $options;
83	24	}
84
85		/**
86		*/
87	16	public function connect()
88		{
89	16	if ($this->isConnected()) {
90	16	return;
91		}
92
93		$retries = isset($this->options['connectRetries'])? $this->options['connectRetries'] : 0;
94		do {
95		try {
96		$defaultPdoOptions = [
97		PDO::ATTR_TIMEOUT => 5,
98		// We want emulation by default (faster for single queries). Disable if you want to
99		// use proper native prepared statements
100		PDO::ATTR_EMULATE_PREPARES => true,
101		PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
102		PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
103		];
104		$pdoOptions = $defaultPdoOptions + (isset($this->options['pdo']) ? $this->options['pdo'] : []);
105
106		$this->pdo = new PDO(
107		$this->dsn,
108		$this->username,
109		$this->password,
110		$pdoOptions
111		);
112
113		return;
114		} catch (PDOException $e) {
115		if ($this->isConnectionExceptionCausedByConnection($e) && $retries > 0) {
116		// Sleep for 100 - 500 ms until next retry
117		usleep(rand(100000, 500000));
118		} else {
119		throw new ConnectionException($e);
120		}
121		}
122
123		} while ($retries-- > 0);
124		}
125
126		/**
127		* @param PDOException $exception
128		* @return bool
129		*/
130		private function isConnectionExceptionCausedByConnection(PDOException $exception)
131		{
132		return in_array($exception->getCode(), [
133		2002, // Can't connect to MySQL server (Socket)
134		2003, // Can't connect to MySQL server (TCP)
135		2006, // MySQL server has gone away
136		2013, // Lost connection to MySQL server during query
137		]);
138		}
139
140		/**
141		* Close the database connection.
142		*/
143	1	public function disconnect()
144		{
145	1	$this->pdo = null;
146	1	}
147
148		/**
149		*/
150	1	public function reconnect()
151		{
152	1	$this->disconnect();
153	1	$this->connect();
154	1	}
155
156		/**
157		* Check if database connection is open.
158		*
159		* @return bool
160		*/
161	17	public function isConnected()
162		{
163	17	return ($this->pdo instanceof PDO);
164		}
165
166		/**
167		* Returns the PDO handle.
168		*
169		* Can be used to gain access to any special PDO methods.
170		*
171		* @return PDO
172		*/
173	2	public function getPdo()
174		{
175	2	return $this->pdo;
176		}
177
178		/**
179		* Creates and executes a PDO statement.
180		*
181		* @param string $sql
182		* @param array $parameters
183		* @return PDOStatement
184		*/
185	8	protected function executeQuery($sql, array $parameters = [])
186		{
187	8	$this->connect();
188
189	8	$dbParameters = $this->prepareParameters($parameters);
190		try {
191	7	$pdoStatement = $this->pdo->prepare($sql);
192	6	if(!$pdoStatement->execute($dbParameters)) {
193	1	$errorInfo = $pdoStatement->errorInfo();
194
195	1	throw new PDOException($errorInfo[2], $errorInfo[1]);
196		}
197
198	5	return $pdoStatement;
199	2	} catch (PDOException $e) {
200	2	throw new QueryException($e, $sql, $dbParameters);
201		}
202		}
203
204		/**
205		* Execute an SQL statement and return the number of affected rows.
206		*
207		* @param string $sql
208		* @param array $parameters
209		* @return int The number of rows affected
210		*/
211	5	public function exec($sql, array $parameters = [])
212		{
213	5	$statement = $this->executeQuery($sql, $parameters);
214
215	2	return $statement->rowCount();
216		}
217
218		/**
219		* Execute an SQL statement and return the first row.
220		*
221		* @param string $sql
222		* @param array $parameters
223		* @param bool $indexedKeys
224		* @return array\|false
225		*/
226	1	public function fetchRow($sql, array $parameters = [], $indexedKeys = false)
227		{
228	1	$statement = $this->executeQuery($sql, $parameters);
229
230	1	return $statement->fetch($indexedKeys ? PDO::FETCH_NUM : PDO::FETCH_ASSOC);
231		}
232
233		/**
234		* Execute an SQL statement and return all rows as an array.
235		*
236		* @param string $sql
237		* @param array $parameters
238		* @param bool $indexedKeys
239		* @return array
240		*/
241	1	public function fetchRows($sql, array $parameters = [], $indexedKeys = false)
242		{
243	1	$statement = $this->executeQuery($sql, $parameters);
244
245	1	return $statement->fetchAll($indexedKeys ? PDO::FETCH_NUM : PDO::FETCH_ASSOC);
246		}
247
248		/**
249		* Execute an SQL statement and return the first column of the first row.
250		*
251		* @param string $sql
252		* @param array $parameters
253		* @return string\|false
254		*/
255	1	public function fetchValue($sql, array $parameters = [])
256		{
257	1	$statement = $this->executeQuery($sql, $parameters);
258
259	1	return $statement->fetchColumn(0);
260		}
261
262		/**
263		* Quote a value for a safe use in query (eg. bla'bla -> 'bla''bla').
264		*
265		* @param mixed $value
266		* @return string
267		*/
268	1	public function quote($value)
269		{
270	1	$this->connect();
271
272	1	return $this->pdo->quote($value);
273		}
274
275		/**
276		* Get id of the last inserted row.
277		*
278		* @return int
279		*/
280	1	public function getLastInsertId()
281		{
282	1	$this->connect();
283
284	1	return (int) $this->pdo->lastInsertId();
285		}
286
287		/**
288		* Prepare parameters for database use.
289		*
290		* @param array $parameters
291		* @return array
292		*/
293	8	protected function prepareParameters(array $parameters = [])
294		{
295	8	foreach ($parameters as &$parameterValue) {
296	7	if (is_bool($parameterValue)) {
297	1	$parameterValue = (int) $parameterValue;
298	7	} elseif ($parameterValue instanceof \DateTimeInterface) {
299	1	$parameterValue = $parameterValue->format('Y-m-d H:i:s');
300	6	} elseif (!is_scalar($parameterValue) && $parameterValue !== null) {
301	1	throw new \InvalidArgumentException(
302	7	sprintf('Invalid db parameter type "%s"', gettype($parameterValue))
303		);
304		}
305		}
306	7	unset($parameterValue);
307
308	7	return $parameters;
309		}
310
311		/**
312		* Begin transaction (turns off autocommit mode).
313		*
314		* @param bool $onlyIfNoActiveTransaction
315		* @return bool
316		*/
317	4	public function beginTransaction($onlyIfNoActiveTransaction = false)
318		{
319	4	$this->connect();
320
321	4	if ($onlyIfNoActiveTransaction && $this->hasActiveTransaction()) {
322	1	return false;
323		}
324
325	4	$this->pdo->beginTransaction();
326	4	$this->hasActiveTransaction = true;
327
328	4	return true;
329		}
330
331		/**
332		* Commits current active transaction (restores autocommit mode).
333		*/
334	2	public function commit()
335		{
336	2	$this->connect();
337
338	2	$this->pdo->commit();
339	2	$this->hasActiveTransaction = false;
340	2	}
341
342		/**
343		* Rolls back current active transaction (restores autocommit mode).
344		*/
345	2	public function rollBack()
346		{
347	2	$this->connect();
348
349	2	$this->pdo->rollBack();
350	2	$this->hasActiveTransaction = false;
351	2	}
352
353		/**
354		* @return bool
355		*/
356	3	public function hasActiveTransaction()
357		{
358	3	return $this->hasActiveTransaction;
359		}
360
361		/**
362		* @param string $table
363		* @param array $data
364		* @param bool $updateOnDuplicateKey
365		* @return int The number of rows affected
366		*/
367	3	public function insert($table, array $data, $updateOnDuplicateKey = false)
368		{
369	3	$sql = 'INSERT INTO `' . $table . '`';
370	3	if (empty($data)) {
371	1	$sql .= "\nVALUES ()";
372	1	$parameters = [];
373		} else {
374	2	$fields = array_keys($data);
375	2	$fieldsSql = '`' . implode('`, `', $fields) . '`';
376	2	$placeholdersSql = implode(', ', array_fill(0, count($fields), '?'));
377
378	2	$sql .= ' (' . $fieldsSql . ")\n";
379	2	$sql .= 'VALUES (' . $placeholdersSql . ")\n";
380
381	2	$parameters = array_values($data);
382
383	2	if ($updateOnDuplicateKey) {
384	1	$assignmentSql = $this->getAssignmentSql(array_fill_keys($fields, '?'));
385	1	$sql .= 'ON DUPLICATE KEY UPDATE ' . $assignmentSql;
386	1	$parameters = array_merge($parameters, $parameters);
387		}
388		}
389
390	3	return $this->exec($sql, $parameters);
391		}
392
393		/**
394		* @param array $assignmentData
395		* @return string
396		*/
397	2	protected function getAssignmentSql(array $assignmentData)
398		{
399	2	$assignments = [];
400	2	foreach ($assignmentData as $field => $value) {
401	2	$assignments[] = sprintf('`%s` = %s', $field, $value);
402		}
403
404	2	return implode(', ', $assignments);
405		}
406
407		/**
408		* @param string $table
409		* @param array $data
410		* @param string $whereSql
411		* @param array $whereParameters
412		* @return int The number of rows affected
413		*/
414	1	public function update($table, array $data, $whereSql = '', array $whereParameters = [])
415		{
416	1	$fields = array_keys($data);
417	1	$assignmentSql = $this->getAssignmentSql(array_fill_keys($fields, '?'));
418
419	1	$sql = 'UPDATE `' . $table . "`\n"
420	1	. 'SET ' . $assignmentSql
421	1	. ($whereSql ? "\nWHERE " . $whereSql : '')
422		;
423
424	1	$parameters = array_merge(array_values($data), $whereParameters);
425
426	1	return $this->exec($sql, $parameters);
427		}
428		}
429

starweb / starlit-db

GitHub Access Token became invalid

Pull Request — master (#12)

Db::exec() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like