1 contributor
=encoding utf8
=head1 NAME
DBIx::Custom::Guide - DBIx::Custom Guide
=head1 GUIDE
B<This guide is now writing.>
L<DBIx::Custom> is the class to make easy to execute SQL.
This is L<DBI> wrapper class like L<DBIx::Class> or L<DBIx::Simple>.
You can do thing more easy than L<DBIx::Class>, more flexible
than L<DBIx::Simple>.
L<DBIx::Custom> is B<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
L<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 L<DBI>.
If you already learn SQL, it is easy to use L<DBIx::Custom>.
I explain L<DBIx::Custom> a little in this section.
In L<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.
=over 4
=item 1. Specify place holder binding value as hash refernce
If you use L<DBI>, you must specify place holder binding value
as array.
$sth->execute(@bind);
If you use L<DBIx::Custom>, you specify it as hash reference.
my $param = {title => 'Perl', author => 'Ken'};
$dbi->execute($sql, $param);
=item 2. Filtering
L<DBIx::Custom> provides filtering system.
For example, You think that about date value you want to
manipulate it as date object like L<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'}
);
C<out> is perl-to-database way. C<in> is perl-from-database way.
This filter is automatically enabled in many method.
$dbi->insert(table => 'book', param => {issue_date => $tp});
=item 3. Selective search condition
It is difficult to create selective where clause in L<DBI>.
For example, If C<title> and C<author> is specified, we create
the following SQL.
select * from book where title = ? and author = ?;
If only C<title> is specified, the following one
select * from book where title = ?;
If only C<author> is specified, the following one,
select * from book where author = ?;
This is hard work. Generally we use modules like L<SQL::Abstract>.
L<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 C<or> condition
=item 4. Methods for insert, update, delete, select
L<DBIx::Custom> provides methods for insert, update, delete, select
There are C<insert()>, C<update()>, C<delete()>,C<select()>.
my $param = {title => 'Perl', author => 'Ken'};
$dbi->insert(table => 'book', param => $param);
=item 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 L<DBIx::Custom> make it easy.
=back
L<DBIx::Custom> is very useful.
See the following if you are interested in it.
=head2 1. Connect to database
Load L<DBIx::Custom>.
use DBIx::Custom;
use C<connect()> to connect to database.
Return value is L<DBIx::Custom> object.
my $dbi = DBIx::Custom->connect(
data_source => "dbi:mysql:database=bookstore",
user => 'ken',
password => '!LFKD%$&',
dbi_options => {mysql_enable_utf8 => 1}
);
C<data_source> must be one corresponding to the database system.
The following ones are data source example.
B<MySQL>
"dbi:mysql:database=$database"
"dbi:mysql:database=$database;host=$hostname;port=$port"
B<SQLite>
"dbi:SQLite:dbname=$database"
"dbi:SQLite:dbname=:memory:"
B<PostgreSQL>
"dbi:Pg:dbname=$dbname"
B<Oracle>
"dbi:Oracle:$dbname"
"dbi:Oracle:host=$host;sid=$sid"
B<ODBC(Microsoft Access)>
"dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=hoge.mdb"
B<ODBC(SQL Server)>
"dbi:ODBC:driver={SQL Server};Server=(local);database=test;Trusted_Connection=yes;AutoTranslate=No;"
If authentication is needed, you can specify C<user> and C<password>
L<DBIx::Custom> is wrapper class of L<DBI>.
You can use all methods of L<DBI> from L<DBIx::Custom> object.
$dbi->do(...);
$dbi->begin_work;
use C<dhb()> to get database handle of L<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.
=head2 2. �}��A�X�V�A�폜�A�I��̂��߂̃��\�b�h
L<DBIx::Custom>�́A
C<insert()>�AC<update()>�AC<delete()>�AC<select()>
�̂悤�ȑ}��A�X�V�A�폜�A�I���s�����߂̃��\�b�h���Ă��܂��B
�ȒP�Ȃ��Ƃ��s���̂ł���ASQL����ŋL�q����K�v������܂���B
=head3 �f�[�^�̑}�� C<insert()>
�f�[�^�x�[�X�Ƀf�[�^��}���ɂ�C<insert()>��g�p���܂��B
$dbi->insert(table => 'book',
param => {title => 'Perl', author => 'Ken'});
C<table>�ɂ̓e�[�u�����AC<param>�ɂ͑}����f�[�^��w�肵�܂��B
����SQL�����s����܂��B
insert into (title, author) values (?, ?);
=head3 �f�[�^�̍X�V C<update()>
�f�[�^�x�[�X�̃f�[�^��X�V����ɂ́AC<update()>��g�p���܂��B
$dbi->update(table => 'book',
param => {title => 'Perl', author => 'Ken'},
where => {id => 5});
C<table>�ɂ̓e�[�u�����AC<param>�ɂ͑}����f�[�^�AC<where>�ɂ�
���w�肵�܂��B
����SQL�����s����܂��B
update book set title = ?, author = ?;
C<update>���\�b�h�͈�S�̂���
where��̂Ȃ�SQL�s���邱�Ƃ���Ă��܂���B
�����ׂĂ̍s��X�V�������ꍇ��
C<update_all()>��g�p�����������B
$dbi->update_all(table => 'book',
param => {title => 'Perl', author => 'Ken'});
=head3 �f�[�^�̍폜 C<delete()>
�f�[�^�x�[�X�̃f�[�^��1���폜����ɂ́AC<delete()>��g�p���܂��B
$dbi->delete(table => 'book',
where => {author => 'Ken'});
C<table>�ɂ̓e�[�u�����AC<where>�ɂ͏��w�肵�܂��B
����SQL�����s����܂��B
delete from book where id = ?;
C<delete>���\�b�h�͈�S�̂���
where��̂Ȃ�SQL�s���邱�Ƃ���Ă��܂���B
�����ׂĂ̍s��폜�������ꍇ��
C<delete_all()>��g�p�����������B
$dbi->delete_all(table => 'book');
=head3 �f�[�^�̑I�� C<select()>
�s��I���ɂ�C<select()>��g�p���܂��B
my $result = $dbi->select(table => 'book');
C<table>������w�肵�āA���̏��w�肵�Ȃ��ꍇ�͎���SQL�����s����܂��B
select * from book;
C<select()>���\�b�h�̖߂�l��L<DBIx::Custom::Result>
�I�u�W�F�N�g�ł��B�s��t�F�b�`����ɂ�C<fetch()>��g�p���܂��B
while (my $row = $result->fetch) {
my $title = $row->[0];
my $author = $row->[1];
}
L<DBIx::Custom::Result>�ɂ��Ă͂��̌�L<3. �s�̃t�F�b�`/"3. �s�̃t�F�b�`">�ŏڂ��������܂��B
���܂��܂�C<select()>�̎g�����Ă����܂��傤�B
����C<select>�͍s�̖��O��where���w�肵����̂ł��B
my $result = $dbi->select(
table => 'book',
column => ['author', 'title'],
where => {author => 'Ken'}
);
C<column>�ɂ͗�AC<where>�ɂ͏��w�肷�邱�Ƃ��ł��܂��B
����SQL�����s����܂��B
select author, title from book where author = ?;
�e�[�u������������ꍇ�͂�C<relation>�Ƀe�[�u����
�W��L�q���܂��B
my $result = $dbi->select(
table => ['book', 'rental'],
where => {book.name => 'Perl'},
relation => {'book.id' => 'rental.book_id'}
);
book�e�[�u����id���rental�e�[�u����book_id���֘A�t�����܂��B
����SQL�����s����܂��B
select * from book, rental where book.name = ? and book.id = rental.book_id;
SQL���̖���ɕ������lj�������ꍇ��<append>��g�p���܂��B
my $result = $dbi->select(
table => 'book',
where => {author => 'Ken'},
append => 'for update',
);
����SQL�����s����܂��B
select * book where author = ? for update;
�܂�C<append>�́AC<select>�����łȂ�C<insert()>�AC<update()>�AC<update_all()>
C<delete()>�AC<delete_all()>�AC<select()>�Ŏg�p���邱�Ƃ�ł��܂��B
=head3 SQL�̎�s C<execute()>
�C�ӂ�SQL���s����ɂ�execute���\�b�h��g�p���܂��B
$dbi->execute("select * from book;");
C<execute()>��L<DBIx::Custom>�̍����̃��\�b�h�ł���^�O��W�J���܂��B
$dbi->execute(
"select * from book {= title} and {= author};"
param => {title => 'Perl', author => 'Ken'}
);
��L�̃^�O��܂�SQL�͎��̂悤�ɓW�J����܂��B
select * from book title = ? and author = ?;
SQL����s�����Ƃ��Ƀv���[�X�z���_(?)�ɑΉ�����ʒu��title��author
�̒l���������I�ɖ��ߍ��܂�܂��B
�^�O�ɂ��Ă�L<5. �^�O/"5. �^�O">�ŏڂ�������܂����A
�ЂƂ̒��ӓ_������܂��B
�^�O��W�J���邽�߂�C<{>��C<}>�͗\���ɂȂ�Ă��܂��B
�����p�������ꍇ�͒��O��\����ăG�X�P�[�v��s���K�v������܂��B
$dbi->execute("... \\{ ... \\} ...");
\���̂�Perl�̃G�X�P�[�v�����ł��̂ŁA��K�v�ɂȂ�Ƃ����_�ɒ��ӂ��Ă��������B
�܂�execute�̃L���[�g�ȋ@�\�Ƃ��āASQL�̍Ō�ɃZ�~�R��������Ȃ��Ă�
���܂��܂���B
$dbi->execute('select * from book');
=head2 3. �s�̃t�F�b�`
C<select()>���\�b�h�̖߂�l��L<DBIx::Custom::Result>�I�u�W�F�N�g�ł��B
L<DBIx::Custom::Result>�ɂ͍s��t�F�b�`���邽�߂̂��܂��܂ȃ��\�b�h��
�p�ӂ���Ă��܂��B
=head3 1�s�Ât�F�b�`(�z��) C<fetch()>
��s�t�F�b�`���Ĕz��̃��t�@�����X�Ɋi�[����ɂ�C<fetch()>��g�p���܂��B
while (my $row = $result->fetch) {
my $title = $row->[0];
my $author = $row->[1];
}
while���[�v��g��āA���ׂĂ̍s��擾���邱�Ƃ��ł��܂��B
=head3 �ŏ��̍s�����t�F�b�`(�z��) C<fetch_first()>
��s�����t�F�b�`���Ĕz��̃��t�@�����X�Ɋi�[����ɂ�C<fetch_first()>
��g�p���܂��B
my $row = $result->fetch_first;
��s�̃t�F�b�`���I������͂���ȏ�t�F�b�`�ł��Ȃ��Ȃ�܂��B
���I�ɂ�1�s�̃t�F�b�`���I�������
�X�e�[�g�����g�n���h����C<finish()>����s����܂��B
=head3 �����s��Ƀt�F�b�`(�z��) C<fetch_multi()>
�����s��t�F�b�`���Ĕz��̃��t�@�����X��v�f�Ɏ���
�z��̃��t�@�����X�Ɋi�[����ɂ�C<fetch_multi()>��g�p���܂��B
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];
}
��ɂ͎��o�������s����w�肵�܂��B
�w�肵���s��i�[�������̂悤�ȃf�[�^��擾�ł��܂��B
[
['Perl', 'Ken'],
['Ruby', 'Mark']
]
=head3 ���ׂĂ̍s��t�F�b�`(�z��) C<fetch_all>
���ׂĂ̍s��t�F�b�`���Ĕz��̃��t�@�����X��v�f�Ɏ���
�z��̃��t�@�����X�Ɋi�[����ɂ�C<fetch_all()>��g�p���܂��B
my $rows = $result->fetch_all;
���ׂĂ̍s��i�[�������̂悤�ȃf�[�^��擾�ł��܂��B
[
['Perl', 'Ken'],
['Ruby', 'Mark']
]
=head3 1�s�Ât�F�b�`(�n�b�V��) C<fetch_hash()>
��s�t�F�b�`���ăn�b�V���̃��t�@�����X�Ɋi�[����ɂ�C<fetch_hash()>��g�p���܂��B
while (my $row = $result->fetch_hash) {
my $title = $row->{title};
my $author = $row->{author};
}
=head3 �ŏ��̍s�����t�F�b�`(�n�b�V��) C<fetch_hash_first()>
��s�����t�F�b�`���ăn�b�V���̃��t�@�����X�Ɋi�[����ɂ�
C<fetch_hash_first()>��g�p���܂��B
my $row = $result->fetch_hash_first;
��s�̃t�F�b�`���I������͂���ȏ�t�F�b�`�ł��Ȃ��Ȃ�܂��B
���I�ɂ�1�s�̃t�F�b�`���I�������
�X�e�[�g�����g�n���h����C<finish()>����s����܂��B
=head3 �����s��Ƀt�F�b�`(�n�b�V��) C<fetch_hash_multi()>
�����s��t�F�b�`���ăn�b�V���̃��t�@�����X��v�f�Ɏ���
�z��̃��t�@�����X�Ɋi�[����ɂ�C<fetch_hash_multi()>
��g�p���܂��B
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};
}
��ɂ͎��o�������s����w�肵�܂��B
�w�肵���s��i�[�������̂悤�ȃf�[�^��擾�ł��܂��B
[
{title => 'Perl', author => 'Ken'},
{title => 'Ruby', author => 'Mark'}
]
=head3 ���ׂĂ̍s��t�F�b�`(�n�b�V��) C<fetch_hash_all()>
���ׂĂ̍s��t�F�b�`���ăn�b�V���̃��t�@�����X��v�f�Ɏ���
�z��̃��t�@�����X�Ɋi�[����ɂ�C<fetch_hash_all()>
��g�p���܂��B
my $rows = $result->fetch_hash_all;
���ׂĂ̍s��i�[�������̂悤�ȃf�[�^��擾�ł��܂��B
[
{title => 'Perl', author => 'Ken'},
{title => 'Ruby', author => 'Mark'}
]
=head3 �X�e�[�g�����g�n���h�� C<sth()>
�X�e�[�g�����g�n���h���ɒ��ڃA�N�Z�X�������ꍇ��
<sth>�Ŏ擾���邱�Ƃ��ł��܂��B
my $sth = $result->sth;
�t�F�b�`�̃p�t�H�[�}���X���p�������Ȃ��Ƃ��ɂ́A
�X�e�[�g�����g�n���h������
���p�ł��鑬�x�̑������\�b�h�𗘗p���邱�Ƃ��ł��܂��B
=head2 4. �t�B���^�����O
�f�[�^�x�[�X�Ƀf�[�^��o�^����Ƃ���f�[�^�x�[�X����f�[�^��擾����
�Ƃ��Ɏ����I�ɒl�̕ϊ���s�������ꍇ�������Ǝv���܂��B
���Ƃ��A��t��\�������̏ꍇ�́A
�f�[�^�x�[�X�ɓo�^����ꍇ��L<Time::Piece>�I�u�W�F�N�g����
�f�[�^�x�[�X�̓�t�̃t�H�[�}�b�g�ɁA
�f�[�^�x�[�X����f�[�^��擾����Ƃ��́A���̋t��s����ƕ֗��ł��B
=head3 �t�B���^�̓o�^ C<register_filter()>
�t�B���^��o�^����ɂ�C<register_filter()>��g�p���܂��B
$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');
},
);
�o�^�����t�B���^��C<apply_filter()>�Ȃǂŗ��p���邱�Ƃ��ł��܂��B
=head3 �t�B���^�̓K�p C<apply_filter()>
�쐬�����t�B���^��K�p����ɂ́AC<apply_filter()>��g�p���܂��B
$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'}
);
����̓e�[�u�����ł��B����ȍ~�́A�ƃt�B���^���[���̃y�A��L�q���܂��B
�t�B���^���[����out�ɂ́A�f�[�^�x�[�X�Ƀf�[�^�𑗐M����Ƃ��ɓK�p����t�B���^��A
�t�B���^���[����in�ɂ́A�f�[�^�x�[�X����f�[�^��擾����Ƃ��ɓK�p����t�B���^��
�L�q���܂��Bout���f�[�^�x�[�X�ɑ��M������Ain���f�[�^�x�[�X������o�����ł��B
�t�B���^�ɂ́AC<register_filter>�œo�^�����t�B���^���̑��ɁA�R�[�h���t�@�����X��
�w�肷�邱�Ƃ�ł��܂��B
issue_date => {out => sub { ... }, in => sub { ... }}
�K�p���ꂽ�t�B���^��C<insert()>�AC<update()>�AC<update_all()>�AC<delete()>�A
C<delete_all()>�AC<select()>�ŗL��ɂȂ�܂��B
my $tp = Time::Piece->strptime('2010/10/14', '%Y/%m/%d');
my $result = $dbi->select(table => 'book', where => {issu_date => $tp});
�f�[�^�x�[�X�Ƀf�[�^�����M�����Ƃ��ɁAL<Time::Piece>�I�u�W�F�N�g��
�f�[�^�x�[�X�̓�t�̃t�H�[�}�b�g�u2010-10-14�v�ɕϊ�����܂��B
�܂��t�Ƀf�[�^��t�F�b�`����Ƃ��ɂ́A�f�[�^�x�[�X�̓�t�̃t�H�[�}�b�g��
�^�C���s�[�X�I�u�W�F�N�g�ɕϊ�����܂��B
my $row = $resutl->fetch_hash_first;
my $tp = $row->{issue_date};
���̂悤�Ȏ����I�Ɏ�s�����t�B���^��o�^�ł��邱�Ƃ�L<DBIx::Custom>��
����̂ЂƂł��B
C<apply_filter()>�œK�p���ꂽ�t�B���^�̓e�[�u�������܂ޗɑ��Ă�L��ł��B
$dbi->select(
table => 'book',
where => {'book.title' => 'Perl', 'book.author' => 'Ken'}
);
�e�[�u�����ʂ���K�v������Ƃ��ɕ֗��ȋ@�\�ł��B
=head3 �ʂ̃t�B���^�̓K�p C<filter>
C<apply_filter()>��g��čŏ��ɂ��ׂẴe�[�u���̗�ɂ���
�t�B���^���`���邱�Ƃ�ł��܂����A
�ʂɃt�B���^��K�p���邱�Ƃ�ł��܂��B
�ʂ̃t�B���^��C<apply_filter()>�œK�p�����t�B���^��㏑���܂��B
�ʂ̃t�B���^��SQL��as��g��āA��̕ʖ���쐬����K�v������ꍇ�Ɋ��܂��B
�f�[�^�x�[�X�ɑ��M����ꍇ�ɁA�ʂ̃t�B���^��K�p����ɂ́A�e���\�b�h��
C<filter>�I�v�V������g�p���܂��B�ʂ̃t�B���^�́AC<insert()>�AC<update()>�A
C<update_all()>�AC<delete()>�AC<delete_all()>�AC<select()>�AC<execute()>
�Ŏg�p���邱�Ƃ��ł��܂��B
C<insert()>�̗����܂��B
$dbi->insert(
table => 'book',
param => {issue_date => $tp, first_issue_date => $tp},
filter => {issue_date => 'tp_to_date', first_issue_date => 'tp_to_date'}
);
C<execute()>�̗����܂��B
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'}
);
�����C<filter>��g���ǂ������ł��Bissue_date�̕ϊ��ɂ��Ă�C<apply_filter()>
�œo�^���Ă���̂ł����A�V�����쐬������ł���issue_year�ɂ��ẮA
���̕ϊ���o�^����Ă��܂���B�ł��̂ŁA�ʂɃt�B���^��ݒ肵�Ă��܂��B
�܂����ɍs��t�F�b�`����Ƃ��ɂ�ʂ̃t�B���^��K�p���邱�Ƃ��ł��܂��B
�t�B���^��K�p����ɂ́A
C<DBIx::Custom::Result>�N���X��C<filter>���\�b�h��g�p���܂��B
$result->filter(issue_year => 'year_to_tp');
�p�ɂɗ��p����̂ł���A�ʂɓo�^�������C<apply_filter()>�œo�^
���Ă������ق����֗��ł��傤�BC<apply_filter()>�͑��݂��Ȃ���ɑ��Ă�
�t�B���^��K�p�ł��邩��ł��B
$dbi->apply_filter('book',
'issue_year' => {out => 'tp_to_year', in => 'year_to_tp'}
);
=head3 �ŏI�o�͂̂��߂̃t�B���^�����O C<end_filter()>
C<DBIx::Custom::Result>�ł͂���ɍŌ�ɂ���x�A�t�B���^��lj��
�o�^���邱�Ƃ��ł��܂��B���Ƃ���HTML�ɏo�͂������ꍇ�ɁATime::Piece
�I�u�W�F�N�g����ǂ݂₷���L�q�ɕϊ����邱�Ƃ��ł��܂��B
�Ō�̃t�B���^��o�^����ɂ́AC<end_filter()>��g�p���܂��B
$result->end_filter(issue_date => sub {
my $tp = shift;
return '' unless $tp;
return $tp->strftime('%Y/%m/%d %h:%m:%s (%a)');
});
��t��₷���`�Ƀt�H�[�}�b�g���邱�Ƃ��ł��܂��B
�t�B���^�̓t�F�b�`��s���O�ɓo�^���Ă����K�v�����邱�Ƃ�
���ӂ��Ă��������B
$result->filter(...);
$result->end_filter(...);
my $row = $result->fetch_hash_first;
=head3 ��̏���Ƀt�B���^��K�p���� C<each_column()>
��t�^�̗�͎蓮�Őݒ肵�Ȃ��Ă�A�����I�ɐݒ�ł���ƕ֗��ł��B
���̂��߂Ƀf�[�^�x�[�X�̃e�[�u���̗�̂��ׂĂ̏���
���Ԃɏ������邽�߂�C<each_column()>������܂��B
$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�̓R�[���o�b�N����܂��B�R�[���o�b�N�̈��
���Ԃ�L<DBIx::Custom>�I�u�W�F�N�g�A�e�[�u�����A�A��̏��ł��B
��̌^���̏����ƂɎ����I�ɁA�t�B���^��K�p���Ă��܂��B
�ЂƂ̒��ӓ_�Ƃ��ăR�[���o�b�N�̒�����A�R�[���o�b�N�̊O��
�̕ϐ���Q�Ƃ��Ȃ��悤�ɒ��ӂ��Ă��������Beach_column��
���X1����s����邾���Ȃ̂ŁA�قƂ�ǂ̏ꍇ��肠��܂��A
�z�Q�Ƃɂ�郁�������[�N���������Ă��܂��\�����Ă��邩��ł��B
=head2 5. �^�O
=head3 �^�O�̋@�\
L<DBIx::Custom>��SQL�̒��Ƀ^�O�ߍ��ދ@�\���Ă��܂��B
select * from book where {= title} and {like author};
{= title}��{like author}�̕������^�O�ł��B�^�O�͎��̂悤�Ȍ`��
����܂��B
{�^�O�� ��1 ��2 ...}
�^�O��C<{>�Ŏn�܂�AC<}>�ŏI���܂��B�ŏ���C<{>�ƃ^�O���̊�
�ɂ͋�}��Ȃ��悤���ӂ��Ă��������B
�^�O�̋@�\�̂��߂�C<{>��C<}>�͗\���ɂȂ�Ă��܂��B
�����p�������ꍇ�͒��O��\����ăG�X�P�[�v��s���K�v������܂��B
select from book \\{ ... \\}
C<\>���̂�Perl�̃G�X�P�[�v�����ł��̂ŁA
�G�X�P�[�v����ꍇ��C<\>����K�v�ɂȂ�Ƃ����_�ɒ��ӂ��Ă��������B
��L�̃^�O��SQL����s�����O�Ɏ���SQL�ɓW�J����܂��B
select * from book where title = ? and author like ?;
�^�O��܂�SQL���s����ɂ�C<execute()>��g�p���܂��B
my $sql = "select * from book where {= author} and {like title};"
$dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'});
C<param>�I�v�V������g��āA�v���[�X�z���_�ɖ��ߍ��݂����l��
�n�b�V�����t�@�����X�Ŏw�肷�邱�Ƃ��ł��܂��B
���̃��\�b�h�Ɠ��l��C<execute()>�ɂ����Ă�C<filter>��w�肷�邱�Ƃ��ł��܂��B
$dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'}
filter => {title => 'to_something');
C<execute>�̂ЂƂ̒��ӓ_�Ƃ��Ă�C<apply_filter()>�œK�p���ꂽ�t�B���^
�̓f�t�H���g�ł͗L��ł͂Ȃ��Ƃ������Ƃɒ��ӂ��Ă��������B
C<apply_filter()>�œK�p���ꂽ�t�B���^��L��ɂ���ɂ́A
C<table>��w�肷��K�v������܂��B
$dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'}
table => ['book']);
=head2 �^�O�ꗗ
L<DBIx::Custom>�ł͎��̃^�O���g�p�\�ł��B
���̂悤�Ƀ^�O��g���SQL����\������̂�L<DBIx::Custom>��
����ł��B�ȉ��̃^�O�����p�\�ł��B
=head3 C<?>
C<?>�^�O�͈ȉ��̂悤�ɓW�J����܂��B
{? NAME} -> ?
=head3 C<=>
C<=>�^�O�͈ȉ��̂悤�ɓW�J����܂��B
{= NAME} -> NAME = ?
=head3 C<E<lt>E<gt>>
C<E<lt>E<gt>>�^�O�͈ȉ��̂悤�ɓW�J����܂��B
{<> NAME} -> NAME <> ?
=head3 C<E<lt>>
C<E<lt>>�^�O�͈ȉ��̂悤�ɓW�J����܂��B
{< NAME} -> NAME < ?
=head3 C<E<gt>>
C<E<gt>>�^�O�͈ȉ��̂悤�ɓW�J����܂��B
{> NAME} -> NAME > ?
=head3 C<E<gt>=>
C<E<gt>=>�^�O�͈ȉ��̂悤�ɓW�J����܂��B
{>= NAME} -> NAME >= ?
=head3 C<E<lt>=>
C<E<lt>=>�^�O�͈ȉ��̂悤�ɓW�J����܂��B
{<= NAME} -> NAME <= ?
=head3 C<like>
C<like>�^�O�͈ȉ��̂悤�ɓW�J����܂��B
{like NAME} -> NAME like ?
=head3 C<in>
C<in>�^�O�͈ȉ��̂悤�ɓW�J����܂��B�v���[�X�z���_��
�����Ŏw�肷��K�v�����邱�Ƃɒ��ӂ��Ă��������B
{in NAME COUNT} -> NAME in [?, ?, ..]
=head3 C<insert_param>
C<insert_param>�^�O�͈ȉ��̂悤�ɓW�J����܂��B
{insert_param NAME1 NAME2} -> (NAME1, NAME2) values (?, ?)
=head3 C<update_param>
C<update_param>�^�O�͈ȉ��̂悤�ɓW�J����܂��B
{update_param NAME1 NAME2} -> set NAME1 = ?, NAME2 = ?
=head2 �����̗�̈���
�����̗��܂ރ^�O������ꍇ�ɂ�ASQL���s���邱�Ƃ��ł��܂��B
���Ƃ��A��̓�t�Ŕ�r���Ȃ���Ȃ�Ȃ��ꍇ��
�l���Č��܂��傤�B
my $sql = "select * from table where {> date} and {< date};";
���̂悤�ȏꍇ�͑Ή�����p�����[�^�̒l��z��̃��t�@�����X�ɂ��܂��B
my $dbi->execute($sql, param => {date => ['2010-10-01', '2012-02-10']});
=head2 �^�O�̒lj� C<register_tag()>
L<DBIx::Custom>�ł̓^�O��Ǝ��ɒlj���邱�Ƃ��ł��܂��B
�^�O��lj����ɂ�C<register_tag()>��g�p���܂��B
$dbi->register_tag(
'=' => sub {
my $column = shift;
return ["$column = ?", [$column]];
}
);
�����ł̓f�t�H���g��C<=>�^�O���ǂ̂悤�Ɏ������Ă��邩����Ă��܂��B
�^�O��o�^������̈�̓^�O�̒��ɏ����ꂽ��ɂȂ�܂��B
{�^�O�� ��1 ��2}
C<=>�^�O�̏ꍇ��
{= title}
�Ƃ����`���ł�����A�T�u���[�`���ɂ�title�Ƃ����ЂƂ̗��킽��Ă��܂��B
�T�u���[�`���̖߂�l�ɂ͎��̌`���̔z��̃��t�@�����X��Ԃ��K�v������܂��B
[
�W�J��̕�����,
[�v���[�X�z���_�ɖ��ߍ��݂ɗ��p�����1, ��2, ...]
]
��ڂ̗v�f�͓W�J��̕�����ł��B���̗�ł�
'title = ?'
��Ԃ��K�v������܂��B
��ڂ̗v�f�̓v���[�X�z���_�ɖ��ߍ��݂ɗ��p�����܂ޔz���
���t�@�����X�ł��B����̗�ł�
['title']
��Ԃ��K�v������܂��B�����̃v���[�X�z���_��܂ޏꍇ�́A���̕�����
�����ɂȂ�܂��BC<insert_param>�^�O��C<update_param>�^�O��
���̕�������ە����ɂȂ�Ă��܂��B
��L��킹���
['title = ?', ['title']]
��Ԃ��K�v������Ƃ������Ƃł��B
�^�O�̎���̑��̃T���v����L<DBIx::Custom::Tag>�̃\�[�X�R�[�h
����ɂȂ�Ă݂Ă��������B
=head2 6. Where��̓��I�Ȑ���
=head3 Where��̓��I�Ȑ��� where()
�����̌�����w�肵�āA�����s�������ꍇ������܂��B
����3�̃P�[�X��where���l���Ă݂܂��傤�B
���L�̂悤��where�傪�K�v�ɂȂ�܂��B
title�̒l�����Ō�������ꍇ
where {= title}
author�̒l�����Ō�������ꍇ
where {= author}
title��author�̗���̒l�Ō�������ꍇ
where {= title} and {=author}
L<DBIx::Custom>�ł͓��I��Where��̐�����T�|�[�g���Ă��܂��B
�܂�C<where()>��L<DBIx::Custom::Where>�I�u�W�F�N�g�����܂��B
my $where = $dbi->where;
����C<clause()>��g�p����where���L�q���܂��B
$where->clause(
['and', '{= title'}, '{= author}']
);
clause�̎w���@�͎��̂悤�ɂȂ�܂��B
['or' ���邢�� 'and', �^�O1, �^�O2, �^�O3]
����ɂ�or���邢��and��w�肵�܂��B����ȍ~�ɂ�
������^�O��g��ċL�q���܂��B
C<clause>�̎w��͓��q�ɂ��邱�Ƃ�ł��A����ɕ��G�ȏ�
��L�q���邱�Ƃ�ł��܂��B
['and',
'{= title}',
['or', '{= author}', '{like date}']
]
���̂悤��C<clause>��ݒ肵�����C<param>�Ƀp�����[�^��w�肵�܂��B
my $param => {title => 'Perl'};
$where->param($param);
���̗�ł�title�������p�����[�^�Ɋ܂܂�Ă��܂��B
���̌�C<to_string()>���s�����$param�Ɋ܂܂��p�����[�^����
where������邱�Ƃ��ł��܂��B
my $where_clause = $where->to_string;
�p�����[�^��title�����ł��̂ŁA���̂悤��where�傪��������܂��B
where {= title}
�܂�L<DBIx::Custom>�͕�����̕]����I�[�o�[���[�h���āAC<to_string()>
��Ăяo���悤�ɂ��Ă��܂��̂ŁA���̂悤�ɂ���where������邱�Ƃ�
�ł��܂��B
my $where_clause = "$where";
�����SQL�̒���where��ߍ��ނƂ��ɂƂĂ�𗧂@�\�ł��B
=head3 ����̗�܂ޏꍇ
�^�O�̒��ɓ���̖��O���̂����݂����ꍇ�ł��I��
where���쐬���邱�Ƃ��ł��܂��B
���Ƃ��A�p�����[�^�Ƃ��ĊJ�n��t�ƏI����t��������Ƃ�
�l���Ă݂Ă��������B
my $param = {start_date => '2010-11-15', end_date => '2011-11-21'};
�܂��J�n��t�ƏI����t�̕Е���A�ǂ������Ȃ��ꍇ���邩����܂���B
���̏ꍇ�͎��̂悤�ȃp�����[�^�ɕϊ����邱�ƂőΉ����邱�Ƃ��ł��܂��B
my $p = {date => ['2010-11-15', '2011-11-21']};
�l���z��̃��t�@�����X�ɂȂ�Ă��邱�Ƃɒ��ڂ��Ă��������B���̂悤�ɂ����
�����̗��܂ރ^�O�ɏ��Ԃɖ��ߍ��ނ��Ƃ��ł��܂��B
$where->clause(
['and', '{> date}', '{< date}']
);
$where->param($p);
�܂��J�n��t�����݂��Ȃ��ꍇ�͎��̂悤�ȃf�[�^��쐬���܂��B
my $p = {date => [$dbi->not_exists, '2011-11-21']};
L<DBIx::Custom>��C<not_exists>��DBIx::Custom::NotExists�I�u�W�F�N�g��
�擾�ł��܂��B����͑Ή�����l�����݂��Ȃ����Ƃ�����߂̂�̂ł��B
�܂��I����t�����݂��Ȃ��ꍇ�͎��̂悤�ȃf�[�^��쐬���܂��B
my $p = {date => ['2010-11-15']};
�ǂ�����݂��Ȃ��ꍇ�͎��̂悤�ȃf�[�^��쐬���܂��B
my $p = {date => []};
��������̂ň�ԊȒP�ɍ쐬�ł��郍�W�b�N����Ă����܂��B
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};
=head3 C<select()>�Ƃ̘A�g
L<DBIx::Custom::Where>�I�u�W�F�N�g��
C<select()>��C<where>�ɒ��ړn�����Ƃ�
�ł��܂��B
my $where = $dbi->where;
$where->clause(...);
$where->param($param);
my $result = $dbi->select(table => 'book', where => $where);
���邢��C<update()>�AC<delete()>��where�Ɏw�肷�邱�Ƃ�\�ł��B
=head3 C<execute()>�Ƃ̘A�g
C<execute()>�Ƃ̘A�g�ł��BSQL��쐬����Ƃ��ɖ��ߍ��ނ��Ƃ��ł��܂��B
my $where = $dbi->where;
$where->clause(...);
$where->param($param);
my $sql = <<"EOS"
select * from book;
$where
EOS
$dbi->execute($sql, param => $param);
=head2 7. �e�[�u�����f��
=head3 �e�[�u���I�u�W�F�N�g�̍쐬 C<table()>
L<DBIx::Custom>�̓��W�b�N�Ƃ��ăe�[�u���𒆐S�ɂ�����
���f���̍쐬��x�����܂��B
�A�v���P�[�V�����̃��W�b�N��L�q����Ƃ��ɁA���̃��W�b�N��
�f�[�^�x�[�X�̃e�[�u���ɂ����邱�Ƃ́ARDBMS�𗘗p����
���f���ł���A�R�[�h�̏d�����Ȃ�
�킩��₷����̂ɂȂ�܂��B
�e�[�u���I�u�W�F�N�g������ɂ�C<table()>��g�p���܂��B
my $table = $dbi->table('book');
��ۂɃf�[�^�x�[�X�Ƀe�[�u���͑��݂��Ă���K�v�͂���܂���B
����͉��z�I�ȃe�[�u���I�u�W�F�N�g�ł��B�����
L<DBIx::Customm::Table>�I�u�W�F�N�g�ɂȂ�܂��B
�e�[�u���I�u�W�F�N�g�����C<insert()>�AC<update()>�AC<update_all()>�A
C<delete()>�AC<delete_all()>�AC<select()>�Ȃǂ̃��\�b�h�Ăяo�����Ƃ��ł��܂��B
L<DBIx::Custom>�ƈقȂ�Ƃ���́AC<table>��K������w�肷��K�v��
�Ȃ��Ƃ������Ƃł��B
$table->insert(param => $param);
C<table���̒l�͎����I��book�ɐݒ肳��܂��B
�܂��e�[�u���I�u�W�F�N�g�ɂ͓Ǝ��̃��\�b�h��lj���邱�Ƃ��ł��܂��B
$table->method(
register => sub {
my $self = shift;
my $table_name = $self->name;
# something
},
list => sub {
my $self = shift;
my $table_name = $self->name;
# something
}
);
���\�b�h�ɓn���������L<DBIx::Custom::Table>�I�u�W�F�N�g�ł��B
C<name()>��g�p���āA�e�[�u������擾���邱�Ƃ��ł��܂��B
���̂悤�ɂ��ēo�^�������\�b�h�͒��ڌĂяo�����Ƃ��ł��܂��B
$table->register(...);
$table->list(...);
�܂��e�[�u����p�̃��\�b�h��I�[�o�[���C�h���č쐬���邱�Ƃ�ł��܂��B
$table->method(
insert => sub {
my $self = shift;
$self->base_insert(...);
# something
}
);
��Ƃ�Ƒ��݂��Ă���C<insert()>��ĂԂɂ�C<base_$method>�Ƃ��܂��BL<DBIx::Custom::Table>
�̃I�[�o�[���C�h�̋@�\�͊ȈՓI�Ȃ�̂ł����A�ƂĂ�֗��ł��B
=head2 �e�[�u���ŋ��L�̃��\�b�h�̓o�^
���ׂẴe�[�u���Ń��\�b�h��L����ɂ�C<table>���\�b�h�Ńe�[�u����쐬����O�ɁA
C<base_table>�Ƀ��\�b�h��o�^���Ă����܂��B
$dbi->base_table->method(
count => sub {
my $self = shift;
return $self->select(column => ['count(*)']);
}
);
�܂��e�[�u�������L<DBIx::Custom>��L<DBI>�̂��ׂẴ��\�b�h��Ăяo�����Ƃ��ł��܂��B
# DBIx::Custom method
$table->execute($sql);
# DBI method
$table->begin_work;
$table->commit;
=head2 ��ʓI�ȃ��f���̍\��
��ʓI�ɂ́AL<DBIx::Custom>��p�����ăR���X�g���N�^�̒��ɁA���f����쐬
����̂��悢�ł��傤�B
package MyDBI;
use base 'DBIx::Custom';
sub connect {
my $self = shift->SUPER::connect(@_);
$self->base_table->method(
... => sub { ... }
);
$self->table('book')->method(
insert_multi => sub { ... },
... => sub { ... }
);
$self->table('company')->method(
... => sub { ... },
);
}
���̂悤�ɂ��Ē�`���Ă����A���̂悤�ɗ��p���邱�Ƃ��ł��܂��B
my $dbi = MyDBI->connect(...);
$dbi->table('book')->insert_multi(...);
=head2 8. �p�t�H�[�}���X�̉�P
=head3 �N�G���̍쐬
��C<insert()>���\�b�h��g�p���ăC���T�[�g���s�����ꍇ�A
�K�v�ȃp�t�H�[�}���X���Ȃ��ꍇ�����邩����܂���B
C<insert()>���\�b�h�́ASQL���ƃX�e�[�g�����g�n���h����
����쐬���邽�߂ł��B
���̂悤�ȏꍇ�́AC<query>�I�v�V������w�肷�邱�ƂŁA
�N�G����擾���邱�Ƃ��ł��܂��B
my $query = $dbi->insert(table => 'book', param => $param, query => 1);
�܂�C<create_query()>���\�b�h��g��ĔC�ӂ�SQL�̃N�G����쐬
���邱�Ƃ�ł��܂��B
my $query = $dbi->create_query(
"insert into book {insert_param title author};";
);
�߂�l��L<DBIx::Custom::Query>�I�u�W�F�N�g�ł��B
���̃I�u�W�F�N�g��SQL���ƃp�����[�^�o�C���h���̗�
�ێ����Ă��܂��B�܂��X�e�[�g�����g�n���h����ێ����Ă��܂��B
{
sql => 'insert into book (title, author) values (?, ?);',
columns => ['title', 'author'],
sth => $sth
}
�N�G���I�u�W�F�N�g��g��ČJ��Ԃ���s����ɂ�C<execute()>��g�p���܂��B
my $params = [
{title => 'Perl', author => 'Ken'},
{title => 'Good days', author => 'Mike'}
];
foreach my $param (@$paramss) {
$dbi->execute($query, table => 'book', param => $input);
}
C<execute>���\�b�h�̑���ɃN�G���I�u�W�F�g��n�����Ƃ��ł��܂��B
C<insert()>���\�b�h�������ł��B
���ӓ_������������܂��B����̓p�����[�^�̐��͕K�������łȂ��Ă͂Ȃ�Ȃ�
�Ƃ������Ƃł��B�ŏ���3�̃p�����[�^������n�����̂ɁA���̎�s�ł�
��̃p�����[�^��n���Ɨ\��Ȃ����ʂɂȂ�܂��B�����
���I�ɐ������ꂽSQL�Ɋ܂܂��v���[�X�z���_�̐����قȂ邩��ł��B
�܂�C<execute()>�ɂ��Ă͎����I�ɂ̓t�B���^���L��ɂȂ�Ȃ��̂ŁA
C<table>��w�肷��K�v�̂��邱�Ƃɒ��ӂ��Ă��������B
�{���ɕK�v�ȏꍇ�������p���Ă��������B
=head2 9. ���̑��̋@�\
=head3 ���\�b�h�̓o�^
���\�b�h��o�^����ɂ�C<method()>��g�p���܂��B
$dbi->method(
update_or_insert => sub {
my $self = shift;
# something
},
find_or_create => sub {
my $self = shift;
# something
}
);
<method()>�œo�^�������\�b�h��
L<DBIx::Custom>�I�u�W�F�N�g���璼�ڌĂяo�����Ƃ��ł��܂��B
$dbi->update_or_insert;
$dbi->find_or_create;
=head3 ���ʃN���X�̕ύX
�K�v�Ȃ�Ό��ʃN���X��ύX���邱�Ƃ��ł��܂��B
package MyResult;
use base 'DBIx::Custom::Result';
sub some_method { ... }
1;
package main;
use MyResult;
my $dbi = DBIx::Custom->connect(...);
$dbi->result_class('MyResult');
=head3 �L���b�V���O
�^�O�̓W�J���SQL�̓p�t�H�[�}���X�̗��R�̂��߂ɃL���b�V������܂��B
�����C<chace>�Őݒ�ł��A�f�t�H���g�ł̓L���b�V����s���ݒ�ł��B
$dbi->cache(1);
�L���b�V����@��C<cache_method>�Ƀ��\�b�h��w�肷�邱�Ƃ�
�ύX���邱�Ƃ��ł��܂��B
�f�[�^�̕ۑ��Ǝ擾�̂��߂̃��\�b�h���`���܂��B
�f�t�H���g�ł͎��̂悤�Ƀ�������ɃL���b�V����s����̂ɂȂ�Ă��܂��B
$dbi->cache_method(sub {
sub {
my $self = shift;
$self->{_cached} ||= {};
if (@_ > 1) {
# Set
$self->{_cached}{$_[0]} = $_[1]
}
else {
# Get
return $self->{_cached}{$_[0]}
}
}
});
����L<DBIx::Custom>�I�u�W�F�N�g�ł��B
����̓^�O�̓W�J�����O��SQL�ł��B
��O��̓^�O�̓W�J���SQL�ł��B
�����ō쐬����ꍇ�͑�O�����݂����ꍇ�̓L���b�V����ݒ肵�A
���݂��Ȃ�����ꍇ�̓L���b�V����擾��������
�����������B
=cut
=head1 EXAMPLES
L<DBIx::Custom Wiki|https://github.com/yuki-kimoto/DBIx-Custom/wiki> - Many useful examples
=cut