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 => 'book', param => {title => 'Perl', author => 'Ken'}, filter => {title => 'encode_utf8'}); # Update $dbi->update(table => 'book', param => {title => 'Perl', author => 'Ken'}, where => {id => 5}, filter => {title => 'encode_utf8'}); # Update all $dbi->update_all(table => 'book', param => {title => 'Perl'}, filter => {title => 'encode_utf8'}); # Delete $dbi->delete(table => 'book', where => {author => 'Ken'}, filter => {title => 'encode_utf8'}); # Delete all $dbi->delete_all(table => 'book');
Select
# Select my $result = $dbi->select(table => 'book'); # Select, more complex my $result = $dbi->select( table => 'book', 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 => ['book', 'rental'], column => ['book.name as book_name'] relation => {'book.id' => 'rental.book_id'} ); # Select, more flexible where my $result = $dbi->select( table => 'book', where => ['{= author} and {like title}', {author => 'Ken', title => '%Perl%'}] );
Execute SQL
# Execute SQL $dbi->execute("select title from book"); # Execute SQL with hash binding and filtering $dbi->execute("select id from book 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 book 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.
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) apply_filter
$dbi->apply_filter( $table, $column1 => {in => $infilter1, out => $outfilter1} $column2 => {in => $infilter2, out => $outfilter2} ..., );
apply_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.
apply_filter
insert
update
delete
select
execute
If you want to have effect execute() method, use table arguments.
execute()
table
$result = $dbi->execute( "select * from table1 where {= key1} and {= key2};", param => {key1 => 1, key2 => 2}, table => ['table1'] );
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 book 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()
$dbi->execute($query, {author => 'Ken', title => '%Perl%'});
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.
Example:
my $result = $dbi->execute( "select * from book 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
{book => {title => 'Perl', author => 'Ken'}}
is expanded to
('book.title' => 'Perl', 'book.author' => 'Ken')
This is used in select()
$dbi->delete(table => $table, where => \%where, append => $append, filter => \%filter, query => 1);
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. query is if you don't execute sql and get DBIx::Custom::Query object as return value. default to 0. This is experimental. Return value of delete() is the count of affected rows.
where
append
filter
query
$dbi->delete(table => 'book', 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 => 'book');
(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, query => 1);
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. query is if you don't execute sql and get DBIx::Custom::Query object as return value. default to 0. This is experimental. This is overwrites default_bind_filter. Return value of insert() is the count of affected rows.
param
default_bind_filter
$dbi->insert(table => 'book', 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.
(experimental) or
$or = $dbi->or(1, 5);
Create DBIx::Custom::Or object. This is used with select method's where option.
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, query => 1);
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. query is if you don't execute sql and get DBIx::Custom::Query object as return value. default to 0. This is experimental.
column
relation
# select * from book; my $result = $dbi->select(table => 'book'); # select * from book where title = ?; my $result = $dbi->select(table => 'book', where => {title => 'Perl'}); # select title, author from book where id = ? for update; my $result = $dbi->select( table => 'book', column => ['title', 'author'], where => {id => 1}, appned => 'for update' ); # select book.name as book_name from book, rental # where book.id = rental.book_id; my $result = $dbi->select( table => ['book', 'rental'], column => ['book.name as book_name'] relation => {'book.id' => 'rental.book_id'} );
If you use more complex condition, you can specify a array reference to where argument.
my $result = $dbi->select( table => 'book', 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, query => 1)
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. query is if you don't execute sql and get DBIx::Custom::Query object as return value. default to 0. This is experimental. This is overwrites default_bind_filter. Return value of update() is the count of affected rows.
$dbi->update(table => 'book', param => {title => 'Perl', author => 'Taro'}, where => {id => 5}, append => "some statement", filter => {title => 'encode_utf8'});
(experimental) txn_scope
{ my $txn = $dbi->txn_scope; $dbi->insert(table => 'book', param => {title => 'Perl'}); $dbi->insert(table => 'book', param => {title => 'Good days'}); $txn->commit; }
Create transaction scope. If you escape scope(that is { .. }) and commited, Rollback is automatically done.
Note that this is feature of DBIx::TransactionManager DBIx::TransactionManager is required.
(experimental) table
$dbi->table('book', insert => sub { ... }, update => sub { ... } ); my $table = $dbi->table('book');
Create a DBIx::Custom::Table object, or get a DBIx::Custom::Table object.
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 => 'book', param => {author => 'taro'}, filter => {author => 'encode_utf8'});
(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);
(deprecated) cache_method
$dbi = $dbi->cache_method(\&cache_method); $cache_method = $dbi->cache_method
Method to set and get caches.
$dbi->cache_method( sub { my $self = shift; $self->{_cached} ||= {}; if (@_ > 1) { $self->{_cached}{$_[0]} = $_[1] } else { return $self->{_cached}{$_[0]} } } );
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.