inferno-code/opendbal

OpenDBAL is an alternative for Doctrine DBAL.

0.5.2 2020-12-15 18:29 UTC

Requires

• php: ^7.0
• ext-curl: *
• ext-json: *
• psr/log: ^1.1

MIT

• inferno-code

• dbal
• pdo

README

OpenDBAL is an alternative for Doctrine DBAL. It is more lightweight and guarantees backward compatibility of API in a future releases.

Features:

• Logging queries and errors
• Can rise an exceptions or work is silent mode
• Stable PDO-like API with more comfortable methods

Future plans:

• Unit tests
• API documentation
• Add support CSV/TSV data streaming for exporting/importing datasets.

Introduction

DBAL - database abstraction & access layer offers a lightweight and thin runtime layer around a PDO-like API and others.

The fact that the OpenDBAL abstracts the concrete PDO API away through the use of interfaces that closely resemble the existing PDO API makes it possible to implement custom drivers that may use existing native or self-made APIs.

Data Retrieval And Manipulation

OpenDBAL follows the PDO API very closely. If you have worked with PDO before you will get to know OpenDBAL very quickly.

First start with connection:

$url = "pgsql://username:password@host:port/dbname";

$conn = new \OpenDBAL\PDO\Connection($url);

Write an SQL query and pass it to the query() method of your connection:

$statement = $conn->query("SELECT * FROM clients");

The query method executes the SQL and returns a database statement object. A database statement object can be iterated to retrieve all the rows that matched the query until there are no more rows:

while (($row = $statement->fetchAssoc()) !== null)
	process($row);

Execute the query and fetch all results into an array:

$clients = $conn->fetchAllAssoc("SELECT * FROM clients");
/*
	[
		[ 'email' => '[e-mail]', 'name' => 'Alan Deker', 'age' => 35 ],
		[ 'email' => '[e-mail]', 'name' => 'Elizabeth Brown', 'age' => 27 ],
	]
*/

You can fetch results using meny different methods to get comfortable data structures. As an arrays:

$clients = $conn->fetchAllArray("SELECT * FROM clients");
/*
	[
		[ '[e-mail]', 'Alan Deker', 35 ],
		[ '[e-mail]', 'Elizabeth Brown', 27 ],
	]
*/

Or as an objects:

$clients = $conn->fetchAllObject("SELECT * FROM clients");
/*
	[
		(object) [ 'email' => '[e-mail]', 'name' => 'Alan Deker', 'age' => 35 ],
		(object) [ 'email' => '[e-mail]', 'name' => 'Elizabeth Brown', 'age' => 27 ],
	]
*/

Or get value of single column:

$email = $conn->fetchColumn(
	"SELECT email FROM clients WHERE id = :clientId LIMIT 1",
	[ 'clientId' => 123 ]
);
/*
	'[e-mail]'
*/

Fetch the data as an associative array where the key represents the first or specified column and the value is an associative array of the rest of the columns and their values:

$clients = $conn->fetchAllDictAssoc("SELECT email, name FROM clients");
/*
	[
		'[e-mail]' => 'Alan Deker',
		'[e-mail]' => 'Elizabeth Brown'
	]
*/

$clients = $conn->fetchAllDictAssoc("SELECT email, name, age FROM clients");
/*
	[
		'[e-mail]' => [ 'name' => 'Alan Deker', 'age' => 35 ],
		'[e-mail]' => [ 'name' => 'Elizabeth Brown', 'age' => 27 ]
	]
*/

OpenDBAL called this as dictionaries. Fetch same dictionary but get rows as an arrays:

$clients = $conn->fetchAllDictArray("SELECT email, name, age FROM clients");
/*
	[
		'[e-mail]' => [ 'Alan Deker', 35 ],
		'[e-mail]' => [ 'Elizabeth Brown', 27 ]
	]
*/

Or as an objects:

$clients = $conn->fetchAllDictObject("SELECT email, name, age FROM clients");
/*
	[
		'[e-mail]' => (object) [ 'name' => 'Alan Deker', 'age' => 35 ],
		'[e-mail]' => (object) [ 'name' => 'Elizabeth Brown', 'age' => 27 ]
	]
*/