Saturday, October 10, 2009

Bootstrapping Multiple Databases

Introduction

The current release of the Zend Framework (1.9.3) includes a handful of built-in, or standard, resource plugins that help developers more easily bootstrap their applications. One of those standard resource plugins is called Zend_Application_Resource_Db. According to the documentation, it "will initialize a Zend_Db adapter based on the options passed to it. By default, it also sets the adapter as the default adapter for use with Zend_Db_Table." Using this plugin as a part of ZF's application bootstrapping functionality, it is very easy for a developer to define a database connection in a configuration file and know that this database resource will be available when the application is run. A great use case for this kind of functionality is when a developer wants to have the application connect to a development database in the development environment and the production database in the production environment. Only the configuration file needs to be updated or changed. The code remains clean and untouched.

The authors of ZF did a good job in providing this simple, concise, compact functionality as a standard in the library. However, sometimes an application requires connecting to more than one database. Although this is not too difficult to accomplish, it would be nice to be able to use the configuration file to declare multiple databases in much the same way that a developer declares one. This article will show one way that a developer might resolve this problem.

Assumptions

I would like to share a couple of assumptions that I am making before I continue:
  • You have a fair understanding of PHP and application development.
  • You have a general knowledge of the ZF and how one typically builds applications using it.
  • You have a good handle on Zend_Application and the bootstrapping functionality that comes along with it.
If I'm wrong about any of these assumptions, then you may want to take some time to get comfortable with those topics that I've mentioned.

How Zend_Application_Resource_Db Works Out of the Box

Alright, the first thing one should do is understand how Zend_Application_Resource_Db works. ZF's documentation provides settings from a sample configuration file that is used to define a database resource that ZF's bootstrapper will initialize:

 [production]
 resources.db.adapter = "pdo_mysql"
 resources.db.params.host = "localhost"
 resources.db.params.username = "webuser"
 resources.db.params.password = "XXXXXXX"
 resources.db.params.dbname = "test"
 resources.db.isDefaultTableAdapter = true

The first thing you may notice is that each line starts with resources.db. The first part, resources, is the standard way to indicate that you're declaring a resource that you intend for ZF's bootstrapper to pick up and initialize for you.  The second part, db, helps identify the specific resource plugin class that is responsible for initializing the resource that is being declared.  What ZF will do in this case is to find a registered plugin class that ends in "Db".  In this case, the standard plugin classes are registered with "Zend_Application_Resource" as the prefix, so ZF finds Zend_Application_Resource_Db, which is then called upon to initialize the database resource.

Back to the Problem

So, back to our problem.  What do we do if we would like to define more than one database?  How can we enjoy something as convenient as the automatic initialization of the DB resource plugin?

As I indicated before, there are many ways one can do this.  One way is to write part of your bootstrapping code to look for specific configuration options that have to do with the databases and then initialize the databases accordingly.  But, another way is to continue to use the "resources" approach and follow ZF's advice when it comes to utilizing resource plugins: "create re-usable bootstrap resources and to offload much of your coding to discrete classes."

So that's what we'll do....

Changing the Configuration File

First of all, we need to determine how we want to define our databases in the configuration file.  We know that we want to follow the conventions already used to define a single database.  However, it will be necessary to make a distinction, of course.  So, this is what I will suggest:

 [production]
 resources.dbs.internal.adapter = "pdo_mysql"
 resources.dbs.internal.params.host = "localhost"
 resources.dbs.internal.params.username = "webuser"
 resources.dbs.internal.params.password = "XXXXXXX"
 resources.dbs.internal.params.dbname = "test"
 resources.dbs.internal.isDefaultTableAdapter = true
 resources.dbs.internal.getConnection = true

You can see that this looks very similar to what we had before.  However, there are also some obvious differences:
  • First, instead of a "db" resource, I chose to have a "dbs" resource.  This can be whatever you would like it to be, of course, but it must be something that the bootstrapper can tie to a plugin class.  We'll look at that in a bit.
  • Next, you will see that I've marked each setting as "internal".  This is arbitrary and it happens to match the designation for one of my two fictional databases.  Since we're going to have multiple databases, there must be a way to distinguish each adapter from the others.  I plan to have the resource plugin store each database adapter in an array where each element's key corresponds to this particular value.
  • Finally, you will notice one extra setting: getConnection.  I added this in to indicate to the resource plugin whether or not to attempt to connect to the database when the adapter is created.  By default, ZF connects lazily to the database.  Generally, I prefer not to connect lazily.  So, setting this to true will tell the resource plugin to call the adapter's getConnection() method once it is created.
