NAME
DBIx::Custom - Useful database access, respecting SQL!
SYNOPSYS
use DBIx::Custom;
# Connect
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'}
);
# Update
$dbi->update(
table => 'book',
param => {title => 'Perl', author => 'Ken'},
where => {id => 5},
);
# Delete
$dbi->delete(
table => 'book',
where => {author => 'Ken'},
);
# Select
my $result = $dbi->select(
table => 'book',
where => {author => 'Ken'},
);
# Select, more complex
my $result = $dbi->select(
table => 'book',
column => [
'book.author as book__author',
'company.name as company__name'
],
where => {'book.author' => 'Ken'},
join => ['left outer join company on book.company_id = company.id'],
append => 'order by id limit 5'
);
# Fetch
while (my $row = $result->fetch) {
}
# Fetch as hash
while (my $row = $result->fetch_hash) {
}
# Execute SQL with parameter.
$dbi->execute(
"select id from book where {= author} and {like title}",
param => {author => 'ken', title => '%Perl%'}
);
DESCRIPTIONS
DBIx::Custom is DBI wrapper module.
FEATURES
There are many basic methods to execute various queries.
insert()
,update()
,update_all()
,delete()
,delete_all()
,select()
,insert_at()
,update_at()
,delete_at()
,select_at()
,execute()
Filter when data is send or receive.
Data filtering system
Model support.
Generate where clause dinamically.
Generate join clause dinamically.
GUIDE
DBIx::Custom::Guide - DBIx::Custom Guide
Wiki
ATTRIBUTES
data_source
my $data_source = $dbi->data_source;
$dbi = $dbi->data_source("DBI:mysql:database=dbname");
Data source, used when connect()
is executed.
dbi_option
my $dbi_option = $dbi->dbi_option;
$dbi = $dbi->dbi_option($dbi_option);
DBI option, used when connect()
is executed. Each value in option override the value of default_dbi_option
.
default_dbi_option
my $default_dbi_option = $dbi->default_dbi_option;
$dbi = $dbi->default_dbi_option($default_dbi_option);
DBI default option, used when connect()
is executed, default to the following values.
{
RaiseError => 1,
PrintError => 0,
AutoCommit => 1,
}
You should not change AutoCommit
value directly, the value is used to check if the process is in transaction.
filters
my $filters = $dbi->filters;
$dbi = $dbi->filters(\%filters);
Filters, registered by register_filter()
.
models
EXPERIMENTAL
my $models = $dbi->models;
$dbi = $dbi->models(\%models);
Models, included by include_model()
.
password
my $password = $dbi->password;
$dbi = $dbi->password('lkj&le`@s');
Password, used when connect()
is executed.
query_builder
my $sql_class = $dbi->query_builder;
$dbi = $dbi->query_builder(DBIx::Custom::QueryBuilder->new);
Query builder, default to DBIx::Custom::QueryBuilder object.
reserved_word_quote
EXPERIMENTAL
my reserved_word_quote = $dbi->reserved_word_quote;
$dbi = $dbi->reserved_word_quote('"');
Reserved word quote, default to empty string.
result_class
my $result_class = $dbi->result_class;
$dbi = $dbi->result_class('DBIx::Custom::Result');
Result class, default to DBIx::Custom::Result.
safety_character
my $safety_character = $self->safety_character;
$dbi = $self->safety_character($character);
Regex of safety character for table and column name, default to '\w'. Note that you don't have to specify like '[\w]'.
user
my $user = $dbi->user;
$dbi = $dbi->user('Ken');
User name, used when connect()
is executed.
METHODS
DBIx::Custom inherits all methods from Object::Simple and use all methods of DBI and implements the following new ones.
apply_filter
EXPERIMENTAL
$dbi->apply_filter(
'book',
'issue_date' => {
out => 'tp_to_date',
in => 'date_to_tp',
end => 'tp_to_displaydate'
},
'write_date' => {
out => 'tp_to_date',
in => 'date_to_tp',
end => 'tp_to_displaydate'
}
);
Apply filter to columns. out
filter is executed before data is send to database. in
filter is executed after a row is fetch. end
filter is execute after in
filter is executed.
Filter is applied to the follwoing tree column name pattern.
PETTERN EXAMPLE
1. Column : author
2. Table.Column : book.author
3. Table__Column : book__author
If column name is duplicate with other table, Main filter specified by table
option is used.
You can set multiple filters at once.
$dbi->apply_filter(
'book',
[qw/issue_date write_date/] => {
out => 'tp_to_date',
in => 'date_to_tp',
end => 'tp_to_displaydate'
}
);
connect
my $dbi = DBIx::Custom->connect(
data_source => "dbi:mysql:database=dbname",
user => 'ken',
password => '!LFKD%$&',
dbi_option => {mysql_enable_utf8 => 1}
);
Connect to the database and create a new DBIx::Custom object.
DBIx::Custom is a wrapper of DBI. AutoCommit
and RaiseError
options are true, and PrintError
option is false by default.
create_model
my $model = $dbi->create_model(
table => 'book',
primary_key => 'id',
join => [
'inner join company on book.comparny_id = company.id'
],
filter => [
publish_date => {
out => 'tp_to_date',
in => 'date_to_tp',
end => 'tp_to_displaydate'
}
]
);
Create DBIx::Custom::Model object and initialize model. the module is also used from model() method.
$dbi->model('book')->select(...);
create_query
my $query = $dbi->create_query(
"insert into book {insert_param title author};";
);
Create DBIx::Custom::Query object.
If you want to get high performance, create DBIx::Custom::Query object and execute the query by execute()
instead of other methods, such as insert
, update
.
$dbi->execute($query, {author => 'Ken', title => '%Perl%'});
dbh
my $dbh = $dbi->dbh;
$dbi = $dbi->dbh($dbh);
Get and set database handle of DBI.
If process is spawn by forking, new connection is created automatically.
each_column
$dbi->each_column(
sub {
my ($dbi, $table, $column, $column_info) = @_;
my $type = $column_info->{TYPE_NAME};
if ($type eq 'DATE') {
# ...
}
}
);
Iterate all column informations of all table from database. Argument is callback when one column is found. Callback receive four arguments, dbi object, table name, column name and column information.
execute
my $result = $dbi->execute(
"select * from book where {= title} and {like author}",
param => {title => 'Perl', author => '%Ken%'}
);
Execute SQL, containing tags. Return value is DBIx::Custom::Result in select statement, or the count of affected rows in insert, update, delete statement.
Tag is turned into the statement containing place holder before SQL is executed.
select * from where title = ? and author like ?;
See also "Tags" in Tags.
The following opitons are currently available.
filter
-
Filter, executed before data is send to database. This is array reference. Filter value is code reference or filter name registerd by
register_filter()
.# Basic $dbi->execute( $sql, filter => [ title => sub { uc $_[0] } author => sub { uc $_[0] } ] ); # At once $dbi->execute( $sql, filter => [ [qw/title author/] => sub { uc $_[0] } ] ); # Filter name $dbi->execute( $sql, filter => [ title => 'upper_case', author => 'upper_case' ] );
These filters are added to the
out
filters, set byapply_filter()
.
delete
$dbi->delete(table => 'book', where => {title => 'Perl'});
Delete statement.
The following opitons are currently available.
table
-
Table name.
$dbi->delete(table => 'book');
where
-
Where clause. This is hash reference or DBIx::Custom::Where object or array refrence, which contains where clause and paramter.
# Hash reference $dbi->delete(where => {title => 'Perl'}); # DBIx::Custom::Where object my $where = $dbi->where( clause => ['and', '{= author}', '{like title}'], param => {author => 'Ken', title => '%Perl%'} ); $dbi->delete(where => $where); # Array refrendce (where clause and parameter) $dbi->delete(where => [ ['and', '{= author}', '{like title}'], {author => 'Ken', title => '%Perl%'} ] );
append
-
Append statement to last of SQL. This is string.
$dbi->delete(append => 'order by title');
filter
-
Filter, executed before data is send to database. This is array reference. Filter value is code reference or filter name registerd by
register_filter()
.# Basic $dbi->delete( filter => [ title => sub { uc $_[0] } author => sub { uc $_[0] } ] ); # At once $dbi->delete( filter => [ [qw/title author/] => sub { uc $_[0] } ] ); # Filter name $dbi->delete( filter => [ title => 'upper_case', author => 'upper_case' ] );
These filters are added to the
out
filters, set byapply_filter()
.
column
EXPERIMENTAL
my $column = $self->column(book => ['author', 'title']);
Create column clause. The follwoing column clause is created.
book.author as book__author,
book.title as book__title
query
-
Get DBIx::Custom::Query object instead of executing SQL. This is true or false value.
my $query = $dbi->delete(query => 1);
You can check SQL.
my $sql = $query->sql;
delete_all
$dbi->delete_all(table => $table);
Delete statement to delete all rows. Options is same as delete()
.
delete_at()
Delete statement, using primary key.
$dbi->delete_at(
table => 'book',
primary_key => 'id',
where => '5'
);
This method is same as delete()
exept that primary_key
is specified and where
is constant value or array refrence. all option of delete()
is available.
primary_key
-
Primary key. This is constant value or array reference.
# Constant value $dbi->delete(primary_key => 'id'); # Array reference $dbi->delete(primary_key => ['id1', 'id2' ]);
This is used to create where clause.
where
-
Where clause, created from primary key information. This is constant value or array reference.
# Constant value $dbi->delete(where => 5); # Array reference $dbi->delete(where => [3, 5]);
In first examle, the following SQL is created.
delete from book where id = ?;
Place holder is set to 5.
insert
$dbi->insert(
table => 'book',
param => {title => 'Perl', author => 'Ken'}
);
Insert statement.
The following opitons are currently available.
table
-
Table name.
$dbi->insert(table => 'book');
param
-
Insert data. This is hash reference.
$dbi->insert(param => {title => 'Perl'});
append
-
Append statement to last of SQL. This is string.
$dbi->insert(append => 'order by title');
filter
-
Filter, executed before data is send to database. This is array reference. Filter value is code reference or filter name registerd by
register_filter()
.# Basic $dbi->insert( filter => [ title => sub { uc $_[0] } author => sub { uc $_[0] } ] ); # At once $dbi->insert( filter => [ [qw/title author/] => sub { uc $_[0] } ] ); # Filter name $dbi->insert( filter => [ title => 'upper_case', author => 'upper_case' ] );
These filters are added to the
out
filters, set byapply_filter()
. query
-
Get DBIx::Custom::Query object instead of executing SQL. This is true or false value.
my $query = $dbi->insert(query => 1);
You can check SQL.
my $sql = $query->sql;
insert_at()
Insert statement, using primary key.
$dbi->insert_at(
table => 'book',
primary_key => 'id',
where => '5',
param => {title => 'Perl'}
);
This method is same as insert()
exept that primary_key
is specified and where
is constant value or array refrence. all option of insert()
is available.
primary_key
-
Primary key. This is constant value or array reference.
# Constant value $dbi->insert(primary_key => 'id'); # Array reference $dbi->insert(primary_key => ['id1', 'id2' ]);
This is used to create parts of insert data.
where
-
Parts of Insert data, create from primary key information. This is constant value or array reference.
# Constant value $dbi->insert(where => 5); # Array reference $dbi->insert(where => [3, 5]);
In first examle, the following SQL is created.
insert into book (id, title) values (?, ?);
Place holders are set to 5 and 'Perl'.
insert_param_tag
my $insert_param_tag = $dbi->insert_param_tag({title => 'a', age => 2});
Create insert parameter tag.
(title, author) values ({? title}, {? author});
include_model
EXPERIMENTAL
$dbi->include_model('MyModel');
Include models from specified namespace, the following layout is needed to include models.
lib / MyModel.pm
/ MyModel / book.pm
/ company.pm
Name space module, extending DBIx::Custom::Model.
MyModel.pm
package MyModel;
use base 'DBIx::Custom::Model';
1;
Model modules, extending name space module.
MyModel/book.pm
package MyModel::book;
use base 'MyModel';
1;
MyModel/company.pm
package MyModel::company;
use base 'MyModel';
1;
MyModel::book and MyModel::company is included by include_model()
.
You can get model object by model()
.
my $book_model = $dbi->model('book');
my $company_model = $dbi->model('company');
See DBIx::Custom::Model to know model features.
merge_param
EXPERIMENTAL
my $param = $dbi->merge_param({key1 => 1}, {key1 => 1, key2 => 2});
Merge paramters.
$param:
{key1 => [1, 1], key2 => 2}
method
EXPERIMENTAL
$dbi->method(
update_or_insert => sub {
my $self = shift;
# Process
},
find_or_create => sub {
my $self = shift;
# Process
}
);
Register method. These method is called directly from DBIx::Custom object.
$dbi->update_or_insert;
$dbi->find_or_create;
model
EXPERIMENTAL
$dbi->model('book')->method(
insert => sub { ... },
update => sub { ... }
);
my $model = $dbi->model('book');
Set and get a DBIx::Custom::Model object,
mycolumn
EXPERIMENTAL
my $column = $self->mycolumn(book => ['author', 'title']);
Create column clause for myself. The follwoing column clause is created.
book.author as author,
book.title as title
new
my $dbi = DBIx::Custom->new(
data_source => "dbi:mysql:database=dbname",
user => 'ken',
password => '!LFKD%$&',
dbi_option => {mysql_enable_utf8 => 1}
);
Create a new DBIx::Custom object.
not_exists
my $not_exists = $dbi->not_exists;
DBIx::Custom::NotExists object, indicating the column is not exists. This is used by clause
of DBIx::Custom::Where .
register_filter
$dbi->register_filter(
# Time::Piece object to database DATE format
tp_to_date => sub {
my $tp = shift;
return $tp->strftime('%Y-%m-%d');
},
# database DATE format to Time::Piece object
date_to_tp => sub {
my $date = shift;
return Time::Piece->strptime($date, '%Y-%m-%d');
}
);
Register filters, used by filter
option of many methods.
register_tag
$dbi->register_tag(
update => sub {
my @columns = @_;
# Update parameters
my $s = 'set ';
$s .= "$_ = ?, " for @columns;
$s =~ s/, $//;
return [$s, \@columns];
}
);
Register tag, used by execute()
.
See also "Tags" in Tags about tag registered by default.
Tag parser receive arguments specified in tag. In the following tag, 'title' and 'author' is parser arguments
{update_param title author}
Tag parser must return array refrence, first element is the result statement, second element is column names corresponding to place holders.
In this example, result statement is
set title = ?, author = ?
Column names is
['title', 'author']
replace
EXPERIMENTAL
my $join = [
'left outer join table2 on table1.key1 = table2.key1',
'left outer join table3 on table2.key3 = table3.key3'
];
$join = $dbi->replace(
$join,
'left outer join table2 on table1.key1 = table2.key1',
'left outer join (select * from table2 where {= table2.key1}) ' .
'as table2 on table1.key1 = table2.key1'
);
Replace join clauses if match the expression.
select
my $result = $dbi->select(
table => 'book',
column => ['author', 'title'],
where => {author => 'Ken'},
);
Select statement.
The following opitons are currently available.
table
-
Table name.
$dbi->select(table => 'book');
column
-
Column clause. This is array reference or constant value.
# Hash refernce $dbi->select(column => ['author', 'title']); # Constant value $dbi->select(column => 'author');
Default is '*' unless
column
is specified.# Default $dbi->select(column => '*');
where
-
Where clause. This is hash reference or DBIx::Custom::Where object, or array refrence, which contains where clause and paramter.
# Hash reference $dbi->select(where => {author => 'Ken', 'title' => 'Perl'}); # DBIx::Custom::Where object my $where = $dbi->where( clause => ['and', '{= author}', '{like title}'], param => {author => 'Ken', title => '%Perl%'} ); $dbi->select(where => $where); # Array refrendce (where clause and parameter) $dbi->select(where => [ ['and', '{= author}', '{like title}'], {author => 'Ken', title => '%Perl%'} ] );
join
EXPERIMENTAL-
Join clause used in need. This is array reference.
$dbi->select(join => [ 'left outer join company on book.company_id = company_id', 'left outer join location on company.location_id = location.id' ] );
If column cluase or where clause contain table name like "company.name", needed join clause is used automatically.
$dbi->select( table => 'book', column => ['company.location_id as company__location_id'], where => {'company.name' => 'Orange'}, join => [ 'left outer join company on book.company_id = company.id', 'left outer join location on company.location_id = location.id' ] );
In above select, the following SQL is created.
select company.location_id as company__location_id from book left outer join company on book.company_id = company.id where company.name = Orange
param
EXPERIMETNAL-
Parameter shown before where clause.
$dbi->select( table => 'table1', column => 'table1.key1 as table1_key1, key2, key3', where => {'table1.key2' => 3}, join => ['inner join (select * from table2 where {= table2.key3})' . ' as table2 on table1.key1 = table2.key1'], param => {'table2.key3' => 5} );
For example, if you want to contain tag in join clause, you can pass parameter by
param
option. append
-
Append statement to last of SQL. This is string.
$dbi->select(append => 'order by title');
filter
-
Filter, executed before data is send to database. This is array reference. Filter value is code reference or filter name registerd by
register_filter()
.# Basic $dbi->select( filter => [ title => sub { uc $_[0] } author => sub { uc $_[0] } ] ); # At once $dbi->select( filter => [ [qw/title author/] => sub { uc $_[0] } ] ); # Filter name $dbi->select( filter => [ title => 'upper_case', author => 'upper_case' ] );
These filters are added to the
out
filters, set byapply_filter()
. query
-
Get DBIx::Custom::Query object instead of executing SQL. This is true or false value.
my $query = $dbi->select(query => 1);
You can check SQL.
my $sql = $query->sql;
type
EXPERIMENTAL-
Specify database data type.
$dbi->select(type => [image => DBI::SQL_BLOB]); $dbi->select(type => [[qw/image audio/] => DBI::SQL_BLOB]);
This is used to bind paramter by
bind_param()
of statment handle.$sth->bind_param($pos, $value, DBI::SQL_BLOB);
select_at()
Select statement, using primary key.
$dbi->select_at(
table => 'book',
primary_key => 'id',
where => '5'
);
This method is same as select()
exept that primary_key
is specified and where
is constant value or array refrence. all option of select()
is available.
primary_key
-
Primary key. This is constant value or array reference.
# Constant value $dbi->select(primary_key => 'id'); # Array reference $dbi->select(primary_key => ['id1', 'id2' ]);
This is used to create where clause.
where
-
Where clause, created from primary key information. This is constant value or array reference.
# Constant value $dbi->select(where => 5); # Array reference $dbi->select(where => [3, 5]);
In first examle, the following SQL is created.
select * from book where id = ?
Place holder is set to 5.
update
$dbi->update(
table => 'book',
param => {title => 'Perl'},
where => {id => 4}
);
Update statement.
The following opitons are currently available.
table
-
Table name.
$dbi->update(table => 'book');
param
-
Update data. This is hash reference.
$dbi->update(param => {title => 'Perl'});
where
-
Where clause. This is hash reference or DBIx::Custom::Where object or array refrence.
# Hash reference $dbi->update(where => {author => 'Ken', 'title' => 'Perl'}); # DBIx::Custom::Where object my $where = $dbi->where( clause => ['and', '{= author}', '{like title}'], param => {author => 'Ken', title => '%Perl%'} ); $dbi->update(where => $where); # Array refrendce (where clause and parameter) $dbi->update(where => [ ['and', '{= author}', '{like title}'], {author => 'Ken', title => '%Perl%'} ] );
append
-
Append statement to last of SQL. This is string.
$dbi->update(append => 'order by title');
filter
-
Filter, executed before data is send to database. This is array reference. Filter value is code reference or filter name registerd by
register_filter()
.# Basic $dbi->update( filter => [ title => sub { uc $_[0] } author => sub { uc $_[0] } ] ); # At once $dbi->update( filter => [ [qw/title author/] => sub { uc $_[0] } ] ); # Filter name $dbi->update( filter => [ title => 'upper_case', author => 'upper_case' ] );
These filters are added to the
out
filters, set byapply_filter()
. query
-
Get DBIx::Custom::Query object instead of executing SQL. This is true or false value.
my $query = $dbi->update(query => 1);
You can check SQL.
my $sql = $query->sql;
update_all
$dbi->update_all(table => 'book', param => {title => 'Perl'});
Update statement to update all rows. Options is same as update()
.
update_at()
Update statement, using primary key.
$dbi->update_at(
table => 'book',
primary_key => 'id',
where => '5',
param => {title => 'Perl'}
);
This method is same as update()
exept that primary_key
is specified and where
is constant value or array refrence. all option of update()
is available.
primary_key
-
Primary key. This is constant value or array reference.
# Constant value $dbi->update(primary_key => 'id'); # Array reference $dbi->update(primary_key => ['id1', 'id2' ]);
This is used to create where clause.
where
-
Where clause, created from primary key information. This is constant value or array reference.
# Constant value $dbi->update(where => 5); # Array reference $dbi->update(where => [3, 5]);
In first examle, the following SQL is created.
update book set title = ? where id = ?
Place holders are set to 'Perl' and 5.
update_param_tag
my $update_param_tag = $dbi->update_param_tag({title => 'a', age => 2});
Create update parameter tag.
set title = {? title}, author = {? author}
You can create tag without 'set ' by no_set
option. This option is EXPERIMENTAL.
my $update_param_tag = $dbi->update_param_tag(
{title => 'a', age => 2}
{no_set => 1}
);
title = {? title}, author = {? author}
where
my $where = $dbi->where(
clause => ['and', '{= title}', '{= author}'],
param => {title => 'Perl', author => 'Ken'}
);
Create a new DBIx::Custom::Where object.
setup_model
EXPERIMENTAL
$dbi->setup_model;
Setup all model objects. columns
of model object is automatically set, parsing database information.
Tags
The following tags is available.
table
Table tag
{table TABLE} -> TABLE
This is used to tell execute()
what table is needed .
?
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 = ?
ENVIRONMENT VARIABLE
DBIX_CUSTOM_DEBUG
If environment variable DBIX_CUSTOM_DEBUG
is set to true, executed SQL is printed to STDERR.
STABILITY
DBIx::Custom is stable. APIs keep backword compatible except EXPERIMENTAL one 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-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.
2 POD Errors
The following errors were encountered while parsing the POD:
- Around line 1894:
You forgot a '=back' before '=head2'
- Around line 1903:
'=item' outside of any '=over'