=encoding utf8

=head1 NAME

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

=head1 GUIDE

B<This guide is now writing.>

L<DBIx::Custom> is the class to make easy to execute SQL.
This is L<DBI> wrapper class like L<DBIx::Class> or L<DBIx::Simple>.
You can do thing more easy than L<DBIx::Class>, more flexible
than L<DBIx::Simple>.

L<DBIx::Custom> is B<not< O/R mapper, O/R mapper is usefule, but
you must learn many things. Created SQL is sometimes inefficient,
and in many cases you create raw SQL because
O/R mapper can't make complex SQL

L<DBIx::Custom> is opposit of O/R mapper.
The main purpose is that we respect SQL
and make easy difficult works if you use only L<DBI>.
If you already learn SQL, it is easy to use L<DBIx::Custom>.

I explain L<DBIx::Custom> a little in this section.
In L<DBIx::Custom>, you embbed tag in SQL.

    select * from book where {= title} and {=author};

The part arround {} is tag.
This SQL is converted to the one which contains place holder.

    select * from book where title = ? and author = ?;

Maybe you ask me that this conversion is meaningful.
On the top of this, usuful features is implemented.
See the following descriptions.

=over 4

=item 1. Specify place holder binding value as hash refernce

If you use L<DBI>, you must specify place holder binding value
as array.

    $sth->execute(@bind);

If you use L<DBIx::Custom>, you specify it as hash reference.
    
    my $param = {title => 'Perl', author => 'Ken'};
    $dbi->execute($sql, $param);

=item 2. Filtering

L<DBIx::Custom> provides filtering system.
For example, You think that about date value you want to 
manipulate it as date object like L<Time::Piece> in Perl,
and want to convert it to database DATE format.
and want to do reverse.

You can use filtering system.

At first, register filter.

    $dbi->register_filter(
        tp_to_date => sub {
            ...
        },
        date_to_tp => sub {
            ...
        }
    );

next, apply this filter to each column.

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

C<out> is perl-to-database way. C<in> is perl-from-database way.

This filter is automatically enabled in many method.

    $dbi->insert(table => 'book', param => {issue_date => $tp});


=item 3. Selective search condition

It is difficult to create selective where clause in L<DBI>.
For example, If C<title> and C<author> is specified, we create 
the following SQL.

    select * from book where title = ? and author = ?;

If only C<title> is specified, the following one

    select * from book where title = ?;

If only C<author> is specified, the following one,

    select * from book where author = ?;

This is hard work. Generally we use modules like L<SQL::Abstract>.
L<DBIx::Custom> prepare the way to make it easy.

    # Where object
    my $where = $dbi->where;
    
    # Search condition
    $where->clause(
        ['and', '{= title}', {'= author'}]
    );
    
    # Setting to automatically select needed column
    $where->param({title => 'Perl'});

    # Embbed where clause to SQL
    my $sql = "select * from book $where";

You can create where clause which has selected search condition.
You can write nesting of where clause and C<or> condition

=item 4. Methods for insert, update, delete, select

L<DBIx::Custom> provides methods for insert, update, delete, select
There are C<insert()>, C<update()>, C<delete()>,C<select()>.

    my $param = {title => 'Perl', author => 'Ken'};
    $dbi->insert(table => 'book', param => $param);

=item 5. Register method for table.

You can register method for table.

    $dbi->table('book')->method(
        list => sub {
            ...
        },
        something => sub {
            ...
        }
    );

use the mehtod.

    $dbi->table('book')->list;

Many O/R mapper must create class for table,
but L<DBIx::Custom> make it easy.

=back

L<DBIx::Custom> is very useful.
See the following if you are interested in it.

=head2 1. Connect to database

Load L<DBIx::Custom>.

    use DBIx::Custom;

