Symfony World blog is not maintained anymore. Check new sys.exit() programming blog.

Multiple database symfony configuration (doctrine)

Scene from ingenious "Platoon" by Oliver Stone (1986)
It may happen that we'll need to connect to many databases inside one project. As this problem is not very common, I'll try to present the easiest way to manage multiple database connections.


First of all, edit the databases.yml file and set all database connections with appropriate labels. This means, the doctrine connection label means nothing and therefore it should be changed. Usually, in such case, apart from all external databases, the project itself has its own database which connection to is called master. Below is an example of multiple connection project databases.yml file:


  master:
    class: sfDoctrineDatabase
    param:
      dsn:      mysql:host=localhost;dbname=admin
      username: admin
      password: Mt5iA0Lcm0

  production:
    class: sfDoctrineDatabase
    param:
      dsn:      mysql:host=localhost;dbname=old_shop
      username: shop
      password: Mt5iA0Lcm0

  shop_one:
    class: sfDoctrineDatabase
    param:
      dsn:      mysql:host=localhost;dbname=shop_one
      username: shop
      password: Mt5iA0Lcm0

  shop_two:
    class: sfDoctrineDatabase
    param:
      dsn:      mysql:host=localhost;dbname=shop_two
      username: shop
      password: Mt5iA0Lcm0

This is an example database list of a project that migrates all data from one shop (which is, for example, too old and too bad to be mantained anymore) into two new distinct shops (each shop is deployed using open source software, like magento or prestashop) and one admin database (which is created for the project itself).

Now we need to apply a small change to the schema.yml files:

