[Dbix-class] A problem with Postgres schemas

[Rolf Schaufelberger]

On Friday 29 December 2006 15:29, John Napiorkowski wrote:
> --- Rolf Schaufelberger <rs at plusw.de> wrote:
> > On Thursday 28 December 2006 22:06, Kevin Esteb
> >
> > wrote:
> > > I new to DBIx::Class, so please bear with me.
> > >
> > > I am running RHEL4 and have a postgres database
> >
> > with a schema named
> >
> > > "analog". The DBIx::Class modules are the latest
> >
> > and greatest from CPAN,
> >
> > > so is the DBD and the DBI for postgres. When I run
> >
> > the below code, I get
> >
> > > this error:
> >
> > I'm using the "on_connect_do" parameter in my
> > connection setup :
> >
> >  {on_connect_do => ['SET search_path TO
> > analog,public']}
> >
> > and can use my schemas as "normal".
>
> Do you have any trouble with this if the tables in the
> different schemas have the same name?  Or can you just
> disambiguate as normal with schema.table in those
> cases?
>
> Just curious.
> John
>

First, I'm not using DBIx::Class in production currently, just for migrating a 
Class::DBI App, but during testing I had no problems at all and I use (more 
or less)  the same way with Class::DBI for years. Since it acts directly on 
the database I can see no reason, why it should behave other than it does 
when using psql or any other tool, so when you have two tables with the same 
name it searches in the order listed in the search_path. But you can use 
schema.table as well, so in this example , if we had a table analog.users and 
public.users and you  make "select * from users" (or 
MySchema->Users->search() ) , anlaog.users would be used. 
I'm using schemas  in a different way: I have two (or more) schemas with the 
same tables but another content, and I have two( or more) applications 
(virtual hosts) , and each one has "his" schema listed in his search_path, 
while all other common tables are in other, common schemas. So each app may 
have its own users table, while the rest of the database and my Class::DBI 
code (still !) is identical for both. Works fine.
Still curious ?

Rolf