DeployPHP Series, Part 4: Using the PEAR::MDB2 Database Abstraction Layer with Oracle
by Tobias Schlitt

An introduction to the use of the MDB2 database abstraction layer with Oracle, with simple as well as advanced examples.

Downloads for this article: Sample code and examples Oracle Database 10g PEAR::MDB2

Review complete DeployPHP index

PEAR (PHP Extension and Application Repository) is a huge collection of high-quality PHP components. The repository contains PHP classes for almost any imaginable purpose, from database authentication, to networking, to images, to XML.

In this installment of the DeployPHP Series, I'll describe how to use MDB2, a high-level database abstraction layer that can be found in PEAR. (Disclaimer: PEAR::MDB2 is currently rated "beta" quality; it has a well-tested and stable code basis, but its API may change over time. For this reason, it cannot be rated as "stable" in the PEAR repository.)

The use of a database abstraction layer makes sense in many situations when developing PHP applications—especially when you deal with different database systems or if you plan to migrate an application from one database to another. Especially sensible is the use of MDB2 when you want to manipulate database structures from your application. Its interface is much more convenient than having to create all the necessary SQL statements by hand, and the simple mechanism shown in one of our examples here allows you to simply create tables on the fly. The schema manager in MDB2 allows you to get your database automatically altered when your application is run the first time in an updated version, so you don't have to worry about that during installation.

PEAR Background

Before starting with MDB2, you have to take a little closer look at PEAR, since it not only provides the specified packages but a unique installation infrastructure to manage them, as well. Therefore, you first have to learn the installation procedure for the packages required for this article.

The PEAR Installer has been shipped with every PHP distribution since version 4.3.0. (You should have it, unless you deactivated it.) To test if PEAR is installed properly, simply type "pear" in your shell; then press Return. If you see a list of possible PEAR commands, you can skip the next step.

Maybe you have an older version of PHP or you have (by accident or intentionally) deactivated PEAR during installation. In those cases, you can easily bootstrap PEAR from the Web. Depending on your operating system, you can use the following command chain (this should work on most UNIX or UNIX-like systems):

lynx -source http://go-pear.org | php -q

Next, you have to install the necessary packages. The PEAR Installer provides the command pear install for that, which has to be followed simply by a package name. The installer will then download the package from the PEAR Web site and install it. In our case, you have to type "pear install MDB2". Usually this approach will fail at first because the preferred_state option of your virgin PEAR installation is set to stable. The preferred_state option means that the PEAR Installer will not install packages that are rated with a lower stability than the one you defined in preferred_state (to not break your applications). Since MDB2 is currently in beta phase, you have to force the installation using pear install MDB2-beta.

The MDB2 package has been split into several subpackages, of which you will have to install some, too. So you have to do pear install MDB2_Schema-beta to get support for database schemas, which you will use for the later examples. Each of the database drivers reside in its own subpackage. Therefore, you have to install the Oracle driver separately using pear install MDB2_Driver_oci8-alpha. You can learn more details through the individual package Web sites; for example, for the Oracle driver, pear.php.net/package/MDB2_Driver_oci.

After the successful installation of these packages, we can go on, but before we do, let me introduce the concept of a database abstraction layer in general.

Database Abstraction

Call abstraction. This kind of abstraction is highly recommended when your developers have to deal with different databases. Every PHP database extension provides a completely different API; for example, the Oracle 8 (OCI) extension uses oci_execute(). Call abstraction provides a common API for each of the database extensions.

Data type abstraction. A higher level of abstraction is the data type abstraction. Every database has its own data types and convention for them (for example, date and time fields). Abstracting those data types allows you to access and use the different kinds of data in a unique way.

SQL abstraction. Whether you put SQL abstraction at a higher level or on the same level as the data type abstraction, it's a very nice feature when you migrate from one database to another. Every database supports some proprietary extensions to standard SQL. Although most of these extensions aim at similar problems with pure SQL, they use a different syntax. In other cases, a feature is implemented in one database, but not available in another. Both cases are covered by SQL abstraction, which offers a unique API to those features and emulates the functionality, if it's not available for the specific database.

About MDB2

