=head1 NAME

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

=head1 GUIDES

=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. Fetch row

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

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 C<register_filter()> method.

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

C<encode_utf8> and C<decode_utf8> filter is registerd by default.

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

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

C<filter> argument can be specified to suger methods, such as
C<insert()>, C<update()>, C<update_all()>,
C<delete()>, C<delete_all()>, C<select()>.

    # insert(), having filter argument
    $dbi->insert(table  => 'book',
                 param  => {title => 'Perl', author => 'Ken'},
                 filter => {title => 'encode_utf8'});
    
    # select(), having filter argument
    my $result = $dbi->select(
        table  => 'book',
        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');

C<filter()> argument overwrites this default filter.
    
    $dbi->default_bind_filter('encode_utf8');
    $dbi->insert(
        table  => 'book',
        param  => {title => 'Perl', author => 'Ken', price => 1000},
        filter => {author => 'to_upper_case', price => undef}
    );

This is same as the following example.

    $dbi->insert(
        table  => 'book',
        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 => 'book');
    $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');

C<filter()> method of L<DBIx::Custom::Result>
overwrites this default filter.

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

This is same as the following one.

    my $result = $dbi->select(
        table => 'book',
        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.

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, $FORMATS->{db_datetime});
    }
    
    # DATE to Time::Piece object
    date_to_tp => sub {
        return Time::Piece->strptime(shift, $FORMATS->{db_date});
    }
    
=head2 6. 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 7. 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 Custamize query builder object

You can custamize query builder object

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

=head2 EXAMPLES

=head3 Limit clause

    my $rows = $dbi->select(
        table => 'table1',
        where => {key1 => 1},
        append => "order by key2 {limit 1 0}" # {limit COUNT OFFSET}
    )->fetch_hash_all;

SQLite

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

MySQL

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

=cut