src/Connection.php
<?php
declare(strict_types=1);
namespace Atk4\Dsql;
use Doctrine\DBAL\Connection as DbalConnection;
use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Platforms\OraclePlatform;
use Doctrine\DBAL\Platforms\PostgreSQL94Platform;
use Doctrine\DBAL\Platforms\SQLServer2012Platform;
use Doctrine\DBAL\Result as DbalResult;
/**
* Class for establishing and maintaining connection with your database.
*/
abstract class Connection
{
use \Atk4\Core\DiContainerTrait;
/** @var string Query classname */
protected $query_class = Query::class;
/** @var string Expression classname */
protected $expression_class = Expression::class;
/** @var DbalConnection */
protected $connection;
/**
* Stores the driverSchema => connectionClass array for resolving.
*
* @var array
*/
protected static $connectionClassRegistry = [
'sqlite' => Sqlite\Connection::class,
'mysql' => Mysql\Connection::class,
'pgsql' => Postgresql\Connection::class,
'oci' => Oracle\Connection::class,
'sqlsrv' => Mssql\Connection::class,
];
/**
* Specifying $properties to constructors will override default
* property values of this class.
*
* @param array $properties
*/
public function __construct($properties = [])
{
if (!is_array($properties)) {
throw (new Exception('Invalid properties for "new Connection()". Did you mean to call Connection::connect()?'))
->addMoreInfo('properties', $properties);
}
$this->setDefaults($properties);
}
public function __destruct()
{
// needed for DBAL connection to be released immeditelly
if ($this->connection !== null) {
$this->connection->close();
}
}
/**
* Normalize DSN connection string.
*
* Returns normalized DSN as array ['dsn', 'user', 'pass', 'driverSchema', 'rest'].
*
* @param array|string $dsn DSN string
* @param string $user Optional username, this takes precedence over dsn string
* @param string $pass Optional password, this takes precedence over dsn string
*
* @return array
*/
public static function normalizeDsn($dsn, $user = null, $pass = null)
{
// Try to dissect DSN into parts
$parts = is_array($dsn) ? $dsn : parse_url($dsn);
// If parts are usable, convert DSN format
if ($parts !== false && isset($parts['host'], $parts['path'])) {
// DSN is using URL-like format, so we need to convert it
$dsn = $parts['scheme'] . ':host=' . $parts['host']
. (isset($parts['port']) ? ';port=' . $parts['port'] : '')
. ';dbname=' . substr($parts['path'], 1);
$user = $user ?? ($parts['user'] ?? null);
$pass = $pass ?? ($parts['pass'] ?? null);
}
// If it's still array, then simply use it
if (is_array($dsn)) {
return $dsn;
}
// If it's string, then find driver
if (is_string($dsn)) {
if (strpos($dsn, ':') === false) {
throw (new Exception('Your DSN format is invalid. Must be in "driverSchema:host;options" format'))
->addMoreInfo('dsn', $dsn);
}
[$driverSchema, $rest] = explode(':', $dsn, 2);
$driverSchema = strtolower($driverSchema);
} else {
// currently impossible to be like this, but we don't want ugly exceptions here
$driverSchema = null;
$rest = null;
}
return ['dsn' => $dsn, 'user' => $user ?: null, 'pass' => $pass ?: null, 'driverSchema' => $driverSchema, 'rest' => $rest];
}
/**
* Connect to database and return connection class.
*
* @param string|\PDO|DbalConnection $dsn
* @param string|null $user
* @param string|null $password
* @param array $args
*
* @return Connection
*/
public static function connect($dsn, $user = null, $password = null, $args = [])
{
// If it's already PDO or DbalConnection object, then we simply use it
if ($dsn instanceof \PDO) {
$connectionClass = self::resolveConnectionClass($dsn->getAttribute(\PDO::ATTR_DRIVER_NAME));
$connectionArg = $connectionClass::connectDbalConnection(['pdo' => $dsn]);
} elseif ($dsn instanceof DbalConnection) {
/** @var \PDO */
$pdo = self::isComposerDbal2x()
? $dsn->getWrappedConnection()
: $dsn->getWrappedConnection()->getWrappedConnection(); // @phpstan-ignore-line
$connectionClass = self::resolveConnectionClass($pdo->getAttribute(\PDO::ATTR_DRIVER_NAME));
$connectionArg = $dsn;
} else {
$dsn = static::normalizeDsn($dsn, $user, $password);
$connectionClass = self::resolveConnectionClass($dsn['driverSchema']);
$connectionArg = $connectionClass::connectDbalConnection($dsn);
}
return new $connectionClass(array_merge([
'connection' => $connectionArg,
], $args));
}
/**
* Adds connection class to the registry for resolving in Connection::resolve method.
*
* Can be used as:
*
* Connection::registerConnection(MySQL\Connection::class, 'mysql'), or
* MySQL\Connection::registerConnectionClass()
*
* CustomDriver\Connection must be descendant of Connection class.
*
* @param string $connectionClass
* @param string $driverSchema
*/
public static function registerConnectionClass($connectionClass = null, $driverSchema = null): void
{
if ($connectionClass === null) {
$connectionClass = static::class;
}
if ($driverSchema === null) {
/** @var static $c */
$c = (new \ReflectionClass($connectionClass))->newInstanceWithoutConstructor();
$driverSchema = $c->getDatabasePlatform()->getName();
}
self::$connectionClassRegistry[$driverSchema] = $connectionClass;
}
/**
* Resolves the connection class to use based on driver type.
*/
public static function resolveConnectionClass(string $driverSchema): string
{
if (!isset(self::$connectionClassRegistry[$driverSchema])) {
throw (new Exception('Driver schema is not registered'))
->addMoreInfo('driver_schema', $driverSchema);
}
return self::$connectionClassRegistry[$driverSchema];
}
final public static function isComposerDbal2x(): bool
{
return !class_exists(DbalResult::class);
}
/**
* Establishes connection based on a $dsn.
*
* @return DbalConnection
*/
protected static function connectDbalConnection(array $dsn)
{
if (isset($dsn['pdo'])) {
$pdo = $dsn['pdo'];
} else {
$pdo = new \PDO($dsn['dsn'], $dsn['user'], $dsn['pass']);
}
// Doctrine DBAL 3.x does not support to create DBAL Connection with already
// instanced PDO, so create it without PDO first, see:
// https://github.com/doctrine/dbal/blob/v2.10.1/lib/Doctrine/DBAL/DriverManager.php#L179
// https://github.com/doctrine/dbal/blob/3.0.0/src/DriverManager.php#L142
// TODO probably drop support later
if (self::isComposerDbal2x()) {
$dbalConnection = DriverManager::getConnection([
'pdo' => $pdo,
]);
} else {
$pdoConnection = (new \ReflectionClass(\Doctrine\DBAL\Driver\PDO\Connection::class))
->newInstanceWithoutConstructor();
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
\Closure::bind(function () use ($pdoConnection, $pdo): void {
$pdoConnection->connection = $pdo;
}, null, \Doctrine\DBAL\Driver\PDO\Connection::class)();
$dbalConnection = DriverManager::getConnection([
'driver' => 'pdo_' . $pdo->getAttribute(\PDO::ATTR_DRIVER_NAME),
]);
\Closure::bind(function () use ($dbalConnection, $pdoConnection): void {
$dbalConnection->_conn = $pdoConnection;
}, null, \Doctrine\DBAL\Connection::class)();
}
// DBAL 3.x removed some old platforms, to support instanceof reliably,
// make sure that DBAL 2.x platform is always supported in DBAL 3.x, see:
// https://github.com/doctrine/dbal/pull/3912
// TODO drop once DBAL 2.x support is dropped
if (
in_array(get_class($dbalConnection->getDatabasePlatform()), [ // @phpstan-ignore-line
'Doctrine\DBAL\Platforms\SQLServerPlatform',
'Doctrine\DBAL\Platforms\SQLServer2005Platform',
'Doctrine\DBAL\Platforms\SQLServer2008Platform',
], true) && !($dbalConnection->getDatabasePlatform() instanceof SQLServer2012Platform)
) {
\Closure::bind(function () use ($dbalConnection) {
$dbalConnection->platform = new SQLServer2012Platform();
}, null, DbalConnection::class)();
} elseif (
in_array(get_class($dbalConnection->getDatabasePlatform()), [ // @phpstan-ignore-line
'Doctrine\DBAL\Platforms\PostgreSqlPlatform',
'Doctrine\DBAL\Platforms\PostgreSQL91Platform',
'Doctrine\DBAL\Platforms\PostgreSQL92Platform',
], true) && !($dbalConnection->getDatabasePlatform() instanceof PostgreSQL94Platform)
) {
\Closure::bind(function () use ($dbalConnection) {
$dbalConnection->platform = new PostgreSQL94Platform();
}, null, DbalConnection::class)();
}
// SQL Server DBAL platform has buggy identifier escaping, fix until fixed officially, see:
// https://github.com/doctrine/dbal/pull/4360
if ($dbalConnection->getDatabasePlatform() instanceof SQLServer2012Platform) {
\Closure::bind(function () use ($dbalConnection) {
$dbalConnection->platform = new class() extends SQLServer2012Platform {
protected function getCreateColumnCommentSQL($tableName, $columnName, $comment)
{
if (strpos($tableName, '.') !== false) {
[$schemaName, $tableName] = explode('.', $tableName, 2);
} else {
$schemaName = $this->getDefaultSchemaName();
}
return $this->getAddExtendedPropertySQL(
'MS_Description',
(string) $comment,
'SCHEMA',
$schemaName,
'TABLE',
$tableName,
'COLUMN',
$columnName
);
}
protected function getAlterColumnCommentSQL($tableName, $columnName, $comment)
{
if (strpos($tableName, '.') !== false) {
[$schemaName, $tableName] = explode('.', $tableName, 2);
} else {
$schemaName = $this->getDefaultSchemaName();
}
return $this->getUpdateExtendedPropertySQL(
'MS_Description',
(string) $comment,
'SCHEMA',
$schemaName,
'TABLE',
$tableName,
'COLUMN',
$columnName
);
}
protected function getDropColumnCommentSQL($tableName, $columnName)
{
if (strpos($tableName, '.') !== false) {
[$schemaName, $tableName] = explode('.', $tableName, 2);
} else {
$schemaName = $this->getDefaultSchemaName();
}
return $this->getDropExtendedPropertySQL(
'MS_Description',
'SCHEMA',
$schemaName,
'TABLE',
$tableName,
'COLUMN',
$columnName
);
}
private function quoteSingleIdentifierAsStringLiteral(string $levelName): string
{
return $this->quoteStringLiteral(preg_replace('~^\[|\]$~s', '', $levelName));
}
public function getAddExtendedPropertySQL(
$name,
$value = null,
$level0Type = null,
$level0Name = null,
$level1Type = null,
$level1Name = null,
$level2Type = null,
$level2Name = null
) {
return 'EXEC sp_addextendedproperty'
. ' N' . $this->quoteStringLiteral($name) . ', N' . $this->quoteStringLiteral((string) $value)
. ', N' . $this->quoteStringLiteral((string) $level0Type)
. ', ' . $this->quoteSingleIdentifierAsStringLiteral((string) $level0Name)
. ', N' . $this->quoteStringLiteral((string) $level1Type)
. ', ' . $this->quoteSingleIdentifierAsStringLiteral((string) $level1Name)
. (
$level2Type !== null || $level2Name !== null
? ', N' . $this->quoteStringLiteral((string) $level2Type)
. ', ' . $this->quoteSingleIdentifierAsStringLiteral((string) $level2Name)
: ''
);
}
public function getDropExtendedPropertySQL(
$name,
$level0Type = null,
$level0Name = null,
$level1Type = null,
$level1Name = null,
$level2Type = null,
$level2Name = null
) {
return 'EXEC sp_dropextendedproperty'
. ' N' . $this->quoteStringLiteral($name)
. ', N' . $this->quoteStringLiteral((string) $level0Type)
. ', ' . $this->quoteSingleIdentifierAsStringLiteral((string) $level0Name)
. ', N' . $this->quoteStringLiteral((string) $level1Type)
. ', ' . $this->quoteSingleIdentifierAsStringLiteral((string) $level1Name)
. (
$level2Type !== null || $level2Name !== null
? ', N' . $this->quoteStringLiteral((string) $level2Type)
. ', ' . $this->quoteSingleIdentifierAsStringLiteral((string) $level2Name)
: ''
);
}
public function getUpdateExtendedPropertySQL(
$name,
$value = null,
$level0Type = null,
$level0Name = null,
$level1Type = null,
$level1Name = null,
$level2Type = null,
$level2Name = null
) {
return 'EXEC sp_updateextendedproperty'
. ' N' . $this->quoteStringLiteral($name) . ', N' . $this->quoteStringLiteral((string) $value)
. ', N' . $this->quoteStringLiteral((string) $level0Type)
. ', ' . $this->quoteSingleIdentifierAsStringLiteral((string) $level0Name)
. ', N' . $this->quoteStringLiteral((string) $level1Type)
. ', ' . $this->quoteSingleIdentifierAsStringLiteral((string) $level1Name)
. (
$level2Type !== null || $level2Name !== null
? ', N' . $this->quoteStringLiteral((string) $level2Type)
. ', ' . $this->quoteSingleIdentifierAsStringLiteral((string) $level2Name)
: ''
);
}
protected function getCommentOnTableSQL(string $tableName, ?string $comment): string
{
if (strpos($tableName, '.') !== false) {
[$schemaName, $tableName] = explode('.', $tableName, 2);
} else {
$schemaName = $this->getDefaultSchemaName();
}
return $this->getAddExtendedPropertySQL(
'MS_Description',
(string) $comment,
'SCHEMA',
$schemaName,
'TABLE',
$tableName
);
}
};
}, null, DbalConnection::class)();
}
// Oracle CLOB/BLOB has limited SQL support, see:
// https://stackoverflow.com/questions/12980038/ora-00932-inconsistent-datatypes-expected-got-clob#12980560
// fix this Oracle inconsistency by using VARCHAR/VARBINARY instead (but limited to 4000 bytes)
if ($dbalConnection->getDatabasePlatform() instanceof OraclePlatform) {
\Closure::bind(function () use ($dbalConnection) {
$dbalConnection->platform = new class() extends OraclePlatform {
private function forwardTypeDeclarationSQL(string $targetMethodName, array $column): string
{
$backtrace = debug_backtrace(\DEBUG_BACKTRACE_PROVIDE_OBJECT | \DEBUG_BACKTRACE_IGNORE_ARGS);
foreach ($backtrace as $frame) {
if ($this === ($frame['object'] ?? null)
&& $targetMethodName === ($frame['function'] ?? null)) {
throw new Exception('Long CLOB/TEXT (4000+ bytes) is not supported for Oracle');
}
}
return $this->{$targetMethodName}($column);
}
public function getClobTypeDeclarationSQL(array $column)
{
$column['length'] = $this->getVarcharMaxLength();
return $this->forwardTypeDeclarationSQL('getVarcharTypeDeclarationSQL', $column);
}
public function getBlobTypeDeclarationSQL(array $column)
{
$column['length'] = $this->getBinaryMaxLength();
return $this->forwardTypeDeclarationSQL('getBinaryTypeDeclarationSQL', $column);
}
};
}, null, DbalConnection::class)();
}
return $dbalConnection;
}
/**
* Returns new Query object with connection already set.
*
* @param string|array $properties
*/
public function dsql($properties = []): Query
{
$c = $this->query_class;
$q = new $c($properties);
$q->connection = $this;
return $q;
}
/**
* Returns Expression object with connection already set.
*
* @param string|array $properties
* @param array $arguments
*/
public function expr($properties = [], $arguments = null): Expression
{
$c = $this->expression_class;
$e = new $c($properties, $arguments);
$e->connection = $this;
return $e;
}
/**
* @return DbalConnection
*/
public function connection()
{
return $this->connection;
}
/**
* Execute Expression by using this connection.
*
* @return DbalResult|\PDOStatement PDOStatement iff for DBAL 2.x
*/
public function execute(Expression $expr): object
{
if ($this->connection === null) {
throw new Exception('Queries cannot be executed through this connection');
}
return $expr->execute($this->connection);
}
/**
* Atomic executes operations within one begin/end transaction, so if
* the code inside callback will fail, then all of the transaction
* will be also rolled back.
*
* @param mixed ...$args
*
* @return mixed
*/
public function atomic(\Closure $fx, ...$args)
{
$this->beginTransaction();
try {
$res = $fx(...$args);
$this->commit();
return $res;
} catch (\Exception $e) {
$this->rollBack();
throw $e;
}
}
/**
* Starts new transaction.
*
* Database driver supports statements for starting and committing
* transactions. Unfortunately most of them don't allow to nest
* transactions and commit gradually.
* With this method you have some implementation of nested transactions.
*
* When you call it for the first time it will begin transaction. If you
* call it more times, it will do nothing but will increase depth counter.
* You will need to call commit() for each execution of beginTransactions()
* and only the last commit will perform actual commit in database.
*
* So, if you have been working with the database and got un-handled
* exception in the middle of your code, everything will be rolled back.
*/
public function beginTransaction(): void
{
try {
$this->connection->beginTransaction();
} catch (\Doctrine\DBAL\ConnectionException $e) {
throw new Exception('Begin transaction failed', 0, $e);
}
}
/**
* Will return true if currently running inside a transaction.
* This is useful if you are logging anything into a database. If you are
* inside a transaction, don't log or it may be rolled back.
* Perhaps use a hook for this?
*/
public function inTransaction(): bool
{
return $this->connection->isTransactionActive();
}
/**
* Commits transaction.
*
* Each occurrence of beginTransaction() must be matched with commit().
* Only when same amount of commits are executed, the actual commit will be
* issued to the database.
*/
public function commit(): void
{
try {
$this->connection->commit();
} catch (\Doctrine\DBAL\ConnectionException $e) {
throw new Exception('Commit failed', 0, $e);
}
}
/**
* Rollbacks queries since beginTransaction and resets transaction depth.
*/
public function rollBack(): void
{
try {
$this->connection->rollBack();
} catch (\Doctrine\DBAL\ConnectionException $e) {
throw new Exception('Rollback failed', 0, $e);
}
}
/**
* Return last inserted ID value.
*
* Drivers like PostgreSQL need to receive sequence name to get ID because PDO doesn't support this method.
*/
public function lastInsertId(string $sequence = null): string
{
return $this->connection()->lastInsertId($sequence);
}
public function getDatabasePlatform(): AbstractPlatform
{
return $this->connection->getDatabasePlatform();
}
}