connection: master
This should be added in the beginning of each schema yml file, defining the connection name. It means, that all models defined in such schema file will be bound to chosen database. Remember to execute the clear cache command, because otherwise you may force difficulties with class files autoload (also when you're using plugins).

The main feature in connecting different classes with distinct databases is to have class-database connection binding set properly. In each base class, you will see

// Connection Component Binding
Doctrine_Manager::getInstance()->bindComponent('Product', 'master');
This line of code states, that all Product objects will be fetched from master database connection.

the structure to be chosen


From this moment, the model classes generated by the framework will include classes from all databases. And now here comes the main question to answer - how complex is my project and do I have to follow all symfony default mechanisms? Answer to this question is essential, because many implementation solutions will depend on the decision made. I'll briefly describe two alternative approaches:


  • Stick to the defaults. Suppose, you'll have about one hundred tables in each database. If we have 4 connections set, it gives 400 distinct model classes (to be precise: 400 base model classes, 400 model classes and 400 table classes, which is 1200).They all should have connection bindings set correctly, anyway, searching through 400 classes in a tree-view may be frustrating - this is the main disadvantage. Another one is that you'll have to generate all classes even if you won't need them (e.g. if you use open source software, you may need only some of them for your project). And one more disdvantage - you'll also have to keep all form and filter classes for all tables even if they're not needed. But the advantage is that if you modify anything in schema.yml file, base classes will be updated automatically and you don't have to care about that.
  • Choose your own lib directory structure. The task now is to separate all generate classes into different directories so that everything will be in order. The basic solution is to organise one directory for each database connection. The good things about this solution are: everything is more structured, easier to find; you don't have to keep all classes (if you need only few). And you don't have to keep all model/form/filter classes that doctrine generates - you choose them manually. The disadvantage is that all modifications made in schema files have to be updated manually (replace old version of your base model class in a special directory with a just generated new base model class). Subversion (or other versioning systems) helps greatly to keep everything up to date and user-friendly. After running symfony doctrine:generate-model you update the files you want (which means, destination base class files are replaced with their new versions) - then you commit your changes and afterwards clean it (remove model directory and update it from repository, so that all files you changed are saved in the repository and all the rest - which you don't want - is deleted).
Although the second option may seem too complicated or weird (or even both at the same time), I definitely prefer it. One of its great advantages I still haven't to mentioned is that it if you use your own lib classes structure, it allows you to do advanced and highly complex solutions (example: you've got few shops powered by the same OS software. And you need one tool to manage them all - an administration application. If you want to follow symfony default mechanisms, you have a very big problem, since table names will surely be the same in different shops. And connection binding is really basic in doctrine, since such problem is really rare. Even if you use distinct prefixes to set table names for distinct shop databases, you'll still force a big problem, since you'll need to copy-paste all methods between analogical classes, let's say, you define getFullName method in Product class - you'll have to copy this method into XxxProduct, YyyProduct, ZzzProduct and so on, if you want to stick to symfony defaults. In my opinion it's enough to prove, that symfony defaults are good as long as the project is easy, but if it's really advanced, non-standard solutions are usually the only way). Sticking to symfony defaults and being ready to regenerate model at the same time disables some higly complex features.

look out for the bug!

Unfortunately, there's a bug in symfony core, dealing with multiple database connections. It has been reported some time ago on trac, however, the bug is still there. In short, in some cases DoctrineManager takes the last connection from the databases.yml list, instead of the one you choose (you can read the details on the ticket page). To avoid problems with it, I pay attention to have all base model classes defined with the correct connection binding - and this is enough to get all project working.


As you can see, you're already ready to go - your site can handle multiple database connections!

Ajax/symfony easy & user friendly example

Scene from "The Wizard of Oz" by Victor Fleming (1939)

This time I would like to present a piece of code making use of symfony built-in AJAX functionalities. We're going to build a switch for activating/deactivating users in the listing of the sfDoctrineGuardPlugin (should be the same with Propel).

Dependencies

We're gonna use two major symfony plugins: sfDoctrineGuardPlugin and sfJqueryReloadedPlugin. I'm not going to show how to install plugins here.

Problem description

The moderator of a website needs to have a quick access to the is_active attribute of all users. This means it takes too much time for him to enter the edit form of each users, change the is_active property of a user, save changes, go back to the list, then choose another one and so on. Too many times the page has to be reloaded. We need to make a simpler access to this kind of information.

Another smart use of this technique may be enabing/disabling posts/comments in a blog, guestbook, forum entries and many, many more...

Solution

First step is to add activate and deactivate to object_actions section in the sfGuardUser/config/generator.yml:

generator:
  param:
    config:
      list:
        object_actions:
          _edit: ~
          _delete: ~
          activate: ~
          deactivate: ~

Now, the templates/_list_td_actions.php has two lines added, one for activating and the other for deactivating the user. But activating an active user is senseless, such as deactivating a non-active user. So we need to display either activate or deactivate user link depending on it's is_active value:

<span id="user_is_active_action_<?php echo $sf_guard_user->getId() ?>">
  <?php if ($sf_guard_user->getIsActive()): ?>
    <?php include_partial('sfGuardUser/ajax_deactivate', array('sf_guard_user' => $sf_guard_user)) ?>
  <?php else: ?>
    <?php include_partial('sfGuardUser/ajax_activate', array('sf_guard_user' => $sf_guard_user)) ?>
  <?php endif; ?>
</span>
Now, we put above code into the _ajax_main_active.php partial. _list_td_actions template should look like this now:
<td>
  <ul class="sf_admin_td_actions">
    <?php echo $helper->linkToEdit($sf_guard_user, array(  'params' =>   array(  ),  'class_suffix' => 'edit',  'label' => 'Edit',)) ?>
    <?php echo $helper->linkToDelete($sf_guard_user, array(  'params' =>   array(  ),  'confirm' => 'Are you sure?',  'class_suffix' => 'delete',  'label' => 'Delete',)) ?>
    <?php include_partial('ajax_main_active', array('sf_guard_user' => $sf_guard_user)) ?>
  </ul>
</td>
And now, create those two templates, sfGuardUser/templates/_ajax_deactivate.php:
<li class="sf_admin_action_deactivate_user" id="ajax_deactivate_<?php echo $sf_guard_user->getId() ?>">
<?php use_helper('jQuery'); ?>
  <?php echo jq_link_to_remote(__('Deactivate', array(), 'sf_admin'), array(
    'update'   => 'user_is_active_action_'.$sf_guard_user->getId(),
    'url'      => '@ajax_sf_guard_user_deactivate?id='.$sf_guard_user->getId(),
    'script' => true,
    'complete' => jq_remote_function( array(
      'update' => 'user_is_active_column_'.$sf_guard_user->getId(),
      'url'    => 'graphics/empty',
      'script' => true
    )),
  )) ?>
</li>
sfGuardUser/templates/_activate.php is very similar:
<li class="sf_admin_action_activate_user" id="ajax_activate_<?php echo $sf_guard_user->getId() ?>">
<?php use_helper('jQuery'); ?>
  <?php echo jq_link_to_remote(__('Activate', array(), 'sf_admin'), array(
    'update'   => 'user_is_active_action_'.$sf_guard_user->getId(),
    'url'      => '@ajax_sf_guard_user_activate?id='.$sf_guard_user->getId(),
    'script' => true,
    'complete' => jq_remote_function( array(
      'update' => 'user_is_active_column_'.$sf_guard_user->getId(),
      'url'    => 'graphics/tick',
      'script' => true
    )),
  )) ?>
</li>

Seems quite a lot of templates, uh? This part is the most difficult one, since all parameters have to be set correctly. Software, such as firebug are extremely helpful, making it easy to spot any errors, mistakes, typos, etc.

The update parameter defines the id of the tag that content is going to be changed (when the AJAX action is performed). url option defines the url of the actions that is going to be performed. complete option defines what is going to be executed after the main action is completed.

Aditionally, graphics module is added inside the project (but this can be done in many many different ways). Graphics/tick action shows a tick icon and nothing more. The action code:

  /**
  * Displays tick image.
  *
  * @param sfRequest $request A request object
  */
  public function executeTick(sfWebRequest $request)
  {
  }
and the template code:
<?php echo image_tag('/sfDoctrinePlugin/images/tick.png') ?>

In the admin generators, I miss a possibility to set a custom object action as a partial (e.g. _object_action_one.php) - the only way to edit them is to override the _td_actions.php file from cache and replace custom object actions with your own code.

The main actions the are going to perform activating and deactivating users shall be defined in the routing.yml:

ajax_sf_guard_user_activate:
  url:   /ajax_user_activate/:id
  param: { module: sfGuardUser, action: activate }
  requirements:
    id: \d+

ajax_sf_guard_user_deactivate:
  url:   /ajax_user_deactivate/:id
  param: { module: sfGuardUser, action: deactivate }
  requirements:
    id: \d+

... and in the sfGuardUser/actions/actions.class.php file:

  /**
   * Activates a user from admin generator list using AJAX.
   *
   * @param sfWebRequest $request
   * @return Partial - generated partial enabling user deactivating (switch).
   */
  public function executeActivate(sfWebRequest $request)
  {
    $user = sfGuardUserTable::getUserByIdQuery($request->getParameter('id'))->fetchOne();
    $user->activate();
 
    return $this->renderPartial('sfGuardUser/ajax_deactivate', array('sf_guard_user' => $user));
  }
 
  /**
   * Deactivates a user from admin generator list using AJAX.
   *
   * @param sfWebRequest $request
   * @return Partial - generated partial enabling user activating (switch).
   */
  public function executeDeactivate(sfWebRequest $request)
  {
    $user = sfGuardUserTable::getUserByIdQuery($request->getParameter('id'))->fetchOne();
    $user->deactivate();
 
    return $this->renderPartial('sfGuardUser/ajax_activate', array('sf_guard_user' => $user));
  }

In the code above, activate and deactivate methods are called on the sfGuardUser objects, therefore we define them inside the sfGuardUser.class.php:

  /**
   * Activates user (and saves itself afterwards).
   */
  public function activate()
  {
    $this->setIsActive(true);
    $this->save();
  }
 
  /**
   * Deactivates user (and saves itself afterwards).
   */
  public function deactivate()
  {
    $this->setIsActive(false);
    $this->save();
  }

Screenshots

Let's take a look how does our mechanism work:
Above is the admin generated sfGuardUser module, users are listed.
On the right hand side, there's a Actions column, where you can find either activate or deactivate links (depending on user's is_active value).
Clicking on activate/dactivate link executes three actions:

  • the user is activated/deactivated in the database
  • the activate/deactivate button is switched to the opposite one
  • the active column's tick icon is either shown or hidden

