Retrieve database names / dsn info from symfony / doctrine databases.yml

I have a complex data structure that means I have not been able to fully use relationships defined in schema.yml for my model.

Now I have some reporting queries which need to use tables from several databases.

Because of the complexity of the model these queries are written in raw sql. So I need to use the database names in the query in order to select the 开发者_StackOverflow社区correct databases for various tables.

I am using symfony 1.4 and Doctrine 1.2

How can I extract the database names for my current environment (Prod, Dev, Test etc) from databases.yml in order to use them in the raw sql queries?

If you are using symfony with Doctrine, you probably have generated all models (if you weren't forced to use raw SQL, I suppose that you'd do the query with Query Builder). All model classes have information about related table in their code, so you must simply create instances of them and retrieve table names from the inside. See this:

abstract class BaseModel extends sfDoctrineRecord
    {
    public function setTableDefinition()
        {
        $this->setTableName('models');
        $this->hasColumn(/* several definitions */);

        $this->option('collate', 'utf8_general_ci');
        $this->option('charset', 'utf8');
        $this->option('type', 'InnoDB');
        }
    }

There will be a class:

class Model extends BaseModel {}

So you need to:

$model = new Model();
$tableName = $model->getTable()->getTableName();

And then write your query using retrieved information.

Im sure there must be a better way, but turns out you can get the DSN from sfDatabaseManager. So until I find a proper way, I've extended sfDatabaseManager to add the following getDsn method

class sfDatabaseManagerExt extends sfDatabaseManager
{
  public function getDsn($conn)
  {  
    $db = $this->getDatabase($conn);
    $dsn = $db->getParameter('dsn');
    return $dsn;
  }


}

Then in the model where I need to get the database name for a specific connection and environment:

$appConfig= ProjectConfiguration::getApplicationConfiguration(sfConfig::get('sf_app'),      sfConfig::get('sf_env'), false);     
$dbManager= new sfDatabaseManagerExt($appConfig);      
$dsn=$dbManager->getDsn('doctrine');

Just need to explode / regex on the dsn then to get the dbname.

I would LOVE it if someone would post to tell me how rubbish this is, but of course only if they have a better solution! I have looked, and looked and looked....

Achieved the same result with:

$oCurrentConnection = Doctrine_Manager::getInstance()->getCurrentConnection();
$sdsn = $oCurrentConnection->getOption('dsn');

I would like to add that if we are going to send a raw sql query, in a multiple database project, the connection must be the one specific to the database and module that we would like to send the sql.

if (!isset($sModuleName))
{
    $sModuleName = sfContext::getInstance()->getModuleName();
}
$oCurrentConnection = Doctrine_Manager::getInstance()->getConnectionForComponent($sModuleName);
$results = $oCurrentConnection->fetchAssoc($scomandoSQL);

I think its a very common problem and this code solves most cases.