Issues

ZF-7997: Automatically initialize multiple databases...

Description

I think that it would be nice to introduce the ability to have Zend_Application automatically initialize multiple databases.

For example, one can define a database in a configuration file that will drive the initialization of a Zend_Db adapter. (See here: http://framework.zend.com/manual/en/…)

I suspect that there are a handful of situations where an application will use multiple database connections. Sometimes completely different hosts may be used. Sometimes the same database may be used, but as a different user. Sometimes the same host will be used, but with a different database. I'm guessing what kinds of situations people might be using, however there is an application that I work on that has some data on an "internal" server and other data on an "external" server. Both databases need to be used.

I realize that it's quite easy to set up multiple db adapters, each with different connection parameters, but one loses the ability to use the configuration file to drive this....a feature I very much enjoy and want to continue to use.

I noticed that there was a semi-related "new feature" issue already in the tracker system: http://framework.zend.com/issues/browse/ZF-6719. This issue is asking for the ability to have multiple configuration files, allowing, among other things, the ability to clean up and separate configuration information into smaller, focused files....kind of like Apache's HTTP conf files of late. I can see a focused DB configuration file that would simply declare which database(s) to connect to automatically. The ability to name each database and declare one as a default would be great.

So, that's it, in a nutshell. If, for some reason, someone doesn't understand what I'm talking about, please let me know and I'll try to explain it better.

Comments

This is already possible and very easily with application.ini

http://jaybill.com/2007/09/…

@Paul: Thanks for the feedback, but I don't think that you understand what I'm asking for....

I have been doing something similar to what is described in the link you provided for quite some time. That's not a problem.

However, what I am asking for is the ability to initialize multiple databases using Zend_Application_Resource_Db (part of Zend_Application and the automated bootstrapping functionality). Check out the first link in the description I wrote.

If I've misunderstood something, please let me know. I've been wrong a few times before. ;-)

Has anyone give much thought on how this is going to be implemented?

