Temporary Connection to External Database with Symfony/Doctrine

Question

Summary:

I want to be able to run a query against an external database to grab some needed data at the time the user logs in. I don't want this site to do anything else with the external database. Long term, it may need to be able to push data back, but specifically, I don't want symfony to try to build the schema for the external db, it should just leave it alone and allow a connection from time to time.

Details:

I'm trying to create a temporary connection to a database of another symfony application and I can't seem to figure out how to do it.

I have an existing symfony site that is set up and running. I'm trying to create a sort of management system for users of this main site. The management system will have separate deployments for each users that opts in to it, so it will also have its own database associated with it. However, this management systems needs access to 2 or 3 tables from the main sites system.

I've tried adding separate entries in databases.yml in the management system to create connections to both databases, however whenever I build all, it wants to put my schema in both databases.

The best idea I have come up with is placing connection: management in all my tables for the management system, and also place connection: main_site in all the tables from the main site. However, that would require me to maintain the yml files both in the management system and on the main site to make sure they stayed current with each other.

I hope that was even slightly clear.

Thanks for the help :D

Answer 1

For a temporary connection without the ability to use DQL on the remote site, just open the connection via Doctrine_Manager:

$conn = Doctrine_Manager::getInstance()->openConnection('mysql://username:password@localhost/database', 'connection 2', false);

Make sure the 3rd argument is false so that it doesn't become the current connection. You can then run queries on the connection using PDO:

$conn->exec('SELECT * FROM table WHERE conditon = ?', array('value'));

If you want to be able to use DQL and the remote schema, you'll have to do it the way you outlined: define two connections and maintain the schema in both places (your version control should be able to handle this).