Db::connect() - Code Metrics - Inspection of "Merge pull request #450 from AntonShevchuk/master" - bluzphp/framework - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( d292ba...467d47 )

by Anton

created 2017-11-08 13:52 UTC

Db::connect() A

↳ Parent: Db

Complexity

Conditions	3
Paths	9

Size

Total Lines	22
Code Lines	15

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	12
CRAP Score	3.0261

Importance

Changes

Metric	Value
cc	3
eloc	15
nc	9
nop	0
dl	0
loc	22
ccs	12
cts	14
cp	0.8571
crap	3.0261
rs	9.2
c	0
b	0
f	0

<?php
/**
 * Bluz Framework Component
 *
 * @copyright Bluz PHP Team
 * @link      https://github.com/bluzphp/framework
 */

declare(strict_types=1);

namespace Bluz\Db;

use Bluz\Common\Exception\ConfigurationException;
use Bluz\Common\Options;
use Bluz\Db\Exception\DbException;
use Bluz\Db\Query;
use Bluz\Proxy\Logger;

/**
 * PDO wrapper
 *
 * @package  Bluz\Db
 * @author   Anton Shevchuk
 * @link     https://github.com/bluzphp/framework/wiki/Db
 */
class Db
{
    use Options;

    /**
     * PDO connection settings
     *
     * @var  array
     * @link http://php.net/manual/en/pdo.construct.php
     */
    protected $connect = [
        'type' => 'mysql',
        'host' => 'localhost',
        'name' => '',
        'user' => 'root',
        'pass' => '',
        'options' => []
    ];

    /**
     * PDO connection flags
     *
     * @var  array
     * @link http://php.net/manual/en/pdo.setattribute.php
     */
    protected $attributes = [
        \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
    ];

    /**
     * @var \PDO PDO instance
     */
    protected $handler;

    /**
     * Setup connection
     *
     * Just save connection settings
     * <code>
     *     $db->setConnect([
     *         'type' => 'mysql',
     *         'host' => 'localhost',
     *         'name' => 'db name',
     *         'user' => 'root',
     *         'pass' => ''
     *     ]);
     * </code>
     *
     * @param  array $connect options
     *
     * @throws ConfigurationException
     * @return void
     * @throws DbException
     */
    public function setConnect(array $connect)
    {
        $this->connect = array_merge($this->connect, $connect);
        $this->checkConnect();
    }

    /**
     * Check connection options
     *
     * @return void
     * @throws ConfigurationException
     */
    private function checkConnect()
    {
        if (empty($this->connect['type']) ||
            empty($this->connect['host']) ||
            empty($this->connect['name']) ||
            empty($this->connect['user'])
        ) {
            throw new ConfigurationException(
                'Database adapter is not configured.
                Please check `db` configuration section: required type, host, db name and user'
            );
        }
    }

    /**
     * Setup attributes for PDO connect
     *
     * @param  array $attributes
     *
     * @return void
     */
    public function setAttributes(array $attributes)
    {
        $this->attributes = $attributes;
    }

    /**
     * Connect to Db
     *
     * @return bool
     * @throws DbException
     */
    public function connect() : bool
    {
        try {
            $this->checkConnect();
            $this->log('Connect to ' . $this->connect['host']);
            $this->handler = new \PDO(
                $this->connect['type'] . ':host=' . $this->connect['host'] . ';dbname=' . $this->connect['name'],
                $this->connect['user'],
                $this->connect['pass'],
                $this->connect['options']
            );

            foreach ($this->attributes as $attribute => $value) {
                $this->handler->setAttribute($attribute, $value);
            }

            $this->ok();
        } catch (\Exception $e) {
            throw new DbException("Attempt connection to database is failed: {$e->getMessage()}");
        }
        return true;
    }

    /**
     * Disconnect PDO and clean default adapter
     *
     * @return void
     */
    public function disconnect()
    {
        if ($this->handler) {
            $this->handler = null;
        }
    }

    /**
     * Return PDO handler
     *
     * @return \PDO
     * @throws DbException
     */
    public function handler() : \PDO
    {
        if (null === $this->handler) {
            $this->connect();
        }
        return $this->handler;
    }

    /**
     * Prepare SQL query and return PDO Statement
     *
     * @param  string $sql    SQL query with placeholders
     * @param  array  $params params for query placeholders
     *
     * @todo Switch to PDO::activeQueryString() when it will be possible
     * @link https://wiki.php.net/rfc/debugging_pdo_prepared_statement_emulation
     *
     * @return \PDOStatement
     * @throws DbException
     */
    protected function prepare($sql, $params) : \PDOStatement
    {
        $stmt = $this->handler()->prepare($sql);
        $stmt->execute($params);

        $this->log($sql, $params);

        return $stmt;
    }

