[Dbix-class] Branch for supporting column attributes checked in

[John Napiorkowski]

--- Matt S Trout <dbix-class at trout.me.uk> wrote:

> John Napiorkowski wrote:
> > Hi,
> > 
> > I checked in a branch off of current under
> > branches/DBIx-Class/param_bind which alters how
> SQL
> > statements are executed so that you can specify
> bind
> > parameters in your schema classes.
> > 
> > This is sorely needed by people trying to get
> > Postgresql BYTEA and Oracle BLOB columns to work
> > without having to hack around.
> > 
> > The summary of the problem is that typically when
> you
> > bind a value to a placeholder in your SQL
> statement,
> > that value gets treated like a VARCHAR.  For some
> > database and column types this is a problem,
> > particularly if the column is for a binary type. 
> > Typical issues are that the value will get
> truncated
> > at some length, or the sql statement will just
> fail.
> > 
> > When using straight DBI, people with these kinds
> of
> > problems can bind the column type as a hashref:
> > 
> > $sth->bind_param(1, 'value', { pg_type =>
> > DBD::Pg::PG_BYTEA });
> > 
> > This branch makes some key alterations to support
> this
> > in DBIx::Class.  You can add the required
> attributes
> > in you Schema:
> > 
> > __PACKAGE__->add_columns(
> >   "media", { 
> >   
> >     data_type => "bytea", 
> >     default_value => undef, 
> >     is_nullable => 1, 
> >     bind_attributes => { 
> >       pg_type => DBD::Pg::PG_BYTEA },
> >   },
> > );
> > 
> > If you have need for this please check it out and
> see
> > if it works for you.  This currently solves my
> > particular itch as well as passed all my other
> tests,
> > but I am hoping to get feedback on the best way to
> > make this suitable for wider consumption.
> > 
> > The biggest change I've introduced is that
> > SQL::Abstract is initialised with
> > "bindtype=>'columns'", which means that the array
> of
> > binds returned will be an arrayref of the column
> name
> > and the value instead of just an array of values. 
> So
> > if you are using the SQL::Abstract inside of
> > ..Storage::DBI then this change will affect you. 
> I
> > think I got all the places but I might have missed
> > something.
> > 
> > In order to get the actual bind attributes into
> the
> > storage object I had to mess with
> ..ResultSet->update
> > and ..Row->update|insert.
> 
> That's horrific. When we were discussing this I said
> I thought that the 
> $source object was passed and that if it wasn't, it
> should be. Turns out it 
> isn't, so the solution is that it should be - i.e.
> in Row->update
> 
>    my $rows = $self->result_source->storage->update(
>                 $self->result_source->from,
> \%to_update, $ident_cond);
> 
> would become
> 
>    my $rows = $self->result_source->storage->update(
>                 $self->result_source, \%to_update,
> $ident_cond);
>

Yes, that would be better, particularly if we ever
need the source object for other stuff.  This is just
my first set of changes to the code so I was trying to
be cautious :)

> Also, I see absolutely no reason to pollute the
> column_info with DB-specific 
> crud - why can't Storage::DBI::Pg detect 
> $source->column_info($col)->{data_type} being
> 'bytea' and DWIM?

I'd like to have it work this way as well, but it was
mentioned several times to make it part of the column
info.  I also don't like the idea of database specific
stuff there, since that is supposed to be cross
database.  I have to check and see what the convention
is for the dtabases that need it.  Postgresql uses {
pg_type => PG_[column type]...} and oracle uses {
ora_type => ORA_[column type].  I'll check to see what
the other databases are doing.

Thanks for the feedback,  
John

> 
> -- 
>       Matt S Trout       Offering custom
> development, consultancy and support
>    Technical Director    contracts for Catalyst,
> DBIx::Class and BAST. Contact
> Shadowcat Systems Ltd.  mst (at)
> shadowcatsystems.co.uk for more information
> 
> + Help us build a better perl ORM:
> http://dbix-class.shadowcatsystems.co.uk/ +
> 
> _______________________________________________
> List:
>
http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN:
>
http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive:
>
http://www.mail-archive.com/dbix-class@lists.rawmode.org/
> 



 
____________________________________________________________________________________
Sponsored Link

Don't quit your job - take classes online
www.Classesusa.com