package DBIx::Custom; use strict; use warnings; use base 'Object::Simple'; use Carp 'croak'; use DBI; use DBIx::Custom::Result; use DBIx::Custom::SQLTemplate; use DBIx::Custom::Query; use Encode qw/encode_utf8 decode_utf8/; __PACKAGE__->attr('dbh'); __PACKAGE__->attr([qw/user password data_source/]); __PACKAGE__->attr([qw/default_query_filter default_fetch_filter/]); __PACKAGE__->dual_attr('filters', default => sub { {} }, inherit => 'hash_copy'); __PACKAGE__->register_filter( encode_utf8 => sub { encode_utf8($_[0]) }, decode_utf8 => sub { decode_utf8($_[0]) } ); __PACKAGE__->attr(result_class => 'DBIx::Custom::Result'); __PACKAGE__->attr(sql_template => sub { DBIx::Custom::SQLTemplate->new }); __PACKAGE__->attr(cache => 1); __PACKAGE__->attr(cache_method => sub { sub { my $self = shift; $self->{_cached} ||= {}; if (@_ > 1) { $self->{_cached}{$_[0]} = $_[1] } else { return $self->{_cached}{$_[0]} } } }); sub connect { my $proto = shift; # Create my $self = ref $proto ? $proto : $proto->new(@_); # Information my $data_source = $self->data_source; my $user = $self->user; my $password = $self->password; # Connect my $dbh = eval {DBI->connect( $data_source, $user, $password, { RaiseError => 1, PrintError => 0, AutoCommit => 1, } )}; # Connect error croak $@ if $@; # Database handle $self->dbh($dbh); return $self; } sub register_filter { my $invocant = shift; # Add filter my $filters = ref $_[0] eq 'HASH' ? $_[0] : {@_}; $invocant->filters({%{$invocant->filters}, %$filters}); return $invocant; } our %VALID_INSERT_ARGS = map { $_ => 1 } qw/table param append filter/; sub insert { my ($self, %args) = @_; # Check arguments foreach my $name (keys %args) { croak "\"$name\" is invalid name" unless $VALID_INSERT_ARGS{$name}; } # Arguments my $table = $args{table} || ''; my $param = $args{param} || {}; my $append = $args{append} || ''; my $filter = $args{filter}; # Insert keys my @insert_keys = keys %$param; # Not exists insert keys croak("Key-value pairs for insert must be specified to 'insert' second argument") unless @insert_keys; # Templte for insert my $template = "insert into $table {insert " . join(' ', @insert_keys) . '}'; $template .= " $append" if $append; # Execute query my $ret_val = $self->execute($template, param => $param, filter => $filter); return $ret_val; } our %VALID_UPDATE_ARGS = map { $_ => 1 } qw/table param where append filter allow_update_all/; sub update { my ($self, %args) = @_; # Check arguments foreach my $name (keys %args) { croak "\"$name\" is invalid name" unless $VALID_UPDATE_ARGS{$name}; } # Arguments my $table = $args{table} || ''; my $param = $args{param} || {}; my $where = $args{where} || {}; my $append_statement = $args{append} || ''; my $filter = $args{filter}; my $allow_update_all = $args{allow_update_all}; # Update keys my @update_keys = keys %$param; # Not exists update kyes croak("Key-value pairs for update must be specified to 'update' second argument") unless @update_keys; # Where keys my @where_keys = keys %$where; # Not exists where keys croak("Key-value pairs for where clause must be specified to 'update' third argument") if !@where_keys && !$allow_update_all; # Update clause my $update_clause = '{update ' . join(' ', @update_keys) . '}'; # Where clause my $where_clause = ''; my $new_where = {}; if (@where_keys) { $where_clause = 'where '; foreach my $where_key (@where_keys) { $where_clause .= "{= $where_key} and "; } $where_clause =~ s/ and $//; } # Template for update my $template = "update $table $update_clause $where_clause"; $template .= " $append_statement" if $append_statement; # Rearrange parammeters foreach my $wkey (@where_keys) { if (exists $param->{$wkey}) { $param->{$wkey} = [$param->{$wkey}] unless ref $param->{$wkey} eq 'ARRAY'; push @{$param->{$wkey}}, $where->{$wkey}; } else { $param->{$wkey} = $where->{$wkey}; } } # Execute query my $ret_val = $self->execute($template, param => $param, filter => $filter); return $ret_val; } sub update_all { shift->update(allow_update_all => 1, @_) }; our %VALID_DELETE_ARGS = map { $_ => 1 } qw/table where append filter allow_delete_all/; sub delete { my ($self, %args) = @_; # Check arguments foreach my $name (keys %args) { croak "\"$name\" is invalid name" unless $VALID_DELETE_ARGS{$name}; } # Arguments my $table = $args{table} || ''; my $where = $args{where} || {}; my $append_statement = $args{append}; my $filter = $args{filter}; my $allow_delete_all = $args{allow_delete_all}; # Where keys my @where_keys = keys %$where; # Not exists where keys croak("Key-value pairs for where clause must be specified to 'delete' second argument") if !@where_keys && !$allow_delete_all; # Where clause my $where_clause = ''; if (@where_keys) { $where_clause = 'where '; foreach my $wkey (@where_keys) { $where_clause .= "{= $wkey} and "; } $where_clause =~ s/ and $//; } # Template for delete my $template = "delete from $table $where_clause"; $template .= " $append_statement" if $append_statement; # Execute query my $ret_val = $self->execute($template, param => $where, filter => $filter); return $ret_val; } sub delete_all { shift->delete(allow_delete_all => 1, @_) } our %VALID_SELECT_ARGS = map { $_ => 1 } qw/table column where append relation filter param/; sub select { my ($self, %args) = @_; # Check arguments foreach my $name (keys %args) { croak "\"$name\" is invalid name" unless $VALID_SELECT_ARGS{$name}; } # Arguments my $tables = $args{table} || []; $tables = [$tables] unless ref $tables eq 'ARRAY'; my $columns = $args{column} || []; my $where = $args{where} || {}; my $relation = $args{relation}; my $append = $args{append}; my $filter = $args{filter}; my $param = $args{param} || {}; # SQL template for select statement my $template = 'select '; # Column clause if (@$columns) { foreach my $column (@$columns) { $template .= "$column, "; } $template =~ s/, $/ /; } else { $template .= '* '; } # Table $template .= 'from '; foreach my $table (@$tables) { $template .= "$table, "; } $template =~ s/, $/ /; # Where clause my @where_keys = keys %$where; if (@where_keys) { $template .= 'where '; foreach my $where_key (@where_keys) { $template .= "{= $where_key} and "; } } $template =~ s/ and $//; # Relation if ($relation) { $template .= @where_keys ? "and " : "where "; foreach my $rkey (keys %$relation) { $template .= "$rkey = " . $relation->{$rkey} . " and "; } } $template =~ s/ and $//; # Append some statement $template .= " $append" if $append; # Execute query my $result = $self->execute($template, param => $where, filter => $filter); return $result; } sub create_query { my ($self, $template) = @_; # Create query from SQL template my $sql_template = $self->sql_template; my $cache = $self->cache; # Create query my $query; if ($cache) { # Cached query my $q = $self->cache_method->($self, $template); # Create query $query = DBIx::Custom::Query->new($q) if $q; } unless ($query) { # Create query $query = eval{$sql_template->create_query($template)}; croak($@) if $@; # Cache query $self->cache_method->($self, $template, {sql => $query->sql, columns => $query->columns}) if $cache; } # Prepare statement handle my $sth = eval {$self->dbh->prepare($query->{sql})}; croak $@ if $@; # Set statement handle $query->sth($sth); return $query; } our %VALID_EXECUTE_ARGS = map { $_ => 1 } qw/param filter/; sub execute{ my ($self, $query, %args) = @_; # Check arguments foreach my $name (keys %args) { croak "\"$name\" is invalid name" unless $VALID_EXECUTE_ARGS{$name}; } my $params = $args{param} || {}; # First argument is SQL template unless (ref $query eq 'DBIx::Custom::Query') { my $template; if (ref $query eq 'ARRAY') { $template = $query->[0]; } else { $template = $query } $query = $self->create_query($template); } my $filter = $args{filter} || $query->filter || {}; # Create bind value my $bind_values = $self->_build_bind_values($query, $params, $filter); # Execute my $sth = $query->sth; my $affected = eval {$sth->execute(@$bind_values)}; croak $@ if $@; # Return resultset if select statement is executed if ($sth->{NUM_OF_FIELDS}) { # Get result class my $result_class = $self->result_class; # Create result my $result = $result_class->new({ sth => $sth, default_filter => $self->default_fetch_filter, filters => $self->filters }); return $result; } return $affected; } sub _build_bind_values { my ($self, $query, $params, $filter) = @_; # binding values my @bind_values; # Build bind values my $count = {}; foreach my $column (@{$query->columns}) { croak "\"$column\" is not exists in params" unless exists $params->{$column}; # Value my $value = ref $params->{$column} eq 'ARRAY' ? $params->{$column}->[$count->{$column} || 0] : $params->{$column}; # Filter $filter ||= {}; # Filter name my $fname = $filter->{$column} || $self->default_query_filter || ''; my $filter_func; if ($fname) { if (ref $fname eq 'CODE') { $filter_func = $fname; } else { my $filters = $self->filters; croak "Not exists filter \"$fname\"" unless exists $filters->{$fname}; $filter_func = $filters->{$fname}; } } push @bind_values, $filter_func ? $filter_func->($value) : $value; # Count up $count->{$column}++; } return \@bind_values; } =head1 NAME DBIx::Custom - DBI with hash parameter binding and filtering system =cut our $VERSION = '0.1604'; =head1 STABILITY This module is not stable. Method name and implementations will be changed. =head1 SYNOPSYS # Connect my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=books", user => 'ken', password => '!LFKD%$&'); # Insert $dbi->insert(table => 'books', param => {title => 'perl', author => 'Ken'}, filter => {title => 'encode_utf8'}); # Update $dbi->update(table => 'books', param => {title => 'aaa', author => 'Ken'}, where => {id => 5}, filter => {title => 'encode_utf8'}); # Update all $dbi->update_all(table => 'books', param => {title => 'aaa'}, filter => {title => 'encode_utf8'}); # Delete $dbi->delete(table => 'books', where => {author => 'Ken'}, filter => {title => 'encode_utf8'}); # Delete all $dbi->delete_all(table => 'books'); # Select my $result = $dbi->select(table => 'books'); # Select(more complex) my $result = $dbi->select( table => 'books', column => [qw/author title/], where => {author => 'Ken'}, append => 'order by id limit 1', filter => {tilte => 'encode_utf8'} ); # Select(Join table) my $result = $dbi->select( table => ['books', 'rental'], column => ['books.name as book_name'] relation => {'books.id' => 'rental.book_id'} ); # Execute SQL $dbi->execute("select title from books"); # Execute SQL with parameters and filter $dbi->execute("select id from books where {= author} && {like title}", param => {author => 'ken', title => '%Perl%'}, filter => {tilte => 'encode_utf8'}); # Create query and execute it my $query = $dbi->create_query( "select id from books where {= author} && {like title}" ); $dbi->execute($query, param => {author => 'ken', title => '%Perl%'}) # Default filter $dbi->default_query_filter('encode_utf8'); $dbi->default_fetch_filter('decode_utf8'); # Fetch while (my $row = $result->fetch) { # ... } # Fetch hash while (my $row = $result->fetch_hash) { } # Get DBI object my $dbh = $dbi->dbh; =head1 DESCRIPTION L is useful L extention. This module have hash parameter binding and filtering system. Normally, binding parameter is array. L enable you to pass binding parameter as hash. This module also provide filtering system. You can filter the binding parameter or the value of fetching row. And have useful method such as insert(), update(), delete(), and select(). =head2 Features =over 4 =item * Hash parameter binding. =item * Value filtering. =item * Provide suger methods, such as insert(), update(), delete(), and select(). =back =head1 ATTRIBUTES =head2 C $dbi = $dbi->user('Ken'); $user = $dbi->user; Database user name. This is used for connect(). =head2 C $dbi = $dbi->password('lkj&le`@s'); $password = $dbi->password; Database password. This is used for connect(). =head2 C $dbi = $dbi->data_source("dbi:mysql:dbname=$database"); $data_source = $dbi->data_source; Database data source. This is used for connect(). =head2 C $dbi = $dbi->dbh($dbh); $dbh = $dbi->dbh; Database handle. This is a L object. You can call all methods of L my $sth = $dbi->dbh->prepare("..."); my $errstr = $dbi->dbh->errstr; $dbi->dbh->begin_work; $dbi->dbh->commit; $dbi->dbh->rollback; =head2 C $dbi = $dbi->filters({%filters}); $filters = $dbi->filters; Filter functions. By default, "encode_utf8" and "decode_utf8" is registered. $encode_utf8 = $dbi->filters->{encode_utf8}; $decode_utf8 = $dbi->filters->{decode_utf8}; =head2 C $dbi = $dbi->default_query_filter('encode_utf8'); $default_query_filter = $dbi->default_query_filter Default filter for value binding =head2 C $dbi = $dbi->default_fetch_filter('decode_utf8'); $default_fetch_filter = $dbi->default_fetch_filter; Default filter for fetching. =head2 C $dbi = $dbi->result_class('DBIx::Custom::Result'); $result_class = $dbi->result_class; Result class for select statement. Default to L =head2 C $dbi = $dbi->sql_template(DBIx::Cutom::SQLTemplate->new); $sql_template = $dbi->sql_template; SQLTemplate instance. sql_template attribute must be the instance of L subclass. Default to DBIx::Cutom::SQLTemplate object =head1 METHODS This class is L subclass. You can use all methods of L =head2 C my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=books", user => 'ken', password => '!LFKD%$&'); Connect to database. "AutoCommit" and "RaiseError" option is true, and "PrintError" option is false by default. =head2 C $affected = $dbi->insert(table => $table, param => {%param}, append => $append, filter => {%filter}); Insert row. Retrun value is the count of affected rows. B $dbi->insert(table => 'books', param => {title => 'Perl', author => 'Taro'}, append => "some statement", filter => {title => 'encode_utf8'}) =head2 C $affected = $dbi->update(table => $table, param => {%params}, where => {%where}, append => $append, filter => {%filter}) Update rows. Retrun value is the count of affected rows. B $dbi->update(table => 'books', param => {title => 'Perl', author => 'Taro'}, where => {id => 5}, append => "some statement", filter => {title => 'encode_utf8'}); =head2 C $affected = $dbi->update_all(table => $table, param => {%params}, filter => {%filter}, append => $append); Update all rows. Retrun value is the count of affected rows. B $dbi->update_all(table => 'books', param => {author => 'taro'}, filter => {author => 'encode_utf8'}); =head2 C $affected = $dbi->delete(table => $table, where => {%where}, append => $append, filter => {%filter}); Delete rows. Retrun value is the count of affected rows. B $dbi->delete(table => 'books', where => {id => 5}, append => 'some statement', filter => {id => 'encode_utf8'}); =head2 C $affected = $dbi->delete_all(table => $table); Delete all rows. Retrun value is the count of affected rows. B $dbi->delete_all(table => 'books'); =head2 C