++ed by:
FUKAI AZJADFTRE STAUGAARD XIAOCANG

4 PAUSE users
7 non-PAUSE users.

Yuki Kimoto

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

Register method. These method is called from DBIx::Custom object directory.

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

Register filter. Registered filters is available in the following attributes or arguments.

  • 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 {
            ...;
        }
    );

Register tag.

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.

Tags

The following tags is available.

(experimental) table

Table tag

    {table TABLE}    ->    TABLE

This is used to teach what is applied table to execute().

?

Placeholder tag.

    {? NAME}    ->   ?

=

Equal tag.

    {= NAME}    ->   NAME = ?

<>

Not equal tag.

    {<> NAME}   ->   NAME <> ?

<

Lower than tag

    {< NAME}    ->   NAME < ?

>

Greater than tag

    {> NAME}    ->   NAME > ?

>=

Greater than or equal tag

    {>= NAME}   ->   NAME >= ?

<=

Lower than or equal tag

    {<= NAME}   ->   NAME <= ?

like

Like tag

    {like NAME}   ->   NAME like ?

in

In tag.

    {in NAME COUNT}   ->   NAME in [?, ?, ..]

insert_param

Insert parameter tag.

    {insert_param NAME1 NAME2}   ->   (NAME1, NAME2) values (?, ?)

update_param

Updata parameter tag.

    {update_param NAME1 NAME2}   ->   set NAME1 = ?, NAME2 = ?

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

Copyright 2009-2011 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.