NAME
DBIx::Custom::Guide - DBIx::Custom Guide
GUIDE
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.
- 1. Specify place holder binding value as hash refernce
-
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);
- 2. Filtering
-
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.This filter is automatically enabled in many method.
$dbi->insert(table => 'book', param => {issue_date => $tp});
- 3. Selective search condition
-
It is difficult to create selective where clause in DBI. For example, If
title
andauthor
is specified, we create the following SQL.select * from book where title = ? and author = ?;
If only
title
is specified, the following oneselect * 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 - 4. Methods for insert, update, delete, select
-
DBIx::Custom provides methods for insert, update, delete, select There are
insert()
,update()
,delete()
,select()
.my $param = {title => 'Perl', author => 'Ken'}; $dbi->insert(table => 'book', param => $param);
- 5. Register method for table.
-
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.
1. Connect to database
Load DBIx::Custom.
use DBIx::Custom;
use connect()
to connect to database. Return value is DBIx::Custom object.
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.
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
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
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.
2. Methods for insert, update, delete, or insert
There are following methods.
insert()
use insert()
to insert row into database
$dbi->insert(table => 'book',
param => {title => 'Perl', author => 'Ken'});
table
is table name, param
is insert data.
Following SQL is executed.
insert into (title, author) values (?, ?);
update()
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.
Following SQL is executed.
update book set title = ?, author = ?;
You can't execute update()
without where
for safety. use update_all()
if you want to update all rows.
$dbi->update_all(table => 'book',
param => {title => 'Perl', author => 'Ken'});
delete()
use delete()
to delete rows from database.
$dbi->delete(table => 'book',
where => {author => 'Ken'});
table
is table name, where
is condition.
Following SQL is executed.
delete from book where id = ?;
You can't execute delete()
without where
for safety. use delete_all()
if you want to delete all rows.
$dbi->delete_all(table => 'book');
select()
use select()
to select rows from database
my $result = $dbi->select(table => 'book');
Following SQL is executed.
select * from book;
Return value is DBIx::Custom::Result object. use fetch()
to fetch row.
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.
Following SQL is executed.
select author, title from book where author = ?;
Next example.
my $result = $dbi->select(
table => 'book',
column => ['company.name as company__name']
where => {'book.name' => 'Perl'},
join => ['left outer join company on book.company_id = company.id]
);
You can join table by join
.
Following SQL is executed.
select company.name as company__name
from book
left outer join company on book.company_id = company.id
where book.name = ?;
company_if of book and id of company is left outer joined.
Note that only when where
or column
contain table name, join
is joined. if you specify the following option, join
is not joined because join
is not needed.
my $result = $dbi->select(
table => 'book',
where => {'name' => 'Perl'},
join => ['left outer join company on book.company_id = company.id]
);
Following SQL is executeed.
select * from book where book.name = ?;
You can specify column names easily using mycolumn()
and column()
.
my $result = $dbi->select(
table => 'book',
column => [
$dbi->mycolumn('book' => ['name']),
$dbi->column('company' => ['id', 'name'])
],
join => ['left outer join company on book.company_id = company.id]
);
The following SQL is executed.
select book.name as name,
company.id as comapny__id,
company.name as company__name
from book
left outer join company on book.company_id = company.id
Next example.
my $result = $dbi->select(
table => 'book',
where => {author => 'Ken'},
append => 'for update',
);
append
is string appending to end of SQL.
Following SQL is executed.
select * book where author = ? for update;
appned
is also used at insert()
, update()
, update_all()
delete()
, delete_all()
, and select()
.
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'}
);
Following SQL is executed.
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');
insert by using primary key : insert_at()
To insert row by using primary key, use insert_at()
$dbi->insert_at(
table => 'book', primary_key => ['id'],
where => ['123'], param => {name => 'Ken'}
);
In this example, row which id column is 123 is inserted. NOTE that you must pass array reference as where
. If param
contains primary key, the key and value is delete from param
.
Update by using primary key : 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 by using primary key : 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 by using primary key : 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
.
You can also write arguments like this.
$dbi->select_at(table => 'book', primary_key => ['id'], param => {id => '123'});
3. Fetch row
Return value of select()
is DBIx::Custom::Result object. There are many methods to fetch row.
Fetch a row (array) : fetch()
use fetch()
to fetch a row and assign it into array reference.
my $row = $result->fetch;
You can get all rows.
while (my $row = $result->fetch) {
my $title = $row->[0];
my $author = $row->[1];
}
Fetch only first row (array) : 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.
Fetch rows (array) : 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 rows (array) : fetch_all
use fetch_all()
to fetch all rows and assign it into array reference which has array reference as element.
my $rows = $result->fetch_all;
You can get the following data.
[
['Perl', 'Ken'],
['Ruby', 'Mark']
]
Fetch a row (hash) : 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 only first row (hash) : fetch_hash_first()
use fetch_hash_first()
to fetch only first row and assign it into hash reference.
my $row = $result->fetch_hash_first;
You can't fetch rest rows because statement handle finish()
is executed.
Fetch rows (hash) : 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};
}
Specify row count as argument.
You can get the following data.
[
{title => 'Perl', author => 'Ken'},
{title => 'Ruby', author => 'Mark'}
]
Fetch all rows (hash) : 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;
You can get the following data.
[
{title => 'Perl', author => 'Ken'},
{title => 'Ruby', author => 'Mark'}
]
Statement handle : sth()
use <sth()> to get statement handle.
my $sth = $result->sth;
4. Filtering
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 : 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 : 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.
$dbi->apply_filter('book',
issue_date => {out => 'tp_to_date', in => 'date_to_tp',
end => 'tp_to_displaydate'},
);
Individual filter 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()
.
$result->filter(issue_year => 'year_to_tp');
You can remove filter by remove_filter()
$result->remove_filter;
End filtering : 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.
You can remove end_filter by end_filter
$result->remove_end_filter;
Automate applying filter : 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.
5. Tag
Basic of Tag
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
option
$dbi->execute($sql, table => ['author', 'book']);
Tag list
The following tag is available.
?
{? NAME} -> ?
=
{= NAME} -> NAME = ?
<>
{<> NAME} -> NAME <> ?
<
{< NAME} -> NAME < ?
>
{> NAME} -> NAME > ?
>=
{>= NAME} -> NAME >= ?
<=
{<= NAME} -> NAME <= ?
like
{like NAME} -> NAME like ?
in
{in NAME COUNT} -> NAME in [?, ?, ..]
insert_param
{insert_param NAME1 NAME2} -> (NAME1, NAME2) values (?, ?)
update_param
{update_param NAME1 NAME2} -> set NAME1 = ?, NAME2 = ?
Manipulate same name's columns
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 : 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.
{TAG_NAME ARG1 ARG2 ...}
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.
6. Dinamically create where clause
Dinamically create where clause : where()
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()
.
my $where = $dbi->where;
Set clause by clause()
$where->clause(
['and', '{= title'}, '{= author}']
);
clause
is the following format.
['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.
my $where_clause = $where->to_string;
Parameter name is only title, the following where clause is created.
where {= title}
You can also create where clause by stringification.
my $where_clause = "$where";
This is useful to embbed it into SQL.
In case where clause contains same name columns
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.
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};
With select()
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()
With execute()
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);
7. Model
Model
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()
$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()
.
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()
.
my @models = keys %{$self->models};
You can set primary key to model.
$model->primary_key(['id', 'number_id']);
Primary key is used by insert_at
, update_at()
, delete_at()
, select_at()
.
by filter
you can define filters applied by apply_filter()
$model->filter({
title => {out => ..., in => ..., end => ...},
author => {out => ..., in => ..., end => ...}
});
This filters is applied when include_model()
is called.
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()
.
$dbi->setup_model;
You can set join
$model->join(['left outer join company on book.company_id = company.id']);
This join
is used by select()
, select_at()
Class name, Model name, Table name
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"
Create column clause automatically : mycolumn(), column()
To create column clause automatically, use mycolumn()
. Valude of table
and columns
is used.
my $column_clause = $model->mycolumn;
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.
You can create column clause from columns of other table.
my $column_clause = $model->column('company');
If table
is 'company'�Acolumn
is ['id', 'name'], the following clause is created.
company.id as company__id, company.name as company__name
Create column clause automatically : column_clause()
To create column clause automatically, use column_clause()
. Valude of table
and columns
is used.
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.
my $column_clause = $model->column_clause(remove => ['id']);
If you add some column, use add
option.
my $column_clause = $model->column_clause(add => ['company.id as company__id']);
Model Examples
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 { ... }
8. Improve performance
Create query
If you can't get performance, create query by query
option. For example, many insert is needed.
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()
.
my $query = $dbi->create_query(
"insert into book {insert_param title author};";
);
9. Other features
Add method
You can add method to DBIx::Custom object. use 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;
Change result class
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');
EXAMPLES
You can see exsamples in the following wiki.
DBIx::Custom Wiki - Many useful examples