    /**
     * Quotes a string for use in a query
     *
     * Example of usage
     * <code>
     *     $db->quote($_GET['id'])
     * </code>
     *
     * @param  string $value
     * @param  int    $type
     *
     * @return string
     * @throws DbException
     */
    public function quote($value, $type = \PDO::PARAM_STR) : string
    {
        return $this->handler()->quote($value, $type);
    }

    /**
     * Quote a string so it can be safely used as a table or column name
     *
     * @param  string $identifier
     *
     * @return string
     */
    public function quoteIdentifier($identifier) : string
    {
        // switch statement for DB type
        switch ($this->connect['type']) {
            case 'mysql':
                return '`' . str_replace('`', '``', $identifier) . '`';
            case 'postgresql':
            case 'sqlite':
            default:
                return '"' . str_replace('"', '\\' . '"', $identifier) . '"';
        }
    }

    /**
     * Execute SQL query
     *
     * Example of usage
     * <code>
     *     $db->query("SET NAMES 'utf8'");
     * </code>
     *
     * @param  string $sql    SQL query with placeholders
     *                        "UPDATE users SET name = :name WHERE id = :id"
     * @param  array  $params params for query placeholders (optional)
     *                        array (':name' => 'John', ':id' => '123')
     * @param  array  $types  Types of params (optional)
     *                        array (':name' => \PDO::PARAM_STR, ':id' => \PDO::PARAM_INT)
     *
     * @return integer the number of rows
     * @throws DbException
     */
    public function query($sql, $params = [], $types = [])
    {
        $stmt = $this->handler()->prepare($sql);
        foreach ($params as $key => &$param) {
            $stmt->bindParam(
                (is_int($key) ? $key + 1 : ':' . $key),
                $param,
                $types[$key] ?? \PDO::PARAM_STR
            );
        }
        $this->log($sql, $params);
        $stmt->execute($params);
        $this->ok();
        return $stmt->rowCount();
    }

    /**
     * Create new query select builder
     *
     * @param array|string ...$select The selection expressions
     *
     * @return Query\Select
     */
    public function select(...$select) : Query\Select
    {
        $query = new Query\Select();
        $query->select(...$select);
        return $query;
    }

    /**
     * Create new query insert builder
     *
     * @param  string $table
     *
     * @return Query\Insert
     */
    public function insert($table) : Query\Insert
    {
        $query = new Query\Insert();
        $query->insert($table);
        return $query;
    }

    /**
     * Create new query update builder
     *
     * @param  string $table
     *
     * @return Query\Update
     */
    public function update($table) : Query\Update
    {
        $query = new Query\Update();
        $query->update($table);
        return $query;
    }

    /**
     * Create new query update builder
     *
     * @param  string $table
     *
     * @return Query\Delete
     */
    public function delete($table) : Query\Delete
    {
        $query = new Query\Delete();
        $query->delete($table);
        return $query;
    }

    /**
     * Return first field from first element from the result set
     *
     * Example of usage
     * <code>
     *     $db->fetchOne("SELECT COUNT(*) FROM users");
     * </code>
     *
     * @param  string $sql     SQL query with placeholders
     *                         "SELECT * FROM users WHERE name = :name AND pass = :pass"
     * @param  array  $params  params for query placeholders (optional)
     *                         array (':name' => 'John', ':pass' => '123456')
     *
     * @return string
     * @throws DbException
     */
    public function fetchOne($sql, $params = [])
    {
        $stmt = $this->prepare($sql, $params);
        $result = $stmt->fetch(\PDO::FETCH_COLUMN);

        $this->ok();
        return $result;
    }

    /**
     * Returns an array containing first row from the result set
     *
     * Example of usage
     * <code>
     *     $db->fetchRow("SELECT name, email FROM users WHERE id = ". $db->quote($id));
     *     $db->fetchRow("SELECT name, email FROM users WHERE id = ?", [$id]);
     *     $db->fetchRow("SELECT name, email FROM users WHERE id = :id", [':id'=>$id]);
     * </code>
     *
     * @param  string $sql     SQL query with placeholders
     *                         "SELECT * FROM users WHERE name = :name AND pass = :pass"
     * @param  array  $params  params for query placeholders (optional)
     *                         array (':name' => 'John', ':pass' => '123456')
     *
     * @return array           array ('name' => 'John', 'email' => '[email protected]')
     * @throws DbException
     */
    public function fetchRow($sql, $params = [])
    {
        $stmt = $this->prepare($sql, $params);
        $result = $stmt->fetch(\PDO::FETCH_ASSOC);

        $this->ok();
        return $result;
    }

