2006-03-27

Class::DBI::Sweet::Pie

　気が向いたので集約関数を追加するClass::DBI::Sweet用のプラグインを作ってみました。

　Class::DBI::Plugin::AggregateFunctionとか作ってもいいのかもしれません。

  package MyData::CD;
  use base qw/Class::DBI::Sweet/;
  use Class::DBI::Sweet::Pie;
  __PACKAGE__->mk_aggregate_function('sum');
  __PACKAGE__->mk_aggregate_function( max => 'maximum');

  package main;
  # SELECT MAX(price) FROM __TABLE__
  my $max = MyData::CD->maximum( 'price' );

  # SELECT SUM(price) FROM __TABLE__ WHERE artist = 'foo'
  my $sum = MyData::CD->sum( 'price', artist => 'foo' );

ソースコード

package Class::DBI::Sweet::Pie;
use strict;
use vars qw/$VERSION @EXPORT @EXPORT_OK/;
$VERSION = '0.01';

use Exporter::Lite;
@EXPORT = qw/mk_aggregate_function/;

sub mk_aggregate_function {
    my $class = shift;
    my ($aggregate_func, $alias) = @_;
    $alias ||= $aggregate_func;

    $class->set_sql( "Join_Retrieve_$aggregate_func" => <<__SQL__ );
  SELECT $aggregate_func( %s )
  FROM   %s
  WHERE  %s
__SQL__

    no strict 'refs';
    *{"$class\::$alias"} = sub {
	my $proto = shift;
	my $class = ref($proto) || $proto;
	my $aggregate_column = shift;

	my ($criteria, $attributes) = $class->_search_args(@_);

	# make sure we take copy of $attribues since it can be reused
	my $sum_attr = { %{$attributes} };

	# no need for LIMIT/OFFSET and ORDER BY in AGGREGATE_FUNC()
	delete @{$sum_attr}{qw( rows offset order_by )};

	my ($sql_parts, $classes, $columns, $values) = $proto->_search( $criteria, $sum_attr );

	my $sql_method = 'sql_' . ($attributes->{sql_method} || 'Join_Retrieve') . "_$aggregate_func";

	my $sth = $class->$sql_method( "me.$aggregate_column", @{%$sql_parts}{qw/ from where /} );

	$class->_bind_param( $sth, $columns );
	return $sth->select_val(@$values);
    }
}

1;
__END__
=head1 NAME

Class::DBI::Sweet::Pie

=head1 SYNOPSYS

  package MyData::CD;
  use base qw/Class::DBI::Sweet/;
  use Class::DBI::Sweet::Pie;
  __PACKAGE__->mk_aggregate_function('sum');
  __PACKAGE__->mk_aggregate_function( max => 'maximum');

  package main;
  my $sum = MyData::CD->sum( 'price' ); 	# SELECT SUM(price) 
  my $max = MyData::CD->maximum( 'price' );	# SELECT MAX(price) 

=cut

Class::DBI::Plugin::AggregateFunction

　というわけでClass::DBI::Plugin::AggregateFunctionも作りました。

　SQL::Abstractを利用していて、Class::DBI::AbstractSearch風になってます。

　ベースクラスを識別してClass::DBI::Sweetの時は上のを使うようにしたほうがいいのかな、と思いつつ。似てるけど別物だしなぁ……。

ソースコード

package Class::DBI::Plugin::AggregateFunction;
use strict;
use vars qw/$VERSION @EXPORT @EXPORT_OK/;
$VERSION = '0.01';

use Exporter::Lite;
@EXPORT = qw/mk_aggregate_function/;

sub mk_aggregate_function {
    my $class = shift;
    my ($aggregate_func, $alias) = @_;
    $alias ||= $aggregate_func;

    $class->set_sql( "Retrieve_$aggregate_func" => <<__SQL__ );
  SELECT $aggregate_func( %s )
  FROM   __TABLE__
  WHERE  %s
__SQL__

    no strict 'refs';
    *{"$class\::$alias"} = sub {
	my $proto = shift;
	my $class = ref($proto) || $proto;
	my $aggregate_column = shift;

	my $where = (ref $_[0]) ? $_[0]          : { @_ };
	my $attr  = (ref $_[0]) ? $_[1]          : undef;

	my $sql = SQL::Abstract->new(%$attr);
	my($phrase, @bind) = $sql->where($where);
	$phrase =~ s/^\s*WHERE\s*//i;

	my $sql_method = "sql_Retrieve_$aggregate_func";
	my $sth = $class->$sql_method( $aggregate_column, $phrase );

	return $sth->select_val( @bind );
    }
}

1;
__END__
=head1 NAME

Class::DBI::Plugin::AggregateFunction

=head1 SYNOPSYS

  package MyData::CD;
  use base qw/Class::DBI/;
  use Class::DBI::Plugin::AggregateFunction;
  __PACKAGE__->mk_aggregate_function('sum');
  __PACKAGE__->mk_aggregate_function( max => 'maximum');

  package main;
  # SELECT MAX(price) FROM __TABLE__
  my $max = MyData::CD->maximum( 'price' );

  # SELECT SUM(price) FROM __TABLE__ WHERE artist = 'foo'
  my $sum = MyData::CD->sum( 'price', artist => 'foo' );

=head1 DESCRIPTION

　Class::DBIを使ったテーブルで簡単に集約関数が使えるようになります。

=cut