The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
DBIx-Custom-0.1636
River stage one • 4 direct dependents • 4 total dependents
14 ++
/ DBIx::Custom::Guide

NAME

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

GUIDE

1. Connect to the database

    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.

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

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  => '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.

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

update()

Execute update statement.

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

The following SQL is executed.

    update book 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  => 'book',
                 where  => {author => 'Ken'});

The following SQL is executed.

    delete from book 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 => 'book');

The following SQL is executed.

    select * from book;

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  => 'book',
        column => [qw/author title/],
        where  => {author => 'Ken'}
    );

The following SQL is executed.

    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.

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

The following SQL is executed.

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

The following SQL is executed.

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

filter argument can be specified. see also "METHODS" section.

3. Result manipulation

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

    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 \\}'

5. Filtering

If you want to filter the value, you can do this. For example, Time::Piece object to database date format, or reverse.

    $dbi->register_filter(
        tp_to_date => sub {
            return shift->strftime('%Y-%m-%d');
        },
        date_to_tp => sub {
            return Time::Piece->strptime(shift, '%Y-%m-%d');
        }
    );

In this example, Time::Piece object is converted to 'yyyy-mm-dd' format , and reverse.

You can apply this filter to use apply_filter() method.

    $dbi->apply_filter('book',
        puplication_date => {out => 'tp_to_date', in => 'date_to_tp'},
        someting_date => {out => 'tp_to_date', in => 'date_to_tp'}
    );

In this case, book's publication_date is automatically converted. out means Perl to Database, in means Database to Perl.

These applied filters have effect insert, update, update_all, delete, delete_all, select

    my $tp = Time::Piece::localtime;
    $dbi->insert(
        table => 'book',
        param => {name => 'Perl', publication_date => $tp}
    );
    
    my $result = $dbi->select(table => 'book');
    my $tp = $result->{publication_date};

Note that this has'nt execute method by default. If you want to have effect execute() method, use table option.

    my $result = $dbi->execute(
        "select * from book where {= id};",
         param => {id => 5},
         table => ['book']
    );

You can also specify registered filters to filter option of insert(), update(), update_all(), delete(), delete_all(), select() execute(). This is overwirte applied filter.

    $dbi->insert(
        table => 'book',
        param => {name => 'Perl', publication_date => $tp},
        filter => {publication_date => 'tp_to_date'}
    );

You can also specify DBIx::Custom::Result object. This is overwrite applied filter.

    my $result = $dbi->select(table => 'book');
    $result->filter(publication_date => 'date_to_tp');

Filter examples

6.Create table object

You can create table object which have methods.

    $dbi->table('book');

This class have insert(), update(), update_all(), delete(), delete_all(), select(). These is same as DBIx::Custom's methods except that you don't have to specify table.

    $dbi->table('book')->insert(
        param => {author => 'Taro', name => 'Perl'}
    );

You can define method for table.

    $dbi->table('book',
        insert_multi => sub {
            my $self = shift;
            my $table = $self->name;
            my $dbi = $self->dbi;
            
            # Do something
        },
        cross_summary => sub {
            my $self = shift;
            my $table = $self->name;
            my $dbi = $self->dbi;
            
            # Do something
        }
    );

Each method receive DBIx::Custom::Table object as first argument. This class have name() to get table name and dbi() to get DBIx::Custom object.

Defined method is called from table class.

    $dbi->table('book')->insert_multi(param => $param);

7. Get high performance

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

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

Register tag processor

You can custamize query builder object

    my $dbi = DBIx::Custom->connect(...);
    $dbi->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
        }
    );

Register helper methods. These method can be called from DBIx::Custom object directory.

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

EXAMPLES

DBIx::Custom Wiki - Many useful examples