NAME

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

SYNOPSYS

Connect to the database.

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

Insert, update, and delete

    # Insert 
    $dbi->insert(table  => 'book',
                 param  => {title => 'Perl', author => 'Ken'},
                 filter => {title => 'encode_utf8'});
    
    # Update 
    $dbi->update(table  => 'book', 
                 param  => {title => 'Perl', author => 'Ken'}, 
                 where  => {id => 5},
                 filter => {title => 'encode_utf8'});
    
    # Update all
    $dbi->update_all(table  => 'book',
                     param  => {title => 'Perl'},
                     filter => {title => 'encode_utf8'});
    
    # Delete
    $dbi->delete(table  => 'book',
                 where  => {author => 'Ken'},
                 filter => {title => 'encode_utf8'});
    
    # Delete all
    $dbi->delete_all(table => 'book');

Select

    # Select
    my $result = $dbi->select(table => 'book');
    
    # Select, more complex
    my $result = $dbi->select(
        table  => 'book',
        column => [qw/author title/],
        where  => {author => 'Ken'},
        append => 'order by id limit 5',
        filter => {title => 'encode_utf8'}
    );
    
    # Select, join table
    my $result = $dbi->select(
        table    => ['book', 'rental'],
        column   => ['book.name as book_name']
        relation => {'book.id' => 'rental.book_id'}
    );
    
    # Select, more flexible where
    my $result = $dbi->select(
        table  => 'book',
        where  => ['{= author} and {like title}', 
                   {author => 'Ken', title => '%Perl%'}]
    );

Execute SQL

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

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

Other features.

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

Fetch row.

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

DESCRIPTIONS

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. Created 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 and filtering system, and suger methods, such as insert(), update(), delete(), select() to execute SQL easily.

DBIx::Custom respects SQL. SQL is very complex and not beautiful, but de-facto standard, so all people learing database know it. If you already know SQL, you learn a little thing to use DBIx::Custom.

See DBIx::Custom::Guides for more details.

ATTRIBUTES

`cache`

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

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

`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.

`dbi_options`

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

DBI options. connect() method use this value to connect the database.

Default filter when row is fetched.

`filters`

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

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

`filter_check`

    my $filter_check = $dbi->filter_check;
    $dbi             = $dbi->filter_check(0);

this attribute is now deprecated and has no mean because check is always done.

`password`

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

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

`query_builder`

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

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

`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.

`user`

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

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

METHODS

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

`(experimental) apply_filter`

    $dbi->apply_filter(
        $table,
        $column1 => {in => $infilter1, out => $outfilter1}
        $column2 => {in => $infilter2, out => $outfilter2}
        ...,
    );

apply_filter is automatically filter for columns of table. This have effect insert, update, delete. select and DBIx::Custom::Result object. but this has'nt execute method.

If you want to have effect execute() method, use table arguments.

    $result = $dbi->execute(
        "select * from table1 where {= key1} and {= key2};",
         param => {key1 => 1, key2 => 2},
         table => ['table1']
    );

`begin_work`

    $dbi->begin_work;

Start transaction. This is same as DBI's begin_work.

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

`commit`

    $dbi->commit;

Commit transaction. This is same as DBI's commit.

