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);
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?
--
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/[email protected]/
