If you’ve ever had cause to contemplate or execute the rewriting of an application to move from MySQL to Postgres or Oracle to MySQL or any other variety of database migration, the importance of database abstraction is obvious to you. For many common database operations, the SQL syntax is the same, the basic operations are the same, and the only difference is the client API you use to access the database. This article will explain why you should consider using MDB2 as your abstraction layer, and show you how to do it. It is the second part of a four-part article that explains the virtues of PEAR.
Why Database Abstraction
In PHP this may take the form of mysql_connect() and mysql_query() or pg_connect() and pg_query(). You issue some UPDATE and INSERT statements, and you retrieve and loop over the results of SELECT statements, and occasionally DELETE records. Very little of the code is terribly specific to the database, and a simple find-and-replace could switch over the bulk of your code, but this would be an error-prone operation. It also ignores issues such as type conversion, database feature set differences and handling of sequences (auto_increments).
This is where database abstraction comes into play. Rather than using the MySQL or Postgres API directly, you'll call methods or functions of your abstraction layer and simply change a few configuration parameters to switch your application over to the new database.
This is not without a price. One thing to consider, when you decide to use a database abstraction layer, is that you will generally take a performance hit. The extra layer of transformations that happen to your data and the layer of indirection between your calling code and the raw database layer will generally be slower than the native client library (which is generally written in C and compiled as a shared library). In most cases, while a concern, the impact is negligible. Most database bottlenecks are not a result of the database API layer, but of the actual database query volume, locking and server utilization.
What is MDB2?
MDB2 is the latest and greatest abstraction layer provided by the PEAR (PHP Extension and Application Repository) system. MDB2 abstracts many different aspects of your database access including connection parameters, type conversion, error codes, sequence/ auto_increment management and prepared statements (and emulation) and presents a unified interface to a variety of open-source and proprietary databases.
All of the abstraction for MDB2 is written in PHP and not as a compiled PHP extension, which means it can be easily installed. As mentioned before, this will cause a small performance penalty.
The interface of MDB2 is presented as a set of object oriented classes. Eighteen classes are included in the latest release, but normal operations will deal with three or four: a Connection, ResultSet, Row and possible Error class. Each class is well documented, with end-user API documentation produced from in-line code comments.