`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 options are true, and PrintError option is false by default.

`create_query`

    my $query = $dbi->create_query(
        "select * from book where {= author} and {like title};"
    );

Create the instance of DBIx::Custom::Query from the source of SQL. If you want to get high performance, use create_query() method and execute it by execute() method instead of suger methods.

    $dbi->execute($query, {author => 'Ken', title => '%Perl%'});

`execute`

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

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

Example:

    my $result = $dbi->execute(
        "select * from book where {= author} and {like title}", 
        param => {author => 'Ken', title => '%Perl%'}
    );
    
    while (my $row = $result->fetch) {
        my $author = $row->[0];
        my $title  = $row->[1];
    }

`(experimental) expand`

    my %expand = $dbi->expand($source);

The following hash

    {book => {title => 'Perl', author => 'Ken'}}

is expanded to

    ('book.title' => 'Perl', 'book.author' => 'Ken')

This is used in select()

`delete`

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

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. query is if you don't execute sql and get DBIx::Custom::Query object as return value. default to 0. This is experimental. Return value of delete() is the count of affected rows.

Example:

    $dbi->delete(table  => 'book',
                 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 => 'book');

`(experimental) helper`

    $dbi->helper(
        update_or_insert => sub {
            my $self = shift;
            # do something
        },
        find_or_create   => sub {
            my $self = shift;
            # do something
        }
    );

    $dbi->update_or_insert;
    $dbi->find_or_create;

`insert`

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

Execute insert statement. insert method have table, param, append and filter arguments. table is a table name. param is the pairs of column name value. 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. query is if you don't execute sql and get DBIx::Custom::Query object as return value. default to 0. This is experimental. This is overwrites default_bind_filter. Return value of insert() is the count of affected rows.

Example:

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

`new`

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

Create a new DBIx::Custom object.

`(experimental) iterate_all_columns`

    $dbi->iterate_all_columns(
        sub {
            my ($table, $column, $column_info) = @_;
            
            # do something;
        }
    );

Iterate all columns of all tables. Argument is callback. You can do anything by callback.

`(experimental) or`

    $or = $dbi->or(1, 5);

Create DBIx::Custom::Or object. This is used with select method's where option.

`register_filter`

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

filter argument of insert(), update(), update_all(), delete(), delete_all(), select() methods
execute() method
default_filter and filter of DBIx::Custom::Query
default_filter and filter of DBIx::Custom::Result

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)
        }
    );

`rollback`

    $dbi->rollback;

Rollback transaction. This is same as DBI's rollback.

`select`

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

Execute select statement. select method have table, column, where, append, relation and filter arguments. table is a table name. where is where clause. this is normally hash reference. append is a string added at the end of the SQL statement. filter is filters when parameter binding is executed. query is if you don't execute sql and get DBIx::Custom::Query object as return value. default to 0. This is experimental.

Example:

    # select * from book;
    my $result = $dbi->select(table => 'book');
    
    # select * from book where title = ?;
    my $result = $dbi->select(table => 'book', where => {title => 'Perl'});
    
    # select title, author from book where id = ? for update;
    my $result = $dbi->select(
        table  => 'book',
        column => ['title', 'author'],
        where  => {id => 1},
        appned => 'for update'
    );
    
    # select book.name as book_name from book, rental
    # where book.id = rental.book_id;
    my $result = $dbi->select(
        table    => ['book', 'rental'],
        column   => ['book.name as book_name']
        relation => {'book.id' => 'rental.book_id'}
    );

If you use more complex condition, you can specify a array reference to where argument.

    my $result = $dbi->select(
        table  => 'book',
        column => ['title', 'author'],
        where  => ['{= title} or {like author}',
                   {title => '%Perl%', author => 'Ken'}]
    );

First element is a string. it contains tags, such as "{= title} or {like author}". Second element is paramters.

`update`

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

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. query is if you don't execute sql and get DBIx::Custom::Query object as return value. default to 0. This is experimental. This is overwrites default_bind_filter. Return value of update() is the count of affected rows.

Example:

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

`(experimental) txn_scope`

    {
        my $txn = $dbi->txn_scope;
        $dbi->insert(table => 'book', param => {title => 'Perl'});
        $dbi->insert(table => 'book', param => {title => 'Good days'});
        $txn->commit;
    }

Create transaction scope. If you escape scope(that is { .. }) and commited, Rollback is automatically done.

Note that this is feature of DBIx::TransactionManager DBIx::TransactionManager is required.

`(experimental) table`

    $dbi->table('book',
        insert => sub { ... },
        update => sub { ... }
    );
    
    my $table = $dbi->table('book');

Create a DBIx::Custom::Table object, or get a DBIx::Custom::Table object.

`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  => 'book', 
                     param  => {author => 'taro'},
                     filter => {author => 'encode_utf8'});

`(deprecated) default_bind_filter`

    my $default_bind_filter = $dbi->default_bind_filter;
    $dbi                    = $dbi->default_bind_filter($fname);

Default filter when parameter binding is executed.

`(deprecated) default_fetch_filter`

    my $default_fetch_filter = $dbi->default_fetch_filter;
    $dbi = $dbi->default_fetch_filter($fname);

`(deprecated) 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]}
            }
        }
    );

STABILITY

DBIx::Custom is now stable. APIs keep backword compatible in the feature.

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

SYNOPSYS

DESCRIPTIONS

ATTRIBUTES

cache

data_source

dbh

dbi_options

filters

filter_check

password

query_builder

result_class

user

METHODS

(experimental) apply_filter

begin_work

commit

connect

create_query

execute

(experimental) expand

delete

delete_all

(experimental) helper

insert

new

(experimental) iterate_all_columns

(experimental) or

register_filter

rollback

select

update

(experimental) txn_scope

(experimental) table

update_all

(deprecated) default_bind_filter

(deprecated) default_fetch_filter

(deprecated) cache_method

STABILITY

BUGS

AUTHOR

COPYRIGHT & LICENSE

Module Install Instructions

`cache`

`data_source`

`dbh`

`dbi_options`

`filters`

`filter_check`

`password`

`query_builder`

`result_class`

`user`

`(experimental) apply_filter`

`begin_work`

`commit`

`connect`

`create_query`

`execute`

`(experimental) expand`

`delete`

`delete_all`

`(experimental) helper`

`insert`

`new`

`(experimental) iterate_all_columns`

`(experimental) or`

`register_filter`

`rollback`

`select`

`update`

`(experimental) txn_scope`

`(experimental) table`

`update_all`

`(deprecated) default_bind_filter`

`(deprecated) default_fetch_filter`

`(deprecated) cache_method`