use C<connect()> to connect to database.
Return value is L<DBIx::Custom> object.

    my $dbi = DBIx::Custom->connect(
        data_source => "dbi:mysql:database=bookstore",
        user => 'ken',
        password => '!LFKD%$&',
        dbi_options => {mysql_enable_utf8 => 1}
    );

C<data_source> must be one corresponding to the database system.
The following ones are data source example.

B<MySQL>

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

B<SQLite>

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

B<PostgreSQL>

    "dbi:Pg:dbname=$dbname"

B<Oracle>

    "dbi:Oracle:$dbname"
    "dbi:Oracle:host=$host;sid=$sid"

B<ODBC(Microsoft Access)>

    "dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=hoge.mdb"

B<ODBC(SQL Server)>

   "dbi:ODBC:driver={SQL Server};Server=(local);database=test;Trusted_Connection=yes;AutoTranslate=No;"

If authentication is needed, you can specify C<user> and C<password>

L<DBIx::Custom> is wrapper class of L<DBI>.
You can use all methods of L<DBI> from L<DBIx::Custom> object.

    $dbi->do(...);
    $dbi->begin_work;

use C<dhb()> to get database handle of L<DBI>

    my $dbh = $dbi->dbh;

By default, the following ones is set to database handle attributes.

    RaiseError  ->  1
    PrintError  ->  0
    AutoCommit  ->  1

If fatal error occuer, program terminate.
If SQL is executed, commit is executed automatically.

=head2 2. Methods for insert, update, delete, or insert

There are following methods.

=head3 C<insert()>

use C<insert()> to insert row into database

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

C<table> is table name, C<param> is insert data.

Following SQL is executed.

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

=head3 C<update()>

use C<update()> to update row in database.

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

C<table> is table name, C<param> is update data, C<where> is condition.

Following SQL is executed.

    update book set title = ?, author = ?;

You can't execute C<update()> without C<where> for safety.
use C<update_all()> if you want to update all rows.

    $dbi->update_all(table  => 'book', 
                     param  => {title => 'Perl', author => 'Ken'});

=head3 C<delete()>

use C<delete()> to delete rows from database.

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

C<table> is table name, C<where> is condition.

Following SQL is executed.

    delete from book where id = ?;

You can't execute C<delete()> without C<where> for safety.
use C<delete_all()> if you want to delete all rows.

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

=head3 C<select()>

use C<select()> to select rows from database

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

Following SQL is executed.

    select * from book;

Return value is L<DBIx::Custom::Result> object.
use C<fetch()> to fetch row.

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

See L<3. Fetch row/"3. Fetch row"> about L<DBIx::Custom::Result>.

Continue more examples.

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

C<column> is column names, C<where> is condition.

Following SQL is executed.

    select author, title from book where author = ?;

Next example.

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

C<relation> is relation of tables. This is inner join.

Following SQL is executed.

    select * from book, rental where book.name = ? and book.id = rental.book_id;

Next example.

    my $result = $dbi->select(
        table  => 'book',
        where  => {author => 'Ken'},
        append => 'for update',
    );

C<append> is string appending to end of SQL.

Following SQL is executed.

    select * book where author = ? for update;

C<appned> is also used at C<insert()>, C<update()>, C<update_all()>
C<delete()>, C<delete_all()>, and C<select()>.

Instead of C<column> and C<table>,
you can use C<selection>.
This is used to specify column names and table names at once

    my $selection = <<"EOS";
    title, author, company_name
    from book inner join company on book.company_id = company.id
    EOS

    $dbi->select(selection => $selection);

Note that you can't use where clause in C<selection>.
use clause like "inner join".

=head3 C<execute()> SQL

use C<execute()> to execute SQL

    $dbi->execute("select * from book;");

Process tag and execute SQL.

    $dbi->execute(
        "select * from book {= title} and {= author};"
        param => {title => 'Perl', author => 'Ken'}
    );

Following SQL is executed.

    select * from book title = ? and author = ?;

Values of title and author is embbdeded into placeholder.

