Skip to content

DBAL

Jump to bottom
Vincent QUATREVIEUX edited this page Nov 17, 2022 · 1 revision

DBAL and connections usage

This page shows usage of low level database access layer.

Overview

See Declare your connections for connection options and declaration. Prime internally use Doctrine DBAL for handle database connections. All connections implement ConnectionInterface. To handle long live scripts (ex: queued message handler), an auto-reconnect mechanism is implemented, in case of connection lost.

Main components of connections are :

ConnectionInterface API

ConnectionInterface provides some utilities, query creations and execution methods.

Type conversion

Type conversions are performed by PlatformTypesInterface, which can be accessed by calling $connection->plaform()->types().

$connection = $prime->connection('DB');

// Perform conversion of a raw database value to PHP value
$connection->fromDatabase(',foo,bar,', 'array'); // ['foo', 'bar']
$connection->platform()->types()->fromDatabase(',foo,bar,', 'array'); // Same as above 


// Perform a conversion of a PHP value to database value
// If the type is not provided, it would be resolved 
$connection->toDatabase(new DateTime('+1 day')); // "2022-11-16 12:11:32"
$connection->toDatabase(['foo' => 'bar'], 'json'); // {"foo":"bar"}
$connection->platform()->types()->toDatabase(['foo' => 'bar'], 'json'); // Same as above

// Resolve native type used by database
$connection->platform()->types()->native('json'); // Will return SqlStringType, unless database supports natively a JSON type
$connection->platform()->types()->native('integer'); // Will return SqlIntegerType because it's supported natively

// Resolve type (can be native or facade type) from a PHP value
$connection->platform()->types()->resolve(42); // SqlIntegerType
$connection->platform()->types()->resolve('foo'); // SqlStringType
$connection->platform()->types()->resolve(new stdClass()); // ObjectType (facade type)

Query creation and execution

Queries creation is performed by QueryFactoryInterface, available by calling ConnectionInterface::factory(). ConnectionInterface also provide some shortcut methods.

use Bdf\Prime\Query\Custom\KeyValue\KeyValueQuery;

$connection = $prime->connection('DB');

// Use builder() to create the default query builder
// On MySQL connection, an instance of `Bdf\Prime\Query\Query` will be returned
// Query returned by this method should be a general purpose one, allowing performing read and write operations
$connection->builder()
    ->from('my_table')
    ->where('first_name', 'John')
    ->orWhere('last_name', 'Doe')
    ->all()
;

// Same as above using `ConnectionInterface::from()` shortcut
$connection->from('my_table')
    ->where('first_name', 'John')
    ->orWhere('last_name', 'Doe')
    ->all()
;

// Custom or optimised can be created using make
$connection->make(KeyValueQuery::class)
    ->from('my_table')
    ->where('first_name', 'John')
    ->all()
;

// Execute a raw SQL query
// Use `ResultSetInterface` API for parsing result
$connection->select('SELECT pseudo FROM users WHERE pseudo LIKE ?', ['Jo%'])->asColumn()->all();

// Manually execute a query object
foreach ($connection->execute($connection->from('my_table')->where('login', ':like', '%jo%'))->asClass(UserStruct::class) as $struct) {
    // ...
}

Schema manager

You can access to schema manager utility by calling ConnectionInterface::schema(). This utility object always implements DatabaseManagerInterface, and can implement other optional interfaces. To ensure that a given functionality is supported by the current platform, use instanceof operator before calling requested method.

List of interfaces :

  • DatabaseManagerInterface : Base type. Handle database and table management (check existence, list, create and remove).
  • DatabaseStructureManagerInterface : Handle loading, adding and diff computing of table.
  • TableManagerInterface : Extension of interface bellow. Allows table creation and modification using a table builder object.
  • QueryManagerInterface : Allow buffering of schema modification queries. Useful for simulate schema upgrade.
  • SchemaManagerInterface : Extends all previous interfaces, and allows handling of doctrine schema.
use Bdf\Prime\Connection\ConnectionInterface;
use Bdf\Prime\Schema\Manager\TableManagerInterface;
use Bdf\Prime\Schema\Builder\TypesHelperTableBuilder;
use Bdf\Prime\Schema\Manager\QueryManagerInterface;

/** @var ConnectionInterface $connection */
$connection = $prime->connection('DB');

$schema = $connection->schema();

// Declare table, if supported by platform
if (!$schema->has('person') && $schema instanceof TableManagerInterface) {
    $schema->table('person', function (TypesHelperTableBuilder $builder) {
        $builder
            ->integer('id')->primary()->autoincrement()
            ->string('first_name')
            ->string('last_name')
        ;
    });
}

// Flush buffered queries
if ($schema instanceof QueryManagerInterface && $schema->isBuffered()) {
    $schema->flush();
}

Transactions

When a connection implements TransactionManagerInterface, you can use transaction system. Nested transactions can be used, if supported by the database platform.

use Bdf\Prime\Connection\ConnectionInterface;
use Bdf\Prime\Connection\TransactionManagerInterface;

/** @var ConnectionInterface $connection */
$connection = $prime->connection('DB');

