behance/nbd.php-dbal

This package is abandoned and no longer maintained. No replacement package was suggested.

Behance DB Access Layer

2.2.1 2018-07-17 14:59 UTC

Requires

• php: >=7.1
• symfony/event-dispatcher: ~2.6 || ~3.0

Requires (Dev)

• jimbojsb/pseudo: dev-feature/improve-prepared-statements
• phpunit/phpunit: ~6.0

proprietary

• Bryan Latten
• Luis Jimenez
• Matthew LeBrun

README

behance/nbd.php-dbal

There are million of database adapters out there. But very few tick all (our very specific) boxes

Goals

1. Very minimal dependencies, to be used in very diverse environments
2. Make every attempt to shield connection and management logic from implementer
3. Support master + many-slave replication patterns
4. Semi-intelligent connection choices:
   • Writes automatically choose master
   • Read queries randomly choose a single replica connection per request, unless...
   • Choosing a master connection at any point in the lifecycle will always use it going forward
   • Loosely follows Doctrine's tenets @see http://www.doctrine-project.org/api/dbal/2.0/class-Doctrine.DBAL.Connections.MasterSlaveConnection.html
   • [NEW] When connection retrievals include a table, master connection rules are segmented by table, with automatic fallback behavior when not specified.
5. Out-of-the-box convenience support for CRUD operations, accessors, and fallback to raw SQL (works with other SQL generators as well).
   • Automatic conversion to prepared statements for convenience parameters
6. Automatic retries for "mysql gone away" in long-running crons, workers, scripts
7. Provide deep introspection with events

Usage

use Behance\NBD\Dbal;

$config['master'] = [
    'username' => 'admin',
    'password' => 'password',
    'host'     => 'db',
    'port'     => 3306,
    'database' => 'dbal_test',
    'driver'   => 'Pdo_Mysql'
];

$config['replicas'] = [
    [
        'username' => 'admin',
        'password' => 'password',
        'host'     => 'replica',
        'port'     => 3306,
        'database' => 'dbal_test',
        'driver'   => 'Pdo_Mysql'
    ],
    //[
    //    ...add as many slaves as necessary
    //]
];

$db = Dbal\Factory::create( $config );

Testing

Unit testing:

1. composer install
2. ./vendor/bin/phpunit

Integration testing: leveraging Docker, using actual mysql container

1. docker-compose up -d
2. docker exec -it nbdphpdbal_web_1 /bin/bash
3. cd /app
4. ./vendor/bin/phpunit

Operations

Operation	Example	Result	Notes
insert	$adapter->insert( 'table', [ 'key' => 'value' ] );	last insert ID
insertIgnore	$adapter->insertIgnore( 'table', [ 'key' => 'value' ] );	last insert ID, false if not inserted
insertOnDuplicateUpdate	$adapter->insertOnDuplicateUpdate( 'table', [ 'key' => 'value' ], [ 'created_on' => new Sql( 'NOW()' ) ] );	last insert ID, otherwise, 2 if updated	*see WHERE usage
update	$adapter->update( 'table', [ 'key' => 'new_value' ] );	rows affected	*see WHERE usage, enforces a non-empty WHERE is required
delete	$adapter->delete( 'table', [ 'id' => 12345 ] );	rows affected	*see WHERE usage, enforces a non-empty WHERE is required
beginTransaction	$adapter->beginTransaction();	bool successful	Nested transactions are not supported
commit	$adapter->commit();	bool successful
rollBack	$adapter->rollBack();	bool successful
queryTable	$adapter->queryTable( ‘table’, "SELECT * FROM `table` WHERE id=? AND enabled=?", [ 12345, 0 ] );	PDOStatement	*PDOStatement is already executed
queryTableMaster	$adapter->queryTableMaster( ‘table’, "SELECT * FROM `table` WHERE id=:id AND enabled=:enabled, [ ':id' => 12345, ':enabled' => 0 ] );	PDOStatement	*PDOStatement is already executed, master connection used additional `table` queries
query	$adapter->query( "SELECT * FROM `table` WHERE id=? AND enabled=?", [ 12345, 0 ] );	PDOStatement	*PDOStatement is already executed
queryMaster	$adapter->queryMaster( "SELECT * FROM `table` WHERE id=:id AND enabled=:enabled, [ ':id' => 12345, ':enabled' => 0 ] );	PDOStatement	*PDOStatement is already executed
queryMaster	$adapter->queryMaster( "SELECT * FROM `table` WHERE id=:id AND enabled=:enabled, [ ':id' => 12345, ':enabled' => 0 ] );	PDOStatement	*PDOStatement is already executed, master connection is used for all future queries
quote	$adapter->queryMaster( "SELECT * FROM `table` WHERE id=:id AND enabled=:enabled, [ ':id' => 12345, ':enabled' => 0 ] );	string	Parameterized statements