MDB2 is not the only abstraction in PEAR; PEAR::DB also offers a not-so-high-level abstraction (call abstraction and some parts of SQL abstraction). In contrast, MDB2 offers full-datatype and more SQL abstraction. Furthermore, it offers some other nice features that we will get to shortly.

All examples will show the use of MDB2 in conjunction with Oracle; however, because MDB2 is an abstraction layer, its use with other relational database systems is very similar.

Getting Started

Deployment .Maybe you are even familiar with PDO (the new abstraction layer in PHP 5.1). The API of MDB2 tries to get as near as possible to PDO and MDB2 tries to extend PDO to the PHP userland.

To initialized database connections, MDB2 uses a mechanism known as a DSN (data source name). A DSN can either be represented by a string or an associative array. Basic elements you need to have are "hostspec," which is usually the database host to connect to, and "phptype," the type of database (e.g., "oci8" for Oracle). In the case of Oracle, hostspec is a local connection identifier. If you leave this out with MDB2, it will try to gather the Oracle System ID (SID) through the specific environment variable ORACLE_SID. Furthermore, a user name and, in most cases, a password are required. Whereas the array representation uses the element names as keys, the string representation uses a URI-like format:

oci://username:password@hostspec

As you can see, there are plenty of comments in the code, but let's review it all the same.

The first thing in this file is the definition of a DSN array, as explained previously. (If you want to try out these scripts, this is the part you will have to customize according to your environment.) After the declaration of the DSN, the connection is established using MDB2::connect(). What is interesting here is the use of PEAR error handling. MDB2::connect() either returns a database driver object or an object of type PEAR_Error; the latter can be checked using PEAR::isError(). If an error occurred, the script is stopped and the error message displayed on the screen. (We'll see this kind of error handling in later examples as well.)

Besides the connect() method, MDB2 also offers a method called singleton(), which does not recreate the database connection when you use it, but rather reuses an existing one if you are already connected. This allows you to get a DB connection everywhere in your application without passing it by parameters or directly accessing globals (the so-called singleton pattern).

If you are having problems getting in contact with your Oracle database, you can use $db->getDebugInfo(); to get more detailed information on what is going wrong. The call to getDebugInfo() will help you in every case you experience strange behavior and receive errors.

As you now have a database connection established, let's take a look at some easy queries and basic MDB2 methods. All examples are available in the sample code download.

Example 1: Simple Queries (example_01_simplecalls.php)

In later parts of this example, several simple SQL statements are created and submitted to the database using $db->query(). Again, note the PEAR error handling in action, since query() returns a PEAR_Error object on failure. In other cases, query() will return either the constant MDB2_OK or a result set object (when selecting data). After querying the database with the select, you'll see two different methods (one is commented) to fetch results from our result set returned by the query() method (lines 43ff and 52ff). The fetchRow() method is used here to retrieve the next result record. The commented code uses the default fetch mode where the fields of the returned array are numbered. The not-commented one uses a different fetch mode (MDB2_FETCHMODE_ASSOC), which returns an array indexed by the names of the columns. There are more options for fetching available, such as MDB2_FETCHMODE_OBJECT, which returns objects instead of arrays, that have the columns as their properties.

After the "insert" and "delete" queries you'll notice more advanced methods for fetching data. For example, the queryAll() method queries the database using your SQL statement and returns all retrieved results in an array, indexed by numbers.

The basic use of MDB2 should now have become clear and we can move on to some more advanced features. Note that I have intentionally left out some features here, such as MDB2's data type abstraction—and with it the possibility to automatically casting datatypes in PHP (e.g., for dates)—in the interest of focusing on more important features.

Example 2: Advanced Queries (example_02_extendedcalls.php)

Initially, this example script looks just like the previous one. But at line 26, we load some more advanced functionality into our database driver. This is one of the best features in MDB2: modularity. If you don't need high-level abstractions and only want to use a simple and unique API for all kinds of databases, MDB2 just loads this functionality (which is the default). Any other code can be loaded on demand. In our case, we load the "Extended" module, which enables some more methods for us.

The first feature you'll see is part of the SQL abstraction. Every database implements a feature to limit the amount of records returned by a "select" query, but nearly the same amount of different SQL syntaxes exist for this action. The limitQuery() call builds the specific query needed for the database used, in our case Oracle. The first parameter for limitQuery is the query to submit; next you could define the data types returned (we don't use that feature, so we pass null here). After that, the two limitation parameters follow. The first one indicates the number of records to fetch, and the second one gives the offset number to start the fetch from. An alternative method for selecting only a certain number of rows is to first call $db->setLimit() and then run a query as seen before, using the query() or queryAll() methods.

