=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. 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, $FORMATS->{db_datetime});

    }

    # DATE to Time::Piece object

    date_to_tp => sub {

        return Time::Piece->strptime(shift, $FORMATS->{db_date});

    }

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