NAME

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

GUIDE

(This guide will be completed nearly future and contains EXPERIMENTAL features The features marked EXPERIMENTAL in POD of DBIx::Custom is EXPERIMENTAL ones)

DBIx::Custom is the class to make easy to execute SQL. This is DBI wrapper class like DBIx::Class or DBIx::Simple. You can do thing more easy than DBIx::Class, more flexible than DBIx::Simple.

DBIx::Custom is not O/R mapper, O/R mapper is usefule, but you must learn many things. Created SQL is sometimes inefficient, and in many cases you create raw SQL because O/R mapper can't make complex SQL

DBIx::Custom is opposit of O/R mapper. The main purpose is that we respect SQL and make easy difficult works if you use only DBI. If you already learn SQL, it is easy to use DBIx::Custom.

I explain DBIx::Custom a little in this section. In DBIx::Custom, you embbed tag in SQL.

    select * from book where {= title} and {=author};

The part arround {} is tag. This SQL is converted to the one which contains place holder.

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

Maybe you ask me that this conversion is meaningful. On the top of this, usuful features is implemented. See the following descriptions.

1. Specify place holder binding value as hash refernce

If you use DBI, you must specify place holder binding value as array.

    $sth->execute(@bind);

If you use DBIx::Custom, you specify it as hash reference.

    my $param = {title => 'Perl', author => 'Ken'};
    $dbi->execute($sql, $param);

2. Filtering

DBIx::Custom provides filtering system. For example, You think that about date value you want to manipulate it as date object like Time::Piece in Perl, and want to convert it to database DATE format. and want to do reverse.

You can use filtering system.

At first, register filter.

    $dbi->register_filter(
        tp_to_date => sub {
            ...
        },
        date_to_tp => sub {
            ...
        }
    );

next, apply this filter to each column.

    $dbi->apply_filter('book',
        'issue_date' => {out => 'tp_to_date', in => 'date_to_tp'}
    );

out is perl-to-database way. in is perl-from-database way.

This filter is automatically enabled in many method.

    $dbi->insert(table => 'book', param => {issue_date => $tp});

3. Selective search condition

It is difficult to create selective where clause in DBI. For example, If title and author is specified, we create the following SQL.

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

If only title is specified, the following one

    select * from book where title = ?;

If only author is specified, the following one,

    select * from book where author = ?;

This is hard work. Generally we use modules like SQL::Abstract. DBIx::Custom prepare the way to make it easy.

    # Where object
    my $where = $dbi->where;
    
    # Search condition
    $where->clause(
        ['and', '{= title}', {'= author'}]
    );
    
    # Setting to automatically select needed column
    $where->param({title => 'Perl'});

    # Embbed where clause to SQL
    my $sql = "select * from book $where";

You can create where clause which has selected search condition. You can write nesting of where clause and or condition

4. Methods for insert, update, delete, select

DBIx::Custom provides methods for insert, update, delete, select There are insert(), update(), delete(),select().

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

5. Register method for table.

You can register method for table.

    $dbi->table('book')->method(
        list => sub {
            ...
        },
        something => sub {
            ...
        }
    );

use the mehtod.

    $dbi->table('book')->list;

Many O/R mapper must create class for table, but DBIx::Custom make it easy.

DBIx::Custom is very useful. See the following if you are interested in it.

1. Connect to database

Load DBIx::Custom.

    use DBIx::Custom;

use connect() to connect to database. Return value is DBIx::Custom object.

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

data_source must be one corresponding to the database system. The following ones are data source example.

MySQL

    "dbi:mysql:database=$database"
    "dbi:mysql:database=$database;host=$hostname;port=$port"

SQLite

    "dbi:SQLite:dbname=$database"
    "dbi:SQLite:dbname=:memory:"

PostgreSQL

    "dbi:Pg:dbname=$dbname"

Oracle

    "dbi:Oracle:$dbname"
    "dbi:Oracle:host=$host;sid=$sid"

