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 5',
        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'}
    );
    
    # Select, more flexible where
    my $result = $dbi->select(
        table  => 'books',
        where  => ['{= author} and {like title}', 
                   {author => 'Ken', title => '%Perl%'}]
    );

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} 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 books where {= author} and {like title}"
    );
    $dbi->execute($query, param => {author => 'Ken', title => '%Perl%'})

Other 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. 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 select(), 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 know SQL, you learn a little thing to do your works, using DBIx::Custom

1. 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%$&');

If database is SQLite, use DBIx::Custom::SQLite. you connect database easy way.

    use DBIx::Custom::SQLite;
    my $dbi = DBIx::Custom->connect(database => 'books');

If database is MySQL, use DBIx::Costom::MySQL.

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

2. Suger methods

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

insert()

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.

append and filter argument can be specified to insert() method if you need.

update()

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.

append and filter argument can be specified to update() method if you need.

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

delete()

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 the placehodler.

append and filter argument can be specified to delete() method if you need.

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

select()

Execute select statement, only table argument specified :

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

The following SQL is executed.

    select * from books;

the result of select() method is DBIx::Custom::Result object. You can fetch row.

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

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 you want to join tables, specify relation argument.

    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;

append argument add a string to the end of SQL statement. You can add "order by" or "limit" cluase.

    # Select, more complex
    my $result = $dbi->select(
        table  => 'books',
        where  => {author => 'Ken'},
        append => 'order by price limit 5',
    );

The following SQL is executed.

    select * books where author = ? order by price limit 5;

filter argument can be specified to filter parameters if you need.

3. Fetch row

select() method return DBIx::Custom::Result object. You can fetch row by various methods.

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 raw statement handle of DBI, use sth() attribute.

    my $sth = $result->sth;

4. Hash parameter binding

DBIx::Custom provides hash parameter binding.

At frist, I show normal way of parameter binding.

    use DBI;
    my $dbh = DBI->connect(...);
    my $sth = $dbh->prepare(
        "select * from books 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, it is secure.

DBIx::Custom hash parameter binding system improve normal parameter binding way to specify hash parameter.

    my $result = $dbi->execute(
        "select * from books where {= author} and {like title};"
        param => {author => 'Ken', title => '%Perl%'}
    );

This is same as the normal way, execpt that the parameter is hash. {= author} is called tag. tag is expand to placeholder string internally.

    select * from books where {= author} and {like title}
      -> select * from books where author = ? and title like ?;

The following tags is available.

Tags

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 NAME1 NAME2}   ->   (NAME1, NAME2) values (?, ?)
    {update NAME1 NAME2}   ->   set NAME1 = ?, NAME2 = ?

5. Filtering

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.

    $dbi->register_filter(
        to_upper_case => sub {
            my $value = shift;
            return uc $value;
        }
    );

encode_utf8 and decode_utf8 filter is registerd by default.

You can specify these filters to filter argument of execute() method.

    my $result = $dbi->execute(
        "select * from books where {= author} and {like title};"
        param  => {author => 'Ken', title => '%Perl%'},
        filter => {author => 'to_upper_case, title => 'encode_utf8'}
    );

you can also specify filter in suger methods, such as select(), update(), update_all, delete(), delete_all(), select().

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

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

Filter works each parmeter, but you prepare default filter for all parameters.

    $dbi->default_bind_filter('encode_utf8');

filter() argument overwrites the filter specified by default_bind_filter().

    $dbi->default_bind_filter('encode_utf8');
    $dbi->insert(
        table  => 'books',
        param  => {title => 'perl', author => 'Ken', price => 1000},
        filter => {author => 'to_upper_case', price => undef}
    );

This is same as the following one.

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

You can also specify filter when the row is fetched. This is reverse of bind filter.

    my $result = $dbi->select(table => 'books');
    $result->filter({title => 'decode_utf8', author => 'to_upper_case'});

you can specify default_fetch_filter().

    $dbi->default_fetch_filter('decode_utf8');

DBIx::Custom::Result::filter() overwrites the filter specified by default_fetch_filter()

    $dbi->default_fetch_filter('decode_utf8');
    my $result = $dbi->select(
        table => 'books',
        columns => ['title', 'author', 'price']
    );
    $result->filter({author => 'to_upper_case', price => undef});

This is same as the following one.

    my $result = $dbi->select(
        table => 'books',
        columns => ['title', 'author', 'price']
    );
    $result->filter({title => 'decode_utf8', author => 'to_upper_case'});

In fetch filter, column name must be lower case even if column conatain upper case charactor. This is requirment not to depend database systems.

6. Performance

Disable filter checking

filter_check is 1 by defaut. This is useful in debug.

This filter check maybe damege performance. If you require performance, set filter_check to 0.

Using execute() method instead suger methods

If you execute insert statement by using select() method, you sometimes can't meet performance requirment.

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.

    my $query = $dbi->create_query(
        "insert into books {insert title author};"
    );
    
    # (In the case of update statement)
    my $query = $dbi->create_query(
        "update books {update author};";
    );

Execute query repeatedly

    my $inputs = [
        {title => 'Perl',      author => 'Ken'},
        {title => 'Good days', author => 'Mike'}
    ];
    
    foreach my $input (@$inputs) {
        $dbi->execute($query, $input);
    }

This is faster than insert() and update() method.

caching

execute() method cache the parsing result of SQL soruce. Default to 1

    $dbi->cache(1);

Caching is on memory, but you can change this by cache_method(). First argument is DBIx::Custom object. Second argument is SQL source, such as "select * from books where {=title} and {=author};"; Third argument is parsed result, such as {sql => "select * from books where title = ? and author = ?", columns => ['title', 'author']}, this is hash reference. If argument is more than two, this is called ti set cache. otherwise, called to get cache.

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

7. More features

Get DBI object

You can get DBI object and call any method of DBI.

    $dbi->dbh->begin_work;
    $dbi->dbh->commit;
    $dbi->dbh->rollback;

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

Custamize SQL builder object

You can custamize SQL builder object

    my $dbi = DBIx::Custom->connect(...);
    $dbi->query_builder->start_tag('|');
    $dbi->query_builder->end_tag('|');
    $dbi->query_builder->register_tag_processor(
        name => sub {
           ...
        }
    );

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

`filter_check`

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

Enable filter check. Default to 1. This check maybe damege performance. If you require performance, set filter_check to 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

1. Connect to the database

2. Suger methods

insert()

update()

delete()

select()

3. Fetch row

4. Hash parameter binding

Tags

5. Filtering

6. Performance

Disable filter checking

Using execute() method instead suger methods

caching

7. More features

Get DBI object

Change Result class

Custamize SQL builder object

ATTRIBUTES

user

password

data_source

dbh

filters

default_bind_filter

default_fetch_filter

result_class

sql_builder

cache

cache_method

filter_check

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`

`filter_check`

`connect`

`insert`

`update`

`update_all`

`delete`

`delete_all`

`select`

`create_query`

`execute`

`register_filter`