Column name based filter

Column name based filter

DBIx::Custom have a feature to filter query value based on column name. You can use filter in both sending data and receiving data.

Register filter

At first, you register filter by register_filter method. Let's create Base64 filter.

use MIME::Base64 qw/encode_base64 decode_base64/;

$dbi->register_filter(
  encode_base64 => sub { encode_base64($_[0]) },
  decode_base64 => sub { decode_base64($_[0]) }
);

encode_base64 is used when data is send to database to convert binary data to base64 format data. decode_base64 is used when data is receive from database to convert base64 format data to binary data.

Note that register_filter only provide the mechanism to call filter by name. Filter is off until filter option or attribute is used.

Sending filter

Let's use the sending filter.

For example, book table have id and image column. image is binary data. you want to save this binary data as base64 format. Use filter option. this is easy.

$dbi->insert(
  {id => 1, image => $image},
  table => 'book',
  filter => {image => 'encode_base64'}
);

image value is sended to database as base64 format. filter option is also available in update, delete, select, and execute method.

filter option can receive code reference directory.

filter => {image => sub { encode_base64($_[0]) }}

If you want to filter multiple column values, use array reference.

filter => [['image1', 'image2'] => 'encode_base64'}]

Caution when using both id option and filter option

In many cases id option's value is simple value or string , so filtering is usually no needed. but i write some cautions in specification.

In insert method, you can use the column name which is specified in primary_key option.

$dbi->insert(
  table => 'book',
  id => 1,
  primary_key => 'book_id',
  filter => {book_id => sub { ... }}
);

In update, delete, select, this is a little different, because internally full-qualified column name is used. so the colun name which is specified in filter option must be full-qualified column name.

$dbi->delete(
  table => 'book',
  id => 1,
  primary_key => 'book_id',
  filter => {'book.book_id' => sub { ... }}
);

Receiving filter

select method return DBIx::Custom::Result object. DBIx::Custom::Result object have filter attribute.

Filtering is executed when row is fetched.

my $result = $dbi->select(...);
$result->filter({image => 'decode_base64'});
my $rows = $result->all;

In this case, I use "all" in DBIx::Custom::Resut. but filtering is executed in all methods for fetching such as "fetch" in DBIx::Custom::Result.

You can use code reference or array reference as filter.

$result->filter({image => sub { decode_base64($_[0]) }});
$resul->filter([['image1', 'image2'] => 'decode_base64']);