Feature: #75454 - Added PHP library "Doctrine DBAL" for Database Connections within TYPO3
See forge#75454
Description
The PHP library Doctrine DBAL
has been added via composer dependency to work as a powerful database
abstraction layer with many features for database abstraction, schema introspection and
schema management within TYPO3.
A TYPO3-specific PHP class called TYPO3\
has been added as a
manager for database connections.
All connections configured below $GLOBALS
are
accessible using this manager, enabling the parallel usage of multiple database systems.
By using the database abstraction options and the QueryBuilder provided SQL statements being built will be properly quoted and compatible with different DBMS out of the box as far as possible.
Existing $GLOBALS
options have been removed and/or migrated to the
new Doctrine-compliant options.
Documentation for Doctrine DBAL can be found at http://www.doctrine-project.org/projects/dbal.html.
The Connection
class provides convenience methods for insert
, select
, update
,
delete
and truncate
statements. For select
, update
and delete
only simple
equality comparisons (WHERE "a
) are supported. For complex statements it's
required to use the Query
.
It is possible to swap tables out of the default database and use a specific setup (e.g. for logging or caching). This snippet of a LocalConfiguration could for exampe be used to swap the sys_log table out to another database or even another database server:
//LocalConfiguration.php
//[...]
'DB' => [
'Connections' => [
'Default' => [
'charset' => 'utf8',
'dbname' => 'default_dbname',
'driver' => 'mysqli',
'host' => 'default_host',
'password' => '***',
'port' => 3306,
'user' => 'default_user',
],
'Syslog' => [
'charset' => 'utf8',
'dbname' => 'syslog_dbname',
'driver' => 'mysqli',
'host' => 'syslog_host',
'password' => '***',
'port' => 3306,
'user' => 'syslog_user',
],
],
'TableMapping' => [
'sys_log' => 'Syslog'
]
],
//[...]
Impact
Currently the Database
class only uses Doctrine to establish the database
connection to MySQL, no advanced options are being used yet.
Connections always need to be requested with a table name so that the abstraction of table names to database connections stays intact.
The Connection
class can be used like this:
// Get a connection which can be used for multiple operations
/** @var \TYPO3\CMS\Core\Database\Connecction $conn */
$conn = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('aTable');
$affectedRows = $conn->insert(
'aTable',
$fields, // Associative array of column/value pairs, automatically quoted & escaped
);
// Get a QueryBuilder, which should only be used a single time
$query = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('aTable);
$query->select('*')
->from('aTable)
->where(
$query->expr()->eq('aField', $query->createNamedParameter($aValue)),
$query->expr()->lte(
'anotherField',
$query->createNamedParameter($anotherValue)
)
);
$rows = $query->execute()->fetchAll();
Extension authors are advised to use the Connection
and Connection
classes instead of using
the Doctrine DBAL directly in order to ensure a clear upgrade path when updates to the underlying
API need to be done.