    /**
     * Returns an array containing all of the result set rows
     *
     * Example of usage
     * <code>
     *     $db->fetchAll("SELECT * FROM users WHERE ip = ?", ['192.168.1.1']);
     * </code>
     *
     * @param  string $sql    SQL query with placeholders
     *                        "SELECT * FROM users WHERE ip = :ip"
     * @param  array  $params params for query placeholders (optional)
     *                        array (':ip' => '127.0.0.1')
     *
     * @return array[]
     * @throws DbException
     */
    public function fetchAll($sql, $params = [])
    {
        $stmt = $this->prepare($sql, $params);
        $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        $this->ok();
        return $result;
    }

    /**
     * Returns an array containing one column from the result set rows
     *
     * @param  string $sql    SQL query with placeholders
     *                        "SELECT id FROM users WHERE ip = :ip"
     * @param  array  $params params for query placeholders (optional)
     *                        array (':ip' => '127.0.0.1')
     *
     * @return array
     * @throws DbException
     */
    public function fetchColumn($sql, $params = [])
    {
        $stmt = $this->prepare($sql, $params);
        $result = $stmt->fetchAll(\PDO::FETCH_COLUMN);

        $this->ok();
        return $result;
    }

    /**
     * Returns an array containing all of the result set rows
     *
     * Group by first column
     *
     * <code>
     *     $db->fetchGroup("SELECT ip, COUNT(id) FROM users GROUP BY ip", []);
     * </code>
     *
     * @param  string $sql    SQL query with placeholders
     *                        "SELECT ip, id FROM users"
     * @param  array  $params params for query placeholders (optional)
     * @param  mixed  $object
     *
     * @return array
     * @throws DbException
     */
    public function fetchGroup($sql, $params = [], $object = null)
    {
        $stmt = $this->prepare($sql, $params);

        if ($object) {
            $result = $stmt->fetchAll(\PDO::FETCH_CLASS | \PDO::FETCH_GROUP, $object);
        } else {
            $result = $stmt->fetchAll(\PDO::FETCH_ASSOC | \PDO::FETCH_GROUP);
        }

        $this->ok();
        return $result;
    }

    /**
     * Returns an array containing all of the result set rows
     *
     * Group by first column
     *
     * @param  string $sql    SQL query with placeholders
     *                        "SELECT ip, id FROM users"
     * @param  array  $params params for query placeholders (optional)
     *
     * @return array
     * @throws DbException
     */
    public function fetchColumnGroup($sql, $params = [])
    {
        $stmt = $this->prepare($sql, $params);
        $result = $stmt->fetchAll(\PDO::FETCH_COLUMN | \PDO::FETCH_GROUP);

        $this->ok();
        return $result;
    }

    /**
     * Returns an array containing all of the result set rows
     *
     * Group by first unique column
     *
     * @param  string $sql    SQL query with placeholders
     *                        "SELECT email, name, sex FROM users"
     * @param  array  $params params for query placeholders (optional)
     *
     * @return array
     * @throws DbException
     */
    public function fetchUniqueGroup($sql, $params = [])
    {
        $stmt = $this->prepare($sql, $params);
        $result = $stmt->fetchAll(\PDO::FETCH_UNIQUE | \PDO::FETCH_ASSOC | \PDO::FETCH_GROUP);

        $this->ok();
        return $result;
    }

    /**
     * Returns a key-value array
     *
     * @param  string $sql    SQL query with placeholders
     *                        "SELECT id, username FROM users WHERE ip = :ip"
     * @param  array  $params params for query placeholders (optional)
     *                        array (':ip' => '127.0.0.1')
     *
     * @return array
     * @throws DbException
     */
    public function fetchPairs($sql, $params = [])
    {
        $stmt = $this->prepare($sql, $params);
        $result = $stmt->fetchAll(\PDO::FETCH_KEY_PAIR);

        $this->ok();
        return $result;
    }

