DBIx::Custom::Guides - DBIx::Custom Guides
use DBIx::Custom; my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=$database", user => 'ken', password => '!LFKD%$&');
use connect() to connect to the database. You can sepecfiy data_soruce, user, and password.
connect()
data_soruce
user
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;"
DBIx::Custom has suger methods, such as insert(), update(), delete() or select(). If you want to do small works, You don't have to create SQL statements.
insert()
update()
delete()
select()
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 title and author is embedded into the placeholders.
title
author
append and filter argument can be specified. See also "METHODS" section.
append
filter
Execute update statement.
$dbi->update(table => 'book', param => {title => 'Perl', author => 'Ken'}, where => {id => 5});
update book set title = ?, author = ?;
append and filter argument can be specified. See also "METHOD" section.
If you want to update all rows, use update_all() method.
update_all()
Execute delete statement.
$dbi->delete(table => 'book', where => {author => 'Ken'});
delete from book where id = ?;
The value of id is embedded into the placehodler.
id
append and filter argument can be specified. see also "METHODS" section.
If you want to delete all rows, use delete_all() method.
delete_all()
Execute select statement, only table argument specified :
table
my $result = $dbi->select(table => 'book');
select * from book;
the result of select() method is DBIx::Custom::Result object. You can fetch a row by fetch() method.
fetch()
while (my $row = $result->fetch) { my $title = $row->[0]; my $author = $row->[1]; }
DBIx::Custom::Result has various methods to fetch row. See "3. Fetch row".
column and where arguments specified.
column
where
my $result = $dbi->select( table => 'book', column => [qw/author title/], where => {author => 'Ken'} );
select author, title from book where author = ?;
the value of author is embdded into the placeholder.
If you want to join tables, specify relation argument.
relation
my $result = $dbi->select( table => ['book', 'rental'], column => ['book.name as book_name'] relation => {'book.id' => 'rental.book_id'} );
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 append argument.
my $result = $dbi->select( table => 'book', where => {author => 'Ken'}, append => 'order by price limit 5', );
select * book where author = ? order by price limit 5;
filter argument can be specified. see also "METHODS" section.
select() method return 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 DBI, use sth attribute.
sth
my $sth = $result->sth;
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.
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 tag. tag is expand to placeholder string internally.
tag
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 DBIx::Custom::QueryBuilder.
{ and } 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 \\}'
Usually, Perl string is kept as internal string. If you want to save the string to database, You must encode the string. Filtering system help you to convert a data to another data when you save to the data and get the data form database.
If you want to register filter, use register_filter() method.
register_filter()
$dbi->register_filter( to_upper_case => sub { my $value = shift; return uc $value; } );
You can specify these filters to filter argument of execute() method.
execute()
my $result = $dbi->execute( "select * from book where {= author} and {like title};" param => {author => 'Ken', title => '%Perl%'}, filter => {author => 'to_upper_case, title => 'encode_utf8'} );
filter argument can be specified to suger methods, such as insert(), update(), update_all(), delete(), delete_all(), select().
# insert(), having filter argument $dbi->insert(table => 'book', param => {title => 'Perl', author => 'Ken'}, filter => {title => 'encode_utf8'}); # select(), having filter argument my $result = $dbi->select( table => 'book', column => [qw/author title/], where => {author => 'Ken'}, append => 'order by id limit 1', filter => {title => 'encode_utf8'} );
You can also specify filter when the row is fetched. This is reverse of bind filter.
my $result = $dbi->select(table => 'book'); $result->filter({title => 'decode_utf8', author => 'to_upper_case'});
Filter examples
# Time::Piece object to DATETIME format tp_to_datetime => sub { return shift->strftime('%Y-%m-%d %H:%M:%S'); } # Time::Piece object to DATE format tp_to_date => sub { return shift->strftime('%Y-%m-%d'); }, # DATETIME to Time::Piece object datetime_to_tp => sub { return Time::Piece->strptime(shift, '%Y-%m-%d %H:%M:%S'); } # DATE to Time::Piece object date_to_tp => sub { return Time::Piece->strptime(shift, '%Y-%m-%d'); }
# Time::Piece object to DATETIME format tp_to_datetime => sub { return shift->strftime('%Y-%m-%d %H:%M:%S'); } # Time::Piece object to DATE format tp_to_date => sub { return shift->strftime('%Y-%m-%d'); }, # DATETIME to Time::Piece object datetime_to_tp => sub { return Time::Piece->strptime(shift, $FORMATS->{db_datetime}); } # DATE to Time::Piece object date_to_tp => sub { return Time::Piece->strptime(shift, $FORMATS->{db_date}); }
If you execute insert statement by insert() method, you sometimes can't get required performance.
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 create_query() method.
create_query()
my $query = $dbi->create_query( "insert into book {insert_param title author};" );
Return value of create_query() is 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 insert() method.
You can get DBI object and call any method of DBI.
$dbi->dbh->begin_work; $dbi->dbh->commit; $dbi->dbh->rollback;
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');
You can custamize query builder object
my $dbi = DBIx::Custom->connect(...); $dbi->query_builder->register_tag_processor( name => sub { ... } );
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 DBIx::Custom object directory.
$dbi->update_or_insert; $dbi->find_or_create;
my $rows = $dbi->select( table => 'table1', where => {key1 => 1}, append => "order by key2 {limit 1 0}" # {limit COUNT OFFSET} )->fetch_hash_all;
$dbi->query_builder->register_tag_processor( limit => sub { my ($count, $offset) = @_; my $s = ''; $s .= "limit $count"; $s .= " offset $offset" if defined $offset; return [$s, []]; } );
$dbi->query_builder->register_tag_processor( limit => sub { my ($count, $offset) = @_; my $s = ''; $offset = 0 unless defined $offset; $s .= "limit $offset"; $s .= ", $count"; return [$s, []]; } );
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.