1 contributor
=head1 NAME
DBIx::Custom::Guide - DBIx::Custom Guides
=head1 GUIDE
=head2 1. Connect to the database
use DBIx::Custom;
my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=$database",
user => 'ken', password => '!LFKD%$&');
use C<connect()> to connect to the database.
You can sepecfiy C<data_soruce>, C<user>, and C<password>.
The following ones are data source exmaple in variouse dabase system.
SQLite
"dbi:SQLite:dbname=$database"
"dbi:SQLite:dbname=:memory:"
MySQL
"dbi:mysql:database=$database"
"dbi:mysql:database=$database;host=$hostname;port=$port"
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;"
=head2 2. Suger methods
L<DBIx::Custom> has suger methods, such as C<insert()>, C<update()>,
C<delete()> or C<select()>. If you want to do small works,
You don't have to create SQL statements.
=head3 insert()
Execute insert statement.
$dbi->insert(table => 'book',
param => {title => 'Perl', author => 'Ken'});
The following SQL is executed.
insert into (title, author) values (?, ?);
The values of C<title> and C<author> is embedded into the placeholders.
C<append> and C<filter> argument can be specified.
See also "METHODS" section.
=head3 update()
Execute update statement.
$dbi->update(table => 'book',
param => {title => 'Perl', author => 'Ken'},
where => {id => 5});
The following SQL is executed.
update book set title = ?, author = ?;
The values of C<title> and C<author> is embedded into the placeholders.
C<append> and C<filter> argument can be specified.
See also "METHOD" section.
If you want to update all rows, use C<update_all()> method.
=head3 delete()
Execute delete statement.
$dbi->delete(table => 'book',
where => {author => 'Ken'});
The following SQL is executed.
delete from book where id = ?;
The value of C<id> is embedded into the placehodler.
C<append> and C<filter> argument can be specified.
see also "METHODS" section.
If you want to delete all rows, use C<delete_all()> method.
=head3 select()
Execute select statement, only C<table> argument specified :
my $result = $dbi->select(table => 'book');
The following SQL is executed.
select * from book;
the result of C<select()> method is L<DBIx::Custom::Result> object.
You can fetch a row by C<fetch()> method.
while (my $row = $result->fetch) {
my $title = $row->[0];
my $author = $row->[1];
}
L<DBIx::Custom::Result> has various methods to fetch row.
See "3. Fetch row".
C<column> and C<where> arguments specified.
my $result = $dbi->select(
table => 'book',
column => [qw/author title/],
where => {author => 'Ken'}
);
The following SQL is executed.
select author, title from book where author = ?;
the value of C<author> is embdded into the placeholder.
If you want to join tables, specify C<relation> argument.
my $result = $dbi->select(
table => ['book', 'rental'],
column => ['book.name as book_name']
relation => {'book.id' => 'rental.book_id'}
);
The following SQL is executed.
select book.name as book_name from book, rental
where book.id = rental.book_id;
If you want to add some string to the end of SQL statement,
use C<append> argument.
my $result = $dbi->select(
table => 'book',
where => {author => 'Ken'},
append => 'order by price limit 5',
);
The following SQL is executed.
select * book where author = ? order by price limit 5;
C<filter> argument can be specified.
see also "METHODS" section.
=head2 3. Result manipulation
C<select()> method return L<DBIx::Custom::Result> object.
You can fetch row by various methods.
Note that in this section, array means array reference,
and hash meanse hash reference.
Fetch row into array.
while (my $row = $result->fetch) {
my $author = $row->[0];
my $title = $row->[1];
}
Fetch only a first row into array.
my $row = $result->fetch_first;
Fetch multiple rows into array of array.
while (my $rows = $result->fetch_multi(5)) {
my $first_author = $rows->[0][0];
my $first_title = $rows->[0][1];
my $second_author = $rows->[1][0];
my $second_value = $rows->[1][1];
}
Fetch all rows into array of array.
my $rows = $result->fetch_all;
Fetch row into hash.
# Fetch a row into hash
while (my $row = $result->fetch_hash) {
my $title = $row->{title};
my $author = $row->{author};
}
Fetch only a first row into hash
my $row = $result->fetch_hash_first;
Fetch multiple rows into array of hash
while (my $rows = $result->fetch_hash_multi(5)) {
my $first_title = $rows->[0]{title};
my $first_author = $rows->[0]{author};
my $second_title = $rows->[1]{title};
my $second_author = $rows->[1]{author};
}
Fetch all rows into array of hash
my $rows = $result->fetch_hash_all;
If you want to access statement handle of L<DBI>, use C<sth> attribute.
my $sth = $result->sth;
=head2 4. Parameter binding
L<DBIx::Custom> provides hash parameter binding.
At frist, I show normal parameter binding.
use DBI;
my $dbh = DBI->connect(...);
my $sth = $dbh->prepare(
"select * from book where author = ? and title like ?;"
);
$sth->execute('Ken', '%Perl%');
This is very good way because database system can enable SQL caching,
and parameter is quoted automatically. this is secure.
L<DBIx::Custom> hash parameter binding system improve
normal parameter binding to use hash parameter.
my $result = $dbi->execute(
"select * from book where {= author} and {like title};"
param => {author => 'Ken', title => '%Perl%'}
);
This is same as the normal way, execpt that the parameter is hash.
{= author} and {like title} is called C<tag>.
tag is expand to placeholder string internally.
select * from book where {= author} and {like title}
-> select * from book where author = ? and title like ?;
The following tags is available.
[TAG] [REPLACED]
{? NAME} -> ?
{= NAME} -> NAME = ?
{<> NAME} -> NAME <> ?
{< NAME} -> NAME < ?
{> NAME} -> NAME > ?
{>= NAME} -> NAME >= ?
{<= NAME} -> NAME <= ?
{like NAME} -> NAME like ?
{in NAME COUNT} -> NAME in [?, ?, ..]
{insert_param NAME1 NAME2} -> (NAME1, NAME2) values (?, ?)
{update_param NAME1 NAME2} -> set NAME1 = ?, NAME2 = ?
See also L<DBIx::Custom::QueryBuilder>.
C<{> and C<}> is reserved. If you use these charactors,
you must escape them using '\'. Note that '\' is
already perl escaped charactor, so you must write '\\'.
'select * from book \\{ something statement \\}'
=head2 5. Filtering
If you want to filter the value, you can do this. For example,
L<Time::Piece> object to database date format, or reverse.
$dbi->register_filter(
tp_to_date => sub {
return shift->strftime('%Y-%m-%d');
},
date_to_tp => sub {
return Time::Piece->strptime(shift, '%Y-%m-%d');
}
);
In this example, L<Time::Piece> object is converted to 'yyyy-mm-dd' format
, and reverse.
You can apply this filter to use C<apply_filter()> method.
$dbi->apply_filter('book',
puplication_date => {out => 'tp_to_date', in => 'date_to_tp'},
someting_date => {out => 'tp_to_date', in => 'date_to_tp'}
);
In this case, C<book>'s C<publication_date> is automatically converted.
C<out> means Perl to Database, C<in> means Database to Perl.
These applied filters have effect C<insert>, C<update>, C<update_all>,
C<delete>, C<delete_all>, C<select>
my $tp = Time::Piece::localtime;
$dbi->insert(
table => 'book',
param => {name => 'Perl', publication_date => $tp}
);
my $result = $dbi->select(table => 'book');
my $tp = $result->{publication_date};
Note that this has'nt C<execute> method by default.
If you want to have effect C<execute()> method, use C<table>
option.
my $result = $dbi->execute(
"select * from book where {= id};",
param => {id => 5},
table => ['book']
);
You can also specify registered filters to C<filter> option of
C<insert()>, C<update()>, C<update_all()>, C<delete()>, C<delete_all()>,
C<select()> C<execute()>. This is overwirte applied filter.
$dbi->insert(
table => 'book',
param => {name => 'Perl', publication_date => $tp},
filter => {publication_date => 'tp_to_date'}
);
You can also specify C<DBIx::Custom::Result> object.
This is overwrite applied filter.
my $result = $dbi->select(table => 'book');
$result->filter(publication_date => 'date_to_tp');
B<Filter examples>
=head2 6.Create table object
You can create table object which have methods.
$dbi->table('book');
This class have C<insert()>, C<update()>, C<update_all()>,
C<delete()>, C<delete_all()>, C<select()>.
These is same as L<DBIx::Custom>'s methods except that
you don't have to specify table.
$dbi->table('book')->insert(
param => {author => 'Taro', name => 'Perl'}
);
You can define method for table.
$dbi->table('book',
insert_multi => sub {
my $self = shift;
my $table = $self->name;
my $dbi = $self->dbi;
# Do something
},
cross_summary => sub {
my $self = shift;
my $table = $self->name;
my $dbi = $self->dbi;
# Do something
}
);
Each method receive L<DBIx::Custom::Table> object as first argument.
This class have C<name()> to get table name and C<dbi()>
to get L<DBIx::Custom> object.
Defined method is called from table class.
$dbi->table('book')->insert_multi(param => $param);
=head2 7. Get high performance
=head3 Use execute() method instead suger methods
If you execute insert statement by C<insert()> method,
you sometimes can't get required performance.
C<insert()> method is a little slow because SQL statement and statement handle
is created every time.
In that case, you can prepare a query by C<create_query()> method.
my $query = $dbi->create_query(
"insert into book {insert_param title author};"
);
Return value of C<create_query()> is L<DBIx::Custom::Query> object.
This keep the information of SQL and column names.
{
sql => 'insert into book (title, author) values (?, ?);',
columns => ['title', 'author']
}
Execute query repeatedly.
my $params = [
{title => 'Perl', author => 'Ken'},
{title => 'Good days', author => 'Mike'}
];
foreach my $param (@$params) {
$dbi->execute($query, $param);
}
This is faster than C<insert()> method.
=head2 8. More features
=head3 Get DBI object
You can get L<DBI> object and call any method of L<DBI>.
$dbi->dbh->begin_work;
$dbi->dbh->commit;
$dbi->dbh->rollback;
=head3 Change Result class
You can change Result class if you need.
package Your::Result;
use base 'DBIx::Custom::Result';
sub some_method { ... }
1;
package main;
use Your::Result;
my $dbi = DBIx::Custom->connect(...);
$dbi->result_class('Your::Result');
=head3 Register tag processor
You can custamize query builder object
my $dbi = DBIx::Custom->connect(...);
$dbi->register_tag_processor(
name => sub {
...
}
);
=head3 Resister helper method
You can resiter helper method.
$dbi->helper(
update_or_insert => sub {
my $self = shift;
# do something
},
find_or_create => sub {
my $self = shift;
# do something
}
);
Register helper methods.
These method can be called from L<DBIx::Custom> object directory.
$dbi->update_or_insert;
$dbi->find_or_create;
=head1 EXAMPLES
L<DBIx::Custom Wiki|https://github.com/yuki-kimoto/DBIx-Custom/wiki> - Many useful examples
=cut