NAME

DBIx::Custom - DBI with hash parameter binding and filtering system

VERSION

Version 0.1502

STATE

This module is not stable. Method name and functionality will be change.

SYNOPSYS

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

# Insert 
$dbi->insert(table  => 'books',
             param  => {title => 'perl', author => 'Ken'},
             filter => {title => 'encode_utf8'});

# Update 
$dbi->update(table  => 'books', 
             param  => {title => 'aaa', author => 'Ken'}, 
             where  => {id => 5},
             filter => {title => 'encode_utf8'});

# Update all
$dbi->update_all(table  => 'books',
                 param  => {title => 'aaa'},
                 filter => {title => 'encode_utf8'});

# Delete
$dbi->delete(table  => 'books',
             where  => {author => 'Ken'},
             filter => {title => 'encode_utf8'});

# Delete all
$dbi->delete_all(table => 'books');

# Select
my $result = $dbi->select(table => 'books');

# Select(more complex)
my $result = $dbi->select(
    table  => 'books',
    column => [qw/author title/],
    where  => {author => 'Ken'},
    append => 'order by id limit 1',
    filter => {tilte => 'encode_utf8'}
);

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

# Execute SQL
$dbi->execute("select title from books");

# Execute SQL with parameters and filter
$dbi->execute("select id from books where {= author} && {like title}",
              param  => {author => 'ken', title => '%Perl%'},
              filter => {tilte => 'encode_utf8'});

# Default filter
$dbi->default_query_filter('encode_utf8');
$dbi->default_fetch_filter('decode_utf8');

# Fetch
while (my $row = $result->fetch) {
    # ...
}

# Fetch hash
while (my $row = $result->fetch_hash) {
    
}

# DBI instance
my $dbh = $dbi->dbh;

ATTRIBUTES

user

Database user name.

$dbi  = $dbi->user('Ken');
$user = $dbi->user;

password

Database password.

$dbi      = $dbi->password('lkj&le`@s');
$password = $dbi->password;

data_source

Database data source.

$dbi         = $dbi->data_source("dbi:mysql:dbname=$database");
$data_source = $dbi->data_source;

If you know data source more, See also DBI.

sql_template

SQLTemplate instance. sql_template attribute must be the instance of DBIx::Cutom::SQLTemplate subclass.

$dbi          = $dbi->sql_template(DBIx::Cutom::SQLTemplate->new);
$sql_template = $dbi->sql_template;

the instance of DBIx::Cutom::SQLTemplate is set to this attribute by default.

filters

Filters

$dbi     = $dbi->filters({%filters});
$filters = $dbi->filters;

encode_utf8 and decode_utf8 is set to this attribute by default.

$encode_utf8 = $dbi->filters->{encode_utf8};
$decode_utf8 = $dbi->filters->{decode_utf8};

default_query_filter

Default query filter.

$dbi                  = $dbi->default_query_filter('encode_utf8');
$default_query_filter = $dbi->default_query_filter

default_fetch_filter

Fetching filter.

$dbi                  = $dbi->default_fetch_filter('decode_utf8');
$default_fetch_filter = $dbi->default_fetch_filter;

result_class

Result class.

$dbi          = $dbi->result_class('DBIx::Custom::Result');
$result_class = $dbi->result_class;

DBIx::Custom::Result is set to this attribute by default.

dbh

Database handle.

$dbi = $dbi->dbh($dbh);
$dbh = $dbi->dbh;

METHODS

This class is Object::Simple subclass. You can use all methods of Object::Simple

auto_commit

Auto commit.

$self        = $dbi->auto_commit(1);
$auto_commit = $dbi->auto_commit;

This is equal to

$dbi->dbh->{AutoCommit} = 1;
$auto_commit = $dbi->dbh->{AutoCommit};

commit

Commit.

$dbi->commit;

This is equal to

$dbi->dbh->commit;

rollback

Rollback.

$dbi->rollback

This is equal to

