Db::fetchColumnGroup() - Code Metrics - Inspection of "Updated codestyle" - bluzphp/framework - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — develop ( ec7ed8...af61f1 )

by Anton

created 2019-05-29 15:49 UTC

Db::fetchColumnGroup() A

↳ Parent: Db

Complexity

Conditions	1
Paths	1

Size

Total Lines	7
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	5
CRAP Score	1

Importance

Changes

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

bluzphp / framework

Push — develop ( ec7ed8...af61f1 )

Db::fetchColumnGroup() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like