NAME

DBIx::Custom - DBI interface, having hash parameter binding and filtering system

SYNOPSYS

    use DBIx::Custom;
    my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=dbname",
                                    user => 'ken', password => '!LFKD%$&',
                                    dbi_option => {mysql_enable_utf8 => 1});

    # Insert 
    $dbi->insert(table  => 'book',
                 param  => {title => 'Perl', author => 'Ken'},
                 filter => [title => 'to_something']);
    
    # Update 
    $dbi->update(table  => 'book', 
                 param  => {title => 'Perl', author => 'Ken'}, 
                 where  => {id => 5},
                 filter => [title => 'to_something']);
    
    # Update all
    $dbi->update_all(table  => 'book',
                     param  => {title => 'Perl'},
                     filter => [title => 'to_something']);
    
    # Delete
    $dbi->delete(table  => 'book',
                 where  => {author => 'Ken'},
                 filter => [title => 'to_something']);
    
    # Delete all
    $dbi->delete_all(table => 'book');

    # Select
    my $result = $dbi->select(
        table  => 'book',
        column => [qw/author title/],
        where  => {author => 'Ken'},
        relation => {'book.id' => 'rental.book_id'},
        append => 'order by id limit 5',
        filter => [title => 'to_something']
    );

    # Execute SQL
    $dbi->execute("select title from book");
    
    # Execute SQL with hash binding and filtering
    $dbi->execute("select id from book where {= author} and {like title}",
                  param  => {author => 'ken', title => '%Perl%'},
                  filter => [title => 'to_something']);

    # Create query and execute it
    my $query = $dbi->create_query(
        "select id from book where {= author} and {like title}"
    );
    $dbi->execute($query, param => {author => 'Ken', title => '%Perl%'})

    # Get DBI object
    my $dbh = $dbi->dbh;

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

DESCRIPTIONS

DBIx::Custom is one of DBI interface modules, such as DBIx::Class, DBIx::Simple.

This module is not O/R mapper. O/R mapper is useful, but you must learn many syntax of the O/R mapper, which is almost another language. Created SQL statement is offten not effcient and damage SQL performance. so you have to execute raw SQL in the end.

DBIx::Custom is middle area between DBI and O/R mapper. DBIx::Custom provide flexible hash parameter binding and filtering system, and suger methods, such as insert(), update(), delete(), select() to execute SQL easily.

DBIx::Custom respects SQL. SQL is very complex and not beautiful, but de-facto standard, so all people learing database know it. If you already know SQL, you learn a little thing to use DBIx::Custom.

See DBIx::Custom::Guide for more details.

GUIDE

DBIx::Custom::Guide - DBIx::Custom complete guide

EXAMPLES

DBIx::Custom Wiki - Many useful examples

ATTRIBUTES

`cache`

    my $cache = $dbi->cache;
    $dbi      = $dbi->cache(1);

Enable parsed DBIx::Custom::Query object caching. Default to 1.

`data_source`

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

Data source. connect() method use this value to connect the database.

`dbh`

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

DBI object. You can call all methods of DBI.

`dbi_option`

    my $dbi_option = $dbi->dbi_option;
    $dbi            = $dbi->dbi_option($dbi_option);

DBI options.

Each option specified can ovewrite default_dbi_option.

connect() method use this value to connect the database.

`default_dbi_option`

    my $default_dbi_option = $dbi->default_dbi_option;
    $dbi            = $dbi->default_dbi_option($default_dbi_option);

DBI default options.

    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,

connect() method use this value to connect the database.

Default filter when row is fetched.

`filters`

    my $filters = $dbi->filters;
    $dbi        = $dbi->filters(\%filters);

Filters

`(experimental) models`

    my $models = $dbi->models;
    $dbi       = $dbi->models(\%models);

Models

`password`

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

Password. connect() method use this value to connect the database.

`query_builder`

    my $sql_class = $dbi->query_builder;
    $dbi          = $dbi->query_builder(DBIx::Custom::QueryBuilder->new);

SQL builder. query_builder() must be the instance of DBIx::Custom::QueryBuilder subclass. Default to DBIx::Custom::QueryBuilder object.

`result_class`

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

Result class for select statement. Default to DBIx::Custom::Result.

`(experimental) safety_column_name`

    my $safety_column_name = $self->safety_column_name;
    $dbi                   = $self->safety_column_name($name);

Safety column name regex. Default is qr/^[\w\.]*$/

`user`

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

User name. connect() method use this value to connect the database.

METHODS

DBIx::Custom inherits all methods from Object::Simple and use all method of DBI and implements the following new ones.

`(experimental) apply_filter`

    $dbi->apply_filter(
        $table,
        $column1 => {in => $infilter1, out => $outfilter1, end => $endfilter1}
        $column2 => {in => $infilter2, out => $outfilter2, end =. $endfilter2}
        ...,
    );

