NAME

DBIx::Custom - DBI interface, having hash parameter binding and filtering system

STABILITY

This module is not stable. Method name and implementations will be changed for a while.

SYNOPSYS

Connect to the database.

use DBIx::Custom;
my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=books",
                                user => 'ken', password => '!LFKD%$&');

Insert, update, and delete

# Insert 
$dbi->insert(table  => 'books',
             param  => {title => 'perl', author => 'Ken'},
             filter => {title => 'encode_utf8'});

# Update 
$dbi->update(table  => 'books', 
             param  => {title => 'aaa', author => 'Ken'}, 
             where  => {id => 5},
             filter => {title => 'encode_utf8'});

# Update all
$dbi->update_all(table  => 'books',
                 param  => {title => 'aaa'},
                 filter => {title => 'encode_utf8'});

# Delete
$dbi->delete(table  => 'books',
             where  => {author => 'Ken'},
             filter => {title => 'encode_utf8'});

# Delete all
$dbi->delete_all(table => 'books');

Select

# Select
my $result = $dbi->select(table => 'books');

# Select, more complex
my $result = $dbi->select(
    table  => 'books',
    column => [qw/author title/],
    where  => {author => 'Ken'},
    append => 'order by id limit 1',
    filter => {title => 'encode_utf8'}
);

# Select, join table
my $result = $dbi->select(
    table    => ['books', 'rental'],
    column   => ['books.name as book_name']
    relation => {'books.id' => 'rental.book_id'}
);

Execute SQL

# Execute SQL
$dbi->execute("select title from books");

# Execute SQL with hash binding and filtering
$dbi->execute("select id from books where {= author} && {like title}",
              param  => {author => 'ken', title => '%Perl%'},
              filter => {title => 'encode_utf8'});

# Create query and execute it
my $query = $dbi->create_query(
    "select id from books where {= author} && {like title}"
);
$dbi->execute($query, param => {author => 'ken', title => '%Perl%'})

More features.

# Default filter
$dbi->default_bind_filter('encode_utf8');
$dbi->default_fetch_filter('decode_utf8');

# Get DBI object
my $dbh = $dbi->dbh;

Fetch row.

# Fetch
while (my $row = $result->fetch) {
    # ...
}

# Fetch hash
while (my $row = $result->fetch_hash) {
    
}

DESCRIPTION

1. Features

DBIx::Custom is one of DBI interface modules, such as DBIx::Class, DBIx::Simple.

This module is not O/R mapper. O/R mapper is useful, but you must learn many syntax of the O/R mapper, which is almost another language Create SQL statement is offten not effcient and damage SQL performance. so you have to execute raw SQL in the end.

DBIx::Custom is middle area between DBI and O/R mapper. DBIx::Custom provide flexible hash parameter binding adn filtering system, and suger method, such as select(), update(), delete(), select() to execute a query easily.

DBIx::Custom respects SQL. SQL is not beautiful, but de-facto standard, so all people learing database system know it. If you know SQL statement, you learn a little thing about DBIx::Custom to do your works.

2. Basic usage

connect to the database

connect() method create a new DBIx::Custom object and connect to the database.

use DBIx::Custom;
my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=books",
                                user => 'ken', password => '!LFKD%$&');

Suger methods

DBIx::Custom has suger methods, such as insert(), update(), delete() and select(). If you want to do simple works, You don't have to create SQL statement.

Execute insert statement:

$dbi->insert(table  => 'books',
             param  => {title => 'perl', author => 'Ken'});

The following SQL is executed.

insert into (title, author) values (?, ?)

The values of title and author is embedded into placeholders.

Execute update statement:

$dbi->update(table  => 'books', 
             param  => {title => 'aaa', author => 'Ken'}, 
             where  => {id => 5});

The following SQL is executed.

update books set title = ?, author = ?;

The values of title and author is embedded into placeholders.

If you want to update all rows, use update_all() method instead.

Execute delete statement:

$dbi->delete(table  => 'books',
             where  => {author => 'Ken'});

The following SQL is executed.

delete from books where id = ?;

The value of id is embedded into a placehodler.

If you want to delete all rows, use delete_all() method instead.

Execute select statement:

Specify only table:

my $result = $dbi->select(table => 'books');

The following SQL is executed.

select * from books;

the result of select() method is DBIx::Custom::Result object. use fetch() method to fetch a row.

while (my $row = $result->fetch) {
    my $title  = $row->[0];
    my $author = $row->[1];
}

DBIx::Custom::Result has various methods to fetch row. See "2. Result of select statement".

