Chad 2010-05-07

Recently I've been working on a project which is using Symfony 1.4 and has the following features:

1. multiple database connectinos;

2. using sfDoctrineGuardPlugin.

Here's what the database configuration looks like:

Code - YMLPlain Text
 
  1. // config/databases.yml  
  2.    
  3. all:                      
  4.   my_db:    
  5.     class: sfDoctrineDatabase    
  6.     param:    
  7.       dsn:      mysql:host=localhost;dbname=my_db   
  8.       username: root   
  9.     
  10.   db_to_read:    
  11.     class: sfDoctrineDatabase    
  12.     param:    
  13.       dsn:      mysql:host=localhost;dbname=db_to_read   
  14.       username: root    

And the shema:

Code - YMLPlain Text
 
  1. // config/doctrine/schema.yml   
  2.      
  3. People:    
  4.   actAs:    
  5.     Timestampable: ~    
  6.   columns:    
  7.     name: { type: string(255), notnull: true }   

But something undesired happened when I run this command:

Code - Command LinePlain Text
 
  1. >symfony doctrine:build --all --and-load  

I want the `People` table to be added into `my_db`, but it's added into `db_to_read`. Aparently, that command was using the "db_to_read" connection, instead of "my_db".

It's OK if I change the order of the connections in database.yml, but I wonder if there is a configuration option to set "default database connection" or not.

 

To set a defalut database in Symfony

After searching for a while, I found that there're already some posts about this topic[1] [2].

It is said that although we don't have a default database connection configuration opton, there are two alternative solutions to this:

A. Because the last connection would be used by default for those tables which does not specify a database connection explicitly in doctrine schema files, we can change the sequence of the database connections in the databases.yml to have our "default" database.

B. Explicitly set the connection property on all schema definitions, including those from plugins (we can take advantage of the cascading configuration feature of Symfony, by simply define each tables' connection property from each plugins).

 

Code - YMLPlain Text
 
  1. // config/doctrine/schema.yml     
  2.     
  3. # #     
  4. # some configurations     
  5. # ...     
  6. # #  
  7.      
  8. sfGuardGroup:     
  9.   connection: my_db   
  10.     
  11. sfGuardPermission:     
  12.   connection: my_db   
  13.     
  14. sfGuardGroupPermission:     
  15.   connection: my_db   
  16.     
  17. sfGuardUser:     
  18.   connection: my_db   
  19.     
  20. sfGuardUserPermission:     
  21.   connection: my_db   
  22.     
  23. sfGuardUserGroup:     
  24.   connection: my_db   
  25.     
  26. sfGuardRememberKey:     
  27.   connection: my_db   

I'd like to use solution B since it looks clearer. Everything went fine until I created a fixture file for the `sf_guard_user` table:

Code - YMLPlain Text
 
  1. // data/fixtures/fixtures.yml  
  2.  
  3. sfGuardUser:    
  4.   sgu_admin:    
  5.     username:       admin    
  6.     password:       admin    
  7.     is_super_admin: true   

The doctrine:data-load task will try to delete and insert the data defined above to the "db_to_read" connection, which is defined as the last connection. But the fixture data for the `people` table is OK. That's weird!

Since I don't have a better solution for this "default database connection" issue now, I have no choice but to define the one at the end in databases.yml, which I want the sfDoctrineGuardPlugin to use.

 

Work with readonly databases in Symfony

After the changes caused by the reasons illustrated above, I now have the following database connection configuration:

Code - YMLPlain Text
 
  1. // config/databases.yml  
  2.    
  3. all:    
  4.   db_to_read:    
  5.     class: sfDoctrineDatabase    
  6.     param:    
  7.       dsn:      mysql:host=localhost;dbname=db_to_read   
  8.       username: root   
  9.                    
  10.   my_db:    
  11.     class: sfDoctrineDatabase    
  12.     param:    
  13.       dsn:      mysql:host=localhost;dbname=my_db   
  14.       username: root 

This is still not what exactly I want. Because when I run the doctrine:build --all --and-load task, all the data in `db_to_read` will also be DELETED!!!

Then I tried to create a database user with only the "select" privilege just for the connection of the`db_to_read` table, and run that task again. Perfect this time!

So, to prevent the doctrine:build --all --and-load  task from modifying the structure and data of a specific database, we can supply the connection of it a user with few privileges (only "select" in my case).

But later I found that there's still a drawback for this simple solution: if I have one or more table set to use the readonly database, then an insufficient privilege error will occur when running the doctrine:build --all --and-load task. This is because the doctrine:insert-sql task trys to create table into the readonly table with readonly privilege. To avoid this, I don't have a better solution so far rather than to change the SQL script manually.

Here's the final configurations of the databases.yml.

Code - YMLPlain Text
 
  1. // config/databases.yml  
  2.    
  3. all:    
  4.   db_to_read:    
  5.     class: sfDoctrineDatabase    
  6.     param:    
  7.       dsn:      mysql:host=localhost;dbname=db_to_read   
  8.       username: readonly  
  9.       password: readonly  
  10.                    
  11.   my_db:    
  12.     class: sfDoctrineDatabase    
  13.     param:    
  14.       dsn:      mysql:host=localhost;dbname=my_db   
  15.       username: root  

 

References:

[1] http://forum.symfony-project.org/index.php/m/92203/

[2] http://osdir.com/ml/symfony-users/2010-04/msg00319.html