NAME

DBIx::Custom::Guide - DBIx::Custom Guide

FEATURES

DBIx::Custom is the wrapper class of DBI to execute SQL easily. This module have the following features.

Execute insert, update, delete, or select statement easily
Create where clause flexibly
Named place holder support
Model support
Connection manager support
Choice your favorite relational database management system, MySQL, SQLite, PostgreSQL, Oracle, Microsoft SQL Server, Microsoft Access, DB2 or anything,
Filtering by data type or column name
Create order by clause flexibly

GUIDE

Connect to database

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

You can connect to database by connect method. dsn is data source name, user is user name, password is password.

dbi_option is DBI option. By default, the following option is set. Exeption is thrown when fatal error occur and commit mode is auto commit.

    {
        RaiseError  =>  1
        PrintError  =>  0
        AutoCommit  =>  1
    }

Execute query

Insert Statement : `insert`

If you want to execute insert statement, use insert method.

    $dbi->insert({title => 'Perl', author => 'Ken'}, table  => 'book');

First argument is insert row data, table is table name.

Update Statement : `update`

If you want to execute update stateimuse, use update method.

    $dbi->update(
        {title => 'Perl', author => 'Ken'},
        table  => 'book', 
        where  => {id => 5}
    );

First argument is update row data, table is table name, where is condition.

Note that you can't execute update method without where. If you want to update all rows, use update_all.

    $dbi->update_all({title => 'Perl', author => 'Ken'}, table  => 'book');

Delete Statement : `delete`

If you want to execute delete statement, use delete method.

    $dbi->delete(table  => 'book', where  => {author => 'Ken'});

table is table name, where is condition.

Note that you can't execute delete method without where. If you want to delete all rows, use delete_all method.

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

Select Statement : `select`

If you want to execute select statement, use select method.

    my $result = $dbi->select(table => 'book');

Return value is DBIx::Custom::Result object. You can fetch rows by fetch method.

    while (my $row = $result->fetch) {
        my $title  = $row->[0];
        my $author = $row->[1];
    }

You can specify column names by column option and condition by where option.

    my $result = $dbi->select(
        table  => 'book',
        column => ['author',  'title'],
        where  => {author => 'Ken'}
    );