ODBC(Microsoft Access)

    "dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=hoge.mdb"

ODBC(SQL Server)

   "dbi:ODBC:driver={SQL Server};Server=(local);database=test;Trusted_Connection=yes;AutoTranslate=No;"

If authentication is needed, you can specify user and password

DBIx::Custom is wrapper class of DBI. You can use all methods of DBI from DBIx::Custom object.

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

use dhb() to get database handle of DBI

    my $dbh = $dbi->dbh;

By default, the following ones is set to database handle attributes.

    RaiseError  ->  1
    PrintError  ->  0
    AutoCommit  ->  1

If fatal error occuer, program terminate. If SQL is executed, commit is executed automatically.

2. Methods for insert, update, delete, or insert

There are following methods.

`insert()`

use insert() to insert row into database

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

table is table name, param is insert data.

Following SQL is executed.

    insert into (title, author) values (?, ?);

`update()`

use update() to update row in database.

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

table is table name, param is update data, where is condition.

Following SQL is executed.

    update book set title = ?, author = ?;

You can't execute update() without where for safety. use update_all() if you want to update all rows.

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

`delete()`

use delete() to delete rows from database.

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

table is table name, where is condition.

Following SQL is executed.

    delete from book where id = ?;

You can't execute delete() without where for safety. use delete_all() if you want to delete all rows.

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

`select()`

use select() to select rows from database

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

Following SQL is executed.

    select * from book;

Return value is DBIx::Custom::Result object. use fetch() to fetch row.

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

See "3. Fetch row" in 3. Fetch row about DBIx::Custom::Result.

Continue more examples.

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

column is column names, where is condition.

Following SQL is executed.

    select author, title from book where author = ?;

Next example.

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

You can join table by join.

Following SQL is executed.

    select company.name as company__name
    from book
      left outer join company on book.company_id = company.id
    where book.name = ?;

company_if of book and id of company is left outer joined.

Note that only when where or column contain table name, join is joined. if you specify the following option, join is not joined because join is not needed.

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

Following SQL is executeed.

    select * from book where book.name = ?;