See L<5. Tag/"5. Tag"> about tag.

You don't have to wirte last semicolon in C<execute()>. 

    $dbi->execute('select * from book');

=head2 3. Fetch row

Return value of C<select()> is L<DBIx::Custom::Result> object.
There are many methods to fetch row.

=head3 Fetch a row (array) C<fetch()>

use C<fetch()> to fetch a row and assign it into array reference.

    my $row = $result->fetch;

You can get all rows.

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

=head3 Fetch only first row (array) C<fetch_first()>

use C<fetch_first()> to fetch only first row.

    my $row = $result->fetch_first;

You can't fetch rest rows
because statement handle C<finish()> is executed.

=head3 Fetch rows (array) C<fetch_multi()>

use C<fetch_multi()> to fetch rows and assign it into
array reference which has array references as element.

    while (my $rows = $result->fetch_multi(2)) {
        my $title0   = $rows->[0][0];
        my $author0  = $rows->[0][1];
        
        my $title1   = $rows->[1][0];
        my $author1  = $rows->[1][1];
    }

Specify row count as argument.

You can get the following data.

    [
        ['Perl', 'Ken'],
        ['Ruby', 'Mark']
    ]

=head3 Fetch all rows (array) C<fetch_all>

use C<fetch_all()> to fetch all rows and assign it into
array reference which has array reference as element.

    my $rows = $result->fetch_all;

You can get the following data.

    [
        ['Perl', 'Ken'],
        ['Ruby', 'Mark']
    ]

=head3 Fetch a row (hash) C<fetch_hash()>

use C<fetch_hash()> to fetch a row and assign it into hash reference.

    while (my $row = $result->fetch_hash) {
        my $title  = $row->{title};
        my $author = $row->{author};
    }

=head3 Fetch only first row (hash) C<fetch_hash_first()>

use C<fetch_hash_first()> to fetch only first row
and assign it into hash reference.

    my $row = $result->fetch_hash_first;

You can't fetch rest rows
because statement handle C<finish()> is executed.

=head3 Fetch rows (hash) C<fetch_hash_multi()>

use C<fetch_hash_multi()> to fetch rows and
assign it into array reference which has hash references as element.

    while (my $rows = $result->fetch_hash_multi(5)) {
        my $title0   = $rows->[0]{title};
        my $author0  = $rows->[0]{author};
        my $title1  = $rows->[1]{title};
        my $author1 = $rows->[1]{author};
    }

Specify row count as argument.

You can get the following data.

    [
        {title => 'Perl', author => 'Ken'},
        {title => 'Ruby', author => 'Mark'}
    ]

=head3 Fetch all rows (hash) C<fetch_hash_all()>

use C<fetch_hash_all()> to fetch all rows and
assign it into array reference which has hash 
references as element.

    my $rows = $result->fetch_hash_all;

You can get the following data.

    [
        {title => 'Perl', author => 'Ken'},
        {title => 'Ruby', author => 'Mark'}
    ]

=head3 Statement handle C<sth()>

use <sth()> to get statement handle.

    my $sth = $result->sth;

=head2 4. Filtering

L<DBIx::Custom> provide value filtering. 
For example, You maybe want to convert L<Time::Piece> object to
database date format when register data into database.
and convert database date fromat to L<Time::Piece> object
when get data from database.

=head3 Register filter C<register_filter()>

use C<register_filter() to register filter.

    $dbi->register_filter(
        # Time::Piece object to DATE format
        tp_to_date => sub {
            my $date = shift;

            return '0000-00-00' unless $tp;
            return $tp->strftime('%Y-%m-%d');
        },
        
        # DATE to Time::Piece object
        date_to_tp => sub {
            my $date = shift;

            return if $date eq '0000-00-00';
            return Time::Piece->strptime($date, '%Y-%m-%d');
        },
    );

Registered filter is used by C<apply_filter()> or etc.

=head3 Apply filter C<apply_filter()>

