Multiple databases support on ZF

Zend Framework is nice but learning it is very confusing and time demanding. Official doc isn’t that clear, lacks of good examples and sometimes is outdated. Whenever I want something to be done I read the official documentation and then google it so I can get a good picture of what everyone did.

One thing that I recently needed was to enable multiple schema support for a specific project. One schema stores client info, the other stores products. One very interesting thing is that the products schema depends on the account so clients for region 1 should use a schema called reg1, while clients from region 2 a schema called reg2.

So the products schema kind of switches dynamically according to users setup.

The first thing to do is setup the config file. This is the easy part, I added some lines that looked like this:

resources.multidb.maindb.adapter = oracle
resources.multidb.maindb.persistent = true
resources.multidb.maindb.host =
resources.multidb.maindb.username = usr
resources.multidb.maindb.password = usr123
resources.multidb.maindb.dbname = "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=oracle.host.com)(PORT=1521)) (ADDRESS= (PROTOCOL=TCP)(HOST=oracle2.host.com)(PORT=1521)) (LOAD_BALANCE=yes) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srvcnm)))"

resources.multidb.seconddb.adapter = oracle
resources.multidb.seconddb.persistent = true
resources.multidb.seconddb.host =
resources.multidb.seconddb.username = usr2
resources.multidb.seconddb.password = usr2123
resources.multidb.seconddb.dbname = "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=soracle.host.com)(PORT=1521)) (ADDRESS= (PROTOCOL=TCP)(HOST=soracle2.host.com)(PORT=1521)) (LOAD_BALANCE=yes) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srvcnm2)))"

resources.multidb.thirddb.adapter = oracle
resources.multidb.thirddb.persistent = true
resources.multidb.thirddb.host =
resources.multidb.thirddb.username = usr3
resources.multidb.thirddb.password = usr3123
resources.multidb.thirddb.dbname = "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=toracle.host.com)(PORT=1521)) (ADDRESS= (PROTOCOL=TCP)(HOST=toracle2.host.com)(PORT=1521)) (LOAD_BALANCE=yes) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srvcnm3)))"

Notice I’m storing everything in a multidb array with three different elements: maindb, seconddb and thirddb. Let’s consider the maindb to be the users schema, for products I’ll be switching between seconddb and thirddb.

First of all the main connection needs to be set so we can login, update, delete and o more stuff with a user account. Alo this connection will be set as the default adapter. I did this in the bootstrap. Here is the code:

/**
* Init the main database connection.
*/
protected function _initDatabase(){
    $applicationConfig = new Zend_Config_Ini(APPLICATION_PATH . '/configs/application.ini', APPLICATION_ENV);
    $config = $applicationConfig->toArray();
    $dbIni = $config['resources']['multidb']['maindb'];
    $db = Zend_Db::factory($dbIni['adapter'], $dbIni);
    Model_DbTable_Db::setDefaultAdapter($db);
}

So now we have the main connection up and working as a default adapter. This works the same as a basic database configuration. Now to the interesting part… set up the other database connection according to the user region. In my case the user’s account has a field/property that stores the correct products schema name so one of the things I’ll be doing is reading that property to know which schema I need to connect to for the products details. I won’t explain the account part because it’s quite simple.

We use a baseController from which we inherit all other controllers so we can easily concentrate most of the common methods in one single place. Within that baseController I added the following method:

/**
* Depending on the account we set up the adapter for the products tables
* @param Account A valid account object
*/
private function setProductsSchema(Account $account){
    if(!Zend_Registry::isRegistered('erpdb')){
        $applicationConfig = new Zend_Config_Ini(APPLICATION_PATH . '/configs/application.ini', APPLICATION_ENV);
        $config = $applicationConfig->toArray();
        $dbIni = $config['resources']['multidb'][strtolower($account->getSchemaName())]; // In this case $account->getSchemaName() may return 'seconddb' or 'thirddb' 😀
        $erpdb = Zend_Db::factory($dbIni['adapter'], $dbIni);
        Zend_Registry::set('erpdb',$erpdb);
    }
}

Now the application is able to dynamically choose the proper schema for products according to the account. It’s important to add it to the registry with a name, in this case is erpdb. This is necessary in order to let the DbTable objects know which schema they’ll use. Be sure to call this method before making a query to the secondary schema.

Now let’s set up the DbTable objects so they know which connection they’ll be using. Right now all DbTable objects are using the default adapter set in the bootstrap, for those objects that refer to products we need to make them aware that they’ll be using the secondary connection. doing this is pretty straightforward.

Go to your DbTable object and add the following method:

/**
* Set the default adapter for this object
*/
public function init(){
    $this->_setAdapter('erpdb');
}

That’s it! Notice that we set the adapter name the same as the db registry added before.

And that’s basically all we need to do to make the connection dynamic 😀

Hope it helps.

Tags: , , ,

Comments are closed.