Pear::DB Primer

Often I have been a victim of badly designed code or by changes made to the initial specification of a project. Sometimes my personal pet project changes hosting providers and because of that, it needs to use a different database server, or a corporate project changes its direction and consequently its platform. In any case, I end up changing database servers.

Things like these happen very frequently in the IT world. People start out thinking that platform A is the best one and out of the blue, a business need or a strategic partnership necessitates a change to the project plan. Most of the source code will need to be reviewed to check for problems caused by the database switch.

That is, unless the project was designed to work with any database server that has a significant market share. This is the objective of the PEAR::DB library, which is a part of the PEAR project.

In the PHP community, there are several other good projects that aim to do pretty much the same thing as PEAR::DB, such as PHPLIB, Metabase, and ADODB. However, I believe PEAR::DB has the most modern interface to talk to database servers, as it is fully object oriented and provides the most general error-handling class that exists today. Besides that, several PHP Core developers work on PEAR to make it the best organized and cleanest distribution of reusable libraries for the PHP world.

Because of this heavy project backing, it will probably continue to be developed and improved. Stig Bakken, the originator of the project and also a PHP Core developer since the PHP 2.0 era, is even working on porting the PEAR::DB framework to C. This will boost the performance of the library and provide a standard way to interact with databases. This C version of PEAR is still in the very early stages of development, and doesn't contain any database-related routines. It currently only contains the main PEAR class and its error-handling class, PEAR_Error.

PEAR::DB certainly has problems and will continue to improve. Many critics of PEAR::DB, such as Manuel Lemos the author of Metabase, say PEAR::DB is too slow and doesn't provide a true abstraction to the interaction with database servers. I believe the most important part of a database abstraction library is its design -- I want to be able to use a high-quality library on my code; differences of 0.02% are not so important.

Connecting to a database server

In PEAR::DB you connect to a database server by passing an array of parameters that contain the connnection information such as the host name of the database server, the user name and password to use on the connection, and also which database to use.

The following code connects to MySQL running on host presidio.impleo.net using "anonymous" as the user name and "hellokitty" as the password. I specified the database name as "mydb".

<?php

include("DB.php");

$dsn = array(

    'phptype'  => "mysql",

    'hostspec' => "presidio.impleo.net",

    'database' => "mydb",

    'username' => "anonymous",

    'password' => "hellokitty"

);

$dbh = DB::connect($dsn);

?>

The phptype parameter tells PEAR::DB which driver to use for this connection. In this case, we are using MySQL. Several different database drivers are available to use, and on my last visit to PEAR's CVS repository, the following ones were available: MySQL, PostgreSQL, Oracle, MS SQL Server, Frontbase, Informix, Sybase, Interbase, mSQL, and ODBC. Like any other open-source project, some of these drivers are not as up-to-date as the MySQL or PostgreSQL drivers, but they are being mantained actively.

The DB::connect() line returns an object that can be used to run queries, fetch result sets, and such. Like all other PEAR software, it can return an object of type PEAR_Error to send back error messages and full debugging descriptions. This is a standard way for PEAR to handle error situations gracefully. In this case, one could use the following checks to see if an error happened when PEAR::DB tried to connect to the database:

<?php
if (PEAR::isError($dbh)) {
    echo "An error occurred while trying to connect to the database server.<br>\n";
    echo "Error message: " . $dbh->getMessage() . "<br>\n";
    echo "A more detailed error description: " . $dbh->getDebugInfo() . "<br>\n";
}
?>

The connect() method of the DB class contains code that according to the parameter phptype, includes the appropriate driver script and tries to run the driver's connect() method. This way, you can use the same piece of code (DB::connect()) and still connect to different database servers just by changing one parameter. This is a piece of the abstraction and code re-use that I was talking about.

So connect() tries to use the driver's connect() method to actually connect to the database, and if any error happens on this process, a PEAR_Error object is created and returned. This PEAR_Error instance will contain the normal error message and a detailed version of it for debugging purposes. This is how the connect() method deals with error situations, and all other methods do it pretty much in the same way. This is a piece of the standardization of error handling of PEAR libraries.