Re: [CDBI] Class::DBI patch

Am Freitag 18 Januar 2008 11:27:07 schrieb Matt S Trout:
> On Thu, Jan 17, 2008 at 02:18:46PM -0500, Edward J. Sabol wrote:
> > Veselin Slavov wrote:
> > > I'm using '*Class::DBI*' in my projects with '*DBD::Pg*' database
> > > driver. When use some tables like this
> > > *__PACKAGE__->table('schema_name.table_name');*
> > > and inserts more than 1 record - inserting failed!
> > > To simulate this problem please see attached file '*simulate.pl*'.
> > > I found solution - *Class_DBI.diff* - and think it will be useful for
> > > other PostgreSQL users also.
> > >
> > > I will be very happy if You apply patch in future version of package.
> >
> > I probably shouldn't speak for Tony, but I can't imagine him accepting
> > such a patch. It's very PostgreSQL-specific.
>
> No it isn't, it's improving Class::DBI's support for the DBI documented
> standard approach of getting the last inserted id.
>
> Stunningly depressing how bad support in the various DBDs is though;
> DBD::Pg didn't used to handle it, so DBIx::Class uses a completely
> different approach (basically "fun with finding sequence names via
> heuristics" ... don't ask :)

This is DBIx::Class specific !!

But the DBIx::Class approach isn't waterproof as well:
I' m using Postgres schemas in a way, that I have some tables in common 
schemas, and some tables in user specific schemas, setting the search_path 
after connect.  So user A sees  table   schemaA.users , while userB sees 
schemaB. users.  Now in my classes I had written 
__PACKAGE__->table ('users');
__PACKAGE__->sequence('users_id_seq');
__PACKAGE__->add_columns ( qw (id ... ));
__PACKAGE__->primary_key('id');

that, is , I set the sequence "before" setting the primary key (maybe it 
should be mentioned in the docs to place it after the primary_key line, or 
labeled as deprecated), and thus it had no effect. Now DBIx::Class tried to 
find out the sequence name, but if you have more tables with the same name it  
just takes the first result which could be right or wrong. Since it uses 
DBI's table_info (or column_info ?)  I can think the only solution would be, 
to "join" it with the search_path in pg_settings or pass the $schema 
parameter as a comma sep. list (like in pg_settings) 
I agree this situation is certainly not very  wide spread, but if you detect 
this error it is hard to find the bug. The problem also shows with modules 
like DBIC::Schema::Loader etc when you are using postgres schemas. The 
support for this feature is generally very week.

-- 
Rolf Schaufelberger