use C<apply_filter()> to apply registered filter.

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

First argument is table name. Arguments after first argument are pairs of column
name and fitering rule. C<out> of filtering rule is filter which is used when data
is send to database. C<in> of filtering rule is filter which is used when data
is got from database. 

You can specify code reference as filter.

    issue_date => {out => sub { ... }, in => sub { ... }}

Applied filter become effective at insert()>, C<update()>, C<update_all()>,
C<delete()>, C<delete_all()>, C<select()>.

    my $tp = Time::Piece->strptime('2010/10/14', '%Y/%m/%d');
    my $result = $dbi->select(table => 'book', where => {issue_date => $tp});

When data is send to database, L<Time::Piece> object is converted
to database date format "2010-10-14"

When data is fetched, database date format is
converted to L<Time::Piece> object.

    my $row = $resutl->fetch_hash_first;
    my $tp = $row->{issue_date};

You can also use column name which contains table name.

    $dbi->select(
        table => 'book',
        where => {'book.issue_date' => $tp}
    );

In fetching, Filter is effective if you use "TABLE__COLUMN" as column name.

    my $result = $dbi->execute(
       "select issue_date as book__issue_date from book");

You can apply C<end> filter execute after C<in> filter.

    $dbi->apply_filter('book',
        issue_date => {out => 'tp_to_date', in => 'date_to_tp',
                       end => 'tp_to_displaydate'},
    );

=head3 Individual filter C<filter>

You can apply individual filter .
This filter overwrite the filter by C<apply_filter()>

use C<filter> option to apply individual filter
when data is send to database.
This option is used at C<insert()>, C<update()>,
C<update_all()>, C<delete()>, C<delete_all()>, C<select()>,
C<execute()>.

C<insert()> example:

    $dbi->insert(
        table => 'book',
        param => {issue_date => $tp, first_issue_date => $tp},
        filter => {issue_date => 'tp_to_date', first_issue_date => 'tp_to_date'}
    );

C<execute()> example:

my $sql = <<"EOS";
select YEAR(issue_date) as issue_year
from book
where YEAR(issue_date) = {? issue_year}
EOS
   
    my $result = $dbi->execute(
        $sql,
        param => {issue_year => '2010'},
        filter => {issue_year => 'tp_to_year'}
    );

You can also apply indivisual filter when you fetch row.
use C<DBIx::Custom::Result>'s C<filter()>.

    $result->filter(issue_year => 'year_to_tp');

=head3 End filtering : C<end_filter()>

You can add filter at end.
It is useful to create last output.
use C<end_filter()> to add end filter.

    $result->end_filter(issue_date => sub {
        my $tp = shift;
        
        return '' unless $tp;
        return $tp->strftime('%Y/%m/%d %h:%m:%s (%a)');
    });

In this example, L<Time::Piece> object is converted to readable format.

=head3 Automate applying filter C<each_column()>

It is useful to apply filter automatically at date type columns.
You can use C<each_column()> to process all column infos.

    $dbi->each_column(
        sub {
            my ($self, $table, $column, $info) = @_;
            
            my $type = $info->{TYPE_NAME};
            
            my $filter = $type eq 'DATE'     ? {out => 'tp_to_date', in => 'date_to_tp'}
                       : $type eq 'DATETIME' ? {out => 'tp_to_datetime', in => 'datetime_to_tp'}
                                             : undef;
            
            $self->apply_filter($table, $column, $filter)
              if $filter;
        }
    );

C<each_column() receive callback.
callback arguments are L<DBIx::Custom> object, table name, column name, column information.
Filter is applied automatically by column type.

=head2 5. Tag

=head3 Basic of Tag

You can embedd tag into SQL.

    select * from book where {= title} and {like author};

{= title} and {like author} are tag. Tag has the folloring format.

    {TAG_NAME ARG1 ARG2 ...}

