NAME

DBIx::Custom::Guides - DBIx::Custom Guides

GUIDES

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=dbname",
                                    user => 'ken', password => '!LFKD%$&');

Data source exmaples:

MySQL

    "dbi:mysql:database=$database"
    "dbi:mysql:database=$database;host=$hostname;port=$port"

SQLite

    "dbi:SQLite:dbname=$database"
    "dbi:SQLite:dbname=:memory:"

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

2. Suger methods

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()

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

append and filter argument can be specified. See also "METHODS" section.

update()

Execute update statement.

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

The following SQL is executed.

    update books set title = ?, author = ?;

The values of title and author is embedded into the placeholders.

append and filter argument can be specified. See also "METHOD" section.

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

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. see also "METHODS" section.

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

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 a row by fetch() method.

    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.

    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 the 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, rental
    where books.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  => '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. see also "METHODS" section.

3. Fetch row

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.

    my $sth = $result->sth;

4. Hash parameter binding

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 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. this is secure.

DBIx::Custom hash parameter binding system improve normal parameter binding to use 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} and {like title} 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.

    [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 = ?

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

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  => 'books',
                 param  => {title => 'Perl', author => 'Ken'},
                 filter => {title => 'encode_utf8'});
    
    # select(), having filter argument
    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 this default 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 example.

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

Filter works each column value, but you prepare a default filter for all clumn value.

    $dbi->default_fetch_filter('decode_utf8');

filter() method of DBIx::Custom::Result overwrites this default 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'});

Note that in fetch filter, column names must be lower case even if the column name conatains upper case charactors. This is requirment not to depend database systems.

Filter examples

MySQL

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

SQLite

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

6. Get high performance

Disable filter checking

Filter checking is executed by default. This is done to check right filter name is specified, but sometimes damage performance.

If you disable this filter checking, Set filter_check attribute to 0.

    $dbi->filter_check(0);

Use execute() method instead suger methods

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.

    my $query = $dbi->create_query(
        "insert into books {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 books (title, author) values (?, ?);',
        columns => ['title', '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() method.

caching

execute() method caches the parsed result of the source of SQL. 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 a source of SQL, 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 arguments is more than two, this method is called to set cache. If not, this method is called to get cache.

    $dbi->cache_method(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->register_tag_processor(
        name => sub {
           ...
        }
    );

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

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

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

GUIDES

1. Connect to the database

2. Suger methods

insert()

update()

delete()

select()

3. Fetch row

4. Hash parameter binding

5. Filtering

6. Get high performance

Disable filter checking

Use execute() method instead suger methods

caching

7. More features

Get DBI object

Change Result class

Custamize SQL builder object

Resister helper method

Module Install Instructions