You can specify column names easily using mycolumn() and column().

    my $result = $dbi->select(
        table  => 'book',
        column => [
            $dbi->mycolumn('book' => ['name']),
            $dbi->column('company' => ['id', 'name'])
        ],
        join   => ['left outer join company on book.company_id = company.id]
    );

The following SQL is executed.

    select book.name as name,
      company.id as comapny__id,
      company.name as company__name
    from book
      left outer join company on book.company_id = company.id

Next example.

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

append is string appending to end of SQL.

Following SQL is executed.

    select * book where author = ? for update;

appned is also used at insert(), update(), update_all() delete(), delete_all(), and select().

`execute()`

use execute() to execute SQL

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

Process tag and execute SQL.

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

Following SQL is executed.

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

Values of title and author is embbdeded into placeholder.

See "5. Tag" in 5. Tag about tag.

You don't have to wirte last semicolon in execute().

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

insert by using primary key : `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.

Update by using primary key : `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.

Delete by using primary key : `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'});

Select by using primary key : `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.

You can also write arguments like this.

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

3. Fetch row

Return value of select() is DBIx::Custom::Result object. There are many methods to fetch row.

Fetch a row (array) : `fetch()`

use fetch() to fetch a row and assign it into array reference.

    my $row = $result->fetch;

You can get all rows.

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

Fetch only first row (array) : `fetch_first()`

use fetch_first() to fetch only first row.

    my $row = $result->fetch_first;

You can't fetch rest rows because statement handle finish() is executed.

Fetch rows (array) : `fetch_multi()`

use fetch_multi() to fetch rows and assign it into array reference which has array references as element.

    while (my $rows = $result->fetch_multi(2)) {
        my $title0   = $rows->[0][0];
        my $author0  = $rows->[0][1];
        
        my $title1   = $rows->[1][0];
        my $author1  = $rows->[1][1];
    }

Specify row count as argument.

You can get the following data.

    [
        ['Perl', 'Ken'],
        ['Ruby', 'Mark']
    ]

Fetch all rows (array) : `fetch_all`

use fetch_all() to fetch all rows and assign it into array reference which has array reference as element.

    my $rows = $result->fetch_all;

You can get the following data.

    [
        ['Perl', 'Ken'],
        ['Ruby', 'Mark']
    ]

Fetch a row (hash) : `fetch_hash()`

use fetch_hash() to fetch a row and assign it into hash reference.

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

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

use fetch_hash_first() to fetch only first row and assign it into hash reference.

    my $row = $result->fetch_hash_first;

You can't fetch rest rows because statement handle finish() is executed.

Fetch rows (hash) : `fetch_hash_multi()`

use fetch_hash_multi() to fetch rows and assign it into array reference which has hash references as element.

    while (my $rows = $result->fetch_hash_multi(5)) {
        my $title0   = $rows->[0]{title};
        my $author0  = $rows->[0]{author};
        my $title1  = $rows->[1]{title};
        my $author1 = $rows->[1]{author};
    }

Specify row count as argument.

You can get the following data.

    [
        {title => 'Perl', author => 'Ken'},
        {title => 'Ruby', author => 'Mark'}
    ]

Fetch all rows (hash) : `fetch_hash_all()`

use fetch_hash_all() to fetch all rows and assign it into array reference which has hash references as element.

    my $rows = $result->fetch_hash_all;

You can get the following data.

    [
        {title => 'Perl', author => 'Ken'},
        {title => 'Ruby', author => 'Mark'}
    ]

Statement handle : `sth()`

use <sth()> to get statement handle.

    my $sth = $result->sth;

4. Filtering

DBIx::Custom provide value filtering. For example, You maybe want to convert Time::Piece object to database date format when register data into database. and convert database date fromat to Time::Piece object when get data from database.

Register filter : `register_filter()`

use register_filter() to register filter.

    $dbi->register_filter(
        # Time::Piece object to DATE format
        tp_to_date => sub {
            my $date = shift;

            return '0000-00-00' unless $tp;
            return $tp->strftime('%Y-%m-%d');
        },
        
        # DATE to Time::Piece object
        date_to_tp => sub {
            my $date = shift;

            return if $date eq '0000-00-00';
            return Time::Piece->strptime($date, '%Y-%m-%d');
        },
    );

Registered filter is used by apply_filter() or etc.

Apply filter : `apply_filter()`

use apply_filter() to apply registered filter.

    $dbi->apply_filter('book',
        issue_date => {out => 'tp_to_date', in => 'date_to_tp'},
        first_issue_date => {out => 'tp_to_date', in => 'date_to_tp'}
    );

First argument is table name. Arguments after first argument are pairs of column name and fitering rule. out of filtering rule is filter which is used when data is send to database. in of filtering rule is filter which is used when data is got from database.

You can specify code reference as filter.

    issue_date => {out => sub { ... }, in => sub { ... }}

Applied filter become effective at insert()>, update(), update_all(), delete(), delete_all(), select().

    my $tp = Time::Piece->strptime('2010/10/14', '%Y/%m/%d');
    my $result = $dbi->select(table => 'book', where => {issue_date => $tp});

When data is send to database, Time::Piece object is converted to database date format "2010-10-14"

When data is fetched, database date format is converted to Time::Piece object.

    my $row = $resutl->fetch_hash_first;
    my $tp = $row->{issue_date};

You can also use column name which contains table name.

    $dbi->select(
        table => 'book',
        where => {'book.issue_date' => $tp}
    );

In fetching, Filter is effective if you use "TABLE__COLUMN" as column name.

    my $result = $dbi->execute(
       "select issue_date as book__issue_date from book");

You can apply end filter execute after in filter.

    $dbi->apply_filter('book',
        issue_date => {out => 'tp_to_date', in => 'date_to_tp',
                       end => 'tp_to_displaydate'},
    );

Individual filter `filter`

You can apply individual filter . This filter overwrite the filter by apply_filter()

use filter option to apply individual filter when data is send to database. This option is used at insert(), update(), update_all(), delete(), delete_all(), select(), execute().

insert() example:

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

execute() example:

my $sql = <<"EOS"; select YEAR(issue_date) as issue_year from book where YEAR(issue_date) = {? issue_year} EOS

    my $result = $dbi->execute(
        $sql,
        param => {issue_year => '2010'},
        filter => {issue_year => 'tp_to_year'}
    );

You can also apply indivisual filter when you fetch row. use DBIx::Custom::Result's filter().

    $result->filter(issue_year => 'year_to_tp');

You can remove filter by remove_filter()

    $result->remove_filter;

End filtering : `end_filter()`

You can add filter at end. It is useful to create last output. use end_filter() to add end filter.

    $result->end_filter(issue_date => sub {
        my $tp = shift;
        
        return '' unless $tp;
        return $tp->strftime('%Y/%m/%d %h:%m:%s (%a)');
    });

In this example, Time::Piece object is converted to readable format.

You can remove end_filter by end_filter

    $result->remove_end_filter;

Automate applying filter : `each_column()`

It is useful to apply filter automatically at date type columns. You can use each_column() to process all column infos.

    $dbi->each_column(
        sub {
            my ($self, $table, $column, $info) = @_;
            
            my $type = $info->{TYPE_NAME};
            
            my $filter = $type eq 'DATE'     ? {out => 'tp_to_date', in => 'date_to_tp'}
                       : $type eq 'DATETIME' ? {out => 'tp_to_datetime', in => 'datetime_to_tp'}
                                             : undef;
            
            $self->apply_filter($table, $column, $filter)
              if $filter;
        }
    );

each_column() receive callback. callback arguments are DBIx::Custom object, table name, column name, column information. Filter is applied automatically by column type.

5. Tag

Basic of Tag

You can embedd tag into SQL.

    select * from book where {= title} and {like author};

{= title} and {like author} are tag. Tag has the folloring format.

    {TAG_NAME ARG1 ARG2 ...}

Tag start { and end }. Don't insert space between {} and tag name.

{ and } are reserved word. If you want to use these, escape it by '\';

    select from book \\{ ... \\}

\ is perl's escape character, you need two \.

Tag is expanded before executing SQL.

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

use execute() to execute SQL which contains tag

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

You can specify values embedded into place holder as hash reference using param option.

You can specify filter() at execute().

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

Note that at execute() the filter applied by apply_filter() don't has effective to columns. You have to use table tag in SQL

    my $sql = "select * from {table book} where {= author} and {like title};"

The following tag is available.

`table`

    {table NAME} -> NAME

This is used to specify table name in SQL. If you specify table name, Filtering by apply_filter() is effective.

`?`

    {? NAME}    ->   ?

`=`

    {= NAME}    ->   NAME = ?

`<>`

    {<> NAME}   ->   NAME <> ?

`<`

    {< NAME}    ->   NAME < ?

`>`

    {> NAME}    ->   NAME > ?

`>=`

    {>= NAME}   ->   NAME >= ?

`<=`

    {<= NAME}   ->   NAME <= ?

`like`

    {like NAME}   ->   NAME like ?

`in`

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

`insert_param`

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

`update_param`

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

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} and {< date};";

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

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