    /**
     * Returns an object containing first row from the result set
     *
     * Example of usage
     * <code>
     *     // Fetch object to stdClass
     *     $stdClass = $db->fetchObject('SELECT * FROM some_table WHERE id = ?', [$id]);
     *     // Fetch object to new Some object
     *     $someClass = $db->fetchObject('SELECT * FROM some_table WHERE id = ?', [$id], 'Some');
     *     // Fetch object to exists instance of Some object
     *     $someClass = $db->fetchObject('SELECT * FROM some_table WHERE id = ?', [$id], $someClass);
     * </code>
     *
     * @param  string $sql    SQL query with placeholders
     *                        "SELECT * FROM users WHERE name = :name AND pass = :pass"
     * @param  array  $params params for query placeholders (optional)
     *                        array (':name' => 'John', ':pass' => '123456')
     * @param  mixed  $object
     *
     * @return array
     * @throws DbException
     */
    public function fetchObject($sql, $params = [], $object = 'stdClass')
    {
        $stmt = $this->prepare($sql, $params);

        if (is_string($object)) {
            // some class name
            $result = $stmt->fetchObject($object);
        } else {
            // some instance
            $stmt->setFetchMode(\PDO::FETCH_INTO, $object);
            $result = $stmt->fetch(\PDO::FETCH_INTO);
        }

        $stmt->closeCursor();
        $this->ok();
        return $result;
    }

    /**
     * Returns an array of objects containing the result set
     *
     * @param  string $sql    SQL query with placeholders
     *                        "SELECT * FROM users WHERE name = :name AND pass = :pass"
     * @param  array  $params params for query placeholders (optional)
     *                        array (':name' => 'John', ':pass' => '123456')
     * @param  mixed  $object Class name or instance
     *
     * @return array
     * @throws DbException
     */
    public function fetchObjects($sql, $params = [], $object = null)
    {
        $stmt = $this->prepare($sql, $params);

        if (is_string($object)) {
            // fetch to some class by name
            $result = $stmt->fetchAll(\PDO::FETCH_CLASS, $object);
        } else {
            // fetch to StdClass
            $result = $stmt->fetchAll(\PDO::FETCH_OBJ);
        }

        $stmt->closeCursor();
        $this->ok();
        return $result;
    }

    /**
     * Returns an array of linked objects containing the result set
     *
     * @param  string $sql    SQL query with placeholders
     *                        "SELECT '__users', u.*, '__users_profile', up.*
     *                        FROM users u
     *                        LEFT JOIN users_profile up ON up.userId = u.id
     *                        WHERE u.name = :name"
     * @param  array  $params params for query placeholders (optional)
     *                        array (':name' => 'John')
     *
     * @return array
     * @throws DbException
     */
    public function fetchRelations($sql, $params = [])
    {
        $stmt = $this->prepare($sql, $params);

        $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        // prepare results
        $result = Relations::fetch($result);

        $stmt->closeCursor();
        $this->ok();
        return $result;
    }

    /**
     * Transaction wrapper
     *
     * Example of usage
     * <code>
     *     $db->transaction(function() use ($db) {
     *         $db->query("INSERT INTO `table` ...");
     *         $db->query("UPDATE `table` ...");
     *         $db->query("DELETE FROM `table` ...");
     *     })
     * </code>
     *
     * @param  callable $process callable structure - closure function or class with __invoke() method
     *
     * @return mixed|bool
     * @throws DbException
     */
    public function transaction(callable $process)
    {
        try {
            $this->handler()->beginTransaction();
            $result = $process();
            $this->handler()->commit();
            return $result ?? true;
        } catch (\PDOException $e) {
            $this->handler()->rollBack();
            Logger::error($e->getMessage());
            return false;
        }
    }

    /**
     * Setup timer
     *
     * @return void
     */
    protected function ok()
    {
        Logger::info('<<<');
    }

    /**
     * Log queries by Application
     *
     * @param  string $sql SQL query for logs
     * @param  array  $context
     *
     * @return void
     */
    protected function log($sql, array $context = [])
    {
        $sql = str_replace('%', '%%', $sql);
        $sql = preg_replace('/\?/', '"%s"', $sql, count($context));

        // replace mask by data
        $log = vsprintf($sql, $context);

        Logger::info($log);
    }
}


