NAME

DBIx::Custom - Customizable DBI

VERSION

Version 0.0903

SYNOPSYS

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

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

# Query with parameters
$dbi->query("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'}, {id => 5});

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

# Select
$dbi->select('books');
$dbi->select('books', {author => 'taro'}); 
$dbi->select('books', [qw/author title/], {author => 'Ken'});
$dbi->select('books', [qw/author title/], {author => 'Ken'},
             'order by id limit 1');

Accessors

user

Set and get database user name

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

password

Set and get database password

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

data_source

Set and get 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

Set and get database name

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

host

Set and get host name

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

You can also set IP address like '127.03.45.12'.

port

Set and get port

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

options

Set and get DBI option

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

sql_tmpl

Set and get SQL::Template object

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

filters

Set and get 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 add_filter method.

formats

Set and get 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 add_format method.

bind_filter

Set and get binding filter

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

The following is bind filter sample

$dbi->bind_filter(sub {
    my ($value, $key, $dbi, $infos) = @_;
    
    # edit $value
    
    return $value;
});

Bind filter arguemts is

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

fetch_filter

Set and get Fetch filter

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

The following is fetch filter sample

$dbi->fetch_filter(sub {
    my ($value, $key, $dbi, $infos) = @_;
    
    # edit $value
    
    return $value;
});

Bind filter arguemts is

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

no_bind_filters

Set and get no filter keys when binding

$dbi             = $dbi->no_bind_filters(qw/title author/);
$no_bind_filters = $dbi->no_bind_filters;

no_fetch_filters

Set and get no filter keys when fetching

$dbi              = $dbi->no_fetch_filters(qw/title author/);
$no_fetch_filters = $dbi->no_fetch_filters;

result_class

Set and get resultset class

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

dbh

Get database handle

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

query_cache_max

Set and get query cache max

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

Default value is 50

Accessor summary

                   Accessor type       Variable type
user               class and object    scalar(string)
password           class and object    scalar(string)
data_source        class and object    scalar(string)
database           class and object    scalar(string)
host               class and object    scalar(string)

port               class and object    scalar(int)
options            class and object    hash(string)
sql_tmpl           class and object    scalar(DBIx::Custom::SQL::Template)
filters            class and object    hash(code ref)
formats            class and object    hash(string)

bind_filter        class and object    scalar(code ref)
fetch_filter       class and object    scalar(code ref)
no_bind_filters    class and object    array(string)
no_fetch_filters   class and object    array(string)
result_class       class and object    scalar(string)

dbh                object              scalar(DBI)
query_cache_max    class               scalar(int)

Methods

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;

filter_off

bind_filter and fitch_filter off

$dbi->filter_off

This method is equeal to

$dbi->bind_filter(undef);
$dbi->fetch_filter(undef);

add_filter

Resist filter

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

The following is add_filter sample

$dbi->add_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)
    }
);

add_format

Add format

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

The following is add_format sample.

$dbi->add_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->query($query, $params);

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

query

Query

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

The following is query sample

$result = $dbi->query("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::SQL::Template

Return value of query method is DBIx::Custom::Result object

run_transaction

Run transaction

$dbi->run_transaction(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 sample.

$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 sample.

$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 sample.

$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 sample.

$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 sample.

$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 samples

# 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->bind_filter(sub {
            # ...
        });
    }
}

last_insert_id

Get last insert id

$last_insert_id = $dbi->last_insert_id;

This method is implemented by subclass.

prepare

Prepare statement handle.

$sth = $dbi->prepare('select * from books;');

This method is same as DBI prepare method.

do

Execute SQL

$affected = $dbi->do('insert into books (title, author) values (?, ?)',
                    'Perl', 'taro');

Retrun value is affected rows count.

This method is same as DBI do method.

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

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

To install DBIx::Custom, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIx::Custom

CPAN shell

perl -MCPAN -e shell
install DBIx::Custom

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

VERSION

SYNOPSYS

Accessors

user

password

data_source

database

host

port

options

sql_tmpl

filters

formats

bind_filter

fetch_filter

no_bind_filters

no_fetch_filters

result_class

dbh

query_cache_max

Accessor summary

Methods

connect

disconnect

reconnect

connected

filter_off

add_filter

add_format

create_query

query

run_transaction

create_table

drop_table

insert

update

update_all

delete

delete_all

select

last_insert_id

prepare

do

DBIx::Custom default configuration

Inheritance of DBIx::Custom

AUTHOR

COPYRIGHT & LICENSE

Module Install Instructions