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|
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:
- 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.
- 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.
Doctrine_Manager::getInstance() ->bindComponent('PsProduct', 'shop_a_connection');
- 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).
- 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
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.
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
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!