NAME
DBIx::Custom - DBI interface, having hash parameter binding and filtering system
SYNOPSYS
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) {
}
DESCRIPTIONS
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.
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.
ATTRIBUTES
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.
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.
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.
METHODS
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.
If you want to have effect <execute< method, use auto_filter_table
arguments.
$result = $dbi->execute(
"select * from table1 where {= key1} and {= key2};",
param => {key1 => 1, key2 => 2},
auto_filter_table => ['table1']
);
Example:
$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
.
DBIx::Custom inherits all methods from Object::Simple and implements the following new ones.
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.
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.
$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);
execute
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 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()
delete
$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.
Example:
$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.
Example:
$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;
insert
$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.
Example:
$dbi->insert(table => 'books',
param => {title => 'Perl', author => 'Taro'},
append => "some statement",
filter => {title => 'encode_utf8'})
new
my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=dbname",
user => 'ken', password => '!LFKD%$&');
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 ofinsert()
,update()
,update_all()
,delete()
,delete_all()
,select()
methodsexecute()
methoddefault_filter
andfilter
ofDBIx::Custom::Query
default_filter
andfilter
ofDBIx::Custom::Result
Example:
$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
.
select
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.
Example:
# 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.
update
$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.
Example:
$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.
Example:
$dbi->update_all(table => 'books',
param => {author => 'taro'},
filter => {author => 'encode_utf8'});
STABILITY
DBIx::Custom is now stable. APIs keep backword compatible in the feature.
BUGS
Please tell me bugs if found.
<kimoto.yuki at gmail.com>
http://github.com/yuki-kimoto/DBIx-Custom
AUTHOR
Yuki Kimoto, <kimoto.yuki at gmail.com>
COPYRIGHT & LICENSE
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.