How to control which connection is selected from the database.yml in a symfony1.4 task

Question

(See my edit below for a better question) How do I control which connection is selected (from the right environment section in the database.yml) in a symfony1.4 task using just a generic Doctrine_Query::create() to create the query?

I'm using a database.yml that looks something like this:

prod:
  doctrine:
    class: sfDoctrineDatabase
    param:
      dsn: mysql://some:pass@domain:port/database

  log:
    class: sfDoctrineDatabase
    param:
      dsn: mysql://some:pass@domain:port/database
  auth:
    class: sfDoctrineDatabase
    param:
      dsn: mysql://some:pass@domain:port/database

dev:
  doctrine:
    class: sfDoctrineDatabase
    param:
      dsn: mysql://some:otherpass@domain:port/database

  log:
    class: sfDoctrineDatabase
    param:
      dsn: mysql://some:otherpass@domain:port/database

  auth:
    class: sfDoctrineDatabase
    param:
      dsn: mysql://some:otherpass@domain:port/database

And I want to be able to control which one of those database definitions is used when calling something like:

$query = Doctrine_Query::create()
        ->select('*')
        ->from('Products p')
        ->where('p.id = ?', $productID)
        ->limit(1);

$OfpFlPr = $query->execute()->getFirst();

At the moment I'm not able to set the connection like so $query = Doctrine_Query::create($conn);.

Any help would be greatly appreciated!

---

EDIT:

I have lots of code deeper in the software where Doctrine_Query::create() is used (without the connection argument). It seems to select the right environment and connection through web requests. But I can't figure out how it does this, so I can make my CLI commands work the same way (they don't select the right connection and environment at the moment). That's why I need to know how to control which connection is 'automaticly' used (selected by default).

Question:

So I guess in conclusion my question would be:

How can I control which connection is selected by default in lower level code which uses Doctrine_Query::create() while the code is being executed as a symfony CLI command?

Answer 1

$query = Doctrine_Query::create($doctrineManager->getConnection('doctrine'))
    ->select('*')
    ->from('Products p')
    ->where('p.id = ?', $productID)
    ->limit(1);

should work. Depending on where you select your Product from, the parameter may be 'doctrine', 'log', or 'auth'.

Could you elaborate why you are "not able to set the connection" this way?

EDIT:

So if I get it right, you like to specify environment in cli command, to use dsn connection string from the right section in database.yml. You can use env option for your cli commands to do so. You may need to add something like

$this->addOption('env', null, sfCommandOption::PARAMETER_OPTIONAL, 'Specify environment', 'prod'); 

to the task configuration.

Answer 2

By default, task is generated with this code:

$databaseManager = new sfDatabaseManager($this->configuration);
$connection = $databaseManager->getDatabase($options['connection'])->getConnection();

This initializes just the one connection, and does not initialize context. Instead, replace it with this:

sfContext::createInstance(new frontendConfiguration($options['env'], true));

This will create context, using application option of the task. You likely want to set default for it, change the task's configure() method to have:

    $this->addOptions(array(
        new sfCommandOption('application', null, sfCommandOption::PARAMETER_REQUIRED, 'The application name', 'frontend'),
        // ...
    ));

Notice I added frontend to initialize frontend app. You can also adjust the default for env option.

Answer 3

Cause of my actual problem

After some deep debugging using a cleanly created task I finally found my culprit. Symfony 1.4 seems to run the configure method of each task in the tasks directory before actually running the intended task.

Unfortunately some uninformed prankster (ex colleague^*) included some hard coded context initialization into one of these methods like this:

// inside a task
protected function configure() {
    // context was initialized
    $configuration    = ProjectConfiguration::getApplicationConfiguration('app_name', 'prod', true);
    $context          = sfContext::createInstance($configuration);

    // so the following was available in the configure method
    $save_path = sfConfig::get('sf_app_config_dir');

    // rest of configure method implementation 
    // ...
}

This screwed up the database settings for all cronjobs on all other environments except for the production environment (luckily).

I managed to work around it by doing something like this:

protected function configure() {
    $configuration    = ProjectConfiguration::getApplicationConfiguration('app_name', 'prod', true);
    // removed the context creation

    $configuration->activate(); // this makes the sfConfig::get() work
    $save_path = sfConfig::get('sf_app_config_dir');

    // rest of configure method implementation 
    // ...
}

Answer to my question

Also I found that when it comes to controlling which database connection is used by Doctrine_Query::create() you could have some control by using something like this on a higher level function:

// for making sure the 'auth' database settings are used as a default
Doctrine_Manager::getInstance()->setCurrentConnection('auth');

However, this doesn't have any power over what 'environment section' is used to select the right configuration/dsn for the database. This is done by doing something like this:

// somewhere in the execute method
$env              = 'dev'; // the environment section name; 
$configuration    = ProjectConfiguration::getApplicationConfiguration('app_name', $env, true);
$context          = sfContext::createInstance($configuration);

As also correctly hinted in the answers of Alex Blex and Marek. Using a task 'option' for it to make it support multiple environments as they suggest makes sense.

_{*: My ex colleague which I can hardly be mad at because of the unlikeliness and counter intuitive nature of this problem ;)}