DBIx::Custom - Useful database access, respecting SQL!
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%'} );
DBIx::Custom is DBI wrapper module.
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()
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.
DBIx::Custom::Guide - DBIx::Custom Guide
DBIx::Custom Wiki
connector
my $connector = $dbi->connector; $dbi = $dbi->connector(DBIx::Connector->new(...));
Connection manager object. if connector is set, you can get dbh() from connection manager. conection manager object must have dbh() mehtod.
dbh()
This is DBIx::Connector example. Please pass default_dbi_option to DBIx::Connector.
default_dbi_option
my $connector = DBIx::Connector->new( "dbi:mysql:database=$DATABASE", $USER, $PASSWORD, DBIx::Custom->new->default_dbi_option ); my $dbi = DBIx::Custom->new(connector => $connector);
data_source
my $data_source = $dbi->data_source; $dbi = $dbi->data_source("DBI:mysql:database=dbname");
Data source, used when connect() is executed.
connect()
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.
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.
AutoCommit
filters
my $filters = $dbi->filters; $dbi = $dbi->filters(\%filters);
Filters, registered by register_filter().
register_filter()
models
my $models = $dbi->models; $dbi = $dbi->models(\%models);
Models, included by include_model().
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
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.
DBIx::Custom inherits all methods from Object::Simple and use all methods of DBI and implements the following new ones.
apply_filter
$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.
out
in
end
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.
table
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.
RaiseError
PrintError
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.
insert
update
$dbi->execute($query, {author => 'Ken', title => '%Perl%'});
dbh
my $dbh = $dbi->dbh;
Get DBI database handle. if connector is set, you can get database handle from connector.
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 by apply_filter().
apply_filter()
delete
$dbi->delete(table => 'book', where => {title => 'Perl'});
Delete statement.
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');
# 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' ] );
column
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 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 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.
$dbi->insert( table => 'book', param => {title => 'Perl', author => 'Ken'} );
Insert statement.
$dbi->insert(table => 'book');
param
Insert data. This is hash reference.
$dbi->insert(param => {title => 'Perl'});
$dbi->insert(append => 'order by title');
# 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' ] );
my $query = $dbi->insert(query => 1);
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.
# Constant value $dbi->insert(primary_key => 'id'); # Array reference $dbi->insert(primary_key => ['id1', 'id2' ]);
This is used to create parts of insert data.
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]);
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
$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().
model()
my $book_model = $dbi->model('book'); my $company_model = $dbi->model('company');
See DBIx::Custom::Model to know model features.
merge_param
my $param = $dbi->merge_param({key1 => 1}, {key1 => 1, key2 => 2});
Merge paramters.
$param:
{key1 => [1, 1], key2 => 2}
method
$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
$dbi->model('book')->method( insert => sub { ... }, update => sub { ... } ); my $model = $dbi->model('book');
Set and get a DBIx::Custom::Model object,
mycolumn
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 .
clause
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
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.
$dbi->select(table => 'book');
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 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
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
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.
$dbi->select(append => 'order by title');
# 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' ] );
my $query = $dbi->select(query => 1);
type
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.
bind_param()
$sth->bind_param($pos, $value, DBI::SQL_BLOB);
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.
# Constant value $dbi->select(primary_key => 'id'); # Array reference $dbi->select(primary_key => ['id1', 'id2' ]);
# Constant value $dbi->select(where => 5); # Array reference $dbi->select(where => [3, 5]);
select * from book where id = ?
$dbi->update( table => 'book', param => {title => 'Perl'}, where => {id => 4} );
Update statement.
$dbi->update(table => 'book');
Update data. This is hash reference.
$dbi->update(param => {title => 'Perl'});
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%'} ] );
$dbi->update(append => 'order by title');
# 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' ] );
my $query = $dbi->update(query => 1);
update_all
$dbi->update_all(table => 'book', param => {title => 'Perl'});
Update statement to update all rows. Options is same as update().
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.
# Constant value $dbi->update(primary_key => 'id'); # Array reference $dbi->update(primary_key => ['id1', 'id2' ]);
# Constant value $dbi->update(where => 5); # Array reference $dbi->update(where => [3, 5]);
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.
no_set
my $update_param_tag = $dbi->update_param_tag( {title => 'a', age => 2} {no_set => 1} ); title = {? title}, author = {? author}
my $where = $dbi->where( clause => ['and', '{= title}', '{= author}'], param => {title => 'Perl', author => 'Ken'} );
Create a new DBIx::Custom::Where object.
setup_model
$dbi->setup_model;
Setup all model objects. columns of model object is automatically set, parsing database information.
columns
The following tags is available.
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 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_DEBUG
If environment variable DBIX_CUSTOM_DEBUG is set to true, executed SQL is printed to STDERR.
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.
2 POD Errors
The following errors were encountered while parsing the POD:
You forgot a '=back' before '=head2'
'=item' outside of any '=over'
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.