$dbi->dbh->rollback;

connect

Connect to database.

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

"AutoCommit" and "RaiseError" option is true, and "PrintError" option is false by dfault.

disconnect

Disconnect database.

$dbi->disconnect;

If database is already disconnected, this method do nothing.

reconnect

Reconnect to database.

$dbi->reconnect;

connected

Check if database is connected.

$is_connected = $dbi->connected;

register_filter

Resister filter.

$dbi->register_filter(%filters);

Example.

$dbi->register_filter(
    encode_utf8 => sub {
        my $value = shift;
        
        require Encode;
        
        return Encode::encode('UTF-8', $value);
    },
    decode_utf8 => sub {
        my $value = shift;
        
        require Encode;
        
        return Encode::decode('UTF-8', $value)
    }
);

create_query

Create the instance of DBIx::Custom::Query. This receive the string written by SQL template.

my $query = $dbi->create_query("select * from authors where {= name} and {= age}");

execute

Execute the query or the string written by SQL template.

$result = $dbi->execute($query,    param => $params, filter => {%filter});
$result = $dbi->execute($template, param => $params, filter => {%filter});

Example.

$result = $dbi->execute("select * from authors where {= name} and {= age}", 
                        {name => 'taro', age => 19});

while (my @row = $result->fetch) {
    # do something
}

See also DBIx::Custom::SQLTemplate.

Returned value DBIx::Custom::Result instance.

insert

Insert row.

$affected = $dbi->insert(table  => $table, 
                         param  => {%param},
                         append => $append,
                         filter => {%filter});

Retruned value is affected rows count.

Example.

# insert
$dbi->insert(table  => 'books', 
             param  => {title => 'Perl', author => 'Taro'},
             append => "some statement",
             filter => {title => 'encode_utf8'})

update

Update rows.

$affected = $dbi->update(table  => $table, 
                         param  => {%params},
                         where  => {%where},
                         append => $append,
                         filter => {%filter})

Retruned value is affected rows count

Example.

#update
$dbi->update(table  => 'books',
             param  => {title => 'Perl', author => 'Taro'},
             where  => {id => 5},
             append => "some statement",
             filter => {title => 'encode_utf8'});

update_all

Update all rows.

$affected = $dbi->update_all(table  => $table, 
                             param  => {%params},
                             filter => {%filter},
                             append => $append);

Retruned value is affected rows count.

Example.

# update_all
$dbi->update_all(table  => 'books', 
                 param  => {author => 'taro'},
                 filter => {author => 'encode_utf8'});

delete

Delete rows.

$affected = $dbi->delete(table  => $table,
                         where  => {%where},
                         append => $append,
                         filter => {%filter});

Retrun value is affected rows count

Example.

# delete
$dbi->delete(table  => 'books',
             where  => {id => 5},
             append => 'some statement',
             filter => {id => 'encode_utf8');

delete_all

Delete all rows.

$affected = $dbi->delete_all(table => $table);

Retruned value is affected rows count.

Example

# delete_all
$dbi->delete_all('books');

select

Select rows.

$result = $dbi->select(table    => $table,
                       column   => [@column],
                       where    => {%where},
                       append   => $append,
                       relation => {%relation}
                       filter   => {%filter});

Returned value is DBIx::Custom::Result instance.

Example.

# select * from books;
$result = $dbi->select('books');

# select * from books where title = 'Perl';
$result = $dbi->select('books', {title => 1});

# select title, author from books where id = 1 for update;
$result = $dbi->select(
    table  => 'books',
    column  => ['title', 'author'],
    where  => {id => 1},
    appned => 'for update'
);

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

AUTHOR

Yuki Kimoto, <kimoto.yuki at gmail.com>

Github http://github.com/yuki-kimoto

I develope this module on http://github.com/yuki-kimoto/DBIx-Custom

COPYRIGHT & LICENSE

Copyright 2009 Yuki Kimoto, all rights reserved.

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.