DBIx::Custom - DBI interface, having hash parameter binding and filtering system
Connect to the database.
use DBIx::Custom; my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=dbname", user => 'ken', password => '!LFKD%$&');
Insert, update, and delete
# Insert $dbi->insert(table => 'books', param => {title => 'Perl', author => 'Ken'}, filter => {title => 'encode_utf8'}); # Update $dbi->update(table => 'books', param => {title => 'Perl', author => 'Ken'}, where => {id => 5}, filter => {title => 'encode_utf8'}); # Update all $dbi->update_all(table => 'books', param => {title => 'Perl'}, filter => {title => 'encode_utf8'}); # Delete $dbi->delete(table => 'books', where => {author => 'Ken'}, filter => {title => 'encode_utf8'}); # Delete all $dbi->delete_all(table => 'books');
Select
# 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 5', filter => {title => 'encode_utf8'} ); # Select, join table my $result = $dbi->select( table => ['books', 'rental'], column => ['books.name as book_name'] relation => {'books.id' => 'rental.book_id'} ); # Select, more flexible where my $result = $dbi->select( table => 'books', where => ['{= author} and {like title}', {author => 'Ken', title => '%Perl%'}] );
Execute SQL
# Execute SQL $dbi->execute("select title from books"); # Execute SQL with hash binding and filtering $dbi->execute("select id from books where {= author} and {like title}", param => {author => 'ken', title => '%Perl%'}, filter => {title => 'encode_utf8'}); # Create query and execute it my $query = $dbi->create_query( "select id from books where {= author} and {like title}" ); $dbi->execute($query, param => {author => 'Ken', title => '%Perl%'})
Other features.
# Get DBI object my $dbh = $dbi->dbh;
Fetch row.
# Fetch while (my $row = $result->fetch) { # ... } # Fetch hash while (my $row = $result->fetch_hash) { }
DBIx::Custom is one of DBI interface modules, such as DBIx::Class, DBIx::Simple.
This module is not O/R mapper. O/R mapper is useful, but you must learn many syntax of the O/R mapper, which is almost another language. Created SQL statement is offten not effcient and damage SQL performance. so you have to execute raw SQL in the end.
DBIx::Custom is middle area between DBI and O/R mapper. DBIx::Custom provide flexible hash parameter binding and filtering system, and suger methods, such as insert(), update(), delete(), select() to execute SQL easily.
insert()
update()
delete()
select()
DBIx::Custom respects SQL. SQL is very complex and not beautiful, but de-facto standard, so all people learing database know it. If you already know SQL, you learn a little thing to use DBIx::Custom.
See DBIx::Custom::Guides for more details.
cache
my $cache = $dbi->cache; $dbi = $dbi->cache(1);
Enable parsed DBIx::Custom::Query object caching. Default to 1.
cache_method
$dbi = $dbi->cache_method(\&cache_method); $cache_method = $dbi->cache_method
Method to set and get caches.
Example:
$dbi->cache_method( sub { my $self = shift; $self->{_cached} ||= {}; if (@_ > 1) { $self->{_cached}{$_[0]} = $_[1] } else { return $self->{_cached}{$_[0]} } } );
data_source
my $data_source = $dbi->data_source; $dbi = $dbi->data_source("DBI:mysql:database=dbname");
Data source. connect() method use this value to connect the database.
connect()
dbh
my $dbh = $dbi->dbh; $dbi = $dbi->dbh($dbh);
DBI object. You can call all methods of DBI.
dbi_options
my $dbi_options = $dbi->dbi_options; $dbi = $dbi->dbi_options($dbi_options);
DBI options. connect() method use this value to connect the database.
Default filter when row is fetched.
filters
my $filters = $dbi->filters; $dbi = $dbi->filters(\%filters);
Filter functions. "encode_utf8" and "decode_utf8" is registered by default.
filter_check
my $filter_check = $dbi->filter_check; $dbi = $dbi->filter_check(0);
this attribute is now deprecated and has no mean because check is always done.
password
my $password = $dbi->password; $dbi = $dbi->password('lkj&le`@s');
Password. connect() method use this value to connect the database.
query_builder
my $sql_class = $dbi->query_builder; $dbi = $dbi->query_builder(DBIx::Custom::QueryBuilder->new);
SQL builder. query_builder() must be the instance of DBIx::Custom::QueryBuilder subclass. Default to DBIx::Custom::QueryBuilder object.
query_builder()
result_class
my $result_class = $dbi->result_class; $dbi = $dbi->result_class('DBIx::Custom::Result');
Result class for select statement. Default to DBIx::Custom::Result.
user
my $user = $dbi->user; $dbi = $dbi->user('Ken');
User name. connect() method use this value to connect the database.
DBIx::Custom inherits all methods from Object::Simple and implements the following new ones.
(experimental) auto_filter
$dbi->auto_filter( $table, [$column1, $bind_filter1, $fetch_filter1], [$column2, $bind_filter2, $fetch_filter2], [...], );
auto_filter is automatically filter for columns of table. This have effect insert, update, delete. select and DBIx::Custom::Result object. but this has'nt execute method.
auto_filter
insert
update
delete
select
execute
If you want to have effect <execute< method, use auto_filter_table arguments.
auto_filter_table
$result = $dbi->execute( "select * from table1 where {= key1} and {= key2};", param => {key1 => 1, key2 => 2}, auto_filter_table => ['table1'] );
$dbi->auto_filter('books', 'sale_date', 'to_date', 'date_to');
begin_work
$dbi->begin_work;
Start transaction. This is same as DBI's begin_work.
commit
$dbi->commit;
Commit transaction. This is same as DBI's commit.
connect
my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=dbname", user => 'ken', password => '!LFKD%$&');
Create a new DBIx::Custom object and connect to the database. DBIx::Custom is a wrapper of DBI. AutoCommit and RaiseError options are true, and PrintError option is false by default.
AutoCommit
RaiseError
PrintError
create_query
my $query = $dbi->create_query( "select * from books where {= author} and {like title};" );
Create the instance of DBIx::Custom::Query from the source of SQL. If you want to get high performance, use create_query() method and execute it by execute() method instead of suger methods.
create_query()
execute()
$dbi->execute($query, {author => 'Ken', title => '%Perl%'});
(deprecated) default_bind_filter
my $default_bind_filter = $dbi->default_bind_filter; $dbi = $dbi->default_bind_filter($fname);
Default filter when parameter binding is executed.
(deprecated) default_fetch_filter
my $default_fetch_filter = $dbi->default_fetch_filter; $dbi = $dbi->default_fetch_filter($fname);
my $result = $dbi->execute($query, param => $params, filter => \%filter); my $result = $dbi->execute($source, param => $params, filter => \%filter);
Execute query or the source of SQL. Query is DBIx::Custom::Query object. Return value is DBIx::Custom::Result if select statement is executed, or the count of affected rows if insert, update, delete statement is executed.
my $result = $dbi->execute( "select * from books where {= author} and {like title}", param => {author => 'Ken', title => '%Perl%'} ); while (my $row = $result->fetch) { my $author = $row->[0]; my $title = $row->[1]; }
(experimental) expand
my %expand = $dbi->expand($source);
The following hash
{books => {title => 'Perl', author => 'Ken'}}
is expanded to
('books.title' => 'Perl', 'books.author' => 'Ken')
This is used in select()
$dbi->delete(table => $table, where => \%where, append => $append, filter => \%filter);
Execute delete statement. delete method have table, where, append, and filter arguments. table is a table name. where is where clause. this must be hash reference. append is a string added at the end of the SQL statement. filter is filters when parameter binding is executed. Return value of delete() is the count of affected rows.
table
where
append
filter
$dbi->delete(table => 'books', where => {id => 5}, append => 'some statement', filter => {id => 'encode_utf8'});
delete_all
$dbi->delete_all(table => $table);
Execute delete statement to delete all rows. Arguments is same as delete method, except that delete_all don't have where argument. Return value of delete_all() is the count of affected rows.
delete_all()
$dbi->delete_all(table => 'books');
(experimental) helper
$dbi->helper( update_or_insert => sub { my $self = shift; # do something }, find_or_create => sub { my $self = shift; # do something } );
Register helper methods. These method is called from DBIx::Custom object directory.
$dbi->update_or_insert; $dbi->find_or_create;
$dbi->insert(table => $table, param => \%param, append => $append, filter => \%filter);
Execute insert statement. insert method have table, param, append and filter arguments. table is a table name. param is the pairs of column name value. this must be hash reference. append is a string added at the end of the SQL statement. filter is filters when parameter binding is executed. This is overwrites default_bind_filter. Return value of insert() is the count of affected rows.
param
default_bind_filter
$dbi->insert(table => 'books', param => {title => 'Perl', author => 'Taro'}, append => "some statement", filter => {title => 'encode_utf8'})
new
Create a new DBIx::Custom object.
(experimental) iterate_all_columns
$dbi->iterate_all_columns( sub { my ($table, $column, $column_info) = @_; # do something; } );
Iterate all columns of all tables. Argument is callback. You can do anything by callback.
register_filter
$dbi->register_filter(%filters); $dbi->register_filter(\%filters);
Register filter. Registered filters is available in the following attributes or arguments.
filter argument of insert(), update(), update_all(), delete(), delete_all(), select() methods
update_all()
execute() method
default_filter and filter of DBIx::Custom::Query
default_filter
DBIx::Custom::Query
default_filter and filter of DBIx::Custom::Result
DBIx::Custom::Result
$dbi->register_filter( encode_utf8 => sub { my $value = shift; require Encode; return Encode::encode('UTF-8', $value); }, decode_utf8 => sub { my $value = shift; require Encode; return Encode::decode('UTF-8', $value) } );
rollback
$dbi->rollback;
Rollback transaction. This is same as DBI's rollback.
my $result = $dbi->select(table => $table, column => [@column], where => \%where, append => $append, relation => \%relation, filter => \%filter);
Execute select statement. select method have table, column, where, append, relation and filter arguments. table is a table name. where is where clause. this is normally hash reference. append is a string added at the end of the SQL statement. filter is filters when parameter binding is executed.
column
relation
# select * from books; my $result = $dbi->select(table => 'books'); # select * from books where title = ?; my $result = $dbi->select(table => 'books', where => {title => 'Perl'}); # select title, author from books where id = ? for update; my $result = $dbi->select( table => 'books', column => ['title', 'author'], where => {id => 1}, appned => 'for update' ); # select books.name as book_name from books, rental # where books.id = rental.book_id; my $result = $dbi->select( table => ['books', 'rental'], column => ['books.name as book_name'] relation => {'books.id' => 'rental.book_id'} );
If you use more complex condition, you can specify a array reference to where argument.
my $result = $dbi->select( table => 'books', column => ['title', 'author'], where => ['{= title} or {like author}', {title => '%Perl%', author => 'Ken'}] );
First element is a string. it contains tags, such as "{= title} or {like author}". Second element is paramters.
$dbi->update(table => $table, param => \%params, where => \%where, append => $append, filter => \%filter)
Execute update statement. update method have table, param, where, append and filter arguments. table is a table name. param is column-value pairs. this must be hash reference. where is where clause. this must be hash reference. append is a string added at the end of the SQL statement. filter is filters when parameter binding is executed. This is overwrites default_bind_filter. Return value of update() is the count of affected rows.
$dbi->update(table => 'books', param => {title => 'Perl', author => 'Taro'}, where => {id => 5}, append => "some statement", filter => {title => 'encode_utf8'});
update_all
$dbi->update_all(table => $table, param => \%params, filter => \%filter, append => $append);
Execute update statement to update all rows. Arguments is same as update method, except that update_all don't have where argument. Return value of update_all() is the count of affected rows.
$dbi->update_all(table => 'books', param => {author => 'taro'}, filter => {author => 'encode_utf8'});
DBIx::Custom is now stable. APIs keep backword compatible in the feature.
Please tell me bugs if found.
<kimoto.yuki at gmail.com>
http://github.com/yuki-kimoto/DBIx-Custom
Yuki Kimoto, <kimoto.yuki at gmail.com>
Copyright 2009 Yuki Kimoto, all rights reserved.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
To install DBIx::Custom, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Custom
CPAN shell
perl -MCPAN -e shell install DBIx::Custom
For more information on module installation, please visit the detailed CPAN module installation guide.