Tag start C<{> and end C<}>. 
Don't insert space between C<{} and tag name.

C<{> and C<}> are reserved word.
If you want to use these, escape it by '\';

    select from book \\{ ... \\}

\ is perl's escape character, you need two \.

Tag is expanded before executing SQL.

    select * from book where title = ? and author like ?;

use C<execute()> to execute SQL which contains tag

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

You can specify values embedded into place holder as hash reference using
C<param> option.

You can specify C<filter()> at C<execute()>.

    $dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'}
                  filter => {title => 'to_something');

Note that at C<execute()> the filter applied by C<apply_filter()>
don't has effective to columns.
You have to use C<table> tag in SQL

    my $sql = "select * from {table book} where {= author} and {like title};"

=head3 Tag list

L<DBIx::Custom>では次のタグが使用可能です。

The following tag is available.

=head4 C<table>

    {table NAME} -> NAME

This is used to specify table name in SQL.
If you specify table name, Filtering by 
C<apply_filter()> is effective.

=head4 C<?>

    {? NAME}    ->   ?

=head4 C<=>

    {= NAME}    ->   NAME = ?

=head4 C<E<lt>E<gt>>

    {<> NAME}   ->   NAME <> ?

=head4 C<E<lt>>

    {< NAME}    ->   NAME < ?

=head4 C<E<gt>>

    {> NAME}    ->   NAME > ?

=head4 C<E<gt>=>

    {>= NAME}   ->   NAME >= ?

=head4 C<E<lt>=>

    {<= NAME}   ->   NAME <= ?

=head4 C<like>

    {like NAME}   ->   NAME like ?

=head4 C<in>

    {in NAME COUNT}   ->   NAME in [?, ?, ..]

=head4 C<insert_param>

    {insert_param NAME1 NAME2}   ->   (NAME1, NAME2) values (?, ?)

=head4 C<update_param>

    {update_param NAME1 NAME2}   ->   set NAME1 = ?, NAME2 = ?

=head3 Manipulate same name's columns

It is ok if there are same name's columns.
Let's think two date comparison.

    my $sql = "select * from table where {> date} and {< date};";

In this case, You specify paramter values as array reference.

    my $dbi->execute($sql, param => {date => ['2010-10-01', '2012-02-10']});

=head3 Register Tag C<register_tag()>

You can register custom tag.
use C<register_tag()> to register tag.

    $dbi->register_tag(
        '=' => sub {
            my $column = shift;
            
            return ["$column = ?", [$column]];
        }
    );

This is implementation of C<=> tag.
Tag format is the following one.

    {TAG_NAME ARG1 ARG2 ...}

In case C<=> tag. Format is

    {= title}

So subroutine receive one argument "title".
You have to return array reference in the following format.

    [
        String after expanding,
        [COLUMN1(This is used for place holder), COLUMN2 , ...]
    ]

First element is expanded stirng. In this example,

    'title = ?'

Secount element is array reference which is used to embedd value to
place holder. In this example,

    ['title']

If there are more than one placeholders,
This elements is multipul.

You return the following array reference.

    ['title = ?', ['title']]

See source of L<DBIx::Custom::Tag> to see many implementation.

=head2 6. Where句の動的な生成

=head3 Where句の動的な生成 where()

複数の検索条件を指定して、検索を行いたい場合があります。
次の3つのケースのwhere句を考えてみましょう。
下記のようなwhere句が必要になります。

titleの値だけで検索したい場合

    where {= title}

authorの値だけで検索したい場合

    where {= author}

titleとauthorの両方の値で検索したい場合

    where {= title} and {=author}

L<DBIx::Custom>では動的なWhere句の生成をサポートしています。
まずC<where()>でL<DBIx::Custom::Where>オブジェクトを生成します。

    my $where = $dbi->where;

次にC<clause()>を使用してwhere句を記述します。

    $where->clause(
        ['and', '{= title'}, '{= author}']
    );