apply_filter is automatically filter for columns of table. This have effect insert, update, delete. select and DBIx::Custom::Result object. but this has'nt execute method.

If you want to have effect execute() method, use table arguments.

    $result = $dbi->execute(
        "select * from table1 where {= key1} and {= key2};",
         param => {key1 => 1, key2 => 2},
         table => ['table1']
    );

You can use three name as column name.

    1. column        : author
    2. table.column  : book.author
    3. table__column : book__author

`connect`

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

Create a new DBIx::Custom object and connect to the database. DBIx::Custom is a wrapper of DBI. AutoCommit and RaiseError options are true, and PrintError option is false by default.

`create_query`

    my $query = $dbi->create_query(
        "select * from book where {= author} and {like title};"
    );

Create the instance of DBIx::Custom::Query from the source of SQL. If you want to get high performance, use create_query() method and execute it by execute() method instead of suger methods.

    $dbi->execute($query, {author => 'Ken', title => '%Perl%'});

`execute`

    my $result = $dbi->execute($query,  param => $params, filter => \@filter);
    my $result = $dbi->execute($source, param => $params, filter => \@filter);

Execute query or the source of SQL. Query is DBIx::Custom::Query object. Return value is DBIx::Custom::Result if select statement is executed, or the count of affected rows if insert, update, delete statement is executed.

`delete`

    $dbi->delete(table  => $table,
                 where  => \%where,
                 append => $append,
                 filter => \@filter,
                 query  => 1);

Execute delete statement. delete method have table, where, append, and filter arguments. table is a table name. where is where clause. this must be hash reference. append is a string added at the end of the SQL statement. filter is filters when parameter binding is executed. query is if you don't execute sql and get DBIx::Custom::Query object as return value. default to 0. This is experimental. Return value of delete() is the count of affected rows.

`delete_all`

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

Execute delete statement to delete all rows. Arguments is same as delete method, except that delete_all don't have where argument. Return value of delete_all() is the count of affected rows.

`(experimental) delete_at()`

To delete row by using primary key, use delete_at()

    $dbi->delete_at(
        table => 'book',
        primary_key => ['id'],
        where => ['123']
    );

In this example, row which id column is 123 is deleted. NOTE that you must pass array reference as where.

You can also write arguments like this.

    $dbi->delete_at(
        table => 'book',
        primary_key => ['id'],
        param => {id => '123'}
    );

`insert`

    $dbi->insert(table  => $table, 
                 param  => \%param,
                 append => $append,
                 filter => \@filter,
                 query  => 1);

Execute insert statement. insert method have table, param, append and filter arguments. table is a table name. param is the pairs of column name value. this must be hash reference. append is a string added at the end of the SQL statement. filter is filters when parameter binding is executed. query is if you don't execute sql and get DBIx::Custom::Query object as return value. default to 0. This is experimental. This is overwrites default_bind_filter. Return value of insert() is the count of affected rows.

`(experimental) insert_at()`

To insert row by using primary key, use insert_at()

    $dbi->insert_at(
        table => 'book',
        primary_key => ['id'],
        where => ['123'],
        param => {name => 'Ken'}
    );

In this example, row which id column is 123 is inserted. NOTE that you must pass array reference as where. If param contains primary key, the key and value is delete from param.

`(experimental) insert_param`

    my $insert_param = $dbi->insert_param({title => 'a', age => 2});

Create insert parameter tag.

    {title => 'a', age => 2}   ->   {insert_param title age}

`(experimental) each_column`

    $dbi->each_column(
        sub {
            my ($self, $table, $column, $column_info) = @_;
            
            my $type = $column_info->{TYPE_NAME};
            
            if ($type eq 'DATE') {
                # ...
            }
        }
    );
Get column informations from database.
Argument is callback.
You can do anything in callback.
Callback receive four arguments, dbi object, table name,
column name and column information.

`(experimental) include_model`

    $dbi->include_model(
        'MyModel' => [
            'book', 'person', 'company'
        ]
    );

Include models. First argument is name space. Second argument is array reference of class base names.

If you don't specify second argument, All models under name space is included.

    $dbi->include_model('MyModel');

Note that in this case name spece module is needed.

    # MyModel.pm
    package MyModel;
    
    use base 'DBIx::Custom::Model';

The following model is instantiated and included.

    MyModel::book
    MyModel::person
    MyModel::company

You can get these instance by model().

    my $book_model = $dbi->model('book');

If you want to other name as model class, you can do like this.

    $dbi->include_model(
        'MyModel' => [
            {'book' => 'Book'},
            {'person' => 'Person'}
        ]
    );

`(experimental) method`

    $dbi->method(
        update_or_insert => sub {
            my $self = shift;
            # do something
        },
        find_or_create   => sub {
            my $self = shift;
            # do something
        }
    );

    $dbi->update_or_insert;
    $dbi->find_or_create;

`new`

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

Create a new DBIx::Custom object.

