Yii Framework Forum: Postgresql schemas not possible - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

Postgresql schemas not possible Rate Topic: -----

#1 User is offline   christian 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 18
  • Joined: 20-May 09

Posted 20 May 2009 - 12:45 PM

Hi!

I'm testing Yii to see it's benefits. I like yii a lot but when it comes to Postgresql connections I have some problems.

My db model uses different schemas for holding different data, but when I tried to inherit PgSqlSchema I noticed that schema was a constant 'DEFAULT_SCHEMA'. Is there any way to use Postgresql schemas extending PgsqlSchema class without lot of function rewriting? I need to do something like "SET search_path 'schema1,schema2,schema3'"

Thank you!
0

#2 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,900
  • Joined: 04-October 08
  • Location:DC, USA

Posted 20 May 2009 - 01:49 PM

Why would you need to extend CPgsqlSchema? If you are using ActiveRecord or Yii DAO, you can prefix the schema name to the table name.
0

#3 User is offline   christian 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 18
  • Joined: 20-May 09

Posted 20 May 2009 - 02:40 PM

Thanks!!!


0

#4 User is offline   christian 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 18
  • Joined: 20-May 09

Posted 27 May 2009 - 06:45 PM

I've used schema.table, but I still have problems with create rows in the tables.

Postgresql uses sequences for adding auto increment values, but it tries to find the sequence in public schema, not in the schema the table is.

Is there a way I can set the schema with the command "set search_path 'schema'"?.

Example:
schema 'schema'

table users
id NOT NULL nextval(('users_seq'::text)::regclass) PRIMARY KEY,
name VARCHAR(100),
passwd VARCHAR(100)

sequence users_seq;

When it tries to add a row it throws an error: relation users_seq does not exists.

best
0

#5 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,900
  • Joined: 04-October 08
  • Location:DC, USA

Posted 27 May 2009 - 07:55 PM

So your sequence relies on search_path? You can extend CDbConnection and override its init() method. In the method, after calling the parent's init(), you can execute this command to set the search_path.

In your app config, when you configure 'db', you need to specify its 'class' option to point to your new DB connection class.
0

#6 User is offline   christian 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 18
  • Joined: 20-May 09

Posted 29 May 2009 - 10:45 PM

OK! I did it this way:

Added a file in protected/db/MyDbConnection.php with:
class MyDbConnection extends CDbConnection {
  protected function initConnection($pdo)
  {
    parent::initConnection($pdo);     
    $stmt=$pdo->prepare("set search_path to myscheme");
    $stmt->execute();     
  }
}

Also each table in its model is named as myscheme.tablename.

And in config/main.php the db connection uses this class:
'class'=>'application.db.MyDbConnection',

And it worked fine!  ;D

Hope this helps anyone who has the same issue.

I only wonder why do I have to set myscheme.tablename if the seach_path has been set. I know it's because of the AR, but maybe it can be improved? 

Best!

0

#7 User is offline   ricardograna 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 376
  • Joined: 31-March 09
  • Location:Manaus/AM - Brazil

Posted 29 May 2009 - 10:57 PM

It is because Postgres-AR defaults to public schema. If it is not in public schema, you have to especify the schema.

But yes, it could be improved.
0

#8 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,900
  • Joined: 04-October 08
  • Location:DC, USA

Posted 29 May 2009 - 11:09 PM

It's because you are relying on search path to look for schema name. Yii has no way to know the exact schema name if the squence name is given without schema name.
0

#9 User is offline   piwer 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 04-April 09

Posted 23 February 2010 - 11:34 AM

I`m sorry for digging out an old topic, but i have problem similar to it`s autor one. How can i set schema to work with? I have my db in other than 'public' schema, and i couldnt find where to set it. For my purpose i had to set DEFAULT_SCHEMA in CPgsqlSchema to be my schema. How to do it proper way?

edit:
changing DEFAULT_SCHEMAdid nothing, cause selects still use public schema
0

#10 User is offline   sergiorosales 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 11
  • Joined: 19-September 10
  • Location:México

Posted 11 April 2011 - 11:09 PM

In Postgres de default search path is "$user", public
If you can create a role with the same name as the schema you would not have problems, the other way is to change the postgres configuration but its not good idea.

In Yii it would be nice to have another conection parameter like:

'schema'=>'myschema'

I used the Qiang recomendation and worked fine.

in protected/components/MydbConection.php
class MydbConection extends CDbConnection {
  protected function initConnection($pdo)
  {
    parent::initConnection($pdo);
    $stmt=$pdo->prepare("set search_path to yourschema, public");
    $stmt->execute();
  }
}


and
'db'=>array(
                        'class'=> 'MydbConection',
                        'connectionString' => 'pgsql:host=localhost;port=5432;dbname=yourdb',
			'username' => 'youruser',
			'password' => 'yourpassd',
                       
	
		),

in config/main.php
as christian did
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users