What's next

Of course, this is just the simplest possible example. With symfony, one can develop much more complex AJAX modules. I strongly encourage to do so, since modules auto-generated with symfony are, in general, just a simple interface to the data in the database. Many modifications are needed. Last, but not leaset, AJAX can be very user-friendly. In case of any questions, don't hesitate to write them here. If you have any feedback or want to post your ideas, please, do so.

Plugin references

Some of my plugins are provided with this symfony/AJAX technique. These are:

Install them and start using symfony AJAX.

where is include_custom in symfony 1.2-1.4 form?

The include_custom feature was available in version 1.0 and has disappeared from symfony forms since newer versions. Take a look at the doctrine form api - add_empty options shall be used. There are two possible ways to define new include_custom. The first one is to include a blank option:

$this->widgetSchema['market_id'] =  new sfWidgetFormDoctrineChoice(array(
  'model' => $this->getRelatedModelName('Market'),
  'add_empty' => true
));

The second way is to include an option with a defined label:

$this->widgetSchema['market_id'] = new sfWidgetFormDoctrineChoice(array(
  'model' => $this->getRelatedModelName('Market'),
  'add_empty' => 'whichever you want'
));

And that's it.

getRaw escaping problems

Scene from "Disney's Adventures of the Gummi Bears" by Jymn Magon & Art Vitello (introduced in 1985)

