DBIx::Custom - DBI interface, having hash parameter binding and filtering system
use DBIx::Custom; my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=dbname", user => 'ken', password => '!LFKD%$&', dbi_option => {mysql_enable_utf8 => 1}); # Insert $dbi->insert(table => 'book', param => {title => 'Perl', author => 'Ken'}, filter => {title => 'to_something'}); # Update $dbi->update(table => 'book', param => {title => 'Perl', author => 'Ken'}, where => {id => 5}, filter => {title => 'to_something'}); # Update all $dbi->update_all(table => 'book', param => {title => 'Perl'}, filter => {title => 'to_something'}); # Delete $dbi->delete(table => 'book', where => {author => 'Ken'}, filter => {title => 'to_something'}); # Delete all $dbi->delete_all(table => 'book'); # Select my $result = $dbi->select( table => 'book', column => [qw/author title/], where => {author => 'Ken'}, relation => {'book.id' => 'rental.book_id'}, append => 'order by id limit 5', filter => {title => 'to_something'} ); # 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 => 'to_something'}); # 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%'}) # Get DBI object my $dbh = $dbi->dbh; # 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::Guide for more details.
DBIx::Custom::Guide - DBIx::Custom complete guide
DBIx::Custom Wiki - Many useful examples
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_option
my $dbi_option = $dbi->dbi_option; $dbi = $dbi->dbi_option($dbi_option);
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);
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.
(experimental) safety_column_name
my $safety_column_name = $self->safety_column_name; $dbi = $self->safety_column_name($name);
Safety column name regex. Default is qr/^[\w\.]*$/
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 use all method of DBI 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'] );
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.
$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
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->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
(experimental) each_column
$dbi->each_column( sub { my ($self, $table, $column, $info) = @_; my $type = $info->{TYPE_NAME}; if ($type eq 'DATE') { # ... } } ); Get column informations from database. Argument is callback. You can do anything in callback. Callback receive four arguments, dbi object, table name, column name and columninformation.
(experimental) method
$dbi->method( update_or_insert => sub { my $self = shift; # do something }, find_or_create => sub { my $self = shift; # do something } );
Register method. These method is called from DBIx::Custom object directory.
$dbi->update_or_insert; $dbi->find_or_create;
new
Create a new DBIx::Custom object.
(experimental) not_exists
my $not_exists = $dbi->not_exists;
Get DBIx::Custom::NotExists object.
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
register_tag
$dbi->register_tag( limit => sub { ...; } );
Register tag.
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
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.
(experimental) table
$dbi->table('book')->method( 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.
(experimental) where
my $where = $dbi->where;
Create a new DBIx::Custom::Where object.
cache_method
$dbi = $dbi->cache_method(\&cache_method); $cache_method = $dbi->cache_method
Method to set and get caches.
The following tags is available.
?
Placeholder tag.
{? NAME} -> ?
=
Equal tag.
{= NAME} -> NAME = ?
<>
Not equal tag.
{<> NAME} -> NAME <> ?
<
Lower than tag
{< NAME} -> NAME < ?
>
Greater than tag
{> NAME} -> NAME > ?
>=
Greater than or equal tag
{>= NAME} -> NAME >= ?
<=
Lower than or equal tag
{<= NAME} -> NAME <= ?
like
Like tag
{like NAME} -> NAME like ?
in
In tag.
{in NAME COUNT} -> NAME in [?, ?, ..]
insert_param
Insert parameter tag.
{insert_param NAME1 NAME2} -> (NAME1, NAME2) values (?, ?)
update_param
Updata parameter tag.
{update_param NAME1 NAME2} -> set NAME1 = ?, NAME2 = ?
DBIx::Custom is stable. APIs keep backword compatible except experimental one 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-2011 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.