Connect to Multiple data bases using Zend Framewor

Question

Hello i'm using Zend Framework 2 and DoctrineORMModule. I need to access to different data bases connections and map two different set of schemas.

'doctrine' => array(
    'connection' => array(
        'orm_default' => array(
            'driverClass' => 'Doctrine\DBAL\Driver\PDODblib\Driver',
            'params' => array(
                'host'     => 'HOST',
                'port'     => '1433',
                'user'     => 'USER',
                'password' => 'PASS',
                'dbname'   => 'DBNAME',
            )
        )
    )
),
/////////////

'doctrine' => array(
        'connection' => array(
            'orm_default' => array(
                'driverClass' => 'Doctrine\DBAL\Driver\PDOMySql\Driver',
                'params' => array(
                    'host'     => '127.0.0.1',
                    'port'     => '3306',
                    'user'     => 'root',
                    'password' => 'root',
                    'dbname'   => 'test',
                )
            )
        ),
    ),

I found this in the documentation: https://github.com/doctrine/DoctrineORMModule/blob/master/docs/configuration.md#how-to-use-two-connections But it is not very descriptive.

Can anyone help me?

Answer 1

use Doctrine\DBAL\DriverManager;
use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Configuration;
use Doctrine\DBAL\Connection;

/**
 * @author  Rafał Książek
 */
class DbFactory
{

    /**
     * @var array 
     */
    protected $config;

    /**
     * @return array
     */
    public function getConfig()
    {
        return $this->config;
    }

    /**
     * @param array $config
     */
    public function __construct(array $config)
    {
        $this->config = $config;
    }

    /**
     * Create connection to database
     * 
     * @param string $dbName
     * @return \Doctrine\DBAL\Connection
     * @throws \InvalidArgumentException
     * @throws \Exception
     */
    public function getConnectionToDatabase($dbName)
    {
        $config = $this->getConfig();

        if (empty($config['doctrine']['connection']['orm_default']['params'])) {
            throw new \InvalidArgumentException('There is insufficient data in the configuration file!');
        }

        $params           = $config['doctrine']['connection']['orm_default']['params'];
        $params['dbname'] = $dbName;
        $params['driver'] = 'pdo_mysql';

        if (!($dbConnection = DriverManager::getConnection($params)))
            throw new \Exception('There was a problem with establish connection to client db');

        return $dbConnection;
    }

    /**
     * 
     * @param \Doctrine\DBAL\Connection $dbConnection
     * @param \Doctrine\ORM\Configuration $config
     * @return \Doctrine\ORM\EntityManager
     */
    public function getEntityManager(Connection $dbConnection, Configuration $config)
    {
        return EntityManager::create($dbConnection, $config);
    }

}

How to used:

$applicationConfig = $sm->get('config');
$em = $sm->get('Doctrine\ORM\EntityManager');
$emDefaultConfig = $em->getConnfiguration();

$dbFactory = new DbFactory($applicationConfig);
$anotherConnection = $dbFactory->getConnectionToDatabase('another_db');
$anotherEntityManager = $dbFactory->getEntityManager($anotherConnection, $emDefaultConfig);

$usersOnAnotherDb = $anotherEntityManager->getRepository('Application\Entity\User')->findAll();

Answer 2

Have a look at link and link. Unfortunately the use statements seems to be missing. These are the factories with FQDN:

'factories' => array(
        'doctrine.connection.orm_alternative' => new \DoctrineORMModule\Service\DBALConnectionFactory('orm_alternative'),
        'doctrine.configuration.orm_alternative' => new \DoctrineORMModule\Service\ConfigurationFactory('orm_alternative'),
        'doctrine.entitymanager.orm_alternative' => new \DoctrineORMModule\Service\EntityManagerFactory('orm_alternative'),
        'doctrine.driver.orm_alternative' => new \DoctrineModule\Service\DriverFactory('orm_alternative'),
        'doctrine.eventmanager.orm_alternative' => new \DoctrineModule\Service\EventManagerFactory('orm_alternative'),
    ),

If you only want to access another database (on the same server, same user, etc.), you can easily define your tables in the entities like this:

@ORM\Table(name="database.table")

and save yourself a lot of trouble.

Answer 3

U can set this array config for every separate module in your module config file

return array(
    'doctrine' => array(
        'orm_autoload_annotations' => true,

        'connection' => array(
            'orm_default' => array(
                'configuration' => 'orm_default',
                'eventmanager'  => 'orm_default',

                'params' => array(
                    'host'     => 'localhost',
                    'port'     => '3306',
                    'user'     => 'username',
                    'password' => 'password',
                    'dbname'   => 'database',
                )
            ),
        ),

        'configuration' => array(
            'orm_default' => array(


                'driver'            => 'orm_default',

                'generate_proxies'  => true,
                'proxy_dir'         => 'data/DoctrineORMModule/Proxy',
                'proxy_namespace'   => 'DoctrineORMModule\Proxy',
                'filters'           => array()
            )
        ),

        'driver' => array(
            'orm_default' => array(
                'class'   => 'Doctrine\ORM\Mapping\Driver\DriverChain',
                'drivers' => array()
            )
        ),

        'entitymanager' => array(
            'orm_default' => array(
                'connection'    => 'orm_default',
                'configuration' => 'orm_default'
            )
        ),

        'eventmanager' => array(
            'orm_default' => array()
        ),

        'sql_logger_collector' => array(
            'orm_default' => array(),
        ),

        'entity_resolver' => array(
            'orm_default' => array()
        ),

        'authentication' => array(
            'orm_default' => array(
                'objectManager' => 'doctrine.entitymanager.orm_default',
                //'identityClass' => 'Application\Model\User',
                //'identityProperty' => 'username',
                //'credentialProperty' => 'password'
            ),
        ),
    ),

    // zendframework/zend-developer-tools specific settings

    'view_manager' => array(
        'template_map' => array(
            'zend-developer-tools/toolbar/doctrine-orm' => __DIR__ . '/../view/zend-developer-tools/toolbar/doctrine-orm.phtml',
        ),
    ),

    'zenddevelopertools' => array(
        'profiler' => array(
            'collectors' => array(
                'orm_default' => 'doctrine.sql_logger_collector.orm_default',
            ),
        ),
        'toolbar' => array(
            'entries' => array(
                'orm_default' => 'zend-developer-tools/toolbar/doctrine-orm',
            ),
        ),
    ),
);