Db::fetchGroup() - Code Metrics - Inspection of "Merge pull request #472 from bluzphp/develop" - bluzphp/framework - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 83cea8...e47c19 )

by Anton

created 2021-07-02 14:56 UTC

Db::fetchGroup() A

↳ Parent: Db

Complexity

Conditions	2
Paths	2

Size

Total Lines	12
Code Lines	7

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	6
CRAP Score	2.0116

Importance

Changes	1
Bugs	0	Features	0

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

<?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;
use PDO;
use PDOException;
use PDOStatement;

/**
 * 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
     */
    public function setConnect(array $connect): void
    {
        $this->connect = array_merge($this->connect, $connect);
        $this->checkConnect();
    }

    /**
     * Check connection options
     *
     * @return void
     * @throws ConfigurationException
     */
    private function checkConnect(): void
    {
        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): void
    {
        $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(): void
    {
        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(string $sql, array $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(string $value, int $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(string $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(string $sql, array $params = [], array $types = []): int
    {
        $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 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(string $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(string $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(string $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|false
     * @throws DbException
     */
    public function fetchOne(string $sql, array $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|false     array ('name' => 'John', 'email' => '[email protected]')
     * @throws DbException
     */
    public function fetchRow(string $sql, array $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[]|false
     * @throws DbException
     */
    public function fetchAll(string $sql, array $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|false
     * @throws DbException
     */
    public function fetchColumn(string $sql, array $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|false
     * @throws DbException
     */
    public function fetchGroup(string $sql, array $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|false
     * @throws DbException
     */
    public function fetchColumnGroup(string $sql, array $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|false
     * @throws DbException
     */
    public function fetchUniqueGroup(string $sql, array $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|false
     * @throws DbException
     */
    public function fetchPairs(string $sql, array $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 mixed
     * @throws DbException
     */
    public function fetchObject(string $sql, array $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|false
     * @throws DbException
     */
    public function fetchObjects(string $sql, array $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|false
     * @throws DbException
     */
    public function fetchRelations(string $sql, array $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(): void
    {
        Logger::info('<<<');
    }

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

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

        Logger::info($log);
    }
}


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

bluzphp / framework

Push — master ( 83cea8...e47c19 )

Db::fetchGroup() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like