1		<?php
2		/**
3		* Bluz Framework Component
4		*
5		* @copyright Bluz PHP Team
6		* @link https://github.com/bluzphp/framework
7		*/
8
9		declare(strict_types=1);
10
11		namespace Bluz\Db;
12
13		use Bluz\Common\Exception\ConfigurationException;
14		use Bluz\Common\Options;
15		use Bluz\Db\Exception\DbException;
16		use Bluz\Db\Query;
17		use Bluz\Proxy\Logger;
18
19		/**
20		* PDO wrapper
21		*
22		* @package Bluz\Db
23		* @author Anton Shevchuk
24		* @link https://github.com/bluzphp/framework/wiki/Db
25		*/
26		class Db
27		{
28		use Options;
29
30		/**
31		* PDO connection settings
32		*
33		* @var array
34		* @link http://php.net/manual/en/pdo.construct.php
35		*/
36		protected $connect = [
37		'type' => 'mysql',
38		'host' => 'localhost',
39		'name' => '',
40		'user' => 'root',
41		'pass' => '',
42		'options' => []
43		];
44
45		/**
46		* PDO connection flags
47		*
48		* @var array
49		* @link http://php.net/manual/en/pdo.setattribute.php
50		*/
51		protected $attributes = [
52		\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
53		];
54
55		/**
56		* @var \PDO PDO instance
57		*/
58		protected $handler;
59
60		/**
61		* Setup connection
62		*
63		* Just save connection settings
64		* <code>
65		* $db->setConnect([
66		* 'type' => 'mysql',
67		* 'host' => 'localhost',
68		* 'name' => 'db name',
69		* 'user' => 'root',
70		* 'pass' => ''
71		* ]);
72		* </code>
73		*
74		* @param array $connect options
75		*
76		* @throws ConfigurationException
77		* @return void
78		* @throws DbException
79		*/
80	47	public function setConnect(array $connect)
81		{
82	47	$this->connect = array_merge($this->connect, $connect);
83	47	$this->checkConnect();
84	47	}
85
86		/**
87		* Check connection options
88		*
89		* @return void
90		* @throws ConfigurationException
91		*/
92	47	private function checkConnect()
93		{
94	47	if (empty($this->connect['type']) \|\|
95	47	empty($this->connect['host']) \|\|
96	47	empty($this->connect['name']) \|\|
97	47	empty($this->connect['user'])
98		) {
99	1	throw new ConfigurationException(
100	1	'Database adapter is not configured.
101		Please check `db` configuration section: required type, host, db name and user'
102		);
103		}
104	47	}
105
106		/**
107		* Setup attributes for PDO connect
108		*
109		* @param array $attributes
110		*
111		* @return void
112		*/
113		public function setAttributes(array $attributes)
114		{
115		$this->attributes = $attributes;
116		}
117
118		/**
119		* Connect to Db
120		*
121		* @return bool
122		* @throws DbException
123		*/
124	39	public function connect() : bool
125		{
126		try {
127	39	$this->checkConnect();
128	39	$this->log('Connect to ' . $this->connect['host']);
129	39	$this->handler = new \PDO(
130	39	$this->connect['type'] . ':host=' . $this->connect['host'] . ';dbname=' . $this->connect['name'],
131	39	$this->connect['user'],
132	39	$this->connect['pass'],
133	39	$this->connect['options']
134		);
135
136	39	foreach ($this->attributes as $attribute => $value) {
137	39	$this->handler->setAttribute($attribute, $value);
138		}
139
140	39	$this->ok();
141		} catch (\Exception $e) {
142		throw new DbException("Attempt connection to database is failed: {$e->getMessage()}");
143		}
144	39	return true;
145		}
146
147		/**
148		* Disconnect PDO and clean default adapter
149		*
150		* @return void
151		*/
152	22	public function disconnect()
153		{
154	22	if ($this->handler) {
155	16	$this->handler = null;
156		}
157	22	}
158
159		/**
160		* Return PDO handler
161		*
162		* @return \PDO
163		* @throws DbException
164		*/
165	39	public function handler() : \PDO
166		{
167	39	if (null === $this->handler) {
168	38	$this->connect();
169		}
170	39	return $this->handler;
171		}
172
173		/**
174		* Prepare SQL query and return PDO Statement
175		*
176		* @param string $sql SQL query with placeholders
177		* @param array $params params for query placeholders
178		*
179		* @todo Switch to PDO::activeQueryString() when it will be possible
180		* @link https://wiki.php.net/rfc/debugging_pdo_prepared_statement_emulation
181		*
182		* @return \PDOStatement
183		* @throws DbException
184		*/
185	26	protected function prepare($sql, $params) : \PDOStatement
186		{
187	26	$stmt = $this->handler()->prepare($sql);
188	26	$stmt->execute($params);
189
190	26	$this->log($sql, $params);
191
192	26	return $stmt;
193		}
194
195		/**
196		* Quotes a string for use in a query
197		*
198		* Example of usage
199		* <code>
200		* $db->quote($_GET['id'])
201		* </code>
202		*
203		* @param string $value
204		* @param int $type
205		*
206		* @return string
207		* @throws DbException
208		*/
209	1	public function quote($value, $type = \PDO::PARAM_STR) : string
210		{
211	1	return $this->handler()->quote($value, $type);
212		}
213
214		/**
215		* Quote a string so it can be safely used as a table or column name
216		*
217		* @param string $identifier
218		*
219		* @return string
220		*/
221	16	public function quoteIdentifier($identifier) : string
222		{
223		// switch statement for DB type
224	16	switch ($this->connect['type']) {
225	16	case 'mysql':
226	16	return '`' . str_replace('`', '``', $identifier) . '`';
227		case 'postgresql':
228		case 'sqlite':
229		default:
230		return '"' . str_replace('"', '\\' . '"', $identifier) . '"';
231		}
232		}
233
234		/**
235		* Execute SQL query
236		*
237		* Example of usage
238		* <code>
239		* $db->query("SET NAMES 'utf8'");
240		* </code>
241		*
242		* @param string $sql SQL query with placeholders
243		* "UPDATE users SET name = :name WHERE id = :id"
244		* @param array $params params for query placeholders (optional)
245		* array (':name' => 'John', ':id' => '123')
246		* @param array $types Types of params (optional)
247		* array (':name' => \PDO::PARAM_STR, ':id' => \PDO::PARAM_INT)
248		*
249		* @return integer the number of rows
250		* @throws DbException
251		*/
252	22	public function query($sql, $params = [], $types = [])
253		{
254	22	$stmt = $this->handler()->prepare($sql);
255	22	foreach ($params as $key => &$param) {
256	11	$stmt->bindParam(
257	11	(is_int($key) ? $key + 1 : ':' . $key),
258	11	$param,
259	11	$types[$key] ?? \PDO::PARAM_STR
260		);
261		}
262	22	$this->log($sql, $params);
263	22	$stmt->execute($params);
264	22	$this->ok();
265	22	return $stmt->rowCount();
266		}
267
268		/**
269		* Create new query select builder
270		*
271		* @param array\|string ...$select The selection expressions
272		*
273		* @return Query\Select
274		*/
275	1	public function select(...$select) : Query\Select
276		{
277	1	$query = new Query\Select();
278	1	$query->select(...$select);
279	1	return $query;
280		}
281
282		/**
283		* Create new query insert builder
284		*
285		* @param string $table
286		*
287		* @return Query\Insert
288		*/
289	1	public function insert($table) : Query\Insert
290		{
291	1	$query = new Query\Insert();
292	1	$query->insert($table);
293	1	return $query;
294		}
295
296		/**
297		* Create new query update builder
298		*
299		* @param string $table
300		*
301		* @return Query\Update
302		*/
303	1	public function update($table) : Query\Update
304		{
305	1	$query = new Query\Update();
306	1	$query->update($table);
307	1	return $query;
308		}
309
310		/**
311		* Create new query update builder
312		*
313		* @param string $table
314		*
315		* @return Query\Delete
316		*/
317	9	public function delete($table) : Query\Delete
318		{
319	9	$query = new Query\Delete();
320	9	$query->delete($table);
321	9	return $query;
322		}
323
324		/**
325		* Return first field from first element from the result set
326		*
327		* Example of usage
328		* <code>
329		* $db->fetchOne("SELECT COUNT(*) FROM users");
330		* </code>
331		*
332		* @param string $sql SQL query with placeholders
333		* "SELECT * FROM users WHERE name = :name AND pass = :pass"
334		* @param array $params params for query placeholders (optional)
335		* array (':name' => 'John', ':pass' => '123456')
336		*
337		* @return string
338		* @throws DbException
339		*/
340	5	public function fetchOne($sql, $params = [])
341		{
342	5	$stmt = $this->prepare($sql, $params);
343	5	$result = $stmt->fetch(\PDO::FETCH_COLUMN);
344
345	5	$this->ok();
346	5	return $result;
347		}
348
349		/**
350		* Returns an array containing first row from the result set
351		*
352		* Example of usage
353		* <code>
354		* $db->fetchRow("SELECT name, email FROM users WHERE id = ". $db->quote($id));
355		* $db->fetchRow("SELECT name, email FROM users WHERE id = ?", [$id]);
356		* $db->fetchRow("SELECT name, email FROM users WHERE id = :id", [':id'=>$id]);
357		* </code>
358		*
359		* @param string $sql SQL query with placeholders
360		* "SELECT * FROM users WHERE name = :name AND pass = :pass"
361		* @param array $params params for query placeholders (optional)
362		* array (':name' => 'John', ':pass' => '123456')
363		*
364		* @return array array ('name' => 'John', 'email' => '[email protected]')
365		* @throws DbException
366		*/
367	1	public function fetchRow($sql, $params = [])
368		{
369	1	$stmt = $this->prepare($sql, $params);
370	1	$result = $stmt->fetch(\PDO::FETCH_ASSOC);
371
372	1	$this->ok();
373	1	return $result;
374		}
375
376		/**
377		* Returns an array containing all of the result set rows
378		*
379		* Example of usage
380		* <code>
381		* $db->fetchAll("SELECT * FROM users WHERE ip = ?", ['192.168.1.1']);
382		* </code>
383		*
384		* @param string $sql SQL query with placeholders
385		* "SELECT * FROM users WHERE ip = :ip"
386		* @param array $params params for query placeholders (optional)
387		* array (':ip' => '127.0.0.1')
388		*
389		* @return array[]
390		* @throws DbException
391		*/
392	3	public function fetchAll($sql, $params = [])
393		{
394	3	$stmt = $this->prepare($sql, $params);
395	3	$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
396
397	3	$this->ok();
398	3	return $result;
399		}
400
401		/**
402		* Returns an array containing one column from the result set rows
403		*
404		* @param string $sql SQL query with placeholders
405		* "SELECT id FROM users WHERE ip = :ip"
406		* @param array $params params for query placeholders (optional)
407		* array (':ip' => '127.0.0.1')
408		*
409		* @return array
410		* @throws DbException
411		*/
412	1	public function fetchColumn($sql, $params = [])
413		{
414	1	$stmt = $this->prepare($sql, $params);
415	1	$result = $stmt->fetchAll(\PDO::FETCH_COLUMN);
416
417	1	$this->ok();
418	1	return $result;
419		}
420
421		/**
422		* Returns an array containing all of the result set rows
423		*
424		* Group by first column
425		*
426		* <code>
427		* $db->fetchGroup("SELECT ip, COUNT(id) FROM users GROUP BY ip", []);
428		* </code>
429		*
430		* @param string $sql SQL query with placeholders
431		* "SELECT ip, id FROM users"
432		* @param array $params params for query placeholders (optional)
433		* @param mixed $object
434		*
435		* @return array
436		* @throws DbException
437		*/
438	1	public function fetchGroup($sql, $params = [], $object = null)
439		{
440	1	$stmt = $this->prepare($sql, $params);
441
442	1	if ($object) {
443		$result = $stmt->fetchAll(\PDO::FETCH_CLASS \| \PDO::FETCH_GROUP, $object);
444		} else {
445	1	$result = $stmt->fetchAll(\PDO::FETCH_ASSOC \| \PDO::FETCH_GROUP);
446		}
447
448	1	$this->ok();
449	1	return $result;
450		}
451
452		/**
453		* Returns an array containing all of the result set rows
454		*
455		* Group by first column
456		*
457		* @param string $sql SQL query with placeholders
458		* "SELECT ip, id FROM users"
459		* @param array $params params for query placeholders (optional)
460		*
461		* @return array
462		* @throws DbException
463		*/
464	1	public function fetchColumnGroup($sql, $params = [])
465		{
466	1	$stmt = $this->prepare($sql, $params);
467	1	$result = $stmt->fetchAll(\PDO::FETCH_COLUMN \| \PDO::FETCH_GROUP);
468
469	1	$this->ok();
470	1	return $result;
471		}
472
473		/**
474		* Returns an array containing all of the result set rows
475		*
476		* Group by first unique column
477		*
478		* @param string $sql SQL query with placeholders
479		* "SELECT email, name, sex FROM users"
480		* @param array $params params for query placeholders (optional)
481		*
482		* @return array
483		* @throws DbException
484		*/
485	2	public function fetchUniqueGroup($sql, $params = [])
486		{
487	2	$stmt = $this->prepare($sql, $params);
488	2	$result = $stmt->fetchAll(\PDO::FETCH_UNIQUE \| \PDO::FETCH_ASSOC \| \PDO::FETCH_GROUP);
489
490	2	$this->ok();
491	2	return $result;
492		}
493
494		/**
495		* Returns a key-value array
496		*
497		* @param string $sql SQL query with placeholders
498		* "SELECT id, username FROM users WHERE ip = :ip"
499		* @param array $params params for query placeholders (optional)
500		* array (':ip' => '127.0.0.1')
501		*
502		* @return array
503		* @throws DbException
504		*/
505	1	public function fetchPairs($sql, $params = [])
506		{
507	1	$stmt = $this->prepare($sql, $params);
508	1	$result = $stmt->fetchAll(\PDO::FETCH_KEY_PAIR);
509
510	1	$this->ok();
511	1	return $result;
512		}
513
514		/**
515		* Returns an object containing first row from the result set
516		*
517		* Example of usage
518		* <code>
519		* // Fetch object to stdClass
520		* $stdClass = $db->fetchObject('SELECT * FROM some_table WHERE id = ?', [$id]);
521		* // Fetch object to new Some object
522		* $someClass = $db->fetchObject('SELECT * FROM some_table WHERE id = ?', [$id], 'Some');
523		* // Fetch object to exists instance of Some object
524		* $someClass = $db->fetchObject('SELECT * FROM some_table WHERE id = ?', [$id], $someClass);
525		* </code>
526		*
527		* @param string $sql SQL query with placeholders
528		* "SELECT * FROM users WHERE name = :name AND pass = :pass"
529		* @param array $params params for query placeholders (optional)
530		* array (':name' => 'John', ':pass' => '123456')
531		* @param mixed $object
532		*
533		* @return array
534		* @throws DbException
535		*/
536	3	public function fetchObject($sql, $params = [], $object = 'stdClass')
537		{
538	3	$stmt = $this->prepare($sql, $params);
539
540	3	if (is_string($object)) {
541		// some class name
542	2	$result = $stmt->fetchObject($object);
543		} else {
544		// some instance
545	1	$stmt->setFetchMode(\PDO::FETCH_INTO, $object);
546	1	$result = $stmt->fetch(\PDO::FETCH_INTO);
547		}
548
549	3	$stmt->closeCursor();
550	3	$this->ok();
551	3	return $result;
552		}
553
554		/**
555		* Returns an array of objects containing the result set
556		*
557		* @param string $sql SQL query with placeholders
558		* "SELECT * FROM users WHERE name = :name AND pass = :pass"
559		* @param array $params params for query placeholders (optional)
560		* array (':name' => 'John', ':pass' => '123456')
561		* @param mixed $object Class name or instance
562		*
563		* @return array
564		* @throws DbException
565		*/
566	12	public function fetchObjects($sql, $params = [], $object = null)
567		{
568	12	$stmt = $this->prepare($sql, $params);
569
570	12	if (is_string($object)) {
571		// fetch to some class by name
572	11	$result = $stmt->fetchAll(\PDO::FETCH_CLASS, $object);
573		} else {
574		// fetch to StdClass
575	1	$result = $stmt->fetchAll(\PDO::FETCH_OBJ);
576		}
577
578	12	$stmt->closeCursor();
579	12	$this->ok();
580	12	return $result;
581		}
582
583		/**
584		* Returns an array of linked objects containing the result set
585		*
586		* @param string $sql SQL query with placeholders
587		* "SELECT '__users', u., '__users_profile', up.
588		* FROM users u
589		* LEFT JOIN users_profile up ON up.userId = u.id
590		* WHERE u.name = :name"
591		* @param array $params params for query placeholders (optional)
592		* array (':name' => 'John')
593		*
594		* @return array
595		* @throws DbException
596		*/
597		public function fetchRelations($sql, $params = [])
598		{
599		$stmt = $this->prepare($sql, $params);
600
601		$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
602
603		// prepare results
604		$result = Relations::fetch($result);
605
606		$stmt->closeCursor();
607		$this->ok();
608		return $result;
609		}
610
611		/**
612		* Transaction wrapper
613		*
614		* Example of usage
615		* <code>
616		* $db->transaction(function() use ($db) {
617		* $db->query("INSERT INTO `table` ...");
618		* $db->query("UPDATE `table` ...");
619		* $db->query("DELETE FROM `table` ...");
620		* })
621		* </code>
622		*
623		* @param callable $process callable structure - closure function or class with __invoke() method
624		*
625		* @return mixed\|bool
626		* @throws DbException
627		*/
628	6	public function transaction(callable $process)
629		{
630		try {
631	6	$this->handler()->beginTransaction();
632	6	$result = $process();
633	5	$this->handler()->commit();
634	5	return $result ?? true;
635	1	} catch (\PDOException $e) {
636	1	$this->handler()->rollBack();
637	1	Logger::error($e->getMessage());
638	1	return false;
639		}
640		}
641
642		/**
643		* Setup timer
644		*
645		* @return void
646		*/
647	39	protected function ok()
648		{
649	39	Logger::info('<<<');
650	39	}
651
652		/**
653		* Log queries by Application
654		*
655		* @param string $sql SQL query for logs
656		* @param array $context
657		*
658		* @return void
659		*/
660	39	protected function log($sql, array $context = [])
661		{
662	39	$sql = str_replace('%', '%%', $sql);
663	39	$sql = preg_replace('/\?/', '"%s"', $sql, count($context));
664
665		// replace mask by data
666	39	$log = vsprintf($sql, $context);
667
668	39	Logger::info($log);
669	39	}
670		}
671

bluzphp / framework

Push — master ( d292ba...467d47 )

Db::connect() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like