The other extended feature we see in this example is the getAssoc() method, which acts like queryAll() but reindexes the results returned by their first column. In other words, the array of result records returned is no more indexed by a continuous integer value (0, 1, 2, . . .) but by the first column queried in the SELECT statement (should be some kind of ID). Note that if the column used does not have a unique index on it, later values will overwrite earlier ones.

Example 3: Managing Databases (example_03_manager.php)

The situation shown in this third example is a typical example of the need to create your database tables on demand. You won't need any kind of database installation script for your applications, because the application does this work on the fly.

First, try querying the database using a simple select on a database table. If MDB2 returns a MDB2_ERROR_NOSUCHTABLE as the error code, you know that the table we you to use does not exist. This means that you have to create the table, which is done using a "manager."

The manager is a MDB2 module similar to the "Extended" one we used in the second example, with the difference being that it does not enhance the facilities of the database driver we currently use, but rather creates a new object, the manager (line 22).

Using this manager object, we are now able to create and manipulate database tables on the fly, using a simple table schema represented by the $table array in the code (line 28ff). Each key of the array represents a column of our table, which is defined by another array as its value. These column arrays define at least a type (which is one of the MDB2's abstract types I described previously) and optionally some more attributes, as you can see in the code. The format of this array is well documented at the createTable() method of the manager class. The simple call to $manager->createTable() receives the name of the table to create and the definition array.

Furthermore, you want to define the primary key of the new table. This is possible through the manager as well, although it does not support "primary keys" themselves. Instead of using real primary key constraints, the manager until now only supports unique indexes. These indexes act more or less exactly like primary keys, if their field value is explicitly set to "not null". Of course, this solution is among the most common of database features, but, in a way, this is the cost of abstraction.

So, let's define a unique index as our primary key. Indexes are defined like tables—using arrays. At least an index definition array must have a 'fields' key , which is assigned to an array of field names being part of the index. In our case, we have only one field, the ID. The call to createIndex() is very simple, as well. The method receives the name of the table where the index should be created, the name for the index, and the definition array.

The last action inside the large IF statement is to retry the SELECT statement to receive a valid result object for the error check after the large IF statement. Finally, check the result again (maybe it's another error as MDB2_ERROR_NOSUCHTABLE or after creating the table the "select" still returned an error).

You should take a look at your database structure after running this script for the first time: Open SQL*Plus and run something like "describe categories" to look at how the new table has been created.

Example 4: Schema (example_04_schema.xml)

So let's take a look at the necessary PHP code and what we can do with a schema. First of all, a schema can define a complete database or just single tables. It defines the structure of elements and data in a manner similar to that of the manager arrays in the second example.

First, initialize the module needed for working with schemas. This module is the first one, which was separated from MDB2 into its own subpackage. Therefore, the syntax is not the same as we have used until now. The class MDB2_Schema (which must be explicitly required, as you can see) provides a factory method, which creates a schema manager for our database connection.

Using the newly created schema manager, you can use updateDatabase() (line 27) and let your database get updated according to the schema file. As you can see in the code, the updateDatabase() method is called with two filename parameters here. The second parameter is meant to be a backup file, where the most recent database schema is stored. It does not matter if this file exists when the script is called first. (Even better, the backup file should not be there when the script is called the first time, meaning the database does not exist yet.) When no backup file is found, the manager assumes that the table does not exist yet and that it has to be created. After the table is successfully created, the manager saves the actual schema file to the backup location. The reason for this is simple: Every time the program reaches this code, the two schema files are compared. If the backup does not match the new one, the manager computes the difference between both and issues the necessary ALTER statements. Your database will be updated on the fly, when a new application version is deployed.

Now check out what the example did. Simply use describe maintainers in SQL*Plus and watch the magic.

Conclusion

You should now have good working knowledge of MDB2. Happy PHP coding!

Send us your comments