=head1 NAME

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

=head1 GUIDE

=head2 1. Connect to the database

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

use C<connect()> to connect to the database.
You can sepecfiy C<data_soruce>, C<user>, and C<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;"

=head2 2. Suger methods

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

=head3 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 C<title> and C<author> is embedded into the placeholders.

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

=head3 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 C<title> and C<author> is embedded into the placeholders.

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

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

=head3 delete()

Execute delete statement.

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

The following SQL is executed.

    delete from book where id = ?;

The value of C<id> is embedded into the placehodler.

C<append> and C<filter> argument can be specified.
see also "METHODS" section.

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

=head3 select()

Execute select statement, only C<table> argument specified :

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

The following SQL is executed.

    select * from book;

the result of C<select()> method is L<DBIx::Custom::Result> object.
You can fetch a row by C<fetch()> method.

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

L<DBIx::Custom::Result> has various methods to fetch row.
See "3. Fetch row".

C<column> and C<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 C<author> is embdded into the placeholder.

If you want to join tables, specify C<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 C<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;

C<filter> argument can be specified.
see also "METHODS" section.

=head2 3. Result manipulation

C<select()> method return L<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 L<DBI>, use C<sth> attribute.

    my $sth = $result->sth;

=head2 4. Parameter binding

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

L<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 C<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 L<DBIx::Custom::QueryBuilder>.

C<{> and C<}> 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 \\}'

=head2 5. Filtering

If you want to filter the value, you can do this. For example,
L<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, L<Time::Piece> object is converted to 'yyyy-mm-dd' format
, and reverse.

You can apply this filter to use C<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, C<book>'s C<publication_date> is automatically converted.
C<out> means Perl to Database, C<in> means Database to Perl.

These applied filters have effect C<insert>, C<update>, C<update_all>,
C<delete>, C<delete_all>, C<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 C<execute> method by default.
If you want to have effect C<execute()> method, use C<table>
option.

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


You can also specify registered filters to C<filter> option of
C<insert()>, C<update()>, C<update_all()>, C<delete()>, C<delete_all()>,
C<select()> C<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 C<DBIx::Custom::Result> object.
This is overwrite applied filter.

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

B<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, '%Y-%m-%d %H:%M:%S');
    }
    
    # DATE to Time::Piece object
    date_to_tp => sub {
        return Time::Piece->strptime(shift, '%Y-%m-%d');
    }

=head2 6.Create table object

You can create table object which have methods.

    $dbi->table('book');

This class have C<insert()>, C<update()>, C<update_all()>,
C<delete()>, C<delete_all()>, C<select()>.
These is same as L<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 L<DBIx::Custom::Table> object as first argument.
This class have C<name()> to get table name and C<dbi()>
to get L<DBIx::Custom> object.

Defined method is called from table class.

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

=head2 7. Get high performance

=head3 Use execute() method instead suger methods

If you execute insert statement by C<insert()> method,
you sometimes can't get required performance.

C<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 C<create_query()> method.
    
    my $query = $dbi->create_query(
        "insert into book {insert_param title author};"
    );

Return value of C<create_query()> is L<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 C<insert()> method.

=head2 8. More features

=head3 Get DBI object

You can get L<DBI> object and call any method of L<DBI>.

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

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

=head3 Register tag processor

You can custamize query builder object

    my $dbi = DBIx::Custom->connect(...);
    $dbi->register_tag_processor(
        name => sub {
           ...
        }
    );

=head3 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 L<DBIx::Custom> object directory.

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

=head1 EXAMPLES

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

=head3 Limit clause
    
    # {limit COUNT OFFSET}
    select * from book {limit 1 0};

SQLite

    $dbi->register_tag_processor(
        limit => sub {
            my ($count, $offset) = @_;
            
            my $s = '';
            $s .= "limit $count";
            $s .= " offset $offset" if defined $offset;
            
            return [$s, []];
        }
    );

MySQL

    $dbi->register_tag_processor(
        limit => sub {
            my ($count, $offset) = @_;
            
            my $s = '';
            $offset = 0 unless defined $offset;
            $s .= "limit $offset";
            $s .= ", $count";
            
            return [$s, []];
        }
    );

=cut