AMAZIUM: IT and Management Consulting

One of the first experiments I did with Application Resources was writing a application resource for loading multiple databases. We generally connect to multiple databases in reporting applications or in scripts where we want to aggregate data from the main database to an offsite database. Since that post I received a lot of questions on how to integrate this in the models and how to join different databases in queries using zend framework. In this post I'll explore how to do both. Since in the meantime someone has used the idea and added a multiple db resource to the Zend Framework, I will use the "official" application resource loader instead of my own.

This tutorial was made with Zend Framework 1.10.4.

Same host / user

So, let's start at the beginning. We have a server, with 2 schemas (databases) on it. Let's assume that the same user has access on both the schemas. You do not need to use the resource loader for loading multiple databases in this case.

You start by adding the database configuration to the application.ini :

resources.db.adapter                            = PDO_MYSQL
resources.db.params.dbname                      = dbtest
resources.db.params.username                    = dbtest
resources.db.params.password                    = ******
resources.db.params.hostname                    = localhost
resources.db.isDefaultTableAdapter              = true

I created 2 databases "dbtest" and "dbtest2". The dbtest database has a table customer and customer_product. The dbtest2 database has a product database:

-- on dbtest

CREATE TABLE `customer` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `customer` (`id`, `firstname`, `lastname`) VALUES
(1, 'Jeroen', 'Keppens'), 
(2, 'John', 'Doe'), 
(3, 'Someone', 'Else');

CREATE TABLE `customer_product` (
    `customer_id` int(11) unsigned NOT NULL,
    `product_id` int(11) unsigned NOT NULL,
    PRIMARY KEY  (`customer_id`, `product_id`)
);

INSERT INTO `customer_product` (`customer_id`, `product_id`) VALUES
(1, 3), (2, 1), (3, 2);

-- on dbtest2

CREATE TABLE `product` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `title` varchar(100) NOT NULL,
    PRIMARY KEY  (`id`)
);

INSERT INTO `product` (id, title) VALUES
(1, 'IPad 16Gb Wifi'),
(2, 'IPad 32Gb Wifi'),
(3, 'IPad 64Gb Wifi');

Using Zend_Db_Select

Now, let's first do it the Query way. We'll try to Zend_Db_Table later. When adding your FROM or JOIN clause, you can specify the schema (optional last parameter) in Zend_Db_Select. This will enable you to tell in which schema the database is:

$db = Zend_Db_Table::getDefaultAdapter();
$select = new Zend_Db_Select($db);
$select->from('customer', 
               array('firstname', 'lastname'))
        ->join('customer_product', 
               'customer.id = customer_product.customer_id', 
               null)
        ->join('product', 
               'product.id = customer_product.product_id', 
               array('title'), 
               'dbtest2'); // Added "dbtest2" as schema
$customerProducts = $db->query($select)->fetchAll();
var_dump($customerProducts);

This will give you the customer products per customer:

array
  0 => 
    array
      'firstname' => string 'Jeroen' (length=6)
      'lastname' => string 'Keppens' (length=7)
      'title' => string 'IPad 64Gb Wifi' (length=14)
  1 => 
    array
      'firstname' => string 'John' (length=4)
      'lastname' => string 'Doe' (length=3)
      'title' => string 'IPad 16Gb Wifi' (length=14)
  2 => 
    array
      'firstname' => string 'Someone' (length=7)
      'lastname' => string 'Else' (length=4)
      'title' => string 'IPad 32Gb Wifi' (length=14)

As you can see it joined as we wanted. Now, this only works because the databases are on the same server and the user has access to both.

Using Zend_Db_Table

When defining your table, you can specify the schema:

class Customer extends Zend_Db_Table_Abstract
{
    protected $_name   = 'customer';
    protected $_schema = 'dbtest';
    
    protected $_dependentTables = array('CustomerProduct');
}

class CustomerProduct extends Zend_Db_Table_Abstract
{
    protected $_name   = 'customer_product';
    protected $_schema = 'dbtest';

    protected $_referenceMap    = array(
        'Customer' => array(
            'columns'           => 'customer_id',
            'refTableClass'     => 'Customer',
            'refColumns'        => 'id'
        ),
        'Product' => array(
            'columns'           => 'product_id',
            'refTableClass'     => 'Product',
            'refColumns'        => 'id'
        ),
    );
}

class Product extends Zend_Db_Table_Abstract
{
    protected $_name   = 'product';
    protected $_schema = 'dbtest2';
    
    protected $_dependentTables = array('CustomerProduct');
}

Now you can easily request the products for a customer like this:

$tblCustomer = new Customer();
$customer = $tblCustomer->find(1)->current();
$products = $customer->findManyToManyRowset('Product', 'CustomerProduct');
var_dump($products->toArray());

This will yield the following result:

array
  0 => 
    array
      'customer_id' => string '1' (length=1)
      'product_id' => string '3' (length=1)
      'id' => string '3' (length=1)
      'title' => string 'IPad 64Gb Wifi' (length=14)

Different user/host

Now, it becomes more difficult if you have a different in how mysql connects to the two schemas. For example different users that each have access on one schema or two different servers.

So let's start by setting up our test system. On a different server, I added a table "dbtest", a user dbtest with the same credentials and "%" as host. This way it can be accessed from my local test environment. Obligatory disclaimer: Don't forget to turn this off later because you open up your database to the whole world. In production environments don't use %, but the IP of the calling server.

In application.ini we remove the settings we used before and we replace it by the following:

resources.multidb.local.adapter                 = pdo_mysql
resources.multidb.local.host                    = localhost
resources.multidb.local.username                = dbtest
resources.multidb.local.password                = ******
resources.multidb.local.dbname                  = dbtest
resources.multidb.local.default = true

resources.multidb.remote.adapter                = pdo_mysql
resources.multidb.remote.host                   = remote_host
resources.multidb.remote.username               = dbtest
resources.multidb.remote.password               = ******
resources.multidb.remote.dbname                 = dbtest
resources.multidb.remote.default                = false

In the bootstrap you add the _initDbRegistry block like below, this will add the different databases to the registry:

<?php

class Bootstrap extends Zend_Application_Bootstrap_Bootstrap
{

    /**
     * Add databases to the registry
     * 
     * @return void
     */
    public function _initDbRegistry()
    {
        $this->bootstrap('multidb');
        $multidb = $this->getPluginResource('multidb');
        Zend_Registry::set('db_local', $multidb->getDb('local'));
        Zend_Registry::set('db_remote', $multidb->getDb('remote'));
    }
    
}

Seperated DB Calls

Let's first see how we set everything up so we can access each database trough Zend_Db_Table seperately before we go to joining.

The models you modify, so they look like this (note the extra $_adapter atribute where we added the registry key for the database):

class Customer extends Zend_Db_Table_Abstract
{
    protected $_name    = 'customer';
    protected $_schema  = 'dbtest';
    protected $_adapter = 'db_local';
    
    protected $_dependentTables = array('CustomerProduct');
}

class CustomerProduct extends Zend_Db_Table_Abstract
{
    protected $_name    = 'customer_product';
    protected $_schema  = 'dbtest';
    protected $_adapter = 'db_local';

    protected $_referenceMap    = array(
        'Customer' => array(
            'columns'           => 'customer_id',
            'refTableClass'     => 'Customer',
            'refColumns'        => 'id'
        ),
        'Product' => array(
            'columns'           => 'product_id',
            'refTableClass'     => 'Product',
            'refColumns'        => 'id'
        ),
    );
}

class Product extends Zend_Db_Table_Abstract
{
    protected $_name    = 'product';
    protected $_schema  = 'dbtest2';
    protected $_adapter = 'db_remote';
    
    protected $_dependentTables = array('CustomerProduct');
}

Ok, now it's quite easy to access each one:

$tblCustomer = new Customer();
$customer = $tblCustomer->find(1)->current();
var_dump($customer->toArray());

$tblProduct = new Product();
$product = $tblProduct->find(1)->current();
var_dump($product->toArray());

This gives us the following result:

array
  'id' => string '1' (length=1)
  'firstname' => string 'Jeroen' (length=6)
  'lastname' => string 'Keppens' (length=7)

array
  'id' => string '1' (length=1)
  'title' => string 'IPad 16Gb Wifi' (length=14)

Ok, so far so good...

Querying Dependant Rowset

Now, what would happen if we tried the code from above where we got a dependant rowset?

$tblCustomer = new Customer();
$customer = $tblCustomer->find(1)->current();
$products = $customer->findManyToManyRowset('Product', 'CustomerProduct');
var_dump($products->toArray());

Well, I must say that the result I got was quite unexpected!

array
  0 => 
    array
      'id' => string '3' (length=1)
      'title' => string 'IPad 64Gb Wifi' (length=14)

In other words, this worked fine! Zend_Db_Table was smart enough to make it work. No

Pushing our luck?

I wondered if we could get Zend_Db_Select to accept this as well. So I decided to give it a shot... I soon realized however that this would never work. Zend_Db_Select does nothing else than to compose a query and then sent it to a database server. This would mean that (in my case) mysql needed to support this and that's just not the case.

Conclusion

So, what did we learn? We can connect to multiple database from our application. We can configure our Db_Tables to use the correct database so we don't have to worry about it. We can even request depending rowsets trough the use of Zend_Db_Table, without having to worry where the data came from.

What can't we do? Well, it's quite easy: you can not write your own queries (hardcoded or by using Zend_Db_Select) getting data from two different databases.

I hope you enjoyed the tutorial!

Have fun,

Jeroen