So, what does it look like when I add one more database to the mix and make all this work actually useful...? Take a look:

 [production]
 resources.dbs.internal.adapter = "pdo_mysql"
 resources.dbs.internal.params.host = "localhost"
 resources.dbs.internal.params.username = "webuser"
 resources.dbs.internal.params.password = "XXXXXXX"
 resources.dbs.internal.params.dbname = "test"
 resources.dbs.internal.isDefaultTableAdapter = true
 resources.dbs.internal.getConnection = true

 resources.dbs.external.adapter = "pdo_mysql"
 resources.dbs.external.params.host = "external.server"
 resources.dbs.external.params.username = "differentuser"
 resources.dbs.external.params.password = "XXXXXXX"
 resources.dbs.external.params.dbname = "externaltest"
 resources.dbs.external.isDefaultTableAdapter = false
 resources.dbs.external.getConnection = true

Naming Our Custom Resource Plugin

Now that we have determined how we will define our database connections in the configuration file, we will want to create the resource plugin class that will take these settings and do something with them.  We will start this process by declaring a new class: Acme_Application_Resource_Dbs.

The name of the class has some significance:
  • The first part, Acme, is the name of my fictional company.  Like Zend does with ZF, each of my classes starts with Acme.
  • The second and third parts, Application_Resource, simply follows the same naming convention that ZF's standard resource plugins follow.
  • The final part, Dbs, is the unique name that we gave the resource in the configuration file.  The "dbs" in the set of configuration file settings must match this.  This is what I was talking about earlier when I noted that we'd "look at that in a bit."

To Implement or to Extend

Now, according to ZF's documentation on developing resource plugins, for a resource plugin class to work, it must implement Zend_Application_Resource_Resource.  It looks like this:

 interface Zend_Application_Resource_Resource
 {
     public function __construct($options = null);
     public function setBootstrap(
         Zend_Application_Bootstrap_Bootstrapper $bootstrap
     );
     public function getBootstrap();
     public function setOptions(array $options);
     public function getOptions();
     public function init();
 }

However, to make things easier for us, they have included Zend_Application_Resource_ResourceAbstract.  It implements everything for us except the init() method.  So, we'll have our new class extend this one.

Take a look at what we have so far:

 class Acme_Application_Resource_Dbs extends Zend_Application_Resource_ResourceAbstract
 {
     // ...
 }

Pretty simple.  Now, we have only to implement the init() method.

Copy, Paste, and Modify

What I did was to take a copy of the Zend_Application_Resource_Db class as my starting point.  I stripped out nearly everything and then made the appropriate adjustments to accomplish my goal.

First of all, ZF's "db" implementation uses a private member variable to keep the database adapter.  I followed the same approach, changing the name and the type:

 class Acme_Application_Resource_Dbs extends Zend_Application_Resource_ResourceAbstract
 {
     /**
      * @var array
      */
     protected $_dbs = array();
 }

Then, I move along to implementing init().  My init() method looks like this:

 public function init()
 {
     if (null !== ($dbs = $this->_buildDbs())) {
         return $dbs;
     }
 }

Using an approach similar to Zend_Application_Resource_Db, I call an internal, private method, _buildDbs(), which does all of the heavy listing and assigns the return value to the variable, $dbs.  If $dbs is not null, then I return it.

The Nitty Gritty

It's easy enough, so let's move on to _buildDbs().  This is where most of the action takes place.

 protected function _getDbs()
 {
     $options = $this->getOptions();
     foreach ($options as $name => $opt) {
         $adapter = $opt['adapter'];
         $params = $opt['params'];
         $isDefaultTableAdapter = $opt['isDefaultTableAdapter'];
         $getConnection = $opt['getConnection'];
         $db = Zend_Db::factory($adapter, $params);
         if ($getConnection) {
             try {
                 $db->getConnection();
             } catch (Exception $e) {
                 throw new Acme_Application_Resource_Dbs_Exception("Could not connect to database: {$e->getMessage()}");
             }
         }
         if ($isDefaultTableAdapter) {
             Zend_Db_Table::setDefaultAdapter($db);
         }
         $this->_dbs[$name] = $db;
     }
     return $this->_dbs;
 }

The first thing I do is call getOptions().  This returns all of the configuration settings specific to this resource.  So, every setting "underneath" resources.dbs will be given to this resource plugin class.  Since we decided that our configuration settings would be grouped, then all we have to do is iterate through them.  Using our sample settings, we will first go through all of the "internal" settings on the first pass, and then all of the "external" settings on the second pass.  We grab the name of the group in $name to use later when inserting the adapter into the array.

