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
Attachments
- sf_postgresql_working_multiple_schemas_1.1.patch (5.3 kB) - added by patrickespake on 07/18/08 19:38:33.
