/ManaPHP/Db.php
PHP | 665 lines | 276 code | 80 blank | 309 comment | 35 complexity | 1f1df13b2575b3f452ccbd7fba2d1922 MD5 | raw file
- <?php
- namespace ManaPHP {
- use ManaPHP\Db\ConditionParser;
- use ManaPHP\Db\Exception;
- class Db extends Component implements DbInterface
- {
- /**
- * Descriptor used to connect to a database
- *
- * @var array
- */
- protected $_descriptor;
- /**
- * Type of database system driver is used for
- *
- * @var string
- */
- protected $_type;
- /**
- * Active SQL Statement
- *
- * @var string
- */
- protected $_sql;
- /**
- * Active SQL bound parameter variables
- *
- * @var array
- */
- protected $_bind;
- /**
- * Current transaction level
- *
- * @var int
- */
- protected $_transactionLevel = 0;
- /**
- * @var \PDO
- */
- protected $_pdo;
- /**
- * Last affected rows
- *
- * @var int
- */
- protected $_affectedRows;
- /**
- * \ManaPHP\Db\Adapter constructor
- *
- * @param array $descriptor
- */
- public function __construct($descriptor)
- {
- if (!isset($descriptor['options'])) {
- $descriptor['options'] = [];
- }
- $descriptor['options'][\PDO::ATTR_ERRMODE] = \PDO::ERRMODE_EXCEPTION;
- $this->_descriptor = $descriptor;
- $this->_connect();
- }
- /**
- * This method is automatically called in ManaPHP\Db\Adapter\Pdo constructor.
- * Call it when you need to restore a database connection
- *
- *<code>
- * //Make a connection
- * $connection = new \ManaPHP\Db\Adapter\Pdo\Mysql(array(
- * 'host' => '192.168.0.11',
- * 'username' => 'sigma',
- * 'password' => 'secret',
- * 'dbname' => 'blog',
- * ));
- *
- * //Reconnect
- * $connection->connect();
- * </code>
- *
- * @return boolean
- */
- protected function _connect()
- {
- $descriptor = $this->_descriptor;
- $username = isset($descriptor['username']) ? $descriptor['username'] : null;
- $password = isset($descriptor['password']) ? $descriptor['password'] : null;
- $options = $descriptor['options'];
- unset($descriptor['username'], $descriptor['password'], $descriptor['options']);
- if (isset($descriptor['dsn'])) {
- $dsn = $descriptor['dsn'];
- } else {
- $dsn_parts = [];
- foreach ($descriptor as $k => $v) {
- $dsn_parts[] = $k . '=' . $v;
- }
- $dsn = implode(';', $dsn_parts);
- }
- $this->_pdo = new \PDO($this->_type . ':' . $dsn, $username, $password, $options);
- }
- /**
- * Executes a prepared statement binding. This function uses integer indexes starting from zero
- *
- *<code>
- * $statement = $db->prepare('SELECT * FROM robots WHERE name = :name');
- * $result = $connection->executePrepared($statement, array('name' => 'mana'));
- *</code>
- *
- * @param \PDOStatement $statement
- * @param array $bind
- *
- * @return \PDOStatement
- * @throws \ManaPHP\Db\Exception
- */
- protected function _executePrepared($statement, $bind)
- {
- foreach ($bind as $parameter => $value) {
- if (is_string($value)) {
- $type = \PDO::PARAM_STR;
- } elseif (is_int($value)) {
- $type = \PDO::PARAM_INT;
- } elseif (is_bool($value)) {
- $type = \PDO::PARAM_BOOL;
- } elseif ($value === null) {
- $type = \PDO::PARAM_NULL;
- } else {
- throw new Exception("The type of parameter of '$parameter' is not support: " . gettype($value));
- }
- if (is_int($parameter)) {
- $statement->bindValue($parameter + 1, $value, $type);
- } else {
- if ($parameter[0] === ':') {
- throw new Exception("Bind does not require started with ':' for parameter: " . $parameter);
- }
- $statement->bindValue(':' . $parameter, $value, $type);
- }
- }
- $statement->execute();
- return $statement;
- }
- /**
- * Sends SQL statements to the database server returning the success state.
- * Use this method only when the SQL statement sent to the server is returning rows
- *
- *<code>
- * //Querying data
- * $resultset = $connection->query("SELECT * FROM robots WHERE type='mechanical'");
- * $resultset = $connection->query("SELECT * FROM robots WHERE type=?", array("mechanical"));
- *</code>
- *
- * @param string $sql
- * @param array $bind
- * @param int $fetchMode
- *
- * @return \PdoStatement
- * @throws \ManaPHP\Db\Exception
- */
- public function query($sql, $bind = [], $fetchMode = \PDO::FETCH_ASSOC)
- {
- $this->_sql = $sql;
- $this->_bind = $bind;
- if ($this->fireEvent('db:beforeQuery') === false) {
- return false;
- }
- try {
- if (count($bind) !== 0) {
- $statement = $this->_pdo->prepare($sql);
- $statement = $this->_executePrepared($statement, $bind);
- } else {
- $statement = $this->_pdo->query($sql);
- }
- $statement->setFetchMode($fetchMode);
- } catch (\PDOException $e) {
- throw new Exception($e->getMessage());
- }
- $this->fireEvent('db:afterQuery');
- return $statement;
- }
- /**
- * Sends SQL statements to the database server returning the success state.
- * Use this method only when the SQL statement sent to the server does n't return any rows
- *
- *<code>
- * //Inserting data
- * $success = $connection->execute("INSERT INTO robots VALUES (1, 'Boy')");
- * $success = $connection->execute("INSERT INTO robots VALUES (?, ?)", array(1, 'Boy'));
- *</code>
- *
- * @param string $sql
- * @param array $bind
- *
- * @return int
- * @throws \ManaPHP\Db\Exception
- */
- public function execute($sql, $bind = [])
- {
- $this->_sql = $sql;
- $this->_bind = $bind;
- $this->_affectedRows = 0;
- $this->fireEvent('db:beforeQuery');
- try {
- if (count($bind) !== 0) {
- $statement = $this->_pdo->prepare($sql);
- $newStatement = $this->_executePrepared($statement, $bind);
- $this->_affectedRows = $newStatement->rowCount();
- } else {
- $this->_affectedRows = $this->_pdo->exec($sql);
- }
- } catch (\PDOException $e) {
- throw new Exception($e->getMessage());
- }
- if (is_int($this->_affectedRows)) {
- $this->fireEvent('db:afterQuery');
- }
- return $this->_affectedRows;
- }
- /**
- * Escapes a column/table/schema name
- *
- * <code>
- * echo $connection->escapeIdentifier('my_table'); // `my_table`
- * echo $connection->escapeIdentifier('companies.name']); // `companies`.`name`
- * <code>
- *
- * @param string|array $identifier
- *
- * @return string
- */
- public function escapeIdentifier($identifier)
- {
- $identifiers = explode('.', $identifier);
- $list = [];
- foreach ($identifiers as $identifier) {
- if ($identifier[0] === '`') {
- $list[] = $identifier;
- } else {
- $list[] = "`$identifier`";
- }
- }
- return implode('.', $list);
- }
- /**
- * Returns the number of affected rows by the last INSERT/UPDATE/DELETE reported by the database system
- *
- * @return int
- */
- public function affectedRows()
- {
- return $this->_affectedRows;
- }
- /**
- * Returns the first row in a SQL query result
- *
- *<code>
- * //Getting first robot
- * $robot = $connection->fetchOne("SELECT * FROM robots");
- * print_r($robot);
- *
- * //Getting first robot with associative indexes only
- * $robot = $connection->fetchOne("SELECT * FROM robots", \ManaPHP\Db::FETCH_ASSOC);
- * print_r($robot);
- *</code>
- *
- * @param string $sql
- * @param array $bind
- * @param int $fetchMode
- *
- * @throws \ManaPHP\Db\Exception
- * @return array|false
- */
- public function fetchOne($sql, $bind = [], $fetchMode = \PDO::FETCH_ASSOC)
- {
- $result = $this->query($sql, $bind, $fetchMode);
- return $result->fetch();
- }
- /**
- * Dumps the complete result of a query into an array
- *
- *<code>
- * //Getting all robots with associative indexes only
- * $robots = $connection->fetchAll("SELECT * FROM robots", \ManaPHP\Db::FETCH_ASSOC);
- * foreach ($robots as $robot) {
- * print_r($robot);
- * }
- *
- * //Getting all robots that contains word "robot" withing the name
- * $robots = $connection->fetchAll("SELECT * FROM robots WHERE name LIKE :name",
- * ManaPHP\Db::FETCH_ASSOC,
- * array('name' => '%robot%')
- * );
- * foreach($robots as $robot){
- * print_r($robot);
- * }
- *</code>
- *
- * @param string $sql
- * @param array $bind
- * @param int $fetchMode
- *
- * @throws \ManaPHP\Db\Exception
- * @return array
- */
- public function fetchAll($sql, $bind = [], $fetchMode = \PDO::FETCH_ASSOC)
- {
- $result = $this->query($sql, $bind, $fetchMode);
- return $result->fetchAll();
- }
- /**
- * Inserts data into a table using custom SQL syntax
- *
- * <code>
- * //Inserting a new robot
- * $success = $connection->insert(
- * "robots",
- * array("Boy", 1952),
- * array("name", "year")
- * );
- *
- * //Next SQL sentence is sent to the database system
- * INSERT INTO `robots` (`name`, `year`) VALUES ("boy", 1952);
- * </code>
- *
- * @param string $table
- * @param array $columnValues
- *
- * @return boolean
- * @throws \ManaPHP\Db\Exception
- */
- public function insert($table, $columnValues)
- {
- if (count($columnValues) === 0) {
- throw new Exception('Unable to insert into ' . $table . ' without data');
- }
- $escapedTable = $this->escapeIdentifier($table);
- if (array_key_exists(0, $columnValues)) {
- $insertedValues = rtrim(str_repeat('?,', count($columnValues)), ',');
- $sql = /** @lang Text */
- "INSERT INTO $escapedTable VALUES ($insertedValues)";
- } else {
- $columns = array_keys($columnValues);
- $insertedValues = ':' . implode(',:', $columns);
- $insertedColumns = '`' . implode('`,`', $columns) . '`';
- $sql = /** @lang Text */
- "INSERT INTO $escapedTable ($insertedColumns) VALUES ($insertedValues)";
- }
- return $this->execute($sql, $columnValues) === 1;
- }
- /**
- * Updates data on a table using custom SQL syntax
- *
- * <code>
- * //Updating existing robot
- * $success = $connection->update(
- * "robots",
- * array("name"),
- * array("New Boy"),
- * "id = 101"
- * );
- *
- * //Next SQL sentence is sent to the database system
- * UPDATE `robots` SET `name` = "boy" WHERE id = 101
- * </code>
- *
- * @param string $table
- * @param array $columnValues
- * @param string|array $conditions
- * @param array $bind
- *
- * @return int|false
- * @throws \ManaPHP\Db\Exception
- */
- public function update($table, $columnValues, $conditions, $bind = [])
- {
- $escapedTable = "`$table`";
- if (count($columnValues) === 0) {
- throw new Exception('Unable to update ' . $table . ' without data');
- }
- $where = (new ConditionParser())->parse($conditions, $conditionBind);
- $bind = $bind ? array_merge($conditionBind, $bind) : $conditionBind;
- $setColumns = [];
- foreach ($columnValues as $k => $v) {
- $setColumns[] = "`$k`=:$k";
- $bind[$k] = $v;
- }
- $updateColumns = implode(',', $setColumns);
- $updateSql = /** @lang Text */
- "UPDATE $escapedTable SET $updateColumns WHERE $where";
- return $this->execute($updateSql, $bind);
- }
- /**
- * Deletes data from a table using custom SQL syntax
- *
- * <code>
- * //Deleting existing robot
- * $success = $connection->delete(
- * "robots",
- * "id = 101"
- * );
- *
- * //Next SQL sentence is generated
- * DELETE FROM `robots` WHERE `id` = 101
- * </code>
- *
- * @param string $table
- * @param string|array $conditions
- * @param array $bind
- *
- * @return boolean
- * @throws \ManaPHP\Db\Exception
- */
- public function delete($table, $conditions, $bind = [])
- {
- $where = (new ConditionParser())->parse($conditions, $conditionBind);
- $sql = /**@lang Text */
- "DELETE FROM `$table` WHERE " . $where;
- if ($bind === null) {
- $bind = $conditionBind;
- } else {
- $bind = array_merge($conditionBind, $bind);
- }
- return $this->execute($sql, $bind);
- }
- /**
- * Appends a LIMIT clause to $sqlQuery argument
- *
- * <code>
- * echo $connection->limit("SELECT * FROM robots", 5);
- * </code>
- *
- * @param string $sql
- * @param int $number
- * @param int $offset
- *
- * @return string
- */
- public function limit($sql, $number, $offset = null)
- {
- return $sql . ' LIMIT ' . $number . ($offset === null ? '' : (' OFFSET ' . $offset));
- }
- /**
- * Active SQL statement in the object
- *
- * @return string
- */
- public function getSQL()
- {
- return $this->_sql;
- }
- /**
- * @param mixed $value
- * @param int $preservedStrLength
- *
- * @return int|string
- */
- protected function _parseBindValue($value, $preservedStrLength)
- {
- if (is_string($value)) {
- if ($preservedStrLength > 0 && strlen($value) >= $preservedStrLength) {
- return $this->_pdo->quote(substr($value, 0, $preservedStrLength) . '...');
- } else {
- return $this->_pdo->quote($value);
- }
- } elseif (is_int($value)) {
- return $value;
- } elseif ($value === null) {
- return 'NULL';
- } elseif (is_bool($value)) {
- return (int)$value;
- } else {
- return $value;
- }
- }
- /**
- * Active SQL statement in the object with replace the bind with value
- *
- * @param int $preservedStrLength
- *
- * @return string
- * @throws \ManaPHP\Db\Exception
- */
- public function getEmulatedSQL($preservedStrLength = -1)
- {
- if ($this->_bind === null || count($this->_bind) === 0) {
- return $this->_sql;
- }
- $bind = $this->_bind;
- if (isset($bind[0])) {
- $pos = 0;
- return preg_replace_callback('/(\?)/',
- function () use ($bind, &$pos, $preservedStrLength) {
- return $this->_parseBindValue($bind[$pos++], $preservedStrLength);
- }, $this->_sql);
- } else {
- $replaces = [];
- foreach ($bind as $key => $value) {
- $replaces[':' . $key] = $this->_parseBindValue($value, $preservedStrLength);
- }
- return strtr($this->_sql, $replaces);
- }
- }
- /**
- * Active SQL statement in the object
- *
- * @return array
- */
- public function getBind()
- {
- return $this->_bind;
- }
- /**
- * Starts a transaction in the connection
- *
- * @return boolean
- * @throws \ManaPHP\Db\Exception
- */
- public function begin()
- {
- if ($this->_transactionLevel !== 0) {
- throw new Exception('There is in a active transaction already.');
- }
- $this->fireEvent('db:beginTransaction');
- $this->_transactionLevel++;
- return $this->_pdo->beginTransaction();
- }
- /**
- * Checks whether the connection is under a transaction
- *
- *<code>
- * $connection->begin();
- * var_dump($connection->isUnderTransaction()); //true
- *</code>
- *
- * @return bool
- */
- public function isUnderTransaction()
- {
- return $this->_pdo->inTransaction();
- }
- /**
- * Rollbacks the active transaction in the connection
- *
- * @return boolean
- * @throws \ManaPHP\Db\Exception
- */
- public function rollback()
- {
- if ($this->_transactionLevel === 0) {
- throw new Exception('There is no active transaction');
- }
- $this->fireEvent('db:rollbackTransaction');
- $this->_transactionLevel--;
- return $this->_pdo->rollBack();
- }
- /**
- * Commits the active transaction in the connection
- *
- * @return boolean
- * @throws \ManaPHP\Db\Exception
- */
- public function commit()
- {
- if ($this->_transactionLevel === 0) {
- throw new Exception('There is no active transaction');
- }
- $this->fireEvent('db:commitTransaction');
- $this->_transactionLevel--;
- return $this->_pdo->commit();
- }
- /**
- * Returns insert id for the auto_increment column inserted in the last SQL statement
- *
- * @return int
- */
- public function lastInsertId()
- {
- return (int)$this->_pdo->lastInsertId();
- }
- /**
- * Return internal PDO handler
- *
- * @return \PDO
- */
- public function getInternalHandler()
- {
- return $this->_pdo;
- }
- }
- }