Source of file Db.php
Size: 18,433 Bytes - Last Modified: 2017-11-08T13:54:24+00:00
/home/travis/build/bluzphp/framework/src/Db/Db.php
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671 | <?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' => 'john@smith.com') * @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); } } |