/ 
DBIx-Custom-0.1301
River stage one • 6 direct dependents • 6 total dependents
/ DBIx::Custom

NAME

DBIx::Custom - DBI with hash bind and filtering system

VERSION

Version 0.1301

STATE

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

SYNOPSYS

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

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

# Query with parameters
$dbi->execute("select id from books where {= author} && {like title}",
            {author => 'ken', title => '%Perl%'});

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

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

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

# Select
my $result = $dbi->select('books');
my $result = $dbi->select('books', {where => {author => 'taro'}}); 

my $result = $dbi->select(
   'books', 
   {
       columns => [qw/author title/],
       where   => {author => 'Ken'}
    }
);

my $result = $dbi->select(
    'books',
    {
        columns => [qw/author title/],
        where   => {author => 'Ken'},
        append  => 'order by id limit 1'
    }
);

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.

database

Database name

$dbi      = $dbi->database('books');
$database = $dbi->database;

host

Host name

$dbi  = $dbi->host('somehost.com');
$host = $dbi->host;

You can also set IP address like '127.03.45.12'.

port

Port number

$dbi  = $dbi->port(1198);
$port = $dbi->port;

options

DBI options

$dbi     = $dbi->options({PrintError => 0, RaiseError => 1});
$options = $dbi->options;

sql_tmpl

SQLTemplate object

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

See also DBIx::Custom::SQLTemplate.

filters

Filters

$dbi     = $dbi->filters({filter1 => sub { }, filter2 => sub {}});
$filters = $dbi->filters;

This method is generally used to get a filter.

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

If you add filter, use resist_filter method.

formats

Formats

$dbi     = $dbi->formats({format1 => sub { }, format2 => sub {}});
$formats = $dbi->formats;

This method is generally used to get a format.

$filter = $dbi->formats->{datetime};

If you add format, use resist_format method.

default_query_filter

Binding filter

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

The following is bind filter example

$dbi->resist_filter(encode_utf8 => sub {
    my $value = shift;
    
    require Encode 'encode_utf8';
    
    return encode_utf8($value);
});

$dbi->default_query_filter('encode_utf8')

Bind filter arguemts is

1. $value : Value
2. $key   : Key
3. $dbi   : DBIx::Custom object
4. $infos : {table => $table, column => $column}

default_fetch_filter

Fetching filter

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

The following is fetch filter example

$dbi->resist_filter(decode_utf8 => sub {
    my $value = shift;
    
    require Encode 'decode_utf8';
    
    return decode_utf8($value);
});

$dbi->default_fetch_filter('decode_utf8');

Bind filter arguemts is

1. $value : Value
2. $key   : Key
3. $dbi   : DBIx::Custom object
4. $infos : {type => $table, sth => $sth, index => $index}

result_class

Resultset class

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

Default is DBIx::Custom::Result

dbh

Database handle

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

query_cache_max

Query cache max

$class           = DBIx::Custom->query_cache_max(50);
$query_cache_max = DBIx::Custom->query_cache_max;

Default value is 50

METHODS

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

connect

Connect to database

$dbi->connect;

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;

resist_filter

Resist filter

$dbi->resist_filter($fname1 => $filter1, $fname => $filter2);

The following is resist_filter example

$dbi->resist_filter(
    encode_utf8 => sub {
        my ($value, $key, $dbi, $infos) = @_;
        utf8::upgrade($value) unless Encode::is_utf8($value);
        return encode('UTF-8', $value);
    },
    decode_utf8 => sub {
        my ($value, $key, $dbi, $infos) = @_;
        return decode('UTF-8', $value)
    }
);

resist_format

Add format

$dbi->resist_format($fname1 => $format, $fname2 => $format2);

The following is resist_format example.

$dbi->resist_format(date => '%Y:%m:%d', datetime => '%Y-%m-%d %H:%M:%S');

create_query

Create Query object parsing SQL template

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

$query is <DBIx::Query> object. This is executed by query method as the following

$dbi->execute($query, $params);

If you know SQL template, see also DBIx::Custom::SQLTemplate.

execute

Query

$result = $dbi->execute($template, $params);

The following is query example

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

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

If you now syntax of template, See also DBIx::Custom::SQLTemplate

execute() return DBIx::Custom::Result object

transaction

Get DBIx::Custom::Transaction object, and you run a transaction.

$dbi->transaction->run(sub {
    my $dbi = shift;
    
    # do something
});

If transaction is success, commit is execute. If tranzation is died, rollback is execute.

create_table

Create table

$dbi->create_table(
    'books',
    'name char(255)',
    'age  int'
);

First argument is table name. Rest arguments is column definition.

drop_table

Drop table

$dbi->drop_table('books');

insert

Insert row

$affected = $dbi->insert($table, \%$insert_params);
$affected = $dbi->insert($table, \%$insert_params, $append);

Retrun value is affected rows count

The following is insert example.

$dbi->insert('books', {title => 'Perl', author => 'Taro'});

You can add statement.

$dbi->insert('books', {title => 'Perl', author => 'Taro'}, "some statement");

update

Update rows

$affected = $dbi->update($table, \%update_params, \%where);
$affected = $dbi->update($table, \%update_params, \%where, $append);

Retrun value is affected rows count

The following is update example.

$dbi->update('books', {title => 'Perl', author => 'Taro'}, {id => 5});

You can add statement.

$dbi->update('books', {title => 'Perl', author => 'Taro'},
             {id => 5}, "some statement");

update_all

Update all rows

$affected = $dbi->update_all($table, \%updat_params);

Retrun value is affected rows count

The following is update_all example.

$dbi->update_all('books', {author => 'taro'});

delete

Delete rows

$affected = $dbi->delete($table, \%where);
$affected = $dbi->delete($table, \%where, $append);

Retrun value is affected rows count

The following is delete example.

$dbi->delete('books', {id => 5});

You can add statement.

$dbi->delete('books', {id => 5}, "some statement");

delete_all

Delete all rows

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

Retrun value is affected rows count

The following is delete_all example.

$dbi->delete_all('books');

select

Select rows

$resut = $dbi->select(
    $table,                # must be string or array;
    \@$columns,            # must be array reference. this can be ommited
    \%$where_params,       # must be hash reference.  this can be ommited
    $append_statement,     # must be string.          this can be ommited
    $query_edit_callback   # must be code reference.  this can be ommited
);

$reslt is DBIx::Custom::Result object

The following is some select examples

# 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(
    'books',              # table
    ['title', 'author'],  # columns
    {id => 1},            # where clause
    'for update',         # append statement
);

You can join multi tables

$result = $dbi->select(
    ['table1', 'table2'],                # tables
    ['table1.id as table1_id', 'title'], # columns (alias is ok)
    {table1.id => 1},                    # where clase
    "where table1.id = table2.id",       # join clause (must start 'where')
);

You can also edit query

$dbi->select(
    'books',
    # column, where clause, append statement,
    sub {
        my $query = shift;
        $query->query_filter(sub {
            # ...
        });
    }
}

run_transaction

DBIx::Custom default configuration

DBIx::Custom have DBI object. This module is work well in the following DBI condition.

1. AutoCommit is true
2. RaiseError is true

By default, Both AutoCommit and RaiseError is true. You must not change these mode not to damage your data.

If you change these mode, you cannot get correct error message, or run_transaction may fail.

Inheritance of DBIx::Custom

DBIx::Custom is customizable DBI. You can inherit DBIx::Custom and custumize attributes.

package DBIx::Custom::Yours;
use base DBIx::Custom;

my $class = __PACKAGE__;

$class->user('your_name');
$class->password('your_password');

AUTHOR

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

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

I develope this module 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.