`(experimental) not_exists`

    my $not_exists = $dbi->not_exists;

Get DBIx::Custom::NotExists object.

`register_filter`

    $dbi->register_filter(%filters);
    $dbi->register_filter(\%filters);

filter argument of insert(), update(), update_all(), delete(), delete_all(), select() methods
execute() method
default_filter and filter of DBIx::Custom::Query
default_filter and filter of DBIx::Custom::Result

`register_tag`

    $dbi->register_tag(
        limit => sub {
            ...;
        }
    );

`rollback`

    $dbi->rollback;

Rollback transaction. This is same as DBI's rollback.

`select`

    my $result = $dbi->select(
        table     => $table,
        column    => [@column],
        where     => \%where,
        append    => $append,
        relation  => \%relation,
        join => ['left outer join company on book.company_id = company.id']
        filter    => \%filter,
        query     => 1,
        selection => $selection
    );

Execute select statement. select method have table, column, where, append, relation and filter arguments. table is a table name. column is column names. this is array reference or string. where is where clause. this is normally hash reference. append is a string added at the end of the SQL statement. filter is filters when parameter binding is executed. query is if you don't execute sql and get DBIx::Custom::Query object as return value. default to 0. This is experimental. selection is string of column name and tables. This is experimental

    selection => 'name, location.name as location_name ' .
                 'from company inner join location'

First element is a string. it contains tags, such as "{= title} or {like author}". Second element is paramters.

join is join clause after from clause. This is experimental.

`(experimental) select_at()`

To select row by using primary key, use select_at().

    $dbi->select_at(table => 'book', primary_key => ['id'], where => ['123']);

In this example, row which id colunm is 123 is selected. NOTE that you must pass array reference as where.

`update`

    $dbi->update(table  => $table, 
                 param  => \%params,
                 where  => \%where,
                 append => $append,
                 filter => \@filter,
                 query  => 1)

Execute update statement. update method have table, param, where, append and filter arguments. table is a table name. param is column-value pairs. this must be hash reference. where is where clause. this must be hash reference. append is a string added at the end of the SQL statement. filter is filters when parameter binding is executed. query is if you don't execute sql and get DBIx::Custom::Query object as return value. default to 0. This is experimental. This is overwrites default_bind_filter. Return value of update() is the count of affected rows.

`(experimental) update_param`

    my $update_param = $dbi->update_param({title => 'a', age => 2});

Create update parameter tag.

    {title => 'a', age => 2}   ->   {update_param title age}

`(experimental) model`

    $dbi->model('book')->method(
        insert => sub { ... },
        update => sub { ... }
    );
    
    my $model = $dbi->model('book');

Set and get a DBIx::Custom::Model object,

`(experimental) setup_model`

    $dbi->setup_model;

Setup all model objects. columns and primary_key is automatically set.

`update_all`

    $dbi->update_all(table  => $table, 
                     param  => \%params,
                     filter => \@filter,
                     append => $append);

Execute update statement to update all rows. Arguments is same as update method, except that update_all don't have where argument. Return value of update_all() is the count of affected rows.

`(experimental) update_at()`

To update row by using primary key, use update_at()

    $dbi->update_at(
        table => 'book',
        primary_key => ['id'],
        where => ['123'],
        param => {name => 'Ken'}
    );

In this example, row which id column is 123 is updated. NOTE that you must pass array reference as where. If param contains primary key, the key and value is delete from param.

`(experimental) where`

    my $where = $dbi->where(
        clause => ['and', '{= title}', '{= author}'],
        param => {title => 'Perl', author => 'Ken'}
    );

Create a new DBIx::Custom::Where object.

`cache_method`

    $dbi          = $dbi->cache_method(\&cache_method);
    $cache_method = $dbi->cache_method

Method to set and get caches.

STABILITY

DBIx::Custom is stable. APIs keep backword compatible except experimental one in the feature.

BUGS

Please tell me bugs if found.

<kimoto.yuki at gmail.com>

http://github.com/yuki-kimoto/DBIx-Custom

AUTHOR

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

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

SYNOPSYS

DESCRIPTIONS

GUIDE

EXAMPLES

ATTRIBUTES

cache

data_source

dbh

dbi_option

default_dbi_option

filters

(experimental) models

password

query_builder

result_class

(experimental) safety_column_name

user

METHODS

(experimental) apply_filter

connect

create_query

execute

delete

delete_all

(experimental) delete_at()

insert

(experimental) insert_at()

(experimental) insert_param

(experimental) each_column

(experimental) include_model

(experimental) method

new

(experimental) not_exists

register_filter

register_tag

rollback

select

(experimental) select_at()

update

(experimental) update_param

(experimental) model

(experimental) setup_model

update_all

(experimental) update_at()

(experimental) where

cache_method

Tags

(experimental) table

?

=

<>

<

>

>=

<=

like

in

insert_param

update_param