Probably most of us have encountered difficulties connected with default escaping mechanism in symfony templates. Yes, it is frustrating at times. But there are solutions to handle that.


single template solution

Suppose we have a executeShow action in our frontend module. We pass the object just retrieved from the db to the template.

public function executeShow(sfWebRequest $request)
{
  $this->article = Doctrine::getTable('Article')->find(array($request->getParameter('id')));
}

Then we have to add one line at the beginning of the showSuccess.php template file:

$article = $sf_data->getRaw('article');

From this line, no data shall be escaped (and all formatting will be displayed properly).


nested templates solution

We can also have more complicated templates structure. Suppose we have a executeIndex action in our frontend module. We pass the list of objects to the template.

public function executeIndex(sfWebRequest $request)
{
  $this->articles = Doctrine::getTable('Article')
    ->createQuery('a')
    ->execute();
}

Additionally, the indexSuccess.php file uses another template file:

<?php foreach ($articles as $article): ?>
  <?php include_partial('article/single', array('article' => $article)) ?>
<?php endforeach; ?>

We have to use the getRaw method IN ALL template/partial files. Just like in the previous example, we have to add one line at the beginning of the indexSuccess.php template file:


$articles = $sf_data->getRaw('articles');

And one line shall be added at the beginning of the _single.php partial file (the same as in the first example):

$article = $sf_data->getRaw('article');


Above solutions shall be sufficient in most cases.

session timeout in symfony 1.3, 1.4

Scene from "The Shining" by Stanley Kubrick (1980)

One of the main features to configure while developing a website that has access for users (in frontend, backend or both) is to set the session timeout. Its value defines time (measured in seconds) after which user session data will become out of date, if that user has performed no activity. For example, the default session expire time in symfony is 1800, which is 30 minutes (60*30), if a user is logged in and he won't do anything for at least 1800 seconds, he'll be automatically logged out.


solution

There are several blog posts in the internet describing where to change this value, but they refer only to symfony 1.0 (which is really old... and solutions described there do not apply to sf 1.3/1.4). So, the file is apps/APP_NAME/config/factories.yml. You need to add the following lines:

all:
  user:
    class: myUser
    param:
      timeout: 7200

where 7200 can be replaced with whatever unsigned integer value you want. Take a look at the main factories configuration file in symfony core: lib/vendor/symfony/lib/config/config/factories.yml. You'll find the default/user section there which holds all interesting stuff.