Development

HowToPatchSymfony1.1ToThePostgreSQLWorkingMultipleSchemas

You must first sign up to be able to contribute.

Version 12 (modified by angel1975, 7 years ago)
--

symfony 1.1

Propel ORM

By default the symfony can not work with multiple schemas in DB's in PostgreSQL.

I created a patch that changes the following files:

/usr/share/php/symfony/plugins/sfPropelPlugin/lib/vendor/creole/drivers/pgsql/metadata/PgSQLDatabaseInfo.php
/usr/share/php/symfony/plugins/sfPropelPlugin/lib/vendor/creole/drivers/pgsql/PgSQLConnection.php
/usr/share/php/symfony/util/sfContext.class.php
/usr/share/php/symfony/plugins/sfPropelPlugin/lib/vendor/propel/Propel.php

Changes made in the archives:

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

@@ -30,6 +30,16 @@
  */
 class PgSQLDatabaseInfo extends DatabaseInfo {
 
+    /** 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'];
+    }
+
     /**
      * @throws SQLException
      * @return void
@@ -55,14 +65,17 @@
         pg_free_result ($result);
         $result = null;
 
-        $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");
+        $result = pg_query($this->conn->getResource(),"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");
 
         if (!$result) {
             throw new SQLException("Could not list tables", pg_last_error($this->dblink));
@@ -71,8 +84,8 @@
         while ($row = pg_fetch_assoc($result)) {
             $this->tables[strtoupper($row['relname'])] = new PgSQLTableInfo($this, $row['relname'], $version, $row['oid']);
         }
-		
-		$this->tablesLoaded = true;
+
+    $this->tablesLoaded = true;
     }
 
     /**
@@ -83,18 +96,19 @@
      */
     protected function initSequences()
     {
-     
-	 	$this->sequences = array();
-		   
-        $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 relname");
-														
+
+    $this->sequences = array();
+
+        $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");
+
         if (!$result) {
             throw new SQLException("Could not list sequences", pg_last_error($this->dblink));
         }

/usr/share/php/symfony/plugins/sfPropelPlugin/lib/vendor/creole/drivers/pgsql/PgSQLConnection.php

@@ -104,7 +104,10 @@
 			$cleanconnstr = preg_replace('/password=\'.*?\'($|\s)/', 'password=\'*********\'', $connstr);
             throw new SQLException('Could not connect', $php_errormsg, $cleanconnstr);
         }
-        
+
+        if(!empty($dsninfo['schema']))
+          pg_query('SET search_path TO '.$dsninfo['schema'].';');
+
         $this->dblink = $conn;        
     }
     

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

@@ -275,7 +275,7 @@
 
   public function retrieveObjects($class, $peerMethod)
   {
-    $retrievingClass = 'sf'.ucfirst(sfConfig::get('sf_orm', 'propel')).'DataRetriever';
+    $retrievingClass = 'sfPropelDataRetriever';
 
     return call_user_func(array($retrievingClass, 'retrieveObjects'), $class, $peerMethod);
   }

/usr/share/php/symfony/plugins/sfPropelPlugin/lib/vendor/propel/Propel.php

@@ -485,6 +485,12 @@
 
 			try {
 				$con = Creole::getConnection($dsn);
+				
+				if ($dsn['phptype'] == 'pgsql') {
+				  $sql = "SET SEARCH_PATH = 'public'";
+				  $stmt = $con->prepareStatement($sql);
+				  $stmt->executeQuery();
+        }
 			} catch (SQLException $e) {
 				throw new PropelException($e);
 			}

To apply the patch:

cd /usr/share/php/symfony


sudo patch -p1 < /home/patrick/my_project/patch/sf_postgresql_working_multiple_schemas_1.1.patch

Adding the name of the schemas from your database:

Open the file /usr/share/php/symfony/plugins/sfPropelPlugin/lib/vendor/propel/Propel.php

Added the code below, the names of the schemas from your database:

$sql = "SET SEARCH_PATH = 'public'";

For example:

$sql = "SET SEARCH_PATH = 'public','cms','review','system','supplier','financial'";

This patch only works with Propel ORM.

Doctrine ORM

To do work on Doctrine ORM is very simple.

You must add the name of schema in the attribute "tableName" in mapping Doctrine.

For example:

Change from audience to review.audience.
Change from variable to system.variable.
Change from attachment to cms.attachment.

Where review, system, cms are the names of the schemas in the database.

Audience:
  tableName: review.audience
  columns:
    id:    {type: integer(4), notnull: true, primary: true, autoincrement: true}
    name:  {type: string(100), notnull: true}
    weight:  {type: integer(4), notnull: true}

Variable:
  tableName: system.variable
  templates: [ Doctrine_Template_Timestampable ]
  columns:
    name:       {type: string(128), notnull: true, primary: true}
    value:      {type: text, notnull: true}
    created_at: {type: timestamp, notnull: true}
    updated_at: {type: timestamp, notnull: true}

Attachment:
  tableName: cms.attachment
  columns:
    file_id:     {type: integer(4), notnull: true, primary: true}
    message_id:  {type: integer(4), notnull: true, primary: true}
  relations:
    DFile:
      local: file_id
    DMessage:
      local: message_id

symfony 1.0

http://www.symfony-project.org/forum/index.php/m/56149/ dissertation help funeral program templates

Attachments