The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
DBIx-Custom-0.1501
River stage one • 4 direct dependents • 4 total dependents
14 ++
/ DBIx::Custom

NAME

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

VERSION

Version 0.1501

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(
        'books',
        {
            columns => [qw/author title/],
            where   => {author => 'Ken'},
            append  => 'order by id limit 1',
            filter  => {tilte => 'encode_utf8'}
        }
    );

    # 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) {
        
    }

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_template

SQLTemplate object

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

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 register_filter method.

default_query_filter

Default query filter

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

Query filter example

    $dbi->register_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
    3. $dbi   : DBIx::Custom instance

default_fetch_filter

Fetching filter

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

Fetch filter example

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

    $dbi->default_fetch_filter('decode_utf8');

Fetching filter arguemts is

    1. Value
    2. DBIx::Custom instance

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;

METHODS

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

auto_commit

Set and Get auto commit

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

connect

Connect to database

    $dbi = DBIx::Custom->connect(%args);
    $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;

register_filter

Resister filter

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

register_filter 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 Query object parsing SQL template

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

$query is <DBIx::Query> instance. 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}", 
                            {name => '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 instance

insert

Insert row

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

Retrun 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})

Retrun 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);

Retrun value is affected rows count

Example

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

delete

Delete rows

    # delete
    $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);

Retrun 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,
                           filter => {%filter});

$reslt is DBIx::Custom::Result instance

The following is some select examples

    # select
    $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',
        where  => ['title', 'author'],
        where  => {id => 1},
        appned => 'for update'
    );

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')
    );

DBIx::Custom default configuration

By default, "AutoCommit" and "RaiseError" is true.

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.