Specify column and where arguments:

my $result = $dbi->select(
    table  => 'books',
    column => [qw/author title/],
    where  => {author => 'Ken'});

The following SQL is executed.

select author, title from books where author = ?;

the value of author is embdded into placeholder.

If relation argument is specifed, you can join tables.

my $result = $dbi->select(
    table    => ['books', 'rental'],
    column   => ['books.name as book_name']
    relation => {'books.id' => 'rental.book_id'}
);

The following SQL is executed.

select books.name as book_name from books
where books.id = rental.book_id;

ATTRIBUTES

`user`

my $user = $dbi->user;
$dbi     = $dbi->user('Ken');

User name. connect() method use this value to connect the database.

`password`

my $password = $dbi->password;
$dbi         = $dbi->password('lkj&le`@s');

Password. connect() method use this value to connect the database.

`data_source`

my $data_source = $dbi->data_source;
$dbi            = $dbi->data_source("DBI:mysql:database=dbname");

Data source. connect() method use this value to connect the database.

`dbh`

my $dbh = $dbi->dbh;
$dbi    = $dbi->dbh($dbh);

DBI object. You can call all methods of DBI.

`filters`

my $filters = $dbi->filters;
$dbi        = $dbi->filters(\%filters);

Filter functions. "encode_utf8" and "decode_utf8" is registered by default.

`default_bind_filter`

my $default_bind_filter = $dbi->default_bind_filter
$dbi                    = $dbi->default_bind_filter('encode_utf8');

Default filter when parameter binding is executed.

`default_fetch_filter`

my $default_fetch_filter = $dbi->default_fetch_filter;
$dbi                     = $dbi->default_fetch_filter('decode_utf8');

Default filter when row is fetched.

`result_class`

my $result_class = $dbi->result_class;
$dbi             = $dbi->result_class('DBIx::Custom::Result');

Result class for select statement. Default to DBIx::Custom::Result.

`sql_builder`

my $sql_class = $dbi->sql_builder;
$dbi          = $dbi->sql_builder(DBIx::Custom::QueryBuilder->new);

SQL builder. sql_builder must be the instance of DBIx::Custom::QueryBuilder subclass. Default to DBIx::Custom::QueryBuilder object.

`cache`

my $cache = $dbi->cache;
$dbi      = $dbi->cache(1);

Enable parsed DBIx::Custom::Query object caching. Default to 1.

`cache_method`

$dbi          = $dbi->cache_method(\&cache_method);
$cache_method = $dbi->cache_method

Method to set and get caches.

Example:

$dbi->cache_method(
    sub {
        my $self = shift;
        
        $self->{_cached} ||= {};
        
        if (@_ > 1) {
            $self->{_cached}{$_[0]} = $_[1] 
        }
        else {
            return $self->{_cached}{$_[0]}
        }
    }
);

METHODS

DBIx::Custom inherits all methods from Object::Simple and implements the following new ones.

`connect`

my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=dbname",
                                user => 'ken', password => '!LFKD%$&');

Create a new DBIx::Custom object and connect to the database. DBIx::Custom is a wrapper of DBI. AutoCommit and RaiseError option is true, and PrintError option is false by default.

`insert`

$dbi->insert(table  => $table, 
             param  => \%param,
             append => $append,
             filter => \%filter);

Execute insert statement. insert method have table, param, append and filter arguments. table is a table name. param is column-value pairs. this must be hash reference. append is a string added at the end of the SQL statement. filter is filters when parameter binding is executed. This is overwrites default_bind_filter. Return value of insert is the count of affected rows.

Example:

$dbi->insert(table  => 'books', 
             param  => {title => 'Perl', author => 'Taro'},
             append => "some statement",
             filter => {title => 'encode_utf8'})

`update`

$dbi->update(table  => $table, 
             param  => \%params,
             where  => \%where,
             append => $append,
             filter => \%filter)

Execute update statement. update method have table, param, where, append and filter arguments. table is a table name. param is column-value pairs. this must be hash reference. where is where clause. this must be hash reference. append is a string added at the end of the SQL statement. filter is filters when parameter binding is executed. This is overwrites default_bind_filter. Return value of update is the count of affected rows.

Example:

$dbi->update(table  => 'books',
             param  => {title => 'Perl', author => 'Taro'},
             where  => {id => 5},
             append => "for update",
             filter => {title => 'encode_utf8'});

`update_all`

$dbi->update_all(table  => $table, 
                 param  => \%params,
                 filter => \%filter,
                 append => $append);