Register Tag : `register_tag()`

You can register custom tag. use register_tag() to register tag.

    $dbi->register_tag(
        '=' => sub {
            my $column = shift;
            
            return ["$column = ?", [$column]];
        }
    );

This is implementation of = tag. Tag format is the following one.

    {TAG_NAME ARG1 ARG2 ...}

In case = tag. Format is

    {= title}

So subroutine receive one argument "title". You have to return array reference in the following format.

    [
        String after expanding,
        [COLUMN1(This is used for place holder), COLUMN2 , ...]
    ]

First element is expanded stirng. In this example,

    'title = ?'

Secount element is array reference which is used to embedd value to place holder. In this example,

    ['title']

If there are more than one placeholders, This elements is multipul.

You return the following array reference.

    ['title = ?', ['title']]

See source of DBIx::Custom::Tag to see many implementation.

6. Dinamically 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}

Case2: Search only author

    where {= author}

Case3: Search title and author

    where {= title} and {=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'}, '{= author}']
    );

clause is the following format.

    ['or' or 'and', TAG1, TAG2, TAG3]

First argument is 'or' or 'and'. Later than first argument are tag names.

You can write more complex format.

    ['and', 
      '{= title}', 
      ['or', '{= author}', '{like date}']
    ]

This mean "{=title} and ( {=author} or {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 parameter name.

    my $where_clause = $where->to_string;

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

    where {= 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 tags 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 tags.

    $where->clause(
        ['and', '{> 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}', '{= author}']);
    $where->param({title => 'Perl'});
    my $result = $dbi->select(table => 'book', where => $where);

You can also pass it to where of update()�Adelete()

With `execute()`

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

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

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

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

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

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

Next, you create each model classes.

MyModel::book

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

MyModel::company

    package MyModel::company;
    
    use base 'MyModel';
    
    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 => $param);