clauseの指定方法は次のようになります。

    ['or' あるいは 'and', タグ1, タグ2, タグ3]

第一引数にはorあるいはandを指定します。第二引数以降には
検索条件をタグを使って記述します。

C<clause>の指定は入れ子にすることもでき、さらに複雑な条件
を記述することもできます。

    ['and', 
      '{= title}', 
      ['or', '{= author}', '{like date}']
    ]

このようにC<clause>を設定した後にC<param>にパラメータを指定します。
    
    my $param => {title => 'Perl'};
    $where->param($param);

この例ではtitleだけがパラメータに含まれています。

この後C<to_string()>を実行すると$paramに含まれるパラメータを満たす
where句を生成することができます。

    my $where_clause = $where->to_string;

パラメータはtitleだけですので、次のようなwhere句が生成されます。

    where {= title}

またL<DBIx::Custom>は文字列の評価をオーバーロードして、C<to_string()>
を呼び出すようにしていますので、次のようにしてwhere句を生成することも
できます。

    my $where_clause = "$where";

これはSQLの中にwhere句を埋め込むときにとても役立つ機能です。

=head3 同一の列名を含む場合

タグの中に同一の名前を持つものが存在した場合でも動的に
where句を作成することができます。

たとえば、パラメータとして開始日付と終了日付を受け取ったことを
考えてみてください。

    my $param = {start_date => '2010-11-15', end_date => '2011-11-21'};

また開始日付と終了日付の片方だけや、どちらも受け取らない場合もあるかもしれません。

この場合は次のようなパラメータに変換することで対応することができます。

    my $p = {date => ['2010-11-15', '2011-11-21']};

値が配列のリファレンスになっていることに注目してください。このようにすれば
同名の列を含むタグに順番に埋め込むことができます。

    $where->clause(
        ['and', '{> date}', '{< date}']
    );
    $where->param($p);

また開始日付が存在しない場合は次のようなデータを作成します。

    my $p = {date => [$dbi->not_exists, '2011-11-21']};

L<DBIx::Custom>のC<not_exists>でDBIx::Custom::NotExistsオブジェクトを
取得できます。これは対応する値が存在しないことを示すためのものです。

また終了日付が存在しない場合は次のようなデータを作成します。

    my $p = {date => ['2010-11-15']};

どちらも存在しない場合は次のようなデータを作成します。

    my $p = {date => []};

少し難しいので一番簡単に作成できるロジックを示しておきます。

    my @date;
    push @date, exists $param->{start_date} ? $param->{start_date}
                                            : $dbi->not_exists;
    push @date, $param->{end_date} if exists $param->{end_date};
    my $p = {date => \@date};

=head3 C<select()>との連携

L<DBIx::Custom::Where>オブジェクトは
C<select()>のC<where>に直接渡すことが
できます。
    
    my $where = $dbi->where;
    $where->clause(...);
    $where->param($param);
    my $result = $dbi->select(table => 'book', where => $where);

あるいはC<update()>、C<delete()>のwhereに指定することも可能です。

=head3 C<execute()>との連携

C<execute()>との連携です。SQLを作成するときに埋め込むことができます。


    my $where = $dbi->where;
    $where->clause(...);
    $where->param($param);

    my $sql = <<"EOS"
    select * from book;
    $where
    EOS

    $dbi->execute($sql, param => $param);

=head2 7. Table object

=head3 Create table object C<table()>

You can create table object to access table in database.
use C<tabel()> to create table object.

    my $table = $dbi->table('book');

Return value is L<DBIx::Custom::Table>.
From table object, you can call C<insert()>, C<update()>, C<update_all()>、
C<delete()>, C<delete_all()>, C<select()>.
You don't have to specify table name.

    $table->insert(param => $param);

You can add any method to table object.

    $table->method(
        register => sub {
            my $self = shift;
            my $table_name = $self->name;
            # ...
        },
        list => sub { ... }
    );

