DBIx::Custom::Guide - DBIx::Custom Guide
This guide is now writing.
DBIx::Custom is the class to make easy to execute SQL. This is DBI wrapper class like DBIx::Class or DBIx::Simple. You can do thing more easy than DBIx::Class, more flexible than DBIx::Simple.
DBIx::Custom is not O/R mapper, O/R mapper is usefule, but you must learn many things. Created SQL is sometimes inefficient, and in many cases you create raw SQL because O/R mapper can't make complex SQL
DBIx::Custom is opposit of O/R mapper. The main purpose is that we respect SQL and make easy difficult works if you use only DBI. If you already learn SQL, it is easy to use DBIx::Custom.
I explain DBIx::Custom a little in this section. In DBIx::Custom, you embbed tag in SQL.
select * from book where {= title} and {=author};
The part arround {} is tag. This SQL is converted to the one which contains place holder.
select * from book where title = ? and author = ?;
Maybe you ask me that this conversion is meaningful. On the top of this, usuful features is implemented. See the following descriptions.
If you use DBI, you must specify place holder binding value as array.
$sth->execute(@bind);
If you use DBIx::Custom, you specify it as hash reference.
my $param = {title => 'Perl', author => 'Ken'}; $dbi->execute($sql, $param);
DBIx::Custom provides filtering system. For example, You think that about date value you want to manipulate it as date object like Time::Piece in Perl, and want to convert it to database DATE format. and want to do reverse.
You can use filtering system.
At first, register filter.
$dbi->register_filter( tp_to_date => sub { ... }, date_to_tp => sub { ... } );
next, apply this filter to each column.
$dbi->apply_filter('book', 'issue_date' => {out => 'tp_to_date', in => 'date_to_tp'} );
out is perl-to-database way. in is perl-from-database way.
out
in
This filter is automatically enabled in many method.
$dbi->insert(table => 'book', param => {issue_date => $tp});
It is difficult to create selective where clause in DBI. For example, If title and author is specified, we create the following SQL.
title
author
If only title is specified, the following one
select * from book where title = ?;
If only author is specified, the following one,
select * from book where author = ?;
This is hard work. Generally we use modules like SQL::Abstract. DBIx::Custom prepare the way to make it easy.
# Where object my $where = $dbi->where; # Search condition $where->clause( ['and', '{= title}', {'= author'}] ); # Setting to automatically select needed column $where->param({title => 'Perl'}); # Embbed where clause to SQL my $sql = "select * from book $where";
You can create where clause which has selected search condition. You can write nesting of where clause and or condition
or
DBIx::Custom provides methods for insert, update, delete, select There are insert(), update(), delete(),select().
insert()
update()
delete()
select()
my $param = {title => 'Perl', author => 'Ken'}; $dbi->insert(table => 'book', param => $param);
You can register method for table.
$dbi->table('book')->method( list => sub { ... }, something => sub { ... } );
use the mehtod.
$dbi->table('book')->list;
Many O/R mapper must create class for table, but DBIx::Custom make it easy.
DBIx::Custom is very useful. See the following if you are interested in it.
Load DBIx::Custom.
use DBIx::Custom;
use connect() to connect to database. Return value is DBIx::Custom object.
connect()
my $dbi = DBIx::Custom->connect( data_source => "dbi:mysql:database=bookstore", user => 'ken', password => '!LFKD%$&', dbi_options => {mysql_enable_utf8 => 1} );
data_source must be one corresponding to the database system. The following ones are data source example.
data_source
MySQL
"dbi:mysql:database=$database" "dbi:mysql:database=$database;host=$hostname;port=$port"
SQLite
"dbi:SQLite:dbname=$database" "dbi:SQLite:dbname=:memory:"
PostgreSQL
"dbi:Pg:dbname=$dbname"
Oracle
"dbi:Oracle:$dbname" "dbi:Oracle:host=$host;sid=$sid"
ODBC(Microsoft Access)
"dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=hoge.mdb"
ODBC(SQL Server)
"dbi:ODBC:driver={SQL Server};Server=(local);database=test;Trusted_Connection=yes;AutoTranslate=No;"
If authentication is needed, you can specify user and password
user
password
DBIx::Custom is wrapper class of DBI. You can use all methods of DBI from DBIx::Custom object.
$dbi->do(...); $dbi->begin_work;
use dhb() to get database handle of DBI
dhb()
my $dbh = $dbi->dbh;
By default, the following ones is set to database handle attributes.
RaiseError -> 1 PrintError -> 0 AutoCommit -> 1
If fatal error occuer, program terminate. If SQL is executed, commit is executed automatically.
There are following methods.
use insert() to insert row into database
$dbi->insert(table => 'book', param => {title => 'Perl', author => 'Ken'});
table is table name, param is insert data.
table
param
Following SQL is executed.
insert into (title, author) values (?, ?);
use update() to update row in database.
$dbi->update(table => 'book', param => {title => 'Perl', author => 'Ken'}, where => {id => 5});
table is table name, param is update data, where is condition.
where
update book set title = ?, author = ?;
You can't execute update() without where for safety. use update_all() if you want to update all rows.
update_all()
$dbi->update_all(table => 'book', param => {title => 'Perl', author => 'Ken'});
use delete() to delete rows from database.
$dbi->delete(table => 'book', where => {author => 'Ken'});
table is table name, where is condition.
delete from book where id = ?;
You can't execute delete() without where for safety. use delete_all() if you want to delete all rows.
delete_all()
$dbi->delete_all(table => 'book');
use select() to select rows from database
my $result = $dbi->select(table => 'book');
select * from book;
Return value is DBIx::Custom::Result object. use fetch() to fetch row.
fetch()
while (my $row = $result->fetch) { my $title = $row->[0]; my $author = $row->[1]; }
See "3. Fetch row" in 3. Fetch row about DBIx::Custom::Result.
Continue more examples.
my $result = $dbi->select( table => 'book', column => ['author', 'title'], where => {author => 'Ken'} );
column is column names, where is condition.
column
select author, title from book where author = ?;
Next example.
my $result = $dbi->select( table => 'book', where => {'book.name' => 'Perl'}, relation => {'book.id' => 'rental.book_id'} );
relation is relation of tables. This is inner join.
relation
select * from book, rental where book.name = ? and book.id = rental.book_id;
my $result = $dbi->select( table => 'book', where => {author => 'Ken'}, append => 'for update', );
append is string appending to end of SQL.
append
select * book where author = ? for update;
appned is also used at insert(), update(), update_all() delete(), delete_all(), and select().
appned
Instead of column and table, you can use selection. This is used to specify column names and table names at once
selection
my $selection = <<"EOS"; title, author, company_name from book inner join company on book.company_id = company.id EOS $dbi->select(selection => $selection);
Note that you can't use where clause in selection. use clause like "inner join".
execute()
use execute() to execute SQL
$dbi->execute("select * from book;");
Process tag and execute SQL.
$dbi->execute( "select * from book {= title} and {= author};" param => {title => 'Perl', author => 'Ken'} );
select * from book title = ? and author = ?;
Values of title and author is embbdeded into placeholder.
See "5. Tag" in 5. Tag about tag.
You don't have to wirte last semicolon in execute().
$dbi->execute('select * from book');
update_at()
To update row by using primary key, use update_at()
$dbi->update_at( table => 'book', primary_key => ['id'], where => ['123'], param => {name => 'Ken'} );
In this example, row which id column is 123 is updated. NOTE that you must pass array reference as where. If param contains primary key, the key and value is delete from param.
delete_at()
To delete row by using primary key, use delete_at()
$dbi->delete_at(table => 'book', primary_key => ['id'], where => ['123']);
In this example, row which id column is 123 is deleted. NOTE that you must pass array reference as where.
You can also write arguments like this.
$dbi->delete_at(table => 'book', primary_key => ['id'], param => {id => '123'});
select_at()
To select row by using primary key, use select_at().
$dbi->select_at(table => 'book', primary_key => ['id'], where => ['123']);
In this example, row which id colunm is 123 is selected. NOTE that you must pass array reference as where.
$dbi->select_at(table => 'book', primary_key => ['id'], param => {id => '123'});
Return value of select() is DBIx::Custom::Result object. There are many methods to fetch row.
use fetch() to fetch a row and assign it into array reference.
my $row = $result->fetch;
You can get all rows.
fetch_first()
use fetch_first() to fetch only first row.
my $row = $result->fetch_first;
You can't fetch rest rows because statement handle finish() is executed.
finish()
fetch_multi()
use fetch_multi() to fetch rows and assign it into array reference which has array references as element.
while (my $rows = $result->fetch_multi(2)) { my $title0 = $rows->[0][0]; my $author0 = $rows->[0][1]; my $title1 = $rows->[1][0]; my $author1 = $rows->[1][1]; }
Specify row count as argument.
You can get the following data.
[ ['Perl', 'Ken'], ['Ruby', 'Mark'] ]
fetch_all
use fetch_all() to fetch all rows and assign it into array reference which has array reference as element.
fetch_all()
my $rows = $result->fetch_all;
fetch_hash()
use fetch_hash() to fetch a row and assign it into hash reference.
while (my $row = $result->fetch_hash) { my $title = $row->{title}; my $author = $row->{author}; }
fetch_hash_first()
use fetch_hash_first() to fetch only first row and assign it into hash reference.
my $row = $result->fetch_hash_first;
fetch_hash_multi()
use fetch_hash_multi() to fetch rows and assign it into array reference which has hash references as element.
while (my $rows = $result->fetch_hash_multi(5)) { my $title0 = $rows->[0]{title}; my $author0 = $rows->[0]{author}; my $title1 = $rows->[1]{title}; my $author1 = $rows->[1]{author}; }
[ {title => 'Perl', author => 'Ken'}, {title => 'Ruby', author => 'Mark'} ]
fetch_hash_all()
use fetch_hash_all() to fetch all rows and assign it into array reference which has hash references as element.
my $rows = $result->fetch_hash_all;
sth()
use <sth()> to get statement handle.
my $sth = $result->sth;
DBIx::Custom provide value filtering. For example, You maybe want to convert Time::Piece object to database date format when register data into database. and convert database date fromat to Time::Piece object when get data from database.
register_filter()
use register_filter() to register filter.
$dbi->register_filter( # Time::Piece object to DATE format tp_to_date => sub { my $date = shift; return '0000-00-00' unless $tp; return $tp->strftime('%Y-%m-%d'); }, # DATE to Time::Piece object date_to_tp => sub { my $date = shift; return if $date eq '0000-00-00'; return Time::Piece->strptime($date, '%Y-%m-%d'); }, );
Registered filter is used by apply_filter() or etc.
apply_filter()
use apply_filter() to apply registered filter.
$dbi->apply_filter('book', issue_date => {out => 'tp_to_date', in => 'date_to_tp'}, first_issue_date => {out => 'tp_to_date', in => 'date_to_tp'} );
First argument is table name. Arguments after first argument are pairs of column name and fitering rule. out of filtering rule is filter which is used when data is send to database. in of filtering rule is filter which is used when data is got from database.
You can specify code reference as filter.
issue_date => {out => sub { ... }, in => sub { ... }}
Applied filter become effective at insert()>, update(), update_all(), delete(), delete_all(), select().
my $tp = Time::Piece->strptime('2010/10/14', '%Y/%m/%d'); my $result = $dbi->select(table => 'book', where => {issue_date => $tp});
When data is send to database, Time::Piece object is converted to database date format "2010-10-14"
When data is fetched, database date format is converted to Time::Piece object.
my $row = $resutl->fetch_hash_first; my $tp = $row->{issue_date};
You can also use column name which contains table name.
$dbi->select( table => 'book', where => {'book.issue_date' => $tp} );
In fetching, Filter is effective if you use "TABLE__COLUMN" as column name.
my $result = $dbi->execute( "select issue_date as book__issue_date from book");
You can apply end filter execute after in filter.
end
$dbi->apply_filter('book', issue_date => {out => 'tp_to_date', in => 'date_to_tp', end => 'tp_to_displaydate'}, );
filter
You can apply individual filter . This filter overwrite the filter by apply_filter()
use filter option to apply individual filter when data is send to database. This option is used at insert(), update(), update_all(), delete(), delete_all(), select(), execute().
insert() example:
$dbi->insert( table => 'book', param => {issue_date => $tp, first_issue_date => $tp}, filter => {issue_date => 'tp_to_date', first_issue_date => 'tp_to_date'} );
execute() example:
my $sql = <<"EOS"; select YEAR(issue_date) as issue_year from book where YEAR(issue_date) = {? issue_year} EOS
my $result = $dbi->execute( $sql, param => {issue_year => '2010'}, filter => {issue_year => 'tp_to_year'} );
You can also apply indivisual filter when you fetch row. use DBIx::Custom::Result's filter().
DBIx::Custom::Result
filter()
$result->filter(issue_year => 'year_to_tp');
end_filter()
You can add filter at end. It is useful to create last output. use end_filter() to add end filter.
$result->end_filter(issue_date => sub { my $tp = shift; return '' unless $tp; return $tp->strftime('%Y/%m/%d %h:%m:%s (%a)'); });
In this example, Time::Piece object is converted to readable format.
each_column()
It is useful to apply filter automatically at date type columns. You can use each_column() to process all column infos.
$dbi->each_column( sub { my ($self, $table, $column, $info) = @_; my $type = $info->{TYPE_NAME}; my $filter = $type eq 'DATE' ? {out => 'tp_to_date', in => 'date_to_tp'} : $type eq 'DATETIME' ? {out => 'tp_to_datetime', in => 'datetime_to_tp'} : undef; $self->apply_filter($table, $column, $filter) if $filter; } );
each_column() receive callback. callback arguments are DBIx::Custom object, table name, column name, column information. Filter is applied automatically by column type.
You can embedd tag into SQL.
select * from book where {= title} and {like author};
{= title} and {like author} are tag. Tag has the folloring format.
{TAG_NAME ARG1 ARG2 ...}
Tag start { and end }. Don't insert space between {} and tag name.
{
}
{}
{ and } are reserved word. If you want to use these, escape it by '\';
select from book \\{ ... \\}
\ is perl's escape character, you need two \.
Tag is expanded before executing SQL.
select * from book where title = ? and author like ?;
use execute() to execute SQL which contains tag
my $sql = "select * from book where {= author} and {like title};" $dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'});
You can specify values embedded into place holder as hash reference using param option.
You can specify filter() at execute().
$dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'} filter => {title => 'to_something');
Note that at execute() the filter applied by apply_filter() don't has effective to columns. You have to use table tag in SQL
my $sql = "select * from {table book} where {= author} and {like title};"
The following tag is available.
{table NAME} -> NAME
This is used to specify table name in SQL. If you specify table name, Filtering by apply_filter() is effective.
?
{? NAME} -> ?
=
{= NAME} -> NAME = ?
<>
{<> NAME} -> NAME <> ?
<
{< NAME} -> NAME < ?
>
{> NAME} -> NAME > ?
>=
{>= NAME} -> NAME >= ?
<=
{<= NAME} -> NAME <= ?
like
{like NAME} -> NAME like ?
{in NAME COUNT} -> NAME in [?, ?, ..]
insert_param
{insert_param NAME1 NAME2} -> (NAME1, NAME2) values (?, ?)
update_param
{update_param NAME1 NAME2} -> set NAME1 = ?, NAME2 = ?
It is ok if there are same name's columns. Let's think two date comparison.
my $sql = "select * from table where {> date} and {< date};";
In this case, You specify paramter values as array reference.
my $dbi->execute($sql, param => {date => ['2010-10-01', '2012-02-10']});
register_tag()
You can register custom tag. use register_tag() to register tag.
$dbi->register_tag( '=' => sub { my $column = shift; return ["$column = ?", [$column]]; } );
This is implementation of = tag. Tag format is the following one.
In case = tag. Format is
{= title}
So subroutine receive one argument "title". You have to return array reference in the following format.
[ String after expanding, [COLUMN1(This is used for place holder), COLUMN2 , ...] ]
First element is expanded stirng. In this example,
'title = ?'
Secount element is array reference which is used to embedd value to place holder. In this example,
['title']
If there are more than one placeholders, This elements is multipul.
You return the following array reference.
['title = ?', ['title']]
See source of DBIx::Custom::Tag to see many implementation.
You want to search multiple conditions in many times. Let's think the following three cases.
Case1: Search only title
where {= title}
Case2: Search only author
where {= author}
Case3: Search title and author
where {= title} and {=author}
DBIx::Custom support dinamic where clause creating. At first, create DBIx::Custom::Where object by where().
where()
my $where = $dbi->where;
Set clause by clause()
clause()
$where->clause( ['and', '{= title'}, '{= author}'] );
clause is the following format.
clause
['or' or 'and', TAG1, TAG2, TAG3]
First argument is 'or' or 'and'. Later than first argument are tag names.
You can write more complex format.
['and', '{= title}', ['or', '{= author}', '{like date}'] ]
This mean "{=title} and ( {=author} or {like date} )".
After setting clause, set param.
$where->param({title => 'Perl'});
In this example, parameter contains only title.
If you execute string_to(), you can get where clause which contain only parameter name.
string_to()
my $where_clause = $where->to_string;
Parameter name is only title, the following where clause is created.
You can also create where clause by stringification.
my $where_clause = "$where";
This is useful to embbed it into SQL.
Even if same name tags exists, you can create where clause. Let's think that there are starting date and ending date.
my $param = {start_date => '2010-11-15', end_date => '2011-11-21'};
In this case, you set parameter value as array reference.
my $p = {date => ['2010-11-15', '2011-11-21']};
You can embbed these values into same name tags.
$where->clause( ['and', '{> date}', '{< date}'] ); $where->param($p);
If starting date isn't exists, create the following parameter.
my $p = {date => [$dbi->not_exists, '2011-11-21']};
You can get DBIx::Custom::NotExists object by not_exists() This mean correnspondinf value isn't exists.
not_exists()
If ending date isn't exists, create the following parameter.
my $p = {date => ['2010-11-15']};
If both date isn't exists, create the following parameter.
my $p = {date => []};
This logic is a little difficut. See the following ones.
my @date; push @date, exists $param->{start_date} ? $param->{start_date} : $dbi->not_exists; push @date, $param->{end_date} if exists $param->{end_date}; my $p = {date => \@date};
You can pass DBIx::Custom::Where object to where of select().
my $where = $dbi->where; $where->clause(['and', '{= title}', '{= author}']); $where->param({title => 'Perl'}); my $result = $dbi->select(table => 'book', where => $where);
You can also pass it to where of update()�Adelete()
DBIx::Custom::Where object is embedded into SQL.
my $where = $dbi->where; $where->clause(['and', '{= title}', '{= author}']); $where->param({title => 'Perl'}); my $sql = <<"EOS"; select * from {table book}; $where EOS $dbi->execute($sql, param => $param);
you can define model extending DBIx::Custom::Model to improve source code view.
At first, you create basic model class extending <DBIx::Custom::Model>.
package MyModel; use base 'DBIx::Custom::Model';
Next, you create each model classes.
MyModel::book
package MyModel::book; use base 'MyModel'; sub insert { ... } sub list { ... }
MyModel::company
package MyModel::company; use base 'MyModel'; sub insert { ... } sub list { ... }
The follwoing modules location is needed.
MyModel.pm MyModel / book.pm / company.pm
You can include these models by include_model()
include_model()
$dbi->include_model('MyModel');
First argument is name space of model.
You can use model like this.
my $result = $dbi->model('book')->list;
In mode, You can use such as methods, insert(), update(), update_all(), delete(), delete_all(), select() without table option.
$dbi->model('book')->insert(param => $param);
Model is DBIx::Custom::Model.
If you need table name�Ayou can get it by table().
table()
my $table = $model->table;
You can get DBIx::Custom.
my $dbi = $model->dbi;
You can also call all methods of DBIx::Custom and DBI.
# DBIx::Custom method $model->execute($sql); # DBI method $model->begin_work; $model->commit;
If you want to get all models, you can get them by keys of models().
models()
my @models = keys %{$self->models};
You can set primary key to model.
$model->primary_key(['id', 'number_id']);
Primary key is used by update_at(), delete_at(), select_at().
You can set column names
$model->columns(['id', 'number_id']);
Column names is automarically set by setup_model(). This method is needed to be call after include_model().
setup_model()
$dbi->setup_model;
You can set relation
$model->relation({'book.company_id' => 'company.id'});
This relation is used by select(), select_at()
Class name, model name, and table name is a little different. Generally Class name is model name, and table name is model name.
CLASS MODEL TABLE book (CLASS) -> book (MODEL) -> book
You can change model name.
package MyModel::book; __PACAKGE__->attr(name => 'book_model'); CLASS MODEL TABLE book book_model (MODEL) -> book_model
Model name is the name used by model() of DBIx::Custom.
$dbi->model('book_model');
You can change table name.
package MyModel::book; __PACAKGE__->attr(table => 'book_table'); CLASS MODEL TABLE book (CLASS) -> book book_table
Table name is the table really accessed.
$dbi->model('book')->insert(...); # access to "book_table"
To create column clause automatically, use column_clause(). Valude of table and columns is used.
column_clause()
columns
my $column_clause = $model->column_clause;
If table is 'book'�Acolumn is ['id', 'name'], the following clause is created.
book.id as id, book.name as name
These column name is for removing column name ambiguities.
If you remove some columns, use remove option.
remove
my $column_clause = $model->column_clause(remove => ['id']);
If you add some column, use add option.
add
my $column_clause = $model->column_clause(add => ['company.id as company__id']);
Model examples
package MyDBI; use base 'DBIx::Custom'; sub connect { my $self = shift->SUPER::connect(@_); $self->include_model( MyModel => [ 'book', 'company' ] ); } package MyModel::book; use base 'DBIx::Custom::Model'; __PACKAGE__->attr('primary_key' => sub { ['id'] }; sub insert { ... } sub list { ... } package MyModel::company; use base 'DBIx::Custom::Model'; __PACKAGE__->attr('primary_key' => sub { ['id'] }; sub insert { ... } sub list { ... }
If you can't get performance, create query by query option. For example, many insert is needed.
query
my $params = [ {title => 'Perl', author => 'Ken'}, {title => 'Good day', author => 'Tom'} ] my $query = $dbi->insert(table => 'book', param => $params->[0], query => 1);
Return value is DBIx::Custom::Query object. This query is executed by execute().
foreach my $param (@$params) { $dbi->execute($query, $param); }
Performance is improved because statement handle is reused query option is used in insert(), update(), update_all(), delete(), delete_all().
Note that parameters count is same as method for creating query and execute().
You can create query from any SQL by create_query().
create_query()
my $query = $dbi->create_query( "insert into book {insert_param title author};"; );
You can add method to DBIx::Custom object. use method().
method()
$dbi->method( update_or_insert => sub { my $self = shift; # something }, find_or_create => sub { my $self = shift; # something } );
You can call these methods from DBIx::Custom object.
$dbi->update_or_insert; $dbi->find_or_create;
You can change result class. By default it is DBIx::Custom::Result.
package MyResult; use base 'DBIx::Custom::Result'; sub some_method { ... } 1; package main; use MyResult; my $dbi = DBIx::Custom->connect(...); $dbi->result_class('MyResult');
SQL after parsing tag is cached for performance. You can set cache(). By default, chaching is true.
cache()
$dbi->cache(1);
The way to cache is changed by cache_method(). Default method is the following one. Cache is saved to memory.
cache_method()
$dbi->cache_method(sub { sub { my $self = shift; $self->{_cached} ||= {}; if (@_ > 1) { # Save cache $self->{_cached}{$_[0]} = $_[1] } else { # Get cache return $self->{_cached}{$_[0]} } } });
First argument is DBIx::Custom object. Second argument is SQL before parsing. Third argument is SQL information after parsing. This is hash reference.
If third argument exists, you save cache, and if third argument isn't exists, you get chace.
You can see exsamples in the following wiki.
DBIx::Custom Wiki - Many useful examples
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.