Model is DBIx::Custom::Model.

If you need table name�Ayou 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_at, update_at(), delete_at(), select_at().

by filter you can define filters applied by apply_filter()

    $model->filter({
        title  => {out => ..., in => ..., end => ...},
        author => {out => ..., in => ..., end => ...}
    });

This filters is applied when include_model() is called.

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

This join is used by select(), select_at()

Class name, Model name, Table name

Class name, model name, and table name is a little different. Generally Class name is model name, and table name is model name.

    CLASS        MODEL              TABLE
    book         (CLASS) -> book    (MODEL) -> book

You can change model name.

    package MyModel::book;
    
    __PACAKGE__->attr(name => 'book_model');

    CLASS        MODEL         TABLE
    book         book_model    (MODEL) -> book_model

Model name is the name used by model() of DBIx::Custom.

    $dbi->model('book_model');

You can change table name.

    package MyModel::book;
    
    __PACAKGE__->attr(table => 'book_table');

    CLASS        MODEL              TABLE
    book         (CLASS) -> book    book_table

Table name is the table really accessed.

    $dbi->model('book')->insert(...); # access to "book_table"

Create column clause automatically : mycolumn(), column()

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

    my $column_clause = $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 $column_clause = $model->column('company');

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

    company.id as company__id, company.name as company__name

Create column clause automatically : column_clause()

To create column clause automatically, use column_clause(). Valude of table and columns is used.

    my $column_clause = $model->column_clause;

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.

If you remove some columns, use remove option.

    my $column_clause = $model->column_clause(remove => ['id']);

If you add some column, use add option.

    my $column_clause = $model->column_clause(add => ['company.id as company__id']);

Model Examples

