Using Multiple Databases in Multiple Environments with Zend Framework

I have been using Zend Framework quite extensively during the last few years. On a recent project, we had 8-9 separate applications integrated into the a single website. Many of these applications used a different a databases (Oracle, MySql, etc.), so we had to set up Zend Framework to be able to connect to any database at any given time.

Application.ini

First we set up the database configuration in the application.ini file (/application/configs/application.ini). We worked in multiple environments (development, test, production), so we set up the database for each environment. We created a resource named ‘multdb’ to store all of our database configurations.

[production]
resources.multidb.popdev.adapter = "PDO_OCI"
resources.multidb.popdev.dbname = "proddbname"
resources.multidb.popdev.username = "productionusername"
resources.multidb.popdev.password = "productionpassword"
resources.multidb.popdev.isDefaultTableAdapter = true
 
resources.multidb.mris_train.adapter = "PDO_OCI"
resources.multidb.mris_train.dbname = "proddbname"
resources.multidb.mris_train.username = "productionusername"
resources.multidb.mris_train.password = "productionpassword"
resources.multidb.mris_train.port = "1111"
resources.multidb.mris_train.isDefaultTableAdapter = false
 
resources.multidb.provisioning.adapter = "PDO_MYSQL"
;resources.multidb.provisioning.adapterNamespace = "Mris_Db_Adapter"
resources.multidb.provisioning.host = "192.192.2.10"
resources.multidb.provisioning.username = "productionusername"
resources.multidb.provisioning.password = "productionpassword"
resources.multidb.provisioning.dbname = "mris_provisioning"
resources.multidb.provisioning.isDefaultTableAdapter = false
 
resources.multidb.drupal.adapter = "PDO_MYSQL"
resources.multidb.drupal.host = "192.192.2.10"
resources.multidb.drupal.username = "productionusername"
resources.multidb.drupal.password = "productionpassword"
resources.multidb.drupal.dbname = "mris_drupal"
resources.multidb.drupal.isDefaultTableAdapter = false
 
[test : production]
 
resources.multidb.popdev.dbname = "testdbname"
 
resources.multidb.mris_train.dbname = "testdbname"
 
resources.multidb.provisioning.host = "192.192.0.14"
resources.multidb.provisioning.username = "testusername"
resources.multidb.provisioning.password = "testpassword"
 
resources.multidb.drupal.host = "192.192.0.11"
resources.multidb.drupal.username = "testusername"
resources.multidb.drupal.password = "testpassword"
 
[development : production]
 
etc.
 
[localdev : production]
 
;This section is for local development
etc.

So first we set up the database configuration for the production environment. All other environments will inherit the production settings. We only need to enter the settings that are different for the environment. So for the popdev connection in the test environment, other than the dbname, all other settings are the same. And with the Drupal connection, only the host, username, and password are different for the test environment.

Bootstrap.php

Once we have the database configuration setup, we need to tell Zend Framework that they are there. We do this in the Bootstrap.

class Bootstrap extends Zend_Application_Bootstrap_Bootstrap
{
    protected function _initDatabase()
    {
        $resource = $this->getPluginResource('multidb');
        $resource->init();
 
        Zend_Registry::set('popdev', $resource->getDb('popdev'));
        Zend_Registry::set('drupal', $resource->getDb('drupal'));
	Zend_Registry::set('provisioning', $resource->getDb('provisioning'));
        Zend_Registry::set('transit', $resource->getDb('transit'));
        Zend_Registry::set('mris_train', $resource->getDb('mris_train'));
        Zend_Registry::set('pr_data_status', $resource->getDb('pr_data_status'));
    }
}

Using a Database Connection

So now, we can use the Zend Registry class to connect to a database anywhere in our application.

$db = Zend_Registry::get('mris_train');
$sql = “select whatever from table”;
$result = $db->fetchAll($sql);

That’s it. Using multiple databases with Zend Framework is quite simple. Enjoy.

Be Sociable, Share!

Checkout My New Site - T-shirts For Geeks