Multiple databases with prefixes

I am a novice Zend Framework 2 and Doctrine developer. I could not find answers for the problems described below.

I am using multiple databases for one website. For example there is a users database with all user details and a database with statistics. It's possible that there are statistics for a user so there are relations (constrains) between both databases. For previous projects I used one configuration file and one class/directory with entities (for one db).

  • How can I use multiple databases in one project with correct table relations?
  • How/where should I store the database configuration?
  • How/where should I store the entities?

In the same project I use multiple versions of the databases (develop, testing, production) so I want to use prefixes for the database name. For example dev_users and tst_users. I think I should use a prefix from an configuration file and use it somewhere to prefix the databases.

  • How should I prefix all databases?

At last I am really curious how other people use database entities when the have multiple modules using them.

Answers


  • How can I use multiple databases in one project with correct table relations?

Doctrine uses one Entity Manager per DB Connection. So you can connect to multiple databases but you can't link those entities. The only way to put constraints on entity from within Doctrine or within the database is if the are in the same DB.

You can however enforce the constraint yourself and use two separate Entity Managers however i wouldn't recommend this

  • How/where should I store the database configuration?

the database connection details should be stored in your /config/autoload/local.php or /config/autoload/{ownname}.local.php and for ownname you can for example choose 'doctrine'

doctrine.local.php

<?php

return array(
    'doctrine' => array(
        'connection' => array(
            'orm_default' => array(
                'configuration' => 'orm_default',
                'driverClass' =>'Doctrine\DBAL\Driver\PDOMySql\Driver',
                'eventmanager' => 'orm_default',
                'params' => array(
                    'host' => 'localhost',
                    'port' => '3306',
                    'user' => '{username}',
                    'password' => '{password}',
                    'dbname' => '{db}',
                ),
            ),
        ),
    ),
);

The general doctrine setting like where your entites are located for reading the class meta data can be placed in /config/autoload/global.php file or in a file ending with global.php like in the previous with local or you can store it in your modules config file

  • How/where should I store the entities?

The entities should be stored in your module in a directory Entity like: root/module/{modulename}/src/{modulename}/Entity/

for how to store them see: Doctrine Documentation

if you use your entities with multiple modules it is better to make a common module and put that as a dependency with all the modules that require this entity

  • Testing mode

You can change your databases yourself for testing environments or use a module for chnaging between config files like: Config Module Which is made by the people who made ZF2


Need Your Help

EXT JS 4 Grid with Filter and Pagination

extjs pagination grid filtering

I have a ext js grid with pagination and filtering on browser side. for example if there are 3 pages and i apply the filter to display last page records. Then it shows first and 2nd page blank and ...

Get facebook like count of a url using api

facebook facebook-graph-api

I used the API for getting the share count. Any API for getting the facebook like count of a url. The old api is deprecated now.