You can get table name by C<name()>.

You can call these method from table object.

    $table->register(...);
    $table->list(...);

=head2 Use L<DBIx::Custom> and <DBI> methods

You can call all methods of L<DBIx::Custom> and L<DBI>

    # DBIx::Custom method
    $table->execute($sql);
    
    # DBI method
    $table->begin_work;
    $table->commit;

=head2 Add table shared method

To share methods in all tables,
add method to base table.
You can get base table by C<base_table()>.

    $dbi->base_table->method(
        count => sub {
            my $self = shift;
            return $self->select(column => ['count(*)']);
        }
    );

This method is used by all talbes.

    $dbi->table('book')->count(...);

Even if same method name is added to table,
You can use base table method by C<base_METHOD()>.

    $table->method(
        count => sub {
            my $self = shift;
            
            $self->base_count(...);
            
            # ...
        }
    );

=head2 Model example

Generally, it is good to create model in constructor
in the class extending L<DBIx::Custom>.

    package MyDBI;
    
    use base 'DBIx::Custom';
    
    sub connect {
        my $self = shift->SUPER::connect(@_);
        
        $self->base_table->method(
            delete_multi => sub { ... }
        );
        
        $self->table('book')->method(
            register => sub { ... },
            remove   => sub { ... },
        );
        
        $self->table('company')->method(
            register => sub { ... },
            remove   => sub { ... },
        );
    }

You can use this class in the following way.

    my $dbi = MyDBI->connect(...);
    $dbi->table('book')->delete_multi(...);

=head2 8. Improve performance

=head3 Create query

If you can't get performance, create query by C<query> option.
For example, many insert is needed.

    my $params = [
        {title => 'Perl', author => 'Ken'},
        {title => 'Good day', author => 'Tom'}
    ]
    my $query = $dbi->insert(table => 'book', param => $params->[0], query => 1);

Return value is L<DBIx::Custom::Query> object.
This query is executed by C<execute()>.

    foreach my $param (@$params) {
        $dbi->execute($query, $param);
    }

Performance is improved because statement handle is reused
C<query> option is used in C<insert()>, C<update()>, C<update_all()>,
C<delete()>, C<delete_all()>.

Note that parameters count is same as method for creating query and C<execute()>.

You can create query from any SQL by C<create_query()>.

    my $query = $dbi->create_query(
        "insert into book {insert_param title author};";
    );

=head2 9. Other features

=head3 Add method

You can add method to L<DBIx::Custom> object.
use C<method().

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

You can call these methods from L<DBIx::Custom> object.

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

=head3 Change result class

You can change result class. By default it is L<DBIx::Custom::Result>.

    package MyResult;
    use base 'DBIx::Custom::Result';
    
    sub some_method { ... }

    1;
    
    package main;
    
    use MyResult;
    
    my $dbi = DBIx::Custom->connect(...);
    $dbi->result_class('MyResult');

=head3 キャッシング

SQL after parsing tag is cached for performance.
You can set C<cache()>. By default, chaching is true.

    $dbi->cache(1);

The way to cache is changed by C<cache_method()>.
Default method is the following one.
Cache is saved to memory.

    $dbi->cache_method(sub {
        sub {
            my $self = shift;
            
            $self->{_cached} ||= {};
            
            if (@_ > 1) {
                # Save cache
                $self->{_cached}{$_[0]} = $_[1] 
            }
            else {
                # Get cache
                return $self->{_cached}{$_[0]}
            }
        }
    });
    
First argument is L<DBIx::Custom> object.
Second argument is SQL before parsing.
Third argument is SQL information after parsing. This is hash reference.

If third argument exists, you save cache,
and if third argument isn't exists, you get chace.

=head1 EXAMPLES

You can see exsamples in the following wiki.

L<DBIx::Custom Wiki|https://github.com/yuki-kimoto/DBIx-Custom/wiki> - Many useful examples

=cut