My proposal (subjected to everyone's scrutiny) is through a new Resource Plugins {{Zend_Application_Resource_DbAlias}} :



resources.dbAlias.alias.maindb.adapter = "PDO_MYSQL"
resources.dbAlias.alias.maindb.params.dbname = "myMainDb"
resources.dbAlias.alias.maindb.params.host = "host1.example.com"
resources.dbAlias.alias.maindb.params.username = "dbuser1"
resources.dbAlias.alias.maindb.params.password = "password1"

resources.dbAlias.alias.legacydb.adapter = "PDO_MSSQL"
resources.dbAlias.alias.legacydb.params.dbname = "myLegacyDb"
resources.dbAlias.alias.legacydb.params.host = "host2.example.com"
resources.dbAlias.alias.legacydb.params.username = "dbuser2"
resources.dbAlias.alias.legacydb.params.password = "password2"

resources.dbAlias.defaultTableAlias = "maindb"

Together with my proposed new {{Zend_Db_Alias}} class and additional code to {{Zend_Db_Table_Abstract}} ^code listed further down below^, we can support multiple Database Adapters in a much cleaner and easier way and with less coding. See example:



class MySite_Model_DbTable_MyMainTable1 extends Zend_Db_Table_Abstract
{
    protected $_dbAlias = "maindb";
    protected $_name = "MyTable1";
}

class MySite_Model_DbTable_MyMainTable2 extends Zend_Db_Table_Abstract
{
    // uses the defaultTableAlias, i.e. maindb
    protected $_name = "MyTable2";
}


class MySite_Model_DbTable_MyLegacyTable1 extends Zend_Db_Table_Abstract
{
    protected $_dbAlias = "legacydb";
    protected $_name = "MyTable1";
}

This proposal also allows implement without using {{Zend_Application}}. See example:



[maindb]
adapter = "PDO_MYSQL"
params.dbname = "myMainDb"
params.host = "host1.example.com"
params.username = "dbuser1"
params.password = "password1"

[legacydb]
adapter = "PDO_MSSQL"
params.dbname = "myLegacyDb"
params.host = "host2.example.com"
params.username = "dbuser2"
params.password = "password2"



Zend_Db_Alias::setConfig( 
    new Zend_Config_Ini( APPLICATION_PATH."/configs/database.ini" )
);
Zend_Db_Table_Abstract::setDefaultAlias( 'maindb' );

Here is my code of {{Zend_Db_Alias}}:



class Zend_Db_Alias 
{
    /**
     * Configuration of aliases
     *
     * @var Zend_Config
     */
    protected static $_config = NULL;
    
    /**
     * Set the configuration of aliases
     * 
     * @param $config Zend_Config
     * @return void
     */
    public static function setConfig( Zend_Config $config ) {
        self::$_config = $config;
    }
    
    /**
     * Get the configuration of aliases
     * 
     * @return Zend_Config
     */
    public static function getConfig() {
        return self::$_config;
    }

    /**
     * Get Zend_Db_Adapter_Abstract from alias
     * 
     * @param $alias string
     * @return Zend_Db_Adapter_Abstract
     */
    public static function getAdapter( $alias ) {
        
        $reg_key = __CLASS__."_Adapter_".$alias;
        if ( Zend_Registry::isRegistered( $reg_key ) ) {
            return Zend_Registry::get( $reg_key );
        }
        
        if ( isset( self::$_config->$alias ) ) {
            
            $db = Zend_Db::factory( self::$_config->$alias );
            
            Zend_Registry::set( $reg_key, $db );
            return $db;
            
        } else {
            
            throw new Zend_Db_Exception("Alias '{$alias}' not found.");
            
        }
        
    }
    
}

and additional changes to {{Zend_Db_Table_Abstract}}:


    
    /**
     * Default Db Alias.
     *
     * @var string
     */
    protected static $_defaultDbAlias = null;
    
    /**
     * The database alias.
     *
     * @var string
     */
    protected $_dbAlias = null;



    /**
     * Set default database alias.
     *
     * @param $alias string
     * @return void
     */
    public static function setDefaultAlias( $alias = null )
    {
        self::$_defaultDbAlias = $alias;
        self::setDefaultAdapter( Zend_Db_Alias::getAdapter( $alias ) );
    }
    
    /**
     * Get default database alias.
     *
     * @return string
     */
    public static function getDefaultAlias( )
    {
        return self::$_defaultDbAlias;
    }
    
    /**
     * Set database alias.
     *
     * @param $alias string
     * @return void
     */
    public function setAlias( $alias )
    {
        $this->_dbAlias = $alias;
    }
    
    /**
     * Get database alias.
     *
     * @return string
     */
    public function getAlias( )
    {
        return $this->_dbAlias;
    }
    
}



/**
 * Initialize database adapter.
 *
 * @return void
 */
protected function _setupDatabaseAdapter()
{
    // Start of added code
    if ( $this->_dbAlias ) {
        $this->_db = Zend_Db_Alias::getAdapter( $this->_dbAlias );
    }
    // End of added code
    
    if (! $this->_db) {
        $this->_db = self::getDefaultAdapter();
        if (!$this->_db instanceof Zend_Db_Adapter_Abstract) {
            require_once 'Zend/Db/Table/Exception.php';
            throw new Zend_Db_Table_Exception('No adapter found for ' . get_class($this));
        }
    }
}

TODO Code for {{Zend_Application_Resource_DbAlias}}

@Joe: Thanks for your feedback/input.

Yes, I've thought a bit about how it could be implemented. I posted a potential solution at http://zfgroove.blogspot.com. (It's my first blog article, so keep that in mind if you read it.)

I like what you proposed for Zend_Db_Table_Abstract. It does seem to offer a cleaner, nicer way of tying the DB adapter to the individual tables...in a way that is still consistent with the other aspects of Zend_Db_Table_Abstract.

With regards to Zend_Db_Alias, I didn't like it at first...although I'm quickly warming up to it. ;-) The way I see it, it appears to be the (necessary) liaison between the multiple databases and the Zend_Db_Table_Abstract descendants. It's short and sweet and has minimal impact on Zend_Db_Table_Abstract. I wonder, though, how many other classes may need to be impacted. Any ideas...?