Execute update statement to update all rows. Arguments is same as update method, except that update_all don't have where argument. Return value of update_all is the count of affected rows.

Example:

$dbi->update_all(table  => 'books', 
                 param  => {author => 'taro'},
                 filter => {author => 'encode_utf8'});

`delete`

$dbi->delete(table  => $table,
             where  => \%where,
             append => $append,
             filter => \%filter);

Execute delete statement. delete method have table, where, append, and filter arguments. table is a table name. where is where clause. this must be hash reference. append is a string added at the end of the SQL statement. filter is filters when parameter binding is executed. Return value of delete is the count of affected rows.

Example:

$dbi->delete(table  => 'books',
             where  => {id => 5},
             append => 'some statement',
             filter => {id => 'encode_utf8'});

`delete_all`

$dbi->delete_all(table => $table);

Execute delete statement to delete all rows. Arguments is same as delete method, except that delete_all don't have where argument. Return value of delete_all is the count of affected rows.

Example:

$dbi->delete_all(table => 'books');

`select`

my $result = $dbi->select(table    => $table,
                          column   => [@column],
                          where    => \%where,
                          append   => $append,
                          relation => \%relation,
                          filter   => \%filter);

Execute select statement. select method have table, column, where, append relation and filter arguments. table is a table name. where is where clause. this must be hash reference or a string containing such tags as "{= title} or {= author}". append is a string added at the end of the SQL statement. filter is filters when parameter binding is executed.

Example:

# select * from books;
my $result = $dbi->select(table => 'books');

# select * from books where title = ?;
my $result = $dbi->select(table => 'books', where => {title => 'Perl'});

# select title, author from books where id = ? for update;
my $result = $dbi->select(
    table  => 'books',
    column => ['title', 'author'],
    where  => {id => 1},
    appned => 'for update'
);

# select books.name as book_name from books, rental 
# where books.id = rental.book_id;
my $result = $dbi->select(
    table    => ['books', 'rental'],
    column   => ['books.name as book_name']
    relation => {'books.id' => 'rental.book_id'}
);

`create_query`

my $query = $dbi->create_query(
    "select * from authors where {= name} and {= age};"
);

Create the instance of DBIx::Custom::Query from SQL source.

`execute`

my $result = $dbi->execute($query,  param => $params, filter => \%filter);
my $result = $dbi->execute($source, param => $params, filter => \%filter);

Execute query or SQL source. Query is DBIx::Csutom::Query object. Return value is DBIx::Custom::Result in select statement, or the count of affected rows in insert, update, delete statement.

Example:

my $result = $dbi->execute("select * from authors where {= name} and {= age}", 
                        param => {name => 'taro', age => 19});

while (my $row = $result->fetch) {
    # do something
}

`register_filter`

$dbi->register_filter(%filters);
$dbi->register_filter(\%filters);

default_bind_filter()
default_fetch_filter()
filter argument of insert(), update(), update_all(), delete(), delete_all(), select(), execute method.
DBIx::Custom::Query::default_filter()
DBIx::Csutom::Query::filter()
DBIx::Custom::Result::default_filter()
DBIx::Custom::Result::filter()

Example:

$dbi->register_filter(
    encode_utf8 => sub {
        my $value = shift;
        
        require Encode;
        
        return Encode::encode('UTF-8', $value);
    },
    decode_utf8 => sub {
        my $value = shift;
        
        require Encode;
        
        return Encode::decode('UTF-8', $value)
    }
);

BUGS

Please tell me bugs if found.

<kimoto.yuki at gmail.com>

http://github.com/yuki-kimoto/DBIx-Custom

AUTHOR

Yuki Kimoto, <kimoto.yuki at gmail.com>

COPYRIGHT & LICENSE

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

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.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

STABILITY

SYNOPSYS

DESCRIPTION

1. Features

2. Basic usage

connect to the database

Suger methods

ATTRIBUTES

user

password

data_source

dbh

filters

default_bind_filter

default_fetch_filter

result_class

sql_builder

cache

cache_method

METHODS

connect

insert

update

update_all

delete

delete_all

select

create_query

execute

register_filter

BUGS

AUTHOR

COPYRIGHT & LICENSE

Module Install Instructions

`user`

`password`

`data_source`

`dbh`

`filters`

`default_bind_filter`

`default_fetch_filter`

`result_class`

`sql_builder`

`cache`

`cache_method`

`connect`

`insert`

`update`

`update_all`

`delete`

`delete_all`

`select`

`create_query`

`execute`

`register_filter`