Difference between #1 and #2 of Multiple-database support in Yii

unchanged
Title
Multiple-database support in Yii
unchanged
Category
How-tos
unchanged
Tags
Database
changed
Content
The customary configuration of a Yii application includes just a single database
section in the `protected/config/main.php` file, but it's easy to extend this to
support more than one, tying each Model to one of the databases.

We'll extend the standard blog example to tie into a separate Advertising
database: though it's related to the blog, it's still an independent system.

Config Setup
------------
The first step configures the second database into the configuration next to the
first DB, and though you can call it `db2` if you want, it it's perhaps helpful
to name it more usefully: we're calling it `dbadvert`:
~~~
[php]
// protected/main/config.php

return array(
    ...
    'components' => array(
        'db' => array(
            'connectionString' => 'mysql:host=dbserver1;dbname=my1db',
            ...
        ),
        'dbadvert' => array(
            'connectionString' =>
'mysql:host=adserver2;dbname=advertisingDB',
            'username'         => 'advertuser',
            'password'         => '***********',
            ...
            'class'            => 'CDbConnection'          // DO NOT FORGET
THIS!
        ),
        ...
~~~
The parameters should generally follow the pattern of the first entry, but you
**must** include the `class` parameter in the second so that Yii knows you're
defining a DB Connection object. It will fail without this.

Once this is defined, the second database is referred to as
`Yii::app()->dbadvert` rather than `Yii::app()->db` (of course, the first
is still available).

But we can do much better integration than this, starting with Gii and ending
with AR support.
Using Gii
---------
Gii only knows how to use the primary database connection, so for a brief time
while creating models/controllers/crud, you'll have to edit your
`protected/config/main.php` file to temporarily make the advertising database
the primary `db` connection:
~~~
[php]
// protected/config/main.php
    'components' => array(
#       'db' => array(
#           'connectionString' => 'mysql:host=dbserver1;dbname=my1db',
#           ...
#        ),
        // TEMPORARY (put back to 'dbadvert' when done)
        'db' => array(
            'connectionString' =>
'mysql:host=adserver2;dbname=advertisingDB',
~~~
Once this is done, use the Gii code generator to create what you need, then edit
your config file back to make both database connections live.
GetDbConnection() override
--------------------------
Every model defined in `protected/models/*.php` includes `GetDbConnection()` in
the base class, and it returns a handle to the DB connection object for the
primary database. We need to override this method in the models representing the
advertising database to return the second DB connection.

Though it's possible to do this in the model definition file itself, this
doesn't scale well as it would duplicate a lot of code if more than one model
lives in the Advertising database. Better is to use a custom wrapper class to
`CActiveRecord` where this can be centralized.

The notion of custom wrapper classes is described in [this<a
href=/wiki/121/extending-common-classes-to-allow-better-customization/">this
wiki
article](http://www.yiiframework.com/wiki/121/extending-common-classes-to-allow-better-customization/),article</a>,
and we'll assume that you've created a `protected/components/MyActiveRecord.php`
file, and taughtteach all of your model files to extend
`MyActiveRecord` rather than `CActiveRecord`.
~~~
[php]
// protected/components/MyActiveRecord.php

class MyActiveRecord extends CActiveRecord {
    ...
    private static $dbadvert = null;

    protected static function getAdvertDbConnection()
    {
        if (self::$dbadvert !== null)
            return self::$dbadvert;
        else
        {
            self::$dbadvert = Yii::app()->dbadvert;
            if (self::$dbadvert instanceof CDbConnection)
            {
                self::$dbadvert->setActive(true);
                return self::$dbadvert;
            }
            else
                throw new CDbException(Yii::t('yii','Active Record requires a
"db" CDbConnection application component.'));
            }
    }
    ...
~~~
This method is **purposely** static: the underlying cached `$dbadvert` value is,
so the function may as well be be too. Now, with this helper prepared, we can
edit the model itself:
~~~
[php]
// protected/models/Ad.php

class Ad extends MyActiveRecord {
    ...
    public function getDbConnection()
    {
        return self::getAdvertDbConnection();
    }
    ...
~~~
Now this model will properly fetch from the Advertising database instead of the
blog database, and this can be extended to as many models as you like.
Limitations on Multi-DB support
-------------------------------
* Tables in one database cannot directly reference tables in another database,
and this means that relations don't cross DB boundaries.