Once in our loop, we pull out the specific settings that we need for building the adapter, declaring the default adapter, and addressing lazy connections.  Pulling these out isn't necessary, of course, but some developers prefer to do this.  Although the code is a little longer, some would say that it appears a little cleaner.  The alternative would be to dispense with the handful of variables and simply use the $opt array directly whenever necessary.  The code will be shorter, but some would say that it appears a little messier.  I would argue that it's a matter of preference and would recommend you make it look however you like.

Now that we have the adapter and params settings, we pass them along to Zend_Db in exchange for an adapter object.

We then check our getConnection setting.  If it's true, then we attempt to connect to the database by calling getConnection().  I wrapped the call to getConnection() in a try/catch block in case there is a problem.  What I have done in this example to handle the exception, is to throw a custom exception that can be handled in the calling code.

Next, we check the isDefaultTableAdapter setting.  If it's true, then we declare this particular database adapter as the default table adapter.

The last thing we do in the loop is to insert our new adapter into our array, using the name as the index.

We will repeat this loop for as many distinct database connections as we have declared in our configuration file...so long as there are no errors or exceptions.  Once done, then we simply return the array of databases to the calling method, init(), which is subsequently returned to its calling method.

And that is the end of our new class.  Here's how it looks:

 class Acme_Application_Resource_Dbs extends Zend_Application_Resource_ResourceAbstract
 {
     /**
      * @var array
      */
     protected $_dbs = array();
     protected function _getDbs()
     {
         $options = $this->getOptions();
         foreach ($options as $name => $opt) {
             $adapter = $opt['adapter'];
             $params = $opt['params'];
             $isDefaultTableAdapter = $opt['isDefaultTableAdapter'];
             $getConnection = $opt['getConnection'];
             $db = Zend_Db::factory($adapter, $params);
             if ($getConnection) {
                 try {
                     $db->getConnection();
                 } catch (Exception $e) {
                     throw new Acme_Application_Resource_Dbs_Exception("Could not connect to database: {$e->getMessage()}");
                 }
             }
             if ($isDefaultTableAdapter) {
                 Zend_Db_Table::setDefaultAdapter($db);
              }
             $this->_dbs[$name] = $db;
         }
         return $this->_dbs;
     }
     /**
      * Defined by Zend_Application_Resource_Resource
      *
      * @return array|null
      */
     public function init()
     {
         if (null !== ($dbs = $this->_getDbs())) {
             return $dbs;
         }
     }
 }

Putting it to Work: Registration

Now that we're done with all of that, then we're nearly ready to put our new resource plugin to work.  However, in order for ZF to use it, it must first be registered.

To easily register your new resource plugin, simply add the following line to your configuration file:

 pluginPaths.Acme_Application_Resource = "Acme/Application/Resource"

Although I haven't tested it, I believe this line should come before your "resources" settings.

Putting it to Work: Easy Retrieval

That's it.  We're done....Technically, anyway.  Everything should now work fine when executed.  However, we may also wish to grab our databases and do something useful with them.  But, where are they?  We return the array of database adapters to the bootstrapping functionality, but we don't necessarily know where that is, and we don't necessarily know what is done with it.

There are at least a couple of solutions.  The first one is to introduce an _initXXX() method in your bootstrap class that will retrieve the array and store it using ZF's Zend_Registry.  This is simple enough, but may require a simple explanation to go along with it.  Here's what it might look like:

 protected function _initModifiedDbs()
 {
     $this->bootstrap('Dbs');
     $dbs = $this->getResource('Dbs');
 
     // Register them all at once...
     Zend_Registry::set("dbs", $dbs);

     // OR...register each of them individually...
     foreach ($dbs as $name => $db) {
         Zend_Registry::set($name, $db);
     }
 }

The first thing we need to do is to create the method, being careful not to name it _initDbs().  If we name it _initDbs(), then there will be a conflict and ZF will squawk.  So, I've given it a similar name, _initModifiedDbs().

The next thing that we do is to make sure that the "Dbs" resource has been bootstrapped.  After that, we can be confident that our two adapters have been created with the settings that we provided and that they were able to successfully connect to the databases.  To get the array of databases, however, all we need to do is to simply ask the bootstrapper to give us the resource in which we're interested.  In this case, it's the "Dbs" resource, and we can request it by calling the getResource() method.

Finally, in this example, I show a couple of ways to store the database adapters in the registry.

Another approach to gaining access to the database adapters might be to have the resource plugin class do all the work of saving the adapters to the registry.  Some people would frown on this approach, however, since that's not really the responsibility of the class.  I tend to agree.

Conclusion

Well, that's it.  I hope that this article has been helpful and would welcome any questions or constructive criticism.

Thanks...and happy coding...!