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!
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.
Thanks!!!
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
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.
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! 
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!
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.
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.
Im sorry for digging out an old topic, but i have problem similar to its 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
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