DBIx::Custom - Execute insert, update, delete, and select statement easily
use DBIx::Custom; # Connect my $dbi = DBIx::Custom->connect( dsn => "dbi:mysql:database=dbname", user => 'ken', password => '!LFKD%$&', dbi_option => {mysql_enable_utf8 => 1} ); # Insert $dbi->insert({title => 'Perl', author => 'Ken'}, table => 'book'); # Update $dbi->update({title => 'Perl', author => 'Ken'}, table => 'book', where => {id => 5}); # Delete $dbi->delete(table => 'book', where => {author => 'Ken'}); # Select my $result = $dbi->select(table => 'book', column => ['title', 'author'], where => {author => 'Ken'}); # Select, more complex my $result = $dbi->select( table => 'book', column => [ {book => [qw/title author/]}, {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 = :author and title like :title", {author => 'ken', title => '%Perl%'} );
DBIx::Custom is DBI wrapper module to execute SQL easily. This module have the following features.
Execute insert, update, delete, or select statement easily
insert
update
delete
select
Create where clause flexibly
where
Model support
Connection manager support
Choice your favorite relation database management system, MySQL, SQLite, PostgreSQL, Oracle, Microsoft SQL Server, Microsoft Access, DB2 or anything,
MySQL
SQLite
PostgreSQL
Oracle
Microsoft SQL Server
Microsoft Access
DB2
Filtering by data type or column name(EXPERIMENTAL)
Create order by clause flexibly(EXPERIMENTAL)
order by
DBIx::Custom::Guide - How to use DBIx::Custom
DBIx::Custom Wiki - Theare are various examples.
Mosdule documentations - DBIx::Custom::Result, DBIx::Custom::Query, DBIx::Custom::Where, DBIx::Custom::Model, DBIx::Custom::Order
connector
my $connector = $dbi->connector; $dbi = $dbi->connector(DBIx::Connector->new(...));
Connection manager object. if connector is set, you can get dbh through connection manager. conection manager object must have dbh mehtod.
dbh
This is DBIx::Connector example. Please pass default_dbi_option to DBIx::Connector new method.
default_dbi_option
new
my $connector = DBIx::Connector->new( "dbi:mysql:database=$DATABASE", $USER, $PASSWORD, DBIx::Custom->new->default_dbi_option ); my $dbi = DBIx::Custom->connect(connector => $connector);
dsn
my $dsn = $dbi->dsn; $dbi = $dbi->dsn("DBI:mysql:database=dbname");
Data source name, used when connect method is executed.
connect
dbi_option
my $dbi_option = $dbi->dbi_option; $dbi = $dbi->dbi_option($dbi_option);
DBI option, used when connect method 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 method is executed, default to the following values.
{ RaiseError => 1, PrintError => 0, AutoCommit => 1, }
filters
my $filters = $dbi->filters; $dbi = $dbi->filters(\%filters);
Filters, registered by register_filter method.
register_filter
last_sql
my $last_sql = $dbi->last_sql; $dbi = $dbi->last_sql($last_sql);
Get last successed SQL executed by execute method.
execute
models
my $models = $dbi->models; $dbi = $dbi->models(\%models);
Models, included by include_model method.
include_model
password
my $password = $dbi->password; $dbi = $dbi->password('lkj&le`@s');
Password, used when connect method 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.
quote
my quote = $dbi->quote; $dbi = $dbi->quote('"');
Reserved word quote. Default to double quote '"' except for mysql. In mysql, default to back quote '`'
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]'.
tag_parse
my $tag_parse = $dbi->tag_parse(0); $dbi = $dbi->tag_parse;
Enable DEPRECATED tag parsing functionality, default to 1. If you want to disable tag parsing functionality, set to 0.
user
my $user = $dbi->user; $dbi = $dbi->user('Ken');
User name, used when connect method is executed.
DBIx::Custom inherits all methods from Object::Simple and use all methods of DBI and implements the following new ones.
available_data_type
print $dbi->available_data_type;
Get available data types. You can use these data types in type rule's from1 and from2 section.
type rule
from1
from2
available_type_name
print $dbi->available_type_name;
Get available type names. You can use these type names in type_rule's into1 and into2 section.
type_rule
into1
into2
assign_param
my $assign_param = $dbi->assign_param({title => 'a', age => 2});
Create assign parameter.
title = :title, author = :author
This is equal to update_param exept that set is not added.
update_param
column
my $column = $dbi->column(book => ['author', 'title']);
Create column clause. The follwoing column clause is created.
book.author as "book.author", book.title as "book.title"
You can change separator by separator method.
separator
# Separator is double underbar $dbi->separator('__'); book.author as "book__author", book.title as "book__title" # Separator is hyphen $dbi->separator('-'); book.author as "book-author", book.title as "book-title"
my $dbi = DBIx::Custom->connect( dsn => "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.
AutoCommit
RaiseError
PrintError
my $model = $dbi->create_model( table => 'book', primary_key => 'id', join => [ 'inner join company on book.comparny_id = company.id' ], );
Create DBIx::Custom::Model object and initialize model. the module is also used from model method.
model
$dbi->model('book')->select(...);
my $dbh = $dbi->dbh;
Get DBI database handle. if connector is set, you can get database handle through connector object.
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.
each_table
$dbi->each_table( sub { my ($dbi, $table, $table_info) = @_; my $table_name = $table_info->{TABLE_NAME}; } );
Iterate all table informationsfrom database. Argument is callback when one table is found. Callback receive three arguments, dbi object, table name, table information.
my $result = $dbi->execute( "select * from book where title = :title and author like :author", {title => 'Perl', author => '%Ken%'} ); my $result = $dbi->execute( "select * from book where title = :book.title and author like :book.author", {'book.title' => 'Perl', 'book.author' => '%Ken%'} );
Execute SQL. SQL can contain column parameter such as :author and :title. You can append table name to column name such as :book.title and :book.author. Second argunet is data, embedded into column parameter. Return value is DBIx::Custom::Result object when select statement is executed, or the count of affected rows when insert, update, delete statement is executed.
Parameter is replaced by placeholder ?.
?
# Before select * from book where title = :title and author like :author # After select * from where title = ? and author like ?;
You can specify operator with parameter by name{operator} syntax. This is EXPERIMENTAL.
name{operator}
# Before select * from book where :title{=} and :author{like} # After select * from where title = ? and author like ?;
The following opitons are available.
filter
filter => { title => sub { uc $_[0] } author => sub { uc $_[0] } } # Filter name filter => { title => 'upper_case', author => 'upper_case' } # At once filter => [ [qw/title author/] => sub { uc $_[0] } ]
Filter. You can set subroutine or filter name registered by by register_filter. This filter is executed before data is saved into database. and before type rule filter is executed.
query
query => 1
execute method return DBIx::Custom::Query object, not executing SQL. You can check executed SQL and columns order.
my $sql = $query->sql; my $columns = $query->columns;
table
table => 'author'
If you want to omit table name in column name and enable into1 and into2 type filter, You must set table option.
$dbi->execute("select * from book where title = :title and author = :author", {title => 'Perl', author => 'Ken', table => 'book'); # Same $dbi->execute( "select * from book where title = :book.title and author = :book.author", {title => 'Perl', author => 'Ken');
bind_type
Specify database bind data type.
bind_type => [image => DBI::SQL_BLOB] bind_type => [[qw/image audio/] => DBI::SQL_BLOB]
This is used to bind parameter by bind_param of statment handle.
bind_param
$sth->bind_param($pos, $value, DBI::SQL_BLOB);
table_alias
table_alias => {user => 'hiker'}
Table alias. Key is real table name, value is alias table name. If you set table_alias, you can enable into1 and into2 type rule on alias table name.
type_rule_off
type_rule_off => 1
Turn into1 and into2 type rule off.
type_rule1_off
type_rule1_off => 1
Turn into1 type rule off.
type_rule2_off
type_rule2_off => 1
Turn into2 type rule off.
$dbi->delete(table => 'book', where => {title => 'Perl'});
Execute delete statement.
append
Same as select method's append option.
Same as execute method's filter option.
id
id => 4 id => [4, 5]
ID corresponding to primary_key. You can delete rows by id and primary_key.
primary_key
$dbi->delete( parimary_key => ['id1', 'id2'], id => [4, 5], table => 'book', );
The above is same as the followin one.
$dbi->delete(where => {id1 => 4, id2 => 5}, table => 'book');
prefix
prefix => 'some'
prefix before table name section.
delete some from book
Same as execute method's query option.
table => 'book'
Table name.
Same as select method's where option.
See id option.
Same as execute method's bind_type option.
Same as execute method's type_rule_off option.
Same as execute method's type_rule1_off option.
Same as execute method's type_rule2_off option.
delete_all
$dbi->delete_all(table => $table);
Execute delete statement for all rows. Options is same as delete.
$dbi->insert({title => 'Perl', author => 'Ken'}, table => 'book');
Execute insert statement. First argument is row data. Return value is affected row count.
If you want to set constant value to row data, use scalar reference as parameter value.
{date => \"NOW()"}
ID corresponding to primary_key. You can insert a row by id and primary_key.
$dbi->insert( {title => 'Perl', author => 'Ken'} parimary_key => ['id1', 'id2'], id => [4, 5], table => 'book' );
$dbi->insert( {id1 => 4, id2 => 5, title => 'Perl', author => 'Ken'}, table => 'book' );
prefix => 'or replace'
prefix before table name section
insert or replace into book
primary_key => 'id' primary_key => ['id1', 'id2']
Primary key. This is used by id option.
insert_param
my $insert_param = $dbi->insert_param({title => 'a', age => 2});
Create insert parameters.
(title, author) values (title = :title, age = :age);
$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 DBIx::Custom::Model -base; 1;
Model modules, extending name space module.
MyModel/book.pm
package MyModel::book; use MyModel -base; 1;
MyModel/company.pm
package MyModel::company; use MyModel -base; 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.
map_param
my $map_param = $dbi->map_param( {id => 1, authro => 'Ken', price => 1900}, 'id' => 'book.id', 'author' => ['book.author' => sub { '%' . $_[0] . '%' }], 'price' => [ 'book.price', {if => sub { length $_[0] }} ] );
Map paramters to other key and value. First argument is original parameter. this is hash reference. Rest argument is mapping. By default, Mapping is done if the value length is not zero.
'id' => 'book.id'
This is only key mapping. Value is same as original one.
(id => 1) is mapped to ('book.id' => 1) if value length is not zero.
'author' => ['book.author' => sub { '%' . $_[0] . '%' }]
This is key and value mapping. Frist element of array reference is mapped key name, second element is code reference to map the value.
(author => 'Ken') is mapped to ('book.author' => '%Ken%') if value length is not zero.
'price' => ['book.price', {if => 'exists'}] 'price' => ['book.price', sub { '%' . $_[0] . '%' }, {if => 'exists'}] 'price' => ['book.price', {if => sub { defined shift }}]
If you need condition, you can sepecify it. this is code reference or 'exists'. By default, condition is the following one.
sub { defined $_[0] && length $_[0] }
merge_param
my $param = $dbi->merge_param({key1 => 1}, {key1 => 1, key2 => 2});
Merge parameters.
{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;
my $model = $dbi->model('book');
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
my $dbi = DBIx::Custom->new( dsn => "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
order
my $order = $dbi->order;
Create a new DBIx::Custom::Order object.
$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.
$dbi->type_rule( into1 => { date => sub { ... }, datetime => sub { ... } }, into2 => { date => sub { ... }, datetime => sub { ... } }, from1 => { # DATE 9 => sub { ... }, # DATETIME or TIMESTAMP 11 => sub { ... }, } from2 => { # DATE 9 => sub { ... }, # DATETIME or TIMESTAMP 11 => sub { ... }, } );
Filtering rule when data is send into and get from database. This has a little complex problem.
In into1 and into2 you can specify type name as same as type name defined by create table, such as DATETIME or DATE.
DATETIME
DATE
Note that type name and data type don't contain upper case. If these contain upper case charactor, you convert it to lower case.
into2 is executed after into1.
Type rule of into1 and into2 is enabled on the following column name.
issue_date issue_datetime
This need table option in each method.
book.issue_date book.issue_datetime
You get all type name used in database by available_type_name.
In from1 and from2 you specify data type, not type name. from2 is executed after from1. You get all data type by available_data_type.
You can also specify multiple types at once.
$dbi->type_rule( into1 => [ [qw/DATE DATETIME/] => sub { ... }, ], );
my $result = $dbi->select( table => 'book', column => ['author', 'title'], where => {author => 'Ken'}, );
Execute select statement.
append => 'order by title'
Append statement to last of SQL.
column => 'author' column => ['author', 'title']
Column clause.
if column is not specified, '*' is set.
column => '*'
You can specify hash of array reference.
column => [ {book => [qw/author title/]}, {person => [qw/name age/]} ]
This is expanded to the following one by using colomn method.
colomn
book.author as "book.author", book.title as "book.title", person.name as "person.name", person.age as "person.age"
You can specify array of array reference, first argument is column name, second argument is alias.
column => [ ['date(book.register_datetime)' => 'book.register_date'] ];
Alias is quoted properly and joined.
date(book.register_datetime) as "book.register_date"
ID corresponding to primary_key. You can select rows by id and primary_key.
$dbi->select( parimary_key => ['id1', 'id2'], id => [4, 5], table => 'book' );
$dbi->select( where => {id1 => 4, id2 => 5}, table => 'book' );
param
param => {'table2.key3' => 5}
Parameter shown before where clause.
For example, if you want to contain tag in join clause, you can pass parameter by param option.
join => ['inner join (select * from table2 where table2.key3 = :table2.key3)' . ' as table2 on table1.key1 = table2.key1']
prefix => 'SQL_CALC_FOUND_ROWS'
Prefix of column cluase
select SQL_CALC_FOUND_ROWS title, author from book;
join
join => [ 'left outer join company on book.company_id = company_id', 'left outer join location on company.location_id = location.id' ]
Join clause. If column cluase or where clause contain table name like "company.name", join clausees needed when SQL is created is used automatically.
$dbi->select( table => 'book', column => ['company.location_id as 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, column and where clause contain "company" table, the following SQL is created
select company.location_id as location_id from book left outer join company on book.company_id = company.id where company.name = ?;
You can specify two table by yourself. This is useful when join parser can't parse the join clause correctly. This is EXPERIMENTAL.
$dbi->select( table => 'book', column => ['company.location_id as location_id'], where => {'company.name' => 'Orange'}, join => [ { clause => 'left outer join location on company.location_id = location.id', table => ['company', 'location'] } ] );
# Hash refrence where => {author => 'Ken', 'title' => 'Perl'} # DBIx::Custom::Where object where => $dbi->where( clause => ['and', 'author = :author', 'title like :title'], param => {author => 'Ken', title => '%Perl%'} ); # Array reference 1 (array reference, hash referenc). same as above where => [ ['and', 'author = :author', 'title like :title'], {author => 'Ken', title => '%Perl%'} ]; # Array reference 2 (String, hash reference) where => [ 'title like :title', {title => '%Perl%'} ] # String where => 'title is null'
Where clause.
wrap
Wrap statement. This is array reference.
$dbi->select(wrap => ['select * from (', ') as t where ROWNUM < 10']);
This option is for Oracle and SQL Server paging process.
$dbi->update({title => 'Perl'}, table => 'book', where => {id => 4});
Execute update statement. First argument is update row data.
ID corresponding to primary_key. You can update rows by id and primary_key.
$dbi->update( {title => 'Perl', author => 'Ken'} parimary_key => ['id1', 'id2'], id => [4, 5], table => 'book' );
$dbi->update( {title => 'Perl', author => 'Ken'} where => {id1 => 4, id2 => 5}, table => 'book' );
update or replace book
update_all
$dbi->update_all({title => 'Perl'}, table => 'book', );
Execute update statement for all rows. Options is same as update method.
my $update_param = $dbi->update_param({title => 'a', age => 2});
Create update parameter tag.
set title = :title, author = :author
my $where = $dbi->where( clause => ['and', 'title = :title', 'author = :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
DBIX_CUSTOM_DEBUG
If environment variable DBIX_CUSTOM_DEBUG is set to true, executed SQL and bind values are printed to STDERR.
DBIX_CUSTOM_DEBUG_ENCODING
DEBUG output encoding. Default to UTF-8.
DBIx::Custom
# Attribute methods data_source # will be removed at 2017/1/1 dbi_options # will be removed at 2017/1/1 filter_check # will be removed at 2017/1/1 reserved_word_quote # will be removed at 2017/1/1 cache_method # will be removed at 2017/1/1 # Methods create_query # will be removed at 2017/1/1 apply_filter # will be removed at 2017/1/1 select_at # will be removed at 2017/1/1 delete_at # will be removed at 2017/1/1 update_at # will be removed at 2017/1/1 insert_at # will be removed at 2017/1/1 register_tag # will be removed at 2017/1/1 default_bind_filter # will be removed at 2017/1/1 default_fetch_filter # will be removed at 2017/1/1 insert_param_tag # will be removed at 2017/1/1 register_tag_processor # will be removed at 2017/1/1 update_param_tag # will be removed at 2017/1/1 # Options select method relation option # will be removed at 2017/1/1 select method param option # will be removed at 2017/1/1 select method column option [COLUMN, as => ALIAS] format # will be removed at 2017/1/1 # Others execute("select * from {= title}"); # execute method's # tag parsing functionality # will be removed at 2017/1/1 Query caching # will be removed at 2017/1/1
DBIx::Custom::Model
# Attribute method filter # will be removed at 2017/1/1 name # will be removed at 2017/1/1 type # will be removed at 2017/1/1
DBIx::Custom::Query
# Attribute method default_filter # will be removed at 2017/1/1
DBIx::Custom::QueryBuilder
# Attribute method tags # will be removed at 2017/1/1 tag_processors # will be removed at 2017/1/1 # Method register_tag # will be removed at 2017/1/1 register_tag_processor # will be removed at 2017/1/1 # Others build_query("select * from {= title}"); # tag parsing functionality # will be removed at 2017/1/1
DBIx::Custom::Result
# Attribute method filter_check # will be removed at 2017/1/1 # Methods end_filter # will be removed at 2017/1/1 remove_end_filter # will be removed at 2017/1/1 remove_filter # will be removed at 2017/1/1 default_filter # will be removed at 2017/1/1
DBIx::Custom::Tag
This module is DEPRECATED! # will be removed at 2017/1/1
If a functionality is DEPRECATED, you can know it by DEPRECATED warnings except for attribute method. You can check all DEPRECATED functionalities by document. DEPRECATED functionality is removed after five years, but if at least one person use the functionality and tell me that thing I extend one year each time he tell me it.
EXPERIMENTAL functionality will be changed without warnings.
This policy was changed at 2011/6/28
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'
Unknown directive: =itme
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.