Model examples

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

    __PACKAGE__->attr('primary_key' => sub { ['id'] };
    
    sub insert { ... }
    sub list { ... }

8. Improve performance

Create query

If you can't get performance, create query by query option. For example, many insert is needed.

    my $params = [
        {title => 'Perl', author => 'Ken'},
        {title => 'Good day', author => 'Tom'}
    ]
    my $query = $dbi->insert(table => 'book', param => $params->[0], query => 1);

Return value is DBIx::Custom::Query object. This query is executed by execute().

    foreach my $param (@$params) {
        $dbi->execute($query, $param);
    }

Performance is improved because statement handle is reused query option is used in insert(), update(), update_all(), delete(), delete_all().

Note that parameters count is same as method for creating query and execute().

You can create query from any SQL by create_query().

    my $query = $dbi->create_query(
        "insert into book {insert_param title author};";
    );

9. Other features

Add method

You can add method to DBIx::Custom object. use method().

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

You can call these methods from DBIx::Custom object.

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

Change result class

You can change result class. By default it is DBIx::Custom::Result.

    package MyResult;
    use base 'DBIx::Custom::Result';
    
    sub some_method { ... }

    1;
    
    package main;
    
    use MyResult;
    
    my $dbi = DBIx::Custom->connect(...);
    $dbi->result_class('MyResult');

Caching

SQL after parsing tag is cached for performance. You can set cache(). By default, chaching is true.

    $dbi->cache(1);

The way to cache is changed by cache_method(). Default method is the following one. Cache is saved to memory.

    $dbi->cache_method(sub {
        sub {
            my $self = shift;
            
            $self->{_cached} ||= {};
            
            if (@_ > 1) {
                # Save cache
                $self->{_cached}{$_[0]} = $_[1] 
            }
            else {
                # Get cache
                return $self->{_cached}{$_[0]}
            }
        }
    });

First argument is DBIx::Custom object. Second argument is SQL before parsing. Third argument is SQL information after parsing. This is hash reference.

If third argument exists, you save cache, and if third argument isn't exists, you get chace.

EXAMPLES

You can see exsamples in the following wiki.

DBIx::Custom Wiki - Many useful examples

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

GUIDE

1. Connect to database

2. Methods for insert, update, delete, or insert

insert()

update()

delete()

select()

execute()

insert by using primary key : insert_at()

Update by using primary key : update_at()

Delete by using primary key : delete_at()

Select by using primary key : select_at()

3. Fetch row

Fetch a row (array) : fetch()

Fetch only first row (array) : fetch_first()

Fetch rows (array) : fetch_multi()

Fetch all rows (array) : fetch_all

Fetch a row (hash) : fetch_hash()

Fetch only first row (hash) : fetch_hash_first()

Fetch rows (hash) : fetch_hash_multi()

Fetch all rows (hash) : fetch_hash_all()

Statement handle : sth()

4. Filtering

Register filter : register_filter()

Apply filter : apply_filter()

Individual filter filter

End filtering : end_filter()

Automate applying filter : each_column()

5. Tag

Basic of Tag

Tag list

table

?

=

<>

<

>

>=

<=

like

in

insert_param

update_param

Manipulate same name's columns

Register Tag : register_tag()

6. Dinamically create where clause

Dinamically create where clause : where()

In case where clause contains same name columns

With select()

With execute()

7. Model

Model

Class name, Model name, Table name

Create column clause automatically : mycolumn(), column()

Create column clause automatically : column_clause()

Model Examples

8. Improve performance

Create query

9. Other features

Add method

Change result class

Caching

EXAMPLES

Module Install Instructions

`insert()`

`update()`

`delete()`

`select()`

`execute()`

insert by using primary key : `insert_at()`

Update by using primary key : `update_at()`

Delete by using primary key : `delete_at()`

Select by using primary key : `select_at()`

Fetch a row (array) : `fetch()`

Fetch only first row (array) : `fetch_first()`

Fetch rows (array) : `fetch_multi()`

Fetch all rows (array) : `fetch_all`

Fetch a row (hash) : `fetch_hash()`

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

Fetch rows (hash) : `fetch_hash_multi()`

Fetch all rows (hash) : `fetch_hash_all()`

Statement handle : `sth()`

Register filter : `register_filter()`

Apply filter : `apply_filter()`

Individual filter `filter`

End filtering : `end_filter()`

Automate applying filter : `each_column()`

`table`

`?`

`=`

`<>`

`<`

`>`

`>=`

`<=`

`like`

`in`

`insert_param`

`update_param`

Register Tag : `register_tag()`

With `select()`

With `execute()`