if ($connection instanceof TransactionManagerInterface) {
    $connection->beginTransaction(); // Start the transaction
    
    // Perform some write operation 
    if (doSomeWrites($connection)) {
        $connection->commit(); // Commit writes on success
    } else {
        $connection->rollBack(); // Cancel transaction
    }
}

Master/Slave connection

The master/slave connection contains two connections :

  • A master connection which executes all write operations.
  • A slave connection which executes read operations.

The class MasterSlaveConnection will be used, instead of SimpleConnection.

Note: If a transaction is active, all read operations will be executed by the master.

To force usage of master connection on the next read operation, you need to call method MasterSlaveConnection::force(). You can access to slave connection by calling MasterSlaveConnection::getReadConnection() or SubConnectionManagerInterface::getConnection('read').

This connection is automatically used when the option read is set on connection parameter or DSN. This option configure the slave connection, by overriding master parameters ones. Slave connection should use same driver and platform as master.

use Bdf\Prime\ConnectionManager;

$connections = new ConnectionManager();

// Declare using array syntax
$connections->declareConnection('DB', [
    // Configure master options
    'dbname'   => 'mydb',
    'user'     => 'master_user',
    'password' => 'secret',
    'host'     => 'master.db.example.com',
    'adapter'  => 'mysql',
    
    // Configure slave options
    'read' => [
        'user'     => 'slave_user',
        'password' => 'other_secret',
        'host'     => 'slave.db.example.com',
        // unconfigured options (like dbname) are inherited from master
    ]
]);

// Same declaration with DSN syntax
$connections->declareConnection('DB', 'mysql://master_user:secret@master.db.example.com/mydb?read[user]=slave_user&read[password]=other_secret&read[host]=slave.db.example.com');

$db = $connections->getConnection('DB'); // MasterSlaveConnection instance

$db->from('person')
    ->values([
        'first_name' => 'John',
        'last_name' => 'Doe',
    ])
    ->insert() // Insert on master
;

// Read from slave
// This example may return nothing because of synchronisation time between master and slave 
$db->from('person')
    ->where('first_name', 'John')
    ->where('last_name', 'Doe')
    ->first()
;

// Read from master : here the query will return a result regardless of synchronisation state
$db->force()->from('person')
    ->where('first_name', 'John')
    ->where('last_name', 'Doe')
    ->first()
;

Sharding

Sharding connection allows to split data into multiple databases by using a distribution key. This key is usually an integer field on the table, and a modulo operation is applied to select the corresponding shard. Shard selection is performed on all queries. The distribution key is present on where clause of select query, or values of update or insert query, the corresponding shard will be selected, and query will be executed on this shard connection. If the distribution key is not present, query will be executed on all shards, and result will be aggregated (ex: if you execute a select query with 5 shards and limit of 10, 50 results may be returned by the query).

The sharding connection is an instance of ShardingConnection. This class adds methods :

  • useShard to change the active shard. Call with null to unselect the shard, and query execution will be performed on all shards.
  • pickShard to change the active shard by using a distribution key value instead of shard id. Call with null to unselect the shard
  • getCurrentShardId, isUsingShard to check shard in use.
  • getShardConnection, getConnection to get a shard connection instance.

Sharding connection declaration works like Master/Slave but using shards option instead of read. This option is an associative array, with shard ID as key and shard connection options as value. The option distributionKey is also required, which is the database field to use for perform shard selection. The option shardChoser can also be defined to choose the instance of Bdf\Prime\Sharding\ShardChoserInterface to use for select shard. By default, ModuloChoser is used.

use Bdf\Prime\ConnectionManager;

$connections = new ConnectionManager();

// Declare using array syntax
$connections->declareConnection('DB', [
    // Configure master options
    'dbname'   => 'mydb',
    'user'     => 'user',
    'password' => 'secret',
    'host'     => 'db.example.com',
    'adapter'  => 'mysql',
    
    'distributionKey' => 'id',
    
    // Configure shards
    // unconfigured options (like dbname) are inherited from master
    'shards' => [
        'john' => ['host' => 'john.db.example.com'],
        'alan' => ['host' => 'alan.db.example.com'],
        'bob'  => ['host' => 'bob.db.example.com'],
    ]
]);

// Same declaration with DSN syntax
$connections->declareConnection('DB', 'mysql://user:secret@db.example.com/mydb?distributionKey=id&shards[john][host]=john.db.example.com&shards[alan][host]=alan.db.example.com&shards[bob][host]=bob.db.example.com');

$db = $connections->getConnection('DB'); // ShardingConnection instance

$db->from('person')
    ->values([
        'id' => 5,
        'first_name' => 'John',
        'last_name' => 'Doe',
    ])
    ->insert() // Insert on "bob", because 5 mod 3 = 2, so the shard at index 2 is used
;

$db->from('person')
    ->values([
        'id' => 42,
        'first_name' => 'Hans',
        'last_name' => 'Schmidt',
    ])
    ->insert() // Insert on "john", because 42 mod 3 = 0, so the shard at index 0 is used
;

// Distribution key not set : search on all shards
$db->from('person')->where('first_name', ':like', '%n%')->all();

// Distribution key is set : search only on "john"
$db->from('person')->where('id', 42)->first();

Clone this wiki locally