The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
DBIx-Custom-0.1646
River stage one • 4 direct dependents • 4 total dependents
14 ++
/ DBIx::Custom

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. connect() method use this value to connect the database.

Default filter when row is fetched.

filters

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

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.

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) each_column

    $dbi->each_column(
        sub {
            my ($self, $table, $column, $info) = @_;
            
            my $type = $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 columninformation.

(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,
                              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. 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.

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) model

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

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

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) where

    my $where = $dbi->where;

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.