You can specify join clause by join option.

    my $result = $dbi->select(
        table  => 'book',
        column => ['company.name as company_name']
        where  => {'book.name' => 'Perl'},
        join   => ['left outer join company on book.company_id = company.id]
    );

Note that join clause is joined only when where or column option contains table name, such as book.name.

You can append statement to the end of whole statement by append option.

    my $result = $dbi->select(
        table  => 'book',
        where  => {author => 'Ken'},
        append => 'for update',
    );

`execute`

If you want to execute SQL, use execute method.

    $dbi->execute("select * from book;");

You can specify named placeholder.

    $dbi->execute(
        "select * from book title = :title and author = :author;"
        {title => 'Perl', author => 'Ken'}
    );

:title and :author is named placeholder, which is replaced to placeholers.

    select * from book title = ? and author = ?;

`dbh`

    my $dbh = $dbi->dbh;

Get get database handle object of DBI.

`DBI` methods

    $dbi->do(...);
    $dbi->begin_work;

You can call all methods of DBI from DBIx::Custom object.

Fetch Rows

select method return value is DBIx::Custom::Result object. You can fetch a row or rows by various methods.

Fetch a row (array) : `fetch`

    my $row = $result->fetch;

fetch method fetch a row and put it into array reference. You can continue to fetch

    while (my $row = $result->fetch) {
        my $title  = $row->[0];
        my $author = $row->[1];
    }

Fetch only first row (array) : `fetch_first`

    my $row = $result->fetch_first;

fetch_first fetch a only first row and finish statment handle, and put it into array refrence.

Fetch all rows (array) : `fetch_all`

    my $rows = $result->fetch_all;

fetch_all fetch all rows and put them into array of array reference.

Fetch a row (hash) : `fetch_hash`

    my $row = $result->fetch_hash;

fetch_hash fetch a row and put it into hash reference. You can fetch a row while row exists.

    while (my $row = $result->fetch_hash) {
        my $title  = $row->{title};
        my $author = $row->{author};
    }

Fetch only a first row (hash) : `fetch_hash_first`

    my $row = $result->fetch_hash_first;

fetch_hash_first fetch only a first row and finish statement handle, and put them into hash refrence.

one is fetch_hash_first synonym to save word typing.

    my $row = $result->one;

Fetch all rows (hash) : `fetch_hash_all`

    my $rows = $result->fetch_hash_all;

fetch_hash_all fetch all rows and put them into array of hash reference.

Statement Handle : `sth`

    my $sth = $result->sth;

If you want to get statment handle, use <sth> method.

Named placeholder

Basic of Parameter

You can embedd named placeholder into SQL.

    select * from book where title = :title and author like :author;

:title and :author is named placeholder

Named placeholder is replaced by place holder.

    select * from book where title = ? and author like ?;

use execute to execute SQL.

    my $sql = "select * from book where title = :title and author like :author;"
    $dbi->execute($sql, {title => 'Perl', author => '%Ken%'});

You can specify filter at execute.

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

Manipulate same name's columns

It is ok if there are same name's columns. Let's think two date comparison.

    my $sql = "select * from table where date > :date and date < :date;";

In this case, You specify parameter values as array reference.

    my $dbi->execute($sql, {date => ['2010-10-01', '2012-02-10']});

Create where clause

Dinamically create where clause : where

You want to search multiple conditions in many times. Let's think the following three cases.

Case1: Search only title

    where title = :title

Case2: Search only author

    where author = :author

Case3: Search title and author

    where title = :title and author = :author

DBIx::Custom support dinamic where clause creating. At first, create DBIx::Custom::Where object by where.

    my $where = $dbi->where;

Set clause by clause

    $where->clause(
        ['and', 'title = :title, 'author = :author']
    );

clause is the following format.

    ['or' or 'and', PART1, PART1, PART1]

First argument is 'or' or 'and'. Later than first argument are part which contains named placeholder.

You can write more complex format.

    ['and', 
      'title = :title', 
      ['or', 'author = :author', 'date like :date']
    ]

This mean "title = :title and ( author = :author or date like :date )".

After setting clause, set param.

    $where->param({title => 'Perl'});

In this example, parameter contains only title.

If you execute string_to, you can get where clause which contain only named placeholder.

    my $where_clause = $where->to_string;

Parameter name is only title, the following where clause is created.

    where title = :title

You can also create where clause by stringification.

    my $where_clause = "$where";

This is useful to embbed it into SQL.

In case where clause contains same name columns

Even if same name parameters exists, you can create where clause. Let's think that there are starting date and ending date.

    my $param = {start_date => '2010-11-15', end_date => '2011-11-21'};

In this case, you set parameter value as array reference.

    my $p = {date => ['2010-11-15', '2011-11-21']};

You can embbed these values into same name parameters.

    $where->clause(
        ['and', 'date > :date', 'date < :date']
    );
    $where->param($p);

If starting date isn't exists, create the following parameter.

    my $p = {date => [$dbi->not_exists, '2011-11-21']};

You can get DBIx::Custom::NotExists object by not_exists This mean correnspondinf value isn't exists.

If ending date isn't exists, create the following parameter.

    my $p = {date => ['2010-11-15']};

If both date isn't exists, create the following parameter.

    my $p = {date => []};

This logic is a little difficut. See the following ones.

    my @date;
    push @date, exists $param->{start_date} ? $param->{start_date}
                                            : $dbi->not_exists;
    push @date, $param->{end_date} if exists $param->{end_date};
    my $p = {date => \@date};

With `select`

You can pass DBIx::Custom::Where object to where of select.

    my $where = $dbi->where;
    $where->clause(['and', 'title = :title', 'author = :author']);
    $where->param({title => 'Perl'});
    my $result = $dbi->select(table => 'book', where => $where);

You can also pass it to where of updateAdelete

With `execute`

DBIx::Custom::Where object is embedded into SQL.

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

    my $sql = <<"EOS";
    select * from book;
    $where
    EOS

    $dbi->execute($sql, $param, table => 'book');

Filtering

Register filter : `register_filter`

If you want to register filter, use register_filter.

    $dbi->register_filter(
        # Time::Piece object to DATE format
        tp_to_date => sub {
            my $date = shift;
            return $tp->strftime('%Y-%m-%d');
        },
        
        # DATE to Time::Piece object
        date_to_tp => sub {
            my $date = shift;
            return Time::Piece->strptime($date, '%Y-%m-%d');
        },
    );

Filter before sending data into database : `filter` option

If you filter sending data, use filter option.

    $dbi->execute(
        'insert into book (date) values (:date)',
        {date => $tp},
        filter => {date => 'tp_to_date'}
    );

You can use filter option in insert, update, delete, select method.

    $dbi->insert(
        {date => $tp},
        table => 'book',
        filter => {date => 'tp_to_date'}
    );

Filter after fetching data from database.

If you filter fetch data, use DBIx::Custom::Result's filter method.

    my $result = $dbi->select(column => 'date', table => 'book');
    $result->filter(date => 'date_to_tp');
    my $row = $result->one;

7. Model

Model

you can define model extending DBIx::Custom::Model to improve source code view.

At first, you create basic model class extending <DBIx::Custom::Model>. Each DBIx::Custom class inherit Object::Simple. so you can inherit the following way.

    package MyModel;
    use DBIx::Custom::Model -base;

Next, you create each model classes.

MyModel::book

    package MyModel::book;
    use MyModel -base;
    
    sub insert { ... }
    sub list { ... }

MyModel::company

    package MyModel::company;
    use MyModel -base;
    
    sub insert { ... }
    sub list { ... }

The follwoing modules location is needed.

    MyModel.pm
    MyModel / book.pm
            / company.pm

You can include these models by include_model

    $dbi->include_model('MyModel');

First argument is name space of model.

You can use model like this.

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

In mode, You can use such as methods, insert, update, update_all, delete, delete_all, select without table option.

    $dbi->model('book')->insert($param);

Model is DBIx::Custom::Model.

If you need table nameAyou can get it by table.

    my $table = $model->table;

You can get DBIx::Custom.

    my $dbi = $model->dbi;

You can also call all methods of DBIx::Custom and DBI.

    # DBIx::Custom method
    $model->execute($sql);
    
    # DBI method
    $model->begin_work;
    $model->commit;

If you want to get all models, you can get them by keys of models.

    my @models = keys %{$self->models};

You can set primary key to model.

   $model->primary_key(['id', 'number_id']);

Primary key is used by insert, update, delete, and select methods.

You can set column names

    $model->columns(['id', 'number_id']);

Column names is automarically set by setup_model. This method is needed to be call after include_model.

    $dbi->setup_model;

You can set join

    $model->join(['left outer join company on book.company_id = company.id']);

join is used by select method.

Create column clause automatically : mycolumn, column

To create column clause automatically, use mycolumn. Valude of table and columns is used.

    my $mycolumns = $model->mycolumn;

If table is 'book'Acolumn is ['id', 'name'], the following clause is created.

    book.id as id, book.name as name

These column name is for removing column name ambiguities.

You can create column clause from columns of other table.

    my $columns = $model->column('company');

If table is "company", column return ['id', 'name'], the following clause is created.

    company.id as "company.id", company.name as "company.name"

Model Examples

Model examples

    package MyDBI;
    use DBIx::Custom -base;
    
    sub connect {
        my $self = shift->SUPER::connect(@_);
        
        $self->include_model(
            MyModel => [
                'book',
                'company'
            ]
        );
    }
    
    package MyModel::book;
    use DBIx::Custom::Model -base;
    
    has primary_key => sub { ['id'] };
    
    sub insert { ... }
    sub list { ... }
    
    package MyModel::company;
    use DBIx::Custom::Model -base;

    has primary_key => sub { ['id'] };
    
    sub insert { ... }
    sub list { ... }

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

FEATURES

GUIDE

Connect to database

Execute query

Insert Statement : insert

Update Statement : update

Delete Statement : delete

Select Statement : select

execute

dbh

DBI methods