symfony framework forum: Installation and setup => symfony working with multiple schemas and views on PostgreSQL

By default the symfony not work with multiple schemas in bases in PostgreSQL. I do not understand why it has not been added this functionality still in symfony, is not something as complex to do.

To achieve this we need to change some methods of CREOLE, CREOLE is responsible for making the abstraction of the database in symfony, we must also change some things in the part of utilities and control of the CORE symfony.

I understand that as good developers should not tamper with the CORE of the framework, but in this case there is no other solution, it is necessary to make those changes or not work with the PostgreSQL DBMS.

The files that we need change are:

/usr/share/php/symfony/vendor/creole/drivers/pgsql/metadata/ PgSQLDatabaseInfo.php

/usr/share/php/symfony/vendor/creole/drivers/pgsql/PgSQLConn ection.php

/usr/share/php/symfony/util/sfContext.class.php symfony/util/sfContext.class.php

/usr/share/php/symfony/controller/sfRouting.class.php

Developed a new patch to make these changes, this patch is working on top of the latest version of symfony 1.0.10. The patch already performing the task to modify and add the details necessary for the symfony to work with multiple schemas in PostgreSQL.

The patch is attached.

To run the patch:

cd /usr/share/php/symfony
sudo patch -p1 < /var/www/your_symfony_project/batch/symfonyMultipleSchemasPatch-1.0.10.patch

For the purpose of knowledge.
Below the changes made in each file.

/usr/share/php/symfony/vendor/creole/drivers/pgsql/metadata/ PgSQLDatabaseInfo.php

*** Added the method __construct ***

    /** Database schema selection */
    protected $dbschemas = null;
    public function __construct(Connection $conn, $vendorInfo = array())
    {
      parent::__construct($conn,$vendorInfo);
      $dsn = $this->conn->getDSN();
      $this->dbschemas = $dsn['schema'];
    }

*** Replaced ***

        $result = pg_query($this->conn->getResource(), "SELECT c.oid, 														
case when n.nspname='public' then c.relname else n.nspname||'.'||c.relname end as relname 											
			FROM pg_class c join pg_namespace n on (c.relnamespace=n.oid)												
		WHERE c.relkind = 'r' AND n.nspname NOT IN ('information_schema','pg_catalog')											
			  AND n.nspname NOT LIKE 'pg_temp%' AND n.nspname NOT LIKE 'pg_toast%'

	ORDER BY relname");

*** By ***

        $sql = "SELECT oid, relname FROM pg_class WHERE (relkind = 'r' OR relkind = 'v')
              AND relnamespace in (
          SELECT
            oid
          FROM
            pg_namespace
          WHERE
              nspname NOT IN ('information_schema','pg_catalog')
              AND nspname NOT LIKE 'pg_temp%' ".
              (isset($this->dbschemas) ? "AND nspname IN (".$this->dbschemas.")" : "" ). "
              AND nspname NOT LIKE 'pg_toast%') ORDER BY relname";

        $result = pg_query($this->conn->getResource(),$sql);

*** Replaced ***

        $result = pg_query($this->conn->getResource(), "SELECT c.oid, 				
										
case when n.nspname='public' then c.relname else n.nspname||'.'||c.relname end as relname 											
			FROM pg_class c join pg_namespace n on (c.relnamespace=n.oid)												
		WHERE c.relkind = 'S' AND n.nspname NOT IN ('information_schema','pg_catalog')											
			  AND n.nspname NOT LIKE 'pg_temp%' AND n.nspname NOT LIKE 'pg_toast%' 

       ORDER BY name");

*** By ***

        $result = pg_query($this->conn->getResource(), "SELECT oid, relname FROM pg_class
                    WHERE relkind = 'S' AND relnamespace = (SELECT oid
                    FROM pg_namespace
                    WHERE
                         nspname NOT IN ('information_schema','pg_catalog')
                         AND nspname NOT LIKE 'pg_temp%'
                         AND nspname NOT LIKE 'pg_toast%'
                    LIMIT 1)
                    ORDER BY relname");

/usr/share/php/symfony/vendor/creole/drivers/pgsql/PgSQLConn ection.php

*** After the code ***

			// hide the password from connstr
			$cleanconnstr = preg_replace('/password=\'.*?\'($|\s)/', 'password=\'*********\'', $connstr);
            throw new SQLException('Could not connect', $php_errormsg, $cleanconnstr);
        }

*** Is added ***

        if(!empty($dsninfo['schema']))
           pg_query('SET search_path TO '.$dsninfo['schema'].';');

/usr/share/php/symfony/util/sfContext.class.php

*** Replaced ***

    $retrievingClass = 'sf'.ucfirst(sfConfig::get('sf_orm', 'propel')).'DataRetriever';

*** By ***

    $retrievingClass = 'sfPropelDataRetriever';

/usr/share/php/symfony/controller/sfRouting.class.php

*** Replaced ***

        // we must match all requirements for rule
        foreach ($requirements as $req_param => $req_regexp)
        {
          if (!preg_match('/'.str_replace('/', '\\/', $req_regexp).'/', $tparams[$req_param]))
          {
            continue 2;
          }
        }

*** By ***

        // we must match all requirements for rule
        foreach ($requirements as $req_param => $req_regexp)
        {
          $req_regexp = '/'.str_replace('/', '\\/', $req_regexp).'/'; 
          if (!@preg_match($req_regexp, $tparams[$req_param]))  {
	    continue 2;
          }
          if (!preg_match("($req_regexp)?", $tparams[$req_param])) {
            continue 2;
          }
        }