Multiple database symfony configuration - part 2

introduction

In one of the previous posts I've briefly described how to set up multiple connections in a symfony project. This time I'll show how to set up multiple connections to distinct databases which have exactly the same structure.

example study case

Suppose we work for a company that runs few E-commerce shops that are based on the same open source software package (like magento, osCommerce or prestashop). Let's say, there are 3 different shops: shopA, shopB and shopC. Of course, each of them is provided with its own admin application (standard for open source software). And this creates a problem: the more different admin applications employees need to access, the more time it will take to do a simple task. The solution is to create a center administration application (written in symfony framework) to manage everything that is accessible in our shops (could be something else, not only shops). Therefore, our new symfony application will access 4 databases, 3 for shops: shop_a, shop_b, shop_c and the last, most important one, admin for the entire admin application which needs its own database.

and the problem is...

... model, forms and filter classes. The current version of Symfony framework forces us to create separate classes for each table in each database (symfony does NOT support accessing multiple databases with the same structure). For example, to provide access for our 3 shops for just one table, we need to generate lots of classes:

	shop A	shop B	shop C
base model	BaseShopAClass	BaseShopBClass	BaseShopCClass
model	ShopAClass	ShopBClass	ShopCClass
model table	ShopAClassTable	ShopBClassTable	ShopCClassTable
base form	BaseShopAClassForm	BaseShopBClassForm	BaseShopCClassForm
form	ShopAClassForm	ShopBClassForm	ShopCClassForm
base filter	BaseShopAClassFormFilter	BaseShopBClassFormFilter	BaseShopCClassFormFilter
filter	ShopAClassFormFilter	ShopBClassFormFilter	ShopCClassFormFilter

We don't need to have so many base classes, which define exactly the same model. So we'll do some tricks! They can be quite complicated at first sight, but don't worry.

useful tools

Something I probably wouldn't manage without is a versioning system (even if I'm the only developer). This is because lots of operations on files will be made - and a versioning system makes it really easy to work with. The following article is based on SVN.

the basic idea

We'll share access for a given table in all distinct databases within the same classes. Why? Here are some of the main reasons:

1. Since all shop databases share the same structure (but different content - e.g. different pics, different prices), there's no need to create different (lib/form/filter) base classes for the ORM - they'd be the same afterall.
2. By default, symfony libs generators will generate some useless junk, unfortunately. Most common situation is that all tables in databases shop_a, shop_b and shop_c will have the same names, like ps_product and ps_product_lang (representing products in prestashop). Symfony will create following files:
   
   • (model) BasePsProduct.class.php,
   • (model) PsProduct.class.php,
   • (model) PsProductTable.class.php,
   • (form) BasePsProductForm.class.php,
   • (form) PsProductForm.class.php,
   • (filter) BasePsProductFormFilter.class.php and
   • (filter) PsProductFormFilter.class.php.
   And the problem is described more or less here - PsProduct class (PsProductForm and PsProductFormFilter as well) will refer to THE LAST CONNECTION described in the databases.yml file. And there is, unfortunately, no effect if you put following code into your classes:
   

   Doctrine_Manager::getInstance()
     ->bindComponent('PsProduct', 'shop_a_connection');

3. You don't have to rename tables in your shop databases. Usually open source packages use a table prefix: wp_ for wordpress, ps_ for prestashop and so on. If you want to create full set of classes for each database, you are forced to use a different prefix for each database (which may be what you don't want to do).
4. Finally, the main reason in the long run: having full set of classes for each database, you are forced to copy all methods between ShopA, ShopB and ShopC model, form and filter classes. And this can easily lead to dozens of mistakes (copy-paste is supposed to be the source of 80% of all bugs). If only one base class is created, you won't have to copy-paste all methods and all relations in your project! If you stay with

schema files

Create separate schema files for admin and shop databases: all admin database structure goes to admin.yml file and the shared shop database structure goes to shop.yml file. Use default schema.yml file as a temporary/buffer file.

lib-reload script

Create batch directory inside your root project directory (the same which existed in symfony 1.0). Create a shell script in this directory called lib-reload.sh with the following code:

#!/bin/sh
 
echo 'deleting generated model/form/filter'
rm -fR lib/model
rm -fR lib/form
rm -fR lib/filter
echo 'model/form/filter deleted.'
 
echo 'restoring svn model.'
svn up lib/model
echo 'svn model restored.'
 
echo 'restoring svn form.'
svn up lib/form
echo 'svn form restored.'
 
echo 'restoring svn filter.'
svn up lib/filter
echo 'svn filter restored.'

The idea of this script is to:

• generate all clas files (this includes useless junk, unfortunately),
• commit changes you want to save,
• delete ALL model, form and filter files,
• then fetch all committed files from SVN

Now our libs include only those files which we need (all 'temporary' files are removed). You can find similar script in another article, outsourcing applications with symfony.

summary

I've described my solution for symfony access to many databases sharing the same structure (with different content). I'm absolutely aware of the fact that there may be better ways to solve this problem, but this one is the easiest & fastest - in my opinion. Please, feel free to share your opinions and suggestions!