<?php
/**
* SQL database management to be used by several providers at the same time.
*
* @author Josantonius <hello@josantonius.com>
* @copyright 2017 - 2018 (c) Josantonius - PHP-Database
* @license https://opensource.org/licenses/MIT - The MIT License (MIT)
* @link https://github.com/Josantonius/PHP-Database
* @since 1.0.0
*/
namespace Josantonius\Database;
use Josantonius\Database\Exception\DBException;
/**
* Database handler.
*/
class Database
{
/**
* Database identifier.
*
* @since 1.1.6
*
* @var mixed
*/
public static $id;
/**
* Database provider.
*
* @var object
*/
public $provider;
/**
* Last insert id of the last query.
*
* @var int
*/
public $lastInsertId;
/**
* Get number of rows affected of the last query.
*
* @var int
*/
public $rowCount;
/**
* Database settings.
*
* @var array
*/
public $settings;
/**
* Configurations for queries.
*
* @since 1.2.1
*
* @var null|array
*/
private $items = [
'on' => null, // array → db reference table (foreing)
'type' => null, // string → type of query
'data' => null, // array → columns and values
'table' => null, // string → database table name
'order' => null, // mixed → order clause
'charset' => null, // string → database charset
'limit' => null, // int → limit clause
'where' => null, // mixed → where clause
'engine' => null, // string → database engine
'result' => null, // string → result datatype: array|object|rows
'columns' => null, // mixed → columns
'foreing' => null, // array → foreing
'actions' => null, // array → action when delete/update (foreing)
'reference' => null, // array → references for foreing
'statements' => null, // array → prepared statements
];
/**
* Database connection.
*
* @var array
*/
private static $conn;
/**
* Query.
*
* @var string
*/
private $query;
/**
* Query response.
*
* @var object
*/
private $response;
/**
* Database provider constructor.
*
* @param string $provider → name of provider class
* @param string $host → database host
* @param string $user → database user
* @param string $name → database name
* @param string $password → database password
* @param array $settings → database options
* @param array $settings['port'] → database port
* @param array $settings['charset'] → database charset
*
* @throws DBException → if the provider class specified does not exist
* @throws DBException → if could not connect to provider
*/
protected function __construct(
$provider,
$host,
$user,
$name,
$password,
$settings
) {
$provider = "Josantonius\\Database\\Provider\\$provider";
if (! class_exists($provider)) {
throw new DBException("The provider doesn't exist: $provider");
}
$this->provider = new $provider;
$this->provider->connect($host, $user, $name, $password, $settings);
if (! $this->provider->isConnected()) {
throw new DBException(
"Could not connect to provider: $provider. " .
$this->provider->getError()
);
}
$this->settings = $settings;
}
/**
* Close connection to database.
*/
public function __destruct()
{
$this->provider->kill();
}
/**
* Get connection.
*
* Create a new if it doesn't exist or another provider is used.
*
* @param string $id → identifying name for the database
* @param string $provider → name of provider class
* @param string $host → database host
* @param string $user → database user
* @param string $name → database name
* @param string $password → database password
* @param array $settings → database options
* @param string $settings['port'] → database port
* @param string $settings['charset'] → database charset
*
* @return object → object with the connection
*/
public static function getConnection(
$id,
$provider = null,
$host = null,
$user = null,
$name = null,
$password = null,
$settings = null
) {
if (isset(self::$conn[self::$id = $id])) {
return self::$conn[$id];
}
return self::$conn[$id] = new self(
$provider,
$host,
$user,
$name,
$password,
$settings
);
}
/**
* Process query and prepare it for the provider.
*
* @param string $query → query
* @param array $statements → null by default or array for statements
*
* @param string $result → 'obj' → result as object
* → 'array_num' → result as numeric array
* → 'array_assoc' → result as associative array
* → 'rows' → affected rows number
* → 'id' → last insert id
*
* @throws DBException → invalid query type
*
* @return mixed → result as object, array, int...
*/
public function query($query, $statements = null, $result = 'obj')
{
$this->items['type'] = trim(explode(' ', $query)[0]);
$this->query = $query;
$this->items['result'] = $result;
$this->items['statements'] = $statements;
$types = '|SELECT|INSERT|UPDATE|DELETE|CREATE|TRUNCATE|DROP|USE|';
if (! strpos($types, $this->items['type'])) {
throw new DBException(
'Unknown query type:' . $this->items['type']
);
}
$this->implement();
return $this->getResponse();
}
/**
* Create table statement.
*
* @param array $data → column name and configuration for data types
*
* @return object
*/
public function create($data)
{
$this->items['type'] = 'CREATE';
$this->items['data'] = $data;
return $this;
}
/**
* Set foreing key.
*
* @since 1.1.2
*
* @param string $id → column id
*
* @return object
*/
public function foreing($id)
{
$this->items['foreing'][] = $id;
return $this;
}
/**
* Set reference for foreing keys.
*
* @since 1.1.2
*
* @param array $table → table name
*
* @return object
*/
public function reference($table)
{
$this->items['reference'][] = $table;
return $this;
}
/**
* Set database table name.
*
* @since 1.1.2
*
* @param array $table → table name
*
* @return object
*/
public function on($table)
{
$this->items['on'][] = $table;
return $this;
}
/**
* Set actions when delete or update for foreing key.
*
* @since 1.1.2
*
* @param string $action → action when delete or update
*
* @return object
*/
public function actions($action)
{
$this->items['actions'][] = $action;
return $this;
}
/**
* Set engine.
*
* @since 1.1.2
*
* @param string $type → engine type
*
* @return object
*/
public function engine($type)
{
$this->items['engine'] = $type;
return $this;
}
/**
* Set charset.
*
* @since 1.1.2
*
* @return object
*/
public function charset($type)
{
$this->items['charset'] = $type;
return $this;
}
/**
* Select statement.
*
* @param mixed $columns → column/s name
*
* @return object
*/
public function select($columns = '*')
{
$this->items['type'] = 'SELECT';
$this->items['columns'] = $columns;
return $this;
}
/**
* Insert into statement.
*
* @param array $data → column name and value
* @param array $statements → null by default or array for statements
*
* @return object
*/
public function insert($data, $statements = null)
{
$this->items['type'] = 'INSERT';
$this->items['data'] = $data;
$this->items['statements'] = $statements;
return $this;
}
/**
* Update statement.
*
* @param array $data → column name and value
* @param array $statements → null by default or array for statements
*
* @return object
*/
public function update($data, $statements = null)
{
$this->items['type'] = 'UPDATE';
$this->items['data'] = $data;
$this->items['statements'] = $statements;
return $this;
}
/**
* Replace a row in a table if it exists or insert a new row if not exist.
*
* @param array $data → column name and value
* @param array $statements → null by default or array for statements
*
* @return object
*/
public function replace($data, $statements = null)
{
$this->items['type'] = 'REPLACE';
$this->items['data'] = $data;
$this->items['statements'] = $statements;
return $this;
}
/**
* Delete statement.
*
* @return object
*/
public function delete()
{
$this->items['type'] = 'DELETE';
return $this;
}
/**
* Truncate table statement.
*
* @return object
*/
public function truncate()
{
$this->items['type'] = 'TRUNCATE';
return $this;
}
/**
* Drop table statement.
*
* @return object
*/
public function drop()
{
$this->items['type'] = 'DROP';
return $this;
}
/**
* Set database table name.
*
* @param string $table → table name
*
* @return object
*/
public function in($table)
{
$this->items['table'] = $table;
return $this;
}
/**
* Set database table name.
*
* @param string $table → table name
*
* @return object
*/
public function table($table)
{
$this->items['table'] = $table;
return $this;
}
/**
* Set database table name.
*
* @param string $table → table name
*
* @return object
*/
public function from($table)
{
$this->items['table'] = $table;
return $this;
}
/**
* Where clauses.
*
* @param mixed $clauses → column name and value
* @param array $statements → null by default or array for statements
*
* @return object
*/
public function where($clauses, $statements = null)
{
$this->items['where'] = $clauses;
if (is_array($this->items['statements'])) {
$this->items['statements'] = array_merge(
$this->items['statements'],
$statements
);
} else {
$this->items['statements'] = $statements;
}
return $this;
}
/**
* Set SELECT order.
*
* @param string $type → query sort parameters
*
* @return object
*/
public function order($type)
{
$this->items['order'] = $type;
return $this;
}
/**
* Set SELECT limit.
*
* @param int $number → query limiting parameters
*
* @return object
*/
public function limit($number)
{
$this->items['limit'] = $number;
return $this;
}
/**
* Execute query.
*
* @param string $result → 'obj' → result as object
* → 'array_num' → result as numeric array
* → 'array_assoc' → result as associative array
* → 'rows' → affected rows number
* → 'id' → last insert id
*
* @return int → number of lines updated or 0 if not updated
*/
public function execute($result = 'obj')
{
$this->items['result'] = $result;
$type = strtolower($this->items['type']);
switch ($this->items['type']) {
case 'SELECT':
$params = [
$this->items['columns'],
$this->items['table'],
$this->items['where'],
$this->items['order'],
$this->items['limit'],
$this->items['statements'],
$this->items['result'],
];
break;
case 'INSERT':
$params = [
$this->items['table'],
$this->items['data'],
$this->items['statements'],
];
break;
case 'UPDATE':
$params = [
$this->items['table'],
$this->items['data'],
$this->items['statements'],
$this->items['where'],
];
break;
case 'REPLACE':
$params = [
$this->items['table'],
$this->items['data'],
$this->items['statements'],
];
break;
case 'DELETE':
$params = [
$this->items['table'],
$this->items['statements'],
$this->items['where'],
];
break;
case 'CREATE':
$params = [
$this->items['table'],
$this->items['data'],
$this->items['foreing'],
$this->items['reference'],
$this->items['on'],
$this->items['actions'],
$this->items['engine'],
$this->items['charset'],
];
break;
case 'TRUNCATE':
$params = [
$this->items['table'],
];
break;
case 'DROP':
$params = [
$this->items['table'],
];
break;
}
$provider = [$this->provider, $type];
$this->response = call_user_func_array($provider, $params);
return $this->getResponse();
}
/**
* Query handler.
*
* @return object → returns query to be executed by provider class
*/
private function implement()
{
if (is_array($this->items['statements'])) {
return $this->implementPrepareStatements();
}
return $this->implementQuery();
}
/**
* Run query with prepared statements.
*/
private function implementPrepareStatements()
{
$this->response = $this->provider->statements(
$this->query,
$this->items['statements']
);
}
/**
* Run query without prepared statements.
*/
private function implementQuery()
{
$this->response = $this->provider->query(
$this->query,
$this->items['type']
);
}
/**
* Get response after executing the query.
*
* @throws DBException → error executing query
*
* @return mixed → result as object, array, int...
*/
private function getResponse()
{
$this->lastInsertId = $this->provider->lastInsertId();
$this->rowCount = $this->provider->rowCount($this->response);
if (is_null($this->response)) {
$this->reset();
throw new DBException(
'Error executing the query ' . $this->provider->getError()
);
}
$response = $this->fetchResponse();
$this->reset();
return $response;
}
/**
* Process query as object or numeric or associative array.
*
* @return mixed|bool → results
*/
private function fetchResponse()
{
$type = $this->items['type'];
if (strpos('|INSERT|UPDATE|DELETE|REPLACE|', $type)) {
if ($this->items['result'] === 'id') {
return $this->lastInsertId;
}
return $this->rowCount;
} elseif ($type === 'SELECT') {
if ($this->items['result'] !== 'rows') {
return $this->provider->fetchResponse(
$this->response,
$this->items['result']
);
}
if (is_object($this->response)) {
return $this->provider->rowCount($this->response);
}
}
return true;
}
/**
* Reset query parameters.
*/
private function reset()
{
$this->items['columns'] = null;
$this->items['table'] = null;
$this->items['where'] = null;
$this->items['order'] = null;
$this->items['limit'] = null;
$this->items['statements'] = null;
$this->items['foreing'] = null;
$this->items['reference'] = null;
$this->items['on'] = null;
$this->items['actions'] = null;
$this->items['engine'] = null;
$this->items['charset'] = null;
}
}