PHP Database Abstraction Layer – Chapter 1: Doctrine

Widely known for its ORM tool, Doctrine also has a DBAL project very interesting and robust, with native support for nine DBMS drivers (including PostgreSQL, Oracle, MySQL and IBM DB2), besides allowing the development of new drivers through its PDO-based API.

Doctrine 2.0.0BETA4 requires PHP 5.3 due to namespaces, which makes files very readable and organized. It was very pleasant to read that bunch of source codes who encapsulates PDO very well. The documentation is also very complete and almost error-free.

(Note: The database table used here was described in the introduction post)

Class Loader

The class loading system encapsulates the SPL autoloading:

require_once '/path/to/Doctrine/Common/ClassLoader.php';
$loader = new \Doctrine\Common\ClassLoader('Doctrine', '/path/to/Doctrine');
$loader->register();

This code snippet is enough for you just forget about including Doctrine files in your source code.

Connection

Obtaining a connection resource is as simple as class loading:

$params = array(
    'dbname'   => 'mydb',
    'user'     => 'me',
    'password' => 'secret',
    'host'     => 'localhost',
    'driver'   => 'pdo_mysql'
);
$conn = \Doctrine\DBAL\DriverManager::getConnection($params);

Data manipulation

There are specific methods for data manipulation actions:

Insert


$affectedRows = $conn->insert(
    'customer',
    array(
         'first_name' => 'George',
         'last_name'  => 'Secrieru'
    )
);

Update

There are two ways of executing this kind of operation:

// 1 - Executing a prepared statement
$affectedRows = $conn->executeUpdate(
    'UPDATE customer SET last_name = ? WHERE id = ?',
    array('S.', 1)
);
 
// 2 - Using key / value identifiers
$affectedRows = $conn->update(
    'customer',
    array('first_name' => 'G.'),
    array('id' => 1)
);

Delete


$affectedRows = $conn->delete(
    'customer',
    array('id' => 11)
);

There is another approach that can be used for other queries than DELETE. It generates a prepared statement for later execution:

// PDOStatement instance
$stmt = $conn->executeQuery(
    'DELETE FROM customer WHERE id = ?',
    array(12)
);
 
$res = $stmt->execute(); // boolean

Data retrieval

There are also methods for retrieving data from your model.

$sql = 'SELECT * FROM customer';
 
// Array with all results
$customers = $conn->fetchAll($sql);
 
/*
 
Array
(
    [0] => Array
    (
        [id] => 1
        [first_name] => G.
        [last_name] => S.
    )
 
    [1] => Array
    (
        [id] => 2
        [first_name] => J.
        [last_name] => D.
    )
)
 
*/
 
// Returning both associative and numeric indexes
$stmt = $conn->prepare('SELECT * from customer'); // \Doctrine\DBAL\Driver\Statement instance
$stmt->execute();
$customers = $stmt->fetchAll();
 
/*
 
Array
(
    [0] => Array
    (
        [id] => 1
        [0] => 1
        [first_name] => G.
        [1] => G.
        [last_name] => S.
        [2] => S.
    )
 
    [1] => Array
    (
        [id] => 2
        [0] => 2
        [first_name] => J.
        [1] => J.
        [last_name] => D.
        [2] => D.
    )
)
 
*/

If you wish, you can retrieve all fields from the first line…

$sql = 'SELECT * FROM customer WHERE id = ?';
 
// Numeric indexes
$firstRow = $conn->fetchArray($sql, array(1));
 
/*
 
Array
(
    [0] => 1
    [1] => G.
    [2] => S.
)
 
*/

…or just a single column:

$lastName = $conn->fetchColumn($sql, array(1), 2); // string

Quoting

By default there is no escaping in Doctrine. The documentation says that the ORM project does it, since it has a lot of meta data available. But when you use DBAL as standalone you have to care about it yourself.

// Quotes a value
$quoted = $conn->quote('foo');
 
// Quotes an identifier, like table name or column (e.g. if you are dealing with reserved words)
$quotedIdentifier = $conn->quoteIdentifier('order');

Transactions

Transactions rely on try-catch blocks for its manipulation.

$conn->beginTransaction();
try {
    $stmt = $conn->insert(
        'customer',
        array(
            'first_name' => 'George',
            'last_name'  => 'Secrieru'
        )
    );
 
    $conn->commit();
} catch (Exception $e) {
    $conn->rollback();
    throw $e;
}

The next approach is very interesting and encapsulates the previous one: it manages commit() and automatically rollback() in case of exception

$func = function($conn) {
    $stmt = $conn->insert(
        'customer',
        array(
            'first_name' => 'George',
            'last_name'  => 'Secrieru'
        )
    );
};
$conn->transactional($func);

Although not recommended by its docs, Doctrine suggests the possibility of nested transactions.

Schema Manager

I think I would not be able to explain this in a better way than documentation:

A Schema Manager instance helps you with the abstraction of the generation of SQL assets such as Tables, Sequences, Foreign Keys and Indexes.

$schema = $conn->getSchemaManager();
 
// Array with the name of the schema's databases
$databases = $schema->listDatabases();
 
/**
 * Associative array with column names as indexes. Each indexes is a
 * Doctrine\DBAL\Schema\Column object containing its meta data (e.g.
 * data type, length, default value).
 */
$columns = $schema->listTableColumns('customer');

Conclusion

It was very simple to work with Doctrine and it has proven to be a very useful tool for abstracting databases in PHP applications. This article intended to introduce the basic functionalities of this component, which provides a great mobility regarding DBMS changes. Combined with an Object-relational Mapping tool, you should be capable of doing any kind of transactions in your databases in a simple and reliable way.

3 Comments

  1. Greg

    Hi,
    Great post,
    I played a little with the Schema Manager but I didn’t manage to retrieve the Schema Manager of an owner that is not the connected user (Oracle).
    Do you know how to do that ?
    Thanks,
    Greg

Leave a Reply to Greg Cancel reply

Your email address will not be published. Required fields are marked *